Transcript MySQL資料庫教學

SQL-99: 綱要定義、基本限制與查詢 (2)
國立聯合大學 資訊管理學系
陳士杰老師
▊ Outlines






Database Languages
SQL資料型態
Data Definition Language, DDL (資料定義語言)
Data Manipulation Language, DML (資料處理語言)
Data Query Language, DQL (資料查詢語言)
Data Control Language, DCL (資料控制語言)
(see Chapter 8, Chapter 9; 實作講義Ch. 5~Ch. 7)
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)
▊ Data Query Language, DQL (資料查詢語言)

建立好資料表及其所有的資料後，可以使用DQL來查詢資料。

DQL只有一個指令– Select，用以查詢資料庫的相關資料，語法如下：
SELECT <attribute list>
//指定查詢時，所欲輸出的欄位(屬性)
FROM <table list>
//指定查詢時，所會用到的表格有哪些
WHERE <condition>
//指定查詢的條件
GROUP BY <grouping attributes>
//將SELECT的結果依照某屬性欄位加以分群
HAVING <grouping condition>
//GROUP BY的限制條件，必須配合GROUP BY使用
ORDER BY <column name> ASC/DESC //依照某欄位值作遞增(ASE)或遞減(DESC)排列 (預設為ASC)

SQL查詢語言由上述六個子句所組成，只有Select和From是必要的。

執行順序：

FROM→WHERE →GROUP BY →HAVING →SELECT →ORDER BY
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)

使用說明：



SELECT * 表示選擇FROM中關聯表格的所有屬性
查詢中若沒有WHERE，表示沒有限制條件，即: 將FROM所指定之表格的
所有資料，根據SELECT所要求的欄位全部列出。
若有多個表格中的欄位名稱重覆，可改寫成 “table_name . attribute_name”
加以區分


如：假設A表格與B表格中，皆有一個名為ID的欄位，且在資料查詢時
此兩表格皆同時用上，為了怕混肴，則以 A.ID 與 B.ID 來區分。
在做資料查詢時，可將使用到的表格名稱或是欄位名稱另取別名：

“表格名稱 AS 別名” 或是 “表格名稱 別名”

Ex: Project_supp_Component as PSC 或 Project_supp_Component PSC

亦可對欄位取別名
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)
範例題組1 (一般查詢: 無條件、有條件)

一般查詢可分成無條件查詢與有條件查詢

依照slide 9的四個表格，用SQL回答下列問題：

列出個別專案的資料。(無條件)
Sol:

列出所有重量在20以上，且不為黑色的零件名稱、顏色、重量。(有條件)
Sol:
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)
SQL語法的運算子

SQL語法中可能會用到的運算子：
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)
範例題組2 (排序查詢、分組查詢、聚合函數)

在從事資料查詢時，除了可以在where子句加上所需要之限
制條件外，尚可將查詢的結果做一些加工，如：

根據某欄位，將查詢結果做排序

根據某欄位，將查詢結果做分組

根據某欄位，將查詢結果利用一些聚合函數做運算
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)

依照slide 9的四個表格，用SQL回答下列問題：

依照數量由小到大列出供應商S1所參與之專案名稱、零件名稱，以
及數量。 (排序)
Sol:
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)

由專案供應零件中，列出零件代號並統計各零件之總數量。(分組 + 聚合)
Sol:

由專案供應零件中，總數超過1000之零件的總數量並列出其代號。(聚合)
Sol:
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)
SQL語法的函數

聚合函數：

COUNT(attribute_name): 計算屬性值個數

SUM(attribute_name): 計算屬性中數值的總合

AVG(attribute_name): 計算屬性中數值的平均

MAX(attribute_name): 找出屬性中數值的最大值

MIN(attribute_name): 找出屬性中數值的最小值
(其它類型的SQL函數請參考網路講義)
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)


聚合函數中COUNT的用法：

COUNT(*): 計算有幾筆記錄

COUNT(attribute_name): 此屬性有幾筆非空值

COUNT(DISTINCT attribute_name): 此屬性有幾筆不同的非空值
例： 表格Employee共有1000筆記錄，其中有900人分別屬於10個
不同的部門，100人無部門。

SELECT COUNT(*) FROM Employee;  共 1000 筆

SELECT COUNT(Dept) FROM Employee;  共 900 筆

SELECT COUNT(Eno) FROM Employee;  共 1000 筆

SELECT COUNT(DISTINCT Dept) FROM Employee;  共 10 筆
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)

依照slide 9的四個表格，用SQL回答下列問題：

列出所有供應商 “大勝” 有供應的零件中，重量最重之重量為何。 (聚合)
Sol:

列出位於高雄的供應商數目。 (聚合)
Sol:
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)
範例題組3 (巢狀查詢，或稱子查詢)

何謂子查詢(Subquery)？

在一個 SQL 語法的條件子句中，放入另一個 SQL 語法

當我們在WHERE子句或HAVING等條件子句中，插入另一個 SQL 語句時，我們
就有一個子查詢 (Subquery) 的架構。

語法：
SELECT …
FROM <表格>
WHERE <欄位名或欄位運算式> <比較運算子>
( SELECT …
FROM <表格名>
WHERE <條件> );
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)

根據子查詢語句所回傳之記錄值狀況，區分成：


單一記錄值子查詢

此類子查詢只傳回單一記錄值 (如：李四的學生基本資料)

所有的邏輯比較運算子 (如：>，=，<，…等) 皆可使用
多記錄值子查詢

此類子查詢傳回的是一組記錄值 (如：張三、李四、王五的學生基本資料)

須使用多值比較運算子
SELECT …
FROM <表格>
WHERE <欄位名或欄位運算式> <比較運算子>
( SELECT …
FROM <表格名>
WHERE <條件> );
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)
單一記錄值子查詢

此類子查詢只傳回單一記錄值，因此所有邏輯運算子 (如
＞，=，＜ 等等) 都可以用。
邏輯運算子
意義與說明
=
相等。
!= OR <>
不等於。
<
小於。
>
大於。
>=
大於或等於。
<=
小於或等於。
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)

依照slide 9的四個表格，用SQL回答下列問題：

列出供應商 “大勝” 所供應之零件總數量
Sol：
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)
多記錄值子查詢


此類子查詢傳回不是單一值，而是某特定欄位的所有資料。這種查詢
必須用多值比較運算子與主查詢相連繫。
[Not] IN:



[Not] ALL, [Not] ANY：用來做數值的比較



比較IN之前的數值，是否和IN後面集合中任一數值相同，及判別是否在此
集合中。
NOT IN則為IN的反義
(需搭配邏輯運算子)
ALL是將某值與集合中的所有內容值做比較
ANY是指與集合中任何值做比較
[Not] EXISTS:




若子查詢有結果存在時，則列出父查詢的查詢結果 (無關子查詢)
找出存在於子查詢結果表格中的資料 (相關子查詢)
所有變數中只要有一個為true，EXISTS即成立
NOT EXISTS為EXISTS的反義詞，所有變數皆必須為FALSE，NOT EXISTS才成立
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)
注意
單一記錄子查詢所使用的邏輯運算子，和多記錄子查詢所使
用的多值比較運算子IN, ANY, ALL (EXISTS除外)，需在運算子前、
後分別指定父查詢的某特定欄位與子查詢傳回結果的某特
定欄位，且子查詢傳回欄位的個數及資料型態必須要與父查
詢匹配，才能使該運算子正常工作。
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)

依照slide 9的四個表格，用SQL回答下列問題：

列出所有有參與J2專案的供應商名稱。
Sol:
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)

列出所有沒有參與J2專案的供應商名稱。
Sol:
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)

列出所有有參與J2專案、且位於台南的供應商名稱。
Sol:
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)

列出所有有供應黑色零件的供應商代號與名稱。
Sol:
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)

列出所有有供應黑色零件的供應商代號與名稱，且重覆資料只列
出一筆。
Sol:

DISTINCT：表示相同的資料只列出一筆。
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)

列出所有沒有供
應黑色零件的供
應商代號與名稱
Sol: (第二個查詢結果重
覆資料只出現一次)
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)

當Where子句後的子查詢有結果存在 (為True) 時，列出父查詢工作之
結果 (即：所有供應商的名稱與編號)
Sol:
此查詢工作同義於下列查詢：
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)
無關子查詢 vs. 相關子查詢

相關子查詢是指WHERE 條件中的子查詢，有引用主查詢的查詢資料。反
之我們稱之無關子查詢。


前面所有的子查詢範例皆為無關子查詢
依照slide 9的四個表格，用SQL回答下列問題：

列出有支援專案 J3所需零件 的所有供應商代號與名稱。
Sol:

無關子查詢中，其父查詢與子查詢可視為兩個獨立的查詢工作，這兩
個查詢工作之結果透過比較運算子加以結合。
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)

列出所有沒有銷售黑色零件的供應商代號與名稱。
Sol 1:
Sol 2:
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)

列出所有供應零件P1的供應商名稱。
Sol 1:
Sol 2:
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)

上一查詢工作的Sol 2，若沒有加上 “DISTINCT” 會產生什麼結果？
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)
範例題組4 (其它比較運算子)
運算或函式
意義與說明
IS NULL
檢查欄位的值是否為 NULL。 當一個欄位處於沒有儲存著任何值
的狀態時，就稱為儲存著 NULL 值。
IS NOT NULL
檢查欄位的值是否不為 NULL。
BETWEEN A AND B
指定一個介於 A 與 B 之間的範圍。
IN (Value1,Value2....)
檢查是否符合列舉項目的範圍。
NOT IN
(Value1,Value2......)
檢查是否不在列舉項目的範圍。

LIKE：利用萬用字元(%及_)做相似字串的比對

百分符號 % 表示0~多個任意字元

底線符號 _ 表示單一個任意字元

例：%大_ _  在 “大” 字前可有任意個字元； 在 “大” 字後為恰有兩個字元
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)

找出所在城市為未知的供應商名稱 (假設有一個供應商 “大聯合工
坊”，其代號為 “S5”，其所在城市未知)
Sol:
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)

找出名稱的第一個
字為 ‘大’ 的供應
商之名稱與代號
Sol:
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)
▊ Data Definition Language, DDL (資料定義語言) - - (2)

DDL主要有CREATE, DROP, ALTER三個指令，並可針對以下三個物件進行操作：

資料庫 (database)

表格 (Table)

觀點 (View)
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)
何謂 View (觀點、視觀)？
父母
男/女朋友
老闆
男女
朋友
個性
姓名
工作
經驗
個性
虛擬表格
學生資料庫
成績
興趣
…
…
…
…
…
…
實際表格
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)
…
…
修習
課程
…
電話
…
住址
…
姓名
成績
男女
朋友
…
男女
朋友
修習
課程
…
…
興趣
…
電話
…
…
住址
…
…
姓名
…
…
工作
經驗
…
成績
…
修習
課程
…
姓名
建立、刪除觀點 (View)

觀點 (View):

不以實體表格的形式存在，可視為一個虛擬表格

其內容是以SELECT指令的執行結果構成，所以其定義方式正是以
SELECT為基礎

在MySQL中，

可使用SQL標準語法 (即：CREATE VIEW) 來建構 view。

利用show tables，可以看到所建立出來的view和其它的表格是放在一起
的，就像是一個真正的表格一樣，只是它是屬於虛擬表格。

正是因為它是虛擬表格，所以不能使用DROP TABLE來刪除它!! 可使用
SQL標準語法的DROP VIEW來刪除一個view。
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)

CREATE VIEW: 建立一個新的觀點

指令：

CREATE VIEW <view name> AS
SELECT…
FROM…
WHERE…
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)

例:

為名稱的第一個字為
‘大’ 的所有供應商之
名稱與代號建立View，
其View的名稱為
“Supplier_Da”。

同時，請對此View做資
料查詢。
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)

DROP VIEW: 刪除一個觀點
DROP VIEW <view name>



例: DROP VIEW Supplier_Da;
View的優點：

可提供邏輯資料獨立性 (Logical Data Independence)

隱藏不需要或具私密性的資料

同一關聯可建立多種不同的觀點，讓使用者以不同的角度看同一份
資料。
View的缺點：

觀點的更新(Update)有諸多限制，無法與關聯提供相同的功能。
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)
▊ Data Control Language, DCL (資料控制語言)

此語言主要從事資料庫的權限控管，包含Grant, Revoke, Alter
Password…等指令。

Grant：增加資料庫的權限內容。
GRANT <authority> ON <object> TO <users>


例：

GRANT Select ON EMPLOYEE(FName,LName) TO PUBLIC;

GRANT Delete ON EMPLOYEE TO Jacy;
Revoke: 取消某使用者之權限。
REVOKE <authority> ON <object> FROM <users>

例：

REVOKE Delete ON EMPLOYEE FROM Jacy;
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)
在MySQL增加資料庫使用者

在mysql中我們使用grant敘述，來設定新使用者並給予其相應之權限。
grant privileges
on what
to user identified by “密碼”

說明：


Privleges: 設定權限等級給新使用者。可分成 “使用者層級權限” 與 “管理者
層級權限”
What: 設定新使用者所能使用到的資料庫名稱，及相關之資料表格


User:設定新的使用者，包括使用者名稱與主機名稱。如：jacy@localhost


*.*: 第一個 * 可指定某一個資料庫名稱，第二個 * 可指定該資料庫之下的某一個
資料表名稱。若都是用 *，則表示可使用所有資料庫的所有資料表。
@符號之前是新使用者名稱，@之後是所在的主機
密碼: 指定使用者所用的密碼。如果新增的使用者沒有指定(identified)密碼，這
代表該使用者沒有密碼。
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)
使用者層級權限
管理者層級權限
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)
在MySQL取消使用者權限與移除使用者

要取消某個使用者的權限，可用revoke敘述。
revoke priviledges
on what
from user

例：使用revoke來取消lulu在資料庫主機localhost的所有MySQL存取權限。
revoke all on *.* from lulu@localhost

revoke敘述只移除權限而非移除使用者，該使用者的項目仍存於user資料
表中，所以使用者依舊可以連到伺服器上。

要完整的移除使用者，必須使用delete指令明確的由user資料表上，將使用
者紀錄刪除。

在MySQL資料庫系統中，在上述delete指令移除使用者後，需使用flush
privileges來更新MySQL資料庫的權限內容。
國立聯合大學 資訊管理學系
資料庫系統課程 (陳士杰)