【MySQL】MySQL内置函数--日期函数/字符串函数/数学函数/其他相关函数

1.日期函数

MySQL中内置了一下函数：

获得年月日

mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2023-11-11     |
+----------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

获得时分秒

mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 15:46:47       |
+----------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

获得时间戳

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2023-11-11 15:47:19 |
+---------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

在日期的基础上加日期

mysql> select date_add('2023-11-11',interval 10 day);
+----------------------------------------+
| date_add('2023-11-11',interval 10 day) |
+----------------------------------------+
| 2023-11-21                             |
+----------------------------------------+
1 row in set (0.01 sec)
1
2
3
4
5
6
7

在日期的基础上减去时间

mysql> select date_sub('2023-11-11',interval 10 day);
+----------------------------------------+
| date_sub('2023-11-11',interval 10 day) |
+----------------------------------------+
| 2023-11-01                             |
+----------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

计算两个日期之间相差多少天

mysql> select datediff('2023-11-11','2023-10-21');
+-------------------------------------+
| datediff('2023-11-11','2023-10-21') |
+-------------------------------------+
|                                  21 |
+-------------------------------------+
1 row in set (0.01 sec)
1
2
3
4
5
6
7

创建一个留言表

mysql> create table messsage(
    -> id int primary key auto_increment,
    -> content varchar(32) not null,
    -> sendtime datetime
    -> );
Query OK, 0 rows affected (0.03 sec)
1
2
3
4
5
6

插入数据

mysql> insert into message(content,sendtime) values('hello world',now());
mysql> insert into message(content,sendtime) values('hello world',now());
mysql> select * from message;
+----+-------------+---------------------+
| id | content     | sendtime            |
+----+-------------+---------------------+
|  3 | hello world | 2023-11-11 16:03:38 |
|  4 | hello world | 2023-11-11 16:03:48 |
+----+-------------+---------------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

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

mysql> select * from message where date_add(sendtime ,interval 2 minute) > now();
+----+-------------+---------------------+
| id | content     | sendtime            |
+----+-------------+---------------------+
|  3 | hello world | 2023-11-11 16:03:38 |
|  4 | hello world | 2023-11-11 16:03:48 |
+----+-------------+---------------------+
2 rows in set (0.00 sec)

------------------------------|-----------|-------------|------------------
                              初始时间     now()       初始时间+2min
1
2
3
4
5
6
7
8
9
10
11

2.字符串函数

获取emp表的ename列的字符集

select charset(ename) from emp;
1

要求显示exam_result表中的信息，显示格式：“XXX的语文是XXX分，数学XXX分，英语XXX分”

select concat(name, '的语文是',chinese,'分，数学是',math,'分') as '分数' from student;
1

求学生表中学生姓名占用的字节数

select length(name), name from student;
1

注意：length函数返回字符串长度，以字节为单位。如果是多字节字符则计算多个字节数；如果是单字节字符则算作一个字节。比如：字母，数字算作一个字节，中文表示多个字节数（与字符集编码有关）

将emp表中所有名字中有S的替换成’上海’

select replace(ename, 'S', '上海') ,ename from emp;
1

截取emp表中ename字段的第二个到第三个字符

select substring(ename, 2, 2), ename from emp;
1

以首字母小写的方式显示所有员工的姓名

select concat(lcase(substring(ename,1,1)),substring(ename,2)) from emp;
1

3.数学函数

绝对值

select abs(-100.2);
mysql> select abs(-100.2);
+-------------+
| abs(-100.2) |
+-------------+
|       100.2 |
+-------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8

向上取整

mysql> select ceiling(3.1);
+--------------+
| ceiling(3.1) |
+--------------+
|            4 |
+--------------+
1 row in set (0.00 sec)

mysql> select ceiling(3.9);
+--------------+
| ceiling(3.9) |
+--------------+
|            4 |
+--------------+
1 row in set (0.00 sec)

mysql> select ceiling(-3.1);
+---------------+
| ceiling(-3.1) |
+---------------+
|            -3 |
+---------------+
1 row in set (0.00 sec)

mysql> select ceiling(-3.9);
+---------------+
| ceiling(-3.9) |
+---------------+
|            -3 |
+---------------+
1 row in set (0.00 sec
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

向下取整

mysql> select floor(3.1);
+------------+
| floor(3.1) |
+------------+
|          3 |
+------------+
1 row in set (0.00 sec)

mysql> select floor(3.9);
+------------+
| floor(3.9) |
+------------+
|          3 |
+------------+
1 row in set (0.00 sec)

mysql> select floor(-3.1);
+-------------+
| floor(-3.1) |
+-------------+
|          -4 |
+-------------+
1 row in set (0.00 sec)

mysql> select floor(-3.9);
+-------------+
| floor(-3.9) |
+-------------+
|          -4 |
+-------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

保留2位小数位数（小数四舍五入)

mysql> select format(12.345,2);
+------------------+
| format(12.345,2) |
+------------------+
| 12.35            |
+------------------+
1 row in set (0.00 sec)

mysql> select format(99.99,2);
+-----------------+
| format(99.99,2) |
+-----------------+
| 99.99           |
+-----------------+
1 row in set (0.00 sec)

mysql> select format(99.999,2);
+------------------+
| format(99.999,2) |
+------------------+
| 100.00           |
+------------------+
1 row in set (0.00 sec)
mysql> select format(-12.223,2);
+-------------------+
| format(-12.223,2) |
+-------------------+
| -12.22            |
+-------------------+
1 row in set (0.00 sec)

mysql> select format(-12.227,2);
+-------------------+
| format(-12.227,2) |
+-------------------+
| -12.23            |
+-------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

产生随机数

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.5963675860281732 |
+--------------------+
1 row in set (0.00 sec)

mysql> select rand()*10;
+--------------------+
| rand()*10          |
+--------------------+
| 3.1565381243420187 |
+--------------------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

4.其它函数

• user() 查询当前用户

select user();
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8

• database()显示当前正在使用的数据库

select database();
mysql> select database();
+------------+
| database() |
+------------+
| scott      |
+------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8

• md5(str)对一个字符串进行md5摘要，摘要后得到一个32位字符串

select md5('admin');
mysql> select md5('admin');
+----------------------------------+
| md5('admin')                     |
+----------------------------------+
| 21232f297a57a5a743894a0e4a801fc3 |
+----------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8

• password()函数，MySQL数据库使用该函数对用户加密

select password('root');
mysql> select password('root');
+-------------------------------------------+
| password('root')                          |
+-------------------------------------------+
| *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+-------------------------------------------+
1 row in set, 1 warning (0.01 sec)
1
2
3
4
5
6
7
8

• ifnull（val1， val2） 如果val1为null，返回val2，否则返回val1的值

mysql> select ifnull('abc',null);
+--------------------+
| ifnull('abc',null) |
+--------------------+
| abc                |
+--------------------+
1 row in set (0.00 sec)

mysql> select ifnull(null, '123');
+---------------------+
| ifnull(null, '123') |
+---------------------+
| 123                 |
+---------------------+
1 row in set (0.00 sec)

mysql> select ifnull('abc', '123');
+----------------------+
| ifnull('abc', '123') |
+----------------------+
| abc                  |
+----------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23