内置函数

# 日期函数

函数名称	描述
current_date()	获取当前日期
current_time()	获取当前时间
current_timestamp()	获取当前时间戳
now()	获取当前日期时间
date(datetime)	获取datetime参数的日期部分
date_add(date, interval d_value_type)	在date中添加日期或时间，interval后的数值单位可以是：year、month、day、hour、minute、second
date_sub(date, interval d_value_type)	在date中减去日期或时间，interval后的数值单位可以是：year、month、day、hour、minute、second
datediff(date1, date2)	获取两个日期的差，单位是天

示例：

mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2025-03-12     |
+----------------+
1 row in set (0.00 sec)

mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 20:05:19       |
+----------------+
1 row in set (0.00 sec)

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2025-03-12 20:05:26 |
+---------------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2025-03-12 20:05:35 |
+---------------------+
1 row in set (0.00 sec)

mysql> select date(now());
+-------------+
| date(now()) |
+-------------+
| 2025-03-12  |
+-------------+
1 row in set (0.00 sec)

mysql> select date_add(current_date(), interval 100 day);
+--------------------------------------------+
| date_add(current_date(), interval 100 day) |
+--------------------------------------------+
| 2025-06-20                                 |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select datediff(current_date(), '1949-10-01');
+----------------------------------------+
| datediff(current_date(), '1949-10-01') |
+----------------------------------------+
|                                  27556 |
+----------------------------------------+
1 row in set (0.01 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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

案例：

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

mysql> create table if not exists comment (
    -> id int primary key auto_increment,
    -> name varchar(10) not null,
    -> content varchar(50) not null,
    -> sendtime datetime
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc comment;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int         | NO   | PRI | NULL    | auto_increment |
| name     | varchar(10) | NO   |     | NULL    |                |
| content  | varchar(50) | NO   |     | NULL    |                |
| sendtime | datetime    | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> insert into comment (name, content, sendtime) values('zhangsan', '好好学习，天天向上！', now());
Query OK, 1 row affected (0.01 sec)

mysql> insert into comment (name, content, sendtime) values('lisi', 'Hava a good day!', now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into comment (name, content, sendtime) values('John', 'Hello!', now());
Query OK, 1 row affected (0.00 sec)

mysql> select * from comment;
+----+----------+--------------------------------+---------------------+
| id | name     | content                        | sendtime            |
+----+----------+--------------------------------+---------------------+
|  1 | zhangsan | 好好学习，天天向上！             | 2025-03-12 20:18:42 |
|  2 | lisi     | Hava a good day!               | 2025-03-12 20:19:28 |
|  3 | John     | Hello!                         | 2025-03-12 20:19:57 |
+----+----------+--------------------------------+---------------------+
3 rows in set (0.00 sec)

mysql> select * from comment where date_add(sendtime, interval 8 minute)>now();
Empty set (0.00 sec)

-- 10分钟之内的评论
mysql> select * from comment where date_add(sendtime, interval 10 minute)>now();
+----+------+---------+---------------------+
| id | name | content | sendtime            |
+----+------+---------+---------------------+
|  3 | John | Hello!  | 2025-03-12 20:19:57 |
+----+------+---------+---------------------+
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
39
40
41
42
43
44
45
46
47
48
49

# 字符串函数

函数名称	描述
charset(str)	获取字符串使用的字符集
concat(str1, str2 [, …])	获取连接后的字符串
instr(str, substr)	获取substr在str中首次出现的位置，没有出现返回0
ucase(str)	获取转换成大写后的字符串
lcase(str)	获取转换成小写后的字符串
left(str, length)	从字符串的左边开始，向后截取length个字符
length(str)	获取字符串占用的字节数
replace(str, search_str, replace_str)	将字符串中的search_str替换成replace_str
strcmp(str1, str2)	逐字符比较两个字符串的大小
substring(str, position [, length])	从字符串的position开始，向后截取length个字符
ltrim(str)、rtrim(str)、trim(str)	去除字符串的前空格、后空格、前后空格

示例：

-- concat(str1, str2 [, …])
mysql> select * from grades;
+----------+---------+------+---------+
| name     | chinese | math | english |
+----------+---------+------+---------+
| lisi     |      60 |  100 |      29 |
| zhangsan |      23 |   56 |      98 |
+----------+---------+------+---------+
2 rows in set (0.00 sec)

mysql> select concat('姓名：', name, '、语文成绩：', chinese, '、数学成绩：', math, '、英语成绩：', math) as 'show grades' from grades;
+-------------------------------------------------------------------------------+
| show grades                                                                   |
+-------------------------------------------------------------------------------+
| 姓名：lisi、语文成绩：60、数学成绩：100、英语成绩：100                        |
| 姓名：zhangsan、语文成绩：23、数学成绩：56、英语成绩：56                      |
+-------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

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

-- replace(str, search_str, replace_str)
mysql> select * from emp;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)

mysql> select ename, replace(job, 'CLERK', '职员') from emp;
+--------+---------------------------------+
| ename  | replace(job, 'CLERK', '职员')   |
+--------+---------------------------------+
| SMITH  | 职员                            |
| ALLEN  | SALESMAN                        |
| WARD   | SALESMAN                        |
| JONES  | MANAGER                         |
| MARTIN | SALESMAN                        |
| BLAKE  | MANAGER                         |
| CLARK  | MANAGER                         |
| SCOTT  | ANALYST                         |
| KING   | PRESIDENT                       |
| TURNER | SALESMAN                        |
| ADAMS  | 职员                            |
| JAMES  | 职员                            |
| FORD   | ANALYST                         |
| MILLER | 职员                            |
+--------+---------------------------------+
14 rows 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
39
40
41
42

# 数学函数

函数名称	描述
abs(number)	绝对值函数
bin(decimal_number)	十进制转换成二进制
hex(decimal_number)	十进制转换成十六进制
conv(number, from_base, to_base)	from_base进制转换成to_base进制
ceiling(number)	向上取整
floor(number)	向下取整
format(number, n)	格式化，保留n位小数（四舍五入）
rand() 生成随机浮点数，	范围 [0.0, 1.0)
mod(number, denominator)	求余

# 其他函数

user()

mysql> select user();
+--------+
| user() |
+--------+
| cen@   |
+--------+

1
2
3
4
5
6

database()

mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+

1
2
3
4
5
6

password(str)

password函数用于对用户数据进行加密。如下：

mysql> select password('123456');
+-------------------------------------------+
| password('123456')                        |
+-------------------------------------------+
| *D0EB0B0EB0B0EB0B0EB0B0EB0B0EB0B0EB0B0EB |
+-------------------------------------------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7

ifnull(expr1, expr2)

ifnull函数用于判断expr1是否为NULL，如果是NULL则返回expr2，否则返回expr1。

上次更新: 2025/03/14, 11:20:46

← 表的增删查改复合查询→