翻轉電子書系列：程資料庫系統概論(含邏輯設計)
翻轉工作室：粘添壽
第四章 資料庫操作- SQL Select
『結構化查詢語言』（Structure Query Language, SQL）是一套標準化的資料庫操作命令。幾乎所有資料庫系統都可以利用此它操作與管理，也就是說，學會了 SQL 查詢命令，就可以操作任何廠家的資料庫系統，譬如 Windows SQL Server、MySQL、Access、、等等。當然啦，直接用命令來操作資料庫並不方便，因為它需要一個命令、接一個命令的輸入，再由它的輸出結果來決定下一個應該輸入哪個命令。如果我們能夠依照需求，將多個 SQL 命令整合起來完成它，並且可以重複使用，就非常完美了。為了達成此目標，我們將 SQL 命令嵌程式入語言中，利用它編寫程式就可完成了，譬如將 SQL 嵌入 PHP 語言、Visual Basic（VB.net） 內、等等，這就是所謂『資料庫程式設計』了。
如何學習資料庫系統，許多書都由介紹資料庫架構開始，接著介紹如何建構資料庫，最後才了了草草的介紹如何操作資料庫。前面兩個階段已打垮學生的信心，最後再介紹如何操作已沒甚麼意思了。我們剛好相反，建構一個很完整的資料庫範例，學生利用 SQL 命令操作資料庫，認識資料庫的功能何在，讓學生了解為何要建立資料庫，並產生興趣。
SQL 大略上可區分為：DDL、DML、DRL、TCL、以及 DCL 等五大部分，如圖 4-1 所示，以下分別介紹之：
『資料定義語言』（Data Definition Language, DDL）：提供可產生、變更、刪除資料庫或資料表之命令，如 Create、Alter、Drop、Truncate、Comment、以及 Rename等命令。
『資料處理語言』（Data Manipulation Language, DML）：提供可以處理資料表之命令，譬如：Select、Insert、Update、Delete等。
『資料控制語言』（Data Control Language, DCL）：提供管理資料庫或資料表授權使用之命令，如：Grant、Revoke 等。
『交易控制語言』（Transaction Control Language）：多人使用環境的交易控制命令，如：Commit、Savepoint、Rollback、以及 Settransaction 等。
圖 4-1 SQL 語言總類
前兩者 DDL 與 DML 本課程將會介紹到，其它部分等待爾後有其它進階課程再介紹。為了讓同學快速認識資料庫系統，我們還是由 DML 開始，而且同學如能善加利用 SQL DML 工具，就能將很枯燥無味的『資料』，產生變化萬千的『訊息』或『資訊』。
從關聯式資料庫而言，SQL 命令即是操作資料庫內的資料表，也許一次操作一個、兩個、或多個資料表，但處理結果的輸出也是資料表，但欄位數目與資料筆數會隨著查詢結果而改變，如下圖所示：
圖4-2 SQL 語言處理模式
|
命 令 |
格 式 |
命 令 |
格 式 |
|
|
Select |
Select "欄位" From "表格名"; |
Distinct |
Select Distinct "欄位" From "表格名"; |
|
|
Where |
Select "欄位" From "表格名" Where "condition"; |
|||
|
And/OR |
Select "欄位" From "表格名" Where "簡單條件" {[And|Or] "簡單條件"}+ ; |
|||
|
In |
SELECT "欄位" |
|||
|
Between |
SELECT "欄位" |
|||
|
Like |
SELECT "欄位" |
|||
|
Order By |
SELECT "欄位" |
|||
|
Count |
SELECT COUNT("欄位") |
|||
|
Group by |
SELECT "欄位1",
SUM("欄位2") |
|||
|
Having |
SELECT "欄位1",
SUM("欄位2") |
|||
|
Create |
CREATE TABLE "表格名" |
|||
|
Insert |
INSERT INTO "表格名"
("欄位1",
"欄位2",
...) |
|||
|
Drop |
DROP TABLE "表格名"; |
Delete |
DELETE FROM "表格名" |
|
|
Truncate |
TRUNCATE TABLE "表格名"; |
|||
|
Update |
UPDATE "表格名" |
(Not) Like |
*、_(底線)、%、[ABC]、Pattern |
|
|
運算 |
+、-、*、/、% |
|||
|
|
|
|||
|
比較 |
=、 <> 、 < 、> 、 >=、between |
|
|
|
|
聚合 |
Count(欄位)、Ave、Sum、Max、Min |
接下來，所有程式範例皆在 course_db 資料庫操作，吾人開啟該資料庫之後，直接利用 SQL 命令來查詢該資料內容，擷取所要的訊息。
(A) 開啟瀏覽器
輸入 URL：localhost，再點選 Database Manager 進入資料庫系統，如下：
(B) 登入 MySQL 資料庫系統
(C) 選擇資料庫
吾人選擇 course_db 資料庫，共有 7 個資料表，如下：
4-4 Select 查詢命令
Select 命令是用來擷取或查詢資料表的內容。擷取表示由取出表格某些欄位內容（無 Where 條件判斷），查詢表示取出某些滿足條件的資料表內容（需 Where 條件判斷），當然也有擷取與查詢混合使用。Select 格式如下：
|
SELECT [* | DISTINCT | <欄位串列>] FROM 資料表名稱 [WHERE <條件式> [GROUP BY <群組欄位>] [HAVING <群組條件>] [ORDER BY <欄位>[SAC |DESC]] |
比較通用的語法是：
|
SELECT 欄位1, 欄位2,, FROM 資料表名稱 WHERE <條件式>; |
注意：SQL 語法是用 ; 來表式語句的結束。
圖 4-3 select 命令功能
4-4 簡單擷取表格
(A) 程式功能：Ex4_1
請列印出所有學生的的所有資料。
(B) 系統分析：
全校學生資料都儲存於『students』資料表內，只要將它內容全部印出來即可。
(C) 程式範例：
|
SELECT * FROM students; |
其中 * 號表示所有欄位的意思。此命令表示由 students 資料表中擷取所有的欄位資料。
(D) 系統操作：
(1) 點選 SQL 選單：
(2) 輸入 SQL 命令：
(3) 顯示查詢結果：
由查詢結果可以看出，學校登錄每一位學生的資料有：學號(student_ID)、姓名(name)、班級代碼(class_ID)、性別(sex)、電話(tel)、電子郵件(mailbox)與地址(address)等資料。每項資料由一個欄位來儲存。
(A) 程式功能：Ex4_2
請擷取所有學生的學號、姓名與電話。
(B) 系統分析：
全校所有學生資料都儲存 students 資料表內，但僅要求擷取每位學生的學號(student_ID)、姓名(name) 與電話(tel)，因此僅擷取 students 資料表內某些欄位，如下圖所示：
圖 4-5 擷取部分欄位
(C) 程式範例：
|
SELECT student_D, name, tel FROM students; |
該命令的功能是：由 students 資料表內擷取 student_ID、name 與 tel 等欄位的資料。
(D) 執行結果
4-5 Select/Where查詢命令
由資料表中查詢滿足某些條件的資料，則透過 Where 命令來過濾，其語法如下：
|
SELECT [* | <欄位串列>] FROM 資料表名稱 WHERE <條件式> |
條件判斷可以取出資料表內滿足條件的紀錄。其中 Where 條件式可以是：
等於（=）：如，性別 = ‘女’。
大於（>）：如，分數 < 60。
不等於（<>）、大於並等於（>=）、小於並等於（=<）或 In (集合)。
(Not) Between A and B、Is (Not)Null、
圖 4-6 Select/where 命令功能
(A)程式功能：Ex4_3
請列印出全校所有女學生的所有資料。
(B)系統分析：
全校學生都儲存於『students』資料表內，擷取時必須指定是女性，條件是： sex = ‘女’。
(C)程式範例： Ex4_3
|
SELECT * FROM students Where sex = ‘女’; |
(D) 執行結果：
(A)程式功能：Ex4_4
請列印出全校女學生的姓名、電話與地址。
(B)系統分析：
查詢全校女同學資料的過濾條件是：sex =’女’，但只要擷取某些欄位即可，如圖 4-4 擷取某一表格內的部分記錄中的某些欄位。
圖 4-7 擷取部分資料的某些欄位
(C)程式範例： Ex3_4：
由『students』表格查詢程式如下：
|
SELECT name, tel, address FROM students Where sex = ‘女’; |
(D) 執行結果：
(A)程式功能：Ex4_5
請列印出某一班級(class_ID = 400111) 學生的學號(student_ID)、姓名(name)、電話(tel)、地址(address)，並依照學號(student_ID) 由小到大順序印出。
(B)系統分析：
由 students 表格內查詢 400111 班的同學(where class_ID = ‘400111’)，但僅擷取 student_ID、name、tel 與 address 欄位，但輸出需要依照 student_ID 順序(order by student_ID)。
(C)程式範例：
|
Select student_ID, name, tel, address From students Where class_ID = ‘400111’ Order by student_ID; |
(D)執行結果：
自我挑戰：
請思考如何依照 student_ID 由大到小順序印出，如下：
4-6自我挑戰：簡單擷取
(A)程式功能：PM4_1
請印出所有教師的資料。
(B)系統提示：
全校老師資料都儲存於 teachers 資料表內，只要顯示該表格內容即可。
程式功能：PM4_2
請印出資管系(系別代碼 dep_ID = ‘11’)教師的姓名(name)、電話(tel) 與郵件地址(mailbox)。
系統提示：
由 teachers 資料表中過濾(where Dep_ID = ‘11’)條件，即得到資管系老師資料，但僅擷取 name、tel 與 mailbox 欄位即可。
(A)程式功能：PM4_3
請印出各系系主任的姓名(name)、電話(tel) 與地址(address)。
(B)系統提示：
由 teachers 擷取資料需要過濾一個條件，(where title = ‘系主任’)，僅擷取name、tel 與 address 欄位即可。
(A)程式功能：PM4_4
請印出資管系主任的教師代碼(teacher_ID)、姓名(name)、電話(tel) 與地址(address)。
(B)系統提示：
由 teachers 擷取資料需要過濾兩個條件，(where title = ‘系主任’ and Dep_ID = ‘11’)，僅擷取 teacher_ID、name、tel 與 address 欄位即可。
4-7修飾輸出敘述
(A)程式功能：Ex4_6
請列印出『態度積極』、『提升專業能力』、『明天會更好』等字樣。
(B)系統分析：
在 select 語句中直接給予字串，系統會按照順序印出。
(C)程式範例： Ex4-6：
由『students』表格查詢程式如下：
|
Select “態度積極”, “提升專業能力”, “明天會更好”; |
(D) 執行結果：
(A)程式功能：Ex4_7
請查詢粘添壽老師的電話、地址、與電話，輸出格式如下：
(B)系統分析：
在 select 語句中插入字串即可，但 select 由表格查詢後的結果也表格格式，因此插入的字串也是欄位。
(C)程式範例： Ex4_7：
由『students』表格查詢程式如下：
|
Select "姓名=", name, "地址=", "電話=", tel From teachers Where name = "粘添壽"; |
(D)自我挑戰：
上述範例輸出增加了 3 個字串欄位，是否可以直接變更欄位名稱則比較簡潔。如下：(備註：變更輸出欄位名稱，如 name as “姓名”, address as “地址”, tel as “電話”)
(A)程式功能：PM4_5
請查詢資管系(Dep = ‘11’)教師名冊，列印格式如下：
(B)系統提示：
只要將擷取欄位以字串取代即可(如：name as “姓名)。
4-8-1 常用數學函數
吾人可將查詢結果經過某一數學函數計算後，再輸出其計算結果，常用函數如下：
|
AVG() |
計算平均值 |
|
COUNT() |
計算資料筆數 |
|
MAX() |
求最大值 |
|
MIN() |
求最小值 |
|
SUM() |
求總和 |
4-8-2範例研討：列印修課成績
(A)程式功能：Ex4_8
粘添壽老師希望查詢他所開的資料庫管理系統(open_course_ID = ‘1’)，全班學生修課成績，如下：(部分資料)
(B)系統分析：
全校學生選課與成績都登錄在 select_course 資料表內，吾人可利用 (select * from select_course;) 查詢所有資料，如下：(部分資料)
其中，student_ID 是學號，表示學生選修了這門課，這門課的代碼是 open_course_ID，它詳細資料登錄於 open_course 資料表內，會登錄哪一位老師開的哪一門課。另外。Normal、midTerm、finalTerm 登錄各項成績，學期成績則登錄於 final 欄位內。
目前暫時知道粘添壽老師開的資料庫系統的開課代碼(open_course_ID = ‘1’)，則可以查出該班有哪幾位學生修課(student_ID) 與學期成績(final)。
(C)程式範例： Ex4_8
由『select_course』表格查詢程式如下：
|
Select student_ID, final From select_course Where open_course_ID = ‘1’; |
4-8-2範例研討：查詢修課人數
(A)程式功能：Ex4_9
粘添壽老師希望查詢他所開的資料庫管理系統(open_course_ID = ‘1’)，修課人數多少，如下：
(B)系統分析：
如同 Ex4_8，查詢出來修課學生之後，再利用 count(*) 計算其人數。
(C)程式範例： Ex4_9：
由『select_course』表格查詢程式如下：
|
Select count(*) as “修課人數” From select_course Where open_course_ID = ‘1’; |
4-8-3自我挑戰：查詢平均分數
(A)程式功能：PM4_6
粘添壽老師希望查詢他所開的資料庫管理系統(open_course_ID = ‘1’)，該班平均分數多寡，如下：
(B)系統提示：
如同 Ex4_9 查詢出該班成績之後，再利用 avg(final) 計算其平均分數，又將該欄位名稱改為(avg(final) as “平均分數”)。
4-8-4自我挑戰：查詢最高與最低分數
程式功能：PM4_7
粘添壽老師希望查詢他所開的資料庫管理系統(open_course_ID = ‘1’)，該班成績最高與最低分數，如下：
系統提示：
如同 Ex4_9 查詢出該班成績之後，再利用 max(final) 與 min(final) 分別找出最高與最低分數，又將該欄位名稱改為(max(final) as “最高分數”)。
4-9-1範例研討：查詢住在高雄市教師
(A)程式功能：Ex4_10
請查詢地址在高雄市老師的姓名(name)、電話(tel) 與地址(address)，結果如下：
(B)系統分析：
由教師資料表(teachers) 中查詢地址有類似 “高雄市” 名稱。
(C)程式範例： Ex4_10
其中 % 表示任何字元。
|
Select name as "姓名", tel as "電話", address as "地址" From teachers Where address Like '高雄市%'; |
4-9-2範例研討：查詢住在三民區學生
(A) 程式功能：Ex4_11
請查詢地址在高雄市三民區學生的姓名(name)、電話(tel) 與地址(address)，結果如下：
(B)系統分析：
由學生資料表(teachers) 中查詢地址有類似 “高雄市” 與 “三民區”字樣者。
(C)程式範例：
其中 % 表示任何字元。
|
Select name as "姓名", tel as "電話", address as "地址" From students Where (address Like ‘高雄市%’) and (address Like ‘%三民區%’); |
將查詢出來結果將某一欄位結果成一群組 (group)，並可以利用 having 語句附加條件。
4-10-1範例研討：查詢教師開課數目
(A) 程式功能：Ex4_12
請查詢全校每一位老師開課數目，結果如下：
(B)系統分析：
全校教師開課課程皆登錄於 open_course 資料表內，可利用 (select * from open_course;) 查詢出所有教師開課課程，但我們可利用 order by 將同一位教師成一個群組，再利用 count(*) 計算每一群組的數目，如下圖所示。
(C)程式範例：
|
Select teacher_ID as "教師編號", count(*) as "開課數目" From open_course group by teacher_ID; |
4-10-2範例研討：查詢開課超過 3 門老師
程式功能：Ex4_13
請查詢開課超過(含) 3 門課的老師代碼與開課數，結果如下：
系統分析：
同上題，但僅列印出超出(含) 含 3 門課老師(Having count(*) >=3)。
程式範例：
|
Select teacher_ID as "教師編號", count(*) as "開課數目" From open_course group by teacher_ID Having count(*) >= 3; |
4-10-3自我挑戰：查詢班級開課數
程式功能：PM4_8
請查詢這學期全校各班級各開了幾門課，查詢結果如下：
系統提示：
全校這學期開課狀態皆登錄於 open_course 資料表內，每一紀錄包含 open_course_ID(開課代碼)、course_ID(課程代碼)、class_ID(班級代碼)、教師編號(teacher_ID)，我們只要查詢此資料表，並以 class_ID 為群組，即可計算出每一班級開了幾門課。
4-10-4自我挑戰：查詢修課人數少於 5 人的課程
程式功能：PM4_9
請查詢修課人數少於 5 人的課程，查詢結果如下(以開課代碼 open_course_ID 表示)：
系統提示：
全校學生選課狀況登錄於 select_course 表內，只要查詢該表格，以 open_course_ID 為群組(count(*))，再過濾輸出少於 5 筆記錄即是，select_course 資料表內容如下：
4-11綜合習題
(1)請印出學分數大於 2 的課程名稱與學分數，提示由 all_course 表查詢，如下：
(2)某位學生想知道資管系(Dep_ID=11)全學年規劃了哪些課程，提示由 all_course 查詢，如上圖。
(3)請列印出住在高雄市學生的姓名、電話、出生日期，提示由 students 查詢，如下：
(4)請印出住在台南市老師的姓名、電話、以及地址，提示：由 teachers 表查詢，如下：
(5)請查詢出學號 student_ID = 90211305同學這學期修讀了哪些課程(僅印出課程編號)，提示：由 select_course 表查詢，如下：
(6)請查詢出粘添壽老師（教師代碼=0108）這學期開了哪些課程(僅印出編號)，由 open_course 表查詢，如下：
(7)請查詢日間部資管系四年甲班(班級代碼：400111)學生名冊，請依學號、姓名、電話號碼、地址之順序印出，提示：由 students 表查詢。
(8)請列印出進班級代碼class_ID = 902113 班級的女同學名冊。請依學號、姓名、電話號碼、地址之順序印出，提示：由 students 表查詢。
(8)請查詢 100 年度入學有哪幾個班，請印出班級名稱(與系別代碼（由班級資料表查出），提示：由 classes 表查詢，如下：
(9)請查詢本校與『程式』相關課程有哪些，請印出課程名稱、系別代碼與學分數，提示：由 all_course 表，並利用 Where course_name like ‘%程式%’，如下：
(10)請查詢本校目前 3 年級有哪些班，請列出班級名稱(class_name)，提示：由 classes 表查詢。
(11)請查詢出進班級代碼 class_ID = 902113班級女同學中姓『陳』的學號、姓名、電話與地址。
(12)請查詢班級代碼 class_ID = 400111 班級中男同學名字是『良昆』的學號、姓名與電話。
(13)請查詢出住在『高雄市』地區的學生有多少位。