結構化查詢語言

1. 結構化查詢語言 SQL

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

3. SQL的概念 1 引言 • 用戶先列出數據庫檔及查詢的條件。 • SQL 程序便會在這數據庫檔內檢查每筆記錄是否符合這項條件，並把有關的資料顯示出來。這個過程稱為檢索。 • 可查問統計數項。 • 查詢所得的結果會以表格的形式顯示。

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

5. 2 基本結構

6. 2 實例：學生個人資料 STUDENT. DBF • 欄名 類型 欄寬內容 • 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 [AScol1], expr2 [AScol2] ; FROMtablenameWHEREcondition

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

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

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

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

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

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

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

16. I 一般語法 例 4 求出1B班女生的名字和年齡。 「1B 女生」的條件: 1) class = "1B" 2) sex = "F" 3) 符合以上兩項條件 (AND運算符)

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

18. I 一般語法 例 4 求出1B班女生的名字和年齡。 使用以下函數: 求日數: 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 求出所有出生於星期三或星期六的學生。 SELECT name, class, CDOW(dob) AS bdate ; FROM student ; WHERE DOW(dob) IN (4,7)

23. II 結果 比較 例 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列出個別橫列所須符合的條件，而 HAVINGrequirement則列出個別群組須符合的條件。

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

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

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

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

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

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

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

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

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

37. III 結果 個別條件 群組條件 群組 例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 結果 ORDER BY dcode 顯示次序 例16列出1A班男生的名字，並按名字序顯示。 SELECT name, id FROM student ; WHERE sex="M" AND class="1A" ORDER BY name

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

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

42. IV 顯示次序 例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。 SELECT * FROM student ; ORDER BY name DESC INTO TABLE name.dbf

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

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

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

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

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