MySQL（九）連接配接查詢（多表查詢）

索引：

連接配接查詢、多表查詢、笛卡爾乘積

一、連接配接查詢

連接配接查詢，又稱之為多表查詢，當查詢的字段來自于多個表的時候，就會用到連接配接查詢。

為了我們的示例，我們先建立一個資料庫，并建立兩張表，分别為beauty和boys。我把表的内容放在文章末尾的附錄上。

笛卡爾乘積

我們可以做一個查詢，

SELECT 
  name,
  boyName 
FROM
  boys,
  beauty ;
           

我們看一下結果：

我們可以看到，結果總共有48行。

我們進行簡單的觀察，不難發現，beauty中的每一個name都對應了所有的boyName，也就是相當于：

這種現象，就叫做笛卡爾乘積。假設表1有m行，表2有n行，那麼這麼查詢的結果就有m*n行，這樣就形成了我們看到的48行結果。出現這種現象的原因就是沒有添加有效的連接配接條件。

是以我們應該在多表查詢中添加連接配接條件，限制組合的出現形式，例如我們希望beauty表中的boyfriend_id和boys表中的id一樣時才比對，我們就可以這麼寫：

SELECT 
  NAME AS 女,
  boyName AS 男朋友 
FROM
  boys,
  beauty 
WHERE beauty.boyfriend_id = boys.id ;
           

我們使用

表名.字段

的形式來确定是哪一張表的字段，結果如下：

連接配接查詢的分類

我們可以根據不同的标準對連接配接查詢進行不同的分類。

• 根據年代進行分類：

  分為SQL92标準和SQL99标準。

  推薦使用SQL99标準。

• 還可以根據功能進行分類，分為内連接配接，外連接配接，交叉連接配接。

  其中，内連接配接又分為等值連接配接，非等值連接配接，自連接配接。

  外連接配接又分為左外連接配接，右外連接配接，全外連接配接。

其中的SQL92标準僅僅支援内連接配接和部分外連接配接。

SQL99标準支援内連接配接、部分外連接配接（左外連接配接+右外連接配接）和交叉連接配接。

二、SQL92标準

SQL92标準支援所有的内連接配接。

等值連接配接

我們舉幾個例子：

• 查詢女神名字和對應的男朋友的名字。

  SELECT 
    name AS 女,
    boyName AS 男朋友 
  FROM
    boys,
    beauty 
  WHERE beauty.boyfriend_id = boys.id ;
             

  結果為：

  

• 查詢員工名和對應的部門名稱。

  SELECT 
    last_name AS NAME,
    department_name AS 部門名稱 
  FROM
    departments,
    employees 
  WHERE employees.department_id = departments.department_id ;
             

  結果為：

  

• 查詢員工名，以及對應的工種号、工種名。

  SELECT 
    employees.last_name,
    employees.job_id,
    jobs.job_title 
  FROM
    employees,
    jobs 
  WHERE employees.job_id = jobs.job_id ;
             

  我們也是使用

  表名.字段名

  的形式來區分是哪一個表的相應字段。

  同樣我們可以給表起别名，然後使用别名進行區分：

  SELECT 
    e.last_name,
    e.job_id,
    j.job_title 
  FROM
    employees AS e,
    jobs AS j 
  WHERE e.job_id = j.job_id ;
             

  結果都是一樣的：

  

1. 添加篩選條件

• 查詢有獎金的員工的名字和部門名

  SELECT 
    last_name,
    department_name,
    commission_pct 
  FROM
    employees,
    departments 
  WHERE employees.department_id = departments.department_id 
    AND employees.commission_pct IS NOT NULL ;
             

  結果：

  

• 查詢城市名中第二個字元為“o”的部門名和城市名。

  SELECT 
    department_name,
    city
  FROM
    departments,
    locations 
  WHERE departments.location_id = locations.location_id 
    AND city LIKE '_o%' ;
             

  結果：

  

2. 添加分組條件

• 查詢每個城市的部門個數。

  SELECT 
    COUNT(*),
    city 
  FROM
    departments d,
    locations l 
  WHERE d.location_id = l.location_id 
  GROUP BY city ;
             

  結果：

  

• 查詢有獎金的每個部門的部門名和各部門的上司編号和該部門的最低工資。

  SELECT 
    department_name,
    d.manager_id,
    MIN(salary) 
  FROM
    employees e,
    departments d 
  WHERE e.department_id = d.department_id 
    AND commission_pct IS NOT NULL 
  GROUP BY department_name,
    d.manager_id ;
             

  結果：

  

3. 添加排序條件

• 查詢每個工種的工種名和員工的個數，并且按員工個數降序排序。

  SELECT 
    job_title,
    COUNT(*) 
  FROM
    jobs,
    employees 
  WHERE employees.job_id = jobs.job_id 
  GROUP BY job_title 
  ORDER BY COUNT(*) DESC ;
             

  結果：

  

4. 三表連接配接

• 查詢員工名、部門名和所在城市

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

  結果：

  

小結

我們可以總結出一些特點。

1. 多表連接配接的結果為多表的交集部分。
2. n表連接配接，至少需要n-1個連接配接條件。
3. 多表的順序沒有要求。
4. 一般需要對表起别名。
5. 多表連接配接可以搭配我們前面學習的查詢子句使用。

非等值連接配接

示例：查詢員工的工資和工資級别

首先先建立一個工資級别表：

CREATE TABLE job_grades
(grade_level VARCHAR(3),
 lowest_sal  int,
 highest_sal int);

INSERT INTO job_grades
VALUES ('A', 1000, 2999);

INSERT INTO job_grades
VALUES ('B', 3000, 5999);

INSERT INTO job_grades
VALUES('C', 6000, 9999);

INSERT INTO job_grades
VALUES('D', 10000, 14999);

INSERT INTO job_grades
VALUES('E', 15000, 24999);

INSERT INTO job_grades
VALUES('F', 25000, 40000);
           

如下：

查詢SQL：

SELECT
	last_name,
	salary,
	grade_level 
FROM
	employees e,
	job_grades j 
WHERE
	e.salary BETWEEN j.lowest_sal AND j.highest_sal
           

結果：

三、自連接配接

自連接配接就是自己連接配接自己，這個情況比較特殊，比較少見。

例如：

• 查詢員工的姓名，以及該員工上司的姓名。

  

  SELECT
  	e1.employee_id AS 員工ID,
  	e1.last_name AS 員工姓名,
  	e2.employee_id AS 上司ID,
  	e2.last_name AS 上司姓名 
  FROM
  	employees AS e1,
  	employees AS e2 
  WHERE
  	e1.manager_id = e2.employee_id
             

  結果：

  

附錄

這是我們的示例用的兩張表的内容，我們可以仿照這個形式自行建立。夠我們自己練習使用就好。

• beauty：

  

• boys：