完美Oracle數(shù)據(jù)庫(kù)知識(shí)學(xué)習(xí)文檔總匯

1、PAGE .PAGE ：.；PAGE 38第 PAGE 38 頁(yè) 共 NUMPAGES 38 頁(yè)第 PAGE 1 頁(yè) 共 NUMPAGES 38 頁(yè)ORACLEDDL(數(shù)據(jù)定義言語(yǔ))Oracle自動(dòng)在語(yǔ)句執(zhí)行前后都加上commit, 那么DDL之前的沒(méi)提交改動(dòng)被隱式提交, 不能回滾.數(shù)據(jù)類(lèi)型合法表示符標(biāo)示符1-30個(gè)字符,以字符(可以# $ _ )開(kāi)場(chǎng),數(shù)據(jù)庫(kù)鏈接可以包含( , )兩個(gè)特殊符號(hào). 不能包含引號(hào),不能是保管字.特殊情況: 數(shù)據(jù)庫(kù)稱號(hào)最多8個(gè)字符, 數(shù)據(jù)庫(kù)鏈接最多128個(gè)字符, 把列名放入引號(hào)可以運(yùn)用保管字作為列名.ORACLE數(shù)據(jù)庫(kù)的端口默許是1521NUMBER Number(

2、precision,scale) precision: 有效位數(shù)總數(shù), 138 , 包括小數(shù)位scale: 小數(shù)位數(shù),沒(méi)有小數(shù)可以省略小數(shù)和小數(shù)點(diǎn); 省略此參數(shù)表示一個(gè)整數(shù);當(dāng) scale precision,那么會(huì)在有效位之前加0CHAR char(size) size: 12000字節(jié), 值缺乏size時(shí),會(huì)自動(dòng)在值后面補(bǔ)充空格. char(size CHAR|BYTE) 指出基于字符或字節(jié),9i以上版本 思索數(shù)據(jù)庫(kù)參數(shù) NLS_LENGTH_SEMATICS(show parameters nls_length_semantics 可以看當(dāng)前設(shè)置).NCHAR與char差別在于它規(guī)定的是

3、最大字符個(gè)數(shù).支持Unicode可變長(zhǎng)度的字符集. 最大也是2000字節(jié)容量.VarChar2與char差別僅僅是:不會(huì)自動(dòng)在值后面補(bǔ)充空格.最大4000字節(jié),默許基于字節(jié).NVarChar2存儲(chǔ)可變長(zhǎng)度且基于字符數(shù)量的數(shù)據(jù).RAW與VarChar2類(lèi)似,用于可變長(zhǎng)度的二進(jìn)制數(shù)據(jù),但最大只需2000字節(jié).LOB大對(duì)象,包括BLOB(二進(jìn)制字節(jié)) CLOB(字符) NCLOB(國(guó)家字符) BFILE(二進(jìn)制文件), 最大可到4GB的數(shù)據(jù). 支持分段訪問(wèn), 支持Oracle對(duì)象, 還可以用PL/SQL的DBMS_LOB包進(jìn)展處置.Date 準(zhǔn)確到秒的時(shí)間. 通常被誤以為只是到天. 9i之前獨(dú)一存在

4、的時(shí)間類(lèi)型.Date yyyy-mm-dd 生成一個(gè)日期, date 2006-08-15 . 這里沒(méi)有小時(shí),分,秒, 也必需yyyy-mm-dd的格式.To_date(日期值, 格式串) 生成一個(gè)日期, 格式串: yyyy mm dd 分別表示年月日.運(yùn)用DD- MON-YYYY, MON-DD-YYYY YYYY-MON-DD-YYYY表示時(shí)都應(yīng)該基于其安裝的系統(tǒng), 中文Oracle需求運(yùn)用月標(biāo)識(shí)月字段. show parameters nls_date_format(查看默許日期格式), 9i默許的格式: DD-Mon-RR8.x: DD-Mon-RR7.3及以下: DD-Mon-YY

5、假設(shè)在windows系統(tǒng)中設(shè)置了了注冊(cè)表中的NLS_LANG, 那么一切的NLS_*參數(shù)都會(huì)被忽略. 這時(shí)或者根本沒(méi)有設(shè)置nls_date_format參數(shù), 運(yùn)用不加格式串的to_date函數(shù)就會(huì)失敗. insert into Test(event,data) values(Table Made,TO_DATE(Dec-2-2004,MON-DD-YYYY) ); /Dec無(wú)效 ! 中文Oracle需求運(yùn)用月標(biāo)識(shí)月字段. insert into Test(event,data) values(Table Made,SYSDATE ) ; /SYSDATE生成系統(tǒng)日期 insert into

6、test2(event,Date) values(Table Made,TO_DATE(01-12月-04,DD-MON-YY) ) ; insert into test2(event,Date) values (Insert first value, to_date(12月-212004 , MON-DD - YYYY ); insert into test2(event,Date) values (Insert first value, to_date(2004-12月-31,YYYY-MON -DD) ); insert into test2(event,Date) values(Tab

7、le Made,TO_DATE(01-12月-04,DD-MON-YYYY); insert into test2(event,Date) values(Table Made,TO_DATE(01-12月-03)/ 日-月-年 月-日-年 年-月-日 三種順序有效 !; SQL select to_char(Date,DD-MON-YY HH12:MI:SS) Date from test2;Date31-12月-04 03:52:3931-12月-04 12:00:0021-12月-04 12:00:00TIMESTAMP 對(duì)秒提供最多9位小數(shù)精度的時(shí)間. 9i new!Sysdate 可以

8、直接產(chǎn)生一個(gè)timestamp的. Timestamp seconds_precision /精度指出秒的小數(shù)位數(shù),默以為6,可選0-9.TIMESTAMP WITH TIME ZONE Timestamp seconds_precision WITH Time Zonetimestamp 2004-12-31 15:00:000000 GMTtimestamp 2006-08-14 17:41:000000 5:00TIMESTAMP WITH LOCAL TIME ZONE插入的數(shù)據(jù)在查詢時(shí)將思索時(shí)區(qū)轉(zhuǎn)換到當(dāng)前時(shí)區(qū).alter session set time_zone=-05:00 /設(shè)

9、置時(shí)區(qū), 執(zhí)行查詢時(shí)將根據(jù)設(shè)置的時(shí)區(qū)前往相應(yīng)的結(jié)果.插入:timestamp 2004-12-31 20:00:000000 GMT假設(shè)當(dāng)?shù)貢r(shí)區(qū)為-05:00,那么查詢結(jié)果為31-Dec-04 03.00.00.000000PM改時(shí)區(qū)為GMT 再次查詢,結(jié)果為:31-Dec-04 08.00.00.000000PM- Create tablecreate table TIMECOMP( EVENT VARCHAR2(100), NORMALC DATE, TIMESTAMPC TIMESTAMP(6), TIMESTAMPZC TIMESTAMP(6) WITH TIME ZONE, TIME

10、STAMPLOCALZC TIMESTAMP(6) WITH LOCAL TIME ZONE)insert into timecomp (event,normalc,timestampc,timestampzc,timestamplocalzc) values(Test 各個(gè)日期的區(qū)別,sysdate, sysdate, timestamp 2006-08-14 18:14:15.000000 -5:00, timestamp 2006-08-14 18:14:15.000000 EST) select * from timecomp;EVENTTest 各個(gè)日期的區(qū)別NORMALC2006-

11、8-14 18:36:25TIMESTAMPC14-8月 -06 000000 下午TIMESTAMPZC14-8月 -06 000000 下午 -05:00TIMESTAMPLOCALZC15-8月 -06 000000 上午alter session set time_zone = -5:00select * from timecomp;EVENTTest 各個(gè)日期的區(qū)別NORMALC2006-8-14 18:36:25TIMESTAMPC14-8月 -06 000000 下午TIMESTAMPZC14-8月 -06

12、000000 下午 -05:00TIMESTAMPLOCALZC14-8月 -06 000000 下午alter session set time_zone = -8:00select * from timecomp;EVENTTest 各個(gè)日期的區(qū)別NORMALC2006-8-14 18:36:25TIMESTAMPC14-8月 -06 000000 下午TIMESTAMPZC14-8月 -06 000000 下午 -05:00TIMESTAMPLOCALZC14-8月 -06 000000 下午INT

13、ERVAL YEAR TO MONTHINTERVAL YEAR (YEAR_PRECISION) TO MONTH /精度默許2,可選09INTERVAL DAY TO SECOND INTERVAL DAY(DAY_PRCISION) TO SECOND (SECOND_ PRECISION)/可選0-9, 日精度默許2, 秒精度默許6 .create table INTERVAL( NAME VARCHAR2(20), EVENT VARCHAR2(100), BEGIN TIMESTAMP(6) WITH TIME ZONE, END TIMESTAMP(6) WITH TIME ZO

14、NE, BREAK INTERVAL DAY(4) TO SECOND(2) insert into interval(name,event,begin,end) values (AiSee,interval, timestamp 2006-08-14 19:12:19.000000 -5:00 , timestamp 2006-08-14 19:13:45.000000 -6:00 ) select * from intervalAiSee測(cè)試interval數(shù)據(jù)類(lèi)型14-8月 -06 000000 下午 -05:0014-8月 -06 000000 下午

15、 -06:00insert into interval select name, event,begin,end,end-begin from intervalselect * from intervalAiSee測(cè)試interval數(shù)據(jù)類(lèi)型14-8月 -06 000000 下午 -05:0014-8月 -06 000000 下午 -06:00AiSee測(cè)試interval數(shù)據(jù)類(lèi)型14-8月 -06 000000 下午 -05:0014-8月 -06 000000 下午 -06:00+0000 01:01:26.00ANS

16、I 數(shù)據(jù)類(lèi)型ORACLE支持工業(yè)規(guī)范的ANSI數(shù)據(jù)類(lèi)型, 以及IBM DB/2 和 SQL/DS數(shù)據(jù)類(lèi)型. 出于某種緣由選擇它們比較適宜:一些數(shù)據(jù)庫(kù)設(shè)計(jì)工具可以導(dǎo)出能被其他數(shù)據(jù)庫(kù)運(yùn)用的ANSI兼容的SQL. 在其他數(shù)據(jù)庫(kù)上運(yùn)用的SQL教本不需求轉(zhuǎn)換稱為ORACLE語(yǔ)法就可以在ORACLE運(yùn)用,當(dāng)運(yùn)用ANSI, IBM BD/2 , SQL/DS 數(shù)據(jù)類(lèi)型建立列的時(shí)候, 會(huì)被隱式轉(zhuǎn)換位適宜的ORACLE內(nèi)部類(lèi)型.create table ANSITYPE2( INTEGER2NUMBER INTEGER, DEC2NUMBER dec(9,2), CHARACTER2VARCHAR2 chara

17、cter varying(30), NATIONALCHAR2NCHAR National CHAR(3) SQL desc ANSITYPE ;Name Type Nullable Default Comments INTEGER2NUMBER INTEGER Y DEC2NUMBER NUMBER(9,2) Y CHARACTER2VARCHAR2 VARCHAR2(30) Y NATIONALCHAR2NCHAR NCHAR(3) Y等價(jià)于:- Create tablecreate table ANSITYPE( INTEGER2NUMBER INTEGER, DEC2NUMBER NU

18、MBER(9,2), CHARACTER2VARCHAR2 VARCHAR2(30), NATIONALCHAR2NCHAR NCHAR(3)可用SQL數(shù)據(jù)類(lèi)型Oracle等價(jià)數(shù)據(jù)類(lèi)型NUMERIC(PRECISION,SCALE)NUMBER(PRECISION,SCALE)DECIMAL(PRECISION,SCALE)NUMBER(PRECISION,SCALE)INTEGERNUMBER(38)INTNUMBER(38)SMALLINTNUMBER(38)FLOAT(b)NUMBERDOUBLEPRECISIONNUMBERREALNUMBERCHARACTER(size)CHAR(s

19、ize)CHAR(size)CHAR(size)CHARACTER VARYING(size)VARCHAR(size)CHAR VARING(size)VARCHAR(size)NATIONAL CHARACTER(size)NCHAR(size)NATIONAL CHAR(size)NCHAR(size)NCHAR(size)NCHAR(size)NATIONAL CHARACTER VARYING(size)NVARCHAR2(size)NATIONAL CHAR VARYING(size)NVARCHAR2(size)NCHAR VARYING(size)NVARCHAR2(size)

20、SELECTSelect增值效力有哪些表,試圖呢 ?select table_name from dba_tables;當(dāng)前用戶是誰(shuí) ?select user from user_tables group by user ;show user ; - The same as above paragraphs當(dāng)前用戶有哪些表 :SQL select table_name from user_tables ;選擇某一用戶的表名select table_name from dba_tables where owner = SYSTEM ;想知道有多少個(gè)用戶嗎 ?select owner from d

21、ba_tables group by owner ;想知道表EMP是誰(shuí)的嗎 ?select owner from dba_tables where table_name = EMP ;索引添加的描畫(huà)SELECT NAME ,HEIGHT,LF_BLKS,LF_ROWS,DEL_LF_ROWS, PCT_USED FROM INDEX_STATS 查詢: 表名,索引高度,葉子結(jié)點(diǎn)數(shù),行數(shù),標(biāo)志為刪除的行數(shù),索引結(jié)點(diǎn)占空間的百分比例(有自在空間時(shí)一定小于100)由于針對(duì)索引表,前期需求:SQL create index index_objname_indexTest on indexTest(ob

22、ject_name) pctfree 10;索引已創(chuàng)建。SQL analyze index index_objname_indexTest validate structure ;索引已分析SQL analyze indexTest index_objname_indexTest compute statistics for owner ;索引已分析查詢過(guò)濾where 限定語(yǔ)句select from where a=b ab ab a != b ab NULL 不等于NULL BETWEEN a AND b a LIKE b 表達(dá)式b 運(yùn)用通配符 %表示0,1,多個(gè)字符 _ 代表一個(gè)字符 a

23、NOT LIKE and / or :在where中實(shí)現(xiàn)銜接多個(gè)子句,構(gòu)成復(fù)合條件, 留意 AND 的優(yōu)先級(jí)高于 OR留意 可以用括號(hào)提高優(yōu)先級(jí)DISTINCT 不想要反復(fù)的信息 ?select distinct owner from DBA_TABLES ; / 反復(fù)的owner只顯示一條distinct 只支持一個(gè)字段的查詢，一個(gè)SQl語(yǔ)句中只能用一次留意：通配符 * 會(huì)被以為是一個(gè)字段Like / Not Likeselect * from insert_Select where word not like _ct%; _代表單字符必占用, %代表多個(gè)字符可占可不占通配符和本義字符_ 代

24、表一個(gè)恣意字符,% 代表恣意數(shù)量的恣意字符ESCAPE: 定義一個(gè)本義字符, 如 ESCAPE 定義 為本義字符, 所謂本義字符指緊跟在它后面的特殊字符( 上面的 _ % ) 表示普通的字符.select * from TMP where name not like _% ESCAPE ;查詢name第一個(gè)字母不是 _ 的一切記錄IN /NOT IN 運(yùn)算符select * from insert_Select where word in( act,active);Any /ALLselect * from insert_Select where word All (select word f

25、rom enstudy where word Like a%) ;select * from insert_Select where word All (select word from enstudy where word Like a%) and exists(select * from insert_Select in_S, enstudy ensd where in_S.word=ensd.word) ;BETWEEN運(yùn)算符select * from insert_Select where word between act and zz;小的值應(yīng)該放在前面部分.銜接運(yùn)算符: | 重新組

26、合/命名結(jié)果select WORD | | pronounce | from insert_Select;/SQL Server, MS Access 運(yùn)用 + 作銜接符order by desc排序 可接多個(gè)列或表達(dá)式+order by salary desc 按salary降序陳列前往值order by salary, id desc 首先按salary升序陳列,再按id降序陳列order by 1 ; 表示按第一列排序select * from tmp order by 1,2,3 desc 按第一第二升序,第三列降序陳列留意: 參與排序的列不一定會(huì)出如今select子句中, 也即是不一

27、定出如今結(jié)果中.NULL出如今排序的最前面.group by 和 having此時(shí)select字句中的每個(gè)列都是group by字句的列或列函數(shù).group by 把前往結(jié)果按指定列名分組, 同一組只顯示一項(xiàng)having 在運(yùn)用了group by 后成為可選, 后面跟一個(gè)條件. 與where不同, where是在數(shù)據(jù)分組前進(jìn)展挑選,having是在查詢結(jié)果中分組之后進(jìn)展再選.distinct 只支持一個(gè)字段的查詢，一個(gè)SQl語(yǔ)句中只能用一次留意：通配符 * 會(huì)被以為是一個(gè)字段group by 支持多字段的查詢，select event, avg(salary), max(salary) fro

28、m test2 where event is not NULL group by avg(salary) having event is not NULL order by max(salary);集合函數(shù)SUM 求和MAX 最大MIN 最小AVG 平均數(shù)COUNT 個(gè)數(shù)表別名不可以加as, 列別名可以加asselect cxdx.cxdx_dm,cxdx_mc,zbmc,tjzd.cxzb_dm,tjzd.tjfrom dsdm.dm_zhcx_cxdx cxdx inner join dsdm.dm_zhcx_tjzd tjzdon cxdx.cxdx_dm = tjzd.cxdx_dm

29、where cxdx.cxdx_dm between 13 and 13/select cxdx_dm “Dm,cxdx_mc AS Mc, zbmc,cxzb_dm,tjfrom dsdm.dm_zhcx_cxdx natural join dsdm.dm_zhcx_tjzd where cxdx_dm between 13 and 13; /as 關(guān)鍵字可選對(duì)于在不同表中有一樣稱號(hào)的列才會(huì)強(qiáng)迫加上表別名. 把別名用雙引號(hào)援用可以防止前往結(jié)果用完全大寫(xiě)的列名.強(qiáng)調(diào)為小寫(xiě)ANSI 聯(lián)接, 直接表關(guān)聯(lián)自然聯(lián)接 natural join: 自動(dòng)將兩個(gè)表一樣稱號(hào)的列聯(lián)接,用戶不需求命名原來(lái)聯(lián)接的列,

30、 這里不允許運(yùn)用別名.這將會(huì)強(qiáng)迫設(shè)計(jì)者將要聯(lián)接的列運(yùn)用一樣的稱號(hào),而且不能讓其他的列有一樣的稱號(hào),否那么oracle將一同聯(lián)接他們.R1(A, B)R2(A, C)R3(A, D)用sql語(yǔ)句把這三個(gè)表銜接成一個(gè)方式為(A, B, C, D)的表 SELECT R1.A AS A,R1.B AS B,R2.C AS C,R3.D AS D FROM R1 INNER JOIN R2 ON R1.A=R2.A INNER JOIN R3 ON R1.A=R3.A 內(nèi)聯(lián)接 inner joinselect form t1 inner join t2 on 用戶規(guī)定了要聯(lián)接的各個(gè)表的列. 將只前往滿

31、足條件的行,忽略條件一方為NULL的行.select * from student st inner join score sc on st.id =sc.id /內(nèi)聯(lián)接：兩個(gè)表存在主外鍵關(guān)系時(shí)常用內(nèi)聯(lián)接查詢。外聯(lián)接 select * from student st left outer join score sc on st.id=sc.id /左外聯(lián)接：結(jié)果包括左表的一切行，假設(shè)左表的行在右表中沒(méi)有匹配的行，那么為空值select * from student st right outer join score sc on st.id=sc.id /右外聯(lián)接：結(jié)果包括右表的一切行，假設(shè)右表

32、的行在左表中沒(méi)有匹配的行，那么為空值select * from student st full join score sc on st.id=sc.id /完全外聯(lián)接：前往左表和右表中的一切行l(wèi)eft outer join / right outer join/full outer join/ /前往內(nèi)聯(lián)接的結(jié)果以及存在左/右表中, 而另一個(gè)表沒(méi)有相應(yīng)行的一切行.(同內(nèi)聯(lián)接有on 條件子句.) Left時(shí),盡能夠多前往第一個(gè)表的行,right時(shí)盡能夠前往第二個(gè)表的行.完全外聯(lián)接 在where子句后面用帶括號(hào)的加號(hào)(+)表示. 無(wú)join 和 on子句.select cxdx.cxdx_dm,c

33、xdx_mc,zbmc,tjzd.cxzb_dm,tjzd.tjfrom dsdm.dm_zhcx_cxdx cxdx , dsdm.dm_zhcx_tjzd tjzdwhere cxdx.cxdx_dm = tjzd.cxdx_dm (+)and cxdx.cxdx_dm between 10 and 12一種說(shuō)法是+ 號(hào)的另一側(cè)的列可以為空(P37,Oracle9i JDBC 編程).Conn IC/IC ;select NVL(t1.id,99099) AS ID1,NVL(,I am NULL) AS name1, t1.p6s3, NVL(,I am NU

34、LL) AS name2, NVL(t2.id,99900) ID2 , t2.salary from temp t2 , tmp t1 where (+) = ;這里以為 可以為空. 但結(jié)果包含了部分為空的記錄.下面的更加直接.select NVL(t1.id,99099) AS ID1,NVL(,I am NULL) AS name1, t1.p6s3, NVL(,I am NULL) AS name2, NVL(t2.id,99900) ID2 , t2.salary from temp t2 , t

35、mp t1 where = OR is null and is NOT NULL留意 AND 的優(yōu)先級(jí)高于 OR自聯(lián)接 某些表可以擁有自援用外鍵,一個(gè)列是其所在表的主鍵的一個(gè)外鍵.這時(shí)需求運(yùn)用表別名消除模糊:select e1.last_name “Employee e2.last_name “Report Tofrom employees e1 left outer join employees e2 on e1.manager_id = e2.employee_idorder by e1.employee_id 不想用聯(lián)接 ?

36、直接查詢兩個(gè)表SQL select a.CXZB_DM,a.CXZB_MC,b.CXDX_DM from DSDM.DM_ZHCX_CXZB a, DSDM.dm_zhcx_tjzd b where CXZB_LX=2 and a.CXZB_DM = b.CXZB_DM ;/ 把where字句的=改為*= 或者=* 就表示左/右外聯(lián)接集合操作不允許運(yùn)用order, 不能在BLOB, Long 這樣的大數(shù)據(jù)對(duì)象上運(yùn)用集合操作.各個(gè)單獨(dú)的Select表的數(shù)量和數(shù)據(jù)類(lèi)型必需完全一樣.UNION取第一個(gè)查詢的一切行與第二個(gè)查詢的一切行的并集.Select employee_id, last_name

37、from employees where last_name like A% or last_name like B%UNIONSelect employee_id, last_name from employees where last_name like B% or last_name like C%將前往last_name以A , B 或C開(kāi)頭的所以employee_id , last_name行UNION ALL取兩次查詢的和,而不消除反復(fù)的行. A B B C 開(kāi)頭的,留意B兩次出現(xiàn).INTERSECT取兩次的交集. 即B開(kāi)頭MINUS取在第一次前往而第二此不前往的結(jié)果. 即 A 開(kāi)

38、頭Table(表)Table DDL，Constraint建立表create table schema. ( default , default . );約束語(yǔ)法alter table schema. add constraint 創(chuàng)建表時(shí)的語(yǔ)法:constraint 主鍵 constraint cs_name primary key(columun_name) 任一表最多一個(gè)主鍵, 可以由一個(gè)或者多個(gè)列(max=32)共同構(gòu)成一個(gè)主鍵. 表中恣意兩行的主鍵列的值不能為NULL也不能一樣.獨(dú)一性 constraint cs_name unique(columun_name)最多由32列構(gòu)成,多

39、個(gè)列即為復(fù)合獨(dú)一鍵, 獨(dú)一鍵約束允許多個(gè)NULL.獨(dú)一鍵建立后, oracle會(huì)在后臺(tái)建立一個(gè)獨(dú)一性索引來(lái)強(qiáng)迫獨(dú)一性.假設(shè)曾經(jīng)為獨(dú)一鍵建立了索引, oracle會(huì)運(yùn)用已有的索引.外 鍵 constraint cs_name foreign key(columun_name) references parent_table_name(columun_name) 被參照的父表列為獨(dú)一鍵或者主鍵,子表插入記錄時(shí)可以為空. 外鍵可以自參照. 也可組合多個(gè)列成為復(fù)合外鍵(此時(shí),復(fù)合外鍵所擁有的子表列要與父表列的數(shù)量類(lèi)型都匹配). 當(dāng)插入一行,外鍵不為空而父表中被參照的列沒(méi)有相應(yīng)的行, 那么插入失敗. 外

40、鍵本身不要求非空.alter table insert_Select add constraint fk_ insert_Select foreign key (word) references enstudy(word) ;Check約束 constraint cs_name check(sex in ( MALE, FEMALE)建立有約束的表create table significant_Others( customer_number int not null primary key, significant_other int, information NVARCHAR2(15) ,

41、 foreign key (information) references enstudy) ;/ enstudy 必需有主鍵,而且主鍵為NVARCHAR2(15).create table CONSTRAINTS2( ID1 VARCHAR2(10) not null, ID2 VARCHAR2(10) not null, NAME VARCHAR2(20), SEX VARCHAR2(1), constraint pk_constraints2 primary key(id1,id2)create table as select wherecreate table insert_Selec

42、t2 as select word, pronounce from enstudy ;查詢一個(gè)表以創(chuàng)建另一個(gè)表, 這里支持對(duì)象(如 約束/索引/觸發(fā)器) 將不會(huì)被建立.運(yùn)用where 1=0 可以只復(fù)制構(gòu)造. 刪除約束alter table enstudy drop constraint pk ;/ERROR 位于第 1 行:ORA-02273: 此獨(dú)一/主鍵已被某些外部關(guān)鍵字援用創(chuàng)建視圖create view cor_View as select word as WORD_Se, pronounce as PRO from enstudy ;由于視圖是一個(gè)指定的查詢結(jié)果集, 所以可以運(yùn)用符合

43、查詢或者嵌套,JOIN,UNION對(duì)視圖的改動(dòng)將反映到原始表 !DML (Data Manipulation language)INSERT 插入一行Insert into (,.) Values(,) ;insert into select . . from whereinsert into copy_tab Select word , pronounce from enstudy where word=able ;UPDATE 修正行Update employees Set salary = salary * 1.15Where job_id=IT_PROG ;DELETEDelete f

44、rom employees where job_id = IT_PROG ;這里不限運(yùn)用一個(gè)條件,假設(shè)不提供條件那么會(huì)刪除表的一切記錄.COMMIT一旦提交就不能回滾回到前面的形狀.ROLLBACK 回滾到最近一次commit或者rollback后的形狀.全局暫時(shí)表create global temporary table session_tabon commit preserve rowsas select *from employeescreate global temporary table teansaction_tabon commit delete rows as select *

45、 from employeeswhere 1=0修正表構(gòu)造添加一列 addalter table people add( cell_number varchar2(10) ) ;修正一列 Modify/NOT NULL 約束Alter table peopleModify ( ssn number(9) not null) ;刪除列 標(biāo)注不用的列alter table Drop cloumn alter table Drop cloumn (cloumn1,column2.)/上面語(yǔ)句將重寫(xiě)表到磁盤(pán),并移走舊的數(shù)據(jù)./下面將不重寫(xiě)也不收回空間,對(duì)于大表將節(jié)約時(shí)間.Alter table set

46、 unused columnAlter table set unused (column,column2)/下面刪除unused列alter table DROP UNUSED COLUMN/查找不用的列select * from user_unused_col_tabs ; / 前往兩列(table_name, count)重命名表alter table people rename to employee刪除表drop table cascade constraints可選參數(shù)選中后將同時(shí)刪除子表外鍵.TRUNCATE TABLE 去除數(shù)據(jù),保管表TRUNCATE TABLE SCHEMA.

47、 DROP STORAGE | REUSE STORAGE / 快速去除表中的數(shù)據(jù),保管表,且不會(huì)生成回滾數(shù)據(jù).不會(huì)激活ON DELETE 觸發(fā)器必需先禁用所以子表外鍵,必需具有DROP TABLE 特權(quán), 是一個(gè)DDL語(yǔ)句,所以不能回滾.DROP STORAGE: 默許選項(xiàng).只需最初為表分配的盤(pán)區(qū)(MINEXTENTS)會(huì)被保管給新行.REUSE STORAGE:保管表的一切盤(pán)區(qū), 適宜快速增長(zhǎng)的表.View(視圖) (省略了很多細(xì)節(jié))關(guān)系視圖建立視圖create view my_v asselect first_name|, |last_name NAME from HR.employee

48、s ; -重命名是必需的,當(dāng)運(yùn)用列銜接時(shí).- Group by , order by , having 子句是允許的. 也允許Union兩個(gè)查詢.我需求什么權(quán)限 ?需求有視圖所援用的表的選擇的權(quán)限.需求有創(chuàng)建視圖的權(quán)限:grant create any view to IC ; - user IC 具有了創(chuàng)建視圖的權(quán)限視圖的作用 ?把復(fù)雜的查詢定義在可重用視圖中, 可以隱藏查詢的擔(dān)任性,限制查詢的前往, 比如定義視圖時(shí)不選擇薪資字段, 再把視圖的選擇權(quán)授予public, 而收回表的選擇權(quán), 可以限制表數(shù)據(jù)的前往, 也可以在視圖的查詢中添加WHERE子句, 限制行的前往,經(jīng)過(guò)賦予視圖的SELEC

49、T權(quán)限更加有效的維護(hù)表中的數(shù)據(jù).可以建立同義詞create public synonym table_name for user1.table_name;有哪些視圖 ?select view_name from user_views ;視圖的原始定義select view_name, text from user_views ;改動(dòng)視圖create or replace view ms_v as select * from ms ;- 不會(huì)損失以前授予對(duì)視圖操作的特權(quán) 刪除視圖drop view 編譯視圖compilealter view view_t compile ; -視圖無(wú)效后, 手工

50、編譯視圖建立強(qiáng)迫視圖forcecreate or replace force view golf as select * from iAmNoExsit ;警告: 創(chuàng)建的視圖帶有編譯錯(cuò)誤。視圖的哪些列是可更新的? 查數(shù)據(jù)詞典user_updatable_columnsdesc user_updatable_columns稱號(hào) 能否為空? 類(lèi)型 OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME NOT NULL VARCHAR2(30) UPDATABLE VARCHAR2(3) INSERTABLE

51、 VARCHAR2(3) DELETABLE VARCHAR2(3)select * from user_updatable_columns where table_name =COMPANY_PHONE_BOOK;WITH READ ONLY 建立只讀視圖create or replace force view golf as select * from iAmNoExsit WITH READ ONLY;- 使得視圖只讀, 防止用戶經(jīng)過(guò)視圖改動(dòng)了表數(shù)據(jù).經(jīng)過(guò)視圖更新數(shù)據(jù)普通直接從數(shù)據(jù)表中選擇的列可以直接更新, 但一些銜接( |)成的虛擬列或者統(tǒng)計(jì)列不能直接更新, 此時(shí)可以思索在視圖上建立I

52、NSTEAD OF 觸發(fā)器.WITH CHECK OPTION 約束create or replace view friend as select * from workMess where ID=9 with check option ;由于加上了約束, 那么當(dāng)DML數(shù)據(jù)時(shí), 只需那些能被視圖選中的數(shù)據(jù)才允許插入或者更新.update friend set id=10 ;update friend set id=10 *ERROR 位于第 1 行:ORA-01402: 視圖 WITH CHECK OPTIDN 違反 where 子句但是, 此時(shí)運(yùn)用where id=9條件使得照?？梢愿?i

53、nsert into friend (id) values(11) ;insert into friend (id) values(11) *ERROR 位于第 1 行:ORA-01402: 視圖 WITH CHECK OPTIDN 違反 where 子句delete friend ;已刪除 1 行。內(nèi)嵌視圖內(nèi)嵌視圖根本上就是嵌入到父查詢中的查詢, 可以在任何可以運(yùn)用表的地方運(yùn)用. 內(nèi)嵌視圖是暫時(shí)的,它只存在于父查詢的運(yùn)轉(zhuǎn)期間, 但是它可以讓他在整個(gè)查詢的任何部分運(yùn)用視圖結(jié)果.什么樣子的select department_name,count(*), TO_CHAR(count(*)/tota

54、l_empt)*100,90.99)|% pct from hr.departments dep, hr.employees emp, ( select count(*) cnt from hr.employees ) total_emp where dep.department_id = emp.department_id group by department_name, total_empttotal_emp 成為內(nèi)嵌視圖select count(*) cnt from hr.employees 的別名.類(lèi)似select department_name,count(*), TO_CHAR

55、(count(*)/( select count(*) from hr.employees ) )*100,90.99)|% pct from hr.departments dep, hr.employees emp where dep.department_id = emp.department_id group by department_nameROWNUM有用嗎?ROWNUM 是一個(gè)反映了從表選取行的次序的偽列, 所要選取的第一行ROWNUM值為1, 第二行的rownum為2，后面的行依次類(lèi)推。但是, ROWNUM值會(huì)在用ORDER BY子句之前就指定給了每個(gè)行.內(nèi)嵌視圖使ROWNUM

56、真正有用:select last_name, hire_date from (select last_name, hire_date from hr.employees order by hire_date )where rownum=2 and afterOrderResultNo=3select * from ( select id,ro r from(select id, row_number()over(order by id desc) ro from rownumtest ) )where r between 1000 and 1020 ; 數(shù)據(jù)詞典視圖常見(jiàn)的視圖(省略了很多細(xì)節(jié))

57、數(shù)據(jù)詞典數(shù)據(jù)詞典是用戶在整個(gè)ORACLE數(shù)據(jù)庫(kù)的編目, 當(dāng)建立用戶,表, 約束 和 其他數(shù)據(jù)庫(kù)對(duì)象時(shí), ORACLE都會(huì)自動(dòng)維護(hù)一個(gè)在數(shù)據(jù)庫(kù)中存儲(chǔ)的項(xiàng)編目. 用戶經(jīng)過(guò)數(shù)據(jù)詞典可以知道:所擁有的表的稱號(hào)和特性有權(quán)查看的一切表的稱號(hào)和屬性檢索存儲(chǔ)過(guò)程的代碼特定表空間中存儲(chǔ)的一切數(shù)據(jù)文件羅列依賴于其他對(duì)象的對(duì)象.dictionary 保管了數(shù)據(jù)詞典中一切視圖的完好列表和簡(jiǎn)短描畫(huà).table_name : 稱號(hào)comments : 文本注釋DICt_COLUMNS詞典表中的列描畫(huà):table_name : 稱號(hào)column_name : 列名comments : 列的文本注釋DBA_objects數(shù)據(jù)

58、庫(kù)中一切對(duì)象的列表和相關(guān)內(nèi)容dba_tables數(shù)據(jù)庫(kù)中一切表的列表和相關(guān)內(nèi)容dba_tab_columns表的一切列, 視圖, 簇的信息dba_tab_privs賦予數(shù)據(jù)庫(kù)中對(duì)象上的特權(quán)信息dba_col_privs數(shù)據(jù)庫(kù)特定列上一切特權(quán)dba_tab_comments/dba_col_comments表和視圖上建立的注釋dba_constraints表上的約束信息dba_cons_columns數(shù)據(jù)庫(kù)表上約束的列的信息信息dba_external_tables外部表的信息dba_external_locations外部表的數(shù)據(jù)位置信息dba_object_tables對(duì)象表的信息dba_c

59、oll_types一切類(lèi)集類(lèi)型的相關(guān)信息dba_jobs一切排定作業(yè)的相關(guān)信息dba_types一切對(duì)象類(lèi)型的信息dba_type_attrs對(duì)象類(lèi)型屬性的信息dba_type_methods對(duì)象類(lèi)型中方法的信息dba_method_params對(duì)象類(lèi)型方法的參數(shù)信息dba_method_results調(diào)用者可以運(yùn)用的方法前往類(lèi)型信息dba_lobs一切大對(duì)象或者lobdba_views一切視圖的信息dba_updatable_columns聯(lián)接視圖的一切列,以及能否可以經(jīng)過(guò)他們修正數(shù)據(jù)dba_triggers一切與觸發(fā)器相關(guān)的信息dba_trigger_cols觸發(fā)器的一切列的信息dba_s

60、ource實(shí)踐的PL/SQL源代碼dba_procedures過(guò)程和函數(shù)的信息all_arguments過(guò)程和函數(shù)的參數(shù)和前往值的信息dba_dependencies對(duì)象之間的依賴信息dba_errorsshow errors將查詢的視圖, 前往最近一次的錯(cuò)誤信息dba_indexes索引的相關(guān)信息dba_ind_columns索引的列的信息dba_roles角色信息dba_role_privs數(shù)據(jù)庫(kù)的用戶或者角色 被 賦予了何種角色的信息dba_sys_privs數(shù)據(jù)庫(kù)的用戶或者角色 被 賦予了何種系統(tǒng)特權(quán)dba_directories所建立的目錄對(duì)象的信息global_name只需一行,一