数据库管理、表管理、约束、事务控制语言TCL

作者: 秋天丢了李姑娘 | 来源:发表于2021-08-06 19:37 被阅读0次

数据库管理

创建数据库

语法：

CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>] 
[[DEFAULT] COLLATE <校对规则名>];

[ ]中的内容是可选的
<数据库名>：创建数据库的名称。MySQL 的数据存储区将以目录方式表示 MySQL 数据库，因此数据库名称必须符合操作系统的文件夹命名规则，不能以数字开头，尽量要有实际意义。
IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免数据库已经存在而重复创建的错误。
[DEFAULT] CHARACTER SET：指定数据库的字符集。指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况。如果在创建数据库时不指定字符集，那么就使用系统的默认字符集。
[DEFAULT] COLLATE：指定字符集的默认校对规则。
MySQL 的字符集（CHARACTER）和校对规则（COLLATION）是两个不同的概念。字符集是用来定义 MySQL 存储字符串的方式，校对规则定义了比较字符串的方式。

修改数据库

语法：

ALTER DATABASE [数据库名] { 
[ DEFAULT ] CHARACTER SET <字符集名> |
[ DEFAULT ] COLLATE <校对规则名>}

ALTER DATABASE 用于更改数据库的全局特性。
使用 ALTER DATABASE 需要获得数据库 ALTER 权限。
数据库名称可以忽略，此时语句对应于默认数据库。
CHARACTER SET 子句用于更改默认的数据库字符集。

删除数据库

语法：

DROP DATABASE [ IF EXISTS ] <数据库名>

<数据库名>：指定要删除的数据库名。
IF EXISTS：用于防止当数据库不存在时发生错误。
DROP DATABASE：删除数据库中的所有表格并同时删除数据库。
如果要使用 DROP DATABASE，需要获得数据库 DROP 权限。

表管理

关系数据库的规范化

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

访问效率高
减少数据冗余，节省存储空间，便于进一步扩展
可以使应用程序的开发变得更容易

关系数据库的规范化理论为：关系数据库中的每一个关系都要满足一定的规范。根据满足规范的条件不同，可以分为6个等级：第一范式（1NF）、第二范式（2NF）……第五范式（5NF）。其中，NF是Normal Form的缩写。一般情况下，只要把数据规范到第三范式标准就可以满足需要了。

第一范式（1NF）

在一个关系中，消除重复字段，且各字段都是最小的逻辑存储单位。即，要满足原子性(不可拆分的)。
第一范式是第二和第三范式的基础，是最基本的范式。第一范式包括下列指导原则。
（1）数据组的每个属性只可以包含一个值。
（2）关系中的每个数组必须包含相同数量的值。
（3）关系中的每个数组一定不能相同。
在任何一个关系数据库中，第一范式是对关系模式的基本要求，不满足第一范式的数据库就不是关系型数据库。

第二范式（2NF）

第二范式是在第一范式的基础上建立起来的，即满足第二范式必先满足第一范式（1NF）。
第二范式要求数据库表中的每个实体（即各个记录行）必须可以被唯一地区分。
为实现区分各行记录通常需要为表设置一个“区分列”，用以存储各个实体的唯一标识。这个唯一属性列被称为主关键字或主键（不可重复的）。
第二范式要求实体的属性完完全依赖于主关键字，即不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。

第三范式（3NF）

第三范式是在第二范式的基础上建立起来的，即满足第三范式必先满足第二范式。
第三范式要求关系表不存在非关键字列对任意候选关键字列的传递函数依赖，也就是说，第三范式要求一个关系表中不包含已在其他表中包含的非主关键字信息。
除主键外，其他字段必须依赖主键(主键与字段关系)。

表管理语句

创建表

语法：

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
mysql> create database mydb default charset utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql> use mydb;
Database changed

# 创建部门表
mysql> create table departments (
    ->   id int,
    ->   dept_name varchar(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

修改表

修改列名

语法：

ALTER TABLE 表
CHANGE [COLUMN] 列表 数据类型

示例：

mysql> alter table departments
    -> change id dept_id int;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改列的类型或约束

语法：

ALTER TABLE 表
MODIFY [COLUMN] 列名 类型

示例：

mysql> alter table departments
    -> modify dept_name varchar(10);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

添加新列

语法：

ALTER TABLE 表
ADD [COLUMN] 列名 类型

示例：

mysql> alter table departments
    -> add manager_id int;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除列

语法：

ALTER TABLE 表
DROP [COLUMN] 列名

示例：

mysql> alter table departments
    -> drop manager_id;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改表名

语法：

ALTER TABLE 表名
RENAME TO 新表名

示例：

mysql> alter table departments
    -> rename to depts;
Query OK, 0 rows affected (0.00 sec)

删除表

语法：

DROP TABLE [IF EXISTS] 表名

示例：

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

表复制

仅复制表结构

语法：

CREATE TABLE 待创建的表名 LIKE 已有表名

示例：

mysql> create table departments like nsd2021.departments;
Query OK, 0 rows affected (0.01 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

约束

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

约束分类

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

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

约束应用

列级应用

创建表时使用约束

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> 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)  -- 外键
    -> );
    
# 查看约束
mysql> select * from information_schema.table_constraints  where table_name='employees2' \G

自定义约束名称

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)
    -> );

删除约束

语法：

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>

示例：

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

例：创建员工数据库的三张表

# 创建部门表
create table departments(
    dept_id int AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(10) UNIQUE
);

# 创建员工表
create table employees(
    employee_id INT auto_increment primary key,
    name VARCHAR(10) not null,
    hire_date DATE,
    birth_date DATE,
    email varchar(25) UNIQUE,
    phone_number varchar(11),
    dept_id int,
    FOREIGN KEY(dept_id) references departments(dept_id)
);

# 创建工资表
create table salary(
    id int AUTO_INCREMENT PRIMARY KEY,
    date DATE,
    employee_id int,
    basic int,
    bonus int,
    FOREIGN KEY(employee_id) references employees(employee_id)
);

事务控制语言TCL

事务(要么全都执行，要么全不执行)

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

事务必须满足的4个条件

原子性(Atomicity)：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性(Consistency)：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性(Isolation)：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性(Durability)：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务控制语句

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。

MySQL事物处理的方法

用 BEGIN, ROLLBACK, COMMIT来实现
- BEGIN开始一个事务
- ROLLBACK事务回滚
- COMMIT提交事务
直接用 SET 来改变 MySQL 的自动提交模式
- SET AUTOCOMMIT=0 禁止自动提交
- SET AUTOCOMMIT=1*开启自动提交

事务的创建

隐式事务

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

mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 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)

创建事务步骤

开启事务

set aotocommit=0;
start transaction;    # 可选

编写事务语句：INSERT、UPDATE、DELETE语句
结束事务

commit | rollback;

事务示例

创建银行表

mysql> use mydb;
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, '牛犇', 10000), (2, '张志刚', 10000);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

使用事务：正常提交

mysql> set autocommit=0;
mysql> update bank set balance=balance-1000 where name='牛犇';
mysql> update bank set balance=balance+1000 where name='张志刚';
# 此时在另一终端查看bank表，数据并未改变
mysql> commit;

使用事务：回滚

mysql> set autocommit=0;
mysql> update bank set balance=balance+1000 where name='牛犇';
mysql> update bank set balance=balance-1000 where name='张志刚';
# 此时在另一终端查看bank表，数据并未改变
mysql> rollback;

事务隔离

事务隔离要解决的问题

脏读：脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并不一定最终存在的数据，这就是脏读。
可重复读：可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的。通常针对数据更新（UPDATE）操作。
不可重复读：对比可重复读，不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据更新（UPDATE）操作。
幻读：幻读是针对数据插入（INSERT）操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，但其实是事务B刚插入进来的，让用户感觉很魔幻，感觉出现了幻觉，这就叫幻读。

事务隔离级别

读未提交（READ UNCOMMITTED）
读提交（READ COMMITTED）
可重复读（REPEATABLE READ）
串行化（SERIALIZABLE）

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

事务隔离其实就是为了解决上面提到的脏读、不可重复读、幻读这几个问题。只有串行化的隔离级别解决了全部这 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.01 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

测试

# 在第一个终端上执行以下2条语句
mysql> set autocommit=0;
mysql> update bank set balance=balance+1000 where name='牛犇';

# 以下3条语句在第二个终端上执行
mysql> set session transaction isolation level read uncommitted;
mysql> set autocommit=0;
mysql> select * from bank;   # 此时牛犇账户上已经增加1000

# 回到第一个终端回滚
mysql> rollback;

# 在第2个终端上重新查询
mysql> select * from bank;   # 此时牛犇账户上又减少了1000

SAVEPOINT应用

基本用法

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

SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier

使用 SAVEPOINT identifier 来创建一个名为identifier的回滚点
ROLLBACK TO identifier，回滚到指定名称的SAVEPOINT，这里是identifier
使用 RELEASE SAVEPOINT identifier 来释放删除保存点identifier
如果当前事务具有相同名称的保存点，则将删除旧的保存点并设置一个新的保存点。
如果执行START TRANSACTION，COMMIT和ROLLBACK语句，则将删除当前事务的所有保存点。

SAVEPOINT示例

mysql> set autocommit=0;
mysql> update bank set balance=balance+1000 where name='牛犇';
mysql> savepoint aaa;    # 创建保存点
mysql> select * from bank;   # 牛老师账号已增加1000
mysql> update bank set balance=balance-1000 where name='张志刚';
mysql> select * from bank;   # 张志刚账号已减少1000
mysql> rollback to aaa;  # 回滚到保存点aaa
mysql> select * from bank;   # 牛老师账号已增加1000，张志刚账号未改变
mysql> exit;  # 退出
# 再连入之后查询
mysql> select * from bank;  # 因为从未执行过commit。所以查到的结果与执行事务之前查到的结果一样。