1 /*
2 需求：建立产品和分类表
3 1.查询每种分类的产品数量，没有产品的分类也要统计。(cname,quantity)
4 2.根据分类名称查询分类中的所有产品
5 */
6
7 -- -------------------
8 -- category
9 -- 先建立主表，再建立从表，可以在从表创建时添加外键。
10 -- -------------------
11 drop table if exists category;
12 create table category(
13 cid int unsigned key auto_increment,
14 cname varchar(255)
15 );
16 -- show create table category;
17
18 -- -------------------
19 -- product
20 -- -------------------
21 drop table if exists product;
22 create table product(
23 pid int unsigned key auto_increment,
24 pname varchar(255),
25 price decimal(10, 2),
26 cid int unsigned,
27 constraint category_fk foreign key (cid) references category(cid)
28 );
29 -- show create table product;
30
31
32 -- -------------------
33 -- 插入测试数据
34 -- -------------------
35 insert into category(cname) values('蔬菜');
36 insert into category(cname) values('水果');
37 insert into category(cname) values('饮料');
38
39 insert into product (pname, price, cid)
40 values('豆角', 2.35, (select cid from category where cname='蔬菜'));
41 insert into product (pname, price, cid)
42 values('萝卜', 1.5, (select cid from category where cname='蔬菜'));
43 insert into product (pname, price, cid)
44 values('香蕉', 3.6, (select cid from category where cname='水果'));
45 insert into product (pname, price, cid)
46 values('苹果', 3.6, null);
47
48 -- -------------------
49 -- 1.查询每种分类的产品数量，没有产品的分类也要统计。(cname,quantity)
50 -- -------------------
51 select cname, count(*) quantity
52 from product p right join category c
53 on p.cid=c.cid
54 group by cname;
55
56 -- -------------------
57 -- 2.根据分类名称查询分类中的所有产品
58 -- -------------------
59 -- 方法1 内连接
60 select p.pname, p.price
61 from product p join category c
62 on p.cid=c.cid and c.cname='蔬菜';
63
64 -- 方法2 子查询
65 select p.pname, p.price
66 from product p
67 where p.cid=(select c.cid from category c where cname='蔬菜');
68
69 -- -------------------
70 -- 3.使用union实现全外连接
71 -- -------------------
72 select * from product p left join category c
73 on p.cid=c.cid
74 union
75 select * from product p right join category c
76 on p.cid=c.cid;
