SQL数据库高级查询语句--去重，排序，分组查询，聚合函数，分组及综合查询_sql查询中用于排序和去掉重复记录的选项子句

高级查询–distint,order by,limit

#`select`语句中，可以使用`distinct`关键字对查询的结果集进行去重。
select distinct 列1, ... , 列n  from table_name [其他子句];
#`order by`用于对结果进行排序显示，可以使用`ASC`(升序) / `DESC`(降序)两种方式进行排序，可以有多个排序条件
select [distinct] 列1, ... , 列n from table_name [其他子句] order by 排序列1 [DESC], 排序列2 [DESC];
#分页查询limit子句
select * from emp limit 0,2;
#- 第一个参数0是表示从第几条开始查询 (这里 0 是可省略)；第二个参数 表示查询出几条数据
select * from table_name  limit (页码 - 1) * 每页数量, 每页数量;
1
2
3
4
5
6
7
8

select distinct deptname  from dept ;
select * from dept order by deptno desc;-- 部门编号降序， 部门名称降序
select * from dept order by deptno DESC, deptname DESC;
-- ASC升序，DESC降序。默认升序。select * from dept order by deptno;
select * from dept limit 1,3;-- 从第一条开始查三条数据
select * from dept limit 2,3;-- 从第二条数据开始查三条数据
-- n表示页数 s 表示页大小.  limit (n - 1) * s, s
1
2
3
4
5
6
7

insert into dept(deptno, deptname) values(10, '研发部');
Query OK, 1 row affected (0.04 sec)

mysql> insert into dept(deptno, deptname) values(2, '研发部');
Query OK, 1 row affected (0.00 sec)

mysql> select * from dept;
+--------+-------------+
| deptno | deptname    |
+--------+-------------+
|      1 | kfm·研发部 |
|      1 | kfm·人事部 |
|      1 | kfm·宣传部 |
|      5 | kfm·组织部 |
|      6 | kfm·后勤部 |
|      7 | kfm·财务部 |
|     10 | 研发部      |
|      2 | 研发部      |
+--------+-------------+
8 rows in set (0.00 sec)

mysql> select distinct deptname  from dept ;
+-------------+
| deptname    |
+-------------+
| kfm·研发部 |
| kfm·人事部 |
| kfm·宣传部 |
| kfm·组织部 |
| kfm·后勤部 |
| kfm·财务部 |
| 研发部      |
+-------------+
7 rows in set (0.00 sec)

mysql> select * from dept;
+--------+-------------+
| deptno | deptname    |
+--------+-------------+
|      1 | kfm·研发部 |
|      1 | kfm·人事部 |
|      1 | kfm·宣传部 |
|      5 | kfm·组织部 |
|      6 | kfm·后勤部 |
|      7 | kfm·财务部 |
|     10 | 研发部      |
|      2 | 研发部      |
+--------+-------------+
8 rows in set (0.00 sec)

mysql> select * from dept order by deptno desc;
+--------+-------------+
| deptno | deptname    |
+--------+-------------+
|     10 | 研发部      |
|      7 | kfm·财务部 |
|      6 | kfm·后勤部 |
|      5 | kfm·组织部 |
|      2 | 研发部      |
|      1 | kfm·研发部 |
|      1 | kfm·人事部 |
|      1 | kfm·宣传部 |
+--------+-------------+
8 rows in set (0.00 sec)

mysql> select * from dept order by deptno DESC, deptname DESC;
+--------+-------------+
| deptno | deptname    |
+--------+-------------+
|     10 | 研发部      |
|      7 | kfm·财务部 |
|      6 | kfm·后勤部 |
|      5 | kfm·组织部 |
|      2 | 研发部      |
|      1 | kfm·研发部 |
|      1 | kfm·宣传部 |
|      1 | kfm·人事部 |
+--------+-------------+
8 rows in set (0.00 sec)

mysql> select * from dept limit 1,3;
+--------+-------------+
| deptno | deptname    |
+--------+-------------+
|      1 | kfm·人事部 |
|      1 | kfm·宣传部 |
|      5 | kfm·组织部 |
+--------+-------------+
3 rows in set (0.00 sec)

mysql> select * from dept limit 2,3;
+--------+-------------+
| deptno | deptname    |
+--------+-------------+
|      1 | kfm·宣传部 |
|      5 | kfm·组织部 |
|      6 | kfm·后勤部 |
+--------+-------------+
3 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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100

聚合函数

SUM 求和、max求最大值、min求最小值、avg求平均值、count求数量

select sum(列)/max(列)/min(列)/avg(列)/count(列) from table_name [其他子句];
1

select sum(deptno) deptno from dept;
select max(deptno) deptno from dept;
select min(deptno) deptno from dept;
select avg(deptno) deptno from dept;
select count(deptno) deptno from dept;
-- 使用了聚合函数的查询，目前 在select 的字段只能是聚合函数
1
2
3
4
5
6

mysql> select sum(deptno) deptno from dept;
+--------+
| deptno |
+--------+
|     33 |
+--------+
1 row in set (0.00 sec)

mysql> select max(deptno) deptno from dept;
+--------+
| deptno |
+--------+
|     10 |
+--------+
1 row in set (0.00 sec)

mysql> select min(deptno) deptno from dept;
+--------+
| deptno |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

mysql> select avg(deptno) deptno from dept;
+--------+
| deptno |
+--------+
| 4.1250 |
+--------+
1 row in set (0.00 sec)

mysql> select count(deptno) deptno from dept;
+--------+
| deptno |
+--------+
|      8 |
+--------+
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

group by,having

group by—对数据进行分组,表中有相同值的分为一组。

group by分组后的查询中，select的列不能出现除了group by 分组条件以及聚合函数外的其他列。

select 列1, 列2, (聚合函数) from table_name group by 列1, 列2;
-- group by 语句通常用于配合聚合函数
1
2

select cart_id,goods_no,num,count(*) from cart group by cart_id,goods_no,num; 

1
2

mysql> use store;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_store |
+-----------------+
| account         |
| cart            |
| category        |
| goods           |
+-----------------+
4 rows in set (0.00 sec)

mysql> select * from cart;
+---------+----------+------+------------+---------------------+
| cart_id | goods_no | num  | account_id | create_time         |
+---------+----------+------+------------+---------------------+
|       1 |        1 |    2 |          2 | 2023-03-11 19:50:00 |
|       2 |        2 |    2 |          3 | 2023-03-12 19:50:00 |
|       3 |        4 |   10 |          4 | 2023-03-13 19:50:00 |
|       4 |        6 |   20 |          3 | 2023-03-12 19:50:00 |
|       5 |        9 |    2 |          2 | 2023-03-12 17:50:00 |
|       6 |        2 |   10 |          4 | 2023-03-13 17:50:00 |
+---------+----------+------+------------+---------------------+
6 rows in set (0.00 sec)

mysql> select cart_id,goods_no,num,count(*) from cart group by cart_id,goods_no,num;
+---------+----------+------+----------+
| cart_id | goods_no | num  | count(*) |
+---------+----------+------+----------+
|       1 |        1 |    2 |        1 |
|       2 |        2 |    2 |        1 |
|       3 |        4 |   10 |        1 |
|       4 |        6 |   20 |        1 |
|       5 |        9 |    2 |        1 |
|       6 |        2 |   10 |        1 |
+---------+----------+------+----------+
6 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

having是对group by分组后的结果集进行筛选。

select 列1, 列2, (聚合函数) from table_name group by 列1, 列2 having 分组后条件;
1

select good_no,sum(price) from goods group by good_no having sum(price) > 3;
1

mysql> select good_no,sum(price) from goods group by good_no having sum(price) > 3;
+---------+------------+
| good_no | sum(price) |
+---------+------------+
|       1 |          5 |
|       2 |        3.5 |
|       3 |       25.5 |
|       6 |         50 |
|       7 |          8 |
|       8 |       1700 |
|       9 |         50 |
+---------+------------+
7 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

综合查询

SELECT DISTINCT emp.deptno FROM emp JOIN dept ON emp.deptno = dept.deptno WHERE bridate >= '2000-01-01' GROUP BY emp.deptno HAVING count(*) >= 2 ORDER BY count(*) DESC 
1

select distinct cost,max(category_no) from goods where good_no > 3 group by cost having max(category_no);
1

mysql> select distinct cost,max(category_no) from goods where good_no > 3 group by cost having max(category_no);
+------+------------------+
| cost | max(category_no) |
+------+------------------+
|  0.8 |                2 |
|  0.2 |                2 |
|   30 |                3 |
|    5 |                3 |
| 1350 |                5 |
|   40 |                4 |
+------+------------------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

rows in set (0.00 sec)

### 综合查询

```mysql
SELECT DISTINCT emp.deptno FROM emp JOIN dept ON emp.deptno = dept.deptno WHERE bridate >= '2000-01-01' GROUP BY emp.deptno HAVING count(*) >= 2 ORDER BY count(*) DESC 
1
2
3
4
5

select distinct cost,max(category_no) from goods where good_no > 3 group by cost having max(category_no);
1

mysql> select distinct cost,max(category_no) from goods where good_no > 3 group by cost having max(category_no);
+------+------------------+
| cost | max(category_no) |
+------+------------------+
|  0.8 |                2 |
|  0.2 |                2 |
|   30 |                3 |
|    5 |                3 |
| 1350 |                5 |
|   40 |                4 |
+------+------------------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12