数据库_MySQL多表_查询_子查询

01.MySQL多表_建表原则：
1).要保证每个列只记录一个数据，如果一个列记录了多条数据，那么应该拆分为多列存储；
订单信息：
id 订单日期 订单金额 商品信息
1 2017-10-01 1000 1,2,3,4
2).为每个表创建一个"主键"字段。
3).要保证每个表只记录一个事情，如果记录了多件事情，应该分表存储；
学员信息表：
id 姓名 性别 年龄 科目(科目需要被分离出来，单独做表)
1 张三 男 22 JavaEE
2 李四 女 18 ISO
02.MySQL多表_实战_一对多关系：
1).客户和订单：
1).主从关系：
主表：客户
从表：订单
2).主表：客户表：
id 客户名称 注册日期 等级
   从表：订单表：
id 订单日期 订单金额 客户ID
2).部门和员工：
1).主从关系：
主表：部门
从表：员工
2).主表：部门表
id 部门名称 创建日期
   从表：员工表
id 姓名 性别 入职日期 薪水 部门ID(外键)
3).省和市：
1).主从关系：
主表：省
从表：市
2).主表：省
id name
   从表：市
id name 省ID
3).自连接实现：
id name parentId
1 北京         NULL
2 河北         NULL
3 河南         NULL
4 北京         1
5 廊坊         2
6 石家庄 2
7 郑州         3
8 洛阳         3
03.MySQL多表_实战_多对多关系：
1).学生和课程:(前提：一个学生可以选修多门课程)
1).主表：学员表：
id 姓名 性别 年龄
1 zhangsan 男 20
   主表：课程表：
id 课程名称 课时
   从表：学员_课程表(关系表)
学员ID 课程ID
1 1
1 2
1 3
2 1
2 2
2).用户和角色:
1).主表：用户表：
id 姓名
   主表：角色表
id 角色名
   从表：用户_角色(关系表)
用户ID 角色ID
1 1
1 2
2 1
3 2
04.MySQL多表_查询_内连接查询
0) .查询结果：(也叫：等值连接查询)可以查询出两个关联表中的"等值部分"的记录。【最常用的】
1).隐式内连接：select * from 表1 , 表2 where 表1和表2的等值关系;
1).需求：查询所有的商品并显示类别名称
select * from products,category where products.category_id = category.cid;
2).显示内连接：select * from 表1 INNER JOIN 表2 ON 两个表的等值关系;
select * from products inner join category on products.category_id = category.cid;
3).注意事项：
1).隐式内连接：select * from 表1,表2 ON 两个表的等值关系;//错误
2).显示内连接：select * from 表1 inner join 表2 where 两个表的等值关系;//OK的--（但不建议，及其的不规范）
3).增加条件语句：
隐式内连接：select .. from 表1,表2 where 两个表的等值关系 and 其它条件
显示内连接：select .. from 表1 inner join 表2 on 两个表的等值关系 where 其它条件;
4).使用表别名：
例如下面语句查询商品大于2000元的商品，并且显示类别名称：
SELECT * FROM products , category WHERE products.category_id = category.cid AND products.price > 2000;
1).products.category_id:前面的"表名"的限定不是必须的，前提是两个表没有"同名字段"。如果要使用两个表中的"同名字段"就需要使用"表名"前缀。
2).可以为表起个别名，使用起来可以方便些：
隐式内连接：
select * from products p , category c where p.cid = c.cid and p.price > 2000;
显示内连接：
select * from products p inner join category c on p.cid = c.cid where p.price > 2000;

   注意：使用别名后，后面不能再使用表全名了。

                5).关于筛选字段：

多表连接查询的结果字段，可以来自于两个表：
商品名 商品价格   类别名称
select p.pname,p.price,c.cname from products p , category c where p.cid = c.cid;
05.MySQL多表_查询_外连接查询
1).左外连接查询：select ... from 表1 LEFT JOIN 表2 ON 两个表的等值关系;
需求：查询所有的商品信息，有类别的要显示类别名称。
select * from products p LEFT JOIN category c on p.cid = c.cid;
查询结果：可以查询出"左表"的所有记录，以及"右表"的等值记录。
2).右外连接查询：
一样的需求：查询所有的商品信息，有类别的要显示类别名称。
select * from category c RIGHT JOIN products p on p.cid = c.cid;
查询结果：可以查询出"右表"的所有记录，以及"左表"的等值记录。
06.MySQL多表_查询_练习：
1).查询出所有的"服饰"类商品的：商品名称，价格，类别名称。要求结果按价格升序排序；
select p.pname,p.price,c.cname from products p , category c where p.cid = c.cid and c.cname = '服饰' order by p.price asc;
2).查询价格高于2000元的商品，要求同时显示商品类别名称。
select * from products p, category c where p.cid = c.cid and p.price > 2000;
07.MySQL多表_三种查询的结果示意图：没有（^_^）
08.MySQL多表_三表联查：
学员表：
id 姓名
科目表：
id 名称
学员_科目(关系表)
学员ID 科目ID
1).内连接：
1).隐式内连接：
select ... from 表1 , 关系表 , 表2 
where 表1 和 "关系表"的等值关系 and 表2 和"关系表"的等值关系；
2).显示内连接：
select ... from 表1 INNER JOIN 关系表 ON 表1和"关系表"的等值关系 INNER JOIN 表2 ON "关系表"和"表2"的等值关系；
2).外连接：
1).左外连接：
select ... from 表1 left join 关系表 on 等值关系 left join 表2 on 关系表和表2的等值关系；
2).右外连接：
select ... from 表1 right join 关系表 on 等值关系 right join 表2 on 关系表和表2的等值关系；
09.MySQL子查询：
1).需求：查询出价格高于"劲霸"的商品信息
select * from products where price > (select price from products where pname = '劲霸');
2).写在一个查询内部的查询就叫：子查询。
3).子查询通常作为外部查询的:条件，虚拟表来使用;
A).作为条件：
示例：查询出价格高于"劲霸"的商品信息
      select * from products where price > (select price from products where pname = '劲霸');
      查询所有的"家电"类和"服饰"类的商品信息(只要获取商品信息即可)
      select * from products where cid = 'c001' or cid = 'c002';
      select * from products where cid in (select cid from category where cname in('家电','服饰'));
B).作为外部查询的虚拟表：
示例：查询所有的"服饰"类商品
正常两个表联查：select * from products p , category c where p.cid = c.cid and c.name = '服饰';
使用子查询：    select * from products p , (select * from category where name= '服饰') c where p.cid = c.cid;
4).练习：
A).查询"化妆品"分类上架商品详情:
a).正常使用两个表联查：
select * from products p, category c where p.cid = c.cid and c.cname = '化妆品' and p.flag = 1;
b).使用子查询两个关联查询：
select * from products p , (select * from category where cname = '化妆品') c where p.cid = c.cid and p.flag = 1;
c).单表查询，只查商品表：
select * from products where cid = (select cid from category where cname = '化妆品') and flag = 1;
=========================黄金分割线===========================================
总结：
1，使用内连接进行多表查询:查询结果：两个表中的"等值部分的记录"
a， 内连接的两种查询方式
1).隐式内连接
2).显示内连接
b， 显式内连接的SQL语句
select ... from 表1 INNER JOIN 表2 ON 两个表的等值条件;
c， 隐式内连接的SQL语句
select ... from 表1 , 表2 where 两个表的等值条件;
2，能够使用外连接进行多表查询
a， 外连接的两种查询方式
1).左外连接:查询结果：左表的所有记录(等值的+不等值的) 和 右表的等值记录。
2).右外连接:查询结果：右表的所有记录(等值的+不等值的) 和 左表的等值记录。
b， 左外连接的SQL语句
select ... from 表1 LEFT JOIN 表2 ON 两个表的等值条件;
c， 右外连接的SQL语句
select ... from 表1 RIGHT JOIN 表2 ON 两个表的等值条件;
3，能够使用子查询进行多表查询
查询价格高于"劲霸"的商品信息：
select * from products where price > (select price from products where pname = '劲霸');