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

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

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

2、修改現(xià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 表名;刪除表后自動提交，不能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%');
在添加數(shù)據(jù)時如果column1的數(shù)據(jù)中沒有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)建一個視圖或重定義一個視圖
create or replace view view_name as subquery

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

使用視圖修改數(shù)據(jù)
create view view_001
as
select * from table_001 where id<10
修改不在視圖范圍內(nèi)的數(shù)據(jù)同樣會成功。
update view_001 set column1='123' where id=10;
修改不在視圖范圍內(nèi)的數(shù)據(jù)則會失?。ㄖ荒苄薷膇d<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;

創(chuàng)建只讀視圖 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)、索引、同義詞

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

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

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

AD：

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

最新評論