第11章 SQL基本查詢指令 11-1 SELECT查詢指令 11-2 WHERE子句的比較運算子 11-3 WHERE子句的邏輯運算子

Presentation on theme: "第11章 SQL基本查詢指令 11-1 SELECT查詢指令 11-2 WHERE子句的比較運算子 11-3 WHERE子句的邏輯運算子"— Presentation transcript:

1 第11章 SQL基本查詢指令 11-1 SELECT查詢指令 11-2 WHERE子句的比較運算子 11-3 WHERE子句的邏輯運算子
11-5 聚合函數 11-6 SQL語言的群組與排序

2 11-1 SELECT查詢指令 11-1-1 顯示資料表的部分欄位 11-1-2 顯示資料表的所有欄位 11-1-3 欄位沒有重複值
欄位別名

3 11-1 SELECT查詢指令-說明 SQL語言DML的資料查詢只有一個SELECT指令，其基本語法如下所示：
SELECT column1, column2, …, columnN FROM table1, table2, …, tableN WHERE conditions GROUP BY column1, column2, …, columnN HAVING search_conditions ORDER BY column1, column2, …, columnN

4 11-1 SELECT查詢指令-圖例

5 11-1 SELECT查詢指令-資料表範例

6 顯示資料表的部分欄位-說明 SELECT指令查詢資料表時，可以指明查詢結果所需的欄位清單，換句話說，可以只查詢資料表中所需的部分欄位。

7 11-1-1 顯示資料表的部分欄位-範例 SQL查詢範例：Ch11-1-1.sql
查詢Students資料表的所有學生記錄，不過，只顯示學號、姓名和生日三個欄位，如下所示： SELECT sid, name, birthday FROM Students

8 顯示資料表的所有欄位-說明 如果查詢結果需要顯示資料表的所有欄位，SELECT指令可以直接使用’*’符號代表資料表的所有欄位，而不用一一列出欄位清單。

9 11-1-2 顯示資料表的所有欄位-範例 SQL查詢範例：Ch11-1-2.sql
查詢Courses資料表的所有課程記錄並且顯示所有欄位，如下所示： SELECT * FROM Courses

10 欄位沒有重複值-說明 如果資料表記錄的欄位值擁有重複值，在SELECT指令後可以使用DISTINCT指令分辨重複的欄位值，一旦欄位擁有重複值，就只會顯示其中一筆記錄。

11 11-1-3 欄位沒有重複值-範例 SQL查詢範例：Ch11-1-3.sql
查詢Courses資料表課程資料擁有不同的學分數種類，如下所示： SELECT DISTINCT credits FROM Courses

12 欄位別名-說明 SELECT指令預設使用關聯表綱要的欄位名稱來顯示查詢結果，基於顯示需要，我們可以使用AS關鍵字指定中文或英文的欄位別名。

13 11-1-4 欄位別名-範例1 SQL查詢範例：Ch11-1-4-1.sql
查詢Students資料表的sid和name和birthday欄位資料，為了方便閱讀，顯示欄位名稱是學號、姓名和生日的中文欄位別名，如下所示： SELECT sid AS 學號, name AS 姓名, birthday AS 生日 FROM Students

14 11-1-4 欄位別名-範例2 SQL查詢範例：Ch11-1-4-2.sql
查詢Students資料表的sid和name和tel欄位資料，為了方便閱讀，顯示的欄位名稱是S_No、StudentName和Telephone的英文欄位別名，如下所示： SELECT sid AS S_No, name AS StudentName, tel AS Telephone FROM Students

15 11-2 WHERE子句的比較運算子 條件值為字串 條件值為數值 條件值為日期/時間

16 11-2 WHERE子句的比較運算子 WHERE子句的條件是一個運算式，運算元是欄位值，可以是文字、數值或日期/時間，在運算式可以使用的比較運算子（Comparison Operators），如下表所示：

17 條件值為字串-說明 WHERE子句的條件運算式可以使用比較運算子執行字串比較，欄位條件的字串需要使用單引號括起。

18 11-2-1 條件值為字串-範例1 SQL查詢範例：Ch11-2-1-1.sql
在Students資料表查詢學號為’S002’學生的詳細資料，如下所示： SELECT * FROM Students WHERE sid='S002'

19 11-2-1 條件值為字串-範例2 SQL查詢範例：Ch11-2-1-2.sql
在Students資料表查詢學號不等於’S002’的所有學生記錄和欄位資料，如下所示： SELECT * FROM Students WHERE sid<>'S002'

20 條件值為數值-說明 WHERE子句的條件運算式如果使用數值進行比較，數值欄位不需使用單引號括起。

21 11-2-2 條件值為數值-範例1 SQL查詢範例：Ch11-2-2-1.sql
查詢Students資料表的成績GPA欄位等於3.0的學生記錄，如下所示： SELECT * FROM Students WHERE GPA=3.0

22 11-2-2 條件值為數值-範例2 SQL查詢範例：Ch11-2-2-2.sql
查詢Students資料表的成績GPA欄位小於等於3.0的學生記錄，如下所示： SELECT * FROM Students WHERE GPA<=3.0

23 條件值為日期/時間-說明 WHERE子句的條件運算式如果是日期/時間的比較，如同字串，也需要使用單引號括起。

24 11-2-3 條件值為日期/時間-範例1 SQL查詢範例：Ch11-2-3-1.sql
查詢Students資料表學生生日是 的學生記錄，如下所示： SELECT * FROM Students WHERE birthday=' '

25 11-2-3 條件值為日期/時間-範例2 SQL查詢範例：Ch11-2-3-2.sql
查詢Students資料表的學生生日小於等於 的學生記錄，如下所示： SELECT * FROM Students WHERE birthday<=' '

26 11-3 WHERE子句的邏輯運算子 11-3-1 LIKE包含子字串運算子 11-3-2 IS NULL運算子
EXISTS運算子 BETWEEN/AND範圍運算子 IN運算子 NOT運算子 AND運算子 OR運算子

27 11-3 WHERE子句的邏輯運算子 WHERE子句的條件運算式可以使用邏輯運算子（Logical Operators）執行多樣化比較，或連接多條件建立複雜的邏輯運算式，如下表所示：

28 LIKE包含子字串運算子-說明 WHERE子句的條件欄位可以使用LIKE運算子進行比較，LIKE運算子是子字串查詢，只需是子字串就符合條件，配合萬用字元可以進行範本字串的比對，如下表所示：

29 11-3-1 LIKE包含子字串運算子-範例1 SQL查詢範例：Ch11-3-1-1.sql
查詢Instructors講師資料表中屬於資訊相關科系CS和CIS的講師記錄，如下所示： SELECT * FROM Instructors WHERE department LIKE '%S%'

30 11-3-1 LIKE包含子字串運算子-範例2 SQL查詢範例：Ch11-3-1-2.sql
查詢Classes資料表上課教室是在二樓的學生上課資料，如下所示： SELECT eid, sid, c_no, c_time, room FROM Classes WHERE room LIKE '%2_-%'

31 IS NULL運算子-說明 如果查詢資料表中指定欄位值是否為空值NULL，可以使用IS NULL運算式和欄位值進行比較。

32 11-3-2 IS NULL運算子-範例 SQL查詢範例：Ch11-3-2.sql
查詢Students資料表沒有電話資料的學生記錄，也就是tel欄位為空值，如下所示： SELECT * FROM Students WHERE tel IS NULL

33 EXISTS運算子-說明 EXISTS運算子可以檢查括號中的SQL查詢指令所查詢的記錄是否存在，通常是使用在SQL的子查詢（Subquery），在第12章有進一步說明。

34 11-3-3 EXISTS運算子-範例 SQL查詢範例：Ch11-3-3.sql 在Classes資料表找出有學生選修的課程清單，如下所示：
SELECT title FROM Courses WHERE EXISTS (SELECT c_no FROM Classes WHERE Courses.c_no = Classes.c_no )

35 11-3-4 BETWEEN/AND範圍運算子-說明

36 11-3-4 BETWEEN/AND範圍運算子-範例1
SQL查詢範例：Ch sql 查詢Students資料表生日birthday欄位的範圍是1978年1月1日到2000年12月31日出生的學生記錄，這是第十屆到第三十三屆的學生清單，如下所示： SELECT * FROM Students WHERE birthday BETWEEN ‘ ’ AND ‘ ’

37 11-3-4 BETWEEN/AND範圍運算子-範例2
SQL查詢範例：Ch sql 學生修課學分數還差2到3個學分，查詢Courses資料表看看還有哪些課可以選修，如下所示： SELECT * FROM Courses WHERE credits BETWEEN 2 AND 3

38 IN運算子-說明 IN運算子只需清單其中之一即可，需要列出一串文字或數值清單作為條件，如果欄位值是其中之一就符合條件。

39 11-3-5 IN運算子-範例1 SQL查詢範例：Ch11-3-5-1.sql
學生一共選了CS101、CS222、CS100和CS213四門課，查詢Courses資料表關於這些課程的詳細資料，如下所示： SELECT * FROM Courses WHERE c_no IN ('CS101', 'CS222', ‘CS100’, 'CS213')

40 11-3-5 IN運算子-範例2 SQL查詢範例：Ch11-3-5-2.sql
學生修課的學分數還差3到4個學分，請使用IN運算子查詢Courses資料表看看還有哪些課可以選修，如下所示： SELECT * FROM Courses WHERE credits IN (3, 4)

41 NOT運算子-說明 NOT運算子是用來搭配之前的邏輯運算子，可以取得與條件相反的查詢結果，如下表所示：

42 11-3-6 NOT運算子-範例 SQL查詢範例：Ch11-3-6.sql
學生已經選修CS101、CS222、CS100和CS213四門課，查詢Courses資料表看看還有什麼課程可以選修，如下所示： SELECT * FROM Courses WHERE c_no NOT IN ('CS101', 'CS222', ‘CS100’, 'CS213')

43 AND運算子-說明 AND運算子連接的前後運算式都必須同時為真，整個WHERE子句的條件才為真。

44 11-3-7 AND運算子-範例 SQL查詢範例：Ch11-3-7.sql
查詢Courses資料表的課程c_no欄位包含’1’子字串，而且課程名稱title欄位有’程式’子字串，如下所示： SELECT * FROM Courses WHERE c_no LIKE '%1%' AND title LIKE '%程式%'

45 OR運算子-說明 OR運算子在WHERE子句連接的前後條件只需任何一個條件為真，即為真。

46 11-3-8 OR運算子-範例 SQL查詢範例：Ch11-3-8.sql
查詢Courses資料表的課程c_no欄位包含’1’子字串，或課程名稱title欄位有’程式’子字串，如下所示： SELECT * FROM Courses WHERE c_no LIKE '%1%' OR title LIKE '%程式%'

47 11-4 WHERE子句的算術運算子-說明 WHERE子句的運算式條件可以使用算術運算子（Arithmetic Operators），算術運算子可以使用SELECT指令的欄位清單，用來計算2個欄位的和，使用各欄位組成一個算術運算式或是加上一個固定值。

48 11-4 WHERE子句的算術運算子-範例1 SQL查詢範例：Ch11-4-1.sql
因為課程學分小於4的課程學分都額外多了1個學分，在尚未更新Courses資料表前，可以使用算術運算式查詢Courses資料表的課程資料，替這些課程的學分自動加一來顯示，如下所示： SELECT c_no, title, credits + 1 AS NewCredits FROM Courses WHERE credits < 4

49 11-4 WHERE子句的算術運算子-範例2 SQL查詢範例：Ch11-4-2.sql
查詢Students資料表的學生成績GPA欄位小於另一班平均值10.0/3.0= 3.333的學生記錄，如下所示： SELECT sid, name, birthday, tel FROM Students WHERE GPA < (10.0 / 3.0)

50 11-5 ANSI-SQL的聚合函數 11-5-1 COUNT()函數 11-5-2 AVG()函數 11-5-3 MAX()函數
MIN()函數 SUM()函數

51 11-5 聚合函數 「聚合函數」（Aggregate Functions）可以進行資料表欄位的筆數、平均、範圍和統計函數，以便提供進一步欄位資料的分析結果，如下表所示：

52 11-5-1 COUNT()函數-範例1 SQL查詢範例：Ch11-5-1-1.sql 查詢Students資料表的學生總數，如下所示：
SELECT COUNT(*) FROM Students

53 11-5-1 COUNT()函數-範例2 SQL查詢範例：Ch11-5-1-2.sql
查詢Students資料表的擁有姓名的學生總數，即學生欄位不是空值，如下所示： SELECT COUNT(name) FROM Students

54 11-5-1 COUNT()函數-範例3 SQL查詢範例：Ch11-5-1-3.sql
學生GPA成績是3.0，查詢Students資料表的學生GPA成績大於3.0的學生總數，以便查詢有幾位學生的成績比我高，如下所示： SELECT COUNT(*) FROM Students WHERE GPA > 3.0

55 11-5-2 AVG()函數-範例1 SQL查詢範例：Ch11-5-2-1.sql
查詢Students資料表各位學生的GPA平均值，如下所示： SELECT AVG(GPA) FROM Students

56 11-5-2 AVG()函數-範例2 SQL查詢範例：Ch11-5-2-2.sql
查詢Courses資料表中，課程編號c_no包含’1’子字串的課程總數和學分的平均值，如下所示： SELECT COUNT(*) AS Count, AVG(credits) AS Average FROM Courses WHERE c_no LIKE '%1%'

57 11-5-3 MAX()函數-範例1 SQL查詢範例：Ch11-5-3-1.sql
查詢Students資料表的第一名學生的GPA成績，如下所示： SELECT MAX(GPA) FROM Students

58 11-5-3 MAX()函數-範例2 SQL查詢範例：Ch11-5-3-2.sql
查詢Courses資料表中，課程編號c_no包含’1’子字串的最大學分數，如下表所示： SELECT MAX(credits) FROM Courses WHERE c_no LIKE '%1%'

59 11-5-4 MIN()函數-範例1 SQL查詢範例：Ch11-5-4-1.sql
查詢Students資料表的最後一名學生的GPA成績，如下所示： SELECT MIN(GPA) FROM Students

60 11-5-4 MIN()函數-範例2 SQL查詢範例：Ch11-5-4-2.sql
查詢Courses資料表中，課程編號c_no包含’1’子字串的最少學分數，如下所示： SELECT MIN(credits) FROM Courses WHERE c_no LIKE '%1%'

61 11-5-5 SUM()函數-範例1 SQL查詢範例：Ch11-5-5-1.sql
計算Students資料表這一班學生GPA成績的總和和平均，如下所示： SELECT SUM(GPA), SUM(GPA)/COUNT(*) FROM Students

62 11-5-5 SUM()函數-範例2 SQL查詢範例：Ch11-5-5-2.sql
計算Courses資料表中，課程編號c_no包含’1’子字串的學分數總和，如下所示： SELECT SUM(credits) FROM Courses WHERE c_no LIKE '%1%'

63 11-6 SQL語言的群組與排序 11-6-1 群組資料GROUP BY子句 11-6-2 GROUP BY與 WHERE和HAVING子句
排序資料ORDER BY 子句

64 群組資料GROUP BY子句-說明 群組是以指定欄位進行分類，將欄位值中重複的值結合起，例如：在Classes資料表統計每一門課有多少位學生選修，課程c_no欄位是群組欄位，可以將選修課程的學生結合起來，如下圖所示：

65 11-6-1 群組資料GROUP BY子句-範例1 SQL查詢範例：Ch11-6-1-1.sql
查詢Classes資料表顯示課程編號和計算每一門課程有多少位學生選修，如下所示： SELECT c_no, COUNT(*) FROM Classes GROUP BY c_no

66 11-6-1 群組資料GROUP BY子句-範例2 SQL查詢範例：Ch11-6-1-2.sql
查詢Classes資料表，統計每一間教室提供給幾門課作為教室，如下所示： SELECT room, COUNT(*) FROM Classes GROUP BY room

67 11-6-2 GROUP BY與WHERE和HAVING子句-說明

68 11-6-2 GROUP BY與WHERE和HAVING子句-範例1
SQL查詢範例：Ch sql 查詢Classes資料表找出有有幾位學生選修課程CS222，如下所示： SELECT c_no, COUNT(*) FROM Classes WHERE c_no = ‘CS222’ GROUP BY c_no

69 11-6-2 GROUP BY與WHERE和HAVING子句-範例2
SQL查詢範例：Ch sql 查詢Classes資料表找出學生S001選修的課程清單，如下所示： SELECT sid, c_no FROM Classes GROUP BY c_no, sid HAVING sid = 'S001'

70 11-6-2 GROUP BY與WHERE和HAVING子句-範例3
SQL查詢範例：Ch sql 查詢Classes資料表找出一門課有超過2位學生選修的課程清單，如下所示： SELECT c_no, COUNT(*) FROM Classes GROUP BY c_no HAVING COUNT(*) >= 2

71 排序資料ORDER BY 子句-說明 SQL指令的查詢結果如果需要以指定欄位進行排序，可以使用ORDER BY子句指定依照欄位由小到大或由大到小進行排序。 由小到大排序：在SQL指令的WHERE子句或GROUP BY之後加上ORDER BY子句，就可以指定顯示資料的排序欄位。 由大到小排序：如果查詢結果的排序順序是倒過來由大到小，只需在ORDER BY子句的最後加上DESC指令。

72 11-6-3 排序資料ORDER BY 子句-範例1 SQL查詢範例：Ch11-6-3-1.sql
查詢Students資料表學生成績GPA大於等於3.0的學生記錄，並且使用GPA欄位進行由小到大排序，如下所示： SELECT * FROM Students WHERE GPA >= 3.0 ORDER BY GPA ASC

73 11-6-3 排序資料ORDER BY 子句-範例2 SQL查詢範例：Ch11-6-3-2.sql
查詢Students資料表學生成績GPA大於等於3.0的學生記錄，並且使用GPA欄位進行由大到小排序，如下所示： SELECT * FROM Students WHERE GPA >= 3.0 ORDER BY GPA DESC