四.函数
一.引入
函数分为内置函数和自定义函数，而MySQL的内置函数则分为单行函数和多行函数（也称为聚合函数或分组函数）。
下述内容的函数多为单行函数。
二.数值函数
1.基本函数
| 函数 | 用法 |
|---|---|
| ABS(x) | 返回x的绝对值 |
| SIGN(x) | 返回x的符号，返回值为1,-1,0 |
| PI() | 返回圆周率的值 |
| CEIL(x) | 上取整 |
| FLOOR(x) | 下取整 |
| LEAST(e1,e2,…) | 取最小值 |
| GREATEST(e1,e2,…) | 取最大值 |
| MOD(x,y) | 取余 |
| RAND() | 返回0~1的随机数 |
| RAND(x) | 同上，但输入seed值x |
| ROUND(x) | 四舍五入 |
| ROUND(x,y) | 保留小数点y位的四舍五入 |
| TRUNCATE(x,y) | 保留小数点y位的截断 |
| SQRT(x) | 开根号，x为负时返回NULL |
2.三角函数
角度弧度相关：
| 函数 | 用法 |
|---|---|
| RADIANS(x) | 角度转化为弧度 |
| DEGREES(x) | 弧度转化为角度 |
三角函数相关：
| 函数(x为弧度制) | 用法(re为结果) |
|---|---|
| SIN(x) | re = sin x |
| ASIN(x) | re = arcsin x |
| COS(x) | re = cos x |
| ACOS(x) | re = arccos x |
| TAN(x) | re = tan x |
| ATAN(x) | re = arctan x |
| ATAN2(m,n) | re = arctan(m/n) |
| COT(x) | re = cot x |
3.指数和对数
| 函数 | 用法(re为结果) |
|---|---|
| POW(x,y) POWER(x,y) | re = x^y |
| EXP(x) | re = e^x |
| LN(x) LOG(x) | re = ln x |
| LOG10(x) | re = log~10~x |
| LOG2(x) | re = log~2~x |
4.进制转换
| 函数 | 用法 |
|---|---|
| BIN(x) | 返回x的二进制编码 |
| HEX(x) | 返回x的十六进制编码 |
| OCT(x) | 返回x的八进制编码 |
| CONV(x,f1,f2) | 将f1进制转为f2进制并返回结果 |
三.字符串函数
| 函数 | 用法 |
|---|---|
| ASCII(s) | 返回字符串s的第一个字符的ASCII码 |
| CHAR_LENGTH(s) | 返回字符串s的字符数 |
| LENGTH(s) | 返回字符串s的字节数 |
| CONCAT(s1,s2,s3,…) | 连接s1,s2,s3…为一个字符串 |
| CONCAT_WS(x,s1,s2,s3,…) | 在s1,s2,s3…之间加入x并连接 |
| INSERT(str,idx,len,replacestr) | 将字符串str从idx开始，len个字符长度的子串替换为replacestr |
| REPLACE(str,a,b) | 用字符串b替代str中所有子串a |
| 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个字符 |
| LTRIM(s) | 去掉字符串s左侧的空格 |
| RTRIM(s) | 去掉字符串s右侧的空格 |
| TRIM(s) | 去掉字符串s开始和结尾的空格 |
| TRIM(s1 FROM s) | 去掉字符串s开始和结尾的s1 |
| TRIM(LEADING s1 FROM s) | 去掉字符串s开始的s1 |
| TRIM(TRAILING s1 FROM s) | 去掉字符串s结尾的s1 |
| REPEAT(str,n) | 返回字符串str重复n次后的字符串 |
| SPACE(n) | 返回n个空格 |
| STRCMP(s1,s2) | 比较s1和s2的ASCII码值的大小 |
| SUBSTR(s,index,len) SUBSTRING(s,n,len) MID(s,n,len) | 返回从字符串s的index位置起的len个字符 |
| LOCATE(substr,str) POSITION(substr IN str) INSTR(str,substr) | 返回字符串substr在字符串str中首次出现的位置，若未找到则返回0 |
| ELT(m,s1,s2,…,sn) | 返回指定位置的字符串，如果m=1，则返回S1，如果m=2，则返回S2，如果m=n，则返回Sn |
| FIELD(s,s1,s2,…,sn) | 返回字符串s在字符串列表中第一次出现的位置 |
| FIND_IN_SET(s1,s2) | 返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串 |
| REVERSE(s) | 反转字符串s |
| NULLIF(value1,value2) | 比较两个字符串，若value1与value2相等，则返回NULL，否则返回value1 |
四.日期和时间函数
1.获取时间
| 函数 | 用法 |
|---|---|
| CURDATE() | 返回当前时间（年月日） |
| CURTIME() | 返回当前时间（时分秒） |
| NOW() | 返回当前系统日期和时间 |
2.时间戳
| 函数 | 时间 |
|---|---|
| UNIX_TIMESTAMP() | 以UNIX时间戳的形式返回当前时间 |
| UNIX_TIMESTAMP(date) | 将时间date以UNIX时间戳的形式返回 |
| FROM_UNIXTIME(timestamp) | 将UNIX时间戳转化为普通格式时间 |
3.月份和星期
| 函数 | 用法 |
|---|---|
| YEAR/MONTH/DATE(date) | 返回日期值 |
| HOUR/MINUTE/SECOND(date) | 返回时间值 |
| MONTHNAME(date) | 返回月份名称 |
| DAYNAME(date) | 返回星期几的名称 |
| WEEKDAY(date) | 返回周几的数字（从0开始） |
| QUARTER(date) | 返回季度的数字 |
| WEEK(date) WEEKOFYEAR(date) | 返回一年中的第几周 |
| DAYOFYEAR(date) | 返回date是一年中的第几天 |
| DAYOFMONTH(date) | 返回date位于所在月份第几天 |
| DAYOFWEEK(date) | 返回周几的数字（从1开始） |
4.时间转换
| 函数 | 用法 |
|---|---|
| TIME_TO_SEC(time) | 将time转化为秒 |
| SEC_TO_TIME(seconds) | 将seconds转化为时分秒 |
5.时间运算
| 函数 | 用法 |
|---|---|
| ADDTIME(time1,time2) | 返回time1+time2 |
| SUBTIME(time1,time2) | 返回time1-time2 |
| DATEDIFF(date1,date2) | 返回date1-date2的日期间隔 |
| TIMEDIFF(time1,time2) | 返回time1-time2的日期间隔 |
| FROM_DAYS(N) | 返回从0000年1月1日起，N天以后的日期 |
| TO_DAYS(date) | 返回日期date距离0000年1月1日的天数 |
| LAST_DAY(date) | 返回date所在月份的最后一天的日期 |
| PERIOD_ADD(time,n) | 返回time加上n后的时间 |
6.日期格式化
| 函数 | 用法 |
|---|---|
| DATE_FORMAT(date,fmt) | 按照字符串fmt格式化日期date值 |
| TIME_FORMAT(time,fmt) | 按照字符串fmt格式化时间time值 |
| GET_FORMAT(date_type,format_type) | 返回日期字符串的显示格式 |
| STR_TO_DATE(str,fmt) | 按照字符串fmt对str进行解析，解析为一个日期 |
五.流程控制函数
| 函数 | 用法 |
|---|---|
| IF(value,value1,value2) | 如果value的值为TRUE，返回value1，否则返回value2 |
| IFNULL(value1,value2) | 若value1不为NULL，返回value1，否则返回value2 |
| CASE WHEN 条件1 THEN 结果1 … ELSE 结果 END | 类似类c语言中的if else，else为default选项 |
| CASE expr WHEN 条件1 THEN 结果1 … ELSE 结果 END | 类似类c语言中的switch case，else为default选项 |
六.聚合函数
聚合函数可输入若干个数值，但返回时一定为一个数值。
1.常见的聚合函数
a. AVG / SUM 函数
SELECT AVG(salary),SUM(salary) FROM employees;
AVG用于求某列所有数据的平均值，SUM用于求所有数据的和。
b. MAX / MIN 函数
SELECT MAX(salary),MIN(salary) FROM employees;
MAX用于求某列所有数据的最大值，MIN用于求某列所有数据的最小值。
c. COUNT 函数
- 计算指定字段在查询结构中出现的个数。
SELECT COUNT(employee_id),COUNT(salary) FROM employees;
返回出现的个数。
- 计算出现个数时NULL不会被计入。
- 搭配
SUM函数可以求AVG的值，AVG = SUM / COUNT。 - 要在计算
AVG的时候算入NULL字段的数据，则可写作AVG = SUM / COUNT(*)
2.GROUP BY函数
SELECT employee_id,AVG(salary),SUM(salary) FROM employees GROUP BY department_id;
将employees按照department_id分组，数值相同的分为一组，并分别按组来计算AVG / SUM值。
GROUP BY的声明在FROM后边，WHERE后边，ORDER BY前边，LIMIT前边。
SELECT employee_id,AVG(salary),SUM(salary) FROM employees GROUP BY department_id WITH ROLLUP;
WITH ROLLUP可以将所有数据当做一个组并附在GROUP BY给出的数据之后，使用WITH ROLLUP后不适合再进行ORDER BY操作。
3.HAVING函数
a.使用条件
如果过滤条件中使用了聚合函数，则必须使用HAVING来替换WHERE。
HAVING需要声明在GROUP BY的后边。
SELECT department_id,MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary)>10000;
上述代码将输出根据department_id为分组条件，筛选出各组中MAX(salary)大于10000的department_id及其MAX(salary)。
使用HAVING的前提是使用了GROUP BY函数。
WHERE在底层执行时的效率高于HAVING。
b.WHERE与HAVING的对比
-
从适用范围上讲，
HAVING的适用范围更广。 -
在关联查询中，
WHERE是先筛选后链接，占用的资源较少。HAVING则是先将数据关联，再进行筛选，占用的资源较多，执行效率较低。
七.底层逻辑
SQL 语句的执行过程：
FROM -> ON -> (LEFT / RIGHT)JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT
