多表查询

-- 1.交叉连接查询(基本不会使用-得到的是两个表的乘积) [了解]
-- 语法：select * from A,B;

SELECT * FROM products,category;

-- 2.内连接查询(使用的关键字 inner join  -- inner可以省略)
-- 隐式内连接：select * from A,B where 条件;
-- 显示内连接：select * from A inner join B on 条件;  此处的on相当于where

-- 隐式
SELECT * FROM category,products WHERE category.cid=products.category_id;
-- 显式
SELECT products.*,category.cname FROM products INNER JOIN category ON category.cid=products.category_id;

-- 3.外连接查询(使用的关键字 outer join -- outer可以省略)
-- 左外连接：left outer join
-- select * from A left outer join B on 条件;
-- 右外连接：right outer join
-- select * from A right outer join B on 条件;
# 左连接以左边为主 左边表有的 在查询结果里必须有
# 右连接以右边为主 右边表有的 在查询结果里必须有
SELECT * FROM category LEFT OUTER JOIN products ON category.cid=products.category_id;

SELECT * FROM products RIGHT OUTER JOIN category ON category.cid=products.category_id;

# 查询化妆品分类 商品详情
SELECT  * FROM products WHERE products.category_id= (SELECT cid FROM category WHERE cname='化妆品');  -- 子查询必须用括号括起来s

SELECT p.* FROM products p,category c WHERE p.category_id=c.cid AND cname='化妆品'  -- 正常查询


# 查询化妆品和 家电 两个分类商品详情
SELECT  * FROM products WHERE products.category_id= (SELECT cid FROM category WHERE cname='化妆品') 
                                                                                    OR  products.category_id= (SELECT cid FROM category WHERE cname='家电');  -- 子查询必须用括号括起来

SELECT  * FROM products WHERE products.category_id in 
((SELECT cid FROM category WHERE cname='化妆品' ),( SELECT cid FROM category WHERE cname='家电')) ;

SELECT  products.*  FROM products WHERE products.category_id in (SELECT cid  FROM category WHERE cname in ('化妆品','家电'));