[大数据]MySQL多表关系

外键: 从表中的某个字段,该字段的值是引用主表中主键的值 主表： 约束别人的表 副表/从表： 被别人约束的表

删除外键

• alter table 表 drop foreign key 外键名称;

什么是级联操作： 在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作 ON UPDATE CASCADE -- 级联更新，主表主键发生更新时，外键也会更新 ON DELETE CASCADE -- 级联删除，主键主键发生删除时，外键也会删除

多表间关系

多对多

多对多（m:n） 例如：老师和学生，学生和课程，用户和角色

一个老师可以有多个学生,一个学生也可以有多个老师 多对多的关系

一个学生可以选多门课程,一门课程也可以由多个学生选择 多对多的关系

一个用户可以有多个角色,一个角色也可以有多个用户 多对多的关系

多对多关系建表原则: 需要创建一张中间表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。

一对一

一对一（1:1）: 公司和地址, 老公和老婆

例如: 一个公司只能有一个注册地址，一个注册地址只能对应一个公司。

例如: 一个老公可以有一个老婆,一个老婆只能有一个老公

在实际的开发中应用不多.因为一对一可以创建成一张表。 两种建表原则：

• 外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一UNIQUE

• 外键是主键：主表的主键和从表的主键，形成主外键关系

多表设计之多表分析及创建

• 需求:完成一个学校的选课系统，在选课系统中包含班级，学生和课程这些实体。

• 分析:

  • 分析多表间的关系:

    • 班级和学生表: 一对多

    • 学生和课程表: 多对多

  • 建表的原则:

    • 一对多: 在多的一方创建一个字段作为外键,指向一的一方的主键

    • 多对多: 创建一张中间表,该表至少有2个字段,这2个字段作为外键指向各自一方的主键

    • 一对一: 创建一张表即可

内连接查询

select ... from 表1,表2 where 连接条件 [and 其他条件] ?--(连接条件:外键的值等于主表主键的值)?

外连接【重点】

左外连接

以join左边的表为主表,展示主表的所有数据,根据条件查询连接右边表的数据,若满足条件则展示,若不满足则以null显示.

可以理解为：==在内连接的基础上保证左边表的数据全部显示==

select ... from 左表名 left [outer] join 右表名 on 连接条件

-- 练习:查询所有部门下的员工,没有员工就显示null
select * from dept left outer join emp on dept.id = emp.dept_id;
select * from dept left ?join emp on dept.id = emp.dept_id;

内连接和外连接的区别

• 内连接: 查询的是公共部分,满足连接条件的部分

• 左外连接: 左边的数据全部显示出来. 再通过连接条件匹配出右边表的数据, 如果满足连接条件, 展示右边表的数据; 如果不满足, 右边的数据通过null代替

• 右外连接: 右表的数据全部显示出来. 再通过连接条件匹配出左边表的数据, 如果满足连接条件, 展示左边表的数据; 如果不满足, 左边的数据通过null代替

子查询【重点】

SELECT ... FROM 表 WHERE 字段 [=,>,<,<>,...]（子查询）;

子查询的结果是多行多列

子查询结果只要是多行多列，肯定在FROM后面作为表 SELECT ... FROM （子查询） 表别名 WHERE 条件; 子查询作为表需要取别名，否则这张表没用名称无法访问表中的字段

• 查询出2011年以后入职的员工信息，包括部门名称

自动管理事务(mysql默认)

一条sql语句就是一个事务(mysql默认自动开启事务,自动提交事务)

手动管理事务

• 方式一: 手动开启事务的方式 【掌握】

  start transaction;开启事务

  commit；提交

  rollback；回滚