数据库Day03

今天继续来学习DQL查询语句~~😎😎☝️☝️接上文哦

目录

七、排序查询

八、聚合查询

九、分组查询

十、分页查询

十一、外键约束

十二、多表查询之内连接

---

七、排序查询

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

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

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

八、聚合查询

思考: 什么叫做聚合???

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

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

在你的生活中有哪些数据是可以聚合的?

1. 平均成绩

2. 最高温度

3. 最低录取分数线

4. 总分

5. 众数 : 投票

6. 中位数 : 统计全中国人员收入水平

7. 均方差 : 计算当前值与平均值的差别,也就是计算当前数据的波动情况.

思考: 什么是函数?

数学中: 一个固定的公式,有固定个数的变量,传入不同的变量可以得到一个唯一值.

编程中: 在计算机中函数就是将特定的功能和方法打包成一个字符组合的形式,如果我需要使用该功能,就调用他

sql中常用的聚合函数:

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参与运算

九、分组查询

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

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

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

-- 分组查询 : 就是按照指定字段的值的种类,将数据记录分配到不同的分组之中, 一般后续会进行按照组的聚合计算
-- 关键字 : 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;

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

-- 注意: 一般分组后都需要聚合计算, 在很多开发场景中, 不分组的聚合是被禁止的, 从服务端禁止.(代码无法执行)

注意事项：

1. ==在使用group之后,无法在select中使用除分组字段之外的其他字段==

2. 聚合函数配合分组进行使用时,聚合的范围是当前分组内部.

3. 我们也可以根据多个字段进行分组操作, 多个字段完全相同的会被归为一组数据.

4. 在开发中where 条件筛选, 要使用在 group by 之前, order by 要用在group by 之后

-- 注意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;

9.1分组后筛选数据使用having

    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;

having 和 where 有哪些不同?

1. having是在group by 之后使用的,而where 是在group by之前使用的

2. having中可以使用聚合函数, 而where中不能使用聚合函数

3. having中不能使用除分组字段外的其他字段, 而where中可以使用任意字段

十、分页查询

-- 分页查询 : 按照一定的规则,查询全部数据中的一部分信息, 又叫做边界查询
-- 关键字 : 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;

十一、外键约束

-- 外键约束: 就是主表主键和从表外键之间的关联关系, 添加约束后, 强制关联, 关联不成功则无法插入
USE bigdata_db;
-- 1. 添加外键约束
-- 主表:
CREATE TABLE category
(
    c_id   INT PRIMARY KEY,
    c_name VARCHAR(30)
);

-- 从表
CREATE TABLE products
(
    p_id        INT PRIMARY KEY,
    p_name      VARCHAR(30),
    price       DOUBLE,
    category_id INT, -- 外键字段
    FOREIGN KEY (category_id) REFERENCES category (c_id)
);

-- 2. 查看外键约束
DESC category; -- 主表没有任何变化
DESC products; -- 外键约束添加成功

-- 3. 如果在定义表时没有添加外键约束, 也可以使用alter table 进行添加
CREATE TABLE products1
(
    p_id        INT PRIMARY KEY,
    p_name      VARCHAR(30),
    price       DOUBLE,
    category_id INT
);

-- 查看表的约束情况
DESC products1;

-- 添加外键约束
ALTER TABLE products1 ADD CONSTRAINT fk_1 FOREIGN KEY (category_id) REFERENCES category(c_id);

-- 查看表的约束情况, 已经添加成功
DESC products1;

-- 扩展: 删除外键约束,需要使用外键名称删除
ALTER TABLE products1 DROP FOREIGN KEY fk_1;

-- 查看建表语句, 外键约束已经删除
-- 删除外键后, 该字段依然是一个键,但不是外键.
SHOW CREATE TABLE  products1;

-- 如果我们没有指定外键名称则会使用默认名称, 此时需要使用 show create table 进行查询 products_ibfk_1  (表名_ibfk_编号)
ALTER TABLE products DROP FOREIGN KEY products_ibfk_1;

-- 0. 数据准备
-- 主表:
USE bigdata_db;

DROP TABLE IF EXISTS category;
CREATE TABLE IF NOT EXISTS category
(
    c_id   INT PRIMARY KEY,
    c_name VARCHAR(30)
);

-- 从表
DROP TABLE IF EXISTS products;
CREATE TABLE IF NOT EXISTS products
(
    p_id        INT PRIMARY KEY,
    p_name      VARCHAR(30),
    price       DOUBLE,
    category_id INT, -- 外键字段
    FOREIGN KEY (category_id) REFERENCES category (c_id)
);

-- 此时, 我们有两张表 :  主表: category   从表: products   两张表具备外键约束   从表category_id  >> 从表c_id

-- 1. 向从表products中插入数据
-- 错误: Cannot add or update a child row: a foreign key constraint fails (`bigdata_db`.`products`, CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `category` (`c_id`))
-- 我们此处有外键约束, 但是从表外键使用的值 在主表主键中不存在,所以报错
-- INSERT INTO products VALUES (1, '雕牌打字机', 23.55, 2);

-- 先添加主表主键
INSERT INTO
    category
VALUES
    (2, '办公用品');
-- 再添加从表数据, 此时使用的外键值,必须是主表中存在的
INSERT INTO
    products
VALUES
    (1, '雕牌打字机', 23.55, 2);
-- 查询数据是否插入成功
SELECT *
FROM
    products;

INSERT INTO
    products
VALUES
    (2, '心脏支架', 66.33, NULL);
-- 结论: 如果两张表绑定了外键约束规则.在从表中插入的外键值,只能是主表中存在的主键值, 但是外键值可以为空.

-- 2. 删除主表中的数据内容
-- 再添加几个主表记录
INSERT INTO category VALUES (1, '床上用品'), (3, '蔬菜'), (4, '服饰鞋包');

-- 删除主表记录
DELETE FROM category WHERE c_id = 1; -- 删除成功
-- Cannot delete or update a parent row: a foreign key constraint fails (`bigdata_db`.`products`, CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `category` (`c_id`))
-- 如果主表中的数据记录, 被从表的外键引用(绑定), 则 主表中的该数据记录无法被删除.
-- DELETE FROM category WHERE c_id = 2;

-- 先删除从表中, 使用该id值的从表记录
DELETE FROM products WHERE category_id = 2;

-- 再删除主表中的该记录即可
DELETE FROM category WHERE c_id = 2;

SELECT * FROM category;

-- 总结: 删除主表中的数据记录时, 该记录不能被从表中的外键引用(绑定), 否则无法删除.

-- 注意:
-- 1. 外键约束主要是保证了数据的一致性和完整性.
-- 2. 外键约束也降低了数据的查询效率和插入效率.
-- 3. 增加了数据插入的难度. 提高了数据操作复杂性.

外键约束的规则:

1. 从表的外键字段,只能使用主表中的主键值或者null

2. 删除主表记录时,该记录不能被任何从表外键引用

十二、多表查询之内连接

外键约束,只能约束多表关联时的数据插入和删除, 不能约束查询.

内连接获取的是，左表和右表中匹配成功的数据内容.

-- 在存储数据时,为了减少数据冗余,我们进行了分表操作.
-- 在提取数据时,为了数据准确且完整,我们需要进行表的连接操作.

-- 数据准备
-- 创建一个英雄表
CREATE TABLE hero
(
  hid       INT PRIMARY KEY,
  hname     VARCHAR(100),
  kongfu_id INT
);

-- 创建一个功夫表
CREATE TABLE kongfu
(
  kid   INT PRIMARY KEY,
  kname VARCHAR(100)
);

-- 在此案例中不添加外键约束,只演示连接方式
-- 向hero中插入数据
INSERT INTO
  hero
VALUES
  (1, '乔峰', 3),
  (2, '虚竹', 4),
  (3, '段誉', 1),
  (4, '金轮法王', 5);

-- 向kongfu表插入数据
INSERT INTO
  kongfu
VALUES
  (1, '吸星大法'),
  (2, '龟派气功'),
  (3, '降龙十八掌'),
  (4, '天山折梅手');

-- 1. 内连接: inner join
-- 规则: 左表和右表中的数据, 按照指定的规则进行连接, 连接成功则保留, 连接失败则不保留.
-- 格式: select 列... from 左表 inner join 右表  on 链接规则;
-- 解释: 左表就是写在左表的表, 和主从关系无关.
SELECT *
FROM
  hero
      INNER JOIN kongfu ON hero.kongfu_id = kongfu.kid;

十三、多表查询之左外连接

左外连接会保留左表中的全部数据,和右表中的匹配成功数据,如果未匹配成功在末尾补null

-- 2. 左外连接
-- 规则: 保留左表中全部的数据, 和右表中与左表连接成功的数据, 其余数据不保留
-- 格式: select 列... from 左表 left outer join 右表 on 连接规则;
-- 解释: 左表就是写在左表的表, 和主从关系无关. 一般使用左连接,则左表的数据更重要.
SELECT *
FROM
    hero
        LEFT OUTER JOIN kongfu ON hero.kongfu_id = kongfu.kid;

注意事项:

1. 左表是主表还是从表呢? 没有任何关系 , 写在左边的表就是左表,写在右边的表就是表右

2. hero作为左表左连接时, 和hero作为右表右连接时获取的数据内容是完全相同的(列的顺序可以随意调整)

思考题: 左表有 10条数据 右表有6条数据, 两张表左连接后 最多有多少条数据, 最少有多少条数据. ？？？？？？？？？？（答案见下文）

思考:左表有 10条数据 右表有6条数据, 两张表内连接 最多有多少条数据, 最少有多少条数据

？？？？？？？？？？（答案见下文）