ORACLE數據庫最基礎教程

1、ORACLEORACLEORACLE數據庫教程數據庫教程數據庫教程數據庫教程數據庫教程數據庫教程20072007年年5 5月月SQL-001SQL-001簡介簡介SQLSQL（Structured Query LanguageStructured Query Language）結構化查詢）結構化查詢語言是語言是IBMIBM公司公司San JoseSan Jose實驗室為實驗室為System RSystem R而設而設計的查詢語言，后被國際標準化組織（計的查詢語言，后被國際標準化組織（ISOISO）批）批準作為關系數據庫語言的國際標準。準作為關系數據庫語言的國際標準。SQLSQL目前遵循的是目前

2、遵循的是19921992年標準，即年標準，即SQL-92SQL-92。各數據庫廠家對各數據庫廠家對SQL-92SQL-92標準均有擴充，擴充部標準均有擴充，擴充部分不能完全保證數據庫之間的兼容性。分不能完全保證數據庫之間的兼容性。簡介簡介SQLSQL可以分為可以分為: : （基礎篇只介紹前兩種）（基礎篇只介紹前兩種）DML:DML:數據操作語言（數據操作語言（Data Manipulation LanguageData Manipulation Language）: : select, insert, update, delete, (select, insert, update, delet

3、e, (其他：其他：truncate) truncate) DDL:DDL:數據定義語言數據定義語言 （Data Definition LanguagesData Definition Languages）: : create, drop , alter,(create, drop , alter,(其他：其他：rename)rename)DCLDCL數據控制語言：數據控制語言：grantgrant、revokerevoke、set role set role 事務控制：事務控制：commitcommit、rollbackrollback、savepointsavepoint（其他：（其他：l

4、ock lock tabletable、set set constraint(sconstraint(s) )、set transactionset transaction）審計控制：審計控制：auditaudit、noauditnoaudit系統(tǒng)控制：系統(tǒng)控制：alter system alter system 會話控制：會話控制：alter session alter session 其他語句：其他語句：commentcomment（添加注釋）、（添加注釋）、explain planexplain plan、analyzeanalyze、validatevalidate、call call

5、 DDL數據定義語言數據定義語言CREATE(CREATE(建立建立) )ALTER(ALTER(修改修改) )DROP(DROP(刪除刪除) )語言描述約定語言描述約定 ：表示可選項：表示可選項 | ：表示選擇項：表示選擇項CREATE：粗體表示關鍵字或必輸項：粗體表示關鍵字或必輸項Table_name：斜體表示用戶輸入項：斜體表示用戶輸入項創(chuàng)建創(chuàng)建 ：藍色表示說明：藍色表示說明DDL數據定義語言數據定義語言CREATE-CREATE-創(chuàng)建用戶創(chuàng)建用戶CREATE USER user_name IDENTIFIED BY password DEFAULT TABLESPACE tablesp

6、ace_name TEMPORARY TABLESPACE tablespace_name;創(chuàng)建創(chuàng)建user_nameuser_name用戶，密碼為用戶，密碼為passwordpassword，使用，使用tablespace_nametablespace_name表空間，如果沒有指定表空間，默認表表空間，如果沒有指定表空間，默認表空間為空間為systemsystem表空間。表空間。Oracle10gOracle10g有用戶默認表空間設置。有用戶默認表空間設置。建議在創(chuàng)建用戶時一定要指名表空間。建議在創(chuàng)建用戶時一定要指名表空間。DDL數據定義語言數據定義語言CREATE-CREATE-創(chuàng)建數據表

7、創(chuàng)建數據表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;DatatypeDatatype 是數據類型是數據類型:varchar2(x), :varchar2(x), number(x,xnumber(x,x), date, integer), date, integer等。等。Not

8、nullNot null非空限制，如果不寫表示可為空。非空限制，如果不寫表示可為空。Primary keyPrimary key主鍵，可以緊跟在字段后，或在最后使用主鍵，可以緊跟在字段后，或在最后使用constraintconstraint。未指名表空間，數據表建立在用戶默認的表空間中。未指名表空間，數據表建立在用戶默認的表空間中。DDL數據定義語言數據定義語言CREATE-CREATE-創(chuàng)建索引創(chuàng)建索引CREATE UNIQUE INDEX index_name ON table_name(column_list)tablespace tablespace_name;在在table_name

9、table_name表上按照表上按照column_listcolumn_list建立索引，索引名為建立索引，索引名為index_nameindex_name，索引保存在，索引保存在tablespace_nametablespace_name表空間中。表空間中。UNIQUE:UNIQUE:唯一性索引。唯一性索引。DDL數據定義語言數據定義語言ALTER-ALTER-修改表修改表ALTER TABLE table_name RENAME TO new_table_name;將將table_nametable_name表名修改為表名修改為new_table_namenew_table_name。AL

10、TER 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表字段屬性，長度或精度不能小于原長度或精度。表字段屬性，長度或精度不能小于原長度或精度。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主鍵。主鍵。一個表只能有一個主鍵一個表只能有一個主鍵DDL數據定義語言數據定義語言DROP -DROP

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

14、E TABLE temp_table_name AS SELECT * FROM table_name;2.DROP TABLE table_name;3.CREATE TABLE table_name(column);4.INSERT INTO table_name VALUES(column_list) (SELECT column_list1 FROM temp_table_name);5.DROP TABLE table_name;需要注意的是，刪除表后，表中的索引也被刪除，所以，在執(zhí)行上需要注意的是，刪除表后，表中的索引也被刪除，所以，在執(zhí)行上述操作前要保留好表的索引腳本。述操作前要

15、保留好表的索引腳本。這里沒有寫這里沒有寫DROP DATABASE的語法，如果大家想的語法，如果大家想要嘗試刪除數據庫的話，最好在專家的指導下進行！要嘗試刪除數據庫的話，最好在專家的指導下進行！DDL數據操作語言數據操作語言SELECT(查詢查詢)INSERT(插入插入)UPDATE(更新更新)DELETE(刪除刪除)DDL數據操作語言數據操作語言SELECT-查詢查詢SELECT ALL | DISTINCT ON ( expression , . ) * | expression AS output_name , . INTO TEMPORARY | TEMP TABLE new_tabl

16、e FROM from_item , . WHERE condition GROUP BY expression , . HAVING condition , . UNION | INTERSECT | EXCEPT ALL select ORDER BY expression ASC | DESC | USING operator , . FOR UPDATE OF class_name , . LIMIT count | ALL OFFSET | , start 后續(xù)查詢范例均以后續(xù)查詢范例均以oracleoracle中的中的SCOTT/TIGERSCOTT/TIGER用戶表為例用戶表為例

17、DDL數據操作語言數據操作語言SCOTT/TIGER表介紹表介紹DEPTDEPT部門定義表，部門定義表，DEPTNODEPTNO是主鍵是主鍵EMPEMP職工表職工表,EMPNO,EMPNO主鍵，主鍵，DEPTNODEPTNO外鍵關聯外鍵關聯DEPTDEPT其他表沒有數據或沒有關系，不在這里介紹其他表沒有數據或沒有關系，不在這里介紹DDL數據操作語言數據操作語言SELECT-單表查詢單表查詢查詢表中所有字段和所有記錄，查詢表中所有字段和所有記錄，select后跟后跟* 表示所有字段表示所有字段SELECT * FROM DEPT;查詢指定字段，在查詢指定字段，在select后跟查詢的字段名列表，

18、字段間后跟查詢的字段名列表，字段間用用,隔開隔開SELECT DEPTNO, DNAME FROM DEPT;條件查詢，條件查詢，FROM后面使用后面使用WHERE，在，在WHERE中可以使中可以使用用=, , ,=, 10GROUP BY DEPTNO, DNAMEHAVING COUNT(*) 0ORDER BY DEPTNO DESC; -ORDER BY 是對返回的結果進行排序，所以必須放在最后一句。是對返回的結果進行排序，所以必須放在最后一句。SELECT DEPTNO, DNAMEFROM SCOTT.DEPTWHERE DEPTNO 10GROUP BY DEPTNO, DNAM

19、EHAVING COUNT(*) 0ORDER BY 1 DESC; -結果同第一個查詢，字段位置從結果同第一個查詢，字段位置從1開始。開始。DDL數據操作語言數據操作語言SELECT-FOR UPDATE鎖等待查詢。當查詢結果中有被鎖定記錄時等待解鎖，當記錄鎖等待查詢。當查詢結果中有被鎖定記錄時等待解鎖，當記錄被解鎖后返回結果集，并鎖定返回的記錄。如果被解鎖后返回結果集，并鎖定返回的記錄。如果FOR UPDATE后跟后跟NOWAIT，遇到鎖后不等待，返回錯誤。，遇到鎖后不等待，返回錯誤。ORACLE使用記錄級鎖定，當事物被提交或回滾后鎖定被釋放。使用記錄級鎖定，當事物被提交或回滾后鎖定被釋放

20、。死鎖問題死鎖問題當兩個事物以不同的順序同時更新多個表時就會發(fā)生死鎖當兩個事物以不同的順序同時更新多個表時就會發(fā)生死鎖（對牛），解決死鎖的途徑是手工殺掉死鎖的進程或者重新（對牛），解決死鎖的途徑是手工殺掉死鎖的進程或者重新啟動數據庫。因此，在使用事物更新多表數據時一定要小心，啟動數據庫。因此，在使用事物更新多表數據時一定要小心，使用面向對象的方法封裝數據操作可以在很大程度上解決死使用面向對象的方法封裝數據操作可以在很大程度上解決死鎖問題。鎖問題。DDL數據操作語言數據操作語言SELECT-子查詢子查詢查詢語句可以嵌套，任何產生數值的地方都可以使用子查詢。查詢語句可以嵌套，任何產生數值的地方都可

21、以使用子查詢。在查詢條件中使用子查詢時，當子查詢返回多個結果時只能使用在查詢條件中使用子查詢時，當子查詢返回多個結果時只能使用IN。查詢順序是先執(zhí)行被依賴的底層查詢，然后一層層向上查。查詢順序是先執(zhí)行被依賴的底層查詢，然后一層層向上查。子查詢只被執(zhí)行一次。子查詢只被執(zhí)行一次。查詢平均工資最低的部門情況查詢平均工資最低的部門情況SELECT DEPTNO, AVG(SAL)FROM SCOTT.EMPGROUP BY DEPTNOHAVING AVG(SAL) = (SELECT MIN(AVG(SAL) FROM SCOTT.EMPGROUP BY DEPTNO);-子查詢先查詢出最低的部門平

22、均工資，然后查詢部門最低平均工資等子查詢先查詢出最低的部門平均工資，然后查詢部門最低平均工資等于子查詢工資的部門于子查詢工資的部門DDL數據操作語言數據操作語言SELECT-子查詢子查詢在在FROM中使用子查詢，子查詢在這里相對與中使用子查詢，子查詢在這里相對與VIEWSELECT D.DEPTNO, D.DNAME, S.AVG_SAL, S.SUM_SAL, S.NUMFROM SCOTT.DEPT D, (SELECT DEPTNO, AVG(SAL) AS AVG_SAL, SUM(SAL) AS SUM_SAL, COUNT(*) AS NUMFROM SCOTT.EMPGROUP

23、BY DEPTNO) SWHERE D.DEPTNO = S.DEPTNO;在在WHERE中使用子查詢，例子不好，應該直接寫表關聯，這里只是為中使用子查詢，例子不好，應該直接寫表關聯，這里只是為了說明語法。了說明語法。SELECT * FROM SCOTT.EMP WHERE DEPTNO IN (SELECT DEPTNO FROM SCOTT.DEPT WHERE DEPTNO = 20);DDL數據操作語言數據操作語言SELECT-子查詢子查詢使用使用EXISTS(NOT EXISTS)替換替換IN(NOT IN)IN(NOT IN)在執(zhí)行數據庫操作時性能非常低下，應該使用在執(zhí)行數據庫操

24、作時性能非常低下，應該使用EXISTS(NOT EXISTS)替換，特別是替換，特別是NOT IN子句將執(zhí)行一個內部的排子句將執(zhí)行一個內部的排序和合并序和合并;EXISTS子查詢使用主表的字段限制查詢數據子查詢使用主表的字段限制查詢數據SELECT * FROM SCOTT.EMP E WHERE EXISTS (SELECT * FROM SCOTT.DEPT WHERE DEPT.DEPTNO = E.DEPTNO AND DEPTNO = 20);-因為因為EXISTS可以看到外表，所以，如果表名重復，使用表別名區(qū)分，可以看到外表，所以，如果表名重復，使用表別名區(qū)分，在子查詢中一定寫清楚

25、和外表的關聯關系，另外，子查詢寫在子查詢中一定寫清楚和外表的關聯關系，另外，子查詢寫SELECT * 是對的，不用寫字段名。是對的，不用寫字段名。DDL數據操作語言數據操作語言UPDATE修改修改用來更新數據表中的數據用來更新數據表中的數據 UPDATE table_nameSET column_name = new_value, WHERE condition;更新更新DEPT表，將表，將20號部門名稱修改為號部門名稱修改為銷售部銷售部，地址修改為，地址修改為北京北京UPDATE DEPT SET DNAME = 銷售部銷售部, LOC = 北京北京WHERE DEPTNO = 20;子查詢

26、也可在子查詢也可在UPDATE中使用中使用,將職工領導是將職工領導是KING的人員薪資增加的人員薪資增加100元元UPDATE SCOTT.EMP SET SAL = SAL + 100WHERE EMPNO IN (SELECT E.EMPNO FROM SCOTT.EMP E, SCOTT.EMP G WHERE E.MGR = G.EMPNO AND G.ENAME = KING); UPDATE SCOTT.EMP SET SAL = SAL + 100WHERE EXISTS (SELECT * FROM SCOTT.EMP E, SCOTT.EMP G WHERE E.MGR =

27、G.EMPNO AND E.EMPNO = EMP.EMPNO AND G.ENAME = KING); DDL數據操作語言數據操作語言INSERT-插入插入語法語法:INSERT INTO table_name (column,.) VALUES (expression ,) | SELECT query當表字段和插入值相同時，可以省略字段列表當表字段和插入值相同時，可以省略字段列表INSERT INTO SCOTT.BONUS VALUES(TURNER, SALESMAN, 200, 40);當只插入部分字段時，必須列舉字段，未賦值字段使用默認值或為空當只插入部分字段時，必須列舉字段，未

28、賦值字段使用默認值或為空INSERT INTO SCOTT.BONUS(ENAME, JOB, SAL) VALUES(CLARK, MANAGER, 100);也可以使用查詢給表插入數據也可以使用查詢給表插入數據INSERT INTO SCOTT.BONUS(ENAME, JOB, SAL) SELECT ENAME, JOB, SAL FROM SCOTT.EMP WHERE MGR = 7698;DDL數據操作語言數據操作語言DELETE-刪除刪除語法語法:DELETE FROM table_nameWHERE condition;刪除刪除BONUS中中ENAME為為CLARK的記錄的記

29、錄DELETE FROM SCOTT.BONUS WHERE ENAME = CLARK刪除重復記錄，刪除重復記錄，ROWID是記錄的物理位置，一經確定永不改變是記錄的物理位置，一經確定永不改變DELETE FROM SCOTT.BONUS B WHERE ROWID 1);-刪除刪除ENAME重復記錄重復記錄序列號序列號創(chuàng)建創(chuàng)建CREATE SEQUENCE name INCREMENT BY n START WITH n MAXVALUE n | NOMAXVALUE MINVALUE n | NOMINVALUE CYCLE | NOCYCLE CACHE n | NOCACHE;說明：

30、說明：INCREMENT BY n 一次增長一次增長n 個數字個數字START WITH n 初始值初始值NOMAXVALUE 缺省值缺省值10E+27NOMINVALUE 缺省值缺省值1NOCYCLE 不循環(huán)不循環(huán), 常用于唯一關鍵字常用于唯一關鍵字CACHE n 在內存里緩存在內存里緩存n個序列個序列,出錯回退時會丟失出錯回退時會丟失創(chuàng)建從創(chuàng)建從1開始，不緩存的開始，不緩存的EMP_EMPNO序列。序列。CREATE SEQUENCE EMP_EMPNO START WITH 1 NOCACHE;修改修改ALTER SEQUENCE name INCREMENT BY n MAXVALUE

31、 n | NOMAXVALUE MINVALUE n | NOMINVALUE CYCLE | NOCYCLE CACHE n | NOCACHE;-起始值不能改變，如果要改變序列的起始值起始值不能改變，如果要改變序列的起始值, 先刪除先刪除, 再新建。再新建。刪除刪除DROP SEQUENCE name;DROP SEQUENCE EMP_EMPNO;序列號序列號使用使用NEXTVAL下一個序列號值下一個序列號值CURRVAL當前序列號值當前序列號值如果如果EMP_EMPNO是一個序列號，可以用下面是一個序列號，可以用下面SQL取序列值取序列值-去序列的當前值去序列的當前值SELECT EM

32、P_EMPNO.CURRVAL FROM DUAL;-去序列的下一個值去序列的下一個值SELECT EMP_EMPNO.NEXTVAL FROM DUAL;-使用序列插入數據使用序列插入數據INSERT INTO EMP(EMPNO, ENAME,) VALUES(EMP_EMPNO.NEXTVAL, JONE,);不能用序列號的不能用序列號的nextval和和currval的地方的地方視圖查詢、視圖查詢、distinct查詢、有查詢、有group by,having,order by的查詢、的查詢、有子查詢的查詢、表里的缺省值有子查詢的查詢、表里的缺省值注釋注釋/* 注釋內容注釋內容 */-

33、注釋內容注釋內容-SELECT * DELETEFROM EMPWHERE DEPTNO = 20;提示：刪除數據前最好先查詢一下。可以先寫提示：刪除數據前最好先查詢一下?？梢韵葘慡ELECT *，然后把，然后把SELECT *注釋掉，增加注釋掉，增加DELETE。常用函數常用函數數字函數數字函數ABS 取絕對值取絕對值 POWER 乘方乘方 LN 10為底數取為底數取冪冪SQRT 平方根平方根 EXP e的的n次乘方次乘方 LOG(m,n) m為底數為底數n取取冪冪數學運算函數數學運算函數:ACOS ATAN ATAN2 COS COSH SIGN SIN SINH TAN TANH CEI

34、L 大于或等于取整數大于或等于取整數 FLOOR 小于或等于取整數小于或等于取整數MOD 取余數取余數 ROUND(n,m) 按按m的位數取四舍五入值如果的位數取四舍五入值如果round(日期日期): 中午中午12以后以后將是明天的日期將是明天的日期. round(sysdate,Y)是年的第一天是年的第一天TRUNC(n,m) 按按m的位數取前面的數值如果的位數取前面的數值如果trunc(日期日期), 確省的是去掉確省的是去掉時間時間常用函數常用函數字符函數字符函數CHR 按數據庫的字符集由數字返回字符按數據庫的字符集由數字返回字符 CONCAT(c1,c2) 把兩個字符把兩個字符c1,c2

35、組合成一個字符組合成一個字符, 和和 | 相同相同REPLACE(c,s,r) 把字符把字符c里出現里出現s的字符替換成的字符替換成r, 返回新字符返回新字符 SUBSTR(c,m,n) m大于大于0,字符字符c從前面從前面m處開始取處開始取n位字符位字符,m等于等于0和和1一樣一樣, m小與小與0,字符字符c從后面從后面m處開始取處開始取n位字符位字符TRANSLATE(c,f1,t1) 字符字符c按按f1到到t1的規(guī)則轉換成新的字符串的規(guī)則轉換成新的字符串INITCAP 字符首字母大寫字符首字母大寫,其它字符小寫其它字符小寫LOWER 字符全部小寫字符全部小寫UPPER 字符全部大寫字符全部大寫LTRIM(c1,c2) 去掉字符去掉字符c1左邊出現的字符左邊出現的字符c2RTRIM(c1,c2) 去掉字符去掉字符c1右右邊出現的字符邊出現的字符c2TRIM(c1,c2) 去掉字符去掉字符c1左右兩邊的字符左右兩邊的字符c2LPAD(c1,n,c2) 字符字符c1按按指指定的位數定的位數n顯示顯示，不足的位數用，不足的位數用c2字符串替換左邊的空位字符串替換左邊的空位RPAD(c1,n,c2) 字符字符c1按按指指定的位數定的位數n顯示顯示，不足的位數用，不足的位數用c2字符串替換字符串替換右右邊的空位邊的空位提示：去掉字符串中間的空格可以使用提示：去掉字符串中間的