SQL常用函数

目录

一、时间函数

二、聚合函数

三、字符串函数   

四、 数学函数

五、控制流函数 

六、格式化函数 

七、系统信息函数

八、加密函数

一、时间函数

1.date\datetime\time三个时间类型的区别

	时间类型	含义	例子
1	date	用年月日表示时间	2024/5/1
2	datetime	用年月日 时分秒表示时间	2024/5/1 14:23
3	time	用时分秒表示时间	14:23

2.返回当前时间+

• now()
• current_timestamp()
• localtime() 

返回系统当前的时间：
select now() as 当前时间1, current_timestamp() as 当前时间2,localtime() as 当前时间3;

 

 3.返回对应日期对应的年/月/日/月份名/星期数

• year()
• month()
• day()
• monthname()
• dayname()

返回对应日期对应的年/月/日/月份名/星期数
select year(now())as 年,month(now())as 月,day(now())as 日,monthname(now())as 月份名,dayname(now())as 星期数;

 4.返回对应日期所对应的星期几和周数

• weekday()  星期几
• week()  周数

weekday返回的值默认是按0-6来表示星期日、星期一...星期六

这两者都是默认从星期日开始

返回对应日期所对应的星期几和对应年份的周数，这两者都是默认从星期日开始算，但按我们的习惯算的话，应该是对应的公式加一。
select weekday(now())+1,week(now())+1;

• weekofyear()  对应的周数
• yearweek()  年份周数
• dayofweek()  一周的第几天
• dayofyear()  一年的第几天

 select weekofyear(now())+1 as 对应的周数,yearweek(now())+1 as 年份周数,
dayofweek(now())as 一周的第几天,dayofyear(now()) as 一年的第几天;

 5.返回两个日期时间之间的差值

• datediff(expr1,expr2) 
• timestampdiff(unit,datetime_expr1,datetime_expr2)

两者的区别：

atediff(expr1,expr2)返回日期expr1与expr2相差的天数，当expr1大于expr2时，返回值为正数，当expr1小于expr2时，返回的值是负数。
而timestampdiff返回的正负号刚好与datediff相反，timestampdiff(unit,datetime_expr1,datetime_expr2)返回datetime_expr1与datetime_expr2的unit差值，unit可以是second\hour\second\year\day。

 --datediff函数运用
当expr1大于expr2时，返回dif1的结果为正数，反之返回dif2的结果为负数

select datediff('2024-5-27','2024-5-20')as dif1, datediff('2024-5-20','2024-5-27')as dif2;

 

分别返回小时差、天数差和年份差

select timestampdiff(hour,'2024-5-27 23:00:00','2024-5-20 22:00:00')as 小时差,
timestampdiff(hour,'2024-5-20 22:00:00','2024-5-27 23:00:00')as 小时差2,
timestampdiff(day,'2024-5-13 12:00:00','2024-5-20 13:00:00')as 天数差,
timestampdiff(year,'2022-5-19 23:00:00','2023-5-20 13:00:00')as 年份差;

6.将日期格式进行转化 

• date_format(date,format)   将日期格式转化

 将datetime时间类型转换成date时间类型，即将年月日时分秒转化成年月日。
select now() as 当前时间,date_format(now(),'%Y-%m-%d') as 转换后的时间;

7.返回某日期被减去指定时间间隔后的日期 

•  date_sub(date,INTERVAL expr unit)

计算当前时间对应的美国时间，当前时间指的是北京时间，已知美国时间比北京时间晚13个小时 
select now() as 北京时间,date_sub(now(),interval 13 hour)as 美国时间;

 8.返回某日期被加上指定时间间隔后的日期

•  date_ad(date,INTERVAL expr unit)

select now() as 北京时间,date_add(now(),interval -13 hour)as 美国时间;

 

二、聚合函数

• AVG()  只适用于数值类型的字段或变量。不包含NULL值
• SUM()   只适用于数值类型的字段或变量。不包含NULL值 
• MAX()  适用于数值类型、字符串类型、日期时间类型的字段（或变量）不包含NULL值
• MIN()  适用于数值类型、字符串类型、日期时间类型的字段（或变量）不包含NULL值
• COUNT()  计算指定字段在查询结构中出现的个数（不包含NULL值）

三、字符串函数 

1.保留到小数点后 n 位，最后一位四舍五入

• FORMAT(x,n)   

SELECT FORMAT(150520.54345, 2); 

2. 字符串替换

• INSERT(s1,x,len,s2)

SELECT INSERT("malegb.com", 1, 6, "qunima");

  

 3.字符串截取

• SUBSTR(s, start, length)
• SUBSTRING(s, start, length)

SELECT SUBSTR("startar", 2, 3),SUBSTRING("startar", 2, 3);

4.字符串按字符截取  

• SUBSTRING_INDEX(string,sep,num)  

string：用于截取目标字符串的字符串。可为字段，表达式等。

sep：分隔符，string存在且用于分割的字符，比如“，”、“.”等。

num：序号，为非0整数。若为整数则表示从左到右数，若为负数则从右到左数。

SELECT SubString_index('VN,狗熊,小法,稻草人,妖姬,炸弹人',',',3)

 

5.其他字符串函数

• ASCII(char)   返回字符的ASCII码值
• BIT_LENGTH(str)   返回字符串的比特长度
• CONCAT(s1,s2...,sn)   将s1,s2...,sn连接成字符串
• CONCAT_WS(sep,s1,s2...,sn)   将s1,s2...,sn连接成字符串，并用sep字符间隔
• FIND_IN_SET(str,list)   分析逗号分隔的list列表，如果发现str，返回str在list中的位置
• LCASE(str)或LOWER(str)    返回将字符串str中所有字符改变为小写后的结果
• LEFT(str,x)   返回字符串str中最左边的x个字符
• LENGTH(s)   返回字符串str中的字符数
• LTRIM(str)   从字符串str中切掉开头的空格
• POSITION(substr,str)   返回子串substr在字符串str中第一次出现的位置
• QUOTE(str)   用反斜杠转义str中的单引号
• REPEAT(str,srchstr,rplcstr)   返回字符串str重复x次的结果
• REVERSE(str)   返回颠倒字符串str的结果
• RIGHT(str,x)   返回字符串str中最右边的x个字符
• RTRIM(str)   返回字符串str尾部的空格
• STRCMP(s1,s2)   比较字符串s1和s2
• TRIM(str)   去除字符串首部和尾部的所有空格
• UCASE(str)或UPPER(str)   返回将字符串str中所有字符转变为大写后的结果

四、 数学函数

• ABS(x)   返回x的绝对值
• BIN(x)   返回x的二进制（OCT返回八进制，HEX返回十六进制）
• CEILING(x)   返回大于x的最小整数值
• EXP(x)   返回值e（自然对数的底）的x次方
• FLOOR(x)   返回小于x的最大整数值
• GREATEST(x1,x2,...,xn)返回集合中最大的值
• LEAST(x1,x2,...,xn)      返回集合中最小的值
• LN(x)    返回x的自然对数
• LOG(x,y)   返回x的以y为底的对数
• MOD(x,y)    返回x/y的模（余数）
• PI()返回pi的值（圆周率）
• RAND()返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
• ROUND(x,y)   返回参数x的四舍五入的有y位小数的值
• SIGN(x)    返回代表数字x的符号的值
• SQRT(x)   返回一个数的平方根
• TRUNCATE(x,y)     返回数字x截短为y位小数的结果

五、控制流函数 

• CASE WHEN[test1] THEN [result1]...ELSE [default] END     如果testN是真，则返回resultN，否则返回default
• CASE [test] WHEN[val1] THEN [result]...ELSE [default]END     如果test和valN相等，则返回resultN，否则返回default
• IF(test,t,f)    如果test是真，返回t；否则返回f
• IFNULL(arg1,arg2)    如果arg1不是空，返回arg1，否则返回arg2
• NULLIF(arg1,arg2)    如果arg1=arg2返回NULL；否则返回arg1

六、格式化函数 

• DATE_FORMAT(date,fmt)    依照字符串fmt格式化日期date值
• FORMAT(x,y)    把x格式化为以逗号隔开的数字序列，y是结果的小数位数
• INET_ATON(ip)    返回IP地址的数字表示
• INET_NTOA(num)    返回数字所代表的IP地址
• TIME_FORMAT(time,fmt)    依照字符串fmt格式化时间time值

七、系统信息函数

• DATABASE()   返回当前数据库名
• BENCHMARK(count,expr)  将表达式expr重复运行count次
• CONNECTION_ID()   返回当前客户的连接ID
• FOUND_ROWS()   返回最后一个SELECT查询进行检索的总行数
• USER()或SYSTEM_USER()  返回当前登陆用户名
• VERSION()   返回MySQL服务器的版本

八、加密函数

• AES_ENCRYPT(str,key)   返回用密钥key对字符串str利用高级加密标准算法加密后的结果，调用AES_ENCRYPT的结果是一个二进制字符串，以BLOB类型存储
• AES_DECRYPT(str,key)   返回用密钥key对字符串str利用高级加密标准算法解密后的结果
• DECODE(str,key)    使用key作为密钥解密加密字符串str
• ENCRYPT(str,salt)   使用UNIXcrypt()函数，用关键词salt(一个可以惟一确定口令的字符串，就像钥匙一样)加密字符串str
• ENCODE(str,key)   使用key作为密钥加密字符串str，调用ENCODE()的结果是一个二进制字符串，它以BLOB类型存储
• MD5()    计算字符串str的MD5校验和
• PASSWORD(str)    返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。
• SHA()     计算字符串str的安全散列算法(SHA)校验和