Oracle筆記系列這幾篇是來(lái)自一位老師的筆記，分享給大家放在簡(jiǎn)書(shū)上阔逼，主要方便自己時(shí)常復(fù)習(xí)邮绿，還有學(xué)習(xí)Oracle的伙伴參考。（@來(lái)自邵永波老師整理分享）
第十章：數(shù)據(jù)字典(了解)
作用:幫助用戶(hù)了解當(dāng)前數(shù)據(jù)庫(kù)的一些信息或是對(duì)象的信息或是用戶(hù)的信息.
1).數(shù)據(jù)字典在數(shù)據(jù)庫(kù)被創(chuàng)建時(shí)創(chuàng)建的镀层。
2).數(shù)據(jù)字典中的數(shù)據(jù)被數(shù)據(jù)庫(kù)服務(wù)器自動(dòng)更新和維護(hù)
1.常見(jiàn)的數(shù)據(jù)字典(它們都是視圖)
USER開(kāi)頭的視圖里面存放著用戶(hù)自己擁有的對(duì)象(表镰禾、視圖、索引唱逢、同義詞吴侦、序列)
ALL開(kāi)頭的視圖存放著用戶(hù)有權(quán)限查看的對(duì)象
DBA開(kāi)頭的視圖存放著數(shù)據(jù)庫(kù)所有的對(duì)象
V$開(kāi)頭的視圖存放數(shù)據(jù)庫(kù)運(yùn)行的一些性能屬性數(shù)據(jù)
2.以u(píng)ser開(kāi)頭的數(shù)據(jù)字典
包含當(dāng)前用戶(hù)所擁有的相關(guān)對(duì)象信息。
//查詢(xún)用戶(hù)擁有的所有表的名字
select table_name
from user_tables;
//查詢(xún)用戶(hù)對(duì)象表,找出對(duì)象類(lèi)型是TABLE類(lèi)型的對(duì)象名字
//table view sequence index(只要是唯一的列坞古，默認(rèn)索引) synonym等都是oracle中的對(duì)象
//注意【字符串的值是區(qū)分大小寫(xiě)】的
select object_name
from user_objects
where object_type = upper('table');
//查詢(xún)用戶(hù)對(duì)象表,找出對(duì)象類(lèi)型的類(lèi)型都有哪些
select distinct object_type
from user_objects;
//查詢(xún)出s_emp表中的列及其對(duì)應(yīng)的約束名字
select constraint_name, column_name
from user_cons_columns
where table_name = 'S_EMP';
//查詢(xún)出s_emp表中的約束名字
select constraint_name
from user_constraints
where table_name = 'S_EMP';
3.以all開(kāi)頭的數(shù)據(jù)字典
包含當(dāng)前用戶(hù)有權(quán)限訪(fǎng)問(wèn)的所有對(duì)象的信息
//查到當(dāng)前用戶(hù)有權(quán)限訪(fǎng)問(wèn)的對(duì)象
select table_name from all_tables;?
4.以dba開(kāi)頭的數(shù)據(jù)字典
包含數(shù)據(jù)庫(kù)所有相關(guān)對(duì)象的信息备韧。
//只能是有dba權(quán)限的用戶(hù)查詢(xún),能查到數(shù)據(jù)庫(kù)中所有對(duì)象
select table_name from dba_tables;
(conn切換用戶(hù),輸入用戶(hù)名system,? 輸入密碼oracle)
5.其他視圖
dictionary視圖中只有倆列:
TABLE_NAME 表示當(dāng)前【表的名字】
COMMENTS 表示對(duì)這個(gè)【表的描述】
SQL> desc dictionary
名稱(chēng)
--------------------
TABLE_NAME
COMMENTS
select *
from dictionary
where table_name='USER_TABLES';
select *
from dictionary
where table_name='ALL_TABLES';
select table_name
from dictionary
where table_name like 'USER%';
select table_name
from dictionary
where table_name like 'V$%';
第十一章：DML語(yǔ)句 和 事務(wù)控制
主要內(nèi)容:
DML語(yǔ)句，直接操作數(shù)據(jù)庫(kù)對(duì)象 里面的數(shù)據(jù)
insert into
update
delete
事務(wù)控制語(yǔ)句
commit
savepoint
rollback
sql語(yǔ)句的分類(lèi):
select查詢(xún)語(yǔ)句
DML insert update delete
DDL create alter drop truncate rename
DCL
事務(wù)控制語(yǔ)句 【針對(duì)DML】
DML: insert update delete
測(cè)試用表: 沒(méi)有主外鍵關(guān)聯(lián)
create table t_user(
id number,
name varchar2(50) constraint user_name_nn not null,
email varchar2(50),
gender char(1),
age number,
birthday date,
constraint user_id_pk primary key(id),
constraint user_email_un unique(email),
constraint user_gender_ck check(gender in('f','m'))
);
drop table t_user;
1.insert語(yǔ)句學(xué)習(xí)
向表中插入數(shù)據(jù):
格式：【insert into 表名[(列名1,列名2,...)] values(對(duì)應(yīng)值1痪枫，對(duì)應(yīng)值2,...);】
注意：在插入數(shù)據(jù)時(shí)织堂，要特別【留意約束條件】。
1).默認(rèn)是向表中的每一個(gè)列中【依次】插入數(shù)據(jù)
insert into t_user values(1,'tom','abc','f',20,'11-8月-98');
注意:違反任意一種約束那么就插入數(shù)據(jù)失敗
2).也可以指明向表中的哪些列插入數(shù)據(jù)
insert into t_user(id,name,email,gender,age,birthday)? values(2,'tom','abc1','f',20,'11-8月-98');
注意:可以任意交換下面列名的位置,只有values語(yǔ)句中的值也對(duì)應(yīng)交換即可
3).列的值可以是null的話(huà),那么也在插入的時(shí)候不指定這個(gè)列
注意:【unique約束】和【check約束】的值,都【可以為null】
【主鍵約束】和【非空約束】的值,都不可以為null
insert into t_user(id,name,email,gender) values(3,'tom','abc3','f');
insert into t_user(id,name,email) values(3,'tom','abc4');
insert into t_user(id,name) values(5,'tom');
使用運(yùn)行時(shí)參數(shù)設(shè)置需要輸入表中的值
insert into t_user(id,name) values(&id,'&name');
2.insert插入的特殊情況
我們可以把【select語(yǔ)句查詢(xún)結(jié)果】插入到表中奶陈！
但是的有個(gè)【前提易阳，就是查詢(xún)列】的順序和要插入表中列的【順序一致】(數(shù)據(jù)類(lèi)型)。另外查詢(xún)結(jié)果值要【滿(mǎn)足其他約束】條件吃粒。
insert into t_user(id,name,birthday)
select id,last_name,start_date
from s_emp;
3.update語(yǔ)句
格式： update 表名 set 列名=值[,列名=值,...]
? [where ...]
1).修改表中所有行的age值為20歲
update t_user set age=20;
2).修改表中所有數(shù)據(jù)的age和gender的值
update t_user set age=25,gender='m';
3).修改表中id小于10數(shù)據(jù)的age和gender的值為null
update t_user set age=null,gender=null where id<10;
4).修改id為18的用戶(hù)的名字為zhangsan
update t_user set name='zhangsan' where id=18;
4.delete語(yǔ)句
格式：delete from 表名 [where ...];
1).刪除表中id大于20的用戶(hù)信息
delete from t_user where id>20;
2).刪除名字為張三的用戶(hù)信息
delete from t_user where name='zhangsan';
3).刪除表中所有的數(shù)據(jù)
delete from t_user;
以上是DML語(yǔ)句(insert update delete)語(yǔ)句的基本用法,下面我們重點(diǎn)測(cè)試一下【主外鍵相關(guān)的DML操作】潦俺。
5.進(jìn)一步使用DML操作
測(cè)試主外鍵關(guān)聯(lián)的情形
先準(zhǔn)備兩個(gè)表，里面有主外鍵。
create table t_customer(
id number,
name varchar2(20) constraint customer_name_nn not null,
constraint customer_id_pk primary key(id)
);
create table t_order(
id number,
price number,
customer_id number,
constraint order_id_pk primary key(id),
constraint order_cid_fk foreign key(customer_id) references t_customer(id)
);
drop table t_order;
drop table t_customer;
1).準(zhǔn)備操作數(shù)據(jù)insert語(yǔ)句：
//t_customer表中插入數(shù)據(jù)
insert into t_customer(id,name) values(1,'tom1');
insert into t_customer(id,name) values(2,'tom2');
insert into t_customer(id,name) values(3,'tom3');
2).往t_order表中插入正常數(shù)據(jù)
//customer_id外鍵列的值必須是t_customer表中出現(xiàn)過(guò)的
insert into t_order(id,price,customer_id) values(1,1000,1);
insert into t_order(id,price,customer_id) values(2,2000,2);
3).表中插入無(wú)效數(shù)據(jù)
//插入出錯(cuò),因?yàn)?這個(gè)值并沒(méi)有在t_customer表中出現(xiàn)過(guò)的
insert into t_order(id,price,customer_id) values(3,3000,6);
4).其他注意事項(xiàng)
//t_order表中插入數(shù)據(jù)
//默認(rèn)情況下,【外鍵列值可以為空】
insert into t_order(id,price,customer_id) values(3,3000,null);
insert into t_order(id,price) values(4,4000);
注意:如果在外鍵列上加一個(gè)非空約束,那么這個(gè)外鍵列的值就不能為null了【可以給一個(gè)列上添加多種約束】
//t_order表中插入數(shù)據(jù)
//默認(rèn)情況下,【外鍵列上值可以重復(fù)】
insert into t_order(id,price,customer_id) values(5,5000,1);
insert into t_order(id,price,customer_id) values(6,6000,1);
注意:如果在外鍵列上加一個(gè)唯一約束,那么這個(gè)外鍵列的值就不能重復(fù)了(可以給一個(gè)列上添加多種約束)
6.update語(yǔ)句
1).準(zhǔn)備工作
把倆個(gè)測(cè)試表刪除黑竞，然后重新創(chuàng)建,再往表中插入一些數(shù)據(jù)
drop table t_order;
drop table t_customer;
//t_customer表中插入數(shù)據(jù)
insert into t_customer(id,name) values(1,'tom1');
insert into t_customer(id,name) values(2,'tom2');
insert into t_customer(id,name) values(3,'tom3');
//t_order表中插入數(shù)據(jù)
insert into t_order(id,price,customer_id) values(1,1000,1);
insert into t_order(id,price,customer_id) values(2,2000,2);
2).修改指定列的值
//把t_order表中id=1的數(shù)據(jù)的customer_id列修改為3
update t_order set customer_id = 3 where id = 1;
//把t_order表中id=1的數(shù)據(jù)的customer_id列修改為null
update t_order set customer_id = null where id = 1;
注意捕发，主外鍵約束相關(guān)的列，修改的值一定要符合要求很魂。
//把t_order表中id=1的數(shù)據(jù)的customer_id列修改為20
update t_order set customer_id = 20 where id = 1;
sql執(zhí)行出錯(cuò),因?yàn)榫蜎](méi)id=20的顧客
7.delete語(yǔ)句
1).普通刪除情況
//刪除t_order表中的的所有數(shù)據(jù)
delete from t_order;
可以成功刪除,沒(méi)有問(wèn)題,因?yàn)閯h除t_order不會(huì)對(duì)t_costomer表的數(shù)據(jù)產(chǎn)生任何影響.
//t_order表中插入數(shù)據(jù)
insert into t_order(id,price,customer_id) values(1,1000,1);
insert into t_order(id,price,customer_id) values(2,2000,2);
//刪除t_customer表中id=3的數(shù)據(jù)
delete from t_customer where id = 3;
可以刪除成功,t_order表中外鍵列中沒(méi)有引用過(guò)這個(gè)值
2).刪除時(shí)候特殊情況
//刪除t_customer表中id=1的數(shù)據(jù)
delete from t_customer where id = 1;
刪除失敗,因?yàn)閠_order表中外鍵列中已經(jīng)引用了這個(gè)值
8.on delete語(yǔ)句
【在上述那樣的情況下,on delete 語(yǔ)句就可以起作用了】
級(jí)聯(lián)cascade針對(duì)表的刪除扎酷， on delete針對(duì)表中列的刪除
on delete語(yǔ)句
on delete no action【默認(rèn)情況:什么不都寫(xiě)】
on delete cascade
on delete set null
1).情形1測(cè)試，默認(rèn)情況下的on delete
如果在【建外鍵的時(shí)候,不加on delete語(yǔ)句,就是on delete no action】
例如:
先建立兩張表遏匆，主外鍵關(guān)聯(lián)
create table t_customer(
id number,
name varchar2(20) constraint customer_name_nn not null,
constraint customer_id_pk primary key(id)
);
create table t_order(
id number,
price number,
customer_id number,
constraint order_id_pk primary key(id),
constraint order_cid_fk foreign key(customer_id) references t_customer(id)
);
//drop table t_order;
//drop table t_customer;
然后插入測(cè)試數(shù)據(jù):
//t_customer表中插入數(shù)據(jù)
insert into t_customer(id,name) values(1,'tom1');
insert into t_customer(id,name) values(2,'tom2');
insert into t_customer(id,name) values(3,'tom3');
//t_order表中插入數(shù)據(jù)
insert into t_order(id,price,customer_id) values(1,1000,1);
insert into t_order(id,price,customer_id) values(2,2000,2);
刪除主鍵列數(shù)據(jù)
delete from t_customer where id = 1;
刪除失敺òぁ： ORA-02292: 【違反完整約束條件】 - 已找到子記錄
2).情形2測(cè)試 on delete cascade(應(yīng)用在外表)
先建立兩張表出來(lái)，
【聲明外鍵列的時(shí)候在最后加上on delete cascade語(yǔ)句】
create table t_order(
id number,
price number,
customer_id number,
constraint order_id_pk primary key(id),
constraint order_cid_fk foreign key(customer_id) references t_customer(id) on delete cascade
);
表中插入數(shù)據(jù)幅聘，為刪除做準(zhǔn)備凡纳；
最后做和上述操作相同的刪除測(cè)試；
刪除主表中 id為1的數(shù)據(jù)(外表中使用了1這個(gè)值)
delete from t_customer where id = 1;
刪除成功,同時(shí)級(jí)聯(lián)(cascade)刪除了t_order表中所關(guān)聯(lián)的那條數(shù)據(jù)帝蒿。
注意荐糜，【on delete 語(yǔ)句出現(xiàn)在外鍵列約束 后面】。
3).情形3葛超，on delete set null
刪除兩張表暴氏，重新建立，在聲明外鍵列的時(shí)候加入on delete set null語(yǔ)句
create table t_order(
id number,
price number,
customer_id number,
constraint order_id_pk primary key(id),
constraint order_cid_fk foreign key(customer_id) references t_customer(id) on delete set null
);
insert插入測(cè)試數(shù)據(jù)绣张；
開(kāi)始刪除測(cè)試答渔，刪除主表中id為1的行。
delete from t_customer where id = 1;
刪除成功侥涵；
同時(shí)我們會(huì)發(fā)現(xiàn),t_order表中所關(guān)聯(lián)的那條數(shù)據(jù)的【外鍵值已經(jīng)被設(shè)置為null】
****************
1.數(shù)據(jù)庫(kù)事務(wù) DML
數(shù)據(jù)庫(kù)操作過(guò)程中沼撕，如果操作有誤，可以撤銷(xiāo)相應(yīng)操作(一系列);如果反復(fù)確定操作無(wú)誤芜飘，可以使之生效务豺。
關(guān)鍵字:
commit提交事務(wù);
rollback回滾事務(wù);
savepoint設(shè)置回滾點(diǎn);
2.事務(wù)的產(chǎn)生與結(jié)束：
1).【DML語(yǔ)句執(zhí)行】的時(shí)候,如果當(dāng)前有事務(wù),那么就使用這個(gè)事務(wù);如果當(dāng)前沒(méi)有事務(wù),這個(gè)執(zhí)行的【DML就會(huì)產(chǎn)生一個(gè)新的事務(wù)】；
sqlplus test/test
insert
update
delete
create table ...;
2).只有DML語(yǔ)句才會(huì)產(chǎn)生事務(wù),【其他語(yǔ)句不會(huì)產(chǎn)生事務(wù)】燃箭；
3).commit/rollback/DDL語(yǔ)句都可以把當(dāng)前事務(wù)給結(jié)束掉冲呢；
4).【commit和DDL語(yǔ)句】結(jié)束事務(wù)的方式是把這個(gè)【事務(wù)提交】；
5).【rollback】結(jié)束事務(wù)的方式是把這個(gè)【事務(wù)回滾】招狸。
注意:
【提交事務(wù)】是指讓這個(gè)事務(wù)里面的【所有操作都生效】到數(shù)據(jù)庫(kù)中；
【回滾】事務(wù)是指【撤銷(xiāo)】這個(gè)事務(wù)里所有操作邻薯。
3.具體測(cè)試
測(cè)試用表:
create table t_customer(
id number,
name varchar2(20) constraint customer_name_nn not null,
constraint customer_id_pk primary key(id)
);
drop table t_customer;
具體測(cè)試步驟:
1).使用倆個(gè)終端窗口,同一個(gè)賬號(hào)登錄到數(shù)據(jù)庫(kù)中,觀察事務(wù)是否提交對(duì)用戶(hù)查看數(shù)據(jù)的影響
注:一個(gè)用戶(hù)對(duì)A表做了DML操作,但是沒(méi)有提交事務(wù),這時(shí)候別的用戶(hù)是不能對(duì)A表再做其他的DML操作裙戏。(為了保證數(shù)據(jù)的安全和一致性)
insert into t_customer(id,name) values(1,'tom1');
insert into t_customer(id,name) values(2,'tom2');
2).事務(wù)實(shí)例1
insert ....產(chǎn)生事務(wù)A
update ... 這個(gè)操作是事務(wù)A中的操作
insert ..? 這個(gè)操作是事務(wù)A中的操作
commit;? ? 讓事務(wù)A里面的三個(gè)操作生效、事務(wù)A結(jié)束
delete ... 產(chǎn)生新的事務(wù)B
insert ..? 這個(gè)操作是事務(wù)B中的操作
insert ..? 這個(gè)操作是事務(wù)B中的操作
insert ..? 這個(gè)操作是事務(wù)B中的操作
rollback;? 讓事務(wù)B中的四個(gè)操作都撤銷(xiāo),事務(wù)B結(jié)束
3).事務(wù)實(shí)例2
insert ....產(chǎn)生事務(wù)A
update ... 這個(gè)操作是事務(wù)A中的操作
insert ..? 這個(gè)操作是事務(wù)A中的操作
DDL語(yǔ)句;? 事務(wù)A會(huì)被提交
rollback;? 這時(shí)候回滾已經(jīng)對(duì)事務(wù)A不起作用,因?yàn)槭聞?wù)A以及被提交了
注:create語(yǔ)句 drop語(yǔ)句 alter語(yǔ)句,truncate,rename等都屬于DDL語(yǔ)句
4).回滾點(diǎn)/保存點(diǎn) savepoint
例如:
DML語(yǔ)句1
savepoint A
DML語(yǔ)句2
savepoint B
DML語(yǔ)句3
rollback to A/B
這個(gè)時(shí)候可以通過(guò)這個(gè)回滾點(diǎn)讓事務(wù)回滾到指定的位置,如果不指定回滾點(diǎn)而是【直接rollback】,那么【事務(wù)會(huì)一下子回滾完】厕诡。
特別注意:
【rollback to】到回滾點(diǎn)之后,這個(gè)【事務(wù)可能并沒(méi)結(jié)束】,這個(gè)時(shí)候還可以接著回滾或者commit提交事務(wù)累榜。
create table t_user(
id number primary key,
name varchar2(100),
salary number
);
drop table t_user;
例如:
insert into t_user values(1,'tom',1000);
savepoint A;
insert into t_user(id,name) values(2,'zs');
savepoint B;
delete from t_user;
rollback to B;
然后查詢(xún)看結(jié)果
select * from t_user;
4.事務(wù)特征ACID(了解)
原子性：Atomicity
同時(shí)成功或者同時(shí)失敗
一致性：Consistency
事務(wù)執(zhí)行的結(jié)果必須是使數(shù)據(jù)庫(kù)從一個(gè)一致性狀態(tài)變到另一個(gè)一致性狀態(tài)。
隔離性：Isolation
事務(wù)操作應(yīng)該相互獨(dú)立
持久性：Durability
事務(wù)所做的影響 ，在事務(wù)結(jié)束之后應(yīng)該能夠是持久的壹罚。
5.isolation事務(wù)隔離級(jí)別(了解)
事務(wù)中會(huì)產(chǎn)生的問(wèn)題:
1).臟讀? 主要針對(duì)update操作葛作。 一個(gè)事務(wù)A讀到另一個(gè)事務(wù)B中修改過(guò)但是還沒(méi)有提交的數(shù)據(jù)
2).不可重復(fù)讀? 主要針對(duì)update操作。 一個(gè)事務(wù)A在第一次讀數(shù)據(jù)和第二次讀數(shù)據(jù)之間,有另一個(gè)事務(wù)B把這個(gè)數(shù)據(jù)更改并提交了,所以就出現(xiàn)了事務(wù)A里面讀一個(gè)數(shù)據(jù)倆次,但是讀到的結(jié)果是不同的猖凛。
3).幻讀? 主要針對(duì)的是insert/delete操作赂蠢。事務(wù)A第一次用where條件篩選出了10條數(shù)據(jù),事務(wù)A第二次用同樣的where條件篩選出的卻是11條數(shù)據(jù),因?yàn)槭聞?wù)B在事務(wù)A的第一次和第二次查詢(xún)直接進(jìn)行了插入操作,并且插入的這個(gè)數(shù)據(jù)滿(mǎn)足事務(wù)A的where篩選條件.
事務(wù)隔離級(jí)別有:
read-uncommitted? 不提交也能讀
read-committed? ? 提交之后才能讀 解決了臟讀
repeatable-read? 解決了臟讀和不可重復(fù)讀
serializable? ? ? 三個(gè)問(wèn)題都解決了
【級(jí)別越高解決的問(wèn)題越多但是效率越低】。
注意:并不是所有數(shù)據(jù)庫(kù)都支持這四種事務(wù)隔離級(jí)別,比如【oracle就只支持第二種和第四種】這倆種,比如mysql就四種全支持.
oracle里面【默認(rèn)的事務(wù)隔離級(jí)別是第二種】:read-committed
oralce里面設(shè)置事務(wù)隔離級(jí)別:
Set Transaction Isolation Level Read Uncommitted
Set Transaction Isolation Level Read Committed
Set Transaction Isolation Level Read Repeatable
Set Transaction Isolation Level Serializable
此知識(shí)點(diǎn)重點(diǎn)了解辨泳，后期框架的學(xué)習(xí)會(huì)具體涉及虱岂。
******************
第十二章: 修改表和約束(alter語(yǔ)句)?
alter也屬于DDL語(yǔ)句。
update修改菠红，修改數(shù)據(jù)庫(kù)表|視圖中數(shù)據(jù)第岖；
alter修改對(duì)象結(jié)構(gòu)。
1.準(zhǔn)備測(cè)試用表
create table t_user(
id number constraint user_id_pk primary key,
name varchar2(100),
salary number
);
//drop table t_user;
2.alter具體作用
alter可以修改表的結(jié)構(gòu)试溯，具體格式為:
alter table 表名 add|drop|modify|disable|enable ...;
2.1 在表中【添加一個(gè)新列】
alter table t_user
add birthday date;
2.2 【刪除表的某列】
alter table t_user
drop column birthday;
2.3 給表中的【列添加約束】
這個(gè)約束相當(dāng)于之前的表級(jí)約束
alter table t_user
add constraint user_name_un
unique(name);
//測(cè)試剛添加的唯一約束是否生效
insert into t_user(id,name) values(1,'zs');
insert into t_user(id,name) values(2,'zs');
2.4 【刪除表中的約束】
alter table t_user
drop constraint user_name_un;
2.5 修改表中某列的類(lèi)型
alter table t_user
modify name varchar2(100) not null;
2.6 讓【約束失效】(必須知道約束的名字)
alter table t_user
disable constraint user_id_pk cascade;
(該主鍵可能被其他表引用 作為外鍵使用蔑滓，cascade級(jí)聯(lián)會(huì)使之失效)
//測(cè)試是否設(shè)置成功
insert into t_user(id,name) values(1,'zs1');
insert into t_user(id,name) values(1,'zs2');
2.7 讓失效的【約束再次生效】
alter table t_user
enable constraint user_id_pk;
3.其他DDL操作
truncate截?cái)?/p>
rename重命名
3.1 截?cái)啾碇械臄?shù)據(jù)
截取不需要提交,默認(rèn)已經(jīng)提交,并且不能回滾
truncate table t_user;
相當(dāng)于(將表中數(shù)據(jù)全部刪除，然后提交):
delete from t_user;
commit;
3.2 修改表的名字
rename t_user to mytest;
rename mytest to t_user;
4.注釋操作(了解)
comment是oracle數(shù)據(jù)庫(kù)系統(tǒng)關(guān)鍵字遇绞，可以用來(lái)給表或列添加注釋?zhuān)欣谟脩?hù)對(duì)表結(jié)構(gòu)以及數(shù)據(jù)庫(kù)對(duì)象含義的理解烫饼。
用戶(hù)可以利用數(shù)據(jù)字典查看comment定義的信息。
1).給表添加注釋
格式:
comment on table 表名 is '注釋';
comment on table t_user is '很好';
2).給列添加注釋
格式:
comment on column 表名.列名 is '注釋';
comment on column t_user.name is 'good';
3).查看表中注釋(利用【數(shù)據(jù)字典】)
select * from user_tab_comments
where table_name=upper('t_user');
4).查看列中的注釋
select * from user_col_comments
where
comments is not null
and
table_name=upper('t_user');
對(duì)象: 表 視圖 索引 序列 同義詞
第十三章: 序列
Sequence 序列
作用:幫用戶(hù)自動(dòng)生成主鍵列的值(非空唯一)
currval? nextval
序列名.currval;
序列名.nextval;
1. 創(chuàng)建序列
一般不需要設(shè)置sequence的屬性,使用默認(rèn)的方式去創(chuàng)建就可以了.
基本格式:
create sequence 序列名;
完整格式:
create sequence 序列名
[INCREMENT BY n]? 每次拿出值加多少
[START WITH n]? ? 初始值從幾開(kāi)始
[{MAXVALUE n | NOMAXVALUE}]? 最大值
[{MINVALUE n | NOMINVALUE}]? 最小值
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]
如果需要設(shè)置屬性,那么就加上下面的語(yǔ)句.
[INCREMENT BY n]? 每次拿出值加多少
[START WITH n]? ? 初始值從幾開(kāi)始
[{MAXVALUE n | NOMAXVALUE}]? 最大值
[{MINVALUE n | NOMINVALUE}]? 最小值
[{CYCLE | NOCYCLE}]? 到了最大值后是否循環(huán)(如果【循環(huán)會(huì)從1開(kāi)始】,不循環(huán)出錯(cuò))
[{CACHE n | NOCACHE}] 每次在緩存里面放多少個(gè)值.
2.具體實(shí)例
1).創(chuàng)建序列并設(shè)置屬性
create sequence seq_test
increment by 2
start with 44
maxvalue 60
minvalue 10
cycle
nocache;
使用默認(rèn)屬性創(chuàng)建序列
create sequence seq_test;
2).刪除序列(格式類(lèi)似 刪除表)
drop sequence seq_test;
3).具體序列操作
對(duì)應(yīng)序列,我們只有倆種操作:
a.獲得序列中的下一個(gè)值 【序列名.nextval】
//這個(gè)值對(duì)于當(dāng)前這個(gè)序列來(lái)的其他值來(lái)說(shuō),肯定是非空唯一
select seq_test.nextval
from dual;
b.查詢(xún)序列中當(dāng)前的值是多少【序列名.currval】
select seq_test.currval
from dual;
4).實(shí)際應(yīng)用
向t_user表插入數(shù)據(jù),其中id值可以需要生成
create table t_user(
id number constraint user_id_pk primary key,
name varchar2(100),
salary number
);
drop table t_user;
//創(chuàng)建序列
drop sequence seq_test;
create sequence seq_test;
//插入數(shù)據(jù) 使用序列產(chǎn)生id值
insert into t_user(id,name,salary)
values(seq_test.nextval,concat('tom',seq_test.currval),2000);
5).通過(guò)數(shù)據(jù)字典 查詢(xún)當(dāng)前用戶(hù)中 已經(jīng)定義的序列
select sequence_name
from user_sequences;
第十四章: 視圖view
1.視圖概念
視圖就是【提取一張或者多張表的數(shù)據(jù)】生成一個(gè)映射试读，操作視圖可以達(dá)到【操作原表】的效果杠纵，【方便數(shù)據(jù)的管理】以及實(shí)現(xiàn)【更安全】操作。
2.視圖的作用:
a.安全性 隱藏表中的重要數(shù)據(jù)
b.簡(jiǎn)單性 代替一些比較長(zhǎng)的sql語(yǔ)句
3.視圖的分類(lèi)
視圖可以分為兩類(lèi)钩骇，簡(jiǎn)單視圖和復(fù)雜視圖比藻。
簡(jiǎn)單視圖:
? 視圖所代表的select語(yǔ)句中基于單個(gè)基表，且不包含函數(shù)和數(shù)據(jù)分組操作倘屹。
復(fù)雜視圖
? 視圖所代表的select中如果【有g(shù)roup by語(yǔ)句】,或者【有組函數(shù)】,或者【查詢(xún)的是多張表】,那么這樣的視圖就是復(fù)雜視圖.
注意: 僅僅由多表查詢(xún)的復(fù)雜視圖银亲，可以刪除數(shù)據(jù)成功，但是【刪除的是外表】 中的數(shù)據(jù)纽匙，主表不會(huì)受到影響务蝠。
4.簡(jiǎn)單視圖和復(fù)雜視圖的區(qū)別
通過(guò)【簡(jiǎn)單視圖可以修改】原來(lái)表中的數(shù)據(jù), 這些修改包括插入數(shù)據(jù)，更新數(shù)據(jù)和刪除數(shù)據(jù)烛缔。
但是對(duì)于【復(fù)雜視圖】來(lái)說(shuō), 通過(guò)視圖修改數(shù)據(jù)必須滿(mǎn)足一定的規(guī)則馏段。
復(fù)雜視圖是可以刪除數(shù)據(jù)的，會(huì)影響到原表践瓷。
? 可以插入數(shù)據(jù)院喜，但是有一定限制。
5.創(chuàng)建與刪除視圖
創(chuàng)建視圖(【由select語(yǔ)句獲得視圖】)
CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW view_name
AS
SELECT查詢(xún)
[WITH CHECK OPTION]
[WITH READ ONLY];
OR REPLACE：如果視圖已經(jīng)存在晕翠，則替換舊視圖喷舀。
? ? FORCE：即使基表不存在，也可以創(chuàng)建該視圖，但是該視圖不能正常使用硫麻，當(dāng)基表創(chuàng)建成功后爸邢，視圖才能正常使用。
? ? NOFORCE：如果基表不存在拿愧，無(wú)法創(chuàng)建視圖杠河，該項(xiàng)是默認(rèn)選項(xiàng)。
WITH CHECK OPTION： 指出在視圖上所進(jìn)行的修改都要符合select_statement 所指定的限制條件.
? ? WITH READ ONLY：說(shuō)明視圖是只讀視圖赶掖，不能通過(guò)該視圖進(jìn)行增刪改操作「忻停現(xiàn)實(shí)開(kāi)發(fā)中，基本上不通過(guò)視圖對(duì)表中的數(shù)據(jù)進(jìn)行增刪改操作奢赂。
刪除視圖
drop view 視圖名字;
注意:
在視圖定義時(shí)沒(méi)有with read only的前提下,如果視圖包含下面內(nèi)容
a.分組函數(shù)
b.group by
c.表達(dá)式
d.rownum偽列
則不可以通過(guò)【視圖刪除】表中內(nèi)容陪白。
【插入數(shù)據(jù)】時(shí)，除了要滿(mǎn)足以上要求膳灶，還要滿(mǎn)足【沒(méi)有被視圖包含的列】可以為null值咱士。
如果建立視圖時(shí)包含with check option，則還需滿(mǎn)足指定約束條件轧钓。
【視圖主要用來(lái)查詢(xún)序厉，盡量不要DML操作】。
6.實(shí)際案例
1).準(zhǔn)備測(cè)試表:
create table t_user(
id number constraint user_id_pk primary key,
name varchar2(100),
salary number
);
//drop table t_user;
2).插入測(cè)試數(shù)據(jù):
insert into t_user(id,name,salary)
select id,last_name,salary
from s_emp;
3).創(chuàng)建視圖
create or replace view v_test
as
select *
from t_user
where id > 10;
4).查看視圖內(nèi)容
select *
from v_test;
具體測(cè)試：
通過(guò)*簡(jiǎn)單視圖* 對(duì)原來(lái)的表進(jìn)行數(shù)據(jù)的刪除/更新/插入
delete from v_test where id=16;
update v_test set name = 'zhangsan' where id = 20;
insert into v_test(id,name,salary) values(28,'tom1',3000);
以上操作都可以成功毕箍。
7.視圖創(chuàng)建額外關(guān)鍵字
在創(chuàng)建視圖時(shí)弛房，在Sql語(yǔ)句的最后跟上指定關(guān)鍵字，可以添加額外的約束而柑。
1).with read only語(yǔ)句
特點(diǎn):【只能】通過(guò)視圖進(jìn)行【查詢(xún)】數(shù)據(jù),不能修改
例如:
create or replace view v_test1
as
select *
from t_user
where id > 10
with read only;
這個(gè)視圖v_test將來(lái)只能查詢(xún),不能進(jìn)行修改
2).with check option語(yǔ)句
特點(diǎn):【通過(guò)視圖修改的信息】文捶，必須可以【通過(guò)這個(gè)視圖能夠顯示】出來(lái),否則就操作失敗
準(zhǔn)備測(cè)試用的表及其數(shù)據(jù)
drop table t_user;
create table t_user(
id number constraint user_id_pk primary key,
name varchar2(100),
salary number
);
insert into t_user values(1,'tom',1000);
insert into t_user values(2,'tom2',2000);
創(chuàng)建視圖:
create or replace view v_test
as
select id,name,salary
from t_user
where id>20
with check option;
查詢(xún)視圖中的數(shù)據(jù)
select * from v_test;
插入數(shù)據(jù)會(huì)報(bào)錯(cuò)，因?yàn)檫@個(gè)操作通過(guò)視圖顯示不出來(lái)
insert into v_test values(3,'tom3',3000);
更新也會(huì)失敗 因?yàn)檫@個(gè)操作通過(guò)視圖顯示不出來(lái)
update v_test
set name='lily'
where id=1;
更新成功 因?yàn)檫@個(gè)操作通過(guò)視圖可以顯示出來(lái)
update v_test
set name='lily'
where id=2;
8.復(fù)雜視圖
例如:
create or replace view v_test5
as
select id,avg(salary) avgs
from t_user
group by id;
復(fù)雜視圖盡量用來(lái)查詢(xún),不要DML媒咳，因?yàn)椴僮鞅容^復(fù)雜粹排。
****************
alter table 表名
add 列名 數(shù)據(jù)類(lèi)型 約束;
add constraint 約束名(列名);
drop column 列名;
drop constraint 約束名;
modify 列名 數(shù)據(jù)類(lèi)型 constraint 約束名 約束；
disable constraint 約束名 [cascade];
enable constraint 約束名涩澡；
truncate table 表名;
rename 表名 to 新表名顽耳；
序列
sequence
create sequence 序列名
increment by n
start with n
maxvalue n|nomaxvalue
minvalue n|nominvalue
cycle|nocycle
cache|nocache;
序列名.nextval? 序列名.currval;
生成主鍵值。
視圖
一張或多張表[原始數(shù)據(jù)|加工數(shù)據(jù)]的映射
create view 視圖名
as
select語(yǔ)句妙同；
簡(jiǎn)單視圖?
一個(gè)表射富，沒(méi)有g(shù)roup by，沒(méi)有函數(shù)
增刪改查 (符合基本約束)
操作視圖 == 操作原表
復(fù)雜視圖
多個(gè)表渐溶，函數(shù)辉浦，group by
增刪改查(操作的是外表)
******************
第十五章: 索引(index)
1. 索引的概念
a. 類(lèi)似書(shū)的目錄結(jié)構(gòu)
b. Oracle 的"索引"是建立在表上的【可選對(duì)象】，能提高SQL查詢(xún)語(yǔ)句的速度
c. 索引直接指向包含所查詢(xún)值的行的位置茎辐，減少磁盤(pán)I/O
d. 【索引和表是相互獨(dú)立】的物理結(jié)構(gòu),刪除或創(chuàng)建都不影響表本身。
e. Oracle 【自動(dòng)使用并維護(hù)索引】，插入拖陆、刪除弛槐、更新表后，自動(dòng)更新索引
2. 索引的創(chuàng)建
索引的創(chuàng)建分為兩種情況依啰，自動(dòng)創(chuàng)建和用戶(hù)創(chuàng)建乎串。
1).自動(dòng)創(chuàng)建
當(dāng)在表中指定了primary Key或者unique約束時(shí)會(huì)【自動(dòng)創(chuàng)建唯一值索引】。
2).用戶(hù)創(chuàng)建和刪除
用戶(hù)可以創(chuàng)建非唯一值索引以【提高在訪(fǎng)問(wèn)數(shù)據(jù)時(shí)的效率】速警。
創(chuàng)建索引：
create index 索引名
on 表名(列名);
例如：
create index emp_index
on s_emp(last_name);
刪除索引:
drop index 索引名;
例如:
drop index emp_index;
創(chuàng)建成功后可以通過(guò)【數(shù)據(jù)字典】查看：
select index_name from user_indexes;
3.給某列創(chuàng)建索引的原則（了解）
索引的最大優(yōu)勢(shì)是 提高效率叹誉。
a.列經(jīng)常作為where子句的限定條件或者作為連接條件
b.列包含的數(shù)據(jù)量很大，并且很多非空的值闷旧。
c.兩個(gè)或者更多列頻繁的組合在一起作為where的限定條件或者連接條件
d.列總是作為搜索條件
e.索引查出的數(shù)據(jù)量占總數(shù)據(jù)量的2%~4%
f.索引不是越多越好长豁，不是索引越多越能加速查找。
g.要建立索引的表不經(jīng)常進(jìn)行修改操作
注意:
1.在表中的某一個(gè)合適的列加入上了索引,那么也只有在【數(shù)據(jù)量很大】的時(shí)候,【才能體現(xiàn)】出這個(gè)查詢(xún)的【效率】.
2.【索引一旦建立成功】,那么之后這個(gè)索引就由【系統(tǒng)來(lái)管理】,我們自己是控制不了的.
4.索引的種類(lèi)(了解)
Single column 單行索引
Concatenated? 多行索引
Unique ? 唯一索引
NonUnique? ? 非唯一索引
5.索引結(jié)構(gòu)分類(lèi)(了解)
B-tree(默認(rèn)是這種結(jié)構(gòu))
適合大量的增忙灼、刪匠襟、改（OLTP）；
不能用包含OR操作符的查詢(xún)该园；
適合高基數(shù)的列（唯一值多）
典型的樹(shù)狀結(jié)構(gòu)酸舍；
位圖
做UPDATE代價(jià)非常高；
非常適合OR操作符的查詢(xún)里初；
反轉(zhuǎn)關(guān)鍵字
基于函數(shù)
第十六章: 用戶(hù)權(quán)限控制
1.創(chuàng)建用戶(hù)(管理員權(quán)限)
具體語(yǔ)法:
create user 用戶(hù)名 identified by 密碼
例如
create user zhangsan identified by zhangsan;
2.刪除用戶(hù)
drop user zhangsan cascade;
3.賦予權(quán)限
格式:
grant 具體權(quán)限s to 用戶(hù);
例如:把建表 建序列 建視圖的權(quán)限賦給zhangsan
grant create table,create sequence,create view
to zhangsan;
也可以把connect角色和resource角色賦給zhangsan
角色是一組權(quán)限的集合
grant connect,resource to zhangsan;
注意: 只是登陸oracle數(shù)據(jù)庫(kù)的話(huà) 需要的權(quán)限是【create session】登錄權(quán)限
4.修改密碼
(password zhangsan也可以修改密碼)
alter user 用戶(hù)名 identified by 密碼;
例如:
alter user zhangsan identified by zhangsan123;
5.賦予某一個(gè)用戶(hù)某種對(duì)象操作的權(quán)限
grant 操作 on 對(duì)象 to 用戶(hù)名;
注意啃勉，給指定用戶(hù)賦予操作某種對(duì)象的權(quán)限，對(duì)象可以是表双妨，也可以是視圖淮阐，還可以是索引等等。
具體測(cè)試:
步驟1：使用jd1713用戶(hù)登錄斥难，然后給zhangsan用戶(hù)授予查詢(xún)權(quán)限
grant select
on t_user
to zhangsan;
步驟2：使用zhangsan用戶(hù)登錄枝嘶，然后測(cè)試select語(yǔ)句是否可以使用
select *
from t_user;
注意，這樣寫(xiě)會(huì)出錯(cuò)哑诊，系統(tǒng)會(huì)在當(dāng)前用戶(hù)中找t_user這個(gè)對(duì)象群扶。
所以必須改成 jd1713.t_user;
select *
from jd1713.t_user;
也可以查看表結(jié)構(gòu):
desc jd1713.t_user;
6.回收權(quán)限
revoke operator on object from user;
例如:
revoke select
on t_user
from zhangsan;
7.同義詞synonym(別名)
作用:可以隱藏表原來(lái)的名字。
思考一下dual镀裤，任何用戶(hù)都可以使用這個(gè)對(duì)象竞阐，但是并沒(méi)有正常使用? 【用戶(hù)名.dual】，為什么暑劝？ 就是因?yàn)閐ual是一個(gè)同義詞骆莹。
分為:私有同義詞 公共同義詞
8.私有同義詞
【注意：同義詞 和 原對(duì)象 是同一個(gè)東西】。
創(chuàng)建私有同義詞格式：
create synonym 同義詞
for 對(duì)象名;
1).給表t_user創(chuàng)建一個(gè)私有同義詞
create synonym my_test
for t_user;
創(chuàng)建成功后担猛，一個(gè)表有倆名字幕垦。
2).給用戶(hù)zhangsan授權(quán)可以查詢(xún)my_test
grant select
on my_test
to zhangsan;
3).收回用戶(hù)zhangsan查詢(xún)my_test的權(quán)限
revoke select
on my_test
from zhangsan;
//利用數(shù)據(jù)字典查看同義詞synonyms
//用戶(hù)創(chuàng)建的同義詞有哪些
select synonym_name
from user_synonyms;
//用戶(hù)有權(quán)利查詢(xún)的同義詞有哪些
select synonym_name
from all_synonyms;
//用戶(hù)有權(quán)利查詢(xún)的同義詞有哪些是以字母D開(kāi)頭的
//注意:表中的數(shù)據(jù)都是大寫(xiě)存在
select synonym_name
from all_synonyms
where synonym_name like 'D%';
結(jié)果可以看到我們常用的dual
9.刪除同義詞synonym
格式:
drop [public] synonym 同義詞;
刪除私有同義詞
drop synonym my_test;
刪除公共同義詞
drop public synonym my_test;
10.公共的同義詞
dual就是【公共的同義詞,所有用戶(hù)都可以使用】丢氢。
注意，普通用戶(hù)沒(méi)有權(quán)限創(chuàng)建 public synonym公共同義詞先改，
所以我們需要用dba的身份登錄到數(shù)據(jù)庫(kù)中去創(chuàng)建,sqlplus "/as sysdba",或者使用系統(tǒng)管理員system用戶(hù)登錄創(chuàng)建疚察。
1).創(chuàng)建同義詞:
create public synonym psyn
for jd1713.t_user;
jd1713.t_user表示的是jd1713用戶(hù)下面的t_user對(duì)象。
2).將查詢(xún)這個(gè)同義詞的權(quán)限賦予所有人
grant select on psyn to public;
3).然后使用其他用戶(hù)登錄仇奶，就可以通過(guò)這個(gè)公共的同義詞來(lái)查詢(xún)jd1713用戶(hù)下面的t_user對(duì)象了貌嫡。
【dual就是一個(gè)公共的同義詞】
10,數(shù)據(jù)庫(kù)的導(dǎo)入導(dǎo)出
應(yīng)用場(chǎng)景:
你現(xiàn)在項(xiàng)目里面使用數(shù)據(jù)庫(kù)，保存了一定量的數(shù)據(jù)该溯；現(xiàn)在重裝系統(tǒng)岛抄，重裝數(shù)據(jù)庫(kù)軟件(數(shù)據(jù)丟失)，重裝之前狈茉，就可以使用導(dǎo)出exp命令 數(shù)據(jù)庫(kù)進(jìn)行備份夫椭；
重裝完成以后，使用imp導(dǎo)入 命令 將數(shù)據(jù)庫(kù)數(shù)據(jù)重新導(dǎo)入 數(shù)據(jù)庫(kù)里面论皆。
系統(tǒng)終端執(zhí)行
? 導(dǎo)出：exp? 根據(jù)提示按回車(chē)下一步即可
? 導(dǎo)入：imp ? 根據(jù)提示按回車(chē)下一步即可
1).具體導(dǎo)出過(guò)程
先exit退出登錄益楼，調(diào)整當(dāng)前工作路徑到合適位置(cd 路徑名)
命令行輸入exp
輸入用戶(hù)名 密碼? 【輸入jd1713 jd1713，導(dǎo)出jd1713用戶(hù)信息】
按照提示点晴，全部默認(rèn)往下操作
操作完成后感凤，當(dāng)前工作目錄下多出一個(gè)目錄。
導(dǎo)入過(guò)程：
退出登錄
切換到正確的工作目錄
imp 輸入對(duì)應(yīng)用戶(hù)名和密碼 【zhangsan zhangsan】
按照提示粒督，全部默認(rèn)往下操作 【中間有個(gè)驗(yàn)證陪竿，輸入jd1713這個(gè)用戶(hù)即可】
導(dǎo)入成功后，sqlplus登錄進(jìn)去屠橄，查詢(xún)有沒(méi)有多出來(lái)的表族跛。
