前言
这是我参与11月更文挑战的第1天,活动详情查看:2021最后一次更文挑战 。
MySQL
常用函数不熟练?看我这篇就够了!!
▶ MySQL 常用函数总览
★ 字符串函数
LENGTH(str)
掌握指数:★★★★
函数说明:
返回
str
字符串字节长度
注意:
-
英文的一个字符为 1 个字节
-
GBK
编码的为 2 个字节 -
UTF-8
编码的汉字为 3 个字节
SQL
语句示例:
# 英文字符串字节长度
select length('HUALEI'); # 6
select length('大家好'); # 9
复制代码
INSERT(str,pos,len,newstr)
掌握指数:★★★
函数说明:
向
str
中第pos
位置开始插入长度为len
的newStr
字符串
SQL
语句示例:
如果我想给 str = 'HUALEI'
,想用 insert()
函数将其拼接成 "HUALEI is a hansome boy.",那我该怎么做呢?
select insert('HUALEI', length('HUALEI')+1, length('is a hansome boy.'), 'is a hansome boy.'); # HUALEI
or
select insert('HUALEI', length('HUALEI')+2, length('is a hansome boy.'), 'is a hansome boy.'); # HUALEI
but answer:
select insert('HUALEI ', length('HUALEI')+2, length('is a hansome boy.'), 'is a hansome boy.'); # HUALEI is a hansome boy.
复制代码
注意: MySQL
中从 1 开始算下标,插入要满足 pos <= length(str)
。
那如果是中文汉字字符串呢,如何让 “青花瓷” 变成 “青花烤瓷”,“苏格拉底广场” 变成 “苏格拉底广场舞” ?
select insert('青花瓷', 3, length('烤瓷'), '烤瓷'); # 青花烤瓷
select insert('苏格拉底广场', 7, length('舞'), '舞'); # 苏格拉底广场舞
复制代码
第一个 SQL
中 pos => 3
为汉字字符串的下标索引,从第三个汉字字符开始插入长度为 length('烤瓷')
的 '烤瓷'
;再看第二个 SQL
, 7 大于字符个数,但是并不大于 length('苏格拉底广场') => 18
,所以新字符串在最后一个位置上插入成功。
LEFT(str,len)
掌握指数:★★★
函数说明:
从字符串
str
左边开始截取长度为len
的字符串
SQL
语句示例:
select left('HUALEI', 3); # HUA
复制代码
那 “大家好” 这个汉字字符串我想左截取出 “大家”,该如何做呢?
select left('大家好', length('大家')); # 大家好
复制代码
奇怪,截取的结果和预期不一样,为什么呢?
注意: 这里的 len
还是并不是通过 length()
而是通过 char_length()
得到的,从 1 开始到 len
即为截取的目标字符串。
所以,对于中文字符串来说,正确的写法应该是:
select left('大家好', 2); # 大家
RIGHT(str,len)
掌握指数:★★★
函数说明:
从字符串
str
右边开始截取长度为len
的字符串
SQL
语句示例:
select right('HUALEI', 3); # LEI
select right('大家好', 1); # 好
复制代码
不赘述,和 LEFT()
一个道理。
SUBSTR(str FROM pos) <=> SUBSTR(str,pos) SUBSTR(str FROM pos FOR len) <=> SUBSTR(str,pos,len)
掌握指数:★★★★
函数说明:
从
str
的第pos
个位置开始截取长度为len
的子串,如果没有len
参数则截取到末尾
SQL
语句示例:
select substr('abcdefg' from 2); # bcdefg
# 等价于
select substr('abcdefg', 2); # bcdefg
select substr('abcd' from 1 for 3); # abc
# 等价于
select substr('abcd', 1, 3); # abc
# 这里的 len 理解为从 pos 开始截取几个汉字
select substr('大家好', 2, 1); # 家
复制代码
STRCMP(expr1,expr2)
掌握指数:★★★
函数说明:
expr1 > expr2 =>
返回 1;expr1 = expr2 =>
返回 0;expr1 < expr2 =>
返回 -1
SQL
语句示例:
select strcmp('bbcd','bacd'); # 对应位置上的字符对应相比,返回 1
select strcmp('你好', '你好'); # 两个字符串一模一样,返回 0
select strcmp('ABC','abc'); # 不区分大小写,返回 0
select strcmp('abadf','abadfe'); # 后面大,返回-1
复制代码
CONCAT(str1,str2,...)
掌握指数:★★★★
函数说明:
将
str1
、str2
... 等字符串连接成一个新的字符串
SQL
语句示例:
select concat('hel','llo'); # hello
select concat('大家','好'); # 大家好
复制代码
注意: 只要连接的字符串中存在一个 null
值,最终结果也将是 null
。
select concat(null, 'abc'); # null
select concat('abc',null); # null
复制代码
LOCATE(substr,str) | POSITION(substr IN str) | INSTR(str,substr)
掌握指数:★★★★
函数说明:
返回子串
substr
在父串str
中的开始位置,如果父串中压根就不包含子串那么返回 0
SQL
语句示例:
select locate('LEI', 'HUALEI'); # 子串 'LEI' 在父串 'HUALEI' 中的开始位置是 4
select locate('LEI ', 'HUALEI'); # 子串不存在,返回 0
select position('LEI' in 'HUALEI'); # 子串 'LEI' 在父串 'HUALEI' 中的开始位置是 4
select position('LEI ' in 'HUALEI'); # 子串不存在,返回 0
select instr('HUALEI', 'LEI'); # 4
select instr('HUALEI', 'LEI '); # 0
复制代码
小结:
-
三个函数都能拿到子串在父串中的开始位置
-
locate()
和position()
函数基本类似,就是参数列表不同,后者使用in
表示子串在父串里面中的position
位置,写起来比较好理解,更推荐使用 -
instr()
和locate()
唯一不同的就是参数位置交换了,注意别搞混淆写反了
LOWER(str) | UPPER(str)
掌握指数:★★★
函数说明:
将
str
字符串全部小写(LOWER
)/大写(UPPER
)
SQL
语句示例:
# 小写化
select lower('HUALEI'); # hualei
# 大写话
select upper('hualei'); # HUALEI
复制代码
注意: 仅对英文字符串有效,中文字符串无效。
select lower('大家好,我是 HUALEI'); # 大家好,我是 hualei
复制代码
LTRIM(str) | RTRIM(str) | TRIM([remstr FROM] str)
掌握指数:★★★★
函数说明:
去除 str 字符串中的空格
SQL
语句示例:
select ltrim(' HUALEI'); # HUALEI
select rtim('大家好! '); # 大家好!
select trim(' HUALEI '); # HUALEI
复制代码
注意: trim()
函数只会去除 str
字符串前后的空格并不会去除所有空格!
select trim(' HUA LEI '); # HUA LEI
复制代码
REPEAT(str,count)
掌握指数:★★
函数说明:
返回
str
重复count
遍后的结果
SQL
语句示例:
select repeat('HUALEI ', 5); # HUALEI HUALEI HUALEI HUALEI HUALEI
select repeat('雷猴啊', 2); # 雷猴啊雷猴啊
复制代码
REVERSE(str)
掌握指数:★★
函数说明:
将字符串
str
按倒序反过来
SQL
语句示例:
select reverse('我被反过来了'); # 了来过反被我
select reverse('HUALEI'); # IELAUH
复制代码
RPAD(str,len,padstr) | LPAD(str,len,padstr)
掌握指数:★★★
函数说明:
指定
str
字符串长度len
,len > length(str)
不足用padstr
向右/左填充;len < length(str)
充足则根据指定长度进行截取。
SQL
语句示例:
select rpad('大家', 3, '好'); # 大家好
select rpad('HUALEI', length('HUALEI')+length(' NB'), ' NB'); # HUALEI NB
select length('大家好'); # 9
select lpad('SQL', 3, 'My'); # SQL
select lpad('SQL', length('MySQL'), 'My'); # MySQL
复制代码
★ 数学函数
FORMAT(X,D) | ROUND(X) | ROUND(X,D)
掌握指数:★★★★
函数说明:
对 X 四舍五入保留小数点后 D 位
SQL
语句示例:
select format(3.1415926,3); # 3.142
# 等价于
select ROUND(3.1415926, 3); # 3.142
# 无保留位数时,取整
select round(3.1415926); # 3
复制代码
CEIL(X) | FLOOR(X)
掌握指数:★★★★
函数说明:
ceil
天花板(向更大值方向)取整;floor
地板(向更小值方向)取整
SQL
语句示例:
# 向上取整
select ceil(3.5); # 4
select ceil(-3.5); # -3
# 向下取整
select floor(3.5); # 3
select floor(-3.5); # -4
复制代码
MOD(N,M)
掌握指数:★★★
函数说明:
取
N / M
的余数,等价于N % M
SQL
语句示例:
select mod(10, 3); # 1
# 等价于
select 10 % 3; # 1
复制代码
POW(X,Y) | POWER(X,Y)
掌握指数:★★★
函数说明:
返回 X 的 Y 次方
SQL
语句示例:
select pow(2, 10); # 2^10 = 1024
# 也可以写作
select power(2, 10); # 2^10 = 1024
复制代码
SQRT(X)
掌握指数:★★★
函数说明:
返回 X 的平方根,也就是对 X 开平方
SQL
语句示例:
select sqrt(100); # 10
select sqrt(2); # 根号 2 => 1.4142135623730951
复制代码
GREATEST(expr1, expr2, expr3, …) | LEAST(expr1, expr2, expr3, …)
掌握指数:★★★
函数说明:
返回参数列表中最大/最小值
注意: 参数列表可以是字符序列。
SQL
语句示例:
select greatest(1,2,3,4,51,6,7,8); # 51
select greatest('Java', 'MySQL', 'JavaScript'); # MySQL
select least(-1,2,3,4,5,6,7,8); # -1
select least('Java', 'MySQL', 'JavaScript'); # Java
复制代码
RAND()
掌握指数:★★★★★
函数说明:
返回 (0, 1) 之间的随机数
SQL
语句示例:
select rand(CURRENT_TIMESTAMP); # 给定种子值,这里给当前时间戳作为值,保证相对随机
select format(rand()*100, 0); # 产生 (0, 100) 之间的随机整数
select round(rand()*100); # 产生 (0, 100) 之间的随机整数
select ... order by rand() limit N; # 随机查询取 N 条记录
复制代码
聚合函数 MAX(expr) | MIN(expr) | SUM(expr) | COUNT(expr) | AVG([DISTINCT] expr)
掌握指数:★★★★★
函数说明:
聚合函数,配合
group by
使用,用来求最大、小值/求和/计数/求平均值
SQL
语句示例:
# 查询男同事里薪水最高的人
select max(salary) from emp where sex = '男';
# 查询女同事里薪水最少的人
select min(salary) from emp where sex = '女';
# 查询所有姓王的学生的个人总成绩
select sum(sscore) 姓王的学生的个人总成绩 from score where sid in (select sid from student stu where stu.sname like '王%') group by sid;
# 查询女同事的人数
select count(id) 女同事的人数 from emp group by sex having sex = '女';
# 查询平均薪水
select avg(salary) 平均薪水 from emp;
复制代码
注意:
-
除非另有说明,否则聚合函数会忽略
null
值 -
如果在不包含
group by
子句的语句中使用聚合函数,就等效于对所有行进行分组,结果总是有一行 -
时间类型的值对
sum()
和avg()
无效!它们会将其换成数字,丢弃第一个非数字字符后的所有信息
另外,聚合函数可以传入独立的表达式作为参数:
# 查询分数在 80 分及以上人数
select count(1) from score where sscore >= 80;
# 等价于
select count(if(sscore >= 80, 1, null)) from score;
# 查询分数在 80 分及以上总分
select sum(sscore) from score where sscore >= 80;
# 等价于
select sum(if(sscore>=80, sscore, null)) from score;
# 查询分数在 80 分及以上平均分
select avg(sscore) from score where sscore >= 80;
# 等价于
select avg(if(sscore>=80, sscore, null)) from score;
复制代码
★ 日期函数
CURDATE() <=> CURRENT_DATE
掌握指数:★★★★
函数说明:
返回当前日期,格式为
YYYY-MM-dd
SQL
语句示例:
select curdate(); # 2021-11-07
select current_date; # 2021-11-07
复制代码
CURTIME() <=> CURRENT_TIME
掌握指数:★★★★
函数说明:
返回当前时间,格式为
HH:mm:ss
SQL
语句示例:
select curtime(); # 10:31:23
select current_time; # 10:31:23
复制代码
NOW() <=> CURRENT_TIMESTAMP
掌握指数:★★★★
函数说明:
返回当前日期时间,格式为
YYYY-MM-dd HH:mm:ss
SQL
语句示例:
select now(); # 2021-11-07 10:31:46
select current_timestamp; # 2021-11-07 10:31:46
复制代码
DAY(date) | DAYOFWEEK(date) | DAYOFMONTH(date) | DAYOFYEAR(date)
掌握指数:★★★★
函数说明:
返回
date
中dd
/ 这一天是这一周/月/年中的第几天
SQL
语句示例:
# 取出 date 中的天
select day('2021-11-07'); # 7
# 一周中的第几天
select dayofweek(now()); # 星期天(Sunday) -> 返回 1
# 一个月中的第几天
select dayofmonth(now()); # 7
# 一年中的第几天
select dayofyear(now()); # 311
复制代码
WEEK(date[,mode]) | WEEKOFYEAR(date)
掌握指数:★★★
函数说明:
Mode | First day of week |
---|---|
0 | Sunday => 1 |
1 | Monday => 1 |
使用
mode
指定星期天是一周中的第一天还是星期一,然后根据这个标准判断date
是一年的第几周,返回结果 (0, 52) ;WEEKOFYEAR()
总是以星期一作为一周的开始,即mode
固定就是 1
SQL
语句示例:
# 默认,mode => 0,星期天(Sunday)=> 1
select week('2021-11-07'); # 45
select week('2021-11-07', 0); # 45
# 星期一(Monday)=> 1
select week('2021-11-07', 1); # 44
# 等价于
select weekofyear('2021-11-07'); # 44
复制代码
MONTH(date) | QUARTER(date)
掌握指数:★★★
函数说明:
返回
date
中的月份/所属季度
SQL
语句示例:
select month(now()); # 11
# 第一季度(1, 2, 3)第二季度(4, 5, 6)第三季度(7, 8, 9)第四季度(10, 11, 12)
select quarter(curdate()); # 4
复制代码
YEAR(date) | YEARWEEK(date,mode)
掌握指数:★★★★
函数说明:
返回
date
中的年份/年份+第几周
SQL
语句示例:
select year(curdate()); # 2021
# 默认 mode = 0,星期天(Sunday)=> 1
select yearweek('2021-11-07'); # 202145
# 等价于
select YEARWEEK('2021-11-07', 0); # 202145
# 星期一(Monday) => 1
select YEARWEEK('2021-11-07', 1); # 202144
复制代码
DAYNAME(date) | MONTHNAME(date)
掌握指数:★★
函数说明:
返回该天/月英文名
SQL
语句示例:
select dayname('2021-11-07'); # Sunday 星期天
select monthname('2021-11-07'); # November 11 月
复制代码
STR_TO_DATE(str,format) | DATE_FORMAT(date,format)
掌握指数:★★★★★
函数说明:
根据
date
字符串的格式,转换成日期,相反地,可以将date
转换成指定格式的字符串
SQL
语句示例:
# 字符串转 date 类型
select str_to_date('2021年11月07日', '%Y年%m月%d日'); # 2021-11-07
select str_to_date('2021年11月07日 12点28分34秒', '%Y年%m月%d日 %H点%i分%s秒'); # 2021-11-07 12:28:34
# date 转指定格式的字符串
select date_format(now(), '%Y年%m月%d日 %H点%i分%s秒'); # 2021年11月07日 11点29分56秒
复制代码
DATEDIFF(expr1,expr2)
掌握指数:★★★★★
函数说明:
返回两个
date
相隔的天数
SQL
语句示例:
select concat(datediff(curdate(), '2021-01-01'), '天') as '距离开年以来已经过了'; # 312天
select concat(datediff(str_to_date(concat(year(now()), '/12/31'), '%Y/%m/%d'), now()), '天') as '距离年末仅剩'; # 52天
复制代码
DATE_ADD(date,INTERVAL expr unit) | DATE_SUB(date,INTERVAL expr unit)
掌握指数:★★★★★
函数说明:
对
date
做加减法
SQL
语句示例:
select ceil(rand()*31); # (0, 31]
# 将当前时间戳加上 (0, 31] 区间中的随机 DAY 天数
select date_add(CURRENT_TIMESTAMP, interval ( ceil(rand()*31) ) DAY );
select ceil(rand()*4); # [1, 4]
# 将当前时间戳加上 [1, 4] 区间中的随机 WEEK 周数
select date_add(now(), interval ( ceil(rand()*4) ) WEEK );
# 相反地,对指定 date 做减法,将当前时间戳减去 [0, 10] 区间中的随机 YEAR 年数
select date_sub(CURRENT_TIMESTAMP, interval ( round(rand(CURRENT_TIME)*11) ) YEAR );
复制代码
TO_DAYS(date) | FROM_DAYS(N)
掌握指数:★★
函数说明:
给定一个
date
,返回从公元 0 年到date
的天数
小知识:历史并不存在公元 0 年,但 0 年是公元位数对齐的基础
SQL
语句示例:
select to_days('2021-11-07'); # 738466
# 并不完全等价,to_days() 包含当天,而 datediff() 不然
select datediff('2021-11-07','0-01-01'); # 738465
# to_days() 的逆运用,给定一个从公元 0 年开始的天数,返回一个 date
select from_days(737515); # 2019-4-1
select from_days(to_days('2021-11-07')); # 2021-11-07
复制代码
☛ 流程控制函数
IF(expr1,expr2,expr3)
掌握指数:★★★★
函数说明:
判断 expr1 表达式真假,真返回 expr2,否则返回 expr3
SQL
语句示例:
select if(10 > 5, '10 更大', '5 更大'); # 10 更大
复制代码
IFNULL(expr1,expr2) | NULLIF(expr1,expr2)
掌握指数:★★★
函数说明:
IFNULL
用来判断expr1
是否为null
,如果不是则返回expr2
,否则返回expr1
;NULLIF
则是用来判断expr1、2
是否相等,相等则返回null
,否则返回expr1
SQL
语句示例:
# expr1 is not null.return expr1
select ifnull('exp1 is not null', null); # exp1 is not null
# expr1 is null.return expr2
select ifnull(null, 'exp1 is null'); # exp1 is null
select nullif('HUALEI', 'hualei'); # 相等返回 null
# 等价于(不区分大小写)
select nullif('HUALEI', 'HUALEI'); # 相等返回 null
# expr1 != expr2
select nullif('HUALEI', null); # HUALEI
复制代码
注意: expr1 != null
,否则返回 null
。
select nullif(null, 'HUALEI'); # null
复制代码
IF ... ELSE 语句
掌握指数:★★★★
函数说明:
写法不同于
IF()
函数
语法:
IF search_condition THEN
statement_list
ELSE
statement_list
END IF;
复制代码
SWITCH ... CASE 语句
掌握指数:★★★★
函数说明:
开关语句
语法:
CASE case_value
WHEN when_value THEN
statement_list
ELSE
statement_list
END;
复制代码
☛ 消息摘要函数
PASSWORD(str)
掌握指数:★★★
函数说明:
计算并返回密码字符串
SQL
语句示例:
select PASSWORD('abc') # *0D3CED9BEC10A777AEC23CCC353A8C08A633045E
select PASSWORD('ABC') # *71B101096C51D03995285042443F5C44D59C8A31
复制代码
注意: 该函数在 MySQL8.0.11
版本中被移除了。
MD5(str)
掌握指数:★★★★
函数说明:
计算
MD5
总和校验码
SQL
语句示例:
select MD5("abc"); # 900150983cd24fb0d6963f7d28e17f72
复制代码
SHA(str) | SHA1(str)
掌握指数:★★★★
函数说明:
计算
SHA
/SHA1
总和校验码
SQL
语句示例:
select SHA('abc') # a9993e364706816aba3e25717850c26c9cd0d89d
# 等价于
select SHA1('abc') # a9993e364706816aba3e25717850c26c9cd0d89d
复制代码
☛ 对称加密函数
ENCODE(str,pass_str) | DECODE(crypt_str,pass_str)
掌握指数:★★★★
函数说明:
通过公共密钥加密(
Encode
编码)/解密(Decode
解码)
SQL
语句示例:
# 'password' 作为公钥加密字符串信息 'HUALEI'
select encode('HUALEI', 'password'); # ���e
# 将加密后的密文通过公钥 'password' 进行解密,从而得到加密前的明文
select decode(encode('HUALEI', 'password'), 'password'); # HUALEI
复制代码
AES_ENCRYPT(str,key_str) | AES_DECRYPT(crypt_str,key_str) | DES_ENCRYPT(str[,{key_num|key_str}]) | DES_DECRYPT(crypt_str[,key_str])
掌握指数:★★★★
函数说明:
通过额
AES
(Advanced Encryption Standard 高级加密标准,作为DES
算法的替代品) /DES
(Data Encryption Standard 数据加密标准) 算法对称加密信息
SQL
语句示例:
# AES 算法加密,公钥为 'salt'
select aes_encrypt('HUALEI', 'salt'); # �ɫD�b*�ճ�ϐe�
# AES 算法解密
select aes_decrypt(aes_encrypt('HUALEI', 'salt'), 'salt'); # abc
# DES 算法加密,公钥为 'password'
select des_encrypt('HUALEI', 'password'); # ��o�#�
# DES 算法解密
select des_decrypt(des_encrypt('HUALEI', 'password'), 'password'); # HUALEI
复制代码
☛ 系统信息函数
VERSION()
掌握指数:★★★★
函数说明:
返回当前 MySQL 版本号
SQL
语句示例:
select version(); # 5.7.31-log
复制代码
USER() | CURRENT_USER
掌握指数:★★★★
函数说明:
返回当前用户角色
SQL
语句示例:
select user(); # root@localhost
# 等价于
select current_user; # root@localhost
复制代码
DATABASE()
掌握指数:★★★★
函数说明:
返回当前所在数据库名
SQL
语句示例:
select database(); # mysql
复制代码
CONNECTION_ID()
掌握指数:★★★
函数说明:
返回当前用户连接数
SQL
语句示例:
select connect_id(); # 38
复制代码
函数全概述
以上全部函数,我将它们归纳成一张思维导图,非常直观,供大家学习参考:
结尾
撰文不易,欢迎大家点赞、评论,你的关注、点赞是我坚持的不懈动力,感谢大家能够看到这里!Peace & Love。
近期评论