Oracle數(shù)據(jù)庫Sql語句詳解大全

OracleSQL第一章SELECT查詢本章目標寫一條SELECT查詢語句在查詢中使用表達式、運算符對空值的處理對查詢字段起別名查詢字段的連接SELECT查詢基本語法SELECT<列名>FROM

<表名>SQL>SELECT*FROMs_emp請查詢出s_emp表中所有的員工信息:查詢指定列SQL>SELECTdept_id,salaryFROMs_emp請查詢出s_emp表中所有的員工的部門ID，工資:運用算數(shù)表達式SQL>SELECTsalary*12FROMs_emp請查詢出s_emp表中所有的員工的年薪:運用算數(shù)表達式括號可以改變運算符運算的優(yōu)先順序:SQL>SELECT last_name,salary,12*salary+1002FROM s_emp;

... Velasquez250030100SQL>SELECT last_name,salary,12*(salary+100)2FROM s_emp;

... Velasquez250031200列別名請查詢出s_emp表中所有的員工的姓名:SQL>SELECTfirname_name||last_nameFROMs_empSQL>SELECTfirname_name||last_name“姓名”

FROMs_emp請查詢出s_emp表中所有的員工的姓名:空值的處理請查詢出s_emp表中所有的員工的工資:SQL>SELECT

last_name,salary*commission_pct/100

“工資”

2

FROM

s_emp;SQL>SELECTlast_name,salary+salary*NVL(commission_pct,0)/100

2

FROM

s_emp;去掉重復(fù)行請查詢出s_dept表的部門名稱:SQL>SELECT name2FROM s_dept;SQL>SELECT DISTINCTname2FROM s_dept;去掉多列列重復(fù)行行SQL>SELECTDISTINCTdept_id,title2FROMs_emp;請查詢出出s_emp表中所有有的員工工的部門門ID及職稱:小結(jié)查詢表的的全部記記錄查詢指定定的列給列起別別名NVL函數(shù)及字字符串連連接符重復(fù)行的的處理DistinctSELECT[DISTINCT]{*,column[alias],...}FROMtable;引言第二章條條件查查詢本章目標標WHERE條件查詢詢在查詢中中使用表表達式、、運算符符使用LIKE、BETWEEN、IN進行模糊糊查詢第一章內(nèi)內(nèi)容回顧顧對員工表表中信息息進行查查詢，具具體要求求如下：：1.查詢s_emp表要求輸出出員工姓姓名(firs_name、last_name)和實際工工資(基本工資資+提成)：查詢基本本語法SELECT<列名>FROM<表名>[WHERE<查詢條件件表達式式>]列名稱SELECT

*表名FROMS_emp過濾條件WHEREdept_id=41

請查詢出出s_emp表中dept_id為41的員工信信息:WHERE條件查詢詢請查詢出出s_emp表中l(wèi)ast_name為Smith的員工的的信息:SELECT*FROMs_empWHERElast_name='Smith'請查詢出出s_emp表中部門門ID為50并且工資資大于1500的員工的的信息:SELECT*FROMs_empWHEREsalary>1500anddept_id=50WHERE條件查詢詢-BETWEEN&IN請查詢出出s_emp表中工資資在1500到2000之間的員員工信息息：SELECT*FROMs_empWHEREsalarybetween1500and2000請查詢出出s_dept表中region_id為1，3的部門信信息：SELECT*FROMs_deptWHEREregion_idin(1,3)WHERE條件查詢詢-like請查詢出出s_emp表中姓中中含有字字母a的員工信信息：SELECT*FROMs_empWHERElast_namelike'%a%'請查詢出出當前用用戶下所所有以‘‘s_’開頭的表表：SELECTtable_nameFROMuser_tablesWHEREtable_namelike'S\_%'escape'\'請查詢出出s_emp表姓中第第二個字字母為a的員工信信息：SELECT*FROMs_empWHERElast_namelike‘_a%'空值的查查詢查詢出s_emp表中非銷銷售職位位的員工工信息：：SELECT*FROMs_empWHEREcommission_pctisnull課堂練習(xí)習(xí)已建立好好的S_emp表，對此此表中的的數(shù)據(jù)進進行模糊糊查詢，，具體要求求如下：：查詢一名名last_name以“M”開頭的員員工，他他的dept_id好像是’’3X’查詢工資資在1200至1500之間的員員工查詢來自自部門ID為(41，42，43)的員工查詢結(jié)果果排序查詢出s_emp表將部門門ID為41的員工的的工資按按從高到到低排列列顯示出出來：SELECT*FROMs_empWHEREdept_id=41ORDERBYsalaryDESCSQL>SELECTlast_name,dept_id,salary2FROMs_emp3ORDERBYdept_id,salaryDESC;小結(jié)Where條件查詢詢Between…and&In&Like模糊查詢詢對查詢結(jié)結(jié)果排序序SELECT[DISTINCT]{*,column[alias],...}FROMtable[WHEREcondition(s)][ORDERBY{column,expr,alias}[ASC|DESC]];第三章單單行函函數(shù)本章目標標熟悉各種種類型單單行函數(shù)數(shù)的使用用掌握轉(zhuǎn)換換函數(shù)的的使用兩種SQL函數(shù)單行函數(shù)數(shù)CharacterNumberDateConversionSINGLE-ROWFUNCTION多行函數(shù)數(shù)GroupMULTI-ROWFUNCTION字符函數(shù)數(shù)LOWER將字符串串轉(zhuǎn)換成成小寫UPPER將字符串串變?yōu)榇蟠髮慖NITCAP將字符串串的第一一個字母母變?yōu)榇蟠髮慍ONCAT拼接兩個個字符串串，與||相同SUBSTR取字符串串的子串串LENGTH以字符給出字符符串的長長度NVL以一個值值來替換換空值字符函數(shù)數(shù)舉例LOWER('SQLCourse')sqlcourseUPPER('SQLCourse')SQLCOURSEINITCAP('SQLCourse')SqlCourseSELECT*FROMs_empWHERElast_name=‘PATEL’SELECT*FROMs_empWHEREUPPER(last_name)=‘‘PATEL’’字符操作作函數(shù)CONCAT('Good','String')GoodStringSUBSTR('String',1,3)StrLENGTH('String')6數(shù)字函數(shù)數(shù)ROUND(value,precision)按按precision精度4舍5入TRUNC(value,precision)按precision截取valueSQL>SELECTround(55.5),round(-55.5),trunc(55.5),trunc(-55.5)FROMdual;round(55.5)round(-55.5)trunc(55.5)trunc(-55.5)-----------------------------------------------56-5655-55SQL>SELECTTRUNC(124.16666,-2)trunc1,trunc(124.16666,2)FROMdual;TRUNC1TRUNC(124.16666,2)----------------------------100124.16Round&Trunc函數(shù)ROUND(45.923,2)45.92ROUND(45.923,0)46ROUND(45.923,-1)50TRUNC(45.923,2)45.92TRUNC(45.923)45TRUNC(45.923,-1)40日期函數(shù)數(shù)MONTHS_BETWEEN(date2,date1)給出Date2-date1的月數(shù)ADD_MONTHS增加或減減去月份份NEXT_DAY(date,’day’)給出日期期date之后下一一天的日日期LAST_DAY(date)返回日期期所在月月的最后后一天日期函數(shù)數(shù)MONTHS_BETWEEN(‘01-SEP-95’,‘11-JAN-94’)19.774194ADD_MONTHS('11-JAN-94',6)'11-JUL-94‘NEXT_DAY('01-SEP-95','FRIDAY')'08-SEP-95‘LAST_DAY('01-SEP-95')'30-SEP-95'日期函數(shù)數(shù)ROUND('25-MAY-95','MONTH')01-JUN-95ROUND('25-MAY-95','YEAR')01-JAN-95TRUNC('25-MAY-95','MONTH')01-MAY-95TRUNC('25-MAY-95','YEAR')01-JAN-95轉(zhuǎn)換函數(shù)數(shù)TO_CHAR(date,'fmt')轉(zhuǎn)換日期期格式到到字符串串用下列格格式顯示示字符為為數(shù)字：：9代表一個個數(shù)字.0顯示前綴綴零.$根據(jù)本地地語言環(huán)環(huán)境顯示示貨幣.L采用當?shù)氐刎泿欧?打印一個個小數(shù)點點.,千位計算算法顯示示.日期格式式DY星期幾的縮寫Mon,Tue,...DAY星期幾的全拼Monday,Tuesday,...D一周的星期幾，星期天=1，星期六=71,2,3,4,5,6,7DD一月的第幾天，1311，2，...31W一個月的第幾周，151,2,3,4,5WW,IW一年的第幾周，一年的ISO的第幾周1,2,3,4,...52MM兩為數(shù)的月01,02,03,...12MON月份的縮寫Jan,Feb,Mar,...DecMONTH月份的全拼January,February,...YYYY,YYY,YY,Y四位數(shù)的年，三位數(shù)的年1999,999,99,9YEAR年的全拼NineteenNinety-nineRR當前年份的后兩位數(shù)字01代表2001年HH,HH1212小時制，1121,2,3,...12HH2424小時制，0230,1,2,3,...23MI一小時中的第幾分，0590,1,2,3...59SS一分中的第幾秒，0590,1,2,3,...59To-char舉例SQL>SELECTlast_name,TO_CHAR(start_date,2'fmDdspth"of"MonthYYYYfmHH:MI:SSAM')HIREDATE3FROM s_emp4WHEREstart_dateLIKE'%91';SQL>SELECT'Order‘‘||TO_CHAR(id)||2 'wasfilledforatotalof‘‘||TO_CHAR(total,'fm$9,999,999')3FROMs_ord4WHEREship_date='21-SEP-92';To-char舉例查詢員工工表中入入職日期期在7月份的員員工信息息：SELECT*FROMs_empWHEREto_char(start_date,'mm')=07RR日期格式式CurrentYear1995199520012001SpecifiedDate27-OCT-9527-OCT-1727-OCT-1727-OCT-95RRFormat1995201720171995YYFormat1995191720172095Ifthespecifiedtwo-digityearisIftwodigitsofthecurrentyearare0-490-4950-9950-99Thereturndateisinthecurrentcentury.Thereturndateisinthecenturyafterthecurrentone.Thereturndateisinthecenturybeforethecurrentone.Thereturndateisinthecurrentcentury.轉(zhuǎn)換函數(shù)TO_NUMBER(‘String’)轉(zhuǎn)換字符串到到數(shù)字TO_DATE(‘String’)轉(zhuǎn)換字符串到到日期格式SELECTto_date(‘2009-09-22’,’yyyy-mm-dd’)FROMdual轉(zhuǎn)換函數(shù)的嵌嵌套F3(F2(F1(col,arg1),arg2),arg3)Step1=Result1Step2=Result2Step3=Result3轉(zhuǎn)換函數(shù)嵌套套舉例SQL>SELECT last_name,2NVL(TO_CHAR(manager_id),'NoManager')3FROM s_emp4WHERE manager_idISNULL;查詢員工表中中manager_id為空的員工查查詢出來，并并將空列的值值置為“NoManager”：小結(jié)字符函數(shù)日期函數(shù)數(shù)值函數(shù)轉(zhuǎn)換函數(shù)第四章關(guān)聯(lián)聯(lián)查詢本章目標在一張或多張張表中使用等等值或非等值值連接使用外連接查查詢自連接查詢等值連接的種種類等值連接非等值連接外連接自連接S_EMPTableIDLAST_NAMEDEPT_ID------------------------1Velasquez 502Ngao 413Nagayama314Quick-To-See105Ropeburn506Urguhart417Menchu 428Biri 439Catchpole 4410Havel 4511Magee 3112Giljum3213Sedeghi3314Nguyen3415Dumas 3516Maduro41表間的關(guān)系S_DEPTTableIDNAMEREGION_ID--------------------------30Finance131Sales132Sales243Operations350Administration1S_REGIONTableIDNAME-----------------------1NorthAmerica2SouthAmerica3Africa/MiddleEast4Asia5Europe簡單關(guān)聯(lián)查詢詢的語法查詢員工表中中l(wèi)ast_name為’Biri’的員工的last_name與部門名稱查查詢出來：SELECTtable.column,table.columnFROMtable1,table2WHEREtable1.column1=table2.column2SQL>SELECTe.last_name,2FROMs_empe,s_deptd3WHEREe.dept_id=d.idande.last_name=‘Biri’非等值連接SQL>SELECTe.ename,e.job,e.sal,s.grade2 FROMempe,salgrades3 WHEREe.salBETWEENs.losalANDs.hisal;自連接S_EMP(WORKER)S_EMP(MANAGER)LAST_NAMEMANAGER_ID IDLAST_NAME--------------------- ----------Ngao1 1 Velasquez

Nagayama 11 Velasquez

Ropeburn 1 1VelasquezUrguhart2 2 Ngao

Menchu2 2NgaoBiri 22 Ngao

Magee 33 NagaymaGiljum 33 Nagayma......Server自連接查詢員工表中中l(wèi)ast_name為’Biri’的員工的last_name及其部門經(jīng)理理名稱查詢出出來：SQL>SELECTworker.last_name||'worksfor'||manager.last_name2FROMs_empworker,s_empmanager3WHEREworker.manager_id=manager.id;外連接SQL>SELECTworker.last_name||'worksfor'||manager.last_name2FROMs_empworker,s_empmanager3WHEREworker.manager_id=manager.id（+）;SQL>SELECTworker.last_name||'worksfor'||manager.last_name2FROMs_empworker,s_empmanager3WHEREworker.manager_id（+）=manager.id;SQL>SELECTworker.last_name||'worksfor'||manager.last_name2FROMs_empworkerleftouterjoins_empmanager3onworker.manager_id=manager.id;內(nèi)連接SQL>SELECTe.last_name,2FROMs_empe,s_deptd3WHEREe.dept_id=d.idande.last_name=‘Biri’SQL>SELECTe.last_name,2FROMs_empeinnerjoins_deptdone.dept_id=d.id3WHEREe.last_name=‘Biri’小結(jié)等值連接非等值連接外連接自連接第五章組函函數(shù)本章目標定義及有效的的使用組函數(shù)數(shù)使用GroupBy對查詢數(shù)據(jù)分分組使用HAVING子句對分組后后的數(shù)據(jù)進行行過濾使用GroupBy的查詢語法SELECTcolumn,group_functionFROMtable[WHERE condition][GROUPBYgroup_by_expression][HAVING group_condition][ORDERBYcolumn];常用組函數(shù)AVG(DISTINCT|ALL|n)COUNT(DISTINCT|ALL|expr|*)MAX(DISTINCT|ALL|expr)MIN(DISTINCT|ALL|expr)SUM(DISTINCT|ALL|n)應(yīng)用舉例查詢s_emp表中所有員工工的平均工資資：SQL>SELECTavg（salary）2FROMs_emp查詢s_emp表中各個部門門員工的平均均工資及部門門名稱：SQL>SELECTe.dept_id,max(),avg(e.salary)2FROMs_empe,s_deptd3WHEREe.dept_id=d.id4GROUPBYdept_id；應(yīng)用舉例查詢s_emp表中31部門一共有多多少員工：SQL>SELECTCOUNT(*)2FROM s_emp3WHEREdept_id=31;查詢s_emp表中銷售人員員的數(shù)量(提成率不為空空的記錄個數(shù)數(shù))：SQL>SELECTCOUNT(commission_pct)2FROM s_emp應(yīng)用舉例SQL>SELECTe.dept_id,max(),avg(e.salary),sum(salary)2FROMs_empe,s_deptd3WHEREe.dept_id=d.id4GROUPBYdept_id5ORDERBYsum(salary);查詢s_emp表中各個部門門員工的平均均工資,工資總和及部部門名稱并按按照工資總和和排序：應(yīng)用舉例SQL>SELECTe.dept_id,max(),avg(e.salary),sum(salary)2FROMs_empe,s_deptd3WHEREe.dept_id=d.idande.dept_id!=414GROUPBYdept_id5ORDERBYsum(salary);查詢s_emp表中除41部門以外的部門員工的的平均工資,工資總和及部部門名稱并按按照工資總和和排序：SQL>SELECTe.dept_id,max(),avg(e.salary),sum(salary)2FROMs_empe,s_deptd3WHEREe.dept_id=d.id4GROUPBYdept_id5HAVINGe.dept_id!=416ORDERBYsum(salary);應(yīng)用舉例求平均工資高高于1500的部門的工資資總和，最高高工資，最低低工資：SQL>SELECTe.dept_id,max(),avg(e.salary),sum(e.salary)2FROMs_empe,s_deptd3WHEREe.dept_id=d.id4GROUPBYdept_id5HAVINGavg(e.salary)>15006ORDERBYavg(e.salary);課堂練習(xí)求不以“VP”開頭職位的，，各個職位中中工資總和大大于5000的職位及工資資總和，并按按工資總和排排序：SQL>SELECTtitle,SUM(salary)PAYROLL2FROMs_emp3WHEREtitleNOTLIKE'VP%'4GROUPBYtitle5HAVINGSUM(salary)>50006ORDERBYSUM(salary);小結(jié)SELECTcolumn,group_functionFROMtable[WHEREcondition][GROUPBYgroup_by_expression][HAVINGgroup_condition][ORDERBY column];第六章子子查詢子查詢查詢s_emp表中工資最最低的員工工的姓名：：SQL>SELECTmin（salary）2FROMs_empSQL>SELECTlast_name2FROMs_emp3WHEREsalary=最小小工工資資（（上上一一條條的的運運行行結(jié)結(jié)果果））SQL>SELECTlast_name2FROMs_emp3WHEREsalary=(SELECTmin(salary)FROMs_emp)子查查詢詢查詢詢s_emp表中中平平均均工工資資低低于于32部門門的的部部門門ID：SQL>SELECTavg（salary）2FROMs_emp3WHEREdept_id=32;SQL>SELECTdept_id,avg(salary)2FROMs_emp3GROUPBYdept_id4HAVINGavg(salary)<32部門門的的平平均均工工資資SQL>SELECTdept_id,avg(salary)2FROMs_emp3GROUPBYdept_id4HAVINGavg(salary)<(SELECTavg(salary)5FROMs_emp6WHEREdept_id=32)7ORDERBYDEPT_ID;子查查詢詢查詢詢s_emp表中中平平均均工工資資低低于于32部門門的的部部門門ID及名名稱稱：：SQL>SELECTdept_id,avg(salary),name2FROMs_emp,s_dept3WHEREs_emp.dept_id=s_dept.id4GROUPBYdept_id,name5HAVINGavg(salary)<(SELECTavg(salary)6FROMs_emp7WHEREdept_id=32)8ORDERBYDEPT_ID;子查查詢詢舉舉例例SQL>SELECTlast_name,first_name,title2FROMs_emp3WHEREdept_in=4(SELECTID5FROMs_dept6WHEREname='Finance'7ORregion_id=2);ORA-01427:single-rowsubqueryreturnsmorethanonerow小結(jié)結(jié)SELECTselect_listFROMtableWHEREexproperator(SELECTselect_listFROMtable);第七七章章數(shù)數(shù)據(jù)據(jù)建建模模及及數(shù)數(shù)據(jù)據(jù)庫庫設(shè)設(shè)計計本章章目目標標了解解系系統(tǒng)統(tǒng)開開發(fā)發(fā)的的步步驟驟數(shù)據(jù)據(jù)關(guān)關(guān)系系的的定定義義理解解實實體體關(guān)關(guān)系系映映射射圖圖（（E-R圖））系統(tǒng)統(tǒng)開開發(fā)發(fā)步步驟驟StrategyandAnalysisDesignBuildandDocumentTransitionProduction數(shù)據(jù)據(jù)模模型型Modelofsysteminclient'smindEntitymodelofclient'smodelTablemodelofentitymodelTablesondiskServerEntityRelationshipModelingConceptsEntityAthingofsignificanceaboutwhichinformationneedstobeknownExamples:customers,salesrepresentatives,ordersAttributeSomethingthatdescribesorqualifiesanentityExamples:name,phone,identificationnumberRelationshipAnassociationbetweentwoentitiesExamples:ordersanditems,customersandsalesrepresentativesEntityRelationshipModelCreateanentityrelationshipdiagramfrombusinessspecificationsornarratives.Scenario"...Assignoneormorecustomerstoasalesrepresentative...""...Somesalesrepresentativesdonotyethaveassignedcustomers..."CUSTOMER#* id* nameo phoneEMPLOYEE#* id* lastnameo firstnameassignedtothesalesreptoRelationshipTypesOne-to-oneHaveadegreeofoneandonlyoneinbothdirections.Arerare.Example:Husbandandwife.Many-to-oneHaveadegreeofoneormoreinonedirectionandadegreeofoneandonlyoneintheotherdirection.Areverycommon.Example:passengersandplane.Many-to-manyHaveadegreeofoneormoreinbothdirections.Areresolvedwithanintersectionentity.Example:Employeesandskills.數(shù)據(jù)庫3范式數(shù)據(jù)庫中中的每一一列都是是不可再再分的基基本數(shù)據(jù)據(jù)項，同同一列中中不能有有多個值值數(shù)據(jù)庫表表中不存存在非關(guān)關(guān)鍵字段段對任何何候選關(guān)關(guān)鍵字段段的部分分依賴數(shù)據(jù)庫表表中不存存在非關(guān)關(guān)鍵字段段對任何何候選關(guān)關(guān)鍵字段段的傳遞遞引用約束類型型PK——PrimaryKey唯一且非非空FK——ForeignKey外鍵約束束，值引引用另一一張表已經(jīng)存在在的數(shù)據(jù)據(jù)UK——UniqueKey唯一且可可為空NOTNULL非空約束舉例例IDLAST_NAMEFIRST_NAME...DEPT_ID...1 VelasquezCarmen502 NgaoLaDoris413 Nagayama Midori314 Quick-To-SeeMark105 Ropeburn Audry50IDNAME REGION_ID10Finance 131Sales141Operations 150Administration1PrimaryKeyForeignKeyS_EMPTableS_DEPTTablePrimaryKey第八章創(chuàng)創(chuàng)建表表本章目標標掌握創(chuàng)建建表的語語法Oracle的數(shù)據(jù)類類型使用約束束數(shù)據(jù)結(jié)構(gòu)構(gòu)一個Oracle數(shù)據(jù)庫包包含下列列幾種數(shù)數(shù)據(jù)結(jié)構(gòu)構(gòu)：——Table存儲數(shù)據(jù)據(jù)——View從一個表表或多個個表的數(shù)數(shù)句中得得到的子子集——Sequence生成主鍵鍵值——Index提高查詢詢性能建表語法法CREATETABLE[schema.]table(columndatatype[DEFAULTexpr][column_constraint],...[table_constraint]);Oracle的數(shù)據(jù)類類型Char(size)定長字符符型，字字符長度度不夠自自動在右邊加空格符符號Varchar2(size)可變長字字符型，，大小必必須指定定Number(m,n)數(shù)字型，，可存放放實數(shù)和和整數(shù)Date日期類型型Blob2進制大對對象其最最大大小小為4GB此數(shù)據(jù)類類型映射射到Byte類型的Array。Clob2進制大對對象其最最大大小小為4GB此數(shù)據(jù)類類型映射射到String命名規(guī)范范必須以字字母開頭頭1-30個字符長長度只允許包包含A–Z,a–z,0–9,_,$,and#在一個數(shù)數(shù)據(jù)庫保保證命名名的唯一一不能使用用Oracle內(nèi)部的關(guān)關(guān)鍵字建表舉例例CREATETABLEMytest(idnumber，namevarchar2(32));CREATETABLEMytest(idnumberdeault11，namevarchar2(32));建表使用用約束舉舉例CREATETABLEMytest(idnumbercheck(id>10),namevarchar2(32));CREATETABLEMytest(idnumbercheck(id>10),namevarchar2(32)notnull);CREATETABLEMytest(idnumberUNIQUE,namevarchar2(32)notnull);建表主主鍵約約束舉舉例CREATETABLEMytest(idnumberprimarykey,namevarchar2(32)notnull);CREATETABLEMytest(idnumber,namevarchar2(32)notnull,primarykey(id));CREATETABLEMytest(mnumber,nnumber,primarykey(m,n));建表外外鍵約約束舉舉例CREATETABLEparent(idnumberprimarykey,namevarchar2(32));CREATETABLEchild(idnumberprimarykey,p_idnumberreferencesparent(id));CREATETABLEchild(idnumberprimarykey,p_idnumber,foreignkey(p_id)referencesparent(id));約束命命名CREATETABLEchild(idnumberconstraintmytest_pkprimarykey,p_idnumber,foreignkey(p_id)referencesparent(id));查看表表的約約束SELECT*FROMuser_constraintsWHEREtable_name='CHILD'CONSTRAINT_TYPEC--checkP--primarykeyR--forgienkeyU--unique級聯(lián)刪刪除CREATETABLEchild(idnumberprimarykey,p_idnumberreferencesparent(id)ondeletecascade);CREATETABLEchild(idnumberprimarykey,p_idnumberreferencesparent(id)ondeletesetnull);子查詢詢創(chuàng)建建表CREATETABLEemp_41as(select*froms_empwheredept_id=41);第九章章對對數(shù)據(jù)據(jù)的操操作本章目目標在已創(chuàng)創(chuàng)建表表中插插入新新的數(shù)數(shù)據(jù)修改已已經(jīng)存存在的的數(shù)據(jù)據(jù)刪除表表中的的數(shù)據(jù)據(jù)理解事事物控控制及及其重重要性性DML命令DescriptionAddsanewrowtothetable.Modifiesexistingrowsinthetable.Removesexistingrowsfromthetable.Makesallpendingchangespermanent.Allowsarollbacktothatsavepointmarker.Discardsallpendingdatachanges.CommandINSERTUPDATEDELETECOMMITSAVEPOINTROLLBACKInsert插入語法INSERTINTOtable[(column[,column...])]VALUES(value[,value...]);插入舉例CREATETABLEMytest(idnumberprimarykey,namevarchar2(32),birthDate);INSERTINTOmytest(id,name,brith)VALUES(1,’BluesWang’’,’?’);更新語法UPDATEtableSETcolumn=value[,column=value][WHEREcondition];刪除語法DELETE[FROM] table[WHERE condition];DatabaseTransactionsContainoneofthefollowingstatements:DMLcommandsthatmakeuponeconsistentchangetothedataOneDDLcommandOneDCLcommandBeginwhenthefirstexecutableSQLcommandisexecuted.Endwithoneofthefollowingevents:COMMITorROLLBACKDDLorDCLcommandexecutes(automaticcommit)Errors,exit,orsystemcrash還原點SQL>UPDATE...SQL>SAVEPOINTupdate_done;Savepointcreated.SQL>INSERT...SQL>ROLLBACKTOupdate_done;Rollbackcomplete.ControllingTransactionsCOMMITROLLBACKINSERTUPDATEINSERTDELETESavepointMarkerASavepointMarkerBROLLBACKROLLBACKtoAROLLBACKtoB第十章修修改表結(jié)構(gòu)構(gòu)及添加約約束本章目標增加或者修修改列添加刪除約約束刪除表刪除表所有有的數(shù)據(jù)添加列ALTERTABLEtableADD(columndatatype[DEFAULTexpr][NOTNULL][,columndatatype]...);添加列舉例例向下表mytest添加名為age，類型為number的一列：ALTERTABLEmytestADD(agenumber);再向下表mytest添加名為salary,類型為number(9,1)缺省值為8888的一列：ALTERTABLEmytestADD(salarynumber(9,1)default8888);刪除列ALTERTABLEtableDROPcolumn[,column]...;刪除列舉例例將表mytest的salary列刪除：ALTERTABLEmytestdropcolumnsalary;修改列ALTERTABLEtableMODIFY (columndatatype[DEFAULTexpr][NOTNULL][,columndatatype]...);修改列舉例例將表mytest的age列由原來的的number類型更改為為number（4，2）類型：ALTERTABLEmytestmodify(agenumber(4,2));添加約束SQL>ALTERTABLEtable2ADD[CONSTRAINTconstraint]type(column);添加約束舉舉例將表mytest的id列添加主鍵鍵約束：ALTERTABLEmytestADDconstraintsmytest_pkprimarykey(id);將表child的p_id列添加外鍵鍵約束：ALTERTABLEchildADDconstraintsc_fkforeignkey(p_id)referencesparent(id);刪除約束舉舉例SQL>ALTERTABLEchild2DROPCONSTRAINTc_fk;刪除表及表表的重命名名DROPTABLEtable[CASCADECONSTRAINTS];SQL>RENAME表名TO新名;刪除表數(shù)據(jù)據(jù)SQL>TRUNCATETABLE表名;小結(jié)CommandCREATETABLEALTERTABLEDROPTABLERENAMETRUNCATEDescriptionCreatesatableandindicatedconstraints.Modifiestablestructuresandconstraints.Removestherowsandtablestructure.Changesthenameofatable,view,sequence,orsynonym.Removesallrowsfromatableandreleasesthestoragespace.第十一章創(chuàng)創(chuàng)建序列列本章目標掌握如何使使用sequences創(chuàng)建sequences修改sequences刪除sequences創(chuàng)建sequences語法CREATESEQUENCEname[INCREMENTBYn][STARTWITHn][{MAXVALUEn|NOMAXVALUE}][{MINVALUEn|NOMINVALUE}][{CYCLE|NOCYCLE}][{CACHEn|NOCACHE}]創(chuàng)建序列舉舉例SQL>CREATESEQUENCEs_dept_id2INCREMENTBY13STARTWITH514MAXVALUE99999995NOCACHE6NOCYCLE;Sequencecreated.序列舉例利用創(chuàng)建好好的Sequence向mytest表中添加數(shù)數(shù)據(jù)：insertintomytestvalues(s.nextval,'a')查看當前序序列的值：：SELECTs.currvalFROMdual修改sequences語法AL