MySQL（九）连接查询（多表查询）

悠悠 2021-06-24 16:12 519阅读 0赞

> 索引：  
> 连接查询、多表查询、笛卡尔乘积

# 一、连接查询 #

连接查询，又称之为多表查询，当查询的字段来自于多个表的时候，就会用到连接查询。

**为了我们的示例，我们先建立一个数据库，并建立两张表，分别为beauty和boys。我把表的内容放在文章末尾的附录上。**

## 笛卡尔乘积 ##

我们可以做一个查询，

SELECT 
      name,
      boyName 
    FROM
      boys,
      beauty ;

我们看一下结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ1NjUxMjc_size_16_color_FFFFFF_t_70]  
我们可以看到，结果总共有48行。  
我们进行简单的观察，不难发现，beauty中的每一个name都对应了所有的boyName，也就是相当于：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ1NjUxMjc_size_16_color_FFFFFF_t_70 1]  
这种现象，就叫做**笛卡尔乘积**。假设表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 ;

我们使用`表名.字段`的形式来确定是哪一张表的字段，结果如下：  
![在这里插入图片描述][2019061509531522.png]

## 连接查询的分类 ##

我们可以根据不同的标准对连接查询进行不同的分类。

*  根据年代进行分类：  
    分为**SQL92标准**和**SQL99标准**。  
    推荐使用SQL99标准。
 *  还可以根据功能进行分类，分为**内连接**，**外连接**，**交叉连接**。  
    其中，内连接又分为等值连接，非等值连接，自连接。  
    外连接又分为左外连接，右外连接，全外连接。

其中的SQL92标准仅仅支持内连接和部分外连接。  
SQL99标准支持内连接、部分外连接（左外连接+右外连接）和交叉连接。

--------------------

# 二、SQL92标准 #

SQL92标准支持所有的**内连接**。

## 等值连接 ##

我们举几个例子：

*  **查询女神名字和对应的男朋友的名字。**
    
        SELECT 
          name AS 女,
          boyName AS 男朋友 
        FROM
          boys,
          beauty 
        WHERE beauty.boyfriend_id = boys.id ;
    
    结果为：  
    ![在这里插入图片描述][20190615102004232.png]
 *  **查询员工名和对应的部门名称。**
    
        SELECT 
          last_name AS NAME,
          department_name AS 部门名称 
        FROM
          departments,
          employees 
        WHERE employees.department_id = departments.department_id ;
    
    结果为：  
    ![在这里插入图片描述][20190615102700738.png]
 *  **查询员工名，以及对应的工种号、工种名。**
    
        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 ;
    
    结果都是一样的：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ1NjUxMjc_size_16_color_FFFFFF_t_70 2]

### 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 ;
    
    结果：  
    ![在这里插入图片描述][20190618192402451.png]
 *  查询城市名中第二个字符为“o”的部门名和城市名。
    
        SELECT 
          department_name,
          city
        FROM
          departments,
          locations 
        WHERE departments.location_id = locations.location_id 
          AND city LIKE '_o%' ;
    
    结果：  
    ![在这里插入图片描述][20190618192855386.png]

### 2. 添加分组条件 ###

*  查询每个城市的部门个数。
    
        SELECT 
          COUNT(*),
          city 
        FROM
          departments d,
          locations l 
        WHERE d.location_id = l.location_id 
        GROUP BY city ;
    
    结果：  
    ![在这里插入图片描述][20190618193503678.png]
 *  查询有奖金的每个部门的部门名和各部门的领导编号和该部门的最低工资。
    
        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 ;
    
    结果：  
    ![在这里插入图片描述][20190618194538655.png]

### 3. 添加排序条件 ###

*  查询每个工种的工种名和员工的个数，并且按员工个数降序排序。
    
        SELECT 
          job_title,
          COUNT(*) 
        FROM
          jobs,
          employees 
        WHERE employees.job_id = jobs.job_id 
        GROUP BY job_title 
        ORDER BY COUNT(*) DESC ;
    
    结果：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ1NjUxMjc_size_16_color_FFFFFF_t_70 3]

### 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 ;
    
    结果：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ1NjUxMjc_size_16_color_FFFFFF_t_70 4]

### 小结 ###

我们可以总结出一些特点。

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);

如下：  
![在这里插入图片描述][20200731105040481.png]

查询SQL：

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

结果：  
![在这里插入图片描述][2020073110551284.png]

# 三、自连接 #

自连接就是自己连接自己，这个情况比较特殊，比较少见。

例如：

*  查询员工的姓名，以及该员工领导的姓名。  
    ![在这里插入图片描述][2020073111082636.png]
    
        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
    
    结果：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ1NjUxMjc_size_16_color_FFFFFF_t_70 5]

--------------------

# 附录 #

这是我们的示例用的两张表的内容，我们可以仿照这个形式自行建立。够我们自己练习使用就好。

*  beauty：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ1NjUxMjc_size_16_color_FFFFFF_t_70 6]
 *  boys：  
    ![在这里插入图片描述][20190615093228553.png]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ1NjUxMjc_size_16_color_FFFFFF_t_70]: /images/20210615/5031a921220d4615b28b2a62a2acba2f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ1NjUxMjc_size_16_color_FFFFFF_t_70 1]: /images/20210615/61d711053dff4ba4bf82d78e8cd43a66.png
[2019061509531522.png]: /images/20210615/7b49130a8cad479dabee1614267a473c.png
[20190615102004232.png]: /images/20210615/5a70b759464648bfba2f444989d5c7ff.png
[20190615102700738.png]: /images/20210615/2a0366e57b3749b296957bebe459b1b7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ1NjUxMjc_size_16_color_FFFFFF_t_70 2]: /images/20210615/0ded05e2f181405893ce62083197e6df.png
[20190618192402451.png]: /images/20210615/95b8708f7a0f4667a21afce1631297cb.png
[20190618192855386.png]: /images/20210615/90c99f58c3054a34a61bdbe42c89b77b.png
[20190618193503678.png]: /images/20210615/543adc454db14ea6b3d3c4977c71c021.png
[20190618194538655.png]: /images/20210615/ea2729e2abd94b3d943c155dc4a0628b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ1NjUxMjc_size_16_color_FFFFFF_t_70 3]: /images/20210615/e4d62be2d78c467ba86291bd5d9b8ce7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ1NjUxMjc_size_16_color_FFFFFF_t_70 4]: /images/20210615/b251a9b124ba49939d25457cc64abde4.png
[20200731105040481.png]: /images/20210615/700717f1350747feba77606ad5616e22.png
[2020073110551284.png]: /images/20210615/ee806a06276e4d5d8d279f1d8242d740.png
[2020073111082636.png]: /images/20210615/083fbcd471ea4e20ac1a619dea5cb2fd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ1NjUxMjc_size_16_color_FFFFFF_t_70 5]: /images/20210615/b5944008c1b043e29a11571d5e00209e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ1NjUxMjc_size_16_color_FFFFFF_t_70 6]: /images/20210615/c4c4b07f9b82411eaa3eff45b61cd456.png
[20190615093228553.png]: /images/20210615/0bdc33655d334648bd8c95e054972195.png