MySQL之——函数

 函数

数值函数

• ceil(x):向上取整，返回大于等于x的最小整数值
• floor(x):向下取证，返回小于等于x最大的整数值
• round(x,y=0)：四舍五入，将x四舍五入y位小数，如果不传入y值则返回整数，y为负数的时候，保留x值到小数点左边y位

select round(888.88)        #889
select round(888.88,1)      #888.9
select round(888.88,2)      #888.88
select round(888.88,-1)     #890
select round(888.88,-2)     #900
select round(888.88,-3)     #1000

• truncate(x,y):截断函数，返回被舍去至小数点后y位的数字x，y为负数时阶段小数点左边y位

select round(888.88,1)      #888.8
select round(888.88,2)      #888.88
select round(888.88,-1)     #880
select round(888.88,-2)     #800
select round(888.88,-2)     #0

• mod(x,y):返回x除以y的余数
• rand():生成0-1的随机数

select rand() * 10 #生成0--10的随机数

 字符函数

• concat(s1,s2...)：字符串连接，如果任何一个参数为null，则返回值为null
• concat_ws(x,s1,s2,...)：指定分隔符的字符连接函数，x是连接分隔符，如果分隔符为null则结果为null

select concat_ws(null,'I','U');         #null
select concat_ws('LOVE','I ',' U');     #I LOVE U

• lower(str):小写转大写 
• upper(str):大写转小写
• length(str)：字符串长度
• Itrim(str)：删除字符串左侧空格
• rtrim(str)：删除字符串右侧空格
• trim(str)：删除字符串两侧空格
• substr(str,n,len)：截取字符串，字符串str从n的位置截取长度为len的字符串，如果n为负数，则子字符串的位置起始于字符串结尾的n个字符。

select substr('123456789',1,3);    #123
select substr('123456789',2,3);    #234
select substr('123456789',-1,3);   #9
select substr('123456789',-5,3);   #567

• left(str,n)：返回字符串最左边n个字符
• right(str,n)：返回字符串最右边n个字符

select left('123456789',1);    #1
select right('123456789',1);   #9
select right('123456789',4);   #6789
select left('123456789',10);    #超出长度全部返回 123456789

• replace(str,from_str,to_str):替换函数，字符串str中所有的字符串from_str均被to_str替换，然后返回这个字符串 

select replace('aaabbb','a','A')        #'AAAbbb'
select replace('aaabbb','aa','A')       #'Aabbb'
select replace('aaabbb','aaa','A')      #'Abbb'
select replace('aaabbb','aaaa','A')     #'aaabbb'

• format(x,n)：将数字x格式化，并以四舍五入的方式保留小数点后n位，结果以字符串的形式返回。若n为0，则返回结果不含小数部分。需要与round(x,y)区分的是，format返回字符串形式，round返回数字，round中y可以不传入，format需要传入 

select format(888.88,0)        #'889'
select format(888.88,1)        #'888.9'
select format(888.88,2)        #'888.88'
select format(888.88,-1)       #'890'

日期时间函数

• curdate() / current_date()：获取当前日期，YYYY-MM-DD格式

select curdate();        #2023-12-10
select current_date();   #2023-12-10

• curtime() / current_time()：获取当前时间，HH：MM：SS格式

select curtime();        #19:56:20
select current_time();   #19:56:20

•  now() / sysdate()：获取当前日期和时间，YYYY-MM-DD HH：MM：SS格

select now();            #2023-12-10 19:57:55
select sysdate();        #2023-12-10 19:57:55

•  date_add(date,interval expr type)：执行日期的加运算，date是一个datetime或者是一个date值，指起始时间。expr是时间间隔。type为关键词，如YEAR，MONTH，DAY，WEEK，HOUR等

select date_add('2023-12-10',interval 5 week);        #2024-01-14

• datediff(date1,date2)：计算两个日期之间的天数

select datediff('2023-12-10','2023-12-8');        #2

• date_format(date,format)：date：日期值，日期格式化，format是输入指定格式

select date_format('2023-12-10','%Y-%m-%d');    #2023-12-10
select date_format('2023-12-10','%Y-%b-%d');    #2023-Dec-10

• str_to_date(str,format)：str：字符串，将字符串转换成date类型

date_format	时间日期格式
%Y：4位数形式表示年份	%y：2位数形式表示年份
%b：月份，缩写名称(Jan...Dec)	%c：月份，数字形式(0..12)
%m：月份，数字形式(00...12)	%M：月份名称(january...)
%d：该月日期day(00...31)	%e：该月日期(0...31)
%p：上下午，am，pm	%h：时
%i：分	%s或者%S：秒

聚合函数（分组函数）

• avg(expression)：返回某列的平均值
• sum(expression)：返回某列值的和
• count(expression)：返回某列的行数
• max(expression)：返回某列的最大值
• min(expression)：返回某列的最小值

注意：

• 聚合函数会自动忽略空值，不需要手动增加条件排除控制null
• 聚合函数不能作为where子句后的限制条件

select count(sal) from emp where deptno = 20; #计算deptno = 20 的sal总和
select max(sal) from emp;
select count(*) from emp;                     #计算emp表中有多少行

select * from emp where deptno = 20 and sal > avg(sal);#报错

其他函数(ifnull空值处理函数)

ifnull(column,value)：如果字段column的值为空NULL（注意NULL不是0，是没有），则将字段值替换为value

        在SQL语句中若有NULL值参与数学运算，计算结果一定是NULL，为了防止计算结果出现NULL，一般先使用ifnull空值处理函数预先处理。

select sal + comm from emp;    #comm中含有NULL的数据 和sal相加之后都变成NULL了

select sal + ifnull(comm ,0) from emp; #comm中含有NULL的数据都被转换成0