MySQL --- 函数大全2

目录

1.返回第一个非 NULL 参数   COALESCE()

2.返回字符串参数的排序规则强制值   COERCIBILITY()

3.返回字符串参数的排序规则   COLLATION()

4.以二进制字符串形式返回结果   COMPRESS()

5.返回串联字符串   CONCAT()

6.返回与分隔符串联    CONCAT_WS()

7.返回连接的连接 ID（线程 ID)   CONNECTION_ID()

8.在不同数字基数之间转换数字   CONV()

9.将值强制转换为特定类型   CONVERT()

10.从一个时区转换为另一个时区  CONVERT_TZ()

11.返回余弦   COS()

12.返回余切值  COT()

13.返回返回的行数的计数  COUNT()

14.返回多个不同值的计数   COUNT(DISTINCT)

15.计算循环冗余校验值   CRC32()

16.累计分布值   CUME_DIST()

17.返回当前日期   CURDATE() / CURRENE_DATE() / CURRENT_DATE

18.返回当前活动角色    CURRENT_ROLE()

19.经过身份验证的用户名和主机名   CURRENT_USER() / CURRENT_USER

20.返回当前时间   CURTIME() / CURRENT_TIME() / CURRENT_TIME

21.返回默认（当前）数据库名称   DATABASE()

22.提取日期或日期时间表达式的日期部分   DATE()

23.将时间值（间隔）添加到日期值   DATE_ADD()

24.设置指定日期的格式   DATE_FORMAT()

25.从日期中减去时间值（间隔）DATE_SUB()

26.减去两个日期   DATEDIFF()

27.返回工作日的名称   DAYNAME()

28.返回月份中的某天 （0-31） DAY() / DAYOFMONTH()

29.返回参数的工作日索引   DAYOFWEEK()

30.返回一年中的某一天 （1-366） DAYOFYEAR()

31.返回日期格式字符串 GET_FROMAT()

32.将弧度转换为度   DEGREES()

33.分区内当前行的排名，无间隙   DENSE_RANK()

34.整数除法   DIV()

35.返回索引号处的字符串   ELT()

36.e的多少次方   EXP()

37.返回一个字符串，该字符串表示数字中的位   EXPORT_SET()

38.提取日期的一部分   EXTRACT()

39.使用 XPath 表示法从 XML 字符串中提取值   EXTRACTVALUE()

40.第一个参数在后续参数中的索引（位置)   FIELD()

41.第二个参数中第一个参数的索引（位置）  FIND_IN_SET()

42.窗口框架第一行的参数值   FIRST_VALUE()

43.返回不大于参数的最大整数值   FLOOR()

44.返回格式为指定小数位数的数字   FORMAT()

45.将字节计数转换为带单位的值   FORMAT_BYTES()

46.以皮秒为单位的时间转换为带单位的值   FORMAT_PICO_TIME()

47.返回最大的参数   GREATEST()

48.解码 base64 编码字符串并返回结果   FROM_BASE64()

49.将日期数字转换为日期   FROM_DAYS()

50.将 Unix 时间戳格式化为日期   FROM_UNIXTIME()

---

1.返回第一个非 NULL 参数   COALESCE()

脚本

select coalesce(null,null,2),coalesce(null,null,null);

分析

如果全是null，则会返回null值

结果

2.返回字符串参数的排序规则强制值   COERCIBILITY()

脚本

select coercibility(user()),coercibility('abc'),coercibility(123);

分析

返回值具有如下所示的含义 桌子。值越低，优先级越高。

胁迫性	意义	例
0	显式排序规则	带子句的值COLLATE
1	无排序规则	具有不同排序规则的字符串的串联
2	隐式排序规则	列值、存储的例程参数或局部变量
3	系统常量	用户（）返回值
4	胁迫	文本字符串
5	数值的	数值或时态值
6	忽略	NULL或派生自NULL

结果

3.返回字符串参数的排序规则   COLLATION()

脚本

select collation('abc'),collation(_latin1'abc'),collation(_utf8mb4'abc');

分析

如果字符串前面不加排序规则默认的是utf8mb4_0900_ai_ci

结果

4.以二进制字符串形式返回结果   COMPRESS()

脚本

select compress('123'),length(compress('123'));

分析

COMPRESS()函数将非空字符串存储为未压缩字符串的four-byte长度，然后是压缩字符串。如果字符串以空格结尾，则将“.”字符添加到字符串。另外，应注意，空字符串存储为空字符串。 COMPRESS()函数接受一个参数，该参数是要压缩的字符串。

要想在字符串上实现COMPRESS函数，并在压缩后返回字符串的长度，则要加上length（）函数

结果

5.返回串联字符串   CONCAT()

脚本

select concat('hello',' MySQL',' 123456');

分析

把几个字符串连接起来

结果

6.返回与分隔符串联    CONCAT_WS()

脚本

SELECT CONCAT_WS(',','First name','Second name','Last Name');

分析

CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。

CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。

结果

7.返回连接的连接 ID（线程 ID)   CONNECTION_ID()

脚本

select connection_id();

分析

返回连接的连接 ID（线程 ID）。 每个连接都有一个 ID 在一组 当前连接的客户端。

结果

8.在不同数字基数之间转换数字   CONV()

脚本

SELECT CONV('a',16,2),conv('6E',18,6);

分析

CONV()函数的目的是在不同的数值基数之间进行数字转换。该函数返回值N从from_base到to_base转换的字符串。 最小基数为2，最大值为36。如果任何参数为NULL，则函数返回NULL。

a将从基数16向基数2转换

另一同理

结果

9.将值强制转换为特定类型   CONVERT()

脚本

select convert('150', CHAR), convert('test', CHAR CHARACTER SET utf8mb4), convert('150636', time);

分析

参数	描述
value	必需。要转换的值
type	必需。要转换为的数据类型。 可以是以下之一： 值 描述 DATE 将 value 转换为 DATE。 格式： "YYYY-MM-DD" DATETIME 将 value 转换为 DATETIME.Format： "YYYY-MM-DD HH:MM:SS" DECIMAL 将 value 转换为 DECIMAL。 使用可选的 M 和 D 参数指定最大位数 (M) 和小数点后的位数 (D)。 TIME 将 值 转换为 TIME。 格式： "HH:MM:SS" CHAR 将 value 转换为 CHAR（固定长度字符串） NCHAR 将 value 转换为 NCHAR（类似于 CHAR，但生成带有地区字符集的字符串） SIGNED 将 value 转换为 SIGNED（带符号的 64 位整数） UNSIGNED 将 value 转换为 UNSIGNED（无符号 64 位整数） BINARY 将 value 转换为 BINARY（二进制字符串）
charset	必需。要转换成的字符集

官方讲解连接  ​​​​​​CONVERT

结果

10.从一个时区转换为另一个时区  CONVERT_TZ()

脚本

select convert_tz('2022-12-11 12:00:00','+00:00','+10:00');

分析

转换datetime值dt，从 from_tz 由给定转到 to_tz 时区给出的时区，并返回的结果值。 如果参数无效该函数返回NULL

结果

11.返回余弦   COS()

脚本

select cos(pi()),cos(0);

结果

12.返回余切值  COT()

脚本

select cot(pi()),cot(1);

结果

13.返回返回的行数的计数  COUNT()

脚本

create table a
(
    id    int auto_increment primary key,
    score int
);

insert into a (score)
values (90),
       (85),
       (80),
       (70);


select count(*) from a;

结果

14.返回多个不同值的计数   COUNT(DISTINCT)

脚本

create table a
(
    id    int auto_increment primary key,
    score int
);

insert into a (score)
values (90),
       (85),
       (80),
       (70);

insert into a (score) value (90);

select distinct count(distinct score) from a;

分析

count（）里面加上distinct 会把重复的去掉，a里面有两个90,重复的去掉所以只有四个

结果

15.计算循环冗余校验值   CRC32()

脚本

select crc32('abc'),crc32('ABC'),crc32(123),crc32('123'),crc32(null);

分析

在 MySQL 中，该CRC32()函数计算循环冗余校验值并返回 32 位无符号值。

CRC 代表 循环冗余校验。CRC 是一种错误检测代码，通常用于数字网络和存储设备中，用于检测对原始数据的意外更改（尽管它不一定能防止恶意或故意更改）

expr字符串在哪里。如果参数不是一个字符串，MySQL 无论如何都会把它当作一个字符串来处理（要么这样，要么你会得到一个错误）。如果参数是NULL，则NULL返回。

区分大小写

数字与字符不区分

null值

结果

16.累计分布值   CUME_DIST()

脚本

CREATE TABLE scores (
    name VARCHAR(20) PRIMARY KEY,
    score INT NOT NULL
);

INSERT INTO
    scores(name, score)
VALUES
    ('Smith',81),
    ('Jones',55),
    ('Williams',55),
    ('Taylor',62),
    ('Brown',62),
    ('Davies',84),
    ('Evans',87),
    ('Wilson',72),
    ('Thomas',72),
    ('Johnson',100);


SELECT
    name,
    score,
    CUME_DIST() OVER (ORDER BY score) cume_dist_val
FROM
    scores;

分析

CUME_DIST()函数如何执行计算呢？

对于第一行，该函数查找结果集中的行数，其值小于或等于55。
结果为2。
然后CUME_DIST()函数将2除以总行数，即10：2 / 10。
结果是0.2或20％。
相同的逻辑应用于第二行。

对于第三行，该函数查找值小于或等于62的行数。
共有四行。
然后，CUME_DIST()函数的结果为：4/10 = 0.4，即40％。

相同的计算逻辑将应用于其余行。

结果

17.返回当前日期   CURDATE() / CURRENE_DATE() / CURRENT_DATE

脚本

select curdate();

结果

18.返回当前活动角色    CURRENT_ROLE()

脚本

SELECT CURRENT_ROLE();

分析

这里， 表示当前会话中并没有任何角色。NONE

结果

19.经过身份验证的用户名和主机名   CURRENT_USER() / CURRENT_USER

脚本

select current_user,current_user();

结果

20.返回当前时间   CURTIME() / CURRENT_TIME() / CURRENT_TIME

脚本

select CURTIME(), CURRENT_TIME(), CURRENT_TIME;

结果

21.返回默认（当前）数据库名称   DATABASE()

脚本

select database();

结果

22.提取日期或日期时间表达式的日期部分   DATE()

脚本

select date('2022-12-11 15:50:36');

结果

23.将时间值（间隔）添加到日期值   DATE_ADD()

脚本

select date_add('2022-12-11 15:50:36', interval 1 day),date_add('2022-12-11 15:50:36', interval 1 year);

分析

添加需要用interval

结果

24.设置指定日期的格式   DATE_FORMAT()

脚本

SELECT DATE_FORMAT('2022-12-11 15:54:00', '%W %M %Y'),
       DATE_FORMAT('1900-10-04 22:23:00',
                   '%D %y %a %d %m %b %j');

分析

规范	描述
%a	工作日名称的缩写 (..SunSat)
%b	缩写月份名称 （.JanDec)
%c	月，数字 （.012)
%D	带英语后缀的月份中的某天 （， ， ， ， ...）0th1st2nd3rd
%d	月中的某天，数字 （..0031)
%e	月中的某天，数字 （..031)
%f	微秒 （.000000999999)
%H	小时 （.0023)
%h	小时 （.0112)
%I	小时 （.0112)
%i	分钟，数字 （.0059)
%j	一年中的某一天（.001366)
%k	小时 （.023)
%l	小时 （.112)
%M	月份名称 （.JanuaryDecember)
%m	月，数字 （.0012)
%p	AM或PM
%r	时间，12 小时（hh：mm：ss，后跟或AMPM)
%S	秒 （.0059)
%s	秒 （.0059)
%T	时间，24 小时 （hh：mm：ss)
%U	周 （..），其中星期日是 一周的第一天;周（）模式 00053
%u	周 （..），其中星期一是 一周的第一天;周（）模式 10053
%V	周 （..），其中星期日是 一周的第一天;周（）模式 2;与0153%X
%v	周 （..），其中星期一是 一周的第一天;周（） 模式 3;与0153%x
%W	工作日名称 （.SundaySaturday)
%w	星期几 （=星期日。=星期六）06
%X	星期天是一周第一天的一周的年份，数字， 四位数;与%V
%x	一周的年份，其中星期一是一周的第一天，数字， 四位数;与%v
%Y	年份，数字，四位数字
%y	年份，数字（两位数）
%%	文字字符%
%x	x，对于未列出的任何“x” 以上

结果

25.从日期中减去时间值（间隔）DATE_SUB()

脚本

select date_sub('2022-12-11 15:50:36', interval 1 day), date_sub('2022-12-11 15:50:36', interval 1 year);

分析

与 DATE_ADD（） 相反

结果

26.减去两个日期   DATEDIFF()

脚本

select datediff('2023-12-11 15:50:36',now())

分析

前面的减后面的

结果

27.返回工作日的名称   DAYNAME()

脚本

select dayname('2022-12-11'),dayname('2022-12-12');

结果

28.返回月份中的某天 （0-31） DAY() / DAYOFMONTH()

脚本

select day('2022-12-11'),dayofmonth('2022-12-32');

分析

如果日期超出月份的最大天数则会返回null

结果

29.返回参数的工作日索引   DAYOFWEEK()

脚本

select dayofweek('2022-12-11');

分析

老外的周日是一周的第一天

结果

30.返回一年中的某一天 （1-366） DAYOFYEAR()

脚本

select dayofyear('2002-12-11')

结果

31.返回日期格式字符串 GET_FROMAT()

脚本

SELECT DATE_FORMAT('2022-12-11',GET_FORMAT(DATE,'EUR'));

分析

函数调用	结果
GET_FORMAT(DATE,'USA')	'%m.%d.%Y'
GET_FORMAT(DATE,'JIS')	'%Y-%m-%d'
GET_FORMAT(DATE,'ISO')	'%Y-%m-%d'
GET_FORMAT(DATE,'EUR')	'%d.%m.%Y'
GET_FORMAT(DATE,'INTERNAL')	'%Y%m%d'
GET_FORMAT(DATETIME,'USA')	'%Y-%m-%d %H.%i.%s'
GET_FORMAT(DATETIME,'JIS')	'%Y-%m-%d %H:%i:%s'
GET_FORMAT(DATETIME,'ISO')	'%Y-%m-%d %H:%i:%s'
GET_FORMAT(DATETIME,'EUR')	'%Y-%m-%d %H.%i.%s'
GET_FORMAT(DATETIME,'INTERNAL')	'%Y%m%d%H%i%s'
GET_FORMAT(TIME,'USA')	'%h:%i:%s %p'
GET_FORMAT(TIME,'JIS')	'%H:%i:%s'
GET_FORMAT(TIME,'ISO')	'%H:%i:%s'
GET_FORMAT(TIME,'EUR')	'%H.%i.%s'
GET_FORMAT(TIME,'INTERNAL')	'%H%i%s'

结果

32.将弧度转换为度   DEGREES()

脚本

select degrees(pi()),degrees(pi()*3);

结果

33.分区内当前行的排名，无间隙   DENSE_RANK()

脚本

create table a(
    id    int auto_increment primary key,
    score int
);

insert into a (score)
values (90),
       (90),
       (85),
       (80),
       (70);


select *,dense_rank() over (order by score desc )
from a;

分析

无间隙排名就是，就是如果两个并列第一第三个也是第二

结果

34.整数除法   DIV()

脚本

select 5 div 2,5 / 2;

分析

相当于除法运算符（/）但是整数除法。从除法结果中丢弃小数点右侧的小数部分。

结果

35.返回索引号处的字符串   ELT()

脚本

select elt(1, 'A', 'B', 'C', 'D'),elt(3, 'A', 'B', 'C', 'D');

结果

36.e的多少次方   EXP()

脚本

select exp(2),exp(0);

分析

EXP(X)

返回e的值（e是自然对数，约2.7182818284）提高到X次方

结果

​

37.返回一个字符串，该字符串表示数字中的位   EXPORT_SET()

脚本

SELECT EXPORT_SET(9, 'Y', 'N', ' ', 10),
       EXPORT_SET(10, 1, 0, '-', 4);

分析

用法:

EXPORT_SET
(bits, on, off, separator, number of bits)

参数：
此函数接受5个参数。

• bits -
  结果将被格式化的整数。
• on -
  如果二进制数字为1，则它将返回。
• off -
  如果二进制数字为0，则它​​将返回。
• separator -
  分隔符，将放置在返回值之间。
• 位数-
  结果将要到达的位数

返回值：
此函数将返回一个字符串，该字符串将显示位数。

结果 

​

38.提取日期的一部分   EXTRACT()

脚本

SELECT EXTRACT(YEAR_MONTH FROM '2022-12-11 16:45:23'),EXTRACT(YEAR FROM now());

分析

时间区间表达式和单位参数

单位价值	预期 expr 格式
MICROSECOND	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEARS
SECOND_MICROSECOND	'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND	'MINUTES:SECONDS.MICROSECONDS'
MINUTE_SECOND	'MINUTES:SECONDS'
HOUR_MICROSECOND	'HOURS:MINUTES:SECONDS.MICROSECONDS'
HOUR_SECOND	'HOURS:MINUTES:SECONDS'
HOUR_MINUTE	'HOURS:MINUTES'
DAY_MICROSECOND	'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'
DAY_SECOND	'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE	'DAYS HOURS:MINUTES'
DAY_HOUR	'DAYS HOURS'
YEAR_MONTH	'YEARS-MONTHS'

结果

​

39.使用 XPath 表示法从 XML 字符串中提取值   EXTRACTVALUE()

脚本

 SELECT ExtractValue('<a><b/></a>', 'count(/a/b)');

结果

​

40.第一个参数在后续参数中的索引（位置)   FIELD()

脚本

SELECT FIELD('B', 'A', 'B', 'C', 'D', 'F'),FIELD('z', 'A', 'B', 'C', 'D', 'F');

结果

​

41.第二个参数中第一个参数的索引（位置）  FIND_IN_SET()

脚本

SELECT FIND_IN_SET('b','a,b,c,d'),FIND_IN_SET('z','a,b,c,d');

结果

​

42.窗口框架第一行的参数值   FIRST_VALUE()

脚本

CREATE TABLE order_content
(
    order_id    VARCHAR(8),
    user_id     VARCHAR(8),
    order_price INT,
    order_date  DATE
)
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8;
INSERT INTO order_content (order_id, user_id, order_price, order_date)
    VALUE ('o001', 'u001', 800, '2021-06-18')
    , ('o002', 'u001', 800, '2021-06-19')
    , ('o003', 'u001', 1000, '2021-06-22')
    , ('o004', 'u001', 1200, '2021-06-24')
    , ('o005', 'u002', 400, '2021-06-25')
    , ('o006', 'u002', 1500, '2021-06-26')
    , ('o007', 'u002', 2100, '2021-06-28')
    , ('o008', 'u003', 900, '2021-07-01')
    , ('o009', 'u003', 700, '2021-07-03')
    , ('o010', 'u003', 1700, '2021-07-04');

SELECT *,
       FIRST_VALUE(order_price) OVER (PARTITION BY user_id ORDER BY order_price) AS firstvalue
FROM order_content;

结果​

43.返回不大于参数的最大整数值   FLOOR()

脚本

SELECT FLOOR(1.23), FLOOR(-1.23);

分析

对于精确值数值参数，返回值具有精确值数值类型。对于字符串或浮点 参数，则返回值具有浮点类型。

结果

​

44.返回格式为指定小数位数的数字   FORMAT()

脚本

select format(1.23456,3),format(1.2,5);

分析

FORMAT（X，D）

将数字X的格式转换为指定的小数位 ，规则四舍五入，如果不足小数位则补零

结果

45.将字节计数转换为带单位的值   FORMAT_BYTES()

脚本

SELECT FORMAT_BYTES(64), FORMAT_BYTES(85426854126525412);

分析

给定一个数字字节计数，将其转换为人类可读的 格式并返回由值和单位组成的字符串 指示器。该字符串包含舍入为 的字节数 小数点后 2 位，至少 3 位有效数字。 小于 1024 字节的数字表示为整数 并且不四舍五入

单位指示器取决于字节计数的大小 参数如下表所示。

参数值	结果单位	结果单位指示器
最多 1024-1	字节	bytes
最多 1024² − 1	千字节	KIB
最多 1024³ − 1	兆字节	MIB
最多 1024⁴ − 1	千兆字节	GIB
最多 1024⁵ − 1	泰比字节	TIB
最多 1024⁶ − 1	百兆字节	PIB
1024⁶ 及以上	艾比字节	EIB

结果

46.以皮秒为单位的时间转换为带单位的值   FORMAT_PICO_TIME()

脚本

SELECT FORMAT_PICO_TIME(3501), FORMAT_PICO_TIME(188732396662000);

分析

给定数字性能架构延迟或等待时间 皮秒，将其转换为人类可读的格式并返回 由值和单位指示器组成的字符串。这 字符串包含四舍五入到小数点后 2 位的小数时间 和至少 3 位有效数字。小于 1 的次数 纳秒表示为整数

单位指标取决于时间值的大小 参数如下表所示。

参数值	结果单位	结果单位指示器
最多 10³ − 1	皮秒	ps
最多 10⁶ − 1	纳秒	ns
最多 10⁹ − 1	微秒	us
最多 10¹² − 1	毫秒	ms
高达 60×10¹² − 1	秒	s
最高 3.6×10¹⁵ − 1	纪要	min
高达 8.64×10¹⁶ − 1	小时	h
8.64×10¹⁶ 及以上	日	d

结果

47.返回最大的参数   GREATEST()

脚本

select greatest(25,52,62),greatest('c','b','z');

结果

48.解码 base64 编码字符串并返回结果   FROM_BASE64()

脚本

SELECT FROM_BASE64('YWJj');

分析

 使用的 base-64 编码规则编码的字符串，并返回 将结果解码为二进制字符串。结果是参数是否为有效的 base-64 字符串

YWJj是由TO_BASE64获取到的

结果

49.将日期数字转换为日期   FROM_DAYS()

脚本

select from_days(730689);

分析

FROM_DAYS(N)

给定日期数字N,返回一个DATE值

谨慎使用 FROM_DAYS() 旧日期。它不适用于前面的值 公历的出现（1582年）

结果

50.将 Unix 时间戳格式化为日期   FROM_UNIXTIME()

脚本

SELECT FROM_UNIXTIME(1670752934);

分析

将时间戳转换为具体的年月日

时间戳转换连接

结果

函数大全1

函数大全3

函数大全4

函数大全5

函数大全6

函数大全7

函数大全8