MySQL学习笔记（三）多表连接查询

当查询字段来自于多个表时，就会用到连接查询

笛卡尔乘积：表 1 有 m 行，表 2 有 n 行，结果有 m*n 行

一、分类

按年代分类：
SQL92标准：仅支持内连接
SQL99标准（推荐）：支持内连接+外连接（左外和右外）+交叉连接

按功能分类：
内连接：等值连接、非等值连接、自连接
外连接：左外连接、右外连接
交叉连接

二、SQL92标准

1、等值连接

多表等值连接的结果为多表的交集部分
n表连接，至少需要n-1个连接条件
多表的顺序没有要求
一般需要为表起别名

例：
查询员工名和对应的部门名

select last_name,department_name 
from employees e,departments d
where e.department_id=d.department_id;

添加筛选：查询有奖金的员工名、部门名

select last_name,department_name,commission_pct
from employees e,departments d
where e.department_id=d.department_id
and e.commission_pct is not null;

三表连接：查询员工名，部门名和所在的城市

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;

2、非等值连接

例：查询员工的工资和工资级别

select salary,grade_level
from employees e,job_grades g
where salary between g.lowest_sal and g.highest_sal;

3、自连接

例：查询员工名和上级的名称

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标准

1999年推出的sql语法
通过join关键字实现连接
优点：语句上，连接条件和筛选条件实现了分离，简洁明了。

语法：

select 字段1，字段2, ...
from 表1
【inner|left outer|right outer|cross】join 表2 on  连接条件
【inner|left outer|right outer|cross】join 表3 on  连接条件
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选条件】
【order by 排序的字段或表达式】

1、内连接(★)：inner

语法：

SELECT 查询列表
FROM 表1 别名
INNER JOIN 表2 别名
ON 连接条件;

特点：
inner 可以省略
筛选条件放在where 后面，连接条件放在on 后面，提高分离性，便于阅读
inner join 连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

分类：
1）等值连接
例：
查询名字中包含e的员工名、工种名

SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id`=  j.`job_id`
WHERE e.`last_name` LIKE '%e%';

查询员工名、部门名、工种名、并按部门降序（添加三表连接）

SELECT last_name,department_name,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 department_name DESC;

2）非等值连接
例：查询员工工资级别的个数>20的个数，并按照工资级别降序

SELECT COUNT(*),grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;

3）自连接
例：查询姓名中包含字符k的员工的名字、上级的名字

SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.`manager_id`= m.`employee_id`
WHERE e.`last_name` LIKE '%k%';

2、外连接

应用场景：用于查询一个表中有，另一个表没有的记录

特点：
外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null

例：查询哪个部门没有员工
左外(★):left 【outer】

SELECT d.*,e.employee_id
FROM departments d
LEFT  JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;

右外(★)：right 【outer】

SELECT d.*,e.employee_id 
FROM employees e
RIGHT  JOIN departments d
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;

3、交叉连接：cross

得到两个表的乘积，也叫笛卡尔乘积
第一个表中的每个行与第二个表中的每个行配对

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