使用函数的方便性我就不多说了,在MySQL数据库中,函数可以使用在SELECT语句及其字句(例如WHERE、ORDER BY、HAVING等)中,也可以用在UPDATE、DELETE语句及其字句中。

一、字符串函数

注意:下面只是演示函数的作用,并未选择具体的数据库及表

1. CONCAT( s1,s2,...sn) 函数:把传入的参数连接成一个字符串

注意:任何字符串与NULL进行连接的结果都将是NULL。

mysql> select concat('aaa','bbb','ccc'),concat('aaa',null);
+---------------------------+--------------------+
| concat('aaa','bbb','ccc') | concat('aaa',null) |
+---------------------------+--------------------+
| aaabbbccc                 | NULL               |
+---------------------------+--------------------+
1 row in set (0.00 sec)

2. INSERT(str,x,y,instr)函数:将字符串str从第x位置开始,y个字符长度的字串替换为instr

mysql> select insert('beijing2008you',12,3,'me');
+------------------------------------+
| insert('beijing2008you',12,3,'me') |
+------------------------------------+
| beijing2008me                      |
+------------------------------------+
1 row in set (0.00 sec)

3. LOWER(str)和UPPER(str)函数:把字符串转化成小写或大写

mysql> select lower('BEIJING2008'),UPPER('beijing2008');
+----------------------+----------------------+
| lower('BEIJING2008') | UPPER('beijing2008') |
+----------------------+----------------------+
| beijing2008          | BEIJING2008          |
+----------------------+----------------------+
1 row in set (0.00 sec)

4. LEFT(str,x) 和 RIGHT(str,x)函数:分别返回字符串str最左/最右的x个字符

mysql> select left('beijing2008',7),left('beijing2008',null),right('beijing2008',4),right('beijing2008',null);
+-----------------------+--------------------------+------------------------+---------------------------+
| left('beijing2008',7) | left('beijing2008',null) | right('beijing2008',4) | right('beijing2008',null) |
+-----------------------+--------------------------+------------------------+---------------------------+
| beijing               | NULL                     | 2008                   | NULL                      |
+-----------------------+--------------------------+------------------------+---------------------------+
1 row in set (0.00 sec)

注意:参数x不可省略,当为null时,返回null。

5. LPAD(str, n, pad) 和 RPAD( str, n, pad) 函数:用字符串pad对str最左边和最右边进行填充使得整个字符串长度为n

mysql> select lpad('2008',20,'beijing'),rpad('beijing',20,'2008');
+---------------------------+---------------------------+
| lpad('2008',20,'beijing') | rpad('beijing',20,'2008') |
+---------------------------+---------------------------+
| beijingbeijingbe2008      | beijing2008200820082      |
+---------------------------+---------------------------+
1 row in set (0.00 sec)

6. LTRIM(str)和 RTRIM(str)函数:去掉字符串str左侧和右侧的空格

mysql> select ltrim('    |beijing'),rtrim('beijing|   ');
+-----------------------+----------------------+
| ltrim('    |beijing') | rtrim('beijing|   ') |
+-----------------------+----------------------+
| |beijing              | beijing|             |
+-----------------------+----------------------+
1 row in set (0.00 sec)

7. REPEAT(str, x) 函数:将字符串str重复x次

mysql> select repeat('mysql ',3);
+--------------------+
| repeat('mysql ',3) |
+--------------------+
| mysql mysql mysql  |
+--------------------+
1 row in set (0.00 sec)

8. REPLACE( str, a, b)函数:用字符串b替换str中出现的所有字符串a

mysql> select replace('beijing_2010','_2010','2008');
+----------------------------------------+
| replace('beijing_2010','_2010','2008') |
+----------------------------------------+
| beijing2008                            |
+----------------------------------------+
1 row in set (0.00 sec)

9. STRCMP(s1, s2)函数:比较字符串s1和s2的ASCII码值的大小

如果s1比s2小返回-1,相等返回0,s1比s2大返回1 。

mysql> select strcmp('a','b'),strcmp('a','a'),strcmp('c','b');
+-----------------+-----------------+-----------------+
| strcmp('a','b') | strcmp('a','a') | strcmp('c','b') |
+-----------------+-----------------+-----------------+
|              -1 |               0 |               1 |
+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)

10. TRIM(str)函数:去掉字符串str开头和结尾的空格

mysql> select trim('  $ beijing2008 $      ');
+---------------------------------+
| trim('  $ beijing2008 $      ') |
+---------------------------------+
| $ beijing2008 $                 |
+---------------------------------+
1 row in set (0.00 sec)

11. SUBSTRING(str, x, y)函数:返回字符串str中的第x位置起长度为y的字符串

mysql> select substring('beijing2008',8,4);
+------------------------------+
| substring('beijing2008',8,4) |
+------------------------------+
| 2008                         |
+------------------------------+
1 row in set (0.00 sec)
该函数常用来提取字串

二、数值函数

方便的处理很多数值方面的运算。

1. ABS(X)函数:返回x的绝对值

mysql> select abs(-0.8),abs(0.8);
+-----------+----------+
| abs(-0.8) | abs(0.8) |
+-----------+----------+
|       0.8 |      0.8 |
+-----------+----------+
1 row in set (0.00 sec)

2. CEIL(X)函数:对数x向上取整

mysql> select ceil(-0.8),ceil(0.8);
+------------+-----------+
| ceil(-0.8) | ceil(0.8) |
+------------+-----------+
|          0 |         1 |
+------------+-----------+
1 row in set (0.00 sec)

3. FLOOR(X)函数:对数x向下取整

mysql> select floor(-0.8),floor(0.8);
+-------------+------------+
| floor(-0.8) | floor(0.8) |
+-------------+------------+
|          -1 |          0 |
+-------------+------------+
1 row in set (0.00 sec)

4. MOD(X, Y)函数:返回x/y的模(取余数)

mysql> select mod(15,10),mod(1,11),mod(null,10),mod(1,null),mod(0,2),mod(2,0);
+------------+-----------+--------------+-------------+----------+----------+
| mod(15,10) | mod(1,11) | mod(null,10) | mod(1,null) | mod(0,2) | mod(2,0) |
+------------+-----------+--------------+-------------+----------+----------+
|          5 |         1 |         NULL |        NULL |        0 |     NULL |
+------------+-----------+--------------+-------------+----------+----------+
1 row in set (0.00 sec)

注意: x或y任意一个为null时结果为null,y为0时结果也为null。

5. RAND()函数:返回0~1内的随机数

mysql> select rand(),rand();     每次随机的数字都不一样
+--------------------+--------------------+
| rand()             | rand()             |
+--------------------+--------------------+
| 0.5769799859979935 | 0.8579178544300775 |
+--------------------+--------------------+
1 row in set (0.00 sec)

用该函数做相应变化可以得到指定范围内的随机数,如需要0~100内的随机整数:

mysql> select ceil(100*rand()),floor(100*rand());   向下能取到0,向上能取到100
+------------------+-------------------+
| ceil(100*rand()) | floor(100*rand()) |
+------------------+-------------------+
|               56 |                21 |
+------------------+-------------------+
1 row in set (0.00 sec)

6. ROUND(x, y) 函数:返回参数x的四舍五入的有y位小数的值

如果y省略则默认y为0,即不留小数位;这个函数通常用来统一数据格式。

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

注意: 对于整数即使规定了小数位也不会补0,这与老版本有所区别。

7. TRUNCATE(X, Y)函数:将数字x进行截断,只保留y位小数

该函数只是截断,而round函数是四舍五入,当截断位数不足时补0。

mysql> select round(1.235,2),truncate(1.235,2),truncate(1.2,2);
+----------------+-------------------+-----------------+
| round(1.235,2) | truncate(1.235,2) | truncate(1.2,2) |
+----------------+-------------------+-----------------+
|           1.24 |              1.23 |            1.20 |
+----------------+-------------------+-----------------+
1 row in set (0.00 sec)

三、日期和时间函数

时间函数可以实现很多功能,比如当前时间是多少,下个月的今天是星期几,统计前天之前的总收入等等。

1. CURDATE() 函数:返回当前时间,只有年月日

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2018-12-13 |
+------------+
1 row in set (0.00 sec)

2. CURTIME()函数:返回当前时间,只有时分秒

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 16:20:52  |
+-----------+
1 row in set (0.00 sec)

3. NOW()函数:返回当前的日期和时间,包含年月日时分秒

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2018-12-13 16:22:24 |
+---------------------+
1 row in set (0.00 sec)

4. UNIX_TIMESTAMP(date)函数:返回日期date的unix时间戳

mysql> select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
|            1544689715 |
+-----------------------+
1 row in set (0.00 sec)

5. FROM_UNIXTIME(unixtime)函数:返回unixtime时间戳的日期值;与上面的函数互为逆操作

mysql> select from_unixtime(1544689715);
+---------------------------+
| from_unixtime(1544689715) |
+---------------------------+
| 2018-12-13 16:28:35       |
+---------------------------+
1 row in set (0.00 sec)

6. WEEK(DATE)和YEAR(DATE)函数:返回所给date是哪一年,是一年中第几周

mysql> select week(now()),year(now());
+-------------+-------------+
| week(now()) | year(now()) |
+-------------+-------------+
|          49 |        2018 |
+-------------+-------------+
1 row in set (0.00 sec)

7. HOUR(time)、MINUTE(time)和SECOND(time)函数:返回所给时间的小时/分钟/秒

mysql> select hour(curtime()),minute(curtime()),second(curtime());
+-----------------+-------------------+-------------------+
| hour(curtime()) | minute(curtime()) | second(curtime()) |
+-----------------+-------------------+-------------------+
|              16 |                38 |                31 |
+-----------------+-------------------+-------------------+
1 row in set (0.00 sec)

8. MONTHNAME(date)和DAYNAME(date)函数:返回date的英文月份/星期名称

mysql> select monthname(now()),dayname(now());
+------------------+----------------+
| monthname(now()) | dayname(now()) |
+------------------+----------------+
| December         | Thursday       |
+------------------+----------------+
1 row in set (0.00 sec)

9. DATE_FORMAT(date, fmt) 函数:按字符串格式fmt格式化日期date的值

关于fmt格式,有很多,硬记有点困难,下面介绍几个常用的:

mysql> select date_format(now(),'%M, %D, %Y'),date_format(now(),'%m. %d, %y');
+---------------------------------+---------------------------------+
| date_format(now(),'%M, %D, %Y') | date_format(now(),'%m. %d, %y') |
+---------------------------------+---------------------------------+
| December, 13th, 2018            | 12. 13, 18                      |
+---------------------------------+---------------------------------+
1 row in set (0.00 sec)

10. DATE_ADD(date,INTERVAL expr type) 函数:返回与所给日期date相差一定时间间隔的日期

INTERVAL是间隔类型关键字,expr是表达式,type是表达式对应的类型(MySQL中大概有13种)。

mysql> select now() current,date_add(now(),interval 31 day) after31days,date_add(now(),interval '1_2' year_month) after_oneyear_twomonth;
+---------------------+---------------------+------------------------+
| current             | after31days         | after_oneyear_twomonth |
+---------------------+---------------------+------------------------+
| 2018-12-13 17:02:17 | 2019-01-13 17:02:17 | 2020-02-13 17:02:17    |
+---------------------+---------------------+------------------------+
1 row in set (0.00 sec)

当然,上面的表达式还可以添加‘-’表示它之前的某个日期时间。

11. DATEDIFF(date1,date2)函数:计算两个日期之间相差(date1-date2)的天数

mysql> select datediff('2008-08-08',now());
+------------------------------+
| datediff('2008-08-08',now()) |
+------------------------------+
|                        -3779 |
+------------------------------+
1 row in set (0.00 sec)

四、流程函数

流程函数可以在一个SQL语句中实现条件选择,提高语句效率。

首先我们新建一个职员薪水表并插入一些数据,接下来的例子将使用该表的内容:

mysql> use test1;
Database changed
mysql> create table salary (userid int,salsry decimal(9,2));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into salary values(1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from salary;
+--------+---------+
| userid | salsry  |
+--------+---------+
|      1 | 1000.00 |
|      2 | 2000.00 |
|      3 | 3000.00 |
|      4 | 4000.00 |
|      5 | 5000.00 |
|      1 |    NULL |
+--------+---------+
6 rows in set (0.00 sec)

1. IF( value, t, f)函数:如果value为真返回t,否则返回f

mysql> select if(salsry > 2000,'high','low') from salary;  这里就通过一个条件将所有工资进行分类
+--------------------------------+
| if(salsry > 2000,'high','low') |
+--------------------------------+
| low                            |
| low                            |
| high                           |
| high                           |
| high                           |
| low                            |
+--------------------------------+
6 rows in set (0.00 sec)

2. IFNULL(value1,value2)函数:value1不为空返回value1,否则返回value2

该函数常用来替换表中的null值。

mysql> select ifnull(salsry,0) from salary;
+------------------+
| ifnull(salsry,0) |
+------------------+
|          1000.00 |
|          2000.00 |
|          3000.00 |
|          4000.00 |
|          5000.00 |
|             0.00 |
+------------------+
6 rows in set (0.00 sec)

3. CASE [expr] WHEN [value1] THEN [result1] ......ELSE [default] END函数:按条件分类

mysql> select case when salsry>2000 then 'high' else 'low' end from salary;
+--------------------------------------------------+
| case when salsry>2000 then 'high' else 'low' end |
+--------------------------------------------------+
| low                                              |
| low                                              |
| high                                             |
| high                                             |
| high                                             |
| low                                              |
+--------------------------------------------------+
6 rows in set (0.00 sec)

mysql> select case salsry when 1000 then 'low' when 2000 then 'mid' else 'high' end from salary;
+-----------------------------------------------------------------------+
| case salsry when 1000 then 'low' when 2000 then 'mid' else 'high' end |
+-----------------------------------------------------------------------+
| low                                                                   |
| mid                                                                   |
| high                                                                  |
| high                                                                  |
| high                                                                  |
| high                                                                  |
+-----------------------------------------------------------------------+
6 rows in set (0.00 sec)

用该函数可以实现多分类。

五、其它常用函数

1. DATABASE()函数:返回当前数据库名

mysql> select database();
+------------+
| database() |
+------------+
| test1      |
+------------+
1 row in set (0.00 sec)

2. VERDION()函数:返回当前数据库版本

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.17-log |
+------------+
1 row in set (0.00 sec)

3. USER()函数:返回当前登陆用户名

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@ddkk.com |
+----------------+
1 row in set (0.00 sec)

4. INET_ATON(IP)函数:返回IP地址的网络字节序表示

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

5. INET_NTOA(num)函数:返回网络字节序代表的IP地址

mysql> select inet_ntoa(3232235777)
    -> ;
+-----------------------+
| inet_ntoa(3232235777) |
+-----------------------+
| 192.168.1.1           |
+-----------------------+
1 row in set (0.00 sec)

上面两个函数的主要作用是对IP的相互转化,它可以实现ip地址的比较,比如某个表里存了很多IP地址,你如要想要找到ip在‘192.168.1.3’和‘192.168.1.20’之间一共有多少个IP地址时,如果直接用ip比较,那么它会一个字符一个字符的比较,比较到3和2时由于3大于2导致这两个ip之间是没有其它ip的,这显然不对,因此需要转为网络字节序来进行比较:

select * from t where inet_aton(ip) >= inet_aton('192.168.1.3') and inet_aton(ip) <= inet_aton('192.168.1.20');

这样就可以将表t中ip字段在指定值之间的所有IP查找出来。

6. PASSWORD(str)函数:返回字符串str的加密版本,一个41位长的字符串

注意:此函数只用来加密系统用户的密码,表内数据的加密需要使用MD5等函数。

mysql> select password('123456');
+-------------------------------------------+
| password('123456')                        |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

7. MD5(str)函数:返回字符串str的MD5值,可对数据加密

mysql> select MD5('123456');
+----------------------------------+
| MD5('123456')                    |
+----------------------------------+
| e10adc3949ba59abbe56e057f20f883e |
+----------------------------------+
1 row in set (0.00 sec)

MySQL中还有很多非常有用的内建函数,后面遇到再补充,平时在使用时注意查看MySQL内是否有相应函数,使用它们可以极大提高效率。