创建category表：
create table category
(
categoryId int not null
primary key,
categoryName varchar(50) null
);
原始数据：
origincat.png
创建product表：
create table product
(
productId int auto_increment
primary key,
productName varchar(50) null,
categoryId int null,
price int null,
constraint product_category_categoryId_fk
foreign key (categoryId) references category (categoryId)
);
create index product_category_categoryId_fk
on product (categoryId);
原始数据：
originprod.png
只写join:
select * from `product` join category
output
只写join是做了笛卡尔积，4*2=8，每个product都拼上两种category
真正想要的是categoryId相等的情况
select * from `product` join category on product.categoryId = category.categoryId
output.png
注意：没有nike的记录，因为没有对应的categoryId
如果想把没有对应categoryId的产品也显示出来，用left join（左外连接）
select * from `product` left join category on product.categoryId = category.categoryId
output.png
也就是product中categoryId为null的产品会被显示
小插曲：mysql5.7.5以上版本group by查询问题解决：
group by 报错解决
select * from product left join category c on product.categoryId = c.categoryId group by product.categoryId;
out.png
select product.categoryId,categoryName, count(*) from product left join category c on product.categoryId = c.categoryId group by product.categoryId
out2.png
选择根据categoryName分类后的最便宜的产品：
select product.categoryId,categoryName, MIN(price) from product left join category c on product.categoryId = c.categoryId group by product.categoryId
output.png
把nike对应的categoryId设置为1后,想要得到最便宜产品的名字：
select product.categoryId,categoryName,productName, MIN(price) from product left join category c on product.categoryId = c.categoryId group by product.categoryId
wrong.png
正确写法是使用子查询：
select * from product join (
select product.categoryId,categoryName, MIN(price) from product left join category c
on product.categoryId = c.categoryId group by product.categoryId
) as cat_min
on product.categoryId = cat_min.categoryId
ok1.png
select * from product join (
select product.categoryId,categoryName, MIN(price) as min_price from product left join category c
on product.categoryId = c.categoryId group by product.categoryId,categoryName
) as cat_min
on product.categoryId = cat_min.categoryId
where product.price = cat_min.min_price
ok2.png
优化：
select product.productName,cat_min.categoryName,cat_min.min_price from product join (
select product.categoryId,categoryName, MIN(price) as min_price from product left join category c
on product.categoryId = c.categoryId group by product.categoryId,categoryName
) as cat_min
on product.categoryId = cat_min.categoryId
where product.price = cat_min.min_price
final.png
