mysql ------多表查询（内连接、外连接、子查询）

-- 创建分类表
CREATE TABLE category (
  cid INT PRIMARY KEY AUTO_INCREMENT,
  cname VARCHAR(50)
);
-- 创建产品表
CREATE TABLE products1(
  pid INT PRIMARY KEY ,
  pname VARCHAR(50),
  price INT,
  flag VARCHAR(2),                #是否上架标记为：1表示上架、0表示下架
  category_id INT
);
ALTER TABLE products1 ADD FOREIGN KEY (category_id) REFERENCES category (cid);

#分类
INSERT INTO category(cid,cname) VALUES('1','家电');
INSERT INTO category(cid,cname) VALUES('2','服装');
INSERT INTO category(cid,cname) VALUES('3','化妆品');
#商品
INSERT INTO products1(pid, pname,price,flag,category_id) VALUES('1','联想',5000,'1',1);
INSERT INTO products1(pid, pname,price,flag,category_id) VALUES('2','海尔',3000,'1',1);
INSERT INTO products1(pid, pname,price,flag,category_id) VALUES('3','雷神',5000,'1',1);
INSERT INTO products1(pid, pname,price,flag,category_id) VALUES('4','杰克琼斯',800,'1',2);
INSERT INTO products1(pid, pname,price,flag,category_id) VALUES('5','真维斯',200,'1',2);
INSERT INTO products1(pid, pname,price,flag,category_id) VALUES('6','花花公子',440,'1',2);
INSERT INTO products1(pid, pname,price,flag,category_id) VALUES('7','劲霸',2000,'1',2);
INSERT INTO products1(pid, pname,price,flag,category_id) VALUES('8','香奈儿',800,'1',3);
INSERT INTO products1(pid, pname,price,flag,category_id) VALUES('9','相宜本草',200,'1',3);

-- 内连接查询 :求的是多张表的交集
-- 隐式内连接：select * from A,B where 条件;
SELECT  *  FROM products1 p,category c 
WHERE  c.cid = p.category_id AND cname='服装' 
ORDER BY price DESC;  -- (order by 以什么排序,desc 降序)

-- 显式内连接: select * from A inner join B on 条件;（效率更高）
SELECT * FROM products1 p INNER JOIN category c
WHERE c.cid=p.category_id AND cname='服装';

-- 左外连接 select * from A left outer join B on 条件; 
-- 左外连接以左表为主,左表中的数据全部输出,右表中如果没有同等的数据则补NULL
SELECT * FROM products1 p LEFT OUTER JOIN category c
ON c.cid=p.category_id;

-- 左外连接 select * from A left outer join B on 条件; 
-- 左外连接以左表为主,左表中的数据全部输出,右表中如果没有同等的数据则补NULL
SELECT * FROM products1 p RIGHT OUTER JOIN category c
ON c.cid=p.category_id;

  -- 1  在商品表中查询属于电子分类的商品     
       #1.1 先在分类表中查询家电的id是多少
       SELECT cid  FROM category WHERE cname='家电';
       #1.2 将查询后的cid值作为第二次查询的条件                               
       SELECT * FROM products1 WHERE category_id = (SELECT cid FROM category WHERE cname='家电');

  -- 2 在商品表中查询属于家电分类和化妆品分类的商品   
      #子查询结果有多个时，用in连接
      SELECT * FROM products1 WHERE category_id IN (SELECT cid  FROM category WHERE cname='家电' OR  cname='化妆品');
      
  -- 3 在商品表中查询属于化妆品分类的商品 (select查询之后得到一张表)
      SELECT * FROM products1 p,(SELECT * FROM category WHERE cname='化妆品') c WHERE p.category_id = c.cid;