Mysql多表连接查询

连接查询概述

• 又称为多表查询，当查询的字段来自于多个表时，就会用到连接查询。
• 笛卡尔乘积现象：表1有m行数据，表2有n行数据，结果有m*n行。
  • 原因：没有有效连接条件
  • 方法：添加有效的连接条件

分类

• 年代分类
  • sql192标准：仅支持内连接
  • sql199标准：所有内连接，外连接（不支持全外连接），交叉连接
• 功能
  • 内连接
    • 等值连接
    • 非等值连接
    • 自连接
  • 外连接
    • 左外连接
    • 右外连接
    • 全外连接
  • 交叉连接

sql192标准

等值连接

• 特点：

  • 多表等值连接的结果为多表的交集部分
  • n表连接，至少需要n-1个连接条件
  • 多表的顺序没有要求
  • 一般需要为表起别名
  • 可以搭配前面介绍的所有子句使用，如排序、分组、筛选

• 示例：

  • SELECT last_name, department_name
    FROM employees,departments
    WHERE employees.department_id = departments.department_id;
    

• 为表起别名

  • 提高语句的简洁度

  • 区分多个重名的字段

  • 注：如果为表起了别名，查询时的字段就不能使用原来的表名去限定

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

• 在连接查询中使用筛选条件

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

• 在连接查询中使用筛选条件

  • SELECT count(*) 个数, city
    FROM departments d, locations l
    WHERE d.location_id=l.location_id
    GROUP BY city;
    

• 在连接查询中使用排序

  • SELECT job_title, count(*) 员工个数
    FROM employees e, jobs j
    WHERE e.job_id = j.job_id
    GROUP BY job_title
    ORDER BY 员工个数 ASC;
    

• 三表连接

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

非等值连接

• 示例：

  • SELECT salary, grade_level
    FROM employees e, job_grades jg
    WHERE salary BETWEEN jg.lowest_sal AND jg.highest_sal;
    

自连接

• 示例

  • SELECT e.employee_id, e.last_name, m.employee_id, m.last_name
    FROM employees e, employees m
    WHERE e.manager_id=m.employee_id;
    

sql99语法

• 语法

  • SELECT 查询列表
    FROM 表1 别名 连接类型
    JOIN 表2 别名
    ON 连接条件
    ......
    

• 分类

  • 内连接：innner
    • 可添加排序、分组、筛选
    • innner可以省略
    • 筛选条件放在where后，连接条件放在on后，便于阅读。
    • innner join连接和sql192语法中的等值连接效果是一样的，都是查询多表的交集
  • 外连接：
    • 左外：left
    • 右外：right
    • 全外：full
    • 特点
      • 外连接的查询结果为主表中的所有记录，如果从表中有和他匹配的，则显示匹配的值，如果没有匹配的则显示null，外连接查询结果=内连接结果+主表中有二从表没有的记录。
      • 左外连接，left join左边的是主表
      • 右外连接，right join右边的是主表
      • 左外和右外交换两个表的顺序，可以实现同样的效果
      • 全外连接=内连接的结果+表1中有但表2没有的+表1中没有但表2中有的
  • 交叉连接：cross join
    • 笛卡尔乘积