1 結構化查詢語言 SQL是什麼? 當用戶發出一項查詢Query，便可從數據庫檔(table)內獲得若干資料。

Presentation on theme: "1 結構化查詢語言 SQL是什麼? 當用戶發出一項查詢Query，便可從數據庫檔(table)內獲得若干資料。"— Presentation transcript:

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

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

3 2 基本結構 一般語法 SELECT, 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 SELECT ... 聯合 UNION 建立表格 CREATE TABLE … (…) 插入紀錄 INSERT INTO … VALUES (…) 刪除紀錄 DELETE … FROM … WHERE … 刪除表格 DROP TABLE …

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

5 5 CREATE TABLE student ( id CHAR(4) NOT NULL, name VARCHAR(20),
dob DATE, gender CHAR(1), class CHAR(2), house CHAR(1), district CHAR(3), remission BINARY(1), PRIMARY KEY (id) ); CREATE TABLE MathTest( id int(4), mtest int(3), PRIMARY KEY (id) ); 5

6 2 實例：學生個人資料 STUDENT 橫列 row 欄 field 資料表格table id name dob gender class
house district remission mtest 9801 Peter M 1A R SSP 68 9802 Mary F Y HHM 76 9803 Johnny G 1 38 9804 Wendy 1B B YMT 92 9805 Tobe 60 9806 Kitty 58 9807 Eric 1C MKK 28 9808 Patty 橫列 row 欄 field 資料表格table

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

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

9 I 一般語法 例 1 列出所有學生的資料。 SELECT * FROM student id name dob gender class
例 1 列出所有學生的資料。 SELECT * FROM student id name dob gender class house district remission mtest 9801 Peter M 1A R SSP 68 9802 Mary F Y HHM 76 9803 Johnny G 1 38 9804 Wendy 1B B YMT 92 9805 Tobe 60 9806 Kitty 58 9807 Eric 1C MKK 28 9808 Patty

10 I 一般語法 例 2 列出 1A 班學生的名字和社名。 SELECT name, house, class FROM student
WHERE class="1A" name house class Peter R 1A Mary Y 1A Johnny G 1A Luke G 1A Bobby B 1A Aaron R 1A : : :

11 I 一般語法 例 3 列出紅社社員的居住地區。 例 4 列出1B班女生的名字和年齡。 district HHM KWC MKK
例 3 列出紅社社員的居住地區。 district HHM KWC MKK SSP TST YMT : SELECT DISTINCT district FROM student WHERE house="R" 例 4 列出1B班女生的名字和年齡。 SELECT name, dob, year(curdate()) -year(dob) as age FROM student WHERE class="1B"

12 I II 一般語法 比較 例 5 列出1A班沒有學費減免的學生的名字和編號。
SELECT name, id, class FROM student WHERE class="1A" AND NOT remission II 比較 expr IN ( value1, value2, value3) expr BETWEEN value1 AND value2 expr LIKE "%_"

13 II 比較 例 6 列出所有出生於星期三或星期六的學生。 例 7 列出所有非在四月、六月、九月或十一月出生的學生。
星期日 dayofweek(dob)=1 : 星期六 dayofweek(dob)=7 例 6 列出所有出生於星期三或星期六的學生。 SELECT name, class, dayofweek(dob) FROM student WHERE dayofweek(dob)=4 OR dayofweek(dob)=7 例 7 列出所有非在四月、六月、九月或十一月出生的學生。 SELECT name, class, month(dob) FROM student WHERE month(dob) NOT IN (4,6,9,11)

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

15 II 比較 例 9 列出所有學生其名字是以"T"為起首。 例10 列出所有紅社社員其名字的第二個字母是"a"
SELECT name, class FROM student WHERE name LIKE "T%" 例10 列出所有紅社社員其名字的第二個字母是"a" SELECT name, class, house FROM student WHERE name LIKE "_a%" AND house="R"

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

17 III 群組GROUP BY 例11 列出每一班的人數。 例12 列出每一班的數學測驗平均分。 SELECT class, count(*)
例11 列出每一班的人數。 SELECT class, count(*) FROM student GROUP BY class 例12 列出每一班的數學測驗平均分。 class avg(mtest) 1A 85.9 1B 70.3 1C 37.9 2A 89.4 2B 53.1 2C 32.7 SELECT class, avg(mtest) FROM student GROUP BY class

18 III 群組 例13 列出每一居住地區的女生數目。 例14 列出每一區中一學生數學測驗 的最高分及最低分。
district count HHM 6 KWC 1 MKK 1 SSP 5 TST 4 YMT 6 群組 例13 列出每一居住地區的女生數目。 SELECT district, count(*) FROM student WHERE gender="F" GROUP BY district District min max HHM 36 92 MKK 19 91 SSP 31 91 TST 36 92 TSW 75 75 YMT 38 88 例14 列出每一區中一學生數學測驗 的最高分及最低分。 SELECT district, min(mtest), max(mtest) FROM student WHERE class LIKE "1_" GROUP BY district

19 III 群組 例15 列出每一班男生數學測驗的平均分， 但若該班男生人數少於3人，則不計算在內。
例15 列出每一班男生數學測驗的平均分， 但若該班男生人數少於3人，則不計算在內。 SELECT class, avg(mtest) FROM student WHERE gender="M" GROUP BY class HAVING count(*)>=3 class avg(mtest) 1A 86.0 1B 77.7 1C 35.6 2A 86.5 2B 56.5 SELECT gender, if( gender = "F", 1, 0 ) FROM school; 1A … 4A SELECT class, sum( if( gender = "M", 1, 0 ) ) AS boys, sum( if( gender = "F", 1, 0 ) ) AS girls FROM school GROUP by class; M 1 … F 0

20 IV 顯示次序 例16 列出1A班男生的名字，並按名字序顯示。 SELECT ...... FROM ...... WHERE ......
GROUP BY ; ORDER BY colname ASC / DESC 例16 列出1A班男生的名字，並按名字序顯示。 name id Aaron 9812 Bobby 9811 John 9803 Luke 9810 Peter 9801 Ron 9813 SELECT name, id FROM student WHERE gender="M" AND class="1A" ORDER BY name

21 IV 顯示次序 例17 列出2A班的學生資料， 並按居住地區序顯示。 例18 列出每區居住學生的人數， 並按降冪顯示。 SELECT *
FROM student WHERE class="2A" ORDER BY district 例18 列出每區居住學生的人數， 並按降冪顯示。 SELECT district, count(*) as cnt FROM student GROUP BY district ORDER BY cnt DESC

22 IV 顯示次序 例19 列出每社的男社員名字，並按班別序顯示。 (即先排社，再排班次序) SELECT name, class, house
例19 列出每社的男社員名字，並按班別序顯示。 (即先排社，再排班次序) SELECT name, class, house FROM student WHERE gender="M" ORDER BY house, class

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

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

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

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

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

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

29 3 數據庫聯合、相交及差分 例23 列出兩會的共同會員。(即兩會的相交) SELECT ...... FROM table1 ;
WHERE col IN ( SELECT col FROM table2 ) 例23 列出兩會的共同會員。(即兩會的相交) SELECT * FROM bridge WHERE id IN ( ) SELECT id FROM chess SELECT * FROM chess, bridge WHERE chess.id = bridge.id

30 3 數據庫聯合、相交及差分 例24 列出只參加了橋牌會的名單。(即兩會之差分) 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

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

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

33 4 多個數據庫 接合Cross 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 接合Cross

34 4 多個數據庫 自然接合Natural 相同的 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 自然接合Natural

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

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

37 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 結果

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

39 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 結果

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

41 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 結果

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

43 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 ; 沒有配合

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