mysql多表查詢的幾種分類詳細

2022-02-21 16:00:10

分類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：多個連線條件與 AND 操作符

拓展2：區分重複的列名

多個表中有相同列時，必須在列名之前加上表名字首。
在不同表中具有相同列名的列可以用表名加以區分。

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;

需要注意的是，如果我們使用了表的別名，在查詢欄位中、過濾條件中就只能使用別名進行代替，不能使用原有的表名，否則就會報錯。

阿里開發規範：

【強制】對於資料庫中表記錄的查詢和變更，只要涉及多個表，都需要在列名前加表的別名（或表名）進行限定。

說明：對多表進行查詢記錄、更新記錄、刪除記錄時，如果對操作列沒有限定表的別名（或表名），並且操作列在多個表中存在時，就會拋異常。

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

反例：在某業務中，由於多表關聯查詢語句沒有加表的別名（或表名）的限制，正常執行兩年後，最近在某個表中增加一個同名欄位，在預釋出環境做資料庫變更後，線上查詢語句出現出 1052 異常：Column ‘name’ in field list is ambiguous。

拓展4：連線多個表

總結：連線 n個表,至少需要n-1個連線條件。 比如，連線三個表，至少需要兩個連線條件。

練習：查詢出公司員工的 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 非自連線

當table1和table2本質上是同一張表，只是用取別名的方式虛擬成兩張表以代表不同的意義。然後兩個表再進行內連線，外連線等查詢。

題目：查詢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：內連線 vs 外連線

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

內連線：合併具有同一列的兩個以上的表的行, 結果集中不包含一個表與另一個表不匹配的行

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

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

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

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

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

總結：

內連線: 合併具有同一列的兩個以上的表的行, 結果集中不包含一個表與另一個表不匹配的行。

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

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

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

到此這篇關於mysql多表查詢的幾種分類詳細的文章就介紹到這了,更多相關mysql多表查詢內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！