【Oracle】曾經的Oracle學習筆記（8-15）

標籤：

一、資料庫建模

二、建表

三、資料庫字典

四、DML語句

五、視圖

六、索引

七、序列

八、DDL語句

 

Lesson 8 Overview of Data Modeling and Database Design
-----------------------------------------------------------------------------
資料庫建模

E-R圖

Entity 實體
對象實體
業務實體

Relationship 關係（這裡指的是實體與實體之間）

ER圖中:
# 唯一
* 非空

#* 唯一非空

o 沒有任何約束

 

one-to-one
one-to-many

may be

must be

建表的時候要遵循的範式

第一範式:表中要一個可以做主鍵的欄位(非空唯一)

 

第二範式：表中每一列的值都是不可分割的

第三範式：表中的某一個列不能依賴於其他非主鍵列（即非主鍵列的值得修改不會影響其他列上的值）

Lesson 9 Creating Tables
------------------------------------

建表

create table 表名(
列名1 資料類型 資料行層級條件約束,
列名2 資料類型 資料行層級條件約束,
列名3 資料類型 資料行層級條件約束,
列名4 資料類型 資料行層級條件約束
);

create table 表名(
列名1 資料類型 資料行層級條件約束,
列名2 資料類型 資料行層級條件約束,
列名3 資料類型 資料行層級條件約束,
列名4 資料類型 資料行層級條件約束,
表級約束1,
表級約束2
);

注:資料行層級條件約束和表級約束都是可以不寫的。
create table 表名(
列名1 資料類型,
列名2 資料類型,
列名3 資料類型,
列名4 資料類型
);

約束是指這個列上面的值要遵循什麼樣的要求。

主鍵約束
外鍵約束
唯一約束
非空約束
check約束

常用的資料類型:

number
varchar2
char
date

 

建表
create table student(
id number primary key,
name varchar2(200) not null,
age number,
birthday date
);

刪除表
drop table 表名;
drop table student;

create table customer(
id number primary key,
name varchar2(20) not null,
age number,
email varchar2(100) unique,
gender char check(gender in(‘f‘,‘m‘))
);

drop table customer;

 

constraint關鍵字
-------------------------
建表的時候可以給約束起一個名字,這個名字起的規律一般會是:表名_列名_約束名

如果沒有給約束起名字,那麼系統也會給這個約束起一個預設的名字,只不過這個預設的名字我們看了之後不知道它是哪張表裡面的哪個列上面的什麼約束。

create table customer(
id number constraint cus_id_pk primary key,
name varchar2(20) not null,
age number,
emile varchar2(100) unique,
gender char check(gender in(‘f‘,‘m‘))
);

查看目前使用者所建立的約束
select constraint_name
from user_constraints;

references關鍵字
-----------------------
外鍵的約束，引用某表的主鍵。

create table t_order(
id number primary key,
price number not null,
customer_id number references customer(id)
);

這個時候直接刪除customer表會報錯
因為這個表的主鍵id已經被t_order表引用過去做了外鍵
drop table customer;

1.可以先刪除order表,再刪除customer表
2.使用cascade constraints命令:
drop table customer cascade constraints;
這樣可以把customer表直接刪除掉,同時也把order表所聲明的外鍵約束也刪除了

使用表級約束:
create table customer(
id number,
name varchar2(20) not null,
age number,
email varchar2(100),
gender char,
constraint cus_id_pk primary key(id),
constraint cus_email_un unique(email),
constraint cus_gender_ck check(gender in(‘f‘,‘m‘))
);

 

create table t_order(
id number,
price number not null,
customer_id number,
constraint order_id_pk primary key(id),
constraint order_cus_id_fk foreign key(customer_id) references customer(id)
);

注意:not null 約束只能聲明為資料行層級條件約束

1.表級約束和資料行層級條件約束所寫的位置不一樣
2.not null約束不能用表級約束來聲明
3.表級約束和資料行層級條件約束聲明文法稍有所不同
4.如果要聲明的約束為聯合主鍵、聯合外鍵、聯合唯一的時候,就一定要用表級約束

 

//聲明的一個主鍵
//聯合外鍵和聯合唯一約束也是類似的寫法.
create table t_order(
id number,
price number not null,
customer_id number,
constraint order_id_pk primary key(id,price),
constraint order_cus_id_fk foreign key(customer_id) references customer(id)
);

Lesson 10 Orcale Data Ditionary
-------------------------------------
資料庫字典
作用：協助使用者瞭解當前資料庫的一些資訊或是對象的資訊以及使用者的資訊

view 視圖

USER開頭的視圖裡面存放著使用者自己擁有的對象
ALL開頭的視圖存放著使用者有許可權查看的對象
DBA開頭的視圖存放著資料庫所有的對象
V$開頭的視圖存放資料庫啟動並執行一些效能屬性資料

DICTIONARY
TABLE_PRIVILEGES
IND

通過desc命令來瞭解這些視圖中列的資訊，然後再去查詢混視圖找到想要的資訊

 

 

Lesson11 Manipulating Data
------------------------------------
DML語句

insert
update
delete
commit
savepoint
rollback

DML語句 :這個語句會修改資料庫表中的資料
insert update delelte

create table student (
id number primary key,
name varchar2(20) not null,
age number
);

//這樣表示向student表中全部插入資料
//插入資料的順序是按照建表的時候列的順序
insert into student values(1,‘tom‘,20);

//這樣是可以自己控制向表中那些列插入資料，以及插入資料的順序
insert into student (id,name) values(2,‘jerry‘);

 

DML語句執行的時候會產生事務,事務不提交,那麼我對資料所做的修改都是不能生效的,
事務提交,那麼之前對資料的修改就全部生效了(儲存到硬碟上了)
交易回復,那麼之前對資料的修改全部撤銷.

例如:
1 修改資料1
2 修改資料2
3 修改資料3
4 修改資料4
5 提交事務

在執行第一行DML語句的時候,就會產生了事務,之後的第2 3 4 行執行的DML也會放進第一行所產生的事務裡面,然後第五事務一提交,那麼這個事務裡面的所有操作全部生效了.
如果是第五行是復原了事務,那麼這個事務裡面的所有操作全部撤銷。

每個事務之間都是獨立的,相互不影響,一個事務裡面的所有操作要麼同時成功,要麼同時失敗(撤銷)。

注意：提交事務的命令commit，復原事務的命令式rollback，DDL語句也能把事務提交，相當於commit的效果。

例如:
1 修改資料1
2 修改資料2
3 修改資料3
4 修改資料4
5 執行DDL語句
6 rollback

這裡的第六行復原是沒有用的,因為第五行執行了DDL語句,已經把事務給提交了。

create table student (
id number primary key,
name varchar2(20) not null,
age number
);

insert語句
--------------------------
//這樣表示向student表中全部插入資料
//插入資料的順序是按照建表的時候列的順序
insert into student values(1,‘tom‘,20);

//這樣是可以自己控制向表中那些列插入資料，以及插入資料的順序
insert into student (id,name) values(2,‘jerry‘);

insert into student(id,name,age) values(&id,‘&name‘,&age);

update語句
-----------------
修改表中所有資料
update student
set name = ‘terry‘;

修改某一些資料
update student
set name = ‘terry‘
where id = 2;

若事務未提交，則此資料會鎖住，其他終端不能對此修改，若修改，則卡在那裡，除非到事務提交，則其他終端才能正常修改。

delete語句
-----------------
//刪除表中所有資料
delete from student;

//加入where條件限制,刪除某些資料
delete from student
where id=2;

 

savepoint 復原點
------------------

DML語句1
savepoint A
DML語句2
savepoint B
DML語句3

rollback to A/B

這個時候可以通過這個復原點讓交易回復到指定的位置,如果不指定復原點而是直接rollback,那麼事務會一下子復原完（撤銷整個事務）

lesson 14 view 視圖
-----------------------
視圖 view:
作用1:隱藏表中的重要資料
作用2:代替一些比較長的sql語句

//建立視圖
create view v_student
as
select *
from student
where id = 2;

//查看視圖內容
select *
from v_student;

視圖分為倆類:
簡單視圖:
視圖所代表的sql中如果沒有group by語句,沒有組函數,查詢的只有一張表,那麼這樣的視圖就是簡單視圖.
複雜視圖
視圖所代表的sql中如果有group by語句,或者有組函數,或者查詢的是多張表,那麼這樣的視圖就是複雜視圖.

 

簡單視圖和複雜視圖的區別:
1.簡單視圖可以修改,複雜視圖不能修改。
2.通過簡單視圖可以修改原來表中的資料,通過複雜視圖是不能修改原來的資料的。

 

Lesson 15 index 索引
-----------------------------
作用：提高在表查詢資料的效率

index是建立在列上面的

條件：
1.這個列中的資料會經常被用作查詢資料的條件
2.這個列上的資料不會經常的改動

注意：
1.有主鍵約束的列或者有唯一性限制式性的列上面，系統會預設的加上索引。
2.在一個表中，索引並非是越多越好
3.在表中的某一個列加入上了合適的索引，那麼也只有在資料量很大的時候，才能有所體現出這個查詢的效率。
4.索引一點建立成功，那麼之後這個索引就有系統來管理，我們自己是控制不了的。

 

索引的種類:
B-tree
位元影像
反序
函數

建立索引的格式:

create index 索引名
on 表名(列名);

刪除索引
drop index 索引名;

 

Lesson 13 Creating Sequences 序列
-------------------------------
作用：幫我們產生主鍵列的值（非空唯一）

建立序列:
一般不需要設定sequence的屬性,使用預設的方式去建立就可以了.
create sequence 序列名;

如果需要設定屬性,那麼就加上下面的語句.
[INCREMENT BY n] 每次拿出值加多少
[START WITH n] 初始值從幾開始
[{MAXVALUE n | NOMAXVALUE}] 最大值
[{MINVALUE n | NOMINVALUE}] 最小值
[{CYCLE | NOCYCLE}] 到了最大值後是否迴圈
[{CACHE n | NOCACHE}] 每次在緩衝裡面放多少個值.

 

例子:
create sequence my_seq;

//獲得序列中的下一個值
//這個值對應當前這個序列來說,肯定是非空唯一
select my_seq.nextval
from dual;

//查詢序列中當前的值是多少
select my_seq.currval
from dual;

//在插入表中的主鍵列的時候會經常使用到序列
insert into student(id,name,age) values(my_seq.nextval,‘tom‘,20);
insert into student(id,name,age) values(my_seq.nextval,‘&name‘,&age);

 

drop sequence 序列名;

 

SYNONYM 同義字
synonym

student

my_stu

//給表student建立一個同義字
create synonym my_stu
for student;

//因為一般使用者沒有建立public synonym
的許可權,所有我們需要用dba的身份登入到資料庫中去建立
//sqlplus "/as sysdba"
create public synonym my_stu
for tom.student;

grant select on my_stu to public;//授權

 

一個新的使用者至少有一個什麼許可權才能登入到資料庫裡面?
create session

Lesson 12 Altering Tables 修改表結構
------------------------------------------------
表結構的修改:

alter

在表中添加一個新的列
alter table student
add birthday date;

刪除表的某列
alter table student
drop column birthday;

修改表的名字:
rename student to mystudent;

修改表中某列的類型
ALTER TABLEstudent
MODIFY (name VARCHAR2(500));

讓約束失效:
必須知道約束的名字
ALTER TABLE s_emp
DISABLE CONSTRAINT s_emp_id_pk CASCADE;

讓失效的約束再次生效
ALTER TABLE s_emp
ENABLE CONSTRAINT s_emp_id_pk;

 

rownum 偽列
-----------------------------------
rownum特點：
1.rownum是一個隱藏的行號
2.oracle裡面專屬，其他資料庫沒有
3.每個表裡面或者查詢的結果裡面都存在

rownum的操作特點：
1.能夠<任何數，
2.只能>0，大於的數不是0的話拿不到任何資料
3.只能=1，等於的數不是1的話拿不到任何資料

select rownum,last_name
from s_emp
where rownum>0;

 

select rownum,last_name
from s_emp;

 

 

我有數十萬條資料的地址，看清是數十萬條資料，必須要能夠批量查詢
我要提供資料樣本供客戶查詢，但暫時不能給他顯示具體的數字牌號
我希望能把這些數字隱藏掉或替換成*號，該如何?呢？

資料表格式如：南京路123弄456號7890室
希望查詢後顯示：南京路***弄***號****室

該如何寫該select語句呢？

select replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(地址,‘0‘,‘*‘),‘1‘,‘*‘),‘2‘,‘*‘),‘3‘,‘*‘),‘4‘,‘*‘),‘5‘,‘*‘),‘6‘,‘*‘),‘7‘,‘*‘),‘8‘,‘*‘),‘9‘,‘*‘)

 

【Oracle】曾經的Oracle學習筆記（8-15）