第七章進階的SQL 集合運算式 巢狀查詢句 JOIN的查詢句 分群彙總查詢句 SQL的VIEW 其他進階語法 IN EXISTS 權限控制 語意限制 索引 CURSOR和儲存程式 TRIGGER 黃三益2008 資料庫的核心理論與實務第四版

集合運算式 相乘 交集、聯集、差集等運算子則分別用INTERSECT、UNION，和EXCEPT SELECT mId, pNo FROM Member, Product; 交集、聯集、差集等運算子則分別用INTERSECT、UNION，和EXCEPT Q5: 找出所有瀏覽過或購買過「系統分析理論與實務」的會員之會員編號和會員姓名。 (SELECT M.mId, M.name FROM Product AS P, Browse AS B, Member AS M WHERE pName = '系統分析理論與實務' AND P.pNo = B.pNo AND B.mId = M.mId) UNION FROM Product AS P, Record AS R, Transaction AS T, Member AS M WHERE pName = '系統分析理論與實務' AND P.pNo = R.pNo AND R.tNo = T.tNo AND T.transMid = M.mId); 黃三益2008 資料庫的核心理論與實務第四版

Bag運算式 包括UNION ALL、EXCEPT ALL和INTERSECT ALL R1 UNION ALL R2：結合 R1 跟 R2 的所有記錄 (不去除重複)。 R1 EXCEPT ALL R2：若一個記錄在R1出現k1次但在R2出現K2次，則最後的結果裡該記錄出現K1−K2次。 R1 INTERSECT ALL R2：若一個記錄在R1出現k1次但在R2出現K2次，則最後的結果裡該記錄出現Min(K1, K2)次。 黃三益2008 資料庫的核心理論與實務第四版

a b c d a b c d S R R INTERSECT ALL S R UNION ALL S R EXCEPT ALL S 黃三益2008 資料庫的核心理論與實務第四版 (b)

練習7-1 請找出所有既出CD也出書的創作者。 Ans: (SELECT name FROM Product AS P, Author AS A WHERE category= 'Book' AND P.pNo = A.pNo) INTERSECT WHERE category= 'CD' AND P.pNo = A.pNo); 黃三益2008 資料庫的核心理論與實務第四版

巢狀查詢句 巢狀查詢句：在FROM子句或WHERE子句裡容納另一個查詢子句。 在FROM子句裡的查詢子句 在WHERE子句裡的查詢子句 列出「系統分析理論與實務」的作者姓名 SELECT name FROM (SELECT * FROM Product WHERE pName = '系統分析理論與實務') AS P, Author WHERE P.pNo = Author.pNo; 在WHERE子句裡的查詢子句 很強的表達能力 IN 和EXISTS（用來表達邏輯計算式裡的限制子） 黃三益2008 資料庫的核心理論與實務第四版

巢狀查詢句（IN ） 列出所有購買過「系統分析理論與實務」的會員之會員編號和會員姓名 SELECT mId, name FROM Member WHERE mId IN (SELECT transMid FROM Product AS P, Record AS R, Transaction AS T WHERE pName= '系統分析理論與實務' AND P.pNo = R.pNo AND R.tNo = T.tNo); 黃三益2008 資料庫的核心理論與實務第四版

巢狀查詢句（IN ） Q5: 找出所有瀏覽過或購買過「系統分析理論與實務」的會員之會員編號和會員姓名 SELECT mId, name FROM Member WHERE (mId IN (SELECT mId FROM Browse AS B, Product AS P WHERE pName = '系統分析理論與實務' AND P.pNo = B.pNo)) OR (mId IN (SELECT transMid FROM Product AS P, Record AS R, Transaction AS T WHERE pName = '系統分析理論與實務' AND P.pNo = R.pNo AND R.tNo = T.tNo)); 黃三益2008 資料庫的核心理論與實務第四版

巢狀查詢句（IN ） IN左邊的記錄也可包含兩個或兩個以上的屬性 Q6: 找出購物車裡含有「系統分析理論與實務」的交易之編號 SELECT tNo FROM Cart WHERE (mId, cartTime) IN (SELECT mId, cartTime FROM Order, Product WHERE Order.pNo=Product.pNo AND pName= '系統分析理論與實務'); 或是 FROM Order WHERE pNo IN (SELECT pNo FROM Product WHERE pName = '系統分析理論與實務')); 黃三益2008 資料庫的核心理論與實務第四版

巢狀查詢句（IN ） IN前可以加上NOT來表示否定 找出沒有創作者的商品編號和商品名稱 SELECT pNo, pName FROM Product WHERE pNo NOT IN (SELECT pNo FROM Author); 黃三益2008 資料庫的核心理論與實務第四版

練習7-2 用巢狀查詢句找出所有瀏覽但沒有購買過「系統分析理論與實務」的會員之會員編號和會員姓名。 Ans: SELECT mId, name FROM Member WHERE (mId IN (SELECT B.mId FROM Browse AS B, Product AS P WHERE pName = ‘系統分析理論與實務’ AND P.pNo = B.pNo)) AND (mId NOT IN (SELECT transMid FROM Product AS P, Record AS R, Transaction AS T WHERE pName = ‘系統分析理論與實務’ AND P.pNo = R.pNo AND R.tNo = T.tNo)); 黃三益2008 資料庫的核心理論與實務第四版

巢狀查詢句（IN ） 子查詢句也可以參考到上層查詢句的資料表 Q7: 找出「馬英九」所瀏覽過的商品裡，哪些有真正被他購買。 SELECT pNo FROM Browse AS B, Member AS M WHERE name = '馬英九' AND B.mId = M.mId AND pNo IN (SELECT pNo FROM Record AS R, Transaction AS T WHERE transMid = M.mId AND T.tNo = R.tNo); 黃三益2008 資料庫的核心理論與實務第四版

練習7-3 請將第五章的Q4用巢狀查詢句表達。（列出所有瀏覽過或購買過「系統分析理論與實務」的會員之會員編號和會員姓名。 ） SELECT mId, name FROM Member WHERE (mId IN (SELECT B.mId FROM Browse AS B, Product AS P WHERE pName = ‘系統分析理論與實務’ AND P.pNo = B.pNo)) OR (mId IN (SELECT transMid FROM Product AS P, Record AS R, Transaction AS T WHERE pName = ‘系統分析理論與實務’ AND P.pNo = R.pNo AND R.tNo = T.tNo)); 請將Q7用一般查詢句表達 (找出「馬英九」所瀏覽過的商品裡，哪些有真正被他購買)。 SELECT DISTINCT pNo FROM Browse AS B, Member AS M, Record AS R, Transaction AS T WHERE M.name = ‘馬英九’ AND M.mId = B.mId AND B.pNo=R.pNo AND R.tNo=T.tNo AND transMid = M.mId ; 黃三益2008 資料庫的核心理論與實務第四版

巢狀查詢句（IN ） 除了IN之外，還有其他相關的比較運算子，如 =(>,>=,<,<=) SOME (SELECT...) 。 =(>,>=,<,<=) ALL (SELECT ...) Q8：找出定價比所有書籍都高的商品： SELECT pNo, pName FROM Product WHERE unitPrice > ALL( SELECT unitPrice FROM Product  WHERE category =‘Book’); IN右邊的資料表內容也可直接寫出 SELECT DISTINCT mId FROM Browse WHERE pNo IN (‘b30999’, ‘b10234’, ‘d11222’); 黃三益2008 資料庫的核心理論與實務第四版

巢狀查詢句（EXISTS ） 列出所有購買過「系統分析理論與實務」的會員之會員編號和會員姓名 單元運算子，用來測試一個資料表是否有記錄 SELECT mId, name FROM Member WHERE EXISTS (SELECT * FROM Product, Record, Transaction WHERE pName='系統分析理論與實務' AND Product.pNo = Record.pNo AND Record.tNo = Transaction.tNo AND mId = transMid); 黃三益2008 資料庫的核心理論與實務第四版

巢狀查詢句（EXISTS ） EXISTS前可以加上NOT來表示否定。 Q10：找出所有非由購物車而來的交易的交易編號和會員編號。 SELECT tNo, transMid FROM Transaction AS T WHERE NOT EXISTS (SELECT * FROM Cart WHERE tNo = T.tNo); 黃三益2008 資料庫的核心理論與實務第四版

巢狀查詢句（EXISTS ） 可表達邏輯關係較複雜的查詢 Q11：找出購買所有「Jackey 」所創作商品的會員之會員編號和會員姓名。 假設Jackey所創作的產品所成的集合為J，一位會員（比如張三）所購買的所有產品為C，張三若符合條件則JC= J： SELECT pNo FROM Author WHERE name = ‘Jackey’; C： FROM Transaction AS T, Record AS R WHERE transMid=M.mId AND T.tNo = R.tNo; 黃三益2008 資料庫的核心理論與實務第四版

巢狀查詢句（EXISTS ） FROM Author WHERE name = ‘Jackey’) SELECT mId, name FROM Member AS M WHERE NOT EXISTS ( (SELECT pNo FROM Author WHERE name = ‘Jackey’) EXCEPT FROM Transaction AS T, Record AS R WHERE transMid=M.mId AND T.tNo = R.tNo));  黃三益2008 資料庫的核心理論與實務第四版

JOIN的查詢句 JOIN、NATURAL JOIN ，和OUTER JOIN也可以設定在FROM子句裡 或 SELECT name FROM Product JOIN Author ON Product.pNo = Author.pNo WHERE pName = ‘系統分析理論與實務’; 或 FROM Product NATURAL JOIN Author 黃三益2008 資料庫的核心理論與實務第四版

JOIN的查詢句 Q12：列出每一位會員的會員編號、姓名、生日，以及其介紹者的會員編號和姓名（如果有的話）。 本題需用LEFT OUTER JOIN SELECT M.mId AS member_id, M.name AS member_name, I.mId AS introducer_mId, I.name AS introducer_name FROM Member AS M LEFT OUTER JOIN Member AS I ON M.introducer = I.mId; 黃三益2008 資料庫的核心理論與實務第四版

JOIN的查詢句 Q13：列出每一位會員的會員編號、姓名，以及2005年所瀏覽的商品之商品編號（如果有的話）。 SELECT M.mId, name, pNo FROM Member AS M LEFT OUTER JOIN Browse AS B ON M.mId = B.mId WHERE to_char(browseTime, ‘yyyy’) = ‘2005’; SELECT M.mId, name, pNo FROM Member AS M LEFT OUTER JOIN Browse AS B ON (M.mId = B.mId AND to_char(browseTime, ‘yyyy’) = ‘2005’); 黃三益2008 資料庫的核心理論與實務第四版

練習7-4 列出所有商品的商品編號、商品名稱，以及創作者姓名（如果有的話）。 Ans: SELECT pNo, pName, A.name FROM Product AS P LEFT OUTER JOIN Author AS A ON P.pNo=A.pNo; 黃三益2008 資料庫的核心理論與實務第四版

SQL查詢的彙總函數和分群 一般式如下： SELECT <分群屬性>,<彙總函數> FROM <資料表> WHERE <記錄選取條件> GROUP BY <分群屬性> HAVING <記錄群選取條件> 黃三益2008 資料庫的核心理論與實務第四版

彙總函數 彙總函數：SUM、AVG、COUNT、MAX，和MIN Q14：列出所有商品數、平均定價、最高定價，和最低定價 不同的定價數： SELECT COUNT(*), AVG(unitPrice), MAX(unitPrice), MIN(unitPrice) FROM Product; 不同的定價數： SELECT COUNT(DISTINCT unitPrice) 黃三益2008 資料庫的核心理論與實務第四版

分群查詢句(cont.) Q15：列出每一筆交易的交易編號和交易總金額 SELECT tNo, SUM(salePrice) FROM Record GROUP BY tNo; Q16：列出每一筆上網達成的交易（即method = ‘cart’）之交易編號，和購買商品總樣數： SELECT tNo, COUNT(*) FROM Transaction NATURAL JOIN Record WHERE method = ‘cart’ 黃三益2008 資料庫的核心理論與實務第四版

分群查詢句(cont.) 彙總函數也可用在巢狀查詢句的WHERE子句中 Q17：找出有兩筆以上交易的會員之會員編號和姓名。 SELECT mId, name FROM Member WHERE (SELECT COUNT(*)           FROM Transaction            WHERE mId=transMid)>2; Q18：列出每一筆交易的交易編號、會員編號和交易總金額 SELECT tNo, transMid, SUM(salePrice) FROM Transaction NATURAL JOIN Record GROUP BY tNo, transMid; 黃三益2008 資料庫的核心理論與實務第四版

分群查詢句(cont.) 數群記錄可用HAVING子句來設定挑選條件 Q19：對於每一筆上網達成（即method = ‘cart’），且購買商品種樣數超過2的交易，列出其交易編號和購買商品種樣數。 SELECT tNo, COUNT(pNo) FROM Transaction NATURAL JOIN Record WHERE method = ‘cart’ GROUP BY tNo HAVING COUNT(pNo) > 2; ： 黃三益2008 資料庫的核心理論與實務第四版

練習7-5 對於每一台商品種樣數超過3的購物車，列出其購物時間、會員編號、會員姓名 SELECT M.mId, M.name, cartTime FROM Member AS M , Order AS O WHERE O.mId = M.mId GROUP BY M.mId, M.name, cartTime HAVING COUNT(pNo) > 3; 黃三益2008 資料庫的核心理論與實務第四版

分群查詢句(cont.) HAVING 子句上也可有子查詢句 SELECT tNo, SUM(salePrice) 列出交易金額最高的交易編號和其總交易金額 SELECT tNo, SUM(salePrice) FROM Record GROUP BY tNo HAVING SUM(salePrice) >= ALL (SELECT SUM(salePrice) GROUP BY tNo); 黃三益2008 資料庫的核心理論與實務第四版

分群查詢句(cont.) SQL99 允許 EVERY 和 ANY 列出所有使用購物車、購買商品樣數超過2，且所有購買的商品價格都超過300的交易之交易編號和購買總金額 SELECT tNo, SUM(salePrice) FROM Transaction NATURAL JOIN Record WHERE method = ‘cart’ GROUP BY tNo HAVING COUNT(pNo) > 2 AND EVERY (salePrice > 300) 黃三益2008 資料庫的核心理論與實務第四版

分群查詢句 分群彙總結合巢狀查詢 Q20：對於每一筆上網達成（即method = ‘cart’），且購買商品種樣數超過2的交易，列出其交易編號和所購買定價超過500的商品種類數。 SELECT tNo, COUNT(pNo) FROM (Transaction NATURAL JOIN Record) NATURAL JOIN Product WHERE method = ‘cart’ AND unitPrice > 500 GROUP BY tNo HAVING COUNT(pNo) > 2; WRONG! 黃三益2008 資料庫的核心理論與實務第四版

分群查詢句 正確為： SELECT tNo, COUNT(pNo) FROM (Transaction NATURAL JOIN Record) NATURAL JOIN Product WHERE method = ‘cart’ AND unitPrice > 500 AND tNo IN (SELECT tNo FROM Transaction NATURAL JOIN Record WHERE method = ‘cart’ GROUP BY tNo HAVING COUNT(pNo) > 2) GROUP BY tNo; 黃三益2008 資料庫的核心理論與實務第四版

分群彙總結合OUTER JOIN Q21：對於每一位會員，列出其會員編號、總交易數和總交易金額。 SELECT mId, COUNT(DISTINCT tNo), SUM(salePrice) FROM (Member LEFT OUTER JOIN Transaction ON mId = transMid) NATURAL JOIN Record GROUP BY mId; 黃三益2008 資料庫的核心理論與實務第四版

分群彙總結合OUTER JOIN 正確為 SELECT mId, COUNT(DISTINCT tNo), SUM(salePrice) FROM Member LEFT OUTER JOIN (Transaction NATURAL JOIN Record) ON mId = transMid GROUP BY mId; 黃三益2008 資料庫的核心理論與實務第四版

分群彙總結合更新語法 U6: 產生一個資料表Transaction_total(tNo, totalAmount)來儲存每一筆交易的總金額。 CREATE TABLE Transaction_total (tNo CHAR(5) NOT NULL, totalAmount INT); INSERT INTO Transaction_total SELECT tNo, SUM(salePrice) FROM Transaction NATURAL JOIN Record GROUP BY tNo; U7: 將有兩位以上創作者的商品定價提高二成 。 UPDATE Product SET unitPrice = unitPrice * 1.2 WHERE pNo IN (SELECT pNo FROM Author GROUP BY pNo HAVING COUNT(*) > 2); 黃三益2008 資料庫的核心理論與實務第四版

練習7-6 將居住在台北市的會員之交易商品售價打九折 Ans: UPDATE Record SET salePrice = salePrice*0.9 WHERE tNo IN (SELECT tNo FROM Transaction, Member WHERE address LIKE ‘%台北市%’ AND mId=transMid); 黃三益2008 資料庫的核心理論與實務第四版

SQL的VIEW VIEW就是虛擬資料表 主要用途： 產生VIEW的語法如下： CREATE VIEW <VIEW名稱> AS <SQL查詢句> V1：產生一個VIEW Transaction_total(tNo, totalAmount)來表示每一筆交易的總金額。 CREATE VIEW Transaction_total(tNo, totalAmount) AS SELECT tNo, SUM(salePrice) FROM Record GROUP BY tNo; 黃三益2008 資料庫的核心理論與實務第四版

SQL的VIEW（Cont.) VIEW在查詢句裡的使用方式如同資料表 刪除一個VIEW SELECT totalAmount FROM Transaction_total WHERE tNo = ‘91100’; 刪除一個VIEW DROP VIEW Transaction_total; 黃三益2008 資料庫的核心理論與實務第四版

SQL的VIEW（Cont.) 修改VIEW的記錄意味著修改相對應資料表的記錄 V2：CREATE VIEW Cheap_product AS SELECT pNo, pName, unitPrice FROM Product WHERE unitPrice < 300; 此時，我們可以執行以下的SQL修改句： UV1：UPDATE Cheap_product SET unitPrice = unitPrice * 0.9; 黃三益2008 資料庫的核心理論與實務第四版

SQL的VIEW（Cont.) 以下的VIEW不能被修改，因為沒有唯一的資料表修改方式 此view包含彙總函數在它的定義中。 UV3：UPDATE Transaction_total SET totalAmount = totalAmount – 100; 此view不包含任何關聯鍵（key）。 V3：CREATE VIEW Category_price AS SELECT category, unitPrice FROM Product WHERE unitPrice > 300; 此view由兩個或以上個資料表所JOIN而成。 黃三益2008 資料庫的核心理論與實務第四版

SQL的VIEW（Cont.) 考慮以下的VIEW 以下兩種修改方式都滿足UV4 V4：CREATE VIEW Trans_product AS SELECT tNo, pName FROM Record NATURAL JOIN Product; UV4： UPDATE Trans_product SET pName = ‘OLAP進階’ WHERE tNo = ‘91100’ AND pName = ‘資料庫理論與實務’; 以下兩種修改方式都滿足UV4 UV5：UPDATE Product SET pName = ‘OLAP進階 ’ WHERE pNo = ‘b30999’; UV6：UPDATE Record SET pNo = ‘b20666’ WHERE tNo = ‘91100’AND pNo=‘b30999’; 黃三益2008 資料庫的核心理論與實務第四版

SQL的VIEW（Cont.) 練習7-7： Ans: 請產生一個VIEW列出每一類商品的名稱和總商品樣數。 該VIEW可以修改嗎？ CREATE VIEW Product_amount (category, categoryAmount) AS SELECT category, COUNT(pNo) FROM Product GROUP BY category; 因為此view包含彙總函數(COUNT), 所以會造成修改的不明確, 故此view不能被修改 DROP VIEW Product_amount 黃三益2008 資料庫的核心理論與實務第四版

SQL的權限控制 產生table的權力 新增和刪除記錄的權力 修改記錄屬性的權力 查詢的權力 權利轉移的權力 取消權力 存取VIEW的權力 GRANT CREATETAB TO Account1; 新增和刪除記錄的權力 GRANT INSERT, DELETE ON Product TO Account2; 修改記錄屬性的權力 GRANT UPDATE ON Product (unitPrice) TO Account3; 查詢的權力 GRANT SELECT ON Product TO Account4; 權利轉移的權力 GRANT SELECT ON Product TO Account4 WITH GRANT OPTION; 取消權力 REVOKE SELECT ON Product FROM Account4; 存取VIEW的權力 GRANT SELECT ON Trans_total TO Account5; 黃三益2008 資料庫的核心理論與實務第四版

語意的限制 可用以下語法： CREATE ASSERTION <ASSERTION名稱> CHECK <條件句>; 交易總金額不得少於100才可用網路交易”。 CREATE ASSERTION TransactionAmount_Constraint CHECK (NOT EXISTS (SELECT * FROM Transaction NATURAL JOIN Record WHERE method = ‘cart’ GROUP BY tNo HAVING SUM(salesPrice) < 100)); 黃三益2008 資料庫的核心理論與實務第四版

SQL的索引 索引(Index) 的目的是為了加速查詢的處理 CREATE INDEX Price_index ON Product(unitPrice); 如下的查詢速度便會大幅加快： SELECT * FROM Product WHERE unitPrice BETWEEN 100 AND 2000; 黃三益2008 資料庫的核心理論與實務第四版

SQL的索引（Cont.) 也可以設定在兩個或以上的屬性 CREATE INDEX CatPrice_index ON Product(category ASC, unitPrice DESC); 如下的查詢速度便會大幅加快： SELECT * FROM Product WHERE category = ‘Book’ AND unitPrice BETWEEN 100 AND 2000; 若將索引設定成CLUSTER，表示在硬碟中該資料表的記錄實體是按照該索引所設定的次序排列 CREATE INDEX PNo_index ON Product(pNo) CLUSTER; 黃三益2008 資料庫的核心理論與實務第四版

SQL的CURSOR和儲存程式 CURSOR是一種將查詢的結果一次回傳一筆記錄的機制 PL/SQL程式片段： CURSOR price_cursor IS SELECT pNo, pName, unitPrice FROM Product; … OPEN price_cursor; LOOP FETCH price_cursor INTO a1, a2, a3; EXIT WHEN price_cursor%NOTFOUND; ENDLOOP; CLOSE price_cursor; 黃三益2008 資料庫的核心理論與實務第四版

SQL的TRIGGER DBMS主動偵測資料的內容並採取行動 PL/SQL的一個簡單例子 CREATE TRIGGER Transaction_check AFTER INSERT ON Record FOR EACH ROW WHEN (New.salePrice IS NOT NULL) UPDATE Transaction_total SET totalAmount=totalAmount + New.salePrice WHERE tNo=New.tNo; 黃三益2008 資料庫的核心理論與實務第四版

商用DBMS 的SQL 語法差異 有些DBMS沿襲SQL89舊制，使用MINUS而非EXCEPT來表示集合的差集 大部分DBMS不支援CREATE ASSERTION，若有語意完整限制的需求，可用CREATE TRIGGER來達到類似的效果 有些DBMS不提供NATURAL JOIN 有些DBMS裡，JOIN或不包含關聯鍵的VIEW也可以被修改 黃三益2008 資料庫的核心理論與實務第四版

商用DBMS 的SQL 語法差異(Cont.) 許多DBMS有特有的CREATE INDEX CLUSTER語法 大部分DBMS有其特有的CREATE TRIGGER語法 愈來愈多的DBMS有提供全文索引的功能（如SQL Server和MySQL），但語法不同 其他差異點請參閱書本7.8節 黃三益2008 資料庫的核心理論與實務第四版