共计 5685 个字符，预计需要花费 15 分钟才能阅读完成。
了解编程的人一般都会知道函数的重要性，丰富的函数有的时候可以给我们带来事半功倍的效果，在 MySQL 中提供了许多的内置函数，能够帮助开发人员编写简单快捷的 SQL 语句, 除了这些内置的函数之外，用户也可以自定义函数，本次博客整理了一下 MySQL 中常用的函数，简单的介绍了自定函数，MySQL 版本 mysql-5.7.19。
常用函数
1. 字符串函数
字符串函数是最常用的一种函数。下表列出了常用的字符串函数：
2. 数值函数
MySQL 中另外一类就是数值函数了。这些函数可以处理很多数值方面的运算，常见的数值运算函数如下：
3. 日期和时间函数
有的时候们需要了解当前的时间，这时候我们就可以调用时间函数了。下面就是一些常用的时间函数：
下面来说一下 DATE_FORMAT(date,format)：format 可以有以下格式符：
| Specifier Description | |
| %a Abbreviated weekday name (Sun..Sat) | |
| %b Abbreviated month name (Jan..Dec) | |
| %c Month, numeric (0..12) | |
| %D Day of the month with English suffix (0th, 1st, 2nd, 3rd, …) | |
| %d Day of the month, numeric (00..31) | |
| %e Day of the month, numeric (0..31) | |
| %f Microseconds (000000..999999) | |
| %H Hour (00..23) | |
| %h Hour (01..12) | |
| %I Hour (01..12) | |
| %i Minutes, numeric (00..59) | |
| %j Day of year (001..366) | |
| %k Hour (0..23) | |
| %l Hour (1..12) | |
| %M Month name (January..December) | |
| %m Month, numeric (00..12) | |
| %p AM or PM | |
| %r Time, 12-hour (hh:mm:ss followed by AM or PM) | |
| %S Seconds (00..59) | |
| %s Seconds (00..59) | |
| %T Time, 24-hour (hh:mm:ss) | |
| %U Week (00..53), where Sunday is the first day of the week; WEEK() mode 0 | |
| %u Week (00..53), where Monday is the first day of the week; WEEK() mode 1 | |
| %V Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X | |
| %v Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x | |
| %W Weekday name (Sunday..Saturday) | |
| %w Day of the week (0=Sunday..6=Saturday) | |
| %X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V | |
| %x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v | |
| %Y Year, numeric, four digits | |
| %y Year, numeric (two digits) | |
| %% A literal % character | |
| %x x, for any“x”not listed above |
举个例子吧！
| mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y'); | |
| +------------------------------------------------+ | |
| | DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y') | | |
| +------------------------------------------------+ | |
| | Sunday October 2009 | | |
| +------------------------------------------------+ | |
| 1 row in set (0.00 sec) | |
| mysql> SELECT DATE_FORMAT(now(),'%H %k %I %r %T %S %w'); | |
| +-------------------------------------------+ | |
| | DATE_FORMAT(now(),'%H %k %I %r %T %S %w') | | |
| +-------------------------------------------+ | |
| | 23 23 11 11:49:57 PM 23:49:57 57 5 | | |
| +-------------------------------------------+ | |
| 1 row in set (0.00 sec) |
再来看一下 DATE_ADD(date,INTERVAL expr unit)：其中 INTERVAL 是关键字，expr 是一个表达式，unit 为间隔类型，MySQL 提供如下的间隔类型:
| unit Value Expected expr Format | |
| MICROSECOND MICROSECONDS | |
| SECOND SECONDS | |
| MINUTE MINUTES | |
| HOUR HOURS | |
| DAY DAYS | |
| WEEK WEEKS | |
| MONTH MONTHS | |
| QUARTER QUARTERS | |
| YEAR YEARS | |
| SECOND_MICROSECOND 'SECONDS.MICROSECONDS' | |
| MINUTE_MICROSECOND 'MINUTES:SECONDS.MICROSECONDS' | |
| MINUTE_SECOND 'MINUTES:SECONDS' | |
| HOUR_MICROSECOND 'HOURS:MINUTES:SECONDS.MICROSECONDS' | |
| HOUR_SECOND 'HOURS:MINUTES:SECONDS' | |
| HOUR_MINUTE 'HOURS:MINUTES' | |
| DAY_MICROSECOND 'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS' | |
| DAY_SECOND 'DAYS HOURS:MINUTES:SECONDS' | |
| DAY_MINUTE 'DAYS HOURS:MINUTES' | |
| DAY_HOUR 'DAYS HOURS' | |
| YEAR_MONTH 'YEARS-MONTHS' |
间隔类型
举个例子吧！
| mysql> SELECT DATE_ADD('2000-12-31 23:59:59',INTERVAL 1 SECOND); | |
| +---------------------------------------------------+ | |
| | DATE_ADD('2000-12-31 23:59:59',INTERVAL 1 SECOND) | | |
| +---------------------------------------------------+ | |
| | 2001-01-01 00:00:00 | | |
| +---------------------------------------------------+ | |
| 1 row in set (0.00 sec) | |
| mysql> SELECT DATE_ADD('2100-12-31 23:59:59',INTERVAL '1:1' MINUTE_SECOND); | |
| +--------------------------------------------------------------+ | |
| | DATE_ADD('2100-12-31 23:59:59',INTERVAL '1:1' MINUTE_SECOND) | | |
| +--------------------------------------------------------------+ | |
| | 2101-01-01 00:01:00 | | |
| +--------------------------------------------------------------+ | |
| 1 row in set (0.00 sec) |
4. 流程函数
流程函数也是很常用的一类函数，用户可以使用这类函数在一个 SQL 语句中实现条件选择，这样做能够提高语句的效率。
举例：
现在有员工工资表如下：
| mysql> select * from salary; | |
| +----+---------+ | |
| | id | salary | | |
| +----+---------+ | |
| | 1 | 1000.00 | | |
| | 2 | 2000.00 | | |
| | 3 | 3000.00 | | |
| | 4 | 4000.00 | | |
| | 5 | 5000.00 | | |
| | 6 | 6000.00 | | |
| | 7 | 7000.00 | | |
| | 8 | NULL | | |
| +----+---------+ | |
| 8 rows in set (0.00 sec) |
（1）当工资高于 5000 就显示高工资，低于 5000 的就显示低工资：
| mysql> select id,if(salary>5000,'高工资 ',' 低工资') as level from salary; | |
| +----+-----------+ | |
| | id | level | | |
| +----+-----------+ | |
| | 1 | 低工资 | | |
| | 2 | 低工资 | | |
| | 3 | 低工资 | | |
| | 4 | 低工资 | | |
| | 5 | 低工资 | | |
| | 6 | 高工资 | | |
| | 7 | 高工资 | | |
| | 8 | 低工资 | | |
| +----+-----------+ | |
| 8 rows in set (0.00 sec) |
（2）当没有工资的时候，用 0 代替：
| mysql> select ifnull(salary,0) from salary; | |
| +------------------+ | |
| | ifnull(salary,0) | | |
| +------------------+ | |
| | 1000.00 | | |
| | 2000.00 | | |
| | 3000.00 | | |
| | 4000.00 | | |
| | 5000.00 | | |
| | 6000.00 | | |
| | 7000.00 | | |
| | 0.00 | | |
| +------------------+ | |
| 8 rows in set (0.00 sec) |
（3）用 case when 来实现例子 1：
| mysql> select id,case when salary>5000 then '高工资 ' else ' 低工资' end from salary; | |
| +----+-------------------------------------------------------------+ | |
| | id | case when salary>5000 then '高工资 ' else ' 低工资' end | | |
| +----+-------------------------------------------------------------+ | |
| | 1 | 低工资 | | |
| | 2 | 低工资 | | |
| | 3 | 低工资 | | |
| | 4 | 低工资 | | |
| | 5 | 低工资 | | |
| | 6 | 高工资 | | |
| | 7 | 高工资 | | |
| | 8 | 低工资 | | |
| +----+-------------------------------------------------------------+ | |
| 8 rows in set (0.00 sec) |
（4）当工资是 1000 的为特低工资，工资为 2000 的为低工资，其余为高工资：
| mysql> select id,case salary when 1000 then '特低工资 ' when 2000 then ' 低工资 ' else ' 高工资' end from salary; | |
| +----+-------------------------------------------------------------------------------------------+ | |
| | id | case salary when 1000 then '特低工资 ' when 2000 then ' 低工资 ' else ' 高工资' end | | |
| +----+-------------------------------------------------------------------------------------------+ | |
| | 1 | 特低工资 | | |
| | 2 | 低工资 | | |
| | 3 | 高工资 | | |
| | 4 | 高工资 | | |
| | 5 | 高工资 | | |
| | 6 | 高工资 | | |
| | 7 | 高工资 | | |
| | 8 | 高工资 | | |
| +----+-------------------------------------------------------------------------------------------+ | |
| 8 rows in set (0.00 sec) |
5. 其他函数
MySQL 内置的函数还有很多，这里就不做一一介绍，具体的可以参考官方的文档，里面也有举了很多的栗子：https://dev.mysql.com/doc/refman/5.7/en/functions.html
自定义函数
用户可以自定义函数，语法如下：
| CREATE FUNCTION func_name([parameter1,parameter2...]) | |
| RETURNS type | |
| runtime_body |
下面就来创建一个函数，比如自定义一个两个整数的加法函数：
| DELIMITER $ | |
| CREATE FUNCTION mysum(n1 INT,n2 INT) RETURNS INT | |
| BEGIN | |
| DECLARE num int DEFAULT 0; | |
| SET num = n1 + n2; | |
| RETURN(NUM); | |
| END $ | |
| DELIMITER ; |
执行函数使用 SELECT：
| mysql> SELECT mysum(1,2); | |
| +------------+ | |
| | mysum(1,2) | | |
| +------------+ | |
| | 3 | | |
| +------------+ | |
| 1 row in set (0.00 sec) |
删除自定函数使用:
DROP FUNCTION func_name
比如删除上面创建的函数：
| mysql> drop function mysum; | |
| Query OK, 0 rows affected (0.00 sec) |
自定义函数暂时写到这里，更详细的会在下一篇博客中和存储过程一起介绍。
本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-09/147064.htm
正文完
星哥玩云-微信公众号
