(MariaDB)MySQL内置函数大全

Abel617 等级 339 0 0

1. 字符串函数

完整的内置字符串函数见官方手册

1.1 字符串连接函数

有两个字符串连接函数:concat(s1,s2,s3,...,sN)和concat_ws(sep,s1,s2,s3,...,sN)。

concat()将多个字符串连接起来形成一个长字符串。它会尝试将字符全部转换为字符型,如果存在null,则直接返回null。

mysql> select concat('a','b',1),concat(1,2,3),concat('a',null);
+-------------------+---------------+------------------+
| concat('a','b',1) | concat(1,2,3) | concat('a',null) |
+-------------------+---------------+------------------+
| ab1               | 123           | NULL             |
+-------------------+---------------+------------------+
1 row in set 

concat_ws(sep,s1,s2,...,sN)函数是concat()函数的特殊格式,它的第一个参数sep是用于连接s1,s2,...,sN的分隔符。分隔符可以是一个字符或一个字符串,只要合理即可。如果分隔符sep为null,则返回结果null,如果s1,s2,...,sN之间出现了null,则忽略null。

mysql> select concat_ws(':','23','59','58'),concat_ws('-','1st','2nd'),concat_ws('XXX','wo','shi');
+-------------------------------+----------------------------+-----------------------------+
| concat_ws(':','23','59','58') | concat_ws('-','1st','2nd') | concat_ws('XXX','wo','shi') |
+-------------------------------+----------------------------+-----------------------------+
| 23:59:58                      | 1st-2nd                    | woXXXshi                    |
+-------------------------------+----------------------------+-----------------------------+
1 row in set

mysql> select concat_ws(':','23','59',null,'58'),concat_ws(null,'1st','2nd');
+------------------------------------+-----------------------------+
| concat_ws(':','23','59',null,'58') | concat_ws(null,'1st','2nd') |
+------------------------------------+-----------------------------+
| 23:59:58                           | NULL                        |
+------------------------------------+-----------------------------+
1 row in set 

由于concat()遇到null时总会返回null,这种处理方式可能并非所期望的结果,因此可以采用concat_ws()的方式忽略null或者采用ifnull()的方式将null转换为空字符串。

1.2 lower(string)、upper(string)、left(string,x)、right(string,x)

分别是变小写、变大写、从左取x长度字符、从右取x长度字符

mysql> select lower('MaLong'),upper('MaLong'),left('MaLong',3),right('Malong',3);
+-----------------+-----------------+------------------+-------------------+
| lower('MaLong') | upper('MaLong') | left('MaLong',3) | right('Malong',3) |
+-----------------+-----------------+------------------+-------------------+
| malong          | MALONG          | MaL              | ong               |
+-----------------+-----------------+------------------+-------------------+
1 row in set 

1.3 填充函数

有两种:lpad(string,n,pad)和rpad(string,n,pad)。

使用pad对string最左边和最右边进行填充,直到填充后总长度为n个字符。pad可以是一个字符串,如果是字符串则从左向右取直到符合长度为止。

mysql> select lpad('MaLong',10,'x'),lpad('MaLong',10,'xy'),rpad('MaLong',10,'x');
+-----------------------+------------------------+-----------------------+
| lpad('MaLong',10,'x') | lpad('MaLong',10,'xy') | rpad('MaLong',10,'x') |
+-----------------------+------------------------+-----------------------+
| xxxxMaLong            | xyxyMaLong             | MaLongxxxx            |
+-----------------------+------------------------+-----------------------+
1 row in set 

长度n可以是小于或等于string字符串长度的值,此时lpad或者rpad的作用都是从左进行字符串截取而非填充,直到长度为n。也就是说lpad和rpad函数最强约束条件是长度参数n。

mysql> select rpad('MaLong',3,'x'),lpad('MaLong',3,'x'),lpad('MaLong',0,'x');
+----------------------+----------------------+----------------------+
| rpad('MaLong',3,'x') | lpad('MaLong',3,'x') | lpad('MaLong',0,'x') |
+----------------------+----------------------+----------------------+
| MaL                  | MaL                  |                      |
+----------------------+----------------------+----------------------+
1 row in set 

1.4 trim(string)、ltrim(string)、rtrim(sting)及trim(substring from string)

分别用来消除string行首和行尾、行首、行尾的空格以及行首行尾指定的字符串。

 函数                    作用
-----------------------         -------------------------------
      ltrim(string)             删除行首空格
      rtrim(string)             删除行尾空格
      trim(string)              删除行首和行尾空格
trim(substring from string)     删除行首和行尾的字符串substring 

例如:

mysql> select length(trim(' MaLong ')) as A,
              length(ltrim('MaLong ')) as B,
              length(ltrim(' MaLong ')) as C,
              length(rtrim(' MaLong ')) as D;
+---+---+---+---+
| A | B | C | D |
+---+---+---+---+
| 6 | 7 | 7 | 7 |
+---+---+---+---+
1 row in set (0.00 sec)

mysql> select trim('xy' from 'xyxabxycdxyxy');
+---------------------------------+
| trim('xy' from 'xyxabxycdxyxy') |
+---------------------------------+
| xabxycd                         |
+---------------------------------+
1 row in set 

1.5 重复字符串repeat(string,x)

将string重复x次。

mysql> select repeat('xy',3),length(repeat(' ',3)),repeat('0',3);
+----------------+-----------------------+---------------+
| repeat('xy',3) | length(repeat(' ',3)) | repeat('0',3) |
+----------------+-----------------------+---------------+
| xyxyxy         |                     3 | 000           |
+----------------+-----------------------+---------------+
1 row in set 

1.6 字符串替换函数replace(string,a,b)

使用字符串b替换字符串string中所有的字符串a。注意点是它们都可以是字符串。如果想要替换掉的字符串a不在string中,则不会进行替换。

mysql> select replace('woshiMaLongShuai','s','xxxx'),replace('woshiMaLongShuai','ob','xxxx');
+----------------------------------------+-----------------------------------------+
| replace('woshiMaLongShuai','s','xxxx') | replace('woshiMaLongShuai','ob','xxxx') |
+----------------------------------------+-----------------------------------------+
| woxxxxhiMaLongShuai                    | woshiMaLongShuai                        |
+----------------------------------------+-----------------------------------------+
1 row in set 

1.7 字符串插入替换函数insert(string,p1,len,instead_string)

将string从位置p1开始,len个长度的字符替换为instead_string。

mysql> select insert('woshimalongshuai',6,2,'gao');
+--------------------------------------+
| insert('woshimalongshuai',6,2,'gao') |
+--------------------------------------+
| woshigaolongshuai                    |
+--------------------------------------+
1 row in set 

1.8 字符串提取substring(string,x,y)

返回string中从x位置开始y个长度的字符串。如果给出的位置不存在,则无法提取所以返回空。如果给出的长度超出,则只提取允许范围内的字符串。

mysql> select substring('MaLo',3,4) AS A,substring('MaLo',0,4) AS B,substring('MaLo',10,4) AS C,length(substring('MaLo',3,10)) AS D;
+----+---+---+---+
| A  | B | C | D |
+----+---+---+---+
| Lo |   |   | 2 |
+----+---+---+---+
1 row in set (0.00 sec) 

1.9 字符串比较函数strcmp(string1,string2)

比较string1和string2的ascii码大小,从前向后依次比较。strcmp认为大小写字母是等价的,所以它们相等。且存在null时,直接返回null。

  • 如果string1小于string2,返回-1。
  • 如果string1等于string2,返回0。
  • 如果string1大于string2,返回1。
mysql> select strcmp('a','b'),strcmp('a','A'),strcmp('b','a');
+-----------------+-----------------+-----------------+
| strcmp('a','b') | strcmp('a','A') | strcmp('b','a') |
+-----------------+-----------------+-----------------+
|              -1 |               0 |               1 |
+-----------------+-----------------+-----------------+
1 row in set
mysql> select strcmp('ac','ab'),strcmp('ac','ac'),strcmp('a',null),strcmp(null,'a');
+-------------------+-------------------+------------------+------------------+
| strcmp('ac','ab') | strcmp('ac','ac') | strcmp('a',null) | strcmp(null,'a') |
+-------------------+-------------------+------------------+------------------+
|                 1 |                 0 | NULL             | NULL             |
+-------------------+-------------------+------------------+------------------+
1 row in set 

关于字符串比较,另外两个函数least()和greatest()也能实现,这两个函数更多的用于取最值,特别是用于数值比较,所以在后文解释。

1.10 字符串长度函数length(string)和char_length(string)

length()返回字符串的字节数,注意不是字符数,char_length()返回的才是字符数。在SQL Server中长度函数是len(string),且返回的是字符数。

mysql> select length('woshiyigeren'),length('我');
+------------------------+--------------+
| length('woshiyigeren') | length('我') |
+------------------------+--------------+
|                     12 |            3 |
+------------------------+--------------+
1 row in set

mysql> select char_length('woshiyigeren'),char_length('我');
+-----------------------------+-------------------+
| char_length('woshiyigeren') | char_length('我') |
+-----------------------------+-------------------+
|                          12 |                 1 |
+-----------------------------+-------------------+
1 row in set 

在SQL Server中:

(MariaDB)MySQL内置函数大全

1.11 字符串位置函数locate(sub_str,string)、position(sub_str in string)和instr(str,sub_str)

这三个函数的作用相同,都是返回sub_str在string中的开始位置。和SQL Server中的charindex()函数功能类似。

mysql> SELECT LOCATE('ball','football'),POSITION('ball' IN 'football') ,INSTR('football','ball');
+---------------------------+--------------------------------+--------------------------+
| LOCATE('ball','football') | POSITION('ball' IN 'football') | INSTR('football','ball') |
+---------------------------+--------------------------------+--------------------------+
|                         5 |                              5 |                        5 |
+---------------------------+--------------------------------+--------------------------+
1 row in set 

1.12 字符串位置函数find_in_set(sub_string,str_set)

返回子串sub_string在str_set中的位置,其中str_set是一个由逗号隔开的多个字符串集合。如果找不到位置(sub_str不在str_set中或者str_set为空串)则返回0,如果任意一个为null,则返回null。

mysql> select find_in_set('ab','cd,ab,dc'),find_in_set('ab',''),find_in_set(null,'ab,cd');
+------------------------------+----------------------+---------------------------+
| find_in_set('ab','cd,ab,dc') | find_in_set('ab','') | find_in_set(null,'ab,cd') |
+------------------------------+----------------------+---------------------------+
|                            2 |                    0 | NULL                      |
+------------------------------+----------------------+---------------------------+
1 row in set 

1.13 字符串位置函数field(s,str1,str2,...,strN)

返回字符串s在字符串集合str1,str2,...,strN中的位置。如果找不到或者字符串s为null,则返回0,因为null无法进行比较,也就是找不到。

mysql> select field('ab','abc','1ab','ab','cd') as col1,field(null,'ab','cd') as col2;
+------+------+
| col1 | col2 |
+------+------+
|    3 |    0 |
+------+------+
1 row in set (0.00 sec) 

1.14 指定位置的字符串函数elt(n,str1,str2,...,strN)

elt表示从(数据)仓库中提取需要的东西。n是位置,n=1则返回str1,n=2则返回str2,依次类推。当n<1或者大于字符串的数量,则返回null。

mysql> select elt(1,'a','b','c'),elt(2,'a','b','c'),elt(0,'a','b'),elt(10,'a','b');
+--------------------+--------------------+----------------+-----------------+
| elt(1,'a','b','c') | elt(2,'a','b','c') | elt(0,'a','b') | elt(10,'a','b') |
+--------------------+--------------------+----------------+-----------------+
| a                  | b                  | NULL           | NULL            |
+--------------------+--------------------+----------------+-----------------+
1 row in set 

1.15 字符串反转函数reverse(str)

反转字符串str的字符顺序。

mysql> select reverse('hello');
+------------------+
| reverse('hello') |
+------------------+
| olleh            |
+------------------+
1 row in set (0.00 sec) 

2. 数学函数

完整的内置数学函数见官方手册

2.1 绝对值函数ABS(x)

mysql> select abs(0.9),abs(0),abs(-0.9);
+----------+--------+-----------+
| abs(0.9) | abs(0) | abs(-0.9) |
+----------+--------+-----------+
| 0.9      |      0 | 0.9       |
+----------+--------+-----------+
1 row in set 

2.2 取模函数mod(x,y)

取x/y后的余数。支持小数和负数。如果除数为0或者除数被除数有一个为null,则返回null。

mysql> select mod(31,8),mod(31.56,8),mod(-31.56,8),mod(31,0),mod(0,8);
+-----------+--------------+---------------+-----------+----------+
| mod(31,8) | mod(31.56,8) | mod(-31.56,8) | mod(31,0) | mod(0,8) |
+-----------+--------------+---------------+-----------+----------+
|         7 | 7.56         | -7.56         | NULL      |        0 |
+-----------+--------------+---------------+-----------+----------+
1 row in set 

2.3 四舍五入函数round(x,y)

返回值x含有y位小数的四舍五入后的结果,如果省略y,则默认y为0。

mysql> select round(3.15),round(3.15,1),round(-3.15),round(-3.15,1);
+-------------+---------------+--------------+----------------+
| round(3.15) | round(3.15,1) | round(-3.15) | round(-3.15,1) |
+-------------+---------------+--------------+----------------+
| 3           | 3.2           | -3           | -3.2           |
+-------------+---------------+--------------+----------------+
1 row in set 

2.4 位数截断函数truncate(x,y)

截断x的小数位数使得最终保留y个小数位。它的用法和round(x,y)几乎一样,只不过truncate是用来截断而不用来四舍五入。不能省略y但可以等于0,且y不能为负数。

mysql> select truncate(3.156,2),truncate(3.156,0);
+-------------------+-------------------+
| truncate(3.156,2) | truncate(3.156,0) |
+-------------------+-------------------+
| 3.15              | 3                 |
+-------------------+-------------------+
1 row in set 

2.5 地板函数floor(x)和天花板函数ceiling(x)

地板函数返回比x小的最大整数,天花板函数返回比x大的最小整数。

mysql> select floor(3.4),floor(-3.4),ceiling(3.4),ceiling(-3.4);
+------------+-------------+--------------+---------------+
| floor(3.4) | floor(-3.4) | ceiling(3.4) | ceiling(-3.4) |
+------------+-------------+--------------+---------------+
|          3 |          -4 |            4 |            -3 |
+------------+-------------+--------------+---------------+
1 row in set 

2.6 随机函数rand()

每次随机返回一个0-1之间不包括0和1的数,且每次运行结果都不同。

mysql> select rand(),rand();
+--------------------+----------------------+
| rand()             | rand()               |
+--------------------+----------------------+
| 0.7380041170287915 | 0.055543343588284534 |
+--------------------+----------------------+
1 row in set 

若要取得0-100之间的数,可以使用100去乘随机值,但这样获得的函数还是不包含0和100这两个边界的。

mysql> select 100*rand(),100*rand(),100*rand();
+------------------+-------------------+--------------------+
| 100*rand()       | 100*rand()        | 100*rand()         |
+------------------+-------------------+--------------------+
| 22.5249471352668 | 96.80735235736458 | 16.461923454387044 |
+------------------+-------------------+--------------------+
1 row in set 

若要取整,则可以配合floor()或者ceiling()函数。但这样取得的是[0,99]或者[1,100],而不能是[0,100]。

mysql> select floor(100*rand()) as '[0,99]',ceiling(100*rand()) as '[1,100]';
+--------+---------+
| [0,99] | [1,100] |
+--------+---------+
|     90 |      24 |
+--------+---------+
1 row in set 

如果要想获得[0-100]这样包含边界的值,可以拓宽随机值。以下是两种方法:

mysql> select ceiling(rand()*101-1),floor(rand()*101);
+-----------------------+-------------------+
| ceiling(rand()*101-1) | floor(rand()*101) |
+-----------------------+-------------------+
|                    92 |                55 |
+-----------------------+-------------------+
1 row in set 

2.7 最值函数least(v1,v2,v3,…,vn)

从v1,v2,v3,…,vn中取出最小值。有以下几种情况:
(1)当只有数值类型时,取数值最小的。且负数有效。
(2)当只有字符串时,从第一个字符开始向后比较ascii码,小写字母小于大写字母。
(3)数值和字符串比较,返回结果为0。若要比较,需要先将数字转换为字符串格式,且字符串类型的数字总是小于字母。
(4)当n个成员之间存在null的时候,总是返回null,因为无法比较。

mysql> select least(5,10,-1),least('ab','c','ac'),least('a',1),least('a','999'),least('a',1,null);
+----------------+----------------------+--------------+------------------+-------------------+
| least(5,10,-1) | least('ab','c','ac') | least('a',1) | least('a','999') | least('a',1,null) |
+----------------+----------------------+--------------+------------------+-------------------+
|             -1 | ab                   | 0            | 999              | NULL              |
+----------------+----------------------+--------------+------------------+-------------------+
1 row in set 

2.8 最值函数greatest(v1,v2,v3,…,vn)

和least()函数相反,它取的是最大值。包括以下几种情况:
(1)当只有数值类型时,取最大值。负值有效。
(2)当只有字符串时,比较ascii码,大写字母大于小写字母。
(3)当数字和字符串比较时,数字大于字符串,即返回数字中最大值。但是字符串类型的数字小于字母。这个least()不一样。
(4)当存在null值时,返回null。

mysql> select greatest(5,10,-1) as A,
              greatest('ab','c','ac') as B,
              greatest('a',1) as C,
              greatest('a','999') as D,
              greatest('a',1,null) as E;
+----+---+---+---+------+
| A  | B | C | D | E    |
+----+---+---+---+------+
| 10 | c | 1 | a | NULL |
+----+---+---+---+------+
1 row in set, 2 warnings (0.00 sec) 

3 日期时间函数

有很多很多,官方手册:日期时间函数。以下挑几个介绍。

3.1 当前日期时间

返回当前日期:curdate()、current_date(),它们是同义词;
返回当前时间:curtime()、current_time(),它们是同义词;
返回当前日期时间:now()、current_timestamp()、localtime()、localtimestamp、localtimestamp()、sysdate(),除了sysdate(),其余的都是now()的同义词。

mysql> select curdate(),current_date(),current_timestamp(),curtime(),localtime(),now(),sysdate(); 

(MariaDB)MySQL内置函数大全

注意,now()和sysdate()是不同的。now()返回的是执行SQL语句那一刻的时间(如果now()是在存储过程或函数或触发器中,则now()返回的是这些程序开始调用执行的时刻),而sysdate()返回的是实时更新的当前时间,即操作系统当前的时间。通过下面的例子就知道了:

mysql> SELECT NOW(),CURRENT_TIMESTAMP(),SYSDATE(),LOCALTIME(),
       SLEEP(2),
       NOW(),CURRENT_TIMESTAMP(),SYSDATE(),LOCALTIME()G
*************************** 1. row ***************************
              now(): 2017-03-24 13:30:09
current_timestamp(): 2017-03-24 13:30:09
          sysdate(): 2017-03-24 13:30:09
        localtime(): 2017-03-24 13:30:09
           sleep(2): 0
              now(): 2017-03-24 13:30:09
current_timestamp(): 2017-03-24 13:30:09
          sysdate(): 2017-03-24 13:30:11    # 注意此处sleep 2秒后的时间
        localtime(): 2017-03-24 13:30:09
1 row in set (2.00 sec) 

可以看到,sleep(2)后,sysdate()返回的比其他的函数晚了两秒,而其他的函数返回的和sleep(2)之前的时间是一样的,且都是开始执行语句的时间。

3.2 week(DATE)

返回给定日期在当年是第几周。

mysql> select week(now());
+-------------+
| week(now()) |
+-------------+
|          12 |
+-------------+
1 row in set 

3.3 year(DATE)、monthname(DATE)、day(DATE)、quarter(DATE)

返回所给日期的年份、月份、月中天(所以day()的同义词是dayofmonth()函数)以及季度,不过返回的月份是英文全名。

mysql> select year(now()),monthname(now());
+-------------+------------------+
| year(now()) | monthname(now()) |
+-------------+------------------+
|        2017 | March            |
+-------------+------------------+
1 row in set 

3.4 hour(TIME)、minute(TIME)、second(TIME)

返回给定时间值的小时、分钟、秒部分。

mysql> select now(),hour(now()),minute(now()),second(now());
+---------------------+-------------+---------------+---------------+
| now()               | hour(now()) | minute(now()) | second(now()) |
+---------------------+-------------+---------------+---------------+
| 2017-03-23 14:21:57 |          14 |            21 |            57 |
+---------------------+-------------+---------------+---------------+
1 row in set 

3.5 extract(part from DATE)

从给定的DATETIME中提取秒(second)、分(minute)、时(hour)、日(day)、月(month)、周(week)、年(year),还支持季度(quarter)提取。和SQL Server中的datepart()函数一样的功能。

mysql> select extract(year from now()) as year_part,
              extract(month from now()) as month_part,
              extract(day from now()) as day_part,
              extract(week from now()) as week_part;
+-----------+------------+----------+-----------+
| year_part | month_part | day_part | week_part |
+-----------+------------+----------+-----------+
|      2017 |         10 |       18 |        42 |
+-----------+------------+----------+-----------+
1 row in set (0.00 sec)

mysql> select now(),extract(hour from now()) as hour_part,
              extract(minute from now()) as minute_part,
              extract(second from now()) as second_part;
+---------------------+-----------+-------------+-------------+
| now()               | hour_part | minute_part | second_part |
+---------------------+-----------+-------------+-------------+
| 2017-10-18 04:34:12 |         4 |          34 |          12 |
+---------------------+-----------+-------------+-------------+
1 row in set (0.00 sec) 

3.6 dayname(DATE)和dayofweek(DATE)

dayname返回给定日期是星期几,返回的周日期name的都是英文全名。而dayofweek返回的是数字代表的星期几,1表示周日,7表示周六。

mysql> select dayname(20131111),dayofweek('20131111');
+-------------------+-----------------------+
| dayname(20131111) | dayofweek('20131111') |
+-------------------+-----------------------+
| Monday            |                     2 |
+-------------------+-----------------------+
1 row in set (0.00 sec) 

3.7 日期时间格式化

日期格式化:date_format(date,fmt)
时间格式化:time_format(time,fmt)

其中fmt为日期时间的描述格式,使用%开头进行描述,例如%Y表示4位数字的年份,%m表示2位数字的月份等,更多的格式见官方手册fmt

mysql> select date_format('20131012','%Y-%m-%d');
+------------------------------------+
| date_format('20131012','%Y-%m-%d') |
+------------------------------------+
| 2013-10-12                         |
+------------------------------------+
1 row in set (0.00 sec) 

3.8 日期时间计算

增加日期:DATE_ADD(date,interval expr unit),ADDDATE(date,interval expr unit),它们是同义词;
减去日期:DATE_SUB(date,interval expr unit),SUBDATE(date,interval expr unit),它们是同义词;

在给定日期date基础上加或减去某种格式表达的日期时间。interval是关键字,expr是用来给定加减多少时间的表达式,unit是expr要表达的日期类型,见下图。其中expr的描述方式和unit是对应的。

Unit Description
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

例如year_month单元,从上表中得出它的格式是"years month"表示计算year部分和month部分的间隔。expr中year和month之间使用任意分隔符都可以,例如"1_2"、"1!2"、"1-2"和"1 2"都是允许的。如果使用day_minute单元,它的意义是"days hours.minutes",那么expr中就需要给定3个值,这3个值从前向后分别代表日、时、分,中间可以用任意分隔符分隔,例如'3-2-1'表示3天2小时1分钟。

expr的前面可以加上"+"和"-",分别表示加和减,不写时默认为"+",所以date_add和date_sub之间通过正负符号是可以等价的。

以下是示例:

mysql> select now(),
              date_add(now(),interval 31 day) as add31days,
              date_add(now(),interval '1_2' year_month) as add1year2month;
+---------------------+---------------------+---------------------+
| now()               | add31days           | add1year2month      |
+---------------------+---------------------+---------------------+
| 2017-10-18 05:00:11 | 2017-11-18 05:00:11 | 2018-12-18 05:00:11 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec) 

上述例子中使用了上面的第二列表示在当前日期内加上31天后的时间,第三列表示在当前日期基础上加上1年又2个月之后的时间。

如果date_add中expr使用的是负数,则表示减。

mysql> select now(),
              date_add(now(),interval '-31' day) as jian31days,
              date_add(now(),interval '-1_2' year_month) as jian1year2month;
+---------------------+---------------------+---------------------+
| now()               | jian31days          | jian1year2month     |
+---------------------+---------------------+---------------------+
| 2017-10-18 05:00:58 | 2017-09-17 05:00:58 | 2016-08-18 05:00:58 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec) 

上面第二列表示在当前日期上减去31天后的时间,第三列表示在当前日期基础上减去1年又2个月之后的时间。

3.9 datediff(expr1,expr2)

expr1和expr2之间的天数差,是expr1减去expr2。

mysql> select now(),datediff(now(),'2018-01-01');
+---------------------+------------------------------+
| now()               | datediff(now(),'2018-01-01') |
+---------------------+------------------------------+
| 2017-03-23 14:57:06 |                         -284 |
+---------------------+------------------------------+
1 row in set 

3.10 LAST_DAY(datetime)

返回给定日期所在月的最后一天。

mysql> select last_day(now()),last_day('2016-02-03');
+-----------------+------------------------+
| last_day(now()) | last_day('2016-02-03') |
+-----------------+------------------------+
| 2017-03-31      | 2016-02-29             |
+-----------------+------------------------+
1 row in set 

4 流程控制之条件判断函数

在MySQL/MariaDB中主要有if、ifnull和case语句进行条件判断。其中if语句和SQL Server中的if相差较大。

4.1 if(expr,true_value,false_value)

if函数用来判断expr是否为真,如果为真,则返回true_value,否则返回false_value。这和if语句是不一样的。

mysql> select if(1>2,'a','b'),if(2>1,'a','b');
+-----------------+-----------------+
| if(1>2,'a','b') | if(2>1,'a','b') |
+-----------------+-----------------+
| b               | a               |
+-----------------+-----------------+
1 row in set 

expr判断是否为真的依据是expr的结果非0且非null。所以也可以直接使用数字表示真假,但不能使用字母表示真假。

mysql> select if(99,'a','b'),if(0,'a','b'),if(null,'a','b'),if('2','a','b'),if('c','a','b');
+----------------+---------------+------------------+-----------------+-----------------+
| if(99,'a','b') | if(0,'a','b') | if(null,'a','b') | if('2','a','b') | if('c','a','b') |
+----------------+---------------+------------------+-----------------+-----------------+
| a              | b             | b                | a               | b               |
+----------------+---------------+------------------+-----------------+-----------------+
1 row in set, 1 warning (0.00 sec) 

4.2 ifnull(value1,value2)

如果value1不为空则返回value1,否则返回value2。总之就是给定一个非null值。允许value2为null。

mysql> select ifnull(1,'a'),ifnull('a','b'),ifnull(null,'a'),ifnull('a',null),ifnull(null,null);
+---------------+-----------------+------------------+------------------+-------------------+
| ifnull(1,'a') | ifnull('a','b') | ifnull(null,'a') | ifnull('a',null) | ifnull(null,null) |
+---------------+-----------------+------------------+------------------+-------------------+
| 1             | a               | a                | a                | NULL              |
+---------------+-----------------+------------------+------------------+-------------------+
1 row in set 

MySQL中的ifnull函数基本等价于SQL Server中的isnull()函数,跟SQL Server中的nullif函数相差非常大。且MySQL中的ifnull只能从两个参数中取一个非空值,而SQL Server中的coalesce()函数可以从多个参数中选第一个非空值。

4.3 nullif(expr1,expr2)

如果expr1等于expr2,则返回null,否则返回expr1。也就是说,两者不相等时取前者,否则取NULL。如果expr1和expr2任意一个为null,则直接返回null。这等价于:

case when expr1 = expr2 || expr1 is null || expr2 is null then null 
else expr1
end 

例如:

mysql> select nullif(1,1),nullif(1,2),nullif(null,1);   
+-------------+-------------+----------------+
| nullif(1,1) | nullif(1,2) | nullif(null,1) |
+-------------+-------------+----------------+
|        NULL |           1 | NULL           |
+-------------+-------------+----------------+
1 row in set (0.00 sec) 

4.4 case语句

和SQL Server中的case语法差不多。也是两种格式:case when ...then...else...endcase ...when...then...else...end

-- 格式一:
CASE WHEN express_1 THEN value_1 
     WHEN express_2 THEN value_2 
    … 

ELSE value_n   
END;
-- 格式二:
CASE express WHEN value1 THEN value_1 
             WHEN value2 THEN value_2 
             ... 
ELSE value_n   
END; 

注意,如果采用CASE...WHEN的写法格式,则express只能与value进行等同性检查。例如:

/*格式一示例*/
SELECT  StudentID, 
        CASE WHEN Mark < 60 THEN '不及格' 
             WHEN Mark >= 60 AND Mark < 70 THEN '及格' 
             WHEN Mark >= 70 AND Mark < 80 THEN '良好' 
             ELSE '优秀' 
        END 
FROM    Tscore;

/*格式二示例*/
SELECT  StudentID , 
        CASE FLOOR(Mark / 10) 
          WHEN 5 THEN '不及格' 
          WHEN 6 THEN '及格' 
          WHEN 7 THEN '良好' 
          ELSE '优秀' 
        END 
FROM    Tscore; 

其中格式二为case ... when的格式,它的when部分的值都只能和floor(mark/10)做等于号比较,这是等同性检查。而格式一的写法就灵活的多,既可以做等号比较,也能做大于号或其他方式的比较。

5 类型转换函数cast()和convert()

类型转换函数用来转换数据类型。在MySQL/MariaDB中可以转换的类型有以下几种:

二进制: BINARY[(N)] 
字符型: CHAR[(N)] 
日期 : DATE 
时间: TIME 
日期时间型 : DATETIME
浮点数 : DECIMAL 
整数 : SIGNED 
无符号整数 : UNSIGNED 

其中convert()有两种语法:

CONVERT(expr,type), CONVERT(expr USING transcoding_name) 

后者用于不同字符集之间转换数据。

在转换数据类型时,cast和convert的功能基本是一样的,只是写法不同。

mysql> SELECT CAST('3.35' AS signed);
+------------------------+
| CAST('3.35' AS signed) |
+------------------------+
|                      3 |
+------------------------+
1 row in set

mysql> SELECT  CAST(100 AS CHAR(2)),CONVERT('2013-8-9 12:12:12',TIME);
+----------------------+-----------------------------------+
| CAST(100 AS CHAR(2)) | CONVERT('2013-8-9 12:12:12',TIME) |
+----------------------+-----------------------------------+
| 10                   | 12:12:12                          |
+----------------------+-----------------------------------+
1 row in set 

带有using的convert函数用来转换字符集。

mysql> SELECT  CHARSET('string'),CHARSET(CONVERT('string' USING latin1));
+-------------------+-----------------------------------------+
| CHARSET('string') | CHARSET(CONVERT('string' USING latin1)) |
+-------------------+-----------------------------------------+
| utf8mb4           | latin1                                  |
+-------------------+-----------------------------------------+
1 row in set 

6 其它实用函数

  • (1). sleep(N)
    延迟N秒后执行后面的语句。特殊点在于sleep()函数可以用于select的选择列表。 select a,sleep(2),a from t; 注意上面的语句中,是先查询a,再阻塞2秒,之后再查询a,而不是先阻塞后再查询两次a或查询两次a后再阻塞。也就是说,对于mysql/mariadb来说,select的选择列表之间是有先后顺序的,不像sql server,选择列之间是完全平行等价的。可以通过下面的例子来验证: select sysdate(),sleep(1),sysdate();

  • (2). 返回当前数据库名database()

  • (3). 返回当前数据库版本version()

  • (4). 返回当前登录用户名user()

    mysql> select database(),version(),user();
    +------------+-----------+--------------------+
    | database() | version() | user()             |
    +------------+-----------+--------------------+
    | test       | 5.6.35    | root@192.168.100.1 |
    +------------+-----------+--------------------+
    1 row in set 
  • (5). 返回加密字符串password(str)

  • (6). 返回字符串的MD5值md5(str)

    mysql> select password('abc'),md5('abc');
    +-------------------------------------------+----------------------------------+
    | password('abc')                           | md5('abc')                       |
    +-------------------------------------------+----------------------------------+
    | *0D3CED9BEC10A777AEC23CCC353A8C08A633045E | 900150983cd24fb0d6963f7d28e17f72 |
    +-------------------------------------------+----------------------------------+
    1 row in set 
  • (7). last_insert_id()函数
    LAST_INSERT_ID()返回最后一个INSERT或UPDATE为AUTO_INCREMENT列设置的第一个发生的值。该函数值不是基于表的,这一点和SQL Server是不同的,也就是说,对a表插入的最后一个值是10,再对b表插入的最后一个值是15,那么函数返回的将是15。并且last_insert_id的值和一次插入一条记录还是一次批量插入的方式有关。

    mysql> create table test10(id int primary key auto_increment,name char(20));
    # 一次插入一条记录。
    mysql> insert into test10 values(null,'gaoxiaofang');
    mysql> insert into test10 values(null,'malongshuai');
    mysql> insert into test10 values(null,'longshuai');
    mysql> insert into test10 values(null,'xiaofang');
    mysql> select * from test10;
    +----+-------------+
    | id | name        |
    +----+-------------+
    |  1 | gaoxiaofang |
    |  2 | malongshuai |
    |  3 | longshuai   |
    |  4 | xiaofang    |
    +----+-------------+
    4 rows in set 

    查看last_insert_id的值,结果将是4。

    mysql> select last_insert_id();
    +------------------+
    | last_insert_id() |
    +------------------+
    |                4 |
    +------------------+
    1 row in set 

    一次插入多条记录,并查看last_insert_id()的值。

    mysql> insert into test10 values(null,'tun'er'),(null,'woniu'),(null,'wugui');
    mysql> select *,last_insert_id() from test10;
    +----+-------------+------------------+
    | id | name        | last_insert_id() |
    +----+-------------+------------------+
    |  1 | gaoxiaofang |                5 |
    |  2 | malongshuai |                5 |
    |  3 | longshuai   |                5 |
    |  4 | xiaofang    |                5 |
    |  5 | tun'er      |                5 |
    |  6 | woniu       |                5 |
    |  7 | wugui       |                5 |
    +----+-------------+------------------+
    7 rows in set 

    可以发现这里last_insert_id的值不是7而是5,这是因为批量插入的时候last_insert_id的值将会是批量中的第一条记录的自增列值。 且last_insert_id的值和表是无关的,只和会话环境有关。例如再向另外一个表插入后,last_insert_id的值将变为另一个值。

    mysql> create table test11(id int primary key auto_increment,name char(20));
    mysql> insert into test11 values(null,'gaoxiaofang');
    mysql> insert into test11 values(null,'malongshuai');
    mysql> insert into test11 values(null,'longshuai');
    mysql> insert into test11 values(null,'xiaofang');
    mysql> select last_insert_id();
    +------------------+
    | last_insert_id() |
    +------------------+
    |                4 |
    +------------------+
    1 row in set 

    可以发现它又变回了4。

收藏
评论区

相关推荐

postgresql和mysql哪个好
postgresql和mysql都是免费且功能强大的开源数据库,很多用户面对这两个库都会有一个问题,那就是哪一个才是最好的开源数据库,MySQL还是PostgreSQL呢?该选择哪一个开源数据库呢? postgresql和mysql哪个好 一.PostgreSQL相对于MySQL的优势 1、在SQL的标准实现上要比MySQL完善,而且功能实现比较严谨;
[DB]PostgreSQL 与 MySQL 相比,优势何在?
PostgreSQL 与 MySQL 相比,优势何在? 数据库 知乎 Pg 没有 MySQL 的各种坑 MySQL 的各种 text 字段有不同的限制, 要手动区分 small text, middle text, large text... Pg 没有这个限制, text 能支持各种大小. 按照 SQL 标准, 做 null 判断不能用
MySQL的语句执行顺序
今天遇到一个问题就是mysql中insert into 和update以及delete语句中能使用as别名吗?目前还在查看,但是在查阅资料时发现了一些有益的知识,给大家分享一下,就是关于sql以及MySQL语句执行顺序: sql和mysql执行顺序,发现内部机制是一样的。最大区别是在别名的引用上。  一、sql执行顺序 
MySQL 8.0 创建 user 及允许远程连接
进入 mysql 命令行模式mysql h127.0.0.1 uroot p 查看当前 mysql 版本select version(); 查看当前 mysql 用户列表信息select host, user, authentication_string, plugin from user; 执行完上面的命令后会显示一个表格 查看表格
MySQL数据类型详解和存储机制
1.1 数据类型概览 数据类型算是一种字段约束,它限制每个字段能存储什么样的数据、能存储多少数据、能存储的格式等。MySQL/MariaDB大致有5类数据类型,分别是:整形、浮点型、字符串类型、日期时间型以及特殊的ENUM和SE
(MariaDB)MySQL内置函数大全
1\. 字符串函数 完整的内置字符串函数见官方手册(https://mariadb.com/kb/en/stringfunctions/)。 1.1 字符串连接函数 有两个字符串连接函数:con
47 张图带你 MySQL 进阶!
我们在 MySQL 入门篇主要介绍了基本的 SQL 命令、数据类型和函数,在局部以上知识后,你就可以进行 MySQL 的开发工作了,但是如果要成为一个合格的开发人员,你还要具备一些更高级的技能,下面我们就来探讨一下 MySQL 都需要哪些高级的技能
21分钟 MySQL 入门教程
21分钟 MySQL 入门教程 目录 一、MySQL的相关概念介绍(c1) 二、Windows下MySQL的配置(c2) 配置步骤(d1) MySQL服务的启动、停止与卸载(d2) 三、
MySQL基础(非常全)
MySQL基础一、MySQL概述1、什么是数据库 ? 答:数据的仓库,如:在ATM的示例中我们创建了一个 db 目录,称其为数据库2、什么是 MySQL、Oracle、SQLite、Access、MS SQL Server等 ? 答:他们均是一个软件,都有两个主要的功能: a. 将数据保存到文件或内存 b. 接收特定的命令,然后对文件进行相应
MySQL(一)MySQL基础介绍
最近的学习内容是数据库相关的一些知识,主要以MySQL为主,参考书籍——《MySQL必知必会》MySQL学习及下载地址:https://dev.mysql.com/MySQL学习使用注意事项:1、必须访问一个已有的MySQL服务器,需要一个服务器账号(一个登录名和一个口令)2、MySQL运行在所有主要平台上,包括Windows、Linux、Solaris、M
Mysql Workbench使用教程
<1 MySQL WorkbenchMySQL Workbench 为数据库管理员、程序开发者和系统规划师提供可视化的Sql开发、数据库建模、以及数据库管理功能。 <2.MySQL Workbench 的下载和安装 (1)安装最新MySql时,有是否安装MySql Workbench的选项,可选择安装。 (2)可以独立安装MySql Workbench。
解决mysql ERROR 1045 (28000)-- Access denied for user
解决mysql ERROR 1045 (28000) Access denied for user问题,出现以下问题D:\develop\ide\mysql\mysql5.7\bin mysql u root p Enter password: ERROR 1045 (28000): Access denied for user 'ODBC'@'localh
一文读懂一条 SQL 查询语句是如何执行的
2001 年 MySQL 发布 3.23 版本,自此便开始获得广泛应用,随着不断地升级迭代,至今 MySQL 已经走过了 20 个年头。为了充分发挥 MySQL 的性能并顺利地使用,就必须正确理解其设计思想,因此,了解 MySQL 的逻辑架构是必要的。本文将通过一条 SQL 查询语句的具体执行过程来详细介绍 MySQL 架构中的各个组件。MySQL 逻辑架构
MySQL最全整理,1200页文档笔记,从高级到实战讲的太清楚了
闲话作为一名编程人员,对MySQL一定不会陌生,尤其是互联网行业,对MySQL的使用是比较多的。对于求职者来说,MySQL又是面试中一定会问到的重点,很多人拥有大厂梦,却因为MySQL败下阵来。实际上,MySQL并不难,今天这份最全的MySQL总结,一共1200页,几乎涵盖了MySQL的所有知识,尤其突出了实战技能和高级知识点,无论是工作还是面试看完这篇就足
Python中如何判断表的存在
MySQL如何判断表是否存在MySQL判断表是否存在可以使用下面语句:select from informationschema.tables where tablename 'student';select from informationschema.tables where tablename 'teacher';SQL语句说明:informati

热门文章

2秒内向数据库中插入十万条数据?

最新文章

2秒内向数据库中插入十万条数据?