数据库原理

概述

数据库（DataBase，DB）：指长期保存在计算机的存储设备上，按照一定规则组织起来，可以被各种用户或应用共享的数据集合。又称电子化的文件柜。

数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。 用户通过数据库管理系统访问数据库中的数据。

数据库管理员（Database Administrator，简称DBA），是从事管理和维护数据库管理系统(DBMS)的相关工作人员的统称，属于运维工程师的一个分支，主要负责业务数据库从设计、测试到部署交付的全生命周期管理。

总结前面的知识：根据以往所学，如果存储在集合中的话，应用关闭数据就消失了。如果存储在本地文件中，读写操作速度又太慢。 那应该存在哪里呢？ 数据库就是解决这个问题的，数据库顾名思义指的是数据仓库。是一种内存缓存+文件存储的存储方式，它既保证了存储效率，又保证了数据不易丢失。

常见的数据库管理系统

Oracle：Oracle数据库被认为是业界目前比较成功的关系型数据库管理系统。Oracle数据库可以运行 在UNIX、Windows等主流操作系统平台，完全支持所有的工业标准，并获得最高级别的ISO标准安全性 认证。

MySQL：MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle旗下 产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。

DB2：DB2是IBM公司的产品，DB2数据库系统采用多进程多线索体系结构，其功能足以满足大中公司 的需要，并可灵活地服务于中小型电子商务解决方案。

Microsoft SQL Server：SQL Server 是Microsoft 公司推出的关系型数据库管理系统。具有使用方便可伸缩性好与相关软件集成程度高等优点。

注：关系型数据库管理系统是当今的主流数据库管理系统，关系模型的数据结构非常简单，关系实质上是一张二维表，其中每一行是一个元组，每一列是一个属性

三大范式

第一范式（1NF）：属性不可再分，强调的是列的原子性，即单列不能够再分成其他几列。考虑这样一个表：【联系人】（姓名，性别，电话）如果在实际场景中，一个联系人有家庭电话和公司电话，那么这种表结构设计就没有达到 1NF。要符合 1NF 我们只需把列（电话）拆分，即：【联系人】（姓名，性别，家庭电话，公司电话）。

第二范式（2NF）：**每个非主属性完全函数依赖于键码。**考虑下面一张表：

Sno	Sname	Sdept	Mname	Cname	Grade
1	学生-1	学院-1	院长-1	课程-1	90
2	学生-2	学院-2	院长-2	课程-2	80
2	学生-2	学院-2	院长-2	课程-1	100
3	学生-3	学院-2	院长-2	课程-2	95

以上学生课程关系中，{Sno, Cname} 为键码，有如下函数依赖：

• Sno -> Sname, Sdept
• Sdept -> Mname
• Sno, Cname-> Grade

Grade 完全函数依赖于键码，它没有任何冗余数据，每个学生的每门课都有特定的成绩。Sname, Sdept 和 Mname 都部分依赖于键码，当一个学生选修了多门课时，这些数据就会出现多次，造成大量冗余数据。

分解后

关系-1

Sno	Sname	Sdept	Mname
1	学生-1	学院-1	院长-1
2	学生-2	学院-2	院长-2
3	学生-3	学院-2	院长-2

上面这张表（可依据第三范式继续分解）有以下函数依赖：

• Sno -> Sname, Sdept
• Sdept -> Mname

关系表-2

Sno	Cname	Grade
1	课程-1	90
2	课程-2	80
2	课程-1	100
3	课程-2	95

有以下函数依赖：

• Sno, Cname -> Grade

第三范式 (3NF)：非主属性不传递函数依赖于键码。

上面的 关系-1 中存在以下传递函数依赖：

• Sno -> Sdept -> Mname

可以进行以下分解：

关系-11

Sno	Sname	Sdept
1	学生-1	学院-1
2	学生-2	学院-2
3	学生-3	学院-2

关系-12

Sdept	Mname
学院-1	院长-1
学院-2	院长-2

小结 第二范式（2NF）和第三范式（3NF）的概念很容易混淆，区分它们的关键点在于，2NF：非主键列是 否完全依赖于主键，还是依赖于主键的一部分；3NF：非主键列是直接依赖于主键，还是直接依赖于非主键列。

数据完整性

用来保证存放到数据库中的数据是有效的,即数据的有效性和准确性

确保数据的完整性 = 在创建表时给表中添加约束

约束类型

主键约束：primary key
唯一约束：unique [key]
非空约束：not null
默认约束：default
自动增长：auto_increment
外键约束: foreign key

注意： 建议这些约束应该在创建表的时候设置 ，多个约束条件之间使用空格间隔
示例

create table student(
    studentno int primary key auto_increment,
    loginPwd varchar(20) not null default '123456',
    studentname varchar(50) not null,
    sex char(2) not null,
    gradeid int not null,
    phone varchar(255) not null,
    address varchar(255) default '学生宿舍',
    borndate datetime,
    email varchar(50)
);

完整性的分类：

• 实体完整性(行完整性)
• 域完整性(列完整性)
• 引用完整性(关联表完整性)

实体完整性

实体：即表中的一行(一条记录)代表一个实体（entity）

实体完整性的作用：标识每一行数据不重复。

约束类型： 主键约束（primary key） 唯一约束(unique) 自动增长列(auto_increment)

主键约束

注：每个表中要有一个主键。 特点：数据唯一，且不能为null。

示例：

第一种添加方式：

CREATE TABLE student( id int primary key, name varchar(50) );

第二种添加方式：此种方式优势在于，可以创建联合主键：

CREATE TABLE student( id int, name varchar(50), primary key(id) );
	或者
CREATE TABLE student( classid int, stuid int, name varchar(50), primary key(classid,stuid) );

第三种添加方式：

CREATE TABLE student( id int, name varchar(50) );
ALTER TABLE student ADD PRIMARY KEY (id);

唯一约束

特点：数据不能重复,与主键不同的是，允许为null

CREATE TABLE student( Id int primary key, Name varchar(50) unique );

自动增长列(auto_increment)

作用：给主键添加自动增长的数值，列只能是整数类型

CREATE TABLE student( Id int primary key auto_increment, Name varchar(50) );
INSERT INTO student(name) values(‘tom’);

域完整性

域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较

域代表当前单元格

域完整性约束：数据类型 非空约束（not null）默认值约束(default) check约束

mysql不支持check约束，其他数据库支持，如SQLServer，如在属性性别上约束只能是男或者女：check(sex=‘男’ or sex=‘女’)

数据类型

数值类型

类型	大小	范围（有符号）	范围（无符号 ）	用途
tinyint	1 字节	(-128，127)	(0，255)	小整数值
smallint	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
mediumint	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
bigint	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
float	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
double	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0， (2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值

日期类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。 每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。 TIMESTAMP类型有专有的自动更新特性

类型	大小(字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日 期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1- 19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

小结

CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、 MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。

引用完整性

引用表中的主关键字和引用表中的外部主关键字之间的关系，通过外键约束实现这一完整性！！

外键约束：FOREIGN KEY

添加方式一（创建表时添加）

constraint 自定义外键名称 foreign key(外键列名) references 主键表名(主键列名)

添加方式二：（创建表后添加）

ALTER TABLEscore1 ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES stu(id);

示例

CREATE TABLE student(id int primary key, name varchar(50) not null, 
sex varchar(10) default '男' );

create table score(
id int primary key,
score int,
sid int ,
constraint fk_score_sid foreign key(sid) references student(id) );

事务

数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。事务是数据库并发控制的基本单位。

语法

1. 事务开始的标志：start transaction;或者begin;
2. commit;使得当前的修改确认 ，持久化
3. rollback; 通常所说的回滚，使得当前的修改被放弃，只在commit之前使用才有效

事务的ACID特性

原⼦性（Atomicity）

事务的原⼦性是指事务必须是⼀个原子的操作序列单元。事务中包含的各项操作在⼀次执⾏过程中，只 允许出现两种状态之一。

（1）全部执行成功

（2）全部执行失败

事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执⾏过程中出错， 会回滚到事务开始前的状态，所有的操作就像没有发⽣一样。也就是说事务是⼀个不可分割的整体，就好像化学中学过的原子，是物质构成的基本单位。

⼀致性（Consistency）

事务的一致性是指事务的执⾏不能破坏数据库数据的完整性和一致性，一个事务在执⾏之前和执行之 后，数据库都必须处以⼀致性状态。

比如：如果从A账户转账到B账户，不可能因为A账户扣了钱，⽽B账户没有加钱。

隔离性（Isolation）

事务的隔离性是指在并发环境中，并发的事务是互相隔离的。也就是说，不同的事务并发操作相同的数据时，每个事务都有各自完整的数据空间。 ⼀个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务是不能互相干扰的。 隔离性分4个级别，下面会介绍。

持久性（Duration）

事务的持久性是指事务⼀旦提交后，数据库中的数据 必须被永久的保存下来。即使服务器系统崩溃或服务器宕机等故障。只要数据库重新启动，那么一定能够将其恢复到事务成功结束后的状态

事务的并发问题

脏读：

​ 读取到了没有提交的数据, 事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。

不可重复读：

​ 同⼀条命令返回不同的结果集（更新）.事务 A 多次读取同一数据，事务 B 在事务A 多次读取的过程中，对数据做了更新并提交，导致事务A多次读取同一数据时，结果不一致。

幻读：

​ 重复查询的过程中，数据就发⽣了量的变化（insert， delete）。

不可重复读和幻读有点像，前者是内容发生改变，后者是数据的量发生改变。

事务的隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（READ_UNCOMMITTED）	允许	允许	允许
读已提交（READ_COMMITTED）	禁止	允许	允许
可重复读（REPEATABLLE_READ）	禁止	禁止	可能会
顺序读（SERIALIZABLE）	禁止	禁止	禁止

4种事务隔离级别从上往下，级别越高，并发性越差，安全性就越来越高。 ⼀般数据默认级别是 读以提交或可重复读

如何查看当前会话中事务的隔离级别？

// mysql 8之前使用的指令
select @@tx_isolation;
// mysql 8之后使用的指令
select @@transaction_isolation;

示例（我使用的是mysql 8）

select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

如何设置当前会话中事务的隔离级别？（示例：将隔离级别调为读未提交）

set session transaction isolation level read uncommitted;

隔离级别详解

读未提交（READ_UNCOMMITTED）

​读未提交，该隔离级别允许脏读取，其隔离级别是最低的。换句话说，如果一个事务正在处理理某一数 据，并对其进⾏了更新，但同时尚未完成事务，因此还没有提交事务；而以此同时，允许另一个事务也能够访问该数据。

读已提交（READ_COMMITTED）

​读已提交是不同的事务执行的时候只能获取到已经提交的数据。 这样就不会出现上面的脏读的情况了。 但是在同一个事务中执行同一个读取,结果不一致 不可重复读示例 可是解决了脏读问题，但是还是解决不了可重复读问题。

可重复读（REPEATABLE_READ）

​可重复读就是保证在事务处理理过程中，多次读取同一个数据时，该数据的值和事务开始时刻是一致的。 因此该事务级别限制了不可重复读和脏读，但是有可能出现幻读的数据。

顺序读（SERIALIZABLE）

​顺序读是最严格的事务隔离级别。它要求所有的事务排队顺序执⾏行行，即事务只能一个接一个地处理，不 能并发。