链接:https://pan.baidu.com/s/1zAhDUNv-yuJiWmaFLvTk4w
提取码:ttst
提取码:ttst
专栏中有mysql的导入导出,里面有教如何导入
从这章开始使用新的数据库记得切换
文章目录
一、多表查询
为什么要多表查询?
练习:查询员工名为’Abel’的人在哪个城市工作
1.1 单查询
首先我需要在员工表中查询这个人的部门
SELECT * FROM employees WHERE last_name = 'Abel';
这时就查到了这个人的部门id
知道部门号后在从部门表中查询其地点id
SELECT * FROM departments WHERE department_id = 80;
知道地点id后再地点表中查询地点
SELECT * FROM locations WHERE location_id = 2500;
我们需要三个表employees、departments、locations才能找出我们需要的数据
1.2 多表查询
我们一步一步来首先查询一下员工姓名和所在的部门名称
这个写法 缺失了连接条件 所以是错误的 这错误有个名字,叫做笛卡儿积
SELECT last_name,department_name FROM employees,departments
更具上方sql添加入连接条件这样就正确了
SELECT
last_name,
department_name
FROM
employees,
departments
WHERE
employees.department_id = departments.department_id
额外讲一下别名 取公共字段的时候需要将别名加上确定取哪个表里的值
SELECT
last_name,
department_name,
d.department_id
FROM
employees as e,
departments as d
WHERE
e.department_id = d.department_id
这时候我们就成功的把两个表连接了起来。那么我们来连接一下三张表如以下代码。
# 如果有n个表实现多表查询,则至少需要n-1个连接条件
SELECT
e.employee_id,
e.last_name,
d.department_name,
l.city
FROM
employees e,
departments d,
locations l
WHERE
e.department_id = d.department_id AND d.location_id = l.location_id
最后不要忘了,我们最终的目的是查询员工名为’Abel’的人在哪个城市工作。只需要再where里添加一个新的条件就可以了
SELECT
e.employee_id,
e.last_name,
d.department_name,
l.city
FROM
employees e,
departments d,
locations l
WHERE
e.department_id = d.department_id AND d.location_id = l.location_id AND e.last_name = "Abel"
二、各类连接概念
2.1 等值连接 vs 非等值连接
等值连接我们上面那个案例就是,我就不在多举例,直接看一下非等值连接。
练习:获取某个人的职位
SELECT
e.last_name,
e.salary,
j.grade_level
FROM
employees e,
job_grades j
WHERE
e.salary BETWEEN j.lowest_sal
AND j.highest_sal
2.2 自连接 vs 非自连接
自连接就是自己连接自己。
练习:查询员工id,员工姓名,及其管理者的id和姓名
SELECT
emp.employee_id,
emp.last_name,
mgr.employee_id,
mgr.last_name
FROM
employees emp,
employees mgr
WHERE
emp.manager_id = mgr.employee_id
2.3 内连接 vs 外连接
我们之前使用的都是sql92规则 在这里我们使用sql99规则方便讲解
内连接:取出两个表都存在的记录。
SELECT
employee_id,
department_name
FROM
employees e
JOIN departments d ON e.department_id = d.department_id
# 修改为sql99规则后我们如何做到三表连接
SELECT
employee_id,
department_name
FROM
employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
外连接:以某一个表(左/右)为主(左表有但右表没有,反之也成立。),取出记录。外连接又分为常见的两种和不常见的四种.
常见 左连接/右连接
左连接(left join)
SELECT
employee_id,
department_name
FROM
employees e
LEFT JOIN departments d ON e.department_id = d.department_id
右连接(right join)
SELECT
employee_id,
department_name
FROM
employees e
Right JOIN departments d ON e.department_id = d.department_id
不常用的四种: 左连接反/右连接反/满外连接/满外连接反
左连接反
其实就是写出左连接,然后将都有的部分去掉,只留下左边独有的
SELECT
employee_id,
department_name
FROM
employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE
d.department_id IS NULL
右连接反
和左外同理写出右连接,然后将都有的部分去掉,只留下右边独有的
SELECT
employee_id,
department_name
FROM
employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE
e.department_id IS NULL
满外连接
满外连接就是,左边独有的我要,左右都有的我要,右边独有的我也要!
这时要借助俩关键字,UNION ALL 和 UNION
假设两个表,左边独有的记录数量 1 公共部分 106 右边独有的 16
那么UNION ALL(推荐使用)在连接的时候会这样计算, (左) 1+106 + (右) 106 + 16 不会进行去重操作所以会加快速度,减少运算流程。
使用UNION时计算是这样的,1 + 106 +16,会进行去重操作,降低了运算速度。
而满外连接可以使用 左连接表 union all 右外连接反表实现。左连接表(左独有+左右共有)+ 右连接反表(右独有)。
-- 左连接
SELECT
employee_id,
department_name
FROM
employees e
LEFT JOIN departments d ON e.department_id = d.department_id
UNION ALL
-- 右连接反
SELECT
employee_id,
department_name
FROM
employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE
e.department_id IS NULL
满外连接反
满外连接反:就是左独有+右独有
-- 左反
SELECT
employee_id,
department_name
FROM
employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE
d.department_id IS NULL
UNION ALL
-- 右反
SELECT
employee_id,
department_name
FROM
employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE
e.department_id IS NULL
三、小练习
1.所有有部门的人员信息
SELECT
employee_id,
last_name
FROM
employees e
JOIN departments d ON e.department_id = d.department_id
2.列出所有用户,并显示其部门信息
SELECT
e.employee_id,
e.last_name,
d.department_name
FROM
employees e
left JOIN departments d ON e.department_id = d.department_id
3.列出所有的部门
SELECT
d.department_name,
d.department_id
FROM
employees e
right JOIN departments d ON e.department_id = d.department_id
4.所有不入部门的人
SELECT
d.department_name,
d.department_id,
e.last_name
FROM
employees e
left JOIN departments d ON e.department_id = d.department_id
WHERE
d.department_id is NULL
5.所有没人入的部门
SELECT
d.department_name,
d.department_id,
e.employee_id
FROM
employees e
right JOIN departments d ON e.department_id = d.department_id
WHERE e.employee_id is NULL
6.列出所有人员和部门的对照关系
SELECT
e.employee_id,
e.last_name,
d.department_name,
d.department_id
FROM
employees e
left JOIN departments d ON e.department_id = d.department_id
UNION all
SELECT
e.employee_id,
e.last_name,
d.department_name,
d.department_id
FROM
employees e
right JOIN departments d ON e.department_id = d.department_id
WHERE e.employee_id is NULL
7.列出所有没入部门的人员和没人入的部门
SELECT
e.employee_id,
e.last_name,
d.department_name,
d.department_id
FROM
employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE
d.department_name IS NULL
UNION ALL
SELECT
e.employee_id,
e.last_name,
d.department_name,
d.department_id
FROM
employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE
e.employee_id IS NULL