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

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

<表名>SQL>SELECT*FROMs_emp請查詢出s_emp表中所有的員工信息:查詢指定列SQL>SELECTdept_id,salaryFROMs_emp請查詢出s_emp表中所有的員工的部門ID，工資:運(yùn)用算數(shù)表達(dá)式SQL>SELECTsalary*12FROMs_emp請查詢出s_emp表中所有的員工的年薪:運(yùn)用算數(shù)表達(dá)式括號可以改變運(yùn)算符運(yùn)算的優(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>SELECT

DISTINCTdept_id,title2FROM s_emp;請查詢出s_emp表中所有的員工的部門ID及職稱:小結(jié)查詢表的全部記錄查詢指定的列給列起別名NVL函數(shù)及字符串連接符重復(fù)行的處理DistinctSELECT

[DISTINCT]{*,column[alias],...}FROM

table;引言第二章條件查詢本章目標(biāo)WHERE條件查詢在查詢中使用表達(dá)式、運(yùn)算符使用LIKE、BETWEEN、IN進(jìn)行模糊查詢第一章內(nèi)容回顧對員工表中信息進(jìn)行查詢，具體要求如下：

1.查詢s_emp表要求輸出員工姓名(firs_name、last_name)和實際工資(基本工資+提成)：查詢基本語法SELECT<列名>FROM

<表名>[WHERE<查詢條件表達(dá)式>]列名稱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_emp WHEREsalarybetween1500and2000請查詢出s_dept表中region_id為1，3的部門信息：SELECT*FROMs_deptWHEREregion_idin(1,3)WHERE條件查詢-like請查詢出s_emp表中姓中含有字母a的員工信息：SELECT*FROMs_empWHERElast_namelike'%a%'請查詢出當(dāng)前用戶下所有以‘s_’開頭的表：SELECTtable_name FROMuser_tables WHEREtable_namelike'S\_%'escape'\'請查詢出s_emp表姓中第二個字母為a的員工信息：SELECT*FROMs_empWHERElast_namelike‘_a%'空值的查詢查詢出s_emp表中非銷售職位的員工信息：

SELECT*FROMs_emp

WHEREcommission_pctis

null

課堂練習(xí)已建立好的S_emp表，對此表中的數(shù)據(jù)進(jìn)行模糊查詢，具體要求如下：查詢一名last_name以“M”開頭的員工，他的dept_id好像是’3X’查詢工資在1200至1500之間的員工查詢來自部門ID為(41，42，43)的員工查詢結(jié)果排序查詢出s_emp表將部門ID為41的員工的工資按從高到低排列顯示出來：

SELECT*FROMs_empWHEREdept_id=41

ORDERBYsalaryDESC

SQL>SELECT last_name,dept_id,salary

2FROM s_emp

3ORDERBY dept_id,salaryDESC;小結(jié)Where條件查詢Between…and&In&Like模糊查詢對查詢結(jié)果排序SELECT [DISTINCT]{*,column[alias],...}FROM table[WHERE condition(s)][ORDERBY {column,expr,alias}[ASC|DESC]];第三章單行函數(shù)本章目標(biāo)熟悉各種類型單行函數(shù)的使用掌握轉(zhuǎn)換函數(shù)的使用兩種SQL函數(shù)單行函數(shù)CharacterNumberDateConversionSINGLE-ROWFUNCTION多行函數(shù)GroupMULTI-ROWFUNCTION字符函數(shù)LOWER 將字符串轉(zhuǎn)換成小寫UPPER 將字符串變?yōu)榇髮慖NITCAP 將字符串的第一個字母變?yōu)榇髮慍ONCAT 拼接兩個字符串，與||相同SUBSTR 取字符串的子串LENGTH 以字符給出字符串的長度NVL 以一個值來替換空值字符函數(shù)舉例LOWER('SQLCourse') sqlcourseUPPER('SQLCourse') SQLCOURSEINITCAP('SQLCourse') SqlCourse

SELECT*FROMs_empWHERElast_name=‘PATEL’

SELECT*FROMs_empWHEREUPPER(last_name)=‘PATEL’字符操作函數(shù)CONCAT('Good','String') GoodStringSUBSTR('String',1,3) StrLENGTH('String') 6數(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ù)MONTHS_BETWEEN(date2,date1) 給出Date2-date1的月數(shù)ADD_MONTHS 增加或減去月份NEXT_DAY

(date,’day’)

給出日期date之后下一天的日期LAST_DAY(date) 返回日期所在月的最后一天日期函數(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ù)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ù)TO_CHAR(date,'fmt')

轉(zhuǎn)換日期格式到字符串

用下列格式顯示字符為數(shù)字： 9 代表一個數(shù)字. 0 顯示前綴零. $ 根據(jù)本地語言環(huán)境顯示貨幣. L 采用當(dāng)?shù)刎泿欧?. 打印一個小數(shù)點(diǎn). , 千位計算法顯示.日期格式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當(dāng)前年份的后兩位數(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>SELECT

last_name,TO_CHAR(start_date,2

'fmDdspth"of

"MonthYYYY

fmHH:MI:SSAM')HIREDATE

3

FROM s_emp

4

WHERE

start_dateLIKE'%91';SQL>SELECT

'Order‘

||

TO_CHAR(id)

||2 'wasfilledforatotalof‘

||

TO_CHAR(total,'fm$9,999,999')

3FROM

s_ord

4WHERE

ship_date='21-SEP-92';To-char舉例查詢員工表中入職日期在7月份的員工信息：SELECT*FROMs_empWHEREto_char(start_date,'mm')=07RR日期格式CurrentYear1995199520012001SpecifiedDate27-OCT-9527-OCT-1727-OCT-1727-OCT-95RRFormat1995201720171995YYFormat1995191720172095Ifthespecifiedtwo-digityearisIftwodigitsofthecurrent

yearare0-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,2

NVL(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)查詢本章目標(biāo)在一張或多張表中使用等值或非等值連接使用外連接查詢自連接查詢等值連接的種類等值連接非等值連接外連接自連接S_EMPTableIDLAST_NAME DEPT_ID------------------------1Velasquez 502Ngao 413Nagayama 314Quick-To-See 105Ropeburn 506Urguhart 417Menchu 428Biri 439Catchpole 4410Havel 4511Magee 3112Giljum 3213Sedeghi 3314Nguyen 3415Dumas 3516Maduro 41表間的關(guān)系S_DEPTTableIDNAMEREGION_ID--------------------------30Finance131Sales132Sales243Operations350Administration1S_REGIONTableIDNAME-----------------------1NorthAmerica2SouthAmerica3Africa/MiddleEast4Asia5Europe簡單關(guān)聯(lián)查詢的語法查詢員工表中l(wèi)ast_name為’Biri’的員工的last_name與部門名稱查詢出來：SELECT

table.column,table.columnFROMtable1,table2WHERE

table1.column1=table2.column2SQL>SELECTe.last_name,

2

FROM

s_empe,s_deptd

3

WHERE

e.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_NAME MANAGER_ID ID LAST_NAME

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

Ngao 1 1 Velasquez

Nagayama 1 1 Velasquez

Ropeburn 1 1 VelasquezUrguhart 2 2 Ngao

Menchu 2 2 Ngao

Biri 2 2 Ngao

Magee 3 3 Nagayma

Giljum 3 3 Nagayma

... ...Server自連接查詢員工表中l(wèi)ast_name為’Biri’的員工的last_name及其部門經(jīng)理名稱查詢出來：SQL>SELECT

worker.last_name||'worksfor'||manager.last_name

2FROM

s_empworker,s_empmanager

3WHERE

worker.manager_id=manager.id;外連接SQL>SELECT

worker.last_name||'worksfor'||manager.last_name

2FROM

s_empworker,s_empmanager

3WHERE

worker.manager_id=manager.id

（+）;SQL>SELECT

worker.last_name||'worksfor'||manager.last_name

2FROM

s_empworker,s_empmanager

3WHERE

worker.manager_id（+）=manager.id;SQL>SELECT

worker.last_name||'worksfor'||manager.last_name

2

FROM

s_empworker

leftouterjoin

s_empmanager

3

on

worker.manager_id=manager.id;內(nèi)連接SQL>SELECTe.last_name,

2

FROM

s_empe,s_deptd

3

WHERE

e.dept_id=d.idande.last_name=‘Biri’SQL>SELECTe.last_name,

2

FROM

s_empeinnerjoins_deptdone.dept_id=d.id3WHEREe.last_name=‘Biri’小結(jié)等值連接非等值連接外連接自連接第五章組函數(shù)本章目標(biāo)定義及有效的使用組函數(shù)使用GroupBy對查詢數(shù)據(jù)分組使用HAVING子句對分組后的數(shù)據(jù)進(jìn)行過濾使用GroupBy的查詢語法SELECT

column,group_functionFROM table[WHERE condition][GROUPBY

group_by_expression][HAVING group_condition][ORDERBY column];常用組函數(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>SELECT

avg（salary）

2

FROM

s_emp查詢s_emp表中各個部門員工的平均工資及部門名稱：SQL>SELECTe.dept_id,max(),avg(e.salary)

2

FROM

s_empe,s_deptd3WHEREe.dept_id=d.id4GROUPBYdept_id；應(yīng)用舉例查詢s_emp表中31部門一共有多少員工：SQL>SELECT COUNT(*)2FROM s_emp3WHERE

dept_id=31;查詢s_emp表中銷售人員的數(shù)量(提成率不為空的記錄個數(shù))：SQL>SELECT COUNT(commission_pct)2FROM s_emp應(yīng)用舉例SQL>SELECTe.dept_id,

max(),avg(e.salary),sum(salary)

2

FROM

s_empe,s_deptd3WHEREe.dept_id=d.id4GROUPBYdept_id

5ORDERBYsum(salary);查詢s_emp表中各個部門員工的平均工資,工資總和及部門名稱并按照工資總和排序：應(yīng)用舉例SQL>SELECTe.dept_id,

max(),avg(e.salary),sum(salary)

2

FROM

s_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)

2

FROM

s_empe,s_deptd3WHEREe.dept_id=d.id4GROUPBYdept_id

5HAVINGe.dept_id!=416ORDERBYsum(salary);應(yīng)用舉例求平均工資高于1500的部門的工資總和，最高工資，最低工資：SQL>SELECTe.dept_id,

max(),avg(e.salary),sum(e.salary)

2

FROM

s_empe,s_deptd3WHEREe.dept_id=d.id4GROUPBYdept_id

5HAVINGavg(e.salary)>15006ORDERBYavg

(e.salary);課堂練習(xí)求不以“VP”開頭職位的，各個職位中工資總和大于5000的職位及工資總和，并按工資總和排序：SQL>SELECT

title,SUM(salary)PAYROLL2FROM

s_emp3WHERE

titleNOTLIKE'VP%'4GROUPBY

title5HAVING

SUM(salary)>50006ORDERBY

SUM(salary);小結(jié)SELECT

column,group_functionFROM table[WHERE

condition][GROUPBY

group_by_expression][HAVING

group_condition][ORDERBY column];第六章子查詢子查詢查詢s_emp表中工資最低的員工的姓名：SQL>SELECTmin（salary）

2

FROM

s_empSQL>SELECTlast_name

2

FROM

s_emp3WHEREsalary=最小工資（上一條的運(yùn)行結(jié)果）SQL>SELECTlast_name

2

FROM

s_emp3WHEREsalary=(SELECTmin(salary)FROM

s_emp)子查詢查詢s_emp表中平均工資低于32部門的部門ID：SQL>SELECTavg（salary）

2

FROM

s_emp3WHEREdept_id=32;SQL>SELECTdept_id,avg(salary)

2

FROM

s_emp3GROUPBYdept_id4HAVINGavg(salary)<32部門的平均工資SQL>SELECTdept_id,avg(salary)

2

FROM

s_emp3GROUPBYdept_id

4HAVING

avg(salary)<(SELECTavg(salary)

5

FROM

s_emp6 WHEREdept_id=32)7ORDERBYDEPT_ID;子查詢查詢s_emp表中平均工資低于32部門的部門ID及名稱：SQL>SELECTdept_id,avg(salary),name

2

FROM

s_emp,s_dept3WHEREs_emp.dept_id=s_dept.id4GROUPBYdept_id,name5HAVINGavg(salary)<(SELECTavg(salary)

6

FROM

s_emp7 WHEREdept_id=32)8ORDERBYDEPT_ID;子查詢舉例SQL>SELECT last_name,first_name,title2FROM s_emp3WHERE dept_in=4 (SELECTID5 FROMs_dept6 WHEREname='Finance'7 ORregion_id=2);ORA-01427:single-rowsubqueryreturnsmore

thanonerow小結(jié)SELECT select_listFROM tableWHERE exproperator

(SELECT select_list FROM table);第七章數(shù)據(jù)建模及數(shù)據(jù)庫設(shè)計本章目標(biāo)了解系統(tǒng)開發(fā)的步驟數(shù)據(jù)關(guān)系的定義理解實體關(guān)系映射圖（E-R圖）系統(tǒng)開發(fā)步驟StrategyandAnalysisDesignBuildandDocumentTransitionProduction數(shù)據(jù)模型Modelofsysteminclient'smindEntitymodelofclient'smodelTablemodelofentitymodelTablesondiskServerEntityRelationshipModeling

ConceptsEntityAthingofsignificanceaboutwhichinformationneedstobeknownExamples: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ù)項，同一列中不能有多個值數(shù)據(jù)庫表中不存在非關(guān)鍵字段對任何候選關(guān)鍵字段的部分依賴數(shù)據(jù)庫表中不存在非關(guān)鍵字段對任何候選關(guān)鍵字段的傳遞引用約束類型PK——PrimaryKey唯一且非空FK——ForeignKey外鍵約束，值引用另一張表

已經(jīng)存在的數(shù)據(jù)UK——UniqueKey唯一且可為空NOTNULL非空約束舉例 ID LAST_NAME FIRST_NAME ... DEPT_ID ... 1 Velasquez Carmen 50 2 Ngao LaDoris 41 3 Nagayama Midori 31 4 Quick-To-See Mark 10 5 Ropeburn Audry 50 ID NAME REGION_ID 10 Finance 1 31 Sales 1 41 Operations 1 50 Administration 1PrimaryKeyForeignKeyS_EMPTableS_DEPTTablePrimaryKey第八章創(chuàng)建表本章目標(biāo)掌握創(chuàng)建表的語法Oracle的數(shù)據(jù)類型使用約束數(shù)據(jù)結(jié)構(gòu)一個Oracle數(shù)據(jù)庫包含下列幾種數(shù)據(jù)結(jié)構(gòu)：

——Table存儲數(shù)據(jù)

——View從一個表或多個表的數(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日期類型Blob 2進(jìn)制大對象其最大大小為4GB

此數(shù)據(jù)類型映射到Byte類型的Array。Clob2進(jìn)制大對象其最大大小為4GB

此數(shù)據(jù)類型映射到String命名規(guī)范必須以字母開頭1-30個字符長度只允許包含A–Z,a–z,0–9,_,$,and#在一個數(shù)據(jù)庫保證命名的唯一不能使用Oracle內(nèi)部的關(guān)鍵字建表舉例CREATETABLE Mytest(idnumber，

namevarchar2(32));CREATETABLE Mytest(idnumberdeault11，

namevarchar2(32));建表使用約束舉例CREATETABLE Mytest(idnumbercheck(id>10),namevarchar2(32));CREATETABLE Mytest(idnumbercheck(id>10),namevarchar2(32)notnull);CREATETABLE Mytest(idnumberUNIQUE,namevarchar2(32)notnull);建表主鍵約束舉例CREATETABLE Mytest(idnumberprimarykey,namevarchar2(32)notnull);CREATETABLE Mytest(idnumber,namevarchar2(32)notnull,

primarykey(id));CREATETABLE Mytest(mnumber,nnumber,

primarykey(m,n));建表外鍵約束舉例CREATETABLE parent(idnumberprimarykey,namevarchar2(32));CREATETABLE child(idnumberprimarykey,p_idnumberreferencesparent(id));CREATETABLE child(idnumberprimarykey,p_idnumber,

foreignkey(p_id)referencesparent(id));約束命名CREATETABLE child(idnumberconstraintmytest_pkprimarykey,p_idnumber,foreignkey(p_id)referencesparent(id));查看表的約束SELECT*FROMuser_constraints

WHEREtable_name='CHILD'CONSTRAINT_TYPE

C

--check

P

--primarykey

R

--forgienkey

U

--unique級聯(lián)刪除CREATETABLE child(idnumberprimarykey,p_idnumberreferencesparent(id)ondeletecascade);CREATETABLE child(idnumberprimarykey,p_idnumberreferencesparent(id)ondeletesetnull);子查詢創(chuàng)建表CREATETABLE emp_41as(select*froms_empwheredept_id=41);第九章對數(shù)據(jù)的操作本章目標(biāo)在已創(chuàng)建表中插入新的數(shù)據(jù)修改已經(jīng)存在的數(shù)據(jù)刪除表中的數(shù)據(jù)理解事物控制及其重要性DML命令DescriptionAddsanewrowtothetable.Modifiesexistingrowsinthetable.Removesexistingrowsfromthetable.Makesallpendingchangespermanent.Allowsarollbacktothatsavepointmarker.Discardsallpendingdatachanges.CommandINSERTUPDATEDELETE COMMITSAVEPOINTROLLBACKInsert插入語法INSERTINTO table[(column[,column...])]VALUES (value[,value...]);插入舉例CREATETABLE Mytest(idnumberprimarykey,namevarchar2(32),birthDate);INSERTINTO mytest

(id,name,brith)VALUES (1,’BluesWang’,’?’);更新語法UPDATE tableSET column=value[,column=value][WHERE condition];刪除語法DELETE[FROM] table[WHERE condition];DatabaseTransactionsContainoneofthefollowingstatements:DMLcommandsthatmakeuponeconsistentchangetothedataOneDDLcommandOneDCLcommandBeginwhenthefirstexecutableSQLcommandisexecuted.Endwithoneofthefollowingevents:COMMITorROLLBACKDDLorDCLcommandexecutes(automaticcommit)Errors,exit,orsystemcrash還原點(diǎn)SQL>UPDATE...SQL>SAVEPOINTupdate_done;Savepointcreated.SQL>INSERT...SQL>ROLLBACKTOupdate_done;Rollbackcomplete.ControllingTransactionsCOMMITROLLBACKINSERTUPDATEINSERTDELETESavepointMarkerASavepointMarkerBROLLBACKROLLBACKtoAROLLBACKtoB第十章修改表結(jié)構(gòu)及添加約束本章目標(biāo)增加或者修改列添加刪除約束刪除表刪除表所有的數(shù)據(jù)添加列ALTERTABLEtableADD (columndatatype[DEFAULTexpr][NOTNULL] [,columndatatype]...);添加列舉例向下表mytest添加名為age，類型為number的一列：ALTERTABLEmytestADD(agenumber);再向下表mytest添加名為salary,類型為number(9,1)缺省值為8888的一列：ALTERTABLEmytestADD(salarynumber(9,1)default8888);刪除列ALTERTABLEtableDROP column

[,column]...;刪除列舉例將表mytest的salary列刪除：ALTERTABLEmytestdropcolumnsalary;修改列ALTERTABLE tableMODIFY (columndatatype[DEFAULTexpr][NOTNULL] [,columndatatype]...);修改列舉例將表mytest的age列由原來的number類型更改為number（4，2）類型：ALTERTABLEmytestmodify(agenumber(4,2));添加約束SQL>ALTERTABLE table2ADD[CONSTRAINTconstraint]type(column);添加約束舉例將表mytest的id列添加主鍵約束：ALTERTABLEmytest

ADDconstraintsmytest_pkprimarykey(id);將表child的p_id列添加外鍵約束：ALTERTABLEchildADDconstraintsc_fk

foreignkey(p_id)referencesparent(id);刪除約束舉例SQL>ALTERTABLE child

2DROPCONSTRAINT c_fk;刪除表及表的重命名DROPTABLEtable[CASCADECONSTRAINTS];SQL>RENAME表名TO新名;刪除表數(shù)據(jù)SQL>TRUNCATETABLE

表名;小結(jié)CommandCREATETABLE ALTERTABLEDROPTABLERENAME

TRUNCATE

DescriptionCreatesatableandindicatedconstraints.Modifiestablestructuresandconstraints.Removestherowsandtablestructure.Changesthenameofatable,view,sequence,orsynonym.Removesallrowsfromatableandreleasesthestoragespace.第十一章創(chuàng)建序列本章目標(biāo)掌握如何使用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_id2 INCREMENTBY13 STARTWITH514 MAXVALUE99999995 NOCACHE6 NOCYCLE;Sequencecreated.序列舉例利用創(chuàng)建好的Sequence向mytest表中添加數(shù)據(jù)：insertintomytestvalues(s.nextval,'a')查看當(dāng)前序列的值：SELECTs.currvalFROMdual修改sequences語法ALTERSEQUENCEname [INCREMENTBYn] [STARTWITHn] [{MAXVALUEn|NOMAXVALUE}] [{MINVALUEn|NOMINVALUE}] [{CYCLE|NOCYCLE}] [{CACHEn|NOCACHE}]刪除sequences語法DROPSEQUENCEname第十二章視圖、索引本章目標(biāo)掌握如何使用視圖、索引創(chuàng)建視圖、索引修改視圖、索引刪除視圖、索引IDLAST_NAMEFIRST_NAMETITLEDEPT_ID---------------------------------------------------1VelasquezCarmenPresident502NgaoLaDorisVP,Operations413NagayamaMidoriVP,Sales314Quick-To-SeeMarkVP,Finance105RopeburnAudryVP,Administration506UrguhartMollyWarehouseManager417MenchuRobertaWarehouseManager428BiriBenWarehouseManager439CatchpoleAntoinetteWarehouseManager4410HavelMartaWarehouseManager4511MageeColinSalesRepresentative3112GiljumHenrySalesRepresentative3213SedeghiYasminSalesRepresentative3314NguyenMaiSalesRepresentative3415DumasAndreSalesRepresentative3516MaduroElenaStockClerk4117SmithGeorgeStockClerk4118NozakiAkiraStockClerk4219PatelVikramStockClerk4220NewmanChadStockClerk4321MarkarianAlexanderStockClerk4322ChangEddieStockClerk4423PatelRadhaStockClerk3424DancsBelaStockClerk4525SchwartzSylvieStockClerk45什么是視圖IDLAST_NAMETITLE----------------------------10HavelWarehouseManager24DancsStockClerk25SchwartzStockClerkS_EMPTableEMPVU45View創(chuàng)建視圖的語法CREATE[ORREPLACE][FORCE|NOFORCE]VIEWview[(alias[,alias]...)]ASsubquery[WITHCHECKOPTION[CONSTRAINTconstraint]][WITHREADONLY]視圖舉例CREATEVIEWemp_41ASSelecte.first_name,,e.salaryfroms_empe,s_deptdWheree.dept_id=e.idande.dept_id=41;索引一下情況下適合建立索引：1.查詢多，增刪少2.數(shù)據(jù)量大索引的結(jié)構(gòu)NguSedeghiVelasquezGiljumNgaoIDLAST_NAME--------------1Velasquez2Ngao3Nagayama4Quick-To-See5Ropeburn6Urguhart7Menchu8Biri9Catchpole10Havel11Magee12Giljum13Sedeghi14Nguyen15Dumas16Maduro17Smith18Nozaki19Patel20Newman21Markarian22Chang23Patel24Dancs25SchwartzBiriGiljumMenchuNgaoNguyenSedeghiUrguhartVelasquezROWID索引的語法CREATEINDEXindexONtable(column[,column]...);索引舉例SQL>CREATEINDEX s_emp_last_name_idx2ON s_emp(last_name);Indexcreated.演講完畢，謝謝觀看！附錄資料：不需要的可以自行刪除?oracle數(shù)據(jù)庫培訓(xùn)內(nèi)容*基本概念*Oracle的體系結(jié)構(gòu)SQL*Plus的使用及常用命令*SQL*NET的配置和使用數(shù)據(jù)庫的備份(exp/imp)其它常見問題?一、基本概念什么是數(shù)據(jù)庫：是存儲在一起的相關(guān)數(shù)據(jù)的集合。這些數(shù)據(jù)無有害或不必要的冗余，為多種應(yīng)用服務(wù)。數(shù)據(jù)的存儲獨(dú)立于使用它的應(yīng)用程序。對數(shù)據(jù)庫插入新數(shù)據(jù)、修改和檢索原有數(shù)據(jù)均能用一種公用的和可控的方法進(jìn)行。數(shù)據(jù)庫能做什么：數(shù)據(jù)庫的基本功能就是存儲數(shù)據(jù)，而且能夠為用戶提供便捷、快速的查找、修改、添加和刪除數(shù)據(jù)等功能。什么是SQL語言：是運(yùn)用于數(shù)據(jù)庫的語言。它除了具有數(shù)據(jù)庫查詢、插入、刪除、修改等功能外，可以獨(dú)立使用，也可以嵌入其它主語言中使用。它的語法類似于英語。?什么是oracle：oracle是以高級結(jié)構(gòu)化查詢語言(SQL)為基礎(chǔ)的大型關(guān)系數(shù)據(jù)庫，通俗地講它是用方便邏輯管理的語言操縱大量有規(guī)律數(shù)據(jù)的集合。是目前最流行的客戶/服務(wù)器(CLIENT/SERVER)體系結(jié)構(gòu)的數(shù)據(jù)庫之一。?二、Oracle的體系結(jié)構(gòu)(1)物理結(jié)構(gòu)

ORACLE數(shù)據(jù)庫在物理上是存儲于硬盤的各種文件。它是活動的，可擴(kuò)充的，隨著數(shù)據(jù)的添加和應(yīng)用程序的增大而變化。datafilesredologfilescontrolfilesparameterfile

數(shù)據(jù)文件日志文件控制文件參數(shù)文件

datafilesredologfilescontrolfileparameterfiles*.dbf*.logInitoraid.ora*.ctl?(2)session(連接)

Oracle是多用戶、多任務(wù)、可分布式管理的數(shù)據(jù)庫，同

時可有許多個用戶對數(shù)據(jù)庫操作。oracleuseruserusersession……?

ORACLE數(shù)據(jù)庫在邏輯上是由許多表空間構(gòu)成。主要分為系統(tǒng)表空間和非系統(tǒng)表空間。非系統(tǒng)表空間內(nèi)存儲著各項應(yīng)用的數(shù)據(jù)、索引、程序等相關(guān)信息。我們準(zhǔn)備上馬一個較大的ORACLE應(yīng)用系統(tǒng)時，應(yīng)該創(chuàng)建它所獨(dú)占的表空間，同時定義物理文件的存放路徑和所占硬盤的大小。Oracle數(shù)據(jù)存儲單位a、block數(shù)據(jù)塊：2k最小的I-O單位，伴隨database產(chǎn)生而產(chǎn)生，不可變b、extent一組連續(xù)的數(shù)據(jù)塊：是用戶所能分配存儲的最小單位c、segment段：有共同結(jié)構(gòu)的一個或幾個區(qū)域(extent)d、tablespace表空間：一組物理數(shù)據(jù)的邏輯組合，(象邏輯間數(shù)據(jù)倉庫)e、file文件：屬于某個表空間的物理文件f、database數(shù)據(jù)庫：一組表空間所構(gòu)成的邏輯的可共享的數(shù)據(jù)。(3)存儲結(jié)構(gòu)?表空間tablespacesystemRBSnosystem把system表空間單獨(dú)放在一個硬盤上做成一組鏡像toolsuserstempapplicationdataapplicationindex分開存放減少爭用?segment段的分類data數(shù)據(jù)段存儲對象object(table,view,index,sequence...)index索引段temporary臨時段用做(join,groupby,orderbysorting操作)rollback回滾段用于記錄修改前后信息bootstrap啟動段存儲數(shù)據(jù)字典系統(tǒng)信息不能讀寫，放在系統(tǒng)表空間內(nèi)，約占40幾個block?datasegment中可能有十種不同類型的存儲對象(1)table(2)view實為一個select語句(3)index(一個表不多于3個)(4)cluster(簇)(5)sequence序列(6)synonyms用于定義某個遠(yuǎn)程數(shù)據(jù)庫同義詞，實現(xiàn)分式數(shù)據(jù)庫管理透明(7)snapsot快照(8)storedprocedure(function)(9)package程序包(10)dbtrigger數(shù)據(jù)觸發(fā)器，處理updata,delete,insert中可能出現(xiàn)的問題?常用的ORACLE的數(shù)據(jù)庫字段類型字段類型中文說明限制條件其它說明CHAR固定長度字符串最大長度2000bytesVARCHAR2可變長度的字符串最大長度4000bytes可做索引的最大長度749DATE日期（日-月-年）LONG超長字符串最大長度2G（231-1）足夠存儲大部頭著作RAW固定長度的二進(jìn)制數(shù)據(jù)最大長度2000bytes可存放多媒體圖象聲音等LONGRAW可變長度的二進(jìn)制數(shù)據(jù)