oracle學習筆記(三)

一、建立和管理表
1、建立表文法
create table 表名(column datatype [default expr][,column ...])
default 約束 說明略

2、利用子查詢建立表
create table 表名
as
select * from u表

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

2、修改現有的列(修改類型，大小，預設值)
oracle:alter table table_name
modify(column datatype [defaullt expr][,column datatype]...)

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

三、刪除表
drop table 表名;刪除表後自動認可，不能rollback回來。
重名表:rename 表名 to 新表名
高效清空一張表，用truncate 刪除時不做日誌記錄也就是不能用rollback復原回來。
truncate table 表名

用delete刪除後，可以後悔，復原回來。

給一張表做注釋
comment on table tb_u_1 is '使用者表用來存放使用者資訊....'

四，添加約束
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%');
在添加資料時如果column1的資料中沒有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=''

五、建立視圖Create View

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

建立一個視圖或重定義一個視圖
create or replace view view_name as subquery

如果表table_1不存在，則使用force 建立視圖會成功，否則會報表不存在錯誤。
create force view v_name as select * from table_1

使用視圖修改資料
create view view_001
as
select * from table_001 where id<10
修改不在視圖範圍內的資料同樣會成功。
update view_001 set column1='123' where id=10;
修改不在視圖範圍內的資料則會失敗（只能修改id<10），報：“視圖 with check option 違反 where 字句”
create view view_001
as
select * from table_001 where id<10
with check option;
update view_001 set column1='123' where id=10;

建立唯讀視圖 Create a read only view
create view view_001
as
select * from table_001 where id<10
with read only;

刪除視圖 Drop View
drop view view_Name

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

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

建立序列(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;

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

索引能提高資料檢索的速度，但是降低了UPDATE,DELETE,INSERT資料操作的效能！！！

索引建立原則（摘自csdn）

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

建立同義字Create a Synongms
create [public] synonym sy_name for object
建立一個別名為table_001
create synonym tb1 for table_001
刪除同義字Drop a synonym
Drop synonym tb1
Oracle同義字建立及其作用（摘自http://www.2cto.com/database/201109/106257.html）

Oracle的同義字（synonyms）從字面上理解就是別名的意思，和試圖的功能類似，就是一種映射關係。本文介紹如何建立同義字語句，刪除同義字以及查看同義字語句。
　　oracle的同義字總結：
　　從字面上理解就是別名的意思，和試圖的功能類似。就是一種映射關係。
　　1.建立同義字語句：
　　create public synonym table_name for user.table_name;
　　其中第一個user_table和第二個user_table可以不一樣。
　　此外如果要建立一個遠端資料庫上的某張表的同義字，需要先建立一個Database Link(資料庫連接)來擴充訪問，然後在使用如下語句建立資料庫同義字：create synonym table_name for table_name@DB_Link;
　　當然，你可能需要在user使用者中給目前使用者(user2)授權： grant select/delete/update on user2
　　2.刪除同義字：
　　drop public synonym table_name;
　　3.查看所有同義字：
　　select * from dba_synonyms
　　同義字擁有如下好處：節省大量的資料庫空間，對不同使用者的操作同一張表沒有多少差別;擴充的資料庫的使用範圍，能夠在不同的資料庫使用者之間實現無縫互動;同義字可以建立在不同一個資料庫伺服器上，通過網路實現串連。
Oracle資料庫中提供了同義字管理的功能。Oracle同義字是資料庫方案對象的一個別名，經常用於簡化對象訪問和提高對象訪問的安全性。

AD：

在Oracle中對使用者的管理是使用許可權的方式來管理的，也就是說，如果我們想使用資料庫，我們就必須得有許可權，但是如果是別人將許可權授予了我們，我們也是能對資料庫進行操作的，但是我們必須要已授權的表的名稱前鍵入該表所有者的名稱，所以這就是比較麻煩的，遇到這種情況，我們該怎麼辦呢？建立個Oracle同義字吧！這樣我們就可以直接使用同義字來使用表了。
1.同義字的概念
Oracle資料庫中提供了同義字管理的功能。同義字是資料庫方案對象的一個別名，經常用於簡化對象訪問和提高對象訪問的安全性。在使用同義字時，Oracle資料庫將它翻譯成對應方案對象的名字。與視圖類似，同義字並不佔用實際儲存空間，只有在資料字典中儲存了同義字的定義。在Oracle資料庫中的大部分資料庫物件，如表、視圖、同義字、序列、預存程序、包等等，資料庫管理員都可以根據實際情況為他們定義同義字。
2.Oracle同義字的分類
Oracle同義字有兩種類型，分別是公用Oracle同義字與私人Oracle同義字。
1)公用Oracle同義字:由一個特殊的使用者組Public所擁有。顧名思義，資料庫中所有的使用者都可以使用公用同義字。公用同義字往往用來標示一些比較普通的資料庫物件，這些對象往往大家都需要引用。
2)私人Oracle同義字:它是跟公用同義字所對應，他是由建立他的使用者所有。當然，這個同義字的建立者，可以通過授權控制其他使用者是否有權使用屬於自己的私人同義字。
3.Oracle同義字建立及刪除
建立公有Oracle同義字的文法：Create [public] synonym 同義字名稱 for [username.]objectName；
Drop [public] synonym 同義字名稱
4.Oracle同義字的作用
1) 多使用者協同開發中，可以屏蔽對象的名字及其持有人。如果沒有同義字，當操作其他使用者的表時，必須通過user名.object名的形式，採用了Oracle同義字之後就可以隱蔽掉user名，當然這裡要注意的是：public同義字只是為資料庫物件定義了一個公用的別名，其他使用者能否通過這個別名訪問這個資料庫物件，還要看是否已經為這個使用者授權。
2) 為使用者簡化sql語句。上面的一條其實就是一種簡化sql的體現，同時如果自己建的表的名字很長，可以為這個表建立一個Oracle同義字來簡化sql開發。
3)為分散式資料庫的遠程對象提供位置透明性。
5.Oracle同義字在資料庫鏈中的作用
資料庫鏈是一個命名的對象，說明一個資料庫到另一個資料庫的路徑，通過其可以實現不同資料庫之間的通訊。
Create database link 資料庫鏈名 connect to user名 identified by 口令 using ‘Oracle串連串'; 訪問對象要通過 object名@資料庫鏈名。同義字在資料庫鏈中的作用就是提供位置透明性。