导图社区《数据库系统概论（第6版）》王珊著

《数据库系统概论（第6版）》王珊著

本作品适合学习王珊《数据库系统概论》的同学。这份资料的整体结构严格参考B站索尔以及木子老师的精品课程。内容优化：复杂图例经万兴图示重绘，部分不合理的插图也进行了优化，针对课本里复杂难懂、线条杂乱的图例，全部用万兴图示重新精心绘制，优化后的图例清晰直观、层次分明，能快速看懂各类模型、结构和流程。付费说明：绘制耗费大量精力。助力大家攻克数据库学习难关。第 6 章特增“大学选课系统”案例，直观解析范式演进，附带数据变化 Excel 及 SQL 脚本。最后，知识付费不易，感谢您的支持！

编辑于2026-03-20 09:28:20

数据库设计
数据库系统概论
E-R图
三级模式结构
模拟案例

HackerDL

《数据库系统概论（第6版）》王珊著

社区模板帮助中心，点此进入>>

HackerDL

相似推荐
大纲

开学典礼：时间规划
- 3.9k
- 154
- 79
- 12
- 0
Jason
大学生职业规划思维导图
- 6.0k
- 50
- 39
- 11
- 0
吃太阳的猫????
大学生职业发展规划
- 4.0k
- 158
- 61
- 8
- 0
远走高飞
大学生职业规划知识总结
- 3.7k
- 9
- 60
- 9
- 0
雪茉
大学生毕业去向
- 3.1k
- 14
- 42
- 6
- 0
美少女
大学生行为礼仪规范
- 2.5k
- 53
- 50
- 10
- 0
呱小呱
全国大学一览图
- 3.1k
- 14
- 146
- 23
- 0
17316931273
大一新生入学必备物品清单
- 2.4k
- 7
- 68
- 14
- 0
北冥有鱼
蓓蕾入学
- 1.1k
- 3
- 0
- 0
- 0
金兰＇s
大学生创业基础
- 6.8k
- 112
- 100
- 20
- 0
罙

绪论

数据库系统概述

数据库的地位

数据库技术产生于二十世纪六十年代末，是数据管理的有效技术，是计算机科学的重要分支。

数据库技术是信息系统的核心和基础，它的出现极大地促进了计算机应用向各行各业的渗透。

数据库已经成为每个人生活中不可缺少的部分。

数据库的四个基本概念

数据（Data）

数据是数据库中存储的基本对象

数据的定义

描述事物的符号记录

数据的种类

数字、文字、图形、图像、音频、视频、学生的档案记录等

数据的含义

数据的含义称为数据的语义，数据与其语义是不可分的

举例1

93是一个数据

语义1：学生某门课的成绩

语义2：某人的体重

语义3：计算机系2013级学生人数

语义4：请同学给出 ……

举例2

学生档案中的学生记录（李明，男，199505，江苏南京市，计算机系，2013)

语义

学生姓名、性别、出生年月、出生地、所在院系、入学时间

解释

李明是个大学生，1995年5月出生，江苏南京市人， 2013年考入计算机系

数据库（DataBase）

数据库的定义

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

数据库的基本特征

数据按一定的数据模型组织、描述和储存

可为各种用户共享

冗余度较小

数据独立性较高

易扩展

数据库管理系统（DBMS）

数据库管理系统的定义

位于用户与操作系统之间的一层数据管理软件

是基础软件，是一个大型复杂的软件系统

数据库管理系统的用途

科学地组织和存储数据、高效地获取和维护数据

数据库在计算机系统中的位置

数据库管理系统的主要功能

数据定义功能

提供数据定义语言 (DDL, Data Definition Language）

定义数据库中的数据对象

数据组织、存储和管理

分类组织、存储和管理各种数据

确定组织数据的文件结构和存取方式

实现数据之间的联系

提供多种存取方法提高存取效率

数据操纵功能

提供数据操纵语言（DML, Data Manipulation Language）

实现对数据库的基本操作（查询、插入、删除和修改）

数据库的事务管理和运行管理

数据库在建立、运行和维护时由数据库管理系统统一管理和控制

保证数据的安全性、完整性、多用户对数据的并发使用

发生故障后的系统恢复

数据库的建立和维护功能

数据库的重组织

性能监视、分析等

其它功能

数据库管理系统与网络中其它软件系统的通信

数据库管理系统系统之间的数据转换

异构数据库之间的互访和互操作

数据库不能是独立的，它应该可以和别的软件交流

数据库系统（DBS）

数据库系统的构成

数据库

数据库管理系统（及其应用开发工具）

应用程序

数据库管理员

数据库系统层次结构与管理关系图

数据管理技术的产生和发展

什么是数据管理

对数据进行分类、组织、编码、存储、检索和维护

数据处理的中心问题

数据管理技术的发展过程

人工管理阶段（20世纪50年代中之前）

文件系统阶段（20世纪50年代末~60年代中）

数据库系统阶段（20世纪60年代末~现在）

数据管理技术的发展动力

应用需求的推动

计算机硬件的发展

计算机软件的发展

数据库系统的特点

数据结构化

数据的整体结构化是数据库的主要特征之一

整体结构化

不再仅仅针对某一个应用，而是面向全组织

不仅数据内部结构化，整体是结构化的，数据之间具有联系

数据记录可以变长

数据的最小存取单位是数据项

数据的共享性高，冗余度低且易扩充

数据面向整个系统，可以被多个用户、多个应用共享使用。

数据共享的好处

减少数据冗余，节约存储空间

避免数据之间的不相容性与不一致性

使系统易于扩充

数据独立性高

物理独立性

指用户的应用程序与数据库中数据的物理存储是相互独立的。

当数据的物理存储改变了，应用程序不用改变。

逻辑独立性

指用户的应用程序与数据库的逻辑结构是相互独立的。数据的逻辑结构改变了，应用程序不用改变。

数据独立性由数据库管理系统的二级映像功能来保证。

数据由数据库管理系统统一管理和控制

数据由数据管理系统统一管理和控制

数据库管理系统提供的数据控制功能

（1）数据的安全性（Security）保护

保护数据以防止不合法的使用造成的数据的泄密和破坏

（2）数据的完整性（Integrity）检查

保证数据的正确性、有效性和相容性

（3）并发（Concurrency）控制

对多用户的并发操作加以控制和协调，防止相互干扰而得到错误的结果

（4）数据库恢复 (Recovery)

将数据库从错误状态恢复到某一已知的正确状态

小结

数据库是长期存储在计算机内有组织的大量的共享的数据集合

可以供各种用户共享，具有最小冗余度和较高的数据独立性

数据库管理系统在数据库建立、运用和维护时对数据库进行统一控制，以保证数据的完整性、安全性，并在多用户同时使用数据库时进行并发控制，在发生故障后对数据库进行恢复

数据模型

综述

数据模型是对现实世界数据特征的抽象，通俗地讲数据模型就是现实世界的模拟

数据模型应满足三方面要求

能比较真实地模拟现实世界

容易为人所理解

便于在计算机上实现

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

两类数据模型

数据模型分为两类（两个不同的层次）

（1）概念模型

也称信息模型

它是按用户的观点来对数据和信息建模

用于数据库设计

（2）逻辑模型和物理模型

逻辑模型主要包括网状模型、层次模型、关系模型、面向对象数据模型、对象关系数据模型、半结构化数据模型等。按计算机系统的观点对数据建模，用于DBMS实现

物理模型是对数据最底层的抽象，描述数据在系统内部的表示方式和存取方法，在磁盘或磁带上的存储方式和存取方法

客观对象的抽象过程——两步抽象

现实世界中的客观对象抽象为概念模型

将现实世界抽象为信息世界

把概念模型转换为某一数据库管理系统支持的数据模型

将信息世界转换为机器世界

现实世界中客观对象的抽象过程

概念模型

信息世界中的基本概念

（1）实体（Entity）

客观存在并可相互区别的事物称为实体

可以是具体的人、事、物或抽象的概念

（2）属性（Attribute）

实体所具有的某一特性称为属性

一个实体可以由若干个属性来刻画

（3）码（Key）

唯一标识实体的属性集称为码

（4）实体型（Entity Type）

用实体名及其属性名集合来抽象和刻画同类实体称为实体型

（5）实体集（Entity Set）

同一类型实体的集合称为实体集

（6）联系（Relationship）

现实世界中事物内部以及事物之间的联系在信息世界中反映为实体（型）内部的联系和实体（型）之间的联系

子主题

实体内部的联系通常是指组成实体的各属性之间的联系

实体之间的联系通常是指不同实体集之间的联系

实体之间的联系有一对一、一对多和多对多等多种类型

概念模型的一种表示方法——实体-联系方法【Entity-Relationship Approach】

用E-R图来描述现实世界的概念模型

E-R方法也称为E-R模型

概念模型的用途

概念模型用于信息世界的建模

是现实世界到机器世界的一个中间层次

是数据库设计的有力工具

数据库设计人员和用户之间进行交流的语言

对概念模型的基本要求

较强的语义表达能力

简单、清晰、易于用户理解

数据模型的组成要素

数据结构

描述数据库的静态特性

包括：数据库的组成对象、对象的类型/内容/性质，以及对象之间的联系

是刻画数据模型性质最重要的方面，决定了数据模型的类型（如关系、层次模型等）

数据操作

描述数据库的动态特性

数据操作的定义

对数据库中各种对象（型）的实例（值）允许执行的操作的集合，包括操作及有关的操作规则

数据操作的类型

查询

更新（包括插入、删除、修改）

数据模型对操作的定义

操作符号

操作规则（如优先级）

实现操作的语言

数据的完整性约束条件

一组完整性规则的集合

完整性规则

给定的数据模型中数据及其联系所具有的制约和依存规则

用以限定符合数据模型的数据库状态以及状态的变化，以保证数据的正确、有效和相容

数据模型对完整性约束条件的定义

反映和规定必须遵守的基本的通用的完整性约束条件

提供定义完整性约束条件的机制，以反映具体应用所涉及的数据必须遵守的特定的语义约束条件

常用的数据模型

层次模型（Hierarchical Model）

层次模型是数据库系统中最早出现的数据模型

层次数据库系统的典型代表是IBM公司的IMS（Information Management System）数据库管理系统

层次模型用树形结构来表示各类实体以及实体间的联系

层次模型的数据结构

满足下面两个条件的基本层次联系的集合为层次模型

有且只有一个结点没有双亲结点，这个结点称为根结点

根以外的其它结点有且只有一个双亲结点

层次模型中的几个术语

根结点

双亲结点

兄弟结点

叶结点

概要

层次模型的特点

结点的双亲是唯一的

只能直接处理一对多的实体联系

每个记录类型可以定义一个排序字段，也称为码字段

任何记录值只有按其路径查看时，才能显出它的全部意义

没有一个子女记录值能够脱离双亲记录值而独立存在

教员学生层次数据库模型

“教员-学生”层次数据库模型

“教员-学生”层次数据库的一个值

层次模型的数据操纵与完整性约束

层次模型的数据操纵

查询

插入

删除

更新

无相应的双亲结点值就不能插入子女结点值

如果删除双亲结点值，则相应的子女结点值也被同时删除

更新操作时，应更新所有相应记录，以保证数据的一致性

层次模型的优缺点

优点

层次模型的数据结构比较简单清晰

查询效率高，性能优于关系模型，不低于网状模型

层次数据模型提供了良好的完整性支持

缺点

结点之间的多对多联系表示不自然

对插入和删除操作的限制多，应用程序的编写比较复杂

层次命令趋于程序化

网状模型（Network Model）

网状数据库系统采用网状模型作为数据的组织方式

典型代表是DBTG系统

网状模型的数据结构

满足下面两个条件的基本层次联系的集合

允许一个以上的结点无双亲

一个结点可以有多于一个的双亲

表示方法（与层次数据模型相同）

实体型

用记录类型描述

每个结点表示一个记录类型(实体)

属性

用字段描述

每个记录类型可包含若干个字段

联系

用结点之间的连线表示记录类型（实体）之间的一对多的父子联系

网状模型与层次模型的区别

网状模型允许多个结点没有双亲结点

网状模型允许结点有多个双亲结点

网状模型允许两个结点之间有多种联系（复合联系）

网状模型可以更直接地描述现实世界

层次模型实际上是网状模型的一个特例

网状模型中子女结点与双亲结点的联系可以不唯一要为每个联系命名，并指出与该联系有关的双亲记录和子女记录

多对多联系在网状模型中的表示

用网状模型间接表示多对多联系

方法

将多对多联系直接分解成一对多联系

学生选课数据库模型

“学生选课”数据库模型

网状模型的操纵与完整性约束

网状数据库系统（如DBTG）对数据操纵加了一些限制，提供了一定的完整性约束

码：唯一标识记录的数据项的集合

一个联系中双亲记录与子女记录之间是一对多联系

支持双亲记录和子女记录之间某些约束条件

网状模型的优缺点

优点

能够更为直接地描述现实世界，如一个结点可以有多个双亲

具有良好的性能，存取效率较高

缺点

结构比较复杂，而且随着应用环境的扩大，数据库的结构就变得越来越复杂，不利于最终用户掌握DDL、DML语言复杂，用户不容易使用

记录之间联系是通过存取路径实现的，用户必须了解系统结构的细节

关系模型（Relational Model）

关系数据库系统采用关系模型作为数据的组织方式

1970年美国IBM公司San Jose研究室的研究员E.F.Codd首次提出了数据库系统的关系模型

计算机厂商新推出的数据库管理系统几乎都支持关系模型

关系模型的数据结构

在用户观点下，关系模型中数据的逻辑结构是一张二维表，它由行和列组成

专用名称

关系（Relation）

一个关系对应通常说的一张表

元组（Tuple）

表中的一行即为一个元组

属性（Attribute）

表中的一列即为一个属性，给每一个属性起一个名称即属性名

主码（Key）

也称码键。表中的某个属性组，它可以唯一确定一个元组

域（Domain）

是一组具有相同数据类型的值的集合。属性的取值范围来自某个域

分量

元组中的一个属性值。

关系模式：对关系的描述

关系名 (属性1, 属性2, ……, 属性n)

学生 (学号, 姓名, 年龄, 性别, 系名, 年级)

关系必须是规范化的，满足一定的规范条件

最基本的规范条件

关系的每一个分量必须是一个不可分的数据项，不允许表中还有表

术语对比

关系模型的操纵与完整性约束

数据操作是集合操作，操作对象和操作结果都是关系

查询

插入

删除

更新

存取路径对用户隐蔽

用户只要指出“干什么”，不必详细说明“怎么干”

关系的完整性约束条件

实体完整性

参照完整性

用户定义的完整性

关系模型的优缺点

优点

建立在严格的数学概念的基础上

概念单一

实体和各类联系都用关系来表示

对数据的检索结果也是关系

关系模型的存取路径对用户透明

具有更高的数据独立性，更好的安全保密性

简化了程序员的工作和数据库开发建立的工作

缺点

存取路径对用户透明，查询效率往往不如格式化数据模型

为提高性能，必须对用户的查询请求进行优化，增加了开发数据库管理系统的难度

面向对象数据模型（Object Oriented Data Model）

对象关系数据模型（Object Relational Data Model）

半结构化数据模型（Semistruture Data Model）

数据库系统的结构

三级模式结构

模式

它是数据库中全体（全局）数据的逻辑结构和特征的描述，是现实世界某应用环境（企业、单位）的所有信息内容集合的表示。也是所有用户的公共数据视图

它是数括库系统模式结构的中间层

不涉及数据的物理存储细节和硬件环境

且与具体的应用程序、所使用的应用开发工具及高级程序设计语言无关

外模式

外模式也称为子模式（subschema）或用户模式

它是模式的子集

它是数据库用户（包括应用程序员和最终用户）能够着见和使用的局部数据的逻辑结构和持征的描述

内模式

内模式也称为存储模式或物理模式

它是对数据物理结构和存储方式的描述，是数据在数据库内部的组织方式

两级映像

外模式/模式映像

将外界用户的外模式，翻译为全局模式

作用

当模式改变时，由数据库管理员对各个外模式/模式映像做相对应的改变，可以使外模式保持不变

应用程序是依据数据的外模式编写的，从而应用程序可以不用修改，保证了数据的逻辑独立性

内模式/模式映像

它定义了数据全局逻辑结构与存储结构之间的对应关系

作用

当数据库的存储结构改变时，如选用的另一种存储结构，由数据库管理员对模式内模式映像做相应的改变，可以保证模式保持不变

因而应用程序不必改变，保证了数据的物理独立性

数据库系统的三级模式、两级映像结构

数据库系统的组成

数据库系统一般由数据库、管理系统（及其应用开发工具）、应用系统和数据库管理员组成

硬件平台

软件平台

人员

关系数据库

关系数据库结构及形式化定义

关系

单一的数据结构——关系

现实世界的实体以及实体间的各种联系，均用关系来表示

逻辑结构——二维表

从用户角度，关系模型中数据的逻辑结构是一张二维表

建立在集合代数的基础上

重要概念

域（Domain）

域是一组具有相同数据类型的值的集合

笛卡尔积（Cartesian Product）

笛卡尔积的计算

给定一组域 D1, D2, ……, Dn。其中，允许其中某些域是相同的。

那么，D1, D2, ……, Dn的笛卡尔积为： D1×D2×…×Dn = {(d1, d2, ……, dn) | di∈Di, i=1, 2, ……, n}

重要概念

元组（tuple）

笛卡尔积中每一个元素(d1, d2, ……, dn)叫作一个n元组（n-tuple）或简称元组

分量（component）

笛卡尔积元素(d1, d2, ……, dn)中的每一个值di，叫作一个分量

笛卡尔积可以认为是所有域中，所有取值的一个组合，且不能重复

关系（Relation）

（1）关系

D1×D2×……×Dn的子集，叫作在域D1, D2, ……, Dn上的关系

表示为R(D1, D2, ……, Dn)

R：关系名

n：关系的目或度 (Degree)

（2）元组

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

很多地方也称之为“记录”

（3）单元关系与二元关系

当n=1时，称该关系为单元关系（Unary relation）或一元关系

当n=2时，称该关系为二元关系（Binary relation）

（4）关系的表示

关系也是一个二维表，表的每行对应一个元组，表的每列对应一个域

（5）属性

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

n 目关系必有n个属性

（6）码

候选码（Candidate key）

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

简单的情况：候选码只包含一个属性

全码（All-key）

关系模式的所有属性组都是这个关系模式的候选码

这是一种最极端的情况

（7）三类关系

基本关系（基本表或基表）

是实际存在的表

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

查询表

查询结果对应的表

视图表

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

（8）基本关系的性质

列是同质的（Homogeneous）

不同的列可出自同一个域

其中的每一列称为一个属性

不同的属性要给予不同的属性名

列的顺序无所谓，列的次序可以任意交换

行的顺序无所谓，行的次序可以任意交换

任意两个元组的候选码不能相同

分量必须取原子值

每一个属性列不可再分成若干子列

这是规范条件中最基本的一条

关系模式

什么是关系模式？

关系模式与关系是型与值的关系

关系模式是型

关系是值

关系模式是对关系的描述

元组集合的结构

属性构成

属性来自的域

属性与域之间的映象关系

完整性约束条件

定义关系模式

关系模式可以形式化地表示为：R(U, D, DOM, F)

R：关系名

U：组成该关系的属性名集合

D：U中属性所来自的域

DOM：属性向域的映象集合

F：属性间数据的依赖关系的集合

关系模式与关系的关系

关系模式

对关系的描述

静态的、稳定的

关系

关系模式在某一时刻的状态或内容

动态的、随时间不断变化的

关系模式和关系往往笼统称为关系

通过上下文加以区别

关系数据库

在一个给定的应用领域中，所有关系的集合构成一个关系数据库

关系数据库的型与值

关系数据库的型：关系数据库模式，是对关系数据库的描述

关系数据库的值：关系模式在某一时刻对应的关系的集合，通常称为关系数据库

关系模型的存储结构

关系数据库的物理组织

有的关系数据库管理系统中一个表对应一个操作系统文件，将物理数据组织交给操作系统完成

有的关系数据库管理系统从操作系统那里申请若干个大的文件，自己划分文件空间，组织表、索引等存储结构，并进行存储管理

关系操作

基本的关系操作

常用的关系操作

查询操作：选择、投影、连接、除、并、差、交、笛卡尔积

选择、投影、并、差、笛卡尔基是5种基本操作

数据更新：插入、删除、修改

关系操作的特点

集合操作方式：操作的对象和结果都是集合，一次一集合的方式

关系数据库语言的分类

关系代数语言

用对关系的运算来表达查询要求，代表：ISBL

关系演算语言：用谓词来表达查询要求

元组关系演算语言：谓词变元的基本对象是元组变量，代表：APLHA,QUEL

域关系演算语言：谓词变元的基本对象是域变量，代表：QBE

具有关系代数和关系演算双重特点的语言，代表：SQL（Structured Query Language）

关系的三类完整性约束

实体完整性和参照完整性

关系模型必须满足的完整性约束条件称为关系的两个不变性，应该由关系系统自动支持

用户定义的完整性

应用领域需要遵循的约束条件，体现了具体领域中的语义约束

关系的完整性

实体完整性

实体完整性规则（Entity Integrity）

若属性A是基本关系R的主属性，则属性A不能取空值

空值就是“不知道”或“不存在”或“无意义”的值

实体完整性规则的说明

（1）实体完整性规则是针对基本关系而言的

一个基本表通常对应现实世界的一个实体集

（2）现实世界中的实体是可区分的，即它们具有某种唯一性标识

（3）关系模型中以主码作为唯一性标识

（4）主码中的属性即主属性不能取空值

主属性取空值，就说明存在某个不可标识的实体

即存在不可区分的实体，这与第（2）点相矛盾

因此这个规则称为实体完整性

参照完整性

关系间的引用

在关系模型中实体及实体间的联系都是用关系来描述的，自然存在着关系与关系间的引用

外码

设F是基本关系R的一个或一组属性，但不是关系R的码。如果F与基本关系S的主码Ks相对应，则称F是R的外码

基本关系R称为参照关系（Referencing Relation）

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

关系R和S不一定是不同的关系

目标关系S的主码Ks和参照关系的外码F必须定义在同一个(或一组)域上

外码并不一定要与相应的主码同名

当外码与相应的主码属于不同关系时，往往取相同的名字，以便于识别

参照完整性规则

若属性（或属性组)F是基本关系R 的外码它与基本关系S的主码Ks相对应（基本关系R 和S不一定是不同的关系）

则对于R 中每个元组在F上的值必须为：

或者取空值（F的每个属性值均为空值）

或者等于S中某个元组的主码值

外码可以取空值，但若非空，则一定为被参照关系的主码值

用户定义的完整性

针对某一具体关系数据库的约束条件，反映某一具体应用所涉及的数据必须满足的语义要求

关系模型应提供定义和检验这类完整性的机制，以便用统一的系统的方法处理它们，而不需由应用程序承担这一功能

关系代数

概念

关系代数是一种抽象的查询语言，它用对关系的运算来表达查询

关系代数的运算

运算对象是关系

运算结果亦为关系

关系代数的运算符有两类

集合运算符

专门的关系运算符

关系代数运算符

传统的集合运算是从关系的“水平”方向即行的角度进行

专门的关系运算不仅涉及行而且涉及列

传统的集合运算

（1）并（Union）

（2）差（Difference）

（3）交（Intersection）

（4）笛卡尔积（Cartesian Product）

专门的关系运算

（1）选择

（2）投影

（3）连接

（4）除运算

小结

关系代数运算

并、差、交、笛卡尔积、投影、选择、连接、除

基本运算

并、差、笛卡尔积、投影、选择

交、连接、除，可以用5种基本运算来表达

引进它们并不增加语言的能力，但可以简化表达

关系数据库标准语言SQL

SQL概述

SQL（Structured Query Language）结构化查询语言，是关系数据库的标准语言

SQL是一个通用的、功能极强的关系数据库语言

SQL的特点

综合统一

集数据定义语言（DDL），数据操纵语言（DML），数据控制语言（DCL）功能于一体

可以独立完成数据库生命周期中的全部活动

定义和修改、删除关系模式，定义和删除视图，插入数据，建立数据库

对数据库中的数据进行查询和更新

数据库重构和维护

数据库安全性、完整性控制，以及事务控制

嵌入式SQL和动态SQL定义

用户数据库投入运行后，可根据需要随时逐步修改模式，不影响数据库的运行

数据操作符统一

高度非过程化

非关系数据模型的数据操纵语言“面向过程”，必须指定存取路径

SQL只要提出“做什么”，无须了解存取路径

存取路径的选择，以及SQL的操作过程由系统自动完成

面向集合的操作方式

非关系数据模型采用面向记录的操作方式，操作对象是一条记录SQL采用集合操作方式

操作对象、查找结果可以是元组的集合

一次插入、删除、更新操作的对象可以是元组的集合

以同一种语法结构提供多种使用方式

SQL是独立的语言

能够独立地用于联机交互的使用方式

SQL又是嵌入式语言

SQL能够嵌入到高级语言（例如C，C++，Java）程序中，供程序员设计程序时使用

语言简洁，易学易用

SQL功能极强，完成核心功能只用了9个动词

SQL的基本概念

SQL支持关系数据库三级模式结构

基本表

本身独立存在的表

SQL中一个关系就对应一个基本表

一个（或多个）基本表对应一个存储文件

一个表可以带若干索引

存储文件

逻辑结构组成了关系数据库的内模式

物理结构对用户是隐蔽的

视图

从一个或几个基本表导出的表

数据库中只存放视图的定义而不存放视图对应的数据

视图是一个虚表

用户可以在视图上再定义视图

数据定义

模式的定义与删除

定义模式

定义模式实际上定义了一个命名空间。

在这个空间中可以定义该模式包含的数据库对象，例如基本表、视图、索引等。

在CREATE SCHEMA中可以接受CREATE TABLE，CREATE VIEW和GRANT子句。

语句格式

CREATE SCHEMA <模式名> AUTHORIZATION <用户名>[<表定义子句>|<视图定义子句>|<授权定义子句>]

删除模式

语句格式

DROP SCHEMA <模式名> <CASCADE|RESTRICT>

CASCADE（级联）

删除模式的同时把该模式中所有的数据库对象全部删除

RESTRICT（限制）

如果该模式中定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行

仅当该模式中没有任何下属的对象时才能执行。

基本表的定义、删除与修改

定义基本表

语句格式

CREATE TABLE <表名>( <列名> <数据类型> [<列级完整性约束条件>] [, <列名> <数据类型> [<列级完整性约束条件>]] … [, <表级完整性约束条件>] );

<表名>：所要定义的基本表的名字

<列名>：组成该表的各个属性（列）

<列级完整性约束条件>：涉及相应属性列的完整性约束条件

<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件

如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。

数据类型

SQL中域的概念用数据类型来实现

定义表的属性时需要指明其数据类型及长度选用哪种数据类型、取值范围、要做哪些运算

模式与表

每一个基本表都属于某一个模式

一个模式可包含多个基本表

定义基本表所属模式

方法一：在表名中明显地给出模式名

Create table"S-T".Student(......); /*模式名为 S-T*/

Create table "S-T".Cource(......);

Create table "S-T".SC(......);

方法二：在创建模式语句中同时创建表

方法三：设置所属的模式

修改基本表

语句格式

ALTER TABLE <表名> [ ADD[COLUMN] <新列名> <数据类型> [ 完整性约束 ] ] [ ADD <表级完整性约束>] [ DROP [ COLUMN ] <列名> [CASCADE| RESTRICT] ] [ DROP CONSTRAINT<完整性约束名>[ RESTRICT | CASCADE ] ] [ ALTER COLUMN <列名><数据类型> ];

删除基本表

语句格式

DROP TABLE <表名> [ RESTRICT | CASCADE ];

RESTRICT：删除表是有限制的

欲删除的基本表不能被其他表的约束所引用

如果存在依赖该表的对象，则此表不能被删除

CASCADE：删除该表没有限制

在删除基本表的同时，相关的依赖对象一起删除

索引的建立与删除

建立索引的目的

加快查询速度

关系数据库管理系统中常见索引

顺序文件上的索引

B+树索引

散列（hash）索引

位图索引

特点

B+树索引具有动态平衡的优点

HASH索引具有查找速度快的特点

谁可以建立索引

数据库管理员或表的属主（即建立表的人）

谁维护索引

关系数据库管理系统自动完成

使用索引

关系数据库管理系统自动选择合适的索引作为存取路径，用户不必也不能显式地选择索引

语句格式

CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…);

<表名>：要建索引的基本表的名字

索引：可以建立在该表的一列或多列上，各列名之间用逗号分隔

<次序>：指定索引值的排列次序，升序：ASC，降序： DESC。缺省值：ASC

UNIQUE：此索引的每一个索引值只对应唯一的数据记录

CLUSTER：表示要建立的索引是聚簇索引

数据字典

数据字典是关系数据库管理系统内部的一组系统表，它记录了数据库中所有定义信息

关系模式定义

视图定义

索引定义

完整性约束定义

各类用户对数据库的操作权限

统计信息等

关系数据库管理系统在执行SQL的数据定义语句时，际上就是在更新数据字典表中的相应信息

数据查询

语句查询案例（基于MySQL实现的学生选课数据库）

数据库

建立学生选课数据库

create database if not exists db_edu default CHARSET = utf8mb4 collate = utf8mb4_0900_ai_ci;

删除学生选课数据库

DROP SCHEMA `db_edu`;

基本表

创建学生表

use db_edu # 创建student学生表 create table db_edu.student ( sno bigint not null COMMENT '学号', sname varchar(100) null COMMENT '姓名', ssex char(1) null COMMENT '性别', sage int null COMMENT '年龄', sdept varchar(100) null COMMENT '所在系', constraint student_PK primary key (sno) ) engine = InnoDB default CHARSET = utf8mb4

删除学生表

DROP TABLE db_edu.student;

创建课程表

use db_edu # 创建course课程表 create table db_edu.course ( cno bigint not null COMMENT '课程号', cname varchar(100) null COMMENT '课程名', cpno BIGINT null COMMENT '先行课', ccredit int null COMMENT '学分', constraint course_PK primary key (cno) ) engine = InnoDB default CHARSET = utf8mb4

创建选课成绩表

use db_edu # 创建sc选课成绩表 create table db_edu.sc ( sno BIGINT not null COMMENT '学号', cno BIGINT not null COMMENT '课程号', grade int null COMMENT '成绩', constraint sc_PK primary key (sno, cno), constraint sc_FK foreign key (sno) references db_edu.student(sno), constraint sc_FK_1 foreign key (cno) references db_edu.course(cno) ) engine = InnoDB default CHARSET = utf8mb4

数据

插入学生表数据

INSERT INTO db_edu.student (sno, sname, ssex, sage, sdept) VALUES (201215121, '李勇', '男', 20, 'CS'), (201215122, '刘晨', '女', 19, 'CS'), (201215123, '王敏', '女', 18, 'MA'), (201215125, '张立', '男', 19, 'IS');

插入课程表数据

INSERT INTO db_edu.course (cno, cname, cpno, ccredit) VALUES (1, '数据库', 5, 4), (2, '数学', NULL, 2), (3, '信息系统', 1, 4), (4, '操作系统', 6, 3), (5, '数据结构', 7, 4), (6, '数据处理', NULL, 2), (7, 'PASCAL语言', 6, 4);

插入选课成绩表数据

INSERT INTO db_edu.sc (sno, cno, grade) VALUES (201215121, 1, 92), (201215121, 2, 85), (201215121, 3, 88), (201215122, 2, 90), (201215122, 3, 80);

语句格式

SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>] … FROM <表名或视图名>[,<表名或视图名> ]…|(SELECT 语句) [AS]<别名> [ WHERE <条件表达式> ] [ GROUP BY <列名1> [ HAVING <条件表达式> ] ] [ ORDER BY <列名2> [ ASC|DESC ] ];

SELECT子句：指定要显示的属性列

FROM子句：指定查询对象（基本表或视图）

WHERE子句：指定查询条件

GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。

HAVING短语：只有满足指定条件的组才予以输出

ORDER BY子句：对查询结果表按指定列值的升序或降序排序

单表查询

查询仅涉及一个表

1.选择表中的若干列

查询全体学生的学号与姓名

# 查询全体学生的学号与姓名 select sno, sname from db_edu.student

查询全体学生的姓名、学号、所在系

# 查询全体学生的姓名、学号、所在系 select sno, sname, sdept from db_edu.student

查询全部列

查询全体学生的详细记录

# 法一：不推荐 select * from db_edu.student;

# 法二：更推荐 select s.sno, s.sname, s.ssex, s.sage, s.sdept from db_edu.student s;

查询经过计算的值

查全体学生的姓名及其出生年份

# 查全体学生的姓名及其出生年份 select sname, sage, 2026-sage birthYear from db_edu.student

查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示系名

# 查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示系名 select sname, 2026-sage, LOWER(sdept) from db_edu.student

使用列别名改变查询结果的列标题

# 3.5（变式）使用列别名改变查询结果的列标题 select sname, 2026-sage birthYear, LOWER(sdept) sdept from db_edu.student

2.选择表中的若干元组

查询选修了某课程的学生学号

# 查询选修了课程的学生学号 select distinct sno from db_edu.sc

查询满足条件的元组

常用的查询条件

比较大小

查询计算机科学系全体学生的名单

# 查询计算机科学系全体学生的名单 select sname from db_edu.student s where s.sdept = 'CS'

查询所有年龄在20岁以下的学生姓名及其年龄

# 查询所有年龄在20岁以下的学生姓名及其年龄 select s.sname, s.sage from db_edu.student s where s.sage < 20

查询考试成绩有不及格的学生的学号

# 查询考试成绩有不及格的学生的学号 select distinct sno from db_edu.sc where grade < 60

确定范围

谓词: BETWEEN … AND …；NOT BETWEEN … AND …

查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄

# 查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄 select s.sname, s.sdept, s.sage from db_edu.student s where s.sage > 20 and s.sage < 23

# 查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄 select s.sname, s.sdept, s.sage from db_edu.student s where s.sage between 20 and 23

查询年龄不在20~23岁之间的学生姓名、系别和年龄

# 查询年龄不在20~23岁之间的学生姓名、系别和年龄 select s.sname, s.sdept, s.sage from db_edu.student s where s.sage not between 20 and 23

确定集合

谓词： IN <值表>, NOT IN <值表>

查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别

# 查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别 select sname, ssex from db_edu.student where sdept in ('CS', 'MA', 'IS')

查询既不是计算机科学系、数学系，也不是信息系的学生的姓名和性别

# 查询既不是计算机科学系、数学系，也不是信息系的学生的姓名和性别 select sname, ssex from db_edu.student where sdept not in ('CS', 'MA', 'IS')

字符匹配

谓词： [NOT] LIKE '<匹配串>' [ESCAPE '<换码字符>']

<匹配串>

可以是一个完整的字符串，也可以含有通配符%和_

%（百分号）

代表任意长度（长度可以为0）的字符串

例如，a%b表示以a开头，以b结尾的任意长度的字符串

_（下横线）

代表任意单个字符

例如，a_b表示以a开头，以b结尾的长度为3的任意字符串

匹配串为固定字符串

查询学号为201215121的学生的详细情况

# 查询学号为201215121的学生的详细情况 select * from Student where Sno like '201215121'

# 法二： select * from db_edu.student where sno = '201215121'

匹配串为含通配符的字符串

查询所有姓刘学生的姓名、学号和性别

# 查询所有姓刘学生的姓名、学号和性别 select sname, sno, ssex from db_edu.student where sname like '刘%'

查询姓“欧阳”且全名为三个汉字的学生的姓名

# 查询姓"欧阳"且全名为三个汉字的学生的姓名 select sname from db_edu.student where sname like '欧阳__';

查询名字中第2个字为“阳”字的学生的姓名和学号

# 查询名字中第2个字为“阳”字的学生的姓名和学号 select sname,sno from db_edu.student where sname like '___阳%';

查询所有不姓刘的学生姓名、学号和性别

# 查询所有不姓刘的学生姓名、学号和性别 select sname, sno, ssex from db_edu.student where sname not like '刘%';

查询所有不姓刘的学生姓名、学号和性别

查询DB_Design课程的课程号和学分

# 查询DB_Design课程的课程号和学分 # 法一：错误写法 select cno, ccredit from db_edu.course where cname like 'DB_Design';

# 法二： select cno, ccredit from db_edu.course where cname like 'DB\_Design';

# 法三：完整写法 select cno, ccredit from db_edu.course where cname like 'DB\_Design' escape '\\';

查询以“DB_”开头，且倒数第3个字符为i的课程的详细情况

# 查询以“DB_”开头，且倒数第3个字符为i的课程的详细情况 select * from db_edu.course where cname like 'DB\_%i__' escape '\\';

空值

谓词： IS NULL 或 IS NOT NULL

注意：“IS ” 不能用 “= ” 代替

查询缺少成绩的学生的学号和相应的课程号

某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。

# 查询缺少成绩的学生的学号和相应的课程号 select sno, cno from db_edu.sc where grade is null;

查所有有成绩的学生学号和课程号

# 查所有有成绩的学生学号和课程号 select sno, cno from db_edu.sc where grade is not null;

多重条件（逻辑运算）

逻辑运算符

AND和OR，来连接多个查询条件

AND的优先级高于OR

可以用括号改变优先级

查询计算机系年龄在20岁以下的学生姓名

# 查询计算机系年龄在20岁以下的学生姓名 select sname from db_edu.student where sdept = 'CS' and sage < 20;

查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别

# 查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别 select sname, ssex from db_edu.student where sdept in ('CS ', 'MA ', 'IS');

# 法二 select sname, ssex from db_edu.student where sdept = ' CS' or sdept = ' MA' or sdept = 'IS';

3.ORDER BY子句

ORDER BY子句

可以按一个或多个属性列排序

升序：ASC;

降序：DESC；

缺省值为升序

对于空值，排序时显示的次序由具体系统实现来决定

查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列

# 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列 select sno, grade from db_edu.sc where cno = '3' order by grade desc;

查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列

# 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列 select * from db_edu.student order by sdept, sage desc;

4.聚集函数

基本操作

统计元组个数

COUNT(*)

统计一列中值的个数

COUNT([DISTINCT|ALL] <列名>)

计算一列值的总和（此列必须为数值型）

SUM([DISTINCT|ALL] <列名>)

计算一列值的平均值（此列必须为数值型）

AVG([DISTINCT|ALL] <列名>)

求一列中的最大值和最小值

MAX([DISTINCT|ALL] <列名>)

MIN([DISTINCT|ALL] <列名>)

查询学生总人数

# 查询学生总人数 select COUNT(*) from db_edu.student;

查询选修了课程的学生人数

# 查询选修了课程的学生人数 select COUNT(distinct Sno) from db_edu.sc;

计算1号课程的学生平均成绩

# 计算1号课程的学生平均成绩 select AVG(Grade) from db_edu.sc where cno = '1';

查询选修1号课程的学生最高分数

# 查询选修1号课程的学生最高分数 select MAX(Grade) from db_edu.sc where cno = '1';

查询学生201215012选修课程的总学分数

# 查询学生201215121选修课程的总学分数 select SUM(ccredit) from db_edu.sc, db_edu.course where sno = 201215121 and sc.cno = course.cno; # 将两张表在cno字段上做等值连接

注意点

from sc, course的含义，相当于将两个表做一个笛卡尔积

笛卡尔积后，并不是结果集的所有元组都有意义

where子句中，通过sc.cno = course.cno，将两个表在cno字段上做等值连接

5.GROUP BY子句

细化聚集函数的作用对象

如果未对查询结果分组，聚集函数将作用于整个查询结果

对查询结果分组后，聚集函数将分别作用于每个组

按指定的一列或多列值分组，值相等的为一组

求各个课程号及相应的选课人数

# 求各个课程号及相应的选课人数 select cno, COUNT(sno) from db_edu.sc group by cno;

查询选修了3门以上课程的学生学号

# 查询选修了3门以上课程的学生学号 select sno from db_edu.sc group by sno having COUNT(*) >3;

查询平均成绩大于等于90分的学生学号和平均成绩

典型错解

select sno, AVG(grade) from db_edu.sc where AVG(grade)>= 90 group by sno;

因为WHERE子句中是不能用聚集函数作为条件表达式

正解：

select sno, AVG(grade) from db_edu.sc group by sno having AVG(grade) >= 90;

where、group by、having的执行顺序

where 早于 group by 早于 having

HAVING短语与WHERE子句的区别：作用对象不同

WHERE子句作用于基表或视图，从中选择满足条件的元组

HAVING短语作用于组，从中选择满足条件的组

连接查询

同时涉及两个以上的表的查询

连接条件或连接谓词：用来连接两个表的条件

语句格式

[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>

[<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>

连接字段：连接谓词中的列名称

连接条件中的各连接字段类型必须是可比的，但名字不必相同

连接操作的执行过程

（1）嵌套循环法（NESTED-LOOP）

首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接件的元组，找到后就将表1 中的第一个元组与该元组拼接起来，形成结果表中一个元组。

表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。

重复上述操作，直到表1中的全部元组都处理完毕

（2）索引连接（INDEX-JOIN）

对表2按连接字段建立索引

对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组

等值与非等值连接查询

等值连接

查询每个学生及其选修课程的情况

# 查询每个学生及其选修课程的情况 select #student.* student.sno, sname, ssex, sage, sdept, # sc.sc.* sc.sno, cno, grade from db_edu.student, db_edu.sc where student.sno = sc.sno;

自然连接

查询每个学生及其选修课程的情况（用自然连接实现）

# 查询每个学生及其选修课程的情况（用自然连接完成） select student.sno, sname, ssex, sage, sdept, cno, grade from db_edu.student, db_edu.sc where student.sno = sc.sno;

查询选修2号课程且成绩在85分以上的所有学生的学号和姓名

# 查询选修2号课程且成绩在85分以上的所有学生的学号和姓名 select student.sno, sname from db_edu.student, db_edu.sc where student.sno = sc.sno and cno = 2 and grade > 85;

自身连接

一个表与其自己进行连接，需要给表起别名以示区别

需要给表起别名以示区别

由于所有属性名都是同名属性，因此必须使用别名前缀

查询每一门课的间接先修课（即先修课的先修课）

# 查询每一门课的间接先修课（即先修课的先修课） select FIRST.Cno, SECOND.Cpno from Course first, Course second where FIRST.Cpno = SECOND.Cno;

外连接

外连接与普通连接的区别

普通连接操作只输出满足连接条件的元组

外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出

左外连接

列出左边关系中所有的元组

右外连接

列出右边关系中所有的元组

查询每个学生及其选修课程的情况（用外连接实现）

join关键字实现的内连接

select student.sno, sname, ssex, sage, sdept, cno, grade from db_edu.student join db_edu.sc on (student.sno = sc.sno);

左外连接实现

select student.sno, sname, ssex, sage, sdept, cno, grade from db_edu.student left join db_edu.sc on (student.sno = sc.sno);

右外连接实现

select student.sno, sname, ssex, sage, sdept, cno, grade from db_edu.student right join db_edu.sc on (student.sno = sc.sno);

多表连接

两个以上的表进行连接

查询每个学生的学号、姓名、选修的课程名及成绩

# 查询每个学生的学号、姓名、选修的课程名及成绩 select student.sno, sname, cname, grade from /*多表连接*/ db_edu.student, db_edu.sc, db_edu.course where student.sno = sc.sno and sc.cno = course.cno;

嵌套查询

概述

一个SELECT-FROM-WHERE语句称为一个查询块

将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询

select /* 外层查询/父查询 */ sname from db_edu.student where sno in ( select /* 内层查询/子查询 */ sno from db_edu.sc where cno = 2 );

上层的查询块称为外层查询或父查询

下层查询块称为内层查询或子查询

SQL语言允许多层嵌套查询

即一个子查询中还可以嵌套其他子查询

子查询的限制

不能使用ORDER BY子句

嵌套查询求解方法

不相关子查询：子查询的查询条件不依赖于父查询

由里向外、逐层处理

即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件

相关子查询：子查询的查询条件依赖于父查询

首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表

然后再取外层表的下一个元组

重复这一过程，直至外层表全部检查完为止

带有IN谓词的子查询

查询与“刘晨”在同一个系学习的学生

① 确定“刘晨”所在系名

select sdept from db_edu.student where sname = '刘晨'

② 查找所有在CS系学习的学生

select sno, sname, sdept from db_edu.student where sdept = 'CS';

将第一步查询嵌入到第二步查询的条件中

select sno, sname, sdept from db_edu.student where sdept in ( select sdept from db_edu.student where sname = '刘晨');

查询与“刘晨”在同一个系学习的学生（用自身连接实现）

# 查询与“刘晨”在同一个系学习的学生（用自身链接完成） select S2.sno, S2.sname, S2.sdept from db_edu.student S1, db_edu.student S2 where S1.sname = '刘晨' and S1.sdept = S2.sdept;

查询选修了课程名为“信息系统”的学生学号和姓名

① 首先在Course关系中找出

select cno from db_edu.course where cname = '信息系统'; # “信息系统”的课程号，为3号

② 然后在SC关系中找出选修了3号课程的学生学号

select sno from db_edu.sc where cno = 3;

③ 最后在Student关系中，取出Sno和Sname

select sno, sname from db_edu.student where sno = ...

最终SQL语句

select sno, sname from db_edu.student where sno in ( select sno from db_edu.sc where cno in ( select cno from db_edu.course where cname = '信息系统' ) );

查询选修了课程名为“信息系统”的学生学号和姓名（用连接查询实现）

# 查询选修了课程名为“信息系统”的学生学号和姓名（用连接查询完成） select student.sno, sname from db_edu.student, db_edu.sc, db_edu.course where student.sno = sc.sno and sc.cno = course.cno and course.cname = '信息系统';

带有比较运算符的子查询

查询与“刘晨”在同一个系学习的学生（要求用=代替IN）

# 查询与“刘晨 ”在同一个系学习的学生（要求用=代替IN） select sno, sname, sdept from db_edu.student where Sdept = ( select Sdept from db_edu.student where sname = '刘晨');

找出每个学生超过他选修课程平均成绩的课程号

select sno, cno from db_edu.sc x where grade >= (select AVG(grade) from db_edu.sc y where y.sno = x.sno);

带有ANY（SOME）或ALL谓词的子查询

使用ANY或ALL谓词时，必须同时使用比较运算

> ANY

大于子查询结果中的某个值

> ALL

大于子查询结果中的所有值

< ANY

小于子查询结果中的某个值

< ALL

小于子查询结果中的所有值

>= ANY

大于等于子查询结果中的某个值

>= ALL

大于等于子查询结果中的所有值

<= ANY

小于等于子查询结果中的某个值

<= ALL

小于等于子查询结果中的所有值

= ANY

等于子查询结果中的某个值

=ALL

等于子查询结果中的所有值（通常没有实际意义）

!=（或<>）ANY

不等于子查询结果中的某个值

!=（或<>）ALL

不等于子查询结果中的任何一个值

查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄

# 查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄 select sname, sage from db_edu.student where sage < any ( select sage from db_edu.student where sdept = 'CS') and sdept <> 'CS'; # 父查询块中的条件

查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄（用聚集函数实现）

# 查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄（用聚集函数实现） select sname, sage from db_edu.student where sage < ( select MAX(Sage) from Student where Sdept = 'CS') and Sdept <> 'CS';

查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄

法一：用ALL谓词

select sname, sage from db_edu.student where sage < all ( select sage from db_edu.student where sdept = 'CS') and sdept <> 'CS';

法二：用聚集函数

select sname, sage from db_edu.student where sage < ( select MIN(sage) from db_edu.student where sdept = 'CS') and sdept <> 'CS';

ANY（或SOME），ALL谓词与聚集函数、 IN谓词的等价转换关系

带有EXISTS谓词的子查询

EXISTS谓词

存在量词

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”

若内层查询结果非空，则外层的WHERE子句返回真值

若内层查询结果为空，则外层的WHERE子句返回假值

由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查值，给出列名无实际意义

NOT EXISTS谓词

若内层查询结果非空，则外层的WHERE子句返回假值

若内层查询结果为空，则外层的WHERE子句返回真值

查询所有选修了1号课程的学生姓名

含题目思路解析

法一：使用exist谓词实现

select sname from db_edu.student where exists ( select * from db_edu.sc where sno = student.sno and cno = '1');

法二：使用连接查询实现

select sname from db_edu.student, db_edu.sc where student.sno = sc.sno and cno = '1';

解析

查询没有选修1号课程的学生姓名

# 查询没有选修1号课程的学生姓名 select sname from db_edu.student where not exists ( select * from db_edu.sc where sno = student.sno and cno = '1');

不同形式的查询间的替换

一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换

所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换

用EXISTS/NOT EXISTS实现全称量词

SQL语言中没有全称量词（For all）

可以把带有全称量词的谓词转换为等价的带有存在量词的谓词

查询与“刘晨”在同一个系学习的学生（用带EXISTS谓词的子查询实现）

含题目思路解析

SELECT sname FROM db_edu.student WHERE NOT EXISTS (SELECT * FROM db_edu.course WHERE NOT EXISTS (SELECT * FROM db_edu.sc WHERE sno = student.sno AND cno = course.cno);

解析

集合查询

集合操作的种类

并操作（UNION）

查询计算机科学系的学生及年龄不大于19岁的学生

SELECT * FROM Student WHERE Sdept = 'CS' UNION SELECT * FROM Student WHERE Sage <= 19;

UNION：将多个查询结果合并起来时，系统自动去掉重复元组

SELECT * FROM Student WHERE Sdept = 'CS' UNION SELECT * FROM Student WHERE Sage <= 19;

UNION ALL：将多个查询结果合并起来时，保留重复元组

查询选修了课程1或者选修了课程2的学生

SELECT sno FROM db_edu.sc WHERE cno = '1' UNION SELECT sno FROM db_edu.sc WHERE cno = '2';

交操作（INTERSECT）

查询计算机科学系的学生与年龄不大于19岁的学生的交集

使用INTERSECT关键字

SELECT * FROM Student WHERE Sdept = 'CS' INTERSECT SELECT * FROM Student WHERE Sage <= 19;

等价实现

SELECT * FROM db_edu.student WHERE sdept = 'CS' AND sage <= 19;

差操作（EXCEPT）

查询计算机科学系的学生与年龄不大于19岁的学生的差集

用EXCEPT关键字

SELECT * FROM db_edu.student WHERE sdept = 'CS' EXCEPT SELECT * FROM db_edu.student WHERE sage <= 19;

等价实现

SELECT * FROM db_edu.student WHERE sdept = 'CS' AND sage > 19;

参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同

基于派生表的查询

子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，这时子查询生成的临时派生表（Derived Table）成为主查询的查询对象

找出每个学生超过他选修课程平均成绩的课程号

SELECT sno, cno FROM db_edu.sc, (SELECT sno, Avg(grade) FROM db_edu.sc GROUP BY sno) AS Avg_sc(avg_sno, avg_grade) WHERE sc.sno = Avg_sc.avg_sno and sc.grade >= Avg_sc.avg_grade;

如果子查询中没有聚集函数，派生表可以不指定属性列，子查询SELECT子句后面的列名为其缺省属性

查询所有选修了1号课程的学生姓名

SELECT sname FROM db_edu.student, (SELECT sno FROM db_edu.sc WHERE sc.cno = ' 1 ') AS sc1 WHERE student.sno = sc1.sno;

数据更新

插入数据

两种插入数据方式

插入元组

可以一次插入多个元组

语句格式

INSERT INTO <表名> [(<属性列1>[,<属性列2 >…)] VALUES (<常量1> [,<常量2>] … );

INTO子句

指定要插入数据的表名及属性列

属性列的顺序可与表定义中的顺序不一致

没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致

指定部分属性列：插入的元组在其余属性列上取空值

VALUES子句

提供的值必须与INTO子句匹配

值的个数

值的类型

将一个新学生元组（学号： 201215128;姓名：陈冬;性别：男;所在系： IS;年龄： 18岁）插入到Student表中

INSERT INTO db_edu.student (sno, sname, ssex, sdept, sage) VALUES ('201215128', '陈冬', '男', 'IS', 18);

将学生张成民的信息('201215126','张成民','男',18,'CS')，插入到Student表中

INSERT INTO db_edu.student VALUES ('201215126', '张成民', '男', 18, 'CS');

插入一条选课记录( '200215128','1 ')

法一：

INSERT INTO db_edu.sc(sno, cno) VALUES ('201215128 ', ' 1 ');

关系数据库管理系统将在新插入记录的grade列上自动地赋空值

法二：

INSERT INTO db_edu.sc VALUES (' 201215128 ', ' 1 ', NULL);

插入子查询结果

语句格式

INSERT INTO <表名> [(<属性列1> [,<属性列2> … )] 子查询;

INTO子句

子查询

SELECT子句目标列必须与INTO子句匹配

值的个数

值的类型

对每一个系，求学生的平均年龄，并把结果存入数据库

第一步：建表

CREATE TABLE Dept_age ( sdept CHAR(15), # 系名 Avg_age SMALLINT # 学生平均年龄 );

第二步：插入数据

INSERT INTO Dept_age(sdept, Avg_age) SELECT sdept, AVG(Sage) FROM Student GROUP BY Sdept;

功能

将新元组插入指定表中

修改数据

语句格式

UPDATE <表名> SET <列名>=<表达式>[,<列名>=<表达式>] … [WHERE <条件>];

功能

修改指定表中满足WHERE子句条件的元组

SET子句给出<表达式>的值用于取代相应的属性列

如果省略WHERE子句，表示要修改表中的所有元组

三种修改方式

修改某一个元组的值

将学生201215121的年龄改为22岁

UPDATE db_edu.student SET sage=22 WHERE sno = '201215121';

修改多个元组的值

将所有学生的年龄增加1岁

UPDATE db_edu.student SET sage= sage + 1; # sage++;（典型错误写法！SQL语法中没有++运算符）

带子查询的修改语句

将计算机科学系全体学生的成绩置零

UPDATE db_edu.sc SET grade=0 WHERE sno IN (SELECT sno FROM db_edu.student WHERE sdept = 'CS');

关系数据库管理系统在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则

实体完整性

主码不允许修改

用户定义的完整性

NOT NULL约束

UNIQUE约束

值域约束

删除数据

语句格式

DELETE FROM <表名> [WHERE <条件>];

WHERE子句

指定要删除的元组

缺省表示要删除表中的全部元组，表的定义仍在字典中

功能

删除指定表中满足WHERE子句条件的元组

三种删除方式

删除某一个元组的值

删除学号为201215128的学生记录

DELETE FROM db_edu.student WHERE sno = '201215128';

删除多个元组的值

删除所有的学生选课记录

DELETE FROM db_edu.sc;

带子查询的删除语句

删除计算机科学系所有学生的选课记录

DELETE FROM db_edu.sc WHERE sno IN (SELECT sno FROM db_edu.student WHERE sdept = 'CS');

空值的处理

空值就是“不知道 ”或“不存在 ”或“无意义 ”的值

一般有以下几种情况

该属性应该有一个值，但目前不知道它的具体值

该属性不应该有值

由于某种原因不便于填写

空值的产生

空值是一个很特殊的值，含有不确定性。对关系运算带来特殊的问题，需要做特殊的处理

向SC表中插入一个元组，学生号是“201215126”，课程号是“1 ”，成绩为空

法一：该学生还没有考试成绩，取空值

INSERT INTO db_edu.sc(sno, cno, grade) VALUES ('201215126', '1', NULL);

法二：没有赋值的属性，其值为空值

INSERT INTO db_edu.sc(sno, cno) VALUES ('201215126', '1');

将Student表中学生号为“201215200 ”的学生所属的系改为空值

UPDATE db_edu.student SET sdept = NULL WHERE sno='201215200';

空值的判断

判断一个属性的值是否为空值，用IS NULL或IS NOT NULL来表示

从Student表中找出漏填了数据的学生信息

SELECT * FROM db_edu.student WHERE sname IS NULL OR ssex IS NULL OR sage IS NULL OR sdept IS NULL;

空值的约束条件

属性定义（或者域定义）中

有NOT NULL约束条件的不能取空值

加了UNIQUE限制的属性不能取空值

码属性不能取空值

空值的算术运算、比较运算和逻辑运算

空值与另一个值（包括另一个空值）的算术运算的结果为空值

空值与另一个值（包括另一个空值）的比较运算的结果为UNKNOWN。

有UNKNOWN后，传统二值（TRUE， FALSE）逻辑就扩展成了三值逻辑

T 表示 TRUE F 表示 FALSE U 表示 UNKNOWN

例题

找出选修1号课程的不及格的学生

SELECT sno FROM db_edu.sc WHERE grade < 60 AND cno='1';

查询结果不包括缺考的学生，因为他们的grade值为null

选出选修1号课程的不及格的学生以及缺考的学生

法一

WHERE grade < 60 AND cno = '1' UNION SELECT sno FROM db_edu.sc WHERE grade IS NULL AND cno = '1';

法二

SELECT sno FROM db_edu.sc WHERE cno = '1' AND (grade < 60 OR grade IS NULL);

视图

视图的特点

虚表，是从一个或几个基本表（或视图）导出的表

只存放视图的定义，不存放视图对应的数据

基表中的数据发生变化，从视图中查询出的数据也随之改变

建立视图

语句格式

CREATE VIEW <视图名> [(<列名> [,<列名>]…)] AS <子查询> [WITH CHECK OPTION];

WITH CHECK OPTION

对视图进行UPDATE， INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）

子查询可以是任意的SELECT语句，是否可以含有ORDER BY子句和DISTINCT短语，则决定具体系统的实现

组成视图的属性列名：全部省略或全部指定

全部省略：由子查询中SELECT目标列中的诸字段组成

明确指定视图的所有列名

某个目标列是聚集函数或列表达式

多表连接时选出了几个同名列作为视图的字段

需要在视图中为某个列启用新的更合适的名字

关系数据库管理系统执行CREATE VIEW语句时只是把视图定义存入数据字典，并不执行其中的SELECT语句

在对视图查询时，按视图的定义从基本表中将数据查出。

例题

建立信息系学生的视图

CREATE VIEW IS_Student AS SELECT sno, sname, sage FROM db_edu.student WHERE sdept = 'IS';

建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生

CREATE VIEW IS_Student AS SELECT sno, sname, sage FROM db_edu.student WHERE sdept = 'IS' WITH CHECK OPTION;

定义IS_Student视图时加上了WITH CHECK OPTION子句，对该视图进行插入、修改和删除操作时， RDBMS（关系型数据库管理系统）会自动加上sdept='IS'的条件

建立信息系选修了1号课程的学生的视图（包括学号、姓名、成绩）

基于多个基表的视图

CREATE VIEW IS_S1(sno, sname, grade) AS SELECT Student.Sno, sname, sc.grade FROM db_edu.student, db_edu.sc WHERE sdept = 'IS' AND student.sno = sc.sno AND sc.cno = '1';

建立信息系选修了1号课程且成绩在90分以上的学生的视图

基于视图的视图

CREATE VIEW is_s2 AS SELECT sno, sname, grade FROM is_s1 WHERE grade >= 90;

定义一个反映学生出生年份的视图

带表达式的视图

CREATE VIEW BT_S(sno, sname, sbirth) AS SELECT sno, sname, 2014 - sage FROM db_edu.student;

将学生的学号及平均成绩定义为一个视图

分组视图

CREATE VIEW S_G(sno, gavg) AS SELECT sno, AVG(grade) FROM db_edu.sc GROUP BY sno;

将Student表中所有女生记录定义为一个视图

CREATE VIEW F_Student(F_Sno, name, sex, age, dept) AS SELECT * /*没有不指定属性列*/ FROM db_edu.student WHERE ssex = '女';

删除视图

语句格式

DROP VIEW <视图名>[CASCADE];

该语句从数据字典中删除指定的视图定义

如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除

删除基表时，由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除

删除视图BT_S和IS_S1

DROP VIEW bt_s;

DROP VIEW is_s1;

注意：在MySQL环境中，这条SQL语句是可以被正常执行的

MySQL 官方文档明确说明：这两个关键字在 MySQL 中被允许，但没有任何实际效果（Permitted but have no effect）

查询视图

用户角度：查询视图与查询基本表相同

关系数据库管理系统实现视图查询的方法

视图消解法（View Resolution）

进行有效性检查

转换成等价的对基本表的查询

执行修正后的查询

简言之，视图消解就是把“查询视图”语句，替换成“查询基本表”语句的过程

在信息系学生的视图中找出年龄小于20岁的学生

SELECT sno, sage FROM db_edu.is_student WHERE sage < 20;

法二：视图消解转换后的查询语句

SELECT sno, sage FROM db_edu.student WHERE sdept = 'IS' AND sage < 20;

视图消解的具体步骤

视图消解法的局限

有些情况下，视图消解法不能生成正确的查询

在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩

SELECT * FROM s_g WHERE gavg >= 90;

S_G视图的子查询定义

CREATE VIEW s_g (sno, gavg) AS SELECT sno, AVG(grade) FROM db_edu.sc GROUP BY sno;

不同DBMS对于该视图的查询，可能出现不同表现

错误转换

SELECT sno, AVG(grade) FROM db_edu.sc WHERE AVG(Grade) >= 90 GROUP BY Sno;

正确转换

SELECT sno, AVG(grade) FROM db_edu.sc GROUP BY sno

更新视图

将信息系学生视图IS_Student中学号“201215122”的学生姓名改为“刘辰”

UPDATE db_edu.is_student SET sname= '刘辰' WHERE sno = '201215122';

转换后的语句

UPDATE db_edu.student SET sname= '刘辰' WHERE sno = '201215122' AND sdept = 'IS';

删除信息系学生视图IS_Student中学号为“201215129”的记录

DELETE FROM db_edu.is_student WHERE sno = '201215129';

转换后的语句

DELETE FROM Student WHERE sno = '201215129' AND sdept = 'IS';

更新视图的限制

一些视图是不可更新的

因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新

例3.89定义的视图s_g为不可更新视图

UPDATE db_edu.s_g SET gavg = 90 WHERE sno = '201215121';

解析

视图的作用

视图能够简化用户的操作

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

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

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

适当的利用视图可以更清晰的表达查询

小结

SQL可以分为数据定义、数据查询、数据更新、数据控制四大部分

SQL是关系数据库语言的工业标准

大部分数据库管理系统产品都能支持SQL92，但是许多数据库系统只支持SQL99、SQL2008和SQL2011的部分特征，至今尚没有一个数据库系统能够完全支持SQL99以上的标准

数据库安全性

数据库安全性概述

定义

是指保护数据库，以防不合法使用所造成的数据泄露、篡改和破坏

数据库的不安全因素

非授权用户对数据库的恶意存取和破坏

数据库中重要或敏感的数据被泄露

安全环境的脆弱性

安全标准简介

可信计算机系统评价标准（TCSEC）

IT安全通用准则（CC）

共性：都有7个等级

数据库安全控制

计算机系统的安全模型

数据库管理系统的安全性控制模型

用户身份鉴别

静态口令鉴别

动态口令鉴别

生物特征鉴别

智能卡鉴别

入侵检测

存取控制

存取控制机制

定义用户权限

合法权限检查

定义用户权限和合法权限检查机制，一起组成了数据库管理系统的存取控制子系统

C2级的数据库管理系统支持自主存取控制，B1级的数据库管理系统支持强制存取控制

自主存取控制

简述

用户对于不同的数据库对象有不同的存取权限

不同的用户对同一对象也有不同的权限

用户还可将其拥有的存取权限转授给其他用户

因此自主存取控制非常灵活

组成用户权限的两个要素

数据库对象

操作类型

授予与收回对数据的操作权限

GRANT语句

语句格式

GRANT<权限>[, <权限>] ... ON <对象类型><对象名>[, <对象类型><对象名>] ... TO <用户>[, <用户>] ... [WITH GRANT OPTION];

WITH GRANT OPTION

获得某种权限的用户还可以把这种权限再授予其他的用户

把对Student表和Course表的全部操作权限授子用户U2和U3

GRANT ALL PRIVILEGES ON TABLE db_edu.student, db_edu.course TO u2, u3

几个注意点

在MySQL中，还需注意Grant语句给不存在的用户授权时，不支持创建用户

CREATE USER 'u2'@'%' IDENTIFIED BY '123123123'; CREATE USER 'u3'@'%' IDENTIFIED BY '123123123';

在MySQL中，目前只支持在一条Grant语句中操作1张基本表

需要授权多张表，则需要分拆成多个Grant语句

执行结束后，还应该刷新权限

FLUSH PRIVILEGES;

修正后的 SQL 语句

-- 1. 先确保用户存在 (MySQL 8.0+ 必需，5.7 可省略) -- 假设允许从任何主机连接，密码请根据实际情况设置 CREATE USER IF NOT EXISTS 'u2'@'%' IDENTIFIED BY '123123123'; CREATE USER IF NOT EXISTS 'u3'@'%' IDENTIFIED BY '123123123'; -- 2. 授权 GRANT ALL PRIVILEGES ON db_edu.student TO 'u2'@'%', 'u3'@'%'; GRANT ALL PRIVILEGES ON db_edu.course TO 'u2'@'%', 'u3'@'%'; -- 3. 刷新权限 FLUSH PRIVILEGES;

REVOKE语句

语句格式

REVOKE<权限>[, <权限>] ... ON <对象类型><对象名>[, <对象类型><对象名>] FROM<用户>[, <用户>] ...[ CASCADE | RESTRICT ];

CASCADE | RESTRICT

若语句指定了CASCADE，则级联收回授予的权限

若语句指定了RESTRICT，则转授权限后不能收回

默认值为RESTRICT

把用户u2对sc表的insert权限收回

revoke insert on table db_edu.sc from u2;

强制存取控制

简述

每一个数据库对象被标以一定的密级，每一个用户也被授予某一个级别的许可证

对于任意一个对象，只有具有合法许可证的用户才可以存取

强制存取控制因此相对比较严格

视图机制

审计

定义

审计功能把用户对数据库的所有操作自动记录下来放入审计日志

审计事件

审计功能

AUDIT语句和NOTAUDIT语句

数据加密

定义

数据加密

透明加密

非透明加密

传输加密

链路加密

对报文和报头都加密

端到端加密

只加密报文，不加密报头

其他安全性保护

推理控制

隐蔽信道

数据隐私

“三权分立”的安全管理机制

数据库完整性

理念

数据的正确性

是指数据是符合现实世界语义，反映了当前实际状况的

数据的相容性

是指数据库同一对象在不同关系表中的数据是符合逻辑的

举例

学生的学号必须唯一

性别只能是男或女

本科学生年龄的取值范围为14~50的整数

学生所选的课程必须是学校开设的课程，学生所在的院系必须是学校已成立的院系

为维护数据库的完整性，数据库管理系统必须

提供定义完整性约束条件的机制

完整性约束条件也称为完整性规则，是数据库中的数据必须满足的语义约束条件

SQL标准使用了一系列概念来描述完整性，包括关系模型的实体完整性、参照完整性和用户定义完整性

这些完整性一般由SQL的数据定义语言语句来实现

提供完整性检查的方法

数据库管理系统中检查数据是否满足完整性约束条件的机制称为完整性检查。

一般在INSERT、 UPDATE、 DELETE语句执行后开始检查，也可以在事务提交时检查

违约处理

数据库管理系统若发现用户的操作违背了完整性约束条件，就采取一定的动作

拒绝（NO ACTION）执行该操作

级连（CASCADE）执行其他操作

实体完整性

实体完整性定义

定义

若属性（指一个或一组属性）A是基本关系R的主属性，则A不能取空值

关系模型的实体完整性

CREATE TABLE中用PRIMARY KEY定义

单属性构成的码有两种说明方法

定义为列级约束条件

定义为表级约束条件

对多个属性构成的码只有一种说明方法：定义为表级约束条件

实体完整性检查和违约处理

插入或对主码列进行更新操作时，关系数据库管理系统按照实体完整性规则自动进行检查。包括：

检查主码值是否唯一，如果不唯一则拒绝插入或修改

检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改

检查记录中主码值是否唯一的一种方法是进行全表扫描

依次判断表中每一条记录的主码值与将插入记录上的主码值（或者修改的新主码值）是否相同

表扫描缺点——十分耗时

为避免对基本表进行全表扫描， RDBMS核心一般都在主码上自动建立一个索引

B+树索引

参照完整性

参照完整性定义

在CREATE TABLE中用FOREIGN KEY短语定义哪些列为外码

用REFERENCES短语指明这些外码参照哪些表的主码

参照完整性检查和违约处理

一个参照完整性将两个表中的相应元组联系起来

对被参照表和参照表进行增删改操作时有可能破坏参照完整性，必须进行检查

可能破坏参照完整性的情况及违约处理

参照完整性违约处理

拒绝（NO ACTION）执行

不允许该操作执行

该策略一般设置为默认策略

级联（CASCADE）操作

当删除或修改被参照表（Student）的一个元组造成了与参照表（SC）的不一致，则删除或修改参照表中的所有造成不一致的元组

设置为空值（SET-NULL）

当删除或修改被参照表的一个元组时造成了不一致，则将参照表中的所有造成不一致的元组的对应属性设置为空值

用户定义完整性

用户定义完整性定义

针对某一具体应用的数据必须满足的语义要求

关系数据库管理系统提供了定义和检验用户定义完整性的机制，不必由应用程序承担

属性上约束条件

属性上约束条件的定义

CREATE TABLE时定义属性上的约束条件

列值非空（NOT NULL）

列值唯一（UNIQUE）

检查列值是否满足一个条件表达式（CHECK）

属性上的约束条件检查和违约处理

插入元组或修改属性的值时，关系数据库管理系统检查属性上的约束条件是否被满足

如果不满足则操作被拒绝执行

元组上约束条件

元组上约束条件的定义

在CREATE TABLE时可以用CHECK短语定义元组上的约束条件，即元组级的限制

同属性值限制相比，元组级的限制可以设置不同属性之间的取值的相互约束条件

元组上的约束条件检查和违约处理

插入元组或修改属性的值时，关系数据库管理系统检查元组上的约束条件是否被满足

如果不满足则操作被拒绝执行

关系数据理论

问题的提出

关系数据库逻辑设计

针对具体问题，如何构造一个适合于它的数据模式

数据库逻辑设计的工具──关系数据库的规范化理论

关系模式由五部分组成，是一个五元组R(U, D, DOM, F)

相关SQL语句

-- 非规范化表 CREATE TABLE UnnormalizedData ( StudentID VARCHAR(10), StudentName VARCHAR(50), StudentDept VARCHAR(20), CourseID VARCHAR(10), CourseName VARCHAR(50), CourseDept VARCHAR(20), TeacherID VARCHAR(10), TeacherName VARCHAR(50), TeacherDept VARCHAR(20), Textbooks TEXT, Grade INT );

-- 插入示例数据 INSERT INTO UnnormalizedData VALUES ('S001', '学生_1', '计算机', 'C001', '数据结构', '计算机', 'T001', '教师_1', '计算机', '《数据结构与算法分析》、《算法导论》', 85), ('S001', '学生_1', '计算机', 'C002', '高等数学', '数学', 'T002', '教师_2', '数学', '《高等数学》（同济版）', 90), -- ... 更多数据 ('S100', '学生_100', '化学', 'C020', '药物化学', '化学', 'T005', '教师_5', '化学', '《药物化学》', 88);

存在的问题

表中套表（非原子性）

`Textbooks` 列包含多个值（如 `《算法导论》、《编译原理》（龙书）`），违反了属性不可再分的原则

数据冗余

学生姓名、学号、院系名等等信息多次重复

更新异常

如果教师_2 换了院系，需要修改所有他教的课程记录

插入异常

如果新开设一门课但还没学生选，无法插入数据

因为 StudentID 是主键的一部分

删除异常

如果某门课没人选了，删除记录会导致课程信息丢失

冗余度指标 (初始)

学生信息重复率

100% (每个学生选几门课，信息就重复几次)

教材列存储

非原子，无法直接查询单本教材

阶段1：第一范式（1NF）

理论解释

定义

数据库表的每一列都是不可分割的原子数据项

解决的问题

消除了“表中套表”的情况，使得每一格只存一个值

演进策略

将多值的 `Textbooks` 列拆分到独立的表中，主表只保留课程 ID

遗留问题

虽然原子化了，但数据冗余依然巨大，存在更新/插入/删除异常

Python 演进实现

新建表 SQL 语句

演进后的表结构结果

表名：Step1_1NF_Main

表名：CourseTextbooks

冗余度指标变化

教材列

变为原子值，冗余消除

整体冗余

依然很高（学生、课程、教师信息仍重复）

阶段2：第二范式（2NF）

理论解释

定义

在 1NF 基础上，消除非主属性对码的部分函数依赖

解决的问题

主键是 (StudentID, CourseID)。但 StudentName 只依赖于 StudentID，CourseName 只依赖于 CourseID。

2NF 将这些部分依赖的属性拆分出去

演进策略

拆分为学生表、课程表、选课表

遗留问题

存在传递依赖（如 CourseID -> TeacherID -> TeacherDept）

简单翻译

核心前提（只有“组合主键”才需要担心 2NF）

这是理解 2NF 最关键的一点，很多人忽略了它

如果你的表的主键（Primary Key）只有一个字段（比如用户 ID），那么它自动就是 2NF，不需要做任何操作

2NF 只针对主键是多个字段组合的情况（比如订单 ID + 商品 ID 才能唯一确定一行记录）

因为如果主键只有一个字段，就不存在“部分”依赖的问题了

字段依赖的最小单位是单个字段

不存在依赖半个字段的情况

什么是“部分函数依赖”？

让我们用一个“学生选课系统”的例子。假设我们有一张表，记录哪个学生选了哪门课，以及成绩。

表结构（未优化）

第一步：找主键（码）

单靠学号能确定一行吗？

不能，因为同一名学生选修多门课

单靠课程号能确定一行吗？

不能，因为同一门课可以被多名学生选

主键必须是组合的： (学号，课程号)

第二步：找依赖关系（谁决定了谁？）

现在我们看其他非主键字段（姓名、课程名称、成绩）是由什么决定的：

成绩 (Score)

想知道“张三的数学成绩”，我必须知道学号和课程号

依赖关系：(学号，课程号) -> 成绩

这是完全依赖（依赖整个主键）。没问题

学生姓名 (Name)

想知道“张三的姓名”，我需要知道学号和课程号吗？

不需要！我只需要知道学号就够了。哪怕他没选课，他仍然叫张三

依赖关系：学号 -> 学生姓名

问题来了：主键是 (学号，课程号)，但姓名只依赖主键里的一部分（学号）

这就是部分函数依赖。违反2NF

课程名称 (CourseName)

想知道“数学”这门课的名字，我需要知道学号吗？

不需要！我只需要知道课程号

依赖关系：课程号 -> 课程名称

问题同上：只依赖主键里的一部分（课程号）。违反 2NF

为什么要消除它？（不遵守 2NF 的后果）

如果不把这种“部分依赖”消除掉，把姓名和课程名硬塞在这个表里，会发生什么灾难？

数据冗余（浪费空间）

假设，张三选了 10 门课。那么，“张三”这个名字就要重复存 10 次

假设，数学这门课被 100 个学生选。那么，“数学”这个名字就要存 100 次

更新异常（改起来麻烦）

如果张三改名了，你得找到他选的所有课，把这 10 行记录里的名字全改了。漏改一行，数据就不一致了

如果“数学”课改名叫“高等数学”了，你得更新几百行记录

插入异常（想存存不进）

来了一个新学生，还没选课。你想先把他的信息录进去

不行！因为主键是 (学号，课程号)，课程号不能为空。不选课就不能存学生信息

删除异常（删错了东西）

张三毕业了，我们要删除他的选课记录

如果把他的记录全删了，万一“数学”课只有他一个人选，那么“数学”这门课的信息也从数据库里消失了！

如何做到 2NF？（拆解表格）

实现2NF的要求

就是把“只依赖部分主键”的字段，单独拆出去

以上方我们将上面那张大表（表结构（未优化）为例，这一步将拆成三张小表

表 1：学生表（只放依赖“学号”的）

主键

学号

字段

学生姓名

解释

姓名完全依赖学号，这里没有部分依赖

表 2：课程表（只放依赖“课程号”的）

主键

课程号

字段

课程名称

解释

课程名完全依赖课程号

表 3：选课表（只放依赖“学号 + 课程号”的）

主键

学号

课程号

字段

成绩

解释

成绩必须同时知道是谁、什么课才能确定，这是完全依赖

经过上述改造，这三张表都满足2NF了

一句话总结

第二范式（2NF）的言下之意

在一个表中，如果主键是由多个字段组成的，那么表里的其他所有字段，必须依赖于“整个主键”，而不能只依赖于主键中的“某一部分”

如果发现有字段只依赖于主键的一部分，就把它拆分到另一个表里去

Python 演进实现

新建表 SQL 语句

演进后的表结构结果

Student

表名：Step2_2NF_Student

Course

表名：Step2_2NF_Course

表名：Step2_2NF_SC

CourseTextbooks

保留自 1NF

冗余度指标变化

学生信息

冗余降为 0 (每个学生只存一次)

课程信息

冗余降为 0 (每门课只存一次)

教师信息

仍有冗余 (如果一名教师教 5 门课，教师姓名和院系在 Course 表中重复 5 次)

阶段3：第三范式（3NF）

理论解释

定义

在 2NF 基础上，消除非主属性对码的传递函数依赖

解决的问题

在 Course 表中，CourseID -> TeacherID，而 TeacherID -> TeacherName, TeacherDept

存在传递依赖

如果教师换院系，需修改多行课程记录

演进策略

将教师信息独立成表，课程表只保留 TeacherID

遗留问题

理论上可能存在主属性对码的依赖问题（BCNF 范畴），但在本案例中 3NF 已基本解决大部分异常

简单翻译

核心前提：2NF 已经满足了

现在你的表已经满足了 2NF（没有部分依赖了）

如果是单主键，2NF 自动满足

如果是组合主键，已经拆分成完全依赖了

现在的任务

检查表里的非主键字段之间，有没有“私下的依赖关系”

什么是“传递函数依赖”？

还是用例子说话。假设我们要管理员工信息

表结构（满足 2NF，但不满足 3NF）

第一步：找主键

主键是员工 ID。每个员工 ID 唯一确定一行

因为是单主键，所以它自动满足 2NF（不存在部分依赖）

第二步：找依赖关系

员工 ID -> 姓名 (没问题，直接依赖)

员工 ID -> 部门 ID (没问题，直接依赖)

关键点来了：部门名称和部门地点是由谁决定的？

是由员工 ID 决定的吗？

表面上是，因为知道了员工就知道部门

但是，本质上它们是由部门 ID 决定的！

关系链

员工 ID -> 部门 ID -> 部门名称

这就是传递依赖

A 决定 B，B 决定 C，所以 A 间接决定了 C

在 3NF 看来，部门名称应该直接依赖主键，而不是通过部门 ID 传递过来

为什么要消除它？（不遵守 3NF 的后果）

如果不把这种“传递依赖”消除掉，会有什么问题？

数据冗余

“技术部”和“北京”这两个信息，每有一个员工就要重复存一次。如果有 1000 个技术人员，就要存 1000 次“北京”

更新异常

如果“技术部”搬到了“深圳”，你需要修改所有技术部员工的记录。漏改一个，数据就不一致了（有的显示北京，有的显示深圳）

插入异常

公司新成立了一个“人事部”，但还没招到员工

你想录入“人事部”的信息，不行！因为主键是员工 ID，没有员工就不能存部门信息

删除异常

如果“销售部”的所有员工都离职了，删除员工记录时，“销售部”的信息也跟着消失了

如何做到 3NF？（拆解表格）

实现 3NF 的要求

非主键字段必须直接依赖主键，不能依赖其他非主键字段

我们将员工信息表拆成两张子表

表 1：员工表

主键

员工 ID

字段

姓名

部门 ID

解释

员工直接依赖部门 ID，没问题

表 2：部门表

主键

部门 ID

字段

部门名称

部门地点

解释

部门信息直接依赖部门 ID，消除了传递依赖

一句话总结

第三范式（3NF）的言下之意

表里的非主键字段，只能依赖主键，不能依赖其他非主键字段

Python 演进实现

新建表 SQL 语句

演进后的表结构结果

Student

Teacher

Course

CourseTextbooks

冗余度指标变化

教师信息

冗余降为 0 (每位教师只存一次)

更新异常

消除 (改教师院系只需改 Teacher 表一行)

插入/删除异常

基本消除

阶段4：BC范式（BCNF）

理论解释

定义

在 3NF 基础上，确保每一个决定因素都包含码（即所有非平凡函数依赖 X->Y 中，X 必须是超键）

解决的问题

3NF 允许主属性对码的部分依赖，BCNF 更严格。通常用于解决主键重叠或特殊依赖场景

本案例检查

Student 表

StudentID 是码，无其他依赖。满足

Teacher 表

TeacherID 是码，无其他依赖。满足

Course 表

CourseID 是码。CourseID -> TeacherID。TeacherID 不是码，但 TeacherID 不决定 CourseID (一个老师教多门课)。无违反 BCNF 的依赖

SC表

(StudentID, CourseID) 是码。Grade 完全依赖。满足

结论

本案例在 3NF 分解后，已自然满足 BCNF。无需进一步拆表

简单翻译

核心前提：3NF 已经满足了

BCNF 是 3NF 的“增强版”

绝大多数满足 3NF 的设计也满足 BCNF，但有一种特殊情况 3NF 放过了，BCNF 不允许

现在的任务

检查表里的每一个决定因素（箭头左边），它是不是主键（或超键）

什么是“决定因素包含码”？

这个定义有点绕，我们用一个经典的“学生 - 老师 - 课程”例子来说明

规则设定

一个学生可以选多门课，每门课有一个老师

一个老师只教一门课（比如张老师只教数学）

一门课可以有多个老师（数学可以有张老师、李老师）

表结构（满足 3NF，但不满足 BCNF）

第一步：找主键（候选码）

(学生，课程) 可以确定老师吗？

可以（张三的数学课是张老师）

(学生，老师) 可以确定课程吗？

可以（张三找张老师，肯定是数学课）

所以，这张表有两个候选主键

(学生，课程)

(学生，老师)

第二步：找依赖关系

依赖 1：(学生，课程) -> 老师

这是主键决定非主键，没问题

依赖 2：(学生，老师) -> 课程

这也是主键决定非主键，没问题

关键点来了：还有一个隐藏规则：老师 -> 课程（因为一个老师只教一门课）

在这个依赖里，老师是决定因素（箭头左边）

但是，老师是主键吗？

不是！单独一个“张老师”不能确定表里的唯一一行（因为张老师教很多学生）

3NF 的看法

课程是主键的一部分（ prime attribute ），所以 3NF 允许这种依赖

BCNF 的看法

不行！箭头左边的老师必须是主键（超键）。既然老师不是主键，就不能决定课程

为什么要消除它？（不遵守 BCNF 的后果）

虽然 3NF 说这样可以，但会有隐患

修改异常

如果张老师改教“物理”了

你需要修改所有选张老师课的学生记录。如果漏改一个，就会出现“张三的张老师教数学，李四的张老师教物理”的矛盾

逻辑混乱

“老师教什么课”本来是老师自己的属性，不应该和学生选课绑定在一起

如何做到 BCNF？（拆解表格）

BCNF 的要求

所有的函数依赖 X->Y 中，X 必须是超键（主键）

我们将表拆成两张

表 A：老师授课表

主键

老师

字段

课程

解释

老师决定课程，这里老师是主键，满足 BCNF

表 B：学生选课表

主键

(学生，课程)

(学生，老师)

字段

法一

学生

老师

法二

学生

课程

解释

学生选老师，满足 BCNF

一句话总结

BCNF 的言下之意

只要箭头的左边（决定因素）能决定别人，那它自己必须是个主键

Python 演进实现

无需调整，已经天然满足

新建表 SQL 语句

无新增表，结构同 3NF

演进后的表结构结果

同 3NF 结果

冗余度指标变化

无变化，但理论安全性更高

阶段5：第四范式（4NF）

理论解释

定义

在 BCNF 基础上，消除非平凡的多值依赖 (MVD)

解决的问题

如果一个实体有两个或多个独立的多值属性，会导致笛卡尔积式的冗余

本案例应用

回顾 1NF 步骤，Course 和 Textbooks 是多值依赖关系 (CourseID ->-> Textbook)

如果在 2NF 时，我们将 Textbooks 留在 Course 表，则违反 4NF

即使原子化，如每行存一本教材，导致课程信息重复

我们在 1NF 时已经将其独立为 CourseTextbooks 表

检查 CourseTextbooks 表：CourseID ->-> Textbook。表中只有这两个属性，不存在其他独立的多值属性（如参考教室、参考视频等）

结论

当前的 CourseTextbooks 表设计符合 4NF

作用

确保多值属性独立存储，不与其他属性产生不必要的关联冗余

简单翻译

核心前提：BCNF 已经满足了

到了 4NF，函数依赖（单值依赖）已经处理干净了

现在的任务

处理多值依赖（Multi-Valued Dependency, MVD）。也就是“一个东西对应多个独立的列表”的情况

什么是“多值依赖”？

想象一家披萨店。

规则设定

一家店提供多种口味的披萨（如：芝士、培根）

一家店覆盖多个配送区域（如：A 区、B 区）

关键点

披萨口味和配送区域是独立的。卖芝士披萨不影响送 A 区，两者没关系

表结构（满足 BCNF，但不满足 4NF）

第一步：找主键

主键必须是全部三个：(店铺，披萨，区域)

因为只有合起来才能唯一确定一行

第二步：找依赖关系

这里没有函数依赖

没有 X->Y 这种单值决定关系

但是有多值依赖

店铺 ->-> 披萨口味

一家门店对应多款披萨

店铺 ->-> 配送区域

一家门店对应多个配送区域

问题

这两个表本来逻辑上是独立的

但在表里，它们被强行组合成了笛卡尔积（交叉组合）

如果有 10 种披萨，10 个区域，你就需要存 100 行记录

为什么要消除它？（不遵守 4NF 的后果）

爆炸式冗余

如上所述，数据量是披萨数 × 区域数

插入异常

店里新加了一种“榴莲披萨”

你必须为每个配送区域都插入一行记录（榴莲+A 区，榴莲+B 区...）。如果漏了一个区，系统可能以为那个区不送榴莲披萨。

删除异常

如果"B 区”暂时不配送了

你删除所有 B 区的记录，结果所有披萨在 B 区的信息都没了

而且如果不小心，可能把某些披萨的其他区信息也搞混

如何做到 4NF？（拆解表格）

4NF 的要求

消除独立的非平凡多值依赖，把独立的列表拆开

我们将表拆成两张

表 1：店铺披萨表

主键

(店铺，披萨)

字段

店铺

披萨口味

解释

只存店卖什么披萨

表 2：店铺配送表

主键

(店铺，区域)

字段

店铺

配送区域

解释

只存店送哪些区域

经改造，如果店铺需要上新一款披萨口味、增加一个配送区域，均只需另存1行记录

一句话总结

第四范式（4NF）的言下之意

如果表里有两个或多个相互独立的“一对多”关系，请把它们在物理上分开存

Python 演进实现

无需调整，已经天然满足

新建表 SQL 语句

无新增表，CourseTextbooks 已在 1NF 创建

演进后的表结构结果

同 3NF/BCNF 结果

冗余度指标变化

教材关联

完全独立，无冗余

总结

范式演进对比表

最终推荐数据库 schema (SQL)

数据库设计

数据库设计概念

数据库设计概述

数据库设计定义

是指对于一个给定的应用环境，构造（设计）优化的数据库逻辑模式和物理结构，并据此建立数据库及其应用系统，使之能够有效地存储和管理数据，满足各种用户的应用需求，包括信息管理要求和数据操作要求

信息管理要求

在数据库中应该存储和管理哪些数据对象

数据操作要求

对数据对象需要进行哪些操作，如查询、增、删、改、统计等操作

数据库设计的目标

是为用户和各种应用系统提供一个信息基础设施和高效率的运行环境

高效率的运行环境

数据库数据的存取效率高

数据库存储空间的利用率高

数据库系统运行管理的效率高

数据库设计的特点

数据库建设的基本规律

三分技术、七分管理、十二分基础数据

管理

数据库建设项目管理：企业（即应用部门）的业务管理

基础数据：数据的收集、整理、组织和不断更新

结构（数据）设计和行为（处理）设计相结合

将数据库结构设计和数据处理设计密切结合

结构和行为分离的设计

传统的软件工程：重“行为设计”

忽视对应用中数据语义的分析和抽象，只要有可能就尽量推迟数据结构设计的决策

早期的数据库设计：重“结构设计”

致力于数据模型和数据库建模方法研究，忽视了行为设计对结构设计的影响

数据库设计方法

大型数据库设计是涉及多学科的综合性技术，又是一项庞大的工程项目

它要求多方面的知识和技术。主要包括

计算机的基础知识

软件工程的原理和方法

程序设计的方法和技巧

数据库的基本知识

数据库设计技术

应用领域的知识

典型方法

新奥尔良（New Orleans）方法

基于E-R模型的数据库设计方法

3NF（第三范式）的设计方法

面向对象的数据库设计方法

统一建模语言（UML）方法

数据库设计的基本步骤

数据库设计分6个阶段

需求分析阶段

是否做得充分与准确，决定了构建数据库的速度和质量

概念结构设计阶段

通过对用户需求进行综合、归纳与抽象，形成一个独立于具体数据库管理系统的概念模型

逻辑结构设计阶段

将概念结构转换为某个数据库管理系统所支持的数据模型，并对其进行优化

物理结构设计阶段

为逻辑数据结构选取一个最适合应用环境的物理结构

包括存储结构和存取方法

数据库实施阶段

根据逻辑设计和物理设计的结果构建数据库

编写与调试应用程序

组织数据入库并进行试运行

数据库运行和维护阶段

经过试运行后即可投入正式运行

在运行过程中必须不断对其进行评估、调整与修改

需求分析和概念设计独立于任何数据库管理系统逻辑设计和物理设计与选用的数据库管理系统密切相关

设计一个完善的数据库应用系统往往是上述6个阶段的不断反复

这个设计步骤既是数据库设计的过程，也包括了数据库应用系统的设计过程

把数据库的设计和对数据库中数据处理的设计紧密结合起来，将这两个方面的需求分析、抽象、设计、实现在各个阶段同时进行，相互参照，相互补充，以完善两方面的设计

数据库设计的基本步骤示意图

数据库设计各个阶段的设计描述

数据库设计过程中的各级模式

数据库设计不同阶段形成的数据库各级模式

需求分析

需求分析的目标——分析用户的要求

是设计数据库的起点

结果是否准确地反映了用户的实际要求，将直接影响到后面各个阶段的设计，并影响到设计结果是否合理和实用

需求分析的任务

简述

详细调查现实世界要处理的对象（组织、部门、企业等）

充分了解原系统（手工系统或计算机系统）工作概况

明确用户的各种需求

在此基础上确定新系统的功能

新系统必须充分考虑今后可能的扩充和改变

调查的重点是“数据”和“处理”，获得用户对数据库的要求

信息要求

用户需要从数据库中获得信息的内容与性质

由信息要求可以导出数据要求，即在数据库中需要存储哪些数据

处理要求

用户要完成的处理功能

对处理性能的要求

安全性与完整性要求

确定用户最终需求的难点

用户缺少计算机知识，不能准确地表达自己的需求

提出的需求往往不断地变化

设计人员缺少用户的专业知识，不易理解用户的真正需求，甚至误解用户的需求

解决方法

设计人员必须不断深入地与用户进行交流，才能逐步确定用户的实际需求

需求分析的方法

调查清楚用户的实际需求并进行初步分析

与用户达成共识

分析与表达这些需求

数据字典

简述

数据字典是关于数据库中数据的描述，即元数据，不是数据本身

数据字典在需求分析阶段建立，在数据库设计过程中不断修改、充实、完善

数据字典是进行详细的数据收集和数据分析所获得的主要结果

注意

这里的数据字典，要与关系数据库管理系统中数据字典的区别和联系

数据字典的内容

数据项

数据项是不可再分的数据单位

对数据项的描述

数据项描述={数据项名,数据项含义说明,别名, 数据类型,长度,取值范围,取值含义, 与其他数据项的逻辑关系, 数据项之间的联系}

“取值范围 ”、“与其他数据项的逻辑关系”定义了数据的完整性约束条件，是设计数据检验功能的依据

可以用关系规范化理论为指导，用数据依赖的概念分析和表示数据项之间的联系

数据结构

数据结构反映了数据之间的组合关系

一个数据结构可以由若干个数据项组成

也可以由若干个数据结构组成

或由若干个数据项和数据结构混合组成

对数据结构的描述

数据结构描述={数据结构名, 含义说明, 组成:{数据项或数据结构}}

数据流

数据流是数据结构在系统内传输的路径

对数据流的描述

数据流描述={数据流名,说明,数据流来源,数据流去向,组成:{数据结构},平均流量,高峰期流量}

数据流来源

说明该数据流来自哪个过程

数据流去向

说明该数据流将到哪个过程去

平均流量

在单位时间（每天、每周、每月等）里的传输次数

高峰期流量

在高峰时期的数据流量

数据存储

数据存储是数据结构停留或保存的地方，也是数据流的来源和去向之一

对数据存储的描述

数据存储描述={数据存储名,说明,编号,输入的数据流 ,输出的数据流,组成:{数据结构},数据量,存取频度,存取方式}

存取频度

每小时、每天或每周存取次数，每次存取的数据量等信息

存取方法

批处理 / 联机处理

检索 / 更新

顺序检索 / 随机检索

输入的数据流

数据来源

输出的数据流

数据去向

处理过程

处理过程的具体处理逻辑一般用判定表或判定树来描述。数据字典中只需要描述处理过程的说明性信息

处理过程说明性信息的描述

处理过程描述={处理过程名,说明,输入:{数据流},输出:{数据流},处理:{简要说明}}

简要说明

说明该处理过程的功能及处理要求

功能

该处理过程用来做什么

处理要求

处理频度要求，如单位时间里处理多少事务，多少数据量、响应时间要求等

处理要求是后面物理设计的输入及性能评价的标准

概要

数据项是数据的最小组成单位

若干个数据项可以组成一个数据结构

数据字典通过对数据项和数据结构的定义来描述数据流、数据存储的逻辑内容

需求分析小结

把需求收集和分析作为数据库设计的第一阶段是十分重要的。

第一阶段收集的基础数据（用数据字典来表达）是下一步进行概念设计的基础。

强调两点

设计人员应充分考虑到可能的扩充和改变，使设计易于更改，系统易于扩充

必须强调用户的参与

概念结构设计（E-R图设计）

概念模型

将需求分析得到的用户需求抽象为信息结构（即概念模型）的过程就是概念结构设计

概念模型的特点

能真实、充分地反映现实世界，是现实世界的一个真实模型。

易于理解，从而可以用它和不熟悉计算机的用户交换意见。

易于更改，当应用环境和应用要求改变时，容易对概念模型修改和扩充。

易于向关系、网状、层次等各种数据模型转换描述概念模型的工具

E-R模型

实体之间的联系

两个实体型之间的联系

一对一联系（1 ∶ 1）

如果对于实体集A中的每一个实体，实体集B中至多有一个（也可以没有）实体与之联系，反之亦然，则称实体集A与实体集B具有一对一联系，记为1 ∶ 1。

例如，学校里一个班级只有一个正班长，而一个班长只在一个班中任职，则班级与班长之间具有一对一联系。

一对多联系（1 ∶ n）

如果对于实体集A中的每一个实体，实体集B中有n个实体（n ≥0）与之联系，反之，对于实体集B中的每一个实体，实体集A中至多只有一个实体与之联系，则称实体集A与实体集B有一对多联系，记为1 ∶ n。

例如，一个班级中有若干名学生，而每个学生只在一个班级中学习，则班级与学生之间具有一对多联系。

多对多联系（m ∶ n） E-R模型

如果对于实体集A中的每一个实体，实体集B中有n个实体（n ≥0）与之联系，反之，对于实体集B中的每一个实体，实体集A中也有m个实体（m ≥0）与之联系，则称实体集A与实体集B具有多对多联系，记为m ∶ n。

例如，一门课程同时有若干个学生选修，而一个学生可以同时选修多门课程，则课程与学生之间具有多对多联系。

两个实体型之间的三类联系

两个以上的实体型之间的联系

一般地，两个以上的实体型之间也存在着一对一、一对多、多对多联系

对于课程、教师与参考书3个实体型，如果一门课程可以有若干个教师讲授，使用若干本参考书，而每一个教师只讲授一门课程，每一本参考书只供一门课程使用，则课程与教师、参考书之间的联系是一对多的，如图7.7(a)所示

三个实体型之间的联系示例

单个实体型内的联系

同一个实体集内的各实体之间也可以存在一对一、一对多、多对多的联系。

例如，职工实体型内部具有领导与被领导的联系，即某一职工（干部） “领导 ”若干名职工，而一个职工仅被另外一个职工直接领导，因此这是一对多的联系，如图7.8所示。

单个实体型内的一对多联系示例

联系的度：参与联系的实体型的数目

2个实体型之间的联系度为2，也称为“二元联系”

3个实体型之间的联系度为3，称为“三元联系”

N个实体型之间的联系度为N，也称为“N元联系”

E-R图

E-R图提供了表示实体型、属性和联系的方法

实体型

用矩形表示，矩形框内写明实体名

属性

用椭圆形表示，并用无向边将其与相应的实体型连接起来

例如，学生实体具有学号、姓名、性别、出生年份、系、入学时间等属性，用E-R图表示如图7.9所示

联系

用菱形表示，菱形框内写明联系名，并用无向边分别与有关实体型连接起来，同时在无向边旁标上联系的类型（1 ∶ 1， 1 ∶ n或m ∶ n）

联系可以具有属性

一个实例

某工厂物资管理的概念模型

物资管理涉及的实体有

仓库

属性有仓库号、面积、电话号码

零件

属性有零件号、名称、规格、单价、描述

供应商

属性有供应商号、姓名、地址、电话号码、账号

项目

属性有项目号、预算、开工日期

职工

属性有职工号、姓名、年龄、职称

这些实体之间的联系如下

一个仓库可以存放多种零件，一种零件可以存放在多个仓库中

因此仓库和零件具有多对多的联系

用库存量来表示某种零件在某个仓库中的数量

一个仓库有多个职工当仓库保管员，一个职工只能在一个仓库工作

因此仓库和职工之间是一对多的联系

职工之间具有领导与被领导关系。即仓库主任领导若干保管员

因此职工实体型中具有一对多的联系

一个供应商可以供给若干项目多种零件，每个项目可以使用不同供应商供应的零件，每种零件可由不同供应商供给

因此供应商、项目和零件三者之间具有多对多的联系

绘制E-R图

实体及其属性图

实体及其联系图

完整的实体-联系图

概念结构设计

实体与属性的划分原则

为了简化E-R图的处置，现实世界的事物能作为属性对待的，尽量作为属性对待

两条准则

作为属性，不能再具有需要描述的性质。属性必须是不可分的数据项，不能包含其他属性

属性不能与其他实体具有联系，即E-R图中所表示的联系是实体之间的联系

E-R图的集成

E-R图的集成一般需要分两步

合并

解决各分E-R图之间的冲突，将分E-R图合并起来生成初步E-R图

修改和重构

消除不必要的冗余，生成基本E-R图

合并E-R图，生成初步E-R图

各个局部应用所面向的问题不同，各个子系统的E-R图之间必定会存在许多不一致的地方，称之为冲突

子系统E-R图之间的冲突主要有三类

属性冲突

属性域冲突，即属性值的类型、取值范围或取值集合不同

例如，零件号，有的部门把它定义为整数，有的部门把它定义为字符型

例如，年龄，某些部门以出生日期形式表示职工的年龄，而另一些部门用整数表示职工的年龄

属性取值单位冲突

例如，零件的重量有的以公斤为单位，有的以斤为单位，有的以克为单位

命名冲突

同名异义，即不同意义的对象在不同的局部应用中具有相同的名字

异名同义（一义多名），即同一意义的对象在不同的局部应用中具有不同的名字

如对科研项目，财务科称为项目，科研处称为课题，生产管理处称为工程

命名冲突的发生位置

可能发生在实体、联系一级上

也可能发生在属性一级上

通过讨论、协商等行政手段加以解决

结构冲突

同一对象在不同应用中具有不同的抽象

例如，职工在某一局部应用中被当作实体，而在另一局部应用中则被当作属性

解决方法：把属性变换为实体或把实体变换为属性，使同一对象具有相同的抽象

同一实体在不同子系统的E-R图中所包含的属性个数和属性排列次序不完全相同

解决方法：使该实体的属性取各子系统的E-R图中属性的并集，再适当调整属性的次序

实体间的联系在不同的E-R图中为不同的类型

实体E1与E2在一个E-R图中是多对多联系，在另一个E-R图中是一对多联系

解决方法：是根据应用的语义对实体联系的类型进行综合或调整

逻辑结构设计

逻辑结构设计的任务

把概念结构设计阶段设计好的基本E-R图，转换为与选用数据库管理系统产品所支持的数据模型相符合的逻辑结构

E-R图向关系模型的转换

转换内容

E-R图由实体型、实体的属性和实体型之间的联系三个要素组成

关系模型的逻辑结构是一组关系模式的集合

将E-R图转换为关系模型：将实体型、实体的属性和实体型之间的联系转化为关系模式

转换原则

一个实体型转换为一个关系模式

关系的属性

实体的属性

关系的码

实体的码

实体型间的联系有以下不同情况

一个1:1联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并

转换为一个独立的关系模式

关系的属性

与该联系相连的各实体的码以及联系本身的属性

关系的候选码

每个实体的码均是该关系的候选码

与某一端实体对应的关系模式合并

合并后关系的属性

加入对应关系的码和联系本身的属性

合并后关系的码

不变

一个1:n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并

转换为一个独立的关系模式

关系的属性

与该联系相连的各实体的码以及联系本身的属性

关系的码

n端实体的码

与n端对应的关系模式合并

合并后关系的属性

在n端关系中加入1端关系的码和联系本身的属性

合并后关系的码

不变

可以减少系统中的关系个数，一般情况下更倾向于采用这种方法

一个m:n联系转换为一个关系模式

关系的属性

与该联系相连的各实体的码以及联系本身的属性

关系的码

各实体码的组合

三个或三个以上实体间的一个多元联系转换为一个关系模式

关系的属性

与该多元联系相连的各实体的码以及联系本身的属性

关系的码

各实体码的组合

具有相同码的关系模式可合并

目的

减少系统中的关系个数

合并方法

将其中一个关系模式的全部属性，加入到另一个关系模式中后，去掉其中的同义属性（可能同名也可能不同名）

适当调整属性的次序

物理结构设计

什么是数据库的物理设计？

数据库在物理设备上的存储结构与存取方法称为数据库的物理结构，它依赖于选定的数据库管理系统。

为一个给定的逻辑数据模型选取一个最适合应用要求的物理结构的过程，就是数据库的物理设计。

数据库物理设计的步骤

确定数据库的物理结构

在关系数据库中主要指存取方法和存储结构

对物理结构进行评价

评价的重点是时间和空间效率

若评价结果满足原设计要求，则可进入到物理实施阶段；否则，就需要重新设计或修改物理结构，有时甚至要返回逻辑设计阶段修改数据模型

数据库物理设计的内容和方法

设计物理数据库结构的准备工作

充分了解应用环境，详细分析要运行的事务，以获得选择物理数据库设计所需参数

充分了解所用关系型数据库管理系统的内部特征，特别是系统提供的存取方法和存储结构

选择物理数据库设计所需参数

数据库查询事务

查询的关系

查询条件所涉及的属性

连接条件所涉及的属性

查询的投影属性

数据更新事务

被更新的关系

每个关系上的更新操作条件所涉及的属性

修改操作要改变的属性值

每个事务在各关系上运行的频率和性能要求

关系模式存取方法选择

数据库管理系统常用存取方法

B+树索引存取方法

Hash索引存取方法

选择Hash存取方法的规则

如果一个关系的属性主要出现在等值连接条件中或主要出现在等值比较选择条件中，而且满足下列两个条件之一

该关系的大小可预知，而且不变

该关系的大小动态改变，但所选用的数据库管理系统提供了动态Hash存取方法

聚簇存取方法

什么是聚簇？

为了提高某个属性（或属性组）的查询速度，把这个或这些属性（称为聚簇码）上具有相同值的元组集中存放在连续的物理块中称为聚簇。

该属性（或属性组）称为聚簇码（cluster key）

许多关系型数据库管理系统都提供了聚簇功能

聚簇存放与聚簇索引的区别

聚簇索引

建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中元组的物理顺序一致。

在一个基本表上最多只能建立一个聚簇索引

聚簇索引的适用条件

很少对其中的变长列进行修改操作

聚簇的用途

对于某些类型的查询，可以提高查询效率

大大提高按聚簇属性进行查询的效率

[例] 假设学生关系按所在系建有索引，现在要查询信息系的所有学生名单

计算机系的500名学生分布在500个不同的物理块上时，至少要执行500次I/O操作。

如果将同一系的学生元组集中存放，则每读一个物理块可得到多个满足查询条件的元组，从而显著地减少了访问磁盘的次数。

节省存储空间

聚簇以后，聚簇码相同的元组集中在一起了，因而聚簇码值不必在每个元组中重复存储，只要在一组中存一次就行了。

聚簇的局限性

聚簇只能提高某些特定应用的性能

建立与维护聚簇的开销相当大

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

当一个元组的聚簇码改变时，该元组的存储位置也要做相应改变

聚簇的适用范围

既适用于单个关系独立聚簇，也适用于多个关系组合聚簇

当通过聚簇码进行访问或连接是该关系的主要应用，与聚簇码无关的其他访问很少或者是次要的时，可以使用聚簇

尤其当SQL语句中包含有与聚簇码有关的ORDER BY, GROUP BY, UNION, DISTINCT等子句或短语时，使用聚簇特别有利，可以省去或减化对结果集的排序操作

关系模式存取方法选择

选择索引存取方法的主要依据

根据应用要求确定

对哪些属性列建立索引

对哪些属性列建立组合索引

对哪些索引要设计为唯一索引

选择索引存取方法的一般规则

如果一个（或一组）属性经常在查询条件中出现，则考虑在这个（或这组）属性上建立索引（或组合索引）

如果一个属性经常作为最大值和最小值等聚集函数的参数，则考虑在这个属性上建立索引

如果一个（或一组）属性经常在连接操作的连接条件中出现，则考虑在这个（或这组）属性上建立索引

关系上定义的索引数过多会带来较多的额外开销

维护索引的开销

查找索引的开销

确定数据库的存储结构

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

确定数据的存放位置和存储结构要综合考虑存取时间、存储空间利用率和维护代价3个方面的因素

事务

事务的概念

事务的基本概念

定义

是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位

事务和程序是两个概念

在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序

一个程序通常包含多个事务

事务是恢复和并发控制的基本单位

定义事务

显式定义方式

BEGIN TRANSACTION SQL 语句1 SQL 语句2 …… COMMIT

BEGIN TRANSACTION SQL 语句1 SQL 语句2 …… ROLLBACK

隐式定义方式

当用户没有显式地定义事务时，数据库管理系统按缺省规定自动划分事务

事务结束

COMMIT

事务正常结束

提交事务的所有操作（读+更新）

事务中所有对数据库的更新写回到磁盘上的物理数据库中

ROLLBACK

事务异常终止

事务运行的过程中发生了故障，不能继续执行

系统将事务中对数据库的所有已完成的操作全部撤销

事务滚回到开始时的状态

事务的特性

事务的ACID特性

原子性（Atomicity）

事务是数据库的逻辑工作单位

事务中包括的诸操作要么都做，要么都不做

一致性（Consistency）

事务执行的结果，必须是使数据库从一个一致性状态，变到另一个一致性状态

一致性状态

数据库中只包含成功事务提交的结果

不一致状态

数据库系统运行中发生故障，有些事务尚未完成就被迫中断

这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态

案例：银行转帐

情景描述

从帐号A中取出一万元，存入帐号B

定义事务

该事务包括两个操作

这两个操作要么全做，要么全不做

全做或者全不做，数据库都处于一致性状态

如果只做一个操作，用户逻辑上就会发生错误，少了一万元，数据库就处于不一致性状态

隔离性（Isolation）

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

一个事务内部的操作及使用的数据对其他并发事务是隔离的

并发执行的各个事务之间不能互相干扰

持续性（Durability）

持续性，也称永久性（Permanence）

一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其执行结果有任何影响。

事务的特性

保证事务ACID特性是事务处理的任务

破坏事务ACID特性的因素

多个事务并行运行时，不同事务的操作交叉执行

数据库管理系统必须保证多个事务的交叉运行不影响这些事务的隔离性

事务在运行过程中被强行停止

数据库管理系统必须保证被强行终止的事务对数据库和其他事务没有任何影响