MySQL初级-8-连接查询-SQL99标准

本文最后更新于:2020年7月23日 晚上

mysql Ver 14.14 Distrib 5.5.62

文中案例所用数据库下载

9.1 语法

SELECT 查询列表
FROM1 别名 
[连接类型] JOIN2 别名
ON 连接条件
[WHERE 筛选条件]
[GROUP BY 分组]
[HAVING 筛选条件]
[ORDER BY 排序列表]

9.2 分类

  1. 内连接:INNER。

  2. 外连接

    左外连接:LEFT(OUTER)。

    右外连接:RIGHT(OUTER)。

    全外连接:FULL(OUTER)。–MySQL中不支持。

  3. 交叉连接:CROSS。

9.3 内连接

9.3.1 特点

  1. 允许添加排序、分组、筛选等子句。
  2. INNER关键字可省略。
  3. 筛选条件放在WHERE后,连接条件放在ON后,使功能语句分离,便于阅读。
  4. INNER JOIN连接和SQL92语法中的等值连接效果是一样的,都是查询多表的交集部分。

9.3.2 等值连接

  1. 【基本使用】查询员工名、部门名。

    SELECT e.last_name 员工名,d.department_name 部门名
    FROM employees e
    INNER JOIN departments d
    ON e.`department_id`=d.`department_id`;
  2. 【添加筛选】查询名字中包含字母e的员工名和工种名。

    SELECT e.last_name 员工名,j.job_title 工种名
    FROM employees e
    INNER JOIN jobs j
    ON e.`job_id`=j.`job_id`
    WHERE e.`last_name` LIKE "%e%";
  1. 【添加分组、筛选】查询部门个数大于3的城市名和部门个数。

    SELECT l.city 城市名,COUNT(*) 部门个数
    FROM locations l
    INNER JOIN departments d
    ON l.`location_id`=d.`location_id`
    GROUP BY l.`city`
    HAVING 部门个数>3;
  1. 【添加排序】查询员工个数大于3的部门名和员工个数,并按照员工个数降序。

    SELECT d.department_name 部门名,COUNT(*) 员工个数
    FROM departments d
    INNER JOIN employees e
    ON d.`department_id`=e.`department_id`
    GROUP BY d.`department_id`
    HAVING 员工个数>3
    ORDER BY 员工个数 DESC;
  1. 【三表连接】查询员工名、部门名、工种名。

    SELECT e.last_name 员工名,d.department_name 部门名,j.job_title 工种名
    FROM employees e
    INNER JOIN departments d
    ON e.`department_id`=d.`department_id`
    INNER JOIN jobs j
    ON e.`job_id`=j.`job_id`
    ORDER BY 部门名 DESC;

9.3.3 非等值连接

  1. 查询员工名和对应的工资级别。

    SELECT e.last_name 员工名, j.grade_level 工资级别
    FROM job_grades j
    INNER JOIN employees e
    ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;
  1. 查询工资级别中员工数大于20的级别,并按照级别降序。

    SELECT j.grade_level 工资级别,COUNT(*) 员工数
    FROM job_grades j
    INNER JOIN employees e
    ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`
    GROUP BY 工资级别
    HAVING 员工数>20
    ORDER BY 工资级别 DESC;

9.3.4 自连接

  1. 查询员工名和上级的名字。

    SELECT e1.last_name 员工名,e2.last_name 领导名
    FROM employees e1
    INNER JOIN employees e2
    ON e1.`manager_id`=e2.`employee_id`;
  1. 查询员工名中包含字母z的员工名和上级的名字。

    SELECT e1.last_name 员工名,e2.last_name 领导名
    FROM employees e1
    INNER JOIN employees e2
    ON e1.`manager_id`=e2.`employee_id`
    WHERE e1.`last_name` LIKE "%z%";

9.4 外连接

9.4.1 用途

用于查询一个表中有,另一个表中没有的数据记录。

9.4.2 特点

  1. 外连接的查询结果为主表中的所有记录。

    如果从表中有和它匹配的,则显示匹配的值。

    如果从表中没有和它匹配的,则显示NULL。

    外连接查询结果=内连接结果+主表中有而从表中没有的记录。

  2. 左外连接,LEFT JOIN左边的是主表。

  3. 右外连接,RIGHT JOIN右边的是主表。

  4. 全外连接=内连接的结果+表1中有但表2中没有的+表2中有但表1中没有的。

9.4.3 左外连接

  1. 查询男友不在男神表的女神名。

    SELECT b.`name` 女神
    FROM beauty b
    LEFT OUTER JOIN boys bo
    ON b.`boyfriend_id`=bo.`id`
    WHERE bo.`id` IS NULL;
  2. 查询哪个部门 没有员工。

    SELECT d.department_name 部门
    FROM departments d
    LEFT OUTER JOIN employees e
    ON d.`department_id`=e.`department_id`
    WHERE e.`department_id` IS NULL;

9.4.4 右外连接

  1. 查询男友不在男神表的女神名。

    SELECT b.`name` 女神
    FROM boys bo
    RIGHT OUTER JOIN beauty b
    ON b.`boyfriend_id`=bo.`id`
    WHERE bo.`id` IS NULL;
  2. 查询哪个部门 没有员工。

    SELECT d.department_name 部门
    FROM employees e
    RIGHT OUTER JOIN departments d
    ON d.`department_id`=e.`department_id`
    WHERE e.`department_id` IS NULL;

9.5 交叉连接

相当于笛卡尔乘积。

笛卡尔乘积现象:表1中有m行,表2中有n行,结果为m*n行。

  1. 女神表和男神表交叉连接。

    SELECT b.*,bo.*
    FROM beauty b
    CROSS JOIN boys bo;

版权声明:自由转载-非商用-非衍生-保持署名 Creative Commons BY-NC-ND 3.0 协议,转载请注明出处!

 目录