授課老師：姜子龍Ｅ- mail：tlj2030@knjc.tw

Access第十七單元 結構化查詢語言(一) 授課老師：姜子龍Ｅ-mail：tlj2030@knjc.edu.tw

結構化查詢語言(一)-講授大綱 • 關於SQL • 訂定SQL標準的優點 • SQL語言的分類 • Access選取查詢的SQL檢視 • SQL語法所使用的符號 • 選取查詢：SELECT陳述式語法 • SELECT陳述式語法—INNER JOIN 運算 • SELECT陳述式語法—LEFT JOIN 及 RIGHT JOIN運算 • SELECT陳述式語法—WHERE子句 • SELECT陳述式語法—建立計算欄位 • SELECT陳述式語法—GROUP BY子句 • SELECT陳述式語法—HAVING子句 • SELECT陳述式語法—ORDER BY子句 • SELECT陳述式語法—子查詢的應用 • SELECT陳述式撰寫考量

關於SQL • SQL的全名為：Structured Query Language，經常拼音為"sequel"。 • SQL是套用於關連式資料庫(Relational Database)的查詢與維護語言。 • SQL的前身為SEQUAL，皆由IBM提出。 • 1979 Rational Software,Inc. 發展了一個關連式資料庫產品，名為 Oracle。 • 1981 IBM發表了第一個使用SQL的關連式資料庫產品： SQL/DS。

關於SQL(續) • 使用SQL的關連式資料庫產品漸多，但在語法上未能統一，造成百家爭鳴。 • 美國國家標準協會(ANSI)及國際標準組織(ISO)體會到SQL的重要，在1986年公佈了第一個SQL標準：SQL-86。 • 後續的強化與修正版本有：SQL-2(SQL-92)及 SQL-3(SQL-96)。 • SQL Access Group 公佈了SQL的商業標準：CLI(Common Language Interface)。 • 微軟的ODBC(Open Database Connectivity)即支援此CLI。

訂定SQL標準的優點 • 當使用另一套SQL產品時： • 可節省人員再訓練的時間 • 應用程式可輕易移植 • 應用程式的使用壽命較長 • 增加系統間資料的交換性。

SQL語言的分類 • 查詢語言(Query Language,QL) —用於查詢資料庫中的資料，如：Select fields From tablesWhere condition ;。 • 資料定義語言(Data Definition Language,DDL) —用於定義及維護資料庫中的資料表(包括資料表名稱、欄位名稱、欄位的資料類型…等)，如：Create table、Create index。 • 資料處理語言(Data Manipulation Language,DML) —用於處理資料庫中的資料，如：Insert、Update、Delete。 • 資料控制語言(Data Control Language,DCL) —用於控制資料庫的使用權限及安全程度，如：Grant 、Revoke、Audit。

Access選取查詢的SQL檢視 • 在Access中，可在選取查詢的SQL檢視視窗內使用查詢指令，以進行資料表內容的查詢。 • 初次進入選取查詢的SQL檢視視窗的步驟： • 在資料庫視窗中，切換至查詢頁次。 • 按「新增」圖示　　，即出現新增查詢視窗。 • 雙按設計檢視，即出現顯示資料表視窗。 • 按選「關閉」鈕，便會離開顯示資料表交談窗。 • 進入選取查詢視窗。 • 執行Access視窗工具列中最左邊的　圖示(若未看見可按列示鈕)，即可進入選取查詢的SQL檢視視窗。

Access選取查詢的SQL檢視(續) • 在選取查詢的SQL檢視視窗中輸入完SQL陳述式後，可按下Access工具列上的執行紐　，執行該SQL陳述式。 • 離開選取查詢的SQL檢視視窗： • 按下選取查詢的SQL檢視視窗右上角的「關閉」鈕。 • 在訊息對話方塊按「是」鈕，將查詢指令儲存起來。 • 在另存新檔對話方塊的查詢名稱欄，輸入查詢名稱，再按「確定」鈕，便可將查詢存檔。 • 在資料庫視窗中，會多出一個剛設計的查詢物件。

Access選取查詢的SQL檢視(續)      

Access選取查詢的SQL檢視(續)      

練習用資料表內容 學生課程修習

SQL語法所使用的符號 • 符號說明： • | (選擇符號)：表示選項，當有多種不同選擇項時，可「 | 」加以區隔。 • 中括號(即[與])：通常會與「 | 」一起使用，所括住的部分表示其中選項為多選一，但也可以省略不選。 • 大括號(即{與})：通常會與「 | 」一起使用，所括住的部分表示其中選項可為零次到任意多次的重複。 • , (逗號)：用於分隔意義相同的各個項目。 • … (省略符號) ：表示語法中重複的項目，若於本省略符號前出現逗號，表示各重複項目必須以逗號隔開。 • 大寫字語為陳述式的關鍵字，直接使用，勿需替換。 • 中文或小寫字語為需用真實的識別字來替代的部分。

選取查詢：SELECT陳述式語法 • 在Microsoft Jet 資料庫引擎所用語法： SELECT [述語] { * | 資料表名稱.* | [資料表名稱.]欄位名稱1 [AS 欄位別名1] [, [資料表名稱.]欄位名稱2 [AS 欄位別名2] [, ...]]} FROM資料表運算式1[,資料表運算式2][, ...] [IN 其他資料庫名稱] [WHERE 資料錄篩選條件] [GROUP BY 分類群組] [HAVING 篩選群組條件] [ORDER BY 排序條件] 其中SELECT…FROM…是最基本的語法。SELECT子句的主要作用是決定要挑選出的欄位有哪些。

SELECT陳述式語法(續) • 述語(predicate)—為以下所列述語之一： ALL、DISTINCT、DISTINCTROW或 TOP。可用來限制傳回之資料錄的數量。 • * — 從特定的單個或多個資料表中選取全部的欄位。 • 資料表名稱(table) —欲選取欄位所屬之資料表(或查詢)的名稱。 • 欄位名稱(field) —欲選取之欄位的名稱。如果要擷取之資料包含一個以上的欄位，則將它們依序訂出。 • 欄位別名(alias) — 作為欄位的另一個名稱，以代替在資料表中原有的欄名。

SELECT陳述式語法(續) • 資料表運算式(tabexpression)—要選取之資料來自於單個或多個資料表或查詢的名稱，或由 INNER JOIN、LEFT JOIN 或 RIGHT JOIN 合成的結果。 • 其他資料庫名稱(externaldatabase) —資料表運算式中所述資料表(或查詢)所在之資料庫的名稱(當這些資料表不存在於目前的資料庫時使用)。

SELECT陳述式語法(續) • 述語(predicate)：ALL、DISTINCT、DISTINCTROW或 TOP。 • ALL 如果您沒有包含任何一個述語，此為預設值。將選取與 SQL 陳述式中條件相吻合的所有資料錄。下列兩個範例是相等的，並且皆從課程資料表中傳回所有的資料錄： • SA01: • SELECT ALL * FROM 課程; • SA02: • SELECT * FROM 課程;

SELECT陳述式語法(續) • DISTINCT 略去選取欄位之中包含重複資料的資料錄。使用本述語時，會使查詢結果之中，列在 SELECT 陳述式中的每個欄位值的組合必須是唯一的。例如，在修習資料表之中有一些同學會同時選修某們課，下列 SQL 陳述式會傳回哪些課號已被同學選修： • SD01: • SELECT DISTINCT 課號 FROM 修習; 如果您省略 DISTINCT，此查詢將傳回許多重複的值。如果 SELECT 子句包含一個以上的欄位，對一個要包含在結果中的資料錄，所有欄位值的組合必須是唯一的。加上 DISTINCT 述語的查詢，其輸出是不能更新的，也不會反應其他使用者隨後所做的變更。

SELECT陳述式語法(續) • DISTINCTROW 略去選取欄位之中包含重複資料的資料錄，但該重複資料若來自不同資料錄，則予保留。例如，您可以在客戶編號欄位建立一個連結客戶資料表及訂單資料表的查詢。客戶資料表沒有重複的客戶編號，但有重複的客戶名稱。下列 SQL 陳述式顯示如何使用 DISTINCTROW 產生客戶清單，該清單將列出至少曾下過一筆訂單的客戶： • SELECT DISTINCTROW CustomerName FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

SELECT陳述式語法(續) • TOP n [PERCENT] 傳回一些特定數目的資料錄，且這些資料錄將落在由 ORDER BY 子句指定的前面或後面的範圍之中。假設要找出修習課號C01，成績排名在前面 2 名的學號： • ST01: • SELECT TOP 2 學號 ,成績 FROM 修習 WHERE 課號 = "C01" ORDER BY 成績 DESC; TOP 述語不在相同值間作選擇。在前一個範例中，如果第 2 及第 3 的分數是相同的，查詢將傳回 3 筆資料錄。

SELECT陳述式語法(續) 亦可使用 PERCENT 保留字來傳回特定資料錄的百分比，且這些資料錄將落在由 ORDER BY 子句指定的前面或後面範圍之中。假設要找出修習課號C01，成績排名在後面百分之10的學號： • ST02: • SELECT TOP 10 PERCENT 學號 ,成績 FROM 修習 WHERE 課號 = "C01" ORDER BY 成績 ASC; ASC 述語指定傳回後面的值。接在 TOP 後方的值必須是一個沒有正負值的整數。

SELECT陳述式語法—INNER JOIN 運算 • INNER JOIN 運算： • 組合兩個資料表中的資料錄，使在共同的欄位之中有符合比較條件的值。 • 可以在任何的 FROM 子句中使用 INNER JOIN 運算，這是最基本的連接類型。 • 語法： • FROM 資料表名稱1 INNER JOIN 資料表名稱2 ON資料表名稱1.欄位名稱1 比較運算子資料表名稱2.欄位名稱2 資料表名稱1,資料表名稱2 - 被組合之資料表的名稱。欄位名稱1,欄位名稱2 - 被連接之欄位的名稱，如果不是數值，這些欄位必須屬於相同的資料類型，並且要包含相同類型的資料，但是名稱不需要相同。比較運算子(compopr) - "=", "<", ">", "<=", ">=" 或 "<>"。

SELECT陳述式語法—INNER JOIN 運算(續) • 注意事項： • 如果想連接包含備忘或 OLE物件資料類型的欄位，將會發生錯誤。 • 可以連接任何兩個相同類型的數值欄。例如，您可以連接自動編號及 Long 的欄位，因為它們是相同的類型。但無法結合 Single 和 Double 類型的欄位。 • 可以使用下列語法，在一個 JOIN 陳述式中連結多個 ON 子句： • SELECT 欄位名稱 FROM 資料表名稱1 INNER JOIN 資料表名稱2 ON 資料表名稱1.欄位名稱1 比較運算子資料表名稱2.欄位名稱1 [AND ON 資料表名稱1.欄位名稱2 比較運算子資料表名稱2.欄位名稱2 | OR ON 資料表名稱1.欄位名稱3 比較運算子資料表名稱2.欄位名稱3]

SELECT陳述式語法—INNER JOIN 運算(續) • 注意事項(續)： • 亦可以使用下列語法，寫為巢狀式 JOIN 陳述式： • SELECT 欄位名稱 FROM 資料表名稱1 INNER JOIN (資料表名稱2 INNER JOIN [( ]資料表名稱3 [INNER JOIN [( ]資料表名稱x [INNER JOIN ...)] ON 資料表名稱3.欄位名稱3比較運算子資料表名稱x.欄位名稱x)] ON 資料表名稱2.欄位名稱2比較運算子資料表名稱3.欄位名稱3) ON 資料表名稱1.欄位名稱1 比較運算子資料表名稱2.欄位名稱2 • 在一個 INNER JOIN 之中，可以寫巢狀式的 LEFT JOIN 或 RIGHT JOIN，但是在一個 LEFT JOIN 或一個 RIGHT JOIN 之中不能寫巢狀式的 INNER JOIN。

SELECT陳述式語法—INNER JOIN 運算(續) • IJ01: • SELECT 學生.學號, 學生.姓名, 修習.課號 FROM 學生 INNER JOIN 修習 ON 學生.學號 = 修習.學號; • INNER JOIN 運算範例：

SELECT陳述式語法—INNER JOIN 運算(續) • IJ02:(錯誤用例) • SELECT 學生.學號, 學生.姓名, 修習.課號 FROM 學生 INNER JOIN 修習 ON 學生.學號 > 修習.學號; • INNER JOIN 運算範例：

SELECT陳述式語法—INNER JOIN 運算(續) • IJ03: • SELECT 學生.學號, 學生.姓名, 課程.課號, 課程.課名 FROM 學生 INNER JOIN (課程 INNER JOIN 修習 ON 課程.課號 = 修習.課號) ON 學生.學號 = 修習.學號; • INNER JOIN 運算範例：

SELECT陳述式語法—LEFT JOIN 及 RIGHT JOIN運算 • LEFT JOIN 及 RIGHT JOIN運算： • 使用在任何 FROM 子句中之後，組合來源資料表中的資料錄。 • 使用 LEFT JOIN 建立左邊外部連接。將包含了第一個 (左邊)資料表中的全部資料錄，以及只包括那些連接欄位符合比較條件的第二個 (右邊) 資料表中的資料錄。 • 使用 RIGHT JOIN 建立右邊外部連接。將包含了第二個 (右邊)資料表中的全部資料錄，以及只包括那些連接欄位符合比較條件的第一個 (左邊) 資料表中的資料錄。

SELECT陳述式語法—LEFT JOIN 及 RIGHT JOIN運算 • 語法： • FROM 資料表名稱1 [ LEFT | RIGHT ] JOIN 資料表名稱2 ON 資料表名稱1.欄位名稱1 比較運算子資料表名稱2.欄位名稱2 資料表名稱1,資料表名稱2 -被組合之資料表的名稱。欄位名稱1,欄位名稱2 -被連接之欄位的名稱，且這些欄位必須有相同的資料類型，並包含相同類型的資料，但名稱不需要相同。比較運算子(compopr)： "=", "<", ">", "<=", ">=" 或 "<>"。

SELECT陳述式語法—LEFT JOIN 及 RIGHT JOIN運算(續) • 應用說明： • 有部門(左邊)及員工(右邊)兩個資料表。 • 可以使用 LEFT JOIN 選取所有的部門，包含了沒有分配到員工的部門。 • 可以使用 RIGHT JOIN 選取所有的員工，包含了沒有分配到部門的員工。 • LEFT JOIN 及 RIGHT JOIN運算範例： • LJ01: • SELECT 學生.學號, 學生.姓名, 修習.課號 FROM 學生 LEFT JOIN 修習 ON 學生.學號 = 修習.學號;

SELECT陳述式語法—LEFT JOIN 及 RIGHT JOIN運算(續) • LEFT JOIN 及 RIGHT JOIN運算範例： • RJ01: • SELECT 學生.學號, 學生.姓名, 修習.課號 FROM 學生 RIGHT JOIN 修習 ON 學生.學號 = 修習.學號; • RJ02: • SELECT修習.學號, 學生.姓名, 修習.課號 FROM 學生 RIGHT JOIN 修習 ON 學生.學號 = 修習.學號;

SELECT陳述式語法—WHERE子句 • WHERE子句： • 用於建立篩選資料錄的條件。 • 語法： • WHERE 資料錄篩選條件 • 邏輯運算子： • 用於聯集、交集或否定篩選資料錄的條件。 • 例如：WHERE (課號 = "C01") OR (課號 = "C02")。 • 常用的邏輯運算子：

SELECT陳述式語法—WHERE子句(續) • 字串篩選條件： • 字串值用「"」括住。 • 當知道篩選條件的整個字串時，用 = 運算子，如：課號 = "C01"。 • 當知道篩選條件的部分字串時，用 LIKE 運算子。 LIKE運算子可用一些特殊符號來描述字串的匹配條件：

SELECT陳述式語法—WHERE子句(續) • 字串篩選條件(續)： • 使用 In() 函數，用來判斷輸入值是否等於表列中的任一個值，例如：In(“郵寄”,“掛號”,“自取”)。 • 使用 Left、Right 或 Mid 的函數，這些函數的語法是：Left(字串運算式,n) 、Right(字串運算式,n) 、 Mid(字串運算式,起算位置,n)。 • 可直接以Null來判斷欄位值是否為Null。 • SF01: • SELECT 學生.學號, 姓名, 課號 FROM 學生,修習 WHERE (學生.學號 = 修習.學號) AND (課號 IN ("C01","C03")); • SF02: • SELECT 學生.學號, 姓名, 課號 FROM 學生, 修習 WHERE (學生.學號 = 修習.學號) AND (課號 LIKE "C0[13]");

SELECT陳述式語法—WHERE子句(續) • 數字篩選條件： • 使用比較運算子。

SELECT陳述式語法—WHERE子句(續) • 數字篩選條件(續)： • NF01: • SELECT 學生.學號,學生.姓名,課程.課名,成績 FROM 學生,課程,修習 WHERE (學生.學號 = 修習.學號) AND (課程.課號 = 修習.課號) AND (成績 >= 90);

SELECT陳述式語法—WHERE子句(續) • 日期篩選條件： • 日期值用「#」括住。 • 使用比較運算子。 • 使用 Month、Year、Day等的函數。

SELECT陳述式語法—建立計算欄位 • 建立計算欄位： • 使用運算式建立一個計算資料的公式，組成的成員有下列四類： Int ( [價格] * 0.4) 函數識別字運算子值

SELECT陳述式語法—建立計算欄位(續) • 數字篩選條件(續)： • CF01: • SELECT 學生.姓名,課程.課名,成績*2 AS實得分數 FROM 學生,課程,修習 WHERE (學生.學號 = 修習.學號) AND (課程.課號 = 修習.課號) ;

SELECT陳述式語法—GROUP BY子句 • GROUP BY 子句： • 用於建立分類的群組。 • 與SQL「聚合函數」或「總和函式」搭配使用。 • 將挑選出的資料做分組計算的查詢，稱為「群組式查詢」。 • 在「群組式查詢」中，所有的計算欄位都必須以群組函式來運算。 • 語法： • GROUP BY 群組欄位1 [,群組欄位2 [,…] ] • 群組欄位的數目最多10個。

SELECT陳述式語法—GROUP BY子句(續) • GROUP BY 子句(續)： • 在「群組式查詢」中，所有的計算欄位都必須以群組函式來運算。

SELECT陳述式語法—GROUP BY子句(續) • GF01: • SELECT 學生.姓名,Max(成績) AS 最高分數 FROM 學生,修習 WHERE (學生.學號 = 修習.學號) GROUP BY 學生.姓名; • GROUP BY 子句((續)： • GF02: • SELECT 修習.學號, 學生.姓名,Max(修習.成績) AS 最高分數 FROM 學生 RIGHT JOIN 修習 ON 學生.學號=修習.學號 GROUP BY 修習.學號,學生.姓名;

SELECT陳述式語法—HAVING子句 • HAVING子句： • 用於篩選經過分類群組(GROUP BY)的資料錄。 • 語法： • HAVING 篩選群組條件 • 篩選群組條件的建立方式與WHERE子句相同。

SELECT陳述式語法—HAVING子句(續) • GF03: • SELECT 修習.學號, 學生.姓名,Max(修習.成績) AS 最高分數 FROM 學生 RIGHT JOIN 修習 ON 學生.學號=修習.學號 GROUP BY 修習.學號,學生.姓名 HAVING Max(修習.成績) >= 90; • HAVING子句((續)：

SELECT陳述式語法—ORDER BY子句 • ORDER BY子句： • 將查得的資料錄根據指定的欄位進行排序。 • 語法： • ORDER BY 排序欄位1 [ASC | DESC] [,排序欄位2 [ASC | DESC] [,…] ] • ASC 表示遞增排序；DESC 表示遞減排序。預設為ASC。

SELECT陳述式語法—ORDER BY子句(續) • OD01: • SELECT 修習.學號, 學生.姓名,Max(成績) AS 最高分數 FROM 學生 RIGHT JOIN 修習 ON 學生.學號=修習.學號 GROUP BY 修習.學號,學生.姓名 HAVING Max(成績) >= 90 ORDER BY Max(成績) DESC; • ORDER BY子句((續)：

SELECT陳述式語法—子查詢的應用 • 子查詢的應用： • 在WHERE 子句中，除了可以利用篩選條件進行查詢外，還可利用另外的SELECT陳述式，作為查詢的條件。 • 子查詢的查詢結果僅能傳回一筆資料錄。 • 語法： • WHERE 運算元運算子 (SELECT陳述式 ) [邏輯運算子運算元運算子 (SELECT陳述式) [邏輯運算子 …] ]

SELECT陳述式語法—子查詢的應用(續) • OD01: • SELECT 學生.姓名 FROM 學生 WHERE 學號 = (SELECT 學號 FROM 修習 WHERE 成績 = 92) ; • 子查詢的應用(續)：

SELECT陳述式撰寫考量 • 哪些資料表欄位需要顯示？哪些計算欄位需要顯示？哪些群聚函式需要顯示？–放在SELECT子句後面。若有多個，用“,”隔開；若欄位名稱無法唯一識別來自哪一資料表，加上資料表名稱，其後加上“.”；若顯示之欄位名稱會混淆，加上 AS 及<別名>。 • 相關欄位資料來自哪些資料表？–放在FROM子句後面。若有多個，用“,”隔開。 • 是否有查詢條件？–放在WHERE子句後面。若有多個，用“AND “或”OR”隔開。 • 哪些欄位作為資料分組的根據？–放在GROUP BY 子句後面。若有多個，用“.“隔開。 • 哪些相關欄位需要排序？–放在ORDER BY子句後面。若有多個，用“,”隔開。

練習結構化查詢語言 • 使用練習用資料表的內容，試以SQL撰寫下列選取查詢： • 選取一資料表中所有資料錄的所有欄位內容 • 選取一資料表中所有資料錄的某些欄位內容 • 選取一資料表中所有資料錄的某些欄位內容，並依第一個欄位的內容由大到小排序 • 選取一資料表中所有資料錄的某一欄位內容，但需去除重複內容 • 選取一資料表中部份資料錄(條件自訂)的某些欄位內容 • 計算一資料表中合乎某條件(條件自訂)之部份資料錄的筆數 • 計算一資料表中某數字類型欄位在所有資料錄的平均值，並幫其取一別名 • 選取兩資料表中的某些欄位內容，並以關聯欄位進行連結

問題討論‧ ‧ ‧ ﹋謝謝聆聽﹌

授課老師：姜子龍Ｅ- mail：tlj2030@knjc.tw