MySQL 函数

select 字段列表 | 表达式 函数

 

1、数学函数

PI()：圆周率;跟book表没关系但表必须有数据因为圆周率函数，跟我这张表里面的任何字段都没有关系，所以在查询过程当中是不需要传入任何参数的

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select * from book;
+------+--------+------------+-------+
| num  | name   | datel      | price |
+------+--------+------------+-------+
|    2 | chenxi | NULL       |    90 |
|    1 | linux  | 2020-04-04 |    99 |
+------+--------+------------+-------+
2 rows in set (0.00 sec)
 
mysql> select distinct pi() from book;
+----------+
| pi()     |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)

FLOOR(x)：返回小于x的最大整数值（去掉小树取整）

?
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
mysql> insert into emp(empmo,ename,sal,comm) values(777,'zhang',2356.54,234.45);
Query OK, 1 row affected (0.01 sec)
mysql> select * from emp;
+-------+--------+-----------+------+---------------------+---------+---------+--------+
| empmo | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1982-12-09 00:00:00 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1983-01-12 00:00:00 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
|   777 | zhang  | NULL      | NULL | NULL                | 2356.54 |  234.45 |   NULL |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
15 rows in set (0.00 sec)
mysql> select floor(sal) from emp where empmo = 777;
+------------+
| floor(sal) |
+------------+
|       2356 |
+------------+
1 row in set (0.00 sec)
mysql> select floor(sal) from emp;
+------------+
| floor(sal) |
+------------+
|        800 |
|       1600 |
|       1250 |
|       2975 |
|       1250 |
|       2850 |
|       2450 |
|       3000 |
|       5000 |
|       1500 |
|       1100 |
|        950 |
|       3000 |
|       1300 |
|       2356 |
+------------+
15 rows in set (0.00 sec)

CEILING(x)：返回大于x的最小整数值（进一取整）

?
1
2
3
4
5
6
7
mysql> select ceiling(comm) from emp where empmo = 777;
+---------------+
| ceiling(comm) |
+---------------+
|           235 |
+---------------+
1 row in set (0.00 sec)

ROUND(x)：返回参数x的四舍五入的有y位小数的值（四舍五入）

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> select comm from emp where empmo = 777;
+--------+
| comm   |
+--------+
| 234.45 |
+--------+
1 row in set (0.00 sec)
 
mysql> select round(comm) from emp where empmo = 777;
+-------------+
| round(comm) |
+-------------+
|         234 |
+-------------+
1 row in set (0.00 sec)
 
mysql> select round(comm,1) from emp where empmo = 777;
+---------------+
| round(comm,1) |
+---------------+
|         234.5 |
+---------------+
1 row in set (0.02 sec)

TRUNCATE(x,y):返回数字x剪短为y位小数的结果

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select comm from emp where empmo=777;
+--------+
| comm   |
+--------+
| 234.45 |
+--------+
1 row in set (0.00 sec)
 
mysql> truncate(comm,1)ct comm from emp where empmo=777;
+------------------+
| truncate(comm,1) |
+------------------+
|            234.4 |
+------------------+
1 row in set (0.00 sec)

聚合函数，也称之为分组函数

AVG(col)：返回指定列的平均值

?
1
2
3
4
5
6
7
mysql> select avg(sal) from emp;
+-------------+
| avg(sal)    |
+-------------+
| 2092.102667 |
+-------------+
1 row in set (0.00 sec)

COUNT(x,y)：返回指定列中非 null 值/行的个数（当函数参数为星号*时不会忽略）

?
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
56
57
mysql> select comm from emp;
+---------+
| comm    |
+---------+
|    NULL |
|  300.00 |
|  500.00 |
|    NULL |
| 1400.00 |
|    NULL |
|    NULL |
|    NULL |
|    NULL |
|    0.00 |
|    NULL |
|    NULL |
|    NULL |
|    NULL |
|  234.45 |
+---------+
15 rows in set (0.00 sec)
mysql> select count(comm) from emp; 统计comm列值不为空的记录
+-------------+
| count(comm) |
+-------------+
|           5 |
+-------------+
1 row in set (0.01 sec)
mysql> select empmo from emp;
+-------+
| empmo |
+-------+
|  7369 |
|  7499 |
|  7521 |
|  7566 |
|  7654 |
|  7698 |
|  7782 |
|  7788 |
|  7839 |
|  7844 |
|  7876 |
|  7900 |
|  7902 |
|  7934 |
|   777 |
+-------+
15 rows in set (0.01 sec)
 
mysql> select count(empmo) from emp;找出多少员工
+--------------+
| count(empmo) |
+--------------+
|           15 |
+--------------+
1 row in set (0.01 sec)

　　

MIN(col)：返回指定列的最小值

?
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
mysql> select * from emp;
+-------+--------+-----------+------+---------------------+---------+---------+--------+
| empmo | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1982-12-09 00:00:00 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1983-01-12 00:00:00 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
|   777 | zhang  | NULL      | NULL | NULL                | 2356.54 |  234.45 |   NULL |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
15 rows in set (0.00 sec)
 
mysql> select min(sal) from emp;
+----------+
| min(sal) |
+----------+
|   800.00 |
+----------+
1 row in set (0.03 sec)

MAX(col)：返回指定列的最大值

?
1
2
3
4
5
6
7
mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+
1 row in set (0.00 sec)

SUM(col)：返回指定列的所有之和（聚合函数）

?
1
2
3
4
5
6
7
mysql> select SUM(sal) from emp;
+----------+
| SUM(sal) |
+----------+
| 31381.54 |
+----------+
1 row in set (0.00 sec)

字符串函数

更新一条数据

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> update emp set ename= '  zhang  ' where empmo = 777;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> update emp set empmo= '7777'rom emp where empmo = 777;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select ename from emp where empmo = 7777;
+-----------+
| ename     |
+-----------+
|   zhang   |
+-----------+
1 row in set (0.01 sec)

LTRIM(str)：去掉字符串str开头的空格

?
1
2
3
4
5
6
7
mysql> select ltrim(ename) from emp where empmo = 7777;
+--------------+
| ltrim(ename) |
+--------------+
| zhang        |
+--------------+
1 row in set (0.00 sec)

RTRIM(str)：去掉字符串str尾部的空格

?
1
2
3
4
5
6
7
mysql> select rtrim (ename) from emp where empmo = 7777;
+---------------+
| rtrim (ename) |
+---------------+
|   zhang       |
+---------------+
1 row in set (0.00 sec)

　　

通过length验证长度。查看是否去掉成功。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql>  select length(rtrim(ename)) from emp where empmo = 7777;
+----------------------+
| length(rtrim(ename)) |
+----------------------+
|                    7 |
+----------------------+
1 row in set (0.00 sec)
mysql> select length(ename) from emp where empmo = 7777;
+---------------+
| length(ename) |
+---------------+
|            10 |
+---------------+
1 row in set (0.01 sec)

TRIM(str)：去掉字符串首部和尾部的所有空格

?
1
2
3
4
5
6
7
mysql> select  trim(ename) from emp where empmo = 7777;
+-------------+
| trim(ename) |
+-------------+
| zhang       |
+-------------+
1 row in set (0.00 sec)

通过length验证长度。查看是否去掉成功。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select length(ename) from emp where empmo = 7777;
+---------------+
| length(ename) |
+---------------+
|            10 |
+---------------+
1 row in set (0.01 sec)
 
mysql> select  length(trim(ename)) from emp where empmo = 7777;
+---------------------+
| length(trim(ename)) |
+---------------------+
|                   5 |
+---------------------+
1 row in set (0.00 sec)

INSERT(str,x,y,instr)：将字符串str从第x位置开始，y个字符长的子字符串instr，返回结果

 SUBSTRING(str,x,y)：截取字符串x开始y个；从第一个截取，截取1个。

?
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
56
57
58
59
60
61
62
63
64
mysql>  select substring(ename,1,1) from emp;
+----------------------+
| substring(ename,1,1) |
+----------------------+
| S                    |
| A                    |
| W                    |
| J                    |
| M                    |
| B                    |
| C                    |
| S                    |
| K                    |
| T                    |
| A                    |
| J                    |
| F                    |
| M                    |
|                      |
+----------------------+
15 rows in set (0.00 sec)
 
mysql> select ename from emp;
+------------+
| ename      |
+------------+
| SMITH      |
| ALLEN      |
| WARD       |
| JONES      |
| MARTIN     |
| BLAKE      |
| CLARK      |
| SCOTT      |
| KING       |
| TURNER     |
| ADAMS      |
| JAMES      |
| FORD       |
| MILLER     |
|   zhang    |
+------------+
15 rows in set (0.00 sec)
 
mysql>  select substring(ename,1,2) from emp;
+----------------------+
| substring(ename,1,2) |
+----------------------+
| SM                   |
| AL                   |
| WA                   |
| JO                   |
| MA                   |
| BL                   |
| CL                   |
| SC                   |
| KI                   |
| TU                   |
| AD                   |
| JA                   |
| FO                   |
| MI                   |
|                      |
+----------------------+

后面的长度可以不写。截取到结尾。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql>  select substring(ename,1) from emp;
+--------------------+
| substring(ename,1) |
+--------------------+
| SMITH              |
| ALLEN              |
| WARD               |
| JONES              |
| MARTIN             |
| BLAKE              |
| CLARK              |
| SCOTT              |
| KING               |
| TURNER             |
| ADAMS              |
| JAMES              |
| FORD                |
| MILLER             |
|   zhang            |
+--------------------+
15 rows in set (0.00 sec)

日期函数

YEAR(date)：返回日期date的年份(1000~9999)

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> select year(hiredate) from emp;
+----------------+
| year(hiredate) |
+----------------+
|           1980 |
|           1981 |
|           1981 |
|           1981 |
|           1981 |
|           1981 |
|           1981 |
|           1982 |
|           1981 |
|           1981 |
|           1983 |
|           1981 |
|           1981 |
|           1982 |
|           NULL |
+----------------+
15 rows in set (0.03 sec)

MONTH(date)：返回date的月份值(1~12)

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql>select month(hiredate) from emp;
+-----------------+
| month(hiredate) |
+-----------------+
|              12 |
|               2 |
|               2 |
|               4 |
|               9 |
|               5 |
|               6 |
|              12 |
|              11 |
|               9 |
|               1 |
|              12 |
|              12 |
|               1 |
|            NULL |
+-----------------+
15 rows in set (0.28 sec)

　　

DAY(date)：返回date 的天数部分

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> select day(hiredate) from emp;
+---------------+
| day(hiredate) |
+---------------+
|            17 |
|            20 |
|            22 |
|             2 |
|            28 |
|             1 |
|             9 |
|             9 |
|            17 |
|             8 |
|            12 |
|             3 |
|             3 |
|            23 |
|          NULL |
+---------------+
15 rows in set (0.00 sec)

　　

HOUR(time)： 返回time的小时值(0~23)

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> select hour(hiredate) from emp;
+----------------+
| hour(hiredate) |
+----------------+
|              0 |
|              0 |
|              0 |
|              0 |
|              0 |
|              0 |
|              0 |
|              0 |
|              0 |
|              0 |
|              0 |
|              0 |
|              0 |
|              0 |
|           NULL |
+----------------+
15 rows in set (0.00 sec)

　　

MINUTE(time)：返回time的分钟值(0~59)

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> select minute(hiredate) from emp;
+------------------+
| minute(hiredate) |
+------------------+
|                0 |
|                0 |
|                0 |
|                0 |
|                0 |
|                0 |
|                0 |
|                0 |
|                0 |
|                0 |
|                0 |
|                0 |
|                0 |
|                0 |
|             NULL |
+------------------+
15 rows in set (0.00 sec)

　　

DATE(datetime）：返回datetime的日期值

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> select date(hiredate) from emp;
+----------------+
| date(hiredate) |
+----------------+
| 1980-12-17     |
| 1981-02-20     |
| 1981-02-22     |
| 1981-04-02     |
| 1981-09-28     |
| 1981-05-01     |
| 1981-06-09     |
| 1982-12-09     |
| 1981-11-17     |
| 1981-09-08     |
| 1983-01-12     |
| 1981-12-03     |
| 1981-12-03     |
| 1982-01-23     |
| NULL           |
+----------------+
15 rows in set (0.00 sec)

　　

TIME(datetime）：返回datetime的时间值

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> select time(hiredate) from emp;
+----------------+
| time(hiredate) |
+----------------+
| 00:00:00       |
| 00:00:00       |
| 00:00:00       |
| 00:00:00       |
| 00:00:00       |
| 00:00:00       |
| 00:00:00       |
| 00:00:00       |
| 00:00:00       |
| 00:00:00       |
| 00:00:00       |
| 00:00:00       |
| 00:00:00       |
| 00:00:00       |
| NULL           |
+----------------+
15 rows in set (0.00 sec)

　　MySQL分组函数

别名:可以给字段表达式和函数还有表起别名

字段别名一个字段

?
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
56
57
58
59
60
61
62
63
64
65
mysql> select ename from emp;
+------------+
| ename      |
+------------+
| SMITH      |
| ALLEN      |
| WARD       |
| JONES      |
| MARTIN     |
| BLAKE      |
| CLARK      |
| SCOTT      |
| KING       |
| TURNER     |
| ADAMS      |
| JAMES      |
| FORD       |
| MILLER     |
|   zhang    |
+------------+
15 rows in set (0.00 sec)
 
mysql> select ename name from emp;
+------------+
| name       |
+------------+
| SMITH      |
| ALLEN      |
| WARD       |
| JONES      |
| MARTIN     |
| BLAKE      |
| CLARK      |
| SCOTT      |
| KING       |
| TURNER     |
| ADAMS      |
| JAMES      |
| FORD       |
| MILLER     |
|   zhang    |
+------------+
15 rows in set (0.00 sec)
 
mysql> select ename nam from emp;
+------------+
| nam        |
+------------+
| SMITH      |
| ALLEN      |
| WARD       |
| JONES      |
| MARTIN     |
| BLAKE      |
| CLARK      |
| SCOTT      |
| KING       |
| TURNER     |
| ADAMS      |
| JAMES      |
| FORD       |
| MILLER     |
|   zhang    |
+------------+
15 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
mysql> select ename nam,sal w from emp;
+------------+---------+
| nam        | w       |
+------------+---------+
| SMITH      |  800.00 |
| ALLEN      | 1600.00 |
| WARD       | 1250.00 |
| JONES      | 2975.00 |
| MARTIN     | 1250.00 |
| BLAKE      | 2850.00 |
| CLARK      | 2450.00 |
| SCOTT      | 3000.00 |
| KING       | 5000.00 |
| TURNER     | 1500.00 |
| ADAMS      | 1100.00 |
| JAMES      |  950.00 |
| FORD       | 3000.00 |
| MILLER     | 1300.00 |
|   zhang    | 2356.54 |
+------------+---------+
15 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
mysql>  select ename name,sal*12 from emp;
+------------+----------+
| name       | sal*12   |
+------------+----------+
| SMITH      |  9600.00 |
| ALLEN      | 19200.00 |
| WARD       | 15000.00 |
| JONES      | 35700.00 |
| MARTIN     | 15000.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 |
|   zhang    | 28278.48 |
+------------+----------+
15 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
mysql> select avg(sal) from emp;
+-------------+
| avg(sal)    |
+-------------+
| 2092.102667 |
+-------------+
1 row in set (0.00 sec)
 
mysql> select avg(sal) avg_sal from emp;
+-------------+
| avg_sal     |
+-------------+
| 2092.102667 |
+-------------+
1 row in set (0.00 sec)
 
mysql> select avg(sal) 'avg wer' from emp;
+-------------+
| avg wer     |
+-------------+
| 2092.102667 |
+-------------+
1 row in set (0.01 sec)
 
mysql> select avg(sal) '平均值' from emp;
+-------------+
| 平均值      |
+-------------+
| 2092.102667 |
+-------------+
1 row in set (0.00 sec)

as关键字可以省略

?
1
2
3
4
5
6
7
mysql> select avg(sal) as  '平均值' from emp;
+-------------+
| 平均值      |
+-------------+
| 2092.102667 |
+-------------+
1 row in set (0.29 sec)

--查询出对应的平均工资

?
1
2
3
4
5
6
7
8
9
10
mysql>  select avg(sal) avg_sal,deptno from emp group by deptno;     --查询出对应部门的平均工资group分组  bye那一个字段
+-------------+--------+
| avg_sal     | deptno |
+-------------+--------+
| 2356.540000 |   NULL |
| 2916.666667 |     10 |
| 2175.000000 |     20 |
| 1566.666667 |     30 |
+-------------+--------+
4 rows in set (0.00 sec)

　　分组工资总和

?
1
2
3
4
5
6
7
8
9
10
mysql> select sum(sal) total_sal,deptno from emp group by deptno;
+-----------+--------+
| total_sal | deptno |
+-----------+--------+
|   2356.54 |   NULL |
|   8750.00 |     10 |
|  10875.00 |     20 |
|   9400.00 |     30 |
+-----------+--------+
4 rows in set (0.00 sec)

求每一个部门中的最大值

 

?
1
2
3
4
5
6
7
8
9
10
mysql> select max(sal) max_sal,deptno from emp group by deptno;
+---------+--------+
| max_sal | deptno |
+---------+--------+
| 2356.54 |   NULL |
| 5000.00 |     10 |
| 3000.00 |     20 |
| 2850.00 |     30 |
+---------+--------+
4 rows in set (0.00 sec)

求每一个部门中的最小值

?
1
2
3
4
5
6
7
8
9
10
mysql> select min(sal) min_sal,deptno from emp group by deptno;
+---------+--------+
| min_sal | deptno |
+---------+--------+
| 2356.54 |   NULL |
| 1300.00 |     10 |
|  800.00 |     20 |
|  950.00 |     30 |
+---------+--------+
4 rows in set (0.00 sec)

求每一个部门中的人数；如果使用分组函数，那么不在分组函数中的字段必须存在于 group by 后面。

?
1
2
3
4
5
6
7
8
9
10
mysql> select count(*) count,deptno from emp group by deptno;
+-------+--------+
| count | deptno |
+-------+--------+
|     1 |   NULL |
|     3 |     10 |
|     5 |     20 |
|     6 |     30 |
+-------+--------+
4 rows in set (0.00 sec)

补充having

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> select avg(sal),deptno from emp group by deptno;
+-------------+--------+
| avg(sal)    | deptno |
+-------------+--------+
| 2356.540000 |   NULL |
| 2916.666667 |     10 |
| 2175.000000 |     20 |
| 1566.666667 |     30 |
+-------------+--------+
4 rows in set (0.01 sec)
 
mysql>  select avg(sal) avg_sal,deptno from emp group by deptno where avg_sal > 2000;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where avg_sal > 2000' at line 1
这个时候注意一下：按照以前的方式，查询的过程当中后面可以做一个筛选条件，但是现在用where做筛选条件的时候出现了上面的错误，原因是因为from是emp这张表，但emp这张表里面并没有avg_sal这一个字段，所以说会出现对应的错误。。。那么这个时候，当我们进行分组查询的时候，再分组之后要做筛选条件的时候，一定要用having
 
mysql> select avg(sal) avg_sal,deptno from emp group by deptno having avg_sal > 2000;
+-------------+--------+
| avg_sal     | deptno |
+-------------+--------+
| 2356.540000 |   NULL |
| 2916.666667 |     10 |
| 2175.000000 |     20 |
+-------------+--------+
3 rows in set (0.04 sec)

HAVING 子句允许指定条件来过滤将出现在最终结果中的分组结果。

WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。

Having：当它把整个的表格进行分组查询完之后，会生成一个新的结果。Having语句的avg_sal是新结果的里面的字段。

所以说在使用的时候如果使用where会去emp表里面查询字段，emp表里面并没有avg_sal字段，而having会去新的里面去查询，这时候有了这个字段就可以进行查询的操作了。

　　

 

posted @   烟雨楼台，行云流水  阅读(178)  评论(0)  编辑  收藏  举报
编辑推荐：
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
阅读排行：
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· 葡萄城 AI 搜索升级：DeepSeek 加持，客户体验更智能
· 什么是nginx的强缓存和协商缓存
· 一文读懂知识蒸馏
历史上的今天：
2019-04-11 docker存储管理
点击右上角即可分享
微信分享提示