ORACLE數(shù)據(jù)庫(kù)系列教程-SQL基礎(chǔ)知識(shí)篇

1、ORACLE系列教程系列教程2016年5月張超公安信息化產(chǎn)品中心 研發(fā)五部簡(jiǎn)介簡(jiǎn)介SQLSQL（Structured Query LanguageStructured Query Language）結(jié)構(gòu)化查詢(xún)）結(jié)構(gòu)化查詢(xún)語(yǔ)言是語(yǔ)言是IBMIBM公司公司San JoseSan Jose實(shí)驗(yàn)室為實(shí)驗(yàn)室為System RSystem R而設(shè)而設(shè)計(jì)的查詢(xún)語(yǔ)言，后被國(guó)際標(biāo)準(zhǔn)化組織（計(jì)的查詢(xún)語(yǔ)言，后被國(guó)際標(biāo)準(zhǔn)化組織（ISOISO）批）批準(zhǔn)作為關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言的國(guó)際標(biāo)準(zhǔn)。準(zhǔn)作為關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言的國(guó)際標(biāo)準(zhǔn)。SQLSQL目前遵循的是目前遵循的是19921992年標(biāo)準(zhǔn)，即年標(biāo)準(zhǔn)，即SQL-92SQL-92。各數(shù)據(jù)庫(kù)廠(chǎng)

2、家對(duì)各數(shù)據(jù)庫(kù)廠(chǎng)家對(duì)SQL-92SQL-92標(biāo)準(zhǔn)均有擴(kuò)充，擴(kuò)充部標(biāo)準(zhǔn)均有擴(kuò)充，擴(kuò)充部分不能完全保證數(shù)據(jù)庫(kù)之間的兼容性。分不能完全保證數(shù)據(jù)庫(kù)之間的兼容性。簡(jiǎn)介簡(jiǎn)介SQLSQL可以分為可以分為: : （基礎(chǔ)篇只介紹前兩種）（基礎(chǔ)篇只介紹前兩種）DML:DML:數(shù)據(jù)操作語(yǔ)言（數(shù)據(jù)操作語(yǔ)言（Data Manipulation LanguageData Manipulation Language）: : select, insert, update, delete, (select, insert, update, delete, (其他：其他：truncate) truncate) DDL:DDL:數(shù)據(jù)

3、定義語(yǔ)言數(shù)據(jù)定義語(yǔ)言 （Data Definition LanguagesData Definition Languages）: : create, drop , alter,(create, drop , alter,(其他：其他：rename)rename)DCLDCL數(shù)據(jù)控制語(yǔ)言：數(shù)據(jù)控制語(yǔ)言：grantgrant、revokerevoke、set role set role 事務(wù)控制：事務(wù)控制：commitcommit、rollbackrollback、savepointsavepoint（其他：（其他：lock lock tabletable、set constraint(s)set

4、 constraint(s)、set transactionset transaction）審計(jì)控制：審計(jì)控制：auditaudit、noauditnoaudit系統(tǒng)控制：系統(tǒng)控制：alter system alter system 會(huì)話(huà)控制：會(huì)話(huà)控制：alter session alter session 其他語(yǔ)句：其他語(yǔ)句：commentcomment（添加注釋?zhuān)?、（添加注釋?zhuān)xplain planexplain plan、analyzeanalyze、validatevalidate、call call DDL數(shù)據(jù)定義語(yǔ)言數(shù)據(jù)定義語(yǔ)言CREATE(CREATE(建立建立) )ALTE

5、R(ALTER(修改修改) )DROP(DROP(刪除刪除) )DDL數(shù)據(jù)定義語(yǔ)言數(shù)據(jù)定義語(yǔ)言CREATE-CREATE-創(chuàng)建用戶(hù)創(chuàng)建用戶(hù)CREATE USER user_name IDENTIFIED BY password DEFAULT TABLESPACE tablespace_name TEMPORARY TABLESPACE tablespace_name;創(chuàng)建創(chuàng)建user_nameuser_name用戶(hù)，密碼為用戶(hù)，密碼為passwordpassword，使用，使用tablespace_nametablespace_name表空間，如果沒(méi)有指定表空間，默認(rèn)表表空間，如果沒(méi)有指定表

6、空間，默認(rèn)表空間為空間為USERSUSERS表空間。表空間。例：create user zc1 identified by zc1;DDL數(shù)據(jù)定義語(yǔ)言數(shù)據(jù)定義語(yǔ)言CREATE-CREATE-創(chuàng)建數(shù)據(jù)表創(chuàng)建數(shù)據(jù)表CREATE TABLE table_name(Column1 datatype defaultnot null primary key,constraint key_name primary key(column_list) using index tablespace tablespace_name)tablespace tablespace_name;Datatype Dataty

7、pe 是數(shù)據(jù)類(lèi)型是數(shù)據(jù)類(lèi)型:varchar2(x), number(x,x), date, integer:varchar2(x), number(x,x), date, integer等。等。Not nullNot null非空限制，如果不寫(xiě)表示可為空。非空限制，如果不寫(xiě)表示可為空。Primary keyPrimary key主鍵，可以緊跟在字段后，或在最后使用主鍵，可以緊跟在字段后，或在最后使用constraintconstraint。未指名表空間，數(shù)據(jù)表建立在用戶(hù)默認(rèn)的表空間中。未指名表空間，數(shù)據(jù)表建立在用戶(hù)默認(rèn)的表空間中。例：例：-創(chuàng)建部門(mén)表create table DEPT(DEPT

8、NO NUMBER(2) NOT NULL primary key,DNAME VARCHAR2(14),LOC VARCHAR2(13)DDL數(shù)據(jù)定義語(yǔ)言數(shù)據(jù)定義語(yǔ)言CREATE-CREATE-創(chuàng)建索引創(chuàng)建索引CREATE UNIQUE INDEX index_name ON table_name(column_list)tablespace tablespace_name;在在table_nametable_name表上按照表上按照column_listcolumn_list建立索引，索引名為建立索引，索引名為index_nameindex_name，索引保存在，索引保存在tablespa

9、ce_nametablespace_name表空間中。表空間中。UNIQUE:UNIQUE:唯一性索引。唯一性索引。例：創(chuàng)建：create index pk_DEPT on DEPT(DEPTNO); -(創(chuàng)建主鍵索引)create index pk_EMP on EMP(ENAME); -(創(chuàng)建非主鍵索引)DDL數(shù)據(jù)定義語(yǔ)言數(shù)據(jù)定義語(yǔ)言ALTER-ALTER-修改表修改表ALTER TABLE table_name REMAME TO new_table_name;將將table_nametable_name表名修改為表名修改為new_table_namenew_table_name。ALT

10、ER TABLE table_name ADD(column1 datatype default not null,column2 datatype default not null, . );在在table_nametable_name表中增加表中增加column1,column2.column1,column2.字段，追加到字段最后。字段，追加到字段最后。ALTER TABLE table_name MODIFY(column1 datatype default not null|nullable,column2 datatype default not null|nullable, .

11、修改修改table_nametable_name表字段屬性，長(zhǎng)度或精度不能小于原長(zhǎng)度或精度。表字段屬性，長(zhǎng)度或精度不能小于原長(zhǎng)度或精度。ALTER TABLE table_name DROP COLUMN column_name;刪除刪除table_nametable_name表中的表中的column_namecolumn_name字段。字段。ALTER TABLE table_name RENAME COLUMN old_name TO new_name;修改修改table_nametable_name表的表的old_nameold_name字段名為字段名為new_namenew_name。

12、ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY(column_list);建立建立table_nametable_name表的主鍵，主鍵名表的主鍵，主鍵名pk_namepk_name，主鍵字段，主鍵字段column_listcolumn_list。ALTER TABLE table_name DROP CONSTRAINT pk_name;刪除刪除table_nametable_name表的表的pk_namepk_name主鍵。主鍵。一個(gè)表只能有一個(gè)主鍵一個(gè)表只能有一個(gè)主鍵DDL數(shù)據(jù)定義語(yǔ)言數(shù)據(jù)定義語(yǔ)言DROP -DROP -

13、刪除刪除DROP TABLE table_name;刪除刪除table_nametable_name表。表。DROP INDEX index_name;刪除刪除index_nameindex_name索引。索引。刪除主鍵（強(qiáng)制限制的一種），使用刪除主鍵（強(qiáng)制限制的一種），使用ALTER TABLE table_name DROP CONSTRAINT pk_name;DML小結(jié)小結(jié)從從ALTER和和CREATE及及DROP語(yǔ)法數(shù)量再一次證明需語(yǔ)法數(shù)量再一次證明需求是易變的。求是易變的。如果增加表字段，并要求字段的排列位置，可以使用如果增加表字段，并要求字段的排列位置，可以使用:CREATE T

14、ABLE temp_table_name AS SELECT * FROM table_name;DROP TABLE table_name;CREATE TABLE table_name(column);INSERT INTO table_name VALUES(column_list) (SELECT column_list1 FROM temp_table_name);需要注意的是，刪除表后，表中的索引也被刪除，所以，在執(zhí)行上需要注意的是，刪除表后，表中的索引也被刪除，所以，在執(zhí)行上述操作前要保留好表的索引腳本。述操作前要保留好表的索引腳本。DDL數(shù)據(jù)操作語(yǔ)言數(shù)據(jù)操作語(yǔ)言SELECT(查

15、詢(xún)查詢(xún))INSERT(插入插入)UPDATE(更新更新)DELETE(刪除刪除)DDL數(shù)據(jù)操作語(yǔ)言數(shù)據(jù)操作語(yǔ)言SELECT-查詢(xún)查詢(xún)SELECT ALL | DISTINCT ON ( expression , . ) * | expression AS output_name , . INTO TEMPORARY | TEMP TABLE new_table FROM from_item , . WHERE condition GROUP BY expression , . HAVING condition , . UNION | INTERSECT | EXCEPT ALL select

16、ORDER BY expression ASC | DESC | USING operator , . FOR UPDATE OF class_name , . WHEREWHERE和和HAVINGHAVING的區(qū)別：的區(qū)別：WHERE 子句搜索條件在進(jìn)行分組操作之前應(yīng)用；而 HAVING 搜索條件在進(jìn)行分組操作之后應(yīng)用。HAVING 語(yǔ)法與 WHERE 語(yǔ)法類(lèi)似，但 HAVING 可以包含聚合函數(shù)DDL數(shù)據(jù)操作語(yǔ)言數(shù)據(jù)操作語(yǔ)言DEPT/ EMP EMP表介紹表介紹DEPTDEPT部門(mén)定義表，部門(mén)定義表，DEPTNODEPTNO是主鍵是主鍵EMPEMP職工表職工表,EMPNO,EMPNO主鍵，

17、主鍵，DEPTNODEPTNO外鍵關(guān)聯(lián)外鍵關(guān)聯(lián)DEPTDEPTDDL數(shù)據(jù)操作語(yǔ)言數(shù)據(jù)操作語(yǔ)言SELECT-單表查詢(xún)單表查詢(xún)查詢(xún)表中所有字段和所有記錄，查詢(xún)表中所有字段和所有記錄，select后跟后跟* 表示所有字段表示所有字段SELECT * FROM DEPT;查詢(xún)指定字段，在查詢(xún)指定字段，在select后跟查詢(xún)的字段名列表，字段間后跟查詢(xún)的字段名列表，字段間用用,隔開(kāi)隔開(kāi)SELECT DEPTNO, DNAME FROM DEPT;條件查詢(xún)，條件查詢(xún)，F(xiàn)ROM后面使用后面使用WHERE，在，在WHERE中可以使中可以使用用=, , ,=,5DDL數(shù)據(jù)操作語(yǔ)言數(shù)據(jù)操作語(yǔ)言SELECT-UNI

18、ON，UNION ALL，MINUS, INTERSECT返回兩個(gè)查詢(xún)結(jié)果的集合操作，兩個(gè)查詢(xún)結(jié)果集必須字段相同。返回兩個(gè)查詢(xún)結(jié)果的集合操作，兩個(gè)查詢(xún)結(jié)果集必須字段相同。UNION和和UNION ALL并集操作，并集操作，UNION并集后去掉重復(fù)結(jié)果，并集后去掉重復(fù)結(jié)果，UNION ALL直接并集直接并集MINUS差集操作差集操作INTERSECT交集操作交集操作例：SELECT * FROM DEPTUNION SELECT * FROM DEPT-返回所有部門(mén)，因?yàn)槿サ袅酥貜?fù)的結(jié)果返回所有部門(mén)，因?yàn)槿サ袅酥貜?fù)的結(jié)果DDL數(shù)據(jù)操作語(yǔ)言數(shù)據(jù)操作語(yǔ)言SELECT-ORDER BYORDER BY

19、排序，跟數(shù)字（代表字段位置）或字段名，排序，跟數(shù)字（代表字段位置）或字段名，ASC升序，升序，DESC降序，默認(rèn)升序。降序，默認(rèn)升序。例：SELECT DEPTNO, DNAME,COUNT(*)FROM DEPTWHERE DEPTNO 1GROUP BY DEPTNO, DNAMEHAVING COUNT(*) 0ORDER BY DEPTNO DESC; -ORDER BY 是對(duì)返回的結(jié)果進(jìn)行排序是對(duì)返回的結(jié)果進(jìn)行排序，所以必須放在最后一句。，所以必須放在最后一句。DDL數(shù)據(jù)操作語(yǔ)言數(shù)據(jù)操作語(yǔ)言SELECT-FOR UPDATE鎖等待查詢(xún)。當(dāng)查詢(xún)結(jié)果中有被鎖定記錄時(shí)等待解鎖，當(dāng)記錄鎖等待

20、查詢(xún)。當(dāng)查詢(xún)結(jié)果中有被鎖定記錄時(shí)等待解鎖，當(dāng)記錄被解鎖后返回結(jié)果集，并鎖定返回的記錄。如果被解鎖后返回結(jié)果集，并鎖定返回的記錄。如果FOR UPDATE后跟后跟NOWAIT，遇到鎖后不等待，返回錯(cuò)誤。，遇到鎖后不等待，返回錯(cuò)誤。ORACLE使用記錄級(jí)鎖定，當(dāng)事物被提交或回滾后鎖定被釋放。使用記錄級(jí)鎖定，當(dāng)事物被提交或回滾后鎖定被釋放。死鎖問(wèn)題死鎖問(wèn)題當(dāng)兩個(gè)事物以不同的順序同時(shí)更新多個(gè)表時(shí)就會(huì)發(fā)生死鎖，當(dāng)兩個(gè)事物以不同的順序同時(shí)更新多個(gè)表時(shí)就會(huì)發(fā)生死鎖，解決死鎖的途徑是手工殺掉死鎖的進(jìn)程或者重新啟動(dòng)數(shù)據(jù)庫(kù)。解決死鎖的途徑是手工殺掉死鎖的進(jìn)程或者重新啟動(dòng)數(shù)據(jù)庫(kù)。因此，在使用事物更新多表數(shù)據(jù)時(shí)一定要

21、小心，使用面向?qū)σ虼?，在使用事物更新多表?shù)據(jù)時(shí)一定要小心，使用面向?qū)ο蟮姆椒ǚ庋b數(shù)據(jù)操作可以在很大程度上解決死鎖問(wèn)題。象的方法封裝數(shù)據(jù)操作可以在很大程度上解決死鎖問(wèn)題。DDL數(shù)據(jù)操作語(yǔ)言數(shù)據(jù)操作語(yǔ)言SELECT-子查詢(xún)子查詢(xún)查詢(xún)語(yǔ)句可以嵌套，任何產(chǎn)生數(shù)值的地方都可以使用子查詢(xún)。查詢(xún)語(yǔ)句可以嵌套，任何產(chǎn)生數(shù)值的地方都可以使用子查詢(xún)。在查詢(xún)條件中使用子查詢(xún)時(shí)，當(dāng)子查詢(xún)返回多個(gè)結(jié)果時(shí)只能使用在查詢(xún)條件中使用子查詢(xún)時(shí)，當(dāng)子查詢(xún)返回多個(gè)結(jié)果時(shí)只能使用IN。查詢(xún)順序是先執(zhí)行被依賴(lài)的底層查詢(xún)，然后一層層向上查。查詢(xún)順序是先執(zhí)行被依賴(lài)的底層查詢(xún)，然后一層層向上查。子查詢(xún)只被執(zhí)行一次。子查詢(xún)只被執(zhí)行一次。查詢(xún)平

22、均工資最低的部門(mén)情況查詢(xún)平均工資最低的部門(mén)情況SELECT DEPTNO, AVG(SAL)FROM EMPGROUP BY DEPTNOHAVING AVG(SAL) = (SELECT MIN(AVG(SAL) FROM EMP GROUP BY DEPTNO);-子查詢(xún)先查詢(xún)出最低的部門(mén)平均工資，然后查詢(xún)子查詢(xún)先查詢(xún)出最低的部門(mén)平均工資，然后查詢(xún)部門(mén)最低平均工資等于子查詢(xún)工資的部門(mén)部門(mén)最低平均工資等于子查詢(xún)工資的部門(mén)DDL數(shù)據(jù)操作語(yǔ)言數(shù)據(jù)操作語(yǔ)言SELECT-子查詢(xún)子查詢(xún)使用使用EXISTS(NOT EXISTS)替換替換IN(NOT IN)IN(NOT IN)在執(zhí)行數(shù)據(jù)庫(kù)操作時(shí)性能非常

23、低下，應(yīng)該使用在執(zhí)行數(shù)據(jù)庫(kù)操作時(shí)性能非常低下，應(yīng)該使用EXISTS(NOT EXISTS)替換，特別是替換，特別是NOT IN子句將執(zhí)行一個(gè)內(nèi)部的排子句將執(zhí)行一個(gè)內(nèi)部的排序和合并序和合并;EXISTS子查詢(xún)使用主表的字段限制查詢(xún)數(shù)據(jù)子查詢(xún)使用主表的字段限制查詢(xún)數(shù)據(jù)SELECT * FROM EMP E WHERE EXISTS (SELECT * FROM DEPT WHERE DEPTNO = E.DEPTNO AND DEPTNO = 2);-因?yàn)橐驗(yàn)镋XISTS可以看到外表，所以，如果表名重復(fù)，使用表別名區(qū)分，可以看到外表，所以，如果表名重復(fù)，使用表別名區(qū)分，在子查詢(xún)中一定寫(xiě)清楚和外表的

24、關(guān)聯(lián)關(guān)系，另外，子查詢(xún)寫(xiě)在子查詢(xún)中一定寫(xiě)清楚和外表的關(guān)聯(lián)關(guān)系，另外，子查詢(xún)寫(xiě)SELECT * 是對(duì)的，不用寫(xiě)字段名。是對(duì)的，不用寫(xiě)字段名。DDL數(shù)據(jù)操作語(yǔ)言數(shù)據(jù)操作語(yǔ)言UPDATE修改修改用來(lái)更新數(shù)據(jù)表中的數(shù)據(jù)用來(lái)更新數(shù)據(jù)表中的數(shù)據(jù) UPDATE table_nameSET column_name = new_value, WHERE condition;更新更新DEPT表，將表，將1號(hào)部門(mén)名稱(chēng)修改為號(hào)部門(mén)名稱(chēng)修改為圖書(shū)館圖書(shū)館 UPDATE DEPT SET DNAME = 圖書(shū)館圖書(shū)館,WHERE DEPTNO = 1;INSERT-插入插入語(yǔ)法語(yǔ)法:INSERT INTO table_n

25、ame (column,.) VALUES (expression ,) | SELECT queryDELETE-刪除刪除語(yǔ)法語(yǔ)法:DELETE FROM table_nameWHERE condition;注釋注釋/* 注釋內(nèi)容注釋內(nèi)容 */-注釋內(nèi)容注釋內(nèi)容-SELECT * DELETEFROM EMPWHERE DEPTNO = 2;提示：刪除數(shù)據(jù)前最好先查詢(xún)一下。可以先寫(xiě)提示：刪除數(shù)據(jù)前最好先查詢(xún)一下。可以先寫(xiě)SELECT *，然后把，然后把SELECT *注釋掉，增加注釋掉，增加DELETE。常用函數(shù)常用函數(shù)數(shù)字函數(shù)數(shù)字函數(shù)ABS 取絕對(duì)值取絕對(duì)值 POWER 乘方乘方 LN 1

26、0為底數(shù)取為底數(shù)取冪冪SQRT 平方根平方根 EXP e的的n次乘方次乘方 LOG(m,n) m為底數(shù)為底數(shù)n取取冪冪數(shù)學(xué)運(yùn)算函數(shù)數(shù)學(xué)運(yùn)算函數(shù):ACOS ATAN ATAN2 COS COSH SIGN SIN SINH TAN TANH CEIL 大于或等于取整數(shù)大于或等于取整數(shù) FLOOR 小于或等于取整數(shù)小于或等于取整數(shù)MOD 取余數(shù)取余數(shù) ROUND(n,m) 按按m的位數(shù)取四舍五入值如果的位數(shù)取四舍五入值如果round(日期日期): 中午中午12以后以后將是明天的日期將是明天的日期. round(sysdate,Y)是年的第一天是年的第一天TRUNC(n,m) 按按m的位數(shù)取前面的數(shù)

27、值如果的位數(shù)取前面的數(shù)值如果trunc(日期日期), 確省的是去掉確省的是去掉時(shí)間時(shí)間常用函數(shù)常用函數(shù)字符函數(shù)字符函數(shù)CHR 按數(shù)據(jù)庫(kù)的字符集由數(shù)字返回字符按數(shù)據(jù)庫(kù)的字符集由數(shù)字返回字符 CONCAT(c1,c2) 把兩個(gè)字符把兩個(gè)字符c1,c2組合成一個(gè)字符組合成一個(gè)字符, 和和 | 相同相同REPLACE(c,s,r) 把字符把字符c里出現(xiàn)里出現(xiàn)s的字符替換成的字符替換成r, 返回新字符返回新字符 SUBSTR(c,m,n) m大于大于0,字符字符c從前面從前面m處開(kāi)始取處開(kāi)始取n位字符位字符,m等于等于0和和1一樣一樣, m小與小與0,字符字符c從后面從后面m處開(kāi)始取處開(kāi)始取n位字符位字

28、符TRANSLATE(c,f1,t1) 字符字符c按按f1到到t1的規(guī)則轉(zhuǎn)換成新的字符串的規(guī)則轉(zhuǎn)換成新的字符串INITCAP 字符首字母大寫(xiě)字符首字母大寫(xiě),其它字符小寫(xiě)其它字符小寫(xiě)LOWER 字符全部小寫(xiě)字符全部小寫(xiě)UPPER 字符全部大寫(xiě)字符全部大寫(xiě)LTRIM(c1,c2) 去掉字符去掉字符c1左邊出現(xiàn)的字符左邊出現(xiàn)的字符c2RTRIM(c1,c2) 去掉字符去掉字符c1右右邊出現(xiàn)的字符邊出現(xiàn)的字符c2TRIM(c1,c2) 去掉字符去掉字符c1左右兩邊的字符左右兩邊的字符c2LPAD(c1,n,c2) 字符字符c1按按指指定的位數(shù)定的位數(shù)n顯示顯示，不足的位數(shù)用，不足的位數(shù)用c2字符串替換左邊的空位字符串替換左邊的空位RPAD(c1,n,c2) 字符字符c1按按指指定的位數(shù)定的位數(shù)n顯示顯示，不足的位數(shù)用，不足的位數(shù)用c2字符串替換字符串替換右右邊的空位邊的空位提示：去掉字符串中間的空格可以使用提示：去掉字符串中間的空格可以使用replace常用函數(shù)常用函數(shù)日期函數(shù)日期函數(shù)AD