数据库系统概论 第五版 王珊 笔记

数据库系统概论

第1章 绪论

1.1 数据库概述

1、四个基本概念：数据、数据库、数据库管理系统、数据库系统

数据：数据与其语义不可分；

数据库：是大量数据的集合，具有永久存储、有组织和可共享三个基本特点；

数据库管理系统：是计算机基础软件；具有数据定义、数据组织存储和管理、数据操纵、数据库的事务管理和运行管理、数据库的建立和维护等功能；

数据库系统：由数据库、数据库管理系统（及其应用开发工具）、应用程序和数据库管理员组成的存储、管理、处理和维护数据的系统。是一个整体的概念。

2、数据库技术的发展历程

人工管理阶段->文件系统阶段->数据库系统阶段

人工管理阶段：批处理；数据不保存、应用程序管理数据、数据不共享、数据不具有独立性

文件系统阶段：批处理，联机处理；数据可保存、文件系统管理数据、数据共享型差，冗余度大、数据独立性差

数据库系统阶段：批处理，联机处理，分布处理；数据结构化（与文件系统本质区别）、数据共享性高。冗余度低且易扩充、数据独立性高、数据由数据库管理系统统一管理和控制（安全性保护、完整性检查、并发控制，数据库恢复）

1.2 数据模型

定义：数据模型是对现实世界数据特征的抽象，用于描述数据、组织数据和对数据进行操作；是数据库系统的核心和基础。

组成要素：数据结构，数据操作和数据的完整性约束。

1、数据模型分类：概念模型，逻辑模型和物理模型

概念模型：主要用于数据库设计。

​ 概念：实体，属性，码，实体型，实体集，联系。

​ 一种表示方法：E-R图

逻辑模型和物理模型：

​ 逻辑模型：包括层次模型、网状模型、关系模型（重点）、面向对象模型和对象关系数据模型、半结构化数据模型；

​ 主要用于数据库管理系统的实现。

​ 物理模型：描述数据在系统内部的表示方法和存取方法，或在磁盘或磁带上的存储方式和存取方法。

2、关系模型：

1）数据结构：表

​ 术语：关系，元组，属性，码，域，分量，关系模式

2）数据操作：查询，插入，删除，更新

3）完整性约束：实体完整性，参照完整性，用户定义的完整性

1.3 数据库系统的结构

1、模式：模式是数据库中全体数据的逻辑结构和特征的描述

​ 理解：逻辑结构包括表结构和表间关系（不要和关系模式混淆，关系模式是对表结构的描述）

2、数据库三级模式结构

“由上到下”分别为外模式，（外模式/模式映像），模式，（模式/内模式映像），内模式

外模式（子模式or用户模式）：面向数据库用户；一个数据库可以有多个外模式，但一个应用程序只能使用一个外模式；外模式是保证数据库安全性的有力措施。

外模式/模式映像：保证数据的逻辑独立性。

模式：模式是数据库中全体数据的逻辑结构和特征的描述；一个数据库只有一个模式。

模式/内模式映像：保证数据的物理独立性。

内模式（存储模式）：是数据物理结构和存储方式的描述，始数据在数据库内部的组织方式；一个数据库只有一个内模式。

1.4 数据库系统的组成

数据库一般由数据、数据库、数据库管理系统、数据库系统构成。

相关概念：硬件平台及数据库，软件，人员

硬件平台及数据库：大内存，大磁盘，高通道能力。

软件：数据库管理系统、支持数据库管理系统的操作系统，高级语言及其编译系统（支持数据库接口），数据库应用开发工具，特定数据库应用系统。

人员：数据库管理员，系统分析员和数据库设计人员，应用程序员，用户。

第2章 关系数据库

2.1 关系数据结构

关系模型的数据结构为关系，表示为二维表

1、基本概念：域、笛卡尔积、元组、分量、关系、候选码、主码、主属性、全码

2、关系类型：基本关系（基本表），查询表，视图表（虚表，不对应实际存储数据）

3、基本关系性质：列同质，不同列可同域，列顺序无所谓，不同元组候选码不同，行顺序无所谓，分量不能取原子值（表中不能有表）

4、关系模式

关系的描述成为关系模型，关系是值，关系模型是型

关系模型是静态的、稳定的，关系是动态的、随时间变化的

5、关系数据库

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

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

​ **理解：**共性与特性，型为共性，值为特性；以人、国家作例子，国家等价于关系数据库的型（关系数据库模式），中国等价于关系数据库的值。国家包含人，人等价于关系模式，你我在国家里且属于人，等价于关系。

这里也可以拿具体的数据库做个例子（后续补充）

6、关系模型的存储结构

表（关系）是关系数据的逻辑模型；不同的关系数据库管理系统数据的物理组织可能不同。

2.2 关系操作

1、基本关系操作：查询操作，插入、删除、修改操作两大部分

2、查询操作：

选怎、投影、并、差、笛卡尔积5种基本操作，连接、除、交操作。

3、关系操作对象：集合（操作对象and结果）

4、关系操作语言分类：关系代数，关系演算（元组关系演算和域关系演算），结构化查询语言SQL（关系数据库标准语言）

2.3 关系完整性

1、实体完整性——关系数据库必须支持

若属性是基本关系的主属性，则该属性不能取空值。空值意味着“不知道”、“不存在”或者“无意义”

​ 理解：与现实时间联系，现实世界实体具有唯一性，比如说你和我，在数据库设计中体现为实体完整性。

2、参照完整性——关系数据库必须支持

若F是基本关系R的一组（个）属性，但不是关系R的码，K是基本关系S的主码，如果F与K相对应，则成F为R的外码。R为参照关系，S为被参照关系（目标关系）

​ 说明：1）F不能是关系R的码，但可以是关系R的主属性，这种情况是用于R的主码由多个属性构成；

​ 2）R和S可以为同一关系；

​ 3）关于F的值：要么为空值，要么必须等于S中某个元组的主码值；

​ 4）F和K属性名不要求一定相同。

3、用户定义的完整性

如限制域取值范围，要求分量唯一等，用于满足用用户的约束条件。

2.4 关系代数

1、集合运算符：并、差、交、笛卡尔积（广义）

2、专门的关系运算符：选择、投影、连接、除

选择运算符：符号：σ；操作：行操作；

投影运算符：符号：π；操作：列操作

连接运算符：符号：⋈；操作：行+列操作

​ 特殊连接：等值连接，自然连接

​ 外连接：外连接，左外连接，右外连接

除运算符：符号：÷；操作：行+列操作；

3、比较运算符和逻辑运算符和专门的关系运算符的关系：辅助专门的关系运算符

2.5 关系演算

略

第3章 关系数据库标准语言SQL

3.1 SQL概述

1、SQL特点：综合统一，高度非过程化，面向集合的操作方式，以同一种语法结构提供多种使用方式，语言简洁、易学易用。

2、SQL关系数据库三级模式结构

外模式：若干视图、部分基本表

模式：若干基本表

内模式：若干存储文件（存储文件的逻辑结构组成了关系数据库的内模式）

3.2 学生-课程数据库

共有三个表：

Student（Sno,Sname,Sage,Sdept）
Course(Cno,Cname,Cpno,Ccredit)
SC(Sno,Cno,Grade)

3.3 数据定义DDL

3.3.1 模式定义与删除

1）模式定义

语法

CREATE SCHEMA <模式名> AUTHORIZATION <用户名>[<表定义子句>|<视图定义子句>|<授权定义子句>];
#如果没有制定模型名则模式名隐含为用户名

栗子——为用户GAO定义学生-课程模式S-T

CREATE SCHEMA "S-T" AUTHORIZATION GAO;

2）模式删除

语法

DROP SCHMA <模式名> <CASCADE|RESTRICT>;
#CASCADE级联：删除模式同时删除模式下所有数据库对象（如表）
#RESTRICT限制：若模式下有数据库对象（如视图），则拒绝执行删除语句

例子——删除S-T模式

DROP SCHMA S-T ;

3）MySQL演示

#创建模式
CREATE DATABASE DB1;
#删除模式
DROP DATABASE DB1;

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

1、基本表定义

语法

CREATE TABLE <表名> (
		<列名> <数据类型> [列级完整性约束],
		<列名> <数据类型> [列级完整性约束],
		...
		[,<表级完整性约束>]
);
#列之间，表级完整性约束之间用“,”间隔，最后一句不用

例子——建立学生表

CREATE TABLE Student (
		Sno VARCHAR(10) PRIMARY KEY,
		Sname VARCHAR(10) UNIQUE,
		Ssex VARCHAR(2),
		Sage SMALLINT,
		Sdept VARCHAR(20)
);

2、基本表修改

语法

ALTER TABLE <表名>
[ADD [COLUMN] <新列名> <数据类型> [完整性约束]]
[ADD [表级完整性约束]]
[DROP [COLUMN] <列名> [CASCADE|RESTRICT]]
[DROP CONSTRAINT <完整性约束名> [RESTRICT|CASCADE]]
[ALTER COLUMN <列名> <数据类型>];
"""
DROP COLUMN 用于删除列，指定CASCADE则同时删除引用改列的其他对象（如视图）；指定RESTRICT，如果该列被其他对象引用，则拒绝执行删除语句
怎么修改列名？？？？？？？？？？？？？？？？？？？？？
"""

例子

增加课程名必须取唯一值的约束条件

ALTER TABLE Course ADD UNIQUE(Cname);

3、基本表删除

语法

DROP TABLE <表名> [RESTRICT|CASCADE];
"""
指定RESTRICT，则被删除的表不能被其他表的约束所引用（如外码FOREIGN KEY），不能有视图、触发器、存储过程、函数，否则该表不能被删除
指定CASCADE，该表删除无限制条件，且删除基本表同时，相关依赖对象都会被删除
具体的删除机制因数据库管理系统不同而不同
"""

例子——删除学生表

DROP TABLE Student CASCADE;

3.3.3 索引的建立与删除

1、建立索引

语法

CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
ON <表名>(<列名> [<次序>],<列名> [<次序>],...);
"""
UNIQUE：表明此索引的索引值与数据记录一一对应
CLUSTER：表示建立的是聚簇索引
次序指定索引值的排列次序，有ASC（升序）和DESC（降序）两种方式，默认升序
"""

例子——SC表按学号升序，课程号降序建立唯一索引

CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC);

2、删除索引

语法

DROP INDEX <索引名>;

例子——删除SCno

DROP INDEX SCno;

3.3.4 数据字典

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

3.4 数据查询

总语法

SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>]
FROM <表名或视图名> [,<表名或视图名>] | (<SELECT语句>) [AS] <别名>
[WHERE <条件表达式>]
[GROUP BY <列名 1> [HAVING <条件表达式>]]
[ORDER BY [列名 2] [ASC|DESC]];
"""
整个SELECT语句的含义是，根据WHERE子句的条件表达式从FROM子句指定的基本表、视图、派生表中找出满足条件的元组，再按SELECT子句中的目标列表表达式选择出元组中的属性值形成结果表
如果由GROUP BY子句，则将结果按<列名 1>的值进行分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。如果GROUP BY子句带HAVING短语，则只有满足条件的组才予以输出
如果有ORDER BY子句，则结果表还要按<列名 2>的值升序或降序排列
"""

3.4.1 单表查询

1、查询表中若干列

以Student表为例

#1）查询Sno和Sname列
SELECT Sno, Ssname FROM Student;

#2）查询所有列
SELECT * FROM Student;

#3）查询经过计算的值；
#SELECT子句中的<目标表达式>可以是表中的属性列，还可以是表达式
#下例中2014-Sage为查询视图的列名，属性值为2014减去各个元组在Sage列的分量
SELECT Sname, 2014-Sage FROM Student;

#SELECT子句中的<目标表达式>还可以是字符串常量、函数
#下列中常量'Year of Birth:'为新列名，列值均为'Year of Birth:'；LOWER(Sdept)使得所有系名小写显示
SELECT Sname,'Year of Birth:',2014-Sage,LOWER(Sdept) FROM Student;

#4）用户可以通过指定别名来改变查询结果的列标题
#下例中查询结果中列名2014-Sage被更换为BIRTHDAY
SELECT Sname,'Year of Birth:',2014-Sage BIRTHDAY,LOWER(Sdept) FROM Student;

2、选择表中的若干元组

1）消除值重复的元组,使用DISTINCT关键字，默认为ALL

SELECT DISTINCT Sno FROM SC;

2）查询满足条件的元组

①比较大小

#谓词：<,>,=,!=,!>,!<,<>
SELECT Sname FROM Student WHERE Sdept="CS";
SELECT Sname FROM Student WHERE Sage<20;

②确定范围

#谓词：BETWEEN...AND...,NOT BETWEEN...AND...
#可以用多个①中谓词和多重条件查询谓词实现
SELECT Sname FROM Student WHERE Sage BETWEEN 20 AND 25;
SELECT Sname FROM Student WHERE Sage NOT BETWEEN 20 AND 25;

③确定集合

#谓词：IN，NOT IN
#可以用多个①中谓词和多重条件查询谓词实现
SELECT Sname FROM Student WHERE Sdept IN("CS","MA");
SELECT Sname FROM Student WHERE Sdept NOT IN("CS","MA");

④字符匹配

#谓词：LIKE,NOT LIKE
#语法格式
[NOT] LIKE '<匹配串>' [ESCAPE'<换码字符>']
#匹配串可以是完整的字符串，可可以包含通配符%或者_；
#%代表任意长度字符串；_代表任意单个字符；
#注意：数据库字符集为ASCII时一个汉字需要两个_，GBK时只需要一个_。
#查询所有姓刘的学生的姓名、学号
SELECT Sname,Sno FROM Student WHERE Sname LIKE '刘%';
#如果用户要查询的字符串本身含有通配符%或者_，这是需要使用ESCAPE '转码字符'短语对通配符转义
#下例中的通配符_失效
SELECT Cno,Ccredit FROM WHERE Cname LIKE 'DB \_Design' ESCAPE'\';

⑤涉及空值的查询

#谓词：IS NULL,IS NOT NULL
SELECT Sno,Cno FROM SC WHERE Grade IS NULL;
SELECT Sno,Cno FROM SC WHERE Grade IS NOT NULL;

⑥多重条件查询

#谓词：AND,OR
SELECT Sname FROM Student WHERE Sage>20 AND Sage<30; 

SELECT Sname FROM Student WHERE Sdept IN("CS","MA");
#等价于
SELECT Sname FROM Student WHERE Sdept='CS' OR Sdept='MA'; 

3、ORDER BY子句

#按照系升序，年龄降序
SELECT * FROM Student ORDER BY Sdept, Sage DeSC;
#注意，对于表中有空值情况，可能最先显示也可能最后显示，由具体系统决定

4、聚集函数

#DISTINCT表示计算时取消重复值，ALL（默认值）相反
#当聚合函数遇到空值时，除COUNT(*)外，都跳过
#聚集函数只能用于SELECT子句和HAVING子句
COUNT(*)#统计元组个数
COUNT([DISTINCT|ALL] <列名>) #求一列中值的个数
SUM([DISTINCT|ALL] <列名>) #求一列中值的综合
AVG([DISTINCT|ALL] <列名>) #求一列值的平均值
MAX([DISTINCT|ALL] <列名>) #求一列中的最大值
MIN([DISTINCT|ALL] <列名>) #求一列中的最小值
SELECT COUNT(*) FROM Student;
SELECT COUNT(Sage) FROM Student;
SELECT SUM(Sage) FROM Student;
SELECT AVG(Sage) FROM Student;
SELECT MAX(Sage) FROM Student;
SELECT MIN(Sage) FROM Student;

5、GROUP BY子句

#GROUP BY 子句将查询结果按照某一列或多列的值分组，值相等的为一组
#分组目的是为了细化聚集函数的作用对象，分组后聚集函数将作用域每一个组，即每一个组都有一个函数值
#求各个课程号及相应的选课人数
SELECT Cno,Count(Sno) FROM SC GROUP BY Cno;
#查询选修了三门以上课程的学生学号
SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) > 3;

#区别WHERE和HAVING
作用对象不同：WHERE是基本表或视图；HAVING组（虽然说组也是一种视图，但这个视图来源于GROUP BY）
能否使用聚集函数：WHERE不能；HAVING能

3.4.2 连接查询

包括等值连接，自然连接，非等值连接，自身连接，外连接，复合条件连接，无条件连接

1、等值连接和非等值连接，自然连接，无条件连接，复合条件连接

连接谓词或连接条件：连接查询的where子句中用来连接两个表的条件称为连接条件

格式

#连接谓词中的列名称为连接字段，且各连接字段的类型必须可比
[<表名 1>.]<列名><比较运算符>[<表名 2>.]<列名>
[<表名 1>.]<列名>BETWEEN[<表名 2>.]<列名>AND[<表名 2>.]<列名>

比较运算符：=，<，>，>=，<=，<>，!=

当连接运算符为=时，称为等值连接，否则为非等值连接

例子：查询每个学生选课情况

SELECT student.*,sc.* FROM student,sc WHERE sc.Sno = student.Sno;
#或者
SELECT * FROM student,sc WHERE sc.Sno = student.Sno;

自然连接：在等值连接中把重复的属性列去掉则为自然连接

如去掉上述查询中的重复的Sno列

#关于表名前缀，用于区分两个表中的同名列
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM student,sc 
WHERE sc.Sno = student.Sno;

无条件连接：

不使用where查询条件，等价于两个表做笛卡尔积

SELECT * FROM student,sc;

复合条件连接：连接条件有多个

2、自身连接（重点）

表与表自己连接，称为表的自身连接。理解为复制一份相同的表，通过设置别名加以区分

例子：查询课程先修课程的先修课

注意：因为涉及同一个表，为区分，为同一个表设置不同的别名。

SELECT F.Cno,S.Cpno 
FROM course F,course S
WHERE F.Cpno = S.Cno;

3、外连接

主要作用或者说和等值连接、非等值连接，自然连接，无条件连接区别：保存悬浮元组

悬浮元组：即不满足匹连接条件，两个表连接后单个表中丢失的元组；外连接后在其他表属性值设NULL

'''
格式
左外连接：<表名 1> LEFT OUTER JOIN <表名 2> ON <条件>; 列出左边关系（表名 1）中的所有元组
右外连接：<表名 1> RIGHT OUTER JOIN <表名 2> ON <条件>; 列出右边关系（表名 2）中的所有元组
'''
SELECT Student.*,Cno 
FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno);
#等价于
SELECT Student.*,Cno 
FROM SC RIGHT OUTER JOIN Student ON (Student.Sno=SC.Sno);

3.4.3 嵌套查询

基本概念：

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

​ 嵌套查询：将一个查询块嵌套在另一个查询块的where子句或者having子句中的查询

​ 外层查询或父查询：上层查询块

​ 内层查询或子查询：下层查询块

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

​ 相关子查询：子查询的条件依赖父查询，对应查询语句称为相关嵌套查询

​ 注意：子查询不能用ORDER BY子句，ORDER BY子句只能父查询使用；

​ 有的嵌套查询可以被连接运算代替，有的不可以；

​ 实际应用中由于嵌套查询优化欠优，尽可能采用连接运算

1、带有IN谓词的子查询

子查询结果往往是一个集合

例子：查询与stu02在同一个系的学生

#基本思路，找出stu02所在的系，再判断其他学生的所在系是不是在前者的集合里面
SELECT Sname
FROM student
WHERE Sdept IN 
	(SELECT Sdept
	 FROM student
	 WHERE Sname="stu02");

2、带有比较运算符的子查询

指父查询与子查询之间用比较运算符进行连接，需要保证子查询返回的是单个值

例子：找出每个学生大于等于他自己选修课程平均成绩的课程号

#基本思路：先求出单个学生选修课程的平均成绩，再与其单科成绩比较，选出符合条件的
SELECT Cno
FROM sc X
WHERE Grade >= 
	(SELECT AVG(Grade)
	 FROM sc Y
	 WHERE X.Sno=Y.Sno);

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

与比较运算符结合使用

使用聚集函数查询效率通常更高，相较于ALL和ANY

ANY在这里表示“某个”的意思，而非“任意”

如 >ANY，指大于查询结果中的某一个，或者说大于查询结果中的最小值

翻译表：

	=	<> !=	<	<=	>	>=
ANY	IN	–	< MAX	<= MAX	> MIN	>=MIN
ALL	–	NOT IN	< MIN	<= MIN	>MAX	>=MAX

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

#基本思路：比任意一个小 等价于 比某个小 等价于 <ANY
SELECT Sname,Sage
FROM student
WHERE Sage <ANY(
	SELECT Sage
	FROM student
	WHERE Sdept="CS" )
AND Sdept<>"CS";
#等价于聚集查询，<ANY 等价于 小于最大值
SELECT Sname,Sage
FROM student
WHERE Sage <(
	SELECT MAX(Sage)
	FROM student
	WHERE Sdept="CS" )
AND Sdept<>"CS";

4、带有EXISTS谓词的子查询

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

由于带有EXISTS谓词的子查询不返回任何数据，所以由EXISTS引出的子查询，其目标列表表达式通常用*

1）EXISTS例子：查询所有选修了1001号课程的学生姓名

#基本思路：若学生选修了1号课程，那么查询结果不为空，即为true
SELECT Sname
FROM student
WHERE EXISTS(
	SELECT * 
	FROM sc
	WHERE sc.Cno="1001"
	AND sc.Sno=student.Sno);

2）NOT EXISTS例子：查询没有选修1001号课程的学生

#基本思路：若学生没有选修1001号课程，则返回结果为空，即为false，NOT EXISTS之后，变为true
SELECT Sname
FROM student
WHERE NOT EXISTS(
	SELECT * 
	FROM sc
	WHERE sc.Cno="1001"
	AND sc.Sno=student.Sno);

3）全称量词（难）：SQL中没有全称量词，通过逻辑演算表示全称量词

如查询选修了全部课程的学生 等价为 没有一门课程该学生没选

SELECT Sname
FROM student
WHERE NOT EXISTS(
	SELECT * 
	FROM course
	WHERE NOT EXISTS(
		SELECT * 
		FROM SC
		WHERE Sno=student.Sno
		AND Cno=course.Cno));

4）逻辑蕴含（难）：需要用到逻辑演算的知识，将要求进行转化，从而使用EXIST实现

如查询至少选修了学生10000002选修的全部课程的学生号码

等价于

不存在这样的课程y，学生y选了，而学生x没有选

SELECT Sno
FROM sc X
WHERE NOT EXISTS(
	SELECT *
	FROM sc Y
	WHERE Y.Sno="10000002"
	AND NOT EXISTS(
		SELECT * 
		FROM sc Z
		WHERE X.Sno=Z.Sno
		AND Y.Cno=Z.Cno));

3.4.4 集合查询

select语句的查询结果是元组的集合，集合操作主要包括并操作UNION，交操作INTERSECT，差操作EXCEPT

演示

#并操作
SELECT *
FROM sc
WHERE Cno='1001'
UNION 
SELECT * 
FROM sc
WHERE Cno='1002';
#交操作：MySQL不支持
SELECT *
FROM sc
WHERE Cno='1001'
INTERSECT
SELECT * 
FROM sc
WHERE Cno='1002';
#差操作：MySQL不支持
SELECT *
FROM sc
WHERE Cno='1001'
EXCEPT 
SELECT * 
FROM sc
WHERE Cno='1002';

3.4.5 基于派生表的查询

子查询还可以出现在from子句中，生成的临时派生表作为主查询的查询对象

注意：

​ 如果子查询中没有聚集函数，派生表可以不指定属性列，子查询中Select后的各属性名即为派生表的各属性名

​ 通过from子句生成派生表时，AS关键字可以省略，但必须为派生表关系指定一个别名

例子：查询每个学生超过他自己选修课程平均成绩的课程号

#mysql运行报错，含有聚集函数
SELECT Sno,Cno
FROM sc,(SELECT Sno,AVG(Grade) FROM 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;

3.4.6 select语句的一般格式

一般格式，目标列表达式可选格式，聚集函数一般格式，where子句的条件表达式

3.5 数据更新

插入，修改，删除

3.5.1 插入数据

1、插入元组

格式：

INSERT INTO <表名> [(<属性列 1>),(<属性列 2>),...]

VALUES(<常量 1>,<常量 2>,...)[,(<常量 1>,<常量 2>,...)];

例子：

INSERT INTO student
VALUES('10000010',"stu10","fm",'21','CS');

注意点：

​ 插入数据时，INTO子句中没有出现的属性列，新元组在这些列上取空值NULL；

​ 注意完整性约束，如NOT NULL，实体完整性约束等；

​ 不明确表示插入的属性列，默认插入所有属性列；

​ 属性列顺序可以不和建表时属性列顺序一致。

2、插入子查询结果

这样做可以一次插入大量元组

格式：

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

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

#建立新表，存储系名和平均年龄
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;

3.5.2 修改数据

基本格式：

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

1、修改一个元组的值

UPDATE student
SET Sage=19
WHERE Sno='10000002';

2、修改多个元组的值

#将所有学生年龄增加1岁
UPDATE student
SET Sage = Sage + 1;

3、带子查询的修改语句

#将计算机系所有学生成绩设为0
UPDATE sc
SET Grade = 0
WHERE Sno IN
	(SELECT Sno
	 FROM student
	 WHERE Sdept="CS");
#注意子查询中的表和父查询表不能相同，如下，会报错
UPDATE student
SET Sage = 18
WHERE Sno IN
	(SELECT Sno
	 FROM student
	 WHERE Sdept="CS");

3.5.3 删除数据

基本格式：

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

1、删除一个元组的值

DELETE 
FROM student
WHERE Sno='10000010';

2、删除多个元组的值

DELETE FROM Dept_age;

3、带子查询的删除语句

#删除计算机科学系所有学生的选课记录
DELETE 
FROM sc
WHERE Sno IN(
	SELECT Sno
	FROM student
	WHERE Sdept="CS");

3.6 空值处理

基本概念：

​ 空值：是“不知道”或“不存在”或“无意义”的值；空值是一个很特殊的值，含有不确定性

​ UNKNOWN：介于传统逻辑运算（TRUE和FALSE）之间的逻辑值；与true和false运算产生新的运算关系。P120页

1、空值产生：外连接，空值关系运算

2、空值判断：IS NULL 或者 IS NOT NULL

3、空值约束条件：用户自定义约束NOT NULL，主码

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

​ 空值与另一个值进行算术运算：结果空值

​ 空值与另一个值进行比较运算:：结果UNKNOWN

​ 在查询语句中，只有使where和having子句选择条件未TRUE的元组才会被选择

3.7 视图

基本概念：

​ 视图：是由一个或者几个基本表（视图）导出的表，是一个虚表（映射（我自己说的））

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

​ 创建视图时，只是存储了视图的定义，并不执行查询语句，在对视图查询时才执行查询语句

​ 行列子集视图：从单个基本表导出的，只是去掉了基本表的某些行列，保留主码的视图

​ 虚拟列：定义视图时可以定义一些派生属性，如出生年月；派生属性并不实际存储，以表达式形式存在；带虚 拟列多的视图也被称为带表达式的视图

​ 分组视图：用带有聚集函数和Group by自居的查询来定义视图，这种视图称为分组视图

3.7.1 定义视图

1、建立视图

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

注意：

​ 子查询可以是任意的select语句，具体要求视DBMS不同而不同；

​ WITH CHECK OPTION表示在对视图执行更新操作时需要满足视图定义中的子查询的谓词条件；

​ 组成视图的列名要么全部指定，要么全部不指定；

​ 目标列为聚集函数（MySQL可以使用）或者列表达式、出现同名列等情况时必须要指定视图所有的列名；

​ 使用SELECT *建立视图时，一旦基本表结构被改变，视图与基本表关系会被破坏，只能删除视图，然后重构视图

例子

CREATE VIEW CS_Student
AS
SELECT Sno,Sname
FROM student
WHERE Sdept='CS';

2、删除视图

基本格式：

DROP VIEW <视图名> [CASCADE]

注意：使用CASCADE会级联删除，删除语句会把视图以及它导出的所有视图一起删除；

​ 如果视图存在由它导出的视图，则不使用CASCADE无法删除该视图；

​ 基本表删除后，由它导出的所有视图均无法使用，但不会被自动删除。

3.7.2 查询视图

同基本表查询

注意：

​ 视图查询过程：从数据字典取出视图定义，把定义中的子查询和用户查询结合，转换为等价的对基本表查询，在执行修正后的查询。此过程又称视图消解（字面意思）；

​ 视图消解过程出现问题。目前多数关系数据库对行列子集视图均能进行正确转换，对非行列子集视图，则不一定。

3.7.3 更新视图

同基本表更新

注意：

​ 视图更新过程：由于视图是不是及存储数据的虚表，因此对视图的更新最终要装换为对基本表的更新

​ 若创建视图时使用WITH CHECK OPTION子句，那么视图更新时会检查是否满足视图定义中子查询子句中的条件

​ 并不是所有视图都可以更新；一般行列子集视图是可以更新的；各关系数据库一般只允许对行列子集视图进行更新。

3.7.4 视图的作用

简化用户操作，使用户能以多种角度看待统一数据，对重构数据库提供了一定程度的逻辑独立性，能够对机密数据提供安全保护，适当利用视图可以更清晰地表达查询

第4章 数据库安全性

4.1 数据库安全性概述

4.1.1 数据库不安全因素

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

如盗用密码

安全措施：用户身份鉴别，存取控制，视图

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

安全措施：强制存取控制，数据加密存储，加密传输，审计

3）安全环境的脆弱性

操作系统的脆弱性，网络协议安全保障不足等会造成数据库安全性破坏

安全措施：建立完善的可信标准即安全标准

4.1.2 安全标准

1、计算机安全标准：TCSEC和CC

2、TCSEC/TDI：将TCSEC扩展到数据库管理系统

​ 1）安全性指标划分：安全策略，责任，保证，文档

​ 2）对各项指标支持情况，按照系统可靠或可信程度逐渐增高：D,C1,C2,B1,B2,B3,A1

安全级别	定义	备注
A1	验证设计
B3	安全域	审计
B2	结构化保护	DAC，MAC
B1	标记安全保护	强制存取控制MAC，审计，真正意义上的安全产品
C2	受控的存取保护	实施审计和资源隔离
C1	自主安全保护	自主存取控制DAC
D	最小保护

3、CC

​ 1）信息技术安全性结构：把信息产品安全要求分类为安全功能要求和安全保证要求

​ 2）安全功能要求和安全保证要求结构：类-子类-组件

​ 3）CC评估保证级EAL的划分

评估保证级	定义	TCSEC安全级别
EAL1	功能测试
EAL2	结构测试	C1
EAL3	系统地测试和检查	C2
EAL4	系统的设计，测试和复查	B1
EAL5	半形式化设计和测试	B2
EAL6	半形式化验证的设计和测试	B3
EAL7	形式化验证的设计和测试	A1

4.2 数据库安全性控制

安全技术：用户身份鉴别，

多层存取控制：自主存取控制，强制存取控制，推理控制

审计，视图，数据加密

4.2.1 用户身份鉴别

种类：

​ 1）静态口令验证：如账号密码登录

​ 2）动态口令鉴别：如短信验证码

​ 3）生物特征鉴别：如指纹识别

​ 4）智能卡识别：如用智能卡刷门禁

4.2.2 存取控制

1、存取控制机制组成：定义用户权限，合法权限检查

2、方法：

​ 自主存取控制（C1级别）：从用户角度出发。用户可以自主地决定将数据的存取权限授予何人，决定是否也将“授权”地权限给予别人，因此称这样的存取控制是自主存取控制。

​ 强制存取控制（B1级别）：从数据库对象出发

4.2.3 自主存取控制

1、用户权限组成：数据库对象和操作类型

2、存取控制对象：数据（如基本表中的数据，属性列数据），数据库模式（包括模式，基本表，视图和索引）

3、权限

存取控制对象	操作类型
模式	CREATE SCHEMA
基本表	CREATE TABLE, ALTER TABLE
视图	CREATE VIEW
索引	CREATE INDEX
基本表和视图（数据）	SELECT, INSERT, UPDATE, DELETE, REDERENCES, ALL PRIVILEGES
属性列（数据）	SELECT, INSERT, UPDATE,REFERENCES,ALL PRIVILEGES

注意：给用户授权列INSERT时，一定要包含主码的INSERT权限，否则用户的插入动作会因为主码为空而被拒绝

4.2.4 授权

授权：授予GRANT与收回REVOKE

1、GRANT

GRANT <权限> [,<权限>]
ON <对象类型> <对象名> [,<对象类型> <对象名>]
TO <用户> [,<用户>]
[WITH GRANT OPTION];#若指定[WITH GRANT OPTION]，则被授权用户可以把这种权限再授予其他用户，否则只能					 #使用，而不能传播该权限；且不允许循环授权

演示：

# 把查询Student表的权限授权给用户U1
GRANT SELECT
ON TABLE Student
TO U1;
# 把查询Student表的权限授权给所有用户
GRANT SELECT
ON TABLE Student
TO PUBLIC;
# 把查询Student表和修改Sno列权限给用户U4
GRANT UPDATE(Sno) SELECT
ON TABLE Student
TO U4;
# 传播授权
GRANT SELECT
ON TABLE Student
TO U5
WITH GRANT OPTION;
# 此时用户U5可以把查询Student表的权限给其他用户
GRANT SELECT
ON TABLE Student
TO U6;

2、REVOKE

REVOKE <权限> [,<权限>]
ON <对象类型> <对象名> [,<对象类型> <对象名>]
FROM <用户> [,<用户>] [CASCADE|RESTRICT];#CASCADE级联回收权限；若指定RESTRICT，如要回收用户U5地权										  #限，因为U5给U6授予权限，那么系统拒绝回收U5权限

演示：

# 收回用户U4对Student表Sno列的修改权
REVOKE UPDATE(Sno)
ON TABLE Student
FROM U4;
#收回所有用户对Student表的所有权限
REVOKE ALL PRIVILEGES
ON TABLE Student
FROM PUBLIC;

4.2.5 数据库角色

用于批量授予/回收用户权限

1、创建角色

CREATE ROLE <角色名>;

2、给角色授权

GRANT <权限> [,<权限>]
ON <对象类型> <对象名> [,<对象类型> <对象名>]
TO <角色> [,<角色>]

3、将一个角色授予/收回其他的角色或用户

#这样角色所拥有的权限，被赋予角色地用户也全部拥有
# 授予
GRANT <角色> [,<角色>]
TO <用户> [,<用户>]
[WITH GRANT OPTION];
# 回收
REVOKE <角色> [,<角色>]
FROM <用户> [,<用户>]

4、角色权限收回

REVOKE <权限> [,<权限>]
ON <对象类型> <对象名> [,<对象类型> <对象名>]
FROM <角色> [,<角色>]；
# 一个问题：如果收回了角色的权限，那么具备该角色身份地用户权限是否也被收回？

4.2.6 强制存取控制方法

1、主体与客体：在强制存取控制中，数据库管理系统所管理的全部实体被分为主体和客体

​ 主体：是系统中的活动实体。包括用户和用户进程

​ 客体：受主体操纵，包括文件，基本表，索引，视图等

​ DBMS为主体和客体实例分配敏感度标记。主体的敏感度标记称作许可证级别，客体敏感度标记称为密级

2、敏感度标记：

​ 级别：绝密TS，机密S，可信C，公开P

​ 规则：主体许可证级别大于等于客体密级，主体可读取客体

​ 主体许可证级别小于等于客体密级，主体可写入客体

3、实现强存取控制要先实现自主存取控制。自主存取控制和强存取控制共同构成数据库管理系统的安全机制

4.3 视图机制

视图可以限制用户访问数据的范围；

视图机制直接或间接的实现支持**存取谓词（即存取控制）**的用户权限定义。

4.4 审计

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

2、设置审计

# 对修改SC表结构或修改SC表数据的操作进行审计
AUDIT ALTER,UPDATE
ON SC

3、取消审计

# 取消对修改SC表结构或修改SC表数据的操作进行审计
NOAUDIT ALTER,UPDATE
ON SC

4.5 数据加密

1、基本概念：

​ 明文：原始数据

​ 密文：明文变化为不可直接识别的格式

2、数据加密分类：

​ 1）存储加密：

​ 透明加密：内核级加密保护方式

​ 非透明加密：多个加密函数实现

​ 2）传输加密：

​ 链路加密：在数据链路层对报文和报头均加密

​ 端到端加密：只加密报文

4.6 其他安全性保护

推理控制：处理强制存取控制未解决的问题，避免用户利用其能够访问的数据推知更高密级的数据

隐蔽信道：处理强制存取控制未解决的问题

数据隐私保护：设计是数据收集，存储，处理和发布的各个阶段

第5章 数据库完整性

1、数据库完整性：指数据的正确性和相容性。详细是指数据的正确相容和有效

2、数据库完整性和安全性区别：

​ 对象不同：完整性是防止数据库中存在不合语义的，不正确的的数据，对象是数据

​ 安全性是防止非法用户和非法操作，防止其对数据库数据的非法存取，对象是人

3、数据库完整性功能要求：

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

2）提供完整性检查的方法

3）进行违约处理

4、EDBMS完整性约束定义实现类别：

​ 列级完整性约束：即完整性约束跟随在列定义之后，约束一个列属性

​ 表级完整性约束：同列定义并列定义，可以约束多个列属性

​ 完整性约束命名子句：既可以跟在列定义之后，也可以和列定义并列定义

5.1 实体完整性

5.1.1 定义实体完整性

演示：

# 列级定义主码
CREATE TABLE Sno(
	Sno VARCHAR(10) PRIMARY KEY,
	Sname VARCHAR(10));
# 表级定义主码
CREATE TABLE Sno(
	Sno VARCHAR(10),
	Sname VARCHAR(10),
	PRIMARY KEY(Sno));

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

更新操作时检查：

1）主码值是否唯一，如果不唯一则拒绝插入和修改

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

5.2 参照完整性

5.2.1 定义参照完整性

演示

CREATE TABLE SC(
	Sno VARCHAR(10),
	Cno VARCHAR(10),
	PRIMARY KEY(Sno,Cno),
	FOREIGN KEY(Sno) REFERENCES Student(Sno)
	FOREIGN KEY(Cno) REFERENCES Course(Cno)); #表级定义参照完整性

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

实质：参照表和被参照表互相限制

1）参照表插入数据时，需要外码值与被参照表对应，当：

​ 外码同时是参照表主属性时，不能为空且值必须在被参照表中存在

​ 外码非参照表主属性，可以为空

否则拒绝执行

2）参照表修改数据时，若修改对象为外码，则修改后的值需要在被参照表中存在

否则拒绝执行

3）被参照表删除数据时，若被删除数据属性值在参照表中，

则拒绝执行或级联删除或设置参照表中该属性值为空值

4）被参照表更新数据时，若被修改数据属性在参照表中

则拒绝执行或级联修改或设置参照表中该属性值为空值

综上，参照表的违约处理全部拒绝，被参照表的违约处理可能拒绝执行/级联操作/设置空值

5.3 用户定义的完整性

5.3.1定义用户定义的完整性

1）约束条件

列值非空NOT NULL，列值唯一UNIQUE，检查列值是否满足一个条件表达式（CHECK短语）

2）演示

#NOT NULL
CREATE TABLE teacher(
	Tno VARCHAR(10) PRIMARY KEY,
	Tname VARCHAR(10) NOT NULL);
#UNIQUE
CREATE TABLE teacher(
	Tno VARCHAR(10) PRIMARY KEY,
	Tname VARCHAR(10) UNIQUE);
#CHECK短语
CREATE TABLE teacher(
	Tno VARCHAR(10) PRIMARY KEY,
	Tname VARCHAR(10) UNIQUE,
	Tage SMALLINT,
	CHECK (Sage < 200));
#或者
CREATE TABLE teacher(
	Tno VARCHAR(10) PRIMARY KEY,
	Tname VARCHAR(10) UNIQUE,
	Tage SMALLINT CHECK (Sage < 200));
#CHECK约束多个列属性
CREATE TABLE teacher(
	Tno VARCHAR(10) PRIMARY KEY,
	Tname VARCHAR(10) UNIQUE,
	Tsal NUMERIC(7,2),
	Tage SMALLINT,
	CHECK (Sage < 200 OR Tsal < 100000));

5.4 完整性约束命名子句

1）格式

CONSTRAINT <完整性约束条件名> <完整性约束条件>

好处：可以在创建完表后，通过ALTER可以增加或者删除表的约束条件

2）演示

CREATE TABLE teacher(
	Tno VARCHAR(10) 
		CONSTRAINT C1 PRIMARY KEY,
	Tname VARCHAR(10) UNIQUE);
#or
CREATE TABLE teacher(
	Tno VARCHAR(10),
	Tname VARCHAR(10) UNIQUE),
	CONSTRAINT C1 PRIMARY KEY(Tno);

3）修改表的完整性限制

①删除表的约束条件

ALTER TABLE Student
	DROP CONSTRAINT C3;

②增加表的约束条件

ALTER TABLE Student
	ADD CONSTRAINT C3 CHECK(Sage < 40);

第6章 关系数据库理论

6.1 问题的提出

1、第一范式 1NF

作为一个二维表，关系要符合一个最基本的条件，每一个分量必须是不可分的数据项

即表中不能有表

2、数据依赖

是一个关系内部属性与属性之间的一种约束关系

是语义的体现

分类：函数依赖和多值依赖，其他依赖

3、关系模式可能存在的问题

1）数据冗余：如属性值大量重复出现

2）更新异常：数据冗余，导致数据库维护代价大

3）插入异常：

4）删除异常：

6.2 规范化

6.2.1 函数依赖

1、函数依赖定义

设R(U)是属性集U上的关系模式，X，Y是U的子集，若对于R(U)的任意一个可能关系r，r中不可能存在两个元组在X上属性值相等，而在Y属性值不等，则称X函数确定Y，或Y函数依赖于X，记作X->Y，X也称决定因素

判断依据：语义；站在X的视角

tip：函数依赖是指R中一切关系均要满足的约束条件

2、函数依赖分类：

1）非平凡函数依赖

X->Y，但Y不包含与X

2）平凡函数依赖

X->Y，但Y包含于X，这个显然成立

3）完全函数依赖

X->Y，对于X的任意一个真子集X‘,都有X不确定Y

4）部分函数依赖

X->Y，但Y不完全函数依赖X

5）传递函数依赖

X->Y，Y不属于X，Y不确定X，Y->Z，Z不属于Y

6.2.2 码

1、候选码：

对于R(U,F)，若U完全依赖于K，则K为候选码

2）超码

对于R(U,F)，若K->U，则K为超码

3）主属性：

候选码中的属性

4）主码：

选定的候选码

5）全码：

对于R(U,F)，U完全依赖于K，且K=U

6）码：

主码和候选码简称

6.2.3 范式

1、1NF、2NF、3NF、BCNF、4NF、5NF

2、规范化：低一级范式的关系模式通过模式分解，可以转化为若干高一级范式的关系模式的集合

6.2.4 2NF

1、定义：

若R属于1NF，且每一个非主属性完全函数依赖于任何一个候选码，则R也属于2NF

tip：候选码定义是，整个U完全函数依赖于候选码

6.2.5 3NF

1、定义：

R(U,F)属于1NF，若R中不存在这样的码X，属性组Y和非主属性Z（Z不属于Y）使得X->Y，Y->Z，Y不确定X，则R属于3NF

tip：

1）若R属于3NF，则每一个非主属性既不能传递依赖于码，也不部分依赖于码

2）若R属于3NF，则R属于2NF

2、3NF的不彻底性

表现在可能存在主属性对码的部份依赖和传递依赖

6.2.6 BCNF

1、定义：

R(U,F)属于1NF，若X->Y且Y不属于X时X必含有码，则R属于BCNF

tip：

1）BCNF说明R中每一个决定因素都包含码

2 ）满足BCNF的关系模式有：

​ 所有非主属性对每一个码完全函数依赖

​ 所有主属性对每一个不包含它的码都是完全函数依赖

​ 没有任何属性完全函数依赖于非码的任何一组属性

3）R属于BCNF，则R属于3NF

6.2.7 多值依赖

1、定义：

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

tip：站在X的角度；判断依据：语义

2、分类：

平凡多值依赖

X->->Y，Z为空，则X->->Y为平凡多值依赖

3、多值依赖属性

1）对称性

X->->Y，则X->->Z

2）传递性

X->->Y，Y->->Z，则X->->Z-Y

3）函数依赖是多值依赖特殊情况

X->Y，则X->->Y

tip：利用了函数依赖的任意性

4、函数依赖与多值依赖基本区别

1）多值依赖有效性与属性集范围有关，即X->->Y在W上成立，W真包含于U，则在U上不一定成立

函数依赖只要满足定义，在任何属性集上都成立

2）X->Y成立，则对于任何Y’，Y’真包含于Y，X->Y’成立

X->->Y成立，但对于任何Y’，Y’真包含于Y，X->->Y不一定成立

6.2.9 规范化小结

1NF：

2NF：消除非主属性对码部分函数依赖

3NF：消除非主属性对码传递函数依赖

BCNF：消除主属性对码部分函数依赖和传递函数依赖

4NF：消除非平凡且非函数依赖的多值依赖

第7章 数据库设计

7.1 数据库设计概述

​ 数据库设计定义

​ 数据库设计是指对于一个给定的应用环境，构造优化的数据库逻辑模式和物理结构，并据此建立数据库及其应用系统，使之能够有效地存储和管理数据，满足各种用户的应用需求，包括信息管理要求和数据操作要求

7.1.1 数据库设计的特点

1、数据库建设的基本规律：三分技术，七分管理，十二分基础数据

2、结构（数据）设计和行为（处理）设计相结合：设计过程中要把数据库结构设计和对数据处理设计密切结合起来

7.1.2 数据库设计方法

​ 新奥尔良方法、基于E-R模型的设计方法、3NF的设计方法、面向对象的数据库设计方法、统一建模语言UML方法

7.1.3 数据库设计的基本步骤

1、各阶段

​ 需求分析阶段

​ 概念结构设计阶段

​ 逻辑结构设计阶段

​ 物理结构设计阶段

​ 数据库实施阶段

​ 数据库运行和维护阶段

2、需求分析阶段和概念结构设计阶段独立于任何DBMS

​ 逻辑结构设计阶段和物理结构设计阶段与选用的DBMS密切相关

3、数据库设计参加人员

​ 系统分析人员、数据库设计人员、应用开发人员、数据库管理员和用户代表

7.1.4 数据库设计过程中的各级模式

1、概念结构设计形成概念模式，如E-R图

2、逻辑结构设计阶段确定数据库数据模型，如关系模型，形成逻辑模式，考虑其他要求和安全性，形成外模式

3、物理结构设计阶段形成数据库内模式

7.2 需求分析

7.2.1 需求分析的任务

1、需求分析任务：

​ 1）通过调查，明确用户各种需求

​ 2）充分考虑今后可能的扩充和改变

2、调查重点：数据和处理

​ 通过调查获取用户对数据库的信息要求（可导出数据要求）、处理要求、安全性与完整性要求

7.2.2 需求分析方法

1、调查用户需求步骤

​ 1）调查组织机构情况

​ 2）调查各部门业务活动情况

​ 3）协助用户明确对新系统的各种要求

​ 4）确定新系统的边界

2、常用调查方法

​ 跟班作业、开调查会、请专人介绍、询问、设计调查表请用户填写、查阅记录

3、需求分析流程

​ 1）调查了解用户需求

​ 2）分析和表达用户需求，使用结构化分析方法SA

​ 3）将需求分析报告提交给用户，获取用户认可

7.2.3 数据字典

​ 数据字典是关于数据库中数据的描述。包括数据项、数据结构、数据流、数据存储和处理过程

1、数据项

​ 是不可再分的数据单位

​ 数据项描述={数据项名，数据项含义说明、别名、数据类型、长度、取值范围、取值含义、与其他数据项逻辑关系、数据项之间的关系} 9项

其中取值范围和与其他数据项逻辑关系定义了数据的完整性约束条件

2、数据结构

​ 反映数据间的组合关系

​ 数据结构描述={数据结构名、含义说明、组成：{数据项或数据结构}}

3、数据流

​ 是数据结构在系统内传输的路径

​ 数据流描述={数据流名、说明、数据流来源、数据流去向、组成：{数据结构}、平均流量、高峰流量}

4、数据存储

​ 是数据结构停留或保存的地方

数据存储描述={数据存储名、说明、编号、输入的数据流、输出的数据流、组成：{数据结构}、数据量、存取频度、存取方式}

5、处理过程

​ 处理过程的具体处理逻辑一般用判定表和判定树来描述

​ 处理过程描述={处理过程名、说明、输入：{数据流}，输出：{数据流}，处理：{简要说明}}

处理过程只负责描述用来做什么，而非怎么做

7.3 概念结构设计

​ 将需求分析得到的用户需求抽象为信息结构（即概念模型）的过程就是概念结构设计

7.3.1 概念模型

概念模型特点：

​ 1）能真实、充分反映现实世界

​ 2）易于理解

​ 3）易于更改

​ 4）易于向关系、网状、层次等各种数据模型转换

7.3.2 E-R模型

​ E-R模型用于描述现实世界的概念模型

1、实体间的联系

​ 1）两个实体型之间的联系：一对一、一对多、多对多

​ 2）两个以上实体型之间的联系：一对一、一对多、多对多

3）单个实体型内的联系：一对一、一对多、多对多

联系的度：把参与联系的实体型的数目称为联系的度。两个实体型之间的联系度为2，也成为二元关系

2、E-R图

规范：

​ 1）实体型用矩形表示

​ 2）属性用椭圆型表示

​ 3）联系用菱形表示。若联系有属性，要将联系和属性用无向边连接起来

7.3.5 概念结构设计

1、实体与属性的划分原则

​ 1）作为属性，不能再具有需要描述的性质。即属性必须是不可分的数据项

​ 2）属性不能与其他实体具有联系

2、大型系统分析：自顶向下进行需求分析，自底向上设计概念结构

3、E-R图的集成

​ 两步走

​ 1）合并：解决分E-R图的冲突，将分E-R图合并生成初步E-R图

​ （1）三类冲突：

​ ①属性冲突：包括属性域冲突，属性取值单位冲突

​ ②命名冲突：包括同名异义，异名同义

​ ③结构冲突：包括

​ 同一对象在不同应用中具有不同的抽象

​ 同一实体在不同子系统的E-R图中所包含的属性和属性排列次序不完全相同

​ 实体间的联系在不同的E-R图中为不同的类型：如多对多变一对多

​ 2）修改和重构：消除不必要冗余，生成基本E-R图

​ （1）在初步E-R图中可能存在一些冗余的数据和实体间冗余的联系，容易破坏数据库的完整性，给数据库维 护增加困难

​ ①冗余数据：可由基本数据导出的数据

​ ②冗余联系：可由其他联系导出的联系

​ （2）两种消除冗余方法：分析方法和规范化理论

​ （3）并不是所有的冗余数据和冗余联系都需要被消除，冗余信息有可能提高工作效率

7.4 逻辑结构设计

7.4.1 E-R图向关系模型的转换

1、转换问题：如何将实体型和实体间的联系转换为关系模式，如何确定这些关系模式的属性和码

2、转换一般原则

​ 1）1：1联系

​ ①可以转换为一个独立的关系模式，即建立第三张表

​ ②也可以与任意一端对应的关系模式合并，如一把钥匙对应一把锁，可以把钥匙ID加入到锁所在的关系模式，反之也可 以

​ 2）1：n联系

​ ①可以转换为一个独立的关系模式，即建立第三张表

​ ②可以与n端对应的关系模式合并，如一把钥匙对应多把锁，可以把钥匙ID加入到锁所在关系模式，反之不可以

​ 3）n：n联系

​ ①只可以转换为一个独立的关系模式，即建立第三张表

​ 4）三个或三个以上实体间的多元联系：可以转换为一个独立的关系模式

​ 5）具有相同的码的关系模式可以合并

7.4.2 关系模型的优化

1、关系数据模型的优化通常以规范化理论为指导

​ 1）确定数据依赖。包括关系模式内部和关系模式之间的数据依赖

​ 2）对关系模式间的数据依赖处理。极小化、消除冗余联系

​ 3）分析关系模式属于第几范式

​ 4）确定哪些关系模式需要合并或分解

​ 注意：并不是规范化程度越高的关系越优

​ 5）对关系模式进行分解

​ ①水平分解：把基本关系的元组分为若干个子集和，定义每个子集和为一个子关系

​ ②垂直分解：把关系模式R的属性分解为若干个子集和，形成若干个子关系模式。需确保无损连接性和保持函数依赖

7.4.3 设计用户子模式

1、子模式即外模式

2、考虑用户哪些习惯

​ 1）使用更符合用户习惯的别名

​ 2）对不同级别用户定义不同视图，以保证系统的安全性

​ 3）简化用户对系统的使用

7.5 物理结构设计

​ 数据库物理结构：存储结构和存取方法，依赖于选定的DBMS

​ 数据库物理设计：为一个给定的逻辑数据模型选取一个适合应用要求的物理结构的过程

​ 数据库物理设计步骤：

​ 1）确定数据库物理结构：在RDBMS中即确定存储结构和存取方法

​ 2）对物理结构进行评价：重点为时间和空间效率

7.5.1 数据库物理设计的内容和方法

1、确定关系的存取方法的依据

​ 1）查询事务：查询条件涉及的属性、连接条件涉及的属性、查询的投影属性

​ 2）更新事务：被更新关系、每个关系上的更新操作条件涉及的属性、修改操作要改变的属性值

​ 3）每个事务在各关系上运行的频率和性能要求

2、数据库物理设计的内容：为关系模式选取存取方法、以及设计关系、索引等数据库文件的物理存储结构

7.5.2 关系模式存取方法选择

1、常用的存取方法：索引、聚簇

2、索引

​ 选择索引存取方法即根据应用要求确定对关系的那些属性列建立索引、那些属性列建立组合索引、那些索引设计为唯一索引

​ 关系上的索引并不是越多越好，因为系统需要为维护索引付出代价

​ 1）B+树索引存取方法（普遍使用）

​ 2）hash索引存取方法：适用于属性主要出现在等值连接条件、等值比较选择条件的情况

3、聚簇

​ 为提高某个属性（组）的查询速度，把这个或这些属性上具有相同值的元组集中存放在连续的物理块中称为聚簇，该属性（组）称为聚簇码

​ 一个数据库可以建立多个聚簇，一个关系只能加入一个聚簇

​ 建立与维护聚簇开销相当大，因此聚簇码值要相对稳定

​ 当通过聚簇码进行访问或来连接是该关系的主要应用，与聚簇码无关的其他访问很少时，可以使用聚簇

​ 当SQL语句包含ORDER BY、GROUP BY、UNION、DINTINCT等子句或短语时，使用聚簇特别有利

7.5.3 确定数据库的存储结构

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

​ 需要考虑存取时间、存储空间利用率、维护代价三个方面的因素

1、确定数据的存放位置

​ 根据应用情况将数据分为易变部分与稳定部分、经常存取部分和存取频率较低部分分开存储

2、确定系统配置

​ 初步调整系统配置变量

7.5.4 评价物理结构

​ 评析物理数据库方法完全依赖于所选用的关系数据库管理系统

7.6 数据库的实施和维护

7.6.1 数据的载入和应用程序的调试

​ 数据库应用程序的设计应该与数据库设计同时进行

7.6.2 数据库的试运行

​ 小部分数据入库

​ 测试修改应用程序

​ 测试系统性能指标

​ 试运行基本合格后，大批量输入数据，逐步完成运行评价

​ 做好数据库的转储和恢复工作

7.6.3 数据库的运行和维护

1、数据库的转储和恢复

2、数据库安全性、完整性控制

3、数据库性能监督、分析和改造

4、数据库的重组织与重构造

​ 重组织不修改原设计的逻辑和物理结构，而数据库的重构造则不同。它是指部分修改数据库的模式和内模式

第8章 数据库编程

主语言、主变量、游标、动态SQL

第9章 关系查询处理和查询优化

9.1 关系数据库系统的查询处理

9.1.1 查询处理步骤

​ 查询分析、查询检查、查询优化、查询执行

1、查询分析

​ 对查询语句进行扫描、词法分析和语法分析

​ 识别SQL关键字、属性名、关系名 #注意不检查是否正确

​ 检查查询语句语法是否符合语法规则

2、查询检查

​ 检查关系名、属性名等是否存在和有效

​ 将视图操作转化为基本表操作

​ 用户权限和完整性约束检查

​ 检查完毕后将查询语句转化为关系代数表达式

​ 用查询树或称语法分析树表示扩展的关系代数表达式

3、查询优化

​ 分为代数优化和物理优化

​ 1）代数优化：等价转换更高效的关系代数表达式，优化关系代数表达式

​ 2）物理优化：存取路径和底层操作算法的选择

4、查询执行

​ 优化器生成查询执行计划，代码生成器生成执行查询计划的代码，然后执行

9.1.2 实现查询操作的算法示例

1、选择操作

​ 1）算法：全表扫描，索引扫描

​ 2）选择率较低时采用索引扫描，选择率较高时采用全表扫描

2、连接操作（等值连接）

​ 1）嵌套循环算法：双重循环

​ 2）排序—合并算法：先排序再扫描，扫描一遍表就可以实现连接操作

​ 3）索引连接：类似嵌套循环，但是内循环不是全表扫描，而是根据索引扫描

​ 4）hash join算法：分为划分阶段和连接阶段

9.2 关系数据库系统的查询优化

​ 关系表达式的语义级别很高，使RDBMS可以从关系表达式分析语义，执行查询优化

9.2.1 查询优化概述

1、查询优化的优点不仅在于用户不必如何考虑最好地表达查询以获得较高的效率，而且在与系统可以比用户程序的“优化”做的更好。

2、查询执行总代价=I/O代价 + CPU代价 + 内存代价 + 通信代价

​ 其中通信代价在分布式数据库中存在

9.2.2 一个实例

​ P280

9.3 代数优化

9.3.1 关系代数表达式等价变换规则

1）连接、笛卡尔积的交换律 2）连接、笛卡尔积的结合律 3）投影的串接定律

4）选择的串接定律 5）选择与投影的交换律 6）选择与笛卡尔积的交换律

7）选择与并的分配律 8）选择与差的分配律 9）选择对自然连接的分配律

10）投影与笛卡尔积的分配律 11）投影与并的分配律

9.3.2 查询树的启发式优化

启发式规则：

​ 1）选择运算尽可能先做

​ 2）把投影运算和选择运算同时进行

​ 3）把投影同其前后的双目运算符结合起来

​ 4）把某些选择同在他前面要执行的笛卡尔积结合起来成为一个连接运算

​ 5）找出公共子表达式

案例P285

9.4 物理优化

1、物理优化方法

​ 1）基于规则的启发式优化

​ 2）基于代价估算h的优化

​ 3）两者结合的优化方法

9.5 查询计划的执行

​ 查询计划执行分为自顶向下（被动）和自底向上（主动）两种执行方式。

第10章 数据库恢复技术

10.1 事务的基本概念

1、事务：是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位

2、定义事务的语句：

BEGIN TRANSACTION;
COMMIT;
ROLLBACK;

3、事务ACID特性

​ 原子性、一致性、隔离性、持续性

4、事务是恢复和并发控制的基本单位

10.2 数据库恢复概述

​ 恢复子系统是数据库管理系统的一个重要组成部分，对数据库系统的可靠程度起着决定性作用，是衡量系统性能的重要指标。

10.3 故障的种类

1、事务内部故障：事务撤销

2、系统故障：事务撤销与事务重做；又称为软故障

3、介质故障：又称为硬故障。

4、计算机病毒

5、数据库回复基本原理：冗余

10.4 恢复的实现技术

​ 最常用的技术是数据转储和登记日志文件

10.4.1 数据转储

1、转储方式：海量转储和增量转储

2、转储状态：动态转储和静态转储

3、转储分类：动态海量转储、动态增量转储、静态海量转储、静态增量转储

10.4.2 登记日志文件

1、日志文件是用来记录事务对数据库更新操作的文件

2、日志文件分为：以记录为单位的日志文件和以数据块为单位的日志文件

3、登记日志文件原则：

​ 1）登记次序严格按并发事务执行的时间次序

​ 2）必须先写日志文件，后写数据库

10.5 恢复策略

1、事务故障恢复

​ 恢复子系统利用日志文件撤销事务对数据库进行的修改

​ 反向扫描日志文件

2、系统故障恢复

​ 重做与撤销

​ 正向扫描日志文件

3、介质故障

​ 重装数据库，重做已完成的事务

10.6 具有检查点的恢复技术

1、检查点记录的内容

​ 1）建立检查点时所有正在执行的事务清单

​ 2）这些事务最近一个日志记录的地址

2、使用检查点可以改善恢复效率

3、系统使用检查点方法进行恢复的步骤

​ p303图

10.7 数据库镜像

1、好处：

​ 1）应对介质故障

​ 2）并发操作

第11章 并发控制

​ 交叉并发方式：并行事务的并行操作轮流交叉运行

​ 同时并发方式：实现多个事务真正的并行运行

11.1 并发控制概述

1、事务是并发控制的基本单位

2、并发操作带来的数据不一致行

​ 1）丢失修改

​ 2）不可重复读：同一事务内两次读取的统一数据值不一样

​ 3）读“脏”数据：“脏”数据即不正确的数据

3、并发控制：用正确的方式调度并发操作，使一个用户事务的执行不受其他事务的干扰

4、并发控制主要技术：封锁、时间戳、乐观控制法、多版本并发控制

11.2 封锁

1、封锁使并发控制一个非常重要的技术

2、基本封锁类型：排他锁又称写锁（X锁）。共享锁又称读锁（S锁）

11.3 封锁协议

1、封锁协议定义：约定的规则，关于何时申请X锁或S锁、持锁时间、何时释放等，这些规则称为封锁协议

2、封锁协议：

​ 1）一级封锁协议：事务T在修改数据R之前必须先对其加X锁，直到事务结束才释放

​ 特点：可以防止“丢失修改”，但不能防止“不可重复读”和读“脏”数据

​ 2）二级封锁协议：在一级封锁协议的基础上增加事务T在读取数据R之前必须先对其加S锁，读完后可释放S锁

​ 特点：可以防止“丢失修改”和读“脏”数据，但不能防止“不可重复读”

​ 3）三级封锁协议，在一级封锁协议的基础上增加事务T在读取数据R之前必须先对其加S锁，事务结束后可释放

​ 特点：可以防止“丢失修改”和读“脏”数据，“不可重复读”

11.4 活锁和死锁

11.4.1 活锁

1、定义：某个事务T可能永远等待的情景称为活锁

2、解决策略：采用先来先服务的策略

11.4.2 死锁

1、定义：两个事务互相等待对方，永远不能结束，形成死锁

2、死锁解决策略：预防或诊断解除

3、预防死锁：

​ 1）一次封锁法：要求每个事务必须一次将所有要使用的数据全部加锁，否则就不能继续执行

​ 缺点：降低并发度

​ 2）顺序封锁法：预先对数据对象规定一个封锁顺序，所有事务都按这个顺序实施封锁

​ 缺点：维护资源封锁顺序困难，成本高；很难按规定的顺序施加封锁

总结：预防死锁不适合数据库

4、死锁的诊断和解除

​ 1）超时法：一个事务等待时间超过规定时间，就认为发生了死锁

​ 缺点：规定时间过短，可能误判死锁；规定时间过长，死锁发生后不能即使发现

​ 2）等待图法：并发控制子系统周期性生成事务等待图并进行检测，若图中出现回路，则表示系统中出现了死锁。发现死锁后，选择一个处理死锁代价最小的事务，将其撤销，释放此事务持有的所有的锁，使其他食物得以继续运行下去。

11.5 并发调度的可串行性

​ 正确的并发事务的调度：串行调度

11.5.1 可串行化调度

1、可串行化调度：多个事务并发执行是正确的，当且仅当其结果与按某一次序穿行的执行这些事务时的结果相同

可串行性是并发事务正确调度的准则

11.5.2 冲突可串行化调度

1、冲突操作是指不同的事务对同一个数据的读写操作和写写操作

​ 不同事务的冲突操作和同一事务的两类操作是不能交换次序的

2、冲突可串行化：一个调度Sc在保证冲突次序不变得情况下，通过交换两个事务不冲突操作的次序的到另一个调度Sc` ，如果Sc`是串行得，称带哦都Sc为冲突可串行化得调度

3、若一个调度是冲突可串行化，则一定是可串行化的调度；反之可串行化的调度不一定是冲突可串行化

11.6 两端锁协议

1、DBMS通过两段锁（2PL）协议实现并发调度的可串行性，从而保证调度得正确性

2、两段锁：分为两个阶段，第一阶段获得封锁，也称为扩展阶段；第二阶段释放封锁，也成为收缩阶段，此阶段不能申请任何锁

​ 若并发执行的所有事物均遵守两段锁协议，则对这些事务的任何并发调度策略都是可串行化的

3、事务遵守两段锁协议是可串行化调度的充分条件，而不是必要条件

4、区分两段锁协议和防止死锁的一次封锁法

​ 两段锁协议保证可串行化调度，可能发生死锁；一次封锁发保证不发生死锁。二者适用于不同方面

11.7 封锁的粒度

1、封锁对象的大小称为封锁粒度

2、封锁对象：逻辑单元：属性值，属性值集合，元组，关系，索引项，索引，数据库

物理单元：页，物理记录

3、封锁粒度与系统的并发度和并发控制的开销密切相关：

​ 封锁粒度越大，并发度越小，并发控制开销越小；反之越大

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

5、选择封锁粒度时，应该同时考虑封锁开销和并发度两个因素

11.7.1 多粒度封锁

1、多粒度树：根节点粒度大，子节点粒度小，叶子节点粒度最小

2、多粒度封锁协议允许多粒度树中的每个结点被独立的加锁，对一个结点加锁意味着这个结点的所有后裔结点也被加以同样类型的锁。

3、显式封锁与隐式封锁

​ 1）显式封锁是应事务的要求直接加到数据对象上的锁

​ 2）隐式封锁是该数据对象没有被独立枷锁，是由于其上级结点加锁而使该数据对象加上了锁

4、意向锁：有了意向锁，DBMS系统就无需逐个检查下一级结点的显式封锁

11.7.2 意向锁

1、意向锁的含义是如果对一个结点加意向锁，则寿命该节点的下层结点正在被加锁。对某一个结点加锁时，必须先对它的上层结点加意向锁

2、三类意向锁：意向共享锁（IS锁），意向排他锁（IX锁），共享意向排他锁（SIX）

3、意向锁好处：提高了系统并发度，减少了加锁和解锁的开销