高级查询–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) * 每页数量, 每页数量;
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
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)
聚合函数
SUM 求和、max求最大值、min求最小值、avg求平均值、count求数量
select sum(列)/max(列)/min(列)/avg(列)/count(列) from table_name [其他子句];
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 的字段只能是聚合函数
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)
group by,having
group by—对数据进行分组,表中有相同值的分为一组。
group by分组后的查询中，select的列不能出现除了group by 分组条件以及聚合函数外的其他列。
select 列1, 列2, (聚合函数) from table_name group by 列1, 列2;
-- group by 语句通常用于配合聚合函数
select cart_id,goods_no,num,count(*) from cart group by cart_id,goods_no,num;
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)
having是对group by分组后的结果集进行筛选。
select 列1, 列2, (聚合函数) from table_name group by 列1, 列2 having 分组后条件;
select good_no,sum(price) from goods group by good_no having sum(price) > 3;
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)
综合查询
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
select distinct cost,max(category_no) from goods where good_no > 3 group by cost having max(category_no);
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)
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
select distinct cost,max(category_no) from goods where good_no > 3 group by cost having max(category_no);
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)
