筆記oracle培訓(xùn)教程513章良知行

數(shù)據(jù)庫測試培訓(xùn)教程V1.0就業(yè)培訓(xùn)教材-OracleSQL學(xué)習(xí)目標(biāo)掌握標(biāo)準(zhǔn)SQL語句，特別是查詢語句的使用熟悉Oracle基本用戶和安全管理了解表、索引、序列的管理熟悉PL/SQL工具的使用課程內(nèi)容第一章SQL編寫規(guī)范第二章SQL語言基礎(chǔ)第三章限定和排序數(shù)據(jù)第四章單組函數(shù)第五章從多個(gè)表顯示數(shù)據(jù)第六章使用組函數(shù)第七章子查詢第八章操作數(shù)據(jù)第九章管理表第十章約束第十一章視圖第十二章其他數(shù)據(jù)庫對象第十三章用戶及權(quán)限第五章從多個(gè)表顯示數(shù)據(jù)使用相等和不等連結(jié)寫SELECT語句來訪問多個(gè)表使用外連接查詢數(shù)據(jù)表的自連接從多個(gè)表中獲得數(shù)據(jù)d

EMPNO DEPTNO LOC--------------------7839 10NEWYORK7698 30CHICAGO7782 10NEWYORK7566 20DALLAS7654 30CHICAGO7499 30CHICAGO...14rowsselected.EMPDEPTEMPNO ENAME ... DEPTNO

------ ----- ... ------

7839 KING ... 107698 BLAKE ... 30... 7934 MILLER ... 10DEPTNODNAME LOC---------------- --------10 ACCOUNTING NEWYORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON什么是連接使用連接從多個(gè)表中查詢數(shù)據(jù)在WHERE子句中寫連接條件在多個(gè)表中具有相同的列名SELECT table.column,table.columnFROM table1,table2WHERE table1.column1=table2.column2;笛卡爾結(jié)果笛卡爾結(jié)果形成于:連接條件被省略連接條件無效第一個(gè)表的所有記錄連接到第二個(gè)表的所有記錄為了避免笛卡爾結(jié)果我們總是在WHERE子句中使用有效連接生成笛卡爾結(jié)果ENAME DNAME------ ----------KING ACCOUNTINGBLAKE ACCOUNTING...KING RESEARCHBLAKE RESEARCH...56rowsselected.EMP(14rows)DEPT(4rows)EMPNO ENAME ... DEPTNO

------ ----- ... ------

7839 KING ... 107698 BLAKE ... 30... 7934 MILLER ... 10DEPTNODNAME LOC---------------- --------10 ACCOUNTING NEWYORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON笛卡爾結(jié)果:

14*4=56條記錄連接的類型等值連接不等連接外連接自連接什么是等值連接

EMPDEPTEMPNOENAMEDEPTNO--------------------7839KING107698BLAKE307782CLARK107566JONES207654MARTIN307499ALLEN307844TURNER307900JAMES307521WARD307902FORD207369SMITH20...14rowsselected.DEPTNODNAMELOC-------------------------10ACCOUNTINGNEWYORK30SALES CHICAGO10ACCOUNTING NEWYORK20RESEARCH DALLAS30SALES CHICAGO30SALES CHICAGO30SALES CHICAGO30SALES CHICAGO30SALES CHICAGO20RESEARCH DALLAS20RESEARCH DALLAS...14rowsselected.外鍵主鍵使用等值連接所獲得的記錄

SQL>SELECT emp.empno,emp.ename,emp.deptno,

2 dept.deptno,dept.loc3FROM emp,dept4WHERE emp.deptno=dept.deptno;EMPNOENAME DEPTNODEPTNOLOC--------------------------------7839KING 1010NEWYORK7698BLAKE 3030CHICAGO7782CLARK 1010NEWYORK7566JONES 2020DALLAS...14rowsselected.限定列名使用表名作為前綴在多個(gè)表中指定列名使用表前綴可以改進(jìn)性能使用列的別名以區(qū)分不同表的同名列使用AND運(yùn)算符附加查詢條件顯示KING的工號(hào)、姓名、部門號(hào)、部門地址EMPDEPTEMPNOENAMEDEPTNO--------------------7839KING107698BLAKE307782CLARK107566JONES207654MARTIN307499ALLEN307844TURNER307900JAMES307521WARD307902FORD207369SMITH20...14rowsselected.DEPTNODNAME LOC--------------- --------10ACCOUNTING NEWYORK30 SALES CHICAGO10ACCOUNTING NEWYORK20RESEARCH DALLAS30SALES CHICAGO30SALES CHICAGO30SALES CHICAGO30SALES CHICAGO30SALES CHICAGO20RESEARCH DALLAS20RESEARCH DALLAS...14rowsselected.使用表的別名使用別名簡化查詢SQL>SELECTemp.empno,emp.ename,emp.deptno,2 dept.deptno,dept.loc3FROMemp,dept4WHEREemp.deptno=dept.deptno;SQL>SELECTe.empno,e.ename,e.deptno,2d.deptno,d.loc3FROMempe,deptd4WHEREe.deptno=d.deptno;多表聯(lián)接NAME CUSTID----------- ------JOCKSPORTS 100TKBSPORTSHOP 101VOLLYRITE 102JUSTTENNIS 103K+TSPORTS 105SHAPEUP 106WOMENSSPORTS107... ...9rowsselected.CUSTOMERCUSTIDORDID--------------101610102611104612106601102602106604106605...21rowsselected.ORDORDIDITEMID-------------61036111612160116021...64rowsselected.ITEM非等值連接EMPSALGRADEEMP表中的Salary列值在SALGRADE表的低salary和高salary之間EMPNOENAMESAL-------------------7839KING50007698BLAKE28507782CLARK24507566JONES29757654MARTIN12507499ALLEN16007844TURNER15007900JAMES950...14rowsselected.GRADE LOSALHISAL----------------1700 120021201 140031401 20004 2001 300053001 9999使用非等值連接獲取記錄ENAMESALGRADE----------------------------JAMES9501SMITH8001ADAMS11001...14rowsselected.SQL> SELECT e.ename,e.sal,s.grade2 FROM empe,salgrades3 WHERE e.sal4 BETWEEN s.losalANDs.hisal;外連接

EMPDEPT在OPERATIONS部門沒有員工ENAME DEPTNO

----- ------

KING 10BLAKE 30CLARK 10JONES 20... DEPTNODNAME----------------10 ACCOUNTING30 SALES10 ACCOUNTING20 RESEARCH... 40 OPERATIONS外連接使用外連接看到不滿足連接條件的記錄外連接運(yùn)算符是加號(hào)(+)外連接分為左連接、右連接SELECT table.column,table.columnFROM table1,table2WHERE table1.column(+)=table2.column;SELECT table.column,table.columnFROM table1,table2WHERE table1.column=table2.column(+);使用外連接SQL>SELECT e.ename,d.deptno,d.dname2FROM empe,deptd3WHERE e.deptno(+)=d.deptno4ORDERBY e.deptno;ENAMEDEPTNODNAME--------------------------------KING10ACCOUNTINGCLARK10ACCOUNTING...40OPERATIONS15rowsselected.自連接EMP(WORKER)EMP(MANAGER)在WORKER表中的MGR等于MANAGER表中的EMPNOEMPNO ENAME MGR

----- ------ ----

7839 KING 7698 BLAKE 78397782 CLARK 78397566 JONES 78397654 MARTIN 76987499 ALLEN 7698EMPNO ENAME

----- --------

7839 KING7839 KING7839 KING7698 BLAKE7698 BLAKE表的自連接WORKER.ENAME||'WORKSFOR'||MANAG-------------------------------BLAKEworksforKINGCLARKworksforKINGJONESworksforKINGMARTINworksforBLAKE...13rowsselected.SELECTworker.ename,worker.mgr,'worksfor‘,manager.empno,manager.enameFROM empworker,empmanagerWHERE worker.mgr=manager.empno;OUTERJOIN句法ISO99標(biāo)準(zhǔn)把復(fù)雜的加號(hào)從Oracleouterjoin中拿出去，并使得outerjoinSQL更容易理解。LEFTOUTERJOINRIGHTOUTERJOINLEFTOUTERJOIN在LEFTOUTERJOIN中，會(huì)返回所有左邊表中的行，甚至在被連接的表中沒有可對應(yīng)的列名的情況下也如此。 Oracle8i

selectename,Dept.deptno

fromempe,deptd

wheree.deptno=d.deptno(+);

Oracle9i

selectename,Dept.deptnofromempLEFTOUTERJOINDeptONemp.deptno=Dept.deptno;

返回左表中的所有行，即使左表中有不符合條件的記錄，也會(huì)在查詢結(jié)果中顯示。RIGHTOUTERJOIN在RIGHTOUTERJOIN中返回的是表中所有右邊的行，甚至在被連接的表中沒有對應(yīng)的情況下也如此。 Oracle8i

selectlast_name,d.dept_id

fromemployeese,departmentsd

wheree.department_id(+)=d.department_id;

Oracle9i

selectlast_name,d.dept_id

fromemployeese

RIGHTOUTERJOINdepartmentsd

ONe.department_id=d.department_id;

返回右表中的所有行，即使右表中有不符合條件的記錄，也會(huì)在查詢結(jié)果中顯示。第五章小結(jié)等值連接不等連接外連接自連接SELECT table.column,table.columnFROM table1,table2WHERE table1.column1=table2.column2;第五章練習(xí)查詢EMP、DEPT表，輸出的列包含員工姓名、工資、部門編號(hào)、部門名稱、部門地址.使用自連接查詢EMP表，輸出的列包含員工姓名、主管姓名.在第2題的基礎(chǔ)上，思考下為什么輸出結(jié)果沒有KING的信息？如果要輸出KING的信息，如何修改？課程內(nèi)容第一章SQL編寫規(guī)范第二章SQL語言基礎(chǔ)第三章限定和排序數(shù)據(jù)第四章單組函數(shù)第五章從多個(gè)表顯示數(shù)據(jù)第六章使用組函數(shù)第七章子查詢第八章操作數(shù)據(jù)第九章管理表第十章約束第十一章視圖第十二章其他數(shù)據(jù)庫對象第十三章用戶及權(quán)限第六章使用組函數(shù)熟悉組函數(shù)的用法使用GROUPBY子句將數(shù)據(jù)分組使用HAVING子句包括或排除被分組的記錄什么是分組函數(shù)分組函數(shù)運(yùn)算每一組記錄,每一組返回一個(gè)結(jié)果EMPEMP表中工資的最高值DEPTNOSAL------------------1024501050001013002080020110020300020300020297530160030285030125030950301500301250MAX(SAL)---------5000分組函數(shù)的類型AVG([DISTINCT|ALL]n)求平均數(shù)SUM([DISTINCT|ALL]n)求和COUNT({*|[DISTINCT|ALL]expr})計(jì)數(shù)MAX([DISTINCT|ALL]expr)求最大值MIN([DISTINCT|ALL]expr)最小值STDDEV([DISTINCT|ALL]x)VARIANCE([DISTINCT|ALL]x)使用AVG和SUM函數(shù)在數(shù)字類型數(shù)據(jù)使用AVG和SUM函數(shù)AVG(SAL)MAX(SAL)MIN(SAL)SUM(SAL)-----------------------------------1400160012505600SQL>SELECT AVG(sal),MAX(sal),2 MIN(sal),SUM(sal)3 FROM emp4 WHERE jobLIKE'SALES%';使用MIN和MAX函數(shù)MIN和MAX函數(shù)適用于任何數(shù)據(jù)類型

SQL>SELECT MIN(hiredate),MAX(hiredate)2FROM emp;MIN(HIREDMAX(HIRED------------------17-DEC-8012-JAN-83使用COUNT函數(shù)COUNT(*)返回表中的記錄數(shù)

COUNT(*)---------6SQL>SELECT COUNT(*)2FROM emp3WHERE deptno=30;使用COUNT函數(shù)COUNT(expr)返回非空記錄SQL>SELECT COUNT(comm)2FROM emp3WHERE deptno=30;COUNT(COMM)-----------4分組函數(shù)和空值分組函省略列中的空值SQL>SELECTAVG(comm)2FROMemp;AVG(COMM)---------550在分組函數(shù)中使用NVL函數(shù)NVL函數(shù)強(qiáng)制分組函數(shù)包括空值SQL>SELECTAVG(NVL(comm,0))2FROMemp;AVG(NVL(COMM,0))----------------157.14286產(chǎn)生數(shù)據(jù)組EMPEMP表中每一個(gè)部門的平均工資2916.666721751566.6667DEPTNOAVG(SAL)----------------102916.6667202175301566.6667DEPTNOSAL------------------1024501050001013002080020110020300020300020297530160030285030125030950301500301250GROUPBY子句使用GROUPBY子句將表分成小組組函數(shù)忽略空值,可以使用NVL,NVL2,COALESCE等函數(shù)處理空值SELECT column,group_functionFROM table[WHERE condition][GROUPBY group_by_expression][ORDERBY column];使用GROUPBY子句所有用來分組的列在SELECT列表中不能使用分組函數(shù)SQL>SELECTdeptno,AVG(sal)2FROMemp3GROUPBYdeptno;DEPTNOAVG(SAL)------------------102916.6667202175301566.6667使用GROUPBY子句GROUPBY列不在SELECT列表中(如果把分組列放到字段列表中可讀性更強(qiáng))SQL>SELECTAVG(sal)2FROMemp3GROUPBYdeptno;AVG(SAL)---------2916.666721751566.6667多列分組EMP按部門分組統(tǒng)計(jì)EMP表中各職位的工資總和DEPTNOJOBSAL---------------------------10MANAGER245010PRESIDENT500010CLERK130020CLERK80020CLERK110020ANALYST300020ANALYST300020MANAGER297530SALESMAN160030MANAGER285030SALESMAN125030CLERK95030SALESMAN150030SALESMAN1250JOBSUM(SAL)------------------CLERK1300MANAGER2450PRESIDENT5000ANALYST6000CLERK1900MANAGER2975CLERK950MANAGER2850SALESMAN5600DEPTNO--------101010202020303030在多列上使用GROUPBY子句SQL>SELECTdeptno,job,sum(sal)2FROMemp3GROUPBYdeptno,job;DEPTNOJOBSUM(SAL)---------------------------10CLERK130010MANAGER245010PRESIDENT500020ANALYST600020CLERK1900...9rowsselected.使用分組函數(shù)的非法查詢?nèi)绻麤]有GROUPBY子句SELECT列表中的任何列或表達(dá)式不能使用合計(jì)函數(shù)SQL>SELECT deptno,COUNT(ename)2FROM emp;SELECTdeptno,COUNT(ename)*ERRORatline1:ORA-00937:notasingle-groupgroupfunction在GROUPBY子句中缺少分組的列這個(gè)表述不準(zhǔn)確使用分組函數(shù)時(shí)的非法查詢不能使用WHERE子句限定組可使用HAVING子句限定組SQL>SELECT deptno,AVG(sal)2FROM emp3WHERE AVG(sal)>20004GROUPBY deptno;WHEREAVG(sal)>2000*ERRORatline3:ORA-00934:groupfunctionisnotallowedhere不能使用WHERE子句限定分組結(jié)果限定分組的結(jié)果d查詢工資大于$2900的部門的最高工資EMP500030002850DEPTNOSAL------------------1024501050001013002080020110020300020300020297530160030285030125030950301500301250DEPTNOMAX(SAL)------------------105000203000HAVING子句Having子句的作用是對行分組進(jìn)行過濾記錄被分組使用組函數(shù)匹配HAVING子句的組被顯示SELECT column,group_functionFROM table[WHERE condition][GROUPBY group_by_expression][HAVING group_condition][ORDERBY column];使用HAVING子句SQL>SELECTdeptno,max(sal)2FROMemp3GROUPBYdeptno4HAVINGmax(sal)>2900;DEPTNOMAX(SAL)------------------105000203000使用HAVING子句SQL>SELECTjob,SUM(sal)PAYROLL2FROMemp3WHERE jobNOTLIKE'SALES%'3GROUPBYjob4HAVINGSUM(sal)>50005ORDERBYSUM(sal);JOBPAYROLL------------------ANALYST6000MANAGER8275嵌套組函數(shù)顯示最高的平均工資SQL>SELECTmax(avg(sal))2FROMemp3GROUPBYdeptno;MAX(AVG(SAL))-------------2916.6667第六章小結(jié)SELECT column,group_functionFROM table[WHERE condition][GROUPBY group_by_expression][HAVING group_condition][ORDERBY column];第六章練習(xí)題查詢EMP表，輸出每個(gè)部門的平均工資，并按部門編號(hào)降序排列.查詢EMP表，輸出每個(gè)職位的平均工資，按平均工資升序排列.查詢EMP表，輸出每個(gè)部門的各個(gè)職位的平均工資，并按部門編號(hào)升序、平均工資降序排序。課程內(nèi)容第一章SQL編寫規(guī)范第二章SQL語言基礎(chǔ)第三章限定和排序數(shù)據(jù)第四章單組函數(shù)第五章從多個(gè)表顯示數(shù)據(jù)第六章使用組函數(shù)第七章子查詢第八章操作數(shù)據(jù)第九章管理表第十章約束第十一章視圖第十二章其他數(shù)據(jù)庫對象第十三章用戶及權(quán)限第七章子查詢定義子查詢列出子查詢的種類編寫單行和多行子查詢使用子查詢解決問題找出工資高于Jones的雇員哪些雇員的工資高于Jones主查詢?Jones的工資是多少??子查詢子查詢子查詢在主查詢前執(zhí)行一次主查詢使用子查詢的結(jié)果SELECT select_listFROM tableWHERE exproperator (SELECT select_list FROM table);使用子查詢2975SQL>SELECTename2FROMemp3WHEREsal>4 (SELECTsal5FROMemp6WHEREempno=7566);ENAME----------KINGFORDSCOTT使用子查詢的規(guī)則子查詢要用括號(hào)括起來將子查詢放在比較運(yùn)算符的右邊子查詢中不要加ORDERBY子句對單行子查詢使用單行運(yùn)算符對多行子查詢使用多行運(yùn)算符子查詢的種類單行子查詢多行子查詢多列子查詢主查詢子查詢

返回CLERKCLERKMANAGER主查詢子查詢

返回CLERK7900

MANAGER7698主查詢子查詢

返回單行子查詢返回一行記錄使用單行記錄比較運(yùn)算符操作符=>>= <<= <>含義相等大于大于或等于小于小于或等于不等于執(zhí)行單行子查詢

CLERK1100ENAMEJOB-------------------MILLERCLERKSQL>SELECTename,job

2FROMemp3WHEREjob=4 (SELECT job

5 FROM emp

6 WHERE empno=7369)7ANDsal>8 (SELECT sal9 FROM emp10 WHERE empno=7876);在子查詢中使用分組函數(shù)800ENAMEJOBSAL----------------------------SMITHCLERK800SQL>SELECT ename,job,sal2FROM emp3WHERE sal=4 (SELECT MIN(sal)5 FROM emp);在子查詢中使用HAVING子句先執(zhí)行子查詢?nèi)缓蠓祷亟Y(jié)果到主查詢的HAVING子句800SQL>SELECT deptno,MIN(sal)2FROM emp3GROUPBY deptno4HAVING MIN(sal)>5 (SELECT MIN(sal)6 FROM emp7 WHERE deptno=20);錯(cuò)誤的子查詢ERROR:ORA-01427:single-rowsubqueryreturnsmorethan

onerownorowsselectedSQL>SELECTempno,ename2FROMemp3WHEREsal=4 (SELECTMIN(sal)5 FROMemp6 GROUPBYdeptno);錯(cuò)誤：在多行子查詢中使用單行運(yùn)算符這樣的子查詢有效嗎norowsselectedSQL>SELECTename,job2FROMemp3WHEREjob=4 (SELECT job5 FROM emp6 WHERE ename='SMYTHE');子查詢沒有返回結(jié)果值多行子查詢返回多行使用多行比較運(yùn)算符運(yùn)算符INANY

ALL含義等于列表中的任何值比較子查詢返回的任何值比較子查詢返回的每一個(gè)值在多行子查詢中使用ANY運(yùn)算符<ANY指小于最大值>ANY指大于最小值95080011001300EMPNOENAMEJOBSAL---------------------------------7521WARDSALESMAN12507654MARTINSALESMAN1250SQL>SELECTempno,ename,job2FROMemp3WHEREsal<ANY4 (SELECT sal5 FROM emp6 WHERE job='CLERK')7AND job<>'CLERK';在多行子查詢中使用ALL運(yùn)算符>ALL指大于最大值<ALL指小于最小值2916.666721751566.6667EMPNOENAMEJOBSAL-----------------------------------7566JONESMANAGER29757788SCOTTANALYST30007839KINGPRESIDENT50007902FORDANALYST3000SQL>SELECTempno,ename,job,sal2FROMemp3WHEREsal>ALL4 (SELECT avg(sal)5 FROM emp6 GROUPBY deptno)第七章小結(jié)當(dāng)查詢條件是基于不知道的值時(shí),子查詢是非常有用的SELECT select_listFROM tableWHERE exproperator (SELECTselect_list FROM table);第七章練習(xí)題使用子查詢，找出哪個(gè)部門下沒有員工使用子查詢，找出那些工資低于所有部門的平均工資的員工使用子查詢，找出那些工資低于任意部門的平均工資的員工，比較一下與第2題輸出的結(jié)果是否相同？課程內(nèi)容第一章SQL編寫規(guī)范第二章SQL語言基礎(chǔ)第三章限定和排序數(shù)據(jù)第四章單組函數(shù)第五章從多個(gè)表顯示數(shù)據(jù)第六章使用組函數(shù)第七章子查詢第八章操作數(shù)據(jù)(事務(wù))第九章管理表第十章約束第十一章視圖第十二章其他數(shù)據(jù)庫對象第十三章用戶及權(quán)限第八章操作數(shù)據(jù)描述DML語句使用INSERT,UPDATE和DELETE命令操作數(shù)據(jù)控制事務(wù)數(shù)據(jù)操作語言使用DML語句可執(zhí)行:插入新數(shù)據(jù)修改已有數(shù)據(jù)刪除數(shù)據(jù)一個(gè)事務(wù)是DML語句的邏輯工作單元INSERT語句INSERT語句一次將一個(gè)記錄的數(shù)據(jù)輸入表中例子：

INSERTINTO table[(column[,column...])]VALUES (value[,value...]);SQL>INSERTINTO dept(deptno,dname,loc)2VALUES (50,'DEVELOPMENT','DETROIT');1rowcreated.插入空值例子1:例子2:SQL>INSERTINTO dept(deptno,dname)2VALUES (60,'MIS');1rowcreated.SQL>INSERTINTO dept2VALUES (70,'FINANCE',NULL);1rowcreated.INSERT語句中使用函數(shù)例如使用SYSDATE函數(shù)插入當(dāng)前的日期和時(shí)間增加一個(gè)員工SQL>INSERTINTO emp(empno,ename,job,2 mgr,hiredate,sal,comm,3 deptno)4VALUES (7196,'GREEN','SALESMAN',5 7782,SYSDATE,2000,NULL,6 10);1rowcreated.SQL>INSERTINTOemp2VALUES(2296,'AROMANO','SALESMAN',7782,3 TO_DATE('FEB3,97','MONDD,YY'),4 1300,NULL,10);1rowcreated.從其他表中拷貝記錄SQL>INSERTINTOmanagers(id,name,salary,hiredate)2 SELECT empno,ename,sal,hiredate3 FROMemp4 WHERE job='MANAGER';3rowscreated.UPDATE語句使用UPDATE語句修改已存在的記錄UPDATE tableSET column=value[,column=value][WHERE condition];修改記錄使用WHERE子句修改指定的記錄.修改所有的記錄.SQL>UPDATE emp2SET deptno=203WHERE empno=7782;1rowupdated.SQL>UPDATE employee2SET deptno=20;14rowsupdated.使用多列子查詢修改記錄SQL>UPDATEemp2SET(job,deptno)=3 (SELECTjob,deptno4FROMemp5WHEREempno=7499)6WHEREempno=7698;1rowupdated.修改記錄時(shí)違反完整性約束UPDATEemp*ERRORatline1:ORA-02291:integrityconstraint(USR.EMP_DEPTNO_FK)violated-parentkeynotfoundSQL>UPDATE emp2SET deptno=553WHERE deptno=10;部門編號(hào)為55的部門不存在DELETE語句使用DELETE命令從表中刪除記錄.DELETE[FROM] table[WHERE condition];從表中刪除記錄使用WHERE子句刪除指定的記錄刪除所有的記錄SQL>DELETEFROM dept2WHERE dname='DEVELOPMENT';1rowdeleted.SQL>DELETEFROM dept;4rowsdeleted.刪除的記錄基于其他表的記錄使用子查詢，刪除的記錄基于其他的表.SQL>DELETEFROM emp2WHERE deptno=3 (SELECTdeptno4 FROMdept5 WHEREdname='SALES');6rowsdeleted.刪除記錄時(shí)違反完整性約束SQL>DELETEFROM dept2WHERE deptno=10;DELETEFROMdept*ERRORatline1:ORA-02292:integrityconstraint(USR.EMP_DEPTNO_FK)violated-childrecordfoundYoucannotdeletearowthatcontainsaprimarykeythatisusedasaforeignkeyinanothertable.數(shù)據(jù)庫的事務(wù)一組DML語句，修改的數(shù)據(jù)在他們中保持一致一個(gè)DDL語句一個(gè)DCL語句數(shù)據(jù)庫的事務(wù)開始于第一個(gè)執(zhí)行的語句結(jié)束于:COMMIT或ROLLBACKDDLorDCL(grant/revoke)語句某些錯(cuò)誤，退出，或系統(tǒng)崩潰事務(wù)的自動(dòng)處理當(dāng)下列情況發(fā)生時(shí)事務(wù)自動(dòng)提交:執(zhí)行一個(gè)DDL語句執(zhí)行一個(gè)DCL語句從SQL*Plus正常退出當(dāng)從SQL*PLUS中強(qiáng)行退出或系統(tǒng)失敗時(shí),事物自動(dòng)回滾COMMIT和ROLLBACK的優(yōu)點(diǎn)保證數(shù)據(jù)一致性在數(shù)據(jù)永久性生效前重新查看修改的數(shù)據(jù)相關(guān)邏輯操作單元控制事務(wù)DELETETransactionSavepointAROLLBACKtoSavepointBDELETESavepointBCOMMITINSERTUPDATEROLLBACKtoSavepointAINSERTUPDATEINSERTROLLBACKINSERT提交或回滾前數(shù)據(jù)的狀態(tài)以前的數(shù)據(jù)可恢復(fù).當(dāng)前的用戶可看到DML操作的結(jié)果.其他用戶不能看到DML操作的結(jié)果.被操作的數(shù)據(jù)被鎖住,其他用戶不能修改這些數(shù)據(jù).提交后數(shù)據(jù)的狀態(tài)數(shù)據(jù)的修改被永久寫在數(shù)據(jù)庫中.數(shù)據(jù)以前的狀態(tài)永久性丟失.所有的用戶都能看到操作后的結(jié)果.記錄鎖被釋放,其他用戶可操作這些記錄.所有的savepoints被去掉.提交數(shù)據(jù)修改數(shù)據(jù)提交數(shù)據(jù)SQL>UPDATE emp2SET deptno=103WHERE empno=7782;1rowupdated.SQL>COMMIT;Commitcomplete.回滾后數(shù)據(jù)的狀態(tài)語句將使所有的修改失效.修改的數(shù)據(jù)被回退.恢復(fù)數(shù)據(jù)以前的狀態(tài).行級(jí)鎖被釋放.SQL>DELETEFROM employee;14rowsdeleted.SQL>ROLLBACK;Rollbackcomplete.回退到某一標(biāo)識(shí)使用語句產(chǎn)生一個(gè)標(biāo)識(shí),將事務(wù)分成幾個(gè)階段.可回退到標(biāo)識(shí)指定的階段.SQL>UPDATE...SQL>SAVEPOINTupdate_done;Savepointcreated.SQL>INSERT...SQL>ROLLBACKTOupdate_done;Rollbackcomplete.語句回滾如果一條DML語句執(zhí)行時(shí)失敗,只有此語句回退.Oracle執(zhí)行了一個(gè)自動(dòng)的savepoint.其他的變化被保留.客戶應(yīng)該執(zhí)行COMMIT或ROLLBACK以結(jié)束事務(wù).事務(wù)特性事務(wù)的ACID特性1、原子性(atomicity):一個(gè)事務(wù)中包含的所有sql語句都是一個(gè)不可分割的單元。2、一致性（consistency）事務(wù)必須確保數(shù)據(jù)庫的狀態(tài)是一致的。3、隔離性(isolation)多個(gè)事務(wù)獨(dú)立運(yùn)行，彼此不影響。4、持久性(durability)事務(wù)一旦提交，數(shù)據(jù)庫的變化就會(huì)被永久保留下來。讀一致性讀一致性保證了查詢數(shù)據(jù)得到一致的結(jié)果.不同用戶修改的數(shù)據(jù)不會(huì)發(fā)生沖突.對相同的數(shù)據(jù)操作時(shí)確保:查詢時(shí)不用等寫完成寫時(shí)不用等查詢完成讀一致性的實(shí)現(xiàn)updateemp

setsal=2000whereename='SCOTT'數(shù)據(jù)塊回滾段select*

fromemp變化前和變化后的數(shù)據(jù)數(shù)據(jù)變化前的鏡像UserAUserBRead

consistent

image第八章小結(jié)說明AddsanewrowtothetableModifiesexistingrowsinthetableRemovesexistingrowsfromthetableMakesallpendingchangespermanentAllowsarollbacktothesavepointmarkerDiscardsallpendingdatachanges語句INSERTUPDATEDELETE COMMITSAVEPOINTROLLBACK第八章練習(xí)題在EMP表中，增加一名員工，員工信息參照現(xiàn)有員工構(gòu)造.員工SMITH部門調(diào)動(dòng)到SALES部門，請編寫SQL語句更新員工信息.員工JAMES已經(jīng)離職，請編寫SQL語句更新數(shù)據(jù)庫.用戶執(zhí)行deletefromemp;語句刪除了EMP表的記錄，但沒有提交，請問有辦法恢復(fù)EMP原來的數(shù)據(jù)嗎？課程內(nèi)容第一章SQL編寫規(guī)范第二章SQL語言基礎(chǔ)第三章限定和排序數(shù)據(jù)第四章單組函數(shù)第五章從多個(gè)表顯示數(shù)據(jù)第六章使用組函數(shù)第七章子查詢第八章操作數(shù)據(jù)第九章管理表第十章約束第十一章視圖第十二章其他數(shù)據(jù)庫對象第十三章用戶及權(quán)限第九章管理表了解數(shù)據(jù)庫的對象創(chuàng)建表、改變表的定義，表中列可以使用的數(shù)據(jù)類型刪除表、改表名、截取表的所有記錄數(shù)據(jù)庫的對象數(shù)據(jù)庫對象 說明Table 基本存儲(chǔ)單元；由行和列組成View Logicallyrepresentssubsetsofdatafrom

oneormoretablesSequence 生成主鍵值Index 改進(jìn)查詢的性能Synonym 為對象建立一個(gè)同名對象命名規(guī)則必須以字母開頭可包括數(shù)字只能包含A-Z,a-z,0-9,_,$,and#不要使用oracle的保留字同一用戶的對象不能同名創(chuàng)建表你必須有:建表的權(quán)限有存儲(chǔ)區(qū)域你可指定:表名Tablename列名,列的數(shù)據(jù)類型,列的大小CREATETABLE[schema.]table (columndatatype[DEFAULTexpr];

SQL>SELECT *2 FROM scott.emp;引用其他用戶的表其他用戶的表不能直接訪問使用其他用戶的表時(shí)需加用戶名作為前綴缺省選項(xiàng)在插入記錄時(shí)為列指定缺省值合法的值是字符,表達(dá)式.或SQL函數(shù)非法值為其他的列名.缺省的數(shù)據(jù)類型必須匹配列的數(shù)據(jù)類型.?hiredateDATEDEFAULTSYSDATE,建表確認(rèn)創(chuàng)建的表(sql不識(shí)別)

SQL>CREATETABLEdept

2 (deptno NUMBER(2),3 dname VARCHAR2(14),4 loc VARCHAR2(13));Tablecreated.

SQL>DESCRIBEdeptNameNull?Type--------------------------------------------DEPTNONOTNULLNUMBER(2)DNAMEVARCHAR2(14)LOCVARCHAR2(13)查詢數(shù)據(jù)字典查詢此用戶所擁有哪些表查詢此用戶擁有哪些類型的對象查詢此用戶擁有的表,視圖,同義詞,序列號(hào)

SQL>SELECT *2 FROM user_tables;SQL>SELECT DISTINCTobject_type2 FROM user_objects;SQL>SELECT *2 FROM user_catalog;數(shù)據(jù)類型參考文檔：Oracle數(shù)據(jù)庫常用數(shù)據(jù)類型.doc數(shù)據(jù)類型 說明VARCHAR2(size) 可變長字符串CHAR(size) 定長字符串NUMBER(p,s) 數(shù)字值DATE 日期值LONG 變長度的字符串，最大字節(jié)數(shù)可達(dá)2GBCLOB 單位字符串?dāng)?shù)據(jù)最大字節(jié)數(shù)4GRAWandLONGRAW 存儲(chǔ)二進(jìn)制數(shù)據(jù)的變長度字符串BLOB 二進(jìn)制數(shù)據(jù)，最大字節(jié)數(shù)4GBFILE 二進(jìn)制數(shù)據(jù)外部存儲(chǔ)，最大字節(jié)數(shù)4G使用子查詢創(chuàng)建表使用子查詢創(chuàng)建表列的數(shù)目要和子查詢中的匹配可定義列名和缺省值CREATETABLEtable [column(,column...)]ASsubquery;使用CTAS創(chuàng)建表SQL>CREATETABLE dept302 AS3 SELECTempno,ename,sal*12ANNSAL,hiredate4 FROM emp5 WHERE deptno=30;Tablecreated.NameNull?Type-----------------------------------------EMPNONOTNULLNUMBER(4)ENAMEVARCHAR2(10)ANNSALNUMBERHIREDATEDATESQL>DESCRIBEdept30ALTERTABLE語句使用ALTERTABLE語句可:加一列修改列對新列定義缺省值A(chǔ)LTERTABLEtableADD (columndatatype[DEFAULTexpr] [,columndatatype]...);ALTERTABLEtableMODIFY (columndatatype[DEFAULTexpr] [,columndatatype]...);增加列使用ADD子句增加列.SQL>ALTERTABLEdept302ADD (jobVARCHAR2(9));Tablealtered.EMPNOENAMEANNSALHIREDATEJOB-----------------------------------------7698BLAKE3420001-MAY-817654MARTIN1500028-SEP-817499ALLEN1920020-FEB-817844TURNER1800008-SEP-81...6rowsselected.修改列可修改列的數(shù)據(jù)類型,大小和缺省值.修改后的缺省值只影響以后插入的數(shù)據(jù).ALTERTABLE dept30MODIFY (enameVARCHAR2(15));Tablealtered.修改列名在Oracle9i及后續(xù)版本，可以直接修改列名.SQL>desctest名稱是否為空?類型-----------------------------------------------------------------------------NO NUMBER(5)NAME VARCHAR2(10)SQL>altertabletestrenamecolumnnametodname;表已更改。SQL>desctest名稱是否為空?類型-----------------------------------------------------------------------------NO NUMBER(5)DNAME VARCHAR2(10)刪除表表中所有數(shù)據(jù)將被刪除事務(wù)被提交所有索引被刪除不能回退SQL>DROPTABLEdept30;Tabledropped.改變對象名稱使用RENAME語句改變對象名稱你必須是對象的所有者SQL>RENAMEdeptTOdepartment;Tablerenamed.截取表的所有記錄TRUNCATETABLE語句:刪除表中所有記錄釋放表的存儲(chǔ)空間不能回退和DELETE一樣，是刪除記錄的手段之一SQL>TRUNCATETABLEdepartment;Tabletruncated.在表中加注釋使用COMMENT語句可向表或表中的列加注釋可通過如下數(shù)據(jù)字典視圖看注釋：ALL_COL_COMMENTSUSER_COL_COMMENTSALL_TAB_COMMENTSUSER_TAB_COMMENTSSQL>COMMENTONTABLEemp2IS'EmployeeInformation';Commentcreated.第九章小結(jié)語句 說明CREATETABLE 創(chuàng)建表ALTERTABLE 修改表結(jié)構(gòu)DROPTABLE 刪除記錄和表的結(jié)構(gòu)RENAME 改對象的名字，如表、視圖、序列或同義詞TRUNCATE 刪除表中的所有行，并釋放掉存儲(chǔ)空間COMMENT 為表或視圖增加注釋第九章練習(xí)題假設(shè)order_status2表結(jié)構(gòu)如下：Name Type Nullable Default Comments------------------------------- ------------------------- ------------ ------------ --------ID INTEGERSTATUS CHAR(10) YLAST_MODIFIED DATE Y SYSDATEMODIFIED_BY INTEGER YINITIALLY_CREATED DATE sysdateTEST VARCHAR2(15) YTESTB VARCHAR2(10) Y 'testb'按表結(jié)構(gòu)信息在數(shù)據(jù)中創(chuàng)建order_status2表修改字段test,使其不允許為空且給它賦予一個(gè)缺省的值testing;課程內(nèi)容第一章SQL編寫規(guī)范第二章SQL語言基礎(chǔ)第三章限定和排序數(shù)據(jù)第四章單組函數(shù)第五章從多個(gè)表顯示數(shù)據(jù)第六章使用組函數(shù)第七章子查詢第八章操作數(shù)據(jù)第九章管理表第十章約束第十一章視圖第十二章其他數(shù)據(jù)庫對象第十三章用戶及權(quán)限第十章約束了解什么是約束創(chuàng)建約束和維護(hù)約束什么是約束約束是表級(jí)強(qiáng)制執(zhí)行的規(guī)則.當(dāng)表中數(shù)據(jù)有相互依賴性時(shí),可保護(hù)數(shù)據(jù)不被刪除.Oracle有如下類型的約束:NOTNULLUNIQUEKeyPRIMARYKEYFOREIGNKEYCHECK約束概況Oracle使用SYS_Cn格式命名約束.創(chuàng)建約束:在建表的同時(shí)創(chuàng)建建表后創(chuàng)建可定義列級(jí)或表級(jí)約束.可通過數(shù)據(jù)字典表查看約束.創(chuàng)建約束CREATETABLE[schema.]table (columndatatype[DEFAULTexpr] [column_constraint],

[table_constraint]);CREATETABLEemp (empnoNUMBER(4), enameVARCHAR2(10),

deptnoNUMBER(7,2)NOTNULL, CONSTRAINTemp_empno_pk PRIMARYKEY(EMPNO)); 創(chuàng)建約束列級(jí)約束

表級(jí)約束column[CONSTRAINTconstraint_name]constraint_type,column,...[CONSTRAINTconstraint_name]constraint_type(column,...),非空約束（NOTNULL）確保列值非空

EMPEMPNO ENAME JOB ...COMMDEPTNO7839 KING PRESIDENT 107698 BLAKE MANAGER 307782 CLARK MANAGER 107566 JONES MANAGER 20...非空約束(這一列不能存在空值)不存在非空約束(這一列可以為空)非空約束非空約束（NOTNULL）定義列級(jí)約束SQL>CREATETABLEemp(2 empno NUMBER(4),3 ename VARCHAR2(10)NOTNULL,4 job VARCHAR2(9),5 mgr NUMBER(4),6 hiredate DATE,7 sal NUMBER(7,2),8 comm NUMBER(7,2),9 deptno NUMBER(7,2)NOTNULL);唯一性約束（UNIQUE）

DEPTDEPTNODNAME LOC---------------- --------10 ACCOUNTING NEWYORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON唯一索引字段

50 SALES DETROIT

60 BOSTON插入數(shù)據(jù)不允許插入(DNAM