SQL指令操作

Transcription

1 SQL 指令操作 本章將簡介 SQL 資料處理 ( 包含查詢 資料聚合函數 資料群組 SQL 運算子 新增 刪除 修改 ) SQL 簡介 1. 何謂 SQL? SQL(Structured Query Language) 是美國國家標準局,ANSI(American National Standards Institute) 制定用以與關連式資料庫溝通的標準語言 目前 ANSI SQL 的 SQL/92 版本己被國際標準組織 (International Organization for Standardization, ISO) 認定為國際標準 由於各廠商有其商業考量與技術差異, 各廠商認為 ANSI SQL 限制太多與功能不足, 因此各自提供的 SQL 功能可能有些差異 2. SQL 指令種類? SQL 提供的功能包含資料定義 ; 建立資料庫與資料表 ; 資料表內資料查詢 新增 刪除 修改 ; 資料庫控制與管理等, 歸納成以下幾種 : (1) 資料定義語言 (Data Definition Language, DDL): 用以定義資料庫綱要 (Schema), 即描述資料庫系統架構三層 ( 外部層 概念層 內部層 ) 的定義與其間相互對映關係 (2) 資料處理語言 (Data Manipulation Language, DML): 用來處理資料庫中的資料包含 : 查詢 (Select) 新增(Insert) 修改(Update) 以及刪除 (Delete) 等指令 (3) 資料控制語言 (Data Control Language, DCL): 用來控制資料庫的安全性與使用者權限, 即控制資料可以被使用者存取的權利 (4) 異動控制指令 (Transactional Control Command, TCC): 管理資料的異動情形包含儲存異動 (Commit) 復原異動(Rollback) 建立復原記號(Savepoint) 等 查詢 查詢資料庫必需使用 SELECT 指令, 本節將說明何謂資料庫查詢 如何使用 SELECT 指令 使用 WHERE 加入查詢條件以及查詢來自多個資料表的資料 1. 何謂資料庫查詢? SQL 查詢指令是使用 SELECT 指令至資料庫查詢符合條件的資料 例如查詢資 料庫的員工資料表, 搜尋員工姓名 查詢指令適合用於關連式資料庫中執行任務 2. SELECT 使用語法 SELECT 指令要與 FROM 指令合併使用 SELECT 指令用以指定查詢欄位清單 ; 1/28

2 FROM 指令用以指定查詢資料表清單 SELECT 指令語法如下 : SELECT [ * column1, column2, ] FROM table1, [table2, ] 指令中的 * 用以指定輸出顯示的所有欄位 ; 也可以指定特定欄位輸出查詢資 料 column1, column,, 欄位與欄位之間以逗號隔開 FROM 指令指定特定查詢 表格, 當跨多個表格查詢時也需以逗開 範例 : 查詢員工資料表 (employees) 的員工姓名 (Last Name) 與稱呼 (TitleOfCourtesy) m 檔程式碼參考如下 : 1 % 檔名 :SELECTemployeename.m 查詢 employees 資料表的姓名 (lastname) 與稱呼 (TitleOfCourtesy) 2 conn=database('sampledb', '', ''); 3 curs=exec(conn, 'SELECT LastName, TitleOfCourtesy FROM Employees'); 4 curs=fetch(curs); 5 SelectedData=curs.Data 6 close(curs); 7 close(conn); m 檔內的 SQL 指令的欄位名稱 資料庫名稱或資料表名稱內不能含有空白或 特殊字元 執行結果 : SelectedData = ' 張瑾雯 ' ' 陳季暄 ' ' 趙飛燕 ' ' 林美麗 ' ' 劉天王 ' ' 黎國明 ' ' 郭國臹 ' ' 蘇涵蘊 ' ' 孟庭亭 ' ' 賴俊良 ' ' 何大樓 ' ' 王大德 ' ' 小姐 ' ' 先生 ' ' 小姐 ' ' 小姐 ' ' 先生 ' ' 先生 ' ' 先生 ' ' 小姐 ' ' 小姐 ' ' 先生 ' ' 先生 ' ' 先生 ' 範例 : 查詢託運人資料表 (shippers) 的所有欄位資料 (*) 延用上述範例, 將第 3 2/28

3 行改成下列指令, 可參考檔名 SELECTall.m: curs=exec(conn, 'SELECT * FROM shippers'); 執行結果 : [1.00] ' 快遞 ' '(503) ' [2.00] ' 郵寄 ' '(503) ' [3.00] ' 親送 ' '(503) ' 3. WHERE 條件句 WHERE 指令搭配 SELECT 指令進行有條件句的資料篩選,WHERE 條件句主要目標是篩選出更少且符合條件的資料, 即 WHERE 子句是放查詢的條件 WHERE 子句中可以一個條件以上, 當條件句超過一個時可以使用連結指令 (AND 與 OR) 見後續介紹 範例 : 查詢訂貨明細資料表 (orderdetails) 的訂貨單編號 (orderid) 單價 (UnitPrice) 以及訂貨量 (Quantity) 等欄位資料, 篩選條件為訂貨量大於 120 延 用上述範例, 將第 3 行改成下列指令, 可參考檔名 WHEREquantity120.m curs=exec(conn, 'SELECT orderid, UnitPrice, Quantity FROM WHERE Quantity>120'); OrderDetails 執行結果 : [10764] [ 18] [130] [11072] [ ] [130] 4. 查詢多個資料表查詢跨多個資料表, 先決條件是該表格是有被授權的才能存取 即使用者查詢到的資料可以來自不同資料表 使用方法在 FROM 指定要查詢的資料表, 超過一個時以逗號隔開 ;SELECT 或 WHERE 指令後指定的欄位名稱必須要加上資料表名稱, 並以句號隔開, 語法如下 : SELECT [ * table1.column1, table2.column2, ] FROM table1, [table2, ] 範例 : 查詢產品 (Products) 與供應商 (Suppliers) 兩個資料表的產品名稱, 供應商名稱, 庫存量 產品名稱與庫存量來自於 Products 資料表 ; 供應商名稱來自供應商資料表 查詢條件為產品資料表的供應商編號與供應商資料表的供應商編號, 而且庫存量大於 100 者 m 檔 (SELECTMultipleTable.m) 程式碼參考如下 : 3/28

4 1 % 檔名 :SELECTMultipleTable.m 查詢跨資料表 (Products 與 Suppliers) 的產品資訊與供應商名稱, 2 % 條件是 Products 資料表的庫存量 (Inventory) 大於 conn=database('sampledb', '', ''); 4 curs=exec(conn, 'SELECT Products.ProductName, Suppliers.SupplierName, Products.Inventory FROM Products, Suppliers WHERE (Products.SupplierNo=Suppliers.SupplierNo) AND (Products.Inventory>=100)'); 5 curs=fetch(curs); 6 SelectedData=curs.Data 7 close(curs); 8 close(conn); 執行結果 : ' 醬油 ' ' 生活妙 ' [120.00] ' 再來米 ' ' 掬花 ' [104.00] ' 台中起司 ' ' 德級 ' [112.00] ' 啤酒 ' ' 力錦 ' [111.00] ' 魷魚 ' ' 小坊 ' [112.00] ' 蝦米 ' ' 普三 ' [123.00] ' 鴨肉 ' ' 佳佳 ' [115.00] ' 海鮮醬 ' ' 百達 ' [113.00] ' 海哲皮 ' ' 小坊 ' [101.00] ' 濃縮咖啡 ' ' 義美美 ' [125.00] 資料聚合函數 聚合函數可以協助使用者做一些簡單的資料彙整統計, 相關函數包括記錄筆數 (COUNT) 摘要性統計含最大值 (MAX) 最小值 (MIN) 加總 (SUM) 平均值 (AVG) 等 1. COUNT COUNT 函數主要用於統計資料表的資料筆數或一些不含數值的欄位筆數 範例 :(1) 統計供應商資料表 (Suppliers) 中的供應商家數 ;(2) 使用 COUNT 函數統計不含數值資料的記錄筆數, 條件為供應商居住在 高雄市 的家數 m 檔 (CountRecord.m) 程式碼參考如下 : 1 % 檔名 :CountRecord.m 資料聚合函數 2 clc 3 conn=database('sampledb', '', ''); 4/28

5 4 curs=exec(conn, 'SELECT COUNT(*) FROM suppliers'); 5 curs=fetch(curs); 6 TotalRecord=curs.Data; 7 fprintf('%s=%d%s\n', 'SampleDB 內的供應商家數共 ',cell2mat(totalrecord),' 家 ') 8 %COUNT 計算不含數值資料的記錄筆數 ( 居住在高雄市的家數 ) 9 curs=exec(conn, 'SELECT COUNT(City) FROM suppliers WHERE City='' 高雄市 '''); 10 curs=fetch(curs); 11 TotalNational=curs.Data; 12 fprintf('%s=%d%s\n', ' 供應商地址在高雄市的家數共 ',cell2mat(totalnational),' 家 ') 13 close(curs) 14 close(conn) 執行結果 : SampleDB 內的供應商家數共 =29 家 供應商地址在高雄市的家數共 =2 家 2. 摘要性統計 範例 : 統計產品資料表 (Products) 中的產品單價 (UnitPrice) 之最高價 最低價 平均價以及加總單價 * 訂貨量的金額 m 檔 (MaxMinSumAvg.m) 程式碼參考如下 : 1 % 檔名 :MaxMinSumAvg.m 摘要性統計函數 2 clc 3 conn=database('sampledb', '', ''); 4 curs=exec(conn, 'SELECT MAX(UnitPrice), MIN(UnitPrice),AVG(UnitPrice),SUM(UnitPrice * Ordered) FROM Products'); 5 setdbprefs('datareturnformat', 'numeric') 6 curs=fetch(curs); 7 StatData=curs.Data; 8 fprintf('%s=%8.2f%s\n', ' 產品最高價 ',StatData(1),' 元 ') 9 fprintf('%s=%8.2f%s\n', ' 產品最低價 ',StatData(2),' 元 ') 10 fprintf('%s=%8.2f%s\n', ' 產品平均價 ',StatData(3),' 元 ') 11 fprintf('%s=%10.2f%s\n', ' 總訂貨金額 ( 單價 * 訂貨量 )',StatData(4),' 元 ') 12 close(curs) 13 close(conn) 5/28

6 資料表名稱或欄位名稱不能有空白 執行結果 : 產品最高價 = 元產品最低價 = 2.50 元產品平均價 = 元總訂貨金額 ( 單價 * 訂貨量 )= 元 資料群組資料群組可以使用 GROUP BY 協助處理欄位內資料相同時做分類統計 以下將說明 GROUP BY 配合 SELECT 聚合函數 HAVING 以及 ORDER BY 指令的範例操作 1. GROUP BY 配合 SELECT 與聚合函數員工可能來自於不同城市,GROUP BY 指令可將員工來自同一個城市做人數統計 GROUP BY 搭配 SELECT 與聚合函數的查詢範例如下 : 範例 :(1) 查詢原始的 Employees 資料表的員工代號 姓名 (LastName) 英文名 (FirstName) 以及地址等資料 ;(2) 使用 GROUP BY 指令對 Employees 資料表的行政區 (Region) 做群組並使用 COUNT 函數統計筆數 ;(3) 使用 GROUP BY 對 Products 資料表的 Ordered 欄位做群組並以 MAX MIN AVG SUM 等函數求產品單價的最高價 最低價 平均價以及加總 m 檔 (GroupBySelect.m) 程式碼參考如下 : 1 % 檔名 :GroupBySelect.m 資料分類或群組 2 clc 3 conn=database('sampledb', '', ''); 4 % 查詢原始的 Employees 資料表資料 5 curs=exec(conn, 'SELECT EmployeeID, LastName, FirstName, City FROM Employees'); 6 setdbprefs('datareturnformat', 'cellarray'); 7 curs=fetch(curs); 8 RowData=curs.Data 9 % 使用 GROUP BY 對 Employees 資料表的 Region 做群組並 COUNT 統計筆數 10 curs=exec(conn, 'SELECT Region, COUNT(*) FROM Employees GROUP BY Region'); 11 curs=fetch(curs); 12 GroupByCount=curs.Data 6/28

7 13 % 使用 GROUP BY 對 Products 資料表的 Ordered 欄位做群組並以 MAX 求產品最高價統計其筆數 14 % 請先將 access 內的資料表表名稱空白刪除 15 curs=exec(conn, 'SELECT Ordered, MAX(UnitPrice),MIN(UnitPrice), AVG(UnitPrice), SUM(UnitPrice) FROM Products GROUP BY Ordered'); 16 curs=fetch(curs); 17 GroupByMaxMinAvgSum=curs.Data 18 close(curs) 19 close(conn) 執行結果 : RowData = [ 1.00] ' 張瑾雯 ' 'Mary' ' 台北市 ' [ 2.00] ' 陳季暄 ' 'Bradley' ' 台北市 ' [ 3.00] ' 趙飛燕 ' 'Kim' ' 台北市 ' [ 4.00] ' 林美麗 ' 'Chris' ' 台北市 ' [ 5.00] ' 劉天王 ' 'Mike' ' 台北市 ' [ 6.00] ' 黎國明 ' 'Bill' ' 台北市 ' [ 7.00] ' 郭國臹 ' 'Steven' ' 台北市 ' [ 8.00] ' 蘇涵蘊 ' 'Maggie' ' 台北市 ' [ 9.00] ' 孟庭亭 ' 'Linda' ' 台北市 ' [12.00] ' 賴俊良 ' 'Eddie' ' 台北市 ' [13.00] ' 何大樓 ' 'David' ' 台北市 ' [14.00] ' 王大德 ' 'John' ' 台北市 ' GroupByCount = ' 大同 ' [3.00] ' 中山 ' [2.00] ' 內湖 ' [1.00] ' 松山 ' [2.00] ' 信義 ' [2.00] ' 景美 ' [2.00] GroupByMaxMinAvgSum = [ 0] [263.50] [ 2.50] [31.30] [ ] [ 10.00] [ 46.00] [12.50] [27.88] [ ] [ 20.00] [ 10.00] [10.00] [10.00] [ 10.00] [ 30.00] [ 21.00] [21.00] [21.00] [ 21.00] 7/28

8 [ 40.00] [ 32.00] [10.00] [20.33] [ 61.00] [ 50.00] [ 26.00] [26.00] [26.00] [ 26.00] [ 60.00] [ 20.00] [20.00] [20.00] [ 20.00] [ 70.00] [ 12.75] [ 9.50] [11.19] [ 44.75] [ 80.00] [ 33.25] [33.25] [33.25] [ 33.25] [100.00] [ 17.00] [17.00] [17.00] [ 17.00] 2. GROUP BY 與 ORDER BY 同時使用 GROUP BY 與 ORDER BY 同樣是用來做排序, 兩者比較條列如下 : GROUP BY 用以群組相同的資料 ;ORDER BY 用來將查詢的資料做排序 若選擇做為群組的欄位若為非聚合函數, 必須在 GROUP BY 之後列出該欄位名稱, 反之若為聚合函數處理的欄位非必要列於 GROUP BY 之後 GROUP BY 與 ORDER BY 相同必要搭配 SELECT 指令使用 GROUP BY 做群組時不使用整數欄位 GROUP BY 與 ORDER BY 可以同時配合用於 SELECT 指令之後, 並同 ORDER BY 用放在 GROUP BY 之後 ORDER BY 可以指定等排序欄位的優先順序, 例如指定查詢後, 第 2 個欄位優先排序, 相同時再依第 1 欄位排序 見下列程式碼的第 5 行 範例 : 使用 GROUP BY 對 Employees 資料表的 Region 做群組並 COUNT 統計筆數, 指定某欄位統計筆數以 ORDER BY 做由小到大排序 m 檔 (GroupByOrderBy.m) 程式碼參考如下 : 1 % 檔名 :GroupByOrderBy.m 資料群組與排序 2 clc 3 conn=database('sampledb', '', ''); 4 % 使用 GROUP BY 對 Employees 資料表的 Region 做群組並 COUNT 統計筆數, 指定以統計筆數由小到大排序 5 curs=exec(conn, 'SELECT Region, COUNT(*) FROM Employees GROUP BY Region ORDER BY 2,1'); 6 curs=fetch(curs); 7 GroupOrder=curs.Data 8 close(curs) 9 close(conn) 執行結果 : GroupOrder = 8/28

9 ' 內湖 ' [1.00] ' 中山 ' [2.00] ' 松山 ' [2.00] ' 信義 ' [2.00] ' 景美 ' [2.00] ' 大同 ' [3.00] GroupOrder 請與上述 GroupBySelect.m 的 GroupByCount 矩陣內容做比較 GroupOrder 以第 2 欄由小到大排序 ;GroupByCount 以第 1 欄由小到大排序 3. HAVING 協助 GROUP BY 做條件式篩選 HAVING 指令的功能是優化 GROUP BY 群組化功能 HAVING 與 GROUP BY 必須與 SELECT 指令合併使用 HAVING 與 GROUP BY 的合作關係就如同 SELECT 與 WHERE HAVING 後設定的條件式是依據 GROUP BY 所建立的群組 ; 而 WHERE 的條件式則是建立在 SELECT 的查詢 範例 : 使用 GROUP BY 對 Products 資料表的 Ordered 做群組並以 HAVING 篩選出平均單價大於 20 的資料 m 檔 (GroupByHaving.m) 程式碼參考如下 : 1 % 檔名 :GroupByHaving.m 使用 GROUP BY 做群組並以 HAVING 設定篩選條件 2 clc 3 conn=database('sampledb', '', ''); 4 % 使用 GROUP BY 對 Products 資料表的 Ordered 做群組並以 HAVING 篩選出平均單價大於 20 的資料 5 curs=exec(conn, 'SELECT Ordered, AVG(UnitPrice) FROM Products GROUP BY Ordered HAVING AVG(UnitPrice)>20 ORDER BY 2'); 6 curs=fetch(curs); 7 GroupHavingData=curs.Data 8 close(curs) 9 close(conn) 執行結果 : GroupHavingData = [40.00] [20.33] [30.00] [21.00] [50.00] [26.00] [10.00] [27.88] [ 0] [31.30] 9/28

10 [80.00] [33.25] SQL 運算子 運算子主要用於 WHERE 子句中的輔助運算 SQL 運算子分為 關係運算子 邏輯運算子 算術運算子 以及 否定條件運算子 1. 關係運算子 關係運算子在矩陣運算中很常見, 主要是比較兩個元素之間的大小與相等關係, 關係運算子見下表 關係運算子符號或函數功能說明 > 大於, 例如 :a=5, 判斷 a>0 為真 < 小於, 同上例 a<0 是假 > = 大於等於,a>=0 為真 < = 小於等於, a<=0 為假 <> 不等於,a<>0 為真 = 等於,a=0 為假 範例 :(1) 篩選出 Products 資料表的 UnitPrice 單價欄位, 找出單價大於等於 50; (2) 篩選出 Products 資料表的 Ordered 不等於 0 且 UnitPrice 小於等於 10 的產品代號 單價 m 檔 (ComparisonOperators.m) 程式碼參考如下 : 1 % 檔名 :ComparisonOperators.m 關係運算子 2 clc 3 conn=database('sampledb', '', ''); 4 % 篩選出 UnitPrice 使用大於等於 50 5 curs=exec(conn, 'SELECT ProductID, UnitPrice FROM Products WHERE UnitPrice >= 50'); 6 curs=fetch(curs); 7 UnitPriceGreatherThan50=curs.Data 8 % 篩選出 Ordered 不等於 0 且 UnitPrice 小於等於 10 9 curs=exec(conn, 'SELECT ProductID, UnitPrice, Ordered FROM Products WHERE Ordered <> 0 AND UnitPrice <= 10'); 10 curs=fetch(curs); 11 NonEquality0ANDUnitPrice10=curs.Data 12 close(curs) 13 close(conn) 10/28

11 執行結果 : UnitPriceGreatherThan50 = [ 9.00] [ 97.00] [18.00] [ 62.50] [20.00] [ 81.00] [29.00] [123.79] [38.00] [263.50] [51.00] [ 53.00] [59.00] [ 55.00] NonEquality0ANDUnitPrice10 = [ 3.00] [10.00] [70.00] [21.00] [10.00] [40.00] [45.00] [ 9.50] [70.00] [74.00] [10.00] [20.00] 2. 邏輯運算子 邏輯運算是在計算邏輯變數之間的邏輯關係, 用於多個條件情境下, 判斷滿足某 些條件下該採取什麼動作 指令執行的優先順序中, 算術運算最高, 其次是關係 運算式 最後才是邏輯運算式 SQL 邏輯運算子介紹見表 邏輯運算子 符號或函數 功能說明 IS NULL 尋找欄位值為空值 BETWEEN 設定判斷數值必須介於某一最大值與最小值之間 IN 比較數值和某一指定串字上的數值相同 LIKE 某一數值或字串以萬用字元做比較是否相同 OR 或 (or) 的運算, 其中一個運算元為真, 即為真 AND 且 (and) 的運算, 所有運算元皆為真, 才為真 範例 :(1) 查詢沒有繳照片而且稱呼是先生的員工姓名與稱呼 ;(2) 篩選出 UnitPrice 的價格介於 0 元到 5 元之間的產品代號與單價 ;(3) 篩選出單價為 10 與 20 元的產品代號, 產品名稱, 單價的記錄 ;(4) 篩選客戶位於 宜蘭市 或 桃園縣 的客戶代號, 公司名稱與地址 m 檔 (LogicalOperatorsPartI.m) 程式碼參考如下 : 1 % 檔名 :LogicalOperatorsPartI.m 邏輯運算子第 I 部份 2 clc 3 conn=database('sampledb', '', ''); 4 setdbprefs('datareturnformat', 'cellarray'); 5 % 沒有繳照片而且稱呼是先生的員工姓名與稱呼 11/28

12 6 disp(' 顯示員工沒有繳照片而且稱呼是先生的員工姓名與稱呼 ') 7 curs=exec(conn, 'SELECT LastName, TitleOfCourtesy FROM Employees WHERE Photo IS NULL AND TitleOfCourtesy='' 先生 '''); 8 curs=fetch(curs); 9 NoFaxCompany=curs.Data 10 % 篩選出 UnitPrice 的價格介於 0 元到 5 元之間 11 disp(' 篩選出價格介於 0 元到 5 元之間的產品代號, 單價 ') 12 curs=exec(conn, 'SELECT ProductID, UnitPrice FROM Products WHERE UnitPrice BETWEEN 0 AND 5'); 13 curs=fetch(curs); 14 UnitPriceBetween10And20=curs.Data 15 % 篩選出產品單價為 10 與 20 元的記錄 16 disp(' 篩選出單價為 10 與 20 元的產品代號, 產品名稱, 單價的記錄 ') 17 curs=exec(conn, 'SELECT ProductID, ProductName, UnitPrice FROM Products WHERE UnitPrice IN (10, 20) ORDER BY 3'); 18 curs=fetch(curs); 19 UnitPriceIN1020=curs.Data 20 % 篩選客戶位於宜蘭市或桃園縣的客戶代號, 公司名稱與地址 21 disp(' 篩選客戶位於宜蘭市或桃園縣的客戶代號, 公司名稱與地址 ') 22 curs=exec(conn, 'SELECT CustomerID, CompanyName, Address FROM Customers WHERE City ='' 宜蘭市 '' OR City ='' 桃園縣 '' '); 23 curs=fetch(curs); 24 CountryGermanyFrance=curs.Data 25 close(curs) 26 close(conn) 執行結果 : 顯示員工沒有繳照片而且稱呼是先生的員工姓名與稱呼 NoFaxCompany = ' 賴俊良 ' ' 先生 ' ' 何大樓 ' ' 先生 ' ' 王大德 ' ' 先生 ' 篩選出價格介於 0 元到 5 元之間的產品代號, 單價 UnitPriceBetween10And20 = [24.00] [4.50] [33.00] [2.50] 12/28

13 篩選出單價為 10 與 20 元的產品代號, 產品名稱, 單價的記錄 UnitPriceIN1020 = [74.00] ' 雞湯塊 ' [10.00] [21.00] ' 花生 ' [10.00] [ 3.00] ' 蕃茄醬 ' [10.00] [49.00] ' 薯條 ' [20.00] 篩選客戶位於宜蘭市或桃園縣的客戶代號, 公司名稱與地址 CountryGermanyFrance = 'BLAUS' ' 琴花卉 ' ' 宜蘭市經國路 55 號 ' 'COMMI' ' 美國運海 ' ' 桃園縣富國路 42 號 ' 'FISSA' ' 嘉元實業 ' ' 宜蘭市經國路 38 號 ' 'GOURL' ' 泰安銀行 ' ' 桃園縣富國路 42 號 ' 'HUNGO' ' 師大貿易 ' ' 宜蘭市經國路 55 號 ' 'LILAS' ' 山南人壽 ' ' 桃園縣富國路 77 號 ' 'MORGK' ' 仲堂企業 ' ' 宜蘭市經國路 55 號 ' 'QUEDE' ' 蘭格英語 ' ' 桃園縣富國路 42 號 ' 'RICAR' ' 宇欣實業 ' ' 宜蘭市經國路 55 號 ' 'SUPRD' [1x11 char] ' 桃園縣富國路 42 號 ' 'TRAIH' ' 伸格公司 ' ' 宜蘭市經國路 55 號 ' 'WOLZA' ' 漢典電機 ' ' 桃園縣富國路 42 號 ' LIKE 與 LIKE 配合使用的萬用字元有 %( 百分比 ) 與 _( 底線 ) 百分比符號可用於 0 個 1 個或多個字元 ; 底線用於單一數字或字元, 百分比與底線可以混用 使用說明如下 : WHERE LastName LIKE ''D%''' 尋找任何字串, 以 D 為開頭且以後可為任可字元 WHERE LastName LIKE ''%e%''' 尋找任何字串為開頭, 中間有 e 字串 WHERE LastName LIKE ''_e%''' 尋找第 1 字為任何字元, 第 2 個字元為 e, 以後可為任可字元 WHERE LastName LIKE ''%k''' 尋找任何字串為開頭, 最後為 k 字串 WHERE LastName LIKE ''_e%g''' 尋找第 1 字為任何字元, 第 2 個字元為 e, 以後可為任可字元, 但最後一個字元為 g 範例 :(1) 使用 LIKE 萬用字元 - 尋找任何字串, 以 ' 東 ' 為開頭且以後可為任可字元 的客戶代號與公司名稱 ;(2) 尋找任何字串為開頭, 中間有 ' 忠孝東路 ' 字串的公司 名稱與地址 ;(3) 尋找第 1 字為任何字元, 第 2 個字元為 大, 以後可為任可字 13/28

14 元的員工姓名 ;(4) 尋找任何字串為開頭, 職稱最後為經理字串的員工姓名與職 稱 m 檔 (LIKEOperators.m) 程式碼參考如下 : 1 % 檔名 :LIKEOperators.m LIKE 萬用字元 2 clc 3 conn=database('sampledb', '', ''); 4 setdbprefs('datareturnformat', 'cellarray'); 5 disp(' 尋找任何字串, 以東為開頭且以後可為任可字元的客戶代號與公司名稱 ') 6 curs=exec(conn, 'SELECT CustomerID, CompanyName FROM Customers WHERE CompanyName LIKE '' 東 %'''); 7 curs=fetch(curs); 8 LIKED=curs.Data 9 disp(' 尋找任何字串為開頭, 地址中間有忠孝東路字串的公司名稱與地址 ') 10 curs=exec(conn, 'SELECT CompanyName, Address FROM Customers WHERE Address LIKE ''% 忠孝東路 %'''); 11 curs=fetch(curs); 12 LIKEManye=curs.Data 13 disp(' 尋找第 1 字為任何字元, 第 2 個字元為大, 以後可為任可字元的員工姓名 ') 14 curs=exec(conn, 'SELECT LastName FROM Employees WHERE LastName LIKE ''_ 大 %'''); 15 curs=fetch(curs); 16 LIKEe=curs.Data 17 disp(' 尋找任何字串為開頭, 職稱最後為經理字串的員工姓名與職稱 ') 18 curs=exec(conn, 'SELECT LastName, Title FROM Employees WHERE Title LIKE ''% 經理 '''); 19 curs=fetch(curs); 20 k=curs.data 21 disp(' 尋找第 1 字為任何字元, 第 2 個字元為北, 以後可為任可字元, 但最後幾個字元為 24 號 ') 22 curs=exec(conn, 'SELECT CompanyName, Address FROM Customers WHERE Address LIKE ''_ 北 %24 號 '''); 23 curs=fetch(curs); 24 g=curs.data 25 close(curs) 26 close(conn) 14/28

15 執行結果 : 尋找任何字串, 以東為開頭且以後可為任可字元的客戶代號與公司名稱 LIKED = 'ANATR' ' 東南實業 ' 'HILAA' ' 東遠銀行 ' 'TOMSP' ' 東帝望 ' 尋找任何字串為開頭, 地址中間有忠孝東路字串的公司名稱與地址 LIKEManye = ' 三川實業有限公司 ' [1x12 char] ' 世邦 ' [1x11 char] ' 小中企銀 ' [1x12 char] ' 保信人壽 ' [1x12 char] ' 加美留學中心 ' [1x12 char] ' 上河工業 ' [1x12 char] 尋找第 1 字為任何字元, 第 2 個字元為大, 以後可為任可字元的員工姓名 LIKEe = ' 何大樓 ' ' 王大德 ' 尋找任何字串為開頭, 職稱最後為經理字串的員工姓名與職稱 k = ' 陳季暄 ' ' 業務經理 ' ' 劉天王 ' ' 業務經理 ' 尋找第 1 字為任何字元, 第 2 個字元為北, 以後可為任可字元, 但最後幾個字元 為 24 號 g = ' 邁多貿易 ' ' 台北市北平東路 24 號 ' ' 業永房屋 ' ' 台北市北平東路 24 號 ' ' 台利材料 ' ' 台北市北平東路 24 號 ' ' 德化食品 ' ' 台北市北平東路 24 號 ' ' 千固 ' ' 台北市北平東路 24 號 ' ' 千固 ' ' 台北市北平東路 24 號 ' 3. 算術運算子 SQL 指令與其他程式語言相同可以使用算術運算子, 以下介紹四種傳統的算術 15/28

16 運算子 : 加 減 乖 除見下表 算術運算子 運算符號與函數名稱 功能說明 A+B A 加 B A-B A 減 B A*B A 乘 B A/B A 除 B 範例 : 查詢訂貨明細資料表 (orderdetails) 的訂單金額 ( 單價 * 訂貨量 = UnitPrice*Quantity) 減去折扣 (UnitPrice*Quantity*Discount) 後除以 100( 即以百元為單位 ), 條件是折扣等於 20% 且單價大於 120 者 m 檔 (ArithmeticOperator.m) 程式碼參考如下 : 1 % 檔名 :ArithmeticOperator.m SQL 指令內含算術運算子 2 conn=database('sampledb', '', ''); 3 setdbprefs('datareturnformat', 'numeric'); curs=exec(conn, 'SELECT 4 ((UnitPrice*Quantity)-(UnitPrice*Quantity*Discount))/100 FROM orderdetails WHERE Discount=0.2 and UnitPrice>120'); 5 curs=fetch(curs); 6 SelectedData=num2str(curs.Data,4) 7 close(curs); 8 close(conn); 執行結果 : 否定條件運算子若要改變邏輯運算子的判斷條件, 可以使用 NOT 否定這些運算子的結果, 即取相反的條件 可與 NOT 搭配的邏輯運算子如 IN, LIKE, NULL, BETWEEN 等見下表 否定條件運算子 NOT IN 尋找不存在在條件清單內的記錄 NOT LIKE IS NOT NULL NOT BETWEEN 尋找不相似萬用字元設定條件的記錄 尋找欄位值為非空值的記錄 任何不在某最大值與最小值範圍內的記錄 16/28

17 範例 : 以四個例子說明否定條件式 :(1)NOT IN: 尋找 大同 不存在行政區清單內的員工姓名, 城市, 行政區 ;(2)NOT LIKE: 尋找員工稱呼不相似 先生 之萬用字元的員工姓名, 稱呼 ;(3)IS NOT NULL: 尋找員工資料表中照片欄位 (Photo) 不是空值的員工姓名, 稱呼 ;(4)NOT BETWEEN: 尋找產品資料表中單價不在 5 元到 120 之間的不是空值的產品名稱, 單價 m 檔 (NOTOperator.m) 程式碼參考如下 : 1 % 檔名 :NOTOperator.m 否定條件句 2 clear all 3 clc 4 conn=database('sampledb', '', ''); 5 setdbprefs('datareturnformat', 'cellarray'); 6 disp(' 尋找大同不存在行政區清單內的員工姓名, 城市, 行政區 ') curs=exec(conn, 'SELECT LastName, City, Region FROM employees 7 WHERE Region NOT IN ('' 大同 '')'); 8 curs=fetch(curs); 9 NOTINData=curs.Data 10 disp(' 尋找員工稱呼不相似先生之萬用字元的員工姓名, 稱呼 ') curs=exec(conn, 'SELECT lastname,titleofcourtesy FROM employees 11 WHERE TitleOfCourtesy NOT LIKE ''% 先生 %'''); 12 curs=fetch(curs); 13 NOTLIKEData=curs.Data 14 disp(' 尋找員工資料表中照片欄位 (Photo) 不是空值的員工姓名, 稱呼 ') curs=exec(conn, 'SELECT LastName, TitleOfCourtesy FROM 15 Employees WHERE Photo IS NOT NULL'); 16 curs=fetch(curs); 17 NOTISNULLData=curs.Data disp(' 尋找產品資料表中單價不在 5 元到 120 之間的不是空值的產品名 18 稱, 單價 ') curs=exec(conn, 'SELECT ProductName, UnitPrice FROM products 19 WHERE unitprice NOT BETWEEN 5 AND 120'); 20 curs=fetch(curs); 21 NOTBETWEENData=curs.Data 22 close(curs); 23 close(conn); 執行結果 : 尋找大同不存在行政區清單內的員工姓名, 城市, 行政區 NOTINData = 17/28

18 ' 張瑾雯 ' ' 台北市 ' ' 中山 ' ' 趙飛燕 ' ' 台北市 ' ' 松山 ' ' 林美麗 ' ' 台北市 ' ' 景美 ' ' 劉天王 ' ' 台北市 ' ' 松山 ' ' 黎國明 ' ' 台北市 ' ' 中山 ' ' 蘇涵蘊 ' ' 台北市 ' ' 信義 ' ' 賴俊良 ' ' 台北市 ' ' 信義 ' ' 何大樓 ' ' 台北市 ' ' 景美 ' ' 王大德 ' ' 台北市 ' ' 內湖 ' 尋找員工稱呼不相似先生之萬用字元的員工姓名, 稱呼 NOTLIKEData = ' 張瑾雯 ' ' 小姐 ' ' 趙飛燕 ' ' 小姐 ' ' 林美麗 ' ' 小姐 ' ' 蘇涵蘊 ' ' 小姐 ' ' 孟庭亭 ' ' 小姐 ' 尋找員工資料表中照片欄位 (Photo) 不是空值的員工姓名, 稱呼 NOTISNULLData = ' 張瑾雯 ' ' 小姐 ' ' 陳季暄 ' ' 先生 ' ' 趙飛燕 ' ' 小姐 ' ' 林美麗 ' ' 小姐 ' ' 劉天王 ' ' 先生 ' ' 黎國明 ' ' 先生 ' ' 郭國臹 ' ' 先生 ' ' 蘇涵蘊 ' ' 小姐 ' ' 孟庭亭 ' ' 小姐 ' 尋找產品資料表中單價不在 5 元到 120 之間的不是空值的產品名稱, 單價 NOTBETWEENData = ' 汽水 ' [ 4.50] ' 鴨肉 ' [123.79] ' 台中起司 ' [ 2.50] ' 綠茶 ' [263.50] 以下程式未測試 18/28

19 新增資料新增資料的範例說明如下 : 從 orders 資料表截取運費成本 freight cost 計算平均運費成本 (mean freight cost) 以及計算日期 儲存資料到細胞矩陣 從 Matlab 工作空間匯出資料到資料表 詳細的操作步驟說明如下 : (1) 若資料庫連線已關閉則重新連線 conn=database('sampledb', '', '') (2) SQL 查詢指令中, 資料表改成 orders 欄位改成 freight curs=exec(conn, 'SELECT freight FROM orders') (3) 資料匯出格式改成數值型態 numeric setdbprefs('datareturnformat', 'numeric') (4) 截取游標的 3 筆資料 curs=fetch(curs,3) (5) 設定游標的資料到 Matlab 工作空間的變數 FC FC=curs.Data FC = (6) 計算平均運費成本, 儲存到 MeanFreightCost MeanFreightCost=mean(FC) MeanFreightCost = (7) 設定計算平均運費成本的日期儲存於 FreightCostDate FreightCostDate='29-Dec-2008' FreightCostDate = 29-Dec /28

20 (8) 將 FreightCostDate 與 MeanFreightCost 設定給細胞矩陣 InputData, 準備匯入到資料庫 InputData={FreightCostDate, MeanFreightCost} InputData = '29-Dec-2008' [ ] (9) 指定要匯出到資料表的欄位名稱 (Calc_Date 與 Avg_Cost) 並儲存於細胞矩陣 (colnames) 中, 此欄位必需已經存在 SampleDB 資料庫的 Avg_Freight_Cost 資料表中 colnames={'calc_date', 'Avg_Cost'} colnames = 'Calc_Date' 'Avg_Cost' (10) 取得資料庫自動提交 (AutoCommit) 的狀態, 使用 get 函數取得自動提交的旗幟, on 表示資料庫處於可提交狀態; off 則否 get(conn, 'Autocommit') ans = on (11) 使用 fastinsert 函數將資料匯出到 Avg_Freight_Cost 資料表內 >>fastinsert(conn, 'Avg_Freight_Cost', colnames, InputData) 可查看 Nwind_New.mdb 內的 Avg_Freight_Cost 資料表中的 Calc_Date 與 Avg_Cost 欄位是否多了 29-Dec-2008 與 36 兩項資料見下圖 Avg_Freight_Cost Calc_Date Avg_Cost 29-Dec (12) 關閉游標與資料庫 close(curs) close(conn) 將上述指令寫成 M 檔, 並判斷是否處於可提交狀態, 若是則新增資料到資料庫 ; 若否則以 msgbox 函數提示錯誤, 程式碼參考如下 : 1 % 檔名 :InsertMeanFreightCost.m 新增資料到資料庫 2 conn=database('sampledb', '', '') 3 curs=exec(conn, 'SELECT freight FROM orders') 4 setdbprefs('datareturnformat', 'numeric') 20/28

21 5 curs=fetch(curs,3) 6 FC=curs.Data 7 MeanFreightCost=mean(FC) 8 FreightCostDate='29-Dec-2008' 9 InputData={FreightCostDate, MeanFreightCost} 10 colnames={'calc_date', 'Avg_Cost'} 11 if get(conn, 'Autocommit')=='on' 12 fastinsert(conn, 'Avg_Freight_Cost', colnames, InputData) 13 else 14 msgbox(' 資料庫自動提交狀況有誤 ') 15 end 16 close(curs) 17 close(conn) 2. 匯入多筆資料到資料庫匯入多筆資料到資料庫的範例使用日銷售資料彙整成月資料後再將各月份總銷售額匯入到 tutorial.mdb 資料庫 操作步驟說明如下 : (1) 重新設定 ODBC 資料來源名稱為 dbtoolboxdemo ; 描述為 toturial ; 點選 選取 資料庫會出現 選取資料庫, 選擇您要連線資料庫的儲存路徑與名稱 tutorial.mdb, 按 確定 離開 離開 querybuiler, 重新進入即可看到 Data source 點選 dbtoolboxdemo 之後 Tables 會出現資料庫內的資料表, 表示 ODBC 設定成功見下圖 21/28

22 (2) 重新連線資料庫 conn=database('dbtoolboxdemo', '', '') dbtoolboxdemo 前後不得有空格, 否則無法連線成功,conn 結構中的 Message 會出現下列錯誤訊息 : conn = Instance: ' dbtoolboxdemo ' UserName: '' Driver: [] URL: [] Constructor: [1x1 com.mathworks.toolbox.database.databaseconnect] Message: '[Microsoft][ODBC 驅動程式管理員 ] 找不到資料來源名稱且未指定預設的驅動程式 ' Handle: 0 TimeOut: [] AutoCommit: 'off' Type: 'Database Object' 22/28

23 (3) 使用資料庫偏好設定函數 setdbprefs 函數設定回傳的資料型態為數值 ; 並且 當資料庫內的欄位值為空值 (NULL) 時自動轉換為 0 DataReturnFormat 設定 為 numeric 時,NullNumberRead 也必需是數值型態 setdbprefs({'nullnumberread';'datareturnformat'},{'0';'numeric'}) (4) 執行 SQL 指令, 查詢 salesvolume 資料表的所有欄位並指定給游標 curs=exec(conn, 'select * from salesvolume') curs=fetch(curs) curs = Attributes: [] Data: [10x13 double] DatabaseObject: [1x1 database] RowLimit: 0 SQLQuery: 'select * from salesvolume' Message: [] Type: 'Database Cursor Object' ResultSet: [1x1 sun.jdbc.odbc.jdbcodbcresultset] Cursor: [1x1 com.mathworks.toolbox.database.sqlexec] Statement: [1x1 sun.jdbc.odbc.jdbcodbcstatement] Fetch: [1x1 com.mathworks.toolbox.database.fetchthedata] (5) 使用 columnnames 函數檢視游標即資料表內的欄位名稱儲存於 FieldNames FieldNames=columnnames(curs) FieldNames = 'StockNumber','January','February','March','April','May','June','July','August','Septemb er','october','november','december' (6) 檢視游標的 2 月份 (February) 資料 ( 第 3 欄 ) FebSalesVolume=curs.Data(:,3) FebSalesVolume = /28

24 (7) 最得游標的矩陣維度列與行 [r,c]=size(curs.data); (8) 依據 r 與 c 計算每個月的銷售量 MonthlyData 先將 MonthlyData 與 MonthSalesVolume 設為空集合 ;MonthSalesVolume 變數是依據 curn.data 第 i 欄 ( 即月份 ) 截取的所有產品銷售資料 ;MonthlyData 變數是加總該月份所有產品的銷售量 i 自 2 開始表示從第 2 欄的 January 月份開始截取資料 MonthlyData=[]; MonthSalesVolume=[]; for i=2:c MonthSalesVolume=curs.Data(:,i); MonthlyData(i-1,1)=sum(MonthSalesVolume); MonthSalesVolume=[]; end MonthlyData 執行結果 : MonthlyData = (9) 指定一個欄位名稱的字串矩陣 (salestotal) 儲存於 colnames 細胞矩陣中, 準備 新增到資料表之用 salestotal 欄位必需已經存在於 yearlysales( 即將新增的目 的地資料表中 ) 見下圖 24/28

25 colnames{1,1}='salestotal'; (10) 使用 fastinsert 函數新增多筆資料到 yearlysales 資料表的 salestotal 欄位中, 新增後的結果見下圖 fastinsert(conn, 'yearlysales', colnames, MonthlyData) (11) 關閉游標與資料庫 >>close(curs) >>close(conn) 將上述指令寫成 M 檔, 程式碼參考如下 : 1 % 檔名 :InsertMultipleRecords.m 匯入多筆資料到資料庫 2 conn=database('dbtoolboxdemo', '', '') 3 ping(conn) 4 get(conn, 'Autocommit') 5 setdbprefs({'nullnumberread';'datareturnformat'},{'0';'numeric'}) 6 curs=exec(conn, 'select * from salesvolume') 7 curs=fetch(curs) 8 FieldNames=columnnames(curs) 9 FebSalesVolume=curs.Data(:,3) 10 [r,c]=size(curs.data); 11 MonthlyData=[]; 12 MonthSalesVolume=[]; 25/28

26 13 for i=2:c 14 MonthSalesVolume=curs.Data(:,i); 15 MonthlyData(i-1,1)=sum(MonthSalesVolume); 16 MonthSalesVolume=[]; 17 end 18 MonthlyData 19 colnames{1,1}='salestotal'; 20 fastinsert(conn, 'yearlysales', colnames, MonthlyData) 21 close(curs) 22 close(conn) 更新資料更新資料的範例是延續上述 新增資料 範例操作, 說明如下 : (1) 若資料庫連線已關閉則重新連線 >> conn=database('sampledb', '', '') (2) 設定要更新的資料, 新日期為 29-Dec-2002, 資料要以細胞矩陣方式儲存 >>NewDate={'29-Dec-2002'} NewDate = '29-Dec-2002' 要更新的資料若不是細胞矩陣型態會出現下列錯誤訊息 :??? Error using ==> database.update at 90 Input data must be a cell array, matrix, or structure (3) 更新欄位名稱同 新增資料 >>colnames={'calc_date', 'Avg_Cost'} colnames = 'Calc_Date' 'Avg_Cost' (4) 針對特定記錄做資料更新, 需使用 SQL 的 where 指令, 設定特定條件到 whereclause 變數中, 要更新的原始日期資料為 29-Dec-2008 因為資料屬字串型態, 且要當做傳遞之用, 所以要加入雙單引號 >>whereclause='where Calc_Date = ''29-Dec-2008''' whereclause = where Calc_Date = '29-Dec-2008' (5) 執行更新指令, 指定資料庫連線變數 (conn); 資料表名稱 (Avg_Freight_Cost); 26/28

27 要更新的欄位名稱 (Calc_Date, Avg_Cost) 之儲存的變數 (colnames); 要更新的 新資料之變數名稱 (NewDate) 以及條件式 (whereclause) >>update(conn, 'Avg_Freight_Cost', colnames, NewDate, whereclause) 將上述指令寫成 M 檔, 並判斷是否處於可提交狀態, 若是則新增資料到資料庫 ; 若否則以 msgbox 函數提示錯誤, 程式碼參考如下 : 1 % 檔名 :UpdateMeanFreightCostDate.m 更新資料到資料庫 2 conn=database('sampledb', '', '') 3 NewDate={'29-Dec-2002'} 4 colnames={'calc_date', 'Avg_Cost'} 5 whereclause='where Calc_Date = ''29-Dec-2008''' 6 if get(conn, 'Autocommit')=='on' 7 update(conn, 'Avg_Freight_Cost', colnames, NewDate, whereclause) 8 else 9 msgbox(' 資料庫自動提交狀況有誤 ') 10 end 11 close(curs) 12 close(conn) 更新後可查看 Nwind_New.mdb 內的 Avg_Freight_Cost 資料表中的 Calc_Date 欄 位是否已改為 29-Dec-2002 見下圖 Avg_Freight_Cost Calc_Date Avg_Cost 29-Dec 刪除資料使用者可使用 delete 指令刪除資料表中的記錄,delete 指令用以刪除整筆記錄而非單一或特定欄位資料 delete 指令需要小心使用, 若刪除動作成功, 該筆記錄將永久被刪除於資料庫中 1 % 檔名 :DeleteSQL.m 刪除資料表的資料 2 conn=database('sampledb', '', '') 3 curs=exec(conn, 'delete from Avg_Freight_Cost where Calc_Date = ''29-Dec-2008''') 4 close(curs) 5 close(conn) 27/28

28 請檢視 Avg_Freight_Cost 資料表內的日期為 29-Dec-2008 是否已被刪除 程式 執行請確認該筆資料是否存在 28/28