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

Size: px

Start display at page:

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

螗温管
4 years ago
Views:

1 第 10 章 SQL 合併查詢與子查詢 10-1 SQL 合併查詢的基礎 10-2 交叉合併查詢 10-3 θ 合併與 EquiJoins 合併查詢 10-4 自然合併查詢 10-5 外部合併查詢 10-6 集合運算的查詢 10-7 SQL 子查詢 10-8 合併更新與刪除

2 10-1 SQL 合併查詢的基礎 - 說明 SQL 合併查詢 (Join) 是使用在多個資料表的查詢, 其主要的目的是將關聯式資料庫正規化分析分割的資料表, 還原成使用者所需的資訊, 因為正規化的目的是避免資料重複, 但是, 擁有重複資料的資訊反而易於使用者閱讀和了解例如 : 在 Classes 資料表只有儲存學號 sid 講師編號 eid, 透過合併查詢, 才可以得知學生和講師姓名等相關資訊

3 10-1 SQL 合併查詢的基礎 - 種類 SQL 合併查詢是將儲存在不同資料表的欄位資料取出, 合併成所需的資訊, 常用的合併查詢, 依查詢範圍從大到小, 如下所示 : 交叉合併查詢 θ 合併 EquiJoins 合併查詢自然合併查詢外部合併查詢

4 10-1 SQL 合併查詢的基礎 - 資料表範例

5 10-1 SQL 合併查詢的基礎 - 外來鍵參考圖

6 10-2 交叉合併查詢 - 說明 SQL 的 CROSS JOIN 交叉合併查詢指令是關聯式代數的卡笛生乘積運算 (Cartesian Product), 查詢結果的記錄數是兩個資料表記錄數的乘積

7 10-2 交叉合併查詢 - 範例 1 SQL 查詢範例 :Ch10_2_01.sql 使用交叉合併查詢從學生 Students 資料表取出 sid 與 name 欄位, 和從 Classes 選課資料表的課程編號 c_no 與講師編號 eid 欄位, 如下所示 : SELECT Students.sid, Students.name, Classes.c_no, Classes.eid FROM Students CROSS JOIN Classes

8 10-2 交叉合併查詢 - 範例 2 SQL 查詢範例 :Ch10_2_02.sql 請使用交叉合併查詢配合 WHERE 子句, 找出 Students 和 Classes 資料表每位學生的選課記錄, 條件是 2 個資料表的學號 sid 相等, 如下所示 : SELECT Students.sid, Students.name, Classes.c_no, Classes.eid FROM Students CROSS JOIN Classes WHERE Students.sid = Classes.sid

9 10-3 θ 合併與 EquiJoins 合併查詢 θ 合併查詢 EquiJoins 合併查詢

10 θ 合併查詢 - 說明 θ 合併 (Theta Join) 查詢的 SQL 合併條件是使用 = > < >= <= 或 <> 比較運算子來合併 2 個資料表

10-3-1 θ 合併查詢 - 範例 SQL 查詢範例 :Ch10_3_1_01.sql 在 Classes 資料表找出同一位講師教授的同一門課程中, 有哪些學生的成績比你高, 如下所示 : SELECT c1.c_no, c1.eid, c1.sid AS C1_SID, c1.

11 θ 合併查詢 - 範例 SQL 查詢範例 :Ch10_3_1_01.sql 在 Classes 資料表找出同一位講師教授的同一門課程中, 有哪些學生的成績比你高, 如下所示 : SELECT c1.c_no, c1.eid, c1.sid AS C1_SID, c1.grade AS C1_GRADE, c2.sid AS C2_SID, c2.grade AS C2_GRADE FROM Classes c1, Classes c2 WHERE c1.c_no = c2. c_no AND c1.eid = c2.eid AND c1.grade > c2.grade

12 EquiJoins 合併查詢 - 說明 EquiJoins 合併查詢通常是使用在兩個資料表主鍵和外來鍵擁有關聯性 (Relationship) 的資料查詢, 只取出兩個資料表都符合條件的記錄, 其基本語法如下所示 : SELECT table1.column1, table2.column2 [, table3.column3] FROM table1, table2.[, table3 ] WHERE table1.column = tabel2.column [ and table1.column = table3.column ] EquiJoins 合併查詢 WHERE 子句的條件是使用比較運算子 = 等號合併兩個資料表

10-3-2 EquiJoins 合併查詢 - 範例 1 SQL 查詢範例 :Ch10_3_2_01.

13 EquiJoins 合併查詢 - 範例 1 SQL 查詢範例 :Ch10_3_2_01.sql 在 Students 和 Classes 資料表合併查詢學生的詳細資料, 和每位學生詳細的選課資料, 如下所示 : SELECT * FROM Students, Classes WHERE Students.sid = Classes.sid

10-3-2 EquiJoins 合併查詢 - 範例 2 SQL 查詢範例 :Ch10_3_2_02.

14 EquiJoins 合併查詢 - 範例 2 SQL 查詢範例 :Ch10_3_2_02.sql 在 Courses 和 Classes 資料表查詢每一門課程的詳細資料和其上課的教室編號, 如下所示 : SELECT Courses.*, Classes.room FROM Courses, Classes WHERE Courses.c_no = Classes.c_no

15 10-4 自然合併查詢自然合併查詢 INNER JOIN 指令

16 10-4 自然合併查詢 - 圖例關聯式代數的自然合併 (Natural Join) 查詢就是 SQL 語言的 INNER JOIN 內部合併查詢, 如下圖所示 :

17 自然合併查詢 - 說明如果關聯式資料庫管理系統的 SQL 語言沒有支援內部合併查詢指令 INNER JOIN, 其實, 我們只需稍微修改 EquiJoins 合併查詢的 SQL 指令, 一樣可以顯示自然合併查詢的結果

10-4-1 自然合併查詢 - 範例 SQL 查詢範例 :Ch10_4_1_01.sql 在 Students 和 Classes 兩個資料表查詢學生和選課的詳細資料, 如下所示 : SELECT Students.

18 自然合併查詢 - 範例 SQL 查詢範例 :Ch10_4_1_01.sql 在 Students 和 Classes 兩個資料表查詢學生和選課的詳細資料, 如下所示 : SELECT Students.*,Classes.eid,Classes.c_no, Classes.time,Classes.room FROM Students, Classes WHERE Students.sid = Classes.sid

19 INNER JOIN 指令 - 說明 SQL 語言的 INNER JOIN 內部合併查詢指令就是自然合併查詢, 可以取回兩個資料表都存在合併欄位的記錄

10-4-2 INNER JOIN 指令 - 範例 1 SQL 查詢範例 :Ch10_4_2_01.

20 INNER JOIN 指令 - 範例 1 SQL 查詢範例 :Ch10_4_2_01.sql 使用內部合併查詢從學生 Students 資料表取出 sid 與 name 欄位, 和從 Classes 選課資料表的課程編號 c_no 與講師編號 eid 欄位來顯示學生的選課資料, 合併欄位是學號 sid, 如下所示 : SELECT Students.sid, Students.name, Classes.c_no, Classes.eid FROM Students INNER JOIN Classes ON Students.sid = Classes.sid

21 INNER JOIN 指令 - 範例 2 SQL 查詢範例 :Ch10_4_2_02.sql Transact-SQL 可以擴充 Ch10_4_2_01.sql 的內部合併查詢, 再次執行 INNER JOIN 合併查詢 Courses 資料表, 以便取得課程的詳細資料, 如下所示 : SELECT Students.sid, Students.name, Courses.*, Classes.eid FROM Courses INNER JOIN (Students INNER JOIN Classes ON Students.sid = Classes.sid) ON Classes.c_no = Courses.c_no

22 INNER JOIN 指令 - 結果 2

23 INNER JOIN 指令 - 範例 3 SQL 查詢範例 :Ch10_4_2_03.sql Transact-SQL 可以再擴充 Ch10_4_2_02.sql 的內部合併查詢, 再次執行 INNER JOIN 合併查詢 Instructors 資料表, 以便取得講師的詳細資料, 如下所示 : SELECT Students.sid, Students.name, Courses.*, Instructors.* FROM Instructors INNER JOIN (Courses INNER JOIN (Students INNER JOIN Classes ON Students.sid = Classes.sid) ON Classes.c_no = Courses.c_no) ON Classes.eid = Instructors.eid

24 INNER JOIN 指令 - 結果 3

25 10-5 外部合併查詢 LEFT JOIN 左外部合併查詢 RIGHT JOIN 右外部合併查詢 FULL JOIN 完全外部合併查詢

26 10-5 外部合併查詢 SQL 語言的 OUTER JOIN 外部合併查詢指令可以取回指定資料表的所有記錄, 它和 INNER JOIN 內部合併查詢的差異在於查詢結果並不是兩個資料表都一定存在的記錄, 一共分成三種 OUTER JOIN 指令, 如下所示 : 左外部合併 (LEFT JOIN) 右外部合併 (RIGHT JOIN) 完全外部合併 (FULL JOIN)

27 LEFT JOIN 左外部合併查詢 - 說明左外部合併是在合併的兩個資料表中, 取回左邊資料表內的所有記錄, 不論是否在右邊資料表存在合併欄位值

28 LEFT JOIN 左外部合併查詢 - 範例 SQL 查詢範例 :Ch10_5_1_01.sql 使用 Students 與 Classes 資料表執行左外部合併查詢, 合併欄位是學號 sid, 可以顯示 Students 資料表的所有記錄, 如下所示 : SELECT Students.sid, Students.name, Classes.c_no, Classes.grade FROM Students LEFT JOIN Classes ON Students.sid = Classes.sid

29 LEFT JOIN 左外部合併查詢 - 結果

30 RIGHT JOIN 右外部合併查詢 - 說明右外部合併可以取回右邊資料表內的所有記錄, 不論是否在左邊資料表存在合併欄位值

31 RIGHT JOIN 右外部合併查詢 - 範例 1 SQL 查詢範例 :Ch10_5_2_01.sql 使用 Classes 與 Students 資料表執行右外部合併查詢, 合併欄位是學號 sid, 可以顯示 Students 資料表的所有記錄, 如下所示 : SELECT Classes.c_no, Classes.grade, Students.sid, Students.name FROM Classes RIGHT JOIN Students ON Classes.sid = Students.sid

32 RIGHT JOIN 右外部合併查詢 - 結果 1

33 RIGHT JOIN 右外部合併查詢 - 範例 2 SQL 查詢範例 :Ch10_5_2_02.sql Transact-SQL 使用多種 JOIN 指令來合併 Students Courses 和 Classes 資料表, 如下所示 : SELECT Students.sid, Students.name, Courses.*, Classes.eid FROM Courses RIGHT JOIN (Students INNER JOIN Classes ON Students.sid = Classes.sid) ON Classes.c_no = Courses.c_no

34 RIGHT JOIN 右外部合併查詢 - 結果 2

35 FULL JOIN 完全外部合併查詢 - 說明不同於 LEFT JOIN 左外部合併查詢和 RIGHT JOIN 右外部合併查詢,FULL JOIN 完全外部合併可以取回左右邊資料表內的所有記錄

36 FULL JOIN 完全外部合併查詢 - 範例 SQL 查詢範例 :Ch10_5_3_01.sql 在 Students 與 Classes 資料表執行完全外部合併查詢, 合併欄位是學號 sid, 如下所示 : SELECT Students.sid, Students.name, Classes.c_no, Classes.grade FROM Students FULL JOIN Classes ON Students.sid = Classes.sid

37 FULL JOIN 完全外部合併查詢 - 結果

38 10-6 集合運算的查詢集合運算的查詢種類 UNION 聯集查詢 INTERSECT 交集查詢 EXCEPT 差集查詢

39 集合運算的查詢種類 -1 聯集 UNION: 將兩個資料表的記錄都全部結合成在一起, 如果有重複記錄, 只顯示其中一筆, 其基本語法如下所示 : SELECT column1, column2 FROM table1 UNION SELECT column1, column2 FROM table2 交集 INTERSECT: 從兩個資料表取出同時存在的記錄, 其基本語法如下所示 : SELECT column1, column2 FROM table1 INTERSECT SELECT column1, column2 FROM table2

40 集合運算的查詢種類 -2 差集 EXCEPT: 只取出存在第一列 SELECT 指令的記錄, 但是不存在第二列 SELECT 指令的記錄, 其基本語法如下所示 : SELECT column1, column2 FROM table1 EXCEPT SELECT column1, column2 FROM table2

41 UNION 聯集查詢 SQL 查詢範例 :Ch10_6_2_01.sql 請針對 Students 和 Instructors 兩個資料表的 name 欄位, 使用聯集運算取出所有學生和講師姓名, 如下所示 : SELECT name FROM Students UNION SELECT name FROM Instructors

42 INTERSECT 交集查詢 SQL 查詢範例 :Ch10_6_3_01.sql 請針對 Students 和 Instructors 兩個資料表的 name 欄位, 使用交集運算取出存在 2 個資料表的學生和講師姓名, 如下所示 : SELECT name FROM Students INTERSECT SELECT name FROM Instructors

43 EXCEPT 差集查詢 SQL 查詢範例 :Ch10_6_4_01.sql 請針對 Students 和 Instructors 兩個資料表的 name 欄位, 使用差集運算取出存在 Students 資料表, 但是不存在 Instructors 資料表的姓名資料, 如下所示 : SELECT name FROM Students EXCEPT SELECT name FROM Instructors

44 10-7 SQL 子查詢子查詢的基礎比較運算子的子查詢邏輯運算子的子查詢

45 10-7 SQL 的子查詢 SQL 子查詢 (Subquery) 也屬於一種多重資料表的查詢, 子查詢是指在 SQL 查詢指令之中擁有其他查詢指令, 也稱為巢狀查詢 (Nested Query) 因為每一個子查詢也是一個 SELECT 查詢指令, 換句話說, 我們可以在多個資料表進行查詢, 以便取得所需的查詢結果

46 子查詢的基礎 - 說明子查詢是附屬在 SQL 查詢指令, 通常是位在主查詢 SELECT 指令的 WHERE 子句, 以便透過子查詢取得所需的查詢條件, 子查詢本身也是一個 SELECT 指令如果 SQL 查詢指令擁有子查詢, 首先處理的是子查詢, 然後才依子查詢的條件處理主查詢, 取得查詢結果

47 子查詢的基礎 - 語法子查詢可以使用在 SELECT 指令的 WHERE 子句或 GOUP BY 子句的 HAVING 子句, 也可以使用在邏輯或比較運算子的運算式中, 子查詢的基本語法, 如下所示 : SELECT column1, column2,, columnn FROM table1 WHERE column = (SELECT column FROM table2 WHERE conditions)

48 子查詢的基礎 - 注意事項子查詢是位在 SQL 指令的括號之中通常子查詢的 SELECT 指令只會取得單一欄位值, 以便與主查詢的欄位進行比較運算如果需要排序, 主查詢可以使用 ORDER BY 子句, 子查詢不可以使用 ORDER BY 子句, 不過可以使用 GROUP BY 子句來替代如果子查詢取得多筆記錄, 在主查詢需要使用 IN 邏輯運算子 BETWEEN/AND 邏輯運算子不能使用在主查詢, 但是可以在子查詢使用

49 比較運算子的子查詢 - 範例 1 SQL 查詢範例 :Ch10_7_2_01.sql 在 Students 資料表使用姓名 name 欄位取得學號 sid 後, 查詢 Classes 資料表的學生陳會安共選了幾門課, 如下所示 : SELECT COUNT(*) AS num_of_courses FROM Classes WHERE sid = (SELECT sid FROM Students WHERE name=' 陳會安 ')

50 比較運算子的子查詢 - 範例 2 SQL 查詢範例 :Ch10_7_2_02.sql 在 Courses 資料表找出學分 credits 欄位值高於平均學分的課程資料, 如下所示 : SELECT * FROM Courses WHERE credits > (SELECT AVG(credits) FROM Courses)

51 邏輯運算子的子查詢 - 說明 SQL 的邏輯運算子 EXISTS 和 IN 都可以使用在子查詢, EXISTS 運算子判斷子查詢的結果是否有傳回資料, 其傳回值可以是多個欄位資料 IN 運算子可以檢查是否存在子查詢取得的記錄資料之中, 傳回值是單一欄位的多筆記錄

52 邏輯運算子的子查詢 -EXISTS 指令 ( 範例 1) SQL 查詢範例 :Ch10_7_3_01.sql 在 Students 資料表顯示 Classes 資料表有選修 CS222 課程編號的學生資料, 如下所示 : SELECT sid, name, tel FROM Students WHERE EXISTS (SELECT * FROM Classes WHERE c_no = CS222 and Students.sid = Classes.sid)

53 邏輯運算子的子查詢 -EXISTS 指令 ( 範例 2) SQL 查詢範例 :Ch10_7_3_02.sql 從 Classes 和 Courese 資料表取出所有在 221-S 和 100- M 教室上課的課程資料, 如下所示 : SELECT * FROM Courses WHERE EXISTS (SELECT * FROM Classes WHERE (room= 221-S or room= 100-M ) and Courses.c_no=Classes.c_no)

54 邏輯運算子的子查詢 -EXISTS 指令 ( 範例 3) SQL 查詢範例 :Ch10_7_3_03.sql 請改用合併查詢取得和 Ch10_7_3_02.sql 相同的查詢結果, 如下所示 : SELECT DISTINCT Courses.* FROM Courses, Classes WHERE (Classes.room='221-S' or Classes.room='100-M') and Courses.c_no=Classes.c_no

10-7-3 邏輯運算子的子查詢 -IN 指令 ( 範例 1) SQL 查詢範例 :Ch10_7_3_04.

55 邏輯運算子的子查詢 -IN 指令 ( 範例 1) SQL 查詢範例 :Ch10_7_3_04.sql 從 Courses 和 Classes 資料表取出學號 S004 沒有選修的課程, 如下所示 : SELECT * FROM Courses WHERE c_no NOT IN (SELECT c_no FROM Classes WHERE sid='s004')

56 邏輯運算子的子查詢 -IN 指令 ( 範例 2) SQL 查詢範例 :Ch10_7_3_05.sql 請使用三層巢狀查詢從 Students Classes 和 Instructors 資料表, 找出學生江小魚選修哪些講師的哪些課程, 如下所示 : SELECT * FROM Instructors WHERE eid IN (SELECT eid FROM Classes WHERE sid=(select sid FROM Students WHERE name = ' 江小魚 '))

57 邏輯運算子的子查詢 -IN 指令 ( 範例 3) SQL 查詢範例 :Ch10_7_3_06.sql 在 Ch10_7_3_05.sql 的子查詢也可以改為合併查詢來取得相同結果, 如下所示 : SELECT DISTINCT Instructors.* FROM Instructors, Classes, Students WHERE Instructors.eid = Classes.eid and Classes.sid = Students.sid and Students.name = ' 江小魚 '

58 10-8 合併更新與刪除 UPDATE 與 JOIN 的合併更新 DELETE 與 JOIN 的合併刪除

59 UPDATE 與 JOIN 的合併更新 - 範例 SQL 查詢範例 :Ch10_8_1_01.sql 在 Classes 資料表需要更新講師李鴻章在教室 300-K 的上課時間, 將時間改為 9:00am, 如下所示 : UPDATE Classes SET Classes.time = 9:00am FROM Classes INNER JOIN Instructors ON Classes.eid = Instructors.eid WHERE Instructors.name = 李鴻章 and Classes.room = 300-K

10-8-1 UPDATE 與 JOIN 的合併更新 - 結果接著執行 SQL 指令 ( 檔案名稱為 :Ch10_8_1_02.

60 UPDATE 與 JOIN 的合併更新 - 結果接著執行 SQL 指令 ( 檔案名稱為 :Ch10_8_1_02.sql ) 顯示 Classes 資料表的所有記錄和欄位, 如下所示 : SELECT eid, sid, c_no, room, DATEPART(Hour, time) AS hour FROM Classes

61 DELETE 與 JOIN 的合併刪除 - 範例 SQL 查詢範例 :Ch10_8_2_01.sql 在 Classes 資料表刪除講師李鴻章在教室 300-K 的上課記錄, 如下所示 : DELETE Classes FROM Classes INNER JOIN Instructors ON Classes.eid = Instructors.eid WHERE Instructors.name = 李鴻章 and Classes.room = 300-K

10-8-2 DELETE 與 JOIN 的合併刪除 - 結果接著執行 SQL 指令 ( 檔案名稱為 :Ch10_8_2_02.

62 DELETE 與 JOIN 的合併刪除 - 結果接著執行 SQL 指令 ( 檔案名稱為 :Ch10_8_2_02.sql ) 顯示 Classes 資料表的所有記錄和欄位, 如下所示 : SELECT eid, sid, c_no, room FROM Classes

HTML網頁基礎語言

HTML網頁基礎語言第 12 章 SQL 合併查詢與子查詢 12-1 SQL 合併查詢的基礎 12-2 交叉合併查詢 12-3 θ 合併與 EquiJoins 合併查詢 12-4 自然合併查詢 12-5 外部合併查詢 12-6 集合運算的查詢 12-7 SQL 子查詢 12-8 合併更新與刪除 1 12-1 SQL 合併查詢的基礎 - 說明 SQL 合併查詢 (Join) 是使用在多個資料表的查詢, 其主要的目的是將關聯式資料庫正規化分析分割的資料表,