数据库原理及应用

1、数据库系统概述

1.1 基本概念

数据data

• 数据库中存储的基本对象，描述事物的符号记录

数据库DB

1. 长期存储在计算机内
2. 有组织的
3. 可共享的大量数据的集合

• 数据管理：数据查询、(数据更新)数据插入、数据删除、数据修改

数据库管理系统

• 位于用户和计算机操作系统之间的一层数据管理软件
• 主要功能：
  • 数据定义
  • 数据操作
  • 事务管理和运行管理
  • 数据存储和查询处理
  • 建立和维护功能

数据库系统DBS

• 采用数据库技术的计算机系统称为DBS
• 组成：数据库DB、数据库管理系统DBMS、数据库管理员DBA、应用程序
• 优点：
  • 数据整体结构化 – 数据库系统与文件系统的本质区别
  • 数据的共享性高、冗余度低、易扩充
  • 数据独立性高
    • 物理独立性：数据库数据的存储结构与存取方法与外部应用程序独立
    • 逻辑独立性：局部逻辑数据结构与全局逻辑数据结构之间的独立性
  • 数据统一由DBMS控制

1.2 数据模型

• 数据模型是一种形式机制，用于数据建模，描述数据、数据之间的联系、数据的语义、数据上的操作和数据的完整性约束条件

• 概念模型(信息模型)：实体-联系(E-R)模型是一种广泛使用的概念模型

• 数据模型的三要素

  • 数据结构：数据结构描述数据库中的对象和对象之间的联系，是对系统静态特性的描述
  • 数据操作：数据操作定义数据库中各种数据对象的实例上允许执行的操作和操作规则(查询和更新)，是对系统动态特性的描述
  • 数据完整性约束：数据的完整性约束条件是一组规则，用以限定符合数据模型的数据库状态和状态的变化，保证数据的正确、有效和相容

• 目前最流行的数据模型是关系模型

• 关系的每个属性必须取原子值

1.3 数据库系统的结构

• 模式：对全体数据逻辑结构和特征的描述，对型的描述，不涉及具体值
• 实例：模式的一个具体值

三级模式

• **外模式：**外模式也称子模式或用户模式。外模式介于模式与应用之间，是特定数据库用户的数据视图，是与某一具体应用相关的数据局部逻辑结构的描述，一个数据库有多个外模式
• 模式：模式也称逻辑模式。模式是数据库中全体数据的总体逻辑结构描述，是所有用户的公共数据视图，一个数据库只有一个模式
• **内模式：**内模式也称存储模式或物理模式。内模式是数据物理结构和存储方式的描述，定义数据在数据库内部的表示方式，一个数据库只有一个内模式

二级映像

• 外模式–模式映像

  • 保证数据的逻辑独立性

• 模式–内模式映像

  • 保证数据的物理独立性
  • 唯一的

扩展

• 数据库系统提供三种语言:DDL用于定义数据库模式，DML用于表达数据库的查询和更新，而 DCL用于定义用户对数据对象的访问权限。这三种语言不是独立的，而是集成在一起，形成完整的数据库语言。
• 数据管理员负责管理数据库，实施数据的集中控制。

2、实体 – 联系模型

2.1 基本概念

• 实体：客观存在并且可以相互区分的任何事物，可以是实际对象，也可以是抽象模型

• 实体集：具有相同属性的实体的集合

• 码：

  • 超码：能够唯一确定实体集中每个实体的属性集，(包含无关紧要属性的码)
  • 候选码：真子集都不是超码的极小超码**(”最小“超码)**
  • 主码：用来区分同一实体集中不同实体的候选码**(从候选码中选即可)**，(最多只能有一个的码)
  • 码：表示主码或候选码
    • 对于学生实体集中，{学号}和{学号，姓名}都是超码，{学号}是极小超码，因为它的真子集是空集，不是超码，如果不允许重名的话，{姓名}也是极小超码，{学号}，{姓名}都是候选码，可以选择{学号}作为主码

• 属性：

  • 简单属性：不能划分成更小部分的属性
  • 复合属性：可以划分成更小部分的属性(具有层次关系)
  • 单值属性：是特定的实体在该属性上只能取单个值的属性
  • 多值属性：是特定的实体在该属性上只能取多个值的属性(具有对等关系)
  • 基本属性：不能通过其他属性的值来推导出来（即它的值必须存储在系统中）的属性
  • 派生属性：派生属性的值可以从其他相关属性或实体计算得到，因此派生属性又称计算属性

• E-R图：

2.2 实体-联系图

• 联系是多个实体之间的相互联系

• 联系集是相同类型联系的集合

• 联系的类型

  • 一对一联系
  • 一对多(多对一)联系
  • 多对多联系

2.3 弱实体集

• 如果一个实体集的任何属性集都不足以形成该实体集的码，则称该实体集为弱实体集
• 存在码的实体集称为强实体集

• 标识实体集： 弱实体集必须与另一个称作标识实体集或属主实体集的强实体集相关联才有意义
• 标识性联系： 标识实体集与弱实体集相关联的联系称为标识性联系，表示法：双线菱形
• 分辨符： 如果一个属性可以惟一确定存在依赖于同一个强实体的弱实体，则称该属性集为弱实体集的分辨符，表示法：虚下划线
• 弱实体的码： 弱实体集的标识实体集的码和该弱实体集的分辨符共同形成弱实体集的码
• 部分码： 弱实体集的分辨符又称弱实体集的部分码
• 可以与多个标识实体集关联：有的弱实体集还可以与多个标识实体集关联，该弱实体集的码由诸标识实体集的码和弱实体集的分辨符的组合
• 另一种处理弱实体集的方法：把它作为其标识实体集的一个多值复合属性，例如可以把家属作为职工的一个多值属性
• 不好的设计: 方法将强实体集的码添加到弱实体集的属性集中，将弱实体集转化成强实体集

3、关系数据模型

3.1 关系数据库的结构

• 关系模型只包含单一的数据结构 – 关系

• 域：一个具有相同数据类型的值的集合 – 顺序的取值范围

• 基数：一个域允许的不同取值个数

• 笛卡尔积：域上面的一种集合运算 – 得到每个域中元素的所有可能的组合 – 基数相乘

• 关系：笛卡尔积的子集叫做其在域上的集合(可以用二维表和集合来表示关系)

• 除了语义约束外，对关系的限制主要有两点：

  • 第一，在关系数据库中，只考虑有限关系
  • 第二，关系的每个属性都必须是原子的

• 关系模式概念对应于程序设计语言中的类型概念，是对关系型的描述，可以表示为R(U, D, DOM, F) – 关系模式名(关系模式的诸属性，属性对应的域，U到D的映射，属性间的数据依赖集定义)

• 二维表表示关系时列的次序不重要

• 外码是另外一个表的主码

3.2 从ER模型到关系模型

• 属性处理

  • 派生属性的处理:转换时我们忽略派生属性
  • 复合属性的处理:忽略复合属性本身，而直接考虑它的成分属性
  • 多值属性的处理:为每个多值属性M创建一个关系Rm

• 实体集处理

  • 强实体集处理：实体集名可以作为关系名,实体集的全部属性构成关系的属性
  • 弱实体集处理：弱实体集名可以作为关系名,弱实体集存在依赖的标识实体集的主码和弱实体集的全部属性构成关系的属性

• 联系集处理

• 方法一：联系转换成关系模式

  • 联系是一对一的，则每个实体集的码都是关系的码
  • 联系是**一对多(多对一)的，则“多端”**实体集的码组合成关系的码
  • 联系是多对多的，则参与联系的所有实体集的码组合成关系的码

• 方法二：合并

  • 联系是一对一的，则可以和任意一端实体集转换得到的关系模式合并
  • 联系是一对多的，则和多端实体集转换得到的关系模式合并
  • 联系是多对多的，则该联系集转换成独立的关系模式

3.3 关系操作、完整性约束、关系代数

• 关系语言的分类 ：关系代数、关系演算、SQL语言

• 完整性分类：

  • 实体完整性：关系R的所有元组在主码上的值必须惟一，并且在主码的任何属性上都不能取空值
  • 参照完整性：如果属性集FK是关系R的外码(一个关系中的一个属性是另外一个关系中的主码则这个属性为外码)，它参照关系S的主码Ks ,则R的任何元组在FK上的值等于S的某个元组在主码Ks上的值，或者为空值，对外码取值限制
  • 用户自定义完整性：反映特定的数据库所涉及的数据必须满足的语义约束条件。由于不存在一般性规则，这些约束条件必须由用户根据实际问题的语义指定，对候选码取值限制

• 基本运算：选择(一元运算符)、投影(一元运算符)、并、差、笛卡尔积

• 附加运算：交、除、连接、自然连接（必须有同名属性才能进行）

4、关系数据库的设计理论

• 不好的关系模式：冗余、插入异常、更新异常、删除异常

4.1 函数依赖

• 数据依赖是语义概念,是关于关系诸属性值之间内在相关性的陈述,它规定了关系模式的合法关系实例所必须满足的条件

• 平凡的函数依赖(X包含Y)、非平凡的函数依赖

4.2 关系模式

• 1NF要求关系的所有属性值都是原子的，是对关系模式的基本要求，不满足1NF的数据模式不能称为关系模式，即所有关系模式都是1NF

• 2NF对于任意的非平凡函数依赖X→A∈F⁺ :A是主属性，X不是R的任意码的真子集

• 3NF对于任意的非平凡函数依赖X→A∈F⁺：A是主属性，X是R的码

• BCNF 限制R中成立的所有函数依赖要么是平凡的，要么是对码的依赖。对于任何关系模式R，以下两种两数依赖必定成立:平凡的函数依赖；任意属性对超码的依赖

• 主属性：一个属性只要在任何一个候选码中出现过，这个属性就是主属性。

• 非主属性：一个属性没有在任何候选码中出现过，这个属性就是非主属性。

• 部分函数依赖：设有关系R(A,B,C)，若AB→C，A→C，则C部分函数依赖于AB。

• 完全函数依赖：设有关系R(A,B,C)，若AB→C，A!→C，B!→C，则C完全函数依赖于AB。

• 传递函数依赖：设有关系R(A,B,C)，若A→B，B→C，则C传递函数依赖于A。

• 1NF：关系中的每个属性都不可再分。

• 2NF：在1NF的基础之上，消除了非主属性对于候选码的部分函数依赖。

• 3NF：在2NF的基础之上，消除了非主属性对于候选码的传递函数依赖。

• **BCNF：**在3NF的基础之上，消除主属性对于候选码的部分函数依赖与传递函数依赖。

4.3 在设计中使用规范化理论

• 规范化的总体目标是:消除某些冗余，避免存储异常，产生一种直观、易于扩充、可以很好描述现实世界，并且可以简单地验证某些语义约束的设计
• ER模型与规范化
• 完全规范化导致许多逻辑上相互分离的关系;涉及多个关系的查询需要求多个关系的自然连接，而自然连接是一种非常耗时的运算，从而会影响查询性能。解决方案:引入“冗余”的关系模式，即逆规范(denormalization)
• (操作时间冲突):逆规范化只对某些查询有利，而对另一些查询不利或对更新不利。解决方案:物化视图( materialized view ，对应SQL SERVER索引视图)

5、数据库设计

• 数据库设计步骤：需求分析 → 概念设计 → 逻辑结构设计 → 物理结构设计 → 数据库试运行和维护阶段

5.1 数据库设计概述

• 数据库设计就是根据用户应用需求和数据库的支撑环境（包括DBMS、操作系统和硬件)，设计出数据模式（包括外模式、模式和内模式)、建立数据库和典型的应用程序﹐使之能够有效地存储数据，满足各种用户的信息要求和处理要求。
• 数据库设计的成果有：数据模式，以数据库为基础的典型应用程序
• 数据库设计的目标是为用户和各种应用系统提供一个信息基础设施和高效的运行环境，即存取效率高、存储空间的利用率高，数据库系统运行管理效率高
• 数据库设计的特点：反复性、试探性、多阶段性、多技术性
• 设计之前的工作 – 可行性分析

5.2 需求分析

• 调查应用领域，对应用领域中各种应用的信息要求、处理要求、安全性和完整性要求进行详细分析，形成需求分析说明书
• 数据字典是需求分析阶段所取得的主要成果之一 – 为设计人员提供了关于数据详细描述的信息
• 数据项是数据的基本单元或最小单位
• 数据结构是若干数据项组成的有意义的集合，它反映了数据之间组合关系
• 数据流是数据在系统中的传输路径,它由一个处理传到另一个处理
• 处理过程也称加工过程，这里指数据库应用程序模块

5.3 概念结构设计

• 逐步扩张(滚雪球)和混合策略(自顶向下和自底向上混合)
• 数据抽象 – 将具有某些共同特性和行为的对象抽象为一个概念
• 分E-R图集成：合并(消除冲突 [属性冲突、命名冲突、结构冲突])和优化(消除冗余)
• 概念结构设计阶段的成果是全局ER图

5.4 逻辑结构设计

• 关系的分解：
  • 水平分解：把关系的元组分为若干子集，对每个子集定义一个子关系，以提高系统的效率
  • 垂直分解：把关系模式R的属性分解为若干子集合，形成多个子关系模式，从而将对应的关系也分解成多个子关系，优点是可以提高某些查询的效率
• 逻辑结构设计阶段的成果是关系模式，对关系模式进行优化，外模式设计在此阶段设计

5.5 物理结构设计

• 数据库在物理设备上的存储结构和存取方法就称为数据库的物理结构

• 数据库的物理结构设计∶就是为一个给定数据库的逻辑结构选取一个最适合应用环境的物理结构和存取方法的过程，目的是为了提高数据库的访问速度并有效地利用存储空间

• 常用索引方法 – 聚簇：为了提高某个属性(或属性组)的查询速度，把这个或这些属性(称为取簇码）上具有相同值的元组集中存放在同一个物理块或若干个相邻的物理块或柱面内

• 对已有关系建立聚簇，将导致关系中元组物理存储位置的变化，并使此关系上原有的索引无效，必须重建

• 确定数据库的物理结构主要指：

  • 确定数据的存放位置和存储结构，包括确定关系、索引、聚簇、日志、备份等存储安排和存储结构
  • 确定系统配置

• 确定数据的存放位置和存储结构要综合考虑

  • 存取时间

  • 存储空间利用率

  • 维护代价

• 为了提高系统性能，应该根据应用情况将数据的易变部分与稳定部分，经常存取部分和存取频率较低部分分开存放

• DBMS产品一般都提供了一些系统配置变量、存储分配参数、供设计人员和DBA对数据库进行物理优化

5.6 数据库的实施和维护

• 建立数据库模式
• 加载数据
• 试运行将检验数据库系统的设计是否达到设计目标、能否满足实际需要
• 确认系统能够满足实际需要之后﹐系统才能正式投入使用，进入漫长的运行维护阶段

6、 关系数据库标准语言

6.1 SQL概述

• SQL的数据定义语言(DDL)、SQL的数据操纵语言(DML)、SQL的数据控制语言(DCL)
• SQL的特点集多种数据库语言于一体、高度非过程化(大大减轻了用户负担/有利于提高数据的独立性)、面向集合的操作方式、一种语法两种使用方式、功能强大，语言简洁

6.2 SQL的数据类型

• CHAR(n):定长字符串，长度n由用户指定·省略n时，长度为1,CHAR的全称是CHARACTER
• VARCHAR(n):变长字符串，最大长度n由用户指定, VARCHAR的全称是CHARACTER VARYING
• BIT(n):定长二进位串，长度n由用户指定。省略n时，长度为1
• BIT VARYING(n):变长二进位串，最大长度n由用户指定
• INT:整数，其值域依赖于具体实现。INT的全称是INTEGER
• SMALLINT :小整数，其值域依赖于具体实现，但小于INT的值域
• DEC(p, d): p位有效数字的定点数，其中小数点右边占d位。DEC的全称是DECIMAL
• FLOAT(n):精度至少为n位数字的浮点数，其值域依赖于实现REAL:实数，精度依赖于实现
• DOUBLE PRECISION:双精度实数，精度依赖于实现，但精度比REAL高
• DATE:日期，包括年﹑月﹑日，格式为YYYY-MM-DD
• TIME:时间，包括时﹑分﹑秒,格式为HH:MM:SS ° TIME(n)可以表示比秒更小的单位，秒后取n位
• TIMESTAMP:时间戳，是DATE 和 TIME的结合
• INTERVAL:时间间隔·SQL允许对DATE 、TIME和INTERVAL类型的值进行计算
• SQL提供ETRACT(field FROM Var) , Var可以是DATE ·TIME 或TIMESTAMP数据类型的变
  量,ETRACT函数的功能是从 Var 中提取字段field

6.3 模式、表、索引的定义和删除

模式

• 目录(catlog) – 模式(schema) – 对象

• 符号说明：

  • < X >表示X是需要进一步定义或说明语言成分
  • [X]表示X可以缺省或出现一次
  • {}表示X可以出现一次
  • X |Y表示或者X出现，或者Y出现，但二者不能同时出现

• 模式的定义：

  • CREATE SCHEMA<模式名>[<模式元素>….]
  • CREATE SCHEMA[<模式名>]AUTHORIZATION<用户名> – 授权

• 模式删除：DROP SCHEMA<模式名> CASCADE|RESTRICT

  • CASCADE，则删除<模式名>指定模式得同时并删除该模式中的所有数据库对象(基本表﹑视图﹑断言等)
  • RESTRICT，则仅当<模式名>指定的模式不包含任何数据库对象时才删除指定的模式，否则拒绝删除

表

• 表的定义：CREATE TABLE<表名>

• 列级约束的定义：[CONSTRAINT<约束名>]<列约束>

  • NOT NULL
  • PRIMARY KEY
  • UNIQUE
  • CHECK<条件>

• 表级约束的定义：[CONSTRAINT<约束名>]<表约束>

  • PRIMARY
  • UNIQUE
  • CHECK
  • FOREIGN KEY

• 修改基本表：ALTER TABLE<表名>

  • [ADD[COLUMN]<列名><数据类型[列级约束定义]]
  • [[ALTER[COLUMN]<列名>{SET DEFAULT<缺省值>| DROP DEFAULT}]
  • [DROP [ COLUMN ]<列名>{CASCADE|RESTRICT}]
  • [[ADD<表约束定义>]
  • [DROP CONSTRAINT<约束名>{CASCADE|RESTRICTH]

• 删除基本表：DROP TABLE<表名>{CASCADE|RESTRICT}

索引

• CREATE [UNIQUE] [CLUSTER] INDEX<索引名>ON<表名>(<列名>[<次序>]{,<列名>[<次序>]})
  • <索引名>为建立的索引命名
  • <表名>是要建立索引的基本表的名字
  • 次序可以是 ASC (升序)和 DESC (降序)，缺省值为ASC
• UNIQUE表示该索引为惟一性索引UNIQUE缺省时，创建的索引为非唯一性索引；CLUSTER表示建立的索引是聚簇索引，缺省时为非聚簇索引；创建索引不仅创建索引结构，而且将索引的定义存储在数据字典中
• 索引删除：DROP INDEX<索引名>

6.4 数据操作

• SELECT

  • SELECT[ALL | DISTINCT]<选择序列>
  • FROM<表引用>,…,<表引用>
  • [WHERE<查询条件>]
  • [GROUP BY<分组列>{<分组列>}[HAVING<分组选择条件>]
  • [ORDER BY<排序列>[ASC | DESC]{,<排序列>[ASC lDESC}

• SELECT后可以使用集合量词ALL或DISTINCT，缺省时为ALL;ALL不删除结果的重复行，而DISTINCT将删除结果中的重复行

• BETWEEN表达式：<值表达式>[NOT] BETWEEN<下界>AND<上界>

• IN表达式：

  • <值表达式>[NOT]IN(<值表达式列表>)
  • <元组> |[NOT] IN<子查询>

• LIKE表达式：[NOT] LIKE<匹配串>[ESCAPE‘<换码字符>']

• NULL表达式：<值表达式><子查询>IS [ NOT ] NULL

• 排序：ORDER BY<排序列>[ASC | DESC] {,<排序列>[ASCDESC]}

• 聚集函数

• 分组语句group by：GROUP BY<分组列>{，<分组列>}[HAVING<分组选择条件>]

• 对于带GROUP BY子句的SELECT语句,SELECT子句中的结果列必须是GROUPBY子句中的<分组列>或聚集函数；分组语句细化了聚集函数作用的作用对象

• 连接查询、嵌套查询、子查询、集合查询

数据更新

• 插入：INSERRT INTO T[] VALUES() / INSERT INTO T[]
• 删除：DELETE FROM T [ WHERE<删除条件>]
• 修改：UPDATE T SET A1 = e1, …,Ak = ek[WHERE<修改条件>]

例：

1.在产品表(Products)中找出库存大于50的产品的所有信息，按产品编号升序排序。

SELECT * FROM Products WHERE UnitsInStock>=50 ORDER BY ProductID

2.查询顾客表(Customers)中所有不重复的所在城市，并升序排序。

SELECT DISTINCT City FROM Customers ORDER BY City

3.在顾客表(Customers)中找出所在城市为London、Madrid、Torino和Paris的顾客编号及电话。

SELECT CustomerID,Phone FROM Customers WHERE City IN ('London','Madrid','Torino','Paris')

4.在产品表(Products)中找出单位数量中有box的产品名和产品编号。

SELECT ProductName,ProductID FROM Products WHERE QuantityPerUnit LIKE '%box%'

5.在顾客表(Customers)中找出公司名的首字母为F，第5位为k的顾客编号和联系人名。

SELECT CustomerID,ContactName FROM Customers WHERE CompanyName LIKE 'F___k%'

6.统计在’1997-10-1’ 到’1997-10-7’期间，订单中每个员工的订单数，并按订单数降序排序。

SELECT EmployeeID"员工编号",COUNT(ORDERID)"订单数" FROM Orders WHERE OrderDate BETWEEN '1997-10-1'ANd'1997-10-7' GROUP　BY EmployeeID ORDER BY COUNT(OrderID) DESC

7.请查询平均价格在30元及以上的产品类型。

SELECT CategoryID,AVG(UnitPrice)'平均价格' FROM Products GROUP　BY CategoryID Having AVG(UnitPrice)>=30

8.查询’1997-10-7’当天所售商品的库存情况，包括商品编号、名称、库存。

SELECT Products.ProductID,ProductName,UnitsInStock FROM Products,Orders,[Order Details] WHERE Products.ProductID=[Order Details].ProductID AND [Order Details].OrderID=Orders.OrderID AND OrderDate='1997-10-7'

6.5 视图

• 定义：CREATE VIEW<视图名>[(<列名>，…,<列名>)]AS<查询表达式>[WITH CHECK OPTION]

• 删除：DROP VIEW<视图名>[ CASCADE |RESTRICT ]

• 视图的优点：

1. 视图能够简化用户的操作

2. 视图使用户能以多种角度看待同一数据

3. 视图重构数据库提供了一定程度的逻辑独立性

4. 视图能够对机密数据提供安全保护

5. 视图能够更清晰的表达

7、数据库的完整性

7.1 完整性概述

• 数据库的完整性是指数据库中数据是正确的(数据库的数据是符合语义约束的数据)、一致的(数据之间的逻辑关系是正确的)和相容的(同一个事实的两个数据应当是一致的)

• 约束的分类：(就被约束的数据对象而言)

  • 类型/域约束
  • 属性约束
  • 关系约束
  • 数据库约束

• 约束的分类：(从约束的状态的角度)

  • 静态约束（数据库正确状态）
  • 动态约束（数据库状态转移）

• DBMS必须提供完整性控制的机制有：定义完整性约束条件的方法、完整性检查机制、违约处理

• 可以实现级联操作的短语是：CASCADE

7.2 实体完整性

• 实体完整性要求:

  • 每个关系应该有一个主码，每个元组的主码值惟一确定该元组
  • 主码的任何属性都不能取空值

• 违反的操作： 插入新元祖、修改元祖的主码、DBMS自动检查、SQL支持实体完整性

7.3 参照完整性

• 参照完整性要求:

  • 参照关系R的任何元组在其外码FKR上的值或者等于被参照关系S的某个元组在主码K上的值，或者为空值

• 违法的操作：向参照关系R插入、修改参照关系R外码上的值、删除(修改)被参照关系S的元组

• 向数据库表中插入数据违法参照完整性规则时，系统所做的操作是：拒绝

7.4 用户自定义完整性

• 用户自定义的完整性约束：用户定义的完整性即是针对某个特定关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求，用户定义的完整性可以是：

  • 属性级
  • 关系级
  • 数据库级

• 域约束：每个属性都必须在一个值域上取值

• 属性约束：属性的取值必须来自其定义的值域

• 关系约束：说明关系的合法取值，常常涉及同一关系的多个属性和或多个元祖

• 断言与数据库约束：断言是一种命名约束，表达了数据库状态必须满足的逻辑条件

7.5 触发器

• 触发器( trigger）是特殊类型的存储过程，当某个事件发生时它自动执行
• 按触发事件分：DELETE触发器、INSERT触发器、UPDATE触发器
• 定义触发器操作的SQL语句：CREATE TRIGGER

CREATE TRIGGER `register` AFTER UPDATE ON `student` FOR EACH ROW BEGIN
 UPDATE driver_school SET total_number = (total_number + 1) WHERE driver_school.id = student.school_id;
END

8、数据库编程与访问技术

8.1 T-SQL

• 变量：局部变量–变量名前加一个@，全局变量–变量名前加两个@@
• 变量的声明与赋值：<@var>
• 运算符：算术运算符、比较运算符、逻辑运算符、位运算符、字符串连接运算符–‘+’
• 函数
• 流程控制语句

8.2 存储过程

• 存储过程是为了完成特定功能汇集而成的命名了的一组SQL语句集合
  • 编译、优化后存储，可多次执行
• 优点：将业务操作封装、实现一定程度的安全性保护、适合复杂的统计和查询操作

CREATE PROCEDURE <procedureName> --<procedureName>存储过程名称
	[(<@parameterName><datatype>[=<defaultValue>][OUTPUT]--<@parameterName> 参数名
   	[,<@parameterName><datatype>[=<defaultValue>][OUTPUT]])]--OUTPUT输出参数
As
	<SQL-Statements>

8.3 数据库访问技术

• ODBC：一组对数据库访问的标准API
• DAO与RDO
• JDBC访问步骤：
  • 导入包
  • 加载JDBC驱动程序
  • 打开一个数据库连接
  • 执行一个查询
  • 从结果集中提取数据
  • 清理环境

9、数据库的安全性

9.1 概述

• 数据库的安全性是指保护数据库，防止因用户非法使用数据库造成数据泄露、更改或破坏
• 恶意访问的形式：未经授权读取、修改、删除数据
• 安全性保护手段：用户标识与鉴别、强制存取控制、审计和加密、自主存取和控制、视图

9.2 用户识别与标识

• 用户标识与鉴别是系统提供的最外层安全保护措施

• 其基本方法是:系统提供一定的方式让用户标识自己的名字或身份;系统内部记录着所有合法用户的标识;每次用户要求进入系统(与数据库连接)时，由系统核对用户提供的身份标识;通过鉴别的合法用户才能进入系统，建立数据库连接

9.3 存取控制

• 存取控制是数据库系统的主要安全措施：自主存取控制，强制存取控制

9.4 自主存取控制

• 存取控制的任务：
  • 授权（DCL中提供了相应的授权语句，允许用户自主地定义存取权限，并将用户的授权登记在数据字典中）
  • 合法权限检查（当用户发出存取数据库的操作请求后，DBMS将查找数据字典，根据用户权限进行合法权限的检查;如果用户的操作请求超出了自身的权限，系统将拒绝执行此操作2）
• 权限的授予与回收：
  • GRANT<权限列表>ON<对象名>TO<用户/角色列表>[WITH GTANT OPTION]
  • REVOKE<权限列表>ON<对象名>FROM<用户/角色列表>{CASCADE|RESTRICT}

9.5 强制存取控制

• 自主存取控制不能阻止副本的非授权传播

• 强制存取控制( Mandatory Access Control ，简称MAC）是系统为保证更高程度的安全性所采取的强制存取检查手段

• 在MAC中,DBMS所管理的全部实体被分为主体和客体两大类：

  • 主体是系统中的活动实体，可以是DBMS管理的实际用户或代表用户的各个进程
  • 客体是系统中的被动实体，是受主体操纵的对象·如文件、基本表、索引、视图等

• 敏感度标记：绝密、机密、秘密、公开

  • 主体的敏感度标记称为许可证级别（ Clearance Level)
  • 客体的敏感度标记称为密级（ Classification Level )

• 仅当主体的许可证级别大于或等于客体的密级时，该主体才能读取相应的客体；仅当主体的许可证级别小于或等于客体的密级时，该主体才能写相应的客体

9.6 视图机制

• 视图是定义外模式,为用户提供个性化数据库模型的一种手段
• 利用视图实现安全保护的基本思想是:
  • 首先通过定义视图，屏蔽掉一部分需要对某些用户保密的数据
  • 然后，在视图上定义存取权限，将对视图的访问权授予这些用户，而不允许他们直接访问定义视图的关系(基本表)

9.7 其它措施

• 审计启用一个专门的审计日志（ Audit Log），自动记录所有用户对数据库的更新操作(插入、删除和修改)
• 数据加密的基本思想:按照一定的加密算法，将原始数据(明文)变换成不可直接识别的格式(密文)，使得不知道解密方法的人即使获得数据，也不知道数据的真实内容，从而达到保护数据的目的

10、事务于并发控制

10.1 事务与并发控制

事务

• 事务是用户定义的一个数据库的操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单元
• 事务是并发控制与调度的基本单位，也是数据库恢复的基本单位
• 事务的特点：(ACID)原子性、一致性、隔离性、持久性
• 事务状态
  • 活动状态：初始状态，事务执行时处于该状态
  • 部分提交状态：最后一条语句被执行后的状态
  • 失败状态：正常的执行不能持续后的状态
  • 中止状态：事务回滚并且数据库已被恢复到事务开始执行前的状态
    • 重启事务
    • 废除事务
  • 提交状态：成功完成后的状态

• SQL对事务的支持：COMMIT WORK–提交当前事务；ROLLBACK WORD–中止当前事务；SET TRANSACTION–设置事务的特性
• 这里<级别>从低到高依次为
  • READ UNCOMMITED读未提交的修改，只能在只读状态下使用
  • READ COMMITED读提交的修改
  • REPEATABLE READ可重复读
  • SERIALIZABLE可串行化
• 隔离级别越高(低)，可能出现的问题越少(多)，系统并发度越低（高)

并发控制

• 为了防止数据库的不一致性，对多个运行的事务需要并发控制
• 保证事务的一致性、隔离性
• 相对于串行执行，并发执行优点有：提高吞吐量和资源利用率、减少等待时间；缺点有：丢失修改、读脏数据、不可重复读

10.2 封锁

• 并发控制的主要技术有：封锁、时间戳、乐观控制法、多版本并发控制
• 所谓封锁就是事务T在对某个数据对象操作之前，先向系统发出加锁请求，加锁后事务T就对该数据对象有了一定的控制权，在事务T释放它的锁之前，其它事务不能更新该数据对象
• 基本锁类型：共享锁S(读锁，可读不可写)和排他锁X(写锁，可读可写)

• 封锁协议：

  • 一级封锁协议 防止丢失修改
  • 二级封锁协议 防止读“脏”数据
  • 三级封锁协议 进一步保证可重复读

• 活锁与死锁：活锁又称饥饿，是某个事务因等待锁而处于无限期等待状态(解决–先来先服务)；死锁是两个或两个以上的事务之间的循环等待现象(解决–一次封锁法、顺序封锁法)

• 死锁的检测与解除：超时法和等待图法

• 解除死锁：选择一个或多个处于死锁状态的事务，将其撤销并释放这些事务持有的所有的锁，从而打破了循环等待条件，解除死锁，使得其他事务能够继续运行。当然,被撤消的事务对数据库的更新必须恢复(回滚)，并且要在稍后需要重新运行

10.3 并发调度的可串行化

• 并发调度正确性准则：一组事务的一个并发调度S是正确的，当且仅当调度S的执行结果与某一个串行调度的执行结果相同。此时，我们称并发调度S是可串行化的

10.4 两阶段协议

• 两段锁协议：是最常用的一种封锁协议、理论上已经证明使用该协议可保证并发调度串行化
• 为了避免级联回滚，产生了两种改进的两阶段封锁协议：严格两阶段封锁协议、强两阶段封锁协议

10.5 多粒度封锁

• 系统能够同时支持多种封锁粒度供不同的事务选择的封锁方法称之为多粒度封锁
• 选择封锁粒度时应该同时考虑封锁开销和并发度两个因素：
  • 封锁的粒度越大，数据库所能封锁的数据单元就越少，并发度就越小，系统开销就越小
  • 封锁的粒度越小，并发度就高，系统开销就越大

11、数据库的恢复技术

1.1 概述

• 数据库的恢复机制就是负责将数据库恢复到故障发生前的某一个一致状态，并将崩溃后的数据库不能使用的时间减少到最小
• 故障的类型：
  • 事务故障：事务程序的逻辑错误，系统错误
  • 系统故障：
  • 介质故障：磁盘损坏导致的故障
• 故障恢复技术的基本思想：在系统正常时建立冗余数据（通过登记日志、数据转储），保证有足够的信息可用于故障恢复；故障发生后采取措施将数据库内容恢复到某个一致性状态，保证事务原子性和持久性

1.2 存储器结构

• 存储器类型：

  • 易失性存储器：内存、高速缓存
  • 非易失性存储器：磁盘、磁带
  • 稳定存储器：理想的存储器

• 采用冗余独立磁盘阵列可以保证单个磁盘的故障不会导致数据丢失

• 数据访问通过input和output将块在主存和磁盘之间移动

1.3 恢复技术

基于日志的恢复技术

• 日志是日志记录的序列，记录了数据库中所有的更新活动
• 日志记录的格式：事务标识符、操作类型、操作对象、旧值、新值
• 日志登记的原则：日志记录必须严格按并发事务执行的时间次序登记；必须先记日志，后写数据库
• redo(正序)和undo(逆序)操作
• 延迟更新指的是将事务对数据库的更新推迟到事务提交之后
• 即时更新技术允许事务在活跃状态时就将更新输出到数据库中
• 系统故障发生，哪些事务需要重做，事务需要回滚
  • 什么时候重做——系统故障发生之前，事务A已经提交，事务A需要重做
  • 什么时候回滚——系统故障发生时，事务B已经开始，但未提交，而且没有回滚
  • 什么时候不做处理——系统发生故障前，已经回滚
  • （PS:注意检查点隔断问题！！！)

基于检查点的恢复技术

• 提高系统故障恢复效率的基本方法是使用检查点技术

• 在建立检查点时，不允许事务执行任何更新动作，如写缓冲块或写日志记录；建立检查点之后，所有在检查点前发生的更新都已经输出到数据库中，尚未完成的事务都登记在检查点记录中；这样，发生系统故障时，只需要从最近的检查点记录开始扫描日志

• 对于事务故障用处不大，对于系统故障能提高效率

缓冲技术

• 日志缓冲：一个日志记录通常远小于稳定存储器的块。为了提高I/O效率，日志记录在主存中被缓冲，而不是直接输出到稳定存储器；当缓冲区被日志记录装满，或者执行日志强制输出时，日志记录才被输出到稳定存储器；日志缓冲减少了将日志输出到稳定存储器的开销，但是也带来了风险:一旦发生系统发生故障，缓冲区中的日志记录将丢失
• 数据库缓冲：数据库存储在非易失性存储器中，在需要时再将相应的数据块调入主存，存在闩锁

转储

• 转储是指将整个或部分数据库复制到磁带或另一个磁盘上，产生数据库后备副本的过程；后备副本可以脱机保存，供介质故障恢复时使用（因此，转储又称归档)；一旦数据库遭到破坏，就可以将后备副本重新装入，将数据库恢复到转储时的状态，分为静态转储/动态转储或者海量转储/增量转储

其他恢复技术

• 影子分页技术
• 数据库镜像