授課老師姜子龍E-mailtlj2030knjcedu.ppt

授課老師：姜子龍 -mail：.tw,Access第十七單元 結(jié)構(gòu)化查詢語言(一),1,結(jié)構(gòu)化查詢語言(一)-講授大綱,關(guān)於SQL 訂定SQL標(biāo)準(zhǔn)的優(yōu)點 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陳述式語法子查詢的應(yīng)用 SELECT陳述式撰寫考量,2,關(guān)於SQL,SQL的全名為：Structured Query Language，經(jīng)常拼音為“sequel“。 SQL是套用於關(guān)連式資料庫(Relational Database)的查詢與維護(hù)語言。 SQL的前身為SEQUAL，皆由IBM提出。 1979 Rational Software,Inc. 發(fā)展了一個關(guān)連式資料庫產(chǎn)品，名為 Oracle。 1981 IBM發(fā)表了第一個使用SQL的關(guān)連式資料庫產(chǎn)品： SQL/DS。,3,關(guān)於SQL(續(xù)),使用SQL的關(guān)連式資料庫產(chǎn)品漸多，但在語法上未能統(tǒng)一，造成百家爭鳴。 美國國家標(biāo)準(zhǔn)協(xié)會(ANSI)及國際標(biāo)準(zhǔn)組織(ISO)體會到SQL的重要，在1986年公佈了第一個SQL標(biāo)準(zhǔn)：SQL-86。 後續(xù)的強化與修正版本有：SQL-2(SQL-92)及 SQL-3(SQL-96)。 SQL Access Group 公佈了SQL的商業(yè)標(biāo)準(zhǔn)：CLI(Common Language Interface)。 微軟的ODBC(Open Database Connectivity)即支援此CLI。,4,訂定SQL標(biāo)準(zhǔn)的優(yōu)點,當(dāng)使用另一套SQL產(chǎn)品時： 可節(jié)省人員再訓(xùn)練的時間 應(yīng)用程式可輕易移植 應(yīng)用程式的使用壽命較長 增加系統(tǒng)間資料的交換性。,5,SQL語言的分類,查詢語言(Query Language,QL) 用於查詢資料庫中的資料，如：Select fields From tables Where condition ;。 資料定義語言(Data Definition Language,DDL) 用於定義及維護(hù)資料庫中的資料表(包括資料表名稱、欄位名稱、欄位的資料類型等)，如：Create table、Create index。 資料處理語言(Data Manipulation Language,DML) 用於處理資料庫中的資料，如：Insert、Update、Delete。 資料控制語言(Data Control Language,DCL) 用於控制資料庫的使用權(quán)限及安全程度，如：Grant 、Revoke、Audit。,6,Access選取查詢的SQL檢視,在Access中，可在選取查詢的SQL檢視視窗內(nèi)使用查詢指令，以進(jìn)行資料表內(nèi)容的查詢。 初次進(jìn)入選取查詢的SQL檢視視窗的步驟： 在資料庫視窗中，切換至查詢頁次。 按新增圖示 ，即出現(xiàn)新增查詢視窗。 雙按設(shè)計檢視，即出現(xiàn)顯示資料表視窗。 按選關(guān)閉鈕，便會離開顯示資料表交談窗。 進(jìn)入選取查詢視窗。 執(zhí)行Access視窗工具列中最左邊的 圖示(若未看見可按列示鈕)，即可進(jìn)入選取查詢的SQL檢視視窗。,7,Access選取查詢的SQL檢視(續(xù)),在選取查詢的SQL檢視視窗中輸入完SQL陳述式後，可按下Access工具列上的執(zhí)行紐 ，執(zhí)行該SQL陳述式。 離開選取查詢的SQL檢視視窗： 按下選取查詢的SQL檢視視窗右上角的關(guān)閉鈕。 在訊息對話方塊按是鈕，將查詢指令儲存起來。 在另存新檔對話方塊的查詢名稱欄，輸入查詢名稱，再按確定鈕，便可將查詢存檔。 在資料庫視窗中，會多出一個剛設(shè)計的查詢物件。,8,Access選取查詢的SQL檢視(續(xù)),9,Access選取查詢的SQL檢視(續(xù)),10,練習(xí)用資料表內(nèi)容,學(xué)生,課程,修習(xí),11,SQL語法所使用的符號,符號說明： | (選擇符號)：表示選項，當(dāng)有多種不同選擇項時，可 | 加以區(qū)隔。 中括號(即與)：通常會與 | 一起使用，所括住的部分表示其中選項為多選一，但也可以省略不選。 大括號(即與)：通常會與 | 一起使用，所括住的部分表示其中選項可為零次到任意多次的重複。 , (逗號)：用於分隔意義相同的各個項目。 (省略符號) ：表示語法中重複的項目，若於本省略符號前出現(xiàn)逗號，表示各重複項目必須以逗號隔開。 大寫字語為陳述式的關(guān)鍵字，直接使用，勿需替換。 中文或小寫字語為需用真實的識別字來替代的部分。,12,選取查詢：SELECT陳述式語法,在Microsoft Jet 資料庫引擎所用語法： SELECT 述語 * | 資料表名稱.* | 資料表名稱.欄位名稱1 AS 欄位別名1 , 資料表名稱.欄位名稱2 AS 欄位別名2 , . FROM 資料表運算式1,資料表運算式2, . IN 其他資料庫名稱 WHERE 資料錄篩選條件 GROUP BY 分類群組 HAVING 篩選群組條件 ORDER BY 排序條件 其中SELECTFROM是最基本的語法。SELECT子句的主要作用是決定要挑選出的欄位有哪些。,13,SELECT陳述式語法(續(xù)),述語(predicate) 為以下所列述語之一： ALL、DISTINCT、DISTINCTROW 或 TOP?？捎脕硐拗苽骰刂Y料錄的數(shù)量。 * 從特定的單個或多個資料表中選取全部的欄位。 資料表名稱(table) 欲選取欄位所屬之資料表(或查詢)的名稱。 欄位名稱(field) 欲選取之欄位的名稱。如果要擷取之資料包含一個以上的欄位，則將它們依序訂出。 欄位別名(alias) 作為欄位的另一個名稱，以代替在資料表中原有的欄名。,14,SELECT陳述式語法(續(xù)),資料表運算式(tabexpression) 要選取之資料來自於單個或多個資料表或查詢的名稱，或由 INNER JOIN、LEFT JOIN 或 RIGHT JOIN 合成的結(jié)果。 其他資料庫名稱(externaldatabase) 資料表運算式中所述資料表(或查詢)所在之資料庫的名稱(當(dāng)這些資料表不存在於目前的資料庫時使用)。,15,SELECT陳述式語法(續(xù)),述語(predicate)：ALL、DISTINCT、DISTINCTROW 或 TOP。 ALL 如果您沒有包含任何一個述語，此為預(yù)設(shè)值。將選取與 SQL 陳述式中條件相吻合的所有資料錄。下列兩個範(fàn)例是相等的，並且皆從課程資料表中傳回所有的資料錄：,SA01: SELECT ALL * FROM 課程;,SA02: SELECT * FROM 課程;,16,SELECT陳述式語法(續(xù)),DISTINCT 略去選取欄位之中包含重複資料的資料錄。使用本述語時，會使查詢結(jié)果之中，列在 SELECT 陳述式中的每個欄位值的組合必須是唯一的。 例如，在修習(xí)資料表之中有一些同學(xué)會同時選修某們課，下列 SQL 陳述式會傳回哪些課號已被同學(xué)選修：,SD01: SELECT DISTINCT 課號 FROM 修習(xí);,如果您省略 DISTINCT，此查詢將傳回許多重複的值。 如果 SELECT 子句包含一個以上的欄位，對一個要包含在結(jié)果中的資料錄，所有欄位值的組合必須是唯一的。 加上 DISTINCT 述語的查詢，其輸出是不能更新的，也不會反應(yīng)其他使用者隨後所做的變更。,17,SELECT陳述式語法(續(xù)),DISTINCTROW 略去選取欄位之中包含重複資料的資料錄，但該重複資料若來自不同資料錄，則予保留。 例如，您可以在客戶編號欄位建立一個連結(jié)客戶資料表及訂單資料表的查詢。 客戶資料表沒有重複的客戶編號，但有重複的客戶名稱。 下列 SQL 陳述式顯示如何使用 DISTINCTROW 產(chǎn)生客戶清單，該清單將列出至少曾下過一筆訂單的客戶：,SELECT DISTINCTROW CustomerName FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;,18,SELECT陳述式語法(續(xù)),TOP n PERCENT 傳回一些特定數(shù)目的資料錄，且這些資料錄將落在由 ORDER BY 子句指定的前面或後面的範(fàn)圍之中。 假設(shè)要找出修習(xí)課號C01，成績排名在前面 2 名的學(xué)號：,ST01: SELECT TOP 2 學(xué)號 ,成績 FROM 修習(xí) WHERE 課號 = “C01“ ORDER BY 成績 DESC;,TOP 述語不在相同值間作選擇。 在前一個範(fàn)例中，如果第 2 及第 3 的分?jǐn)?shù)是相同的，查詢將傳回 3 筆資料錄。,19,SELECT陳述式語法(續(xù)),亦可使用 PERCENT 保留字來傳回特定資料錄的百分比，且這些資料錄將落在由 ORDER BY 子句指定的前面或後面範(fàn)圍之中。 假設(shè)要找出修習(xí)課號C01，成績排名在後面百分之10的學(xué)號：,ASC 述語指定傳回後面的值。 接在 TOP 後方的值必須是一個沒有正負(fù)值的整數(shù)。,ST02: SELECT TOP 10 PERCENT 學(xué)號 ,成績 FROM 修習(xí) WHERE 課號 = “C01“ ORDER BY 成績 ASC;,20,SELECT陳述式語法INNER JOIN 運算,INNER JOIN 運算： 組合兩個資料表中的資料錄，使在共同的欄位之中有符合比較條件的值。 可以在任何的 FROM 子句中使用 INNER JOIN 運算， 這是最基本的連接類型。 語法： FROM 資料表名稱1 INNER JOIN 資料表名稱2 ON資料表名稱1.欄位名稱1 比較運算子 資料表名稱2.欄位名稱2 資料表名稱1,資料表名稱2 - 被組合之資料表的名稱。 欄位名稱1,欄位名稱2 - 被連接之欄位的名稱， 如果不是數(shù)值，這些欄位必須屬於相同的資料類型，並且要包含相同類型的資料，但是名稱不需要相同。 比較運算子(compopr) - “=“, “, “=“ 或 “。,21,SELECT陳述式語法INNER JOIN 運算(續(xù)),注意事項： 如果想連接包含備忘或 OLE物件資料類型的欄位，將會發(fā)生錯誤。 可以連接任何兩個相同類型的數(shù)值欄。例如，您可以連接自動編號及 Long 的欄位，因為它們是相同的類型。但無法結(jié)合 Single 和 Double 類型的欄位。 可以使用下列語法，在一個 JOIN 陳述式中連結(jié)多個 ON 子句：,SELECT 欄位名稱 FROM 資料表名稱1 INNER JOIN 資料表名稱2 ON 資料表名稱1.欄位名稱1 比較運算子 資料表名稱2.欄位名稱1 AND ON 資料表名稱1.欄位名稱2 比較運算子 資料表名稱2.欄位名稱2 | OR ON 資料表名稱1.欄位名稱3 比較運算子 資料表名稱2.欄位名稱3 ,22,SELECT陳述式語法INNER JOIN 運算(續(xù)),注意事項(續(xù))： 亦可以使用下列語法，寫為巢狀式 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。,23,SELECT陳述式語法INNER JOIN 運算(續(xù)),INNER JOIN 運算範(fàn)例：,IJ01: SELECT 學(xué)生.學(xué)號, 學(xué)生.姓名, 修習(xí).課號 FROM 學(xué)生 INNER JOIN 修習(xí) ON 學(xué)生.學(xué)號 = 修習(xí).學(xué)號;,24,SELECT陳述式語法INNER JOIN 運算(續(xù)),INNER JOIN 運算範(fàn)例：,IJ02:(錯誤用例) SELECT 學(xué)生.學(xué)號, 學(xué)生.姓名, 修習(xí).課號 FROM 學(xué)生 INNER JOIN 修習(xí) ON 學(xué)生.學(xué)號 修習(xí).學(xué)號;,25,SELECT陳述式語法INNER JOIN 運算(續(xù)),INNER JOIN 運算範(fàn)例：,IJ03: SELECT 學(xué)生.學(xué)號, 學(xué)生.姓名, 課程.課號, 課程.課名 FROM 學(xué)生 INNER JOIN (課程 INNER JOIN 修習(xí) ON 課程.課號 = 修習(xí).課號) ON 學(xué)生.學(xué)號 = 修習(xí).學(xué)號;,26,SELECT陳述式語法 LEFT JOIN 及 RIGHT JOIN運算,LEFT JOIN 及 RIGHT JOIN運算： 使用在任何 FROM 子句中之後，組合來源資料表中的資料錄。 使用 LEFT JOIN 建立左邊外部連接。將包含了第一個 (左邊)資料表中的全部資料錄，以及只包括那些連接欄位符合比較條件的第二個 (右邊) 資料表中的資料錄。 使用 RIGHT JOIN 建立右邊外部連接。將包含了第二個 (右邊)資料表中的全部資料錄，以及只包括那些連接欄位符合比較條件的第一個 (左邊) 資料表中的資料錄。,27,SELECT陳述式語法 LEFT JOIN 及 RIGHT JOIN運算,語法： FROM 資料表名稱1 LEFT | RIGHT JOIN 資料表名稱2 ON 資料表名稱1.欄位名稱1 比較運算子 資料表名稱2.欄位名稱2 資料表名稱1,資料表名稱2 - 被組合之資料表的名稱。 欄位名稱1,欄位名稱2 - 被連接之欄位的名稱，且這些欄位必須有相同的資料類型，並包含相同類型的資料，但名稱不需要相同。 比較運算子(compopr)： “=“, “, “=“ 或 “。,28,SELECT陳述式語法 LEFT JOIN 及 RIGHT JOIN運算(續(xù)),LEFT JOIN 及 RIGHT JOIN運算範(fàn)例：,應(yīng)用說明： 有部門(左邊)及員工(右邊)兩個資料表。 可以使用 LEFT JOIN 選取所有的部門，包含了沒有分配到員工的部門。 可以使用 RIGHT JOIN 選取所有的員工，包含了沒有分配到部門的員工。,LJ01: SELECT 學(xué)生.學(xué)號, 學(xué)生.姓名, 修習(xí).課號 FROM 學(xué)生 LEFT JOIN 修習(xí) ON 學(xué)生.學(xué)號 = 修習(xí).學(xué)號;,29,SELECT陳述式語法 LEFT JOIN 及 RIGHT JOIN運算(續(xù)),LEFT JOIN 及 RIGHT JOIN運算範(fàn)例：,RJ01: SELECT 學(xué)生.學(xué)號, 學(xué)生.姓名, 修習(xí).課號 FROM 學(xué)生 RIGHT JOIN 修習(xí) ON 學(xué)生.學(xué)號 = 修習(xí).學(xué)號;,RJ02: SELECT修習(xí).學(xué)號, 學(xué)生.姓名, 修習(xí).課號 FROM 學(xué)生 RIGHT JOIN 修習(xí) ON 學(xué)生.學(xué)號 = 修習(xí).學(xué)號;,30,SELECT陳述式語法WHERE子句,WHERE子句： 用於建立篩選資料錄的條件。 語法： WHERE 資料錄篩選條件 邏輯運算子： 用於聯(lián)集、交集或否定篩選資料錄的條件。 例如：WHERE (課號 = “C01“) OR (課號 = “C02“)。 常用的邏輯運算子：,31,SELECT陳述式語法WHERE子句(續(xù)),字串篩選條件： 字串值用“括住。 當(dāng)知道篩選條件的整個字串時，用 = 運算子，如：課號 = “C01“。 當(dāng)知道篩選條件的部分字串時，用 LIKE 運算子。 LIKE運算子可用一些特殊符號來描述字串的匹配條件：,32,SELECT陳述式語法WHERE子句(續(xù)),字串篩選條件(續(xù))： 使用 In() 函數(shù)，用來判斷輸入值是否等於表列中的任一個值，例如：In(“郵寄”,“掛號”,“自取”)。 使用 Left、Right 或 Mid 的函數(shù)，這些函數(shù)的語法是：Left(字串運算式,n) 、Right(字串運算式,n) 、 Mid(字串運算式,起算位置,n)。 可直接以Null來判斷欄位值是否為Null。,SF01: SELECT 學(xué)生.學(xué)號, 姓名, 課號 FROM 學(xué)生,修習(xí) WHERE (學(xué)生.學(xué)號 = 修習(xí).學(xué)號) AND (課號 IN (“C01“,“C03“);,SF02: SELECT 學(xué)生.學(xué)號, 姓名, 課號 FROM 學(xué)生, 修習(xí) WHERE (學(xué)生.學(xué)號 = 修習(xí).學(xué)號) AND (課號 LIKE “C013“);,33,SELECT陳述式語法WHERE子句(續(xù)),數(shù)字篩選條件： 使用比較運算子。,34,SELECT陳述式語法WHERE子句(續(xù)),數(shù)字篩選條件(續(xù))：,NF01: SELECT 學(xué)生.學(xué)號,學(xué)生.姓名,課程.課名,成績 FROM 學(xué)生,課程,修習(xí) WHERE (學(xué)生.學(xué)號 = 修習(xí).學(xué)號) AND (課程.課號 = 修習(xí).課號) AND (成績 = 90);,35,SELECT陳述式語法WHERE子句(續(xù)),日期篩選條件： 日期值用#括住。 使用比較運算子。 使用 Month、Year、Day等的函數(shù)。,36,SELECT陳述式語法建立計算欄位,建立計算欄位： 使用運算式建立一個計算資料的公式，組成的成員有下列四類： Int ( 價格 * 0.4) 函數(shù) 識別字 運算子 值,37,SELECT陳述式語法建立計算欄位(續(xù)),數(shù)字篩選條件(續(xù))：,CF01: SELECT 學(xué)生.姓名,課程.課名,成績*2 AS實得分?jǐn)?shù) FROM 學(xué)生,課程,修習(xí) WHERE (學(xué)生.學(xué)號 = 修習(xí).學(xué)號) AND (課程.課號 = 修習(xí).課號) ;,38,SELECT陳述式語法GROUP BY子句,GROUP BY 子句： 用於建立分類的群組。 與SQL聚合函數(shù)或總和函式搭配使用。 將挑選出的資料做分組計算的查詢，稱為群組式查詢。 在群組式查詢中，所有的計算欄位都必須以群組函式來運算。 語法： GROUP BY 群組欄位1 ,群組欄位2 , 群組欄位的數(shù)目最多10個。,39,SELECT陳述式語法GROUP BY子句(續(xù)),GROUP BY 子句(續(xù))： 在群組式查詢中，所有的計算欄位都必須以群組函式來運算。,40,SELECT陳述式語法GROUP BY子句(續(xù)),GROUP BY 子句(續(xù))：,GF01: SELECT 學(xué)生.姓名,Max(成績) AS 最高分?jǐn)?shù) FROM 學(xué)生,修習(xí) WHERE (學(xué)生.學(xué)號 = 修習(xí).學(xué)號) GROUP BY 學(xué)生.姓名;,GF02: SELECT 修習(xí).學(xué)號, 學(xué)生.姓名,Max(修習(xí).成績) AS 最高分?jǐn)?shù) FROM 學(xué)生 RIGHT JOIN 修習(xí) ON 學(xué)生.學(xué)號=修習(xí).學(xué)號 GROUP BY 修習(xí).學(xué)號,學(xué)生.姓名;,41,SELECT陳述式語法HAVING子句,HAVING子句： 用於篩選經(jīng)過分類群組(GROUP BY)的資料錄。 語法： HAVING 篩選群組條件 篩選群組條件的建立方式與WHERE子句相同。,42,SELECT陳述式語法HAVING子句(續(xù)),HAVING子句(續(xù))：,GF03: SELECT 修習(xí).學(xué)號, 學(xué)生.姓名,Max(修習(xí).成績) AS 最高分?jǐn)?shù) FROM 學(xué)生 RIGHT JOIN 修習(xí) ON 學(xué)生.學(xué)號=修習(xí).學(xué)號 GROUP BY 修習(xí).學(xué)號,學(xué)生.姓名 HAVING Max(修習(xí).成績) = 90;,43,SELECT陳述式語法ORDER BY子句,ORDER BY子句： 將查得的資料錄根據(jù)指定的欄位進(jìn)行排序。 語法： ORDER BY 排序欄位1 ASC | DESC ,排序欄位2 ASC | DESC , ASC 表示遞增排序；DESC 表示遞減排序。預(yù)設(shè)為ASC。,44,SELECT陳述式語法ORDER BY子句(續(xù)),ORDER BY子句(續(xù))：,OD01: SELECT 修習(xí).學(xué)號, 學(xué)生.姓名,Max(成績) AS 最高分?jǐn)?shù) FROM 學(xué)生