SQL查询(二)——排序、聚合、分组查询_sql查询先通过名称分组再通过类型分组

数据查询语言笔记：
SQL查询(一)——简单的查询
SQL查询(二)——排序、聚合、分组查询
SQL查询(三)——多表查询
SQL语句练习题

---

  在没有子查询情况下，各个关键字的顺序，即分组排序同时出现会先分组再排序

SELECT [distinct] 列名 FROM 表1 join 表2 on 表1.Key=表2.Key 
where 条件1 group by 列名 having 条件2 order by 列名 limit 参数;
1
2

---

4. 排序查询

  作用：将满足查询条件的记录按照某些列进行排序

语法规则：

-- 单列排序
SELECT 需要显示的列名列表 FROM 表名 ORDER BY 需要排序的列名 ASC/DESC
-- 多列排序
SELECT 需要显示的列名列表 FROM 表名 
ORDER BY 需要排序的列名1 ASC/DESC,需要排序的列名2 ASC/DESC,……
-- 加上where查询条件的排序，先查询再排序
SELECT 需要显示的列名列表 FROM 表名 WHERE 查询条件 
ORDER BY 需要排序的列名 ASC/DESC
1
2
3
4
5
6
7
8

• 排序查询包含where查询条件时，先根据where查询符合查询条件的记录，然后order by将符合where查询条件的记录进行排序
• asc表示升序排序，desc表示降序排序
• 可以按照多个列排序，并且每个列的可按照不同的排序规则排序
• 排序时NULL视为比最小的数据还小

代码演示：

• 单列排序：

-- 从goods表中查询market_price>1000的记录，并将这些记录按照click_count值降序排序
SELECT * FROM goods WHERE market_price>1000 ORDER BY click_count DESC;
1
2

• 多列排序：

1. 先按前一个列排序，在前一个列的值相同的情况下，按照下一个列排序，
2. 两个列排序规则可以不同，即升降序不同

-- 从goods表中查询market_price>1000的记录，并将这些记录按照cat_id升序顺序排序，
-- 当cat_id相同时，按照click_count降序顺序排序
SELECT goods_id,goods_name,cat_id,market_price,click_count 
FROM goods WHERE market_price>1000 ORDER BY cat_id ASC,click_count DESC;
1
2
3
4

5. 聚合查询

  聚合查询用于统计一些数据时使用，比如有多少记录，某列平均值，某列最大最小值等。
  聚合查询中常使用的聚合函数：sum()，avg()，max()，min()，count()。聚合查询这里经常和分组查询一起使用

语法规则：

• sum(col_name)函数：返回数值列col_name的和
• avg(col_name)函数： 返回数值列col_name的平均值
• min(col_name)函数： 返回指定列col_name的最小值
• max(col_name)函数：返回指定列col_name的最大值
• count()函数： 返回匹配指定条件的行数(重要考点)

注意：

1. count()中参数可以是列名、*、常数
2. sum()和avg()的参数只能是数值列。

代码演示：

  1. sum()、avg()、max()、min()的演示

-- 对goods表的所有记录统计所有记录的总的点击量、平均点击量、最高点击量和最低点量
SELECT SUM(click_count) AS '总点击量',AVG(click_count) AS '平均点击',
MAX(click_count) AS '最高点击量',MIN(click_count) AS '最低点击量' 
FROM goods;

-- 对goods表的所有market_price>1000的记录统计总的点击量、平均点击量、最高点击量
SELECT SUM(click_count) AS '总点击量' ,AVG(click_count) AS '平均点击', 
MAX(click_count) AS '最高点击量', MIN(click_count) AS '最低点击量' 
FROM goods WHERE market_price>1000;
1
2
3
4
5
6
7
8
9

  2. count()的演示

-- 使用所有列统计数目
SELECT COUNT(*) AS '商品数目' FROM goods;
-- 使用主键列统计数目
SELECT COUNT(goods_id) AS '商品数目' FROM goods;
-- 使用常量列(自己建的)统计数目
SELECT COUNT(1) AS '商品数目' FROM goods;
-- 使用其他列统计数目(不推荐！)
SELECT COUNT(market_price) AS '商品数目' FROM goods;
1
2
3
4
5
6
7
8

  在测试数据中上面四种方法结果都是相同的，但是需要说一下在统计表中总记录数目的时候，第四种方法很不推荐，因为count()在统计时是不会统计NULL值的，而第四种方法使用其他列统计的时候很有可能会碰到有NULL值的列，这时统计出来的结果是该列值不为NULL的记录数目，而不是总记录数目了。想要使用第四种方法统计总记录数目的前提是明确该列没有NULL值。
  第二种方法使用的列是主键列，主键必定不可能有NULL值
  第三种方法使用的列是自定义常量列，每条记录该列都是我们自定义的值，也没有NULL值

PS：了解一下前三种方法的效率问题，第1、3种方法效率差不多，都是最好的，如果要再细分的话，一般情况认为第3种最好。

6. 分组查询

6.1 只分组

语法规则：

-- 将表按照col列的值分组
SELECT 想要显示的列 FROM 表 group by col列;
1
2

代码演示：

SELECT 想要显示的列 FROM goods group by cat_id;
1

  根据cat_id列进行了分组，cat_id值相同的记录分到一组，可以看到只显示每组第一条记录。因此SELECT后面能放的列名是分组的列名和下面要讲的聚合函数，其他的列即便显示了也不能使用，因为只显示了该组中第一条数据，该组其他数据是没办法得到的

6.2 分组+聚合

  我们很少会单纯的进行分组，往往会先分组，然后统计每组的信息。例如，现在有一个学生信息的记录，我们希望知道男女各多少人，可以按照性别分组，然后男女组内进行count()统计

语法规则：

-- 按照col列分组，并且统计每组中记录的数目
SELECT 想要显示的列 聚合函数() FROM 表名 GROUP BY col列;
1
2

代码演示：

SELECT cat_id '栏目类别',count(*) '该组商品数目',max(market_price) '该组下最高价格' 
FROM goods group by cat_id;
1
2

-- 分组+聚合+排序
-- 将goods表按照cat_id分组，聚合每组的记录数目和每组market_price的最高值，
-- 然后按照cat_id升序顺序排序
SELECT * FROM (SELECT goods.cat_id '栏目类别',count(*) '该组商品数目',
max(goods.market_price) '该组下最高价格' 
FROM goods group by cat_id) s ORDER BY s.栏目类别;
1
2
3
4
5
6

6.3 先筛选再分组

语法规则：

-- 将表中满足筛选条件的记录按照col列分组
SELECT 想要显示的列 FROM 表 WHERE 筛选条件 GROUP BY col列
1
2

代码演示：

SELECT cat_id,COUNT(1) FROM goods WHERE cat_id > 5 GROUP BY cat_id;
1

6.4 先分组再筛选

语法规则：

-- 显示goods中按照col列分组后满足筛选条件的组
SELECT 想要显示的列 FROM 表 GROUP BY col列 HAVING 筛选条件;
1
2

代码演示：

-- 显示goods按照cat_id分组之后组内记录数目大于1条的组的cat_id和组内记录数目
 SELECT cat_id,COUNT(1) FROM goods GROUP BY cat_id HAVING COUNT(1)>1;
1
2

小结

  分组的难点在于组合使用，比如先表中筛选、再分组、再组筛选，或者分组和排序、聚合的组合使用等