JAVA基础WEB阶段第九天——MYSQL数据库（三）多表查询、外键、内连接、外连接

 外键的作用?

 保证数据的完整性,保证数据的安全操作.
-- 设置外键 : 建立真正的 多对一的关系;
-- 给从表设置外键,关联主表的主键

#格式: alter table 从表 add constraint `外键名称_fk` foreign key 从表(外键) references  主表(主键);
ALTER TABLE emp ADD CONSTRAINT `emp_dept_fk` FOREIGN KEY emp(did) REFERENCES dept(did);

1.笛卡尔积（查询两张表中的所有数据）

#需求1:查询分类表和商品表的所有信息.
SELECT * FROM category , products;

#使用条件过滤脏数据  (使用从表的外键 = 主表的主键)
SELECT * FROM category , products WHERE cid = category_id;

 2.内连接查询
   显式内连接  / 隐式内连接
   
   结论:内连接执行多表查询的效果: 取两个表的交集内容,条件是两个表都符合的结果会被查出来.
   
   显式内连接:
   格式: select 字段 from 表A inner join 表B on 条件;
   

   隐式内连接:
   格式: select 字段 from 表A , 表B where 条件

#需求: 使用显式内连接查询 分类表和商品表的所有信息.
SELECT * FROM category INNER JOIN products ON cid=category_id;

#需求2: 建议起别名
SELECT * FROM category  c INNER JOIN products p ON c.cid=p.category_id;

#需求3:使用隐式内连接查询两个表的数据
# 格式: select 字段 from 表A , 表B where 条件
SELECT * FROM category c , products p WHERE c.cid = p.category_id;

3.外连接 
   左外连接    / 右外连接
   
   结论: 
   左外连接: 以左表为基准,会查出来左表所有的数据,以及关联的和右表的交集部分的数据.
   
   右外连接: 以右表为基准,会查出来右表的所有数据,以及关联的和左表的交集部分的数据.
   
   左外连接的格式:
   select 字段 from 表1 left join 表2 on 条件.
   
   右外连接的格式:
   select 字段 from 表1 right join 表2 on 条件.

 

#需求1:使用左外连接查询商品和分类信息  (商品表示左表)
 SELECT * FROM products p LEFT JOIN category c ON p.category_id = c.cid;

 #为了让效果明显: 删除 product商品表中的香奈儿的 category_id null 和 内连接做对比.
 /* 
   右外连接的格式:
   select 字段 from 表1 right join 表2 on 条件. 
 */ 
 #需求2:使用右外连接查询商品和分类信息 
 
# 这时以右边的category表为基准(它的数据都必须存在) 关联 product表进行查询
 SELECT * FROM products p RIGHT JOIN category c ON p.category_id = c.cid;
 
 #需求3:把右外连接的结果,使用左外连接实现
 SELECT * FROM  category c LEFT  JOIN  products p ON p.category_id = c.cid;

  4.子查询:
  查询语句中嵌套查询语句. 子查询
  
  子查询:如果查到的结果是一个一行一列的,可以让该查询结果作为一个条件
  
  子查询: 如果查询到的结果是一个多行1列的,可以让他作为一张表来使用. 作为条件.
  
  子查询: 如果查询的结果是一个多行多列的,可以让该结果作为一张表. 对该表进行查询
  
  子查询:不推荐使用; 因为查询效率太低.

# 需求: 1 子查询, 查询“化妆品”分类上架商品详情

#1.如果不用子查询
SELECT * FROM products p , category c WHERE p.category_id=c.cid AND c.cname='化妆品' AND flag=1;

#2.将上面的语句修改为子查询
# 如果查询的结果是一个一行一列的,让该结果作为一个条件.

#查询分类id , 分类名称为化妆品
SELECT cid FROM category WHERE cname='化妆品';

#使用子查询修改sql
SELECT * FROM products p , category c 
WHERE p.category_id=c.cid 
AND  c.cid = (SELECT cid FROM category WHERE cname='化妆品')
AND flag = 1;

#让查询的结果作为一张表
#查询“化妆品”和“家电”两个分类上架商品详情
SELECT * FROM products p,category c 
WHERE p.category_id = c.cid 
AND (c.cname='化妆品' OR c.cname='家电');

#使用子查询来修改代码

#1.先查询分类表,查询分类为化妆品或家电cid的结果集 
SELECT cid FROM category WHERE cname = '化妆品' OR cname='家电';

#2.上面两个表查询的结构在,cid = c001 ,c003里面选
SELECT * FROM products p ,category c
WHERE p.category_id = c.cid
AND flag = 1
AND c.cid IN  (SELECT cid FROM category WHERE cname = '化妆品' OR cname='家电')

#3.如果查询的结果是一个多行多列的.
SELECT * FROM category WHERE cname = '化妆品' OR cname='家电';

#查询的时候 products category
SELECT * FROM products p , (SELECT * FROM category WHERE cname = '化妆品' OR cname='家电') c 
WHERE p.category_id = c.cid AND flag=1;