数据库复习 | LiamY Blog

type

status

slug

date

summary

第一章数据库及其概念

1.1数据库及其系统概念

什么是数据库

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

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

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

数据库可实现复杂的数据管理，普通数据文件难以实现。

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

数据库的操作访问与控制管理由数据库管理系统(DBMS)软件实现；普通数据文件的操作访问与控制管理，都必须由应用程序实现。

什么是数据模型

数据模型是指描述事物对象的数据结构组成、数据语义联系、数据约束的抽象结构及其说明。

1）数据结构: 用于描述事物对象的静态特征，包括事物对象的数据组成、数据类型、数据性质等。

2）数据操作:用于描述事物对象的动态特征，包括数据的插入、修改、删除和查询等访问操作。

3）数据约束：用于描述数据结构中数据之间的语义联系、数据之间的制约和依存关系，以及数据动态变化的规则等。

数据库使用的数据模型

层次数据模型

网状数据模型

关系数据模型

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

1.层次数据模型

层次数据模型思想：采用“树”结构来组织、存储和管理数据。

2.网状数据模型

网状数据模型思想：采用“网状图”结构组织、存储和管理数据。

3.关系数据模型

关系数据模型思想：采用“二维表”结构组织、存储和管理数据，并以关联列实现表之间的联系。

关系数据模型的优点与局限

关系数据模型优点：

数据结构简单、操作灵活

支持关系与集合运算操作

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

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

关系数据模型局限：

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

支持的数据类型较简单

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

数据库系统

数据库系统（Database Systems）是一类基于数据库进行数据管理与信息服务的软件系统。

数据库系统由用户、数据库应用程序、数据库管理系统(DBMS)和数据库四个部分组成。

数据库管理系统

数据库管理系统（Database Manage System，DBMS ）——是一种专门用来创建数据库、管理数据库、维护数据库，并提供外部对数据库进行访问的系统软件。

数据库管理系统一般具有如下基本功能:

创建数据库、数据库表以及其它对象

读写、修改、删除数据库表中数据

维护数据库结构

执行数据访问规则

提供数据库并发访问控制和安全控制

执行数据库备份和恢复

。。。

数据库

数据库(Database)是一种依照特定数据模型组织、存储和管理数据的文件集合。

关系数据库中数据内容

在关系数据库中，除了存储和管理应用的用户数据外，还需要存储与管理数据库本身的元数据、索引数据、运行数据等系统数据。

关系数据库的对象组织

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

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

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

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

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

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

数据库系统应用结构

1.单机用户结构

特点: 在单机用户结构系统中，整个数据库应用系统都装在一台计算机上，由一个用户进行访问操作，数据不能共享，数据冗余度大。

2.集中式结构

特点: 数据库系统的应用程序、DBMS、数据，都部署在同一服务器上运行，多个用户使用自己的计算机终端网络连接服务器，并可实现共享访问数据库。

优缺点: 结构简单，易于维护，但是当终端用户增加到一定数量后，服务器及网络将成为数据存取访问的瓶颈，使系统的性能大大地降低。

3.客户/服务器结构

特点:数据库应用系统的数据集中在数据库服务器管理、应用分布客户机处理。客户端应用程序通过网络并发访问数据库服务器中的数据库。

优缺点:在客户/服务器结构系统中，客户机程序与数据库服务器分工进行数据处理，提高了系统的负载分担能力，但仍会因大量客户端并发访问数据库服务器，导致系统性能瓶颈。

4.分布式结构

特点：分布式结构的数据库系统既实现数据分布，又实现处理分布。分布式数据库系统的各服务器结点数据库在逻辑上是一个整体，但物理分布在计算机网络的不同服务器结点上运行。每个数据库服务器可通过网络既支持多个本地客户机访问，也支持远程客户机访问。网络中的每一个数据库服务器都可以独立地存取与处理数据，并执行全局应用。

优缺点:分布式结构数据库系统适合跨地区的大型机构及企业等组织对数据库应用的需求，其处理性能强，但数据库的分布处理与维护有一定的开销与技术难度。

数据库应用系统生命周期

1.需求分析

系统分析人员与用户交流，利用软件工程方法获取系统数据需求信息，并采用需求模型定义系统数据组成，及其数据字典。

2.系统设计

系统设计人员根据系统功能和性能需求，对系统数据库进行设计，包括系统概念数据模型、系统逻辑数据模型和系统物理数据模型设计。

3.系统实现

按照系统设计方案进行数据库创建与应用编程实现，主要包括DBMS安装部署、数据库创建、数据对象创建、应用编程实现等方面的工作。

4.系统测试

系统测试人员将测试数据上载到数据库中，对数据库对象进行测试操作访问，实现数据库功能和性能测试。

5.系统运行与维护

系统运维人员在信息系统投入运行过程中，对数据库系统进行定期维护和优化，以保证数据库系统正常地、高效地运行。

1.4典型数据库DBMS系统

1.桌面数据库DBMS系统

桌面数据库DBMS系统主要应用在小规模数据处理、单用户使用的场景。该类DBMS系统软件功能简单、软件体量小、处理能力弱。通常仅支持单一语言的应用程序开发。

2.企业级数据库DBMS系统

企业级数据库DBMS系统主要运用在大规模数据处理、多用户使用的场景；该类DBMS系统软件功能完善、软件体量大、处理能力强；支持多种不同语言的应用程序开发。

第二章关系

2.1（1）实体、关系的概念

1.实体

实体：是指包含有数据特征的事务对象在概念模型世界中的抽象名称

2.关系

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

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

3.关系特征

表中每行存储实体的一个实例数据

表中每列包含实体的一项属性数据

表中单元格只能存储单个值

不允许有重复的行

不允许有重复的列

列顺序可任意

行顺序可任意

关系的数学定义：

定义: 关系是指在集合域 D1、…、Dn 上运算笛卡儿积的有意义子集，其数学描述为 R ⊆ D1 × … × Dn。

说明：关系R只是该笛卡儿积的有意义子集，即关系R的元组数<=笛卡儿积的元组数。

2.1（2）关系键、关系模式

关系的键定义

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

1.复合键

复合键（Compound Key）——是指关系中用来唯一标识元组的多列作为键。

2.候选键

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

3.主键

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

主键作用：

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

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

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

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

4.代理键

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

代理键有什么用途?

由DBMS自动生成的数字序列作为主键，可替代复合主键，以便获得更高性能的数据访问操作处理。

就是serial数据类型

关系模式语句表示

2.2（1）关系模型原理

关系模型

关系模型（Relation Model）——是一种基于二维表结构存储数据实体及实体间联系的数据模型。

由：数据结构、操作方式、数据约束3个部分组成。

数据结构

采用具有关系特征的二维表数据结构来组织与存储数据。在关系数据库中，关系一般被称为关系表或表。一个关系数据库由若干关系表组成，并且表之间存在一定的关联：

数据操作

集合运算操作包括选择（select）、投影（project）、连接（join）、交（intersection）、并（union）、差（difference）等。

专门针对元组的关系操作包括数据行插入（Insert）、修改（Update）、删除（Delete）操作。

数据关系约束

在关系模型中，数据关系约束是指关系表中实体数据完整性、关联表之间数据一致性等要求，一般由业务需求来确定。

在关系模型中，可定义关系表的数据约束如下：

属性列取值类型

属性列取值范围

属性列取值是否唯一

属性列是否允许空值

关联表的参照完整性约束

关系模型的数据操作

并、交、差都需要：关系R与关系S有相同属性组成。

R S

关系模型数据操作采用基于关系代数的数据操作，包括传统的集合运算操作和专门的关系运算操作。

并关系：

关系并运算∪的结果集是由属于R或属于S的所有元组组成。

关系并运算∪的前提：关系R与关系S需有相同属性组成。

差关系：

R-S

关系差运算—的前提：关系R与关系S需有相同属性组成。

交关系：

关系差运算∩的前提：关系R与关系S需有相同属性组成。

笛卡尔积：

笛卡儿积运算的结果集是由所有属于R的元组与所有属于S的元组进行组合而成。

R×S=｛（d1，d2）|d1 ∈R， d2 ∈S}

关系运算

选择、投影、连接、除

表示记号：

选择

1) 选择又称为限制（Restriction）

2) 选择运算符的含义

在关系 R 中选择满足给定条件的诸元组

σF(R) = {t|tR∧F(t)= '真'}

F：选择条件，是一个逻辑表达式

3) 选择运算是从行的角度进行的运算

4)举例设有一个学生-课程数据库，包括学生关系Student、课程关系Course和选修关系SC。

投影

1）投影运算符的含义

从R中选择出若干属性列组成新的关系

(R) = { t[A] | tR }

A：R中的属性列

2）投影操作主要是从列的角度进行运算

但投影之后不仅取消了原关系中的某些列，而且还可能取消某些元组（避免重复行）

连接

自然连接：

在结果中把重复的属性列去掉、

两个关系中进行比较的分量必须是相同的属性组

补充：外连接（自然连接的特例）

左外连接：

ps：这里傻逼cal只是给个示例，结果的表的列不完整，实际上需要连接完整的行，列要补充为两个表的并集

右外连接：

全外连接：

除（很重要）

2.2（2）关系模型原理（完整性约束）

关系模型完整性

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

完整性约束作用：

消除关系表的元组重复存储

保持关联表的数据一致性

实现业务数据规则

关系模型完整性约束组成：

实体完整性约束

参照完整性约束

用户自定义完整性约束

实体完整性约束

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

实体完整性约束规则：

每个关系表中的主键属性列都不允许为空值（NULL），否则就不可能标识实体。

现实世界中的实体是靠主键来标识，主键取值应该唯一，并区分关系表中的每个元组。

参照完整性约束

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

参照完整性约束规则：若关系R中的外键F与关系S中的主键K相关联，则R中外键F值必须与S中主键K值一致。

外键（Foreign key）——在关联的两个关系中，它们具有一个或多个相同属性。若关联列在第一个关系中作为主键，则在第二个关系中作为外键。

用户自定义约束

用户自定义完整性是指用户根据具体业务对数据处理规则要求所定义的数据约束。

用户可以定义如下类型的完整性约束：

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

定义列的缺省值 default

定义列是否允许取空值 null

定义列取值唯一性 unique

定义列之间的数据依赖性

第三章 SQL

3.1 SQL介绍

ps：这里傻逼cal写错了，DML没有SELECT，SELECT是DQL的

1.数据定义语言

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

CREATE DATABASE - 创建新数据库

DROP DATABASE – 删除数据库

ALTER DATABASE - 修改数据库属性

CREATE TABLE - 创建新表

ALTER TABLE – 修改数据库表结构

DROP TABLE - 删除表

CREATE INDEX - 创建索引

DROP INDEX - 删除索引

CREATE VIEW -创建视图

DROP VIEW -删除视图

CREATE TRIGGER -创建触发器

DROP TRIGGER -删除触发器

2.数据操作语言

数据操纵语言（Data Manipulation Language，DML）是SQL语言中用于增添、修改、删除数据的语句。

INSERT - 向数据库表中插入数据

UPDATE - 更新数据库表中的数据

DELETE - 从数据库表中删除数据

3.数据查询语言

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

4.数据控制语言

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

GRANT – 授予用户对数据库对象的权限

DENY – 拒绝授予用户对数据库对象的权限

REVOKE – 撤消用户对数据库对象的权限

5.事务处理语言

事务处理语言（Transaction Process Language，TPL）是SQL语言中用于数据库内部事务处理的语句。

BEGIN TRANSACTION – 开始事务

COMMIT – 提交事务

ROLLBACK – 回滚事务

6.游标控制语言

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

DECLARE CURSOR – 定义游标

FETCH INTO – 提交游标数据

CLOSE CURSOR– 关闭游标

SQL语言的数据类型：

字符：CHAR、VARCHAR、TEXT

名字	存储长度	描述	范围
smallint	2 字节	小范围整数	-32768 到 +32767
integer	4 字节	常用的整数	-2147483648 到 +2147483647
bigint	8 字节	大范围整数	-9223372036854775808 到 +9223372036854775807
decimal	可变长	用户指定的精度，精确	小数点前 131072 位；小数点后 16383 位
numeric	可变长	用户指定的精度，精确	小数点前 131072 位；小数点后 16383 位
real	4 字节	可变精度，不精确	6 位十进制数字精度
double precision	8 字节	可变精度，不精确	15 位十进制数字精度
smallserial	2 字节	自增的小范围整数	1 到 32767
serial	4 字节	自增整数	1 到 2147483647
bigserial	8 字节	自增的大范围整数	1 到 9223372036854775807

日期：DATE、DATETIME

货币：MONEY

ps:

1、

UMERIC类型的语法：

NUMERIC(precision, scale) precision 表示整个数据长度，scale 表示小数部分的长度。如： 1234.567 ，precision 为 7 ，scale 为 3.

NUMERIC 类型在小数点前面长度可达到 131,072 ，小数点后面长度可达到 16,383。scale 可以为0 或正数，下面示例表示 scale 为 0：

NUMERIC(precision) 如果 precision 和 scale 都忽略，则可以存储任何上面提及限制内的长度和精度。

NUMERIC 在 PostgreSQL中 NUMERIC 和 DECIMAL 是等价的，两者都是SQL标准的一部分。如果精度不是必须的，则不应选择 NUMBER，因为计算 NUMBER 要比 integer ，float ，double 慢。

2、

Postgresql提供了三种字符类型：char(n),varchar(n)和text，其中n是正整数。

类型描述 char（n）定长，空白填充 varchar（n）可变长度限制 text，varchar 可变无限长度

char（n）和varchar（n）可以存储最多n个字符的长度，如果存入超过n长度的字符，Postgresql将会发出错误。如果存入的多余字符是空格，Postgresql会截取空格后的字符串存储。如果字符串明确强制转换为char（n）和varchar（n），postgresql将会存储字符串的前n个字符。 text类型可以存储不限长度的字符串。 varchar如果没有指定n整数，则行为类似于text类型，varchar（没有n）的性能和text是相同的。指定n的唯一优点就是当存入超过n长度的字符串时，postgresql会对其进行检查并发出警告。

3.2（1）数据定义SQL语句（数据库）

数据库创建SQL语句：

CREATE DATABASE <数据库名>;

例如：

CREATE DATABASE CourseDB;

数据库修改SQL语句

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

例如：

ALTER DATABASE CourseDB RENAME TO CourseManageDB;

数据库删除SQL语句

DROP DATABASE <数据库名>;

例如：

DROP DATABASE CourseManageDB;

3.2（2）数据定义SQL语句（数据库表）

表创建SQL语句

这里面，对于列的完整性约束有：

列完整性约束关键词

PRIMARY KEY——主键

NOT NULL——非空值

NULL——空值（意思是非必填）

UNIQUE——值唯一

CHECK——有效性检查

DEFAULT——缺省值

表约束定义主键：

使用表约束定义主键的优点：

便于定义复合主键

可命名主键约束

便于定义代理键

表约束定义代理键：

在一些关系表中，为了方便数据处理，可以使用代理键去替代复合主键。在SQL语句中，关系表的代理键采用表约束方式来定义。

表约束定义外键：

在数据库中，一些关系表之间存在关联。在一个表中作为主键的列，在另外的关联表中则作为外键。

例如：

表修改结构SQL语句

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

主要的修改方式：

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

ALTER TABLE <表名> ADD <新列名称><数据类型> [完整性约束]（可有可无）

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

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

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

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

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

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

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

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

表删除SQL语句

DROP TABLE <表名>;

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

比如

DROP TABLE Register;

3.2（3）数据定义sql语句（索引）

什么是索引

索引（Index）是一种按照关系表中指定列的取值顺序组织元组数据存储的数据结构，使用它可以加快表中数据的查询访问。

索引作用：支持对数据库表中数据快速查找，其机理类似图书目录可以快速定位章节内容。

索引优点：

提高数据检索速度

可快速连接关联表

减少分组和排序时间

索引开销：

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

索引会占用额外存储空间

数据操纵因维护索引带来系统性能开销

索引创建SQL语句

CREATE INDEX <索引名> ON <表名><（列名）>；

例在学生信息表Student中，为出生日期Birthday列创建索引，以便支持按出生日期快速查询学生信息。

索引修改SQL语句

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

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

例在学生信息表Student中，将原索引Birthday_Idx更名为Bday_Idx，其索引修改SQL语句如下： ALTER INDEX Birthday_Idx RENAME TO Bday_Idx；

索引删除SQL语句

DROP INDEX <索引名> ；

例在学生信息表Student中，删除bday_idx索引，其索引删除SQL语句如下： DROP INDEX bday_idx；

3.3数据操纵语言DML

数据插入SQL语句

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

例：在学生信息表Student中，插入一个新的学生数据，如:

数据更新SQL语句

数据删除SQL语句

3.4（1）数据查询SQL（单表）

数据查询SQL语句格式

关系的投影

在关系数据库中，最简单的数据查询操作就是从单个关系表中读取指定列的数据，即关系的投影操作。

SELECT <目标列>[，<目标列>…] FROM <关系表>；

为了在结果集中过滤重复数据,可以在查询语句的输出列前加入DISTINCT关键字，比如：

SELECT DISTINCT Major FROM Student

关系的选择

SQL查询语句也可以从一个关系表中读取满足条件的指定行数据，即完成关系数据的元组选择操作。

SELECT * FROM <关系表> WHERE <条件表达式>;

结合选择和投影，从单个表中读取指定行和列

在SQL查询语句中，还可以从一个关系表中读取指定行与指定列范围内的数据。既完成关系的行选择，又完成关系的列投影操作。

SELECT <目标列>[，<目标列>…] FROM <关系表> WHERE <条件表达式>;

比如：

要求：从Student表中查询性别为“男”的学生学号、学生姓名、性别、专业数据。

Where条件子句

在WHERE子句中可以使用如下方式，指定范围数据。

1）使用BETWEEN..AND关键词来限定列值范围，还可以使用关键词LIKE与通配符来限定查询条件。

2）使用通配符来限定字符串数据范围。下划线（_）通配符用于代表一个未指定的字符。百分号（%）通配符用于代表一个或多个未指定的字符。

例1 若要从STUDENT表中查询出生日期在“2000-01-01”到“2000-12-30”的学生数据。其数据查询SQL语句如下：

例2 若要从STUDENT表中查询邮箱域名为“@163.com”的学生数据。其数据查询SQL语句如下：

例3 从STUDENT表中查询性别为“男”，并且专业为“软件工程”的学生数据，其数据查询SQL语句如下。

通配符： %匹配任意长度字符，_匹配单个字符

PS:通配符除了使用LIKE关键字外，还可以使用NOT LIKE关键词用于给出不在范围内的条件。

在SQL查询Where子句中，还可以使用多个条件表达式，并通过逻辑运算符（AND、OR、NOT）连接操作，以及使用IN或NOT IN关键词，进一步限定结果集的数据范围。

例4 在STUDENT表查询时，使用IN关键字限定范围”计算机应用”专业的学生。其SQL语句如下所示。

对结果集进行排序

在SELECT查询语句返回的结果集中,行的顺序是任意的。如果需要结果集排序，可以在SELECT语句中加入ORDER BY关键字。

例若要从STUDENT表中按学生出生日期降序输出学生数据，其数据查询SQL语句如下。

在默认情况下，SQL查询的结果集是按指定列值的升序排列。可以使用关键词ASC和DESC选定排序是升序或降序。

如果结果集需要按多个列排序，可以分别加入关键字ASC或DESC改变。

例若要将STUDENT表查询数据，首先按出生日期降序排列，然后按姓名升序排列，其数据查询SQL语句如下：

3.4（2）数据查询SQL（内置函数、分组统计）

典型SQL内置函数类型如下：

聚合函数

算术函数

字符串函数

日期时间函数

数据类型转换函数

聚合函数

聚合函数是一些对关系表中数值属性列进行计算并返回一个结果数值的函数。

聚合函数	功能
AVG（）	计算结果集指定列数据的平均值
COUNT（）	计算结果集行数
MIN（）	找出结果集指定列数据的最小值
MAX（）	找出结果集指定列数据的最大值
SUM（）	计算结果集指定列数据的总和

例1 若要统计Student表中的学生人数，在SELECT语句中可以使用COUNT（）函数来计算，其查询SQL语句如下：

还比如:

SQL内置函数与分组统计

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

这个例子很经典：

例1 若要分专业统计Student表中的学生人数。在SELECT语句中可以使用GROUP BY分组子句完成统计，其查询SQL语句如下：

例2 若要分专业统计STUDENT表中男生人数，但限定只显示人数大于2的人数，其查询SQL语句如下：

注意：除了使用GROUP BY语句外，列的名称是不允许和内置函数一起混合使用。以下语句不规范。

为什么呢？答案很简单，如果运行这种混合，数量就不匹配了，而GROUP BY的时候，列的名称是分组里面有的，而聚合函数会对每个分组进行一次，那么数量就和分组数数量一致（也就是列名选中的数量）

3.4（3）多表关联查询

子查询与多表关联

子查询SQL语句基本格式：

例在选课管理系统数据库中，希望能检索出“计算机学院”的教师名单。

该操作需要关联教师信息表Teacher和学院信息表College，才能获得这些数据。这里可采用子查询方法实现两表关联查询，其查询SQL语句如下：

使用连接关联多表查询

就是进行笛卡尔积然后再条件筛选

所以，为了更好的性能，我们有下面的方法👇

JOIN … ON

在SQL语言中，实现多表连接关联查询还可以使用JOIN…ON关键词的语句格式。其中两表连接关联查询的JOIN…ON语句格式如下：

例在选课管理系统数据库中，希望获得各个学院的教师信息，包括学院名称、教师编号、教师姓名、教师性别、职称等信息。要求按学院名称、教师编号分别排序输出，其查询SQL语句如下：

(就是重写上面的where方式的笛卡尔积连接筛选方式)

注意：

子查询并不是都可以用关联查询来实现！！！

如果查询条件中含有聚合运算的结果值，必须使用子查询。

换句话说，where子句用以对行进行选择，如果我们要在where子句中使用聚合运算的结果值，由于where子句会先于分组执行，单个查询是无法做到的。必须使用子查询。

外连接

外连接-自然连接的特例

前节介绍的多表连接方式在SELECT查询语句称为内部连接。在一些特殊情况下，如关联表中一些行的主键与外键不匹配，查询结果集就会丢失部分数据。

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

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

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

例如：

3.5 数据控制SQL语句

GRANT权限授予语句

GRANT语句是一种由数据库对象创建者或管理员执行的权限授予语句，它可以把访问数据库对象权限授予给其他用户或角色。

GRANT <权限列表> ON <数据库对象> TO <用户或角色> [ WITH GRANT OPTION ]；

例在选课管理系统数据库中，将课程注册表REGISTER的数据插入、数据修改、数据删除、数据查询访问权限赋予学生角色RoleS。

REVOKE权限收回语句

REVOKE语句是一种由数据库对象创建者或管理员将赋予其它用户或角色的权限进行收回语句，它可以收回原授予给其他用户或角色的权限。

REVOKE <权限列表> ON <数据库对象> FROM <用户或角色> ;

例在选课管理系统数据库中，收回学生角色RoleS在课程注册表REGISTER的数据删除访问权限。

DENY权限拒绝语句

DENY语句用于拒绝给当前数据库内的用户或者角色授予权限，并防止用户或角色通过其组或角色成员继承权限。

DENY <权限列表> ON <数据库对象> TO <用户或角色> ;

例在选课管理系统数据库中，若拒绝教师角色RoleT对教师表TEACHER的数据删除访问权限。

3.6视图SQL语句

什么是视图

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

视图创建

在视图名称后，可以定义组成视图的各个列名。若没有指定列名，则默认采用基础表查询结果集的所有列名作为视图列名。AS关键词后为基础表的SELECT查询语句，其结果集为视图的数据。

例在选课管理系统数据库中，若需要建立一个查看基础课数据的视图BasicCourseView，其创建SQL语句如下。

当视图在数据库中创建后，用户可以像访问关系表一样去操作访问视图。

例使用SELECT语句查询该视图数据，并按课程名称排序输出，其SQL语句如下：

视图删除

DROP VIEW <视图名>；

其中DROP VIEW 为删除视图语句的关键词。<视图名>为将被删除的视图名称。

SQL视图应用

使用视图简化复杂SQL查询操作

数据库开发人员可以将复杂的SQL查询语句封装在视图内，外部程序只需要使用简单的视图访问方式，便可获取所需要的数据。

例在选课管理系统数据库中，查询选修“数据库系统原理与开发”课程的学生名单。这需要关联课程信息表COURSE、开课计划表PLAN、选课注册信息表REGISTER、学生信息表STUDENT，其查询SQL语句如下：

上面这个SQL语句是较复杂和冗长，为了让外部程序简单地实现该信息查询，可以先定义一个名称为DatabaseCourseView视图，其创建SQL语句如下：

使用视图提高数据访问安全性

通过视图可以将数据表中敏感数据隐藏起来，外部用户无法得知数据表的完整数据，降低数据库被攻击的风险。此外，还可以保护用户隐私数据。

提供一定程度的数据逻辑独立性

通过视图，可提供一定程度的数据逻辑独立性。当数据表结构发生改变，只要视图结构不变，应用程序可以不作修改。

集中展示用户所感兴趣的特定数据

通过视图，可以将部分用户不关心的数据进行过滤，仅仅提供他们所感兴趣的数据。

第四章数据库设计

4.1概述

数据库设计方案

数据库设计是数据库应用系统开发的重要内容。在实现数据库之前，必须有明确的设计方案。

数据库应用访问方式设计

数据库应用对数据库访问可以有多种方式，如直接本地接口连接访问、基于标准接口连接访问、基于数据访问层框架连接访问。

数据库结构模型设计

数据库结构模型设计一般分为概念层、逻辑层、物理层设计，它们的设计模型分别为概念数据模型、逻辑数据模型和物理数据模型。

数据库应用架构设计

在不同应用需求场景中，数据库的应用架构方式是不同的。数据库应用架构可分为单用户结构、集中式结构、客户／服务器结构和分布式结构。

结构模型

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

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

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

4.2（1）重点E-R模型方法（基本建模）

什么是E-R模型？

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

在E-R模型中，基本元素包括实体、属性、标识符和联系。

1、实体：

实体(Entity)是指问题域中存在的人、事、物、地点等客观事物在逻辑层面的数据抽象。它用于描述事物的数据对象，如客户、交易、产品、订单等。

2、属性：

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

3、标识符：

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

4、联系

联系(Relationship)是指实体之间的联系，如“学生”与“成绩”的联系、“孩子”与“父亲”、“母亲”的联系等。

二元实体联系类型

1对1（1:1）

1对多（1:N）

多对多（M:N）

上述实体之间的联系反映了一个给定实体有多少实例与另一实体实例存在的数量对应关系。通常采用术语基数（Cardinality）来表示。

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

符号表示：

示例：

4.2（2）重点E-R模型（扩展建模）

实体基继承关系

在E-R模型中，实体之间除了基本联系外，还存在继承联系。继承联系用于表示实体之间的相似性关系。

在实体继承联系中，一端是具有公共属性的实体，称为父实体；另一端是与父实体具有相似属性，同时也具有特殊性的一个或多个实体，称为子实体。

在继承联系中，还可以分为互斥性继承联系和非互斥性继承联系。

互斥继承联系中，父实体中的一个实例只能属于某个子实体，不能同时属于多个子实体。

而在非互斥继承联系中，父实体的一个实例可以属于多个子实体。

除了互斥和非互斥的分类外，继承联系还可以分为完整继承和非完整继承。如果父实体实例必须属于子实体中的某一个，则称为完整继承联系；否则是非完整继承联系。

如果父实体中的实例完整地被各个子实体分别继承，则为完整继承联系，否则为非完整继承联系。

强弱实体联系

在E-R模型中，按照实体之间的语义关系，可以将实体分为弱实体和强实体。弱实体是指那些对于另外实体有依赖关系的实体，即一个实体的存在必须以另一实体的存在为前提。而被依赖的实体称为强实体。

在E-R模型中，根据弱实体在语义上对强实体依赖程度的不同，弱实体又分为标识符（ID）依赖弱实体和非标识符（非ID）依赖弱实体两类。

如果弱实体的标识符中含有所依赖实体的标识符，则该弱实体称为标识符（ID）依赖弱实体。

在有依赖联系的弱实体中，并非所有弱实体都是标识符（ID）依赖弱实体，它们可以有自己的标识符，这样的弱实体即为非标识符（ID）依赖弱实体。

在E-R图中，标识符依赖弱实体和非标识符依赖弱实体的联系连线图符号是不同的

标识符依赖弱实体的联系连线图符号，在弱实体一侧有一个三角形的符号

非标识符依赖弱实体的联系连线图，在弱实体一侧仅为基本鸟足符号

4.3（1）概念数据模型

什么是概念数据模型设计

概念数据模型设计是通过对现实世界中数据实体进行抽取、分类、聚集和概括等处理，建立反映系统业务数据组成结构的过程。

概念数据模型设计一般是采用E-R模型方法进行建模设计。

概念数据模型设计步骤

业务数据分析，抽取数据实体

定义实体属性及其标识

建立实体联系，构建局部E-R模型图

分类、聚集和概括各个部分E-R模型图

完善全局E-R模型图，建立系统业务数据组成结构

4.3（2）数据库建模设计（CDM LDM PDM转换）

数据库结构模型转换设计数据库结构模型转换设计

数据库结构模型设计分为概念数据模型设计、逻辑数据模型设计、物理数据模型设计三个层次。在常规的数据库设计中，将依次对这三种模型进行设计，其中包括不同层次模型之间的转换设计。

不同层次数据模型转换方案

转换原理（CDM/LDM → PDM）

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

E-R模型到关系模型转换原理：将每一个实体转换成一个关系表，实体属性转换为关系表的列，实体标识符转换为关系表的主键或外键。将实体之间的联系转化为关系表之间的参照完整性约束。

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

1:1

1:N

M:N

创建主键、外键的sql语句：

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

当带有实体继承联系的E-R模型转换关系模型时，首先父实体和子实体都各自转换为表，其属性均转换为表的列。

在处理实体继承联系转换时，将父表中的主键放置到子表中，既做主键又做外键。

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

1:N实体递归

先将递归实体转换为表，其属性转换为列，标识符转换为外键，实现自己对自己的参照

M:N实体递归

示例

注意这里的逻辑数据模型和概念数据模型的不同：

逻辑数据模型可以更加具体地给出实体属性定义，如在每个实体中分别标识主键标识符(pi)，及外键标识符(fi)。此外，它将概念数据模型中存在的实体间多对多联系，转换为关联实体(如”预订”)与原实体(“图书”，“借阅者”)之间一对多联系，从而便于在关系数据库中实现

4.4（1）数据库规范化设计（函数依赖）

为什么需要规范化数据库设计

减少数据库中的冗余数据，尽量使同一数据在数据库中仅保存一份，有效降低维护数据一致性的工作量。

设计合理的表间依赖关系和约束关系，便于实现数据完整性和一致性。

设计合理的数据库结构，便于系统对数据高效访问处理。

雇员（雇员编号，姓名，职位，工资，所属部门，部门地址）

部门信息夹杂在雇员信息里面，冗余，且可能导致数据不一致以及数据可能不完整（比如删除员工E0005会导致质检部消失）

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

另一个例子：

数据库表规范化设计是指在数据库中减少数据冗余和定义一个规范的表间结构，实现数据完整性与一致性。所谓数据冗余是指一组数据重复出现在数据库的多个表中。在数据库设计中，尽量避免表间的重复数据列。

规范化数据库设计为数据库系统带来如下益处：冗余数据被消除，同一数据在数据库中仅保存一份，有效降低维护数据一致性的工作量。设计合理的表间依赖关系和约束关系，便于实现数据完整性和一致性。设计合理的数据库结构，便于系统对数据高效访问处理。

函数依赖理论

函数依赖（Functional Dependency)是数据依赖的一种，它反映属性或属性组之间相互依存、互相制约的关系，即反映现实世界的数据约束关系

在函数依赖理论中，定义下列符号：R表示一个关系的模式，U = {A1，A2，…，An}是R的所有属性的集合，F 是R 中函数依赖的集合，r是R 所取的值，t[X]表示元组t在属性X上的取值。

函数依赖的定义

定义：设有一关系模式R（U），X和Y为其属性U的子集，设t，s是关系R中的任意两个元组，如果t [X] = s[X]，则t [Y] = s[Y]。那么称Y函数依赖于X，或X函数决定Y,或X作为决定因子决定Y。也可称 X→Y 在关系模式R（U）上成立。

一个函数依赖要能成立，不但要求关系的当前值都能满足函数依赖条件，而且还要求关系的任一可能值都能满足函数依赖条件。对于当前关系r的任意两个元组，如果X值相同，则要求Y值也相同。即有一个X值就有一个Y值与之对应。或者说，Y值由X值决定，因而这种依赖称为函数依赖。

部分函数依赖

属性传递依赖

多值依赖

设R(U)是属性集U上的一个关系模式。X,Y,Z是U的子集，并且Z=U-X-Y。关系R(U)中多值依赖X→→Y成立，当且仅当对R(U)的任一关系r，给定的一对(x,z)值，有一组Y的值，这组值仅仅决定于x的值而与z值无关。

4.4（2）数据库规范化设计关系规范化范式

关系规范化是把一个有访问异常的关系分解成结构良好的关系的过程，使得这些关系有最小的冗余或没有冗余。

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

第一范式（1NF）

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

第二范式（2NF）

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

第二范式要求关系表中所有非主键属性都要与主键属性有完全函数依赖

例有一个关系（AB,N,O,P）,其复合主键为（AB）,那么N,O,P这三个非键属性都不存在只依赖A或只依赖B情况，则该关系满足第2范式,反之,不满足第2范式。

消除方法是：

将部分函数依赖的属性从原关系中移出，并放入一个新关系中，同时将这些属性的决定因子作为主键放到新关系中。

第三范式（3NF）

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

就是所有非主键属性均不存在传递函数依赖

例若有一个关系（A,N,O,P）,主键为（A）,那么非键属性N,O或P都不能由单个的N,O或P或它们的组合所确定。该关系满足第3范式。

巴斯-科德范式（BCNF）

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

例关系STUDENT(StudentNum, Name, Email)中，决定因子StudentNum也是候选键，该关系满足BCNF范式。

第四范式（4NF）

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

关系规范化程度的利弊

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

不过关系的规范化程度越高，分解出来的关系表就越多，但实现数据查询访问时，需关联多表，其效率降低。

逆规范化处理

规范化减少了数据冗余，易于保证数据的完整性，但规范化过高也会导致数据库性能降低，因此，在利用规范化设计数据库时要平衡两者的关系。为此，有人又提出了逆规范化处理的思想。

所谓逆规范化，就是适当降低规范化范式约束，不再要求一个关系表必须达到很高的规范化程度，而是允许适当的数据冗余性，以获取数据访问性能。

逆规范化处理的基本方法：（1）增加冗余列或派生列（2）多个关系表合并为一个关系表

第五章数据库管理

5.1数据库管理

什么是数据库管理

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

为什么需要数据库管理?

数据库系统随规模增大,系统会变得异常复杂

多用户数据库应用带来数据库访问复杂性

数据安全和数据隐私对机构和用户都非常重要

数据库系统随数据量增加和使用时间增长其性能会降低

系统遭遇意外事件，数据库损坏或数据丢失

数据库管理目标

保障数据库系统正常稳定运行

充分发挥数据库系统的软硬件处理能力

确保数据库系统安全和用户数据隐私性

有效管理数据库用户及其角色权限

解决数据库系统性能优化、系统故障与数据损坏等问题

最大程度地发挥数据库对其所属机构的作用

DMBS管理功能结构

数据库定义、数据库运行管理、数据库组织与存储、数据库维护、数据库通信

DBMS系统层次结构

5.2事务管理

为什么需要事务管理:

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

事务概念：

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

事务状态：

在数据库系统中，事务是DBMS执行的最小任务单元。同时，事务也是DBMS最小的故障恢复任务单元和并发控制任务单元。其生命周期状态变迁如图所示。

事务特性

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

事务ACID特性：

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

一致性（Consistency）：事务多次执行，其结果应一致。指事务执行的结果是数据库从一种正确数据状态变迁到另一种正确数据状态

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

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

事务并发执行

事务并发执行是指多个事务程序在数据库系统中同一时段运行。

事务并发执行原因：

改善系统的资源利用率

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

事务SQL程序

在关系数据库系统中，可以利用SQL语言提供的相应语句编写事务程序。

1.事务SQL语句

BEGIN 或 START TRANSACTION ；事务开始语句

ROLLBACK ；事务回滚语句

COMMIT ；事务提交语句

SAVEPOINT ；事务保存点语句

2.事务SQL程序基本框架

例在选课管理数据库CurriculaDB中，使用事务程序实现对学院信息表College的数据插入，其事务SQL程序如下：

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

创建数据库 CREATE DATABASE

修改数据库 ALTER DATABASE

删除数据库 DROP DATABASE

恢复数据库 RESTORE DATABASE

加载数据库 LOAD DATABASE

备份日志文件 BACKUP LOG

恢复日志文件 RESTORE LOG

授权操作 GRANT

。。。

DBMS默认事务方式

若用户没有显式地定义事务时，DBMS按默认事务方式处理，即每执行一个SQL语句将自动构成一个事务。若将多条SQL语句定义为一个事务时，才使用专门的事务SQL语句显式地定义事务。

5.3（1）并发控制（事务调度）

为什么需要并发控制

当多个事务程序在DBMS系统中同时运行时，可能会出现对一些共享数据同时进行访问操作，如一些事务修改数据，另一些事务读取数据。这些并发的共享数据操作，如果在DBMS中没有一定的约束控制情况下，可能会带来数据不一致性或事务程序死锁问题。因此，在多个事务并发运行时，必须进行并发控制处理。

并发控制目的

支持并发事务处理，使更多用户并行操作，提高系统的并发访问能力。

保证一个事务工作不会对另一个事务工作产生不合理的影响。

脏数据读取

脏数据读取是指一个事务读取了被取消持久化的共享数据。

不可重复读取

不可重复读取是指一个事务对一个共享数据重复多次读取，但前后读取的数据不一致。

很重要的一点，不可重复读是修改或删除。

(1)事务T1按一定条件从数据库中读取了某些数据记录后，事务T2删除了其中部分记录，当T1再次按相同条件读取数据时，发现某些记录消失了。也称为不可重复读取。 (2)事务T1按一定条件从数据库中读取某些数据记录后，事务T2在其中插入了一些记录，当T1再次按相同条件读取数据时，发现多了一些记录。称为幻象读取。

幻像读（Phantom Read）

指一个事务对同一个共享数据读取两次，但是发现第二次读取结果比第一次读取的结果新增了一些数据。出现这种问题的原因：多个事务并发运行，其中一个事务对共享数据进行添加操作。

丢失更新数据

T1、T2两个事务并发执行，它们均对数据库共享数据A进行了非锁定资源的读写操作。

当事务T1和T2均读入该共享数据A并修改，T2提交的结果破坏了T1提交的结果，导致T1的修改被丢失。

并发事务调度原理与策略

事务并发执行可能带来如下问题：当多个事务并发存取共享数据时，由于不当的数据操作顺序，可能出现数据不一致性问题（“丢失更新数据”、“不可重复读”、“脏数据读”等问题）。

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

事务调度原理

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

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

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

5.3（2）并发控制（锁、控制协议、死锁，事务隔离）

数据库锁机制

为了解决多个事务并发对共享数据进行新增、更新、删除、查询带来的数据不一致性问题时，需要对共享数据进行加锁访问。

1.资源锁定访问

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

排它锁定(Lock-X)——锁定后，不允许其它事务对共享数据再加锁

共享锁定(Lock-S)——锁定后，只允许其它事务对共享数据添加读取锁

2.资源锁定粒度

数据库——粒度最大

表——粒度较大

页面——粒度中等

行——粒度小

3.资源锁定实施方式

隐式锁定——DBMS缺省执行

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

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

为了实现并发事务对共享数据访问的串行化调度执行，还必须约束它们对共享数据的操作访问必须是以互斥方式进行。这就需要用到基于数据库锁机制的并发控制协议。

1.锁操作的相容性

加锁协议

在对共享数据进行加锁访问时，还需要按照一些规则实施锁定。例如，合适申请排它锁或共享锁、持锁时间是多少、何时解锁等，这些规则称为加锁协议。不同规则的加锁协议，所能解决的数据库一致性问题是不一样的。

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

特点：使用一级加锁协议，可避免出现更新丢失问题。但不能解决“不可重复读取”、“脏读”等数据不一致问题。

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

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

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

特点：该加锁协议不但可以防止“丢失更新”、“脏读”的数据不一致性问题，还可防止出现“不可重复读取”的数据一致性问题。

不同级别锁协议比较

两阶段锁定协议

并发事务的正确调度准则：一个给定的并发事务调度，当且仅当它是可串行化时，才能保证正确调度。

保证可串行化的一个协议是：二阶段锁定协议

两段锁协议是指同一事务对任何数据进行读写之前必须对该数据加锁；在释放一个封锁之后，事务不再申请和获得任何其他封锁。 三级封锁协议用于解决修改丢失、不可重复读和读脏数据问题，解决问题的焦点是给数据库对象何时加锁、加什么样的锁

两段锁协议解决的是事务调度问题，解决问题的方法是将加锁和锁的释放分为两个阶段，加锁阶段只加锁不释放，只要一个锁开始释放，进入释放阶段，只释放，不加锁，两段锁协议是确保执行结果和这些事务串行的某个执行结果一致的充分条件

二阶段锁定协议规定每个事务必须分两个阶段提出加锁和解锁申请:

增长阶段，事务只能获得锁，但不能释放锁。

缩减阶段，事务只能释放锁，但不能获得新锁。

结论:若并发事务执行的所有事务都遵从两阶段锁定协议(充分条件)，则这些事务的任何并发调度都是可串行化调度，即这些并发调度执行结果可以保证数据库一致性。

需要注意的是采用两段锁协议也有可能产生死锁，这是因为每个事务都不能及时解除被它封锁的数据，可能会导致多个事务互相都要求对方已封锁的数据不能继续运行。

死锁问题解决

1.事务死锁

在基于锁机制的并发事务执行中，如果这些事务同时锁定两个以及以上资源时，可能会出现彼此都不能继续执行的状态，即事务死锁状态。

2.死锁出现的必要条件

互斥条件

请求和保持条件

不剥夺条件

环路等待条件

3.防范死锁的策略

允许用户一次发出当前所需全部资源的锁定，使用完成后，再释放给其它用户访问。

规定所有应用程序锁定资源的顺序必须完全相同。

4.解决死锁的方法

当发生死锁时，回滚其中的一个事务，并取消它对数据库所做的改动。

事务隔离级别

采用加锁协议编写事务程序，可以避免并发事务访问共享数据时可能出现的丢失更新、脏读、不可重复读、幻象读等问题，但这需要基于加锁协议进行比较繁琐的编程处理。在DBMS中，可以采用更为简单的事务隔离级别（Isolation level）。

隔离等级越高，并行度越低。选择何种等级，取决于我们对数据不一致的容忍程度

隔离等级和锁协议等级相关但又不完全对应读取未提交对应无锁读取已提交不取决于锁协议，取决于在事务提交前是否写生效可重读读取需要三级加锁协议可串行化需要满足两阶段锁定-必要条件

5.4（1）安全管理(存取安全模型)

数据库系统安全模型

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

5.4（2）安全管理（用户、角色、权限管理）

用户管理

用户要访问数据库，必须先在DBMS中创建其账号，并成为数据库的用户。此后，用户每次访问数据库，都需要在DBMS进行身份验证，只有合法用户才能进入系统，访问操作数据库对象。

用户管理——在数据库安全管理中，DBMS需要对每个用户进行管理，如用户创建、用户修改、用户删除管理等。

实现用户管理方式：

数据库服务器执行SQL语句管理用户

通过管理工具GUI操作管理用

用户创建SQL语句

有个option挺重要的，这里提出来：

IN ROLE role_name [,…] ：指定创建的用户成为哪些角色的成员

例创建一个新用户，其账号名字为“userA”，密码为“123456”。该用户具有登录权限（Login）和角色继承权限（Inherit）系统权限，但它不是超级用户（SuperUser），不具有创建数据库权限（CreateDB）、创建角色权限（CreateRole）、数据库复制权限（Replication），此外数据库连接数（Connection Limit）不受限。

用户修改SQL语句

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

ALTER USER <用户名> RESET <参数项>; --重置用户参数值

例修改用户“userA”的账号密码为“gres123”。同时也限制该用户的数据库连接数为10。

用户删除SQL语句

DROP USER <用户名>;

例在数据库中，删除用户“userA”。可以通过执行如下用户删除SQL语句实现用户删除。

权限管理

数据库权限管理是指DBA管理员或数据库对象拥有者对其所拥有对象进行权限控制设置。

权限管理基本操作：

授予权限

收回权限

拒绝权限

权限类别：

数据库系统权限

数据库对象访问操作权限

数据库对象定义操作权限

1.权限管理SQL语句

GRANT <权限名> ON <对象名> TO {数据库用户名|用户角色名}； REVOKE <权限名> ON <对象名> FROM {数据库用户名|用户角色名}； DENY <权限名> ON <对象名> TO {数据库用户名|用户角色名}；

例在3.7.1节的工程项目管理系统中，DBA管理员赋予员工用户（userA）对部门表（Department）、员工表（Employee）、项目表（Project）和任务表（Assignment）的读取数据权限。

对用户“userA”实现授权SQL程序如下

角色管理

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

角色管理内容：

创建角色

修改角色

删除角色

角色管理实现方式：

执行SQL语句管理角色

通过GUI操作管理角色

1.角色管理SQL语句

例在工程项目管理系统中，假定需要在ProjectDB数据库内创建经理角色Role_Manager。该角色具有登录权限（Login）和角色继承权限（Inherit）系统权限，但它不是超级用户（SuperUser），不具有创建数据库权限（CreateDB）、创建角色权限（CreateRole）、数据库复制权限（Replication），此外数据库连接数（Connection Limit）不受限。

角色权限授予

例在创建好经理角色Role_Manager后，还需要赋予该角色对数据库表Department、Employee、Project、Assignment的插入、修改、删除、查询权限。

5.5数据备份与恢复

数据库备份与恢复

数据库备份——是指将数据库当前数据和状态进行副本复制，以便当数据库受到破坏或丢失数据时可以进行修复。

数据库恢复——是指数据库中数据丢失或被破坏时，从备份副本将数据库从错误状态恢复到某一正确状态。

备份内容与备份角色

备份内容——包括数据文件、日志文件等。

备份角色——可以是服务器管理员（sysadmin）、数据库所有者（db_owner）、数据库备份员（db_backupoperator）角色之一。

备份介质与备份时机

备份介质——包括磁盘阵列、磁带库、光盘库等。

备份时机——当系统数据库重要数据被修改、日志被清理、用户数据库创建、用户数据库加载等事件出现时。

数据库备份方法

备份方法：

完全数据库备份 将数据库中的所有数据和结构完整地备份到另一个位置或介质中。

差异数据库备份

事务日志备份 是一种只备份自上一次日志备份至今的事务日志数据的方式。事务日志备份所需要的时间和空间比差异数据库备份更少，而且它可以支持事务的回滚操作；使用事务日志备份可以将数据库恢复到故障点时刻的状态。

文件备份 是指将数据库系统中的系统文件、数据文件和日志文件复制到另一个位置或存储介质，以保证数据库数据的安全性和可靠性。

备份方式：

冷备份

冷备份是指在数据库停止运行时进行备份操作，可以确保备份的数据完整性和一致性，但会中断数据库的正常服务。

热备份

热备份是指在数据库运行时进行备份操作，不会中断数据库的正常运行。热备份通常需要数据库支持相应的备份工具和机制。

数据库备份实现

备份实现方式：

执行SQL命令实现备份

操作GUI工具实现备份

实例操作：备份SAMPLE数据库到一个G磁盘的根目录文件Sample.bak中。

数据库恢复方法

1.通过备份文件进行恢复

实例操作：从存储备份文件中恢复SAMPLE数据库。

当使用备份数据库文件将数据库恢复到一个已知节点后,然后重新处理当时的所有工作。

本方法特点：

恢复技术简单，易于实现

对于多用户系统，难以接受备份周期内出现的数据丢失

2.利用事务日志按前滚或回滚方式进行数据恢复

前滚：

未完全提交的事务，即该事务已经被执行commit命令了，只是现在该事务修改所对应的脏数据块中只有一部分被写到磁盘上的数据文件中，还有一部分已经被置为提交标记的脏块还在内存上，如果此时数据库实例崩溃了，则当数据库实例恢复时，就需要用前滚（这个机制）来完成事务的完全提交，即将先前那部分已经被置为提交标记且还在内存上的脏块写入到磁盘上的数据文件中。

回滚：

未提交的事务，即该事务未被执行commit命令。但是此时，该事务修改的脏块中也有可能一部分脏块写入到数据文件中了。如果此时数据库实例崩溃了，则当数据库实例恢复时，就需要用回滚（这个机制）来将先前那部分已经写入到数据文件的脏块从数据文件上撤销掉。

5.6 项目实践

创建Role

赋予各个角色的数据库对象访问权限SQL程序

数据库用户创建

第六章 JDBC、ODBC、触发器、过程

触发器

触发器是特殊类型的存储过程，主要由操作事件(INSERT、UPDATE、DELETE) 触发而被自动执行。

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

触发器本身是一个特殊的事务单位。

存储过程

语法

（1）procedureName：存储过程名；

（2）OR REPLACE ：覆盖同名的存储过程；

（3）IN、OUT或INOUT参数模式。IN为输入参数；OUT为输出;参数缺省值是IN。

（4）Pname：形式参数的名字。

（5）dataType：该存储过程参数的数据类型。

补充语法

一些过程/函数里的操作（plpgsql语言）

PL/pgSQL提供了丰富的控制结构，如条件语句（IF、CASE）、循环语句（FOR、WHILE、LOOP）和异常处理（BEGIN EXCEPTION ... END）。

PL/pgSQL支持创建用户自定义函数和存储过程。

PL/pgSQL允许开发者创建触发器函数，用于在特定事件发生时自动执行。触发器函数可以用于实现数据完整性约束、审计日志等功能。

变量声明

userid INTEGER := 1;

firstname VARCHAR(20) := 'Tom';

Lastname VARCHAR(20) := 'Doe';

Payment NUMERIC(6,2) := 120.6;

特殊类型的声明

1.声明与指定表的行相同类型的变量，称为行类型变量（简称行类型）。常用于数据库查询的结果赋值给变量，定义语法格式为：

变量名表名%ROWTYPE;

例如：stuinfor student%ROWTYPE；

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

变量名 RECORD;

例如：data_rec RECORD;

3.PostgreSQL可使用已定义变量来定义新变量，其定义方法为：

变量1 变量2%TYPE;

例如：v_name s_name%TYPE;

控制语句

例子：

如果需要在函数或存储过程中调用数据库函数，语法形式如下：

select into 自定义变量 from 函数名（参数）；

游标

什么是游标

（1）游标（Cursor）是一种临时的数据库对象；

（2）用来存放从数据库表中查询返回的数据记录；

（3）提供了从结果集中提取并分别处理每一条记录的机制；

（4）游标总是与一条SQL查询语句相关联；

（5）游标包括：SQL语言的查询结果，指向特定记录的指针。

逐行处理数据：允许逐行遍历结果集，对每一行数据进行处理。

控制数据访问：随时可以获取下一行数据，而不需要一次性将整个结果集加载到内存中，减少内存消耗。

支持复杂逻辑：在遍历结果集的过程中进行条件安段、数据更新或数据插入等操作。

数据导航：能自由的在结果集中前进、后退或定位到特定位置。

存储过程和触发器：通常使用在存储过程和触发器中，以实现对数据的逐行处理和操作。

第一步：声明游标

（1）在存储过程中游标类型的变量。例如：游标变量 refcursor;

refcursor是关键字；

此时，游标变量还没有绑定查询语句，因此不能访问游标变量。

（2）使用游标专有的声明语法，如：

游标名 CURSOR [ ( arguments ) ] FOR query;

其中arguments为由逗号分隔的参数列表，用于打开游标时向游标传递参数，类似于存储过程或函数的形式参数；query是select数据查询语句，返回的值存储在游标变量中。

例如：curStudent CURSOR FOR SELECT * FROM student;

第二步：打开游标

未绑定（unbound_cursor）：

OPEN FOR:

其声明形式为：

OPEN unbound_cursor FOR query;

打开未绑定的游标变量，其query查询语句是返回记录的SELECT语句。

例如： OPEN curVars1 FOR SELECT * FROM student WHERE SID = mykey;

还有一种：

OPEN FOR EXECUTE

其声明形式为： OPEN unbound_cursor FOR EXECUTE query-string;

打开未绑定的游标变量。EXECUTE将动态执行查询字符串。例如：

绑定（bounded）：

打开一个绑定的游标，其声明形式为：

OPEN bound_cursor [ ( argument_values ) ];

仅适用于绑定的游标变量，只有当该变量在声明时包含接收参数，才能以传递参数的形式打开该游标，参数将传入到游标声明的查询语句中，例如：

OPEN curStudent;

OPEN curStudentOne(‘20160230302001’);

第三步：从游标获取数据

其声明形式为： FETCH cursor INTO target;

FETCH命令从游标中读取下一行记录的数据到目标中，读取成功与否，可通过PL/SQL内置系统变量FOUND来判断。

例如：

FETCH curVars1 INTO rowvar; --rowvar为行变量

FETCH curStudent INTO SID, Sname, sex;

请注意：游标的属性列必须与 目标列的数量一致，并且类型兼容。

第四步：关闭游标

CLOSE cursorName;

当游标数据不再需要时，需要关闭游标，以释放其占有的系统资源，主要是释放游标数据所占用的内存资源，cursorName是游标名。

例如： CLOSE curStudent;

需要注意：当游标被关闭后，如果需要再次读取游标的数据，需要重新使用open打开游标，这时游标重新查询返回新的结果。

ODBC

定义描述

ODBC定义了一套基于SQL的、公共的、与数据库无关的API（应用程序设计接口）；

使每个应用程序利用相同的源代码就可访问不同的数据库系统，存取多个数据库中的数据；

从而使得应用程序与数据库管理系统（DBMS）之间在逻辑上的独立性，使应用程序具有数据库无关性。

层次结构（重要）

还有，这里的DBMS：

①是用户向各数据库厂商购买的管理数据库的管理软件；

②是管理数据库的系统软件；

③用户可以执行SQL语句；

④创建数据库、表、视图等；

⑤对数据库进行权限管理。

JDBC

1.加载驱动

加载JDBC驱动是通过调用方法： Class.forName("驱动名字")

下面是PostgreSQL数据库驱动程序加载语句: Class.forName("org.postgresql.Driver")；

2.建立连接

与数据库建立连接的方法是调用DriverManager.getConnection(String url)方法。下面与PostgreSQL数据库建立连接的语句：

3.创建Statement对象

可以用Connection对象的方法createStatement()创建Statement。示例如下： Statement stmt = conn.createStatement();

4.执行SQL语句

创建了Statement对象 ,就可以向Statement对象发送SQL语句。主要掌握两种执行SQL语句的方法：executeQuery()、executeUpdate() executeQuery()：返回语句执行后的单个结果集，所以通常用于select语句 executeUpdate()：返回值是一个整数，指示受影响的行数（可以用于update、insert、delete语句）。

示例如下：

5.ResultSet保存结果集

ResultSet对象被称为结果集，它代表符合SQL语句条件的所有行，并且它通过一套getXXX方法提供了对这些行中数据的访问。 ResultSet里的数据一行一行排列，每行有多个字段，并且有一个记录指针，指针所指的数据行叫做当前数据行，我们只能来操作当前的数据行。我们如果想要取得下一条记录，就要使用ResultSet的next()方法 ,如果我们想要得到ResultSet里的所有记录，就应该使用while循环。示例代码如下：

6.关闭连接

作为一种好的编程风格，在不需要ResultSet对象、Statement对象和Connection对象时，应该显式地关闭它们。关闭这些对象的方法为： close() ; 示例： rs.close(); //关闭结果集对象 stmt.close(); //关闭执行对象 conn.close(); //关闭连接对象

补充

数据库存储结构（补充第五章）

数据库存储管理是数据库管理系统（DBMS）中的一个重要组成部分，主要负责管理数据库中数据的存储和组织。定义了数据在磁盘上的存储结构，包括及数据缓冲区、 数据库物理文件（数据文件、索引文件、日志文件）等。它确定了数据在物理存储介质上的组织方式，以便高效地存取数据

数据库存储结构可以分为逻辑存储结构和物理存储结构。

数据库逻辑存储结构是面向数据库编程用户的存储结构。 逻辑存储结构是指用户看到和使用的数据库对象的组织方式，包括表空间、段、页面、记录等逻辑结构。逻辑存储结构与数据库的物理存储无关

物理存储结构是指数据在磁盘上的实际存储方式（面向DBA用户的），包括数据文件、索引文件、日志文件等物理文件。

逻辑存储结构和物理存储结构之间通过数据库管理系统（DBMS）进行映射和管理，用户通过逻辑结构与数据库交互，而DBMS负责将逻辑结构转换为物理结构进行实际的数据存储和管理。合理设计和管理逻辑存储结构和物理存储结构可以提高数据库系统的性能和可靠性，确保数据的安全性和完整性。

数据文件组织

从数据库逻辑存储结构来看，存取访问数据库的基本单位是内存页。从数据库物理存储结构来看，存取数据文件的基本单位是操作系统文件块（os块）。他们都由若干数据记录组成

1.数据文件记录结构—定长记录

（1）当数据文件块的数据区大小不是定长记录大小的整数倍时，会出现一个定长记录在数据文件块中因空闲空间不够而存放不下的情况。

解决方案：只有在文件块中有足够空间时，才能存储到当前文件块内。若没有足够的空间，则需将该记录存储到另一空闲的文件块内。

（2）当数据文件中删除记录后，文件块中空闲出来的记录空间应允许后续插入的其他记录使用，其实现记录删除和插入的算法应高效。

解决方案：在数据文件中，形成一个被删除记录的自由链表。

2.数据文件记录结构—变长记录

数据文件的记录组织

数据文件的记录组织方式，即如何将大量记录组织为数据文件，以便DBMS对数据文件进行数据记录访问。

（1）堆文件组织

记录可以存储在任意的空间位置，磁盘上的存储记录是无序的，更新效率高，但是检索效率低。

（2）顺序文件组织

记录按照某个属性或者属性组值的顺序插入，磁盘上的存储记录是有序的，检索的效率可能高。

（3）多表聚簇文件组织

将若干相关联的表存储在同一个文件中，这样可以提高多表查寻的速度。

（4）Hash文件组织

将记录某个属性或者属性组值，依据散列函数计算出其应该存放的位置：桶号。检索的效率和更新的效率都有一定的提升

数据字典存储

在数据库系统中，存放数据库元数据的系统数据库表及其视图称为数据字典（Data Dictionary），也称为系统目录（System Catalog）。

（1）数据库表、视图的结构数据

（2）系统角色、用户数据

（3）索引数据

用户可通过系统视图对元数据进行查询处理。

存储引擎

在数据库系统中，数据库存储管理功能是由DBMS软件的存储引擎来实现的。数据库存储引擎也称存储管理器，它负责数据库中数据存储、数据检索和数据更新等处理。

数据库索引（补充第五章）

索引是一种数据记录定位结构，它将查询条件键值作为输入，能快速从索引结构 (如索引表) 获取该条件键值对应数据记录的位置指针，通过位置指针从关系表找出结果集记录

我们这里就只讲了B+树索引方式和散列表索引方式

B+树索引

查询

插入

删除

散列表索引

散列表（Hash Table），又称哈希表，是一种根据键值在散列表中获取数据记录指针的数据结构。

主要的散列函数方法：

直接寻址法

数字分析法

平方取中法

取随机数法

除留取余法

散列冲突的解决方法：

开放寻址法

再哈希法

公共溢出区

链地址法

SQL例子大全

建表

建触发器

建索引

建视图

更新插入删除数据

查询

过程

其中的补充语法

基本语句

在这一节和接下来的小节中，我们会描述PL/pgSQL能明确理解的所有语句类型。任何不被识别为这些语句类型之一的被假定为是一个 SQL 命令，并且会被发送给主数据库引擎执行，具体如第 40.5.2 节和第 40.5.3 节中所述。

http://www.postgres.cn/docs/9.5/plpgsql-statements.html

PostgreSQL plpgsql 存储过程、函数 - 状态、异常变量打印、异常捕获… - GET [STACKED] DIAGNOSTICS

背景使用GET STACKED DIAGNOSTICS捕获异常时的STACK内容。使用GET DIAGNOSTICS捕获运行过程中的状态值。GET DIAGNOSTICS捕获运行过程中的状态值There are several ways to determine the effect of a command. The first method is to use the GET DIAGNOSTICS command, which has the form:GET [ CURRENT ] DIAGNOSTICS variable { = | := } item [ , ... ]; GET DIAGNOSTICS integer_var = ROW_COUNT; Name Type Description ROW_COUNT bigint the number of rows processed by the most recent SQL command RESULT_OID oid the OID of the last row inserted by the most recent SQL command (only useful after an INSERT command into a table having OIDs) PG_CONTEXT text line(s) of text describing the current call stack (see Section 43.6.8) The GET DIAGNOSTICS command, previously described in Section 43.5.5, retrieves information about current execution state (whereas the GET STACKED DIAGNOSTICS command discussed above reports information about the execution state as of a previous error).例子CREATE OR REPLACE FUNCTION outer_func() RETURNS integer AS $$ BEGIN RETURN inner_func(); END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION inner_func() RETURNS integer AS $$ DECLARE stack text; BEGIN GET DIAGNOSTICS stack = PG_CONTEXT; RAISE NOTICE E'--- Call Stack ---\n%', stack; RETURN 1; END; $$ LANGUAGE plpgsql; SELECT outer_func(); NOTICE: --- Call Stack --- PL/pgSQL function inner_func() line 5 at GET DIAGNOSTICS PL/pgSQL function outer_func() line 3 at RETURN CONTEXT: PL/pgSQL function outer_func() line 3 at RETURN outer_func ------------ 1 (1 row) GET STACKED DIAGNOSTICS捕获异常时的STACK内容GET STACKED DIAGNOSTICS … PG_EXCEPTION_CONTEXT returns the same sort of stack trace, but describing the location at which an error was detected, rather than the current location. Name Type Description RETURNED_SQLSTATE text the SQLSTATE error code of the exception COLUMN_NAME text the name of the column related to exception CONSTRAINT_NAME text the name of the constraint related to exception PG_DATATYPE_NAME text the name of the data type related to exception MESSAGE_TEXT text the text of the exception’s primary message TABLE_NAME text the name of the table related to exception SCHEMA_NAME text the name of the schema related to exception PG_EXCEPTION_DETAIL text the text of the exception’s detail message, if any PG_EXCEPTION_HINT text the text of the exception’s hint message, if any PG_EXCEPTION_CONTEXT text line(s) of text describing the call stack at the time of the exception (see Section 43.6.8) 例子DECLARE text_var1 text; text_var2 text; text_var3 text; BEGIN -- some processing which might cause an exception ... EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT, text_var2 = PG_EXCEPTION_DETAIL, text_var3 = PG_EXCEPTION_HINT; END; 参考https://www.postgresql.org/docs/11/static/plpgsql-control-structures.html#PLPGSQL-CALL-STACKhttps://www.postgresql.org/docs/11/static/plpgsql-statements.html《Using “GET DIAGNOSTICS integer_var = ROW_COUNT;” capture rows effect by the last SQL》digoal’s 大量PostgreSQL文章入口

https://billtian.github.io/digoal.blog/2018/10/29/03.html

游标

🎉Liam’s blog🎉

-- 全新上线 ---

👏欢迎comment👏

⚠️由于浏览器缓存的原因，有些内容是更新了的但是需要手动刷新3次左右，页面才会显示更新内容

第一章 数据库及其概念