知识点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;
==在使用group之后,无法在select中使用除分组字段之外的其他字段==
聚合函数配合分组进行使用时,聚合的范围是当前分组内部.
我们也可以根据多个字段进行分组操作, 多个字段完全相同的会被归为一组数据.
在开发中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 有哪些不同?
-
having是在group by 之后使用的,而where 是在group by之前使用的
-
having中可以使用聚合函数, 而where中不能使用聚合函数
-
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;
-- 先添加主表主键,再添加从表数据, 此时使用的外键值,必须是主表中存在的
如果两张表绑定了外键约束规则.在从表中插入的外键值,只能是主表中存在的主键值, 但是外键值可以为空.
外键约束的规则:
-
从表的外键字段,只能使用主表中的主键值或者null
-
删除主表记录时,该记录不能被任何从表外键引用
外键约束的作用?
保证数据的一致性和完整性
一致性: 从表的外键值和主表的主键值始终保持一致.
完整性: 主表中被从表引用的数据不能被删除.
在使用多表关联时,一定要使用外键约束么
不一定
外键约束主要是保证了数据的一致性和完整性.
外键约束也降低了数据的查询效率和插入效率.
增加了数据插入的难度. 提高了数据操作复杂性.
知识点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 : 在连接查询后,我们如果想要指定要显示的字段, 一般会使用表名.字段名的形式
-- 原因: 这样写 可读性更高,同时防止两张表中出现重复名称的字段,导致报错
注意事项:
左表是主表还是从表呢? 没有任何关系 , 写在左边的表就是左表,写在右边的表就是表右
hero作为左表左连接时, 和hero作为右表右连接时获取的数据内容是完全相同的(列的顺序可以随意调整)
思考题: 左表有 10条数据 右表有6条数据, 两张表左连接后 最多有多少条数据, 最少有多少条数据.
最多: 60
最少: 10
思考:左表有 10条数据 右表有6条数据, 两张表内连接 最多有多少条数据, 最少有多少条数据
最多: 60
最少: 0
今天的学习笔记就到这里 欢迎大家点赞评论 互相交流
祝大家在程序员的道路上越走越远