【MySQL】多表查询（SQL99）—— 简单练习（尚硅谷）

尚硅谷多表查询章节练习

DB
—— 链接：https://pan.baidu.com/s/1Z6qCAQrZY2kgTEE5w2a7Fg?pwd=wr8i 
—— 提取码：wr8i 

DBMS —— SQLyog

涉及的内容 —— 内连接、外连接

背景简单介绍：
SQL有两个主要的标准SQL92和SQL99
MySQL不支持SQL92外连接（左外连接、右外连接、满外连接）的语法
我们以下使用的是SQL99的语法，它一共有七种连接方式如下图所示，非常的重要

# 第06章_多表查询_课后练习

##################################################################
# 一定要注意执行结果的记录条数，如果不对，肯定是有些关键点没有注意到 #
##################################################################

DESCRIBE employees;
DESCRIBE departments;
DESCRIBE locations;
SELECT * FROM employees;
SELECT * FROM departments;
SELECT * FROM locations;

# 1.显示所有员工的姓名，部门号和部门名称。
# 注意题目里的所有，要考虑没有部门号和部门名称的员工
SELECT e.last_name,d.department_id,d.department_name
FROM employees e 
LEFT JOIN departments d # 由于注意事项，选择左外连接
ON e.department_id = d.department_id; # 一共107条记录

# 2.查询90号部门员工的job_id和90号部门的location_id
SELECT e.job_id,d.location_id
FROM employees e
JOIN departments d # 这里正常情况，直接使用SQL99内连接语法
ON e.department_id = d.department_id
AND e.department_id = 90; # 一共3条记录

# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
# 注意题目里的所有，要考虑有奖金但是没有部门等信息的员工，跟问题1类似
SELECT e.last_name,d.department_name,l.location_id,l.city
FROM employees e
LEFT JOIN departments d # 选择左外连接
ON e.department_id = d.department_id
LEFT JOIN locations l # 注意，仍然需要采用左外连接
ON d.location_id = l.location_id
WHERE e.commission_pct IS NOT NULL; # 一共35条记录

# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name
SELECT e.last_name,e.job_id,d.department_id,d.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
WHERE l.city = 'Toronto'; # 一共2记录

# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
SELECT d.department_name,l.street_address,e.last_name,e.job_id,e.salary
FROM employees e
JOIN departments d # 直接使用内连接
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id
WHERE d.department_name = 'Executive'; # 一共3条记录

# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
# employees  Emp   manager   Mgr
# kochhar    101   king      100
# 根据需求需要给列起别名，用双引号表示
SELECT emp.last_name "employees",emp.employee_id "Emp",mgr.last_name "manager",mgr.employee_id "Mgr"
FROM employees emp
JOIN employees mgr # 自连接
ON emp.manager_id = mgr.employee_id; # 一共106条记录
# 我们的employees表一共有107名员工信息，这里我没考虑员工没有manager的情况，如果想获得全部员工的manager，使用左外连接

# 7.查询哪些部门没有员工
SELECT d.department_id,d.department_name
FROM employees e
RIGHT JOIN departments d # 右外连接，使用的是七种连接方式的最右侧的上数第二个
ON e.department_id = d.department_id
WHERE e.department_id IS NULL; # 一共16条记录

# 8. 查询哪个城市没有部门
# 和第7题可以说是一模一样
SELECT l.city
FROM departments d
RIGHT JOIN locations l
ON d.location_id = l.location_id
WHERE d.location_id IS NULL; # 一共16条记录

# 9. 查询部门名为 Sales 或 IT 的员工信息
SELECT *
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_name IN ('Sales','IT'); # 一共39条记录