SQL編程及高級查詢

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

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

ALLEN

DAVID

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

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

SELECTstuNo,stuNameFROMstuInfo;SELECTDISTINCTstuNo,stuNameFROMstuInfo;

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

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

函數(shù)Oracle提供一系列用于執(zhí)行特定操作的函數(shù)SQL函數(shù)帶有一個或多個參數(shù)并返回一個值以下是SQL函數(shù)的分類：SQL函數(shù)單行函數(shù)分析函數(shù)聚合函數(shù)第15頁/共47頁串講：單行函數(shù)分類單行函數(shù)對于從表中查詢的每一行只返回一個值可以出現(xiàn)在SELECT子句中和WHERE子句中單行函數(shù)可以大致劃分為：日期函數(shù)數(shù)字函數(shù)字符函數(shù)轉(zhuǎn)換函數(shù)其他函數(shù)第16頁/共47頁串講：轉(zhuǎn)換函數(shù)轉(zhuǎn)換函數(shù)將值從一種數(shù)據(jù)類型轉(zhuǎn)換為另一種數(shù)據(jù)類型常用的轉(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頁/共47頁演示示例：

函數(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ù)基于一組行來返回結(jié)果為每一組行返回一個值A(chǔ)VGMINMAXSUMCOUNTSELECTSUM(sal)FROMemp;SELECTAVG(sal)FROMempWHEREjob='CLERK';SELECTMAX(sal)FROMemp;SELECTCOUNT(*)FROMemp;SELECTCOUNT(comm)FROMemp;SELECTCOUNT(DISTINCTjob)FROMemp;聚合函數(shù)第19頁/共47頁講解：GROUPBY和HAVING子句GROUPBY子句用于將信息劃分為更小的組每一組行返回針對該組的單個結(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頁/共47頁演示示例：

函數(shù)-聚合函數(shù)講解：分析函數(shù)分析函數(shù)根據(jù)一組行來計算聚合值用于計算完成聚集的累計排名等分析函數(shù)為每組記錄返回多個行分析函數(shù)DENSE_RANKRANKROW_NUMBER第21頁/共47頁講解：分析函數(shù)以下三個分析函數(shù)用于計算一個行在一組有序行中的排位，序號從1開始ROW_NUMBER返回連續(xù)的排位，不論值是否相等RANK具有相等值的行排位相同，序數(shù)隨后跳躍DENSE_RANK具有相等值的行排位相同，序號是連續(xù)的第22頁/共47頁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í)需求說明針對供應(yīng)情況表進(jìn)行如下操作

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

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

）、（

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

COUNT

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

)

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

查詢-內(nèi)連接下面的查詢語句返回的查詢結(jié)果是一樣的嗎？根據(jù)前面表中數(shù)據(jù)，下面的查詢語句將會返回多少行記錄？串講：內(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;兩個語句的執(zhí)行結(jié)果相同第35頁/共47頁上機(jī)練習(xí)需求說明查詢所有雇員姓名和部門名稱從emp表中找到在工資等級表中對應(yīng)的工資等級工資等級表表結(jié)構(gòu)參照書中資料完成時間：8分鐘共性問題集中講解練習(xí)第36頁/共47頁串講：左外連接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查詢結(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、無匹配，NULL值返回到結(jié)果集猜一猜：這樣寫，返回的查詢結(jié)果是一樣的嗎？第37頁/共47頁演示示例：

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

查詢-分頁查詢講解：分頁查詢方法一SELECT*FROM(SELECT*FROMempORDE