追光学院
Yang Liangxi
2023-03-05

函数

# 单行处理函数

数据处理函数又被称为单行处理函数，特点是一个输入对应一个输出。

student 案例显示的数据如下所示：

mysql> insert into student values(7,'WANGGONG',35,85,'男','1943-01-01 00:00:00',88,89,NULL,'三年级3班');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+------+----------+------+---------+--------+---------------------+------+---------+---------+-----------+
| id   | name     | age  | chinese | gender | birthday            | math | english | address | grade     |
+------+----------+------+---------+--------+---------------------+------+---------+---------+-----------+
|    1 | 王维     |   33 |     100 | 男     | 1923-01-01 00:00:00 |   98 |      87 | 天津    | 三年级1班 |
|    2 | 李白     |   43 |      99 | 女     | 1913-01-01 00:00:00 |   88 |      89 | 上海    | 三年级2班 |
|    3 | 杜甫     |   34 |      98 | 男     | 1929-01-01 00:00:00 |   96 |      79 | 天津    | 三年级3班 |
|    4 | 郭靖     |   36 |      97 | 男     | 1923-01-01 00:00:00 |   96 |      99 | 北京    | 三年级1班 |
|    5 | 王良     |   66 |      97 | 女     | 1943-01-01 00:00:00 |   90 |      88 | 天津    | 三年级3班 |
|    6 | 刘邦     |   46 |      95 | 男     | 1953-01-01 00:00:00 |   93 |      88 | NULL    | 三年级2班 |
|    7 | WANGGONG |   35 |      85 | 男     | 1943-01-01 00:00:00 |   88 |      89 | NULL    | 三年级3班 |
+------+----------+------+---------+--------+---------------------+------+---------+---------+-----------+
7 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# lower 转成小写函数

mysql> select lower(name) as newname from student;
+----------+
| newname  |
+----------+
| 王维     |
| 李白     |
| 杜甫     |
| 郭靖     |
| 王良     |
| 刘邦     |
| wanggong |
+----------+
7 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

# upper 转成大写函数

mysql> select upper(name) as newname from student;
+----------+
| newname  |
+----------+
| 王维     |
| 李白     |
| 杜甫     |
| 郭靖     |
| 王良     |
| 刘邦     |
| WANGGONG |
+----------+
7 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

# substr 取子串函数(字段，起始，长度)

mysql> select substr(name,1,1) as newname from student;
+---------+
| newname |
+---------+
| 王      |
| 李      |
| 杜      |
| 郭      |
| 王      |
| 刘      |
| W       |
+---------+
7 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select * from student where substr(name,1,1) = '王';
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
| id   | name | age  | chinese | gender | birthday            | math | english | address | grade     |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
|    1 | 王维 |   33 |     100 | 男     | 1923-01-01 00:00:00 |   98 |      87 | 天津    | 三年级1班 |
|    5 | 王良 |   66 |      97 | 女     | 1943-01-01 00:00:00 |   90 |      88 | 天津    | 三年级3班 |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

上面的办法等价于下面的办法

mysql> select * from student where name like '王%';
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
| id   | name | age  | chinese | gender | birthday            | math | english | address | grade     |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
|    1 | 王维 |   33 |     100 | 男     | 1923-01-01 00:00:00 |   98 |      87 | 天津    | 三年级1班 |
|    5 | 王良 |   66 |      97 | 女     | 1943-01-01 00:00:00 |   90 |      88 | 天津    | 三年级3班 |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

# length 长度函数

mysql> select length(name) newname from student;
+---------+
| newname |
+---------+
|       6 |
|       6 |
|       6 |
|       6 |
|       6 |
|       6 |
|       8 |
+---------+
7 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

student 案例显示的数据如下所示：

mysql>  insert into student values(7,'WANG GONG',35,85,'男','1943-01-01 00:00:00',88,89,NULL,'三年级3班');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+------+-----------+------+---------+--------+---------------------+------+---------+---------+-----------+
| id   | name      | age  | chinese | gender | birthday            | math | english | address | grade     |
+------+-----------+------+---------+--------+---------------------+------+---------+---------+-----------+
|    1 | 王维      |   33 |     100 | 男     | 1923-01-01 00:00:00 |   98 |      87 | 天津    | 三年级1班 |
|    2 | 李白      |   43 |      99 | 女     | 1913-01-01 00:00:00 |   88 |      89 | 上海    | 三年级2班 |
|    3 | 杜甫      |   34 |      98 | 男     | 1929-01-01 00:00:00 |   96 |      79 | 天津    | 三年级3班 |
|    4 | 郭靖      |   36 |      97 | 男     | 1923-01-01 00:00:00 |   96 |      99 | 北京    | 三年级1班 |
|    5 | 王良      |   66 |      97 | 女     | 1943-01-01 00:00:00 |   90 |      88 | 天津    | 三年级3班 |
|    6 | 刘邦      |   46 |      95 | 男     | 1953-01-01 00:00:00 |   93 |      88 | NULL    | 三年级2班 |
|    7 | WANGGONG  |   35 |      85 | 男     | 1943-01-01 00:00:00 |   88 |      89 | NULL    | 三年级3班 |
|    7 | WANG GONG |   35 |      85 | 男     | 1943-01-01 00:00:00 |   88 |      89 | NULL    | 三年级3班 |
+------+-----------+------+---------+--------+---------------------+------+---------+---------+-----------+
8 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# trim 忽略空格函数

mysql> select * from student where name = trim(' WANG GONG ');
+------+-----------+------+---------+--------+---------------------+------+---------+---------+-----------+
| id   | name      | age  | chinese | gender | birthday            | math | english | address | grade     |
+------+-----------+------+---------+--------+---------------------+------+---------+---------+-----------+
|    7 | WANG GONG |   35 |      85 | 男     | 1943-01-01 00:00:00 |   88 |      89 | NULL    | 三年级3班 |
+------+-----------+------+---------+--------+---------------------+------+---------+---------+-----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

# concat 连接函数

mysql> select concat(name,age) name_age from student;
+-------------+
| name_age    |
+-------------+
| 王维33      |
| 李白43      |
| 杜甫34      |
| 郭靖36      |
| 王良66      |
| 刘邦46      |
| WANGGONG35  |
| WANG GONG35 |
+-------------+
8 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# round 四舍五入函数(字段，num) num 可以取正数 负数 0

num 为 1 代表精确到小数点后一位小数

mysql> select round(chinese,1) from student;
+------------------+
| round(chinese,1) |
+------------------+
|            100.0 |
|             99.0 |
|             98.0 |
|             97.0 |
|             97.0 |
|             95.0 |
|             85.0 |
|             85.0 |
+------------------+
8 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
num 为 -1 代表整数最后一位四舍五入

mysql> select round(chinese,-1) from student;
+-------------------+
| round(chinese,-1) |
+-------------------+
|               100 |
|               100 |
|               100 |
|               100 |
|               100 |
|               100 |
|                80 |
|                80 |
+-------------------+
8 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

round 是四舍五入函数，0 保持整数，-1 整数部分四舍五入，1 保留一个小数位。

# rand 取随机数函数

mysql> select rand() from student;
+---------------------+
| rand()              |
+---------------------+
|  0.9280243384912836 |
|  0.4720301930532141 |
|  0.5760779805258642 |
|  0.4642993542033242 |
|   0.593262860172673 |
|  0.5734162689870375 |
| 0.08729279515081345 |
|  0.7162151995265998 |
+---------------------+
8 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select round(rand()*100,0) from student;
+---------------------+
| round(rand()*100,0) |
+---------------------+
|                  32 |
|                  45 |
|                  28 |
|                   5 |
|                  43 |
|                  99 |
|                  67 |
|                  36 |
+---------------------+
8 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# ifnull 是空函数

student 案例显示的数据如下所示：

mysql> update student set chinese =null where id=7;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from student ;
+------+-----------+------+---------+--------+---------------------+------+---------+---------+-----------+
| id   | name      | age  | chinese | gender | birthday            | math | english | address | grade     |
+------+-----------+------+---------+--------+---------------------+------+---------+---------+-----------+
|    1 | 王维      |   33 |     100 | 男     | 1923-01-01 00:00:00 |   98 |      87 | 天津    | 三年级1班 |
|    2 | 李白      |   43 |      99 | 女     | 1913-01-01 00:00:00 |   88 |      89 | 上海    | 三年级2班 |
|    3 | 杜甫      |   34 |      98 | 男     | 1929-01-01 00:00:00 |   96 |      79 | 天津    | 三年级3班 |
|    4 | 郭靖      |   36 |      97 | 男     | 1923-01-01 00:00:00 |   96 |      99 | 北京    | 三年级1班 |
|    5 | 王良      |   66 |      97 | 女     | 1943-01-01 00:00:00 |   90 |      88 | 天津    | 三年级3班 |
|    6 | 刘邦      |   46 |      95 | 男     | 1953-01-01 00:00:00 |   93 |      88 | NULL    | 三年级2班 |
|    7 | WANGGONG  |   35 |    NULL | 男     | 1943-01-01 00:00:00 |   88 |      89 | NULL    | 三年级3班 |
|    7 | WANG GONG |   35 |    NULL | 男     | 1943-01-01 00:00:00 |   88 |      89 | NULL    | 三年级3班 |
+------+-----------+------+---------+--------+---------------------+------+---------+---------+-----------+
8 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select ifnull(chinese,0)+ math as result from student;
+--------+
| result |
+--------+
|    198 |
|    187 |
|    194 |
|    193 |
|    187 |
|    188 |
|     88 |
|     88 |
+--------+
8 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

ifnull 是将 null 转成一个数字然后参与运算。否则 null 和任何数运算结果都是 null。

# case ... when ... then ... when ... then ... else ... end

mysql> select name, (case address when '天津' then math*1.1 when '上海' then math*1.5 else math end) as newmath from stu
dent;
+-----------+---------+
| name      | newmath |
+-----------+---------+
| 王维      |   107.8 |
| 李白      |   132.0 |
| 杜甫      |   105.6 |
| 郭靖      |      96 |
| 王良      |    99.0 |
| 刘邦      |      93 |
| WANGGONG  |      88 |
| WANG GONG |      88 |
+-----------+---------+
8 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

case ... when ... then ... when ... then ... else ... end。

# format函数(字段，格式)

mysql> select name,format(chinese,'$999.000') as chinese1 from student;
+-----------+----------+
| name      | chinese1 |
+-----------+----------+
| 王维      | 100      |
| 李白      | 99       |
| 杜甫      | 98       |
| 郭靖      | 97       |
| 王良      | 97       |
| 刘邦      | 95       |
| WANGGONG  | NULL     |
| WANG GONG | NULL     |
+-----------+----------+
8 rows in set, 8 warnings (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# str_to_date 函数(日期字符串，格式)

mysql> insert into student values(9,'lili',33,99,'m','1943-01-01 00:00:00',55,66,'tianjin','sannianji1ban');
Query OK, 1 row affected (0.00 sec)
1
2

1943-01-01 00:00:00 标准日期字符串就不用使用 str_to_date 函数进行转换。否则就需要转换。

str_to_date ('1943-01-01 00:00:00','%Y-%m-%d %h:%i:%s') 年月日时分秒对应的格式。

# date_format 函数(字段，格式)

mysql> select name,date_format(birthday,'%Y/%m/%d %h-%i-%s') from student;
+-----------+-------------------------------------------+
| name      | date_format(birthday,'%Y/%m/%d %h-%i-%s') |
+-----------+-------------------------------------------+
| 王维      | 1923/01/01 12-00-00                       |
| 李白      | 1913/01/01 12-00-00                       |
| 杜甫      | 1929/01/01 12-00-00                       |
| 郭靖      | 1923/01/01 12-00-00                       |
| 王良      | 1943/01/01 12-00-00                       |
| 刘邦      | 1953/01/01 12-00-00                       |
| WANGGONG  | 1943/01/01 12-00-00                       |
| WANG GONG | 1943/01/01 12-00-00                       |
| lili      | 1943/01/01 12-00-00                       |
+-----------+-------------------------------------------+
9 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# now函数

now()函数获取系统当前时间。是datetime类型的时间数据。
1

# 多行处理函数

分组函数又被称为多行处理函数，特点是多个输入对应一个输出。

# max 函数

mysql> select max(math) from student;
+-----------+
| max(math) |
+-----------+
|        98 |
+-----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

# min 函数

mysql> select min(math) from student;
+-----------+
| min(math) |
+-----------+
|        88 |
+-----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

# sum 函数

mysql> select sum(math) from student;
+-----------+
| sum(math) |
+-----------+
|       737 |
+-----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

# avg 函数

mysql> select avg(math) from student;
+-----------+
| avg(math) |
+-----------+
|   92.1250 |
+-----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

# count 函数

mysql> select count(name) from student;
+-------------+
| count(name) |
+-------------+
|           8 |
+-------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

分组函数在使用的时候，自动忽略 null。

count(*)统计表的总行数。

count(字段)统计该字段下所有不为 null 的元素总数。

分组函数不能直接使用在 where子句中。

所有的分组函数可以组合在一起使用。

mysql> select count(*),max(math),min(chinese),avg(age),sum(age) from student;
+----------+-----------+--------------+----------+----------+
| count(*) | max(math) | min(chinese) | avg(age) | sum(age) |
+----------+-----------+--------------+----------+----------+
|        8 |        98 |           95 |  41.0000 |      328 |
+----------+-----------+--------------+----------+----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
编辑 (opens new window)
#MYSQL
上次更新: 2025/02/10, 20:20:37

← 分组查询→

最近更新
01
Git问题集合
01-29
02
安装 Nginx 服务器
01-25
03
安装 Docker 容器
01-25
更多文章>
冀ICP备2025101965号
vdoing 支持|copyright©2021-2025 Yang Liangxi
×
×