MySQL学习篇——CRUD 多表操作

学习知识XMind图解：

mysql 多表查询练习

分析图如下：

1 多表的关系及创建

1.1 多表之间的关系

关联关系:在实际开发中,需要根据实体的内容设计数据表,实体间会有各种关联关系,所以根据实体设计的数据表之间也存在着各种关联关系,MySQL中数据表的关联关系有三种,具体如下:

- 一对多/多对一
多对一是数据表中最常见的一种关系,比如,员工和部门之间的关系,一个部门可以有多个员工,而一个员工不能属于多个部门,也就是说部门表中的一行在员工表中可以有许多匹配行,但员工表中的一行在在部门表中只能有一个匹配行。表之间的关系通过外键建立的，在多对一的表关系中，应该将外键建在多的一方，否则造成数据的冗余。
- 多对多
多对多也是数据表中的一种关系，比如学生与课程之间的关系，一个学生可以选择多门课程，当然一门课程也供多个学生选择，也就是说学生表中的一行在课程表中可以有许多匹配行，课程表中的一行在学生表中的也有许多匹配行。通常情况下，为了实现这种关系，需要定义一张中间表（称为连接表），该表会存在两个外键，分别参考课程表和学生表，在多对多关系中，需要注意的是，连接表的两个外键都是可以重复的，但是两个外键之间的关系是不能重复的，所以这两个外键又是连接表的联合主键
- 一对一
一对一关系在实际生活中比较常见，例如人与身份证之间就是一对一的关系，一个人对应一张身份证，一张身份证只能匹配一个人，那么一对一关系的两张表如何建立外键？
首先要分清主从关系，从表需要主表的存在才有意义，身份证需要人的存在才有意义。因此人为主表，身份证为从表，要在身份证中建立外键，由实际的经验可知，身份证中的外键必须是非空唯一的，因此会直接用从表（表身份证）的主键作为外键。

1.2 多表之间的关系如何来维护

– 数据库的五大约束:

 主键约束: primary key 唯一 / 非空
    唯一约束 : unique
    非空约束 : not null
    检查约束: mysql里面不支持 check
    外键约束: foreign key

–外键约束(多表约束): 外键是指引用另一张表中的一列或多列,被引用的列应该具有主键约束或唯一性约束.外键用于建立和加强两个表数据之间的连接，实际上是用来约束两张表,约束从表中的记录必须存在于主表中

作用:就是确保表中数据的完整性
从表:商品表依赖于分类表,所以商品称为从表
主表:分类表
书写语法:

-- 添加外键约束方式一 语法格式
alter table 表名 add foreign key(外键字段名) references 主表表名(主键字段名);

--添加外键约束方式二 建表时候就添加外键 语法格式
foreign key(列名) references 表名(列名)

注意：

• 插入数据: 数据必须先存在于主表,然后再插入从表中的数据
• 删除主表数据时候: 必须先删除从表中已引用了主表中数据,再删除主表中的数据
• 删除表的时候,如果有从表里面有外键约束,必须先删除从表再删除主表
• 从表在添加外键约束的时候,外键通常是主表中的主键

1.3 多表之间的建表原则

- 一对多:
  - 建表原则: 在多的一方添加一个键,然后让它作为外键指向一的一方
  - 例如:学生和班级,一个班级对多个学生,一个学生只属于一个班级,那么就可以在学生表里面建立外键指向班级表的主键
  - 商品分类和商品,一个分类下可以有多个商品,一个商品只属于一个分类,那么就可以在商品表里面建立外键指向商品分类表
- 多对多: 
  - 建表原则:创建一张中间表,将多对多的关系,拆分成一对多的关系, 中间表至少要包含两个外键,这两个外键分别指向各自原来的表主键
  - 例如:学生和选课
- 一对一:  
  - 建表原则: 1. 将两张表的主键建立连接, 2. 将两张合并成一张表 3. 将一对一的关系,拆分成一对多的关系
  - 合并表,拆表
  - 例如公民和身份证号

2 多表查询

概述：在关系型数据库管理系统中，建立表时各个数据之间的关系不必确定，通常将每个实体的所有信息存放在一个表中，当查询数据时候，通过连接操作查询多个表中的实体信息，当两个或多个表中存在相同意义的字段时候，便可以通过这些字段对不同的表进行连接查询，连接查询包括交叉连接查询，内连接查询，外连接查询.

交叉连接查询

概述:交叉连接返回的结果是被连接的两个表中所有数据行的笛卡尔积,也就是返回第一个表中符合查询条件的数据行乘以第二个表中符合查询条件的数据行数,例如 department表中有4个部门,employee表中有4个员工,那么交叉连接的结果有4*4=16条数据，注意:交叉查询得到的是笛卡尔积 : 两张表的乘积, 实际没有意义

-- 交叉连接的语法格式如下:
select * from 表1 cross join 表2;
select * from 表1,表2;
-- 以上语法格式 cross join 用于连接两个要查询的表,通过该语句可以查询两个表中所有的数据组合

-- 交叉查询
SELECT * FROM USER CROSS JOIN orders;
SELECT * FROM USER,orders;

内连接查询:

概述:

 内连接(inner join) 又称为简单连接或自然连接,是一种常见的连接查询,内连接使用比较运算符对两个表中的数据进行比较,并列出与连接条件匹配的数据行,组合成新的结果,也就是说在内连接查询中,只有满足条件的记录才能出现在查询结果中,内连接查询的语法如下:

内连接查询语法:

显式内连接: select 查询字段 from 表 1 inner join 表2 on 表1.关系字段 = 表2.关系字段; -- inner 可以省略不写
隐式内连接: select 查询字段 from 表1,表2 where 条件;

注意:

- inner join 用于连接两个表
- on 用于指定连接条件
- 其中inner 可以省略

 例如需求: 查找出1号用户 下了多少订单

            -- 显式内连接
        SELECT * FROM USER INNER JOIN orders ON user.userid = orders.uno WHERE user.userid = 1;
        -- 省略inner
        SELECT * FROM USER JOIN orders ON user.userid = orders.uno WHERE user.userid = 1;
        -- 隐式内连接
        SELECT * FROM USER,orders WHERE user.userid = orders.uno 
        -- 分析: 从 user表和订单表orders里面查询数据
        SELECT * FROM USER,orders WHERE user.userid = orders.uno AND user.username='zs';
        -- 需求: 查找出1号用户 下了多少订单

        -- 查找1号用户下了多少订单  用户表  订单表
        SELECT * FROM USER INNER JOIN orders ON orders.uno = user.userid WHERE user.userid=1
        SELECT * FROM USER INNER JOIN orders ON orders.uno = user.userid