ASP.NET 2.0網頁設計範例教本

Size: px

Start display at page:

Download "ASP.NET 2.0網頁設計範例教本"

昶贡
6 years ago
Views:

1 第 9 章 SQL 基本查詢指令 9-1 SELECT 查詢指令 9-2 SELECT 子句 9-3 WHERE 子句 9-4 聚合函數的摘要查詢 9-5 群組查詢 GROUP BY 子句 9-6 排序 ORDER BY 子句

2 9-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

3 9-1 SELECT 查詢指令 - 圖例

4 9-1 SELECT 查詢指令 - 資料表範例

5 9-2 SELECT 子句顯示資料表的部分欄位顯示資料表的所有欄位欄位沒有重複值欄位別名

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

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

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

9 9-2-2 顯示資料表的所有欄位 - 範例 SQL 查詢範例 :Ch9_2_2_01.sql 查詢 Courses 資料表的所有課程記錄和欄位, 如下所示 : SELECT * FROM Courses

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

11 9-2-3 欄位沒有重複值 - 範例 SQL 查詢範例 :Ch9_2_3_01.sql 查詢 Courses 資料表的課程資料擁有幾種不同的學分數, 如下所示 : SELECT DISTINCT credits FROM Courses

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

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

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

15 9-3 WHERE 子句比較運算子邏輯運算子算術運算子

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

17 9-3-1 比較運算子 - 種類 WHERE 子句的條件是一個運算式, 運算元是欄位值, 可以是文字數值或日期 / 時間, 在運算式可以使用的比較運算子 (Comparison Operators), 如下表所示 : 運算子說明 = 相等 <> 不相等 > 大於 >= 大於等於 < 小於 <= 小於等於

18 9-3-1 比較運算子 - 條件值為字串 ( 說明 ) WHERE 子句的條件運算式可以使用比較運算子執行字串比較, 欄位條件的字串需要使用單引號括起

19 9-3-1 比較運算子 - 條件值為字串 ( 範例 1) SQL 查詢範例 :Ch9_3_1_01.sql 在 Students 資料表查詢學號為 'S002' 學生的詳細資料, 如下所示 : SELECT * FROM Students WHERE sid='s002'

20 9-3-1 比較運算子 - 條件值為字串 ( 範例 2) SQL 查詢範例 :Ch9_3_1_02.sql 在 Students 資料表查詢學號不等於 'S002' 的所有學生記錄和欄位資料, 如下所示 : SELECT * FROM Students WHERE sid<>'s002'

21 9-3-1 比較運算子 - 條件值為數值 ( 說明 ) WHERE 子句的條件運算式如果使用數值進行比較, 數值欄位不需使用單引號括起

22 9-3-1 比較運算子 - 條件值為數值 ( 範例 1) SQL 查詢範例 :Ch9_3_1_03.sql 查詢 Courses 資料表的學分 credits 欄位等於 3 的課程記錄, 如下所示 : SELECT * FROM Courses WHERE credits = 3

23 9-3-1 比較運算子 - 條件值為數值 ( 範例 2) SQL 查詢範例 :Ch9_3_1_04.sql 查詢 Courses 資料表的學分 credits 欄位小於等於 3 的課程記錄, 如下所示 : SELECT * FROM Courses WHERE credits <= 3

24 9-3-1 比較運算子 - 條件值為日期 / 時間 ( 說明 ) WHERE 子句的條件運算式如果是日期 / 時間的比較, 如同字串, 也需要使用單引號括起

25 9-3-1 比較運算子 - 條件值為日期 / 時間 ( 範例 1) SQL 查詢範例 :Ch9_3_1_05.sql 查詢 Students 資料表學生的生日是的學生記錄, 如下所示 : SELECT * FROM Students WHERE birthday=' '

26 9-3-1 比較運算子 - 條件值為日期 / 時間 ( 範例 2) SQL 查詢範例 :Ch9_3_1_06.sql 查詢 Students 資料表的學生生日小於等於的學生記錄, 如下所示 : SELECT * FROM Students WHERE birthday<=' '

27 9-3-2 邏輯運算子 - 種類 WHERE 子句的條件運算式可以使用邏輯運算子 (Logical Operators) 執行多樣化比較, 或連接多條件建立複雜的邏輯運算式, 如下表所示 : 運算子 LIKE BETWEEN/AND IN IS NULL EXISTS NOT AND OR 說明包含, 只需子字串即符合條件在一個範圍之內屬於清單其中之一是否是空值檢查括號中 SQL 指令查詢的記錄是否存在非, 可以否定運算式的結果且, 需要連接的 2 個運算子都會真, 才是真或, 只需其中一個運算子為真, 即為真

28 9-3-2 邏輯運算子 - LIKE 包含子字串運算子 ( 說明 ) WHERE 子句的條件欄位可以使用 LIKE 運算子進行比較,LIKE 運算子是子字串查詢, 只需是子字串就符合條件, 配合萬用字元可以進行範本字串的比對, 如下表所示 : 萬用字元說明 % 代表任何長度的任何字串 _ 代表一個字元長度的任何字元

29 9-3-2 邏輯運算子 - LIKE 包含子字串運算子 ( 範例 1) SQL 查詢範例 :Ch9_3_2_01.sql 查詢 Instructors 講師資料表中, 屬於資訊相關科系 CS 和 CIS 的講師記錄, 如下所示 : SELECT * FROM Instructors WHERE department LIKE '%S%'

30 9-3-2 邏輯運算子 - LIKE 包含子字串運算子 ( 範例 2) SQL 查詢範例 :Ch9_3_2_02.sql 查詢 Classes 資料表中, 上課教室是在二樓的學生上課資料, 如下所示 : SELECT eid, sid, c_no, time, room FROM Classes WHERE room LIKE '%2_-%'

31 9-3-2 邏輯運算子 -IS NULL 運算子 ( 說明 ) 如果查詢資料表中指定欄位值是否為空值 NULL, 可以使用 IS NULL 運算式和欄位值進行比較

32 9-3-2 邏輯運算子 -IS NULL 運算子 ( 範例 ) SQL 查詢範例 :Ch9_3_2_03.sql 查詢 Students 資料表沒有電話資料的學生記錄, 也就是 tel 欄位為空值, 如下所示 : SELECT * FROM Students WHERE tel IS NULL

33 9-3-2 邏輯運算子 -EXISTS 運算子 ( 說明 ) EXISTS 運算子可以檢查括號中的 SQL 查詢指令所查詢的記錄是否存在, 通常是使用在 SQL 的子查詢 (Subquery), 在第 10 章有進一步說明

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

35 9-3-2 邏輯運算子 - BETWEEN/AND 範圍運算子 ( 說明 ) BETWEEN/AND 運算子定義欄位值符合一個範圍, 範圍值可以是文字數值或和日期 / 時間資料

36 9-3-2 邏輯運算子 - BETWEEN/AND 範圍運算子 ( 範例 1) SQL 查詢範例 :Ch9_3_2_05.sql 查詢 Students 資料表生日 birthday 欄位的範圍是 1978 年 1 月 1 日到 2000 年 12 月 31 日出生的學生記錄, 即 10~33 屆的學生清單, 如下所示 : SELECT * FROM Students WHERE birthday BETWEEN AND

37 9-3-2 邏輯運算子 - BETWEEN/AND 範圍運算子 ( 範例 2) SQL 查詢範例 :Ch9_3_2_06.sql 因為學生修課學分數還差 2~3 個學分, 請查詢 Courses 資料表看還有哪些課可以修, 如下所示 : SELECT * FROM Courses WHERE credits BETWEEN 2 AND 3

38 9-3-2 邏輯運算子 -IN 運算子 ( 說明 ) IN 運算子只需清單其中之一即可, 需要列出一串文字或數值清單作為條件, 如果欄位值是其中之一就符合條件

39 9-3-2 邏輯運算子 -IN 運算子 ( 範例 1) SQL 查詢範例 :Ch9_3_2_07.sql 學生已經選 CS101 CS222 CS100 和 CS213 四門課, 所以準備查詢 Courses 資料表關於這些課程的詳細資料, 如下所示 : SELECT * FROM Courses WHERE c_no IN ('CS101', 'CS222', CS100, 'CS213')

40 9-3-2 邏輯運算子 -IN 運算子 ( 範例 2) SQL 查詢範例 :Ch9_3_2_08.sql 學生修課的學分數還差 3~4 個學分, 請使用 IN 運算子查詢 Courses 資料表看看還有哪些課可以修, 如下所示 : SELECT * FROM Courses WHERE credits IN (3, 4)

41 9-3-2 邏輯運算子 -NOT 運算子 ( 說明 ) NOT 運算子是用來搭配之前的邏輯運算子, 可以取得與條件相反的查詢結果, 如下表所示 : 運算子 NOT LIKE NOT BETWEEN NOT IN 說明否定 LIKE 運算式否定 BETWEEN/AND 運算式否定 IN 運算式

42 9-3-2 邏輯運算子 -NOT 運算子 ( 範例 ) SQL 查詢範例 :Ch9_3_2_09.sql 學生已經選修 CS101 CS222 CS100 和 CS213 四門課, 請查詢 Courses 資料表, 看看還有什麼課程可以修, 如下所示 : SELECT * FROM Courses WHERE c_no NOT IN ('CS101', 'CS222', CS100, 'CS213')

43 9-3-2 邏輯運算子 -AND 與 OR 運算子 ( 說明 ) AND 運算子連接的前後運算元都必須同時為真, 整個 WHERE 子句的條件才為真 OR 運算子在 WHERE 子句連接的前後條件, 只需任何一個條件為真, 即為真

44 9-3-2 邏輯運算子 -AND 與 OR 運算子 ( 範例 1) SQL 查詢範例 :Ch9_3_2_10.sql 查詢 Courses 資料表的課程 c_no 欄位包含 '1' 子字串, 而且課程名稱 title 欄位有 ' 程式 ' 子字串, 如下所示 : SELECT * FROM Courses WHERE c_no LIKE '%1%' AND title LIKE '% 程式 %'

45 9-3-2 邏輯運算子 -AND 與 OR 運算子 ( 範例 2) SQL 查詢範例 :Ch9_3_2_11.sql 查詢 Courses 資料表課程 c_no 欄位包含 '1' 子字串, 或課程名稱 title 欄位有 ' 程式 ' 子字串, 如下所示 : SELECT * FROM Courses WHERE c_no LIKE '%1%' OR title LIKE '% 程式 %'

46 9-3-3 算術運算子 - 說明 WHERE 子句的運算式條件可以使用算術運算子 (Arithmetic Operators), 算術運算子可以使用 SELECT 指令的欄位清單, 用來計算 2 個欄位的和, 使用各欄位組成一個算術運算式或是加上一個固定值運算子說明 + 加法 - 減法 * 乘法 / 除法

47 9-3-3 算術運算子 - 範例 1 SQL 查詢範例 :Ch9_3_3_01.sql 因為課程學分小於 4 的課程增加上課時數, 所以學分數需要加 1, 在尚未更新 Courses 資料表前, 請使用算術運算式查詢 Courses 資料表的課程資料, 替課程的學分自動加 1 來顯示, 如下所示 : SELECT c_no, title, credits + 1 AS NewCredits FROM Courses WHERE credits < 4

48 9-3-3 算術運算子 - 範例 2 SQL 查詢範例 :Ch9_3_3_02.sql 查詢 Courses 資料表的學分 credits 欄位, 都小於平均值 10.0/3.0= 的課程記錄, 如下所示 : SELECT * FROM Courses WHERE credits < (10.0 / 3.0)

49 9-4 聚合函數的摘要查詢 COUNT() 函數 AVG() 函數 MAX() 函數 MIN() 函數 SUM() 函數

50 9-4 聚合函數的摘要查詢聚合函數 (Aggregate Functions) 也稱為欄位函數 (Column Functions), 可以進行選取記錄欄位值的筆數平均範圍和統計函數, 以便提供進一步欄位資料的分析結果一般來說, 如果 SELECT 指令敘述擁有聚合函數, 我們稱為摘要查詢 (Summary Query) 常用的聚合函數說明, 如下表所示 : 函數 COUNT(Column) AVG(Column) MAX(Column) MIN(Column) SUM(Column) 說明計算記錄筆數計算欄位平均值取得記錄欄位的最大值取得記錄欄位的最小值取得記錄欄位的總計

51 9-4-1 COUNT() 函數 - 範例 1 SQL 查詢範例 :Ch9_4_1_01.sql 查詢 Students 資料表的學生總數, 如下所示 : SELECT COUNT(*) FROM Students SQL 查詢範例 :Ch9_4_1_02.sql 在 Students 資料表查詢擁有姓名的學生總數, 即學生欄位不是空值的記錄數, 如下所示 : SELECT COUNT(name) FROM Students

52 9-4-1 COUNT() 函數 - 範例 2 SQL 查詢範例 :Ch9_4_1_03.sql 大部分課程的學分數是 3, 查詢 Courses 資料表的 credits 學分數大於 3 的課程總數, 如下所示 : SELECT COUNT(*) FROM Courses WHERE credits > 3

53 9-4-2 AVG() 函數 - 範例 SQL 查詢範例 :Ch9_4_2_01.sql 在 Courses 資料表查詢各門課程的平均學分數, 如下所示 : SELECT AVG(credits) FROM Courses SQL 查詢範例 :Ch9_4_2_02.sql 在 Courses 資料表查詢課程編號 c_no 包含 '2' 子字串的課程總數和學分的平均值, 如下所示 : SELECT COUNT(*) AS Count, AVG(credits) AS Average FROM Courses WHERE c_no LIKE '%2%'

54 9-4-3 MAX() 函數 - 範例 SQL 查詢範例 :Ch9_4_3_01.sql 在 Students 資料表查詢年紀最小學生的生日 birthday, 如下所示 : SELECT MAX(birthday) FROM Students SQL 查詢範例 :Ch9_4_3_02.sql 在 Courses 資料表查詢課程編號 c_no 包含 '2' 子字串的最大學分數, 如下所示 : SELECT MAX(credits) FROM Courses WHERE c_no LIKE '%2%'

55 9-4-4 MIN() 函數 - 範例 SQL 查詢範例 :Ch9_4_4_01.sql 在 Students 資料表查詢最年長學生的生日 birthday, 如下所示 : SELECT MIN(birthday) FROM Students SQL 查詢範例 :Ch9_4_4_02.sql 在 Courses 資料表查詢課程編號 c_no 包含 '2' 子字串的最少學分數, 如下所示 : SELECT MIN(credits) FROM Courses WHERE c_no LIKE '%2%'

56 9-4-5 SUM() 函數 - 範例 SQL 查詢範例 :Ch9_4_5_01.sql 在 Courses 資料表查詢學分 credits 欄位的總和和平均, 如下所示 : SELECT SUM(credits), AVG(credits) FROM Courses SQL 查詢範例 :Ch9_4_5_02.sql 在 Courses 資料表計算課程編號 c_no 包含 '2' 子字串的學分數總和, 如下所示 : SELECT SUM(credits) FROM Courses WHERE c_no LIKE '%2%'

57 9-5 群組查詢 GROUP BY 子句 GROUP BY 子句的基礎 GROUP BY 與 WHERE 和 HAVING 子句

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

59 9-5-1 GROUP BY 子句的基礎 - 範例 1 SQL 查詢範例 :Ch9_5_1_01.sql 在 Classes 資料表查詢課程編號和計算每一門課程有多少位學生選修, 如下所示 : SELECT c_no, COUNT(*) AS num_of_students FROM Classes GROUP BY c_no

60 9-5-1 GROUP BY 子句的基礎 - 範例 2 SQL 查詢範例 :Ch9_5_1_02.sql 在 Classes 資料表查詢統計每一間教室提供給幾門課程來作為教室, 如下所示 : SELECT room, COUNT(*) AS num_of_courses FROM Classes GROUP BY room

61 9-5-2 GROUP BY 與 WHERE 和 HAVING 子句 - 說明 GROUP BY 子句的群組可以進一步使用 WHERE 子句的條件進行篩選, 或是 HAVING 子句進行搜尋 HAVING 子句是群組中的搜尋條件

62 9-5-2 GROUP BY 與 WHERE 和 HAVING 子句 - 範例 1 SQL 查詢範例 :Ch9_5_2_01.sql 在 Classes 資料表找出有幾位學生選修課程 CS222, 如下所示 : SELECT c_no, COUNT(*) AS num_of_students FROM Classes WHERE c_no = 'CS222' GROUP BY c_no

63 9-5-2 GROUP BY 與 WHERE 和 HAVING 子句 - 範例 2 SQL 查詢範例 :Ch9_5_2_02.sql 在 Classes 資料表找出學生 S001 選修的課程清單, 如下所示 : SELECT sid, c_no FROM Classes GROUP BY c_no, sid HAVING sid = 'S001'

64 9-5-2 GROUP BY 與 WHERE 和 HAVING 子句 - 範例 3 SQL 查詢範例 :Ch9_5_2_03.sql 在 Classes 資料表找出一門課有超過 2 位學生選修的課程清單, 如下所示 : SELECT c_no, COUNT(*) AS num_of_students FROM Classes GROUP BY c_no HAVING COUNT(*) >= 2

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

66 9-6 排序 ORDER BY 子句 - 由小到大排序在 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 9-6 排序 ORDER BY 子句 - 由大到小排序如果查詢結果的排序順序是倒過來由大到小, 只需在 ORDER BY 子句的最後加上 DESC 指令 SQL 查詢範例 :Ch9_6_02.sql 在 Courses 資料表查詢學分數 credits 大於等於 3 的課程記錄, 並且使用 credits 欄位進行由大到小排序, 如下所示 : SELECT * FROM Courses WHERE credits >= 3 ORDER BY credits DESC

Microsoft PowerPoint - ASP_NET_09

Microsoft PowerPoint - ASP_NET_09 第 9 章 T-SQL 語法與參數的 SQL 查詢大綱 SQL 語言的基礎 SELECT 敘述的基本查詢建立參數的 SQL 查詢新增更新與刪除記錄 2 SQL 語言的基礎 SQL (Structured Query Language) 的全名是結構化查詢語言, 本書簡稱 SQL 語言 SQL 語言是 ANSI (American National Standards Institute) 制定的標準資料庫語言,