大数据学习笔记7-Mysql高级

知识点1：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;

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

知识点2：DQL之聚合查询

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;

知识点3：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;

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

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

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

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

分组后筛选数据使用having

--需求: 获取所有商品中商品数量大于2个的品类 每个品类商品的平均价格,并按照价格平均值排序 降序

-- 筛选条件: 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
;

having 和 where 有哪些不同?

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

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

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

知识点4：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 的时候, 起始位置索引可以省略

知识点5：多表关系

减少数据冗余 （范式要求）

在从表中创建一个外键,绑定主表的主键从而形成对应关系
 

知识点6：外键约束

-- 外键约束: 就是主表主键和从表外键之间的关联关系, 添加约束后, 强制关联, 关联不成功则无法插入

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 进行添加

ALTER TABLE products ADD CONSTRAINT fk_1 FOREIGN KEY (category_id) REFERENCES category(c_id);

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

-- 先添加主表主键,再添加从表数据, 此时使用的外键值,必须是主表中存在的

如果两张表绑定了外键约束规则.在从表中插入的外键值,只能是主表中存在的主键值, 但是外键值可以为空.

外键约束的规则:

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

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

外键约束的作用?

保证数据的一致性和完整性

一致性: 从表的外键值和主表的主键值始终保持一致.

完整性: 主表中被从表引用的数据不能被删除.

在使用多表关联时,一定要使用外键约束么

不一定

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

知识点7：多表查询之内连接

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

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

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

知识点8：多表查询之左外连接

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

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

知识点9：多表查询之右外连接

-- 3. 右外连接
-- 规则: 保留右表中的全部数据, 和左表中与右表连接成功的数据, 其余数据不保留. 右表中连接不成功的部分补充null
-- 格式: select 列... from 左表 right outer join 右表 on 连接规则;
-- 解释: 左表就是写在左表的表, 和主从关系无关. 一般使用右连接,则右表的数据更重要.

-- 注意1 : 其实在开发中 左连接和右连接只要记住一个就可以了, 一般开发中,左连接用的比较多
-- 场景 : 作为左表时,左连接的查询结果, 和作为右表时的右连接查询结果相同
-- 此时与上述代码相比,运行结果仅数据列的顺序不一致,但是开发中我们一般都会指定连接后要显示的数据列.
-- 注意2 : 在连接查询后,我们如果想要指定要显示的字段, 一般会使用表名.字段名的形式
-- 原因: 这样写 可读性更高,同时防止两张表中出现重复名称的字段,导致报错

注意事项:

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

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

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

最多: 60

最少: 10

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

最多: 60

最少: 0

今天的学习笔记就到这里 欢迎大家点赞评论 互相交流
祝大家在程序员的道路上越走越远