学习大数据十六课-MySQL的多表查询

前言：多表查询的本质：就是把很多个表根据对应的行组合成一个更大的表去查看内容

一外键以及外键约束

（1）什么是外键

        我们知道，两个表的内容可以用肉眼直接看，那么我们看的思维是什么样的呢？找相同，从这个表看到另一个表相同的内容就知道他们是有关系的可以联系起来的。在MySQL中，外键就相当于找相同这个概念。

        通过上表可以知道，我们通过右边或者左边cid中的内容去对应的另一边表中查看对应内容。一般数量多的表作为从表，少的作为主表。这样可以在从表的后面通过cid对应添加多列主表的内容。

（2）外键约束

        什么是外键约束呢？就是对外键和主键进行条件限制。

        外键约束关键字: foreign key

        外键约束作用: 
                限制从表插入: 如果从表插入的外键值,在主表中不存在,就插入失败
                限制主表删除: 如果主表的主键值已经被从表引用,在主表删除该数据的时候,就删除失败
    
        注意: 如果想要使用外键约束,存储引擎需要是InnoDB。在启动MySQL的时候引擎必须改为InnoDB,myisam存储引擎是不支持外键约束的。

        建表时添加外键约束:  create table 从表名(...[CONSTRAINT] foreign key(外键名) references 主表名(主键名));
        建表后添加外键约束:  alter table 从表名 add [CONSTRAINT] foreign key(外键名) references 主表名(主键名);

# 多表查询准备工作
create database day04;
use day04;
# 分类表
CREATE TABLE category
(
    cid   VARCHAR(32) PRIMARY KEY,
    cname VARCHAR(100) #分类名称
);
# 商品表
CREATE TABLE products
(
    pid         varchar(32) PRIMARY KEY,
    pname       VARCHAR(40),
    price       DOUBLE,
    category_id varchar(32),
    foreign key(category_id) references category(cid) 
);  
# 演示外键约束限制从表插入数据
insert into products values ('p01','联想笔记本',9999,'c001'); # 插入失败
# 如何让从表插入数据成功? 
# 方式1: 主表提前插入从表要引用的c001数据
insert into category values('c001','电脑');
# 再次插入数据
insert into products values ('p01','联想笔记本',9999,'c001'); # 插入成功
insert into products values ('p03','小米笔记本',5999,'c001'); # 插入成功
# 方式2: 从表插入的时候外键保留位空
insert into products values ('p02','联想笔记本',9999,null); # 插入成功
# 演示外键约束限制主表删除数据
delete from category where cid = 'c001';
# 如何让主表删除数据成功? 方式1: 直接把引用主表c001的从表数据删除, 方式2: 把引用主表c001d从表外键值改为空
# 方式1: 直接把引用主表c001的从表数据删除
delete from products where pid = 'p01';
# 方式2: 把引用主表c001d从表外键值改为空
update products set category_id = null where pid = 'p03';
# 主表再次删除
delete from category where cid = 'c001';

二连接查询

        什么是连接查询：把多个表通过主外键关联关系连接(join)合并成一个大表,再去查询

        连接查询的分类：内连接查询，左外连接查询，右外连接查询，交叉连接查询。

（1）内连接

        内连接关键字: inner join ... on

        显式内连接格式: select * from 左表 inner join 右表 on 关联条件;

        隐式内连接格式:  select * from 左表 , 右表 where 关联条件;

        注意: 左表和右表没有特殊含义,只是在前面是左表,在后面的是右表

（2）左外连接和右外连接

        左外连接关键字: left outer join ... on

        右外连接关键字: right outer join ... on

        左外连接格式: select * from 左表 left outer join 右表 on 关联条件;

        左外连接格式: select * from 左表 right outer join 右表 on 关联条件;

        注意: 左表和右表没有特殊含义,只是在前面是左表,在后面的是右表

（3）交叉连接

        交叉连接关键字: cross join

        显式交叉连接格式: select * from 左表 cross join 右表;

        隐式交叉连接格式: select * from 左表,右表;

        注意: 交叉连接了解即可,因为它本质就是一个错误,又叫笛卡尔积(两个表记录数的乘积)

代码演示：

# 创建表
drop table if exists products;
drop table if exists category;
# 分类表
CREATE TABLE category (
  cid VARCHAR(32) PRIMARY KEY ,
  cname VARCHAR(50)
);
# 商品表
CREATE TABLE products(
  pid VARCHAR(32) PRIMARY KEY ,
  pname VARCHAR(50),
  price INT,
  flag VARCHAR(2),    #是否上架标记为：1表示上架、0表示下架
  category_id VARCHAR(32),
  CONSTRAINT products_fk FOREIGN KEY (category_id) REFERENCES category (cid)
);
# 添加数据
#分类
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
INSERT INTO category(cid,cname) VALUES('c004','奢侈品');
#商品
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','联想',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','海尔',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','雷神',5000,'1','c001');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','JACK JONES',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','真维斯',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','花花公子',440,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','劲霸',2000,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','香奈儿',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','相宜本草',200,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p010','华为',9999,'0',null);

# 实战
# 需求1.1:查询哪些分类的商品已经上架,要求展示分类id
# 分析: 完成需求需要的数据来源几个表,如果是一个表直接查询,如果是多个表需要先合成一个表再去查
select DISTINCT category_id
from products
where flag = '1';
# 需求1.2:查询哪些分类的商品已经上架,要求展示分类名称
# 分析: 完成需求需要的数据来源几个表,如果是一个表直接查询,如果是多个表需要先合成一个表再去查
select DISTINCT cname
from products as p inner join category as c ON p.category_id = c.cid
where flag = '1';
# 需求2:查询所有分类商品的个数,要求展示分类名称和个数
# 分析: 完成需求需要的数据来源几个表,如果是一个表直接查询,如果是多个表需要先合成一个表再去查
# 注意: 此题需要利用聚合函数(字段名)忽略null值的特点
select cid,count(category_id) 
from products p right outer join category as c ON p.category_id = c.cid 
group by cid;
# 需求3: 查询'服饰'这个分类对应的所有商品
select * 
from products p inner join category c ON p.category_id = c.cid 
where cname = '服饰';
# 需求4: 查询'服饰'这个分类上架的所有商品
select * 
from products p inner join category c ON p.category_id = c.cid 
where cname = '服饰' and flag = 1;
# 需求5: 查询'服饰'和'化妆品'这两个分类的所有商品
select * 
from products p inner join category c ON p.category_id = c.cid 
where cname = '服饰' or cname = '化妆品';
select * 
from products p inner join category c ON p.category_id = c.cid 
where cname in('服饰' ,'化妆品') ;

三多表查询的进阶

（1）子查询

子查询:  一个select语句作为另外一个select语句的一部分(表或者条件)

注意: 子查询语句作为表使用的时候需要设置别名

（2）自连接查询

自连接: 本质就是内外连接,唯一区别是左表和右表是同一张表

注意: 自连接为了作为多个表使用,必须起别名进行区分

（3）开窗函数

开窗函数: mysql8的新功能,保证输出结果的记录数和输入的数据记录数一致

开窗函数格式:... 开窗函数 over(partition by 分组字段 order by 排序字段 asc|desc)  ...

        聚合函数: sum()  max()  min()  avg()  count()

        排序函数: row_number()   rank()   dense_rank()
                row_number():  巧记1234   唯一且连续
                rank()      :  巧记1224   并列不连续
                dense_rank():  巧记1223   并列且连续