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：