流程控制

MySQL 支持简单的流程控制，通过指定的条件判断来执行不同的指令：

if(A,B,C)

语法：等价于 A?B:C，如果 A 为真，则返回 B，为假则返回 C。

1
SELECT last_name, salary, IF(salary>6000,"有钱","没钱") FROM employees;

case when

语法：CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 …. [ELSE resultn] END。

1
2
3
4
5
6
7
-- case 后面的 when 是逐个对比，符合就返回 then 的后面。
SELECT last_name,salary, CASE
WHEN salary>=15000 THEN '富豪'
WHEN salary>=10000 THEN '有钱人'
WHEN salary>=8000 THEN '打工人'
ELSE '穷人' END -- END 为结束。
FROM employees;

单行函数

指的是输入的值是 输入 单个数据，返回 单个数据 的函数。

数值相关

函数 用法
ABS(x) 返回x的绝对值
SIGN(X) 返回X的符号。正数返回1，负数返回-1，0返回0
CEIL(x) 返回大于或等于某个值的最小整数（取整）
FLOOR(x) 返回小于或等于某个值的最大整数（取整）
LEAST(e1,e2,e3…) 返回列表中的最小值
GREATEST(e1,e2,e3…) 返回列表中的最大值
MOD(x,y) 返回X除以Y后的余数
RAND() 返回0~1的随机值
RAND(x) 返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数
ROUND(x) 返回一个对x的值进行四舍五入后，最接近于X的整数
ROUND(x,y) 返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位
TRUNCATE(x,y) 返回数字x截断为y位小数的结果
SQRT(x) 返回x的平方根。当X的值为负数时，返回NULL

进制转化

函数 用法
BIN(x) 返回x的二进制编码
HEX(x) 返回x的十六进制编码
OCT(x) 返回x的八进制编码
CONV(x,f1,f2) 返回f1进制数变成f2进制数

字符串函数

注意：MySQL 中，字符串下标是从 1 开始的。

函数 用法
ASCII(S) 返回字符串S中的第一个字符的ASCII码值
CHAR_LENGTH(s) 返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同
LENGTH(s) 返回字符串s的字节数，和字符集有关
CONCAT(s1,s2,……,sn) 连接s1,s2,……,sn为一个字符串
CONCAT_WS(x, s1,s2,……,sn) 同CONCAT(s1,s2,…)函数，但是每个字符串之间要加上x
UPPER(s) 或 UCASE(s) 将字符串s的所有字母转成大写字母
LOWER(s) 或LCASE(s) 将字符串s的所有字母转成小写字母
LEFT(str,n) 返回字符串str最左边的n个字符
RIGHT(str,n) 返回字符串str最右边的n个字符
LPAD(str, len, pad) 用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str ,len, pad) 用字符串pad对str最右边进行填充，直到str的长度为len个字符
NULLIF(value1,value2) 比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1

日期和时间

函数 用法
CURRENT_DATE() 返回当前日期，只包含年、月、日
CURRENT_TIME() 返回当前时间，只包含时、分、秒
NOW() 返回当前系统日期和时间
UTC_DATE() 返回UTC（世界标准时间）日期
UTC_TIME() 返回UTC（世界标准时间）时间

下面的是解析日期和时间的，需要配合上面的函数嵌套使用。

函数 用法
YEAR(date) / MONTH(date) / DAY(date) 返回具体的日期值
HOUR(time) / MINUTE(time) / SECOND(time) 返回具体的时间值
MONTHNAME(date) 返回月份：January，…
DAYNAME(date) 返回星期几：MONDAY，TUESDAY…..SUNDAY
WEEKDAY(date) 返回周几，注意，周1是0，周2是1，。。。周日是6
QUARTER(date) 返回日期对应的季度，范围为1～4

MySQL 信息函数

函数 用法
VERSION() 返回当前MySQL的版本号
CONNECTION_ID() 返回当前MySQL服务器的连接数
DATABASE()，SCHEMA() 返回MySQL命令行当前所在的数据库
USER()，CURRENT_USER()、SYSTEM_USER()，SESSION_USER() 返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名”
CHARSET(value) 返回字符串value自变量的字符集
COLLATION(value) 返回字符串value的比较规则

聚合函数

输入多行数据，数据一个数据的聚合函数，一般是配合着 group by 进行分组使用。

分组查询 group by/having

group by 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。我们需要选择一个column列作为分组的依据，然后这个column中数值相同的数据将会被整合到一行数据中，被整合在一起的数据需要进行合并处理，不然就会报错。

也就是说，在 SELECT 语句中出现的元素要么为一个聚合函数的输入值，要么为 GROUP BY 语句的参数。

1
2
3
4
5
select column1, aggregate_function(column2) as new_column
from "table"
where column1=XXX
group by column1
having new_column=XXX;

这样，会返回一个列出所有不同column1的值为一行数据的表格，其中column1值相同的数据所拥有的column2的值会根据aggregate_function()的功能进行计算，然后出现在那行数据里。

也可以根据多个列进进行分组查询，分组的组则是依据多个列数据的 distinct。

列：

1
2
3
SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id;

分组数量为：distinct department_id, job_id，也就有多少行数据。可以得到不同部门和工作的平均薪水。

HAVING 语句！

如果想要将 聚合函数 返回的新的 column 进行筛选，则在 group by 后面使用 having 语句，使用方法和 where 一样，它会在分组之后，进行一次过滤。

虽然 having 可以起到 where 的作用，但是两者的效率差距很大，能 where 就不 having，建议参考 MySQL 语句执行顺序，来理解 having。

优点 缺点
WHERE 先筛选数据再关联，执行效率高 不能使用分组中的计算函数进行筛选
HAVING 可以使用分组中的计算函数 在最后的结果集中进行筛选，执行效率较低

聚合函数

注意，只要 SQL 语句中，出现了聚合函数（尤其是 COUNT()），就是分组查询，就需要考虑分组。如果没有设置 group by 分组条件，那么整张表就是一个分组。

函数 作用数据类型 用法
AVG() 数值型数据 求平均数
SUM() 数值型数据 求累加和
MAX() 任意数据类型 求最大值
MIN() 任意数据类型 求最小值
COUNT() 任意数据类型 求数据个数

例子：

1
2
3
SELECT department_id, AVG(salary), COUNT(*)
FROM employees
GROUP BY department_id;

是分组查询，不过是 employees 因为没有 group by 的分组条件，所以所有数据都是一组，可以直接得到表的总行数。

1
2
SELECT COUNT(*)
FROM employees;

COUNT()

  • count(*) 返回分组中的数据行数。

  • count(column) 返回分组中 column 列不为 NULL 的总数。

练习

  • 查询各个管理者手下员工的最低工资，其中最低工资不能低于 6000，没有管理者的员工不计算在内 
1
2
3
4
5
SELECT manager_id, MIN(salary) AS min_salary
FROM employees
WHERE manager_id IS NOT NULL; -- where 对非聚合列进行提前过滤
GROUP BY manager_id
HAVING min_salary >= 6000; -- having 对聚合列过滤
  • 查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序 
1
2
3
4
5
6
SELECT dep.department_name, dep.location_id, COUNT(emp.employee_id) AS emp_num, AVG(emp.salary) AS salary_avg
FROM departments dep 
LEFT JOIN employees emp
ON dep.department_id = emp.department_id
GROUP BY dep.department_name, dep.location_id
ORDER BY salary_avg DESC;
  • 查询每个工种、每个部门的部门名、工种名和最低工资

每个工种，每个部门，他们之间需要用 FULL OUT JOIN（MySQL 用 Union 实现），工种 jobs 和 部门 departments 借助 employees 建立连接。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT dep.department_name, jobs.job_title, MIN(emp.salary)
FROM departments AS dep
LEFT JOIN employees AS emp 
ON dep.department_id = emp.department_id
LEFT JOIN jobs
ON jobs.job_id = emp.job_id
GROUP BY jobs.job_title, dep.department_name
UNION 
SELECT dep.department_name, jobs.job_title, MIN(emp.salary)
FROM departments AS dep
LEFT JOIN employees AS emp 
ON dep.department_id = emp.department_id
RIGHT JOIN jobs
ON jobs.job_id = emp.job_id
GROUP BY jobs.job_title, dep.department_name;