1、第 五 天 表的管理、約束、索引、試圖、序列 表名和列名的命名：必須以字母開頭必須在1-30個字符之間必須只能包含A-Z，a-z,0-9,_,$和#不能和用戶定義的其它對象重名不能是Oracle的保留字 創(chuàng)建表SQL> create table employee(empid number,2 ename varchar2(20,3 hiredate date default sysdate,4 salary number(7,2;創(chuàng)建表的時候拷貝其它表的數(shù)據(jù)create table emp1 as(select empno,ename,sal,job,deptno from emp wh

2、ere deptno=20;創(chuàng)建表的時候拷貝其它表的數(shù)據(jù),并指定字段名SQL> create table emp2(name,salary,job 2 as (select ename,sal,job from emp where deptno=20;顯示列名和不顯示列名set heading off/on 數(shù)據(jù)字典表表分為：用戶表數(shù)據(jù)字典表(系統(tǒng)表顯示數(shù)據(jù)對象的姓名和對象的類型select object_name,object_type from user_objectswhere object_type='TABLE'顯示數(shù)據(jù)對象表的結(jié)構(gòu)desc user_objec

3、ts;查看用戶定義的各種數(shù)據(jù)對象select distinct object_type from user_objects;查看用戶定義的表，視圖，同義詞和序列select * from user_catalog;查看用戶定義的表.select table_name from user_tables; 數(shù)據(jù)類型char 定長 最大2000字符 查詢速度快varchar2 變長 最大4000字符 查詢速度慢number(p,s 可變長數(shù)值數(shù)據(jù)，可整數(shù)和小數(shù)number 范圍：-10的38此方到10的38次放date 日期 年 月 日 時 分 秒long 課變長字符數(shù)據(jù)，最大字節(jié)數(shù)可達(dá)到2Gclo

4、b 字符答對象，最大字節(jié)數(shù)課達(dá)到4G 對表的操作修改列alter table emp1 modify(job varchar2(20 default 'check'增加列alter table emp1 add(comm number(7,2; 刪除列alter table emp1 drop column comm;重命名列名alter table emp1 rename column sal to salary;重命名表名rename emp1 to pp;set unused 標(biāo)記為不可用alter table emp1 set unused column comm;備注

5、：設(shè)置為不可用，看不到該列，但是并沒有被刪掉刪除所有不可用的列alter table emp1 drop unused column; 刪除表Delete 刪除了表的數(shù)據(jù)，并不刪除結(jié)構(gòu)，可以用rollback回滾Drop table語句不能被回滾，刪除了表的結(jié)構(gòu)和數(shù)據(jù)Truncate 刪除表的內(nèi)容，不刪除表結(jié)構(gòu)，不能rollback回滾數(shù)據(jù)truncate table emp2; 表的注釋 可以通過下列數(shù)據(jù)字典視圖查看所添加的注釋:ALL_COL_COMMENTSUSER_COL_COMMENTSALL_TAB_COMMENTSUSER_TAB_COMMENTS添加表的注釋comment on

6、 table emp1 is 'my is info'查看表的注釋select * from user_tab_comments where table_name='EMP1'列的注釋comment on column emp1.job is 'wo de gong zi'開啟/關(guān)閉語句運行時間set timing on/off;開啟/關(guān)閉系統(tǒng)時間set time on/off; 約束定義：約束是表的強制規(guī)定注意：只能添加和刪除約束，不能修改約束，可以有效和無效化約束添加nut null約束要使用modify語句分類5種：notnull uniq

7、ue primary key foreign key check 注意：約束可以創(chuàng)建表的時候創(chuàng)建如果不指定約束名稱，Oracle可以自動指定約束名稱 not null(非空如果在列上定義了not null,那么當(dāng)插入數(shù)據(jù)時，必須為列提供數(shù)據(jù)。 unique(唯一當(dāng)定義了唯一約束后，該列值是不能重復(fù)的.但是可以為null。 primary key(主鍵用于唯一的標(biāo)示表行的數(shù)據(jù),當(dāng)定義主鍵約束后，該列不但不能重復(fù)而且不能為null。需要說明的是:一張表最多只能有一個主鍵,但是可以有多個unqiue約束。 foreign key(外鍵用于定義主表和從表之間的關(guān)系.外鍵約束要定義在從表上，主表則必須具

8、有主鍵約束或是unique約束.,當(dāng)定義外鍵約束后，要求外鍵列數(shù)據(jù)必須在主表的主鍵列存在或是為null (學(xué)生/班級 圖示 check用于強制行數(shù)據(jù)必須滿足的條件,假定在sal列上定義了check約束,并要求sal列值在10002000之間如果不再10002000之間就會提示出錯。列級定義：列級定義是在定義列的同時定義約束。表級定義：表級定義是指在定義了所有列后，再定義約束.這里需要注意: not null約束只能在列級上定義。Not null創(chuàng)建表的約束SQL> create table emp4(id number,2 name varchar2(20 not null,-列級約束3

9、 sal number(7,2,4 hiredate date constraint emp4_hiredate_nn not null;-表級約束查看約束結(jié)構(gòu)desc user_constraints;查看約束SQL> select constraint_name from user_constraints2 where table_name='EMP4'注意：表名必須大寫Unique保證字段或者字段組合不允許重復(fù)，但允許null創(chuàng)建表級的約束SQL> create table emp5(id number unique,2 name varchar2(20 no

10、t null,3 hiredate date default sysdate,4 email varchar2(20,5 constraint emp5_email_uq unique(email;-創(chuàng)建表級的約束Primary key非空并且唯一列級定義SQL> create table emp6(id number primary key,-列級定義2 name varchar2(20;表級定義SQL> create table emp7(id number,2 name varchar2(20,3 constraint emp7_id_ik primary key(id;-表

11、級定義Foreign key子表外鍵列的值必須在主表參照列值的范圍內(nèi)，或者為空主表參照列被子表參照時，主表記錄不允許被刪除SQL> create table emp8(id number primary key,2 name varchar2(20 not null,3 email varchar2(30,4 hiredate date,5 deptno number,6 constraint emp8_deptno_fk foreign key(deptno references dept(deptno,7 constraint emp8_email_uq unique(email;F

12、OREIGN KEY 約束的關(guān)鍵字FOREIGN KEY: 在表級指定子表中的列(列值必須被父表包含REFERENCES: 標(biāo)示在父表中的列ON DELETE CASCADE: 當(dāng)父表中的列被刪除時，子表中相對應(yīng)的列也被刪除(刪除引用點,如果未設(shè)置該約束,被子表引用的值將不能從父表中刪除ON DELETE SET NULL: 子表中相應(yīng)的列置空Check Check強制固定上線和下線SQL> create table emp9(id number primary key,2 sal number(7,2,3 constraint emp9_sal_ch check(sal>1000

13、;修改約束Alter table emp9 modify (name varchar2(20 not null;添加約束SQL>alter table emp9 add constraint emp9_deptno_fk2 foreign key(deptno references dept(deptno;刪除約束alter table emp9 drop constraint emp9_deptno_fk;使約束無效化(可以看到，但是不可用alter table emp9 disable constraint emp9_id_pk cascade;激活約束alter table emp

14、9 enable constraint emp9_id_pk; 常見的數(shù)據(jù)庫對象表 基本數(shù)據(jù)存儲集合，由行和列組成視圖 虛表，從表中抽出的邏輯上相關(guān)的數(shù)據(jù)集合。序列 提供有規(guī)律的數(shù)值, 通過序列做自增長索引 提高查詢的效率同義詞 給對象起別名 視圖為什么使用視圖（優(yōu)點）：簡化查詢具有數(shù)據(jù)的獨立性控制數(shù)據(jù)的訪問，不能訪問基表避免重復(fù)訪問相同的數(shù)據(jù)語法Create or replace view 視圖名As 子查詢創(chuàng)建視圖SQL> create view V_emp92 as select id,name,deptno from emp9;使用or replace修改視圖 和添加別名（在子查

15、詢中使用）SQL> create or replace view V_emp92 as select id ,name as my_name,deptno from emp9;查詢和表的查詢一樣,名字大小寫不敏感select * from V_EMP9;查看結(jié)構(gòu)Desc V_emp9;插入SQL> insert into v_emp9 values(2,'rr',30;查看視圖desc user_views;查看所有視圖select view_name from user_views;創(chuàng)建復(fù)雜視圖SQL> create or replace view v_em

16、p_sal2 (deptno,maxsal,minsal,avgsal,countnum3 as select deptno,max(sal,min(sal,avg(sal,count(*4 from emp group by deptno order by deptno; 可以在簡單視圖中執(zhí)行 DML 操作當(dāng)視圖定義中包含以下元素之一時不能使用delete: 組函數(shù) GROUP BY 子句 DISTINCT 關(guān)鍵字 ROWNUM 偽列當(dāng)視圖定義中包含以下元素之一時不能使用update : 組函數(shù) GROUP BY子句 DISTINCT 關(guān)鍵字 ROWNUM 偽列 列的定義為表達(dá)式當(dāng)視圖定義中

17、包含以下元素之一時不能使用insert : 組函數(shù) GROUP BY 子句 DISTINCT 關(guān)鍵字 ROWNUM 偽列 列的定義為表達(dá)式 表中非空的列在視圖定義中未包括創(chuàng)建一個只讀視圖with read onlySQL> create or replace view v_emp92 as select id,name,deptno from emp93 with read only; Top-N 分析Top n 分析查詢一個列中最大或最小的n個值語法：Select rownum，字段From (select 字段 from table order by top字段Where rownu

18、m<=n;求薪水最低的5個人的記錄SQL> select rownum,empno,ename,sal,job from2 (select * from emp order by sal3 where rownum<=5; 序列什么是序列: 自動提供唯一的數(shù)值 共享對象 主要用于提供主鍵值 代替應(yīng)用代碼 將序列值裝入內(nèi)存可以提高訪問效率語法：CREATE SEQUENCE sequenceINCREMENT BY nSTART WITH nMAXVALUE n | NOMAXVALUEMINVALUE n | NOMINVALUECYCLE | NOCYCLECACHE n

19、| NOCACHE;Increment by n用于定義序列的步長，默認(rèn)為1start with n定義序列的初始值，默認(rèn)為1maxvalue n|nomaxvalue定義序列的最大值，對于遞增10的27次方，對于遞減-1minue n|nominvalue定義最小值cycle|nocycle 定義是否循環(huán)cache n|nocache 緩存，默認(rèn)為20 Nextval 返回序列中下一個有效的值，任何用戶都可以引用 Currval 中存放序列的當(dāng)前值 創(chuàng)建一個默認(rèn)的序列create sequence myseq;創(chuàng)建一個以100開始，步長為10的序列create sequence seq st

20、art with 100 increment by 10;創(chuàng)建一個以10開始，步長為10，最大值為999999，不循環(huán)，沒有緩存SQL> create sequence seq1 start with 10 increment by 102 maxvalue 999999 nocycle nocache;查看創(chuàng)建的序列SQL> select sequence_name from user_sequences;得到序列的下一個值select seq.nextval from dual;獲得序列的當(dāng)前值select seq.currval from dual;用序列插入值insert

21、into dept values(seq1.nextval,'support','Tian Jin'查看用戶所建的序列select object_name from user_objects where object_type='SEQUENCE'修改序列SQL> alter sequence seq increment by 2 maxvalue 999999;刪除序列drop sequence myseq;查詢數(shù)據(jù)字典視圖 USER_SEQUENCES獲取序列定義信息SQL> SELECT sequence_name, min_v

22、alue, max_value,2 increment_by, last_number3 FROM user_sequences; 索引索引: 一種數(shù)據(jù)庫對象 通過指針加速 Oracle 服務(wù)器的查詢速度 通過快速定位數(shù)據(jù)的方法，減少磁盤 I/O 索引與表相互獨立 Oracle 服務(wù)器自動使用和維護(hù)索引以下情況可以創(chuàng)建索引: 列中數(shù)據(jù)值分布范圍很廣 列中包含大量空值 列經(jīng)常在 WHERE 子句或連接條件中出現(xiàn) 表經(jīng)常被訪問而且數(shù)據(jù)量很大 ，訪問的數(shù)據(jù)大概占數(shù)據(jù)總量的2%到4%下列情況不要創(chuàng)建索引: 表很小 列不經(jīng)常作為連接條件或出現(xiàn)在WHERE子句中 查詢的數(shù)據(jù)大于2%到4% 表經(jīng)常更新 索引

23、的分類按照數(shù)據(jù)存儲方式，可以分為B*樹、反向索引、位圖索引;按照索引列的個數(shù)分類，可以分為單列索引、復(fù)合索引；按照索引列值的唯一性，可以分為唯一索引和非唯一索引.此外還有函數(shù)索引，全局索引，分區(qū)索引語法：Create index 索引名 on table(列名創(chuàng)建索引 自動創(chuàng)建: 在定義 PRIMARY KEY 或 UNIQUE 約束后系統(tǒng)自動在相應(yīng)的列上創(chuàng)建唯一性索引 手動創(chuàng)建: 用戶可以在其它列上創(chuàng)建非唯一的索引，以加速查詢創(chuàng)建索引create index emp2_name_idx on emp2(name;查看索引desc user_indexes;查看所建的索引select inde

24、x_name from user_indexes;或select object_name from user_objects where object_type='INDEX'或SQL> select index_name,table_name,column_name2 from user_ind_columns;drop index 索引名只有索引的持有者或擁有drop any index權(quán)限的用戶 同義詞使用同義詞訪問相同的對象：方便訪問其它用戶的對象縮短對象名字的長度創(chuàng)建public的同義詞SQL> conn sys/tiger as sysdba;SQL&g

25、t; create public synonym ve for scott.emp;查詢select * from ve;刪除SQL> conn sys/tiger as sysdba;SQL> drop public synonym ve; 集合UNION：去除完全重復(fù)的記錄。UNION ALL ：不去除重復(fù)的記錄。注:使用UNION做聯(lián)合查詢時，必須保證上下兩個查詢產(chǎn)生的結(jié)果集字段數(shù)量必須一致，并且字段的類型要一一對應(yīng)。INTERSECT 操作符返回兩個結(jié)果集的交集使用集合操作符注意事項 在SELECT 列表中的列名和表達(dá)式在數(shù)量和數(shù)據(jù)類型上要相對應(yīng) 括號可以改變執(zhí)行的順序 O

26、RDER BY 子句:只能在語句的最后出現(xiàn)，可以使用第一個查詢中的列名, 別名或相 對位置Select * from test1Union allSelect * from test2; EXISTS 操作符 EXISTS 操作符測試子查詢的結(jié)果是否存在；返回 TRUE 或 FALSE 如果一個子查詢找到了結(jié)果：在內(nèi)部子查詢中不再繼續(xù)執(zhí)行,條件被設(shè)為TRUE 如果一個子查詢沒有找到結(jié)果：條件被設(shè)為FALSE 查詢條件為where前面的表的字段約束 NOT EXISTS 操作符 和NOT IN 相對應(yīng)，速度要快，性能好。 查詢條件為where前面的表的字段約束1、not exists(not innot exists:/這條語句返回select * from emp e,dept d where e.deptno=d.deptno and d.deptno=10/條件滿足的結(jié)果集.也就是說,返回的結(jié)果集中不存在d.deptno=10結(jié)果集的記錄,即emp表中沒有d.deptno=10的記錄.select empno,ename,deptno from emp e not