mysql8必知必会7 连接 内连接 外连接 交叉连接

/*
 2　　 需求：建立产品和分类表
    1.查询每种分类的产品数量，没有产品的分类也要统计。(cname,quantity) 
    2.根据分类名称查询分类中的所有产品
*/

-- -------------------
-- category
-- 先建立主表，再建立从表，可以在从表创建时添加外键。
-- -------------------
drop table if exists category;
create table category(
    cid int unsigned key auto_increment,
    cname varchar(255)
);
-- show create table category;

-- -------------------
-- product
-- -------------------
drop table if exists product;
create table product(
    pid int unsigned key auto_increment,
    pname varchar(255),
    price decimal(10, 2),
    cid int unsigned,
    constraint category_fk foreign key (cid) references category(cid)
);
-- show create table product;


-- -------------------
-- 插入测试数据
-- -------------------
insert into category(cname) values('蔬菜');
insert into category(cname) values('水果');
insert into category(cname) values('饮料');

insert into product (pname, price, cid) 
values('豆角', 2.35, (select cid from category where cname='蔬菜'));
insert into product (pname, price, cid) 
values('萝卜', 1.5, (select cid from category where cname='蔬菜'));
insert into product (pname, price, cid) 
values('香蕉', 3.6, (select cid from category where cname='水果'));
insert into product (pname, price, cid) 
values('苹果', 3.6, null);

-- -------------------
-- 1.查询每种分类的产品数量，没有产品的分类也要统计。(cname,quantity)
-- -------------------
select cname, count(*) quantity
from product p right join category c
on p.cid=c.cid
group by cname;

-- -------------------
-- 2.根据分类名称查询分类中的所有产品
-- -------------------
-- 方法1 内连接
select p.pname, p.price
from product p join category c
on p.cid=c.cid and c.cname='蔬菜';

-- 方法2 子查询
select p.pname, p.price 
from product p
where p.cid=(select c.cid from category c where cname='蔬菜');

-- -------------------
-- 3.使用union实现全外连接
-- -------------------
select * from product p left join category c
on p.cid=c.cid
union
select * from product p right join category c
on p.cid=c.cid;