MySQL数据库函数使用详解!分析说明MySQL数据库各个函数的具体使用方式

172 阅读14分钟

这是我参与2022首次更文挑战的第27天,活动详情查看:2022首次更文挑战

基本概念

  • MySQL数据库中包含以下七类函数:
    • 字符串函数
    • 条件函数
    • 数学函数
    • 日期时间函数
    • 系统信息函数
    • 加密函数
    • 扩展函数

字符串函数

  • 字符串函数用于处理MySQL中的字符串数据

char_length()

  • 返回字符串中字符的个数
select char_length('Chova')
5

length()

  • 返回字符串的长度
select length('我是Chova')
-- 中文字符长度为2,英文字符长度为1 --
9

concat()

  • 将多个字符串合并为一个字符串
select concat('Chova','Vea')
ChovaVea

concat_ws()

  • 将多个字符串合并为一个字符串,并以指定的字符串连接
select concat_ws('-','Chova','Vea')
Chova-Vea

insert()

  • 将字符串的指定位置开始的指定长度的位置替换为给定的字符串
select insert('chova',1,1,'C')
Chova

upper()

  • 将字符串中的所有字符变为大写
select upper('Chova')
CHOVA

lower()

  • 将字符串中的所有字符变为小写字母
select lower('Chova')
chova

left()

  • 获取字符串前面的指定个数的字符
select left('Chova',1)
C

right()

  • 获取字符串后面的指定个数的字符
select right('Chova',2)
va

lpad()

  • 使得字符串达到指定的长度在字符串前面填充进指定的字符串
select lpad('Vea',8,'Chova')
ChovaVea

rpad()

  • 使得字符串达到指定的长度在字符串的后面填充进指定的字符串
select rpad('Chova',8,'Vea')
ChovaVea

ltrim()

  • 去掉字符串前面的空格

rtrim()

  • 去掉字符串后面的空格

trim()

  • 去掉字符串前面和后面的空格

trim(from)

  • 去掉前面和后面的指定字符串从指定的字符串中
select trim('Vea' from 'ChovaVea')
Chova

repeat()

  • 将指定的字符串重复指定的次数
select repeat('Vea',3)
VeaVeaVea

space()

  • 获取指定个数的空格

replace()

  • 替换指定字符串中的指定字符串为指定的字符串
select replace('ChovaV','V','Vea')
ChovaVea

strcmp()

  • 比较两个字符串

substring()

  • 获取指定字符串中指定位置开始的指定长度的字符串

mid()

  • 获取指定字符串中指定位置开始的指定长度的字符串

locate()

  • 获取指定字符串在指定字符串中的位置
select locate('V','ChovaVea')
6

position(in)

  • 获取指定字符串在指定字符串中的位置
select position('V' in 'ChovaVea')
6

instr()

  • 获取指定字符串中的指定字符串开始的位置
select instr('Chova','v')
4

reverse()

  • 获取指定字符串的反转字符串
select reverse('aev')
vea

elt()

  • 获取指定位置的字符串
select elt(2,'Chova','Vea')
Vea

export_set()

  • export_set(bits, on, off, bits, on, off, separator, number of bits, number of bits): 返回一个指定处理方式的字符串
    • bits: 将第一个参数的值转换为二进制的值.然后从右向左检查每个位上的值是1还是0
    • on: 如果检查位数是1, 则返回这里on指定的字符串
    • off: 如果检查位数是0, 则返回这里off指定的字符串
    • separator: 指定返回的字符串的分隔符
    • number of bits: 检查位数的长度.即第一个参数转换为二进制的长度,超过位数在前面用0补全
select export_set(5,'Y','N',','6)
-- 5的二进制: 101 --
-- 检查的位数为6, 二进制数表示为: 000101--
'Y','N','Y','N','N','N'

field()

  • 返回指定的字符串在后面的字符串中匹配的位置
select field('Vea','Chova','Vea')
2

find_in_set()

  • find_in_set('',''): 返回指定字符串在指定字符串中匹配的位置

make_set()

  • MAKE_SET(bits,str1,str2,...): 返回一个指定处理方式获取的字符串
    • bits: 将第一个参数的值转换为二进制,可能包含逻辑运算.然后从右向左检查每个位上的值是1还是0
    • str: 每检查一个位后则顺延到下一的字符串
select make_set(1|4,'Chova','Vea')
Chova

substring_index()

  • substring_index('s','x',count): 返回从指定字符串中按照指定分隔符分隔的指定位置分隔的字符串
    • count大于0, 返回指定位置分隔符左边的字符串
    • count小于0, 返回指定位置分隔符右边的字符串
select substring_index('Chova-Vea','-', 1)
Chova

load_file()

  • 读入文件并以字符串的形式返回文件内容
    • 文件必须在服务器上
    • 必须指定到文件的完整路径名
    • 人员必须拥有文件file权限
    • 文件必须所有内容都是可读取的并且小于max_allowed_packet
  • 如果文件不存在或者由于上述原因无法读出则返回null

条件函数

  • 条件函数用于MySQL中的条件判断

if(expr,v1,v2)

  • 如果expr条件成立,返回结果v1
  • 如果expr条件不成立,返回结果v2
select if(1>0,'Y','N')
Y

ifnull(v1,v2)

  • 如果v1的值不为null, 则返回v1
  • 如果v1的值为null, 则返回v2
select ifnull(null,'Chova')
Chova

case

  • case表示函数的开始 ,end表示函数的结束
    • 如果e1成立,则返回v1
    • 如果e2成立,则返回v2
    • 如果全部都不成立,则返回v
  • 当有一个成立之后,后面的语句就不再执行
case 
	when e1 then v1
	when e2 then v2
	...
	else v
end
  • case表示函数的开始 ,end表示函数的结束
    • 如果表达式的值等于e1, 则返回v1
    • 如果表达式的值等于e2, 则返回v2
  • 当有一个成立之后,后面的语句就不再执行
case expr
	when e1 then v1
	when e2 then v2
	...
	else v
end

数学函数

  • 数学函数主要用来处理MySQL中的数值处理,包括整数和浮点数等

abs()

  • 获取数值的绝对值
select abs(-6)
6

ceil()

  • 获取数值的向上取整
select ceil(5.6)
6

floor()

  • 获取数值的向下取证
select floor(6.6)
6

rand()

  • 获取一个01之间的随机数
  • rand(x): 获取一个01之间的随机数,同一个模块下 ,x值相同时获取到的随机数相同
select rand()
0.93099315644334
select rand(6)

select rand(6)
0.713759689954247

0.713759689954247

sign()

  • 获取数值的符号
    • 如果数值是负数,则返回**-1**
    • 如果数值是0, 则返回0
    • 如果数值是正数,则返回1
select sign(6)
1

pi()

  • 获取一个圆周率的值
select pi()
3.141593

truncate()

  • 获取指定数值保留小数点后面的指定位数.直接获取,不进行四舍五入操作
select truncate(3.141593,2)
3.14

round()

  • 获取数值的整数.按照四舍五入进行获取
select round(6.36)
6
  • round(x,n): 获取指定数值保留小数点后面的指定位数.按照四舍五入进行获取
select round(6.686, 2)
6.69

pow()

  • 获取指定数值的指定幂次方的值
select pow(2,3)
8

sqrt()

  • 获取指定数值的平方根
select sqrt(36)
6

exp()

  • 获取e值的指定幂次方的值
select exp(3)
20.085536923188

mod()

  • 获取指定的两个数相除后的余数
select mod(6,5)
1

log()

  • 获取指定数值的自然对数,即以e为底
select log(20.085536923188)
3

log10()

  • 获取指定数值以10为底的对数
select log10(100)
2

radians()

  • 获取指定角度的弧度
select radians(180)
3.1415926535898

degrees()

  • 获取指定弧度的角度
select degrees(3.1415926535898)
180

sin()

  • 获取指定弧度的正弦值
select sin(radians(30))
0.5

asin()

  • 获取指定弧度的反正弦值

cos()

  • 获取指定弧度的余弦值

acos()

  • 获取指定弧度的反余弦值

tan()

  • 获取指定弧度的正切值

atan()

  • 获取指定弧度的反正切值

cot()

  • 获取指定弧度的余切值

日期时间函数

  • 日期时间函数主要用于处理MySQL中的日期时间

curdate()

  • 获取当前的日期
select curdate()
2021-04-02

curtime()

  • 获取当前的时间
select curtime()
16:16:16

now()

  • 获取当前的日期和时间
select now()
2021-04-02 16:16:16

unix_timestamp()

  • 获取当前时间的时间戳格式
select unix_timestamp()
1617351376
  • unix_timestamp(d): 获取指定日期时间的时间戳格式
select unix_timestamp('2021-04-02 16:16:16')
1617351376

from_unixtime()

  • 获取指定时间戳的日期时间格式
select from_unixtime(1617351376)
2021-04-02 16:16:16

utc_date()

  • 获取UTC日期
select utc_date()
2021-04-02

utc_time()

  • 获取UTC时间
select utc_time()
08:16:16

month()

  • 获取指定日期时间所属的月份,值在112之间
select month('2021-04-02 16:16:16')
4

monthname()

  • 获取指定日期时间所属的月份名称
select monthname('2021-04-02 16:16:16')
April

dayname()

  • 获取指定日期时间所属的星期的名称
select dayname('2021-04-02 16:16:16')
Friday

dayofweek()

  • 获取指定日期时间是一周中的第几天 .17表示星期日到星期六
select dayofweek('2021-04-02 16:16:16')
6

weekday()

  • 获取指定日期时间是一周中的第几天 .06表示星期一到星期日
select weekday('2021-04-02 16:16:16')
4

week()

  • 获取指定日期时间是当年的第几个星期.值在053之间
select week('2021-04-02 16:16:16')
13

dayofyear()

  • 获取指定日期时间是当年的第几天
select dayofyear('2021-04-02 16:16:16')
92

dayofmonth()

  • 获取指定日期时间是当月的第几天
select dayofmonth('2021-04-02 16:16:16')
2

quarter()

  • 获取指定日期时间是当年的第几个季度
select quarter('2021-04-02 16:16:16')
2

hour()

  • 获取指定日期或时间中的小时值
select hour('16:16:16')
16

minute()

  • 获取指定日期或时间中的分钟值
select minute('16:16:16')
16

second()

  • 获取指定日期或之间中的秒钟值
select second('16:16:16')
16

extract()

  • extract(type from d): 获取指定类型在指定日期时间中的值
    • type的值:
      • microsecond
      • second
      • minute
      • hour
      • day
      • week
      • month
      • quarter
      • year
      • second_microsecond
      • minute_microsecond
      • minute_second
      • hour_microsecond
      • hour_second
      • hour_minute
      • day_microsecond
      • day_second
      • day_minute
      • day_hour
      • year_month

time_to_sec()

  • 获取指定时间的秒值
select time_to_sec('16:16:16')
58576

sec_to_time()

  • 获取指定的秒值转换的时间的值
select sec_to_time(58576)
16:16:16

to_days()

  • 获取距离00000101日的天数
select to_days('2021-04-02 16:16:16')
738247

from_days()

  • 获取距离00000101日后指定天数的日期
select from_days(738247)
2021-04-02

datediff()

  • 获取指定的两个日期之间相隔的天数. 前面的日期减去后面日期的天数
select datediff('2021-05-02', '2021-04-02')
30

adddate()

  • 获取指定日期时间之后指定天数的日期时间
select adddate('2021-04-02', 30)
2021-05-02
  • adddate(d, interval expr type): 获取指定日期时间之后指定时间后的日期时间
    • type的值:
      • microsecond
      • second
      • minute
      • hour
      • day
      • week
      • month
      • quarter
      • year
      • second_microsecond
      • minute_microsecond
      • minute_second
      • hour_microsecond
      • hour_second
      • hour_minute
      • day_microsecond
      • day_second
      • day_minute
      • day_hour
      • year_month

date_add()

  • date_add(d, interval expr type): 获取指定日期时间之后指定时间的日期时间
    • type的值:
      • microsecond
      • second
      • minute
      • hour
      • day
      • week
      • month
      • quarter
      • year
      • second_microsecond
      • minute_microsecond
      • minute_second
      • hour_microsecond
      • hour_second
      • hour_minute
      • day_microsecond
      • day_second
      • day_minute
      • day_hour
      • year_month

subdate()

  • 获取指定日期时间之前指定天数的时间
select subdate('2021-05-02',30)
2021-04-02
  • subdate(d, interval expr type): 获取指定日期时间之前指定时间的日期时间
    • type的值:
      • microsecond
      • second
      • minute
      • hour
      • day
      • week
      • month
      • quarter
      • year
      • second_microsecond
      • minute_microsecond
      • minute_second
      • hour_microsecond
      • hour_second
      • hour_minute
      • day_microsecond
      • day_second
      • day_minute
      • day_hour
      • year_month

addtime()

  • 获取指定日期时间之后指定秒数的日期时间
select addtime('2021-04-02 16:16:16',20)
2021-04-02 16:16:36

subtime()

  • 获取指定日期时间之前指定秒数的日期时间
select subtime('2021-04-02 16:16:16',10)
2021-04-02 16:16:06

date_format()

  • 获取指定日期时间的指定格式输出的日期时间
select date_format('2021-04-02 16:16:16','%Y-%m-%d %r')
2021-04-02 04:16:16 PM

time_format()

  • 获取指定时间的指定格式输出的时间
select time_format('06:16:16','%r')
06:16:16 AM

get_format()

  • get_format(type,s): 获取指定时间在指定国家地区的输出格式
    • type的值:
      • microsecond
      • second
      • minute
      • hour
      • day
      • week
      • month
      • quarter
      • year
      • second_microsecond
      • minute_microsecond
      • minute_second
      • hour_microsecond
      • hour_second
      • hour_minute
      • day_microsecond
      • day_second
      • day_minute
      • day_hour
      • year_month
select get_format(date, 'usa')
%m.%d.%Y

系统信息函数

  • 系统信息函数用来查询MySQL数据库相关的系统信息

version()

  • 获取数据库的版本号

connection_id()

  • 返回服务器的连接数

database()

  • database()
  • schema
    • 获取当前的数据库名称

user()

  • user()
  • system_user()
  • session_user()
  • current_user()
  • current_user
    • 获取数据库的当前用户

charset()

  • 获取指定字符串的字符集

collation()

  • 获取指定字符串的字符排列方式

last_insert_id()

  • 获取最新生成的AUTO_INCREMENT的值

加密函数

  • 加密函数用来对MySQL中的数据进行加密

password()

  • 获取指定字符串的加密后的值
select password('Chova')
*8C4103E52796CCF4A350218583E396B47CB15311

md5

  • md5(str): 可以对字符串进行散列,可以用于对一些不需要进行解密的数据进行加密
select md5('Chova')
8c94d8f085f29ee2785e796e60e13c07

扩展函数

格式化函数-fomat(x,n)

  • 获取指定数字的小数点后面的指定位数
select format(3.141592643,5)
3.14159

不同进制的数字之间进行转换的函数

  • ASCII(s): 返回字符串第一个字符的ASCII码值
  • BIN(x): 返回x的二进制编码
  • HEX(x): 返回x的十六进制编码
  • OCT(x): 返回x的八进制编码
  • CONV(x,f1,f2): 返回f1进制数变为f2进制数

IP地址与数字相互转换函数

  • inet_aton(ip): 获取指定IP字符串的数值表示
select inet_aton('192.168.0.1')
3232235521
  • inet_ntoa(n): 获取指定数值的IP字符串表示
select inet_ntoa(3232235521)
192.168.0.1

加锁函数和解锁函数

  • get_lock(name,time): 定义一个名称为name, 持续时间长度为time的锁
    • 如果锁定成功,则返回1
    • 如果尝试超时,则返回0
    • 如果发生错误,则返回null
select get_lock('mysql',10)
1
  • release_lock(name): 释放函数名为name的锁
    • 如果释放成功,则返回1
    • 如果尝试超时,则返回0
    • 如果发生错误,则返回null
select release_lock('mysql')
1
  • is_free_lock(name): 判断是否已经使用过名称为name的锁
    • 如果已经使用过,则返回1
    • 如果未使用过,则返回0
select is_free_lock('mysql')
0

重复执行指定操作的函数

  • benchmark(count,expr): 将指定的表达式expr操作重复执行count次,然后返回执行时间. 可以用来判断MySQL处理表达式的速度
select benchmark(10000,now())
0

修改指定的字符集函数

  • convert(s using charset): 将指定字符串s的字符集变为charset字符
select charset(convert('Chova' using utf-8))
utf-8

转换数据类型函数

  • cast(x as type)
  • convert(x as type)
    • 转换数据类型只对以下数据类型生效:
      • binary
      • char
      • date
      • datetime
      • time
      • signed integer
      • unsigned integer
select cast('565' as unsigned integer) + 1
566
-- MySQL可以默认将字符串类型转换为数字类型 --
select '565'+1
566
select convert(now(),date)
2021-04-02