ch09 SQL基本查詢指令

(1)

第9章 SQL基本查詢指令

 _{9-1 SELECT查詢指令}  9-2 SELECT子句  _{9-3 WHERE子句}  9-4 聚合函數的摘要查詢  _{9-5 群組查詢GROUP BY子句}  9-6 排序ORDER BY子句

(2)

 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

(3)

(4)

(5)

9-2 SELECT子句

 _{9-2-1 顯示資料表的部分欄位}  9-2-2 顯示資料表的所有欄位

 _{9-2-3 欄位沒有重複值}  9-2-4 欄位別名

(6)

 _{SELECT指令查詢資料表時，可以指明查詢結果所}

需的欄位清單，換句話說，可以只查詢資料表中所需的部分欄位。

(7)

SQL查詢範例：Ch9_2_1_01.sql

 查詢Students資料表的所有學生記錄，不過，只顯

示學號、姓名和生日3個欄位，如下所示：

SELECT sid, name, birthday FROM Students

(8)

 如果查詢結果需要顯示資料表的所有欄位，

SELECT指令可以直接使用’*’符號代表資料表的所有欄位，而不用一一列出欄位清單。

(9)

 查詢Courses資料表的所有課程記錄和欄位，如下

所示：

SELECT * FROM Courses

(10)

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

(11)

 查詢Courses資料表的課程資料擁有幾種不同的學

分數，如下所示：

SELECT DISTINCT credits FROM Courses

(12)

 _{SELECT指令預設使用關聯表綱要的欄位名稱來顯}

示查詢結果，基於顯示需要，我們可以使用AS關鍵字指定中文或英文的欄位別名。

(13)

 查詢Students資料表的sid、name和birthday欄位資料，為了方便閱讀，顯示欄位名稱是學號、姓名和生日的中文欄位別名，如下所示：

SELECT sid AS 學號, name AS 姓名, birthday AS 生日

FROM Students

(14)

 查詢Students資料表的sid、name和tel欄位資料，

為了方便閱讀，顯示的欄位名稱是StudentNum、 StudentName和Telephone的英文欄位別名，如下所示：

SELECT sid AS StudentNum, name AS StudentName, tel AS Telephone

FROM Students

(15)

9-3 WHERE子句

 _{9-3-1 比較運算子}  9-3-2 邏輯運算子

(16)

9-3 WHERE子句

 _{SELECT和FROM子句可以指出查詢哪個資料表的哪} 些欄位，而WHERE子句的篩選條件才是真正的查詢條件，可以過濾記錄和找出符合條件的記錄資料，其基本語法如下所示： WHERE conditions  上述conditions是搜尋條件，這是使用比較和邏輯運算子建立的過濾條件，可以取回符合條件的記錄資料。

(17)

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

9-3-1 比較運算子-種類

運算子說明 = 相等 <> 不相等 > 大於 >= 大於等於 < 小於 <= 小於等於

(18)

 _{WHERE子句的條件運算式可以使用比較運算子執}

行字串比較，欄位條件的字串需要使用單引號括起。

(19)

 在Students資料表查詢學號為'S002'學生的詳細資

料，如下所示：

SELECT * FROM Students WHERE sid='S002'

(20)

 在Students資料表查詢學號不等於'S002'的所有學

生記錄和欄位資料，如下所示：

SELECT * FROM Students WHERE sid<>'S002'

(21)

 _{WHERE子句的條件運算式如果使用數值進行比較，}

數值欄位不需使用單引號括起。

(22)

 查詢Courses資料表的學分credits欄位等於3的課程

記錄，如下所示：

SELECT * FROM Courses WHERE credits = 3

(23)

 查詢Courses資料表的學分credits欄位小於等於3的

課程記錄，如下所示：

SELECT * FROM Courses WHERE credits <= 3

(24)

 _{WHERE子句的條件運算式如果是日期/時間的比較，}

如同字串，也需要使用單引號括起。

(25)

 查詢Students資料表學生的生日是1978-02-02的學

生記錄，如下所示：

SELECT * FROM Students

WHERE birthday='1978-02-02'

(26)



查詢Students資料表的學生生日小於等於1978-02-02的學生記錄，如下所示：

WHERE birthday<='1978-02-02'

(27)

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

9-3-2 邏輯運算子-種類

運算子說明 LIKE 包含，只需子字串即符合條件 BETWEEN/AND 在一個範圍之內 IN 屬於清單其中之一 IS NULL 是否是空值 EXISTS 檢查括號中 SQL 指令查詢的記錄是否存在 NOT 非，可以否定運算式的結果 AND 且，需要連接的 2 個運算子都會真，才是真 OR 或，只需其中一個運算子為真，即為真

(28)

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

9-3-2 邏輯運算子-

LIKE包含子字串運算子(說明)

萬用字元說明 % 代表任何長度的任何字串 _ 代表一個字元長度的任何字元

(29)

 查詢Instructors講師資料表中，屬於資訊相關科系

CS和CIS的講師記錄，如下所示：

SELECT * FROM Instructors WHERE department LIKE '%S%'

9-3-2 邏輯運算子-

(30)

 查詢Classes資料表中，上課教室是在二樓的學生

上課資料，如下所示：

SELECT eid, sid, c_no, time, room FROM Classes WHERE room LIKE '%2_-%'

9-3-2 邏輯運算子-

(31)

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

(32)

 查詢Students資料表沒有電話資料的學生記錄，也

就是tel欄位為空值，如下所示：

SELECT * FROM Students WHERE tel IS NULL

(33)

 _{EXISTS運算子可以檢查括號中的SQL查詢指令所查}

詢的記錄是否存在，通常是使用在SQL的子查詢（Subquery），在第10章有進一步說明。

(34)

 在Classes資料表找出有學生選修的課程清單，如

下所示：

SELECT title FROM Courses

WHERE EXISTS (SELECT c_no FROM Classes

WHERE Courses.c_no = Classes.c_no )

(35)

 _{BETWEEN/AND運算子定義欄位值符合一個範圍，}

範圍值可以是文字、數值或和日期/時間資料。

9-3-2 邏輯運算子-

(36)

 查詢Students資料表生日birthday欄位的範圍是

1978年1月1日到2000年12月31日出生的學生記錄，即10~33屆的學生清單，如下所示：

WHERE birthday BETWEEN ‘1978-1-1’ AND ‘2000-12-31’

9-3-2 邏輯運算子-

(37)

 因為學生修課學分數還差2~3個學分，請查詢

Courses資料表看還有哪些課可以修，如下所示：

WHERE credits BETWEEN 2 AND 3

9-3-2 邏輯運算子-

(38)

 _{IN運算子只需清單其中之一即可，需要列出一串}

文字或數值清單作為條件，如果欄位值是其中之一就符合條件。

(39)

 學生已經選CS101、CS222、CS100和CS213四門課

，所以準備查詢Courses資料表關於這些課程的詳細資料，如下所示：

WHERE c_no IN ('CS101', 'CS222', ‘CS100’, 'CS213')

(40)

 學生修課的學分數還差3~4個學分，請使用IN運算

子查詢Courses資料表看看還有哪些課可以修，如下所示：

SELECT * FROM Courses WHERE credits IN (3, 4)

(41)

 _{NOT運算子是用來搭配之前的邏輯運算子，可以}

取得與條件相反的查詢結果，如下表所示：

9-3-2 邏輯運算子-NOT運算子(說明)

運算子說明

NOT LIKE 否定 LIKE 運算式

NOT BETWEEN 否定 BETWEEN/AND 運算式 NOT IN 否定 IN 運算式

(42)

 學生已經選修CS101、CS222、CS100和CS213四門

課，請查詢Courses資料表，看看還有什麼課程可以修，如下所示：

WHERE c_no NOT IN ('CS101', 'CS222', ‘CS100’, 'CS213')

(43)

 _{AND運算子連接的前後運算元都必須同時為真，}

整個WHERE子句的條件才為真。OR運算子在

WHERE子句連接的前後條件，只需任何一個條件為真，即為真。

(44)

 查詢Courses資料表的課程c_no欄位包含'1'子字串

，而且課程名稱title欄位有'程式'子字串，如下所示：

WHERE c_no LIKE '%1%' AND title LIKE '%程式%'

(45)

 查詢Courses資料表課程c_no欄位包含'1'子字串，

或課程名稱title欄位有'程式'子字串，如下所示：

WHERE c_no LIKE '%1%' OR title LIKE '%程式%'

(46)

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

9-3-3 算術運算子-說明

運算子說明 + 加法 - 減法 * 乘法 / 除法

(47)

 因為課程學分小於4的課程增加上課時數，所以學

分數需要加1，在尚未更新Courses資料表前，請使用算術運算式查詢Courses資料表的課程資料，替課程的學分自動加1來顯示，如下所示：

SELECT c_no, title, credits + 1 AS NewCredits FROM Courses

WHERE credits < 4

(48)

 _{查詢Courses資料表的學分credits欄位，都小於平}

均值10.0/3.0= 3.333的課程記錄，如下所示：

WHERE credits < (10.0 / 3.0)

(49)

 _{9-4-1 COUNT()函數}  9-4-2 AVG()函數  _{9-4-3 MAX()函數}  9-4-4 MIN()函數  _{9-4-5 SUM()函數}

9-4 聚合函數的摘要查詢

(50)

 「聚合函數」（Aggregate Functions）也稱為「欄位函數」（Column Functions），可以進行選取記錄欄位值的筆數、平均、範圍和統計函數，以便提供進一步欄位資料的分析結果。  一般來說，如果SELECT指令敘述擁有聚合函數，我們稱為「摘要查詢」（Summary Query）。常用的聚合函數說明，如下表所示：

9-4 聚合函數的摘要查詢

函數說明 COUNT(Column) 計算記錄筆數 AVG(Column) 計算欄位平均值 MAX(Column) 取得記錄欄位的最大值 MIN(Column) 取得記錄欄位的最小值 SUM(Column) 取得記錄欄位的總計

(51)

 查詢Students資料表的學生總數，如下所示：

SELECT COUNT(*) FROM Students

 在Students資料表查詢擁有姓名的學生總數，即學

生欄位不是空值的記錄數，如下所示：

SELECT COUNT(name) FROM Students

(52)

 大部分課程的學分數是3，查詢Courses資料表的

credits學分數大於3的課程總數，如下所示：

SELECT COUNT(*) FROM Courses WHERE credits > 3

(53)

 在Courses資料表查詢各門課程的平均學分數，如

下所示：

SELECT AVG(credits) FROM Courses

 在Courses資料表查詢課程編號c_no包含'2'子字串

的課程總數和學分的平均值，如下所示：

SELECT COUNT(*) AS Count, AVG(credits) AS Average

FROM Courses WHERE c_no LIKE '%2%'

(54)

 在Students資料表查詢年紀最小學生的生日

birthday，如下所示：

SELECT MAX(birthday) FROM Students

的最大學分數，如下所示：

SELECT MAX(credits) FROM Courses WHERE c_no LIKE '%2%'

(55)

 在Students資料表查詢最年長學生的生日birthday

，如下所示：

SELECT MIN(birthday) FROM Students

的最少學分數，如下所示：

SELECT MIN(credits) FROM Courses WHERE c_no LIKE '%2%'

(56)

 在Courses資料表查詢學分credits欄位的總和和平

均，如下所示：

SELECT SUM(credits), AVG(credits) FROM Courses

 在Courses資料表計算課程編號c_no包含'2'子字串

的學分數總和，如下所示：

SELECT SUM(credits) FROM Courses WHERE c_no LIKE '%2%'

(57)

 _{9-5-1 GROUP BY子句的基礎}

 9-5-2 GROUP BY與WHERE和HAVING子句

(58)

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

(59)

 在Classes資料表查詢課程編號和計算每一門課程

有多少位學生選修，如下所示：

SELECT c_no, COUNT(*) AS num_of_students FROM Classes

GROUP BY c_no

(60)

 在Classes資料表查詢統計每一間教室提供給幾門

課程來作為教室，如下所示：

SELECT room, COUNT(*) AS num_of_courses FROM Classes

GROUP BY room

(61)

 _{GROUP BY子句的群組可以進一步使用WHERE子句}

的條件進行篩選，或是HAVING子句進行搜尋。

 _{HAVING子句是群組中的搜尋條件。}

9-5-2 GROUP BY與WHERE和HAVING子句-

(62)

 在Classes資料表找出有幾位學生選修課程CS222，

如下所示：

WHERE c_no = 'CS222' GROUP BY c_no

9-5-2 GROUP BY與WHERE和HAVING子句-

(63)

 在Classes資料表找出學生S001選修的課程清單，

如下所示：

SELECT sid, c_no FROM Classes GROUP BY c_no, sid

HAVING sid = 'S001'

9-5-2 GROUP BY與WHERE和HAVING子句-

(64)

 在Classes資料表找出一門課有超過2位學生選修的

課程清單，如下所示：

GROUP BY c_no

HAVING COUNT(*) >= 2

9-5-2 GROUP BY與WHERE和HAVING子句-

(65)

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

9-6 排序ORDER BY子句-說明

(66)

 在SQL指令的WHERE子句或GROUP BY之後，加上 ORDER BY子句，可以指定顯示資料的排序欄位。 SQL查詢範例：Ch9_6_01.sql  在Courses資料表查詢學分數credits大於等於3的課程記錄，並且使用credits欄位進行由小到大排序，如下所示：

SELECT * FROM Courses WHERE credits >= 3

ORDER BY credits ASC

(67)

 如果查詢結果的排序順序是倒過來由大到小，只需在ORDER BY子句的最後加上DESC指令。 SQL查詢範例：Ch9_6_02.sql  在Courses資料表查詢學分數credits大於等於3的課程記錄，並且使用credits欄位進行由大到小排序，如下所示：

SELECT * FROM Courses WHERE credits >= 3

ORDER BY credits DESC

(68)