SQL 結構化查詢語言.

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

1 SQL 結構化查詢語言

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

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

4 1 引言 在 FoxPro 如何使用 SQL 使用SQL必須先把有關的數據庫檔開啟。 用戶可使用指令視窗直接把指令輸入。
若用戶選用字符串的完全配對時，便須輸入 SET ANSI ON。

5 2 基本結構

6 2 實例：學生個人資料 STUDENT. DBF 欄名 類型 欄寬 內容 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 數學測驗分數

7 2 實例：學生個人資料 STUDENT. DBF 欄 表格 橫列

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

9 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 在輸出結果的表格內的欄名。

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

11 I 一般語法 在使用SQL之前，開啟數據庫檔： USE student 例 1 求出所有學生的資料。
例 1 求出所有學生的資料。 SELECT * FROM student 結果

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

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

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

15 I 一般語法 例 4 求出1B班女生的名字和年齡。 1B 女生 ?

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

17 I 一般語法 例 4 求出1B班女生的名字和年齡。 什麼是"年齡"?

18 I 一般語法 使用以下函數: 求日數: DATE( ) – dob 求年數: (DATE( ) – dob)/365
一位小數: ROUND(__ , 1)

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

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

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

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

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

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

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

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

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

28 III 群組 例11 求出每一班的人數。

29 1A 1B 1C 1A 1B 1C 以 Class 為群組 COUNT( ) 數算1A班的人數 COUNT( ) 數算1B班的人數
Student class 1A 1B 1C 1A 1B 1C 1A COUNT( ) 數算1A班的人數 COUNT( ) 1B 數算1B班的人數 COUNT( ) 1C 數算1C班的人數

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

31 III 群組 例12 求出每一班的數學測驗平均分。

32 1A 1B 1C AVG( ) 以 Class 為群組 求1A班的平均分 求1B班的平均分 求1C班的平均分 class 1A 1B 1C
Student class 1A 1B 1C 1A 1B 1C AVG( ) 求1A班的平均分 求1B班的平均分 求1C班的平均分

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

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

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

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

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

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

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

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

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

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

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

44 V 輸出

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

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

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

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

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

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

51 3 數據庫聯合、相交及差分 在使用SQL之前，開啟這兩個數據庫檔： SELECT A USE bridge SELECT B
USE chess

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

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

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

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

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

57 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 接合

58 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 自然接合

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

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

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

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

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

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

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

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

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

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

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

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

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