SQL編程及高級查詢

1、第七章SQLSQL編程及高級查詢編程及高級查詢第二單元本章相關(guān)學(xué)習(xí)資源SQL語言和常用函數(shù)專題高級查詢專題Web前端基礎(chǔ)及數(shù)據(jù)庫開發(fā)第7章 SQL編程及高級查詢學(xué)習(xí)平臺學(xué)習(xí)平臺“Oracle數(shù)據(jù)庫數(shù)據(jù)庫”課程課程學(xué)生用書學(xué)生用書第2頁/共47頁預(yù)習(xí)檢查例舉數(shù)據(jù)操縱語言中常用的例舉數(shù)據(jù)操縱語言中常用的SQLSQL命令命令例舉例舉OracleOracle中常用的函數(shù)中常用的函數(shù)簡述查詢的分類簡述查詢的分類第3頁/共47頁本章任務(wù)任務(wù)任務(wù)1：使用：使用SQL操作學(xué)生表操作學(xué)生表任務(wù)任務(wù)2：使用：使用SQL函數(shù)對表進(jìn)行操作函數(shù)對表進(jìn)行操作任務(wù)任務(wù)3：查詢雇員相關(guān)信息：查詢雇員相關(guān)信息第4頁/共47頁會

2、使用會使用DML語言對數(shù)據(jù)庫進(jìn)行操作語言對數(shù)據(jù)庫進(jìn)行操作能夠熟練運(yùn)用能夠熟練運(yùn)用Oracle常用函數(shù)常用函數(shù)能夠熟練運(yùn)用子查詢能夠熟練運(yùn)用子查詢能夠熟練運(yùn)用連接查詢能夠熟練運(yùn)用連接查詢能夠熟練運(yùn)用查詢實(shí)現(xiàn)分頁顯示功能能夠熟練運(yùn)用查詢實(shí)現(xiàn)分頁顯示功能本章目標(biāo)點(diǎn)點(diǎn)重重點(diǎn)點(diǎn)難難點(diǎn)點(diǎn)難難點(diǎn)點(diǎn)重重點(diǎn)點(diǎn)難難第5頁/共47頁串講：SQL 簡介SQL 是是 Structured Query Language（結(jié)構(gòu)化（結(jié)構(gòu)化查詢語言）的首字母縮寫詞查詢語言）的首字母縮寫詞SQL 是數(shù)據(jù)庫語言，是數(shù)據(jù)庫語言，Oracle 使用該語言存儲和檢使用該語言存儲和檢索信息索信息表是主要的數(shù)據(jù)庫對象，用于存儲數(shù)據(jù)表是主要的

3、數(shù)據(jù)庫對象，用于存儲數(shù)據(jù)SELECT ename FROM Emp;發(fā)送 SQL 查詢Oracle 服務(wù)器enameBLAKESMITH ALLEN DAVID MARTIN發(fā)送命令輸出到用戶端用戶通過 SQL可以可以實(shí)現(xiàn)與Oracle 服務(wù)器的通信服務(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ù)控制語言 GRANT

4、REVOKE第7頁/共47頁串講：數(shù)據(jù)操縱語言 數(shù)據(jù)操縱語言用于檢索、插入和修改數(shù)據(jù)數(shù)據(jù)操縱語言是最常見的SQL命令數(shù)據(jù)操縱語言命令包括：INSERTUPDATEDELETESELECT第8頁/共47頁串講：INSERT 命令CREATE TABLE stuInfo ( stuNo CHAR(6) NOT NULL, -學(xué)號，非空學(xué)號，非空 stuName VARCHAR2(20) NOT NULL , -學(xué)員姓名，非空學(xué)員姓名，非空 stuAge NUMBER(3,0) NOT NULL, -年齡，非空年齡，非空 beginDate DATE -開始學(xué)習(xí)時(shí)間，日期類型開始學(xué)習(xí)時(shí)間，日期類型)

5、 ;INSERT INTO stuInfo(stuNo,stuName,stuAge) VALUES(034,Tom,23);INSERT INTO stuInfo VALUES(032,Ford,22,SYSDATE);INSERT INTO stuInfo SELECT stuNo,stuName,stuAge,beginDate FROM stuInfoBAK;插入數(shù)據(jù)插入數(shù)據(jù)插入來自其他表插入來自其他表的記錄的記錄,其中其中stuInfoBAK為為stuInfo備份表備份表 已知學(xué)生信息表已知學(xué)生信息表stuInfo,如何向表中存入學(xué)生信息？如何向表中存入學(xué)生信息？第9頁/共47頁串講

6、： UPDATE 命令- UPDATE單列單列UPDATE stuInfo SET stuAge = 26WHERE stuNo = 035;- UPDATE多列多列UPDATE stuInfo SET stuage = 26, beginDate = 2012-09-18WHERE stuNo= 035;修改數(shù)據(jù)修改數(shù)據(jù)如何修改年齡和開始學(xué)習(xí)時(shí)間？如何修改年齡和開始學(xué)習(xí)時(shí)間？第10頁/共47頁串講： DELETE 命令-刪除學(xué)號為刪除學(xué)號為035的學(xué)生記錄的學(xué)生記錄DELETE FROM stuInfo WHERE stuNo = 035;刪除數(shù)據(jù)刪除數(shù)據(jù)如何刪除學(xué)號為如何刪除學(xué)號為035的

7、學(xué)生記錄？的學(xué)生記錄？第11頁/共47頁如何查詢所有學(xué)生記錄？如何查詢所有學(xué)生記錄？如何查詢學(xué)號為如何查詢學(xué)號為035的記錄？的記錄？如何選擇指定的學(xué)號、姓名列？如何選擇指定的學(xué)號、姓名列？如何篩除重復(fù)的行？如何篩除重復(fù)的行？串講：SELECT 命令SELECT * FROM stuInfo;SELECT * FROM stuInfo WHERE stuNo = 035; SELECT stuNo,stuName FROM stuInfo; SELECT DISTINCT stuNo,stuName FROM stuInfo; 第12頁/共47頁如何對結(jié)果集排序如何對結(jié)果集排序,先按照年齡降序

8、，如果年先按照年齡降序，如果年齡相同的按照學(xué)號升序排列齡相同的按照學(xué)號升序排列?如何利用現(xiàn)有的表如何利用現(xiàn)有的表(stuInfo)創(chuàng)建新表創(chuàng)建新表(stuInfoBAK)？如何使用列別名？別名中有空格如何解決？如何使用列別名？別名中有空格如何解決？串講：SELECT 命令CREATE TABLE stuInfoBAK as SELECT * FROM stuInfo;SELECT S|stuNo 學(xué)號學(xué)號,stuName “姓姓 名名” FROM stuInfo; SELECT * FROM stuInfo ORDER BY stuAge DESC,stuNo ASC; 第13頁/共47頁演示

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

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

11、一種數(shù)據(jù)轉(zhuǎn)換函數(shù)將值從一種數(shù)據(jù)類型轉(zhuǎn)換為另一種數(shù)據(jù)類型類型常用的轉(zhuǎn)換函數(shù)有：常用的轉(zhuǎn)換函數(shù)有：TO_CHARTO_DATETO_NUMBERSELECT TO_CHAR(sysdate,YYYY年年MM月月DD日日 HH24:MI:SS)FROM dual;SELECT TO_CHAR (123456.03,099,999.99) FROM dual;SELECT TO_DATE(2005-12-06 , yyyy-mm-dd)FROM dual;SELECT TO_NUMBER(100) FROM dual;第17頁/共47頁演示示例：演示示例： 函數(shù)函數(shù)- -轉(zhuǎn)換函數(shù)轉(zhuǎn)換函數(shù)其它函數(shù)轉(zhuǎn)換空

12、值的函數(shù)轉(zhuǎn)換空值的函數(shù)NVLNVLDECODESELECT ename, sal+NVL(comm,0) sal1, NVL2(comm,sal+comm,sal) sal2, DECODE(to_char(hiredate,MM), 01,一月一月, 02,二月二月, 03,三月三月,04,四月四月, 05,五月五月,06,六月六月, 下半年下半年) mon FROM employee;示例示例18/45演示示例演示示例8：其他函數(shù)：其他函數(shù)串講：聚合函數(shù)聚合函數(shù)聚合函數(shù)基于一組行來返回結(jié)果基于一組行來返回結(jié)果為每一組行返回一個(gè)值為每一組行返回一個(gè)值A(chǔ)VGMINMAXSUMCOUNTSELE

13、CT SUM(sal) FROM emp ; SELECT AVG(sal) FROM emp WHERE job=CLERK;SELECT MAX(sal) FROM emp;SELECT COUNT(*) FROM emp;SELECT COUNT(comm) FROM emp; SELECT COUNT(DISTINCT job) FROM emp;聚合聚合函數(shù)函數(shù)第19頁/共47頁講解： GROUP BY和HAVING子句GROUP BY子句子句用于將信息劃分為更小的組每一組行返回針對該組的單個(gè)結(jié)果HAVING子句子句用于指定 GROUP BY 子句檢索行的條件SELECT deptn

14、o,MAX(sal) maxSal,AVG(sal) avgSal,MIN(sal) minSal FROM empGROUP BY deptno;SELECT deptno,MAX(sal) maxSal,AVG(sal) avgSal,MIN(sal) minSalFROM empGROUP BY deptnoHAVING AVG(sal)2000;第20頁/共47頁演示示例：演示示例： 函數(shù)函數(shù)- -聚合函數(shù)聚合函數(shù)講解：分析函數(shù)分析函數(shù)根據(jù)一組行來計(jì)算聚合值分析函數(shù)根據(jù)一組行來計(jì)算聚合值用于計(jì)算完成聚集的累計(jì)用于計(jì)算完成聚集的累計(jì)排名等排名等分析函數(shù)為每組記錄返回多個(gè)行分析函數(shù)為每組記

15、錄返回多個(gè)行分析函數(shù)分析函數(shù)DENSE_RANKRANKROW_NUMBER第21頁/共47頁講解：分析函數(shù)以下三個(gè)分析函數(shù)用于計(jì)算一個(gè)行在一組有序行以下三個(gè)分析函數(shù)用于計(jì)算一個(gè)行在一組有序行中的排位，序號從中的排位，序號從1開始開始ROW_NUMBER 返回連續(xù)的排位，不論值是否相等RANK 具有相等值的行排位相同，序數(shù)隨后跳躍DENSE_RANK 具有相等值的行排位相同，序號是連續(xù)的第22頁/共47頁SELECT ename, job, deptno, sal, ROW_NUMBER() OVER (ORDER BY sal DESC) AS SAL_RANK FROM SCOTT.EMP

16、;SELECT deptno, ename, sal, comm, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC, comm) RANKFROM emp; SELECT d.dname, e.ename, e.sal, DENSE_RANK() OVER (PARTITION BY e.deptno ORDER BY e.sal DESC) AS DENRANKFROM emp e, dept d WHERE e.deptno = d.deptno;演示示例：演示示例： 函數(shù)函數(shù)- -分析函數(shù)分析函數(shù)上機(jī)練習(xí)需求說明需求說明針對供應(yīng)情況表

17、進(jìn)行如下操作查詢供貨商總?cè)藬?shù)查詢供貨商總?cè)藬?shù)查詢一次供應(yīng)茶葉的最大斤數(shù)查詢一次供應(yīng)茶葉的最大斤數(shù)查詢各個(gè)供應(yīng)商編號及供應(yīng)茶葉的總斤數(shù)查詢各個(gè)供應(yīng)商編號及供應(yīng)茶葉的總斤數(shù)查詢提供了查詢提供了2 2種以上茶葉的供貨商編號種以上茶葉的供貨商編號完成時(shí)間完成時(shí)間：15分鐘分鐘共性問題集中講解共性問題集中講解練習(xí)練習(xí)第23頁/共47頁SQLSQL函數(shù)分為（函數(shù)分為（ ) )、( )( )和和( ( ) )。 請列舉聚合函數(shù)，有（請列舉聚合函數(shù)，有（ ）、（）、（ ）、）、（ ）、（）、（ ）等。）等。小結(jié)單行函數(shù)單行函數(shù)聚合函數(shù)聚合函數(shù)分析函數(shù)分析函數(shù)SUMSUM COUNTCOUNT AVGAVGMA

18、X MAX 第24頁/共47頁講解：查詢SELECT語句的作用是什么？語句的作用是什么？多表查詢的類型有哪些？多表查詢的類型有哪些？子查詢 - 將一個(gè)查詢包含到另一個(gè)查詢中連 接 - 合并多個(gè)數(shù)據(jù)表中的列聯(lián) 合 - 合并多個(gè)數(shù)據(jù)表中的行第25頁/共47頁講解：子查詢?yōu)槭裁词褂米硬樵儯繛槭裁词褂米硬樵?？是表達(dá)查詢最自然的方式使編寫SELECT語句變得更加簡單有些查詢?nèi)绻皇褂米硬樵兙蜔o法用SQL表達(dá)出來第26頁/共47頁講解：子查詢實(shí)例一EMP表中，查詢與表中，查詢與“SCOTT”在同一個(gè)部門的雇員在同一個(gè)部門的雇員信息信息SELECT empno,ename,deptnoFROM empWHE

19、RE deptno IN (SELECT deptno FROM emp WHERE ename=SCOTT);實(shí)例 如果將如果將ININ換成換成NOTNOT IN IN又如何？又如何？第27頁/共47頁講解：子查詢實(shí)例二EMP表中，找出每個(gè)雇員超過他所在部門平均工表中，找出每個(gè)雇員超過他所在部門平均工資的雇員編號、雇員名稱、薪水、部門編號資的雇員編號、雇員名稱、薪水、部門編號返回第一步SELECT empno,ename,sal,deptnoFROM emp e1WHERE sal (SELECT AVG(sal) FROM emp e2 WHERE e1.deptno=e2.deptno

20、);實(shí)例 第28頁/共47頁講解：子查詢實(shí)例三查詢其他部門中比查詢其他部門中比30號部門某一雇員薪水少的雇號部門某一雇員薪水少的雇員信息員信息SELECT empno,ename,sal,deptnoFROM emp e1WHERE salANY (SELECT sal FROM emp WHERE deptno=30 ) AND deptno30;實(shí)例 返回多值時(shí)可用返回多值時(shí)可用ANYANY或或ALLALL修飾符修飾符其中，其中，=ANY=ANY等價(jià)于等價(jià)于ININ，ANYANY等價(jià)于等價(jià)于MAX,ALLMAX,ALL等等價(jià)于價(jià)于NOT INNOT IN，ALLALL等價(jià)于等價(jià)于MIN30

21、00);實(shí)例 （1）首先取外層查詢中表的第一個(gè)記錄，根據(jù)它與內(nèi)層查詢相關(guān)的屬）首先取外層查詢中表的第一個(gè)記錄，根據(jù)它與內(nèi)層查詢相關(guān)的屬性值（性值（deptno）處理內(nèi)層查詢，若）處理內(nèi)層查詢，若where子句返回值為真，則取外子句返回值為真，則取外層查詢中該記錄放入結(jié)果表層查詢中該記錄放入結(jié)果表（2）再取外層查詢的下一個(gè)記錄，重復(fù)這一過程，直到外層查詢?nèi)浚┰偃⊥鈱硬樵兊南乱粋€(gè)記錄，重復(fù)這一過程，直到外層查詢?nèi)繖z查完為止檢查完為止由于帶由于帶EXISTS的相關(guān)子查詢只關(guān)心內(nèi)層查詢是否有返回值，并不需的相關(guān)子查詢只關(guān)心內(nèi)層查詢是否有返回值，并不需要查具體值，有時(shí)是高效的方法要查具體值，有時(shí)是高

22、效的方法第30頁/共47頁講解：子查詢的分類子查詢的分類不相關(guān)子查詢不相關(guān)子查詢子查詢的查詢條件不依賴于父查詢屬于較簡單的一類查詢，通常說的嵌套查詢屬于此類相關(guān)子查詢相關(guān)子查詢子查詢的查詢條件依賴于父查詢第31頁/共47頁演示示例：查詢演示示例：查詢- -子查詢子查詢串講：常用的多表連接查詢連接分類連接分類第32頁/共47頁內(nèi)連接內(nèi)連接外連接外連接自連接自連接交叉連接交叉連接連接連接左外連接左外連接右外連接右外連接等值連接等值連接自然連接自然連接不等值連接不等值連接串講：內(nèi)連接內(nèi)連接使用比較運(yùn)算符根據(jù)每個(gè)表的通用列中的內(nèi)連接使用比較運(yùn)算符根據(jù)每個(gè)表的通用列中的值匹配兩個(gè)表中的行值匹配兩個(gè)表中的

23、行 梅超風(fēng)梅超風(fēng)1陳玄風(fēng)陳玄風(fēng)2陸乘風(fēng)陸乘風(fēng)3曲靈風(fēng)曲靈風(fēng)4100197200189200267300276300381梅超風(fēng)梅超風(fēng)00197陳玄風(fēng)陳玄風(fēng)00189陳玄風(fēng)陳玄風(fēng)00267陸乘風(fēng)陸乘風(fēng)00276陸乘風(fēng)陸乘風(fēng)00381Students Score 查詢結(jié)果 第33頁/共47頁串講：內(nèi)連接SELECT FROM 表1 INNER JOIN 表2ON SELECT FROM 表1，表2WHERE SELECT S.SName,C.CourseID,C.Score FROM Score CINNER JOIN Students SON C.StudentID = S.SCode;SELE

24、CT Students.SName, Score.CourseID, Score.ScoreFROM Students,ScoreWHERE Students.SCode = Score.StudentID;等價(jià)第34頁/共47頁查詢學(xué)員姓名、課程和分?jǐn)?shù)查詢學(xué)員姓名、課程和分?jǐn)?shù)演示示例：演示示例： 查詢查詢- -內(nèi)連接內(nèi)連接下面的查詢語句返回的查詢結(jié)果是一樣的嗎？下面的查詢語句返回的查詢結(jié)果是一樣的嗎？根據(jù)前面表中數(shù)據(jù)，下面的查詢語句將會返回根據(jù)前面表中數(shù)據(jù)，下面的查詢語句將會返回多少行記錄？多少行記錄？串講：內(nèi)連接SELECT S.SName,C.CourseID,C.Score FROM

25、Students SINNER JOIN Score CON C.StudentID S.SCode;SELECT S.SName,C.CourseID,C.Score FROM Students SINNER JOIN Score CON C.StudentID = S.SCode;15行行SELECT Students.SName, Score.CourseID, Score.ScoreFROM Students,ScoreWHERE Students.SCode = Score.StudentID;兩個(gè)語句的執(zhí)兩個(gè)語句的執(zhí)行結(jié)果相同行結(jié)果相同第35頁/共47頁上機(jī)練習(xí)需求說明需求說明查詢

26、所有雇員姓名和部門名稱從emp表中找到在工資等級表中對應(yīng)的工資等級工資等級表表結(jié)構(gòu)參照書中資料工資等級表表結(jié)構(gòu)參照書中資料完成時(shí)間完成時(shí)間：8分鐘分鐘共性問題集中講解共性問題集中講解練習(xí)練習(xí)第36頁/共47頁串講：左外連接梅超風(fēng)梅超風(fēng)1陳玄風(fēng)陳玄風(fēng)2陸乘風(fēng)陸乘風(fēng)3曲靈風(fēng)曲靈風(fēng)4100197200189200267300276300381梅超風(fēng)梅超風(fēng)00197陳玄風(fēng)陳玄風(fēng)00189陳玄風(fēng)陳玄風(fēng)00267陸乘風(fēng)陸乘風(fēng)00276陸乘風(fēng)陸乘風(fēng)00381曲靈風(fēng)曲靈風(fēng)NULLNULLStudentsScore查詢結(jié)果SELECT S.SName,C.CourseID,C.Score FROM Score

27、AS CLEFT JOIN Students AS SON C.StudentID = S.SCode不一樣，主不一樣，主表和從表位表和從表位置已互換置已互換SELECT S.SName,C.CourseID,C.Score FROM Students SLEFT JOIN Score CON C.StudentID = S.SCode主表（左表）主表（左表）Students中的數(shù)據(jù)逐中的數(shù)據(jù)逐條匹配從表?xiàng)l匹配從表Score中中的數(shù)據(jù)的數(shù)據(jù)1、匹配，返回到結(jié)果、匹配，返回到結(jié)果集集2、無匹配，、無匹配，NULL值值返回到結(jié)果集返回到結(jié)果集猜一猜：這樣寫，返回的查詢結(jié)果是一樣的嗎？第37頁/共4

28、7頁演示示例：演示示例： 查詢查詢- -左外連接左外連接SELECT 圖書編號圖書編號,圖書名稱圖書名稱,出版社名稱出版社名稱FROM 圖書表圖書表RIGHT OUTER JOIN 出版社表出版社表 ON 圖書表圖書表.出版社編號出版社編號 = 出版社表出版社表.出版社編號出版社編號串講：右外連接圖書編號圖書編號圖書名稱圖書名稱出版社名稱出版社名稱1走進(jìn)走進(jìn)Java編程世界編程世界北大出版社北大出版社2HTML和和CSS網(wǎng)頁技術(shù)網(wǎng)頁技術(shù)清華出版社清華出版社NULLNULL新知出版社新知出版社也許很也許很久沒出久沒出版書籍版書籍了了右外連接的原理與左外連接相同右外連接的原理與左外連接相同右表逐條去匹配記錄；否則右表逐條去匹配記錄；否則NULLNUL