Download
1 / 39
390 likes | 458 Views
Ch 8 Supplement #1. SELECT and JOIN. 楊立偉教授 台灣大學工管系 2013 Fall. 1. Relational algebra. Relational database 的數學理論基礎 對於 Relation 的基本操作包括了 ( 水平 ) 選擇運算 Selection ( 垂直 ) 投影運算 Projection 乘積運算 Product 合併運算 Join 聯集與差集運算 Union / Difference. ( 水平 ) 選擇運算 Selection. SELECT…FROM R WHER P
E N D
Ch 8Supplement #1 SELECT and JOIN 楊立偉教授 台灣大學工管系 2013 Fall 1
Relational algebra • Relational database 的數學理論基礎 • 對於Relation的基本操作包括了 • (水平)選擇運算 Selection • (垂直)投影運算 Projection • 乘積運算 Product • 合併運算 Join • 聯集與差集運算 Union / Difference
(水平)選擇運算 Selection • SELECT…FROM R WHER P • 用給定條件過濾出所要的資料 • 是關聯式資料庫最常用的功能
(水平)選擇運算 Selection – 範例 • SELECT…FROM Books WHERE price<140
(水平)選擇運算 Selection – 範例 • SELECT…FROM Books WHERE id<=3
(水平)選擇運算 Selection – 範例 • SELECT…FROM Books WHERE price<140 or id<=3 透過欄位條件，可方便地取回想要的資料
(垂直)投影運算 Projection • SELECT F1,F2,…FROM…WHER… • 只將想要的欄位投影出來 • SELECT id, author, publish FROM Books
Product 乘積運算 • 又稱 Cartesian Product • 任二張表格紀錄間的全部排列組合
Join 合併運算 • 將表格間依特定條件做合併 • 由FK關聯回PK, 組回一張大表
Join 合併運算 – 範例 SELECT Emp.*, Dept.* FROM Emp JOIN Dept ON Emp.dep_no=Dept.no
Join 合併運算 – 另一種角度 • 合併運算的結果，其實就是Product乘積結果，再用Selection選擇條件做篩選 依dep_no=no 做選擇 先做乘積
Join 合併運算 • Join是關聯式資料庫最核心的功能 • 相當於透過FK與PK的關聯，重新組回一張大表 • 同時讓重複資料降到最少，又不遺失資訊 • Join後的結果，必被包含於乘積之中 • 可執行多次Join以合併多張表格 • 「所有表格運算後的結果仍是表格」 • 此為Relational algebra的封閉特性 (closure)
Join 合併運算 – 範例 SELECT Order.*, Customer.*, Product.* FROM Order JOIN Customer ON Order.c_id=Customer.id JOIN Product ON Order.p_id=Product.id 查詢結果
Join 合併運算 – 另一種角度 • 一樣可視為乘積後、選擇條件篩選後的結果 • 可想成兩張Join後結果與第三張再Join，或三張表格直接做乘積後做條件篩選，結果一樣 • 此為Relational Algebra的結合律 (Associativity) 與交換律 (Commutativity) 先做乘積 共8筆 依c_id=id and p_id=id 做選擇 剩2筆 (與前頁結果一樣)
Join 合併運算 – 配合Where 找出落在10月且為女性之訂單, 其姓名與購買產品為何 SELECT Customer.Name, Product.Name FROM Order JOIN Customer ON Order.c_id=Customer.id JOIN Product ON Order.p_id=Product.id Where date>=‘20091001’ and date<=‘20091031’ and Customer.Gender=‘女’ 查詢結果 (先想Join結果, 再想Where, 再想欄位結果)
Join 合併運算 – RDBMS內部的真實運作方式 • 直觀：透過巢狀迴路(Nested Loop)做查表 • 一個Join隱含大量的查表動作，因此常需Join的欄位，記得要建立索引 (index) • 通則：在PK與FK欄位上建立索引
Ch 8Supplement #2 SELECT 個案練習 楊立偉教授 台灣大學工管系 2012 Fall 17
綜合練習 • 為了建立一個社交網站，請設計一個可以包括會員與交友關係的資料庫
會員與交友關係 (1) • 使用unary relationship來表達 關係上 也有屬性 E-R Model Diagram
會員與交友關係 (2) • 轉換成表格 多對多關係 關係上有屬性 引入 Association Entity
會員與交友關係 (3) • 建立表格、欄位、關聯
基本查詢練習 (1) • 列出住在台北, 有朋友在新竹的所有會員 • 分解動作1: 先列出住在台北的人 SELECT m.* FROM member AS m WHERE m.area='台北';
基本查詢練習 (1) • 分解動作2: 列出住台北的人, 以及他們的朋友 SELECT m.*, f.* FROM member AS m, member_friendship AS f WHERE m.area='台北' and m.mno=f.mno;
基本查詢練習 (1) • 分解動作3: 列出住台北的人, 以及他們的朋友, 連他們的朋友住哪也列出來 SELECT m.*, f.*, n.name, n.area FROM member AS m, member_friendship AS f, member AS N WHERE m.area='台北' and m.mno=f.mno and f.f_mno=n.mno;
基本查詢練習 (1) • 分解動作4: 列出住台北的人, 以及他們的朋友, 連他們的朋友住哪也列出來, 只留下住新竹的 SELECT m.*, f.*, n.name, n.area FROM member AS m, member_friendship AS f, member AS n WHERE m.area='台北' and m.mno=f.mno and f.f_mno=n.mno and n.area='新竹';
基本查詢練習 (2) • 交友人數排行榜 : 列出至少有3位以上朋友, 由人數多的開始, 列出他們的姓名與人數 • 分解動作1: 先統計每個人有多少朋友, 並排序 SELECT mno, count(*) FROM member_friendship GROUP BY mno ORDER BY count(*);
基本查詢練習 (2) • 分解動作2: 把排序方向倒過來, 只留下超過3人以上的, 並把欄位顯示名稱改一下 SELECT mno, count(*) AS ppl FROM member_friendship GROUP BY mno HAVING count(*)>=3 ORDER BY count(*) DESC;
基本查詢練習 (2) 這邊也要加入 , m.name • 分解動作3: 把會員名稱連回來即可 SELECT f.mno, m.name, count(*) AS ppl FROM member_friendship AS f, member AS m WHERE f.mno=m.mno GROUP BY f.mno HAVING count(*)>=3 ORDER BY count(*) DESC;
基本查詢練習 (2) • 第二種解法 • 當查詢很複雜，透過一句SQL無法完成時，可將查詢結果做暫存，之後再利用 • 分解動作1: 先將統計結果暫存成tmp表格 SELECT mno, count(*) AS ppl INTO tmp FROM member_friendship GROUP BY mno HAVING count(*)>=3 ORDER BY count(*) DESC;
基本查詢練習 (2) • 第二種解法 • 分解動作2: 把會員名稱連回來即可 SELECT tmp.mno, name, ppl FROM tmp, member WHERE tmp.mno=member.mno; • 分解動作3: 刪除暫存表格 DROP TABLE tmp; 非不得已不要用多句SQL, 盡量用一句完成
基本查詢練習 (3) • 列出有朋友的人 • 解法1: 統計每人有幾位朋友, 列出1位以上的 SELECT m.name, count(*) FROM member_friendship AS F LEFT JOIN member AS M ON m.mno=f.mno GROUP BY m.name HAVING count(*)>=1 ORDER BY count(*) DESC , m.name; SELECT m.name, count(*) FROM member_friendship AS F, member as M WHERE m.mno=f.mno GROUP BY m.name HAVING count(*)>=1 ORDER BY count(*) DESC , m.name;
基本查詢練習 (3) • 列出有朋友的人 • 解法2: 運用DISTINCT保留字 SELECT m.name FROM member_friendship AS F LEFT JOIN member AS M ON m.mno=f.mno ORDER BY m.name; SELECT DISTINCT m.name FROM member_friendship AS F LEFT JOIN member AS M ON m.mno=f.mno ORDER BY m.name;
基本查詢練習 (3) • 列出有朋友的人 • 解法3: 運用IN運算元 SELECT name FROM member WHERE mno IN (SELECT mno FROM member_friendship) ORDER BY name; IN 在意義上相當於多個OR相連
基本查詢練習 (4) • 列出沒有朋友的人 • 解法1: 利用Outer Join的特性, 列出無關連的 • 分解動作1: 先用Outer Join SELECT m.name, f.f_mno FROM member AS m LEFT OUTER JOIN member_friendship AS f ON m.mno=f.mno ORDER BY m.name;
基本查詢練習 (4) • 分解動作2: 再用WHERE挑選出無關連的資料即可 SELECT m.name, f.f_mno FROM member AS m LEFT OUTER JOIN member_friendship AS f ON m.mno=f.mno WHERE f.f_mno is null ORDER BY m.name;
基本查詢練習 (4) • 列出沒有朋友的人 • 解法2: 運用NOT IN運算元 SELECT name FROM member WHERE mno NOT IN (SELECT mno FROM member_friendship) ORDER BY name;
工具使用 (1) • Excel轉入Access的方法 • 在Access內, 選檔案→取得外部資料 • 匯入 • 連結 選擇檔案類型 為Excel 之後依序決定 欄位屬性即可
工具使用 (2) • Access轉入Excel的方法 • 直接在Excel內選複製, 再到Access貼上即可 • 在Access內, 選檔案→匯出 選擇檔案類型 為Excel
