MySQL函数/数据库函数

一、单行函数

单行函数是每行数据返回一行结果

（一）数学函数 1. round(x)，返回离 x 最近的整数，即对 x 进行四舍五入

mysql> select round(455.476);
+----------------+
| round(455.476) |
+----------------+
|            455 |
+----------------+
1 row in set (0.00 sec)

2. round(x,y)，返回数值 x 保留到小数点后 y 位的值

y 是要保留的小数位数，如果没有指定 y，则默认是 0。

返回最接近 x 的数，其值保留到小数点后面 y 位，截取时会四舍五入。

mysql> select round(455.476,2);
+------------------+
| round(455.476,2) |
+------------------+
|           455.48 |
+------------------+
1 row in set (0.00 sec)

若 y 为负值，则会将小数点左边 y 位的数舍弃为 0。截取时也会进行四舍五入。

mysql> select round(445.476,-2);
+-------------------+
| round(445.476,-2) |
+-------------------+
|               400 |
+-------------------+
1 row in set (0.00 sec)

445.476，小数点左边的 2 位舍弃变成 0，因为从小数点向左数第 2 个数 4 不足以向前进数，所以最终的结果是 400。

mysql> select round(455.476,-2);
+-------------------+
| round(455.476,-2) |
+-------------------+
|               500 |
+-------------------+
1 row in set (0.00 sec)

因为从小数点向左数第 2 个数 5 足以向前进数，所以最终的结果是 500。

3. truncate(x,y)，返回数值 x 保留到小数点后 y 位的值

与 round(x,y) 的区别是不会进行四舍五入。如果没有第二个参数 , 默认是 0。

4. abs(x)，返回 x 的绝对值 5.sqrt(x)，返回 x 的二次方根

x 是一个非负数。

6.mod(x,y)，返回 x 被 y 除后的余数 7.ceil(x) 或 ceiling(x)，返回不小于 x 的最小整数 8.foor(x)，返回不大于 x 的最大整数 9. sign(x)，返回参数 x 的符号，-1 表示负数，0 表示 0，1 表示正数 10. pow(x,y) 或 power(x,y)，返回 x 的 y 次乘方的值 11. exp(x)，返回 e 的 x 乘方后的值

e 是自然对数的底数，是无理数，e 常用于对数，其值是 2.71828…

e = ( 1 + 1 / n ) n (1+1/n)^n (1+1/n)n，其中 n 趋向无穷大。随着 n 的增大，底数 (1+1/n) 就会越来越接近 1，而指数 n 趋向无穷大，那 ( 1 + 1 / n ) n (1+1/n)^n (1+1/n)n 最后的结果到底是趋向于 1 还是无穷大呢？其实，是趋向于 2.718281828…，这是一个无限不循环小数。

什么是对数？ 如果a的x次方等于N（a>0，且a≠1），那么数x叫做以a为底N的对数（logarithm），记作x=loga N。其中，a叫做对数的底数，N叫做真数。

12. log(x)，返回 x 的自然对数

y = log(x)，返回 x 的自然对数，即 x 相对于基数 e 的对数， e y e^y ey = x。

e y e^y ey = x，其中 y 叫做以 e 为底，真数为 x 的对数，这个 y 也叫自然对数。

自然对数就是以无理数 e 为底的对数，记为 lnN。

13. log10(x)，返回 x 的基数为 10 的对数

y = log10(x)， 1 0 y 10^y 10y = x，其中 y 就是以 10 为底数，真数为 x 的对数。

14. radians(x)，返回 x 由角度转化为弧度的值 15. degrees(x)，返回 x 由弧度转化为角度的值 16. sin(x)，前者返回 x 的正弦，其中 x 为给定的弧度值 17. asin(x)，返回 x 的反正弦值，x 为正弦 18. COS(x)，返回 x 的余弦，其中 x 为给定的弧度值 19. ACOS(x)，返回 x 的反余弦值，x 为余弦 20. tan(x)，返回 x 的正切，其中 x 为给定的弧度值 21. atan(x)，返回 x 的反正切值，x 为正切 22. cot(x)，返回给定弧度值 x 的余切 （二）字符串函数 1. left(s,n)，返回字符串 s 左边的 n 个字符

返回字符串 runoob 中的前两个字符：

SELECT LEFT('runoob',2) -- ru

2. concat(s1,s2,…,sn)，将参数拼接成一个字符串

字符串 s1,s2 等多个字符串合并为一个字符串。 如果有任何一个参数为 null，则返回值为 null；如果所有参数都为非二进制字符串，则返回结果为非二进制字符串；如果有任何一个参数为二进制字符串，则返回结果为二进制字符串。

另外要注意数字参数会被转换成等值的二进制数字符串，要避免此种情况，可以使用函数 cast()

合并多个字符串：

SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook") AS concatenated_string;

3. concat_ws(x,s1,s2,…)，返回多个字符串拼接之后的字符串，每个字符串之间有一个x 4. char_length(str)，计算字符串字符个数 （三）日期和时间函数 1. STR_TO_DATE(string, format_mask)，将字符串转变为日期

相当于 Oracle 中的 to_date()。

日期时间格式： %M 解析英文的月份，例如：August %m 解析数字的月份

举例如下：

mysql> SELECT STR_TO_DATE("August 10 2017", "%M %d %Y");
+-------------------------------------------+
| STR_TO_DATE("August 10 2017", "%M %d %Y") |
+-------------------------------------------+
| 2017-08-10                                |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO t_order(order_time) VALUES(str_to_date('2018-05-06','%Y-%m-%d'));
mysql> UPDATE t_project SET start_date = STR_TO_DATE("2021-09-19","%Y-%m-%d") WHERE id = 00000000010;

将日期数据保存到 Date 类型的字段中：

mysql> INSERT INTO t_order(order_time) VALUES(str_to_date('2018-05-06','%Y-%m-%d'));
mysql> UPDATE t_project SET start_date = STR_TO_DATE("2021-09-19","%Y-%m-%d") WHERE id = 00000000010;
mysql> UPDATE t_project SET start_date = STR_TO_DATE("09-23-2019","%m-%d-%Y") WHERE id = 00000000010;
mysql> UPDATE t_project SET start_date = STR_TO_DATE("09.23.2019","%m.%d.%Y") WHERE id = 00000000010;

注意： STR_TO_DATE(字符串日期，解析格式)中的字符串日期要和解析格式一一对应。

2. DATE_FORMAT(d,f)，按表达式 f 的要求显示日期 d

这就是日期转换成字符串的函数，相当于 Oracle 的 to_char()。

mysql> SELECT DATE_FORMAT(sysdate(),'%m-%d-%Y %r');
+--------------------------------------+
| DATE_FORMAT(sysdate(),'%m-%d-%Y %r') |
+--------------------------------------+
| 06-04-2021 09:01:21 AM               |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> select company_name,date_format(insert_time,'%Y,%m,%d') as insert_date from tf_company where company_name like '%建发%';
+-----------------------------------------+-------------+
| company_name                            | insert_date |
+-----------------------------------------+-------------+
| 建发房地产集团上海有限公司                   | 2021,02,21  |
| 建发（上海）有限公司                        | 2021,02,21  |
| 建发房地产集团南京有限公司                   | 2021,02,26  |
+-----------------------------------------+-------------+
3 rows in set (0.03 sec)

mysql> SELECT DATE_FORMAT('2011-11-11 11:11:11','%m-%d-%Y %r');
+--------------------------------------------------+
| DATE_FORMAT('2011-11-11 11:11:11','%m-%d-%Y %r') |
+--------------------------------------------------+
| 11-11-2011 11:11:11 AM                           |
+--------------------------------------------------+
1 row in set (0.00 sec)

说明：

1. 日期时间好像只能按年-月-日-时-分-秒的格式书写，你如果写成月-日-年，则无效
2. %Y-%m-%d %r 显示格式则可以调整位置顺序

如下示例：

mysql> SELECT DATE_FORMAT('11-11-2011 11:11:11','%Y-%m-%d %r');
+--------------------------------------------------+
| DATE_FORMAT('11-11-2011 11:11:11','%Y-%m-%d %r') |
+--------------------------------------------------+
| NULL                                             |
+--------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

也可以将日期转换成字符串格式存到 Date 类型的字段中：

mysql> UPDATE t_project SET start_date = DATE_FORMAT('2021-08-21','%Y-%m-%d') WHERE id = 00000000010;

注意： 1.Date 类型的字段只能按“年月日”格式保存 2.DATE_FORMAT(日期,显示格式)，日期和显示格式要一一对应，且只能按“年月日”的顺序写 3.保存时不论分隔符是什么，查询日期数据的显示格式都是“年-月-日”

mysql> select * from t_project;
+-------------+--------+----------+------------+------------+
| id          | no     | name     | start_date | end_date   |
+-------------+--------+----------+------------+------------+
| 00000000001 | pm0001 | pm000001 | 2008-01-02 | 2008-01-15 |

3. now()，获取当前的系统日期和时间

了解详情，猛戳《MySQL 获取系统日期/系统时间的函数》

4. dayofweek(date)，返回日期 date 是星期几

mysql> select dayofweek('2016-01-16');
+-------------------------+
| dayofweek('2016-01-16') |
+-------------------------+
|                       7 |
+-------------------------+
1 row in set (0.03 sec)

注意：星期天=1，星期一=2， … 星期六=7

5. dayname(date)，返回日期 date 是星期几

显示英文名称。

mysql> select dayname('2016-01-16');
+-----------------------+
| dayname('2016-01-16') |
+-----------------------+
| Saturday              |
+-----------------------+
1 row in set (0.07 sec)

6. time_to_sec(t)，将时间 t 转换为秒

把时间转化成秒数。

7. sec_to_time(s)，将秒数 s 转换成时间

把秒数转化成时间（时分秒格式）。

8. from_unixtime(unix_tiemstamp,format)，把时间戳转化成日期时间

把 unix 时间戳转换为可读的时间

mysql> select from_unixtime(23434343,'%Y %m %d %H %I %S');
+---------------------------------------------+
| from_unixtime(23434343,'%Y %m %d %H %I %S') |
+---------------------------------------------+
| 1970 09 29 13 01 23                         |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime('23434343','%Y %m %d %H %I %S'); 
+-----------------------------------------------+
| from_unixtime('23434343','%Y %m %d %H %I %S') |
+-----------------------------------------------+
| 1970 09 29 13 01 23                           |
+-----------------------------------------------+
1 row in set (0.00 sec)

9. unix_timestamp(date)，日期转换成时间戳

把可读的时间转换为 unix 时间戳。

mysql> select unix_timestamp('2009-08-06 10:10:40');
+---------------------------------------+
| unix_timestamp('2009-08-06 10:10:40') |
+---------------------------------------+
|                            1249524640 |
+---------------------------------------+
1 row in set (0.00 sec)

（四）条件判断函数 1. case 语句

case 语句是数据库中的分支语句 , 相当于 Java 中的 switch-case 语句。

CASE expression
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
   ...
    WHEN conditionN THEN resultN
    ELSE result
END

举例：

select ename , salary , job ,
case job when 'Analyst' then salary * 1.1 --注意这里没有“ , ”
when 'Programmer' then salary * 1.05
when 'clerk' then else salary -- else 相当于 Java 中 case 语句的 default 
end new_salary -- end 是 case 语句的结束标识，new_salary 是从 case 开始到 end 结束这部分 的别名
salary * 1.02
from emp_xxx ; 

2. ifnull(v1,v2)，空值转换函数

如果 v1 的值不为 NULL，则返回 v1，否则返回 v2

（五）其它函数 1. coalesce(expr1, expr2, …, expr_n)，返回参数列表中第一个非空参数

参数列表中最后一个值通常为常量。

举例如下：

select ename, bonus, salary, coalesce( bonus , salary*0.5 , 100 ) bonus_year from emp_htlwk; # bonus不为空则返回bonus的值，若为空，则判断salary*0.5是否为空，不为空则返回salary*0.5的值，若为空则返回常数100

需求描述：有分红的以分红金额作为年终奖，有工资没有分红的以月薪的50%作为年终奖，没有分红又没有月薪的休假人员施舍100元作为年终奖。

2. cast(x AS type)，将 x 变成 type 类型

将数值转换成字符类型：

mysql> select cast(120 as char);
+-------------------+
| cast(120 as char) |
+-------------------+
| 120               |
+-------------------+
1 row in set (0.03 sec)
 
将字符串转换成日期类型：

mysql> select cast('2022-02-2' as date);
+---------------------------+
| cast('2022-02-2' as date) |
+---------------------------+
| 2022-02-02                |
+---------------------------+
1 row in set (0.03 sec)

3. inet_aton(expr)，将IP地址转成32位的二进制整数

mysql> select inet_aton('192.168.0.0');
+--------------------------+
| inet_aton('192.168.0.0') |
+--------------------------+
|               3232235520 |
+--------------------------+
1 row in set (0.00 sec)

4.inet_ntoa(expr)，将二进制整数转成IP地址

mysql> select inet_ntoa('323223');
+---------------------+
| inet_ntoa('323223') |
+---------------------+
| 0.4.238.151         |
+---------------------+
1 row in set (0.00 sec)

mysql> select inet_ntoa(323223); -- 可以去掉引号
+-------------------+
| inet_ntoa(323223) |
+-------------------+
| 0.4.238.151       |
+-------------------+
1 row in set (0.00 sec)

二、组函数

与单行函数不同 , 单行函数是每行数据返回一行结果 , 组函数是多行数据返回一行结果。

（一）数学函数 1. count (expression)，返回查询的记录总数

expression 参数是一个字段或者 * 号。常用于查询表的记录总数。

2. avg(expression)，返回一个表达式的平均值

expression 是一个字段。

返回 products 表中 price 字段的平均值：

SELECT AVG(price) AS average_price FROM products;

3. sum(expression)，返回指定字段的总和

expression 是一个字段。

计算 order_details 表中字段 quantity 的总和：

SELECT SUM(quantity) FROM order_details;

4. max(expression)，返回字段 expression 中的最大值

对所有数据类型都可以操作。如果参数写列名 , 默认忽略空值。

5. min(expression)，返回字段 expression 中的最小值

对所有数据类型都可以操作。如果参数写列名 , 默认忽略空值。