SQL基础——DQL语法2

DQL之排序查询

 排序查询: 就是按照指定字段的大小进行排序, 排序规则分为 升序和降序
 升序(ASC) : 从小到大依次递增
 降序(DESC) : 从大到小依次递减

 关键字: order by
 格式: select 列... from 表 where 条件 order by 排序规则 [ASC|DESC];

-- 0.使用数据库
USE bigdata_db;

-- 需求1: 将所有的商品按照价格从小到大的顺序进行排序操作
-- 排序规则: price  ASC
SELECT *
FROM
    product
ORDER BY
    price ASC; -- 在书写排序规则时, 默认规则为升序此处可以省略ASC
SELECT *
FROM
    product
ORDER BY
    price;

-- 需求2: 将所有商品中名字是三个字的商品筛选出来,并按照价格降序排列
-- 筛选条件: pname like '___'  排序规则: price DESC
SELECT *
FROM
    product
WHERE
    pname LIKE '___'
ORDER BY
    price DESC;

-- 注意: 在书写代码时, 先书写where 条件, 在书写 order by 排序, 同时,执行顺序也如此, 先筛选出需要部分,再对这一部分进行排序.


-- 需求3: 将所有的商品按照价格升序排列,如果价格相同,则按照品类降序排列
-- 排序规则: price ASC category_id DESC
SELECT *
FROM
    product
ORDER BY
    price, category_id DESC;

-- 解释: 对于所有商品按照商品类别降序排列, 如果类别相同,则按照价格升序排列.
SELECT *
FROM
    product
ORDER BY
    category_id DESC, price;

-- 结论: 当排序字段为多个字段时, 先按照第一个字段进行排序,如果字段值相同,再按照第二个字段进行排序,如果第二个也相同则使用第三个,以此类推

DQL之聚合查询

多个数据进行计算,最终变为一个数据的计算方式叫做聚合计算

例如: 全班同学统计平均年龄, 全班同学的年龄(n个) >>> 平均年龄(1个)

 sql中常用的聚合函数:

max 最大值
min 最小值
avg 平均值
count 计数
sum 求和

 

-- 聚合查询
/*
max 最大值
min 最小值
avg 平均值
count 计数
sum 求和
 */

-- 需求1: 获取所有商品中的最大价格
SELECT max(price) FROM product;


-- 需求2: 所有商品中价格大于200元的商品的最小价格
-- 过滤条件: price > 200  聚合规则: min(price)
SELECT min(price) FROM product WHERE price > 200;

-- 需求3: 获取所有c001品类商品的平均价格
-- 过滤条件: category_id = c001  聚合规则: avg(price)
SELECT avg(price) FROM product WHERE category_id = 'c001';


-- 需求4: 获取所有c002品类商品的价格总和
-- 过滤条件: category_id = c002  聚合规则: sum(price)
SELECT sum(price) FROM product WHERE category_id = 'c002';


-- 需求5:获取当前表中一共有几件商品
-- 聚合规则: count(pid)
SELECT count(pid) FROM product;
SELECT count(*) FROM product;  -- 13

-- 注意: null (空值) 不参与聚合函数的计算
SELECT count(price) FROM product;  -- 12

-- 举例: 我们有三个同学, 分别拥有 2个苹果  4 个苹果    null (空值)
-- 求每个人平均有多少个苹果: 3个     (2 + 4)/ 2    null值不参与运算

-- 举例: 我们有三个同学, 分别拥有 2个苹果  4 个苹果    0个苹果
-- 求每个人平均有多少个苹果: 2个     (2 + 4 + 0)/ 3   0参与运算

 DQL之分组查询

分组的意义就是按照指定的字段,将数据分为若干组

举例: 按照班级学员的性别进行分组, 可以分为 男生组 和女生组 因为性别字段仅有两种值 (男 和 女)

通常情况下,分组是和聚合搭配使用的,没有聚合的分组没有什么意义

 分组查询 : 就是按照指定字段的值的种类,将数据记录分配到不同的分组之中, 一般后续会进行按照组的聚合计算
 关键字 : group by
 格式 : select 分组字段, 聚合函数  from 表名 group by 分组字段;
 完整格式 : select 分组字段, 聚合函数  from 表名 where 条件 group by 分组字段 order by 排序规则;

-- 需求1: 统计(按照拼配进行分组)每一个品类的商品各有(count)多少个
-- 分组规则: category_id  聚合方式:  count (pid)
SELECT
    category_id,
    COUNT(pid)
FROM
    product
GROUP BY
    category_id;

-- 需求2: 请列出所有品类的编号
SELECT
    category_id
FROM
    product
GROUP BY
    category_id;

-- 需求3: 获取所有品类商品中 每个品类  最大价格
-- 分组规则: category_id  聚合方式: max(price)
SELECT
    category_id,
    MAX(price)
FROM
    product
GROUP BY
    category_id;


-- 需求4: 查询每种品类每个价格各有几件商品
-- 分组规则: 按照品类和价格进行分组 (category_id , price)  聚合: count(pid)
SELECT
    category_id,
    price,
    COUNT(pid)
FROM
    product
GROUP BY
    category_id, price;

-- 结论: 当我们的分组字段中有多个字段名称时, 两个字段完全相同,则分到同一组. 否则分为两个不同的组.
-- 举例: 按照班级编号和性别, 将学员信息进行分组, 分为: 1班男生,  1班女生 , 2班男生, 2班女生 ....

-- 注意1: 在分组查询中, select 中选择的字段,不能是除了分组字段外的其他字段
-- 需求5: 获取所有商品中 每个类别的商品名称和商品总数
SELECT category_id, pname, COUNT(*)
FROM product
GROUP BY category_id;

-- 此处分组查询后,并不能获得所有的商品, 仅保留该分组的第一条数据记录,此时数据存在缺失,数据不正确,所以不能使用除分组字段外的其他字段,
-- 但是聚合后,数据变为一个,可以和分组字段意义对应,能够使用聚合函数
-- 结论: 在分组后,select 后只能使用分组字段和聚合函数

-- 扩展 : 如果我向获得所有的商品名称,可以借助聚合函数事项 group_concat
SELECT category_id, GROUP_CONCAT(pname), COUNT(*)
FROM product
GROUP BY category_id;


-- 注意2: 在开发中where 条件筛选, 要使用在 group by 之前, order by 要用在group by 之后
-- 需求6: 获取所有商品中除了 c005 品类之外其他品类 每个品类商品的平均价格,并按照价格平均值排序 降序
-- 分析 : 筛选条件 : 品类 != c005 分组规则 category_id 聚合规则 avg  排序规则 avg  降序
SELECT category_id, AVG(price)
FROM product
WHERE category_id != 'c005'
GROUP BY category_id
ORDER BY AVG(price) DESC;

分组后筛选数据使用having

 

-- 需求7: 获取所有商品中商品数量大于2个的品类 每个品类商品的平均价格,并按照价格平均值排序 降序
-- 筛选条件: count(pid) > 2  分组规则: category_id  聚合规则: avg(price)  排序规则: avg(price) desc
/*SELECT
    category_id,
    AVG(price)
FROM
    product
WHERE
    COUNT(pid) > 2  -- Aggregate calls are not allowed here  聚合函数不允许在 where处调用
GROUP BY
    category_id
ORDER BY
    AVG(price) DESC;*/

-- where 条件语句中不能使用聚合函数
-- 因为 where 是在 group by 之前调用的, 此时尚未分组, 所以不能按组聚合.
-- 如果想要在分组之后进行数据筛选, 需要使用having

-- 筛选条件: count(pid) > 2  分组规则: category_id  聚合规则: avg(price)  排序规则: avg(price) desc
-- 完整格式 : select 分组字段, 聚合函数  from 表名 where 分组前筛选条件 group by 分组字段 having 分组后筛选条件 order by 排序规则;
SELECT
    category_id,
    COUNT(pid),
    AVG(price)
FROM
    product
GROUP BY
    category_id
HAVING
    COUNT(pid) > 2
ORDER BY
    AVG(price) DESC
;

-- 需求8: 获取所有商品中高于200元的商品数量大于2个的品类 每个品类商品的平均价格,并按照价格平均值排序 降序
-- 筛选条件: price > 200 , count(pid) > 2 分组规则: category_id  聚合规则: avg(price)  排序规则: avg(price) desc

-- 步骤1: 按照商品品类进行分组, 求平均价格, 并按照平均价格排序 降序
SELECT
    category_id,
    AVG(price)
FROM
    product
GROUP BY
    category_id
ORDER BY
    AVG(price) DESC;

-- 步骤2: 去掉商品价格小于等于200 的商品, 以及 商品数量小于等于2的商品类别
/*SELECT
    category_id,
    AVG(price)
FROM
    product
GROUP BY
    category_id
HAVING price > 200 and count(pid) > 2  -- having 在group by 之后执行, 仅可以使用分组字段和聚合函数.
ORDER BY
    AVG(price) DESC;*/

SELECT
    category_id,
    COUNT(pid),
    AVG(price)
FROM
    product
WHERE
    price > 200 -- where 在group by 之前运行, 可以使用非分组字段, 但是不能使用聚合函数
GROUP BY
    category_id -- 分组字段
HAVING
    COUNT(pid) > 2 -- having 在group by 之后执行, 仅可以使用分组字段和聚合函数.
ORDER BY
    AVG(price) DESC;

 DQL之分页查询

 分页查询 : 按照一定的规则,查询全部数据中的一部分信息, 又叫做边界查询
 关键字 : limit
 格式: select 列名 from 表名  limit m , n;
 完整格式 : select 分组字段, 聚合函数  from 表名 where 分组前筛选条件 group by 分组字段 having 分组后筛选条件 order by 排序规则  limit m , n;

 m 代表查询数据的起始索引 : 索引值从0开始 ,如果从头开始查询,则索引值为0
 n 代表查询数据的条目数 : 如果要查询5条数据则为5
 举例: 要查询从 下标为4的记录开始,查询5条数据  limit 4, 5;

-- 需求1: 查看当前商品列表中价格最高的5件商品的全部信息.
-- 排序规则: price desc  边界规则: limit 0, 5;
SELECT *
FROM
    product
ORDER BY
    price DESC
LIMIT 0, 5;

-- 补充: 当起始位置为0 的时候, 起始位置索引可以省略
SELECT *
FROM
    product
ORDER BY
    price DESC
LIMIT 5;

-- 需求2: 查看当前商品列表中价格在6-10位的商品的全部信息.
-- 排序规则: price desc 边界规则: limit 5, 5;
SELECT *
FROM
    product
ORDER BY
    price DESC
LIMIT 5, 5;

-- 扩展 : 如果我们要进行业务中的分页查询则要根据页码的变化修改查询的数据内容
-- 规则 : 每页 20 条数据内容
-- 1页 : 1-20条数据   m : 0  n : 20
-- 2页 : 21-40条数据  m : 20 n : 20
-- 3页 : 41-60条数据  m : 40 n : 20
-- ....
-- n页 : (n-1)*20 +1 - n*20 条数据   m: (n - 1) * 20  n : 20

-- 结论: 当我们分页查询时 每页的 n 和 n 应该是  :
-- m :  (页码 - 1) * 每页行数
-- n :  每页行数