投影片 1

Size: px

Start display at page:

Download "投影片 1"

罹汪
6 years ago
Views:

1 第 9 章 SQL 基本語法本著作僅授權老師於課堂使用, 切勿置放在網路上播放或供人下載, 除此之外, 未經授權不得將全部或局部內容以任何形式重製轉載變更散佈或以其他任何形式基於任何目的加以利用著作權所有旗標出版股份有限公司

2 本章重點 9-1 認識 SQL 敘述 9-2 SELECT 敘述 9-3 WHERE ORDER BY LIMIT 子句 9-4 多資料表查詢 JOIN 與子查詢 9-5 INSERT UPDATE DELETE 敘述 9-6 常用函式 2

3 9-1 認識 SQL 敘述結構化查詢語言 SQL 關鍵字子句與敘述保留字與字串指定資料表與欄位在 phpmyadmin 中執行 SQL 敘述 3

4 結構化查詢語言 SQL SQL (Structured Query Language, 一般習慣唸成 "sequel", 但正確的唸法應該是 "S-Q-L") 中文譯為結構化查詢語言它是目前關聯式資料庫管理系統所使用的查詢語言, 使用者可以利用 SQL 語法直接對關聯式資料庫進行存取與管理的操作 SQL 的基本語法是由一些簡單的英文句子所構成, 相當簡單易學 4

5 5 結構化查詢語言 SQL 例如, 我們要從 books 資料表中找出價格高於 400 元的書籍, 並列出所有的欄位資料, 用 SQL 語言來寫, 只要下面幾行就可以了 : 我們只需指出自己所要的資料條件, 根本不必知道資料庫是怎麼找到或整理資料的, 可以說是相當省事

6 關鍵字子句與敘述 SQL 語法的基礎是子句 (clause), 子句中會包括一些關鍵字 (keyword) 關鍵字是對 MySQL 有特別意義的字, 例如 "SELECT" "FROM" 與 "WHERE"... 等至於敘述 (statement) 則是指一組可產生存取資料庫結果的子句集合例如 : 6

7 關鍵字子句與敘述前面 3 行子句組合起來便成為一組敘述, 其作用是 : 從 (FROM) books 資料表中, 找出符合條件 (WHERE) 即價格欄位超過 400 元的記錄, 並將這些記錄的所有欄位都挑選 (SELECT) 出來但是, 敘述不一定要由多個子句組成! 如果一個子句就能獨立完成一件事, 該子句就是一個敘述 7

8 8 關鍵字子句與敘述例如 : 上面敘述會建立一個名為 MyDatabase 的資料庫, 此例中, 一個子句就可以完成建立新資料庫的動作, 所以這個子句也是一個敘述

9 保留字與字串在 MySQL 中, 關鍵字函式與資料型別的名稱都有特別的意義, 所以在 SQL 敘述中這些字屬於保留字, 不可以直接用來做為資料庫資料表欄位等名稱例如 : 如果想要建立名為 SELECT 的資料庫而執行以上敘述時, SELECT 對於 MySQL 而言是查詢資料的關鍵字, 因此以上敘述會產生語法錯誤, 無法正確建立資料庫 9

10 用反引號明確定義名稱為了避免混淆, 建議不要使用保留字做為資料庫資料表或欄位名稱不過如果真有其必要, 或是因為保留字太多擔心不小心誤用, 可以使用反引號 (`) 括住名稱, 即可讓伺服器知道此為資料庫資料表或欄位名稱, 而非 SQL 關鍵字或函式例如想要將訂單資料庫取名為 order, 但是 ORDER 卻是 SQL 敘述中用來排序的關鍵字, 此時可如下建立資料庫 : 10

11 11 保留字與字串在 SQL 敘述中需要使用字串時, 必須使用單引號 (') 括住, 例如要從 employee 資料表中找出性別欄位為女的員工, 需執行以下敘述 :

12 12 指定資料表與欄位在 SQL 敘述中, 可以使用資料表名稱. 欄位名稱來指定要使用哪一個資料表的欄位, 例如 : 當然在上例中, 資料來源就只有 books 資料表, 因此 books. 書籍名稱可以省略 books 不寫 :

13 13 指定資料表與欄位但是當我們在 SQL 敘述中同時使用多個資料表時, 資料表名稱. 欄位名稱的寫法才能分清楚各欄位是出自於哪一個資料表, 9-4 節我們就會看到這樣的應用

14 在 phpmyadmin 中執行 SQL 敘述本節將先透過 phpmyadmin 的介面連線 MySQL 資料庫來執行 SQL 敘述, 至於如何在 PHP 中執行 SQL 敘述, 將留待第 10 章再行說明請依照 1-5 節的說明, 以 root 帳號登入 phpmyadmin, 如下操作即可執行 SQL 敘述 : 14

15 在 phpmyadmin 中執行 SQL 敘述 15

16 在 phpmyadmin 中執行 SQL 敘述 16

17 9-2 SELECT 敘述基本語法當我們要從資料庫中查詢資料時, 必須使用 SELECT 敘述, SELECT 敘述的基本語法如下 : 我們先來看一個簡單的應用範例, 下面例子可以查詢 books 資料表中書籍名稱與價格兩個欄位的資料 17

18 SELECT 敘述 18

19 19 SELECT 敘述若要查詢資料表中所有欄位, 則可用星號 * 來代表 :

20 SELECT 敘述使用 AS 設定別名在 SELECT 敘述中, 我們可以替查詢結果的欄位取別名, 以變更輸出的欄位名稱指定別名時必須使用 AS 關鍵字, 其語法如下 : 例如下面例子, 便是利用別名讓欄位名稱變成英文 20

21 SELECT 敘述 21

22 22 SELECT 敘述除了欄位名稱外, 資料表名稱也可以設定別名, 當敘述中出現多次資料表名稱時, 設定資料表別名可以簡化輸入 :

23 SELECT 敘述欄位的運算 SELECT 敘述中的欄位名稱也可以加上運算式, 如此該欄位查詢到的資料都會依照運算式進行運算例如在 books 資料表中有一個價格欄位, 而我們可以將其乘上折數後來產生折扣價 23

24 SELECT 敘述 24

25 25 SELECT 敘述如果您覺得上例中價格 * 0.8 的欄位名稱不好看, 可以利用 AS 將其設定為其他名稱 :

26 9-3 WHERE ORDER BY LIMIT 子句 WHERE 子句基本語法比較運算子 AND OR NOT BETWEEN LIKE ORDER BY 子句 LIMIT 子句 26

27 WHERE 子句在基本的 SELECT 敘述後面, 可以加上各種子句來限制查詢的範圍, 其中最常見的是 WHERE 子句, 其功能是設定查詢的條件基本語法 :WHERE 子句的語法如下 : 例如我們想要從 employee 資料表中, 找出所有女性員工的資料, 就可以寫成下頁形式 27

28 28 WHERE 子句 WHERE 子句可以使用 MySQL 的比較與邏輯運算子來限制查詢的條件, 以下簡單介紹較常用的幾種運算子

29 WHERE 子句比較運算子以下是 MySQL 常用的比較運算子, 用法相當直覺 : AND OR NOT 這 3 個運算子為且或否的意思, 可以組合出複雜的條件 29

30 30 WHERE 子句例如 :

31 WHERE 子句 BETWEEN 和字面的意思一樣, 此運算子可以設定某個範圍的條件, 範圍的上下限間要用 AND 連接 31

32 WHERE 子句上例的 WHERE 條件與價格 >= 400 AND 價格 <= 500 的意思相同另外, 也可以加上 NOT, 例如價格 NOT BETWEEN 400 AND 500, 查詢不在範圍內的資料 LIKE LIKE 運算子可以用部分字串來找尋記錄, 一般會搭配以下萬用字元 : 32

33 33 WHERE 子句下面範例將查詢書名倒數第二個字為 " 實 " 的書籍 :

34 ORDER BY 子句 ORDER BY 子句可以將查詢的結果排序, 語法如下 : 排序方式分成 ASC ( 升冪, 由小而大 ) 與 DESC ( 降冪, 由大而小 ) 兩種, 若未指定, 則預設值為 ASC 下面範例會依照價格欄位, 由大到小排序書籍 34

35 ORDER BY 子句 35

36 36 ORDER BY 子句在 ORDER BY 子句中可指定多個排序 ( 用逗號分隔 ), 例如 :

37 LIMIT 子句 LIMIT 子句可以指定查詢的筆數範圍, 例如我們使用 SELECT 查詢時, 如果只想要取得查詢結果的第筆記錄, 便需要使用 LIMIT 子句其語法如下 : 請注意, 啟始筆數是從 0 開始計算, 例如下面範例會取得查詢結果的第 1 5 筆記錄 37

38 LIMIT 子句 38

39 LIMIT 子句啟始筆數是可以省略的參數, 如果省略, 則表示從查詢結果的第 1 筆開始取資料, 所以上面 LIMIT 子句也可以改成 LIMIT 5 LIMIT 子句通常會搭配 ORDER BY 子句, 用來將查詢排序後, 取出某個範圍的記錄如上例中將書籍依價格由高至低排列, 即可使用 LIMIT 子句找出價格最高的 5 本書 39

40 LIMIT 子句此外, LIMIT 子句也常用於分頁顯示, 例如購物網站可能有上百種商品, 所以顯示商品時會採用分頁的方式假設每頁顯示 10 種商品, 則會以下面方式從資料庫取得記錄 : 40

41 9-4 多資料表查詢 JOIN 與子查詢多資料表查詢多資料表查詢的原理實際範例 JOIN 子查詢 Subquery 41

42 多資料表查詢除了在一個資料表中查詢, SELECT 敘述還可以從多個相關 ( 不一定要建立關聯 ) 的資料表中取出資料, 用法相當有彈性多資料表查詢的原理 : 下面是 A B 兩個資料表的內容 : 42

43 43 多資料表查詢我們可以在 SELECT 敘述的 FROM 子句中指定多個資料表, 即可進行多資料表的查詢

44 多資料表查詢 44

45 多資料表查詢上面可以看到, 進行多資料表的查詢時, A 資料表的每一筆記錄都會和 B 資料表的每筆記錄連接為一筆新記錄, 而產生了 3 * 2 = 6 筆的記錄多資料表查詢所產生的記錄並不是每一筆都是我們想要的, 如上例中只有 3 筆 ( 加框線的記錄 ) 是有意義的資料此時可以使用 WHERE 子句來限制條件, 即可將查詢限制在我們需要的資料上 45

46 多資料表查詢實際範例接著就來看看多資料表查詢的實際範例, 下面是 books 與 employee 資料表的內容 46

47 多資料表查詢 47

48 48 多資料表查詢假如我們想要從 books 與 employee 資料表中, 找出所有書籍及其負責人的資料, 則可以如下查詢 :

49 49 多資料表查詢我們也可以在 WHERE 子句中使用多個限制條件來查詢, 例如下面將查詢所有 Linux 書籍的負責人 :

50 多資料表查詢上述範例中, 因為兩個資料表使用不同的欄位名稱, 所以 WHERE 子句可以僅指定欄位名稱, 而不會產生分不清楚是哪一個資料表的問題反之, 如果不同資料表內使用相同名稱的欄位, 那麼便必須使用資料表名稱. 欄位名稱的方式來指定資料表欄位下面例子為您示範指定資料表欄位的寫法 : 50

51 51 多資料表查詢上面寫法顯得相當繁雜, 此時可以使用別名來簡化 :

52 JOIN JOIN 的意義是將多個資料表的記錄橫向連接起來, 然後利用 ON 來設定條件以過濾不需要的記錄, 其實前述多資料表查詢就是 JOIN, 只是省略了 JOIN 的關鍵子例如前面的例子可以如下改用 JOIN: 52

53 53 JOIN 雖然兩個可以得到相同的結果, 可是比較起來, 用 JOIN...ON... 的方式較具有可讀性, 因為 JOIN 的條件不用和其他查詢條件混在一起 :

54 JOIN 除了可讀性較高以外, JOIN 還有多種方式可運用, 例如 LEFT JOIN CROSS JOIN... 等, 使用上較具彈性由於篇幅所限, 此處不加以說明 JOIN 的各式種類, 若您想要深入瞭解, 請參考 tml 網頁 54

55 子查詢 Subquery 所謂子查詢 (Subquery), 是指包含在主要查詢中的另一個 SELECT 查詢通常我們會利用子查詢先挑選出部份資料, 以做為主要查詢的資料來源或選取條件子查詢的語法和 SELECT 敘述一樣, 但整個子查詢敘述需用小括弧 ( ) 括住, 我們來看個子查詢的應用範例, 下面例子將找出劉敏敏所負責的所有書籍 55

56 子查詢 Subquery 56

57 子查詢 Subquery 上例中, 我們的目的是要從 book 資料表查出劉敏敏所負責的所有書籍, 可是每本書只有負責員工的編號, 員工的詳細資料是儲存在 employee 資料表中所以先利用子查詢在 employee 資料表中找出劉敏敏的員工編號, 然後再根據此員工編號找出其負責的所有書籍 57

58 9-5 INSERT UPDATE DELETE 敘述前面介紹了許多查詢資料的語法, 瞭解如何讀取資料之後, 再來自然是輸入資料本節會為您說明資料編輯 ( 插入更新刪除 ) 的 SQL 語法新增記錄 INSERT 敘述更新記錄 UPDATE 敘述刪除記錄 DELETE 敘述 58

59 新增記錄 INSERT 敘述首先介紹為資料表新增記錄的 INSERT 敘述基本語法如下 : 我們就利用上述的語法, 替 employee 資料表新增記錄為方便對照欄位的屬性, 先將 employee 資料表的結構列示如下 59

60 60 新增記錄 INSERT 敘述接著利用 INSERT 敘述為 employee 資料表新增兩筆記錄

61 新增記錄 INSERT 敘述 61

62 新增記錄 INSERT 敘述上述兩個 INSERT 敘述中有些欄位被省略了, 對於沒有被指定資料的欄位, MySQL 會如下處理 : 如果欄位設定了 auto_increment 屬性, 那麼該欄位將自動累加編號如果欄位有設定預設值, 則填入預設值如果欄位允許 NULL, 則填入 NULL 若前幾項都不符合時, 則會顯示錯誤訊息而取消操作, 不輸入任何資料 62

63 63 更新記錄 UPDATE 敘述我們可以使用 UPDATE 敘述來更新編輯資料表中的記錄, 基本語法如下 : 例如下面敘述可以修改 employee 資料表中的記錄

64 更新記錄 UPDATE 敘述 64

65 更新記錄 UPDATE 敘述 65

66 更新記錄 UPDATE 敘述設定新的欄位值時, 可以引用同一欄位或是其他欄位的值來做變化例如下面敘述可以將 books 資料表中所有 Linux 書籍的價格提高 10%: 66

67 67 刪除記錄 DELETE 敘述如果要刪除資料表中的部分記錄, 可以使用 DELETE 敘述, 其語法如下 : 例如下面敘述將刪除 employee 資料表中的記錄 :

68 9-6 常用函式除了前面各節介紹的 SQL 敘述外, MySQL 還提供了許多好用的函數, 可以方便我們統計處理資料只要善用這些函數, 便可以幫助我們直接取得或計算想要的資料, 而不需要自行撰寫程式來處理本節將為您介紹常用的 MySQL 函式 68

69 69 隨機數字函數 MySQL 的 RAND() 函式可以產生之間的隨機浮點數 : 在 MySQL 中, 如果使用 "ORDER BY RAND()" 的語法, 則可將查詢結果隨機排序

70 隨機數字函數 70

71 隨機數字函數如果想在資料表中隨機取出 n 筆記錄, 例如抽獎時需要隨機取出 n 個使用者, 此時可以加上 LIMITn 來限制範圍下面例子會隨機從 employee 資料表取出 3 筆記錄 : 71

72 72 彙總函數以下函式可以用來統計欄位的最大最小平均... 等值 :

73 73 彙總函數範例如下 :

Microsoft Word - 關聯性資料庫.doc

Microsoft Word - 關聯性資料庫.doc 關聯式資料庫引用自 : 王勝雄, 台中市網 PHP 程式語言編寫研習課程網址 :http://km.tceb.edu.tw/~wsx/php/index.htm SQL 結構化查詢語言定義 & 源起 : SQL 是 Structured Query Language 的縮寫, 簡單的說,SQL 是一種與資料庫溝通的共通語言, 它是當時在 IBM 工作的 E.F. Codd 於 1970 年針對關聯式模型