MySQL | DQL语句-数据处理函数

更新: 3/23/2025 字数: 0 字时长: 0 分钟

MySQL安装教程

sql

mysql> select ename from emp; -- 查询所有员工名字

+--------+
| ename  |
+--------+
| SMITH  |
| ALLEN  |
| WARD   |
| JONES  |
| MARTIN |
| BLAKE  |
| CLARK  |
| SCOTT  |
| KING   |
| TURNER |
| ADAMS  |
| JAMES  |
| FORD   |
| MILLER |
+--------+
14 rows in set (0.00 sec)

关于select语句，我们之前都是这样写：select 字段名 from 表名; 其实，这里的字段名可以看做“变量”，select后面既然可以跟变量，那么可以跟常量吗，尝试一下：

sql

mysql> select 100;

+-----+
| 100 |
+-----+
| 100 |
+-----+
1 row in set (0.00 sec)

mysql> select 100 as ename;

+-------+
| ename |
+-------+
|   100 |
+-------+
1 row in set (0.00 sec)

mysql> select 'abc';

+-----+
| abc |
+-----+
| abc |
+-----+
1 row in set (0.00 sec)

mysql> select abc;
ERROR 1054 (42S22): Unknown column 'abc' in 'field list'

通过以上sql的测试得知，select后面既可以跟变量，又可以跟常量。

以上三条SQL中前两条中100和'abc'都是常量，最后一条SQL的abc没有添加单引号，它会被当做某个表的字段名，因为没有这个字段所以报错。

字符串相关

转大写upper和ucase

sql

# 查询所有员工名字，以大写形式展现
select upper(ename) as ename from emp;

sql

mysql> select upper(ename) as ename from emp;
                                    
+--------+
| ename  |
+--------+
| SMITH  |
| ALLEN  |
| WARD   |
| JONES  |
| MARTIN |
| BLAKE  |
| CLARK  |
| SCOTT  |
| KING   |
| TURNER |
| ADAMS  |
| JAMES  |
| FORD   |
| MILLER |
+--------+
14 rows in set (0.03 sec)

还有一个和upper函数功能相同的函数ucase，也可以转大写，了解一下即可：

sql

# 查询所有员工姓名，以大写形式展现
select ucase(ename) as ename from emp;

sql

mysql> select ucase(ename) as ename from emp;

+--------+
| ename  |
+--------+
| SMITH  |
| ALLEN  |
| WARD   |
| JONES  |
| MARTIN |
| BLAKE  |
| CLARK  |
| SCOTT  |
| KING   |
| TURNER |
| ADAMS  |
| JAMES  |
| FORD   |
| MILLER |
+--------+
14 rows in set (0.00 sec)

sql

# 查询员工smith的岗位、薪资（假如你不知道数据库表中的人名是大写、小写还是大小写混合）
select ename, job, sal from emp where upper(ename) = 'SMITH';

sql

mysql> select ename,job,sal from emp where upper(ename)='SMITH';

+-------+-------+--------+
| ename | job   | sal    |
+-------+-------+--------+
| SMITH | CLERK | 800.00 |
+-------+-------+--------+
1 row in set (0.00 sec)

转小写lower和lcase

很简单，不再赘述，直接上代码：

sql

# 查询员工姓名，以小写形式展现
select lower(ename) as ename from emp;
select lcase(ename) as ename from emp;

sql

mysql> select lower(ename) as ename from emp;

+--------+
| ename  |
+--------+
| smith  |
| allen  |
| ward   |
| jones  |
| martin |
| blake  |
| clark  |
| scott  |
| king   |
| turner |
| adams  |
| james  |
| ford   |
| miller |
+--------+
14 rows in set (0.00 sec)

sql

mysql> select lcase(ename) as ename from emp;

+--------+
| ename  |
+--------+
| smith  |
| allen  |
| ward   |
| jones  |
| martin |
| blake  |
| clark  |
| scott  |
| king   |
| turner |
| adams  |
| james  |
| ford   |
| miller |
+--------+
14 rows in set (0.00 sec)

截取字符串substr

语法：substr('被截取的字符串', 起始下标, 截取长度)

有两种写法：

第一种：substr('被截取的字符串', 起始下标, 截取长度)

第二种：substr('被截取的字符串', 起始下标)，当第三个参数“截取长度”缺失时，截取到字符串末尾

注意：起始下标从1开始，不是从0开始。（1表示从左侧开始的第一个位置，-1表示从右侧开始的第一个位置。）

sql

mysql> select substr('abcdef',1,3);

+----------------------+
| substr('abcdef',1,3) |
+----------------------+
| abc                  |
+----------------------+
1 row in set (0.00 sec)

mysql> select substr('abcdef',2,3);

+----------------------+
| substr('abcdef',2,3) |
+----------------------+
| bcd                  |
+----------------------+
1 row in set (0.00 sec)

mysql> select substr('abcdef',2);

+--------------------+
| substr('abcdef',2) |
+--------------------+
| bcdef              |
+--------------------+
1 row in set (0.00 sec)

练习：找出员工名字中第二个字母是A的

sql

select ename from emp where substr(ename, 2, 1) = 'A';

sql

mysql> select ename from emp where substr(ename,2,1) = 'A';

+--------+
| ename  |
+--------+
| WARD   |
| MARTIN |
| JAMES  |
+--------+
3 rows in set (0.00 sec)

获取字符串长度length

统计字节数，而不是字符数。

sql

select length('你好123');

sql

mysql> select length('你好123');

+-------------------+
| length('你好123') |
+-------------------+
|                 7 |
+-------------------+
1 row in set (0.00 sec)

注意：一个汉字是2个长度。

获取字符的个数char_length

统计字符数，而不是字节数。

sql

select char_length('你好123');

sql

mysql> select char_length('你好123');

+------------------------+
| char_length('你好123') |
+------------------------+
|                      5 |
+------------------------+
1 row in set (0.00 sec)

字符串拼接

语法：concat('字符串1', '字符串2', '字符串3'....)

拼接的字符串数量没有限制。

sql

select concat('abc', 'def', 'xyz');

sql

mysql> select concat('abc','def','xyz');

+---------------------------+
| concat('abc','def','xyz') |
+---------------------------+
| abcdefxyz                 |
+---------------------------+
1 row in set (0.00 sec)

注意：在mysql8之前，双竖线||也是可以完成字符串拼接的。但在mysql8之后，||只作为逻辑运算符，不能再进行字符串拼接了。

sql

select 'abc' || 'def' || 'xyz';

mysql8之后，|| 只作为“或者”运算符，例如：找出工资高于3000或者低于900的员工姓名和薪资：

sql

select ename, sal from emp where sal > 3000 || sal < 900;

sql

mysql> select ename,sal from emp where sal > 3000 || sal < 900;

+-------+---------+
| ename | sal     |
+-------+---------+
| SMITH |  800.00 |
| KING  | 5000.00 |
+-------+---------+
2 rows in set, 1 warning (0.00 sec)

mysql中可以使用+进行字符串的拼接吗？不可以，在mysql中+只作加法运算，在进行加法运算时，会将加号两边的数据尽最大的努力转换成数字再求和，如果无法转换成数字，最终运算结果通通是0

去除字符串前后空白trim

sql

select concat(trim('    abc    '), 'def');

sql

mysql> select concat(trim('    abc    '), 'def');

+------------------------------------+
| concat(trim('    abc    '), 'def') |
+------------------------------------+
| abcdef                             |
+------------------------------------+
1 row in set (0.00 sec)

默认是去除前后空白，也可以去除指定的前缀后缀，例如：

去除前置0

sql

select trim(leading '0' from '000111000');

sql

mysql> select trim(leading '0' from '000111000');

+------------------------------------+
| trim(leading '0' from '000111000') |
+------------------------------------+
| 111000                             |
+------------------------------------+
1 row in set (0.00 sec)

去除后置0

sql

select trim(trailing '0' from '000111000');

sql

mysql> select trim(trailing '0' from '000111000');

+-------------------------------------+
| trim(trailing '0' from '000111000') |
+-------------------------------------+
| 000111                              |
+-------------------------------------+
1 row in set (0.00 sec)

前置0和后置0全部去除

sql

select trim(both '0' from '000111000');

sql

mysql> select trim(trailing '0' from '000111000');

+-------------------------------------+
| trim(trailing '0' from '000111000') |
+-------------------------------------+
| 000111                              |
+-------------------------------------+
1 row in set (0.00 sec)

数字相关

rand()和rand(x)

rand()生成0到1的随机浮点数。

sql

mysql> select rand();

+--------------------+
| rand()             |
+--------------------+
| 0.4465609550909707 |
+--------------------+
1 row in set (0.00 sec)

mysql> select rand();

+--------------------+
| rand()             |
+--------------------+
| 0.3695505944728391 |
+--------------------+
1 row in set (0.00 sec)

rand(x)生成0到1的随机浮点数，通过指定整数x来确定每次获取到相同的浮点值。

sql

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

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

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

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

round(x)和round(x,y)四舍五入

round(x) 四舍五入，保留整数位，舍去所有小数

sql

mysql> select round(9.754);
+--------------+
| round(9.754) |
+--------------+
|           10 |
+--------------+
1 row in set (0.00 sec)

mysql> select round(9.454);
+--------------+
| round(9.454) |
+--------------+
|            9 |
+--------------+
1 row in set (0.00 sec)

round(x,y) 四舍五入，保留y位小数

sql

mysql> select round(9.454,1);
+----------------+
| round(9.454,1) |
+----------------+
|            9.5 |
+----------------+
1 row in set (0.00 sec)

mysql> select round(9.454,0);
+----------------+
| round(9.454,0) |
+----------------+
|              9 |
+----------------+
1 row in set (0.00 sec)

truncate(x, y)舍去

sql

mysql> select truncate(9.999,2);
+-------------------+
| truncate(9.999,2) |
+-------------------+
|              9.99 |
+-------------------+
1 row in set (0.00 sec)

以上SQL表示保留两位小数，剩下的全部舍去。

ceil与floor

数字处理函数除了以上的之外，还有ceil和floor函数：

ceil(x) 向上取整，返回大于或等于x的最小整数。

floor(x) 向下取整，返回小于或等于x的最大整数。

sql

mysql> select ceil(5.3);
+-----------+
| ceil(5.3) |
+-----------+
|         6 |
+-----------+
1 row in set (0.00 sec)

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

空处理

ifnull(x, y)，空处理函数，当x为NULL时，将x当做y处理。

ifnull(comm, 0)，表示如果员工的津贴是NULL时当做0处理。

在SQL语句中，凡是有NULL参与的数学运算，最终的计算结果都是NULL：

sql

mysql> select null + 1;
+----------+
| null + 1 |
+----------+
|     NULL |
+----------+
1 row in set (0.01 sec)

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

mysql> select null * 0;
+----------+
| null * 0 |
+----------+
|     NULL |
+----------+
1 row in set (0.00 sec)

看这样一个需求：查询每个员工的年薪。（年薪 = (月薪 + 津贴) * 12个月。注意：有的员工津贴comm是NULL。）

sql

mysql> select ename,(sal + comm) * 12 as yearsal from emp;

+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |     NULL |
| ALLEN  | 22800.00 |
| WARD   | 21000.00 |
| JONES  |     NULL |
| MARTIN | 31800.00 |
| BLAKE  |     NULL |
| CLARK  |     NULL |
| SCOTT  |     NULL |
| KING   |     NULL |
| TURNER | 18000.00 |
| ADAMS  |     NULL |
| JAMES  |     NULL |
| FORD   |     NULL |
| MILLER |     NULL |
+--------+----------+
14 rows in set (0.00 sec)

以上查询结果中显示SMITH等人的年薪是NULL，这是为什么，这是因为SMITH等人的津贴comm是NULL，有NULL参与的数学运算，最终结果都是NULL，显然这个需要空处理，此时就用到了ifnull函数：

sql

mysql> select ename,(sal + ifnull(comm, 0)) * 12 as yearsal from emp;

+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 22800.00 |
| WARD   | 21000.00 |
| JONES  | 35700.00 |
| MARTIN | 31800.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.00 sec)

日期和时间相关函数

获取当前日期和时间

sql

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2025-03-22 17:56:34 |
+---------------------+
1 row in set (0.00 sec)

mysql> select sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2025-03-22 17:56:38 |
+---------------------+
1 row in set (0.00 sec)

mysql> select now(), sleep(2), sysdate();
+---------------------+----------+---------------------+
| now()               | sleep(2) | sysdate()           |
+---------------------+----------+---------------------+
| 2025-03-22 17:57:14 |        0 | 2025-03-22 17:57:16 |
+---------------------+----------+---------------------+
1 row in set (2.00 sec)

注意：now()和sysdate()的区别：

now()：获取的是执行select语句的时刻。
sysdate()：获取的是执行sysdate()函数的时刻。

获取当前日期

sql

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

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

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

获取当前日期有三种写法，掌握任意一种即可：

curdate()
current_date()
current_date

获取当前时间

sql

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 18:16:27  |
+-----------+
1 row in set (0.01 sec)

mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 18:16:52       |
+----------------+
1 row in set (0.00 sec)

mysql> select current_time;
+--------------+
| current_time |
+--------------+
| 18:16:57     |
+--------------+
1 row in set (0.00 sec)

获取档期时间有三种写法，掌握其中一种即可：

curtime()
current_time()
current_time

获取单独的年、月、日、时、分、秒

sql

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

mysql> select month(now());
+--------------+
| month(now()) |
+--------------+
|            3 |
+--------------+
1 row in set (0.01 sec)

mysql> select day(now());
+------------+
| day(now()) |
+------------+
|         22 |
+------------+
1 row in set (0.01 sec)

mysql> select hour(now());
+-------------+
| hour(now()) |
+-------------+
|          18 |
+-------------+
1 row in set (0.00 sec)

mysql> select minute(now());
+---------------+
| minute(now()) |
+---------------+
|            20 |
+---------------+
1 row in set (0.00 sec)

mysql> select second(now());
+---------------+
| second(now()) |
+---------------+
|            44 |
+---------------+
1 row in set (0.00 sec)

注意：这些函数在使用的时候，需要传递一个日期参数给它，它可以获取到你给定的这个日期相关的年、月、日、时、分、秒的信息。

一次性提取一个给定日期的“年月日”部分，可以使用date()函数，例如：

sql

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

一次性提取一个给定日期的“时分秒”部分，可以使用time()函数，例如：

sql

mysql> select time(now());
+-------------+
| time(now()) |
+-------------+
| 18:28:01    |
+-------------+
1 row in set (0.00 sec)

date_add函数

date_add函数的作用：给指定的日期添加间隔的时间，从而得到一个新的日期。

date_add函数的语法格式：date_add(日期, interval expr 单位)，例如：

以'2025-3-22'为基准，间隔3天之后的日期：'2025-3-25'

sql

mysql> select date_add('2025-3-22', interval 3 day);
+---------------------------------------+
| date_add('2025-3-22', interval 3 day) |
+---------------------------------------+
| 2025-03-25                            |
+---------------------------------------+
1 row in set (0.00 sec)

以'2025-03-22'为基准，间隔3个月之后的日期：'2025-06-22' 详细解释一下这个函数的相关参数：

sql

mysql> select date_add('2025-3-22', interval 3 month);
+-----------------------------------------+
| date_add('2025-3-22', interval 3 month) |
+-----------------------------------------+
| 2025-06-22                              |
+-----------------------------------------+
1 row in set (0.00 sec)

日期：一个日期类型的数据
interval：关键字，翻译为“间隔”，固定写法
expr：指定具体的间隔量，一般是一个数字。也可以为负数，如果为负数，效果和date_sub函数相同。
单位：
- year：年
- month：月
- day：日
- hour：时
- minute：分
- second：秒
- microsecond：微秒（1秒等于1000毫秒，1毫秒等于1000微秒）
- week：周
- quarter：季度

请分析下面这条SQL语句所表达的含义：

sql

mysql> select date_add('2025-3-22 19:16:16', interval -1 microsecond);

+---------------------------------------------------------+
| date_add('2025-3-22 19:16:16', interval -1 microsecond) |
+---------------------------------------------------------+
| 2025-03-22 19:16:15.999999                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

以上SQL表示：以2025-3-22 19:16:16为基准，在这个时间基础上添加-1微秒，也就是减去1微秒。

以上SQL也可以采用date_sub函数完成，例如：

sql

mysql> select date_sub('2025-3-22 19:16:16', interval 1 microsecond);

+--------------------------------------------------------+
| date_sub('2025-3-22 19:16:16', interval 1 microsecond) |
+--------------------------------------------------------+
| 2025-03-22 19:16:15.999999                             |
+--------------------------------------------------------+
1 row in set (0.00 sec)

另外，单位也可以采用复合型单位，例如：

SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND：几分几秒之后
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE：几小时几分之后
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR：几天几小时之后
YEAR_MONTH：几年几个月之后

如果单位采用复合型的话，expr该怎么写呢？例如单位采用：day_hour，假设我要表示3天2小时之后，怎么写？

sql

mysql> select date_add('2025-3-22 19:16:16', interval '3,2' day_hour);

+---------------------------------------------------------+
| date_add('2025-3-22 19:16:16', interval '3,2' day_hour) |
+---------------------------------------------------------+
| 2025-03-25 21:16:16                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

'3,2'这个应该很好理解，表示3天2个小时之后。'3,2'和day_hour是对应的。

date_format日期格式化函数

将日期转换成具有某种格式的日期字符串，通常用在查询操作当中。（date类型转换成char类型）

语法格式：date_format(日期, '日期格式')

该函数有两个参数：

第一个参数：日期。这个参数就是即将要被格式化的日期。类型是date类型。
第二个参数：指定要格式化的格式字符串。
- %Y：四位年份
- %y：两位年份
- %m：月份（1..12）
- %d：日（1..30）
- %H：小时（0..23）
- %i：分（0..59）
- %s：秒（0..59）

例如：获取当前系统时间，让其以这个格式展示：2025-03-22 19:36:58

sql

mysql> select date_format(now(), '%Y-%m-%d %H:%i:%s');

+-----------------------------------------+
| date_format(now(), '%Y-%m-%d %H:%i:%s') |
+-----------------------------------------+
| 2025-03-22 19:36:58                     |
+-----------------------------------------+

注意：在mysql当中，默认的日期格式就是：%Y-%m-%d %H:%i:%s，所以当你直接输出日期数据的时候，会自动转换成该格式的字符串：

sql

mysql> select now();

+---------------------+
| now()               |
+---------------------+
| 2025-03-22 19:41:19 |
+---------------------+
1 row in set (0.00 sec)

str_to_date函数

*该函数的作用是将char类型的日期字符串转换成日期类型date，通常使用在插入和修改操作当中。（char类型转换成date类型）

假设有一个学生表t_student，学生有一个生日的字段，类型是date类型：

sql

drop table if exists t_student;
create table t_student(
  name varchar(255),
  birth date
);
desc t_student;

我们要给这个表插入一条数据：姓名zhangsan，生日85年10月1日，执行以下insert语句：错误原因：日期值不正确。意思是：birth字段需要一个日期，你给的这个字符串'10/01/1985'我识别不了。这种情况下，我们就可以使用str_to_date函数进行类型转换：当然，如果你提供的日期字符串格式能够被mysql解析，str_to_date函数是可以省略的，底层会自动调用该函数进行类型转换：如果日期格式符合以上的几种格式，mysql都会自动进行类型转换的。

dayofweek、dayofmonth、dayofyear函数

dayofweek：一周中的第几天（1~7），周日是1，周六是7。 dayofmonth：一个月中的第几天（1~31） dayofyear：一年中的第几天（1~366）

last_day函数

获取给定日期所在月的最后一天的日期：

datediff函数

计算两个日期之间所差天数：时分秒不算，只计算日期部分相差的天数。

timediff函数

计算两个日期所差时间，例如日期1和日期2所差10:20:30，表示差10小时20分钟30秒。

if函数

如果条件为TRUE则返回“YES”，如果条件为FALSE则返回“NO”：

sql

SELECT IF(500<1000, "YES", "NO");

例如：如果工资高于3000，则输出1，反之则输出0 再例如：如果名字是SMITH的，工资上调10%，其他员工工资正常显示。再例如：工作岗位是MANAGER的工资上调10%，是SALESMAN的工资上调20%，其他岗位工资正常。 上面这个需求也可以使用：case.. when.. then.. when.. then.. else.. end来完成：

cast函数

cast函数用于将值从一种数据类型转换为表达式中指定的另一种数据类型语法：cast(值 as 数据类型) 例如：cast('2020-10-11' as date)，表示将字符串'2020-10-11'转换成日期date类型。在使用cast函数时，可用的数据类型包括：

date：日期类型
time：时间类型
datetime：日期时间类型
signed：有符号的int类型（有符号指的是正数负数）
char：定长字符串类型
decimal：浮点型

加密函数

md5函数，可以将给定的字符串经过md5算法进行加密处理，字符串经过加密之后会生成一个固定长度32位的字符串，md5加密之后的密文通常是不能解密的：

贡献者

XingJi-love

页面历史

最后编辑于 1 天前

查看完整历史

MySQL | DQL语句-数据处理函数 ​

字符串相关 ​

转大写upper和ucase ​

转小写lower和lcase ​

截取字符串substr ​

获取字符串长度length ​

获取字符的个数char_length ​

字符串拼接 ​

去除字符串前后空白trim ​

数字相关 ​

rand()和rand(x) ​

round(x)和round(x,y)四舍五入 ​

truncate(x, y)舍去 ​

ceil与floor ​

空处理 ​

日期和时间相关函数 ​

获取当前日期和时间 ​

获取当前日期 ​

获取当前时间 ​

获取单独的年、月、日、时、分、秒 ​

date_add函数 ​

date_format日期格式化函数 ​

str_to_date函数 ​

dayofweek、dayofmonth、dayofyear函数 ​

last_day函数 ​

datediff函数 ​

timediff函数 ​

if函数 ​

cast函数 ​

加密函数 ​

贡献者