Linux云计算 |【第四阶段】RDBMS1-DAY4

主要内容：

数据库管理（创建数据库、修改数据库、删除数据库）、表管理（关系数据库的规范化1NF\2NF\3NF、创建表、修改表、删除表、复制表）、约束（分类、应用）、事务控制语言TCL

一、数据库管理

库名的命名规则

• ① 仅可以使用数字、字母、下划线、不能纯数字
• ② 区分字母大小写，具有唯一性
• ③ 不可使用指令关键字、特殊字符

1、创建数据库

• 格式：

CREATE DATABASE [IF NOT EXISTS]

[[DEFAULT] CHARACTER SET ]

[[DEFAULT] COLLATE ];

解释说明：

-  <数据库名>：创建数据库的名称。MySQL 的数据存储区将以目录方式表示 MySQL 数据库，因此数据库名称必须符合操作系统的文件夹命名规则，不能以数字开头，尽量要有实际意义。注意在 MySQL 中数据库名区分大小写。

- IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免数据库已经存在而重复创建的错误。

- [DEFAULT] CHARACTER SET：指定数据库的字符集。指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况。如果在创建数据库时不指定字符集，那么就使用系统的默认字符集。（汉语字符集：utf8mb4）

- [DEFAULT] COLLATE：指定字符集的默认校对规则。

- MySQL 的字符集（CHARACTER）和校对规则（COLLATION）是两个不同的概念。字符集是用来定义 MySQL 存储字符串的方式，校对规则定义了比较字符串的方式。

例如：

mysql> create database mydb default charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| nsd2021            |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

常见报错：再次创建同名数据库，则报错数据库已创建，不会覆盖原有数据库

mysql> create database mydb default charset utf8mb4;
ERROR 1007 (HY000): Can't create database 'mydb'; database exists

2、修改数据库

• 格式：

ALTER DATABASE [数据库名] {

[ DEFAULT ] CHARACTER SET |

[ DEFAULT ] COLLATE };

解释说明：

- ALTER DATABASE 用于更改数据库的全局特性。

- 使用 ALTER DATABASE 需要获得数据库 ALTER 权限。

- 数据库名称可以忽略，此时语句对应于默认数据库。

- CHARACTER SET 子句用于更改默认的数据库字符集。

3、删除数据库

• 格式：DROP DATABASE [ IF EXISTS ] ;

解释说明：

- ：指定要删除的数据库名。

- IF EXISTS：用于防止当数据库不存在时发生错误。

- DROP DATABASE：删除数据库中的所有表格并同时删除数据库。

- 如果要使用 DROP DATABASE，需要获得数据库 DROP 权限。

二、表管理

良好的数据库设计表现在以下几方面：

- 访问效率高；

- 减少数据冗余，节省存储空间，便于进一步扩展；

- 可以使应用程序的开发变得更容易；

1、范式（NF，Normal Form）

数据库的设计范式，是符合某一种级别的关系模式的集合，构造数据库必须遵循一定的规则，在关系数据库中，这种规则就是范式（关系数据库中的每一个关系都要满足一定的规范）；根据满足规范的条件不同，可以分为5个等级：第一范式（1NF）、第二范式（2NF）……第五范式（5NF）。一般情况下，只要把数据规范到第三范式标准就可满足需求。

---

① 第一范式（1NF）--> 无重复的列

- 在一个关系中，消除重复字段，且各字段都是最小的逻辑存储单位。即要满足原子性。

- 第一范式是第二和第三范式的基础，是最基本的范式。第一范式包括下列指导原则。

  （1）数据组的每个属性只可以包含一个值。

  （2）关系中的每个数组必须包含相同数量的值。

  （3）关系中的每个数组一定不能相同。

- 在任何一个关系数据库中，第一范式是对关系模式的基本要求，不满足第一范式的数据库就不是关系型数据库。

② 第二范式（2NF）--> 属性完全依赖于主键（非主属性非部分依赖于主关键字）

- 第二范式是在第一范式的基础上建立起来的，即满足第二范式必先满足第一范式（1NF）。

- 第二范式要求数据库表中的每个实体（即各个记录行）必须可以被唯一地区分。为实现区分各行记录通常需要为表设置一个“区分列”，用以存储各个实体的唯一标识。这个唯一属性列被称为主关键字或主键。

- 第二范式要求实体的属性完全依赖于主关键字，即不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体（新表），新实体与原实体之间是一对多的关系。

③ 第三范式（3NF）--> 属性不依赖于其它非主属性

- 第三范式是在第二范式的基础上建立起来的，即满足第三范式必先满足第二范式。

- 第三范式要求关系表不存在非关键字列对任意候选关键字列的传递函数依赖，也就是说，第三范式要求一个关系表中不包含已在其他表中包含的非主关键字信息。

- 除主键外，其他字段必须依赖主键。

补充：完全依赖、部分依赖、传递依赖

① 部分函数依赖：设X,Y是关系R的两个属性集合，存在X→Y，若X’是X的真子集，存在X’→Y，则称Y部分函数依赖于X。

例子：学生基本信息表R中（学号，身份证号，姓名）当然学号属性取值是唯一的，在R关系中，（学号，身份证号）->（姓名），（学号）->（姓名），（身份证号）->（姓名）；所以姓名部分函数依赖与（学号，身份证号）；

② 完全函数依赖：设X,Y是关系R的两个属性集合，X’是X的真子集，存在X→Y，但对每一个X’都有X’!→Y，则称Y完全函数依赖于X。

例子：学生基本信息表R（学号，班级，姓名）假设不同的班级学号有相同的，班级内学号不能相同，在R关系中，（学号，班级）->（姓名），但是（学号）->(姓名)不成立，（班级）->(姓名)不成立，所以姓名完全函数依赖与（学号，班级）；

③ 传递函数依赖：设X,Y,Z是关系R中互不相同的属性集合，存在X→Y(Y !→X),Y→Z，则称Z传递函数依赖于X。

例子：在关系R(学号 ,宿舍, 费用)中，(学号)->(宿舍),宿舍！=学号，(宿舍)->(费用),费用!=宿舍，所以符合传递函数的要求；

2、表管理语句

1）创建表

• 格式：

CREATE TABLE

(

字段名称1 数据类型 [(长度) 约束],

字段名称2 数据类型 [(长度) 约束],

字段名称3 数据类型 [(长度) 约束],

....

);

• 信息种类：

常用数据类型：

数据类型	描述
tinyint(m)	1个字节 范围(-128~127)
smallint(m)	2个字节 范围(-32768~32767)
mediumint(m)	3个字节 范围(-8388608~8388607)
int(m)	4个字节 范围(-2147483648~2147483647)
bigint(m)	8个字节 范围(+-9.22*10的18次方)
float(m,d)	单精度浮点型 8位精度(4字节) m总个数，d小数位
double(m,d)	双精度浮点型 16位精度(8字节) m总个数，d小数位
decimal(m,d)	m表示十进制数字总的个数，d表示小数点后面数字的位数。常用于货币
char(n)	固定长度，最多255个字符，不够指定字符格式时在右边用空格补全
varchar(n)	不固定长度，最多65535个字符，按数据实际大小分配存储空间
tinytext	可变长度，最多255个字符
text	可变长度，最多65535个字符
mediumtext	可变长度，最多2的24次方-1个字符
longtext	可变长度，最多2的32次方-1个字符
date	日期 '2008-12-2'
time	时间 '12:25:36'
datetime	日期时间 '2008-12-2 22:06:44'
timestamp	自动存储记录修改时间
enum(选项1, 选项2, ...)	单选字符串数据类型，适合存储表单界面中的“单选值”
set(选项1,选项2, ...)	多选字符串数据类型，适合存储表单界面的“多选值”

例如：在数据库mydb中，创建departments部门表（id,dept_name）

mysql> use mydb;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| mydb       |
+------------+
1 row in set (0.00 sec)
mysql> create table departments (id int, dept_name varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> desc departments;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int(11)     | YES  |     | NULL    |       |
| dept_name | varchar(20) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

2）修改表

① 修改字段名

• 格式：ALTER TABLE CHANGE 旧字段名 新字段名 数据类型;

mysql> alter table departments change id dept_id int;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc departments;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_id   | int(11)     | YES  |     | NULL    |       |
| dept_name | varchar(20) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

② 修改字段的类型或约束

• 格式：ALTER TABLE MODIFY 字段名 数据类型;

mysql> alter table departments modify dept_name varchar(10);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc departments;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_id   | int(11)     | YES  |     | NULL    |       |
| dept_name | varchar(10) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

③ 添加新字段

• 格式：ALTER TABLE ADD 字段名 数据类型;

mysql> alter table departments add manager_id int;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc departments;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| dept_id    | int(11)     | YES  |     | NULL    |       |
| dept_name  | varchar(10) | YES  |     | NULL    |       |
| manager_id | int(11)     | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

④ 删除字段

• 格式：ALTER TABLE DROP 字段名;

mysql> alter table departments drop manager_id;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc departments;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_id   | int(11)     | YES  |     | NULL    |       |
| dept_name | varchar(10) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

⑤ 修改表名

• 格式：ALTER TABLE RENAME TO ;

mysql> alter table departments rename to depts;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| depts          |
+----------------+
1 row in set (0.00 sec)

⑥ 删除表

• 格式：DROP TABLE [IF EXISTS] ;

mysql> drop table depts;
Query OK, 0 rows affected (0.00 sec)

3）表复制

① 仅复制表结构

• 格式：CREATE TABLE LIKE ;

mysql> create table departments like nsd2021.departments;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| departments    |
+----------------+
1 row in set (0.00 sec)
mysql> desc departments;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| dept_id   | int(4)      | NO   | PRI | NULL    | auto_increment |
| dept_name | varchar(10) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

② 复制表结构及数据

• 格式：CREATE TABLE SELECT 字段, ... FROM ;

mysql> create table departments2 select * from nsd2021.departments;
Query OK, 13 rows affected (0.01 sec)
Records: 13  Duplicates: 0  Warnings: 0
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| departments    |
| departments2   |
+----------------+
2 rows in set (0.00 sec)
mysql> desc departments2;   //由于表结构索引冲突，无法复制
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_id   | int(4)      | NO   |     | 0       |       |
| dept_name | varchar(10) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

补充：仅复制表结构

• 格式：CREATE TABLE SELECT 字段, ... FROM where 1=2;

三、约束条件

约束是一种限制，用于限制表中的数据，为了保证表中数据的准确性和可靠性；创建表时可以添加约束；修改表时可以添加约束；

1、设置约束条件

• null 允许为空（默认设置）
• not null 不允许为null（空）
• key 键值类型
• default 设置默认值，缺省为null
• extra 额外设置

补充：键值类型（根据数据存储要求，选择键值）

① index 普通索引

② unique 唯一索引

③ fulltest 全文索引

④ primary key 主键

⑤ foreign key 外键

 

2、约束分类

• PRIMARY KEY 主键：用于保证该字段的值具有唯一性并且非空。
• NOT NULL 非空：用于保证该字段的值不能为空。
• DEFAULT 默认值：用于保证该字段有默认值。
• UNIQUE 唯一索引：用于保证该字段的值具有唯一性，可以为空。
• FOREIGN KEY 外键：用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某些的值。

3、约束可应用在列级或表级

列表所有约束均支持，但外键约束没有效果；表级约束可以支持主键、唯一、外键约束。

---

约束应用示例：

1）列级约束（创建表字段后面添加约束）

- 创建表时使用约束

mysql> create table employees(
    -> employee_id int primary key,      //主键约束
    -> name varchar(20) not null,       //非空约束
    -> gender enum('男','女'),
    -> email varchar(20) unique,         //唯一约束
    -> nation varchar(10) default '汉族'    //默认值约束
    -> );
Query OK, 0 rows affected (0.00 sec)
mysql> desc employees;
+-------------+-------------------+------+-----+---------+-------+
| Field       | Type              | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+---------+-------+
| employee_id | int(11)           | NO   | PRI | NULL    |       |
| name        | varchar(20)       | NO   |     | NULL    |       |
| gender      | enum('男','女')   | YES  |     | NULL    |       |
| email       | varchar(20)       | YES  | UNI | NULL    |       |
| nation      | varchar(10)       | YES  |     | 汉族    |       |
+-------------+-------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

2）表级约束（创建表字段之后末尾添加约束）

- 创建表时使用约束

mysql> create table employees2 (
    -> employee_id int,
    -> name varchar(20),
    -> email varchar(20),
    -> dept_id int,
    -> primary key (employee_id),     //主键约束
    -> unique (email),      //唯一约束
    -> foreign key (dept_id) references departments (dept_id)    //外键约束
    -> );
Query OK, 0 rows affected (0.00 sec)
mysql> desc employees2;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| employee_id | int(11)     | NO   | PRI | NULL    |       |
| name        | varchar(20) | YES  |     | NULL    |       |
| email       | varchar(20) | YES  | UNI | NULL    |       |
| dept_id     | int(11)     | YES  | MUL | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

3）查看约束

• 命令：select * from information_schema.table_constraints

mysql> select * from information_schema.table_constraints 
    -> where table_name='employees2'\G
*************************** 1. row ***************************
CONSTRAINT_CATALOG: def
 CONSTRAINT_SCHEMA: mydb
   CONSTRAINT_NAME: PRIMARY
      TABLE_SCHEMA: mydb
        TABLE_NAME: employees2
   CONSTRAINT_TYPE: PRIMARY KEY
*************************** 2. row ***************************
CONSTRAINT_CATALOG: def
 CONSTRAINT_SCHEMA: mydb
   CONSTRAINT_NAME: email
      TABLE_SCHEMA: mydb
        TABLE_NAME: employees2
   CONSTRAINT_TYPE: UNIQUE
*************************** 3. row ***************************
CONSTRAINT_CATALOG: def
 CONSTRAINT_SCHEMA: mydb
   CONSTRAINT_NAME: employees2_ibfk_1
      TABLE_SCHEMA: mydb
        TABLE_NAME: employees2
   CONSTRAINT_TYPE: FOREIGN KEY
3 rows in set (0.00 sec)

4）自定义约束名称

mysql> create table employees3 (
    -> employee_id int,
    -> name varchar(20),
    -> dept_id int,
    -> constraint pk primary key(employee_id),   //不报错，不生效
    -> constraint fk_employees3_departments foreign key(dept_id) references departments(dept_id)
-> );

5）删除约束

• 格式：ALTER TABLE DROP FOREIGN KEY

mysql> alter table employees3
    -> drop foreign key fk_employees3_departments;

 例如：创建员工数据的三张表employees、departments、salary

① 创建部门表

create table departments(
  dept_id int auto_increment primary key,
  dept_name varchar(10) unique
);

② 创建员工表

create table employees(
  employee_id int(6) auto_increment primary key,
  name varchar(10) not null,
  hire_date date,
  birth_date date,
  email varchar(25) unique,
  phone_number char(11),
  dept_id int,
  foreign key(dept_id) references departments(dept_id)
);

③ 创建工资表

create table salary(
  id int(11) auto_increment primary key,
  date date,
  employee_id int(6),
  foreign key(employee_id) references employees(employee_id),
  basic int(6),
  bonus int(6)
);

四、事务控制语言TCL

1、数据库事务

数据库事务指的是一组数据操作，主要用于处理操作量大，复杂度高的数据。 在MySQL中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。MySQL默认存储引擎时Innodb。事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。

事务用来管理 insert、update、delete 语句

2、事务必须满足的4个条件（ACID）

- 原子性(Atomicity)：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

- 一致性(Consistency)：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

- 隔离性(Isolation)：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。（补充：涉及锁表）

- 持久性(Durability)：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

3、事务控制语句

• BEGIN 或START TRANSACTION显式地开启一个事务；
• COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
• ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
• SAVEPOINT identifier(标识点)，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
• RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
• ROLLBACK TO identifier 把事务回滚到标记点；
• SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有读未提交READ UNCOMMITTED、读提交READ COMMITTED、可重复读REPEATABLE READ 和 串行化SERIALIZABLE。

4、MySQL事物处理的方法

用 BEGIN, ROLLBACK, COMMIT来实现

① BEGIN开始一个事务

② ROLLBACK事务回滚

③ COMMIT提交事务

直接用 SET 来改变 MySQL 的自动提交模式

① SET AUTOCOMMIT=0 //禁止自动提交

② SET AUTOCOMMIT=1 //开启自动提交

5、事务的创建

① 隐式事务

事务没有明显的开启和结束的标记。如INSERT、UPDATE、DELETE语句。

mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)

② 显示事务

事务具有明显的开启和结束的标记，必须先设置自动提交功能为禁用。

mysql> set autocommit=0;     //只对当前会话生效
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%autocommit%';   //查看变量方式显示事务
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

6、创建事务步骤

① 开启事务，需要先将自动提交功能禁用，否则自动提交如INSERT、UPDATE、DELETE管理事务语句后，将无法形成完整的事务；

> set autocommit=0;

> start transaction;   //开启事务（可选）

② 编写事务语句：INSERT、UPDATE、DELETE语句；

③ 结束事务或回滚撤销事务

> commit | rollback;

事务应用示例：

1）创建银行表并插入数据

mysql> use mydb;    //切换数据库
Database changed
mysql> create table bank(     //创建数据库
    ->    id int primary key,
    ->    name varchar(20),
    ->    balance int
-> );
Query OK, 0 rows affected (0.01 sec)
 
mysql> insert into bank values(1,'tom',10000),(2,'jerry',10000);  //插入语句
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from bank;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | tom   |   10000 |
|  2 | jerry |   10000 |
+----+-------+---------+
2 rows in set (0.00 sec)

2）使用事务：正常提交

mysql> set autocommit=0;     //禁用自动提交功能
mysql> update bank set balance=balance-1000 where name='tom';
mysql> update bank set balance=balance+1000 where name='jerry';
mysql> select * from bank;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | tom   |    9000 |
|  2 | jerry |   11000 |
+----+-------+---------+
2 rows in set (0.00 sec)

# 此时在另一终端查看bank表，数据并未改变

mysql> select * from bank;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | tom   |   10000 |
|  2 | jerry |   10000 |
+----+-------+---------+
2 rows in set (0.00 sec)

mysql> commit;     //提交，结束事务
mysql> select * from bank;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | tom   |    9000 |
|  2 | jerry |   11000 |
+----+-------+---------+
2 rows in set (0.00 sec)

3）使用事务：回滚撤销

mysql> set autocommit=0;    //禁用自动提交功能
mysql> update bank set balance=balance+1000 where name='tom';
mysql> update bank set balance=balance-1000 where name='jerry';
mysql> select * from bank;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | tom   |   10000 |
|  2 | jerry |   10000 |
+----+-------+---------+
2 rows in set (0.00 sec)

# 此时在另一终端查看bank表，数据并未改变

mysql> select * from bank;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | tom   |    9000 |
|  2 | jerry |   11000 |
+----+-------+---------+
2 rows in set (0.00 sec)

mysql> rollback;     //提交，结束事务
mysql> select * from bank;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | tom   |    9000 |
|  2 | jerry |   11000 |
+----+-------+---------+
2 rows in set (0.00 sec)

五、事务隔离

1、事务隔离要解决的问题

① 脏读：脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，即不存在的数据。读到了但并不一定为最终存在的数据，这就是脏读。

② 可重复读：可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的。通常针对数据更新（UPDATE）操作。

③ 不可重复读：对比可重复读，不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据更新（UPDATE）操作。

④ 幻读：幻读是针对数据插入（INSERT）操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，但其实是事务B刚插入进来的，让用户感觉很魔幻，感觉出现了幻觉，这就叫幻读。

2、事务隔离级别

- 读未提交（READ UNCOMMITTED）

- 读提交（READ COMMITTED）

- 可重复读（REPEATABLE READ）

- 串行化（SERIALIZABLE）

从上往下，隔离强度逐渐增强，性能逐渐变差。采用哪种隔离级别要根据系统需求权衡决定，其中，可重复读（REPEATABLE READ）是 MySQL 的默认级别。

事务隔离其实就是为了解决上面提到的脏读、不可重复读、幻读这几个问题。只有串行化的隔离级别解决了全部这3 个问题，其他的3 个隔离级别都有缺陷。

3、设置事务隔离级别

① 查看当前事务隔离级别（@@代表全局）

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |    //可重复读
+-----------------+
1 row in set (0.00 sec)

② 设置隔离事务级别

mysql> set session transaction isolation level read uncommitted;    //设置成读未提交
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |    //读未提交
+------------------+
1 row in set (0.00 sec)

③ 测试

- 在第一个终端上执行

mysql> set autocommit=0;     //禁用自动提交功能（开启事务）
mysql> update bank set balance=balance+1000 where name='tom';
mysql> select * from bank;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | tom   |   10000 |
|  2 | jerry |   11000 |
+----+-------+---------+
2 rows in set (0.00 sec)

- 在第二个终端上执行

mysql> select * from bank;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | tom   |    9000 |
|  2 | jerry |   11000 |
+----+-------+---------+
2 rows in set (0.00 sec)
mysql> set session transaction isolation level read uncommitted;   //设置成读未提交
mysql> set autocommit=0;
mysql> select * from bank;   //此时tom上已经增加1000
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | tom   |   10000 |
|  2 | jerry |   11000 |
+----+-------+---------+
2 rows in set (0.00 sec)

- 回到第一个终端对事务进行回滚

mysql> rollback;
mysql> select * from bank;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | tom   |    9000 |
|  2 | jerry |   11000 |
+----+-------+---------+
2 rows in set (0.00 sec)

- 在第2个终端上重新查询

mysql> select * from bank;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | tom   |    9000 |
|  2 | jerry |   11000 |
+----+-------+---------+
2 rows in set (0.00 sec)

六、SAVEPOINT应用

使用mysql中的savepoint保存点来实现事务的部分回滚

• 格式：

SAVEPOINT identifier

ROLLBACK [WORK] TO [SAVEPOINT] identifier

RELEASE SAVEPOINT identifier

解释说明：

- 使用 SAVEPOINT identifier 来创建一个名为identifier(标识符) 的回滚点

- ROLLBACK TO identifier，回滚到指定名称的SAVEPOINT identifier

- 使用 RELEASE SAVEPOINT identifier 来释放删除保存点

- 如果当前事务具有相同名称的保存点，则将删除旧的保存点并设置一个新的保存点。

- 如果执行START TRANSACTION，COMMIT和ROLLBACK语句，则将删除当前事务的所有保存点。

- 如果在未执行COMMIT的语句状态下，直接退出MySQL，保存点会直接释放，回到原始状态；就好比直接退出事务，数据不会发生任何改变；

SAVEPOINT示例1：

mysql> set autocommit=0;
mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> update bank set balance=balance+1000 where name='tom';
mysql> select * from bank;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | tom   |   10000 |
|  2 | jerry |   11000 |
+----+-------+---------+
2 rows in set (0.00 sec)
mysql> savepoint p1;       //创建保存点p1

mysql> update bank set balance=balance-1000 where name='jerry';
mysql> select * from bank;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | tom   |   10000 |
|  2 | jerry |   10000 |
+----+-------+---------+
2 rows in set (0.00 sec)
mysql> rollback to p1;       //回滚到保存点p1
mysql> select * from bank;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | tom   |   10000 |
|  2 | jerry |   11000 |
+----+-------+---------+
2 rows in set (0.00 sec)

mysql> exit;     //退出并重新连接数据库

因从未执行commit提交操作，所以查到的结果与执行事务之前查到的结果一样。

mysql> select * from mydb.bank;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | tom   |    9000 |
|  2 | jerry |   11000 |
+----+-------+---------+
2 rows in set (0.00 sec)

SAVEPOINT示例2：

mysql> set autocommit=0;
mysql> update bank set balance=balance+1000 where name='tom';
mysql> select * from bank;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | tom   |   11000 |
|  2 | jarry |   10000 |
+----+-------+---------+
2 rows in set (0.00 sec)
mysql> savepoint aaa;     //保存第一个保存点aaa

mysql> update bank set balance=balance-1000 where name='jarry';
mysql> select * from bank;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | tom   |   11000 |
|  2 | jarry |    9000 |
+----+-------+---------+
2 rows in set (0.00 sec)
mysql> savepoint aaa;    //保存第二个保存点aaa（相同名字）

mysql> rollback to aaa;

若当前事务设置了两个相同名称的保存点，则将旧保存点删除，并设以新保存点为保存点

mysql> select * from bank;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | tom   |   11000 |
|  2 | jarry |    9000 |
+----+-------+---------+
2 rows in set (0.00 sec)

补充知识：MySQL键值使用

索引，类似于书的目录，对表中字段值进行排序，索引类型包括：Btree、B+tree、hash

优点：通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性，可以加快数据的查询速度；

缺点：当对表中的数据进行增加、删除和修改的时候，索引也要动态的调整，降低了数据的维护速度，索引需要占物理空间；

1、index普通索引

使用规则：

• ① 一个表中可以有多个Index字段
• ② 字段的值允许重复，且可以赋Null值
• ③ 通常把它作为查询条件的字段，设置为Index字段
• ④ index字段标志是MUL

1）创建索引

方法1：建表时创建索引

• 格式：index(字段名)

方法2：在已有的表里创建索引

• 格式：create index 索引名 on 表名(字段名);

2）删除索引

• 格式：drop index 索引名 on 表名;

3）查看索引

• 格式：show index from 表名 \G;

 

2、primary key主键

使用规则：

• ① 字段值不允许重复，且不允许赋NULL值
• ② 一个表中只能有一个primart key字段
• ③ 多个字段都最为主键，称为复合主键，必须一起创建
• ④ 主键字段的标志为PRI
• ⑤ 主键通常与auto_increment使用
• ⑥ 通常把表中唯一标识记录的字段设置为主键

1）创建主键

方法1：建表时创建主键

• 格式：primary key(字段名)

方法2：在已有表里添加主键

• 格式：alter table 表名 add primary key(字段名);

2）删除主键

• 格式：alter table 表名 drop primary key;

 

3、foreign key外键

功能：插入记录时，字段值在另一个表字段值范围内选择；

使用规则：

• ① 表存储引擎必须是innodb
• ② 字段类型要一致
• ③ 被参照字段必须要是索引类型的一种（Primary key）

1）创建外键

• 格式：

create table 表名 (

字段名列表,

foreign key(字段名) references 表名(字段名) //指定外键

on update cascade //同步更新

on delete cascade //同步删除

)engine=innodb; //指定存储引擎

2）删除外键

• 格式：alter table 表名 drop foreign key 外键名;

思维导图：

小结：

本篇章节为【第四阶段】RDBMS1-DAY4 的学习笔记，这篇笔记可以初步了解到 数据库管理（创建数据库、修改数据库、删除数据库）、表管理（关系数据库的规范化1NF\2NF\3NF、创建表、修改表、删除表、复制表）、约束（分类、应用）、事务控制语言TCL。除此之外推荐参考相关学习网址：

• Mysql 中 MyISAM 和 InnoDB 的区别有哪些？

---

Tip：毕竟两个人的智慧大于一个人的智慧，如果你不理解本章节的内容或需要相关笔记、视频，可私信小安，请不要害羞和回避，可以向他人请教，花点时间直到你真正的理解。