MySQL数据库基础（三）：多表查询，子查询，开窗函数
十一、多表查询（重点、难点）
表与表之间的关系
在SQL语句中，数据表与数据表之间，如果存在关系，一般一共有3种情况：
① 一对一关系（高级）
比如有A、B两张表，A表中的每一条数据，在B表中有一条唯一的数据与之对应。
用户表user
用户详情表user_items
我们把用户表与用户详情表之间的关系就称之为一对一关系。
② 一对多关系（重点）
比如有A、B两张表，A表中的每一条数据，在B表中都有多条数据与之对应，我们把这种关系就称之为一对多关系
产品分类表
产品信息表
我们把产品分类表与产品表之间的关系就称之为一对多关系。
③ 多对多关系（高级）
用户表
权限表
虽然从以上图解来看，两者之间好像没有任何联系，但是两者之间其实是有关系的，这种关系需要通过一张临时表进行呈现。
每个用户，应该有对应的权限，admin账号可以做增删改查，itheima账号可以做查询
反过来
每个权限都应该对应多个用户，查询权限 => admin/itheima
中间表 ：用户_权限表
交叉连接(了解)
没有意义，但是它是所有连接的基础。其功能就是将表1和表2中的每一条数据进行连接。
结果：
字段数 = 表1字段 + 表2的字段
记录数 = 表1中的总数量 * 表2中的总数量（笛卡尔积）
1、内连接
☆ 连接查询的介绍
连接查询可以实现多个表的查询，当查询的字段数据来自不同的表就可以使用连接查询来完成。 连接查询可以分为:
内连接查询
左外连接查询
右外连接查询
自连接查询（自己查询自己）
☆ 内连接查询
查询两个表中符合条件的共有记录
内连接查询语法格式:
select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2
说明:
inner join 就是内连接查询关键字
on 就是连接查询条件
例1：使用内连接查询学生表与班级表:
☆ 小结
内连接使用inner join .. on .., on 表示两个表的连接查询条件
内连接根据连接查询条件取出两个表的 “交集”
2、左外连接
☆ 左连接查询
以左表为主根据条件查询右表数据，如果根据条件查询右表数据不存在使用null值填充
左连接查询语法格式:
select 字段 from 表1 left join 表2 on 表1.字段1 = 表2.字段2
说明:
left join 就是左连接查询关键字
on 就是连接查询条件
表1 是左表
表2 是右表
例1：使用左连接查询学生表与班级表:
☆ 小结
左连接使用left join .. on .., on 表示两个表的连接查询条件
左连接以左表为主根据条件查询右表数据，右表数据不存在使用null值填充。
3、右外连接
☆ 右连接查询
以右表为主根据条件查询左表数据，如果根据条件查询左表数据不存在使用null值填充
右连接查询语法格式:
select 字段 from 表1 right join 表2 on 表1.字段1 = 表2.字段2
说明:
right join 就是右连接查询关键字
on 就是连接查询条件
表1 是左表
表2 是右表
例1：使用右连接查询学生表与班级表:
☆ 小结
右连接使用right join .. on .., on 表示两个表的连接查询条件
右连接以右表为主根据条件查询左表数据，左表数据不存在使用null值填充。
4、自连接查询(扩展)
自连接查询：数据表自己连接自己，前提：连接操作时必须为数据表定义别名！
左表和右表是同一个表，根据连接查询条件查询两个表中的数据。
两个实际的工作场景，求省市区信息，求分类导航信息
地域：area
pid 全称 parent id（父级ID编号），如果pid值为null代表本身就是父级，如果pid是一个具体的数值，则代表其属于子级
例1：查询省的名称为“广东省”的所有城市
创建areas表:
执行sql文件给areas表导入数据:
自连接查询的用法:
说明:
自连接查询必须对表起别名
☆ 小结
自连接查询就是把一张表模拟成左右两张表，然后进行连表查询。
自连接就是一种特殊的连接方式，连接的表还是本身这张表
十二、子查询(三步走)
1、子查询（嵌套查询）的介绍
在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句，外部那个select语句则称为主查询.
主查询和子查询的关系:
子查询是嵌入到主查询中
子查询是辅助主查询的,要么充当条件,要么充当数据源(数据表)
子查询是可以独立存在的语句,是一条完整的 select 语句
2、子查询的使用
例1. 查询学生表中大于平均年龄的所有学生:
需求：查询年龄 > 平均年龄的所有学生
前提：
① 获取班级的平均年龄值
② 查询表中的所有记录，判断哪个同学 > 平均年龄值 第一步：写子查询
第二步：写主查询
第三步：第一步和第二步进行合并
例2. 查询tb_goods产品表中具有分类信息的产品
需求：查询产品表中具有分类信息的产品（没有与之对应分类信息的产品不显示）
前提：① 查询分类表中，到底有哪些分类（获取cid编号）
② 到产品表中进行判断，判断这个商品的cid编号与①中的是否相等 第一步：编写子查询
第二步：编写主查询
第三步：把主查询和子查询合并
例3. 查找年龄最小且成绩最低的学生:
第一步：获取年龄最小值和成绩最小值
第二步：查询所有学员信息（主查询）
第三步：把第一步和第二步合并
注：数据表中必须有这样一条记录，否则可能查询不到结果，重点练习子查询返回多个结果情况。
3、小结
子查询是一个完整的SQL语句，子查询被嵌入到一对小括号里面 掌握子查询编写三步走
十三、外键约束（扩展）
主键：primary key
外键：foreign key（应用场景：在两表或多表关联的时候设置的，用于标志两个表之间的关联关系）
① 主键约束primary key
② 默认值约束default
③ 非空约束not null
④ 唯一约束unique key
⑤ 外键约束foreign key
原则：在一张表中，其是主键。但是在另外一张表中，其是从键（非主键），但是这个字段是两张表的关联字段。
1、外键约束作用
外键约束:对外键字段的值进行更新和插入时会和引用表中字段的数据进行验证，数据如果不合法则更新和插入会失败，保证数据的有效性。
dage表：
xiaodi表：
外键设计原则：保证两张表的关联关系，保证数据的一致性。在选择时，一般在一个表中时关联字段，在另外一个表中是主键，则这个字段建议设置为外键。
2、对于已经存在的字段添加外键约束
3、在创建数据表时设置外键约束
4、删除外键约束
十四、索引[了解]
① 编写SQL ② SQL优化（查询数据把查询时间缩短）
TB级别，10s => 0.01s
1、索引概述
索引作用: 快速检索数据(提高查询效率)，InnoDB引擎其底层主要是使用B+ Tree结构
2、普通索引使用
主键就是一个索引，比如百万条数据，没有主键索引，查询可能需要3-5s，如果我们添加了主键索引且刚好，要查询的字段就是主键，则可以缩短到零点零几秒。
备注：主键、外键、唯一键其实也是索引
创建索引: create index index_cname on category(cname); create index index_cname on category(cname(20));
修改表添加索引: alter table category add index index_cname(cname(20));
查询索引: show index from category;
删除索引: drop index index_cname on category;
查看所有库或者表的索引:
3、唯一索引使用
创建索引
删除索引
扩展：性能监测
4、索引使用注意
索引不是越多越好. 索引使用应该注意以下问题:
磁盘空间消耗
创建索引和维护索引的时间消耗
经常增删改数据，索引需要动态维护，效率低下。
不经常查询的字段不需要创建索引
大部分值相同的字段不需要创建索引
扩展:
开启mysql时间检测: set profiling=1;
查看sql语句执行时间: show profiles;
十五、开窗函数(mysql 8.0后新的)
1、数据准备
2、开窗函数使用
格式:
partition by ：相当于分组group by
order by ：相当于前面的order by
使用:
