SQL常用函数总结（二）

二、常用函数

1.排序 ORDER BY,ASC升序（默认升序），DESC降序

①ORDER BY可以使用列的别名进行排序，但是WHERE不能用列的别名进行过滤

②强调格式：WHERE需要声明在FROM之后，ORDER BY之前

③一般先是FROM指定表，WHERE过滤条件，SELECT查询对应信息，ORDER BY将对应信息排序，排序后若仍有相同的行，可在后面接着二级排序

例如：

#显示员工信息，按照department_id的降序排序，salary的升序排序
SELECT employee_id,salary,department_id
FROM employees
ORDER BY department_id DESC,salary ASC;#ASC可省略不写，因为默认升序

2.分页显示   LIMIT  位置偏移量，条目数

需求：每页显示pageSize条记录，此时显示第pageNo页

公式：LIMIT（pageNo-1）*pageSize,pageSize

#每页显示20条记录，此时显示第1页
SELECT employee_id,last_name
FROM employees
LIMIT 0,20;#偏移量为0，一页显示20条----此时可简写为LIMIT 20;
#每页显示20条记录，此时显示第2页
LIMIT 20,20;
#每页显示20条记录，此时显示第3页
LIMIT 40,20;

 声明顺序：WHERE…ORDER BY…LIMIT…

还可这样写：

公式：LIMIT  条目数  OFFSET  偏移量

！LIMIT可以使用在MySQL，PGSQL，MariaDB，SQLite中表示分页，不能使用在SQL Server，DB2，Oracle

 基本格式：

SELECT…，…，…

FROM…

WHERE…AND/OR/NOT…

ORDER BY…（ASC/DESC),…，…

LIMIT…，…

3.多表查询

①从优化的角度，多表查询时，每个字段都指明其所在的表

②可以给表起别名，在SELECT和WHERE中使用表的别名，若起了别名，在SELECT和WHERE中使用表名则必须使用表的别名，而不能再使用原名

③若有n个表实现多表的查询，则需要至少n-1个连接条件

④多表查询分类

角度1：等值连接 VS 非等值连接

角度2：自连接 VS 非自连接

角度3：内连接 VS 外连接

非等值连接例子：

SELECT e.last_name,e.salary,j.grade_level
FROM employees e,job_grade j
WHERE e.'salary' BETWEEN j.'lowest_sal' AND j.'highest_sal';

自连接的例子：

#查询员工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'

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

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

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

外连接分类：左外连接，右外连接，满外连接

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

需求中出现所有的，各个 时字眼时常常用到外连接。

MySQL不支持MySQL92语法中外连接的写法，SQL99语法使用JOIN…ON的方式实现多表查询，该方法也能解决外连接的问题，MySQL支持此种方式的写法

（1）SQL99语法实现多表查询

#2个表
SELECT last_name,department_name
FROM employees e JOIN departments d
ON e.'department_id'=d.'department_id';
#3个表
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';

(2)SQL语法实现外连接

#查询所有的员工的last_name,department_name信息  ！关键词：所有的
#左外连接：
SELECT last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.'department_id'=d.'department_id';#可直接简写LEFT JOIN
#右外连接
SELECT last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.'department_id'=d.'department_id';
#满外连接，MySQL不支持FULL OUTER JOIN，所以不能写成以下这样，oracle好像支持
SELECT last_name,department_name
FROM employees e FULL OUTER JOIN departments d
ON e.'department_id'=d.'department_id';

（3）UNION的使用

UNION操作符：返回两个查询的结果集的并集，去除重复记录

UNION ALL：返回两个查询结果集的并集，不去除重复部分-----此更多用

结论：若明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量用UNION ALL，以提高数据查询效率

（4）7种JOIN的实现（使用SQL99语法）

①中图：内连接

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 d.'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 d.'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 d.'department_id' IS NULL

 （5）自然连接 NATURAL JOIN

可以将自然连接理解为SQL92中的等值连接，它会帮你自动查询两张连接表中所有相同的字段，然后等值连接.

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';
#此代码等价于以下代码
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

 （6）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);