資料表單的運算

在關聯式代數中,有以下幾種不同的運算:

-- 限制 (Restrict) ~ 選取符合某些條件的值組(記錄),另成一個新的關聯表。

例如: SELECT * FROM tableA WHERE [conditions]

-- 投影 (Project) ~ 選取想要的欄位(屬性),另成一個新的關聯表。

例如: SELECT [some fields] FROM tableA

-- 卡氏積 (Cartesian Product),又稱Cross Product(交叉乘積)、Cross Join(交叉合併) ~ 將兩個關聯表相乘,形成一個新的關聯表。但是光是關聯表相乘,並不是我們真正需要的,所以通常相乘後,都還需要加上另外的條件。

例如: SELECT * FROM tableA, tableB
或是: SELECT * FROM tableA CROSS JOIN tableB

上面兩個表示法,得到的結果是相同的。

例如兩個表單 student (五筆資料) 與 class (十筆資料) ,資料表內容如下:


我們以SELECT * FROM student, class; 及SELECT * FROM student CROSS JOIN class;

執行結果如下 (五十筆資料) :



當然上面兩個表示法,其結果並沒有意義,所以我們可以再加上條件。

例如:

SELECT sid, sname, cid FROM student, class 
WHERE sid='S0001';
找出某特定學生修習哪些課程,列出其學號、姓名、課號。

-- 合併 (Join)



假設上圖中,左圓是老師資料表(teacher),右圓是課程資料表(classmain)。

如果我們使用INNER JOIN,就是取得標示(1)的部分,語法如下:

[INNER JOIN]
SELECT * FROM tableA a INNER JOIN tableB b ON a.key=b.key;
SELECT * FROM tableA a INNER JOIN tableB b USING (key);
SELECT * FROM tableA a, tableB b WHERE a.key=b.key;

SELECT * FROM teacher t inner join classmain c ON t.tid=c.tid;
有開課的老師
也可以寫成 SELECT * FROM teacher t, classmain c WHERE t.tid=c.tid;
也可以寫成 SELECT * FROM teacher t  inner join classmain c USING (tid);

SELECT * FROM classmain c inner join teacher t ON t.tid=c.tid;
有老師開設的課程
也可以寫成 SELECT * FROM classmain c, teacher t WHERE t.tid=c.tid;
也可以寫成 SELECT * FROM classmain c inner join teacher t USING (tid);

使用SELECT * FROM A INNER JOIN B ON A.c=B.c 又稱為Natural JOIN (自然合併)。
使用SELECT * FROM A, B WHERE A.c=B.c 又稱為Equi JOIN (對等合併)。

如果我們使用LEFT OUTER JOIN,就是取得標示(2)的部分,語法如下:

[LEFT OUTER JOIN/LEFT JOIN]
SELECT * FROM tableA a LEFT OUTER JOIN tableB b ON a.key=b.key WHERE [conditions];
SELECT * FROM tableA a LEFT OUTER JOIN tableB b USING (key) WHERE [conditions];

取得還沒有開課的老師

SELECT * FROM teacher t LEFT OUTER JOIN classmain c ON t.tid=c.tid
WHERE c.tid  IS NULL;

或是

SELECT * FROM teacher t LEFT JOIN classmain c ON t.tid=c.tid
WHERE c.tid  IS NULL;

或是

SELECT * FROM teacher t LEFT JOIN classmain c USING (tid)
WHERE c.tid  IS NULL;


如果都不使用JOIN也可以寫成 ...
select tid from teacher where tid not in (select t.tid from teacher t, classmain c where c.tid=t.tid);



如果我們使用RIGHT OUTER JOIN,就是取得標示(3)的部分,語法如下:

[RIGHT OUTER JOIN/RIGHT JOIN]
SELECT * FROM tableA a RIGHT OUTER JOIN tableB b ON a.key=b.key WHERE [conditions];
SELECT * FROM tableA a RIGHT OUTER JOIN tableB b USING (key) WHERE [conditions];

取得還沒有老師的課程

SELECT * FROM teacher t RIGHT OUTER JOIN classmain c ON t.tid=c.tid
WHERE c.tid  IS NULL;

或是

SELECT * FROM teacher t RIGHT JOIN classmain c ON t.tid=c.tid
WHERE c.tid  IS NULL;

或是

SELECT * FROM teacher t RIGHT JOIN classmain c USING t.tid=c.tid
WHERE c.tid  IS NULL;


如果都不使用JOIN也可以寫成 ...
select cid from classmain where tid is null or tid not in (select tid from teacher);


如果我們使用FULL OUTER JOIN (因為MYSQL沒有支援),使用RIGHT JOIN跟LEFT JOIN進行UNION,語法如下:

SELECT * FROM teacher t RIGHT JOIN classmain c ON t.tid=c.tid
WHERE c.tid  IS NULL
UNION
SELECT * FROM teacher t LEFT JOIN classmain c ON t.tid=c.tid
WHERE c.tid  IS NULL;

取得還沒有開課的老師, 以及還沒有老師的課程

-- 除法 (Division),以第一個表格當被除數,以第二個表格當除數。

第一個關聯表格當作是「被除表」,第二個關聯表格當作是「除 表」。此運算是在關聯表格 R1 中找出包含關聯表格 R2 中屬性值 的值組。



留言

這個網誌中的熱門文章

如何使用EXCEL連接MYSQL

關聯模式的五大鍵 Super key、Candidate Key、Primary Key、Alternate Key、Foreign Key

SELECT SQL語法總整理