UESTC 数据库原理及应用 复习笔记

1. 数据库系统基础

1.1 数据库及其系统概念

数据库

数据库是一种依照特定数据模型组织、存储和管理数据的文件集合。这些文件一般存放在外部存储器中，以便长久保存数据，并可存取访问。

优点：

• 数据库可以存储大量数据信息，方使用户进行有效的检索和访问

• 可以有效地保证数据信息的一致性、完整性，降低数据冗余情况。

• 可以满足应用的数据共享、数据存储安全。

• 数据库技术能够提供智能化地分析，产生更多有价值的信息。

• 数据库拥有备份的处理功能，可以防止数据丢失

数据库与普通数据文件的主要区别：

• 数据库可以支持不同应用程序对数据共享访问，普通数据文件难以支持。

• 数据库可实现复杂的数据管理（索引、查询、分区等），普通数据文件一般仅支持简单的数据记录读写操作。

• 数据库可独立于应用程序，普通数据文件与应用程序紧耦合。

• 数据库的操作访问与控制管理由数据库管理系统软件实现；普通数据文件的操作访问与控制管理，都必须由编程人员在应用程序中自己实现

数据模型

数据模型是一种描述事物对象数据特征及其结构的形式化表示。

1. 数据结构：用于描述事物对象的静态特征，包括事物对象的数据属性组成、数据类型、数据组织方式等。
2. 数据操作：用于描述事物对象的动态特征，包括数据的插入、修改、删除和查询等访问操作。
3. 数据约束：用于描述事物对象数据之间的语义联系，以及数据取值范围约束等规则，从而确保数据的完整性、一致性和有效性。

数据库使用的数据模型

1. 层次数据模型：“树”结构

2. 网状数据模型：“网状图”结构

3. 关系数据模型："二维表"结构

优点：

• 数据结构简单、操作灵活

• 支持关系与集合运算操作

• 支持广泛使用的SQL数据库操作语言标准

• 拥有众多的软件厂商产品与用户

局限：

• 只用于结构化数据的组织与存储管理

• 支持的数据类型较简单

• 难以支持互联网广泛应用的非结构化数据和复杂数据管理

1. 其它数据模型（如对象数据模型、键值存储数据模型、列式存储数据模型、文档存储数据模型、图形存储数据模型等）

数据库系统

数据库系统（Database Systems）是一类基于数据库进行数据管理的软件系统。当数据库系统在应用领域实现数据管理服务时，该系统称为数据库应用系统(Database Application Systems）

数据库应用系统由用户、数据库应用程序、数据库管理系统（Database Manage System, DBMS）和数据库四个部分组成。

• 数据库应用程序：是一种在DBMS支持下对数据库中数据进行访问处理的应用程序。

• 数据库管理系统：是一种专门用来创建数据库、管理数据库、维护数据库，并提供外部对数据库进行访问的系统软件，如 Oracle Database、MySQL、PostgreSQL等。

关系数据库

关系数据库的中数据内容：存储用户数据（用户表）和数据库系统自身的数据（系统表），如元数据、索引数据、运行数据等。

关系数据库的对象组成：

• 系统表：存储数据库系统自身数据

• 用户表：存储用户的应用数据

• 视图：通过虚拟表实现数据查询处理

• 索引：通过目录数据结构支持快速的数据查询

• 约束：对关系表及其数据施加的规则

• 存储过程：在数据库内部实现特定数据处理的过程程序

• 触发器：在数据库内部实现操作事件触发自动执行的过程程序

1.2  数据库技术发展

1.3 数据库应用系统

定义

数据库应用系统（Database Application System，DAS）是一类基于数据库管理系统（DBMS）对数据进行存储、管理，以及访问处理的计算机应用系统。

类型

1. 业务处理系统（Transaction Process System，TPS）

对机构业务活动（如订购、销售、支付、出货、核算等）信息进行数据处理

个人网上银行系统、景区门票销售系统、酒店系统

2. 管理信息系统(Manage Information System，MIS）

以机构职能管理为主导

人力资源管理信息系统、办公管理信息系统、CRM管理信息系统、ERP管理信息系统

3. 决策支持系统（Decision Support System）

针对特定领域问题解决，为管理者提供辅助决策服务与方案

新冠疫情防控应急指挥系统、电信业务决策支持系统、基于大数据的地震救灾决策支持系统

场景

OLTP（OnLine Transaction Processing，联机事务处理）

个人网上银行系统、景区门票销售系统、酒店系统

OLAP（OnLine Analytical Processing，联机分析处理）

CRM系统、电信业务决策支持系统、金融风险预测预警系统

架构

• 单机架构：数据库与用户应用程序紧耦合在一起

• 客户/服务器架构：数据集中在服务器管理，应用分布在客户端处理。

• 浏览器/服务器架构：应用系统各组件均部署在服务器上运行

• 分布式架构

• 分布式——主备机架构：备机作为备用机，平常不用

• 分布式——主从式架构：主机负责数据库写访问，从机负责数据库读访问（数据写入主机，从从机读出，主从机间数据复制同步）

• 分布式——多主架构：多个数据库服务器应用之间互为主从。

• 分布式——共享存储多主架构：一种特殊的多主架构，各数据库服务器共享数据存储，而多个服务器实现均衡负载。

• 分布式——分片架构：一个表里的数据放在多个节点上存储

生命周期

1. 需求分析
2. 系统设计
3. 系统实现
4. 系统测试
5. 系统运行与维护

1.4 数据库管理系统基础

DBMS基本功能

DBMS系统分类

1.5 PostgreSQL 对象-关系数据库系统软件

PostgreSQL数据库主要对象

2. 数据库关系模型

2.1 关系及其相关概念

实体、关系的概念

1. 实体

实体（entity)——是指包含有数据特征的事物对象在概念模型世界中的抽象名称。

2. 关系

在关系模型中，使用“关系”来存储“实体”中的数据。

关系（relation）——是指具有关系特征、用于存放实体数据的二维表。关系也常被称为关系表。

行（或 元组），列（或 属性）

3. 关系特征

• 每行存储一个实例数据

• 每列存储一项属性数据（类型要一致）

• 单元格只能储存单个值

• 不允许有重复的行、列

• 行、列顺序可任意

关系的数学定义

关系的键定义

在关系中，可以用来唯一标识元组的属性列，称为键（Key）。

1. 复合键（Compound Key）

关系中用来唯一标识元组的多列作为键。

2. 候选键（Candidate Key）

关系中可能有多个列均适合作为键，将其中每个都称为候选键。

3. 主键（Primarykey）

是关系表中最有代表性的一个候选键。每个关系表中只能定义一个主键。

作用：

• 唯一标识关系表的每行（元组）

• 与关联表的外键建立联系，实现关系表之间连接

• 数据库文件使用主键值来组织关系表的元组数据存储

• 数据库使用主键索引快速检索数据

4. 代理键

采用DBMS自动生成的数字序列作为关系表的主键。

优点：

可替代复合主键，以便获得更高性能的数据访问操作处理。

5. 外键

主键出现在其他表中

关系模式语句表示

Bash RELATION_NAME (Colunm01, Colunm02, …,LastColunm) 关系名（主键属性，属性2，…，属性x）

2.2 关系模型原理

关系模型及其组成

关系模型（Relation Model）是一种基于二维表结构存储实体及其实体间联系信息的数据模型。该模型的二维表都应具有关系特征。

1. 数据结构 表
2. 数据操作 运算操作和关系操作
3. 数据约束

关系模型数据操作

关系模型的集合运算

1. 并运算 ∪

2. 交运算 ∩

3. 差运算 -

4. 笛卡尔积 X

关系模型的专门关系操作

1. 选择运算 σ

选择出满足给定条件的元组集

2. 投影运算 ∏ （派）

投影出部分属性列组成一个新的关系

式中，A为R中的部分属性列。

3. 连接运算

连接运算是将两个关系组合成一个新的关系

a. θ连接

从R和S的笛卡尔积中选取R关系在A属性组上的值与S关系在B属性组上的值满足比较关系θ的元组集合组成新关系

A和B分别为关系R和S上度数相等且具有可以比较值的属性组，θ为比较运算符，它包括{＜，≤，=，>，≥}

b. 自然连接∞

自然连接是一种特殊的连接运算(θ为=)，它不但要求结果集元组在两个关系的关联列具有相同值域，并且还要在结果集中把重复的属性列去掉

c. 外连接

自然连接是一种内连接，它是指将两个关系中符合匹配条件的元组构成结果集。

外连接是一种在内连接基础上增加未匹配元组的连接操作，它包括左外连接、右外连接和全外连接。

• 左外连接

左表为主，右边没有对应的项，左表的人该属性用 NULL

• 右外连接

• 全外连接

同时完成左外连接和右外连接运算

4. 关系的复合运算

对同一个关系进行多种运算

关系模型完整性

关系模型完整性是指在关系数据模型中对关系实施的完整性约束。

关系模型完整性约束分类：

1. 实体完整性约束：消除关系表的元组重复存储

是指在关系表中实施的主键取值约束，以保证关系表中的每个元组可以被唯一标识。

对主键有要求，非空，唯一

2. 参照完整性约束：保持关联列的数据一致性

是指关系表之间需要遵守的数据约束，以保证关系之间关联列之间的数据一致性。

要求主键和在别的表里的外键数据一致

3. 用户自定义完整性约束：实现业务数据规则

是指用户根据具体业务数据处理要求对关系中属性施加的数据约束。

• 定义列的数据类型与取值范围

• 定义列的缺省值

• 定义列是否允许取空值

• 定义列取值唯一性

• 定义列之间的数据依赖性

2.3 PostgreSQL数据库关系操作实践

3. 数据库操作 SQL 语言

3.1 SQL 语言概述

SQL (Structured Query Language,结构化查询语言）是一种对关系数据库进行访问的数据操作语言。

SQL 语言特点

• 数据定义、数据操纵、数据控制一体化

• 使用方式灵活，既可命令行联机操作，也可嵌入编程操作

• 非过程化，数据存取路径与具体操作处理由DBMS系统自动完成

• 语言简洁，易学易用

SQL 对关系型数据库的操作原理

SQL 语言基本数据类型

• 字符：CHAR, VARCHAR

• 整数：SMALLINT, INTEGER

• 浮点数：NUMBER(n,d), FLOAT(n,d)

• 日期：DATE, DATETIME

• 货币：MONEY

3.2 DDL 数据定义 SQL 语句

数据定义语言（Data Definition Languange）DDL 是 SQL 语言中用于创建、修改、删除数据库对象（数据库，表，索引）的语句。

数据库

创建数据库 CREATE DATABASE <数据库名>;

修改数据库 ALTER DATABASE  <数据库名> <修改内容>;

        例如，ALTER DATABASE CourseDB RENAME TO CourseManageDB;

删除数据库 DROP DATABASE <数据库名>;

数据库表

1. 创建数据库表

SQL  CREATE TABLE ＜表名＞ （＜列名1＞ ＜数据类型＞ ［列完整性约束］，   ＜列名2＞ ＜数据类型＞ ［列完整性约束］，   ＜列名3＞ ＜数据类型＞ ［列完整性约束］，... );

列完整性约束

• PRIMARY KEY——主键

• NOT NULL——非空值

• NULL——空值

• UNIQUE——值唯一

• CHECK——有效性检查

• DEFAULT——缺省值 默认值

SQL CREATE TABLE Course (CourseID char(4) PRIMARY Key,  CourseName varchar (20）NOT NULL UNIQUE,  CourseType varchar (10）NULL CHECK（CourseType IN（'基础课'，'专业课'，'选修课')),  CourseCredit smallint NULL,  CoursePeriod smallint NULL,  TestMethod char(4) NOT NULL DEFAULT '闭卷考试'  );

表约束定义键

SQL CREATE TABLE ＜表名＞ （ <代理键列名> <序列类型> NOT NULL,   ＜列名1＞ ＜数据类型＞ ［列完整性约束］，   ＜列名2＞ ＜数据类型＞ ［列完整性约束］，   ＜列名3＞ ＜数据类型＞ ［列完整性约束］，   ...   CONSTRAINT <约束名> PRIMARY KEY(<复合主键列名1>, <复合主键列名2>)   CONSTRAINT <约束名> PRIMARY KEY(<代理键列名>)   CONSTRAINT <约束名> FOREIGN KEY(<外键列名>)     REFERENCES <表名>(<关联主键列名>)     [级联选项] );

2. 修改表结构

ALTER TABLE <表名> <修改方式>;

ADD修改方式，用于增加新列或列完整性约束

ALTER TABLE <表名> ADD <新列名称> <数据类型> | [完整性约束];

DROP修改方式，用于删除指定列或列的完整性约束条件

ALTER TABLE <表名> DROP COLUMN <列名>;

ALTER TABLE <表名> DROP CONSTRAINT <完整性约束名>;

RENAME修改方式，用于修改表名称、列名称

ALTER TABLE <表名> RENAME TO <新表名>；

ALTER TABLE <表名> RENAME <原列名> TO <新列名>；

ALTER修改方式，用于修改列的数据类型

ALTER TABLE <表名> ALTER COLUMN <列名> TYPE <新的数据类型>；

3. 删除表结构

DROP TABLE <表名>;

注意：该语句将删除该表的所有数据及其结构

索引

素引（Index）是一种数据结构它提供了基于一个列或多列的取值顺序快速访问表中元组的机制。

创建和维护索引都需要较大开销

索引创建 CREATE INDEX <索引名> ON <表名> (<列名>);

例如，CREATE INDEX Birthday_Idx ON STUDENT (Birthday);

索引修改 ALTER INDEX <索引名> <修改项>；

例如，索引名称修改语句格式如下：

ALTER INDEX <索引名> RENAME TO <新索引名>;

索引删除 DROP INDEX <索引名>；

3.3 DML 数据操纵SQL语句

数据操纵语言（Data Manipulation）DML 是 SQL 语言中用于关系表中记录数据增添、修改、删除的语句。

1. 数据插入

INSERT INTO <表名|视图名>[<列名表>] VALUES （列值表）；

SQL INSERT INTO Student  VALUES ('2017220201201'，'廖京'，'男'，'2000-02-12'，'计算机应用'，'liaojin@163.com')， （'2017220201202'，'唐明'，'男'，'2000-03-17'，'计算机应用'，'tm@163.com'）， （'2017220201203'，'林琳'，'女'，'2000-05-23'，'计算机应用'，'linglin@163.com'）；

2. 数据更新

SQL UPDATE <表名|视图名> SET <列名1>=<表达式1>，<列名2>=<表达式2>... [WHERE <条件表达式>];

3. 数据删除

SQL DELETE FROM <表名|视图名> [WHERE<条件表达式>]；

4. 表数据清空

TRUNCATE TABLE <表名>；

TRUNCATE 可以从一个表中快速地移除所有行。它与在表上执行无条件 DELETE 语句达到相同的效果，不过它会更快，因为它没有实际扫描表。

3.4 DQL 数据查询SQL语句

数据查询语言（Data Query Language）DQL  是 SQL 语言中用于对数据库进行数据查询的语句。

数据查询

SQL SELECT [ALL|DISTINCT] <目标列>[, <目标列>...] [INTO<新表>] FROM <表名|视图名>[, <表名|视图名>...] [WHERE <条件表达式>] [GROUP BY <列名> [HAVING <条件表达式>]] [ORDER BY <列名> [ASC|DESC]];

WHERE

BETWEEN...AND 限定列值范围

LIKE 与 通配符 限定字符串数据范围

下划线 _ 通配符用于代表一个未指定的字符。

百分号 % 通配符用于代表一个或多个未指定的字符。

AND, OR, NOT

IN, NOT IN

与 HAVING 的区别 WHERE: 在数据分组（GROUP BY）之前应用 于过滤原始数据行 HAVING： 在数据分组（GROUP BY）之后应用 用于筛选分组后的数据，过滤聚合函数（如SUM、COUNT、AVG等）的结果

ORDER BY

ORDER BY <列名> [ASC 升序 | DESC 降序] 默认升序排列

可以按多个列排序

内置函数

聚合函数、算术函数、字符串函数、日期时间函数、数据类型转换函数

GROUP BY

在SQL语言中，可使用内置函数对查询结果集进行分组数据统计。这是通过在SELECT语句中加入Group By子语句来实现。

SQL SELECT 统计函数（目标列） FROM <表名> [WHERE 条件] GROUP BY <目标列> [Having 条件];

子查询与多表关联查询

子查询

SQL SELECT <目标列>[, <目标列>…] FROM <表名> WHERE <条件中嵌套另一关系表的SELECT 查询结果集>

连接关联多表查询

SQL SELECT <目标列>[, <目标列>…] FROM <表名1>,<表名2>,…, <表名n>， WHERE <关系表之间的连接关联条件>

一般连接查询查询比子查询性能高

JOIN...ON 连接查询语句

SQL SELECT <目标列>[, <目标列>…] FROM <表名1> JOIN <表名2> ON <连接条件>;

外部连接

前面的都是内部连接（不符合条件的不出现）

如果希望出现不符合条件的数据，可以使用外部连接

• LEFT JOIN: 左外连接 ，即使没有与右表关联列值匹配，也从左表返回所有的行。

• RIGHT JOIN: 右外连接 ，即使没有与左表关联列值匹配，也从右表返回所有的行。

• FULL JOIN: 全外连接 ， 同时进行左连接和右连接 ，即返回所有行。

FROM A LEFT | RIGHT | FULL JOIN B A是左表，B是右表（原理见上一章）

3.5 视图SQL语句

视图是一种通过基础表或其它视图构建的虚拟表。它本身没有自己的数据，而是使用了存储在基础表中的数据。

视图创建

CREATE VIEW <视图名>[(列名1), (列名2), …]  AS <SELECT查询>；

视图删除

DROP VIEW <视图名>；

好处：

1. 使用视图简化复杂SQL查询操作：数据库开发人员可以将复杂的SQL查询语句封装在视图内，外部程序只需要使用简单的视图访问方式，便可获取所需要的数据。
2. 使用视图提高数据访问安全性：通过视图可以将数据表中敏感数据隐藏起来，外部用户无法得知数据表的完整数据，降低数据库被攻击的风险。此外，还可以保护用户隐私数据。
3. 提供一定程度的数据逻辑独立性：通过视图，可提供一定程度的数据逻辑独立性。当数据表结构发生改变，只要视图结构不变，应用程序可以不作修改。
4. 集中展示用户所感兴趣的特定数据：通过视图，可以将部分用户不关心的数据进行过滤，仅仅提供他们所感兴趣的数据。

如果视图满足一定的条件，例如视图仅包含单个表，且该表的主键列是视图的一部分，并且视图中没有聚合函数或者DISTINCT关键字，那么该视图就是可更新的。在这种情况下，可以通过视图向基础表插入、更新或删除数据。

3.6 数据库SQL编程项目实践

4. 数据库设计与实现

4.1 数据库设计概述

数据库设计方案

数据库应用架构设计

单用户结构、集中式结构、客户／服务器结构、分布式结构。

数据库内部结构设计

概念数据模型、逻辑数据模型、物理数据模型。

数据库应用访问方式设计

直接本地接口连接访问、基于标准接口连接访问、基于数据访问层框架连接访问。

数据库开发过程

4.2 E-R模型方法（理解）

概念

E-R模型是“实体-联系模型”（Entity-Relationship Model）的简称。它是一种设计系统概念数据模型、逻辑数据模型的有效方法。

基本元素

实体

描述事物的数据对象。

一个具体的东西，数据库里的实体

属性

描述实体特征的数据项。每个实体都具有1个或多个属性。

标识符

标识不同实体实例的属性。标识符可以是1个或多个属性。

与主键相似，区别是标识符是一个逻辑概念，主键是物理概念

联系

指实体之间的联系。

联系中关联的实体数目称为联系度数。

二元实体联系类型

实体联系的实例数量称为基数（Cardinality） 最小基数、最大基数

在实体之间除了需要反映了数量对应关系，有时还需要反映实体参与关系的必要性，采用可选（optional）或强制(mandatory)表示

实体继承联系

父实体、子实体

强弱实体联系

按照实体之间的语义关系

弱实体：对于另外实体有依赖关系的实体，即必须以另一实体的存在为前提。

强实体：被依赖的实体。

标识符依赖弱实体：弱实体的标识符中包含了所依赖实体的标识符

非标识符依赖弱实体：有自己独立的标识符

4.3 数据库建模设计 （理解）

依次设计

概念数据模型 (Concept Data Model，CDM）：是一种面向用户的系统数据模型，它用来描述应用系统的概念化数据结构。使系统设计人员在数据库设计的初始阶段，不考虑计算机系统及DBMS的具体技术问题，而集中精力分析业务数据以及数据之间的联系等，建模描述系统的数据对象及其组成关系。

设计初始E-R图，设计实体和关系

逻辑数据模型(Logic Data Model，LDM）：是在概念数据模型基础上，从系统设计角度描述系统的数据对象组成及其关联结构，并考虑这些数据对象符合数据库模型的逻辑表示。

继续完善，考虑到在数据库中的实际逻辑

物理数据模型(Physical Data Model，PDM）：是在逻辑数据模型基础上，针对具体DBMS设计系统数据模型。它用于描述系统数据模型在具体DBMS中的数据库对象组织、存储方式、索引方式、访问路径等实现信息。

设计数据库表，索引等等

概念数据模型设计

一般采用 E-R 模型方法进行建模设计

1. 业务数据分析，抽取数据实体
2. 定义实体属性及其标识
3. 建立实体联系，构建局部E-R模型图
4. 分类、聚集和概括各个部分E-R模型图
5. 完善全局E-R模型图，建立系统业务数据组成结构

CDM/LDM 到 PDM 转换原理

当使用关系数据库时，物理数据模型（PDM）即为关系模型。CDM/LDM到PDM的转换其实就是E-R模型图到关系模型的转换。

1. 将每一个实体转换成一个关系表，实体属性转换为关系表的列，实体标识符转换为关系表的主键或外键。

1. 将实体之间的联系转化为关系表之间的参照完整性约束。

弱实体转换关系表

弱实体转换时有些特殊处理（加外键）

实体联系转换参照完整性约束

“1:1实体联系”转换表示

把其中一个的主键作为外键放到另一个里

“1:N实体联系”转换表示

把父实体的主键作为外键放到子实体里

“M:N实体联系”转换表示

创建一个新的关联表

实体继承联系转换参照完整性约束

把父实体的主键作为外键放到子实体里

实体递归联系转换参照完整性约束

“1:N实体递归联系”的转换

标识符既是主键又是外键

“M:N实体递归联系”的转换

增加关联表

4.4 数据库规范化设计

数据访问操作存在的异常

• 插入数据异常

• 删除数据异常

• 修改数据异常

不规范的关系表可能存在数据冗余，同时引出数据访问操作异常现象，难以使数据库保持数据的一致性。

函数依赖理论

数学定义

类型

1. 完全函数依赖

2. 部分函数依赖

3. 属性传递依赖

4. 多值函数依赖

关系规范化范式

关系规范化是把一个有访问异常的关系表分解成结构良好的关系表，使得这些关系表包含最小的数据元余。

规范化范式（Normal Form，NF）是指关系表符合特定规范化程度的模式

1. 第1范式（1NF）

如果关系表中的属性不可再细分，该关系满足第1范式。反之，该表就不是关系表。

2. 第2范式（2NF）

如果关系满足第1范式，并消除了关系中的属性部分函数依赖，该关系满足第2范式。

3. 第3范式（3NF）

如果关系满足第2范式，并切断了关系中的属性传递函数依赖，该关系满足第3范式。

4. 巴斯-科德范式（BCNF）(了解)

在关系中，所有属性函数依赖的决定因子都是候选键，该关系满足BCNF范式。

5. 第4范式（4NF）

如果关系满足BCNF范式，并消除了多值函数依赖，该关系满足第4范式。

即在一张表里存在“多对多”的关系（且没有单独的主键，是复合键）

关系规范化程度利弊

关系的规范化程度依次提升：

1NF→2NF→3NF→BCNF→4NF

关系的规范化程度越高，关系数据库存储的元余数据就越少，可消除的数据访问异常就越多。

不过关系的规范化程度越高，分解出来的关系表就越多，但在数据查询访问时，需关联更多关系表，其数据库处理效率会降低

逆规范化处理

4.5 数据库设计模型SQL实现

4.6 基于Power Designer的数据库设计建模实践

5. 数据库管理

5.1 数据库管理概述

数据库管理（Database Management）是指为保证数据库系统的正常运行和服务质量必须进行的系统管理工作。

pgAdmi——管理PostgreSQL数据库

5.2 存储管理

数据库元数据与数据字典

九、数据库存储引擎

数据库存储引擎是指数据库管理系统中实现数据库存储管理的功能模块程序，它负责数据库中数据存储、数据检索和数据文件管理等功能处理。

核心组件

5.3 索引管理

B+树索引结构

散列索引

5.4 事务管理

为什么需要事务管理

在数据库应用系统中，完成一个业务处理通常需要多个操作步骤才能完成处理。在每个操作步骤中，都可能遭遇失败。若没有一个处理机制就可能造成操作数据混乱，从而破坏数据一致性。

事务概念

在数据库中，事务（Transaction）是指针对单个业务处理功能的一组数据库访问操作，要求它们要么都成功执行，要么都不执行。

事务特性

为了确保关系数据库共享访问的数据正确性，要求DBMS的事务管理机制维护事务的ACID特性。

事务ACID特性：

• 原子性（Atomicity）：事务所有操作在数据库中要么全部执行，要么全部不执行。

• 一致性（Consistency）：事务多次执行，其结果应一致。

• 隔离性（Isolation）：事务与事务之间隔离，并发执行透明。

• 持续性（Durability）：事务完成后，数据改变必须是永久的。

事务并发执行

多个事务程序在数据库系统中同一时段运行

为什么事务需并发执行？

• 改善系统的资源利用率

• 减少事务程序运行的平均等待时间

事务SQL程序

事务控制语言（Transaction Control Language）TCL 是SQL语言中 用于数据库内部事务处理的语句。

语句

• BEGIN 或 START TRANSACTION；事务开始语句

• ROLLBACK；事务回滚语句

• COMMIT；事务提交语句

• SAVEPOINT：事务保存点语句

程序框架

SQL BEGIN; SQL语句1; SQL语句2; ... COMMIT

SQL BEGIN； SQL语句1; SQL语句2; ... ROLLBACK;

SQL BEGIN； SQL语句1; SQL语句2; ... SAVEPOINT 保存点名; ... SQL语句n; ROLLBACK 保存点名;

事务程序中不能使用的SQL语句

• 创建数据库 CREATE DATABASE

• 修改数据库 ALTER DATABASE

• 删除数据库 DROP DATABASE

• 恢复数据库 RESTORE DATABASE

• 加载数据库 LOAD DATABASE

• 备份日志文件 BACKUP LOG

• 恢复日志文件 RESTORE LOG

• 授权操作GRANT

• ...

基本上只能对DML使用事务

默认事务方式

每一个SQL语句自动构成一个事务

5.5 并发控制

并发控制需解决的问题

1. 丢失更新数据

先后修改数据，最后只保存了一个

2. 不可重复读取

A多次读取数据，想得到相同的数据，中途被别的改变了

3. 脏数据读取

读到的数据，已经在别的进程中回滚了

并发事务调度原理与策略

并发事务调度目标：是控制多个事务的数据操作语句按照恰当的顺序访问共享数据，使这些事务执行之后，避免造成数据的不一致性，即解决“丢失更新数据”、““不可重复读”、脏数据读”等问题。

事务调度原理：在DBMS中，事务管理器将并发执行事务的SQL数据操作语句提交给并发控制调度器。由并发控制调度器将各个事务的SQL数据操作语句按照一定顺序进行调度执行，并完成对数据库缓冲区的读写操作

事务调度策略：在事务并发执行中，只有当事务中数据操作调度顺序的执行结果与事务任务串行执行结果一样时，该并发事务调度才能保证数据操作的正确性和一致性。符合这样效果的调度称为可串行化调度

DBMS并发事务调度效果：使并发事务调度实现的处理结果与串行化调度处理结果一致。

数据库锁机制

资源锁定访问

在DBMS中，通过加入锁表机制，来实现共享数据锁定访问，其加锁方式包含如下类型。

• 排它锁定（Lock-X）——锁定后，不允许其它事务对共享数据再加锁（包括共享锁定和排它锁定），不会被其他事务读或写。

• 共享锁定（Lock-S）——锁定后，只允许其它事务对共享数据添加读取锁，防止其他事务对被锁定的数据项进行写操作（即不能加排它锁）。

资源锁定粒度

• 数据库——粒度最大

• 表——粒度较大

• 页面——粒度中等

• 行——粒度小

资源锁定实施方式

• 隐式锁定——DBMS缺省执行

• 显式锁定——加锁命令显式执行

基于锁机制的并发控制协议

1. 锁操作的相容性

2. 加锁协议

一级加锁协议

任何事务在修改共享数据对象之前，必须对该数据执行排它锁定指令，直到该事务处理完成，才进行解锁指令执行。

在写数据前加排他锁

解决更新丢失问题。但不能解决不可重复读取、脏读问题。

二级加锁协议

在一级加锁协议基础上，当并发事务对共享数据进行读操作，必须先对该数据执行共享锁定指令，读完数据后即刻释放共享锁定。

在读数据前加共享锁，读完一次就释放

可以防止“丢失更新”的数据不一致性问题，还可防止出现脏读数据问题。但有可能会出现“不可重复读取”的数据不一致问题。

三级加锁协议

在一级加锁协议基础上，当并发事务对共享数据进行读操作，必须先对该数据执行共享锁定指令，直到该事务处理结束才释放共享锁定。

在读数据前加共享锁，直到所有数据读完才释放

问题都可以解决

3. 比较

两阶段锁定协议

死锁问题

事务隔离

5.6 安全管理

数据库系统安全模型

数据库用户存取权限控制安全模型

用户管理

数据控制语言（Data Control Language）DCL 是 SQL 语言中用于对数据库对象访问权进行控制的语句。

1. 用户创建SQL语句

SQL CREATE USER <用户账号名> [[WITH] option [...]];

2. 用户修改SOL语句

SQL -- 修改用户的属性 ALTER USER <用户名> [[WITH] option [...]];  -- 修改用户的名称 ALTER USER <用户名> RENAMETO <新用户名>; -- 修改用户的参数值 ALTER USER <用户名> SET <参数项> {TO|=} [value|DEFAULT]; -- 重置用户参数值 ALTER USER <用户名> RESET <参数项>;

3. 用户删除SQL语句

DROP USER <用户名>；

权限管理

数据库权限管理是指DBA管理员或数据库对象拥有者控制其它角色或用户对其所拥有对象进行限制访问。

权限管理基本操作：

• 授予权限

• 收回权限

• 拒绝权限

权限类别：

• 数据库系统权限

• 数据库对象访问操作权限

• 数据库对象定义操作权限

角色管理

在DBMS中，为了方便对众多用户及其权限进行管理，通常将一组具有相同权限的用户定义为角色（Role）。

1. 角色管理SQL语句

SQL -- 创建角色 CREATE ROLE <角色名> [[WITH] option [...]]； -- 修改角色属性 ALTER ROLE <角色名> [[WITH] option [...]]； -- 修改角色名称 ALTER ROLE <角色名> RENAME TO <新角色名>; -- 修改角色参数值 ALTER ROLE <角色名> SET <参数项> {TO|=} {value|DEFAULT}； -- 复位角色参数值 ALTER ROLE <角色名> RESET <参数项>; --删除指定角色 DROP ROLE <角色名>；

2. 角色权限授予

5.7 PostgreSQL数据库管理项目实践

6. 数据库应用编程

6.1 数据库服务器PL/pgSQL编程

数据库服务器编程是指在数据库服务器端运行的数据库访问程序编写，如用户自定义函数、存储过程、触发器、游标等程序。

数据库编程语言PL/pgSQL

PL/pgSQL(Procedural Language/PostgresQL）是一种支持PostgreSQL数据库服务器端运行的面向过程编程语言。

• 支持复杂数据类型和自定义数据类型

• 可编写控制逻辑处理程序

• 可嵌入SQL语句实现数据库访问

• 支持函数、存储过程、触发器编程

• ...

PL/pgSQL基本语法

1. 基本格式

SQL DO $$ DECLARE     声明变量 BEGIN     代码过程 END $$;

大小写不敏感，但一般关键字全大写

缩进不敏感，但一般会缩进

有分号

2. 声明变量

SQL DECLARE     变量名 [CONSTANT] 变量类型 [:=表达式];

声明与指定表的行相同类型的变量，称为行类型变量。常用于数据库查询的结果赋值给变量

SQL 变量名 表名%ROWTYPE；

用户还可声明变量为记录类型，记录型变量与行类型变量类似，但是它们没有预定义的结构。

SQL 变量名 RECORD；

PostgreSQL可使用已定义变量来定义新变量

SQL 变量1 变量2%TYPE；

3. 条件语句

IF-THEN, IF-THEN-ELSE, IF-THEN-ELSIF-ELSE

SQL IF boolean-expression THEN     statements [ELSIF boolean-expression THEN     statements ...] ELSE     statements END IF;

CASE

SQL CASE WHEN boolean-expression1 THEN     statements WHEN boolean-expression2 THEN     statements ELSE     statements ... END CASE;

4. 循环语句

LOOP

SQL LOOP     statements END LOOP [label];

EXIT

SQL EXIT

CONTINUE

SQL CONTINUE [label] [WHEN expression];

WHILE

SQL WHILE expression LOOP     statements END LOOP;

FOR

SQL FOR 变量名 IN [REVERSE(反过来)] expression LOOP     statements END LOOP;

5. 异常处理语句

SQL BEGIN     ...     EXCEPTION WHEN ... THEN     ... END

函数编程

函数创建

OR REPLACE 如果已有函数就替换

函数调用

SQL select 函数名(参数); select * from 函数名(参数); select into 自定义变量 from 函数名(参数);

游标编程

游标控制语言（Cursor Control Language）CCL 是SQL语言中用于数据库游标操作的语句。

游标（cursor）是一种指向数据库查询结果集的指针，通过它可以从结果集中提取每一条记录进行处理。

遍历查表时的临时变量

1. 声明游标

非绑定游标变量

SQL 游标名 refcursor;

绑定游标变量

SQL 游标名 CURSOR [(arguments)] FOR|IS 查询语句;

2. 打开游标

打开未绑定游标

SQL OPEN 游标名 FOR 查询语句； OPEN 游标名 FOR EXECUTE 查询字符串；

EXECUTE 会动态执行查询字符串

打开绑定游标

SQL OPEN 游标名 [(argument_values)];

如果游标变量在声明时包含接收参数，在打开游标时需要传递参数，该参数将传入到游标声明的查询语句中执行。

3. 使用游标

SQL FETCH 游标名 INTO target；

FETCH命令从游标中读取当前指针所指向记录的数据到目标中。可通过PL/pgsaL内置的系统变量FOUND来判断读取是否成功。

4. 关闭游标

SQL CLOSE 游标名;

6.2 存储过程

定义

• 存储过程(Stored Procedure）是数据库中类似函数的一种程序对象，它由一组完成特定数据处理功能的SQL语句和过程语句组成

• 存储过程与函数区别在于，存储过程程序没有返回类型声明

• 外部程序可以调用数据库的存储过程执行。

创建

数据库一般使用CREATE PRECEDURE命令创建存储过程

PostgreSQL 10之前版本只能使用CREATE FUNCTION命令创建存储过程，

PostgreSQL 10之后版本也可以使用CREATE PRECEDURE命令创建存储过程。

inout 代表输出值

调用

SQL CALL 存储过程名（参数）；

删除

和函数基本没区别，只是CREATE PRECEDURE，一定没有 RETURNS，调用使用CALL

优缺点

6.3 触发器编程

触发器概念

触发器是特殊类型的存储过程，其过程程序由事件（如INSERT、UPDATE、DELETE操作等DML和DDL操作）触发而自动执行

可以实现比约束更复杂的数据完整性，经常用于加强数据的完整性约束和业务规则

触发器分类

按DML操作语句分类：INSERT触发器，DELETE触发器，UPDATE触发器

按触发器执行次数分类：

• 语句级触发器：FOR EACH STATEMENT（默认值）执行一条SQL语句只执行一次

• 行级触发器：FOR EACH ROW 每变化一行数据执行一次

按触发时间分类：

• BEFORE触发器：在触发事件之前执行触发器程序

• AFTER触发器：在触发事件之后执行触发器程序

• INSTEAD OF触发器：在触发事件发生后，执行触发器中的语句，而不执行产生触发事件的SQL语句

触发器程序中的特殊变量

NEW

NEW变量数据类型是RECORD。对于行级触发器，它保存了INSERT或UPDATE操作产生的新行记录数据。对于语句级触发器，它的值是NULL

OLD

OLD变量数据类型是RECORD。对于行级触发器，它保存了UPDATE或DELETE操作修改或删除的旧行记录数据。对于语句级触发器，它的值是NULL

TG_OP

TG_OP变量数据类型是text，其值为INSERT、UPDATE、DELETE字符串之一。使用它来获取触发器是由哪类操作引发。

创建触发器

触发器修改

SQL ALTER TRIGGER old_name ON table_name RENAME TO new_name;

触发器删除

SQL DROP TRIGGER [IF EXISTS] trigger_name ON table_name [CASCADE|RESTRICT];

IF EXISTS：不存在时提示而不是报错

CASCADE：级联删除依赖此触发器的对象。

RESTRICT：如果有依赖对象存在，则拒绝删除。该参数缺省是拒绝删除。

事件触发器

可以捕获数据库级别上的对象DDL事件

6.4 应用程序访问数据库

JDBC

• JDBC(Java DataBase Connectivity，Java数据库连接）是一种用于执行SQL语句的Java APl。

• JDBC由java.sql.*包中的一些类和接口组成，它为数据库开发人员提供编程访问数据库的API

Java // 1.加载驱动 Class.forName("驱动名"); Class.forName("org.postgresql.Driver"); // pgsql驱动 // 2.建立连接 String URL = "jdbc:postgresql://localhost:5432/testdb"; String username = "myuser"; String password = "123456"; Connection conn = DriverManager.getConnection(URL, username, password); // 3.创建Statement对象 Statement stmt = conn.createStatement(); // 4.执行sql语句 String sql = "xxx"; // 直接写sql语句 stmt.executeQuery(sql); // 执行select语句 stmt.executeUpdate(sql); // 执行 update, insert, delete语句 // 5.ResultSet结果集 // ResultSet对象作为结果集，保存执行sql语句返回的结果 ResultSet rs = stmt.executeQuery(sql); // next()方法指向下一行 while(rs.next()){     // 通过getXXX方法取出数据     String name = rs.getString("name"); // 取出name列内容     int age = rs.getInt("age"); // 取出age列内容 } // 6.关闭连接 rs.close(); // 关闭结果集 stmt.close(); // 关闭执行对象 conn.close(); // 关闭连接对象

嵌入式SQL与高级语言混合编程

在高级语言中嵌入SQL语句进行数据库应用编程处理

包含了数据库SQL操作语句的高级语言称为宿主语言，而SQL语句在应用程序称为嵌入式SQL（ESQL）

Java中使用JDBC

C中采用

Java EXEC SQL <SQL语句> END_EXEC

6.5 Java WEB数据库编程