mysql基础（五）：连表查询

• 表结构如下
  

• 基础使用

# 两个表的连接查询
SELECT employee_id, department_name
FROM employees, departments
WHERE employees.`department_id` = departments.department_id;

# 如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表
SELECT employees.employee_id, departments.department_name, employees.department_id
FROM employees, departments
WHERE employees.`department_id` = departments.department_id;

# 可以给表起别名，在SELECT和WHERE中使用表的别名
SELECT emp.employee_id, dept.department_name, emp.department_id
FROM employees emp, departments dept
WHERE emp.`department_id` = dept.department_id;

# 如果给表起了别名，一旦在SELECT或WHERE中使用表名的话，则必须使用表的别名，而不能再使用表的原名
# 如下的操作是错误的：
SELECT emp.employee_id, departments.department_name, emp.department_id
FROM employees emp, departments dept
WHERE emp.`department_id` = departments.department_id;

# 如果有n个表实现多表的查询，则需要至少n-1个连接条件
# 查询员工的employee_id,last_name,department_name,city
SELECT e.employee_id, e.last_name, d.department_name, l.city, e.department_id, l.location_id
FROM employees e, departments d, locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`;

• 非等值连接

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`;
WHERE e.`salary` >= j.`lowest_sal` AND e.`salary` <= j.`highest_sal`;

• 自连接

# 查询员工id,员工姓名及其管理者的id和姓名，在1张表中查询
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`;

• 内外连接

# 内连接：合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
SELECT employee_id, department_name
FROM employees e, departments d
WHERE e.`department_id` = d.department_id;  

# SQL99语法实现内连接：
SELECT last_name, department_name
FROM employees e INNER JOIN departments d
ON e.`department_id` = d.`department_id`;

# 多个表内连接 inner join on 可省略inner
SELECT last_name, department_name, city
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON d.`location_id` = l.`location_id`;

# 外连接：合并具有同一列的两个以上的表的行, 结果集中除了包含一个表与另一个表匹配的行之外，还查询到了左表 或 右表中不匹配的行
# 外连接的分类：左外连接、右外连接、满外连接
# 左外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行，这种连接称为左外连接
# 右外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行，这种连接称为右外连接

# 错误写法
# SQL92语法实现外连接：MySQL不支持SQL92语法中外连接的写法，如下案例不支持
SELECT employee_id, department_name
FROM employees e, departments d
WHERE e.`department_id` = d.department_id(+);

# SQL99语法中使用 JOIN ...ON 的方式实现多表的查询。这种方式也能解决外连接的问题。MySQL是支持此种方式的

# SQL99语法实现外连接：
# 查询所有的员工的last_name,department_name信息 
# 左外连接：
SELECT last_name, department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

# 右外连接：
SELECT last_name, department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

# 错误写法
# 满外连接：mysql不支持FULL OUTER JOIN的写法，如下案例不支持
SELECT last_name, department_name
FROM employees e FULL OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

• sql joins
  

• UNION 和 UNION ALL 的使用

# UNION：会执行去重操作
# UNION ALL:不会执行去重操作

# 7种JOIN的实现：
# 中图：内连接
SELECT employee_id, department_name
FROM employees e 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`;

# 右上图：右外连接
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;

# 左下图：满外连接
# 方式1：左上图 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;

# 方式2：左中图 UNION ALL 右上图
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`;

# 右下图：左中图 UNION ALL 右中图
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;

• 新特性

# SQL99语法的新特性1:自然连接
# 原始写法
SELECT employee_id, last_name, department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

# 新写法
# NATURAL JOIN 它会帮你自动查询两张连接表中`所有相同的字段`，然后进行`等值连接`
SELECT employee_id, last_name, department_name
FROM employees e NATURAL JOIN departments d;

# SQL99语法的新特性2:USING
# 原始写法
SELECT employee_id, last_name, department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

# 新写法
SELECT employee_id, last_name, department_name
FROM employees e JOIN departments d
USING (department_id);

# 拓展：使用内连接
SELECT last_name, job_title, department_name 
FROM employees INNER JOIN departments INNER JOIN jobs 
ON employees.department_id = departments.department_id 
AND employees.job_id = jobs.job_id;

课后练习

# 显示所有员工的姓名，部门号和部门名称；使用左外连接
# 由于是查询所有员工，所以员工表的数据需要包含进来
SELECT e.last_name, e.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

# 查询90号部门员工的job_id和90号部门的location_id
# 使用内连接
SELECT e.job_id, d.location_id
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` = 90;

# 选择所有有奖金的员工的 last_name,department_name,location_id,city，使用左外连接
SELECT e.last_name, e.`commission_pct`, d.department_name, d.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; 

# 选择city在Toronto工作的员工的last_name, job_id, department_id, department_name，使用内连接
SELECT e.last_name, e.job_id, e.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';

# sql92语法：
SELECT e.last_name, e.job_id, e.department_id, d.department_name
FROM employees e, departments d, locations l
WHERE e.`department_id` = d.`department_id` 
AND d.`location_id` = l.`location_id`
AND l.`city` = 'Toronto';

# 查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
# 使用左外连接
SELECT d.department_name, l.street_address, e.last_name, e.job_id, e.salary
FROM departments d LEFT JOIN employees e
ON e.`department_id` = d.`department_id`
LEFT JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE d.`department_name` = 'Executive';

# 选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号
SELECT emp.last_name "employees", emp.employee_id "Emp#", mgr.last_name "manager", mgr.employee_id "Mgr#"
FROM employees emp LEFT JOIN employees mgr
ON emp.manager_id = mgr.employee_id;

# 查询哪些部门没有员工
SELECT d.department_id
FROM departments d LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.`department_id` IS NULL;

# 查询哪个城市没有部门 
SELECT l.location_id, l.city
FROM locations l LEFT JOIN departments d
ON l.`location_id` = d.`location_id`
WHERE d.`location_id` IS NULL;

# 查询部门名为 Sales 或 IT 的员工信息
SELECT e.employee_id, e.last_name, e.department_id
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_name` IN ('Sales','IT');