MySQL零散拾遗（二）

列的别名只能在 ORDER BY 中使用，不能在 WHERE 中使用

举个栗子，有一个员工表employees

显示出表 employees 的 员工id，员工的月薪还有年薪，并按年薪进行升序排序。

SELECT employee_id, salary, salary * 12 annual_sal
FROM employees
ORDER BY annual_sal

但是列的别名不能在 WHERE 中使用
下面的MySQL语句会报错

SELECT employee_id, salary, salary * 12 annual_sal
FROM employees
WHERE annual_sal > 81600

为什么？进行解释

当 ORDER BY 和 WHERE 同时出现时，WHERE 要往前放，即 WHERE 放在 FROM 的后面，ORDER BY 放在 WHERE 的后面。

SELECT employee_id, salary
FROM employees
WHERE department_id IN (50, 60, 70)
ORDER BY depart_id DESC

MySQL中 SELECT 查询语句的执行顺序，以上面的语句进行分析，首先执行 FROM employees 从哪个表中取数据，然后执行 WHERE department_id IN (50, 60, 70) 看看有什么过滤条件，筛选完后留下一波数据，然后执行 SELECT employee_id, salary 看看你想查什么数据（哪些字段或者列）这时可以对列进行起别名，最后执行ORDER BY depart_id DESC 有了列的别名后才能使用列的别名进行对查询结果进行排序。

一句话总结：WHERE在SELECT前面执行，所以在SELECT中定义的列的别名，无法在WHERE使用。

WHERE … ORDER BY … LIMIT 声明顺序如下

SELECT employee_id, last_name, salary
FROM employees
WHERE salary > 600
ORDER BY salary DESC
LIMIT 0, 10;
# LIMIT 10;

LIMIT 的格式：严格来说：LIMIT 位置偏移量, 条目数
结构 LIMIT 0, 条目数 等价于 LIMIT 条目数

LIMIT常用于分页机制

需求：每页显示pageSize条记录，此时显示第pageNo页：
公式：LIMIT (pageNo - 1) * pageSize，pageSize;

MySQL 8.0中可以使用 “LIMIT 3 OFFSET 4”，意思是获取从第5条记录开始后面的3条记录，和 “LIMIT 4, 3” 返回的结果相同。

多表查询遇到的问题

三张表，employees表、departments表、locations表

employees表的字段信息：employee_id，first_name，last_name，email，phone_number，job_id，salary，commission_pct，manager_id，department_id

department表的字段信息：department_id，department_name，manager_id，location_id

locations表的字段信息：location_id，street_address，postal_code，city，state_province，country_id

如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表

SELECT employee_id, department_id, employees_department_id
FROM employees, departments
WHERE employeess.department_id = departments.department_id;

建议：从sql优化的角度，建议多表查询时，每个字段前都指明其所在的表

可以给表起别名，在SELECT 和 WHERE中使用表的别名

SELECT emp.employee_id, dept.department_name, dept.department_id 
FROM employees emp, departments dept
WHERE emp.department_id = dept.department_id;

如果给表起了别名，一旦在SELECT或WHERE中使用表名的话，则必须使用表的别名，而不能再使用表的原名。
这是因为，当给表起别名时，别名就会覆盖掉表的原名，注意，这里是 “覆盖”

练习：查询员工的employee_id，last_name, department_name, city

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;

结论：如果有n个表实现多表的查询，则需要至少n-1个连接条件

多表查询的分类

演绎式：提出问题1 —> 解决问题1 —> 提出问题2 —> 解决问题2
归纳式：总 — 分

角度1：等值连接 vs 非等值连接
角度2：自连接 vs 非自连接
角度3：内连接 vs 外连接

说一说，内连接和外连接

内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行

SELECT employee_id, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

外连接：合并具有同一列的两个以上的表的行，结果集中除了包含一个表和另一个表匹配的行之外，还查询到了 左表 或 右表中不匹配的行。
外连接的分类：左外连接、右外连接、满外连接

左外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行，这种连接称为左外连接。
右外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行，这种连接称为右外连接。

练习：查询所有的员工的last_name，department_name信息

注意：在笔试当中或者实际开发当中，看到 “所有” 这个字眼而且查询的字段来自于不同的表，那么一定是多表连接，可能需要查询不匹配的数据，一定是外连接

使用MySQL对上图进行转换

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`;

右下图：

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;