【第二阶段 | MySQL语句详解=多表查询=关联查询】

— 创建分类表和商品表
— 分类表 主
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),category_id varchar (32),
— 添加外键约束
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’,’美
的冰箱’,4500,’1’,’c001’);
INSERT INTO products (pid, pname,price,flag,category_id) VALUES(‘p004’,’篮
球鞋’,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’,’T
恤’,300,’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’);
— 1) 笛卡尔积
— 语法 SELECT 字段名 FROM 表1, 表2;
select from category,products;
— 得到的结果是无法使用的
— 2)进阶 笛卡尔积基础+where条件 过滤出有效数据
select from category c ,products p; where c.cid=p.category_id;
— 3)内连接
内连接的特点:
通过指定的条件去匹配两张表中的数据, 匹配上就显示,匹配不上就不显示
比如通过: 从表的外键 = 主表的主键 方式去匹配
1)
— 笛卡尔积+where 查询家电分类所有商品
select from category c ,products p; where c.cid=p.category_id and cname=’家电’;
— 查询格力空调属于哪个分类
select from category c ,products p where c.cid=p.category_id and pname=’格力空调’;
— 显式内连接
select from category c inner join products p on c.cid=p.category_id;
— 左外关联
select from category c left join products p on c.cid=p.category_id;
— 查询每个分类下的的商品个数
select c.cname,count(p.pname) from category c left join products p on c.cid=p.category_id group by c.cname;
— 右外关联