mysql（三）:多表设计、事务、DCL

1 多表设计

# 创建部门表
CREATE TABLE dept (id int PRIMARY KEY auto_increment,dname VARCHAR(50) not null);

# 创建人员表
CREATE TABLE person (
  id int PRIMARY KEY AUTO_INCREMENT,
  name varchar(32) NOT NULL,
  age tinyint unsigned,
  salary decimal(8,2),
  dept_id int
) ;

上面两个表中每个人员都应该有指定的部门，但是实际上在没有约束的情况下人员是可以没有部门的或者也可以添加一个不存在的部门，这显然是不合理的。

1.1 外键约束（foreign key）

1.1.1 约束

• 约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、关联性

1.1.2 主表和从表

• 若同一个数据库中，B表的外键与A表的主键相对应，则A表为主表，B表为从表。

• 示例

1.1.2 foreign key

• 功能 ：建立表与表之间的某种约束的关系，由于这种关系的存在，能够让表与表之间的数据，更加的完整，关连性更强，为了具体说明创建如下部门表和人员表。

• 语法（可在 CREATE TABLE 和 ALTER TABLE 时使用）：

  [CONSTRAINT symbol] FOREIGN KEY（外键字段） 
  
  REFERENCES tbl_name (主表主键)
  
  [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
  
  [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
  

  • restrict(默认) : on delete restrict on update restrict
    • 当主表删除记录时，如果从表中有相关联记录则不允许主表删除
    • 当主表更改主键字段值时，如果从表有相关记录则不允许更改
  • cascade ：数据级联更新 on delete cascade on update cascade
    • 当主表删除记录或更改被参照字段的值时,从表会级联更新
  • set null : on delete set null on update set null
    • 当主表删除记录时，从表外键字段值变为null
    • 当主表更改主键字段值时，从表外键字段值变为null

1.1.3 可在CREATE TABLE 和 ALTER TABLE 时使用

• 建表时创建

  # 创建表时直接简历外键
  CREATE TABLE person (
    id int PRIMARY KEY AUTO_INCREMENT,
    name varchar(32) NOT NULL,
    age tinyint unsigned,
    salary decimal(10,2),
    dept_id int ,
    constraint dept_fk foreign key(dept_id) references dept(id));
  

• 建立表后增加外键

  alter table person add constraint dept_fk foreign key(dept_id) references dept(id);

  注意：

  1. 并不是任何情况表关系都需要建立外键来约束，如果没有类似上面的约束关系时也可以不建立。
  2. 从表的外键字段数据类型与指定的主表主键应该相同。

1.1.4 通过外键名称解除外键约束

• 解除外键约束

  alter table person drop foreign key dept_fk;
  
  # 查看外键名称
  show create table person;
  

  注意：删除外键后发现desc查看索引标志还在，其实外键也是一种索引，需要将外键名称的索引删除之后才可以。

1.2 表关联关系

当我们应对复杂的数据关系的时候，数据表的设计就显得尤为重要，认识数据之间的依赖关系是更加合理创建数据表关联性的前提。一对多和多对多是常见的表数据关系：

1.2.1 一对多关系

• 一对多

  一张表中有一条记录可以对应另外一张表中的多条记录；但是反过来，另外一张表的一条记录
  只能对应第一张表的一条记录，这种关系就是一对多或多对一

  举例： 一个人可以拥有多辆汽车，每辆车登记的车主只有一人。

  create table person(
    id varchar(32) primary key,
    name varchar(30),
    age int
  );
  
  create table car(
    id varchar(32) primary key,
    brand varchar(30),
    price decimal(10,2),
    pid varchar(32),
    foreign key(pid) references person(id)
  );
  

1.2.2 多对多关系

• 多对多

  一对表中（A）的一条记录能够对应另外一张表（B）中的多条记录；同时B表中的一条记录
  也能对应A表中的多条记录

  举例：一个运动员可以报多个项目，每个项目也会有多个运动员参加,这时为了表达多对多关系需要单独创建关系表。

  CREATE TABLE athlete (
    id int primary key AUTO_INCREMENT,
    name varchar(30),
    age tinyint NOT NULL,
    country varchar(30) NOT NULL
  );
  
  CREATE TABLE item (
    id int primary key AUTO_INCREMENT,
    rname varchar(30) NOT NULL
  );
  
  CREATE TABLE athlete_item (
     id int primary key auto_increment,
     aid int NOT NULL,
     tid int NOT NULL,
     FOREIGN KEY (aid) REFERENCES athlete (id),
     FOREIGN KEY (tid) REFERENCES item (id)
  );
  

1.3 E-R模型图

• 定义

E-R模型(Entry-Relationship)即 实体-关系 数据模型,用于数据库设计
用简单的图(E-R图)反映了现实世界中存在的事物或数据以及他们之间的关系

• 实体、属性、关系

​ 实体

1、描述客观事物的概念
2、表示方法 ：矩形框
3、示例 ：一个人、一本书、一杯咖啡、一个学生

​ 属性

1、实体具有的某种特性
2、表示方法 ：椭圆形
3、示例
   学生属性 ：学号、姓名、年龄、性别、专业 ... 
   感受属性 ：悲伤、喜悦、刺激、愤怒 ...

​ 关系

1、实体之间的联系
2、一对多关联(1:n)
3、多对多关联(m:n) 

• E-R图的绘制

矩形框代表实体,菱形框代表关系,椭圆形代表属性

2 事务控制

2.1 事务概述

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，如果操作就必须同时操作成功，如果有一个不成功则所有数据都不动。这时候数据库操作语句就构成一个事务。事务主要处理数据的增删改操作。

• 定义

一件事从开始发生到结束的过程

• 作用

确保数据操作过程中的数据完整和使用安全。

2.2 事务操作

1. 开启事务

   mysql>begin; 

2. 开始执行事务中的若干条SQL命令（增删改）
3. 终止事务，若begin之后使用commit提交事务或者使用rollback进行事务回滚。

   mysql>commit; # 事务中SQL命令都执行成功,提交到数据库,结束!
   mysql>rollback; # 有SQL命令执行失败,回滚到初始状态,结束!

注意：事务操作只针对数据操作。rollback不能对数据库，数据表结构操作恢复。

2.3 事务四大特性

1. 原子性（atomicity）

一个事务必须视为一个不可分割的最小工作单元，对于一个事务来说，不可能只执行其中的一部分操作,整个事务中的所有操作要么全部提交成功，要么全部失败回滚

2. 一致性（consistency）

事务完成时，数据必须处于一致状态，数据的完整性约束没有被破坏。

3. 隔离性（isolation）

数据库允许多个并发事务同时对其数据进行读写和修改的能力，而多个事务相互独立。隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

4. 持久性（durability）

一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。

2.4 事务隔离级别

事务四大特性中的隔离性是在使用事务时最为需要注意的特性，因为隔离级别不同带来的操作现象也有区别

• 隔离级别

  • 读未提交：read uncommitted

    事物A和事物B，事物A未提交的数据，事物B可以读取到
    这里读取到的数据叫做“脏数据”
    这种隔离级别最低，这种级别一般是在理论上存在，数据库隔离级别一般都高于该级别

  • 读已提交：read committed

    事物A和事物B，事物A提交的数据，事物B才能读取到
    这种隔离级别高于读未提交
    换句话说，对方事物提交之后的数据，我当前事物才能读取到
    这种级别可以避免“脏数据”
    这种隔离级别会导致“不可重复读取”

  • 可重复读：repeatable read

    事务A和事务B，事务A提交之后的数据，事务B读取不到
    事务B是可重复读取数据
    这种隔离级别高于读已提交
    MySQL默认级别
    虽然可以达到可重复读取，但是会导致“幻像读”

  • 串行化：serializable

    事务A和事务B，事务A在操作数据库时，事务B只能排队等待
    这种隔离级别很少使用，吞吐量太低，用户体验差
    这种级别可以避免“幻像读”，每一次读取的都是数据库中真实存在数据，事务A与事务B串行，而不并发

3 数据库安全和管理

3.1 数据库备份

1. 备份命令格式

mysqldump -u 用户名 -p 源库名 > ~/stu.sql

2. 恢复命令格式

mysql -u root -p 目标库名 < stu.sql

3.2 MySQL远程连接

更改配置文件，重启服务！
1.cd /etc/mysql/mysql.conf.d
2.sudo vi mysqld.cnf  找到43行左右,加 # 注释
   # bind-address = 127.0.0.1
   
3.保存退出
4.sudo service mysql restart
5.进入mysql修改用户表host值 
  use mysql;
  update user set host='%' where user='root';
6.刷新权限
  flush privileges;

3.3 添加用户和授权

1. 用root用户登录mysql
   mysql -u root -p
   
2. 添加用户 % 表示自动选择可用IP
   CREATE USER 'username'@'host' IDENTIFIED BY 'password';
   
3. 权限管理

   # 增加权限
   grant 权限列表 on 库.表 to "用户名"@"%" identified by "密码" with grant option;
   
   # 删除权限
   revoke insert,update,select on 库.表 from 'user'@'%';
   
4. 刷新权限
   flush privileges;
   
5. 删除用户
   drop user "用户名"@"%"

权限列表

all privileges ，select ，insert ，update，delete，alter，create，drop等。
库.表 ： *.* 代表所有库的所有表

示例

1. 创建用户
  mysql>create user  'work'@'%'  identified by '123';
  
2. 添加授权用户work,密码123,对所有库的所有表有所有权限
  mysql>grant all privileges on *.* to 'work'@'%' identified by '123' with grant option;
  mysql>flush privileges;
  
3. 添加用户duty，密码123,对books库中所有表有查看，插入权限
  mysql>grant select,insert on books.* to 'duty'@'%' identified by '123' with grant option;
  mysql>flush privileges;
  
4. 删除work用户的删除权限
  mysql>revoke delete on *.* from "work"@"%";
  
5. 删除用户duty
  drop user "duty"@"%";