多表查詢分類

分類1：等值連接 vs 非等值連接

1. 等值連接

SELECT employees.employee_id, employees.last_name, 
       employees.department_id, departments.department_id,
       departments.location_id
FROM   employees, departments
WHERE  employees.department_id = departments.department_id;

拓展1：多個(gè)連接條件與 AND 操作符

拓展2：區(qū)分重復(fù)的列名

• 多個(gè)表中有相同列時(shí)，必須在列名之前加上表名前綴。
• 在不同表中具有相同列名的列可以用表名加以區(qū)分。

SELECT employees.last_name, departments.department_name,employees.department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

拓展3：表的別名

• 使用別名可以簡化查詢。
• 列名前使用表名前綴可以提高查詢效率。

SELECT e.employee_id, e.last_name, e.department_id,
       d.department_id, d.location_id
FROM   employees e , departments d
WHERE  e.department_id = d.department_id;

需要注意的是，如果我們使用了表的別名，在查詢字段中、過濾條件中就只能使用別名進(jìn)行代替，不能使用原有的表名，否則就會(huì)報(bào)錯(cuò)。

阿里開發(fā)規(guī)范：

【強(qiáng)制】對(duì)于數(shù)據(jù)庫中表記錄的查詢和變更，只要涉及多個(gè)表，都需要在列名前加表的別名（或 表名）進(jìn)行限定。

說明：對(duì)多表進(jìn)行查詢記錄、更新記錄、刪除記錄時(shí)，如果對(duì)操作列沒有限定表的別名（或表名），并且操作列在多個(gè)表中存在時(shí)，就會(huì)拋異常。

正例：select t1.name from table_first as t1 , table_second as t2 where t1.id=t2.id;

反例：在某業(yè)務(wù)中，由于多表關(guān)聯(lián)查詢語句沒有加表的別名（或表名）的限制，正常運(yùn)行兩年后，最近在 某個(gè)表中增加一個(gè)同名字段，在預(yù)發(fā)布環(huán)境做數(shù)據(jù)庫變更后，線上查詢語句出現(xiàn)出 1052 異常：Column ‘name’ in field list is ambiguous。

拓展4：連接多個(gè)表

總結(jié)：連接 n個(gè)表,至少需要n-1個(gè)連接條件。 比如，連接三個(gè)表，至少需要兩個(gè)連接條件。

練習(xí)：查詢出公司員工的 last_name,department_name, city

SELECT e.last_name,d.department_name,l.city
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id

2. 非等值連接

SELECT e.last_name, e.salary, j.grade_level
FROM   employees e, job_grades j
WHERE  e.salary BETWEEN j.lowest_sal AND j.highest_sal;
或
WHERE e.salary >= j.lowest_sal AND e.salary <= j.highest_sal;

分類2：自連接 vs 非自連接

當(dāng)table1和table2本質(zhì)上是同一張表，只是用取別名的方式虛擬成兩張表以代表不同的意義。然后兩個(gè)表再進(jìn)行內(nèi)連接，外連接等查詢。

題目：查詢employees表，返回“Xxx works for Xxx”

SELECT CONCAT(worker.last_name ,' works for ' 
       , manager.last_name)
FROM   employees worker, employees manager
WHERE  worker.manager_id = manager.employee_id ;

自連接的例子：
查詢員工id,員工姓名及其管理者的id和姓名

SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
FROM employees emp ,employees mgr
WHERE emp.`manager_id` = mgr.`employee_id`;

分類3：內(nèi)連接 vs 外連接

除了查詢滿足條件的記錄以外，外連接還可以查詢某一方不滿足條件的記錄。

內(nèi)連接：合并具有同一列的兩個(gè)以上的表的行, 結(jié)果集中不包含一個(gè)表與另一個(gè)表不匹配的行

SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.department_id;  #只有106條記錄

外連接：合并具有同一列的兩個(gè)以上的表的行, 結(jié)果集中除了包含一個(gè)表與另一個(gè)表匹配的行之外，還查詢到了左表 或 右表中不匹配的行。

外連接的分類：左外連接、右外連接、滿外連接

左外連接：兩個(gè)表在連接過程中除了返回滿足連接條件的行以外還返回左表中不滿足條件的行，這種連接稱為左外連接。

右外連接：兩個(gè)表在連接過程中除了返回滿足連接條件的行以外還返回右表中不滿足條件的行，這種連接稱為右外連接。

總結(jié)：

內(nèi)連接: 合并具有同一列的兩個(gè)以上的表的行, 結(jié)果集中不包含一個(gè)表與另一個(gè)表不匹配的行。

外連接: 兩個(gè)表在連接過程中除了返回滿足連接條件的行以外還返回左（或右）表中不滿足條件的行 ，這種連接稱為左（或右） 外連接。沒有匹配的行時(shí), 結(jié)果表中相應(yīng)的列為空(NULL)。

如果是左外連接，則連接條件中左邊的表也稱為主表，右邊的表稱為從表。

如果是右外連接，則連接條件中右邊的表也稱為主表，左邊的表稱為從表。

到此這篇關(guān)于mysql多表查詢的幾種分類詳細(xì)的文章就介紹到這了,更多相關(guān)mysql多表查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論