MySQL 結構化查詢語言 MySQL.

Presentation on theme: "MySQL 結構化查詢語言 MySQL."— Presentation transcript:

1 MySQL 結構化查詢語言 MySQL

2 1 引言 SQL是什麼? 當用戶發出一項查詢，便可從數據庫檔內獲得若干資料。
這項查詢是根據用戶所提供的條件 (condition) 所作出的一項檢索。 用戶只須列明查詢的條件，而不須要實際知道有關的檢索方法。 MySQL

3 1 引言 SQL的概念 用戶先列出數據庫檔及查詢的條件。
可查問統計數項。 查詢所得的結果會以表格的形式顯示。 MySQL

4 2 基本結構 一般語法 SELECT, ALL / DISTINCT, *, AS, FROM, WHERE 比較
IN, BETWEEN, LIKE "% _" 群組 GROUP BY, HAVING, COUNT(), SUM(), AVG(), MAX(), MIN() 顯示次序 ORDER BY, ASC / DESC 邏輯運算符 AND, OR, NOT 輸出 CREATE TABLE AS … MySQL

5 2 實例：學生個人資料 Table: STUDENT 欄名 類型 欄寬 內容 id 數字 4 學生編號 name 字符 10 學生名字
欄名 類型 欄寬 內容 id 數字 4 學生編號 name 字符 10 學生名字 dob 日期 8 出生日期 sex 字符 1 性別： M/F class 字符 2 班別 hcode 字符 1 社名： R,Y,B,G dcode 字符 3 地區碼 remission 邏輯 1 學費減免 mtest 數字 2 數學測驗分數 MySQL

6 2 實例：學生個人資料 Table: STUDENT 欄 表格 橫列 MySQL

7 I 一般語法 SELECT ...... FROM ...... WHERE ......
SELECT [ALL / DISTINCT] expr1 [AS col1], expr2 [AS col2] FROM tablename WHERE condition MySQL

8 I 一般語法 SELECT [ALL / DISTINCT] expr1 [AS col1], expr2 [AS col2]
FROM tablename WHERE condition SQL 程序會從數據庫檔 tablename 選取符合條件的橫列 (row) 並以表格的格式顯示。 表達式 expr1, expr2 可以是 (1) 字段 (2) 以函數和字段組成的表達式 而 col1, col2 是表達式 expr1, expr2 在輸出結果的表格內的欄名。 MySQL

9 I 一般語法 SELECT [ALL / DISTINCT] expr1 [AS col1], expr2 [AS col2]
FROM tablename WHERE condition 選項 DISTINCT 會把重覆出現的橫列刪去(即只顯示一次)，而選項 ALL 則會把所有重覆的保留。 條件 condition 可以是 (1) 等式或不等式 (2) 字符串的比較 使用邏輯運算符 AND, OR, NOT MySQL

10 I 一般語法 例 1 求出所有學生的資料。 SELECT * FROM student 結果 MySQL

11 I 一般語法 例 2 求出 1A 班學生的名字和社名。 SELECT name, hcode, class FROM student
WHERE class="1A" 逐一檢查 Class 1A 1B : Class 1A 1B :   class="1A"    MySQL

12 I 一般語法 選擇三欄   結果 Class 1A 1B : Class 1A : hcode name Peter Mary
Johnny Luke Bobby R Y G B MySQL

13 I 一般語法 例 3 求出紅社社員的居住地區。 SELECT DISTINCT dcode FROM student
例 3 求出紅社社員的居住地區。 SELECT DISTINCT dcode FROM student WHERE hcode="R" 結果 MySQL

14 I 一般語法 「1B 女生」的條件: 1) class = "1B" 2) sex = "F" 3) 符合以上兩項條件 (AND運算符)
3) 符合以上兩項條件 (AND運算符) MySQL

15 I 一般語法 例 4 求出1B班女生的名字和年齡。 SELECT name, (2010-year(dob)) AS age
FROM student WHERE class="1B" AND sex="F" 結果 MySQL

16 I 一般語法 SELECT name, id, class FROM student 例 5 求出1A班沒有學費減免的學生的名字和編號。
WHERE class="1A" AND remission!=1 結果 MySQL

17 II 比較 expr IN ( value1, value2, value3) expr BETWEEN value1 AND value2
expr LIKE "%_" MySQL

18 II 比較 SELECT name, class, dayofweek(dob) AS bdate FROM student
例 6 求出所有出生於星期三或星期六的學生。 SELECT name, class, dayofweek(dob) AS bdate FROM student WHERE bdate IN (4,7) 結果 MySQL

19 II 比較 例 7 求出所有不是在一月、三月、六月或九月出生的學生。
例 7 求出所有不是在一月、三月、六月或九月出生的學生。 SELECT name, class, dob FROM student WHERE MONTH(dob) NOT IN (1,3,6,9) 結果 MySQL

20 II 比較 例 8 求出1A班的學生名字，其數學測驗分數 界乎於80至90分之間。
SELECT name, mtest FROM student WHERE class="1A" AND mtest BETWEEN 80 AND 90 結果 MySQL

21 II 比較 例 9 求出所有學生其名字是以"T"為起首。 SELECT name, class FROM student
WHERE name LIKE "T%" 結果 MySQL

22 II 比較 例10 求出所有紅社社員其名字的 第二個字母是"a"。
例10 求出所有紅社社員其名字的 第二個字母是"a"。 SELECT name, class, hcode FROM student WHERE name LIKE "_a%" AND hcode= "R" 結果 MySQL

23 III 群組 SELECT FROM WHERE condition GROUP BY groupexpr [HAVING requirement] 群組函數： COUNT( ), SUM( ), AVG( ), MAX( ), MIN( ) – GROUP BY groupexpr 列出群組組成所依照的表達式。一般都是數據庫檔的一欄。 – WHERE condition 列出個別橫列所須符合的條件，而 HAVING requirement 則列出個別群組須符合的條件。 MySQL

24 III 1A 1B 1C 1A 1B 1C 群組 group by 例11 求每班的人數。 COUNT( ) 數算1A班的人數
Student class 1A 1B 1C 1A 1B 1C 1A COUNT( ) 數算1A班的人數 COUNT( ) 1B 數算1B班的人數 COUNT( ) 1C 數算1C班的人數 MySQL

25 III 群組 例11 求出每班的人數。 SELECT class, COUNT(*) FROM student GROUP BY class
例11 求出每班的人數。 SELECT class, COUNT(*) FROM student GROUP BY class 結果 MySQL

26 III 1A 1B 1C 群組 AVG( ) 例12 求每班的數學測驗 平均分。 求1A班的平均分 求1B班的平均分 求1C班的平均分
Student class 1A 1B 1C 1A 1B 1C AVG( ) 求1A班的平均分 求1B班的平均分 求1C班的平均分 MySQL

27 III 群組 例12 求出每班的數學測驗平均分。 SELECT class, AVG(mtest) FROM student
例12 求出每班的數學測驗平均分。 SELECT class, AVG(mtest) FROM student GROUP BY class 結果 MySQL

28 III 群組 例13 求出每一居住地區的女生數目。 SELECT dcode, COUNT(*) FROM student
例13 求出每一居住地區的女生數目。 SELECT dcode, COUNT(*) FROM student WHERE sex="F" GROUP BY dcode 結果 MySQL

29 III 群組 例14 求出每一區中一學生數學測驗的 最高分及最低分。
例14 求出每一區中一學生數學測驗的 最高分及最低分。 SELECT MAX(mtest), MIN(mtest), dcode FROM student WHERE class LIKE "1_" GROUP BY dcode 結果 MySQL

30 III 群組 男生： sex="M" 最少三人： COUNT(*) >= 3
例15 列出每班男生數學測驗的平均分，但男生人數不及三人的班則不計算在內。 個別條件 男生： sex="M" 群組條件 最少三人： COUNT(*) >= 3 MySQL

31 III 群組 例15 列出每班男生數學測驗的平均分， 但男生人數不及三人的班則不計算在內。 個別條件 群組條件
例15 列出每班男生數學測驗的平均分， 但男生人數不及三人的班則不計算在內。 個別條件 SELECT AVG(mtest), class FROM student WHERE sex="M" GROUP BY class HAVING COUNT(*) >= 3 群組條件 結果 MySQL

32 IV 顯示次序 SELECT ...... FROM ...... WHERE ...... GROUP BY .....
ORDER BY colname ASC / DESC MySQL

33 IV 顯示次序 例16 列出1A班男生的名字，並按名字序顯示。 SELECT name, id FROM student
WHERE sex="M" AND class="1A" ORDER BY name 結果 ORDER BY dcode MySQL

34 IV 顯示次序 例17 列出2A班的學生資料，並按居住地區序顯示。
SELECT name, id, class, dcode FROM student WHERE class="2A" ORDER BY dcode 結果 MySQL

35 IV 顯示次序 例18 求出每區居住學生人數，並按降冪顯示。
例18 求出每區居住學生人數，並按降冪顯示。 SELECT COUNT(*) AS cnt, dcode FROM student GROUP BY dcode ORDER BY cnt DESC 結果 MySQL

36 IV 顯示次序 例19 列出每社的男社員名字並按班別序顯示。 (即社和班的兩層次序)
例19 列出每社的男社員名字並按班別序顯示。 (即社和班的兩層次序) SELECT name, class, hcode FROM student WHERE sex="M" ORDER BY hcode, class MySQL

37 IV 顯示次序 結果 按 class Blue House 按 hcode Green House : MySQL

38 V 輸出 例20 按學生名字的降冪，列出學生的所有資料，並把結果貯存成數據庫檔 NAME。
CREATE TABLE name AS SELECT * FROM student ORDER BY name DESC 結果 MySQL

39 V 輸出 例21 按社員的班別、性別及名字的次序， 把紅社社員的資料列印出來。
例21 按社員的班別、性別及名字的次序， 把紅社社員的資料列印出來。 SELECT class, name, sex FROM student WHERE hcode="R" ORDER BY class, sex DESC, name 結果 MySQL

40 3 數據庫聯合、相交及差分 A 和 B 的聯合 (AB) A B union 檢取屬於 A 或 B 的所有橫列。 MySQL

41 3 數據庫聯合、相交及差分 A 和 B 的相交 (AB) A B intersection 檢取 A 和 B 所共通的橫列。 MySQL

42 3 數據庫聯合、相交及差分 A 和 B 的相交 (A–B) A B 檢取只屬於 A 而不屬於 B 的橫列。 (即從 A 把 B 的部分排出)
difference 檢取只屬於 A 而不屬於 B 的橫列。 (即從 A 把 B 的部分排出) MySQL

43 3 實例：橋牌會和棋藝會 考慮學校的橋牌會和棋藝會的會員， 他們的資料分別貯存於同一結構的 數據庫檔內：
Tables: BRIDGE / CHESS 欄名 類型 欄寬 內容 id 數字 4 學生編號 name 字符 10 學生名字 sex 字符 1 性別： M/F class 字符 2 班別 MySQL

44 3 數據庫聯合、相交及差分 MySQL

45 3 數據庫聯合、相交及差分 例22 本校計劃舉行一次棋橋活動。 列出兩會會員的名單。(即兩會的聯合) 聯合
SELECT FROM WHERE UNION 聯合 例22 本校計劃舉行一次棋橋活動。 列出兩會會員的名單。(即兩會的聯合) CREATE TABLE party AS SELECT * FROM bridge UNION SELECT * FROM chess ORDER BY class, name 結果 MySQL

46 3 數據庫聯合、相交及差分 例23 列印兩會的共同會員。(即兩會的相交) SELECT * FROM bridge WHERE id IN
SELECT FROM table1 WHERE col IN ( SELECT col FROM table2 ) 相交 例23 列印兩會的共同會員。(即兩會的相交) 結果 SELECT * FROM bridge WHERE id IN ( SELECT id FROM chess ) MySQL

47 3 數據庫聯合、相交及差分 例24 求出只參加了橋牌會的名單。 (即兩會之差分) CREATE TABLE diff AS
SELECT FROM table1 WHERE col NOT IN ( SELECT col FROM table2 ) 差分 例24 求出只參加了橋牌會的名單。 (即兩會之差分) 結果 CREATE TABLE diff AS SELECT * FROM bridge WHERE id NOT IN ( SELECT id FROM chess ) MySQL

48 4 多個數據庫 當所查詢的資料貯放於兩個數據 庫檔時，就須使用接合 (join)。
接合的作用是把一個數據庫檔內 的一個橫列與另一個數據庫檔內 的橫列連合起來，從而把所有不 同的組合列出來。 MySQL

49 4 自然接合 在接合中加上一項接合條件，要求兩檔的共通欄(common column)的值是相同，這稱為自然接合。
這目的是要把這兩檔的相關資料連合起來，變成一個合一的大表格，再從這表格中執行查詢工作。 MySQL

50 4 多個數據庫 接合 T1 T2 T3 Peter Mary 9801 9802 9803 John id name T1.id
T1.name T2.id T2.addr 9801 Peter 9801 MongKok 9801 Peter 9802 Yaumetei 9802 Mary 9801 MongKok 9802 Mary Yaumetei T2 9801 9802 MongKok Yaumetei id addr 9803 John 9801 9802 MongKok Yaumetei 接合 MySQL

51 4 多個數據庫 自然接合 相同的 id T4 T3 9801 Peter MongKok 9802 Yaumetei Mary 9803
John T3 T1.id T1.name T2.id T2.addr id T1.name T2.addr T4 9801 Peter MongKok 9802 Mary Yaumetei 自然接合 MySQL

52 4 實例：樂器班 學校規定每個學生都須要學習一件樂器。 tables： STUDENT & MUSIC 共同欄： 學生編號 id MUSIC
欄名 類型 欄寬 內容 id 數字 4 學生編號 type 字符 10 樂器名稱 MySQL

53 4 自然接合 例25 列出所有學生的名字及所學習的樂器名稱。 9801 9801 自然 接合 9801 Student id name
例25 列出所有學生的名字及所學習的樂器名稱。 Student 9801 id name class Music id 9801 type 相同的 id 自然 接合 9801 接合的結果 id name class type MySQL

54 4 自然接合 例25 列出所有學生的名字及所學習的樂器名稱。 SELECT s.class, s.name, s.id, m.type
例25 列出所有學生的名字及所學習的樂器名稱。 SELECT s.class, s.name, s.id, m.type FROM student s, music m WHERE s.id=m.id ORDER BY class, name 結果 MySQL

55 4 自然接合 例26 求出每班學習鋼琴的學生數目。 三部分： (1) 自然接合：以 id 來接合
例26 求出每班學習鋼琴的學生數目。 三部分： (1) 自然接合：以 id 來接合 (2) 條件： m.type="Piano" (3) 群組：GROUP BY class MySQL

56 4 自然接合 例26 自然接合 條件 群組 m.type = "Piano" Group By class 接合結果 Student
Music Student 條件 m.type = "Piano" Group By class 群組 接合結果 MySQL

57 4 自然接合 例26 求每班學習鋼琴的學生數目 SELECT s.class, COUNT(*)
例26 求每班學習鋼琴的學生數目 SELECT s.class, COUNT(*) FROM student s, music m WHERE s.id=m.id AND m.type="Piano" GROUP BY class ORDER BY class 結果 MySQL

58 4 外接合 外接合是自然接合再加上 沒有配合的部分。 (1) 先求出自然接合 方法： (2) 再求出沒有配合 (3) 把兩部分聯合起來
(1) 先求出自然接合 方法： (2) 再求出沒有配合 (3) 把兩部分聯合起來 MySQL

59 4 外接合 例27 列出尚未選擇樂器的學生名字。 (即沒有相配) 9803 沒有配合 Student id name class Music
例27 列出尚未選擇樂器的學生名字。 (即沒有相配) Student 9803 id name class Music id type 沒有配合 MySQL

60 4 外接合 例27 列出尚未選擇樂器的學生名字。(即沒有相配) SELECT class, name, id FROM student
例27 列出尚未選擇樂器的學生名字。(即沒有相配) SELECT class, name, id FROM student WHERE id NOT IN ( SELECT id FROM music ) ORDER BY class, name 結果 MySQL

61 4 外接合 例28 列出一份名單，去查核所有 學生學習的樂器。 名單須包括尚未參加樂器班 的學生名字。 (即外接合) MySQL

62 4 外接合 例28 自然接合 外接合 沒有相配 MySQL

63 4 外接合 例28 自然接合 沒有配合 SELECT s.class, s.name, s.id, m.type
FROM student s, music m WHERE s.id=m.id 自然接合 UNION SELECT class, name, id, "" FROM student WHERE id NOT IN ( SELECT id FROM music ) ORDER BY 1, 2 沒有配合 MySQL

64 4 外接合 外接合 自然接合 空白 沒有相配 MySQL