[大数据]数据库原理

概述

数据库（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, Cname} 为键码，有如下函数依赖：

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

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

分解后

关系-1

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

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

关系表-2

有以下函数依赖：

• Sno, Cname -> Grade

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

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

• Sno -> Sdept -> Mname

可以进行以下分解：

关系-11

关系-12

小结 第二范式（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=‘女’)

数据类型

数值类型

日期类型

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

字符串类型

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

小结

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）。

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

事务的隔离级别

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）

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