關聯查詢.

Presentation on theme: "關聯查詢."— Presentation transcript:

1 關聯查詢

2 JOIN語法 MySQL支持以下JOIN語法, 這些語法用於SELECT語句的table_references部分和多表DELETE和UPDATE語句： table_references: table_reference [, table_reference] … table_reference: table_factor | join_table

3 table_factor: tbl_name [[AS] alias] [{USE|IGNORE|FORCE} INDEX (key_list)] | ( table_references ) | { OJ table_reference LEFT OUTER JOIN table_reference ON conditional_expr }

4 join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition] | table_reference STRAIGHT_JOIN table_factor | table_reference STRAIGHT_JOIN table_factor ON condition | table_reference LEFT [OUTER] JOIN table_reference join_condition | table_reference NATURAL [LEFT [OUTER]] JOIN table_factor | table_reference RIGHT [OUTER] JOIN table_reference join_condition | table_reference NATURAL [RIGHT [OUTER]] JOIN table_factor

5 join_condition: ON conditional_expr | USING (column_list)
與SQL標準相比，table_factor的語法被擴展了。SQL標準只接受table_reference，而不是圓括号内的一系列項目。

6 CROSS JOIN/INNER JOIN 在MySQL中，CROSS JOIN与INNER JOIN等同, 兩者可以互相替换。
在標準SQL中，两者是不等同的。INNER JOIN與ON子句同時使用，CROSS JOIN以其它方式使用。

7 SELECT. FROM t1 LEFT JOIN (t2, t3, t4) ON (t2. a=t1. a AND t3. b=t1
SELECT * FROM t1 LEFT JOIN (t2, t3, t4)   ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c) 相當於： SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)  ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c) …此為正規之 Join指令

8 SELECT t1. name, t2. salary FROM employee AS t1, info AS t2 WHERE t1
SELECT t1.name, t2.salary FROM employee AS t1, info AS t2  WHERE t1.name = t2.name;·  SELECT t1.name, t2.salary FROM employee t1, info t2  WHERE t1.name = t2.name; ON條件句是可以被用於WHERE子句的格式的任何條件。 A table reference can be aliased using tbl_name AS alias_name or tbl_name alias_name:      

9 如果對於在LEFT JOIN中的ON或USING部分中的右表没有匹配的紀錄，则對應該欄之所有列被设置为NULL再套用於右表。
如果一个表在其它表中没有对应部分，您可以使用这种方法在这种表中查找紀錄： SELECT table1.* FROM table1   LEFT JOIN table2 ON table1.id = table2.id  WHERE table2.id IS NULL;

10 USING(column_list)子句
用於為一系列的列進行命名。若這些列必同時在两个表中存在。 EX: 如果表a和表b都包含列c1, c2和c3，则以下联合会对比来自两个表的对应的列：     a LEFT JOIN b USING (c1,c2,c3)

11 Some join examples SELECT * FROM table1, table2;
SELECT * FROM table1 INNER JOIN table2 ON table1.id=table2.id; SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id; SELECT * FROM table1 LEFT JOIN table2 USING (id); SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id LEFT JOIN table3 ON table2.id=table3.id;

12 INNER JOIN和，（逗号）在無联合條件下是語意相同的：两者都可以对指定的表计算出笛卡儿乘積（也就是说，第一个表中的每一行被联合到第二个表中的每一行）。
  RIGHT JOIN的作用与LEFT JOIN的作用類似。要使指令可以在資料庫内移植，建議使用LEFT JOIN代替RIGHT JOIN。 STRAIGHT_JOIN与JOIN相同。除了有一点不一樣，左表会在右表之前被讀取。STRAIGH_JOIN可以被用於這樣的情况，即联合优化符合以錯誤的顺序排列表。

13 USE INDEX（key_list） 指定USE INDEX（key_list）[索引名稱]，可以告知MySQL只使用一个索引来查找表中的行。 另一种语法IGNORE INDEX（key_list）可以被用于告知MySQL不要使用某些特定的索引。 如果EXPLAIN顯示MySQL正在使用来自索引清单中的錯誤索引时，這些提示会有用处。 您也可以使用FORCE INDEX，其作用接近USE INDEX（key_list），不过增加了一项作用，一次表掃描被假设为代价很高。换句话说，只有当無法使用一个给定的索引来查找表中的行時，才使用表掃描。 USE KEY、IGNORE KEY和FORCE KEY是USE INDEX、IGNORE INDEX和FORCE INDEX的同义词。

14 下列SQL等效 a LEFT JOIN b USING (c1,c2,c3)
a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3

15 常用範例 SELECT * FROM table1,table2 WHERE table1.id = table2.id;
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id; SELECT * FROM table1 LEFT JOIN table2 USING (id); SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id  LEFT JOIN table3 ON table2.id = table3.id; SELECT * FROM table1 USE INDEX (key1,key2)  WHERE key1=1 AND key2=2 AND key3=3; SELECT * FROM table1 IGNORE INDEX (key3) WHERE key1=1 AND key2=2 AND key3=3;

16 SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);
在舊版本中，SELECT是合法的，因为t1, t2被隱含地歸為(t1,t2)。新版之JOIN取得了优先权，因此用於ON子句的操作方式是t2和t3。因为t1.i1不是任何一个操作数中的列，所以结果是出现在‘on clause’中有未知列‘t1.i1’的錯誤。 要使联合可以被正常處理，使用圆括号把前两个表明確地歸為一组，這樣用于ON子句的操作則改為(t1,t2)和t3： SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3); 本变更也适用于INNER JOIN，CROSS JOIN，LEFT JOIN和RIGHT JOIN。

17 Subquery語法 例子： SELECT * FROM t1 WHERE column1 = (SELECT c olumn1 FROM t2); 限制條件: 一个子查询的外部语句必须是以下语句之一： SELECT, INSERT, UPDATE, DELETE, SET或DO。 另一限制條件: 您不能在一个子查询中修改一个表，又在同一个表中选择。此點也適用于DELETE, INSERT, REPLACE和UPDATE语句。

18 應用範例 SELECT column1 FROM t1WHERE column1 = (SELECT MAX(column2) FROM t2); SELECT * FROM t1 AS tWHERE 2 = (SELECT COUNT(*) FROM t1 WHERE t1.id = t.id);

19 A table_subquery is also known as a subquery in the FROM clause
A table_subquery is also known as a subquery in the FROM clause. Such subqueries must include an alias to give the subquery result a table name SELECT * FROM (SELECT 1, 2, 3) AS t1;

20 使用ANY, IN和SOME比較 SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2); SELECT s1 FROM t1 WHERE s1 IN    (SELECT s1 FROM t2); 不过，NOT IN不是<> ANY的别名，但是是<> ALL的别名。

21 SELECT s1 FROM t1 WHERE s1 <> ANY  (SELECT s1 FROM t2);SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2); 使用詞語SOME的机会很少，但是本例顯示了为什么SOME是有用的。 对于多数人来说，英語短語“a is not equal to any b”的意思是“没有一个b与a相等”，但是在SQL語法中則是“有部分b与a不相等”。使用<> SOME有助于確認每个人都理解該查询的真正含义。

22 使用ALL進行子查询 SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);
假设表1中有一行包含（10）。如果表t2包含（-5，0，＋5），则表达式为TRUE，因为10比t2中的所有三个值都大。 如果表t2包含（12，6，NULL，－100），则表達式为FALSE，因为表t2中有一个值12大于10。 如果表t2包含（0，NULL，1），则表達式为unknown。

23 行子查询 SELECT * FROM t1 WHERE (1,2) = (SELECT column1, column2 FROM t2);
SELECT * FROM t1 WHERE ROW(1,2) = (SELECT column1, column2 FROM t2); SELECT * FROM t1 WHERE (column1,column2) = (1,1);  SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;ELECT column1, column2 FROM t2);(此敘述可被優化)

24 SELECT column1,column2,column3FROM t1 WHERE (column1,column2,column3) IN(SELECT column1,column2,column3 FROM t2);

25 EXISTS和NOT EXISTS SELECT DISTINCT store_type FROM stores  WHERE EXISTS (SELECT * FROM cities_stores WHERE cities_stores.store_type = stores.store_type); SELECT DISTINCT store_type FROM stores WHERE NOT EXISTS (SELECT * FROM cities_stores  WHERE cities_stores.store_type = stores.store_type);

26 資料轉檔應用 使用專案巨集指令 轉檔前養成備份資料之好習慣

27 檢查異動檔與主控檔鍵值是否不吻合 若須檢查異動檔與主控檔鍵值是否有不吻合之情形者，可加入下述指令範例來檢查兩造鍵值。
Insert Into 主控檔 (鍵值欄) Select 鍵值欄 From updTable Where (鍵值欄) Not In (Select 鍵值欄 From 主控檔 As objTable); 其中updTable為異動檔，後半之子查詢資料來源若與前半之資料表相同，則必須取別名(例如objTable) 。

28 出貨轉銷貨訂單檔範例 (1) 建立暫存檔, 將出貨檔依訂單編號及產品編號小計
Create Temporary Table updTable Select spbill.OD_Blno, splist.PD_No, Sum(splist.SP_Qty) As SP_Qty From spbill Join splist Where spbill.SP_Blno=splist.SP_Blno And splist.CL_Note<>"T“ Group By spbill.OD_Blno, splist.PD_No;

29 出貨轉銷貨訂單檔範例(2) 將相關檔案上鎖 Lock Table updTable Read, spodlst Write, splist Write;

30 出貨轉銷貨訂單檔範例(3) 更新訂貨明細表之已出貨數量
Update spodlst join updTable On spodlst.OD_Blno = updTable.OD_Blno And spodlst.PD_No= updTable.PD_No Set spodlst.sp_qty = spodlst.sp_qty + updTable.sp_qty;

31 出貨轉銷貨訂單檔範例(4) 更新出貨明細表: CL_Note 欄位加註 “T” 資料表解鎖 刪除暫存表
Update splist Set splist.CL_Note ="T" Where splist.CL_Note<>"T"; 資料表解鎖 Unlock Tables; 刪除暫存表 Drop Temporary Table updTable;