交互式SQL和嵌入式SQL

独立使用SQL语言进行数据库操作时，其使用方法称为交互式SQL，独立使用SQL语言进行数据库操作时，其使用方法称为交互式SQL

当一个程序既要访问数据库，又要处理数据时，把SQL语言嵌入程序设计语言即宿主语言中，将SQL语言访问数据库的功能和宿主语言的数据处理功能相结合，是目前解决该问题的最有效途径。

一、数据..相关概念

数据库中储存的是数据与数据的联系

长期储存在计算机内、有组织的、可共享的数据集合

数据库（DB）

社交网络数据库
电子商务平台数据库
企业信息数据库
通讯数据库

数据库中的数据可以共享

数据库减少了数据冗余

数据类型

数值、图形、文本、音频…..

数据(DATA)储存方式

写在纸上的，存在excel表格的…

本节课的数据库（DB）特指长期储存在计算机内、有组织的、可共享的数据集合。

数据库管理系统（DBMS）- Mysql

SQL 语言集数据定义、数据查询、数据操纵、数据控制功能于一体。

数据库管理系统是位于用户与操作系统之间的一层数据管理软件
目标：科学地组织和存储数据、高效地获取和维护数据
位于用户应用与操作系统之间的一层数据管理软件
属于基础软件，是一个大型复杂的软件系统。
一组系统软件，而不是一组应用软件。

数据定义DDL

提供数据定义语言(DDL)，用于定义数据库中的数据对象

语言：DDL

数据操作/数据操控DML

提供数据操纵语言(DML)，用于操纵数据实现对数据库的基本操作(查询、插入、删除和修改)

增删改查

语言：DML

数据库控制

语言：

数据库维护

其他功能

对外连接

数据库管理员（DBA）

负责全面管理和控制数据库系统，是数据库系统中最重要的人员。
职责（我们的学习目标）：

设计和定义数据库系统
帮助最终用户使用数据库系统
监督与控制数据库系统的使用和运行
改进和重组数据库系统
调优数据库系统的性能
转储和恢复数据库
重构数据库

数据库系统（DBS）

特点

①数据结构化
②数据的共享性高，冗余度低，且容易扩充。
③数据独立性高(物理独立性（应用程序与物理存储相互独立，数据的物理存储改变应用程序不改变),（逻辑独立性人应用程序与逻辑结构相互独立，数据的逻辑结构改变应用程序不改变）
④数据由数据库管理系统统一管理和控制

在DBS中，DBMS调用了OS的功能。

数据共享，数据独立性高，数据完整性，减少了数据冗余。

应用数据库技术的主要目的是为了共享数据问题。

在计算机系统中引入数据库后的系统构成

数据库系统的构成：

数据库
数据库管理系统
应用系统（及其开发工具）
相关人员（数据库管理员、应用系统开发人员、用户）

概括：硬件、软件(DBMS运行的操作系统和本身)、数据库、用户

应用系统

用户

应用开发软件

操作系统

一致性

数据库中，产生数据不一致的根本原因是数据冗余

主要原因是事务的隔离性在并发中遭到了破坏

数据模型

数据模型是数据库系统的核心和基础

在数据库中用数据模型这个工具来抽象、表示和处理现实世界中的数据和信息。通俗地讲数据模型就是现实世界的模拟。例如：地图、航模飞机。

数据模型应满足三方面要求
能比较真实地模拟现实世界
容易为人所理解
便于在计算机上实现。

将E-R模型转换成关系模型，属于数据库的逻辑设计。

抽象

现实世界 –> 概念模型 –> 逻辑模型 –> 物理模型 –计算机中数据

概念模型

信息模型

它是面向用户的模型，是现实世界到机器世界的一个中间层次。其基本特征是按用户观点对信息进行建模，与具体DBMS无关。

世界是由一组称作实体的基本对象和这些对象之间的联系构成。

E-R模型：

独立于计算机系统的模型

实体－联系方法
（1）实体（Entity）。实体是指客观存在并可相互区别的事物。实体可以是人、事或物，也可以是抽象的概念。如一件商品、一个客户、一份订单等都是实体。

关系模型中实现实体间 N：M 联系是通过增加一个关系实现。

（2）属性（Attribute）。实体通常有若干特征，每个特征称为实体的一个属性。属性刻画了实体在某方面的特性。例如，商品实体的属性可以有商品编号、商品类别、商品名称、生产商等。
（3）联系（Relationship）。现实世界中事物之间的联系反映在E-R模型中就是实体间的联系。例如，订单就是客户和商品之间的联系。

画图：

实体用矩形框表示，属性用椭圆形框表示，联系用菱形框表示,实体与属性与联系之间用无向直线连接。

概念模型的用途

概念模型用于信息世界的建模
是现实世界到机器世界的一个中间层次
是数据库设计的有力工具
数据库设计人员和用户之间进行交流的语言

对概念模型的基本要求

较强的语义表达能力，能够方便、直接地表达应用中的各种语义知识
简单、清晰、易于用户理解。

转表

如果两个实体之间具有 m：n 联系，则将它们转换为关系模型的结果是3个表。

逻辑模型（数据模型）

大多数时候就直接称为数据模型

由DBMS支持的

三要素1：数据结构

静态特性，数据本身以及数据之间的联系。
刻画数据模型性质的重要方面。
通常采用数据结构的类型来命名数据模型。

关系模型（主流）

基本的数据结构是表格，关系模型使用二维表来表示实体及其联系。
关系模型的数据操作主要包括查询和更新。关系数据操作具有两个显著特点：一是关系操作是集合操作，即操作的对象和结果均为集合；二是关系模型将操作中的存取路径向用户屏蔽起来，用户只要说明做什么，而不必指出怎样做。

网状模型

网状（可以多根）（多个父亲）

允许两个结点间有多种联系（复合联系）

层次模型-树状

（一个根）（一个联系）

面向对象模型*(Object Oriented Model）

对象关系模型*(Object Relational Model)

三要素2：数据操作

数据操作指对数据库中各种对象（型）的实例（值）允许执行的操作及有关的操作规则，它是对数据库动态特性的描述。
数据库中的数据操作主要分为查询、更新两大类，其中数据更新主要是指对数据记录的增、删、改。
数据模型需要定义这些操作的语义、操作符号、操作规则及实现操作的相关语句。

动态特征，允许执行的操作的集合，包括操作及操作规则。一般有检索、更新(插入、副除、修改)操作。

三要素3：数据完整性约束

完整性约束是数据的一组完整性规则的集合。
完整性规则是给定的数据模型中数据及其联系所具有的制约和存储规则，用以限定符合数据模型的数据库状态以及状态的变化，保证数据的正确、有效、相容。

是完整性规则的集合，规定数据库状态及状态变化所应满足的条件。

阶段

在数据库系统阶段，数据和程序之间具有较高的独立性。

数据库系统结构

目的：提高数据和程序的独立性。

系统模式的概念:
- 模式（Schema）
  是对数据库逻辑结构和特征的描述是型的描述，不涉及具体值模式是相对稳定的
- 实例（Instance）
  数据库某一时刻的状态——模式的一个具体值同一个模式可以有很多实例,实例随数据库中的数据的更新而变动

三级模式：（外模式、模式、内模式）

这是数据库开发人员的角度，是内部结构。三级模式是对数据的三个抽象级别

外模式（视图）(子模式）（用户模式)：

视图是存放在数据库的表，为了安全。视图可以有多个，代表外模式也可以有多个。

数据库用户使用的局部数据的逻辑结构和特征的描述

外模式通常是模式的子集、一个模式可以有多个外模式反映了不同的用户的应用需求、看待数据的方式、对数据保密的要求。每个用户只能看见和访问所对应的外模式中的数据，简化用户视图。

单个用户使用的数据视图的描述称为“外模式”。

模式（基本表）(逻辑模式)：

一个数据库只有一个模式

模式是数据库系统模式结构的中心

数据库全体数据的逻辑结构和特征的描述，所有用户的公共数据视图。

这里的基本表并不是指你创建的表，而是本身存放在数据库的基本表，它们构成了一套逻辑结构来代表模式。

与数据的物理存储细节和硬件环境无关、与具体的应用程序、开发工具及高级程序设计语言无关。

内模式（存储模式）：

一个数据库只有一个内模式

是数据物理结构和存储方式的描述
是数据在数据库内部的表示方式记录的存储方式
（例如，顺序存储，堆存储，Cluste按hash方法存储等）
- 索引的组织方式（B+树，Bitmap，Hash）
- 数据是否压缩存储数据是否加密
- 数据存储记录结构的规定—如定长/变长，记录是否可以跨页存放等

DBMS提供三种模式的描述语言（DDL）来严格定义三种模式。

两级映象：（外模式/模式，模式/内模式映象）

二级映象在DBMS内部实现这三个抽象层次的联系和转换

引入知识点：映象，这是一种对应规则，说明映象双方如何进行转换。

1.外模式/模式映象

首先我们要知道这是定义在外模式描述中

官方的概念是：把描述局部逻辑结构的外模式与描述全局逻辑结构的模式联系起来，保证逻辑独立性。

当模式改变时，只要对外模式/模式映象做相应的改变，使外模式保持不变，则以外模式为依据的应用程序不受影响，从而保证了数据与程序之间的逻辑独立性，也就是数据的逻辑独立性。

个人理解是，我创建了一张表，有年龄这一字段，是int类型，我更改了类型为str类型，没有报错，那么对于我在这张表上之前输入的数据，不会改变，视图也不会改变

模式/内模式映象

映像是唯一的

首先我们要知道这是定义在模式描述中

官方概念:把描述全局逻辑结构的模式与描述物理结构的内模式联系起来,保证物理独立性。

当内模式改变时，比如存储设备或存储方式有所改变，只要模式/内模式映象做相应的改变，使模式保持不变，则应用程式保持不变。

二、关系数据库

概念

关系数据库

所有实体及实体之间联系的关系的集合构成一个关系数据库

所有关系的集合

关系数据库模式是关系模式的集合。

关系模式

关系模型，只包含单一的数据结构——关系

实体以及实体间的各种联系均用单一的结构类型——关系来表示

关系模型中的数据的逻辑结构是一张扁平的二维表

建立在集合代数的理论基础上

关系模式通常可以简记为
R(U,D,DOM,F)

U:属性名集合

D：U中属性来自的域

DOM：属性向域的映像集合

F:数据依赖

其中，R为关系名，A1，A2，…，An属性名

完整性约束

关系的目：列数。

域

一组具有相同数据类型的值的集合，反映在二维表中则是某一列的单元格的值的取值范围。

{‘男’，‘女’}/介于2007年7月1日到2008年2月29日之间的日期

笛卡尔积

一种集合运算

$$D _ { 1 } \times D _ { 2 } \times \cdots D _ { n } = { ( d _ { 1 } , d _ { 2 } , \cdots d _ { n } ) | d _ { i } ∈ D _ { i }}$$

所有域的所有取值的一个组合
不能重复

元组

笛卡尔积中每一个元素

$( d _ { 1 } , d _ { 2 } , \cdots d _ { n })$

分量

$( d _ { 1 } , d _ { 2 } , \cdots d _ { n })$的$d_{i}$

基数

多少个元组

D1={张清玫，刘逸}，基数：2

D2={计算机专业，信息专业}，基数：2

D3={李勇，刘晨，王敏}，基数：3

D1×D2×D3的基数2×2×3=12

关系

D1×D2×…Dn的子集叫作在域D1,D2,…Dn上的关系，表示为：R(D1,D2,…Dn)

R为关系名，n为关系的目或度

元组

关系中的每个元素是关系中的元组，通常用t表示。

属性

关系中不同列可以对应相同的域，但必须对每列起一个名字，称为属性（Attribute）

属性的取值范围为域。

n目关系必有n个属性。

候选码

若关系中的某一属性组的值能唯一地标识一个元组，则称该属性组为候选码

最简单：有且只有一个属性能唯一标识元组，即候选码只包含一个属性。
最极端：所有属性组合起来才能唯一标识元组，即候选码包含全部属性，称为全码（All-key）

候选码的各个属性称为主属性（Primeattribute）
不包含在任何侯选码中的属性称为非主(码)属性（Non-key attribute）

规则

判断候选码
已知: AB->C;
自反：AB->AB；
合并：AB->ABC;

已知: B->D;
增广：AB->AD;

已知: A->B,B->C
传递：A->C

主码

若一个关系有多个候选码，则选定其中一个为主控使用者，称为主码（Primary key）

关系的表示

二维表

三种类型

基本关系（又称为基本表或基表）

实际存在的表，是实际存储数据的逻辑表示

查询表

查询结果对应的表，虚表

视图表

由基本表或其他视图表导出的表，是虚表，不对应实际存储的数据

性质

列是同质的（Homogeneous）：每一列中的分量是同一类型的数据，来自同一个域。

不同的列可出自同一个域：每一列称为一个属性。不同的属性要给予不同的属性名。

列的顺序无所谓

行的顺序无所谓

任意两个元组的码不能相同：码的唯一性

分量必须取原子值：一个属性分为两个部分（[导师]–>[导师1]+[导师2]）是不被允许的。

关系模式与关系

关系模式是静态的、稳定的（属性名集合）
关系是动态的、随时间不断变化的（整张表）

基本操作

查询
选择（找到）、投影（选出其中的某些列）、连接、除、并、差、交、笛卡尔积等
数据更新
插入、删除、修改

语言特点

具有关系代数和关系演算双重特点的语言–SQL（StructuredQueryLanguage）

关系的完整性

关系的三类完整性约束,实体完整性和参照完整性适用于任何关系数据库系统,是关系模型必须满足的完整性约束条件，应该由关系系统自动支持

实体完整性

所有主属性不能取空值

参照完整性

关系的引用

外键

外码（外键）（ForeignKey）
设F是基本关系R的一个或一组属性，但不是关系R的码。如果F与基本关系S的主码Ks相对应，则称F是基本关系R的外码。
基本关系R称为参照关系(Referencing Relation)

基本关系S称为被参照关系(Referenced Relation)或目标关系(Target Relation)

外码的特点
目标关系S的主码K_s和参照关系的外码F必须定义在同一个（或一组）域上。

取下面两类值：
（1）空值
表示尚未分配。
（2）非空值
必须一一对应

外码并不一定要与相应的主码同名，但当外码与相应的主码属于不同关系时，往往取相同的名字便于识别。

学生、课程、学生与课程之间的多对多联系

学生（学号，姓名，性别，专业号，年龄）【被参照关系】

课程（课程号，课程名，学分）【被参照关系】

选修（学号，课程号，成绩）【参照关系】中学号和课程号分别是外码，合在一起作为选秀的主码。

选修（学号，课程号，成绩）中学号和课程号分别是外码，合在一起作为选秀的主码。

学生实体及其内部的联系

学生（学号，姓名，性别，专业号，年龄，班长）中，班长是外码。【既是被参照关系也是参照关系】

用户定义的完整性

用户定义的完整性是针对某一具体关系数据库的约束条件，反映某一具体应用所涉及的数据必须满足的要求。

两个不变性

实体完整性
参照完整性

关系代数

五种基本关系代数：包括并 (∪)、差 (-)、广义笛卡尔积 (×)、投影 (π)和选择 (σ)，其他运算的功能都可以由这五种基本运算来实现。

关系代数运算是以集合运算为基础的运算。

关系代数运算的三大要素

运算对象：关系

运算结果：关系

运算符：四类

R和S
具有相同的目n（即两个关系都有n个属性）
相应的属性取自同一个域
t是元组变量，t∈R表示t是R的一个元组

并

R∪S={t|t∈R∨t∈S}

仍为n目关系，由属于R或属于S的元组组成

差

R—S={t|t∈R∧不属于S}

仍为n目关系，由属于R而不属于S的所有元组组成

关系代数表达式R－（R－S），表达的是R∩S。

交

假设2：
R是n目关系，包含k1个元组
S是m目关系，包含k2个元组

笛卡尔积

列：（n+m）列的元组的集合。
元组的前n列是关系R的一个元组；
后m列是关系S的一个元组。行：k1×k2个元组

概念

象集

专门

选择

选择又称为限制（Restriction）

$σ_F (R)$

F：选择条件，是一个逻辑表达式，取“真”或“假”值，其基本形式为：XθY
θ：比较运算符（＞，≥，＜，≤，＝或<>）

$σ_{5 = ‘is’} $意思为第五个属性

$σ_{5<20} $第五个属性小于20

选择行

投影

从R中选择出若干属性列组成新的关系，是从列的角度进行运算。

$π_A(R)$

查询学生关系Student中都有哪些系

$π_{Sdep}(Student)$

连接

关系代数中的联接操作是由选择和笛卡尔积操作组合而成

连接也称为θ连接
从两个关系的笛卡尔积中选取属性间满足一定条件的元组。

从两个关系的笛卡尔积中选取属性间满足一定条件的元组。

等值连接

自然连接

自然连接是一种特殊的等值连接，两个关系中进行比较的分量必须是相同的属性组，在结果中把重复的属性列去掉

悬浮元组：两个关系R和S在自然连接时，关系R和S中被舍弃的元组称为悬浮元组。

外连接

用途：将不满足条件的悬浮元组也加入到输出结果之中。

外连接：如果把舍弃的元组也保存在结果关系中，而在其他属性上填空值(Null)，这种连接就叫做外连接（OUTER JOIN）。

左外连接

如果只把左边关系R中要舍弃的元组保留就叫做左外连接(LEFT OUTER JOIN或LEFT JOIN)

右外连接

如果只把右边关系S中要舍弃的元组保留就叫做右外连接(RIGHT OUTER JOIN或RIGHT JOIN)

除

同时从行和列角度进行运算
R与S的除运算得到一个新的关系P(X)，P是R中满足下列条件的元组在X属性列上的投影：元组在X上分量值x的象集Yx包含S在Y上投影的集合。记作
$$R \div S = { t _ { r } [ X ] | t _ { r } ∈ R ∧ \pi _ { Y } ( S ) ⊆ Y _ { x } }$$

ABC/BCD = A(BC共有的部分的A)

索引

预备知识

主存存取原理

B-Tree数据结构

索引的本质

索引（Index）是一种特殊的数据结构，其作用相当于一本书的目录，可以用来快速查询数据库表中的特定记录。

索引分类

1）从物理结构上可以分为聚集索引和非聚集索引两类：

聚簇索引指索引的键值的逻辑顺序与表中相应行的物理顺序一致，即每张表只能有一个聚簇索引，也就是我们常说的主键索引；
非聚簇索引的逻辑顺序则与数据行的物理顺序不一致。

2）从应用上可以划分为一下几类：

普通索引：MySQL 中的基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了提高查询效率。通过 ALTER TABLE table_name ADD INDEX index_name (column) 创建；
唯一索引：索引列中的值必须是唯一的，但是允许为空值。通过 ALTER TABLE table_name ADD UNIQUE index_name (column) 创建；
主键索引：特殊的唯一索引，也成聚簇索引，不允许有空值，并由数据库帮我们自动创建；
组合索引：组合表中多个字段创建的索引，遵守最左前缀匹配规则；
全文索引：只有在 MyISAM 引擎上才能使用，同时只支持 CHAR、VARCHAR、TEXT 类型字段上使用。

索引B-Tree和B+Tree

B-Tree与hash索引的比较

索引的优缺点

索引使用的注意事项

数据依赖

一个关系内部属性与属性之间的约束关系叫做数据依赖。

规范化

一个低一级范式的关系模式，通过模式分解可以转换为若干个高一级范式的关系模式的集合，这种过程就叫规范化。

函数依赖

设 R 为任一给定关系，如果对于 R 中属性 X 的每一个值，R 中的属性 Y 只有唯一值与之对应，则称 X 函数决定 Y 或称 Y 函数依赖于 X ，记作 X—>Y。其中，X 称为决定因素。

性质

投影性

扩张性

合并性

分解性

码

候选码

完全函数依赖 U 则是候选码，一般键叫做候选键。

超码

部分函数依赖 U 则是超码，候选码是最小的超码。

全码

整个属性集是候选码则是全码

外码

不是自己的关系模式R的候选码，但是是另一个关系模式的候选码

范式

范式是符合某一种级别的关系模式的集合。范式是符合某一种级别的关系模式的集合。构造数据库必须遵循一定的规则。在关系数据库中，这种规则就是范式。

名称	优点	缺点
范式	范式化的表减少了数据冗余，数据表更新操作快、占用存储空间少。	查询时通常需要多表关联查询，更难进行索引优化
反范式	反范式的过程就是通过冗余数据来提高查询性能，可以减少表关联和更好进行索引优化	存在大量冗余数据，并且数据的维护成本更高

1NF

第一范式是对关系模式的最起码的要求。不满足第一范式的数据库模式不能称为关系数据库。

为了确保原子性的，也就是存储的数据具备不可再分性。

2NF

2NF：在1NF基础上，数据表中每一条记录可唯一标识，所有非主键字段必须完全依赖于主键，不能部分依赖于主键，消除部分函数依赖。

2NF设计原则：一张表只表达一个意思, 对于部分依赖于主键的字段，将这部分字段与依赖的部分主键抽取出来建立新表。

表中的每一列都要和主键有关。而不能只与主键的某一部分相关，不存在非主属性对码的部分函数依赖。

采用投影分解法将1NF变成2NF.

3NF

3NF：在2NF基础上，非主属性之间不能相互依赖，非主属性之间相互独立，消除传递依赖。不存在非主属性对码的传递函数依赖。

BCNF

不存在主属性的对码的传递函数依赖

根据范式分解

常见问题

更新异常

更新复杂

插入异常

应该插入的未插入

删除异常

不该被删除的被删除

数据冗余

出现各种问题的原因
关系模式属性列之间存在混乱的依赖关系

解决方法
通过分解关系模式来消除其中不合适的数据依赖。

安全控制

账户控制

日志控制

事务

一组要执行的SQL,要么都执行成功，要么都不执行

事务是数据库管理系统(DBMS)执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。

在MlySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务；

事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。
事务用来管理insert,update,delete语句。

事务结束指的是事务提交成功(COMMIT)/事务回滚(ROLLBACK)

事务使用场景

开启事务后再写数据来保证正确

BEGIN TRANSACTION
COMMIT #事务正常结束
ROLLBACK #事务异常终止，回滚到开始前状态（要么都执行要不都不执行）

---1
BEGIN
READ A
A=A-M
IF A<0
ROLLBACK
IF A>=0
COMMIT

问题

丢失修改

读脏数据

不能重复读

幻读

特性ACID

原子性：

最小单位，逻辑单位，要么都执行要不都不执行

一致性

从一个一致性到另一个一致性的状态

隔离性isolation

一个事务的执行不能被其他事务干扰

隔离级别(按照级别排序)

- (1)读未提交：read uncommitted

提供了事务间最小限度的隔离。

实现原理
读不加锁，写加排它锁（X锁）
直接读磁盘中的数据

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

理解：公司发工资了，领导把5000元打到A的账号上，但是该事务并未提交，而A正好去查看账户，发现工资已经到账，是5000元整，非常高兴。可是不幸的是，领导发现发给A的工资金额不对，是2000元，于是迅速回滚了事务，修改金额后，将事务提交，最后A实际的工资只有2000元。

- (2)读已提交rc：read committed

在一个事务修改数据过程中，如果事务还没提交，其他事务不能读该数据。

这种级别可以避免“脏数据”这种隔离级别会导致“不可重复读取

实现原理
读不加锁,做“快照读”并总是读最新的快照
写加排它锁(X锁)

- (3)可重复读rr：repeatable read

保证同一事务在执行同条SELECT语句数次， 结果总是相同的。

实现原理
读不加锁,做“快照读”
并总是读事务开始第一次读的那个版本的快照（Undo日志中）
写加排它锁(X锁)

可能会导致幻读。

A的老婆工作在银行部门，她时常通过银行内部系统查看A的信用卡消费记录。有一天，她正在查询到A当月信用卡的总消费金额(select sum(amount)from transaction where month=本月）为80元，而A此时正好在外面胡吃海塞后在收银台买单，消费1000元，即新增了一条1000元的消费记录(insert transaction…),并提交了事务，随后A的老婆将A当月信用卡消费的明细打印到A4纸上，却发现消费总额为1080元，A的老婆很诧异，以为出现了幻觉，幻读就这样产生了。

!实现:MVCC（多版本并发控制）(写+读)

MYSQL中可重复读和提交读隔离级别的实现方式

MVCC的核心就是 Undo log+ Read-view，

•MVCC是通过undo日志来实现•读不加锁,做“快照读”

“MV”就是通过 Undo log（回滚的基础）来保存数据的历史版本，实现多版本的管理

“CC”是通过 Read-view（读视图）来实现管理，通过 Read-view原则来决定数据是否显示。

同时针对不同的隔离级别， Read view的生成策略不同，也就实现了不同的隔离级别。

- (4)串行化：serializable

用户之间通过一个接一个顺序执行当前的事务，提供了事务之间最大限度的隔离。

t1 -> t2和t2 -> t1

实现原理:
读加共享锁（S锁)
写加排它锁(X锁)

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

持续性

改变是永久的

INNODB级别日志

储存引擎的日志

Undo_log

Redo_log

实现特性的方式

并发操作

封锁

数据库的锁有排他锁，共享锁，意向锁，自增锁，间隙锁，锁的范围有包括，行锁，表锁，区间锁。数据库的锁有排他锁，共享锁，意向锁，自增锁，间隙锁，锁的范围有包括，行锁，表锁，区间锁。

排他锁/写锁：X

X 锁，事务T加了排他锁，自己可读也可写。禁止并发操作。

Xlock

共享锁/读锁：S

S 锁，相对于悲观锁来说，是低一级的锁，若有事务对于某一条数据加了共享锁后，其他事务依旧可以增加共享锁，但是不能增加排他锁。

允许其他用户可查询不可修改

slock

记号

R(x):读数据x

W(x):写数据x

封锁协议

封锁协议级别越高，一致性程度越高

一级封锁协议-写前加写锁

事务在修改之前需要加X锁，事务结束解锁。(其他事务不能加锁，但是可以直接读)

解决丢失更新。

可以防止丢失修改，并且保证事务可恢复

二级封锁协议-读前加读锁，读完释放

base one level，事务在读之前加S锁，读完释放。

解决污读

活锁

事务T1封锁了数据R,事务T2又请求封锁R,于是T2等待；T3也请求封锁R,当T1释放了R上的封锁之后系统首先批准了T3的请求，T2仍然等待；T4又请求封锁R,当T3释放了R上的封锁之后系统又批准了T4的请求2有可能永远等待，产生活锁.

采用先来先服务策略即可解决。

死锁

事务T1封锁了数据R1,T2封锁了数据R2,T1又请求封锁R2,因T2已封锁了R2,于是T1等待T2释放R2上的锁，接着T2又申请封锁R1,因T1已封锁了R1,T2也只能等待T1,释放R1上的锁，这样T1在等待T2,而T2又在等待T1,T1和T2两个事务永远不能结束，形成死锁.

(1)死锁的预防
1.一次封锁法：每个事务必须一次将所有要使用的数据全部加锁，否则就不能继续执行
2.顺序封锁法：预先对数据对象规定一个封锁顺序，所有事务按这个顺序实施封锁

(2)死锁的诊断与解除
诊断：

超时法：如果一个事务的等待时间超过了规定的时限，就认为发生了死锁
等待图法：构建事务等待图(有向图：节点表示运行的事务，边表示事务等待的情况)，存在回路即死锁解除：选择一个处理死锁代价最小的事务，将其撤销(UNDO),释放此事务持有的所有的锁

两段锁协议

对任何数据进行读写前，需要获得该数据的封锁；当事务在释放任何一个封锁之后，不得再获得其他封锁。

遵循两段锁，是可串行化的充分条件，也是可能发生死锁的。

三级封锁协议-读前加读锁，事务结束释放

base one level，事务在读之前加S锁，事务结束释放。

（事务T在读取数据之前必须先对其加共享锁，排它锁)

解决重复读

封锁粒度

封锁的粒度越大，并发度就越小，系统开销也越小。
封锁的粒度越小，并发度较高，但系统开销也就越大

多粒度封锁

在一个系统中同时支持多种封锁粒度供不同的事务选择

多粒度树

以树形结构来表示多级封锁粒度，根结点是整个数据库，表示最大的数据粒度，叶结点表示最小的数据粒度。

如果对一个结点加意向锁，则说明该结点的下层结点正在被加锁。

意向锁

分为：

意向共享锁(Intent Share Lock，简称IS锁)
意向排它锁(IntentExclusive Lock，简称IX锁)
共享意向排它锁(Share Intent Exclusive Lock，简称SIX锁)SIX=S+IX

Q：具有意向锁的多粒度封锁方法减少了加锁和解所的开销：

在加锁时只要从根结点检查到要加锁的结点，而不需要考虑后代结点，而后代结点的个数远远大于祖先结点，因而减少了加锁时检查的开销。

同时由于后代结点不需要显示加锁，从而也减少了解锁的开销。

相容性矩阵

X	请求的模式	IS	S	IX	SIX
否	意向共享 (IS)	是	是	是	是
否	共享 (S)	是	是	否	否
否	意向排他 (IX)	是	否	是	否
否	意向排他共享 (SIX)	是	否	否	否
否	排他 (X)	否	否	否	否

加入事务T1对数据对象A加了S锁，也就意味着它对A的所有下级结点加了隐式S锁，现在事务T2想要申请数据对象上的IS锁，由于IS锁表示T2将要对A的下级结点加S锁，根据S锁的定义S锁和S锁式相容的，所以T2的封锁请求可以满足，也就是说S锁和IS锁是相容的。

首先要明确下面的结论

1.这个矩阵表示两个事务T1 T2是否可以同时对同一个对象加指定的锁。
2.对于SIX=S+IX锁的理解，虽然两个不同事务是不允许同时向同一对象加S，IX锁的。但是在一个事务中是可以即加S锁又加IX锁的的。因为锁就是保护事务并发的正确性，读和写两种命令在一个事务里就不存在并发的几种错误情况。但看一个事务就不需要考虑并发。理论上可以在一个事务里对同一对象又加S锁又加X锁，但根本没这个必要。

T1加S锁，表示数据的子节点都是隐式的加了 S 锁
- T2 加 S 相容都是读锁
- T2 加 IS 相容都是读锁
- T2 加 IX ，不相容， IX 对子节点意向加 X 锁（逻辑上可能不会对子节点加 X ，但是只要这个可能性存在就要保证正确性），和 S 锁冲突
- T2 加 SIX, 不相容，同上面加 IX
- T2 加 X ，不相容， S 和 X 冲突
T1加X锁，
- T2只能干等着。因为X锁排他，优先级最高
T1加IS锁
- T2不能加X锁。因为T1有对其子节点加S锁的可能而T2扼杀了这个可能性。
- T2 加 IX锁相容是因为I锁只是意向锁，所以是可行的，若事务有非意向锁，根据相容矩阵再次判定。SIX锁综上同理。
T1加IX锁，
- T2不能加S，X，SIX锁的原因不再赘述。(不能加SIX锁的原因，可参考不能加S锁)
T1加SIX锁，
- T2只能加IS锁。不能加S，X，IX，SIX锁的原因分别是：
- T2 加 S 不相容，T1无法完成SIX锁的IX部分
- T2 加 X 不相容，T1首先就无法完成S锁，更别说SIX锁
- T2 加 IX 不相容，表示T2意向修改对象子节点，而T1要读对象，若T2修改其子节点便产生并发错误。故pass
- T2 加 SIX 不相容，综合上面S和IX的情况。pass

恢复技术

常见损坏

事务内部故障

事务没有到达终点.

可预期
不可预期
- 运算溢出
- 发生死锁, 被选中撤销
- 违反完整性约束

系统故障

cpu
操作系统
代码错误
断电

介质故障

硬故障,外存故障

发生可能性小,破坏性大.

磁盘损坏
磁头碰撞
瞬时强磁场干扰.

病毒

人为破坏

基本原理:

数据冗余

恢复实现

如何建立冗余数据:

1. 数据转储(备份backup);

定期将数据复制到磁带磁盘和其他存储介质.(提前写转储计划)

后备副本

转储方法:

(1)静态转储和动态转储

静态:系统中无运行事务时进行,得到的一定是一个数据一致性的副本.

转储必须等正在运行的用户事务结束.

动态:

(2)海量转储和增量转储

海量:全部备份

增量:只转储上次转储后更新过的数据

增量备份通过二进制日志来实现。

2. 登记日志文件(logging)

原则:

Ⅰ. 先写日志文件,后写数据库

原因:若颠倒,日志上没有登记下修改,以后无法恢复这个修改.

Ⅱ. 登记的次序要严格按照并发事务执行的时间

恢复策略

事务故障的恢复

UNDO

系统故障（软故障）

系统故障的情况下，在系统重新启动后，恢复子系统除了需要撤销UNDO未完成的事务以外，还需要REDO（事务重做）
所有已提交的事务。

介质故障的恢复

重装数据库
装入最新的后备数据库副本,恢复到最近一次一致性状态.
重做REDO

计算及病毒会产生上述故障

采用相应的恢复策略

mysql备份工具

mysqldump

备份结果为可读的SQL文件，可用于跨版本跨平台恢复数据。
备份文件的尺寸小于物理备份，便于长时间存储。
mysql发行自带工具，无需安装第三方软件
Mysqldump热备全量逻辑备份

导出逻辑备份数据，备份较慢
还原需要执行sql语句，速度较慢
由于mysqldump的备份原理是对数据库发出很多条select请求,所以对数据库造成一定的负担。

xtrabackup

第三方percano公司研发的免费的物理备份工具
直接备份INNODB底层数据文件
导出不需要转换为SQL语句，速度快
工作时对数据库的压力较小

数据库设计

一、数据库设计的生存期

① 需求分析

需求收集和分析，得到用数据字典描述的数据需求，用数据流图描述的处理需求。明确数据的生命周期，存储
特点。

分析用户以及应用系统的数据需求
明确在数据库中需要存储和管理的数据
明确用户对数据的安全性和完整性方面的需求
明确数据的生命周期，存储特点（案例参考）

② 概念结构设计

对需求进行综合、归纳与抽象，形成一个独立于具体DBMS的概念模型（用E-R表示）。借助E-R模型，表达数据抽象结果，得到独立于具体的DBMS的概念模型

③ 逻辑结构设计

将概念结构转换为某个DBMS所支持的数据模型（例如关系模型），并对其进行优化。关系规范化理论。

对于关系模型，将关系模型转换为关系数据库模式
遵循转换规则
运用关系规范化理论

④ 物理结构设计

为逻辑数据模型选取一个最适合应用环境的物理结构（包括存储结构和存取方法）。

程序设计：应用程序设计的工作开始于物理设计

采用数据定义语言定义数据库模式
确定适合应用环境的存储结构和存取方法

⑤ 数据库实施

运用DBMS提供的数据语言（例如SQL）及其宿主语言（例如C），根据逻辑设计和物理设计的结果

建立数据库，编制与调试应用程序，组织数据入库，并进行试运行。

建立数据库
进行数据库编程
组织数据入库
测试数据库

⑥ 数据库运行和维护

数据库应用系统经过试运行后即可投入正式运行。在数据库系统运行过程中必须不断地对其进行评价、调整与修改。

对数据库进行评估
完善数据库
重组织
重构造

说明：设计一个完善的数据库应用系统是不可能一蹴而就的，它往往是上述六个阶段的不断反复。

二、数据库设计阶段的内容

三、数据库设计阶段的模式

数据库结构设计的不同阶段形成数据库的各级模式，如下图。

需求分析阶段：综合各个用户的应用需求；

概念设计阶段：形成独立于机器特点，独立于各个DBMS产品的概念模式，即E-R图；

逻辑设计阶段：将E-R图转换成具体的数据库产品支持的数据模型，如关系模型，形成数据库逻辑模式；然后根据用户处理的要求、安全性的考虑，在基本表的基础上再建立必要的视图，形成数据的外模式；

物理设计阶段：根据DBMS特点和处理的需要，进行物理存储安排，建立索引，形成数据库内模式。

数据库安全

第四章：数据库安全性（授权）
1、不安全因素
①非授权用户对数据库的恶意存取和破坏
②数据库中重要数据的泄露
③安全环境的脆弱性
2、数据库安全性控制
①用户身份鉴别：静态口令鉴别、动态口令鉴别、生物特征鉴别、智能卡鉴别
②存取控制
③自主存取控制方法
3、为什么要授权
授权是指授予(GRANT)和收回(REVOKE),是数据库安全性控制中的自主存取控制方法。是为了保护数据库
防止不合法使用所造成的数据泄露、更改或破坏。
4、如何授权：授予GRANT
发出如下语句的可以是数据库管理员、也可以是数据库对象创建者、也可以是已经拥有该权限的用户
GRANT权限ON对象类型对象名TO用户名WITH GRANT OPTION];
权限：查询权限SELECT,全部操作权限ALL PRIVILEGES
对象类型&对象名：对象类型可以是TABLE也可以是VEW,对象名为对应的表名或者视图名
用户名：可以是指定用户，也可以是全体用户PUBLIC
如果没有指定WITH GRANT OPTION子句，则获得某种权限的用户只能使用该权限，不能传播该权限
注意：SQL不允许循环授权，即被授权者不能把权限再授回给授权者或其祖先
eg.把查询权限授给用户U1

1	GRANT SELECT ON TABLE Student TO U1;

5、收回授权：收回REVOKE
REVOKE权限ON对象类型对象名FROM用户名[CASCADERESTRICT,
CASCADE:级联回收。将用户传播出去的权限一并收回
RESTRICT:受限回收。若用户传播过该权限，回收将会失败
eg.把用户U4修改学生学号的权限收回

1	REVOKE UPDATE(Sno)ON TABLE Student FROM U4;

6、创建数据库模式的权限
对抢创建数据库模式一类的数据库对象的授权再数据库管理员创建用户时实现：
CREATE USER username [WITH DBARESOURCECONNECT];
只有系统的超级用户才有权创建一个新的数据库用户，新创建的数据库用户有三种权限：DBA、RESOURCE、
CONNECT（此为默认）
DBA：可以创建新用户、创建模式、创建基本表和视图等，拥有对所有数据库对象的存取权限，还可以把这些权限
授予一般用户
RESOURCE:可以创建基本表和视图，但不能创建模式和新用户。数据库对象的属主可以用GRANT语句把该对象
上的存取权限授予其他用户
CONNECT：只能登陆数据库，根据其他用户或者数据库管理员授予的权限的情况对数据库对象进行权限范围内的
操作
7、数据库角色
角色是权限的集合。可以为一组具有相同权限的用户创建一个角色，使用角色来管理数据库权限可以简化授权的过
程
创建角色：CREATE ROLE角色名；
在一个角色中添加角色或者用户：GRANT角色TO某角色/某用户WITH ADMIN OPTION]:
指定NITH ADMIN OPTION子句，则获得某种权限的角色或者用户还可以把这种权限再授予其他角色

5、收回授权：收回REVOKE
REVOKE权限ON对象类型对象名FROM用户名[CASCADERESTRICT,
CASCADE:级联回收。将用户传播出去的权限一并收回
RESTRICT:受限回收。若用户传播过该权限，回收将会失败
eg.把用户U4修改学生学号的权限收回
REVOKE UPDATE(Sno)ON TABLE Student FROM U4;
6、创建数据库模式的权限
对抢创建数据库模式一类的数据库对象的授权再数据库管理员创建用户时实现：
CREATE USER username [WITH DBARESOURCECONNECT];
只有系统的超级用户才有权创建一个新的数据库用户，新创建的数据库用户有三种权限：DBA、RESOURCE、
CONNECT（此为默认）
DBA：可以创建新用户、创建模式、创建基本表和视图等，拥有对所有数据库对象的存取权限，还可以把这些权限
授予一般用户
RESOURCE:可以创建基本表和视图，但不能创建模式和新用户。数据库对象的属主可以用GRANT语句把该对象
上的存取权限授予其他用户
CONNECT：只能登陆数据库，根据其他用户或者数据库管理员授予的权限的情况对数据库对象进行权限范围内的
操作
7、数据库角色
角色是权限的集合。可以为一组具有相同权限的用户创建一个角色，使用角色来管理数据库权限可以简化授权的过
程
创建角色：CREATE ROLE角色名；
在一个角色中添加角色或者用户：GRANT角色TO某角色/某用户WITH ADMIN OPTION]:
指定NITH ADMIN OPTION子句，则获得某种权限的角色或者用户还可以把这种权限再授予其他角色

日志

错误日志

记录发生的错误。

慢查日志

记录执行时间超过指定时间的操作。

通用日志

所有向mysql发出的请求。

二进制日志

记录全部有效的数据修改日志。

一、数据..相关概念

数据库（DB）

数据类型

数据(DATA)储存方式

数据库管理系统（DBMS）- Mysql

数据定义DDL

数据操作/数据操控DML

数据库控制

数据库维护

其他功能

数据库管理员（DBA）

数据库系统（DBS）

特点

应用系统

用户

应用开发软件

操作系统

一致性

数据模型

抽象

概念模型

E-R模型：

画图：

概念模型的用途

对概念模型的基本要求

转表

逻辑模型（数据模型）

三要素1：数据结构

关系模型（主流）

网状模型

层次模型-树状

面向对象模型*(Object Oriented Model）

对象关系模型*(Object Relational Model)

三要素2：数据操作

三要素3：数据完整性约束

阶段

数据库系统结构

三级模式：（外模式、模式、内模式）

外模式（视图）(子模式）（用户模式)：

模式（基本表）(逻辑模式)：

内模式（存储模式）：

两级映象：（外模式/模式，模式/内模式映象 ）

1.外模式/模式映象

模式/内模式映象

二、关系数据库

概念

关系数据库

关系模式

域

笛卡尔积

元组

分量

基数

关系

元组

属性

候选码

规则

主码

关系的表示

三种类型

基本关系（又称为基本表或基表）

查询表

视图表

性质

关系模式与关系

基本操作

语言特点

关系的完整性

实体完整性

参照完整性

外键

用户定义的完整性

两个不变性

关系代数

并

差

交

笛卡尔积

概念

两级映象：（外模式/模式，模式/内模式映象）