mysql函数小结

最新推荐文章于 2022-06-09 16:18:36 发布

舞动的獾

最新推荐文章于 2022-06-09 16:18:36 发布

阅读量490

点赞数

分类专栏： mysql

本文链接：https://blog.youkuaiyun.com/Yu_csdnstory/article/details/104532865

版权

mysql 专栏收录该内容

11 篇文章

订阅专栏

这篇博客总结了MySQL中的数学函数、字符串函数、时间日期函数、条件判断函数和系统信息函数等，包括绝对值、平方根、取整、随机数、字符串长度、合并、替换、日期时间获取、条件判断和系统信息查询等多个方面的内容。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

文章目录

数学函数

1.绝对值函数和圆周率函数

abs函数返回绝对值
例如：

mysql> select abs(-2),abs(2),abs(-3.3);
+---------+--------+-----------+
| abs(-2) | abs(2) | abs(-3.3) |
+---------+--------+-----------+
|       2 |      2 |       3.3 |
+---------+--------+-----------+

圆周率函数直接用PI()
例如：

mysql> select pi();
+----------+
| pi()     |
+----------+
| 3.141593 |
+----------+

2.平方根函数和求余函数

平方根函数sqrt()
例如：

mysql> select sqrt(9),sqrt(19),sqrt(-19);
+---------+-------------------+-----------+
| sqrt(9) | sqrt(19)          | sqrt(-19) |
+---------+-------------------+-----------+
|       3 | 4.358898943540674 |      NULL |
+---------+-------------------+-----------+

求余函数mod(x,y)
函数返回x被y除后的函数。
例如：

mysql> select mod(10,2),mod(11,2),mod(98.4,3.4);
+-----------+-----------+---------------+
| mod(10,2) | mod(11,2) | mod(98.4,3.4) |
+-----------+-----------+---------------+
|         0 |         1 |           3.2 |
+-----------+-----------+---------------+

3.取整函数

ceil(x)和seiling(x)
函数返回不小于x的最大整数值，类型为BIGINT
例如：

mysql> select ceil(3.35),ceiling(3),ceiling(-3.35);
+------------+------------+----------------+
| ceil(3.35) | ceiling(3) | ceiling(-3.35) |
+------------+------------+----------------+
|          4 |          3 |             -3 |
+------------+------------+----------------+

floor(x)
函数返回不大于x的最大整数值

mysql> select floor(3.35),floor(3),floor(-3.35);
+-------------+----------+--------------+
| floor(3.35) | floor(3) | floor(-3.35) |
+-------------+----------+--------------+
|           3 |        3 |           -4 |
+-------------+----------+--------------+

4.随机数函数

rand()
rand()可以添加参数，用来产生重复序列。

mysql> select rand(),rand(),rand();
+---------------------+--------------------+--------------------+
| rand()              | rand()             | rand()             |
+---------------------+--------------------+--------------------+
| 0.04011700026701857 | 0.9884224971648515 | 0.8217615157568469 |
+---------------------+--------------------+--------------------+

mysql> select rand(3),rand(3),rand(3);
+--------------------+--------------------+--------------------+
| rand(3)            | rand(3)            | rand(3)            |
+--------------------+--------------------+--------------------+
| 0.9057697559760601 | 0.9057697559760601 | 0.9057697559760601 |
+--------------------+--------------------+--------------------+

5.四舍五入函数

round(x)
按照四舍五入法保留整数部分。
round(x,y)
保留y位小数的舍入处理，当y位负数时，直接保存为0，不进行舍入处理
例如：

mysql> select round(1.38,1),round(1.38,0),round(232.38,-1),round(232.38,-2);
+---------------+---------------+------------------+------------------+
| round(1.38,1) | round(1.38,0) | round(232.38,-1) | round(232.38,-2) |
+---------------+---------------+------------------+------------------+
|           1.4 |             1 |              230 |              200 |
+---------------+---------------+------------------+------------------+

truncate(x,y)直接舍去小数点的数值，而不是舍入。

mysql> select truncate(1.31,1),truncate(1.99,1),truncate(19.19,-1);
+------------------+------------------+--------------------+
| truncate(1.31,1) | truncate(1.99,1) | truncate(19.19,-1) |
+------------------+------------------+--------------------+
|              1.3 |              1.9 |                 10 |
+------------------+------------------+--------------------+

6.符号函数

sign(x)
返回x的符号，0，-1，1

select sign(2),sign(-3),sign(0);

7.幂运算函数

power(x,y)或者pow(x,y)
函数返回x的y次幂

mysql> select pow(3,4),pow(3,-10);
+----------+-------------------------+
| pow(3,4) | pow(3,-10)              |
+----------+-------------------------+
|       81 | 0.000016935087808430286 |
+----------+-------------------------+

exp(x)
返回e的x次幂

mysql> select exp(-1);
+---------------------+
| exp(-1)             |
+---------------------+
| 0.36787944117144233 |
+---------------------+

8.对数函数

log(x)
对x取对数
logy(x)
对x取以y为底的对数

mysql> select log10(100)
+------------+
| log10(100) |
+------------+
|          2 |
+------------+
1 row in set (0.00 sec)

mysql> select log2(16);
+----------+
| log2(16) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

9.角度与弧度相互转换函数

radians(x)可以将任何角度转化为弧度

mysql> select radians(120),radians(180);
+--------------------+-------------------+
| radians(120)       | radians(180)      |
+--------------------+-------------------+
| 2.0943951023931953 | 3.141592653589793 |
+--------------------+-------------------+

degrees(x)可以将弧度转化为角度

mysql> select degrees(2.8762),degrees(4.123);
+--------------------+--------------------+
| degrees(2.8762)    | degrees(4.123)     |
+--------------------+--------------------+
| 164.79412103552738 | 236.23049893243842 |
+--------------------+--------------------+

10.三角函数

正弦函数sin(x),asin(x)

mysql> select sin(pi()*0.5);
+---------------+
| sin(pi()*0.5) |
+---------------+
|             1 |
+---------------+

asin(x)返回反正弦值，x不在-1，1之间就会为null
cos(x)和acos(x)
类似于正弦sin，用法一样
正切tan(x),atan(x)
atan()是tan的反函数
cot(x)
返回余切值

字符串函数

1.计算字符个数和统计字符串长度

char_length(x)

mysql> select char_length('data1');
+----------------------+
| char_length('data1') |
+----------------------+
|                    5 |
+----------------------+

length(x)
返回字符串长度，效果和char_length一样

2.合并字符串

concat(s1,s2,s3......),concat(s1,s2,s3......)
返回合并的字符串结果，一旦合成有NULL，就会为NULL,有二进制字符串，会以二进制字符串显示。但是测试结果并不是这样！

mysql> select concat('my','sql',' ','is',' ','instering'),concat('my',NULL,'sql'),concat(100101,'mysq','l');
+---------------------------------------------+-------------------------+---------------------------+
| concat('my','sql',' ','is',' ','instering') | concat('my',NULL,'sql') | concat(100101,'mysq','l') |
+---------------------------------------------+-------------------------+---------------------------+
| mysql is instering                          | NULL                    | 100101mysql               |
+---------------------------------------------+-------------------------+---------------------------+

3.替换字符串

insert(s1,x,len,s2)

mysql> select insert ('quest',2,4,'what') as col1,
insert('quest',-1,4,'what') as col2,
insert('quest',3,100,'what') as co
l3;
+-------+-------+--------+
| col1  | col2  | col3   |
+-------+-------+--------+
| qwhat | quest | quwhat |
+-------+-------+--------+

4.字母大小写转换

lower(x)或者lcase(x)
可以将所有大写字母化为小写
upper(x)和ucase(x)
可以将所有小写转为大写

5.获取指定长度字符串

left(s,n)和right(s,n)
left获取左边n位字符串，right获取右边n为字符串

mysql> select left('football',4),right('football',4);
+--------------------+---------------------+
| left('football',4) | right('football',4) |
+--------------------+---------------------+
| foot               | ball                |
+--------------------+---------------------+

6.填充字符串函数

lpad(s1,len,s2)和rpad(s1,len,s2)
都返回s1,lpad将s1用s2在左边填充到len，rpad用s2在右边填充到s2,若s1>len,则缩小到len的长度。

mysql> select rpad('mysql',3,'!!'),lpad('mysql',10,'!s'),rpad('mysql',10,'!s'),lpad('mysql',3,'!!');
+----------------------+-----------------------+-----------------------+----------------------+
| rpad('mysql',3,'!!') | lpad('mysql',10,'!s') | rpad('mysql',10,'!s') | lpad('mysql',3,'!!') |
+----------------------+-----------------------+-----------------------+----------------------+
| mys                  | !s!s!mysql            | mysql!s!s!            | mys                  |
+----------------------+-----------------------+-----------------------+----------------------+

7.删除空格

ltrim(x)
去除字符串左边的空格
trim(x)
去除字符串右边的空格
trim(x)
去除字符串两侧空格

mysql> select '( book )',concat('(',ltrim(' book '),')');
+----------+---------------------------------+
| ( book ) | concat('(',ltrim(' book '),')') |
+----------+---------------------------------+
| ( book ) | (book )                         |
+----------+---------------------------------+
1 row in set (0.01 sec)

mysql> select '( book )',concat('(',rtrim(' book '),')');
+----------+---------------------------------+
| ( book ) | concat('(',rtrim(' book '),')') |
+----------+---------------------------------+
| ( book ) | ( book)                         |
+----------+---------------------------------+

mysql> select '( book )',concat('(',trim(' boo k '),')');
+----------+---------------------------------+
| ( book ) | concat('(',trim(' boo k '),')') |
+----------+---------------------------------+
| ( book ) | (boo k)                         |
+----------+---------------------------------+

trim(x)函数完整的式子是trim(s1 from x)
删除x两端中所有的子字符串s1,不选s1的情况下，默认为空格

mysql> select trim('cv' from 'cvcvcvcvcvcvhjhcvcvcvcvcvvcvcvcvcv');
+------------------------------------------------------+
| trim('cv' from 'cvcvcvcvcvcvhjhcvcvcvcvcvvcvcvcvcv') |
+------------------------------------------------------+
| hjhcvcvcvcvcvv                                       |
+------------------------------------------------------+
mysql> select trim('cv' from 'acvcvcvcvcvcvhjhcvcvcvcvcvvcvcvcvcv');
+-------------------------------------------------------+
| trim('cv' from 'acvcvcvcvcvcvhjhcvcvcvcvcvvcvcvcvcv') |
+-------------------------------------------------------+
| acvcvcvcvcvcvhjhcvcvcvcvcvv                           |
+-------------------------------------------------------+

8.生成重复字符串

repeat(s,n)
生成n个s组成的字符串，n<=0时，返回空值,s或者n为NULL，时也返回NULL.

mysql> select repeat('mysql',3),repeat('mysql',-1),repeat('mysql',NULL);
+-------------------+--------------------+----------------------+
| repeat('mysql',3) | repeat('mysql',-1) | repeat('mysql',NULL) |
+-------------------+--------------------+----------------------+
| mysqlmysqlmysql   |                    | NULL                 |
+-------------------+--------------------+----------------------+

9.空格函数和替换函数

space(n)返回由n个空格组成的字符串,可以联合字符串连接函数使用
replace(s,s1,s2)

mysql> select replace('www.qq.com','qq','baidu');
+------------------------------------+
| replace('www.qq.com','qq','baidu') |
+------------------------------------+
| www.baidu.com                      |
+------------------------------------+

10.字符串比较大小函数

strcmp(s1,s2)s1>s2返回1，小于s2返回s2,等于s2返回0

mysql> select strcmp('txt','txt'),strcmp('txt','u'),strcmp('txt','adjshdjs'),strcmp('txt','tuasdf');
+---------------------+-------------------+--------------------------+------------------------+
| strcmp('txt','txt') | strcmp('txt','u') | strcmp('txt','adjshdjs') | strcmp('txt','tuasdf') |
+---------------------+-------------------+--------------------------+------------------------+
|                   0 |                -1 |                        1 |                      1 |
+---------------------+-------------------+--------------------------+------------------------+

11.字符串截取函数

substring(s,n,len)和mid(s,n,len)
两个函数总用相同，从字符串s返回一个从位置n开始，长度为len的字符串，n为负数的时候，则从字符串的尾部反向数n个位置开始截取字符串。

mysql> select substring('breakfast',5) as col1,
substring('breakfast',5,3)as col2,
substring('lunch',-3) as col3,
substring('lunch',-5,3) as col4,
substring('nsdjcjnjds',-12,3) as col5,
substring('bcjfuhrb',3,100) as col6,
substring('hduewryfvc',5,-3) as col7;
+-------+------+------+------+------+--------+------+
| col1  | col2 | col3 | col4 | col5 | col6   | col7 |
+-------+------+------+------+------+--------+------+
| kfast | kfa  | nch  | lun  |      | jfuhrb |      |
+-------+------+------+------+------+--------+------+

substr是substring1的简写

12.返回子串开始位置

locat(str1,str)和position(str1 in str)和instr(str,str1)
返回子字符串s1在s中的位置

mysql> select locate('fasttt','rueufasttt'),position('nk' in 'jsdjcnknk'),instr('kjcnjka','cn'),locate('cdjsb','cjkye');

+-------------------------------+-------------------------------+-----------------------+-------------------------+
| locate('fasttt','rueufasttt') | position('nk' in 'jsdjcnknk') | instr('kjcnjka','cn') | locate('cdjsb','cjkye') |
+-------------------------------+-------------------------------+-----------------------+-------------------------+
|                             5 |                             6 |                     3 |                       0 |
+-------------------------------+-------------------------------+-----------------------+-------------------------+

13.字符串逆序函数

reverse(s)返回逆序的字符串

14.返回指定位置的字符串

elt(n,str1,str2,str3,str4.....strn)

mysql> select elt(2,'dknc','diweh','kdnkc'),elt(7,'nkc','jdwon','uiwed');
+-------------------------------+------------------------------+
| elt(2,'dknc','diweh','kdnkc') | elt(7,'nkc','jdwon','uiwed') |
+-------------------------------+------------------------------+
| diweh                         | NULL                         |
+-------------------------------+------------------------------+

15.返回字符串位置函数

field(s,s1,s2,s3......sn)
返回s在s1,s2,s3,s4…sn中第一次出现的位置

mysql> select field('women','njcjjc','cndkjcd','womennckds','women') as col1;
+------+
| col1 |
+------+
|    4 |
+------+

find_in_set(s1,s2)
s2是一个字符串列表，以逗号隔开的字符串，且第一个参数包含逗号，会达不到想要的效果，返回0.

mysql> select find_in_set('Hi','hihi,Hey,Hi,bas');
+-------------------------------------+
| find_in_set('Hi','hihi,Hey,Hi,bas') |
+-------------------------------------+
|                                   3 |
+-------------------------------------+

mysql> select find_in_set('H,i','hihi,Hey,H,i,NULL');
+----------------------------------------+
| find_in_set('H,i','hihi,Hey,H,i,NULL') |
+----------------------------------------+
|                                      0 |
+----------------------------------------+

mysql> select find_in_set('NULL','hihi,Hey,H,i,NULL');
+-----------------------------------------+
| find_in_set('NULL','hihi,Hey,H,i,NULL') |
+-----------------------------------------+
|                                       5 |
+-----------------------------------------+

16.选取字符串的函数

make_set(x,s1,s2,s3,s4......sn)
以x的二进制从s1,s2,…sn中选取字符串。
例如，13的二进制为1101，则从右到左的第一位，第三位，第四位会被选中，选择的为s1,s3,s4.

mysql> select make_set(1|4,'hello','nice','world','ok');
+-------------------------------------------+
| make_set(1|4,'hello','nice','world','ok') |
+-------------------------------------------+
| hello,world                               |

1与4的或操作结果为0101，被选的是第一位和第三位。

时间日期函数

1.获取当前时间与日期

current_date()或者curdate()获取当前日期

mysql> select curdate(), curdate()+0;
+------------+-------------+
| curdate()  | curdate()+0 |
+------------+-------------+
| 2020-03-03 |    20200303 |
+------------+-------------+

current_date()和curdate()函数一样，返回yyyy-mm-dd的日期，+0由将日期转化为数值型。
curtime()和current_time()获取当时时间

select curtime(), curtime()+0;
+-----------+-------------+
| curtime() | curtime()+0 |
+-----------+-------------+
| 16:12:40  |      161240 |
+-----------+-------------+

current_timestamp(),localtime,now(),syadate()都返回日期加时间值

select current_timestamp(),localtime(),now(),sysdate();
+---------------------+---------------------+---------------------+---------------------+
| current_timestamp() | localtime()         | now()               | sysdate()           |
+---------------------+---------------------+---------------------+---------------------+
| 2020-03-03 16:16:55 | 2020-03-03 16:16:55 | 2020-03-03 16:16:55 | 2020-03-03 16:16:55 |
+---------------------+---------------------+---------------------+---------------------+

2.unix时间戳函数

unix_timestamp(d)把时间按照时间戳的方式返回时间

 select unix_timestamp();
 +-----------------------+
| unix_timestamp() |
+-----------------------+
|            1583223847 |
+-----------------------+

它有个反函数from_unixtime()

mysql> select from_unixtime(1583223847);
+---------------------------+
| from_unixtime(1583223847) |
+---------------------------+
| 2020-03-03 16:24:07       |
+---------------------------+

3.UTC函数

utc_date()返回当前的时区标准时间日期值。
utc_time()返回世界标准时间值

mysql> select utc_time(),utc_date();
+------------+------------+
| utc_time() | utc_date() |
+------------+------------+
| 08:34:59   | 2020-03-03 |
+------------+------------+

4.月份函数

month(d)返回日期中的月份
monthname(d)返回指定日期的月份英文全称

mysql> select month(20110821),monthname(20110821);
+-----------------+---------------------+
| month(20110821) | monthname(20110821) |
+-----------------+---------------------+
|               8 | August              |
+-----------------+---------------------+

5.获取星期的函数

dayname()返回指定日期的星期数
dayofweek()返回在一周中的索引，1表示周日，2表示周一…
weekday()返回日期对应的工作日周索引，0表示周一，1表示周二…

mysql> select dayname(curdate()),dayofweek(curdate()),weekday(curdate());
+--------------------+----------------------+--------------------+
| dayname(curdate()) | dayofweek(curdate()) | weekday(curdate()) |
+--------------------+----------------------+--------------------+
| Tuesday            |                    3 |                  1 |
+--------------------+----------------------+--------------------+

week(d)用于计算日期d是一年中的第几周，它允许双参数允许指定该星期是否起源于周日或周一的mode，如果不指定，则使用default_format系统变量的值。

mysql> select week(20180812);
+----------------+
| week(20180812) |
+----------------+
|             32 |
+----------------+

6.获取天数的函数

dayofyear(d)返回d在一年中是第几天
dayofmonth返回d在一个月中的第几天

7.获取年份，季度，小时，分钟，秒钟的函数

yeay(d)返回年份
quarter(d)返回日期对应的季度
minute(d)返回指定时间的分钟值
second(d)返回指定时间的秒值

8.获取日期的指定函数

extract(type from date)从日期中截取部分

mysql> select extract(year from '2108-09-23') as col1,
extract(year_month from '2018-09-23') as col2,
extract(day_minute from '2018-09-23 02:03:23') as col3;
+------+--------+--------+
| col1 | col2   | col3   |
+------+--------+--------+
| 2108 | 201809 | 230203 |
+------+--------+--------+

9.时间和秒钟转换函数

time_to_sec(time)

mysql> select time_to_sec('10:20:23');
+-------------------------+
| time_to_sec('10:20:23') |
+-------------------------+
|                   37223 |
+-------------------------+

sec_to_time(seconds)

mysql> select sec_to_time(37223);
+--------------------+
| sec_to_time(37223) |
+--------------------+
| 10:20:23           |
+--------------------+

10.计算日期和时间的函数

函数有date_add(),add_date(),date_sub(),sub_date(),addtime(),subtime(),date_diff()
以date_add(date,interval expr type)函数为例
其中date为date或者datetime类型的时间值，用来表示起始时间；expr是表达式，表示时间间隔，也可以说是一个字符串；type表示expr的类型。

转换关系如下图：

1.date_add(date,interval expr type)和adddate(date,interval expr type)效果一样，执行日期加操作。

mysql> select date_add('2018-12-31 23:23:59',interval 1 second) as col1,
    -> adddate('2018-12-31 23:23:59',interval 1 second) as col2;
+---------------------+---------------------+
| col1                | col2                |
+---------------------+---------------------+
| 2018-12-31 23:24:00 | 2018-12-31 23:24:00 |
+---------------------+---------------------+

2.date_sub()和subdate()作用一样，执行日期相减

mysql> select date_sub('2018-12-31 23:23:59',interval 31 day) as col1,
subdate('2011-01-01 00:01:00',interval '1 0:0:0' day_second) as col2;
+---------------------+---------------------+
| col1                | col2                |
+---------------------+---------------------+
| 2018-11-30 23:23:59 | 2010-12-31 00:01:00 |

3.addtime(date,expr)将expr添加到date
expr为时间表达式

mysql> select addtime('2000-12-31 23:59:59','1:1:1');
+----------------------------------------+
| addtime('2000-12-31 23:59:59','1:1:1') |
+----------------------------------------+
| 2001-01-01 01:01:00                    |
+----------------------------------------+

4.subtime(date,expr)返回date减去expr

mysql> select subtime('2000-12-31 23:59:59','1:1:1');
+----------------------------------------+
| subtime('2000-12-31 23:59:59','1:1:1') |
+----------------------------------------+
| 2000-12-31 22:58:58                    |
+----------------------------------------+

5.datediff(date1,date2)
返回date1与date2之间的天数，date1与date2都可以是时间日期表达式，但是函数只返回这些值的日期部分。

mysql> select datediff('2010-12-31 23:23:34','2009-9-23 22:21:39') as col1,
    -> datediff('2012-2-12 21:23:56','2012-3-11 23:12:32') as col2;
+------+------+
| col1 | col2 |
+------+------+
|  464 |  -28 |
+------+------+

11.格式化时间和日期的函数

date_format(date,format)
数据以格式化的形式输出

说明符	说明
%a	星期的缩写（sun-sat)
%b	月份缩写（jan-dec)
%c	月份，数字形式
%D	带有英语后缀的该月日期
%d	该月日期，（00-31）
%e	该月日期，数字形式（0-31）
%f	微秒（000000-999999）
%H	以两位数表示24小时
%h,%I	以两位数表示12小时
%i	分钟，数字形式（00-59）
%j	一年中的天数
%k	以24小时制表示
%l	以12小时制表示（1-12）
%M	月份名称（january-december)
%m	月份，数字形式（01-12）
%p	上午（AM)下午(PM)
%r	12小时制时间，格式为hh:mm:ss:am或者pm
%S,%s	以两位形式表示秒
%T	时间，24小时制（hh:mm:ss)
%U	周（00-53），周日为每周的第一天
%u	周（00-53），周一为每周的第一天
%V	周（00-53），周日为每周的第一天，与%X同时使用
%v	周（00-53），周一为每周的第一天，与%x同时使用
%W	工作日名称（周日-周六）
%w	一周中的每日（0=周日~6=周六）
%X	该周的年份，周日为每周的第一天：数字形式，4位数，与%V同时使用
%x	该周的年份，周日为每周的第一天：数字形式，4位数，与%V同时使用
%Y	四位数表示年份
%y	两位数表示年份

 select date_format('1997-10-04 22:23:00','%W %M,%Y') as col1,
 date_format('1997-10-04 22:23:00','%D %y %a %d %m %b %j') as col2,
 date_format('1997-10-04 22:23:00','%H:%i:%s')as col3,
 date_format('1997-10-04 22:23:00','%X %V')as col4;
 +-----------------------+--------------------------+----------+---------+
| col1                  | col2                     | col3     | col4    |
+-----------------------+--------------------------+----------+---------+
| Saturday October,1997 | 4th 97 Sat 04 10 Oct 277 | 22:23:00 | 1997 39 |
+-----------------------+--------------------------+----------+---------+

time_format(time,format)只处理时间的格式化

get_format(val_type,format_type)
返回时间或日期字符串的显示格式

select date_format('2000-10-05 22:33:44;,get_format(date,'usa'));
mysql> select date_format('2000-10-05 22:33:44',get_format(date,'usa'));
+-----------------------------------------------------------+
| date_format('2000-10-05 22:33:44',get_format(date,'usa')) |
+-----------------------------------------------------------+
| 10.05.2000                                                |
+-----------------------------------------------------------+

条件判断函数

1.if(expr,v1,v2)

if(expr,v1,v2)如果expr为true,返回v1，否则返回v2。

mysql> select if(1>2,2,3),if(1<2,'yes','no'),if(strcmp('test','test1'),'no','yes');
+-------------+--------------------+---------------------------------------+
| if(1>2,2,3) | if(1<2,'yes','no') | if(strcmp('test','test1'),'no','yes') |
+-------------+--------------------+---------------------------------------+
|           3 | yes                | no                                    |
+-------------+--------------------+---------------------------------------+

2.ifnull(v1,v2)

ifnull(v1,v2)
如果v1不为空，返回v1,否则返回v2.

mysql> select ifnull(1,2),ifnull(NULL,2),ifnull(1/0,'wrong');
+-------------+----------------+---------------------+
| ifnull(1,2) | ifnull(NULL,2) | ifnull(1/0,'wrong') |
+-------------+----------------+---------------------+
|           1 |              2 | wrong               |
+-------------+----------------+---------------------+

3.case分支语句

case expr when v1 then r1 [when v2 then r2] [else rn] end
表示expr等于vn，就执行vn,v1-vn都不相等，执行else，没有else，返回NULL

mysql> select case 5 when 1 then 'one' when 2 then 'two' when 5 then 'five' else 'false' end;
+--------------------------------------------------------------------------------+
| case 5 when 1 then 'one' when 2 then 'two' when 5 then 'five' else 'false' end |
+--------------------------------------------------------------------------------+
| five                                                                           |
+--------------------------------------------------------------------------------+

case when v1 then r1[when v2 then r2] [else rn] end

mysql> select case when 1<0 then 'true' when 1=0 then 'right' else 'error' end;
+------------------------------------------------------------------+
| case when 1<0 then 'true' when 1=0 then 'right' else 'error' end |
+------------------------------------------------------------------+
| error                                                            |
+------------------------------------------------------------------+

系统信息函数

1.获取版本号，连接数，数据库名

version()显示版本号
connection_id()返回mysql服务器当前连接的次数
show processlist产看连接信息
show full processlist

mysql> select connection_id(),version();
+-----------------+-----------+
| connection_id() | version() |
+-----------------+-----------+
|               9 | 5.7.26    |
+-----------------+-----------+

mysql> show full processlist;
+----+------+-----------------+------+---------+------+----------+-----------------------+
| Id | User | Host            | db   | Command | Time | State    | Info                  |
+----+------+-----------------+------+---------+------+----------+-----------------------+
|  9 | root | localhost:40583 | test | Query   |    0 | starting | show full processlist |
+----+------+-----------------+------+---------+------+----------+-----------------------+

datebase()和schema()返回当前用户名

mysql> select database(),schema();
+------------+----------+
| database() | schema() |
+------------+----------+
| test       | test     |
+------------+----------+

2.返回用户名

user() ,current_user ,current_user() ,system_user(), session_user()都能获取当前登陆用户名

mysql> select user(),current_user,current_user(),system_user(),session_user();
+----------------+----------------+----------------+----------------+----------------+
| user()         | current_user   | current_user() | system_user()  | session_user() |
+----------------+----------------+----------------+----------------+----------------+
| root@localhost | root@localhost | root@localhost | root@localhost | root@localhost |
+----------------+----------------+----------------+----------------+----------------+

3.获取字符串的字符集和排序方式

charset(str)

mysql> select charset('aas');
+----------------+
| charset('aas') |
+----------------+
| utf8           |
+----------------+

collation(str)返回str的字符排列方式

mysql> select collation('abs');
+------------------+
| collation('abs') |
+------------------+
| utf8_general_ci  |
+------------------+

4.获取最后一个自动生成的id值的函数

last_insert_id()自动返回最后一个insert或update为auto_increment列设置的函数

加密函数

password(str)单向加密不可逆，加密后的密码保存在权限列表中

mysql> select password('i love mysqL');
+-------------------------------------------+
| password('i love mysqL')                  |
+-------------------------------------------+
| *AD4F258AF60F5FDC801B36BE23BC1D3BB55833C9 |
+-------------------------------------------+

md5(str)

mysql> select md5('i love mysqL');
+----------------------------------+
| md5('i love mysqL')              |
+----------------------------------+
| b8796c4af3c7ac73de3044101a0dc55f |
+----------------------------------+

encode(str,pswd_str)
pswd_str为密码，加密str,反函数为decode(crypt_str,pawd_str)可以解出原字符

mysql> select encode('mysql','love');
+------------------------+
| encode('mysql','love') |
+------------------------+
| 沇G{%                   |
+------------------------+

mysql> select decode('沇G{%','love');
+------------------------+
| decode('沇G{%','love')  |
+------------------------+
| mysql                  |
+------------------------+
1 row in set, 2 warnings (0.00 sec)

其他函数

1.格式化函数

format(x,n)
对x保留n位格式化，按照四舍五入规则。

2.不同进制转换

conv(n,from_base,to_base),最小基数为2，最大基数为32

mysql> select conv('98d2f3a3',16,32);
+------------------------+
| conv('98d2f3a3',16,32) |
+------------------------+
| 2CD5ST3                |
+------------------------+

3.ip地址与数字转换的函数

INET_ATON(IP)
可以将字符串网络点地址转化为数值网络网址
INET_NTOA()是INET_ATON的反函数

mysql> select inet_aton('102.34.23.12');
+---------------------------+
| inet_aton('102.34.23.12') |
+---------------------------+
|                1713510156 |
+---------------------------+

mysql> select inet_ntoa(1713510156);
+-----------------------+
| inet_ntoa(1713510156) |
+-----------------------+
| 102.34.23.12          |
+-----------------------+

4.重复执行指定操作的函数

benchmark(count,expr)重复执行expr count次，返回执行结果

mysql> select benchmark(20000000,md5('mysql'));
+----------------------------------+
| benchmark(20000000,md5('mysql')) |
+----------------------------------+
|                                0 |
+----------------------------------+
1 row in set (4.51 sec)

重复执行MD5函数，返回的时间为4.51，结果为0.报告的时间是客户端经过的时间，并不是cpu执行的时间。

5.改变字符集的函数

用using的convert()函数可以在不同的字符集之间转换数据。
mysql默认的字符集为utf8

mysql> select charset(convert('mysql'using latin1));
+---------------------------------------+
| charset(convert('mysql'using latin1)) |
+---------------------------------------+
| latin1                                |
+---------------------------------------+

6.改变数据类型的函数

cast(x as type)和convert(x,type)可以将一个类型的值转化为另一个类型的值
类型有BINARY,CHAR(N),DATE,TIME,DATETIME,DECIMAL,SIGNED,UNSIGNED.
例如：

mysql> select cast(100 as char(2)),convert('2010-10-01 12:12:12',time);
+----------------------+-------------------------------------+
| cast(100 as char(2)) | convert('2010-10-01 12:12:12',time) |
+----------------------+-------------------------------------+
| 10                   | 12:12:12                            |
+----------------------+-------------------------------------+