SQL編程及高級查詢

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

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

ALLEN

DAVID

MARTIN發(fā)送命令輸出到用戶端用戶通過SQL可以實現(xiàn)與Oracle服務器的通信第6頁/共47頁串講：SQL簡介SQL支持下列類別的命令：數(shù)據(jù)定義語言（DDL）數(shù)據(jù)操縱語言（DML）事務控制語言（TCL）數(shù)據(jù)控制語言（DCL）數(shù)據(jù)定義語言CREATEALTERDROP數(shù)據(jù)操縱語言INSERTSELECTDELETEUPDATE事務控制語言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,--學號，非空stuNameVARCHAR2(20)NOTNULL,--學員姓名，非空stuAgeNUMBER(3,0)NOTNULL,--年齡，非空beginDateDATE--開始學習時間，日期類型);INSERTINTOstuInfo(stuNo,stuName,stuAge)VALUES('034','Tom',23);INSERTINTOstuInfoVALUES('032','Ford',22,SYSDATE);INSERTINTOstuInfoSELECTstuNo,stuName,stuAge,beginDateFROMstuInfoBAK;插入數(shù)據(jù)插入來自其他表的記錄,其中stuInfoBAK為stuInfo備份表已知學生信息表stuInfo,如何向表中存入學生信息？第9頁/共47頁串講：UPDATE命令--UPDATE單列UPDATEstuInfoSETstuAge=26WHEREstuNo='035';--UPDATE多列UPDATEstuInfoSETstuage=26,beginDate='2012-09-18'WHEREstuNo='035';修改數(shù)據(jù)如何修改年齡和開始學習時間？第10頁/共47頁串講：DELETE命令--刪除學號為035的學生記錄DELETEFROMstuInfoWHEREstuNo='035';刪除數(shù)據(jù)如何刪除學號為035的學生記錄？第11頁/共47頁如何查詢所有學生記錄？如何查詢學號為035的記錄？如何選擇指定的學號、姓名列？如何篩除重復的行？串講：SELECT命令SELECT*FROMstuInfo;SELECT*FROMstuInfoWHEREstuNo='035';

SELECTstuNo,stuNameFROMstuInfo;SELECTDISTINCTstuNo,stuNameFROMstuInfo;

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

第13頁/共47頁演示示例：DML語句操作其中兩個字符串拼接用‘||’符號上機練習需求說明針對供應商表S，產(chǎn)品表P，供應情況表SP插入數(shù)據(jù)。參照提供的素材《供應商練習相關表》。用SQL語句完成以下查詢求供應產(chǎn)品編號為P1的供應商編號求供應綠茶等級為1的供應商編號求沒有使用北京公司供應商的茶葉，但產(chǎn)品級別是1級茶葉的產(chǎn)品編號完成時間：10分鐘共性問題集中講解練習第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ù)轉換函數(shù)其他函數(shù)第16頁/共47頁串講：轉換函數(shù)轉換函數(shù)將值從一種數(shù)據(jù)類型轉換為另一種數(shù)據(jù)類型常用的轉換函數(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ù)-轉換函數(shù)其它函數(shù)轉換空值的函數(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ù)基于一組行來返回結果為每一組行返回一個值AVGMINMAXSUMCOUNTSELECTSUM(sal)FROMemp;SELECTAVG(sal)FROMempWHEREjob='CLERK';SELECTMAX(sal)FROMemp;SELECTCOUNT(*)FROMemp;SELECTCOUNT(comm)FROMemp;SELECTCOUNT(DISTINCTjob)FROMemp;聚合函數(shù)第19頁/共47頁講解：GROUPBY和HAVING子句GROUPBY子句用于將信息劃分為更小的組每一組行返回針對該組的單個結果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ù)上機練習需求說明針對供應情況表進行如下操作

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

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

）、（

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

COUNT

AVGMAX第24頁/共47頁講解：查詢SELECT語句的作用是什么？多表查詢的類型有哪些？子查詢-將一個查詢包含到另一個查詢中連接-合并多個數(shù)據(jù)表中的列聯(lián)合-合并多個數(shù)據(jù)表中的行第25頁/共47頁講解：子查詢?yōu)槭裁词褂米硬樵?？是表達查詢最自然的方式使編寫SELECT語句變得更加簡單有些查詢?nèi)绻皇褂米硬樵兙蜔o法用SQL表達出來第26頁/共47頁講解：子查詢實例一EMP表中，查詢與“SCOTT”在同一個部門的雇員信息SELECTempno,ename,deptnoFROMempWHEREdeptnoIN(SELECTdeptnoFROMempWHEREename='SCOTT');執(zhí)行父查詢返回子查詢值給父查詢執(zhí)行子查詢獲得結果值實例如果將IN換成NOTIN又如何？第27頁/共47頁講解：子查詢實例二EMP表中，找出每個雇員超過他所在部門平均工資的雇員編號、雇員名稱、薪水、部門編號返回第一步SELECTempno,ename,sal,deptnoFROMempe1WHEREsal>(SELECTAVG(sal)FROMempe2WHEREe1.deptno=e2.deptno);父查詢傳送列值給子查詢子查詢獲取父查詢傳送的列值子查詢返回查詢值給父查詢父查詢傳送下一行的列值給子查詢(重復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)層查詢相關的屬性值（deptno）處理內(nèi)層查詢，若where子句返回值為真，則取外層查詢中該記錄放入結果表（2）再取外層查詢的下一個記錄，重復這一過程，直到外層查詢?nèi)繖z查完為止由于帶EXISTS的相關子查詢只關心內(nèi)層查詢是否有返回值，并不需要查具體值，有時是高效的方法第30頁/共47頁講解：子查詢的分類不相關子查詢子查詢的查詢條件不依賴于父查詢屬于較簡單的一類查詢，通常說的嵌套查詢屬于此類相關子查詢子查詢的查詢條件依賴于父查詢第31頁/共47頁演示示例：查詢-子查詢串講：常用的多表連接查詢連接分類第32頁/共47頁內(nèi)連接外連接自連接交叉連接連接左外連接右外連接等值連接自然連接不等值連接串講：內(nèi)連接內(nèi)連接使用比較運算符根據(jù)每個表的通用列中的值匹配兩個表中的行SNameSCode梅超風1陳玄風2陸乘風3曲靈風4StudentIDCourseIDScore100197200189200267300276300381SNameCourseIDScore梅超風00197陳玄風00189陳玄風00267陸乘風00276陸乘風00381StudentsScore查詢結果第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頁查詢學員姓名、課程和分數(shù)演示示例：

查詢-內(nèi)連接下面的查詢語句返回的查詢結果是一樣的嗎？根據(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í)行結果相同第35頁/共47頁上機練習需求說明查詢所有雇員姓名和部門名稱從emp表中找到在工資等級表中對應的工資等級工資等級表表結構參照書中資料完成時間：8分鐘共性問題集中講解練習第36頁/共47頁串講：左外連接SNameSCode梅超風1陳玄風2陸乘風3曲靈風4StudentIDCourseIDScore100197200189200267300276300381SNameCourseIDScore梅超風00197陳玄風00189陳玄風00267陸乘風00276陸乘風00381曲靈風NULLNULLStudentsScore查詢結果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、匹配，返回到結果集2、無匹配，NULL值返回到結果集猜一猜：這樣寫，返回的查詢結果是一樣的嗎？第37頁/共47頁演示示例：

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

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