SQL編程及高級(jí)查詢(xún)

第七章SQL編程及高級(jí)查詢(xún)第二單元本章相關(guān)學(xué)習(xí)資源《SQL語(yǔ)言和常用函數(shù)》專(zhuān)題《高級(jí)查詢(xún)》專(zhuān)題《Web前端基礎(chǔ)及數(shù)據(jù)庫(kù)開(kāi)發(fā)》第7章《SQL編程及高級(jí)查詢(xún)》學(xué)習(xí)平臺(tái)“Oracle數(shù)據(jù)庫(kù)”課程學(xué)生用書(shū)第2頁(yè)/共47頁(yè)預(yù)習(xí)檢查例舉數(shù)據(jù)操縱語(yǔ)言中常用的SQL命令例舉Oracle中常用的函數(shù)簡(jiǎn)述查詢(xún)的分類(lèi)第3頁(yè)/共47頁(yè)本章任務(wù)任務(wù)1：使用SQL操作學(xué)生表任務(wù)2：使用SQL函數(shù)對(duì)表進(jìn)行操作任務(wù)3：查詢(xún)雇員相關(guān)信息第4頁(yè)/共47頁(yè)會(huì)使用DML語(yǔ)言對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作能夠熟練運(yùn)用Oracle常用函數(shù)能夠熟練運(yùn)用子查詢(xún)能夠熟練運(yùn)用連接查詢(xún)能夠熟練運(yùn)用查詢(xún)實(shí)現(xiàn)分頁(yè)顯示功能本章目標(biāo)點(diǎn)重點(diǎn)難點(diǎn)難點(diǎn)重點(diǎn)難第5頁(yè)/共47頁(yè)串講：SQL簡(jiǎn)介SQL是StructuredQueryLanguage（結(jié)構(gòu)化查詢(xún)語(yǔ)言）的首字母縮寫(xiě)詞SQL是數(shù)據(jù)庫(kù)語(yǔ)言，Oracle使用該語(yǔ)言存儲(chǔ)和檢索信息表是主要的數(shù)據(jù)庫(kù)對(duì)象，用于存儲(chǔ)數(shù)據(jù)SELECTename

FROMEmp;發(fā)送SQL查詢(xún)Oracle服務(wù)器enameBLAKESMITH

ALLEN

DAVID

MARTIN發(fā)送命令輸出到用戶(hù)端用戶(hù)通過(guò)SQL可以實(shí)現(xiàn)與Oracle服務(wù)器的通信第6頁(yè)/共47頁(yè)串講：SQL簡(jiǎn)介SQL支持下列類(lèi)別的命令：數(shù)據(jù)定義語(yǔ)言（DDL）數(shù)據(jù)操縱語(yǔ)言（DML）事務(wù)控制語(yǔ)言（TCL）數(shù)據(jù)控制語(yǔ)言（DCL）數(shù)據(jù)定義語(yǔ)言CREATEALTERDROP數(shù)據(jù)操縱語(yǔ)言INSERTSELECTDELETEUPDATE事務(wù)控制語(yǔ)言COMMITSAVEPOINTROLLBACK數(shù)據(jù)控制語(yǔ)言GRANTREVOKE第7頁(yè)/共47頁(yè)串講：數(shù)據(jù)操縱語(yǔ)言數(shù)據(jù)操縱語(yǔ)言用于檢索、插入和修改數(shù)據(jù)數(shù)據(jù)操縱語(yǔ)言是最常見(jiàn)的SQL命令數(shù)據(jù)操縱語(yǔ)言命令包括：INSERTUPDATEDELETESELECT第8頁(yè)/共47頁(yè)串講：INSERT

命令CREATETABLEstuInfo(stuNoCHAR(6)NOTNULL,--學(xué)號(hào)，非空stuNameVARCHAR2(20)NOTNULL,--學(xué)員姓名，非空stuAgeNUMBER(3,0)NOTNULL,--年齡，非空beginDateDATE--開(kāi)始學(xué)習(xí)時(shí)間，日期類(lèi)型);INSERTINTOstuInfo(stuNo,stuName,stuAge)VALUES('034','Tom',23);INSERTINTOstuInfoVALUES('032','Ford',22,SYSDATE);INSERTINTOstuInfoSELECTstuNo,stuName,stuAge,beginDateFROMstuInfoBAK;插入數(shù)據(jù)插入來(lái)自其他表的記錄,其中stuInfoBAK為stuInfo備份表已知學(xué)生信息表stuInfo,如何向表中存入學(xué)生信息？第9頁(yè)/共47頁(yè)串講：UPDATE命令--UPDATE單列UPDATEstuInfoSETstuAge=26WHEREstuNo='035';--UPDATE多列UPDATEstuInfoSETstuage=26,beginDate='2012-09-18'WHEREstuNo='035';修改數(shù)據(jù)如何修改年齡和開(kāi)始學(xué)習(xí)時(shí)間？第10頁(yè)/共47頁(yè)串講：DELETE命令--刪除學(xué)號(hào)為035的學(xué)生記錄DELETEFROMstuInfoWHEREstuNo='035';刪除數(shù)據(jù)如何刪除學(xué)號(hào)為035的學(xué)生記錄？第11頁(yè)/共47頁(yè)如何查詢(xún)所有學(xué)生記錄？如何查詢(xún)學(xué)號(hào)為035的記錄？如何選擇指定的學(xué)號(hào)、姓名列？如何篩除重復(fù)的行？串講：SELECT命令SELECT*FROMstuInfo;SELECT*FROMstuInfoWHEREstuNo='035';

SELECTstuNo,stuNameFROMstuInfo;SELECTDISTINCTstuNo,stuNameFROMstuInfo;

第12頁(yè)/共47頁(yè)如何對(duì)結(jié)果集排序,先按照年齡降序，如果年齡相同的按照學(xué)號(hào)升序排列?如何利用現(xiàn)有的表(stuInfo)創(chuàng)建新表(stuInfoBAK)？如何使用列別名？別名中有空格如何解決？串講：SELECT命令CREATETABLEstuInfoBAKasSELECT*FROMstuInfo;SELECT‘S’||stuNo學(xué)號(hào),stuName“姓名”FROMstuInfo;SELECT*FROMstuInfoORDERBYstuAgeDESC,stuNoASC;

第13頁(yè)/共47頁(yè)演示示例：DML語(yǔ)句操作其中兩個(gè)字符串拼接用‘||’符號(hào)上機(jī)練習(xí)需求說(shuō)明針對(duì)供應(yīng)商表S，產(chǎn)品表P，供應(yīng)情況表SP插入數(shù)據(jù)。參照提供的素材《供應(yīng)商練習(xí)相關(guān)表》。用SQL語(yǔ)句完成以下查詢(xún)求供應(yīng)產(chǎn)品編號(hào)為P1的供應(yīng)商編號(hào)求供應(yīng)綠茶等級(jí)為1的供應(yīng)商編號(hào)求沒(méi)有使用北京公司供應(yīng)商的茶葉，但產(chǎn)品級(jí)別是1級(jí)茶葉的產(chǎn)品編號(hào)完成時(shí)間：10分鐘共性問(wèn)題集中講解練習(xí)第14頁(yè)/共47頁(yè)串講：SQL

函數(shù)Oracle提供一系列用于執(zhí)行特定操作的函數(shù)SQL函數(shù)帶有一個(gè)或多個(gè)參數(shù)并返回一個(gè)值以下是SQL函數(shù)的分類(lèi)：SQL函數(shù)單行函數(shù)分析函數(shù)聚合函數(shù)第15頁(yè)/共47頁(yè)串講：?jiǎn)涡泻瘮?shù)分類(lèi)單行函數(shù)對(duì)于從表中查詢(xún)的每一行只返回一個(gè)值可以出現(xiàn)在SELECT子句中和WHERE子句中單行函數(shù)可以大致劃分為：日期函數(shù)數(shù)字函數(shù)字符函數(shù)轉(zhuǎn)換函數(shù)其他函數(shù)第16頁(yè)/共47頁(yè)串講：轉(zhuǎn)換函數(shù)轉(zhuǎn)換函數(shù)將值從一種數(shù)據(jù)類(lèi)型轉(zhuǎn)換為另一種數(shù)據(jù)類(lèi)型常用的轉(zhuǎn)換函數(shù)有：TO_CHARTO_DATETO_NUMBERSELECTTO_CHAR(sysdate,'YYYY"年"MM"月"DD"日"HH24:MI:SS')FROMdual;SELECTTO_CHAR(123456.03,'099,999.99')FROMdual;

SELECTTO_DATE('2005-12-06','yyyy-mm-dd')FROMdual;SELECTTO_NUMBER('100')FROMdual;第17頁(yè)/共47頁(yè)演示示例：

函數(shù)-轉(zhuǎn)換函數(shù)其它函數(shù)轉(zhuǎn)換空值的函數(shù)NVLNVLDECODESELECTename,sal+NVL(comm,0)sal1,

NVL2(comm,sal+comm,sal)sal2,

DECODE(to_char(hiredate,‘MM’),‘

01’,‘一月’,‘02’,‘二月’,

‘03’,‘三月‘,'04','四月',

‘05','五月','06','六月',

'下半年')

mon

FROMemployee;示例18/45演示示例8：其他函數(shù)串講：聚合函數(shù)聚合函數(shù)基于一組行來(lái)返回結(jié)果為每一組行返回一個(gè)值A(chǔ)VGMINMAXSUMCOUNTSELECTSUM(sal)FROMemp;SELECTAVG(sal)FROMempWHEREjob='CLERK';SELECTMAX(sal)FROMemp;SELECTCOUNT(*)FROMemp;SELECTCOUNT(comm)FROMemp;SELECTCOUNT(DISTINCTjob)FROMemp;聚合函數(shù)第19頁(yè)/共47頁(yè)講解：GROUPBY和HAVING子句GROUPBY子句用于將信息劃分為更小的組每一組行返回針對(duì)該組的單個(gè)結(jié)果HAVING子句用于指定GROUPBY子句檢索行的條件SELECTdeptno,MAX(sal)maxSal,AVG(sal)avgSal,MIN(sal)minSal

FROMempGROUPBYdeptno;SELECTdeptno,MAX(sal)maxSal,AVG(sal)avgSal,MIN(sal)minSalFROMempGROUPBYdeptnoHAVINGAVG(sal)>2000;第20頁(yè)/共47頁(yè)演示示例：

函數(shù)-聚合函數(shù)講解：分析函數(shù)分析函數(shù)根據(jù)一組行來(lái)計(jì)算聚合值用于計(jì)算完成聚集的累計(jì)排名等分析函數(shù)為每組記錄返回多個(gè)行分析函數(shù)DENSE_RANKRANKROW_NUMBER第21頁(yè)/共47頁(yè)講解：分析函數(shù)以下三個(gè)分析函數(shù)用于計(jì)算一個(gè)行在一組有序行中的排位，序號(hào)從1開(kāi)始ROW_NUMBER返回連續(xù)的排位，不論值是否相等RANK具有相等值的行排位相同，序數(shù)隨后跳躍DENSE_RANK具有相等值的行排位相同，序號(hào)是連續(xù)的第22頁(yè)/共47頁(yè)SELECTename,job,deptno,sal,ROW_NUMBER()OVER(ORDERBYsalDESC)ASSAL_RANKFROMSCOTT.EMP;SELECTdeptno,ename,sal,comm,RANK()OVER(PARTITIONBYdeptnoORDERBYsalDESC,comm)RANKFROMemp;SELECTd.dname,e.ename,e.sal,DENSE_RANK()OVER(PARTITIONBYe.deptnoORDERBYe.salDESC)ASDENRANKFROMempe,deptdWHEREe.deptno=d.deptno;演示示例：

函數(shù)-分析函數(shù)上機(jī)練習(xí)需求說(shuō)明針對(duì)供應(yīng)情況表進(jìn)行如下操作

查詢(xún)供貨商總?cè)藬?shù)查詢(xún)一次供應(yīng)茶葉的最大斤數(shù)查詢(xún)各個(gè)供應(yīng)商編號(hào)及供應(yīng)茶葉的總斤數(shù)查詢(xún)提供了2種以上茶葉的供貨商編號(hào)完成時(shí)間：15分鐘共性問(wèn)題集中講解練習(xí)第23頁(yè)/共47頁(yè)SQL函數(shù)分為（)、()和(

)。請(qǐng)列舉聚合函數(shù)，有（）、（

）、（

）、（）等。小結(jié)單行函數(shù)聚合函數(shù)分析函數(shù)SUM

COUNT

AVGMAX第24頁(yè)/共47頁(yè)講解：查詢(xún)SELECT語(yǔ)句的作用是什么？多表查詢(xún)的類(lèi)型有哪些？子查詢(xún)-將一個(gè)查詢(xún)包含到另一個(gè)查詢(xún)中連接-合并多個(gè)數(shù)據(jù)表中的列聯(lián)合-合并多個(gè)數(shù)據(jù)表中的行第25頁(yè)/共47頁(yè)講解：子查詢(xún)?yōu)槭裁词褂米硬樵?xún)？是表達(dá)查詢(xún)最自然的方式使編寫(xiě)SELECT語(yǔ)句變得更加簡(jiǎn)單有些查詢(xún)?nèi)绻皇褂米硬樵?xún)就無(wú)法用SQL表達(dá)出來(lái)第26頁(yè)/共47頁(yè)講解：子查詢(xún)實(shí)例一EMP表中，查詢(xún)與“SCOTT”在同一個(gè)部門(mén)的雇員信息SELECTempno,ename,deptnoFROMempWHEREdeptnoIN(SELECTdeptnoFROMempWHEREename='SCOTT');執(zhí)行父查詢(xún)返回子查詢(xún)值給父查詢(xún)執(zhí)行子查詢(xún)獲得結(jié)果值實(shí)例如果將IN換成NOTIN又如何？第27頁(yè)/共47頁(yè)講解：子查詢(xún)實(shí)例二EMP表中，找出每個(gè)雇員超過(guò)他所在部門(mén)平均工資的雇員編號(hào)、雇員名稱(chēng)、薪水、部門(mén)編號(hào)返回第一步SELECTempno,ename,sal,deptnoFROMempe1WHEREsal>(SELECTAVG(sal)FROMempe2WHEREe1.deptno=e2.deptno);父查詢(xún)傳送列值給子查詢(xún)子查詢(xún)獲取父查詢(xún)傳送的列值子查詢(xún)返回查詢(xún)值給父查詢(xún)父查詢(xún)傳送下一行的列值給子查詢(xún)(重復(fù)1-3步)實(shí)例第28頁(yè)/共47頁(yè)講解：子查詢(xún)實(shí)例三查詢(xún)其他部門(mén)中比30號(hào)部門(mén)某一雇員薪水少的雇員信息SELECTempno,ename,sal,deptnoFROMempe1WHEREsal<ANY(SELECTsalFROMempWHEREdeptno=30

)

ANDdeptno<>30;實(shí)例返回多值時(shí)可用ANY或ALL修飾符其中，=ANY等價(jià)于IN，<ANY等價(jià)于<MAX,<>ALL等價(jià)于NOTIN，<ALL等價(jià)于<MIN第29頁(yè)/共47頁(yè)講解：子查詢(xún)實(shí)例四EMP表中，查詢(xún)雇員薪水大于3000的部門(mén)名稱(chēng)SELECTdnameFROMdeptdWHEREexists(SELECT*FROMempeWHEREd.deptno=e.deptnoANDsal>3000);實(shí)例（1）首先取外層查詢(xún)中表的第一個(gè)記錄，根據(jù)它與內(nèi)層查詢(xún)相關(guān)的屬性值（deptno）處理內(nèi)層查詢(xún)，若where子句返回值為真，則取外層查詢(xún)中該記錄放入結(jié)果表（2）再取外層查詢(xún)的下一個(gè)記錄，重復(fù)這一過(guò)程，直到外層查詢(xún)?nèi)繖z查完為止由于帶EXISTS的相關(guān)子查詢(xún)只關(guān)心內(nèi)層查詢(xún)是否有返回值，并不需要查具體值，有時(shí)是高效的方法第30頁(yè)/共47頁(yè)講解：子查詢(xún)的分類(lèi)不相關(guān)子查詢(xún)子查詢(xún)的查詢(xún)條件不依賴(lài)于父查詢(xún)屬于較簡(jiǎn)單的一類(lèi)查詢(xún)，通常說(shuō)的嵌套查詢(xún)屬于此類(lèi)相關(guān)子查詢(xún)子查詢(xún)的查詢(xún)條件依賴(lài)于父查詢(xún)第31頁(yè)/共47頁(yè)演示示例：查詢(xún)-子查詢(xún)串講：常用的多表連接查詢(xún)連接分類(lèi)第32頁(yè)/共47頁(yè)內(nèi)連接外連接自連接交叉連接連接左外連接右外連接等值連接自然連接不等值連接串講：內(nèi)連接內(nèi)連接使用比較運(yùn)算符根據(jù)每個(gè)表的通用列中的值匹配兩個(gè)表中的行SNameSCode梅超風(fēng)1陳玄風(fēng)2陸乘風(fēng)3曲靈風(fēng)4StudentIDCourseIDScore100197200189200267300276300381SNameCourseIDScore梅超風(fēng)00197陳玄風(fēng)00189陳玄風(fēng)00267陸乘風(fēng)00276陸乘風(fēng)00381StudentsScore查詢(xún)結(jié)果第33頁(yè)/共47頁(yè)串講：內(nèi)連接SELECT

……

FROM

表1

INNERJOIN

表2ON ……SELECT

……

FROM

表1，表2WHERE……

SELECT

S.SName,C.CourseID,C.Score

FROM

ScoreCINNERJOIN

StudentsSON

C.StudentID=S.SCode;SELECTStudents.SName,Score.CourseID,Score.ScoreFROM Students,ScoreWHEREStudents.SCode=Score.StudentID;等價(jià)第34頁(yè)/共47頁(yè)查詢(xún)學(xué)員姓名、課程和分?jǐn)?shù)演示示例：

查詢(xún)-內(nèi)連接下面的查詢(xún)語(yǔ)句返回的查詢(xún)結(jié)果是一樣的嗎？根據(jù)前面表中數(shù)據(jù)，下面的查詢(xún)語(yǔ)句將會(huì)返回多少行記錄？串講：內(nèi)連接SELECT

S.SName,C.CourseID,C.Score

FROM

StudentsSINNERJOIN

ScoreCON

C.StudentID<>S.SCode;SELECT

S.SName,C.CourseID,C.Score

FROM

StudentsSINNERJOIN

ScoreCON

C.StudentID=S.SCode;15行SELECTStudents.SName,Score.CourseID,Score.ScoreFROM Students,ScoreWHEREStudents.SCode=Score.StudentID;兩個(gè)語(yǔ)句的執(zhí)行結(jié)果相同第35頁(yè)/共47頁(yè)上機(jī)練習(xí)需求說(shuō)明查詢(xún)所有雇員姓名和部門(mén)名稱(chēng)從emp表中找到在工資等級(jí)表中對(duì)應(yīng)的工資等級(jí)工資等級(jí)表表結(jié)構(gòu)參照書(shū)中資料完成時(shí)間：8分鐘共性問(wèn)題集中講解練習(xí)第36頁(yè)/共47頁(yè)串講：左外連接SNameSCode梅超風(fēng)1陳玄風(fēng)2陸乘風(fēng)3曲靈風(fēng)4StudentIDCourseIDScore100197200189200267300276300381SNameCourseIDScore梅超風(fēng)00197陳玄風(fēng)00189陳玄風(fēng)00267陸乘風(fēng)00276陸乘風(fēng)00381曲靈風(fēng)NULLNULLStudentsScore查詢(xún)結(jié)果SELECT

S.SName,C.CourseID,C.Score

FROM

ScoreASCLEFTJOIN

StudentsASSON

C.StudentID=S.SCode不一樣，主表和從表位置已互換SELECT

S.SName,C.CourseID,C.Score

FROM

StudentsSLEFTJOIN

ScoreCON

C.StudentID=S.SCode主表（左表）Students中的數(shù)據(jù)逐條匹配從表Score中的數(shù)據(jù)1、匹配，返回到結(jié)果集2、無(wú)匹配，NULL值返回到結(jié)果集猜一猜：這樣寫(xiě)，返回的查詢(xún)結(jié)果是一樣的嗎？第37頁(yè)/共47頁(yè)演示示例：

查詢(xún)-左外連接SELECT圖書(shū)編號(hào),圖書(shū)名稱(chēng),出版社名稱(chēng)FROM圖書(shū)表RIGHTOUTERJOIN出版社表ON圖書(shū)表.出版社編號(hào)=出版社表.出版社編號(hào)串講：右外連接圖書(shū)編號(hào)圖書(shū)名稱(chēng)出版社名稱(chēng)1走進(jìn)Java編程世界北大出版社2HTML和CSS網(wǎng)頁(yè)技術(shù)清華出版社………………NULLNULL新知出版社………………也許很久沒(méi)出版書(shū)籍了右外連接的原理與左外連接相同右表逐條去匹配記錄；否則NULL填充第38頁(yè)/共47頁(yè)講解：分頁(yè)查詢(xún)什么是Oracle偽列？Oracle中偽列就像一個(gè)表列，但是它并沒(méi)有存儲(chǔ)在表中偽列可以從表中查詢(xún)，但不能插入、更新和刪除它們的值常用的偽列有哪些？ROWIDROWNUMROWID是表中行的存儲(chǔ)地址，該地址可以唯一地標(biāo)識(shí)數(shù)據(jù)庫(kù)中的一行，可以使用ROWID偽列快速地定位表中的一行ROWNUM是查詢(xún)返回的結(jié)果集中行的序號(hào)，可以使用它來(lái)限制查詢(xún)返回的行數(shù)第39頁(yè)/共47頁(yè)講解：分頁(yè)查詢(xún)方法一SELECT*FROMempWHERErownum<6ORDERBYsalDESC;方法二SELECT*FROM(SELECT*FROMempORDERBYsalDESC)WHERErownum<6;如何獲取雇員表中薪水最高的前5人？方法一為什么錯(cuò)誤？第40頁(yè)/共47頁(yè)演示示例：

查詢(xún)-分頁(yè)查詢(xún)講解：分頁(yè)查詢(xún)方法一SELECT*FROM(SELECT*FROMempORDE