Chapter 8

Size: px

Start display at page:

Download "Chapter 8"

散勃丁
9 years ago
Views:

1 第 8 章 Part I SQL 查詢第 8 章 SQL 查詢

2 SQL 簡介結構化查詢語言 SQL (Structured Query Language) 關連式資料庫系統的標準語言 SQL 功能包括資料定義查詢與更新敘述既是 DDL, 也是 DML 定義資料庫視界安全性與權限完整性及交易控制具備嵌入 Java COBOL 或 C/C++ 等語言的機制第 8 章 SQL 查詢 2

3 SELECT-FROM-WHERE 結構 SELECT <attribute list> FROM <table list> WHERE <condition> <attribute list> 是屬性名稱的列表, 在查詢時需要參考這些屬性的值 <table list> 列出處裡查詢時會用到的關聯名稱 <condition> 是條件 ( 布林 ) 運算式, 用來識別查詢時所要擷取的值組第 8 章 SQL 查詢 3

4 圖 5.5 範例關聯式資料庫綱要第 8 章 SQL 查詢 4

5 圖 5.6 範例資料庫的某個狀態第 8 章 SQL 查詢 5

6 簡單的 SQL 查詢每個簡單查詢範例是針對一個關聯 Q0: 擷取名叫 'John B. Smith' 員工的生日與住址 Q0: SELECT BDATE, ADDRESS FROM EMPLOYEE WHERE FNAME='John' AND MINIT='B AND LNAME='Smith 查詢的結果可能會有重複的值組第 8 章 SQL 查詢 6

7 簡單的 SQL 查詢 Q1: 擷取所有在 'Research' 部門工作的員工的姓名與住址 Q1: SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE, DEPARTMENT WHERE DNAME='Research' AND DNUMBER=DNO (DNAME= Research ) 是選擇條件 (DNUMBER=DNO) 則是合併條件第 8 章 SQL 查詢 7

8 簡單的 SQL 查詢 ( 續 ) Q2: 列出所有位在 'Stafford' 地點的計畫其計畫編號控管部門編號, 以及部門經理的姓氏住址和生日 Q2: SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND PLOCATION='Stafford' 在 Q2 裡有兩個合併條件合併條件 DNUM=DNUMBER 使得計劃與其控管部門產生關聯合併條件 MGRSSN=SSN 則讓控管部門與管理此部門的員工產生關聯第 8 章 SQL 查詢 8

9 修飾屬性名稱在 SQL 中, 只要屬性是屬於不同的關聯, 就可以讓兩個或多個屬性使用同樣的名稱若查詢會參考到兩個或多個同名的屬性, 就必須用關聯名稱放在屬性名稱之前, 並用英文的句點 (.) 來分隔範例 : EMPLOYEE.LNAME, DEPARTMENT.DNAME 第 8 章 SQL 查詢 9

10 別名有些查詢需要對同一個關聯參考兩次在這類情況要對關聯名稱指定別名 Q8: 擷取員工的姓名與其直屬上司的姓名 Q8: SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM WHERE EMPLOYEE E S E.SUPERSSN=S.SSN 在 Q8 中的替代關聯名稱 E 和 S 被稱作 EMPLOYEE 關聯的別名 (alias) 或值組變數我們可以將 E 和 S 想像成 EMPLOYEE 的兩份不同的副本 ; 是代表扮演部屬角色的員工, 而 S 是代表扮演上司角色的員工第 8 章 SQL 查詢 10

11 未指定的 WHERE 子句假如沒有 WHERE 的子句, 代表沒有選擇條件, 因此 FROM 子句裡所指定關聯的所有值組都會被選取 Q9: 在資料庫中選擇所有員工的 SSN 值 Q9: SELECT SSN FROM EMPLOYEE 第 8 章 SQL 查詢 11

12 * 的用法在 SQL 中, 假如要擷取所選值組的所有屬性值, 只需要用一個星號 (*) 即可代表所有的屬性範例 : Q1C: SELECT * FROM EMPLOYEE WHERE DNO=5 Q1D: SELECT * FROM EMPLOYEE, DEPARTMENT WHERE DNAME='Research' AND DNO=DNUMBER 第 8 章 SQL 查詢 12

13 DISTINCT 的用法 SQL 通常不會將關聯視為集合, 因此可以出現重複的值組為了消除查詢結果中的重複值組, 可使用關鍵字 DISTINCT Q11: 擷取每一位員工的薪資, 以及不同的薪資值 Q11 的結果可能會有重複的 SALARY 值 Q11A 則不會有任何重複值 Q11: SELECT SALARY FROM EMPLOYEE Q11A: SELECT DISTINCT SALARY FROM EMPLOYEE 第 8 章 SQL 查詢 13

14 子字串比對 LIKE 比較運算子可用來比對子字串部份字串的指定方式是藉由兩個保留字元 : 百分比 % 字元可取代任意數目的字元, 底線 _ 字元則可取代單一字元第 8 章 SQL 查詢 14

15 子字串比對 Q12: 擷取所有住址在 Houston, Texas 的員工 ; 也就是說 ADDRESS 屬性的值必須包含子字串 'Houston,TX Q25: SELECT FNAME, LNAME FROM EMPLOYEE WHERE ADDRESS LIKE '%Houston,TX%' 第 8 章 SQL 查詢 15

16 子字串比對 Q12A: 擷取所有在 1950 年代出生的員工根據日期的格式, 此例 5 必須是字串的第 3 個字元, 因此 BDATE 值是 5 _, 這裡的底線字元可以是任何一個字元 Q12A: SELECT FNAME, LNAME FROM EMPLOYEE WHERE BDATE LIKE ' 5_ 第 8 章 SQL 查詢 16

17 算術運算在數值的資料或屬性上可使用一般的標準四則運算 ( + - * 和 '/' ) Q13: 顯示出所有工作於 ProductX 計畫的員工加薪 10% 後的薪資結果 Q13: SELECT FNAME, LNAME, 1.1*SALARY FROM EMPLOYEE, WORKS_ON, PROJECT WHERE SSN=ESSN AND PNO=PNUMBER AND PNAME='ProductX 第 8 章 SQL 查詢 17

18 ORDER BY ORDER BY 子句是用來針對值組內的一或多個屬性值, 將查詢結果的值組加以排序 Q15: 擷取員工與他們所工作計畫的清單, 在清單中先針對部門號碼排序, 每個部門內再依員工姓名的字母順序排序 Q15: SELECT DNAME, LNAME, FNAME, PNAME FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT WHERE DNUMBER=DNO AND SSN=ESSN AND PNO=PNUMBER ORDER BY DNAME, LNAME 第 8 章 SQL 查詢 18

19 ORDER BY 預設的順序是遞增排序使用關鍵字 DESC 可變成遞減排序, 而關鍵字 ASC 則是用來更明確的指定遞增排序第 8 章 SQL 查詢 19

20 巢狀查詢所謂的巢狀查詢 (nested query) 是指在一個查詢的 WHERE 子句內, 含有完整的 SELECT-FROM- WHERE 區塊此時這個外部的 WHERE 查詢被稱為外部查詢 (outer query) Q1: 擷取所有在 'Research' 部門工作的員工的姓名與住址 Q1: SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME='Research' ) 第 8 章 SQL 查詢 20

21 巢狀查詢由巢狀查詢先選出 Research 部門的編號外部查詢來選擇其 DNO 值屬於巢狀查詢結果中的 EMPLOYEE 值組這裡的比較運算子 IN 是 v 值與由 V 值所組成的集合 ( 或多重集合 ), 若 v 隸屬於 V 則結果為 TRUE 通常可以使用好幾層巢狀查詢假如屬性沒有明確指定是哪個關聯, 那麼就以最內層 (innermost) 的查詢所宣告的關聯為準此例的巢狀查詢與外部查詢沒有關聯第 8 章 SQL 查詢 21

22 相互關聯的巢狀查詢假如巢狀查詢中 WHERE 子句中的條件, 會參考到宣告在外部查詢的關聯裡的某些屬性, 就稱這兩個查詢是相互關聯的 (correlated) 相互關聯的巢狀查詢其結果與外部查詢關聯的每個值組 ( 或值組組合 ) 不同 Q16: 擷取眷屬的名字和性別與員工本人相同的員工姓名 Q16: SELECT E.FNAME, E.LNAME FROM EMPLOYEE AS E WHERE E.SSN IN (SELECT ESSN FROM DEPENDENT WHERE E.SEX=SEX AND E.FNAME=DEPENDENT_NAME) 第 8 章 SQL 查詢 22

23 相互關聯的巢狀查詢 ( 續 ) 在 Q16 中, 巢狀查詢其結果與外部查詢關聯的結果值組不同以巢狀 SELECT... FROM... WHERE... 區塊所撰寫, 而且使用 = 或 IN 比較運算子的查詢, 一定可以改寫成單一區塊的查詢例如 Q16 可以改寫成 Q16A: SELECT FROM WHERE E.FNAME, E.LNAME EMPLOYEE AS E, DEPENDENT AS D E.SSN=D.ESSN AND E.Sex=D.Sex AND E.FNAME=D.DEPENDENT_NAME 第 8 章 SQL 查詢 23

24 EXISTS 函數 EXISTS 函數, 是用來檢查相互關聯的巢狀查詢的結果是否為空的 ( 沒有任何值組 ) 將查詢範例 16 用 EXISTS 改寫成 Q16B 如下 : Q16B: SELECT FNAME, LNAME FROM EMPLOYEE AS E WHERE EXISTS (SELECT * FROM DEPENDENT WHERE E.SSN=ESSN AND E.Sex=Sex AND FNAME=DEPENDENT_NAME) 第 8 章 SQL 查詢 24

25 EXISTS 函數 Q6: 擷取沒有眷屬的員工姓名 Q6: SELECT FNAME, LNAME FROM EMPLOYEE WHERE NOT EXISTS (SELECT * FROM DEPENDENT WHERE SSN=ESSN) 在 Q6 中, 相互關聯的巢狀查詢會擷取所有與 EMPLOYEE 值組相關聯的 DEPENDENT 值組, 如果不存在則此 EMPLOYEE 值組會被選取第 8 章 SQL 查詢 25

26 明確指定的集合也可以在 WHERE 子句中, 使用明確數值的集合來取代巢狀查詢 Q17: 擷取所有在 1 2 或 3 號計畫工作的員工的社會安全號碼 Q17: SELECT DISTINCT ESSN FROM WORKS_ON WHERE PNO IN (1, 2, 3) 第 8 章 SQL 查詢 26

27 聚合函數包括 COUNT SUM MAX MIN 和 AVG Q19: 計算出所有員工薪資的總和最高薪資最低薪資和平均薪資 Q19: SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY) FROM EMPLOYEE 第 8 章 SQL 查詢 27

28 聚合函數 Q20: 計算出所有在 Research 部門工作的員工其薪資總和, 以及此部門員工的最高薪資最低薪資與平均薪資 Q20: SELECT SUM(SALARY), MIN(SALARY), MAX(SALARY), AVG(SALARY) FROM EMPLOYEE, DEPARTMENT WHERE DNO=DNUMBER AND DNAME='Research' 第 8 章 SQL 查詢 28

29 聚合函數 Q21: 計算公司裡員工的總數 Q21: SELECT COUNT (*) FROM EMPLOYEE Q22: 在 Research 部門工作的員工總數 Q22: SELECT COUNT (*) FROM EMPLOYEE, DEPARTMENT WHERE DNO=DNUMBER AND DNAME='Research 第 8 章 SQL 查詢 29

30 分群在很多時候會需要將聚合函數應用在關聯中, 根據某些屬性值分類的值組子群組上每個子群組是由指定的群組化屬性 (grouping attribute) 分組而成, 每一組的群組化屬性值是相同的針對每個子群組分別使用函數 SQL 提供 GROUP BY 子句用來指定群組化屬性, 它一定要出現在 SELECT 子句中第 8 章 SQL 查詢 30

31 分組 Q24: 列出每個部門的編號, 以及此部門的工作員工人數和平均薪資 Q24: SELECT DNO, COUNT (*), AVG (SALARY) FROM EMPLOYEE GROUP BY DNO 在 Q24 將 EMPLOYEE 值組分成數個群組每個群組都有相同的群組化屬性 DNO 值而在每個值組群組中分別執行 COUNT 與 AVG 函數第 8 章 SQL 查詢 31

32 Q24 查詢結果 Dno Count(*) Avg(Salary) 第 8 章 SQL 查詢 32

33 分組 Q25: 擷取每一個計畫的計畫編號計畫名稱以及在此計畫裡工作的員工人數 Q25: SELECT PNUMBER, PNAME, COUNT (*) FROM PROJECT, WORKS_ON WHERE PNUMBER=PNO GROUP BY PNUMBER, PNAME 此例的分組與函數動作要等到兩個關聯合併之後才會執行第 8 章 SQL 查詢 33

34 Q25 查詢結果 Pnumber Pname Count(*) 1 ProductX 2 2 ProductY 3 3 ProductZ 2 10 Computerization 3 20 Reorganization 3 30 Newbenefits 3 第 8 章 SQL 查詢 34

35 HAVING 子句有時只需要擷取滿足特定條件群組的函數值此時可使用 HAVING 子句在群組 ( 而不是個別值組 ) 上指定選擇條件第 8 章 SQL 查詢 35

36 HAVING 子句 Q26: 對工作員工在兩人以上的每個計畫, 擷取其計畫編號計畫名稱以及計畫中的工作員工人數 Q26: SELECT PNUMBER, PNAME, COUNT(*) FROM PROJECT, WORKS_ON WHERE PNUMBER=PNO GROUP BY PNUMBER, PNAME HAVING COUNT (*) > 2 第 8 章 SQL 查詢 36

37 Q26 查詢結果 Pname Count(*) ProductY 3 Computerization 3 Reorganization 3 Newbenefits 3 第 8 章 SQL 查詢 37

38 SQL 查詢語法總整理 SQL 的查詢最多可包含 6 個子句, 但只有前兩個 SELECT 與 FROM 子句是必要的子句是以下列的順序來指定 : SELECT FROM [WHERE [GROUP BY [HAVING [ORDER BY <attribute list> <table list> <condition>] <grouping attribute(s)>] <group condition>] <attribute list>] 第 8 章 SQL 查詢 38

39 SQL 查詢語法總整理 SELECT 子句中列出被擷取的屬性或函數 FROM 子句是指定所有在查詢時需要的關聯 ( 表格 ), 包括合併的關聯, 但不包括巢狀查詢所需的關聯 WHERE 子句是指定從這些關聯裡選取值組的條件, 同時視需要加入合併條件 GROUP BY 子句中指定群組化屬性 HAVING 子句所指定的是群組的選取條件 ORDER BY 用來指定查詢結果的顯示順序理論上查詢最早執行的會是 FROM 子句, 接著是 WHERE 子句, 然後是 GROUP BY 和 HAVING 子句, 最後是 ORDER BY 子句用來將查詢結果排序第 8 章 SQL 查詢 39

40 學習評量 ( 習題 ) 請以 SQL 語言來撰寫下列針對圖 1.2 資料庫綱要的查詢 a. 擷取所有主修 'CS ( 電腦科學 ) 的大四學生姓名 b. 擷取所有在 2004 及 2005 年由 King 教授所開的課程名稱 c. 對每一學期由 King 教授所開的課程, 擷取其課程編號學期 (semester) 學年 (year) 及修課的學生人數 d. 擷取每個主修 CS 的大四學生 (Class = 4) 的姓名與成績單成績單內容包括每門已修完的課程名稱課程編號學分數學期學年及成績 e. 擷取每科成績全都為 A 的所有學生的姓名與主修系所名稱 f. 擷取每科成績都不是 A 的所有學生的姓名與主修系所名稱第 8 章 SQL 查詢 40

41 學生與課程資訊的資料庫第 8 章 SQL 查詢 41

Microsoft PowerPoint - EMDBCh08SQL1.ppt

Microsoft PowerPoint - EMDBCh08SQL1.ppt 第 8 章 SQL-99: 綱要定義基本限制與查詢學習重點 CREATE TABLE DROP TABLE ALTER TABLE CREATE SCHEMA 參考完整性限制的選項 SQL2 與 SQL-99 的新增資料型態 SQL 中的基本查詢語法 SELECT-FROM-WHERE 結構簡單的 SQL 查詢別名 * 和 DISTINCT 的用法未指定的 WHERE 子句集合運算巢狀查詢與相互關聯的巢狀查詢