【MySQL】内置函数

目录

1.日期函数

1.1.current_date()——年月日

1.2.current_time函数——时分秒

1.3.current_timestamp函数——年月日时分秒

1.4.now函数——年月日时分秒

1.5.date函数

1.6.date_add函数

1.7.date_sub函数

1.8.datediff函数

1.9.其他日期函数

2.字符串函数

2.1.charset函数

2.2. concat函数

 2.3.instr函数

2.4.ucase与lcase函数

2.5.left，right，substring函数

2.6.length函数

2.7.replace函数

2.8.strcmp函数

2.9.ltrim、rtrim和trim函数

2.10.其他字符串函数

3.数学函数

3.1.abs函数

3.2.bin，hex，conv函数

 3.3.ceiling，floor函数

3.4.format函数

3.5.rand函数

3.6.mod函数

3.7.其他数学函数

 4.其他函数

4.1.user函数

4.2.database函数

4.3.md5函数

4.4.案例：在数据库层面简单模拟用户的登录过程

4.5.ifnull函数

5.MySQL 高级函数

MySQL 提供了丰富的内置函数，用于在 SQL 查询中执行各种操作，包括数学运算、字符串处理、日期和时间操作等。

1.日期函数

MySQL中常用的日期函数

1. current_date()

• 描述：这个函数用于获取当前的日期（不包含时间部分）。
• 示例：SELECT current_date(); 会返回如 2023-10-04 这样的日期。

2. current_time()

• 描述：这个函数用于获取当前的时间（不包含日期部分）。
• 示例：SELECT current_time(); 会返回如 14:30:00 这样的时间。

3. current_timestamp()

• 描述：这个函数用于获取当前的时间戳，即当前的日期和时间。
• 示例：SELECT current_timestamp(); 会返回如 2023-10-04 14:30:00 这样的时间戳。

4. now()

• 描述：这个函数与current_timestamp()类似，也用于获取当前的日期和时间。
• 示例：SELECT now(); 会返回如 2023-10-04 14:30:00 这样的日期和时间。

5. date(datetime)

• 描述：这个函数用于从一个日期时间值中提取日期部分。
• 参数：datetime 是一个日期时间值。
• 示例：SELECT date('2023-10-04 14:30:00'); 会返回 2023-10-04。

6. date_add(date, interval d_value_type)

• 描述：这个函数用于在指定的日期上添加指定的时间间隔。时间间隔的单位可以是年（year）、月（month）、日（day）、小时（hour）、分钟（minute）、秒（second）。
• 参数：date 是要添加时间间隔的日期。
• interval 是关键字，表示接下来的值是时间间隔。
• d_value_type 是时间间隔的值和单位，例如 INTERVAL 1 DAY 表示一天。
• 示例：SELECT date_add('2023-10-04', INTERVAL 1 DAY); 会返回 2023-10-05。

7. date_sub(date, interval d_value_type)

• 描述：这个函数用于在指定的日期上减去指定的时间间隔。时间间隔的单位可以是年（year）、月（month）、日（day）、小时（hour）、分钟（minute）、秒（second）。
• 参数：与date_add函数相同。
• 示例：SELECT date_sub('2023-10-04', INTERVAL 1 DAY); 会返回 2023-10-03。

8. datediff(date1, date2)

• 描述：这个函数用于计算两个日期之间的天数差。
• 参数：date1 和 date2 是要计算天数差的两个日期。
• 示例：SELECT datediff('2023-10-05', '2023-10-01'); 会返回 4，表示两个日期之间相差4天。

这些函数在MySQL中非常常用，能够帮助你进行各种日期和时间的操作与计算。

接下来我们来一一讲解。

1.1.current_date()——年月日

这个函数用于获取当前的日期（不包含时间部分）。

select current_date();

1.2.current_time函数——时分秒

使用此函数我们能够获取当前时间（时分秒，格式为xx:xx:xx）

select current_time();

1.3.current_timestamp函数——年月日时分秒

使用current_timestamp函数，我们能够得到当前的时间戳，在MySQL中时间戳已经被自动转换过了，所以我们得到的直接是：日期+时间

select current_timestamp();

1.4.now函数——年月日时分秒

使用该函数我们能够得到当前日期 + 时间

select now();

1.5.date函数

这个函数可以帮助我们进行：将日期+时间转换为日期

例如，我们只想要时间戳的日期部分:

select date(current_timestamp());

我们也可以对任意一个日期+时间进行转换：

select date('2024-10-19 09:59:45');

1.6.date_add函数

使用该函数我们能够对日期 或 时间进行加法运算

• 第一个参数：要运算的日期或时间
• 第二个参数：是间隔数值，interval后的数值单位可以是: year minute second day

例如：计算100天以后是那一天

select date_add(date(now()), interval 100 day);

例如：计算520秒后的日期 +时间

select date_add(now(), interval 520 second);

如果在date_add函数中添加的日期/时间为负值，则相当于在日期的基础上减去日期/时间。如下：

select now();
select date_add(now(), interval -3 year);

1.7.date_sub函数

date_sub函数用于在日期的基础上减去日期或时间，其用法与date_add函数类似。

例如：计算100天以前是哪一天

select now();
select date_sub(now(), interval 100 day);

如果在date_sub函数中减去的日期/时间为负值，则相当于在日期的基础上添加日期/时间。如下：

select now();
select date_sub(now(), interval -3 year);

1.8.datediff函数

使用datediff函数用于获取两个日期的差，单位是天。

例如：计算从建国到现在经历了多少天了

select datediff(date(now()), '1949-10-1');

接下来来通过一个案例来讲解它的作用

创建一个评论表，表中包含自增长的主键id、评论内容和评论时间。如下：

    create table comment_table (
    id bigint primary key auto_increment,
    content varchar(100) not null,
    sendtime datetime not null
    );

 然后我们开始插入一些评论，插入时的时间我们直接选择使用now()

insert into comment_table(content, sendtime) values('欲买桂花同载酒,终不似,少年游', now());

insert into comment_table(content, sendtime) values('庭有枇杷树,吾妻死之年所手植也,今已亭亭如盖矣', now());

1.8.1 显示所有留言信息，发布日期只显示日期，不用显示时间

这个问题还是很简单的，我们的表中的sendtime是datetime类型，这会同时包含日期和时间，但是我们可以使用date()函数来从datetime中取出时间。 

select content, date(sendtime) from comment_table;

1.8.2 请查询在20分钟内发布的帖子

这时一个比较常见的需求，我们只需要比较所有的评论时间是否是大于 当前时间 - 20 minute后得到的时间，即可判断出该评论是否是20分钟以内的评论。

select content , sendtime from comment_table where sendtime > date_sub(now(), interval 20 minute);

这里有两条，是因为两条评论都在二十分钟之内发布的。 

1.9.其他日期函数

SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY);
->2017-06-25

SELECT ADDTIME('2011-11-11 11:11:11', 5);
->2011-11-11 11:11:16 (秒)

SELECT ADDTIME("2020-06-15 09:34:21", "2:10:5"); 
-> 2020-06-15 11:44:26

SELECT CURDATE();
-> 2018-09-19

SELECT CURRENT_DATE();
-> 2018-09-19

SELECT CURRENT_TIME();
-> 19:59:02

SELECT CURRENT_TIMESTAMP()
-> 2018-09-19 20:57:43

SELECT CURTIME();
-> 19:59:02

SELECT DATE("2017-06-15");    
-> 2017-06-15

SELECT DATEDIFF('2001-01-01','2001-02-02')
-> -32

SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY);    
-> 2017-06-25

SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL 15 MINUTE);
-> 2017-06-15 09:49:21

SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR);
->2017-06-15 06:34:21

SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 MONTH);
->2017-03-15 09:34:21

SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r')
-> 2011-11-11 11:11:11 AM

SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate
FROM Orders

SELECT DAY("2017-06-15");  
-> 15

SELECT DAYNAME('2011-11-11 11:11:11')
->Friday

SELECT DAYOFMONTH('2011-11-11 11:11:11')
->11

SELECT DAYOFWEEK('2011-11-11 11:11:11')
->6

SELECT DAYOFYEAR('2011-11-11 11:11:11')
->315

SELECT EXTRACT(MINUTE FROM '2011-11-11 11:11:11') 
-> 11

SELECT FROM_DAYS(1111)
-> 0003-01-16

SELECT HOUR('1:2:3')
-> 1

SELECT LAST_DAY("2017-06-20");
-> 2017-06-30

SELECT LOCALTIME()
-> 2018-09-19 20:57:43

SELECT LOCALTIMESTAMP()
-> 2018-09-19 20:57:43

SELECT MAKEDATE(2017, 3);
-> 2017-01-03

SELECT MAKETIME(11, 35, 4);
-> 11:35:04

SELECT MICROSECOND("2017-06-20 09:34:00.000023");
-> 23

SELECT MINUTE('1:2:3')
-> 2

SELECT MONTHNAME('2011-11-11 11:11:11')
-> November

SELECT MONTH('2011-11-11 11:11:11')
->11

SELECT NOW()
-> 2018-09-19 20:57:43

SELECT PERIOD_ADD(201703, 5);   
-> 201708

SELECT PERIOD_DIFF(201710, 201703);
-> 7

SELECT QUARTER('2011-11-11 11:11:11')
-> 4

SELECT SECOND('1:2:3')
-> 3

SELECT SEC_TO_TIME(4320)
-> 01:12:00

SELECT STR_TO_DATE("August 10 2017", "%M %d %Y");
-> 2017-08-10

SELECT SUBDATE('2011-11-11 11:11:11', 1)
->2011-11-10 11:11:11 (默认是天)

SELECT SUBTIME('2011-11-11 11:11:11', 5)
->2011-11-11 11:11:06 (秒)

SELECT SYSDATE()
-> 2018-09-19 20:57:43

SELECT TIME("19:30:10");
-> 19:30:10

SELECT TIME_FORMAT('11:11:11','%r')
11:11:11 AM

SELECT TIME_TO_SEC('1:12:00')
-> 4320

mysql> SELECT TIMEDIFF("13:10:11", "13:10:10");
-> 00:00:01
mysql> SELECT TIMEDIFF('2000:01:01 00:00:00',
    ->                 '2000:01:01 00:00:00.000001');
        -> '-00:00:00.000001'
mysql> SELECT TIMEDIFF('2008-12-31 23:59:59.000001',
    ->                 '2008-12-30 01:01:01.000002');
        -> '46:58:57.999999'

mysql> SELECT TIMESTAMP("2017-07-23",  "13:10:11");
-> 2017-07-23 13:10:11
mysql> SELECT TIMESTAMP('2003-12-31');
        -> '2003-12-31 00:00:00'
mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');
        -> '2004-01-01 00:00:00'

mysql> SELECT TIMESTAMPDIFF(DAY,'2003-02-01','2003-05-01');   // 计算两个时间相隔多少天
        -> 89
mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');   // 计算两个时间相隔多少月
        -> 3
mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');    // 计算两个时间相隔多少年
        -> -1
mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');  // 计算两个时间相隔多少分钟
        -> 128885

SELECT TO_DAYS('0001-01-01 01:01:01')
-> 366

SELECT WEEK('2011-11-11 11:11:11')
-> 45

SELECT WEEKDAY("2017-06-15");
-> 3

SELECT WEEKOFYEAR('2011-11-11 11:11:11')
-> 45

SELECT YEAR("2017-06-15");
-> 2017

SELECT YEARWEEK("2017-06-15");
-> 201724

2.字符串函数

以下是对这些常用字符串函数的详细解释及例子：

1.charset(str)

• 描述：该函数用于获取字符串使用的字符集。
• 例子：charset("Hello, World!") 可能返回 "utf-8" 或其他字符集名称，具体取决于数据库或编程环境的配置。

2.concat(str1, str2 [, …])

• 描述：将多个字符串连接成一个字符串。
• 例子：concat("Hello", " ", "World", "!") 返回 "Hello World!"。

3.instr(str, substr)

• 描述：返回子字符串 substr 在字符串 str 中首次出现的位置，如果没有出现则返回0。
• 例子：instr("Hello, World!", "World") 返回 8，因为 "World" 从第8个字符开始。

4.ucase(str)

• 描述：将字符串转换为大写。
• 例子：ucase("Hello, World!") 返回 "HELLO, WORLD!"。

5.lcase(str)

• 描述：将字符串转换为小写。
• 例子：lcase("Hello, World!") 返回 "hello, world!"。

6.left(str, length)

• 描述：从字符串的左边开始，向后截取指定长度的字符。
• 例子：left("Hello, World!", 5) 返回 "Hello"。

7.right(str, length)

• 描述：从字符串的右边开始，向后截取指定长度的字符。
• 例子：right("Hello, World!", 6) 返回 "World!"。

8.substring(str, position [, length])

• 描述：从字符串的指定位置开始，向后截取指定长度的字符。如果未指定长度，则截取到字符串的末尾。
• 例子：substring("Hello, World!", 8, 5) 返回 "World"。

9.length(str)

• 描述：返回字符串占用的字节数。注意，这与字符数可能不同，特别是当使用多字节字符集时。
• 例子：length("Hello, World!") 在UTF-8编码下返回 13，因为每个字符占用一个字节（这里假设没有使用多字节字符）。

10.replace(str, search_str, replace_str)

• 描述：将字符串中的 search_str 替换为 replace_str。
• 例子：replace("Hello, World!", "World", "Everyone") 返回 "Hello, Everyone!"。

11.strcmp(str1, str2)

• 描述：逐字符比较两个字符串的大小。如果 str1 小于 str2，则返回负值；如果相等，则返回0；如果 str1 大于 str2，则返回正值。
• 例子：strcmp("apple", "banana") 返回负值，因为 "apple" 在字典序上小于 "banana"。

12.ltrim(str)、rtrim(str)、trim(str)

• 描述：分别去除字符串的前空格、后空格和前后空格。
• 例子：
• ltrim(" Hello, World!") 返回 "Hello, World!"。
• rtrim("Hello, World! ") 返回 "Hello, World!"。
• trim(" Hello, World! ") 返回 "Hello, World!"。

这些函数在编程和数据库操作中非常有用，可以帮助你高效地处理字符串数据。

2.1.charset函数

该函数用于获取字符串使用的字符集。

为了方便进行演示，下面创建一个学生表，表当中包含自增长的主键id、学号、姓名和QQ号。如下：

create table students(
id int unsigned primary key auto_increment,
sn int unsigned not null unique comment '学号',
name varchar(20) not null comment '姓名',
qq varchar(15) unique comment 'qq号码'
);
 
desc students;
 
INSERT INTO students (id, sn, name,qq) VALUES  
(99, 100, '唐僧',NULL),  
(102, 123, '曹操',NULL),  
(103, 187, '刘备',NULL),  
(105, 1023, '孙行者',111111);
 
select * from students;

现在我们要查看id和qq使用的字符集

select charset(id),charset(qq) from students;

可以看到对于字符类型采用的是utf8类型，对于数字采用的是二进制类型。

注意：当有些时候我们发现数据库中的字符，有的乱码有的正常显示我们就可以通过这种方式进行排查是否是字符集出了问题。

2.2. concat函数

将多个字符串连接成一个字符串。

该函数的使用很简单，我们只要将我们想要拼接的字符串放进该函数中就行了，此外就算我们放的内容不是字符串，该函数也会自动给我们转换为字符串。

select concat('aaa',123,'哈哈哈');

案例：例如现有如下成绩表，要求以“恭喜XXX，你的语文是XX分，数学是XX分，英语是XX分！”的格式显示成绩表中的信息。

在这个案例中我们就可以使用concat来连接指定的字符串。

select concat('恭喜',name,', 你的语文成绩是：', chinese, ', 数学成绩是：', math, ', 英语成绩是：', english) as 成绩单 from exam_result;

 2.3.instr函数

通过这个函数我们能够判断一个字符串是否是另外一个字符串的子串。

• 第一个参数是，字符串的全集。
• 第二个参数是，要查找的字符串。
• 其返回值是，字符串在全集中的位置的下标（注意是从0开始的），如果找不到就返回0。

select instr('abcd1234efg', 'd1234') res;

select instr('abcd1234efg', 'd12345') res;

2.4.ucase与lcase函数

ucase函数用于将字符串转换成大写后的字符串。如下：

select ucase('abcd1234ABCD');

ucase函数用于将字符串转换成小写后的字符串。如下：

select lcase('abcd1234ABCD');

注意： ucase和lcase并不会改变原始字符串。

2.5.left，right，substring函数

left函数用于从字符串的左边开始，向后截取指定个数的字符。如下：

select left('abcdefghi', 3);

right函数用于从字符串的右边开始，向前截取指定个数的字符。如下：

select right('abcdefghi', 3);

substring函数用于从字符串的指定位置开始，向后截取指定个数的字符。

select substring('abcdefghi', 3, 3);

当我们不指定字符的个数时，默认直接截取到结尾。

2.6.length函数

length函数用于获取字符串占用的字节数。如下：

select length('abcd');

又例如，我们查看一下中文的字节数：

select length('唐');
select length('唐三');
select length('唐三藏');

我使用的是utf8，所以每个中文字符占3个字节。

说明一下： 对于多字节字符来说，不同编码中一个字符所占的字节个数是不同的，比如utf8中一个字符占用3个字节，而gbk中一个字符占用2个字节。

2.7.replace函数

replace(str, search_str, replace_str)

• 描述：将字符串中的 search_str 替换为 replace_str。

select replace('Hello, World!', 'World','Everyone');

这里发生的事情是：

1. 'Hello, World!' 是原始字符串。
2. 'World' 是要查找并替换的子字符串。
3. 'Everyone' 是用来替换找到的子字符串的新字符串。

因此，REPLACE函数会在原始字符串 'Hello, World!' 中查找 'World'，找到后将其替换为 'Everyone'。 

2.8.strcmp函数

strcmp函数用于逐字符按照ASCII码比较两个字符串的大小，两个字符串大小相等返回0，前者大返回1，后者大返回-1。如下：

select strcmp('abcd','abcd');
select strcmp('abcd','abcc');
select strcmp('abcd','abce');

 需要注意的是，strcmp函数在比较时是不区分大小写的。如下：

select strcmp('ABCD','abcd');

2.9.ltrim、rtrim和trim函数

ltrim函数分别用于去除字符串的前空格。如下：

select ltrim('        你好    世界     ') as ret;

rtrim函数分别用于去除字符串的后空格。如下：

select rtrim('        你好    世界     ') as ret;

trim函数用于去除字符串的前后空格。如下：

select trim('        你好    世界                                 ') as ret;

注意： 所有的trim函数并不会改变原始字符串，如果想要更改请使用update语句进行更新。 

2.10.其他字符串函数

SELECT ASCII(CustomerName) AS NumCodeOfFirstChar
FROM Customers;

SELECT CHAR_LENGTH("RUNOOB") AS LengthOfString;

SELECT CHARACTER_LENGTH("RUNOOB") AS LengthOfString;

SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook") AS ConcatenatedString;

SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")AS ConcatenatedString;

SELECT FIELD("c", "a", "b", "c", "d", "e");

SELECT FIND_IN_SET("c", "a,b,c,d,e");

SELECT FORMAT(250500.5634, 2);     -- 输出 250,500.56

SELECT INSERT("google.com", 1, 6, "runoob");  -- 输出：runoob.com

SELECT LOCATE('st','myteststring');  -- 5

SELECT LOCATE('b', 'abc') -- 2

SELECT LCASE('RUNOOB') -- runoob

SELECT LEFT('runoob',2) -- ru

SELECT LOWER('RUNOOB') -- runoob

SELECT LPAD('abc',5,'xx') -- xxabc

SELECT LTRIM("    RUNOOB") AS LeftTrimmedString;-- RUNOOB

SELECT MID("RUNOOB", 2, 3) AS ExtractString; -- UNO

SELECT POSITION('b' in 'abc') -- 2

SELECT REPEAT('runoob',3) -- runoobrunoobrunoob

SELECT REPLACE('abc','a','x') --xbc

SELECT REVERSE('abc') -- cba

SELECT RIGHT('runoob',2) -- ob

SELECT RPAD('abc',5,'xx') -- abcxx

SELECT RTRIM("RUNOOB     ") AS RightTrimmedString;   -- RUNOOB

SELECT SPACE(10);

SELECT STRCMP("runoob", "runoob");  -- 0

SELECT SUBSTR("RUNOOB", 2, 3) AS ExtractString; -- UNO

SELECT SUBSTRING("RUNOOB", 2, 3) AS ExtractString; -- UNO

SELECT SUBSTRING_INDEX('a*b','*',1) -- a
SELECT SUBSTRING_INDEX('a*b','*',-1)    -- b
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1)    -- c

SELECT TRIM('    RUNOOB    ') AS TrimmedString;

SELECT UCASE("runoob"); -- RUNOOB

SELECT UPPER("runoob"); -- RUNOO

3.数学函数

abs(number)

• 描述：该函数用于计算一个数的绝对值。
• 示例：abs(-5) 返回 5。

bin(decimal_number)

• 描述：该函数将十进制数转换为二进制字符串。
• 示例：bin(10) 返回 '0b1010'，其中 '0b' 是二进制的前缀。

hex(decimal_number)

• 描述：该函数将十进制数转换为十六进制字符串。
• 示例：hex(255) 返回 '0xff'，其中 '0x' 是十六进制的前缀。

conv(number, from_base, to_base)

• 描述：该函数将任意进制的数转换为另一种进制的数。from_base 是原始数的进制，to_base 是目标进制。
• 示例：conv('A', 16, 10) 返回 10，因为 'A' 在十六进制中代表 10。

ceiling(number)

• 描述：该函数用于向上取整，即返回大于或等于指定数的最小整数。
• 示例：ceiling(4.2) 返回 5。

floor(number)

• 描述：该函数用于向下取整，即返回小于或等于指定数的最大整数。
• 示例：floor(4.8) 返回 4。

format(number, n)

• 描述：该函数用于格式化数字，保留 n 位小数，并进行四舍五入。
• 示例：format(12345.6789, 2) 返回 '12,345.68'。

rand()

• 描述：该函数用于生成一个范围在 [0.0, 1.0) 之间的随机浮点数。
• 示例：rand() 可能返回 0.123456789（每次调用都可能不同）。

mod(number, denominator)

• 描述：该函数用于计算 number 除以 denominator 的余数。
• 示例：mod(10, 3) 返回 1，因为 10 除以 3 的余数是 1。

3.1.abs函数

此函数用于对数据求绝对值，不论该数据是：整数还是浮点数都能够使用。

select abs(1024);
select abs(-1024);
select abs(-10.24);

3.2.bin，hex，conv函数

这些函数都是进制转换函数，bin是转换为2进制，hex是转换为16进制，conv函数是任意进制的转换。 

select bin(5);
select bin(5.1);

select hex(11);
select hex(16);

 conv(number, from_base, to_base)

• number， 要转换的数字。
• from_base，要转换的数字(number)的进制。
• to_base，想要转换的进制。

select conv(15,10,2);
select conv(15,10,8);
select conv(15,10,16);

 3.3.ceiling，floor函数

ceiling函数用于向上取整，floor函数用于向下取整。

在介绍怎么使用之前，我们先来了解一下取整的四种方式

        取整是数学和计算机科学中常见的操作，它指的是将实数（或浮点数）转换为整数的过程。以下是关于取整的几种常见方式：

1. 四舍五入：
   • 描述：四舍五入是一种常用的取整方式，它根据实数的小数部分来决定是否进位。具体来说，如果小数部分大于等于0.5，则整数部分加1；如果小数部分小于0.5，则整数部分不变。
   • 示例：四舍五入3.5得到4，四舍五入2.4得到2。
2. 零向取整（截断/舍去小数部分）：
   • 描述：零向取整也被称为截断或舍去小数部分。在这种方式下，实数的小数部分被直接丢弃，只保留整数部分。
   • 示例：零向取整3.7得到3，零向取整-2.3得到-2。需要注意的是，这种方式与四舍五入不同，它不考虑小数部分的值，直接截断。
3. 向上取整：
   • 描述：向上取整是指将实数向上舍入为最接近的整数。如果实数的小数部分不为零，则整数部分加1。
   • 示例：向上取整3.2得到4，向上取整-2.8得到-2（注意，对于负数，向上取整是向数值更大的方向取整，但结果仍然是负数）。
4. 向下取整：
   • 描述：向下取整是指将实数向下舍入为最接近的整数。如果实数的小数部分不为零，则忽略小数部分。
   • 示例：向下取整3.8得到3，向下取整-2.1得到-3（对于负数，向下取整是向数值更小的方向取整）。

 使用ceinling向上取整：

select ceiling(3.14);
select ceiling(3.92);
select ceiling(3.0);

 我们使用floor来进行向下取整

select floor(3.14);
select floor(3.92);
select floor(3.0);

3.4.format函数

该函数是以四舍五入的方式保留指定位数的小数

format(number, n)

• number，要进行精度控制的数字。
• n，保留几位小数。

select format(3.1415926, 2);
select format(3.1415926, 4);

3.5.rand函数

此函数会给我们生成随机浮点数，范围为[0.0, 1.0)

3.5.1 生成随机数

select rand();

3.5.2 生成[0 - 100)的随机数

select format(rand()*100,0);

如果我们想获取【n,m】的随机数，那么我们可以套用下面这个公式：

n + RAND() * (m - n + 1)

• 比如【2，88】

select 2+rand()*(88-2+1);

3.6.mod函数

该函数可以对数据进行取模运算。

select mod(10,3);

3.7.其他数学函数

SELECT ABS(-1) -- 返回1

SELECT ACOS(0.25);

SELECT ASIN(0.25);

SELECT ATAN(2.5);

SELECT ATAN2(-0.8, 2);

SELECT AVG(Price) AS AveragePrice FROM Products;

SELECT CEIL(1.5) -- 返回2

SELECT CEILING(1.5); -- 返回2

SELECT COS(2);

SELECT COT(6);

SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;

SELECT DEGREES(3.1415926535898) -- 180

SELECT 10 DIV 5;  -- 2

SELECT EXP(3) -- 20.085536923188

SELECT FLOOR(1.5) -- 返回1

SELECT GREATEST(3, 12, 34, 8, 25); -- 34

SELECT GREATEST("Google", "Runoob", "Apple");   -- Runoob

SELECT LEAST(3, 12, 34, 8, 25); -- 3

SELECT LEAST("Google", "Runoob", "Apple");   -- Apple

SELECT LN(2);  -- 0.6931471805599453

SELECT LOG(20.085536923188) -- 3
SELECT LOG(2, 4); -- 2

SELECT LOG10(100) -- 2

SELECT LOG2(6);  -- 2.584962500721156

SELECT MAX(Price) AS LargestPrice FROM Products;

SELECT MIN(Price) AS MinPrice FROM Products;

SELECT MOD(5,2) -- 1

SELECT PI() --3.141593

SELECT POW(2,3) -- 8

SELECT POWER(2,3) -- 8

SELECT RADIANS(180) -- 3.1415926535898

SELECT RAND() --0.93099315644334

SELECT ROUND(1.23456) --1

SELECT ROUND(345.156, 2) -- 345.16

SELECT SIGN(-10) -- (-1)

SELECT SIN(RADIANS(30)) -- 0.5

SELECT SQRT(25) -- 5

SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;

SELECT TAN(1.75);  -- -5.52037992250933

SELECT TRUNCATE(1.23456,3) -- 1.234

 4.其他函数

user()

• 描述：这个函数用于查询当前连接到数据库的用户。它通常返回用户的名称和主机名，格式为 'user@host'。
• 例子：如果您在名为 localhost 的主机上以用户名 root 连接到数据库，那么 SELECT user(); 将返回 'root@localhost'。

md5()

• 描述：这个函数用于对一个字符串进行MD5摘要。MD5是一种广泛使用的哈希函数，可以产生一个128位（16字节）的散列值（通常用32位的十六进制字符串表示）。
• 例子：SELECT md5('hello'); 将返回一个字符串，例如 '5d41402abc4b2a76b9719d911017c592'，这是字符串 'hello' 的MD5哈希值。

database()

• 描述：这个函数用于显示当前正在使用的数据库的名称。如果您已经选择了某个数据库，那么这个函数将返回该数据库的名称。
• 例子：假设您已经选择了名为 mydatabase 的数据库，那么 SELECT database(); 将返回 'mydatabase'。

ifnull()

• 描述：这个函数类似于三目运算符 a ? b : c 的功能。它接受两个参数，如果第一个参数不是 NULL，则返回第一个参数的值；如果第一个参数是 NULL，则返回第二个参数的值。
• 例子：SELECT ifnull(NULL, 'default_value'); 将返回 'default_value'，因为第一个参数是 NULL。而 SELECT ifnull('not_null', 'default_value'); 将返回 'not_null'，因为第一个参数不是 NULL。

4.1.user函数

此函数用于获取MySQL连接的当前用户名和主机名。如下：

select user();

4.2.database函数

database函数用于显示当前正在使用的数据库。如下：

select database();

4.3.md5函数

md5函数用于对一个字符串进行md5摘要，摘要后得到一个32位字符串。利用这个特性我们可以对用户的密码进行加密。

select md5('awsl1414');

说明：

• 一般情况下一个公司内部数据库不会存储用户的明文密码，而会将用户密码加密形成摘要后存储对应的摘要，当用户登录账号时，将用户输入的的密码加密形成摘要后与数据库中存储的摘要做对比，如果对比成功则允许登录。

这么做的好处主要有两个：

• 第一个好处就是：公司内部数据库中存储的不是用户的明文信息，就算数据库被盗，用户信息泄露了也不至于立即对用户产生很大影响（破解加密的密码需要时间和技术）。
• 第二个好处就是形成的摘要是定长的，这样有利于数据库表结构的设计。

4.4.案例：在数据库层面简单模拟用户的登录过程

首先我们先创建一张用户表，包含用户编号id，用户姓名name，密码password

 create table user(
 id bigint primary key auto_increment,
 name varchar(30) not null,
 password char(32) not null
 );

 注册过程

然后我们插入一些用户：

insert into user(name, password) values('零一','abcd1234');
insert into user(name, password) values('张三','1674micro');
select * from user;

这时我们看到我们的密码是明文的，这是极其不安全的！

于是我们可以使用md5()进行加密形成32位的摘要然后再插入到数据库中，提高密码的安全性！

这时我们再进行查看数据库的内容，我们发现我们已经不能够看出其密码到底是什么了

insert into user(name, password) values('零一', md5('abcd1234'));

insert into user(name, password) values('张三', md5('1674micro'));

select * from user;

 登录过程

我们在进行登录时，也要使用md5()进行验证才能够正确登录：

我们首先先删除没有使用md5()加密过的明文密码

delete from user where id in(1,2);
select * from user;

不使用md5()进行加密我们发现查找不到！

select * from user where name='零一' and password='abcd1234';

 使用md5()进行加密，然后登录。

select * from user where name='零一' and password=md5('abcd1234');

4.5.ifnull函数

ifnull函数接受两个参数，如果第一个参数不为null则返回第一个参数值，否则返回第二个参数值。如下：

select ifnull(10,20);
select ifnull(null,20);
select ifnull(null,null);

在对一些值为NULL的数据进行特殊处理时，这个函数还是挺常用的。

5.MySQL 高级函数

SELECT BIN(15); -- 1111

SELECT BINARY "RUNOOB";
-> RUNOOB

CASE expression
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
   ...
    WHEN conditionN THEN resultN
    ELSE result
END

SELECT CASE 
　　WHEN 1 > 0
　　THEN '1 > 0'
　　WHEN 2 > 0
　　THEN '2 > 0'
　　ELSE '3 > 0'
　　END
->1 > 0

SELECT CAST("2017-08-29" AS DATE);
-> 2017-08-29

SELECT COALESCE(NULL, NULL, NULL, 'runoob.com', NULL, 'google.com');
-> runoob.com

SELECT CONNECTION_ID();
-> 4292835

SELECT CONV(15, 10, 2);
-> 1111

SELECT CHARSET('ABC')
->utf-8    

SELECT CHARSET(CONVERT('ABC' USING gbk))
->gbk

SELECT CURRENT_USER();
-> guest@%

SELECT DATABASE();   
-> runoob

SELECT IF(1 > 0,'正确','错误')    
->正确

SELECT IFNULL(null,'Hello Word')
->Hello Word

SELECT ISNULL(NULL);
->1

SELECT LAST_INSERT_ID();
->6

SELECT NULLIF(25, 25);
->

SELECT SESSION_USER();
-> guest@%

SELECT SYSTEM_USER();
-> guest@%

SELECT USER();
-> guest@%

SELECT VERSION()
-> 5.6.34

以下是 MySQL 8.0 版本新增的一些常用函数：

SELECT JSON_OBJECT('key1', 'value1', 'key2', 'value2')

SELECT JSON_ARRAY(1, 2, 'three')

SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name')

SELECT JSON_CONTAINS('{"name": "John", "age": 30}', 'John', '$.name')

SELECT ROW_NUMBER() OVER(ORDER BY id) AS row_number, name FROM users

SELECT RANK() OVER(ORDER BY score DESC) AS rank, name, score FROM students

原文地址：https://blog.csdn.net/2301_80224556/article/details/143066825

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！