第18章 SQL結構化查詢語言 18-1 SQL語言的基礎 18-2 SQL的查詢指令 18-3 SQL子查詢與合併查詢.

Presentation on theme: "第18章 SQL結構化查詢語言 18-1 SQL語言的基礎 18-2 SQL的查詢指令 18-3 SQL子查詢與合併查詢."— Presentation transcript:

1 第18章 SQL結構化查詢語言 18-1 SQL語言的基礎 18-2 SQL的查詢指令 18-3 SQL子查詢與合併查詢

2 18-1 SQL語言的基礎 SQL結構化查詢語言 Access查詢物件的SQL檢視

3 SQL結構化查詢語言-說明 「SQL」（Structured Query Language）為「ANSI」（American National Standards Institute）標準的資料庫語言，可以用來存取和更新資料庫的記錄，這是目前關聯式資料庫系統主要支援的資料庫語言。 早在1970年，E. F. Codd建立關聯式資料庫觀念，同時就提出一種構想的資料庫語言，這是一種完整和通用的資料存取方式，雖然當時並沒有真正建立語法，但這便是SQL的源起。

4 18-1-1 SQL結構化查詢語言-種類 SQL語言依指令功能可以分成3種語言，如下所示：
資料定義語言（Data Definition Language）：屬於資料表建立、新增索引和定義欄位等SQL指令。 資料操作語言DML（Data Manipulation Language）：屬於資料表記錄插入、刪除、更新和查詢指令。 資料控制語言DCL（Data Control Language）：屬於資料庫安全設定和權限管理的相關指令。

5 SQL結構化查詢語言-指令 SQL資料操作指令共有4個基本指令，如下表所示：

6 Access查詢物件的SQL檢視 在Access可以使用精靈或設計檢視來建立查詢物件，事實上，查詢物件還提供SQL檢視，可以直接輸入SQL指令來建立查詢物件。

7 18-2 SQL的查詢指令 18-2-1 SELECT指令設定查詢範圍 18-2-2 WHERE條件子句
AND與OR多條件查詢 ORDER BY排序子句 BETWEEN/AND資料範圍子句 IN和NOT運算子 SQL的聚合函數

8 18-2 SQL的查詢指令 SQL查詢指令只有SELECT指令，其完整的指令語法如下所示：
SELECT column1, column2 FROM table WHERE conditions 上述SELECT指令的column1~2為記錄欄位，table為資料表，conditions為查詢條件，這個指令使用口語來說是「從資料表table取回符合WHERE條件所有記錄的欄位column1和column2」。

9 18-2-1 SELECT指令設定查詢範圍- 顯示資料表的部分欄位
SQL指令只顯示【產品資訊】資料表的【產品編號】、【產品名稱】和【定價】欄位，如下所示： SELECT 產品編號, 產品名稱, 定價 FROM 產品資訊

10 18-2-1 SELECT指令設定查詢範圍- 顯示資料表的所有欄位
SQL指令可以顯示產品資訊資料表的所有欄位和記錄，如下所示： SELECT * FROM 產品資訊

11 18-2-1 SELECT指令設定查詢範圍- 欄位沒有重複值
資料表記錄的欄位如果有重複值，即欄位（單一欄位）中的資料重複，擁有相同值，在SELECT指令只需加上DISTINCT指令，就只會顯示其中一筆記錄資料。 在【產品資訊】資料表顯示一共有多少種不同的【定價】，如下所示： SELECT DISTINCT 定價 FROM 產品資訊

12 WHERE條件子句-說明 SELECT指令的WHERE條件子句是查詢主角，在SELECT指令指定查詢哪個資料表和哪些欄位，然後讓WHERE子句條件篩選查詢條件的記錄。 WHERE條件的欄位值可以是文字、數值或日期/時間，使用的運算子，如下表所示：

13 18-2-2 WHERE條件子句-條件值為字串 條件值為字串

14 18-2-2 WHERE條件子句-包含子字串1 包含子字串
LIKE包含運算子只需包含的子字串就符合條件，而且還可以進一步配合萬用字元建立字串範本（Pattern）來進行比對，如下表所示：

15 18-2-2 WHERE條件子句-包含子字串2 查詢產品說明擁有子字串"5"的SQL指令，如下所示： SELECT * FROM 產品資訊
WHERE 產品說明 LIKE '*5*' 查詢產品名稱是以任何字串開頭，中間為"-"，結尾為"GB"的SQL指令，如下所示： WHERE 產品名稱 LIKE '*-?GB' 查詢入庫日期（日期/時間資料類型也可以使用萬用字元）是以子字串"200"開頭，月份是任易一個數字，其SQL指令如下所示： WHERE 入庫日期 LIKE '200?/#/*'

16 WHERE條件子句-條件值為數值 WHERE條件如果為數字欄位就不需要使用單引號括起，可以使用的運算子和範例，如下表所示：

17 18-2-2 WHERE條件子句-條件值為日期/時間

18 18-2-3 AND與OR多條件查詢-AND「且」運算子
AND運算子連接的前後條件都必須同時成立，整個條件才成立，即都為「真」（True），才是「真」（True）。 查詢產品資訊的入庫日期包含"6"，且產品名稱有"Nano"子字串的SQL指令，如下所示： SELECT * FROM 產品資訊 WHERE 入庫日期 LIKE '*6*' AND 產品名稱 LIKE '*Nano*'

19 18-2-3 AND與OR多條件查詢-OR「或」運算子
OR運算子連接的前後條件只需任何一個條件成立即可，也就是說，只需其中之一為「真」（True），就為「真」（True）。 查詢產品資訊的產品名稱包含"Nano"或定價大於5000的SQL指令，如下所示： SELECT * FROM 產品資訊 WHERE 產品名稱 LIKE '*Nano*' OR 定價>5000

20 18-2-3 AND與OR多條件查詢-複雜的WHERE條件子句
WHERE條件如果需要連接3、4個或以上的條件，在WHERE條件可以同時使用AND和OR連結多個不同條件。 查詢產品資訊的產品名稱有"Nano"子字串或入庫日期有"7"子字串，而且定價大於等於5000，其SQL指令如下所示： SELECT * FROM 產品資訊 WHERE 產品名稱 LIKE '*Nano*' OR 入庫日期 LIKE '*7*' AND 定價>=5000

21 18-2-3 AND與OR多條件查詢-擁有括號的WHERE條件子句
查詢產品資訊的產品名稱有"Nano"子字串或入庫日期有"7"子字串，這2個條件使用括號括起，而且定價需要大於等於5000的記錄，其SQL指令如下所示： SELECT * FROM 產品資訊 WHERE (產品名稱 LIKE '*Nano*' OR 入庫日期 LIKE '*7*') AND 定價>=5000

22 18-2-4 ORDER BY排序子句-由小到大排序
在SQL指令只需加上ORDER BY子句指定排序欄位，就可以由小到大進行排序。 查詢定價大於等於3500元的記錄，並且使用定價欄位由小到大進行排序的SQL指令，如下所示： SELECT * FROM 產品資訊 WHERE 定價>=3500 ORDER BY 定價 ASC

23 18-2-4 ORDER BY排序子句-由大到小排序
如果排序順序需要倒過來由大到小，只需在ORDER BY子句的最後加上DESC指令。 查詢定價大於等於3500元的記錄，並且使用定價欄位由大到小進行排序的SQL指令，如下所示： SELECT * FROM 產品資訊 WHERE 定價>=3500 ORDER BY 定價 DESC

24 BETWEEN/AND資料範圍子句 BETWEEN AND子句可以定義SELECT指令WHERE條件子句的範圍，範圍值可以使用文字、數值或日期/時間。 查詢2006年1月1日到12月31日入庫產品記錄的SQL指令，如下所示： SELECT * FROM 產品資訊 WHERE 入庫日期 BETWEEN #2006/1/1# AND #2006/12/31# 查詢定價在5000到10000之間的產品資訊，其SQL指令如下所示： WHERE 定價 BETWEEN 5000 AND 10000

25 18-2-6 IN和NOT運算子-IN運算子 IN運算子 IN運算子可以設定一串文字或數值清單，如果欄位值為其中之一就符合條件。
查詢定價3000、7000和7500的產品資料，其SQL指令如下所示： SELECT * FROM 產品資訊 WHERE 定價 IN (3000, 7000, 7500)

26 18-2-6 IN和NOT運算子-NOT運算子 NOT運算子 NOT運算子可以搭配前述子句，取得與條件相反的查詢結果，如下表所示：
查詢除了一些特定的定價外的產品記錄資料，SQL指令如下所示： SELECT * FROM 產品資訊 WHERE 定價 NOT IN (3000, 7000, 7500)

27 SQL的聚合函數-說明 SQL的聚合函數可以進行資料表欄位的筆數、平均、範圍和統計函數，提供進一步欄位資料的分析結果，如下表所示：

28 18-2-7 SQL的聚合函數-Count()函數

29 SQL的聚合函數-Avg()函數 Avg()函數可以計算數字欄位的平均值，如下表所示：

30 SQL的聚合函數-Max()函數 Max()函數可以找出符合條件記錄中的欄位最大值，如下表所示：

31 SQL的聚合函數-Min()函數 Min()函數可以計算符合條件記錄的欄位最小值，如下表所示：

32 SQL的聚合函數-Sum()函數 Sum()函數可以計算符合條件記錄的欄位總和，如下表所示：

33 18-3 SQL子查詢與合併查詢 18-3-1 SQL的子查詢 18-3-2 內部合併查詢INNER JOIN指令
外部合併查詢OUTER JOIN指令

34 SQL的子查詢 在SELECT指令的WHERE子句可以使用另一個SELECT指令查詢其他資料表的記錄，稱為「子查詢」（Subquery）。 在【學生】資料表使用姓名查詢學號，然後使用取得的學號在【選課】資料表查詢選課的記錄數，SQL指令如下所示： SELECT Count(*) FROM 選課資料表 WHERE 學號 = (SELECT 學號 FROM 學生資料表 WHERE 姓名='周傑倫')

35 18-3-2 內部合併查詢INNER JOIN指令-說明
SQL合併查詢指令是JOIN，可以將關聯式資料庫分割的資料表合併成未分割前的結果，以方便檢視所需的資訊。因為正規化的目的是為了避免資料重複，但是閱讀資訊時，重複資料反了容易閱讀。 SQL合併查詢分為：INNER JOIN和OUTER JOIN指令。INNER JOIN指令可以取回2個資料表都存在的記錄。

36 18-3-2 內部合併查詢INNER JOIN指令-範例1
查詢所有學生選課的課程編號資料，從學生資料表取得學號和姓名，選課資料表取得選課編號，關聯欄位是學號，SQL指令如下所示： SELECT 學生資料表.學號, 學生資料表.姓名, 選課資料表.課程編號 FROM 學生資料表 INNER JOIN 選課資料表 ON 學生資料表.學號 = 選課資料表.學號

37 18-3-2 內部合併查詢INNER JOIN指令-範例2
在上一個查詢物件只取得課程編號，請進一步使用合併查詢，取得【課程資料表】的所有欄位，SQL指令如下所示： SELECT 學生資料表.學號, 學生資料表.姓名, 課程資料表.* FROM 課程資料表 INNER JOIN (學生資料表 INNER JOIN 選課資料表 ON 學生資料表.學號 = 選課資料表.學號) ON 選課資料表.課程編號 = 課程資料表.課程編號

38 18-3-2 內部合併查詢INNER JOIN指令-範例3
在上一個查詢物件只取得教授編號，請進一步使用合併查詢，取得【教授資料表】的所有欄位，SQL指令如下所示： SELECT 學生資料表.學號, 學生資料表.姓名, 課程資料表.*, 教授資料表.* FROM 教授資料表 INNER JOIN (課程資料表 INNER JOIN (學生資料表 INNER JOIN 選課資料表 ON 學生資料表.學號 = 選課資料表.學號) ON 選課資料表.課程編號 = 課程資料表.課程編號) ON 教授資料表.教授編號 = 課程資料表.教授編號

39 18-3-3 外部合併查詢OUTER JOIN指令-說明
OUTER JOIN指令可以取回任一資料表的所有記錄，不論是否是2個資料表都存在的記錄，一共分成2種JOIN指令，如下所示： RIGHT JOIN：取回右邊資料表內的所有記錄。 LEFT JOIN：取回左邊資料表內的所有記錄。

40 18-3-3 外部合併查詢OUTER JOIN指令-範例1
從學生資料表取得學號、姓名，選課資料表取得選課編號，關聯欄位是學號，SQL指令如下所示： SELECT 學生資料表.學號, 學生資料表.姓名, 選課資料表.課程編號 FROM 學生資料表 LEFT JOIN 選課資料表 ON 學生資料表.學號 = 選課資料表.學號

41 18-3-3 外部合併查詢OUTER JOIN指令-範例2
查詢學生的選課資料，從學生資料表取得學號和姓名，課程資料表取得所有欄位，SQL指令如下所示： SELECT 學生資料表.學號, 學生資料表.姓名, 課程資料表.* FROM 課程資料表 RIGHT JOIN (學生資料表 INNER JOIN 選課資料表 ON 學生資料表.學號 = 選課資料表.學號) ON 選課資料表.課程編號 = 課程資料表.課程編號