Microsoft PowerPoint - Ch7

Size: px

Start display at page:

Download "Microsoft PowerPoint - Ch7"

斐五禄
5 years ago
Views:

1 集合運算式巢狀查詢句 IN EXISTS JOIN 的查詢句分群彙總查詢句 SQL 的 VIEW 其他進階語法權限控制語意限制索引 CURSOR 和儲存程式 TRIGGER 第七章進階的 SQL 7-1 集合運算式相乘 SELECT mid, pno FROM Member, Produt; 交集聯集差集等運算子則分別用 INTERSECT UNION, 和 EXCEPT Q5: 找出所有瀏覽過或購買過系統分析理論與實務的會員之會員編號和會員姓名 (SELECT M.mId, M.name FROM Produt AS P, Browse AS B, Member AS M WHERE pname = ` 系統分析理論與實務 ' AND P.pNo = B.pNo AND B.mId = M.mId) UNION (SELECT M.mId, M.name FROM Produt AS P, Reord AS R, Transation AS T, Member AS M WHERE pname = ` 系統分析理論與實務 ' AND P.pNo = R.pNo AND R.tNo = T.tNo AND T.transMid = M.mId); 7-2 1

2 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) 次 7-3 a b a a b b b b b b b b b b d b R S R UNION ALL S R INTERSECT ALL S R EXCEPT ALL S d 7-4 2

3 練習 7-1 請找出所有既出 CD 也出書的創作者 Ans: (SELECT name FROM Produt AS P, Author AS A WHERE atalog= `Book' AND P.pNo = A.pNo) INTERSECT (SELECT name FROM Produt AS P, Author AS A WHERE atalog= `CD' AND P.pNo = A.pNo); 7-5 巢狀查詢句巢狀查詢句 : 在 FROM 子句或 WHERE 子句裡容納另一個查詢子句在 FROM 子句裡的查詢子句列出系統分析理論與實務的作者姓名 SELECT name FROM (SELECT * FROM Produt WHERE pname = ` 系統分析理論與實務 ') AS P, Author WHERE P.pNo = Author.pNo; 在 WHERE 子句裡的查詢子句很強的表達能力 IN 和 EXISTS( 用來表達邏輯計算式裡的限制子 ) 7-6 3

4 巢狀查詢句 (IN ) 列出所有購買過系統分析理論與實務的會員之會員編號和會員姓名 SELECT mid, name FROM Member WHERE mid IN (SELECT transmid FROM Produt AS P, Reord AS R, Transation AS T WHERE pname= ` 系統分析理論與實務 ' AND P.pNo = R.pNo AND R.tNo = T.tNo); 7-7 巢狀查詢句 (IN ) Q5: 找出所有瀏覽過或購買過系統分析理論與實務的會員之會員編號和會員姓名 SELECT mid, name FROM Member WHERE (mid IN (SELECT mid FROM Browse AS B, Produt AS P WHERE pname = ` 系統分析理論與實務 ' AND P.pNo = B.pNo)) OR (mid IN (SELECT transmid FROM Produt AS P, Reord AS R, Transation AS T WHERE pname = ` 系統分析理論與實務 ' AND P.pNo = R.pNo AND R.tNo = T.tNo)); 7-8 4

5 巢狀查詢句 (IN ) IN 左邊的記錄也可包含兩個或兩個以上的屬性 Q6: 找出購物車裡含有系統分析理論與實務的交易之編號 SELECT tno FROM Cart WHERE (mid, arttime) IN (SELECT mid, arttime FROM Order, Produt WHERE Order.pNo=Produt.pNo AND pname=` 系統分析理論與實務 '); 或是 SELECT tno FROM Cart WHERE (mid, arttime) IN (SELECT mid, arttime FROM Order WHERE pno IN (SELECT pno FROM Produt WHERE pname = ` 系統分析理論與實務 ')); 7-9 巢狀查詢句 (IN ) IN 前可以加上 NOT 來表示否定找出沒有創作者的商品編號和商品名稱 SELECT pno, pname FROM Produt WHERE pno NOT IN (SELECT pno FROM Author);

6 練習 7-2 用巢狀查詢句找出所有瀏覽但沒有購買過系統分析理論與實務的會員之會員編號和會員姓名 Ans: SELECT mid, name FROM Member WHERE (mid IN (SELECT B.mId FROM Browse AS B, Produt AS P AND WHERE pname = ` 系統分析理論與實務 ' AND P.pNo = B.pNo)) (mid NOT IN (SELECT transmid FROM Produt AS P, Reord AS R, Transation AS T WHERE pname = ` 系統分析理論與實務 ' AND P.pNo = R.pNo AND R.tNo = T.tNo)); 7-11 巢狀查詢句 (IN ) 子查詢句也可以參考到上層查詢句的資料表 Q7: 找出黃三益所瀏覽過的商品裡, 哪些有真正被他購買 SELECT pno FROM Browse AS B, Member AS M WHERE name = ` 黃三益 ' AND B.mId = M.mId AND pno IN (SELECT pno FROM Reord AS R, Transation AS T WHERE transmid = M.mId AND T.tNo = R.tNo);

7 練習 7-3 請將第五章的 Q4 用巢狀查詢句表達 ( 列出所有瀏覽過或購買過系統分析理論與實務的會員之會員編號和會員姓名 ) SELECT mid, name FROM Member WHERE (mid IN (SELECT B.mId FROM Browse AS B, Produt AS P WHERE pname = ` 系統分析理論與實務 ' AND P.pNo = B.pNo)) OR (mid IN (SELECT transmid FROM Produt AS P, Reord AS R, Transation 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, Reord AS R, Transation AS T WHERE M.name = ` 黃三益 ' AND M.mId = B.mId AND B.pNo=R.pNo AND R.tNo=T.tNo AND transmid = M.mId ; 7-13 巢狀查詢句 (IN ) 除了 IN 之外, 還有其他相關的比較運算子, 如 =(>,>=,<,<=) SOME (SELECT...) =(>,>=,<,<=) ALL (SELECT...) Q8: 找出定價比所有書籍都高的商品 : SELECT pno, pname FROM Produt WHERE unitprie > ALL( SELECT unitprie FROM Produt WHERE atalog= `Book'); IN 右邊的資料表內容也可直接寫出 SELECT DISTINCT mid FROM Browse WHERE pno IN (`b30999', `b10234', `d11222');

8 巢狀查詢句 (EXISTS ) 單元運算子, 用來測試一個資料表是否有記錄列出所有購買過系統分析理論與實務的會員之會員編號和會員姓名 SELECT mid, name FROM Member WHERE EXISTS (SELECT * FROM Produt, Reord, Transation WHERE pname=' 系統分析理論與實務 ' AND Produt.pNo = Reord.pNo AND Reord.tNo = Transation.tNo AND mid = transmid); 7-15 巢狀查詢句 (EXISTS ) EXISTS 前可以加上 NOT 來表示否定 Q10: 找出所有非由購物車而來的交易的交易編號和會員編號 SELECT tno, transmid FROM Transation AS T WHERE NOT EXISTS (SELECT * FROM Cart WHERE tno = T.tNo);

9 巢狀查詢句 (EXISTS ) 可表達邏輯關係較複雜的查詢 Q11: 找出購買所有 Jakey 所創作商品的會員之會員編號和會員姓名假設 Jakey 所創作的產品所成的集合為 J, 一位會員 ( 比如張三 ) 所購買的所有產品為 C, 張三若符合條件則 J C= J: SELECT pno FROM Author WHERE name = `Jakey'; C: SELECT pno FROM Transation AS T, Reord AS R WHERE transmid=m.mid AND T.tNo = R.tNo; 7-17 巢狀查詢句 (EXISTS ) SELECT mid, name FROM Member AS M WHERE NOT EXISTS ( (SELECT pno FROM Author WHERE name = `Jakey') EXCEPT (SELECT pno FROM Transation AS T, Reord AS R WHERE transmid=m.mid AND T.tNo = R.tNo));

10 JOIN 的查詢句 JOIN NATURAL JOIN 和 OUTER JOIN 也可以設定在 FROM 子句裡或 SELECT name FROM Produt JOIN Author ON Produt.pNo = Author.pNo WHERE pname = ` 系統分析理論與實務 '; SELECT name FROM Produt NATURAL JOIN Author WHERE pname = ` 系統分析理論與實務 '; 7-19 JOIN 的查詢句 Q12: 列出每一位會員的會員編號姓名生日, 以及其介紹者的會員編號和姓名 ( 如果有的話 ) 本題需用 LEFT OUTER JOIN SELECT M.mId AS member_id, M.name AS member_name, I.mId AS introduer_mid, I.name AS introduer_name FROM Member AS M LEFT OUTER JOIN Member AS I ON M.introduer = I.mId;

11 JOIN 的查詢句 Q13: 列出每一位會員的會員編號姓名, 以及 2005 年所瀏覽的商品之商品編號 ( 如果有的話 ) SELECT M.mId, name, pno SELECT M.mId, name, pno FROM FROM Member Member AS M LEFT AS M OUTER LEFT JOIN OUTER Browse JOIN AS Browse B AS B ON (M.mId ON M.mId = B.mId = B.mId AND to_har(browsetime, yyyy ) = 2005 ); WHERE to_har(browsetime, `yyyy') = `2005'; 7-21 練習 7-4 列出所有商品的商品編號商品名稱, 以及創作者姓名 ( 如果有的話 ) Ans: SELECT pno, pname, A.name FROM Produt AS P LEFT OUTER JOIN Author AS A ON P.pNo=A.pNo;

12 SQL 查詢的彙總函數和分群一般式如下 : SELECT < 分群屬性 >,< 彙總函數 > FROM < 資料表 > WHERE < 記錄選取條件 > GROUP BY < 分群屬性 > HAVING < 記錄群選取條件 > WHERE GROUP BY HAVING 7-23 彙總函數彙總函數 :SUM AVG COUNT MAX 和 MIN Q14: 列出所有商品數, 平均定價, 最高定價, 和最低定價 SELECT COUNT(*), AVG(unitPrie), MAX(unitPrie), MIN(unitPrie) FROM Produt; SELECT COUNT(DISTINCT unitprie) FROM Produt;

13 分群查詢句 (ont.) Q15: 列出每一筆交易的交易編號和交易總金額 SELECT tno, SUM(salePrie) FROM Reord GROUP BY tno; Q16: 列出每一筆上網達成的交易 ( 即 method = `art') 之交易編號, 和購買商品總樣數 : SELECT tno, COUNT(*) FROM Transation NATURAL JOIN Reord WHERE method = `art' GROUP BY tno; 7-25 分群查詢句 (ont.) 彙總函數也可用在巢狀查詢句的 WHERE 子句中 Q17: 找出有兩筆以上交易的會員之會員編號和姓名 SELECT mid, name FROM Member WHERE (SELECT COUNT(*) FROM Transation WHERE mid=transmid)>2; Q18: 列出每一筆交易的交易編號會員編號和交易總金額 SELECT tno, transmid, SUM(salePrie) FROM Transation NATURAL JOIN Reord GROUP BY tno, transmid;

14 分群查詢句 (ont.) 數群記錄可用 HAVING 子句來設定挑選條件 : Q19: 對於每一筆上網達成 ( 即 method = `art'), 且購買商品種樣數超過 2 的交易, 列出其交易編號和購買商品種樣數 SELECT tno, COUNT(pNo) FROM Transation NATURAL JOIN Reord WHERE method = `art' GROUP BY tno HAVING COUNT(pNo) > 2; 7-27 練習 7-5 對於每一台商品種樣數超過 3 的購物車, 列出其購物時間會員編號會員姓名 SELECT M.mId, M.name, arttime FROM Member AS M, Order AS O WHERE O.mId = M.mId GROUP BY M.mId, M.name, arttime HAVING COUNT(pNo) > 3;

15 分群查詢句 (ont.) HAVING 子句上也可有子查詢句列出所有使用購物車且購買一件以上黃三益創作的商品的交易之交易編號和購買總商品樣數 SELECT tno, COUNT(pNo) FROM (Transation NATURAL JOIN Reord) AS T WHERE method = `art' GROUP BY T.tNo HAVING COUNT(pNo) > 2 AND 1 < (SELECT COUNT(*) FROM Reord NATURAL JOIN Author WHERE name = ` 黃三益 'AND T.tNo = tno); 7-29 分群查詢句 (ont.) SQL99 允許 EVERY 和 ANY 列出所有使用購物車購買商品樣數超過 2, 且所有購買的商品價格都超過 300 的交易之交易編號和購買商品樣數 SELECT tno, COUNT(pNo) FROM Transation NATURAL JOIN Reord WHERE method = `art' GROUP BY tno HAVING COUNT(pNo) > 2 AND EVERY (saleprie > 300)

16 分群查詢句分群彙總結合巢狀查詢 Q20: 對於每一筆上網達成 ( 即 method = `art'), 且購買商品種樣數超過 2 的交易, 列出其交易編號和所購買定價超過 500 的商品種類數 SELECT tno, COUNT(pNo) FROM (Transation NATURAL JOIN Reord) NATURAL JOIN Produt WHERE method = `art' AND unitprie > 500 GROUP BY tno HAVING COUNT(pNo) > 2; WRONG! 7-31 分群查詢句正確為 : SELECT tno, COUNT(pNo) FROM (Transation NATURAL JOIN Reord) NATURAL JOIN Produt WHERE method = `art' AND unitprie > 500 AND tno IN (SELECT tno FROM Transation NATURAL JOIN Reord WHERE method = `art' GROUP BY tno HAVING COUNT(pNo) > 2) GROUP BY tno;

17 分群彙總結合 OUTER JOIN Q21: 對於每一位會員, 列出其會員編號總交易數和總交易金額 SELECT mid, COUNT(DISTINCT tno), SUM(salePrie) FROM (Member LEFT OUTER JOIN Transation ON mid = transmid) NATURAL JOIN Reord GROUP BY mid; 7-33 正確為分群彙總結合 OUTER JOIN SELECT mid, COUNT(DISTINCT tno), SUM(salePrie) FROM Member LEFT OUTER JOIN (Transation NATURAL JOIN Reord) ON mid = transmid GROUP BY mid;

18 分群彙總結合更新語法 U6: 產生一個資料表 Transation_total(tNo, totalamount) 來儲存每一筆交易的總金額 CREATE TABLE Transation_total (tno VARCHAR(10) NOT NULL, totalamount INT); INSERT INTO Transation_total SELECT tno, SUM(salePrie) FROM Transation NATURAL JOIN Reord GROUP BY tno; U7: 將有兩位以上創作者的商品定價提高二成 UPDATE Produt SET unitprie = unitprie * 1.2 WHERE pno IN (SELECT pno FROM Author GROUP BY tno HAVING COUNT(*) > 2); 7-35 練習 7-6 將居住在台北市的會員之交易商品售價打九折 Ans: UPDATE Reord SET saleprie = saleprie*0.9 WHERE tno IN (SELECT tno FROM Transation, Member WHERE address LIKE `% 台北市 %' AND mid=transmid);

19 SQL 的 VIEW VIEW 就是虛擬資料表使用者可以像使用一般資料表般的使用 VIEW, 但其實 VIEW 的實體並不存在主要用途 : 常用的查詢定義成 VIEW, 以方便將來使用 VIEW 可以形成外部綱目, 並據以設定權限, 讓某些使用者只能存取 VIEW 裡的欄位, 而無法存取實體資料表裡的其他欄位產生 VIEW 的語法如下 : CREATE VIEW <VIEW 名稱 > AS <SQL 查詢句 > V1: 產生一個 VIEW Transation_total(tNo, totalamount) 來表示每一筆交易的總金額 CREATE VIEW Transation_total(tNo, totalamount) AS (SELECT tno, SUM(salePrie) FROM Reord GROUP BY tno); 7-37 SQL 的 VIEW(Cont.) VIEW 在查詢句裡的使用方式如同資料表 SELECT totalamount FROM Transation_total WHERE tno = `91100'; 刪除一個 VIEW DROP VIEW Transation_total;

20 SQL 的 VIEW(Cont.) 修改 VIEW 的記錄意味著修改相對應資料表的記錄 V2:CREATE VIEW Cheap_produt AS (SELECT pno, pname, unitprie FROM Produt WHERE unitprie < 300); 此時, 我們可以執行以下的 SQL 修改句 : UV1:UPDATE Cheap_produt SET unitprie = unitprie * 0.9; 7-39 SQL 的 VIEW(Cont.) 以下的 VIEW 不能被修改, 因為沒有唯一的資料表修改方式此 view 包含彙總函數在它的定義中 UV3:UPDATE Transation_total SET totalamount = totalamount 100; 此 view 不包含任何關聯鍵 (key) V3:CREATE VIEW Catalog_prie AS (SELECT atalog, unitprie FROM Produt WHERE unitprie > 300); 此 view 由兩個或以上個資料表所 JOIN 而成

21 SQL 的 VIEW(Cont.) 考慮以下的 VIEW V4:CREATE VIEW Trans_produt AS (SELECT tno, pname FROM Reord NATURAL JOIN Produt); UV4: UPDATE Trans_produt SET pname = `OLAP 進階 ' WHERE tno = `91100' AND pname = ` 資料庫理論與實務 '; 以下兩種修改方式都滿足 UV4 UV5:UPDATE Produt SET pname = `OLAP 進階 ' WHERE pno = `b30999'; UV6:UPDATE Reord SET pno = `b20666' WHERE tno = `91100'AND pno='b30999'; 7-41 練習 7-7: SQL 的 VIEW(Cont.) 請產生一個 VIEW 列出每一類商品的名稱和總商品樣數該 VIEW 可以修改嗎? 請刪除該 VIEW Ans: CREATE VIEW Produt_amount (atalog, atalogamount) AS (SELECT atalog, COUNT(pNo) FROM Produt GROUP BY atalog); 因為此 view 包含彙總函數 (COUNT), 所以會造成修改的不明確, 故此 view 不能被修改 DROP VIEW Produt_amount

22 SQL 的權限控制產生 table 的權力 GRANT CREATETAB TO Aount1; 新增和刪除記錄的權力 GRANT INSERT, DELETE ON Produt TO Aount2; 修改記錄屬性的權力 GRANT UPDATE ON Produt (unitprie) TO Aount3; 查詢的權力 GRANT SELECT ON Produt TO Aount4; 權利轉移的權力 GRANT SELECT ON Produt TO Aount4 WITH GRANT OPTION; 取消權力 REVOKE SELECT ON Produt FROM Aount4; 存取 VIEW 的權力 GRANT SELECT ON Trans_total TO Aount5; 7-43 可用以下語法 : 語意的限制 CREATE ASSERTION <ASSERTION 名稱 > CHECK < 條件句 >; 交易總金額不得少於 100 才可用網路交易 " CREATE ASSERTION TransationAmount_Constraint CHECK(NOT EXISTS (SELECT * FROM Transation NATURAL JOIN Reord WHERE method = `art' GROUP BY tno HAVING SUM(salesPrie) < 100));

23 SQL 的索引索引 (Index) 的目的是為了加速查詢的處理針對常用來做查詢條件的屬性, 建立一個資料結構 ( 稱為 INDEX), 以便加速對該屬性值的搜尋 CREATE INDEX Prie_index ON Produt(unitPrie); 如下的查詢速度便會大幅加快 : SELECT * FROM Produt WHERE unitprie BETWEEN 100 AND 2000; 7-45 SQL 的索引 (Cont.) 也可以設定在兩個或以上的屬性 CREATE INDEX CatPrie_index ON Produt(atalog ASC, unitprie DESC); 如下的查詢速度便會大幅加快 : SELECT * FROM Produt WHERE atalog = `Book' AND unitprie BETWEEN 100 AND 2000; 若將索引設定成 CLUSTER, 表示在硬碟中該資料表的記錄實體是按照該索引所設定的次序排列 CREATE INDEX PNo_index ON Produt(pNo) CLUSTER;

24 SQL 的 CURSOR 和儲存程式 CURSOR 是一種將查詢的結果一次回傳一筆記錄的機制 PL/SQL 程式片段 : CURSOR prie_ursor IS SELECT pno, pname, unitprie FROM Produt; OPEN prie_ursor; LOOP FETCH unit_ursor INTO a1, a2, a3; EXIT WHEN unit_ursor%notfound; ENDLOOP; CLOSE unit_ursor; 7-47 SQL 的 TRIGGER DBMS 主動偵測資料的內容並採取行動 PL/SQL 的一個簡單例子 CREATE TRIGGER Transation_hek AFTER INSERT ON Reord FOR EACH ROW WHEN (New.salePrie IS NOT NULL) UPDATE Transation_total SET totalamount=totalamount + New.salePrie WHERE tno=new.tno;

25 商用 DBMS 的 SQL 語法差異有些 DBMS 沿襲 SQL89 舊制, 使用 MINUS 而非 EXCEPT 來表示集合的差集大部分 DBMS 不支援 CREATE ASSERTION, 若有語意完整限制的需求, 可改用 CREATE TRIGGER 來達到類似的效果有些 DBMS 不提供 NATURAL JOIN 有些 DBMS 裡,JOIN 或不包含關聯鍵的 VIEW 也可以被修改 7-49 商用 DBMS 的 SQL 語法差異 (Cont.) 許多 DBMS 有特有的 CREATE INDEX CLUSTER 語法大部分 DBMS 有其特有的 CREATE TRIGGER 語法愈來愈多的 DBMS 有提供全文索引的功能 ( 如 SQL Server 和 MySQL), 但語法不同其他差異點請參考書本

Microsoft PowerPoint - Ch6

Microsoft PowerPoint - Ch6 第六章基本的 SQL 目的 SQL 的關聯模式 SQL 的資料定義語言 CREATE TABLE DROP TABLE ALTER TABLE 基本的 SQL 查詢 SELECT FROM WHERE SQL 的更新 INSERT UPDATE DELETE 6-1 目的關聯模式定義嚴謹簡潔, 但好用性和執行效率必須