数学函数
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 |
+----------------------+-------------------------------------+