MySQL（二）

MySQL（二）

多表间的关系

有些情况下使用一张表表示数据，数据不好维护，存在数据冗余、比较乱的现象

使用多张表需要对数据进行约束，不约束添加的数据会不合法

外键约束

外键约束作用

• 保证引用完整性，用来维护多表间关系
  • 外键：一张从表中的某个字段引用主表中的主键
  • 主表：约束从表
  • 副表/从表：使用主表的数据，被主表约束

外键的语法

-- 添加外键
-- 1.新建表时增加外键：
[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名);
-- 关键字解释：
CONSTRAINT            -- 约束关键字
FOREIGN KEY(外键字段名) -- 某个字段作为外键
REFERENCES            -- 主表名(主键字段名) 表示参照主表中的某个字段
-- 2.已有表增加外键：
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);

-- 删除外键
ALTER TABLE 表名 drop foreign key 外键名称;

外键的级联

在添加了外键之后，如果从表中有引用的数据，不能直接修改或删除主表主键

在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作

-- 外键级联语法
-- 1.新建表时增加外键级联：
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名) ON UPDATE CASCADE ON DELETE CASCADE;
-- 2.已有表增加外键级联：
alter table 表 add foreign key(列[外键]) references 表(列[主键]) [ON UPDATE CASCADE][ON DELETE  CASCADE];

ON UPDATE CASCADE -- 级联更新，主键发生更新时，外键也会更新
ON DELETE CASCADE -- 级联删除，主键发生删除时，外键也会删除

注意事项

• 对已经创建好的表，添加外键时需要保证从表中作为外键的字段的值不能有不合法的数据
• 外键的这个列的类型必须和参照主表主键列的类型一致
• 参照列必须是主键

多表关系

在现实生活中，实体与实体之间肯定是有关系的，比如：个人与身份证号，部门和员工，老师和学生等

那么我们在设计表的时候，就应该体现出表与表之间的这种关系，分成三种：一对多，多对多，一对一

一对多（1:n）

例如：班级和学生，部门和员工，客户和订单

一的一方：班级 部门 客户

多的一方：学生 员工 订单

一对多建表原则：在从表(1的一方)创建一个字段，字段作为外键指向主表(n的一方)的主键

---

多对多（m:n）

例如：老师和学生，学生和课程，用户和角色

一个老师可以有多个学生，一个学生也可以有多个老师

一个学生可以选多门课程，一门课程也可以由多个学生选择

一个用户可以有多个角色，一个角色也可以有多个用户

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

---

一对一（1:1）

例如：一个公司可以有一个注册地址，一个注册地址只能对一个公司

两种建表原则：

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

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

一对一通常是创建成一张单表

---

连接查询

交叉查询

交叉查询就是把若干张表(>=2)没有条件的连接在一起，进行展示

-- 语法
-- 交叉查询指定列
select a.列,a.列,b.列,b.列 from a,b;
-- 交叉查询所有列
select a.*,b.* from a,b;
-- 或者
select * from a,b;

• 交叉查询其实是一种错误，查询出来的数据大部分是无用数据，称作笛卡尔积
• 假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}，并且可以扩展到多个集合的情况

内连接查询

使用条件过滤可以去除交叉查询产生的错误的、不想要的记录，通常要查询的多个表之间都存在关联关系，那么就通过 关联关系(主外键关系) 去除笛卡尔积

• 内连接的特点

  • 内连接查询的是公共部分，满足连接条件(主外键关系)的部分

• 使用内连接的关键点

  • 使用主外键关系做为条件来去除无用信息，抓住主外键的关系，用主外键作为连接条件

    [b表里面的外键 = a表里面的主键]

  • 显示内连接里面的 on 只能用主外键关联作为条件，如果还有其它条件，后面加 where

隐式内连接

-- 隐式(不出现inner)
select [字段,字段,字段][*] from a,b where a.主键=b.外键 [and 其它条件];

显式内连接

-- 显示(出现inner)
select [字段,字段,字段][*] from a [inner] join b on a.主键=b.外键 [where 其它条件];

外连接查询

内连接查询出来的是公共部分，如果要保证某张表的全部数据情况下进行连接查询，那么就要使用外连接查询，外连接分为左外连接和右外连接

• 内连接和外连接的区别
  • 内连接：查询的是公共部分，满足连接条件的部分

左外连接

以join左边的表为主表，展示主表的所有数据，根据连接条件查询join右边表的数据，若满足连接条件则展示右边表的数据，若不满足则以null显示
可以理解为：在内连接的基础上保证左边表的数据全部显示

-- 语法
select [字段][*] from a left [outer] join b on 连接条件;

右外连接

以join右边的表为主表，展示主表的所有数据，根据连接条件查询join左边表的数据，若满足连接条件则展示右边表的数据，若不满足则以null显示
可以理解为：在内连接的基础上保证右边表的数据全部显示

-- 语法
select [字段][*] from a right [outer] join b on 连接条件;

---

子查询

在遇到很复杂的场景时，内连接和外连接查询可能查询不出来需要的记录，就可以使用子查询

子查询就是嵌套查询，查询的语句可以作为另外一个查询语句的条件，也就是：一条查询语句里面包含了多个 select

• 子查询

  • 一个查询语句的结果作为另一个查询语句的条件
  • 有查询的嵌套，内部的查询称为子查询
  • 子查询要使用括号

• 子查询结果的三种情况

  • 子查询的结果是一个值
  • 子查询结果是单例多行
  • 子查询的结果是多行多列

子查询结果是一个值

子查询结果是一个值(单行单列)，在 WHERE 后面作为 条件

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

子查询结果是单列多行

子查询结果是单列多行，结果集类似于一个数组，父查询使用 IN 运算符

SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;

子查询结果是多行多列

子查询结果是多行多列，在 FROM 后面作为 表

SELECT 查询字段 FROM （子查询） AS 表别名 WHERE 条件;

• 子查询作为表需要取别名，否则无法访问子查询这张表中的字段

---

事务

概述

• 事务指逻辑上的一组操作，组成这组操作的单元要么全部成功，要么全部失败

• 作用：保证一组操作全部成功或者全部失败

MYSQL进行事务管理

自动事务

• 一条sql语句就是一个事务

• mysql中默认为自动事务

手动开启事务

-- 手动开启事务
-- 开启事务
start transaction;
-- 提交   
commit;  
-- 回滚
rollback;

-- 设置MYSQL中的自动提交的参数
-- 查看MYSQL中事务是否自动提交
show variables like '%commit%';
-- 设置自动提交的参数：0:OFF，1:ON
set autocommit = 0;

回滚点

在某些成功的操作完成之后，后续的操作有可能成功有可能失败，可以在当前成功的位置设置一个回滚点，供后续操作失败返回到该位置，而不是返回所有操作，这个点称之为回滚点

-- 回滚点语法
-- 设置回滚点
savepoint 回滚点名;
-- 回到回滚点
rollback to 回滚点名;

• 注意
  • 开启事务之后，要么commit，要么rollback
  • 一旦commit或者rollback，则当前的事务就结束了
  • 当回滚到指定的回滚点后事务并没有结束，需要commit或rollback结束事务

事务特性和隔离级别

事务特性

• 原子性(Atomicity)：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生

• 一致性(Consistency)：事务前后数据的完整性必须保持一致

• 持久性(Durability)：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，即使数据库发生故障也不会对其有任何影响

• 隔离性(Isolation)：事务的隔离性是指多个用户并发操作数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离，也就是事务之间互不干扰

事务隔离级别

事务在操作时的理想状态： 所有的事务之间保持隔离，互不影响

当发生并发操作时，多个用户同时访问同一个数据，则可能引发并发访问的问题

• 如果不考虑隔离性，会引发下面的问题：

  

• 可以通过设置事物隔离级别解决读的问题

  级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
  1	读未提交	read uncommitted	是	是	是
  2	读已提交	read committed	否	是	是	Oracle
  3	可重复读	repeatable read	否	否	是	MySQL
  4	串行化	serializable	否	否	否

  隔离级别越高，性能(效率)越差，安全性越高

  -- 设置事务隔离级别
  set session transaction isolation level 隔离级别;
  eg: set session transaction isolation level read uncommitted;
  
  -- 查询当前事务隔离级别
  select @@tx_isolation;
  

数据库安全性问题

数据库安全性问题请参考：数据库安全性问题

---

数据的备份和还原

语法

-- 备份
mysqldump -u用户名 -p密码 数据库 > 文件的路径:\文件名.sql

-- 恢复
mysqldump -u用户名 -p密码 数据库 < 文件的路径:\文件名.sql    -- 需要已新建好数据库
-- 或者
SOURCE 导入文件的路径;  -- 还原的时候需要先登录MySQL,并选中对应的数据库

---

数据库设计三大范式

好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响

建立科学的，规范的数据库就需要满足一些规则来优化数据的设计和存储，这些规则就称为范式

关系数据库有六种范式：第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、第四范式(4NF)、第五范式(5NF)和第六范式(6NF)

满足最低要求的范式是第一范式(1NF)，在第一范式的基础上进一步满足更多要求的称为第二范式(2NF)，其余范式以此类推，一般说来，数据库只需满足第三范式(3NF)就行了

在任何一个关系数据库中，第一范式(1NF)是对关系模式的基本要求，不满足第一范式(1NF)的数据库就不是关系数据库

1NF

• 数据库表的每一列都是不可分割的原子数据项，不能是集合、数组等非原子数据项。即表中的某个列有多个值时，必须拆分为不同的列。简而言之，第一范式每一列不可再拆分，称为原子性。

• 如果不遵守第一范式，查询出的数据还需要进一步处理(查询不方便)。遵守第一范式，需要什么字段的数据就可以直接查询什么数据(方便查询)。

---

2NF

• 在满足第一范式的前提下，表中的每一个字段都完全依赖于主键。所谓完全依赖是指不能存在仅依赖主键一部分的列。简而言之，第二范式就是在第一范式的基础上所有列完全依赖于主键列。当存在一个复合主键包含多个主键列的时候，才会发生不符合第二范式的情况。比如有一个主键有两个列，不能存在这样的属性，它只依赖于其中一个列，这就是不符合第二范式。

• 简单的说，第二范式需要满足：

  • 一张表只描述一件事情
  • 表中的每一个列都依赖于主键

• 如果不遵守第二范式，则会数据冗余导致相同数据无法区分。遵守第二范式可以减少数据冗余，通过主键来区分相同数据。

---

3NF

• 在满足第二范式的前提下，表中的每一列都直接依赖于主键，而不是通过其它的列来间接依赖于主键。简而言之，第三范式就是所有列不依赖于其它非主键列，也就是在满足 2NF 的基础上，任何非主键列不得传递依赖于主键。所谓传递依赖，指的是如果存在 A → B → C 的决定关系，则 C 传递依赖于 A 。因此，满足第三范式的数据库表应该不存在如下依赖关系： 主键列 → 非主键列x → 非主键列y 。

• 如果不遵守第三范式，可能会导致相同数据无法区分，修改数据的时候多张表都需要修改(不方便修改)。遵守第三范式通过 id 就可以区分相同数据，修改数据的时候只需要修改一张表(方便修改)。

---

总结

---