O數(shù)據(jù)庫(kù)命令操作

（O管理）O數(shù)據(jù)庫(kù)命令操作Oracle數(shù)據(jù)庫(kù)命令操作1，查看oracle版本：select*fromv$version2，重新進(jìn)行數(shù)據(jù)庫(kù)連接CONNECTSCOTT/TIGER@MYDBSCOTT登陸名TIGER口令MYDB稱為網(wǎng)絡(luò)服務(wù)名或稱為連接字符串3，關(guān)閉或退出SQL*PlusEXIT”或“QUIT”命令4，查看SQL*Plus的環(huán)境參數(shù)。通過SHOWALL命令可以查看設(shè)置命令的格式為：SET參數(shù)[ON|OFF|值]5，設(shè)置輸出頁(yè)面的大小。步驟1：輸入并執(zhí)行以下命令，觀察顯示結(jié)果：SELECT*FROMemp;步驟2：在輸入?yún)^(qū)輸入并執(zhí)行以下命令：SETPAGESIZE100SETLINESIZE120或SETPAGESIZE100LINESIZE120步驟3：重新輸入并執(zhí)行以下命令，觀察顯示結(jié)果：SELECT*FROMemp;6，顯示當(dāng)前用戶，輸入并執(zhí)行命令：SHOWUSER執(zhí)行結(jié)果是：USER為"SCOTT"7，使用SPOOL命令記錄操作內(nèi)容。步驟1：執(zhí)行命令：SPOOLC:\TEST步驟2：執(zhí)行命令：SELECT*FROMemp;步驟3：執(zhí)行命令：SELECT*FROMdept;步驟4：執(zhí)行命令：SPOOLOFF步驟5：用記事本打開C:\TEST.LST并查看內(nèi)容：8，打開/關(guān)閉查詢結(jié)果表頭的顯示，默認(rèn)為ON。Setheadingon/off：打開/關(guān)閉查詢結(jié)果表頭的顯示，默認(rèn)為ON。Setfeedbackon/off：打開/關(guān)閉查詢結(jié)果中返回行數(shù)的顯示，默認(rèn)為ON。Setechoon/off：打開/關(guān)閉命令的回顯，默認(rèn)為ON。Settimeon/off：打開/關(guān)閉時(shí)間顯示，默認(rèn)為OFF。9,使用DESCRIBE命令查看表結(jié)構(gòu)DESCRIBE（可簡(jiǎn)寫成DESC）來檢查表的結(jié)構(gòu)信息。輸入并執(zhí)行以下命令(emp為要顯示結(jié)構(gòu)的表名)：DESCRIBEemp10,注釋的方法是：*在一行的開頭處書寫REM，將一行注釋掉。*在一行中插入“--”，將其后的內(nèi)容注釋掉。*使用/**/，可以用來注釋任何一段內(nèi)容。11,保存輸入?yún)^(qū)的內(nèi)容。步驟1：在輸入?yún)^(qū)重新輸入命令：SELECT*FROMemp;SELECT*FROMdept;步驟2：選擇“文件”菜單下的“將輸入另存為”命令，彈出文件存盤對(duì)話框。選擇正確的磁盤位置，為存盤的文件起一個(gè)名字。如果輸入?yún)^(qū)中的內(nèi)容是SQL命令或命令序列，則使用擴(kuò)展名.SQL；否則可以省略或以.TXT做擴(kuò)展名。在本例中，選SELECT.SQL做文件名。12,創(chuàng)建新用戶USER1，口令為ABC123CREATEUSERUSER1IDENTIFIEDBYABC123（createuseruser1identifiedbyabc123）授予連接數(shù)據(jù)庫(kù)權(quán)限、創(chuàng)建表權(quán)限、創(chuàng)建存儲(chǔ)過程和表空間使用權(quán)限。GRANTCONNECTTOUSER1;(grantconnecttouser1)GRANTCREATETABLETOUSER1;(grantcreatetabletouser1)GRANTCREATEPROCEDURETOUSER1;(grantcreateproceduretouser1)GRANTUNLIMITEDTABLESAPCETOUSER1;(grantunlimitedtablesapcetouser1)使用新賬戶登錄。CONNECTUSER1/ABC123@MYDB輸出結(jié)果：已連接。步驟5：以創(chuàng)建表的方式復(fù)制數(shù)據(jù)到新賬戶。CREATETABLEEMPASSELECT*FROMSCOTT.EMP;(createtableempasselect*fromscott.emp)CREATETABLEDEPTASSELECT*FROMSCOTT.DEPT;CREATETABLESALGRADESASSELECT*FROMSCOTT.SALGRADE;13,每個(gè)賬戶對(duì)應(yīng)一個(gè)模式(賬戶.表)以不同的賬戶登錄數(shù)據(jù)庫(kù)時(shí)，就進(jìn)入了不同的模式SELECT*FROMSCOTT.EMP;14,顯示表的行號(hào)輸入并執(zhí)行查詢：SELECTrownum,enameFROMemp;15,查詢語句中的加減乘除運(yùn)算加（+-*/）顯示雇員工資上浮20%的結(jié)果。輸入并執(zhí)行查詢:SELECTename,sal,sal*(1+20/100)FROMemp;顯示結(jié)果為：ENAMESALSAL*(1+20/100)SMITH800960ALLEN1600192016,在查詢中使用列別名。輸入并執(zhí)行：SELECTenameAS"名稱",sal"工資"FROMemp;顯示結(jié)果為：名稱工資SMITH800ALLEN160017,在查詢中使用字符串常量。輸入并執(zhí)行查詢：SELECTename||'IS'||jobAS"雇員和職務(wù)表"FROMemp;輸出結(jié)果為：雇員和職務(wù)表SMITHISCLERKALLENISSALESMAN18，消除重復(fù)行顯示。使用DISTINCT輸入并執(zhí)行查詢：SELECTDISTINCTjobFROMemp;19，升序排序【訓(xùn)練1】查詢雇員姓名和工資，并按工資從小到大排序。輸入并執(zhí)行查詢：SELECTename,salFROMempORDERBYsal;執(zhí)行結(jié)果為：ENAMESALSMITH800JAMES950注意：若省略ASC和DESC，則默認(rèn)為ASC，即升序排序。降序排序?【訓(xùn)練2】查詢雇員姓名和雇傭日期，并按雇傭日期排序，后雇傭的先顯示。輸入并執(zhí)行查詢：SELECTename,hiredateFROMempORDERBYhiredateDESC;結(jié)果如下：ENAMEHIREDATEADAMS23-5月-87SCOTT19-4月-87多列排序SELECTename,deptno,hiredateFROMempORDERBYdeptno,hiredate;在排序中使用別名SELECTempno,ename,sal*Months_between(sysdate,hiredate)AStotalFROMempORDERBYtotal;20，條件查詢ALL同列表或查詢中的每一用的運(yùn)算符包括=>=、<=、>、<等mempWh2000)OT]EXISTS測(cè)試是否子查詢至少返回一行'存在雇員SCOTT'mdualwmempwSELECTename,job,salFROMempWHEREjob='SALESMAN';SELECTename,job,salFROMempWHEREsal>=3000;SELECTename,hiredateFROMempWHEREhiredate>='1-1月-82';SELECTename,job,salFROMempWHEREsal>1000ANDsal<2000;SELECT*FROMempWHEREjob='CLERK'ORjob='MANAGER';SELECT*FROMempWHERENOTdeptno=10;SELECT*FROMempWHERE(deptno=10ORdeptno=20)ANDsal<1500;顯示工資在1000～2000之間的雇員信息。輸入并執(zhí)行查詢：SELECT*FROMempWHEREsalBETWEEN1000AND2000;21，IN條件查詢使用顯示職務(wù)為“SALESMAN'“CLERK”和“MANAGER”的雇員信息。(in前可以加not)輸入并執(zhí)行查詢：SELECT*FROMempWHEREjobIN('SALESMAN','CLERK','MANAGER');22,LIKE模糊查詢顯示姓名以“S”開頭的雇員信息如下（%：代表0個(gè)或多個(gè)任意字符。_：代表一個(gè)輸入并執(zhí)行查詢：SELECT*FROMempWHEREenameLIKE'S%';顯示經(jīng)理編號(hào)沒有填寫的雇員（判斷空值NULL）(也可ISnotNULL)輸入并執(zhí)行查詢：SELECTename,mgrFROMempWHEREmgrISNULL;22,函數(shù)列表使用求絕對(duì)值函數(shù)abs。SELECTabs(-5)FROMdual;使用四舍五入函數(shù)round。SELECTround(45.923,2),round(45.923,0),round(45.923,-1)FROMdual;使用求余數(shù)函數(shù)mod。SELECTmod(1600,300)FROMdual;函數(shù)名稱功能實(shí)例結(jié)果獲得字符的ASCII碼A6523，字符函數(shù)返回與ASCII碼相應(yīng)的字符CA將字符串轉(zhuǎn)換成小寫LCupp將字符串轉(zhuǎn)換成大寫LCSQLCOURSE將字符串轉(zhuǎn)換成每個(gè)單詞以大寫開頭LuSqlC連接兩個(gè)字符串LSQLCb給出起始位置和長(zhǎng)度，返回子字符串b)求字符串的長(zhǎng)度7給出起始位置和出現(xiàn)的次數(shù)，求子字符串在字符串中出現(xiàn)的位置)3用字符填充字符串左側(cè)到指定長(zhǎng)度i用字符填充字符串右側(cè)到指定長(zhǎng)度H如果不知道表的字段內(nèi)容是大寫還是小寫，可以轉(zhuǎn)換后比較。在一個(gè)字符串中去除另一個(gè)字符串OMM輸入并執(zhí)行查詢：用一個(gè)字符串替換另一個(gè)字符串中的子BCADC字符串SELECTempno,ename,deptnoFROMempWHERElower(ename)='blake';顯示雇員名稱和職務(wù)列表。輸入并執(zhí)行查詢：SELECTconcat(rpad(ename,15,'.'),job)as職務(wù)列表FROMemp;結(jié)果為：職務(wù)列表SMITHCLERKALLENSALESMANWARDSALESMAN顯示名稱以“W”開頭的雇員，并將名稱轉(zhuǎn)換成以大寫開頭。輸入并執(zhí)行查詢：SELECTempno,initcap(ename),jobFROMempWHEREsubstr(ename,1,1)='W';結(jié)果為：EMPNOINITCAP(ENJOB7521WardSALESMAN顯示雇員名稱中包含“S”的雇員名稱及名稱長(zhǎng)度。輸入并執(zhí)行查詢：SELECTempno,ename,length(ename)FROMempWHEREinstr(ename,'S',1,1)>0;24，日期函數(shù)返回系統(tǒng)的當(dāng)前日期。輸入并執(zhí)行查詢：SELECTsysdateFROMdual返回2003年2月的最后一天。輸入并執(zhí)行查詢：SELECTlast_day('08-2月-03')FROMdual;假定當(dāng)前的系統(tǒng)日期是2003年2月6日，求再過1000天的日期。輸入并執(zhí)行查詢：SELECTsysdate+1000AS"NEWDATE"FROMdual;假定當(dāng)前的系統(tǒng)日期是2003年2月6日，顯示部門10雇員的雇傭天數(shù)。輸入并執(zhí)行查詢：SELECTename,round(sysdate-hiredate)DAYSFROMempWHEREdeptno=10;25，類型轉(zhuǎn)換函數(shù)函數(shù)功能實(shí)例結(jié)果T轉(zhuǎn)換成字符串類型TT轉(zhuǎn)換成日期類型Tm月0To_num轉(zhuǎn)換成數(shù)值類型To_num自動(dòng)轉(zhuǎn)換字符型數(shù)據(jù)到數(shù)值型。輸入并執(zhí)行查詢：SELECT'12.5'+11FROMdual;自動(dòng)轉(zhuǎn)換數(shù)值型數(shù)據(jù)到字符型。執(zhí)行以下查詢：SELECT'12.5'||11FROMdual;26，日期類型轉(zhuǎn)換MON簡(jiǎn)寫的月份名NARMONTH全拼的月份名NUARY,FEBRUARHH、HH1212小時(shí)制的小時(shí)～12)HH2424小時(shí)制的小時(shí)～23)將日期轉(zhuǎn)換成帶時(shí)間和星期的字符串并顯示。MI分～59)執(zhí)行以下查詢：SS秒～59)原樣顯示的標(biāo)點(diǎn)符號(hào)EXT'引號(hào)中的文本原樣顯示TEXTSELECTTO_CHAR(sysdate,'YYYY-MM-DDHH24:MI:SSAMDY')FROMdual;將日期顯示轉(zhuǎn)換成中文的年月日。輸入并執(zhí)行查詢：SELECTTO_CHAR(sysdate,'YYYY"年"MM"月"DD"日"')FROMdual;將雇傭日期轉(zhuǎn)換成字符串并按新格式顯示。輸入并執(zhí)行查詢：SELECTename,to_char(hiredate,'DDMonthYYYY')HIREDATEFROMemp;以全拼和序列顯示時(shí)間。執(zhí)行以下查詢：SELECTSYSDATE,to_char(SYSDATE,'yyyysp'),to_char(SYSDATE,'mmspth'),to_char(SYSDATE,'ddth')FROMdual;時(shí)間顯示的大小寫。步驟1：執(zhí)行以下查詢：SELECTSYSDATE,to_char(SYSDATE,'yyyysp')FROMdual;結(jié)果為：SYSDATETO_CHAR(SYSDATE,'YYYYSP')07-2月-04twothousandfour步驟2：執(zhí)行以下查詢：SELECTto_char(SYSDATE,'Yyyysp')FROMdual;結(jié)果為：SYSDATETO_CHAR(SYSDATE,'YYYYSP')TwoThousandFour27,數(shù)字類型轉(zhuǎn)換將數(shù)值轉(zhuǎn)換成字符串并按新格式顯示。執(zhí)行以下查詢：SELECTTO_CHAR(123.45,'0000.00'),TO_CHAR(12345,'L9.9EEEE')FROMdual;結(jié)果為：TO_CHAR(TO_CHAR(12345,'L9.90123.45RMB1.2E+04將數(shù)值轉(zhuǎn)換成字符串并按新格式顯示。執(zhí)行以下查詢：SELECTTO_CHAR(sal,'$99,999')SALARYFROMempWHEREename='SCOTT';結(jié)果為：SALARY$4,000函數(shù)功能實(shí)例結(jié)果nvl空值轉(zhuǎn)換函數(shù)'空空實(shí)現(xiàn)分支功能男'女男uv返回環(huán)境信息NGUAGSPLIFIEDCHESE_CHAS16GBK返回參數(shù)的最大值35返回參數(shù)的最小值9NVL:如果不為空，返回原值；為空，則返回給定的值。使用nvl函數(shù)轉(zhuǎn)換空值。執(zhí)行以下查詢：SELECTename,nvl(job,'無'),nvl(hiredate,'01-1月-97'),nvl(comm,0)FROMemp;結(jié)果為：ENAMENVL(JOB,'NNVL(HIREDANVL(COMM,0)SMITHCLERK17-12月-800ALLENSALESMAN20-2月-81300Decode：將職務(wù)轉(zhuǎn)換成中文顯示。執(zhí)行以下查詢：SELECTename,decode(job,'MANAGER','經(jīng)理','CLERK','職員','SALESMAN','推銷員','ANALYST','系統(tǒng)分析員','未知')FROMemp;userenv函數(shù)*ISDBA：判斷會(huì)話用戶的角色是否為SYSDBA，是則返回TRUE。*INSTANCE：返回會(huì)話連接的INSTANCE標(biāo)識(shí)符。*LANGUAGE：返回語言、地區(qū)、數(shù)據(jù)庫(kù)字符集信息。*LANG：返回會(huì)話語言的ISO簡(jiǎn)稱。*TERMINAL：返回正在會(huì)話的終端或計(jì)算機(jī)的標(biāo)識(shí)符。返回用戶終端或系統(tǒng)標(biāo)識(shí)信息。執(zhí)行以下查詢：SELECTuserenv('TERMINAL')FROMdual;結(jié)果為：ORASERVER返回語言、地區(qū)、數(shù)據(jù)庫(kù)字符集信息。執(zhí)行以下查詢：SELECTuserenv('LANGUAGE')FROMdual;結(jié)果為：SIMPLIFIEDCHINESE_CHINA.ZHS16GBK28，相等連接顯示雇員的名稱和所在的部門的編號(hào)和名稱。執(zhí)行以下查詢：SELECTemp.ename,emp.deptno,dept.dnameFROMemp,deptWHEREemp.deptno=dept.deptno;使用表別名。執(zhí)行以下查詢：SELECTename,e.deptno,dnameFROMempe,deptdWHEREe.deptno=d.deptno;29，外連接外連操作符為(+)使用外連顯示不滿足相等條件的記錄。步驟1：顯示雇員的名稱、工資和所在的部門名稱及沒有任何雇員的部門。執(zhí)行以下查詢：SELECTename,sal,dnameFROMemp,deptWHEREemp.deptno(+)=dept.deptno;執(zhí)行結(jié)果為：ENAMESALDNAMESELECTename,sal,dnameFROMemprightouterjoindeptonemp.deptno=dept.deptno;SELECTename,sal,dnameFROMemprightouterjoindeptonemp.deptno=dept.deptno;？？？select*fromTBL_10200_FAC_ORDERBinnerjoinDTCXHDYAonB.MT_FAC_NAME=A.MC30，統(tǒng)計(jì)查詢分組函數(shù)中SUM和AVG只應(yīng)用于數(shù)值型的列，MAX、MIN和COUNT可以應(yīng)用于字符、數(shù)值和日期類型的列。組函數(shù)忽略列的空值。使用GROUPBY組，對(duì)組可以應(yīng)用組函數(shù)。如果不使用分組，將對(duì)整個(gè)表或滿足條件的記錄應(yīng)用組函數(shù)。在組函數(shù)中可使用DISTINCT或ALL關(guān)鍵字。ALL表示對(duì)所有非NULL值(可重復(fù))進(jìn)行運(yùn)算(COUNT除外)DISTINCT表示對(duì)每一個(gè)非NULL值如果不指明上述關(guān)鍵字，默認(rèn)為ALL。求雇員總?cè)藬?shù)。執(zhí)行以下查詢：SELECTCOUNT(*)FROMemp;求有傭金的雇員人數(shù)。執(zhí)行以下查詢：SELECTCOUNT(comm)FROMemp;求部門10的雇員的平均工資。執(zhí)行以下查詢：SELECTAVG(sal)FROMempWHEREdeptno=10;求最晚和最早雇傭的雇員的雇傭日期。執(zhí)行以下查詢：SELECTMAX(hiredate),MIN(hiredate)FROMemp;求雇員表中不同職務(wù)的個(gè)數(shù)。執(zhí)行以下查詢：SELECTCOUNT(DISTINCTjob)FROMemp;返回結(jié)果為：COUNT(DISTINCTJOB)5按職務(wù)統(tǒng)計(jì)工資總和。步驟1：執(zhí)行以下查詢：SELECTSUM(sal)FROMempGROUPBYjob;SELECTjob,SUM(sal)FROMempGROUPBYjob;按部門和職務(wù)分組統(tǒng)計(jì)工資總和。執(zhí)行以下查詢：SELECTdeptno,job,sum(sal)FROMempGROUPBYdeptno,job;統(tǒng)計(jì)各部門的最高工資，排除最高工資小于3000的部門。執(zhí)行以下查詢：SELECTdeptno,max(sal)FROMempGROUPBYdeptnoHAVINGmax(sal)>=3000;注意：WHERE條件在GROUPBY之前執(zhí)行，HAVING條件在分組后執(zhí)行。按職務(wù)統(tǒng)計(jì)工資總和并排序。執(zhí)行以下查詢：SELECTjob職務(wù),SUM(sal)工資總和FROMempGROUPBYjobORDERBYSUM(sal);注意：使用ORDERBY從句對(duì)統(tǒng)計(jì)的結(jié)果進(jìn)行排序，ORDERBY從句要出現(xiàn)在語句的最后。求各部門平均工資的最高值。執(zhí)行以下查詢：SELECTmax(avg(sal))FROMempGROUPBYdeptno;31，子查詢子查詢一般出現(xiàn)在SELECT語句的WHERE子句中，Oracle也支持在FROM或HAVING子句中出現(xiàn)子查詢。子查詢比主查詢先執(zhí)行，結(jié)果作為主查詢的條件，在書寫上要用圓括號(hào)擴(kuò)起來，并放在比較運(yùn)算符的右側(cè)。子查詢可以嵌套使用，最里層的查詢最先執(zhí)行。子查詢可以在SELECT、INSERT、UPDATE、DELETE等語句中使用。查詢比SCOTT工資高的雇員名字和工資。執(zhí)行以下查詢：SELECTename,salFROMempWHEREsal>(SELECTsalFROMempWHEREempno=7788);查詢和SCOTT同一部門且比他工資低的雇員名字和工資。執(zhí)行以下查詢：SELECTename,salFROMempWHEREsal<(SELECTsalFROMempWHEREempno=7788)ANDdeptno=(SELECTdeptnoFROMempWHEREempno=7788);查詢工資高于平均工資的雇員名字和工資。執(zhí)行以下查詢：SELECTename,salFROMempWHEREsal>(SELECTAVG(sal)FROMemp);32，多行子查詢符號(hào)，它們是IN、ANY和ALL。查詢工資低于任何一個(gè)“CLERK”的工資的雇員信息。執(zhí)行以下查詢：SELECTempno,ename,job,salFROMempWHEREsal<ANY(SELECTsalFROMempWHEREjob='CLERK')ANDjob<>'CLERK';查詢工資比所有的“SALESMAN”都高的雇員的編號(hào)、名字和工資。執(zhí)行以下查詢：SELECTempno,ename,salFROMempWHEREsal>ALL(SELECTsalFROMempWHEREjob='SALESMAN');查詢部門20中職務(wù)同部門10的雇員一樣的雇員信息。執(zhí)行以下查詢：SELECTempno,ename,jobFROMempWHEREjobIN(SELECTjobFROMempWHEREdeptno=10)ANDdeptno=20;查詢職務(wù)和SCOTT相同，比SCOTT雇傭時(shí)間早的雇員信息。執(zhí)行以下查詢：SELECTempno,ename,jobFROMempWHEREjob=(SELECTjobFROMempWHEREempno=7788)ANDhiredate<(SELECThiredateFROMempWHEREempno=7788);33，多列子查詢下是多列子查詢的訓(xùn)練實(shí)例?！居?xùn)練8】查詢職務(wù)和部門與SCOTT相同的雇員的信息。執(zhí)行以下查詢：SELECTempno,ename,salFROMempWHERE(job,deptno)=(SELECTjob,deptnoFROMempWHEREempno=7788);34，在FROM從句中使用子查詢?cè)贔ROM從句中也可以使用子查詢，在原理上這與在WHERE條件中使用子查詢類似。查詢雇員表中排在第6～9位置上的雇員。執(zhí)行以下查詢：SELECTename,salFROM(SELECTrownumasnum,ename,salFROMempWHERErownum<=9)WHEREnum>=6;35，集合運(yùn)算操作描述UNION并集，合并兩個(gè)操作的結(jié)果，去掉重復(fù)的部分UNIONALL并集，合并兩個(gè)操作的結(jié)果，保留重復(fù)的部分MINUS差集，從前面的操作結(jié)果中去掉與后面操作結(jié)果相同的部分TERSECT交集，取兩個(gè)操作結(jié)果中相同的部分多個(gè)查詢語句的結(jié)果可以做集合運(yùn)算，結(jié)果集的字段類型、數(shù)量和順序應(yīng)該一樣。查詢部門10和部門20的所有職務(wù)。執(zhí)行以下查詢：SELECTjobFROMempWHEREdeptno=10UNIONSELECTjobFROMempWHEREdeptno=20;查詢部門10和20中是否有相同的職務(wù)和工資。執(zhí)行以下查詢：SELECTjob,salFROMempWHEREdeptno=10INTERSECTSELECTjob,salFROMempWHEREdeptno=20;查詢只在部門表中出現(xiàn)，但沒有在雇員表中出現(xiàn)的部門編號(hào)。執(zhí)行以下查詢：SELECTdeptnoFROMdeptMINUSSELECTdeptnoFROMemp;顯示人數(shù)最多的部門名稱。輸入并執(zhí)行以下查詢：SELECTDECODE(dname,'SALES','銷售部','ACCOUNTING','財(cái)務(wù)部','RESEARCH','研發(fā)部','未知')部門名FROMemp,deptWHEREemp.deptno=dept.deptnoGROUPBYdnameHAVINGCOUNT(*)=(SELECTMAX(COUNT(*))FROMempGROUPBYdeptno);顯示各部門的平均工資、最高工資、最低工資和總工資列表，并按平均工資高低順序排序。輸入并執(zhí)行以下查詢：SELECTdname部門,AVG(sal)平均工資,MAX(sal)最高工資,MIN(sal)最低工資,SUM(sal)總工資FROMemp,deptWHEREemp.deptno=dept.deptnoGROUPBYdnameORDERBYAVG(sal)DESC;36，插入數(shù)據(jù)可以使用INSERT命令，向已經(jīng)存在的表插入數(shù)據(jù)，語法格式如下：INSERTINTO表名[(字段列表)]{VALUES(表達(dá)式1,表達(dá)式2,...)|QUERY語句}；插入字段的值的類型要和字段的類型一一對(duì)應(yīng)。字符串類型的字段值必須用單引號(hào)括起來字符串類型的字段值超過定義的長(zhǎng)度會(huì)出錯(cuò),最好在插入前進(jìn)行長(zhǎng)度校驗(yàn)。字段列表如果省略則代表全部字段。將新雇員插入到emp表：INSERTINTOemp(empno,ename,job)VALUES(1000,'小李','CLERK');插入NULL值日期型的數(shù)據(jù)默認(rèn)格式為DD-MON-YY12點(diǎn)。如果指定的世紀(jì)不是本世紀(jì)或時(shí)間不是午夜12點(diǎn)，則必須使用TO_DATE系統(tǒng)函數(shù)對(duì)字符串進(jìn)行轉(zhuǎn)換。如果要插入表的全部字段，則表名后的字段列表可以省略37，復(fù)制數(shù)據(jù)另一種插入數(shù)據(jù)(相當(dāng)于復(fù)制)方法的語法格式是：INSERTINTO表名(字段列表)SELECT(字段名1,字段名2,...)FROM另外的表名;該形式一次可以插入多行數(shù)據(jù)。步驟1：創(chuàng)建一個(gè)新表managerCREATETABLEmanagerASSELECTempno,ename,salFROMempWHEREjob='MANAGER';執(zhí)行結(jié)果：表已創(chuàng)建。步驟2：從emp表拷貝數(shù)據(jù)到manager：INSERTINTOmanagerSELECTempno,ename,salFROMempWHEREjob='CLERK';38序列使用使用INSERT語句時(shí)，可以通過序列來填寫某些數(shù)值型或字符型的列。序列是一個(gè)要預(yù)先定義的有序的數(shù)值序列,應(yīng)該先建立一個(gè)序列，然后在插入語句中使用插入數(shù)據(jù)中使用序列的練習(xí)。步驟1：創(chuàng)建從2000起始，增量為1的序列abc：CREATESEQUENCEabcINCREMENTBY1STARTWITH2000MAXVALUE99999CYCLENOCACHE;在INSERT語句使用序列，序列的名稱為abc：INSERTINTOmanagerVALUES(abc.nextval,'小王',2500);39,修改數(shù)據(jù)修改數(shù)據(jù)的語句UPDATE對(duì)表中指定字段的數(shù)據(jù)進(jìn)行修改，一般需要通過添加WHERE條件來限定要進(jìn)行修改的行，如果不添加WHERE條件，將對(duì)所有的行進(jìn)行修改。(1)修改數(shù)據(jù)的語句UPDATE的基本語法如下：UPDATE表名SET字段名1=表達(dá)式1,字段名2=表達(dá)式2,...WHERE條件;修改小李(編號(hào)為1000)的工資為3000。執(zhí)行以下的查詢：UPDATEempSETsal=3000WHEREempno=1000;如果修改的值沒有賦值或定義，將把原來字段的內(nèi)容清為NULL。若修改值的長(zhǎng)度超過定義的長(zhǎng)度，則會(huì)出錯(cuò)。(2)UPDATE語句的另外一種用法：UPDATE表名SET(字段名1,字段名2,...)=SELECT(字段名1,字段名2,...)FROM另外的表名WHERE條件;【訓(xùn)練4】根據(jù)其他表修改數(shù)據(jù)。執(zhí)行以下的查詢：UPDATEmanagerSET(ename,sal)=(SELECTename,salFROMempWHEREempno=7788)WHEREempno=1000;執(zhí)行結(jié)果：40,刪除數(shù)據(jù)刪除數(shù)據(jù)的基本語法如下：DELETEFROM表名WHERE條件;要從表中刪除滿足條件的記錄，WHERE【訓(xùn)練1】刪除雇員編號(hào)為1000的新插入的雇員。步驟1：刪除編號(hào)為1000的雇員：DELETEFROMempWHEREempno=1000;如果確實(shí)要?jiǎng)h除一個(gè)大表里的全部記錄，可以用TRUNCATE命令，它可以釋放占用的數(shù)據(jù)塊表空間，語法為：TRUNCATETABLE表名;徹底刪除manager表的內(nèi)容。執(zhí)行以下的命令：TRUNCATETABLEmanager;TRUNCATETABLE命令用來刪除表的全部數(shù)據(jù)而不是刪除表，表依舊存在。41,數(shù)據(jù)庫(kù)事務(wù)COMMIT或ROLLBACK之間的誤，整個(gè)事務(wù)也會(huì)自動(dòng)撤銷。修改，在修改過程中，暫時(shí)不寫入數(shù)之后，就不能撤銷，提交成功后其他用戶才可以通過查詢?yōu)g覽數(shù)據(jù)的變化。以事務(wù)的方式對(duì)數(shù)據(jù)庫(kù)進(jìn)行訪問，有如下的優(yōu)點(diǎn)：*把邏輯相關(guān)的操作分成了一個(gè)組。*在數(shù)據(jù)永久改變前，可以預(yù)覽數(shù)據(jù)變化。*能夠保證數(shù)據(jù)的讀一致性。完成提交或撤銷(回退)工作，無需用戶的干預(yù)。隱式提交的情況包括：當(dāng)用戶正常退出SQL*Plus或執(zhí)行CREATE、DROP、GRANT、REVOKE等命令時(shí)會(huì)發(fā)生事務(wù)的自動(dòng)提交。還有一種情況，如果把系統(tǒng)的環(huán)境變量AUTOCOMMIT設(shè)置為ON(默認(rèn)狀態(tài)為OFF)，則每當(dāng)執(zhí)行一條INSERTDELETE或UPDATE式如下：SETAUTOCOMMITON/OFF隱式回退的情況包括：當(dāng)異常結(jié)束SQL*Plus或系統(tǒng)故障發(fā)生時(shí)，會(huì)發(fā)生事務(wù)的自動(dòng)回退。顯式事務(wù)處理的數(shù)據(jù)庫(kù)事務(wù)操作語句有3條，如表3-2所示。COMMIT操作把多個(gè)步驟對(duì)數(shù)據(jù)庫(kù)的修改，一次性地永久寫入數(shù)據(jù)庫(kù)，代表數(shù)據(jù)庫(kù)事務(wù)的成功執(zhí)行。ROLLBACK操作在發(fā)生問題時(shí)，把對(duì)數(shù)據(jù)庫(kù)已經(jīng)作出的修改撤消，回退到修改前的狀態(tài)。在操作過程中，一旦發(fā)生問題，如果還沒有提交操作，則隨時(shí)可以使用ROLLBACK來撤消前面的操作。SAVEPOINT則用于在事務(wù)中間建立一些保存點(diǎn)，ROLLBACK可以使操作回退COMMITROLLBACK來取消已經(jīng)提交的操作。一旦ROLLBACK完成，被撤消的操作要重做，必須重新執(zhí)行相關(guān)操作語句如何開始一個(gè)新的事務(wù)呢？(即連接數(shù)據(jù)庫(kù))SQL語COMMIT提交或ROLLBACK另外，執(zhí)行DDL(如CREATE)或DCL命令也將自動(dòng)提交前一個(gè)事務(wù)而開始一個(gè)新的事務(wù)。前會(huì)話提交或撤銷后，記錄的鎖定才會(huì)釋放。我們通過以下的訓(xùn)練來為雇員SCOTT增加工資，SCOTT的雇員號(hào)為7788。學(xué)習(xí)使用COMMIT和ROLLBACK。步驟1：執(zhí)行以下命令，提交尚未提交的操作：COMMIT;執(zhí)行結(jié)果：提交完成。顯示SCOTT的現(xiàn)有工資：SELECTename,salFROMempWHEREempno=7788;執(zhí)行結(jié)果：ENAMESALSCOTT3000步驟2：修改雇員SCOTT的工資：UPDATEempSETsal=sal+100WHEREempno=7788;執(zhí)行結(jié)果：已更新1行。顯示修改后的SCOTT的工資：SELECTename,salFROMempWHEREempno=7788;執(zhí)行結(jié)果：ENAMESALSCOTT3100步驟31000而不是100以執(zhí)行以下命令：ROLLBACK;執(zhí)行結(jié)果：回退已完成。顯示回退后SCOTT的工資恢復(fù)為3000：SELECTename,salFROMempWHEREempno=7788;執(zhí)行結(jié)果：ENAMESALSCOTT3000步驟4：重新修改雇員SCOTT的工資，工資在原有基礎(chǔ)上增加1000：UPDATEempSETsal=sal+1000WHEREempno=7788;執(zhí)行結(jié)果：已更新1行。顯示修改后SCOTT的工資：SELECTename,salFROMempWHEREempno=7788;執(zhí)行結(jié)果：ENAMESALSCOTT4000步驟5：經(jīng)查看修改結(jié)果正確，提交所做的修改：COMMIT;執(zhí)行結(jié)果：提交完成。COMMIT后，工資的修改被永久寫入數(shù)據(jù)庫(kù)。本訓(xùn)練的第1步，先使用COMMIT命令提交原來的操作，同時(shí)標(biāo)志一個(gè)新的事務(wù)的開始。注意：在事務(wù)執(zhí)行過程中，隨時(shí)可以預(yù)覽數(shù)據(jù)的變化。對(duì)于比較大的事務(wù)，可以使用SAVEPOINT命令在事務(wù)中間劃分一些斷點(diǎn)，用來作為回退點(diǎn)。【訓(xùn)練2】學(xué)習(xí)使用SAVEPOINT命令。步驟1：插入一個(gè)雇員：INSERTINTOemp(empno,ename,job)VALUES(3000,'小馬','STUDENT');執(zhí)行結(jié)果：已創(chuàng)建1行。步驟2：插入保存點(diǎn)，檢查點(diǎn)的名稱為PA：SAVEPOINTpa;執(zhí)行結(jié)果：保存點(diǎn)已創(chuàng)建。步驟3：插入另一個(gè)雇員：INSERTINTOemp(empno,ename,job)VALUES(3001,'小黃','STUDENT');執(zhí)行結(jié)果：已創(chuàng)建1行。步驟4：回退到保存點(diǎn)PA，則后插入的小黃被取消，而小馬仍然保留。ROLLBACKTOpa;執(zhí)行結(jié)果：回退已完成。步驟5:提交所做的修改：COMMIT;執(zhí)行結(jié)果：提交完成。說4步的回退，將回退到保存點(diǎn)PA，即第3步被撤銷。所以最后的COMMIT只提交了對(duì)小馬的插入。請(qǐng)自行檢查插入的雇員。在Oracle數(shù)據(jù)庫(kù)中，有一個(gè)叫回滾段的特殊的存儲(chǔ)區(qū)域。在提交一個(gè)事物之前，如果用戶ROLLBACK命令或自動(dòng)地進(jìn)行數(shù)據(jù)撤銷。在提交事物之前，用戶自己可以看到修改的數(shù)據(jù)，但因?yàn)樾薷倪€沒有最終提交，其他用戶看到的應(yīng)該是原來的數(shù)據(jù)，也就是回滾段中的數(shù)據(jù)，的讀一致性。當(dāng)?shù)?個(gè)SQL*Plus2個(gè)SQL*Plus1個(gè)SQL*Plus會(huì)話提交（COMMIT）該刪除操作后，兩個(gè)會(huì)話看到的才是一致的數(shù)據(jù)。42，表的鎖定數(shù)據(jù)進(jìn)行相應(yīng)的鎖定。鎖有“只讀鎖“排它鎖“共享排它鎖“行級(jí)鎖一次鎖住一條記錄)“頁(yè)級(jí)鎖)“表級(jí)鎖”(鎖住整個(gè)表)。若為“行級(jí)排它鎖(Update)或刪除(delete)為“表級(jí)排它鎖(select)操作，而無法對(duì)其中的任何記錄進(jìn)行修改或刪除。當(dāng)程序?qū)λ龅男薷倪M(jìn)行提交(commit)或回滾(rollback)后，鎖住的資源便會(huì)得到釋放，從而允許其他用戶進(jìn)行操作。下就會(huì)發(fā)生死鎖。在Oracle數(shù)據(jù)庫(kù)中，修改數(shù)據(jù)操作時(shí)需要一個(gè)隱式的獨(dú)占鎖，以鎖定修改的行，直到修改等到第一個(gè)會(huì)話對(duì)修改使用COMMIT命令進(jìn)行提交或使用ROLLBACK其他會(huì)話對(duì)數(shù)據(jù)的修改。主動(dòng)鎖表：語句描述SELECTFORUPDATE鎖定表行，防止其他會(huì)話對(duì)行的修改LOCKTABLE鎖定表，防止其他會(huì)話對(duì)表的修改鎖定行：對(duì)emp表的部門10的雇員記錄加顯式鎖，并測(cè)試。步驟1：對(duì)部門10加顯式鎖：SELECTempno,ename,job,salFROMempWHEREdeptno=10FORUPDATE;步驟2：?jiǎn)?dòng)第二個(gè)SQL*Plus(第二個(gè)會(huì)話)，以SCOTT賬戶登錄數(shù)據(jù)庫(kù)，對(duì)部門10的雇員CLARK進(jìn)行修改操作。UPDATEempSETsal=sal+100whereempno=7782;執(zhí)行結(jié)果：沒有任何輸出(處于等待解鎖狀態(tài))。步驟3：在第一個(gè)會(huì)話進(jìn)行解鎖操作：COMMIT;步驟4：查看第二個(gè)會(huì)話，有輸出結(jié)果：已更新1行。1對(duì)選定的部門10的雇員加鎖，之后其他會(huì)話不能對(duì)部門10的雇員數(shù)據(jù)進(jìn)行修改或刪除。如果此時(shí)要進(jìn)行修改或刪除，則會(huì)處于等待狀態(tài)。使用COMMIT語句進(jìn)行解鎖之后，如果有掛起的修改或刪除操作，則等待的操作此時(shí)可以執(zhí)行。鎖定表：LOCK語句用于對(duì)整張表進(jìn)行鎖定。語法如下：LOCKTABLE表名IN{SHARE|EXCLUSIVE}MODE對(duì)表的鎖定可以是共享(SHARE)或獨(dú)占(EXCLUSIVE)鎖，但不能加獨(dú)占鎖。在獨(dú)占模式下，其他會(huì)話不能加共享或獨(dú)占鎖。【訓(xùn)練1】對(duì)emp表添加獨(dú)占鎖。步驟1：對(duì)emp表加獨(dú)占鎖：LOCKTABLEempINEXCLUSIVEMODE;結(jié)果為：表已鎖定。步驟2：對(duì)表進(jìn)行解鎖操作：COMMIT;說明：當(dāng)使用LOCK語句顯式鎖定一張表時(shí)，死鎖的概率就會(huì)增加。同樣地，使用COMMIT或ROLLBACK命令可以釋放鎖。注意：必須沒有其他會(huì)話對(duì)該表的任何記錄加鎖，此操作才能成功。43，表的創(chuàng)建表的創(chuàng)建需要CREATETABLE系統(tǒng)權(quán)限，表的基本創(chuàng)建語法如下：CREATETABLE表名(列名數(shù)據(jù)類型(寬度)[DEFAULT表達(dá)式][COLUMNCONSTRAINT],...[TABLECONSTRAINT][TABLE_PARTITION_CLAUSE])；“，是用中文或英文作為表名和列名。表名最大長(zhǎng)度為30個(gè)字符。在同一個(gè)用戶下，表不能重名，但不同用戶表的名稱可以相重。另外，表的名稱不能使用Oracle的保留字。在一張表中最多可以包含2000列。該語法中的其他部分根據(jù)需要添加，作用如下：DEFAULT表達(dá)式：用來定義列的默認(rèn)值。COLUMNCONSTRAINT：用來定義列級(jí)的約束條件。TABLECONSTRAINT：用來定義表級(jí)的約束條件。TABLE_PARTITION_CLAUSE：定義表的分區(qū)子句。創(chuàng)建圖書和出版社表。步驟1：創(chuàng)建出版社表，輸入并執(zhí)行以下命令：CREATETABLE出版社(編號(hào)VARCHAR2(2),出版社名稱VARCHAR2(30),地址VARCHAR2(30),聯(lián)系電話VARCHAR2(20));步驟2：創(chuàng)建圖書表，輸入并執(zhí)行以下命令：CREATETABLE圖書(圖書編號(hào)VARCHAR2(5),圖書名稱VARCHAR2(30),出版社編號(hào)VARCHAR2(2),作者VARCHAR2(10),出版日期DATE,數(shù)量NUMBER(3),單價(jià)NUMBER(7,2));2．通過子查詢創(chuàng)建表如果要?jiǎng)?chuàng)建一個(gè)同已有的表結(jié)構(gòu)相同或部分相同的表，可以采用以下的語法：CREATETABLE表名(列名...)ASSQL查詢語句;WHERE條件決定。通過子查詢創(chuàng)建新的圖書表。步驟1：完全復(fù)制圖書表到“圖書1”，輸入并執(zhí)行以下命令：CREATETABLE圖書1ASSELECT*FROM圖書;執(zhí)行結(jié)果：表已創(chuàng)建。步驟2：創(chuàng)建新的圖書表“圖書2”，只包含書名和單價(jià)，輸入并執(zhí)行以下命令：CREATETABLE圖書2(書名,單價(jià))ASSELECT圖書名稱,單價(jià)FROM圖書;步驟3“圖書3”CREATETABLE圖書3(書名,單價(jià))ASSELECT圖書名稱,單價(jià)FROM圖書WHERE1=2;3．設(shè)置列的默認(rèn)值默認(rèn)值，默認(rèn)值由DEFAULT部分說明?！縿?chuàng)建表時(shí)設(shè)置默認(rèn)值。步驟1：創(chuàng)建表時(shí)，設(shè)置表的默認(rèn)值。CREATETABLE圖書4(圖書編號(hào)VARCHAR2(5)DEFAULTNULL,圖書名稱VARCHAR2(30)DEFAULT'未知',出版社編號(hào)VARCHAR2(2)DEFAULTNULL,出版日期DATEDEFAULT'01-1月-1900',作者VARCHAR2(10)DEFAULTNULL,數(shù)量NUMBER(3)DEFAULT0,單價(jià)NUMBER(7,2)DEFAULTNULL,借出數(shù)量NUMBER(3)DEFAULT0);4．刪除已創(chuàng)建的表刪除表的語法如下：DROPTABLE表名[CASCADECONSTRAINTS]；表的刪除者必須是表的創(chuàng)建者或具有DROPANYTABLE權(quán)限。CASCADECONSTRAINTS表示當(dāng)要?jiǎng)h除的表被其他表參照時(shí)，刪除參照此表的約束條件。44，表的操作1．表的重命名語法如下：RENAME舊表名TO新表名;2．清空表清空表的語法為：TRUNCATETABLE表名；清空表可刪除表的全部數(shù)據(jù)并釋放占用的存儲(chǔ)空間。3．添加注釋(1)為表添加注釋的語法為：COMMENTONTABLE表名IS'...';該語法為表添加注釋字符串。如IS后的字符串為空，則清除表注釋。4為列添加注釋的語法為：COMMENTONCOLUMN表名.列名IS'...'該語法為列添加注釋字符串。如IS后的字符串為空，則清除列注釋。為emp表的deptno列添加注釋：“部門編號(hào)。COMMENTONCOLUMNemp.deptnoIS'部門編號(hào)';5查看表的結(jié)構(gòu)：DESCRIBE表名；DESCRIBE可以簡(jiǎn)寫為DESC?？梢酝ㄟ^對(duì)數(shù)據(jù)字典USER_OBJECTS的查詢，顯示當(dāng)前模式用戶的所有表。顯示當(dāng)前用戶的所有表。SELECTobject_nameFROMuser_objectsWHEREobject_type='TABLE';45，數(shù)據(jù)完整性約束數(shù)據(jù)進(jìn)行插入、刪除和修改時(shí)要對(duì)這些規(guī)則進(jìn)行驗(yàn)證，從而起到約束作用。完整性包括數(shù)據(jù)完整性和參照完整性數(shù)據(jù)之間的約束條件。數(shù)據(jù)完整性由主鍵(PRIMARYKEY)、非空(NOTNULL)、惟一(UNIQUE)和檢查(CHECK)約束條件定義，參照完整性由外鍵(FOREIGNKEY)約束條件定義。表共有五種約束，它們是主鍵、非空、惟一、檢查和外鍵。1．主鍵(PRIMARYKEY)而且一個(gè)表只能定義一個(gè)包含在表級(jí)定義。主鍵一旦成功定B*“圖書編號(hào)”列作主鍵，“圖書編號(hào)”可以惟一地標(biāo)識(shí)圖書表的每一行。主鍵約束的語法如下：[CONSTRANT約束名]PRIMARYKEY--列級(jí)[CONSTRANT約束名]PRIMARYKEY(列名1,列名2,...)--表級(jí)2．非空(NOTNULL)非空約束指定某列不能為空，它只能在列級(jí)定義。在默認(rèn)情況下，Oracle允許列的內(nèi)容為空值。比如“圖書名稱”列要求必須填寫，可以為該列設(shè)置非空約束條件。非空約束語法如下：[CONSTRANT約束名]NOTNULL--列級(jí)約束分為兩級(jí)，一個(gè)約束條件根據(jù)具體情況，可以在列級(jí)或表級(jí)定義。列級(jí)約束：約束表的某一列，出現(xiàn)在表的某列定義之后，約束條件只對(duì)該列起作用。約束表的一列或多列，如果涉及到多列，則必須在表級(jí)定義。表級(jí)約束出現(xiàn)在所有列定義之后。3．惟一(UNIQUE)“聯(lián)系電話不應(yīng)該重復(fù)，可以為其定義惟一約束。惟一約束的語法如下：[CONSTRANT約束名]UNIQUE--列級(jí)[CONSTRANT約束名]UNIQUE(列名1,列名2,...)--表級(jí)4．檢查(CHECK)條件(列的內(nèi)容為空除外)CHECKSYSDATEUSER上可以定義多個(gè)CHECKCHECKCHECK約束包“單價(jià)CHECK約束條件。檢查約束的語法如下：[CONSTRANT約束名]CHECK(約束條件)--列級(jí)，約束條件中只包含本列[CONSTRANT約束名]CHECK(約束條件)--表級(jí)，約束條件中包含多列5．外鍵(FOREIGNKEY)NULL，如果不為NULL，就必須是指定主鍵或惟一鍵的值之一。外鍵通常用來約束兩個(gè)表之間的數(shù)包含一列，則可以在列級(jí)定義；如果包含多列，則必須在表級(jí)定義?！俺霭嫔缇幪?hào)“編號(hào)“編號(hào)列必須先定義成過程中，應(yīng)該先創(chuàng)建主表，后創(chuàng)建子表。外鍵約束的語法如下：第一種語法，如果子記錄存在，則不允許刪除主記錄：[CONSTRANT約束名]FOREIGNKEY(列名1,列名2,...)REFERENCES表名(列名1,列名2,...)第二種語法，如果子記錄存在，則刪除主記錄時(shí)，級(jí)聯(lián)刪除子記錄：[CONSTRANT約束名]FOREIGNKEY(列名1,列名2,...)REFERENCES表名(列名1,列名2,...)ondeletecascade第三種語法，如果子記錄存在，則刪除主記錄時(shí)，將子記錄置成空：[CONSTRANT約束名]FOREIGNKEY(列名1,列名2,...)REFERENCES表名(列名1,列名2,...)ondeletesetnull其中的表名為要參照的表名。在以上5CONSTRANT以SYS_開頭的惟一約束名。約束名的作用是當(dāng)發(fā)生違反約束條件的操作時(shí)，系統(tǒng)會(huì)顯示違反的約束條件名稱，這樣用戶就可以了解到發(fā)生錯(cuò)誤的原因。6，約束條件的創(chuàng)建在表的創(chuàng)建語法中可以定義約束條件：CREATETABLE表名(列名數(shù)據(jù)類型[DEFAULT表達(dá)式][COLUMNCONSTRAINT],...[TABLECONSTRAINT])；其中，COLUMNCONSTRAINTTABLECONSTRAINT用來定義表級(jí)約束條件?！居?xùn)練1】創(chuàng)建帶有約束條件的出版社表(如果已經(jīng)存在，先刪除)：CREATETABLE出版社(編號(hào)VARCHAR2(2)CONSTRAINTPK_1PRIMARYKEY,出版社名稱VARCHAR2(30)NOTNULL,地址VARCHAR2(30)DEFAULT'未知',聯(lián)系電話VARCHAR2(20));執(zhí)行結(jié)果：表已創(chuàng)建。說明：出版社表的主鍵列是“編號(hào)”列，主鍵名為PK_1?！俺霭嫔缑Q”必須填寫，地址的默認(rèn)值為“未知。【訓(xùn)練2】創(chuàng)建帶有約束條件(包括外鍵)的圖書表(如果已經(jīng)存在，先刪除)：CREATETABLE圖書(圖書編號(hào)VARCHAR2(5)CONSTRAINTPK_2PRIMARYKEY,圖書名稱VARCHAR2(30)NOTNULL,出版社編號(hào)VARCHAR2(2)CHECK(LENGTH(出版社編號(hào))=2)NOTNULL,作者VARCHAR2(10)DEFAULT'未知',出版日期DATEDEFAULT'01-1月-1900',數(shù)量NUMBER(3)DEFAULT1CHECK(數(shù)量>0),單價(jià)NUMBER(7,2),CONSTRAINTYS_1UNIQUE(圖書名稱,作者),CONSTRAINTFK_1FOREIGNKEY(出版社編號(hào))REFERENCES出版社(編號(hào))ONDELETECASCADE);說明：因?yàn)閮蓚€(gè)表同屬于一個(gè)用戶，故約束名不能相重，圖書表的主鍵為“圖書編號(hào)”列，主鍵名為PK_2CHECK(LENGTH(出版社編號(hào))=2)表示出版社編號(hào)的長(zhǎng)度必須是2，約束條件UNIQUE(圖書名稱,作者)表示“圖書名稱”和“作者”兩列的內(nèi)容組合必須惟一。FOREIGNKEY(出版社編號(hào))REFERENCES出版社(編號(hào))表示圖書表的“出版社編號(hào)”列參照出版社的“編號(hào)”主鍵列。出版社表為主表，圖書表為子表，出版社表必須先創(chuàng)建。ONDELETECASCADE清華大學(xué)出版社，則圖書表中清華大學(xué)出版社的圖書也會(huì)被刪除。如果同時(shí)出現(xiàn)DEFAULT和CHECK，則DEFAULT需要出現(xiàn)在CHECK約束條件之前。7，查看約束條件數(shù)據(jù)字典USER_CONSTRAINTS中包含了當(dāng)前模式用戶的約束條件信息。其中，CONSTRAINTS_TYPE顯示的約束類型為：C：CHECK約束。P：PRIMARYKEY約束。U：UNIQUE約束。R：FOREIGNKEY約束。其他信息可根據(jù)需要進(jìn)行查詢顯示，可用DESCRIBE命令查看USER_CONSTRAINTS的結(jié)構(gòu)?！繖z查表的約束信息：SELECTCONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITIONFROMUSER_CONSTRAINTSWHERETABLE_NAME='圖書';執(zhí)行結(jié)果：CONSTRAINT_NAMECSEARCH_CONDITIONSYS_C003111C"圖書名稱"ISNOTNULLSYS_C003112C"出版社編號(hào)"ISNOTNULLSYS_C003113CLENGTH(出版社編號(hào))=2SYS_C003114C數(shù)量>0PK_2PYS_1UFK_1RPRIMARYKEY(P)約束PK_2，一個(gè)FOREIGNKEY(R)約束FK_1，一個(gè)UNIQUE(R)約束YS_1和4個(gè)CHECK(C)約束SYS_C003111、SYS_C003112、SYS_C003113和SYS_C003114，4個(gè)CHECK約束的名字是由系統(tǒng)命名的。8，使約束生效和失效約束條件依然發(fā)生作用就會(huì)影響操作的效率，比如導(dǎo)出和導(dǎo)入數(shù)據(jù)時(shí)要暫時(shí)關(guān)閉約束條件，這時(shí)可以使用下面的命令關(guān)閉或打開約束條件。使約束條件失效：ALTERTABLE表名DISABLECONSTRANT約束名;使約束條件生效：ALTERTABLE表名ENABLECONSTRANT約束名;【訓(xùn)練1】使圖書表的數(shù)量檢查失效。步驟1：使約束條件SYS_C003114(數(shù)量>0)失效：ALTERTABLE圖書DISABLECONSTRAINTSYS_C003114;執(zhí)行結(jié)果：表已更改。步驟2：修改數(shù)量為0：UPDATE圖書SET數(shù)量=0WHERE圖書編號(hào)='A0001';執(zhí)行結(jié)果：已更新1行。步驟3：使約束條件SYS_C003114生效：ALTERTABLE圖書ENABLECONSTRAINTSYS_C003114;46，修改表結(jié)構(gòu)1，增加新列增加新列的語法如下：ALTERTABLE表名ADD列名數(shù)據(jù)類型[DEFAULT表達(dá)式][COLUMNCONSTRAINT];如果要為表同時(shí)增加多列，可以按以下格式進(jìn)行：ALTERTABLE表名ADD(列名數(shù)據(jù)類型[DEFAULT表達(dá)式][COLUMNCONSTRAINT]...);對(duì)于有數(shù)據(jù)的表，新增加列的值為NULL，所以有數(shù)據(jù)的表，新增加列不能指定為NOTNULL約束條件。為“出版社”增加一列“電子郵件”：ALTERTABLE出版社ADD電子郵件VARCHAR2(30)CHECK(電子郵件LIKE'%@%');ALTERTABLETBL_MAX_TEN_CIF_BRADDCPCSVARCHAR2(10);2，修改列修改列的語法如下：ALTERTABLE表名MODIFY列名數(shù)據(jù)類型[DEFAULT表達(dá)式][COLUMNCONSTRAINT]如果要對(duì)表同時(shí)修改多列，可以按以下格式進(jìn)行：ALTERTABLE表名MODIFY(列名數(shù)據(jù)類型[DEFAULT表達(dá)式][COLUMNCONSTRAINT]...);增加。其他部分都可以進(jìn)行修改，如果沒有給出新的定義，表示該部分屬性不變。(1)列的寬度可以增加或減小，在表的列沒有數(shù)據(jù)或數(shù)據(jù)為NULL時(shí)才能減小寬度。(2)在表的列沒有數(shù)據(jù)或數(shù)據(jù)為NULL時(shí)才能改變數(shù)據(jù)類型，CHAR和VARCHAR2之間可以隨意轉(zhuǎn)換。(3)只有當(dāng)列的值非空時(shí)，才能增加約束條件NOTNULL。(4)修改列的默認(rèn)值，只影響以后插入的數(shù)據(jù)。修改“出版社”表“電子郵件”列的寬度為40。ALTERTABLE出版社MODIFY電子郵件VARCHAR2(40);altertableTBL_MT_BR_BANCSmodifyADDR_LINE_1VARCHAR2(100)altertableTBL_SME_FAC2modifyIS_NEWVARCHAR2(10);3，刪除列刪除列的語法如下：ALTERTABLE表名DROPCOLUMN列名[CASCADECONSTRAINTS]；如果要同時(shí)刪除多列，可以按以下格式進(jìn)行：ALTERTABLE表名DROP(COLUMN列名數(shù)據(jù)類型[DEFAULT表達(dá)式][COLUMNCONSTRAINT]...)[CASCADECONSTRAINTS];定CASCADECONSTRAINTS才能刪除約束條件。刪除“出版社”表的“電子郵件”列。ALTERTABLE出版社DROPCOLUMN電子郵件;使用以下語法，可以將列置成UNUSED狀態(tài)，這樣就不會(huì)在表中顯示出該列：ALTERTABLE表名SETUNUSEDCOLUMN列名[CASCADECONSTRAINTS];以后可以重新使用或刪除該列。通過數(shù)據(jù)字典可以查看標(biāo)志成UNUSED的列。刪除標(biāo)志成UNUSED的列：ALTERTABLE表名DROPUNUSEDCOLUMNS;ALTERTABLETBL_MT_FACDROPCOLUMNCPCS;4，約束條件的修改可以為表增加或刪除表級(jí)約束條件。1．增加約束條件增加約束條件的語法如下：ALTERTABLE表名ADD[CONSTRAINT約束名]表級(jí)約束條件;【訓(xùn)練1】為emp表的mgr列增加外鍵約束：ALTERTABLEempADDCONSTRAINTFK_3FOREIGNKEY(mgr)REFERENCESemp(empno);執(zhí)行結(jié)果：表已更改。mgr(經(jīng)理編號(hào))列的內(nèi)容必須是雇員編號(hào))之一。2．刪除約束條件刪除約束條件的語法如下：ALTERTABLE表名DROPPRIMARY_KEY|UNIQUE(列名)|CONSTRAINT約束名[CASCADE];【訓(xùn)練2】刪除為emp表的mgr列增加的外鍵約束：ALTERTABLEempDROPCONSTRAINTFK_3;47，分區(qū)表簡(jiǎn)介分區(qū)的依據(jù)可以是一列或多列的值，這一列或多列稱為分區(qū)關(guān)鍵字或分區(qū)列。所有分區(qū)的邏輯屬性是一樣的()理屬性(表空間、存儲(chǔ)參數(shù)等)。分區(qū)有三種：范圍分區(qū)、哈斯分區(qū)和混合分區(qū)。范圍分區(qū)(RANGEPARTITIONING)：根據(jù)分區(qū)關(guān)鍵字值的范圍建立分區(qū)。比如，根據(jù)省份為人口數(shù)據(jù)表建立分區(qū)。哈斯分區(qū)(HASHPARTITIONING)：在分區(qū)列上使用HASH算法進(jìn)行分區(qū)?；旌戏謪^(qū)(COMPOSITEPARTITIONING)：混合以上兩種方法，使用范圍分區(qū)建立主分區(qū)，使用HASH算法建立子分區(qū)。創(chuàng)建和使用分區(qū)表。步驟1：創(chuàng)建按成績(jī)分區(qū)的考生表，共分為3個(gè)區(qū)：CREATETABLE考生(考號(hào)VARCHAR2(5),姓名VARCHAR2(30),成績(jī)NUMBER(3))PARTITIONBYRANGE(成績(jī))(PARTITIONAVALUESLESSTHAN(300)TABLESPACEUSERS,PARTITIONBVALUESLESSTHAN(500)TABLESPACEUSERS,PARTITIONCVALUESLESSTHAN(MAXVALUE)TABLESPACEUSERS);步驟2：插入不同成績(jī)的若干考生：INSERTINTO考生VALUES('10001','王明',280);INSERTINTO考生VALUES('10002','李亮',730);INSERTINTO考生VALUES('10003','趙成',550);INSERTINTO考生VALUES('10004','黃凱',490);INSERTINTO考生VALUES('10005','馬新',360);INSERTINTO考生VALUES('10006','楊麗',670);步驟3：檢查A區(qū)中的考生：SELECT*FROM考生PARTITION(A);執(zhí)行結(jié)果：考號(hào)姓名成績(jī)10001王明280步驟4：檢查全部的考生：SELECT*FROM考生;執(zhí)行結(jié)果：考號(hào)姓名成績(jī)10001王明28010004黃凱49010005馬新36010002李亮73010003趙成55010006楊麗670ABCA區(qū)的分?jǐn)?shù)范圍為300B區(qū)的分?jǐn)?shù)范圍為300至500C區(qū)的分?jǐn)?shù)范圍為5006的區(qū)。48，視圖創(chuàng)建和操作訪問表的全部行和列。的檢索卻很簡(jiǎn)單。*一個(gè)視圖可以檢索多張表的數(shù)據(jù)，因此用戶通過訪問一個(gè)視圖，可完成對(duì)多個(gè)表的訪問。*視圖是相同數(shù)據(jù)的不同表示，通過為不同的用戶創(chuàng)建同一個(gè)表的不同視圖，使用戶可分別訪問同一個(gè)表的不同部分。將同樣起作用。視圖的創(chuàng)建創(chuàng)建視圖需要CREAEVIEW系統(tǒng)權(quán)限，視圖的創(chuàng)建語法如下：CREATE[ORREPLACE][FORCE|NOFORCE]VIEW視圖名[(別名1[，別名2...])]AS子查詢[WITHCHECKOPTION[CONSTRAINT約束名]][WITHREADONLY]其中：ORREPLACE表示替代已經(jīng)存在的視圖。FORCE表示不管基表是否存在，創(chuàng)建視圖。NOFORCE表示只有基表存在時(shí)，才創(chuàng)建視圖，是默認(rèn)值。別名是為子查詢中選中的列新定義的名字，替代查詢表中原有的列名。子查詢是一個(gè)用于定義視圖的SELECT查詢語句，可以包含連接、分組及子查詢。WITHCHECKOPTION該約束條件的名字。WITHREADONLY表示視圖是只讀的。刪除視圖的語法如下：DROPVIEW視圖名;刪除視圖者需要是視圖的建立者或者擁有DROPANYVIEW丟失數(shù)據(jù)。創(chuàng)建簡(jiǎn)單視圖【訓(xùn)練1】創(chuàng)建圖書作者視圖。步驟1：創(chuàng)建圖書作者視圖：CREATEVIEW圖書作者(書名,作者)ASSELECT圖書名稱,作者FROM圖書;輸出結(jié)果：視圖已建立。步驟2：查詢視圖全部?jī)?nèi)容SELECT*FROM圖書作者;輸出結(jié)果：書名作者計(jì)算機(jī)原理劉勇C語言程序設(shè)計(jì)馬麗匯編語言程序設(shè)計(jì)黃海明步驟3：查詢部分視圖：SELECT作者FROM圖書作者;輸出結(jié)果：作者劉勇馬麗黃海明“圖書作者，視圖只包含兩列，為“書名”和“作者，對(duì)“圖書名稱“作者圖隱藏了表的部分內(nèi)容。創(chuàng)建清華大學(xué)出版社的圖書視圖。步驟1：創(chuàng)建清華大學(xué)出版社的圖書視圖：CREATEVIEW清華圖書ASSELECT圖書名稱,作者,單價(jià)FROM圖書WHERE出版社編號(hào)='01';執(zhí)行結(jié)果：視圖已建立。步驟2：查詢圖書視圖：SELECT*FROM清華圖書;執(zhí)行結(jié)果：圖書名稱作者單價(jià)計(jì)算機(jī)原理劉勇25.3步驟3：刪除視圖：DROPVIEW清華圖書;修改作者視圖，加入出版社名稱。步驟1：重建圖書作者視圖：CREATEORREPLACEVIEW圖書作者(書名,作者,出版社)ASSELECT圖書名稱,作者,出版社名稱FROM圖書,出版社WHERE圖書.出版社編號(hào)=出版社.編號(hào);輸出結(jié)果：視圖已建立。步驟2：查詢新視圖內(nèi)容：SELECT*FROM圖書作者;輸出結(jié)果：書名作者出版社計(jì)算機(jī)原理劉勇清華大學(xué)出版社C語言程序設(shè)計(jì)馬麗電子科技大學(xué)出版社匯編語言程序設(shè)計(jì)黃海明電子科技大學(xué)出版社ORREPLACE選項(xiàng)，使新的視圖替代了同名的原有視圖，同時(shí)在查詢中使用了相等連接，使得視圖的列來自于兩個(gè)不同的基表?！縿?chuàng)建一個(gè)統(tǒng)計(jì)視圖。步驟1：創(chuàng)建emp表的一個(gè)統(tǒng)計(jì)視圖：CREATEVIEW統(tǒng)計(jì)表(部門名,最大工資,最小工資,平均工資)ASSELECTDNAME,MAX(SAL),MIN(SAL),AVG(SAL)FROMEMPE,DEPTDWHEREE.DEPTNO=D.DEPTNOGROUPBYDNAME;執(zhí)行結(jié)果：視圖已建立。步驟2：查詢統(tǒng)計(jì)表：SELECT*FROM統(tǒng)計(jì)表;執(zhí)行結(jié)果：部門名最大工資最小工資平均工資ACCOUNTING500013003050RESEARCH30008002175SALES28509501566.66667到統(tǒng)計(jì)結(jié)果。創(chuàng)建只讀視圖創(chuàng)建只讀視圖要用WITHREADONLY選項(xiàng)?！居?xùn)練5】創(chuàng)建只讀視圖。步驟1：創(chuàng)建emp表的經(jīng)理視圖：CREATEORREPLACEVIEWmanagerASSELECT*FROMempWHEREjob='MANAGER'WITHREADONLY;執(zhí)行結(jié)果：視圖已建立。視圖的插入【訓(xùn)練1】視圖插入練習(xí)。步驟1：創(chuàng)建清華大學(xué)出版社的圖書視圖：CREATEORREPLACEVIEW清華圖書ASSELECT*FROM圖書WHERE出版社編號(hào)='01';執(zhí)行結(jié)果：視圖已建立。步驟2：插入新圖書：INSERTINTO清華圖書VALUES('A0005','軟件工程','01','馮娟',5,27.3);執(zhí)行結(jié)果：已創(chuàng)建1行。步驟3：顯示視圖：SELECT*FROM清華圖書;查看用戶擁有的視圖：SELECTobject_nameFROMuser_objectsWHEREobject_type='VIEW';49，數(shù)據(jù)庫(kù)模式對(duì)象50，索引索引(INDEX)是為了加快數(shù)據(jù)的查找而創(chuàng)建的數(shù)據(jù)庫(kù)對(duì)象，特別是對(duì)大表，索引可以有效地提高查找速度，也可以保證數(shù)據(jù)的惟一性。索引是由Oracle自動(dòng)使用和維護(hù)的，一旦創(chuàng)建存放的，當(dāng)刪除索引時(shí)，對(duì)擁有索引的表的數(shù)據(jù)沒有影響。索引的創(chuàng)建創(chuàng)建索引不需要特定的系統(tǒng)權(quán)限。建立索引的語法如下：CREATE[{UNIQUE|BITMAP}]INDEX索引名ON表名(列名1[，列名2，...])；其中：UNIQUE代表創(chuàng)建惟一索引，不指明為創(chuàng)建非惟一索引。BITMAP代表創(chuàng)建位圖索引，如果不指明該參數(shù)，則創(chuàng)建B*樹索引。列名是創(chuàng)建索引的關(guān)鍵字列，可以是一列或多列。刪除索引的語法是：DROPINDEX索引名；刪除索引的人應(yīng)該是索引的創(chuàng)建者或擁有DROPANYINDEX系統(tǒng)權(quán)限的用戶。索引的刪除對(duì)表沒有影響?！居?xùn)練1】創(chuàng)建和刪除索引。步驟1：創(chuàng)建索引：CREATEINDEXEMP_ENAMEONEMP(ENAME);執(zhí)行結(jié)果：索引已創(chuàng)建。步驟2：查詢中引用索引：SELECTENAME,JOB,SALFROMEMPWHEREENAME='SCOTT';執(zhí)行結(jié)果：ENAMEJOBSALSCOTTANALYST3000步驟3：刪除索引：DROPINDEXEMP_ENAME;執(zhí)行結(jié)果：索引已丟棄。查看索引通過查詢數(shù)據(jù)字典USER_INDEXES可以檢查創(chuàng)建的索引。通過查詢數(shù)據(jù)字典USER_IND_COLUMNS可以檢查索引的列?！居?xùn)練1】顯示emp表的索引：SELECTINDEX_NAME,INDEX_TYPE,UNIQUENESSFROMUSER_INDEXESWHERETABLE_NAME='EMP';顯示索引的列。SELECTCOLUMN_NAMEFROMUSER_IND_COLUMNSWHEREINDEX_NAME='EMP_JOBSAL';例如：CREATEINDEXIDX_CPMX_LS_<!SD.YMD!>ONTBL_CPMX_LS_<!SD.YMD!>(ACCT_NO,CREDIT_DT_UPDATED);51，序列序列(SEQUENCE)(類型為數(shù)字)檢查當(dāng)前值，或使序列增至下一個(gè)值。創(chuàng)建序列需要CREATESEQUENCE系統(tǒng)權(quán)限。序列的創(chuàng)建語法如下：CREATESEQUENCE序列名[INCREMENTBYn][STARTWITHn][{MAXVALUEn|NOMAXVALUE}][{MINVALUEn|NOMINVALUE}][{CYCLE|NOCYCLE}][{CACHEn|NOCACHE}]；其中：INCREMENTBY用于定義序列的步長(zhǎng)，如果省略，則默認(rèn)為1，如果出現(xiàn)負(fù)值，則代表序列的值是按照此步長(zhǎng)遞減的。STARTWITH定義序列的初始值(即產(chǎn)生的第一個(gè)值)，默認(rèn)為1。MAXVALUENOMAXVALUE定義，這時(shí)對(duì)于遞增序列，系統(tǒng)能夠產(chǎn)生的最大值是10的27是-1。MINVALUENOMAXVALUE定義，這時(shí)對(duì)于遞減序列，系統(tǒng)能夠產(chǎn)生的最小值是?10的26次方；對(duì)于遞增序列，最小值是1。CYCLE和NOCYCLE表示當(dāng)序列生成器的值達(dá)到限制值后是否循環(huán)。CYCLE代表循環(huán)，NOCYCLE小值時(shí)，循環(huán)到最大值。如果不循環(huán)，達(dá)到限制值后，繼續(xù)產(chǎn)生新值就會(huì)發(fā)生錯(cuò)誤。CACHE(緩沖)定義存放序列的內(nèi)存塊的大小，默認(rèn)為20。NOCACHE表示不對(duì)序列進(jìn)行內(nèi)存緩沖。對(duì)序列進(jìn)行內(nèi)存緩沖，可以改善序列的性能。刪除序列的語法是：DROPSEQUENCE序列名;刪除序列的人應(yīng)該是序列的創(chuàng)建者或擁有DROPANYSEQUENCE就不能被引用了。SATRTWITH響隨后產(chǎn)生的序號(hào)，已經(jīng)產(chǎn)生的序號(hào)不變。修改序列的語法如下：ALTERSEQUENCE序列名[INCREMENTBYn][{MAXVALUEn|NOMAXVALUE}][{MINVALUEn|NOMINVALUE}][{CYCLE|NOCYCLE}][{CACHEn|NOCACHE}]；創(chuàng)建和刪除序列。步驟1：創(chuàng)建序列：CREATESEQUENCEABCINCREMENTBY1STARTWITH10MAXVALUE9999999NOCYCLENOCACHE;執(zhí)行結(jié)果：序列已創(chuàng)建。步驟2：刪除序列：