MySQL进阶-常用函数

发布于:2023-01-22 ⋅ 阅读:(6) ⋅ 点赞:(0) ⋅ 评论:(0)

目录

一、常用函数

 1. 数学函数

2.字符串函数

3.日期和时间函数

4.条件判断函数

5.系统信息函数

6.加密函数

7.其他函数


 

一、常用函数

函数

函数存储着一系列sql语句,调用函数就是一次性执行这些语句。即函数可以降低语句重复。

 1. 数学函数

数学函数是MySQL中常用的一类函数。其主要用于处理数字,包括整型和浮点数等等。

函数 说明
ABS(X) 返回X的绝对值。
FLOOR(X) 返回不大于X的最大整数。
CEIL(X)、CEILING(X) 返回不小于X的最小整数。
TRUNCATE(X,D) 返回数值X保留到小数点后D位的值,截断时不进行四舍五入。
ROUND(X) 返回离X最近的整数,截断时要进行四舍五入。
ROUND(X,D) 保留X小数点后D位的值,截断时要进行四舍五入。
RAND() 返回0~1的随机数。
SIGN(X) 返回X的符号(负数,零或正)对应-1,0或1。
PI() 返回圆周率的值。默认的显示小数位数是6位。
POW(x,y)、POWER(x,y) 返回x的y次乘方的值。
SQRT(x) 返回非负数的x的二次方根。
EXP(x) 返回e的x乘方后的值。
MOD(N,M) 返回N除以M以后的余数。
LOG(x) 返回x的自然对数,x相对于基数2的对数。
LOG10(x) 返回x的基数为10的对数。
RADIANS(x) 返回x由角度转化为弧度的值。
DEGREES(x) 返回x由弧度转化为角度的值。
SIN(x)、ASIN(x) 前者返回x的正弦,其中x为给定的弧度值;后者返回x的反正弦值,x为正弦。
COS(x)、ACOS(x) 前者返回x的余弦,其中x为给定的弧度值;后者返回x的反余弦值,x为余弦。
TAN(x)、ATAN(x) 前者返回x的正切,其中x为给定的弧度值;后者返回x的反正切值,x为正切。
COT(x) 返回给定弧度值x的余切。

 RAND()获取到的是一个 0 ~ 1 之间的小数

 如果要特定范围的随机整数,可以将此小数乘以特定值后取整
select round(rand()*100) from dual;

2.字符串函数

字符串函数是MySQL中最常用的一类函数。字符串函数主要用于处理表中的字符串。

函数 说明
CHAR_LENGTH(str) 计算字符串字符个数。
LENGTH(str) 返回值为字符串str的长度,单位为字节。
CONCAT(s1,s2,...) 返回连接参数产生的字符串,一个或多个待拼接的内容,任意一个为NULL则返回值为NULL。
SUBSTRING(s,n,len)、MID(s,n,len) 两个函数作用相同,从字符串s中返回一个第n个字符开始、长度为len的字符串。
CONCAT_WS(x,s1,s2,...) 返回多个字符串拼接之后的字符串,每个字符串之间有一个x。
INSERT(s1,x,len,s2) 返回字符串s1,其子字符串起始于位置x,被字符串s2取代len个字符。
INSTR(s1, s2) 返回字符串s2,在字符串s1中开始的位置,下标从1开始,没有找到返回0。
LOWER(str)、LCASE(str) 将str中的字母全部转换成小写。
UPPER(str)、UCASE(str) 将字符串中的字母全部转换成大写。
LEFT(s,n)、RIGHT(s,n) 前者返回字符串s从最左边开始的n个字符,后者返回字符串s从最右边开始的n个字符。
LPAD(s1,len,s2)、RPAD(s1,len,s2) 前者返回s1,其左边由字符串s2填补到len字符长度,假如s1的长度大于len,则返回值被缩短至len字符;前者返回s1,其右边由字符串s2填补到len字符长度,假如s1的长度大于len,则返回值被缩短至len字符。
LTRIM(s)、RTRIM(s) 前者返回字符串s,其左边所有空格被删除;后者返回字符串s,其右边所有空格被删除。
TRIM(s) 返回字符串s删除了两边空格之后的字符串。
TRIM(s1 FROM s) 删除字符串s两端所有子字符串s1,未指定s1的情况下则默认删除空格。
REPEAT(s,n) 返回一个由重复字符串s组成的字符串,字符串s的数目等于n。
SPACE(n) 返回一个由n个空格组成的字符串。
REPLACE(s,s1,s2) 返回一个字符串,用字符串s2替代字符串s中所有的字符串s1。
STRCMP(s1,s2) 若s1和s2中所有的字符串都相同,则返回0;根据当前分类次序,第一个参数小于第二个则返回-1,其他情况返回1。
LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1) 三个函数作用相同,返回子字符串str1在字符串str中的开始位置(从第几个字符开始)。
REVERSE(s) 将字符串s反转。
ELT(N,str1,str2,str3,str4,...) 返回第N个字符串。
FIELD(s,s1,s2,...) 返回第一个与字符串s匹配的字符串的位置。
FIND_IN_SET(s1,s2) 返回在字符串s2中与s1匹配的字符串的位置。
MAKE_SET(x,s1,s2,...) 按x的二进制数从s1,s2...,sn中选取字符串

重要的函数示例

SELECT CONCAT(str1,str2,...);
-- concat是连接字符串,mysql中concat支持多个参数
SELECT CONCAT('hello','mysql') ;

3.日期和时间函数

日期和时间函数是MySQL中另一最常用的函数。其主要用于对表中的日期和时间数据的处理。

函数 说明
CURDATE()、CURRENT_DATE() 返回当前日期,格式:yyyy-MM-dd。
CURTIME()、CURRENT_TIME() 返回当前时间,格式:HH:mm:ss。
NOW()、CURRENT_TIMESTAMP()、LOCALTIME()、SYSDATE()、LOCALTIMESTAMP() 返回当前日期和时间,格式:yyyy-MM-dd HH:mm:ss。
DATEDIFF(d1,d2) 计算日期d1与d2之间相隔的天数。
ADDDATE(d,n) 计算起始日期d加上n天的日期,n为正数向后推,负数向前。
DATE_FORMAT(d,f) 按照表达式 f 的要求显示日期d。
TIME_FORMAT(t,f) 按照表达式 f 的要求显示时间t。
STR_TO_DATE(s,f) 按照表达式 f 的要求,转换字符串s成日期时间类型。
UNIX_TIMESTAMP() 返回一个格林尼治标准时间1970-01-01 00:00:00到现在的秒数。
UNIX_TIMESTAMP(date) 返回一个格林尼治标准时间1970-01-01 00:00:00到指定时间的秒数。
FROM_UNIXTIME(date) 和UNIX_TIMESTAMP互为反函数,把UNIX时间戳转换为普通格式的时间。
UTC_DATE() 返回当前UTC(世界标准时间)日期值,其格式为"YYYY-MM-DD"或"YYYYMMDD"。
UTC_TIME() 返回当前UTC时间值,其格式为"YYYY-MM-DD"或"YYYYMMDD"。具体使用哪种取决于函数用在字符串还是数字语境中
MONTH(d) 返回日期d中的月份值,范围是1~12。
MONTHNAME(d) 返回日期d中的月份名称,如:January、February等。
DAYNAME(d) 返回日期d是星期几,如:Monday、Tuesday等。
DAYOFWEEK(d) 返回日期d是星期几,如:1表示星期日,2表示星期一等。
WEEKDAY(d) 返回日期d是星期几,如:0表示星期一,1表示星期二等。
WEEK(d) 计算日期d是本年的第几个星期,范围是0~53。
WEEKOFYEAR(d) 计算日期d是本年的第几个星期,范围是1~53。
DAYOFYEAR(d) 计算日期d是本年的第几天。
DAYOFMONTH(d) 计算日期d是本月的第几天。
YEAR(d) 返回日期d中的年份值。
QUARTER(d) 返回日期d是第几季度,范围是1~4。
HOUR(t) 返回时间t中的小时值。
MINUTE(t) 返回时间t中的分钟值。
SECOND(t) 返回时间t中的秒钟值。
EXTRACT(type FROM date) 从日期中提取一部分,type可以是YEAR、YEAR_MONTH、DAY_HOUR、DAY_MICROSECOND、DAY_MINUTE、DAY_SECOND
TIME_TO_SEC(t) 将时间t转换为秒。
SEC_TO_TIME(s) 将以秒为单位的时间s转换为时分秒的格式。
TO_DAYS(d) 计算日期d至0000年1月1日的天数。
FROM_DAYS(n) 计算从0000年1月1日开始n天后的日期。
ADDDATE(d,INTERVAL expr type) 计算起始日期d加上一个时间段后的日期。
DATE_ADD(d,INTERVAL expr type) 同ADDDATE(d,INTERVAL expr type)
SUBDATE(d,n) 计算起始日期d减去n天的日期。
SUBDATE(d,INTERVAL expr type) 计算起始日期d减去一个时间段后的日期。
ADDTIME(t,n) 计算起始时间t加上n秒的时间。
SUBTIME(t,n) 计算起始时间t减去n秒的时间。
DATE_FORMAT(d,f) 按照表达式 f 的要求显示日期d。
TIME_FORMAT(t,f) 按照表达式 f 的要求显示时间t。
GET_FORMAT(type, s) 根据字符串s获取type类型数据的显示格式。

参数说明:

type格式:

SECOND 秒 SECONDS

MINUTE 分钟 MINUTES

HOUR 时间 HOURS

DAY 天 DAYS

MONTH 月 MONTHS

YEAR 年 YEARS

MINUTE_SECOND 分钟和秒 "MINUTES:SECONDS"

HOUR_MINUTE 小时和分钟 "HOURS:MINUTES"

DAY_HOUR 天和小时 "DAYS HOURS"

YEAR_MONTH 年和月 "YEARS-MONTHS"

HOUR_SECOND 小时, 分钟, "HOURS:MINUTES:SECONDS"

DAY_MINUTE 天, 小时, 分钟 "DAYS HOURS:MINUTES"

DAY_SECOND 天, 小时, 分钟, 秒 "DAYS HOURS:MINUTES:SECONDS"

重要的函数示例

SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日 %H时%i分%s秒');
  -- 输出结果:2022年08月12日08时53分47秒

 

4.条件判断函数

条件函数用来在SQL语句中进行条件判断。根据不同的条件,执行不同的SQL语句。MySQL支持的条件判断函数及作用。

函数 说明
IF(expr,v1,v2) 如果表达式expr成立,则执行v1;否则执行v2。
IFNULL(v1,v2) 如果v1不为空,则显示v1的值;否则显示v2的值。

重要函数示例

SELECT IF(TRUE,'A','B');
 -- 输出结果:A
SELECT IFNULL(NULL,'B');
 -- 输出结果:B
SELECT IFNULL('茸弋的博客','B');
 -- 输出结果:茸弋的博客

 

 

 

5.系统信息函数

系统信息函数用来查询MySQL数据库的系统信息。

函数 说明
VERSION() 获取数据库的版本号。
CONNECTION_ID() 获取服务器的连接数。
DATABASE()、SCHEMA() 获取当前数据库名。
USER()、SYSTEM_USER()、SESSION_USER() 获取当前用户名。
CURRENT_USER()、CURRENT_USER 获取当前用户名。
CHARSET(str) 获取字符串str的字符集。
COLLATION(str) 获取字符串str的字符排序方法。
LAST_INSERT_ID() 获取最近生成的AUTO_INCREMENT值。

6.加密函数

加密函数是MySQL中用来对数据进行加密的函数。因为数据库中有些很敏感的信息不希望被其他人看到,所以就可以通过加密的方式来使这些数据变成看似乱码的数据。

函数 说明
PASSWORD(str) 对字符串str进行加密。经此函数加密后的数据是不可逆的。其经常用于对普通数据进行加密。
MD5(str) 对字符串str进行MD5加密。经常用于对普通数据进行加密。
ENCODE(str,pass_str) 使用字符串pass_str来加密字符串str。加密后的结果是一个二进制数,必须使用BLOB类型的字段来保存它。
DECODE(crypt_str,pass_str) 使用字符串pass_str来为crypt_str解密。

重要函数说明

网上的MD5加密算法是进行反向查询,通过穷举字符组合的方式,来对一些常见的字符串进行匹配。

7.其他函数

MySQL中除了上述内置函数以外,还包含很多函数。例如,数字格式化函数FORMAT(x,n),IP地址与数字的转换函数INET_ATON(ip),还有加锁函数GET_LOCT(name,time)、解锁函数RELEASE_LOCK(name)等等。

函数 说明
FORMAT(X,D) 将数字X格式化,将X保留到小数点后D位,截断时要进行四舍五入。
CONV(N,from_base,to_base) 不同进制数之间的转换,返回值为数值N的字符串表示,由from_base进制转换为to_base进制。
INET_ATON(expr) 给出一个作为字符串的网络地址的点地址表示,返回一个代表该地址数值的整数,地址可以使4或8比特。
INET_NTOA(expr) 给定一个数字网络地址(4或8比特),返回作为字符串的该地址的点地址表示。
BENCHMARK(count,expr) 重复执行count次表达式expr,它可以用于计算MySQL处理表达式的速度,结果值通常是0(0只是表示很快,并不是没有速度)。另一个作用是用它在MySQL客户端内部报告语句执行的时间。
CONVERT(str USING charset) 使用字符集charset表示字符串str。