mysql常用函数总结
作者：YXN-sql 阅读量：233 发布日期：2022-10-01
| 函数 | 解释 |
| abs(x) | 返回x的绝对值 |
| mod(m,n) | 求余 |
| pi() | 圆周率 |
| ceil(x),ceilin(x) | 向上取整 |
| floor(x) | 向下取整 |
| rand() | 随机数 |
| round(x) | 四舍五入去整 |
| round(x,y) | 四舍五入，数值x保留到小数点后y位 |
| truncate(x,y) | 同round，但不会四舍五入 |
| 函数 | 解释 |
| char_length(s) | 返回字符串s的字符数 |
| length(s) | 返回字符串s的字节数 |
| insert(s1,x,len,s2) | 将字符串s2替换s1的x位置开始，长度为len的字符串 |
| ucase() | 把字段的值转换为大写 |
| lcase() | 把字段的值转换为小写 |
| left(s,n) | 截取从字符串s开始的前n个字符 |
| right(s,n) | 截取从字符串s右边开始的n个字符 |
| ltrim(s) | 去掉字符串s开始处的空格 |
| rtrim(s) | 去掉字符串s结尾处的空格 |
| trim(s) | 去掉字符串s开始处和结尾处的空格（去除空格） |
| concat(s1,s2,…) | 连接多个字符串（字符串连接） |
| substring(s,n,len) | 截取字符串s中的第n个位置开始长度为len的字符串 |
| substring_index(str,delim,count) | 按关键字截取 |
| instr(s,s1) | 从字符串s中获取s1的开始位置 |
| located(substring, string [,start]) | 查找，返回字符串中第一次出现的子字符串的位置，不区分大小写 |
| reverse(str) | 返回颠倒字符顺序的字符串str |
3.选择函数if与case
If(expr,v1,v2):如果表达式expr成立，则执行v1,否则执行v2
Ifnull(v1,v2):如果v1为空,则显示v2的值，否则显示v1的值
case
when expr1 then v1
[when expr2 then v2…]
[else wn]
end
case表示函数开始，end表示函数结束
# 或者
case expr
when e1 then v1
[when e2 then v2…]
[else wn]
end
case表示函数开始，end表示函数结束
######案例
select if(10>9,'大于','小于') as result -- 大于
select if(10<9,'大于','小于') as result -- 小于
select ifnull(null,'空') -- 空
select ifnull(1,'空') -- 1
-- 查看员工表性别的分布情况，请使用男或女来表示男女性别
-- 查看员工表性别的分布情况，请使用男或女来表示男女性别
select
姓名,
if(性别='-1','女','男') as 性别
from
员工;4.类型转换函数
cast(expression AS type)
convert(expression, type)
cast() 是依照 ANSI 的规格，convert() 是依照 ODBC 的规格。
MySQL类型转换函数参数 :
整数 : signed
无符号整数 : unsigned
二进制,同带binary前缀的效果 : binary
字符型,可带参数 : char()
日期 : date
时间: time
日期时间型 : datetime
浮点数 : decimal
Format(x,n)# 将数字x进行格式化，将x保留到小数点后n位5.数据分裂
substring_index(str,delim,count) 三个参数的含议：
str：要处理的字符串
delim：分隔符
count：计数
#如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容。 相反，如果是负数，那么就是从右边开始数，第N个分隔符右边的所有内容。
如substring_index('www.mysql.com','.',2)，返回：www.mysql6.数据拼装
concat(str1,str2,…)
#将多个字符串连接成一个字符串。如有任何一个参数为NULL ，则返回值为 NULL。
concat_ws(separator,str1,str2,…)
#第一个参数是其它参数的分隔符。如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。但是CONCAT_WS()不会忽略任何空字符串,会忽略所有的 NULL
group_cancat
功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。
语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )
说明：通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。7.加密函数
md5() 计算字符串str的MD5校验和
基本函数
| 函数 | 说明 |
| count() | 返回匹配指定条件的行数 |
| first() | 返回指定的列中第一个记录的值 |
| last() | 返回指定的列中最后一个记录的值 |
| max() | 返回指定列的最大值 |
| min() | 返回指定列的最小值 |
| avg() | 返回数值列的平均值 |
| sum() | 返回数值列的总数 |
| exists 运算符 | 用于判断查询子句是否有记录，有一或多条返回 True，否则False |
| mid() | 用于从文本字段中提取字符 |
| len() | 返回文本字段中值的长度 |
| round(column_name,decimals) | 用于把数值字段舍入为指定的小数位数 |
| now() | 返回当前系统的日期和时间 |
| format(number,cou) | 用于对字段的显示进行格式化（千位分隔符，保留几位小数，会四舍五入） |
| substring_index(str,delim,count) | 返回从字符串str分隔符delim中的计数发生前的子字符串（截取字符串） |
substring_index(str,delim,count)
——返回从字符串str分隔符delim中的计数发生前的子字符串。
如果计数是正的，则返回一切到最终定界符(从左边算起)的左侧。
如果count为负，则返回一切到最后一个分隔符(右算起)的右侧。
如substring_index('www.mysql.com','.',2)，返回：www.mysql数学函数
| 函数 | 说明 |
| abs(x) | 返回x的绝对值 |
| ceil(x),ceilin(x) | 返回不小于x的最小整数值 |
| floor(x) | 返回不大于x的最大整数值 |
| truncate(x,y) | 返回数值x保留到小数点后y位的值（非四舍五入，直接去除后面） |
| round(x) | 返回离x最近的整数（四舍五入） |
| round(x,y) | 返回数值x保留到小数点后y位的值,（四舍五入） |
| rand() | 返回0~1的随机数 |
| rand(x) | 返回0~1的随机数,x值相同时返回随机数相同 |
| sign(x) | 返回参数作为-1,0或1的符号，该符号取决于x的值为负、零或正 |
| pi() | 返回∏的值，默认的显示小数位数是7位 |
| pow(x,y),power(x,y) | 返回x的y乘方的结果值 |
| sqrt(x) | 返回非负数x的二次方根 |
| exp(x) | 返回e的x乘方后的值 |
| mod(x,y) | 返回x除以y以后的余数 |
| log(x) | 返回x的基数为2的对数 |
| log10(x) | 返回x的基数为10的对数 |
| radians(x) | 将角度转换为弧 |
| degrees(x) | 返回参数x,该参数由弧度被转化为 |
| sin(x) | 返回x正弦，其中x在弧度中被给定 |
| asin(x) | 返回x的反正弦，即正弦为x的值 |
| cos(x) | 返回x的余弦，其中x在弧度中被给定 |
| acos(x) | 返回x的反余弦，即余弦为x的值 |
| tan(x) | 返回x的反正切,正切为x的值 |
| cot(x) | 返回(x):返回x的余切 |
字符串函数
| 函数 | 说明 |
| char_length(s) | 返回字符串s的字符数 |
| length(s) | 返回值为字符串s的长度,单位为字节 |
| concat(s1,s2,…) | 返回结果为连接参数产生的字符串（连接字符串）如有参数含null ，则返回值为 null |
| count_ws(separator,str1,str2,…) | 可以指定参数之间的分隔符，不会忽略任何空字符串，会忽略所有的 NULL |
| insert(s1,x,len,s2) | 将字符串s2替换s1的x位置开始长度为len的字符串 |
| upper(s),ucase(s) | 将字符串s的所有字母都变成大写字母 |
| lower(s),lcase(s) | 将字符串s的所有字母都变成小写字母 |
| left(s,n) | 从字符串s开始的前n个字符 |
| right(s,n) | 从字符串s开始，返回右边的n个字符 |
| substring(s,n,len) | 获取从字符串s中的第n个位置开始长度为len的字符串 |
| rtrim(s) | 返回字符串s,结尾空格字符被删除 |
| trim(s) | 去掉字符串s开始处和结尾处的空格 |
| trim(s1 from s) | 去掉字符串s中开始处和结尾处的字符串s1 |
| ltrim(s) | 返回字符串s,其引导空格字符被删除 |
| instr(s,s1) | 从字符串s中获取s1的开始位置 |
| lpad(s1,len,s2) | 返回字符串s1,其左边由字符串s2填补到len字符长度，假如s1的长度大于len,则返回值被缩短至len字符 |
| rpad(s1,len,s2) | 返回字符串s1,其右边由字符串s2填补到len字符长度，假如s1的长度大于len,则返回值被缩短至len字符 |
| repeat(s,n) | 将字符串s重复n次 |
| space(n) | 返回n个空格 |
| replace(s,s1,s2) | 用字符串s2替代字符串s中的字符串s1（字符串替换） |
| strcmp(s1,s2) | 比较字符串s1和s2 |
| locate(s1,s),position(s1 in s) | 从字符串s中获取s1的开始位置 |
| reverse(s) | 将字符串s的顺序反过来 |
| elt(n,s1,s2,…) | 返回第n个字符串 |
数据类型转换函数
cast(x as type)
cast函数将一个类型的x值转换为另一个类型的值
type参数可以填写char(n)、date、time、datetime、decimal等转换为对应的数据类型
convert(x, type)
#这个类型 可以是以下值其中的 一个：
整数 : signed
无符号整数 : unsigned
二进制,同带binary前缀的效果 : binary
字符型,可带参数 : char()
日期 : date
时间: time
日期时间型 : datetime
浮点数 : decimal
cast() 是依照 ANSI 的规格
convert() 是依照 ODBC 的规格日期时间函数
now()：返回当前日期时间
curdate()：返回当前日期
curtime() 返回当前时间
date(date) 提取时间的日期部分
year(date)、month(date)&day(date)
——获取年月日的函数
date可以是年月日组成的日期，也可以是年月日时分秒组成的日期时间
year(date)返回日期格式中的年份
month(date)返回日期格式中的月份
day(date)返回年日期格式中的日份
date_add(date,interval expr type)
date_sub(date,interval expr type)——对指定起始时间进行加减操作
date用来指定起始时间
date可以是年月日组成的日期，也可以是年月日时分秒组成的日期时间
expr用来指定从起始时间添加或减去的时间间隔
type指示expr被解释的方式，type可以可以是以下值SECOND，MINUTE，HOUR，DAY，WEEK，MONTH，QUARTER，YEAR
date_add函数对起始时间进行加操作，date_sub函数对起始时间进行减操作
例如：date_add('2021-08-03 23:59:59',interval 1 second)返回2021-08-04 24:00:00，
date_sub('2021-08-03 23:59:59',interval 2 month)返回2021-06-03 23:59:59
datediff(date1,date2)——计算两个日期之间间隔的天数
datediff函数由date1-date2计算出间隔的时间，只有date的日期部分参与计算，时间不参与
例如：datediff('2021-06-08','2021-06-01')返回7，
datediff('2021-06-08 23:59:59','2021-06-01 21:00:00')返回7，
datediff('2021-06-01','2021-06-08')返回-7
date_format(date,format)——将日期和时间格式化
date_format函数根据format指定的格式显示date值
可以换使用的格式较多，百度
例如date_format（date,'%Y-%m-%d %T'） 为 2021-01-01 13:20:52curdate(),current_date():返回当前日期
curtime(),current_time():返回当前时间
now(),current_timestamp(),localtime(),sysdate(),localtimestamp():返回当前日期和时间
year(d):返回日期的年份值(年)
month(d):返回日期d中的月份值，范围是1~12(月)
Week(d):计算机日期d是本年的第几个周(周)
day()：(日)
hour(t):返回时间t中的小时值(时)
minute(t):返回时间t中的分钟值(分)
second(t):返回时间t中的秒钟值(秒)
dayname(d):返回日期d是星期几(英文的)
dayofweek(d):返回日期是星期几,1表示星期日
yearweek(date[,mode]):获取年份和周数,mode为1，指一周以星期一开始
dayofyear(d):计算日期d是本年的第几天
dayofmonth(d):计算日期d是本月的第几天
quarter(d):返回日期d是第几季度,范围是1~4
extract(type from d):从日期d中获取指定的值，type指定返回的值,如year,hour等时间转换为秒
time_to_sec(t):将时间t转换为秒
datediff(d1,d2):计算日期d1~d2之间相隔的天数
adddate(d,n): 计算起始日期d加上n天的日期
adddate(d,interval expr type):计算起始日期d加上一个时间段后的日期
timeStampdiff(type,d1,d2)：计算d2-d1返回结果相差的时间单位（由type决定：year,month,day,week...)
timestampdiff(month,'2009-10-01','2019-09-01');
timestampdiff(year,'2009-10-01','2019-09-01');
subdate(d,n):计算起始日期d减去n天后的日期
subdate(d,interval expr type):计算起始日期d减去一个时间段后的日期
addtime(t,n):计算起始时间t加上n秒的时间
subtime(t,n):计算起始时间t减去n秒的时间
get_format(type,s):根据字符串s获取type类型数据的显示格式
from_unixtime(1336542121,时间格式)#时间戳转日期
unix_timestamp('2013-04-08') #日期转时间戳
案例：
adddate(curdate(),-1) # 昨天
adddate(curdate(),interval -2 year) #2年前
adddate(curdate(),interval 2 day) # 2天后
subdate(curdate(),2) # 2天前
subdate(curdate(),interval 2 day) # 2天前
subdate(curdate(),interval 2 year) # 2年前
subdate(now(),interval weekday(now()) day) #本周的第一天
adddate(curdate(),interval -day(curdate())+1 day) #本月第一天
adddate(curdate()-day(curdate())+1,interval 1 month) #下个月的第一天
subdate( subdate( now( ) , INTERVAL dayofmonth( now( ) ) -1 day ) , interval 1 month ) #上个月的第一天时间
datediff(date_add(curdate()-day(curdate())+1,interval 1 month),adddate(curdate(),interval -day(curdate())+1 day)) #当前月的天数
weekday(now())#今天是一周第几天或星期几,返回的是数字：0为周一，6为周日
week(now())#获取某一天是那一年的第几周YXN-sql
2022-10-01