MySQL多表查询及案例

多表查询

一、多表查询的常见分类

1.1内连接：
隐式内连接
显示内连接

1.1.1隐式内连接
语法：

select * from A,B where A.主键 = B.主键;
select * from A  a,B b where a.主键 =b.主键;

1.1.2显示内连接
语法:

select * from A a inner join B b a.主键 =b.主键;

2.1外链接：
左外连接
右外连接
2.1.1左外连接
语法:

select * from A a left outer join B b on a.主键 = b.外键;

2.1.2右外连接
语法:

select * from A a right outer join B b on a.主键 = b.外键;

2.3 子查询

select ... from ... where ...

二、多表查询案例
2.1 提供表单数据

  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 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');

2.2 题目 及 解题代码和实现效果图

2.2.1 查询哪些分类的商品已经上架
代码：
select distinct c.cname from category c, products p where c.cid = p.category_id and flag = ‘1’;
效果图:

2.2.2 查询所有分类商品的个数
代码:
select cid,cname, count(pid) from category c
left outer join products p on c.cid = p.category_id
group by cname ;
效果图:

2.2.3 子查询, 查询“化妆品”分类上架商品详情
代码:
select * from products where category_id = (
select cid from category where cname = ‘化妆品’
) and flag = ‘1’;
效果图: