主頁(yè) > 知識(shí)庫(kù) > oracle學(xué)習(xí)筆記(三)

oracle學(xué)習(xí)筆記(三)

一、創(chuàng)建和管理表
1、創(chuàng)建表語(yǔ)法
create table 表名(column datatype [default expr][,column ...])
default 約束說(shuō)明略

2、利用子查詢創(chuàng)建表
create table 表名
as
select * from u表

二、修改表
1、添加一個(gè)新列
oracle：alter table table_name
add(column datatype [default expr][,column datatype]...);

2、修改現(xiàn)有的列(修改類(lèi)型，大小，默認(rèn)值)
oracle:alter table table_name
modify(column datatype [defaullt expr][,column datatype]...)

3、刪除列
alter table table_name drop column(關(guān)鍵字) 列1
集中刪除列
alter table table_name set unused column 列1
alter table table_name drop unused columns;

三、刪除表
drop table 表名;刪除表后自動(dòng)提交，不能rollback回來(lái)。
重名表:rename 表名 to 新表名
高效清空一張表，用truncate 刪除時(shí)不做日志記錄也就是不能用rollback回滾回來(lái)。
truncate table 表名

用delete刪除后，可以后悔，回滾回來(lái)。

給一張表做注釋
comment on table tb_u_1 is '用戶表用來(lái)存放用戶信息....'

四，添加約束
create table table_name
(id int not null,
lname varchar（20），
fname varchar（20）,
constraint un_key_1 unique(lname,fname)
)

alter table 表名 add constraint ch_1 check(column1 like 'k%');
在添加數(shù)據(jù)時(shí)如果column1的數(shù)據(jù)中沒(méi)有k則添加失敗

添加主鍵約束
oracle/sql:aleter table 表名1 add constraint pk_1 primary key(column1);
添加外鍵
oracle/sql:aleter table 表名2 add constraint fk_1 foreign key(column1) references 表名1（column1）;

刪除主鍵約束
oracle/sql:alter table tb_name drop constraint p1
alter table 表名2 drop constraint fk_1 cascade;
sql:查詢主鍵是否存在p1
if exists(select * from sysobjects where name='p1')

select * from user_constraint

select constraint_name,column_name from user_cons_columns where table_name=''

五、創(chuàng)建視圖Create View

create [or replace] [force|noforce] view view_name
[]
as subquery
[with check option [constraint 11]]
[with read only [constraint 22]]

創(chuàng)建一個(gè)視圖或重定義一個(gè)視圖
create or replace view view_name as subquery

如果表table_1不存在，則使用force 創(chuàng)建視圖會(huì)成功，否則會(huì)報(bào)表不存在錯(cuò)誤。
create force view v_name as select * from table_1

使用視圖修改數(shù)據(jù)
create view view_001
as
select * from table_001 where id10
修改不在視圖范圍內(nèi)的數(shù)據(jù)同樣會(huì)成功。
update view_001 set column1='123' where id=10;
修改不在視圖范圍內(nèi)的數(shù)據(jù)則會(huì)失?。ㄖ荒苄薷膇d10），報(bào)：“視圖 with check option 違反 where 字句”
create view view_001
as
select * from table_001 where id10
with check option;
update view_001 set column1='123' where id=10;

創(chuàng)建只讀視圖 Create a read only view
create view view_001
as
select * from table_001 where id10
with read only;

刪除視圖 Drop View
drop view view_Name

臨時(shí)視圖 inline views
select * from (select * from table_Name)

六、序列(sequence)、索引、同義詞

創(chuàng)建序列(sequence)
create sequence seq_name
[increment by n]
[start with n]
[{maxvalue|nomaxvalue}]
[{minvalue|nominvalue}]
[{cycle|nocycle}]
[{cache|nocache}]
create sequence seq_test1
increment by 1
start with 1
查詢序列Select a Sequence
select seq_test1.currval from daul
select seq_test1.nextvall from daul
select * from user_sequences(用戶序列視圖)

使用序列Using a Sequence
create table tb_1(a int);
insert into tb_1(seq_test1.currval);

修改序列Modifying a Sequence
alter sequence seq_test1
increment by 20
maxvalue 999999
nocache
nocycle;

創(chuàng)建索引Creating an Index
在一列或多列上創(chuàng)建索引Create an index on one or more columns
Create index index_test1
on table(column[,column]...);

索引能提高數(shù)據(jù)檢索的速度，但是降低了UPDATE,DELETE,INSERT數(shù)據(jù)操作的性能！！！

索引創(chuàng)建原則（摘自csdn）

一.B-Tree索引:
1. 選項(xiàng)擇索引字段的原則:
l 在WHERE子句中最頻繁使用的字段
l 聯(lián)接語(yǔ)句中的聯(lián)接字段
l 選擇高選擇性的字段(如果很少的字段擁有相同值,即有很多獨(dú)特值,則選擇性很好)
l ORACLE在UNIQUE和主鍵字段上自動(dòng)建立索引
l 在選擇性很差的字段上建索引只有在這個(gè)字段的值分布非常傾斜的情況下下才有益(在這種情況下,某一,兩個(gè)字段值比其它字值少出現(xiàn)很多)
l 不要在很少獨(dú)特值的字段上建B-TREE索引,在這種情況下,你可以考慮在這些字段上建位圖索引.在聯(lián)機(jī)事務(wù)處理環(huán)境下,所由并發(fā)性非常高,索引經(jīng)常被修改,所以不應(yīng)該建位圖索引
l 不要在經(jīng)常被修改的字段上建索引.當(dāng)有UPDATE,DELETE,INSETT操作時(shí),ORACLE除了要更新表的數(shù)據(jù)外,同時(shí)也要更新索引,而且就象更新數(shù)據(jù)一樣,或產(chǎn)生還原和重做條目
l 不要在有用到函數(shù)的字段上建索引,ORACLE在這種情況,優(yōu)化器不會(huì)用到索引,除非你建立函數(shù)索引
l 可以考慮在外鍵字段上建索引,這些索引允許當(dāng)在主表上UPDATE,DELETE操作時(shí),不需要共享子表的鎖,這非常適用于在父表和子表上有很多并發(fā)的INSERT,UPDATE和DELETE操作的情況
l 當(dāng)建立索引后,請(qǐng)比較一下索引后所獲得的查詢性能的提高和UPDATE,DELETE,INSERT操作性能上的損失,比較得失后,再最后決定是否需建立這個(gè)索引
2. 選擇建立復(fù)合索引
復(fù)合索引的優(yōu)點(diǎn):
l 改善選擇性:復(fù)合索引比單個(gè)字段的索引更具選擇性
l 減少I(mǎi)/O:如果要查詢的字段剛好全部包含在復(fù)合索引的字段里,則ORACLE只須訪問(wèn)索引,無(wú)須訪問(wèn)表
什么情況下優(yōu)化器會(huì)用到復(fù)合索引呢?
(a)當(dāng)SQL語(yǔ)句的WHERE子句中有用到復(fù)合索引的領(lǐng)導(dǎo)字段時(shí),ORACLE優(yōu)化器會(huì)考慮用到復(fù)合索引來(lái)訪問(wèn).
(b)當(dāng)某幾個(gè)字段在SQL語(yǔ)句的WHERE子句中經(jīng)常通過(guò)AND操作符聯(lián)合在一些使用作為過(guò)濾謂詞,并且這幾個(gè)字段合在一起時(shí)選擇性比各自單個(gè)字段的選擇性要更好時(shí),可能考慮用這幾個(gè)字段來(lái)建立復(fù)合索引.
(c)當(dāng)有幾個(gè)查詢語(yǔ)句都是查詢同樣的幾個(gè)字段值時(shí),則可以考慮在這幾個(gè)字段上建立復(fù)合索引.
復(fù)合索引字段排序的原則:
l 確保在WHERE子句中使用到的字段是復(fù)合索引的領(lǐng)導(dǎo)字段
l 如果某個(gè)字段在WHERE子句中最頻繁使用,則在建立復(fù)合索引時(shí),考慮把這個(gè)字段排在第一位(在CREATE INDEX語(yǔ)句中)
l 如果所有的字段在WHERE子句中使用頻率相同,則將最具選擇性的字段排在最前面,將最不具選擇性的字段排在最后面
l 如果所有的字段在WHERE子句中使用頻率相同,如果數(shù)據(jù)在物理上是按某一個(gè)字段排序的,則考慮將這個(gè)字段放在復(fù)合索引的第一位
二.位圖索引
什么情況下位圖索引能夠改善查詢的性能呢?
l WHERE子句包含多個(gè)謂詞于中低基數(shù)的字段
l 單個(gè)的謂詞在這些中低基數(shù)的字段上選取大量的行
l 已經(jīng)有位圖索引創(chuàng)建于某些或全部的這些中低基數(shù)的字段上
l 被查詢的表包含很多行
l 可以在單一個(gè)表上建立多個(gè)位圖索引,因此,位圖索引能夠改善包含冗長(zhǎng)WHERE子句的復(fù)雜查詢的性能,在合計(jì)查詢和星形模型的聯(lián)接查詢語(yǔ)句中,位圖索引也可以提供比較優(yōu)良的性能
位圖索引與B-TREE索引的比較
l 位圖索引更節(jié)省存儲(chǔ)空間
l 位圖索引比較適用于數(shù)據(jù)倉(cāng)庫(kù)環(huán)境,但不適于聯(lián)機(jī)事務(wù)處理環(huán)境.在數(shù)據(jù)倉(cāng)庫(kù)環(huán)境,數(shù)據(jù)維護(hù)通常上通過(guò)批量INSERT和批量UPDATE來(lái)完成的,所以索引的維護(hù)被延遲直互DML操作結(jié)束.舉例:當(dāng)你批量插入1000行數(shù)據(jù)時(shí),這些插入的行被放置到排序緩存中(SORT BUFFER),然后批處理更新這1000個(gè)索引條目,所以,每一個(gè)位圖段在每一個(gè)DML操作中只需更新一次,即使在那個(gè)位圖段里有多行被更新
l 一個(gè)健值的壓縮位圖是由一個(gè)或多個(gè)位圖段所組成,每一個(gè)位圖段大約相當(dāng)于半個(gè)BLOCK SIZE那么大,鎖的最小粒度是一個(gè)位圖段,在聯(lián)機(jī)事務(wù)處理環(huán)境,如果多個(gè)事務(wù)執(zhí)行同時(shí)的更新(即并發(fā)的更新),使用位圖索引就會(huì)影響UPDATE,INSERT,DELETE性能了
l 一個(gè)B-TREE索引的條目只包含一個(gè)ROWID,因此,當(dāng)一個(gè)索引條目被鎖定,即一行被鎖定.但是對(duì)于位圖索引, 一個(gè)索引條目潛在地有可能包含一段ROWID(即某一個(gè)范圍內(nèi)的ROWID,有多個(gè)ROWID),當(dāng)一個(gè)位圖索引條目被鎖定時(shí),則這個(gè)條目包含的那一段ROWID都被鎖定,從而影響并發(fā)性.當(dāng)一個(gè)位圖段內(nèi)的ROWID的數(shù)量越多時(shí),并發(fā)性就越差.雖然如此,對(duì)于BULK INSERT,UPDATE和DELETE,位圖索引的性能還是比B-TREE索引要好
三,索引和NULL
NULL值在索引中是被看做一個(gè)獨(dú)特值的除非當(dāng)一個(gè)索引的兩行或多行的NON-NULL值是相等的情況下.在那種情況下,行被看做是相等的,因此,唯一索引不允許行包含空值以怕被看做是相等的.但是,當(dāng)所有的行都是空值時(shí),這個(gè)規(guī)則就不適用.ORACLE并不索引所有健值都為NULL的表的行,除非是位圖索引或當(dāng)簇鍵字段值是NULL時(shí)

創(chuàng)建同義詞Create a Synongms
create [public] synonym sy_name for object
創(chuàng)建一個(gè)別名為table_001
create synonym tb1 for table_001
刪除同義詞Drop a synonym
Drop synonym tb1
Oracle同義詞創(chuàng)建及其作用（摘自https://www.jb51.net/database/201109/106257.html）

Oracle的同義詞（synonyms）從字面上理解就是別名的意思，和試圖的功能類(lèi)似，就是一種映射關(guān)系。本文介紹如何創(chuàng)建同義詞語(yǔ)句，刪除同義詞以及查看同義詞語(yǔ)句。
　　oracle的同義詞總結(jié)：
　　從字面上理解就是別名的意思，和試圖的功能類(lèi)似。就是一種映射關(guān)系。
　　1.創(chuàng)建同義詞語(yǔ)句：
　　create public synonym table_name for user.table_name;
　　其中第一個(gè)user_table和第二個(gè)user_table可以不一樣。
　　此外如果要?jiǎng)?chuàng)建一個(gè)遠(yuǎn)程的數(shù)據(jù)庫(kù)上的某張表的同義詞，需要先創(chuàng)建一個(gè)Database Link(數(shù)據(jù)庫(kù)連接)來(lái)擴(kuò)展訪問(wèn)，然后在使用如下語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù)同義詞：create synonym table_name for table_name@DB_Link;
　　當(dāng)然，你可能需要在user用戶中給當(dāng)前用戶(user2)授權(quán)： grant select/delete/update on user2
　　2.刪除同義詞：
　　drop public synonym table_name;
　　3.查看所有同義詞：
　　select * from dba_synonyms
　　同義詞擁有如下好處：節(jié)省大量的數(shù)據(jù)庫(kù)空間，對(duì)不同用戶的操作同一張表沒(méi)有多少差別;擴(kuò)展的數(shù)據(jù)庫(kù)的使用范圍，能夠在不同的數(shù)據(jù)庫(kù)用戶之間實(shí)現(xiàn)無(wú)縫交互;同義詞可以創(chuàng)建在不同一個(gè)數(shù)據(jù)庫(kù)服務(wù)器上，通過(guò)網(wǎng)絡(luò)實(shí)現(xiàn)連接。
Oracle數(shù)據(jù)庫(kù)中提供了同義詞管理的功能。Oracle同義詞是數(shù)據(jù)庫(kù)方案對(duì)象的一個(gè)別名，經(jīng)常用于簡(jiǎn)化對(duì)象訪問(wèn)和提高對(duì)象訪問(wèn)的安全性。

AD：

在Oracle中對(duì)用戶的管理是使用權(quán)限的方式來(lái)管理的，也就是說(shuō)，如果我們想使用數(shù)據(jù)庫(kù)，我們就必須得有權(quán)限，但是如果是別人將權(quán)限授予了我們，我們也是能對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作的，但是我們必須要已授權(quán)的表的名稱前鍵入該表所有者的名稱，所以這就是比較麻煩的，遇到這種情況，我們?cè)撛趺崔k呢？創(chuàng)建個(gè)Oracle同義詞吧！這樣我們就可以直接使用同義詞來(lái)使用表了。
1.同義詞的概念
Oracle數(shù)據(jù)庫(kù)中提供了同義詞管理的功能。同義詞是數(shù)據(jù)庫(kù)方案對(duì)象的一個(gè)別名，經(jīng)常用于簡(jiǎn)化對(duì)象訪問(wèn)和提高對(duì)象訪問(wèn)的安全性。在使用同義詞時(shí)，Oracle數(shù)據(jù)庫(kù)將它翻譯成對(duì)應(yīng)方案對(duì)象的名字。與視圖類(lèi)似，同義詞并不占用實(shí)際存儲(chǔ)空間，只有在數(shù)據(jù)字典中保存了同義詞的定義。在Oracle數(shù)據(jù)庫(kù)中的大部分?jǐn)?shù)據(jù)庫(kù)對(duì)象，如表、視圖、同義詞、序列、存儲(chǔ)過(guò)程、包等等，數(shù)據(jù)庫(kù)管理員都可以根據(jù)實(shí)際情況為他們定義同義詞。
2.Oracle同義詞的分類(lèi)
Oracle同義詞有兩種類(lèi)型，分別是公用Oracle同義詞與私有Oracle同義詞。
1)公用Oracle同義詞:由一個(gè)特殊的用戶組Public所擁有。顧名思義，數(shù)據(jù)庫(kù)中所有的用戶都可以使用公用同義詞。公用同義詞往往用來(lái)標(biāo)示一些比較普通的數(shù)據(jù)庫(kù)對(duì)象，這些對(duì)象往往大家都需要引用。
2)私有Oracle同義詞:它是跟公用同義詞所對(duì)應(yīng)，他是由創(chuàng)建他的用戶所有。當(dāng)然，這個(gè)同義詞的創(chuàng)建者，可以通過(guò)授權(quán)控制其他用戶是否有權(quán)使用屬于自己的私有同義詞。
3.Oracle同義詞創(chuàng)建及刪除
創(chuàng)建公有Oracle同義詞的語(yǔ)法：Create [public] synonym 同義詞名稱 for [username.]objectName；
Drop [public] synonym 同義詞名稱
4.Oracle同義詞的作用
1) 多用戶協(xié)同開(kāi)發(fā)中，可以屏蔽對(duì)象的名字及其持有者。如果沒(méi)有同義詞，當(dāng)操作其他用戶的表時(shí)，必須通過(guò)user名.object名的形式，采用了Oracle同義詞之后就可以隱蔽掉user名，當(dāng)然這里要注意的是：public同義詞只是為數(shù)據(jù)庫(kù)對(duì)象定義了一個(gè)公共的別名，其他用戶能否通過(guò)這個(gè)別名訪問(wèn)這個(gè)數(shù)據(jù)庫(kù)對(duì)象，還要看是否已經(jīng)為這個(gè)用戶授權(quán)。
2) 為用戶簡(jiǎn)化sql語(yǔ)句。上面的一條其實(shí)就是一種簡(jiǎn)化sql的體現(xiàn)，同時(shí)如果自己建的表的名字很長(zhǎng)，可以為這個(gè)表創(chuàng)建一個(gè)Oracle同義詞來(lái)簡(jiǎn)化sql開(kāi)發(fā)。
3)為分布式數(shù)據(jù)庫(kù)的遠(yuǎn)程對(duì)象提供位置透明性。
5.Oracle同義詞在數(shù)據(jù)庫(kù)鏈中的作用
數(shù)據(jù)庫(kù)鏈?zhǔn)且粋€(gè)命名的對(duì)象，說(shuō)明一個(gè)數(shù)據(jù)庫(kù)到另一個(gè)數(shù)據(jù)庫(kù)的路徑，通過(guò)其可以實(shí)現(xiàn)不同數(shù)據(jù)庫(kù)之間的通信。
Create database link 數(shù)據(jù)庫(kù)鏈名 connect to user名 identified by 口令 using ‘Oracle連接串'; 訪問(wèn)對(duì)象要通過(guò) object名@數(shù)據(jù)庫(kù)鏈名。同義詞在數(shù)據(jù)庫(kù)鏈中的作用就是提供位置透明性。

您可能感興趣的文章:

Oracle學(xué)習(xí)筆記(六)
Oracle學(xué)習(xí)筆記(五)
Oracle學(xué)習(xí)筆記(四)
oracle學(xué)習(xí)筆記(二)
Oracle學(xué)習(xí)筆記(一)

標(biāo)簽：仙桃衡水崇左湘潭湖南蘭州黃山銅川

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《oracle學(xué)習(xí)筆記(三)》，本文關(guān)鍵詞；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。

佳木斯湛栽影视文化发展公司

oracle學(xué)習(xí)筆記(三)

QQ咨詢

電話咨詢