电子科大~数据库系统原理与开发期末复习完整版_电子科技大学数据库期末复习

第一章 数据库系统概论

• 在数据库管理系统的层次结构中，下面哪个层次负责对数据文件进行操作访问?
  A.操作界面层 B.语言翻译处理层 C.数据存取层 D.数据存储层

  根据处理对象的不同，数据库管理系统的层次结构由外至内依次为应用层、语言翻译处理层、数据存取层、数据存储层。

  (1)应用层：是数据库管理系统与终端用户和应用程序的界面，负责处理各种数据库应用，如使用结构化查询语言SQL发出的事务请求或嵌入通用的程序设计语言的应用程序对数据库的请求。

  (2)语言处理层：由DDL编译器、DML编译器、DCL编译器、查询器等组成，负责完成对数据库语言的各类语句进行词法分析、语法分析和语义分析，生成可执行的代码。此外，还负责进行授权检验、视图转换、完整性检查、查询优化等。

  (3)数据存取层：将上层的集合操作转换为对记录的操作，包括扫描、排序、查找、插入、删除、修改等，完成数据的存取、路径的维护以及并发控制等任务。

  (4)数据存储层：由文件管理器和缓冲区管理器组成，负责完成数据的页面存储和系统的缓冲区管理等任务，包括打开和关闭文件、读写页面、读写缓冲区、页面淘汰、内外存交换以及外层管理等。

• 在数据库领域技术中，下面哪种技术可以实现数据集成?
  A.数据库技术 B.数据仓库技术 C.数据挖掘技术 D.商业智能技术

  数据仓库 (Data Warehouse) 简称DW，存储大量数据的集成中心。数据仓库的目的是构建面向分析的集成化数据环境，为企业提供决策支持（Decision-Support）。它为企业提供一定的BI（商业智能）能力，指导业务流程改进、监视时间、成本、质量以及控制。数据库挖掘可建立在数据仓库基础上进行数据分析处理。

• 下面哪种数据库系统应用结构适合银行业务系统?
  A.集中式结构 B.客户/服务器结构 C.分布式结构 D.以上结构都可以
  分布式数据库的应用场景主要特征是海量并发，所以理论说，业务规模越大，使用分布式数据库的需求也就越迫切。

• MySql——应用广泛的开源关系数据库管理系统，不是企业级DBMS，PostgreSQL——技术领先的开源对象-关系数据库管理系统

• 下面哪个程序实现PostgreSQL服务器起停控制？pg_ctl

  初始化数据库
  pg_ctl init
  启动数据库
  pg_ctl start
  关闭数据库
  pg_ctl stop
  重启数据库
  pg_ctl restart

• 下面哪类数据库是功能最强大的数据库?
  A.MySQL B.PostgreSQL C.Oracle Database D.SQL Server
  Oracle Database——甲骨文公司推出的企业级数据库管理系统

• SQLite数据库可以在手机中运行

• 用户程序可以直接访问数据库文件吗？
  用户程序通常不能直接访问数据库文件。一般来说，用户程序需要通过应用程序向数据库管理系统（DBMS）发送请求，然后由DBMS解析请求并从数据库中检索所需的数据，最后将数据返回给应用程序，以便用户可以使用它们。这样做可以保证数据的安全性和完整性。

• NOSQL数据库适用于存储非结构化或半结构化数据的场景。

• 元数据（Metadata），又称中介数据、中继数据，为描述数据的数据（data about data），主要是描述数据属性（property）的信息，用来支持如指示存储位置、历史数据、资源查找、文件记录等功能。业务规则数据在数据库中也是一种元数据。正确

• 下面哪类数据库应用系统需要使用数据分析处理技术？决策支持

• 记录（Record）
  表中的每一行称为一个记录，它由若干个字段组成．
  字段（Field）
  也称域．表中的每一列称为一个字段．每个字段都有相应的描述信息，如数据类型、数据宽度等

第二章 数据库关系模型

实体完整性是指关系表的属性组成必须是完整的。错误！！

自然连接是一种等值连接。正确！！

代理键是为了唯一标识关系的不同元组，需要在表单或报表中显示出来。错误！！

关系表：

表中每行存储实体的一个实例数据表中每列包含实体的一项属性数据表中单元格只能存储单个值
不允许有重复的行
不允许有重复的列列
顺序可任意
行顺序可任意

笛卡尔积：

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

复合键(Compound Key)——是指关系中用来唯一标识元组的多列作为键。(学号,课程编号)就是一组复合键。

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

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

主键的作用：

唯一标识关系表的每行（元组)
与关联表的外键建立联系，实现关系表之间连接
数据库文件使用主键值来组织关系表的数据存储
数据库使用主键索引快速检索数据

代理键——采用DBMS自动生成的数字序列作为关系表的主键。
代理键有什么用途?
由DBMS自动生成的数字序列作为主键，可替代复合主键，以便获得更高性能的数据访 问操作处理。

关系模式语句中主键表示方法:关系名（主键属性，属性2，，属性x)

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

集合运算操作包括选择(select)、投影（project)、连接（join) 、交(intersection）、并(union)、差(difference）等。
专门关系操作包括数据行插入(Insert) 、修改(Update）、删除(Delete）操作。

以下是老师给的答案，有些许小错误，提供个思路就可以啦~

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

完整性约束作用:

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

保持关联表的数据一致性

实现业务数据规则

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

• 实体完整性约束

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

实体完整性约束规则:

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

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

• 参照完整性约束

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

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

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

• 用户自定义完整性约束

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

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

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

定义列的缺省值

定义列是否允许取空值

定义列取值唯一性

定义列之间的数据依赖性

从关系的数学定义来看，关系是各个域的笛卡尔集合。(×)

应该是子集

1. 关系模型由三个部分组成：数据结构，操作集合和完整性约束。

   (1)关系数据结构:在关系模型中,现实世界的实体以及实体间的各种联系均用单一的结构类型即关系来表示。

   (2)关系操作集合:关系模型中常用的关系操作包括查询操作和插入、删除、修改操作。

   (3)关系完整性约束:关系模型中有实体完整性约束、参照完整性约束和用户定义的完整性约束三类约束。

2. 在关系模型中，对关系有几种基本的数据操作方式，包括选择，投影，连接，除法等。这些操作可以用来查询、更新和控制关系数据。

3. 关系数据查询中的选择运算是从关系中选择满足特定条件的元组。投影运算是从关系中选择特定属性列。连接运算是根据两个关系中共同属性的值相等来组合两个关系中的元组。

4. θ连接(内连接)是指在两个关系上执行一个比较运算符θ的连接操作。θ为“=”的连接运算称为等值连接，自然连接是一种特殊的等值连接（两个关系中进行比较的分量必须是相同的属性组），它在连接后自动删除重复的列。

5. 左外连接返回左表中所有记录和右表中匹配记录；如果右表中没有匹配记录，则结果为NULL。右外连接返回右表中所有记录和左表中匹配记录；如果左表中没有匹配记录，则结果为NULL。全外连接返回左表和右表中所有记录；如果其中一个表没有匹配记录，则结果为NULL。

第三章 数据库操作SQL语言

• SQL语言是一种标准的数据库操作语言，可以支持所有数据库访问操作（错）

  主流的关系型数据库管理系统均支持SQL标准语言实现数据库操作

• 下面哪种数据类型不是PostgreSQL支持的数据类型?
  A.json B.line C. macaddr D. String
  PostgreSQL支持json，line和macaddr数据类型。但是，它不支持名为String的数据类型。在PostgreSQL中，可以使用text或varchar数据类型来存储字串。

数据定义语句（DDL）

创建数据库：

create databse CourseDB;
alter database CourseDB rename to courseDB;
drop database courseDB;
1
2
3

定义基本表 CREATE TABLE

CREATE TABLE 表名();

列级完整性约束条件

PRlMARY KEY //主码:当只有一个主码时，可直接在对应的属性列标注
NOT NULL //非空:表示该属性列不能取空值
UNlQUE //唯一值: 表示该属性列只能取唯一值
CHECK(条件) //检查: 检查该列是否满足某个条件，如 CHECK(某属性>20)

列完整约束

CREATE  TABLE  Course
( CourseID  	char(4)      PRIMARY  Key,
  CourseName  	varchar(20)  NOT  NULL  UNIQUE,
  CourseType  	varchar(10)  NULL CHECK(CourseType IN('基础课','专业课','选修课')),
  TestMethod  	char(4)      NOT  NULL  DEFAULT  '闭卷考试'
);
1
2
3
4
5
6

复合主键

CREATE  TABLE  Plan
( CourseID  	char(4)  	NOT  NULL,
  TeacherID  	char(4)  	NOT  NULL,
  CONSTRAINT	CoursePlan_PK	PRIMARY Key(CourseID,TeacherID)
);
1
2
3
4
5

使用表约束定义主键的优点:便于定义复合主键，可命名主键约束，便于定义代理键。

代理键

CREATE  TABLE  Plan
( CoursePlanID	serial		NOT  NULL,
  CONSTRAINT	CoursePlan_PK	PRIMARY Key(CoursePlanID)
);
1
2
3
4

外键,级联删除

CREATE  TABLE  Register
( CourseRegID  	serial	NOT  NULL,
  CoursePlanID  	Int  		NOT  NULL,
  StudentID  	char(13),
  Note  		varchar(30),
  CONSTRAINT	CourseRegID_PK	PRIMARY Key(CourseRegID),
  CONSTRAINT	CoursePlanID_FK	FOREIGN Key(CoursePlanID)
	REFERENCES  Plan(CoursePlanID)
    ON DELETE CASCADE,
);
1
2
3
4
5
6
7
8
9
10

表修改

alter table <name> add <columnName> <type> <constraint>;
1

ALTER TABLE<表名> DROP  COLUMN <列名>;
ALTER TABLE<表名> DROP  CONSTRAINT <列名>;
ALTER TABLE <表名> RENAME TO <新表名>;
ALTER TABLE <表名> RENAME <原列名> TO <新列名>;
ALTER TABLE <表名> ALTER  COLUMN <列名> TYPE<新的数据类型>;
1
2
3
4
5

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

索引优点:

可快速连接关联表
减少分组和排序时间
提高关系表数据检索速度

索引局限:

索引会占用额外存储空间
创建和维护索引都需要较大时间开销
数据操纵因维护索引带来系统性能开销

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

CREATE INDEX Birthday_Idx ON STUDENT (Birthday) ;
1

修改索引
例在学生信息表Student中，将原索引Birthday_ldx更名为Bday_ldx，其索引修改SQL 语句如下

ALTER INDEX Birthday_Idx RENAME T0 Bday_ldx ;
1

删除索引

DROP  INDEX bday_idx;
1

数据操纵语句（DML）

插入

INSERT INTO Student VALUES('2017220101105','赵东','女','1999-04-23','软件工程', 'liuyin@163.com');
1

更新

UPDATE  Student
SET  Email='zhaodong@163.com'
WHERE   StudentName='赵东';
1
2
3

删除

DELETE 
FROM  STUDENT
WHERE   StudentName='赵东';
1
2
3

清空

TRUNCATE TABLE STUDENT;
1

数据查询语句（DQL）

先分组再排序！！！

SELECT  [ALL|DISTINCT]  <目标列>[，<目标列>…]
[ INTO <新表> ]
FROM  <表名|视图名>[，<表名|视图名>…]
[ WHERE  <条件表达式> ]
[ GROUP BY  <列名> [HAVING <条件表达式> ]]
[ ORDER BY  <列名> [ ASC | DESC ] ];
1
2
3
4
5
6

1）使用BETWEEN…AND关键词来限定列值范围，还可以使用关键词LIKE与通配符来限定查询条件。
2)使用通配符来限定字符串数据范围。下划线（_）通配符用于代表一个未指定的字符。百分号(%）通配符用于代表一个或多个未指定的字符。

SELECT * FROM STUDENT
WHERE BirthDay BETWEEN '2000-01-01' AND '2000-12-30';
SELECT * FROM STUDENT WHERE Email LIKE '%@163.com ' ;
1
2
3

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

SELECT COUNT(*) AS 学生人数 FROM Student;
1

例2:找出STUDENT表中年龄最大和年龄最小的学生出生日期，其查询SQL语句如下:

SELECT Min (Birthday) AS 最大年龄,Max (Birthday) AS 最小年龄 FROM Student;
1

分组：

having 是对于 group by 的列的条件约束,除了group外,列名称不能和聚合函数一起用

SELECT  Major  AS 专业,  COUNT（StudentID） AS 学生人数
FROM  Student
WHERE  StudentGender=’男’
GROUP  BY  Major
HAVING  COUNT(*)>2;
1
2
3
4
5

子查询：

SELECT  TeacherID,  TeacherName,  TeacherTitle
FROM  Teacher
WHERE  CollegeID  IN
        (SELECT  CollegeID  
     FROM  College
     WHERE  CollegeName=’计算机学院’);
1
2
3
4
5
6

多表关联查询：

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

SELECT  B.CollegeName AS 学院名称,  A.TeacherID  AS 编号, A.TeacherName  AS 姓名,  A.TeacherGender  AS 性别,  A. TeacherTitle  AS 职称
FROM  Teacher  AS  A，College  AS  B
WHERE  A.CollegeID=B.CollegeID 
ORDER  BY  B.CollegeName, A.TeacherID;
1
2
3
4

在选课管理数据库中，希望查询所有开设课程的学生选课情况，包括课程名称、任课教师、选课学生人数。这需要关联课程信息表COURSE、教师信息表TEACHER、开课计划表PLAN、选课注册信息表REGISTER。其连接查询的SQL语句如下：

SELECT C.CourseName AS 课程名称, T.TeacherName AS 教师, 
  COUNT (R.CoursePlanID)  AS 选课人数
FROM  COURSE  AS  C  JOIN  PLAN  AS  P  ON  C.CourseID=P.CourseID 
  JOIN  TEACHER  AS  T  ON  P.TeacherID=T.TeacherID
  JOIN  REGISTER  AS  R  ON  P.CoursePlanID=R.CoursePlanID
GROUP  BY C.CourseName, T.TeacherName;
1
2
3
4
5
6

在上面的内连接查询中，只能找出有学生注册的课程名称和选课人数，但不能找出没有学生注册的课程名称。
在SQL应用中，有时候也希望输出那些不满足连接条件的元组数据。这时，可使用JOIN–ON外连接方式实现。其实现方式有三种形式，具体如下:

LEFT JOIN:左外连接，即使没有与右表关联列值匹配，也从左表返回所
有的行。
RIGHT JOIN:右外连接，即使没有与左表关联列值匹配，也从右表返回
所有的行。
FULL JOIN:全外连接，同时进行左连接和右连接，就返回所有行。

对A表和B表进行关联查询，哪种连接可以使B表中不匹配的元组数据可以出现在结果集中?
A. left join B. right join C. full join D. inner join

在选课管理系统数据库中，希望能查询所有开设课程的学生选课情况，包括课程名称、任课教师、选课学生人数。这需要关联课程信息表COURSE、开课计划表CPLAN、教师信息表TEACHER、选课注册信息表REGISTER。若使用左外连接查询，该JOIN–ON连接查询的SQL语句如下:

SELECT C.CourseName AS 课程名称, T.TeacherName AS 教师, 
COUNT  (R.CoursePlanID)  AS 选课人数
FROM  COURSE  AS  C  JOIN  PLAN  AS  P  
ON  C.CourseID=P.CourseID 
JOIN  TEACHER  AS  T  ON  P.TeacherID=T.TeacherID
LEFT  JOIN  REGISTER  AS  R  ON  P.CoursePlanID=R.CoursePlanID
GROUP  BY C.CourseName, T.TeacherName;
1
2
3
4
5
6
7

数据控制语句（DCL）

授予，收回，拒绝权限

GRANT  SELECT, INSERT, UPDATE, DELETE  ON  REGISTER  TO  RoleS;
REVOKE  DELETE  ON  REGISTER  FROM  RoleS;
DENY  DELETE  ON  TEACHER  TO  RoleT;
1
2
3

视图是一个虚拟的表，它是从一个或几个基本表（或视图）导出的表。它包含行和列，就像一个真实的表一样。视图本身并不包含任何数据，它只包含映射到基表的一个查询语句。当基表数据发生变化时，视图数据也随之变化。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，也可以提交数据，就像这些来自于某个单一的表一样。数据库的设计和结构不会受到视图中的函数、where 或 join 语句的影响。

视图的优点：

1. 使用视图简化复杂SQL查询操作
2. 使用视图提高数据访问安全性
3. 提供一定程度的数据逻辑独立性通过视图，可提供一定程度的数据逻辑独立性。当数据表结构发生改变，只要视图结构不变，应用程序可以不作修改。
4. 集中展示用户所感兴趣的特定数据通过视图，可以将部分用户不关心的数据进行过滤，仅仅提供他们所感兴趣的数据。

创建视图

CREATE  VIEW  BasicCourseView  AS
SELECT  CourseName,  CourseCredit,  CoursePeriod,  TestMethod
FROM    COURSE
WHERE  CourseType=‘基础课’;
1
2
3
4

查询视图：

SELECT * FROM BasicTeacherInfoView ORDER BY 所属学院,教师姓名;
1

删除视图：

DROP VIEW<视图名>;
1

下面哪项不是SQL语言的特性?
A.对数据库进行操作
B.实现控制逻辑编程
C.数据库游标操作
D.数据库事务操作

实现控制逻辑编程 不是 SQL 语言的特性。SQL 语言是一种用于管理关系数据库的语言，它可以用来对数据库进行操作、数据库游标操作和数据库事务操作。但它不是一种通用的编程语言，不能用来实现控制逻辑编程。

NUMERIC数据类型是一种精确数字数据类型，其精度在算术运算后保留到最小有效位，numeric是标准sql的数据类型，格式是numeric(m，n）。numeric(a，b)函数有两个参数，前面一个为总的位数，后面一个参数是小数点后的位数，例如numeric（5，2）是总位数为5，小数点后为2位的数，也就是说这个字段的整数位最大是3位。

在数据库中，使用更多索引可以加快数据库处理速度。（x）
索引可以加快查询速度，但不能加快处理速度

针对视图 Create view stuView as select * from student; 可以进行 D.以上均可 操作访问。这个视图定义了一个查询语句，它从 student 表中选择所有列和所有行。因此，可以对这个视图执行 SELECT、INSERT INTO 和 DELETE 操作。
针对视图 Create view stuView as select stuname, major from student; 可以进行 A. Select,C. delete 操作访问。这个视图定义了一个查询语句，它从 student 表中选择 stuname 和 major 列。由于这个视图只包含部分列，因此不能对其执行 INSERT INTO 操作。

第四章 数据库设计与实现

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

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

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

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

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

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

实体联系的实例数量称为基数(Cardinality) 。

联系：实体间的联系，关联的实体数目称为联系度数

实体联系类型

• 数量关系：1：1、1：N、M：N
• 必要性：可选，强制。

在E-R模型中，根据弱实体在语义上对强实体依赖程度的不同，弱实体又分为标识符依赖弱实体和非标识符依赖弱实体两类。如果弱实体的标识符中包含了所依赖实体的标识符，则该弱实体称为标识符依赖弱实体。

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

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

1:1实体联系：

当带有实体继承联系的E-R模型图转换关系模型时，首先父实体和子实体都各自转换为表，其属性均转换为表的列。在处理实体继承联系转换时，将父表中的主键放置到子表中，既做主键又做外键。

数据库规范化设计目的：

• 减少数据库中的冗余数据，尽量使同一数据在数据库中仅保存一份，有效降低维护数据一致性的工作量。
• 设计合理的表间依赖关系和约束关系，便于实现数据完整性和一致性。
• 设计合理的数据库结构，便于系统对数据高效访问处理。

完全函数依赖与部分函数依赖

例1：对于关系R（X, Y, N, O, P），其中（X, Y）为复合主键，若其它属性N,O,P都完整依赖于该复合主键，则称关系R为完全函数依赖。反之，其它属性N,O,P仅依赖于X，或仅依赖于Y，则称R为部分函数依赖。

函数传递依赖

例2：对于关系R（X, N, O, P），其中X为主键，若属性N依赖于X，而X不依赖于N，属性O依赖于N。则属性O函数传递依赖于X。

多值函数依赖

例3：对于教学关系R（课程, 教师, 课程参考书），一门课程可以有多个任课教师，也可以有多本参考书；每个任课教师可以任意选择他的参考书。该关系存在多值函数依赖。

如果X、Y是1:1的联系，则X↔Y。
如学号联系电话，即知道了学号，就可以在表中确定其联系电话;同样地，知道了联系电话，也可以在表中确定其学号。

如果X，Y是n :1的联系，则X→Y。
如学号-班号，即知道了学号，就可以在表中确定其班号;相反地，如果知道了班号，却无法确定学号。

如果X、Y是m : n的联系，则X和Y不存在函数依赖关系。
如学号和课程号没有函数依赖关系。即知道了学号，无法在表中确定课程号;同样地，如果知道了课程号，也无法确定学号。

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

一般在我们设计关系型数据库的时候，最多考虑到BCNF就够。符合高一级范式的设计，必定符合低一级范式，例如符合2NF的关系模式，必定符合1NF。

关系模式”和“关系”的区别，类似于面向对象程序设计中”类“与”对象“的区别。”关系“是”关系模式“的一个实例，你可以把”关系”理解为一张带数据的表，而“关系模式”是这张数据表的表结构。

• 第一范式：关系表中属性不可再细分。

  

  

• 第二范式：消除部分函数依赖，满足第一范式。

  对于关系R（X, Y, N, O, P），其中（X, Y）为复合主键，N,O,P这三个非键属性都不存在只依赖A或只依赖B情况，则该关系满足第2范式,反之,不满足第2范式。

  

  对于**（学号，课名） → 姓名**，有 学号 → 姓名，存在部分函数依赖。
  对于**（学号，课名） → 系名**，有 学号 → 系名，存在部分函数依赖。
  对于**（学号，课名） → 系主任**，有 学号 → 系主任，存在部分函数依赖。

  

  

• 第三范式：切断属性传递依赖，满足第二范式。

  接下来我们看看上表中的设计，是否符合3NF的要求。

  对于选课表，主码为（学号，课名），主属性为学号和课名，非主属性只有一个，为分数，不可能存在传递函数依赖，所以选课表的设计，符合3NF的要求。

  对于学生表，主码为学号，主属性为学号，非主属性为姓名、系名和系主任。因为 学号 → 系名，同时 系名 → 系主任，所以存在非主属性系主任对于码学号的传递函数依赖，所以学生表的设计，不符合3NF的要求。

  

  

• 巴斯-科德范式(BCNF)：所有属性函数依赖的决定因子都是候选键。在3NF基础上，任何主属性不能对主键子集依赖（在3NF基础上消除主属性对主码子集的依赖）

  举例：

  1. 某公司有若干个仓库；
  2. 每个仓库只能有一名管理员，一名管理员只能在一个仓库中工作；
  3. 一个仓库中可以存放多种物品，一种物品也可以存放在不同的仓库中。每种物品在每个仓库中都有对应的数量。

  那么关系模式 仓库（仓库名，管理员，物品名，数量） 属于哪一级范式？

  答：已知函数依赖集：仓库名 → 管理员，管理员 → 仓库名，（仓库名，物品名）→ 数量
  主属性：仓库名、管理员、物品名
  非主属性：数量
  ∵ 不存在非主属性对码的部分函数依赖和传递函数依赖。∴ 此关系模式属于3NF。

  但是存在主属性【仓库名】对于码（管理员，物品名）的部分函数依赖。

  解决办法就是要在 3NF 的基础上消除主属性对于码的部分与传递函数依赖。

  仓库（仓库名，管理员）
  库存（仓库名，物品名，数量）

• 第四范式：消除多值函数依赖，满足巴斯-科德范式

  

关系的规范化程度依次提升:1NF→2NF →3NF→BCNF→ 4NF
关系的规范化程度越高，关系数据库存储的冗余数据就越少，可消除的数据访问异常就越多。不过关系的规范化程度越高，分解出来的关系表就越多，但在数据查询访问时，需关联更多关系表，其数据库处理效率会降低。

综合例题：

所谓逆规范化，就是适当降低规范化范式约束，不再要求一个关系表必须达到很高的规范化程度，而是允许适当的数据冗余性，以获取数据访问性能。
逆规范化处理的基本方法:
(1)增加冗余列或派生列
(2)多个关系表合并为一个关系表

在E-R模型中，一个实体的其他关联实体数量称为什么？联系度数

在E-R模型图中，在定义一个实体时，必须指定它的标识符。 错误！！

第五章 数据库管理

数据库管理概论

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

为什么需要数据库管理?

• 数据库系统随规模增大,系统会变得异常复杂
• 多用户数据库应用带来数据库访问复杂性
• 数据安全和数据隐私对机构和用户都非常重要
• 数据库系统随数据量增加和使用时间增长其性能会降低
• 系统遭遇意外事件，数据库损坏或数据丢失

数据库管理目标

• 保障数据库系统正常稳定运行
• 充分发挥数据库系统的软硬件处理能力
• 确保数据库系统安全和用户数据隐私性
• 有效管理数据库用户及其角色权限
• 解决数据库系统性能优化、系统故障与数据损坏等问题
• 最大程度地发挥数据库对其所属机构的作用

在数据库中，事务(Transaction）是指针对单个业务处理功能的一组数据库访问操作，要求它们要么都成功执行，要么都不执行。在数据库系统中，事务是DBMS执行的最小任务单元。同时，事务也是DBMS最小的故障恢复任务单元和并发控制任务单元。

事务ACID特性:

原子性（Atomicity):事务所有操作在数据库中要么全部执行，要么全部不执行。
一致性(Consistency) :事务多次执行，其结果应一致。
隔离性（lsolation):事务与事务之间隔离，并发执行透明。
持续性(Durability ) :事务完成后，数据改变必须是永久的。

事务SQL

• BEGIN 或 START TRANSACTION ；事务开始语句
• ROLLBACK ；事务回滚语句
• COMMIT ；事务提交语句
• SAVEPOINT ；事务保存点语句

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

START TRANSACTION;
INSERT  INTO college( collegeID, collegename)VALUES ('004', '外语学院');
INSERT  INTO college( collegeID, collegename)VALUES ('005', '数学学院');
INSERT  INTO college( collegeID, collegename)VALUES ('006', '临床医学院');
COMMIT;
1
2
3
4
5

在事务过程中禁止使用的SQL语句：

创建数据库 CREATE DATABASE
修改数据库 ALTER DATABASE
删除数据库 DROP DATABASE
恢复数据库 RESTORE DATABASE
加载数据库 LOAD DATABASE
备份日志文件 BACKUP LOG
恢复日志文件 RESTORE LOG
授权操作 GRANT

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

为什么需要并发控制？

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

并发控制–事务调度

目的

• 支持并发事务处理，使更多用户并行操作，提高系统的并发访问能力。
• 保证一个事务工作不会对另一个事务工作产生不合理的影响。

并发控制需解决的问题：

• 丢失更新数据：对共享数据进行非锁定资源的读写操作。
• 脏数据读取：事务读取一个被取消持久化的共享数据。（事务读到的数据最后被回滚了，读到的是假数据）
• 不可重复读取：指一个事务对一个共享数据重复多次读取，但前后读取的数据不一致。（修改或删除）
• 幻像读取：后一次读比前一次读多了一些记录（插入）

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

事务调度策略

例：银行客户A的账户当前余款为1000元，客户B的账户当前余款为1500元。现在有两个事务T1和T2，其中T1事务将从客户A转账200元到客户B，T2事务也将从客户A转账400元到客户B。

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

锁

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

资源锁定粒度

• 数据库——粒度最大
• 表——粒度较大
• 页面——粒度中等
• 行——粒度小

加锁协议

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

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

例：某航班剩余机票数据A的当前值为100张。现有分别来自不同售票点的两个并发事务T1和T2，其中T1事务将售出1张机票，T2事务将售出2张机票。以下分别给出它们在不加锁和按一级加锁协议的并发事务调度执行情况，见下图所示。

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

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

例：某航班剩余机票数据A的当前值为100张。现有分别来自不同售票点的两个并发事务T1和T2，其中T1事务将售出1张机票，T2事务进行机票空余数查询。以下分别给出它们在按一级加锁协议执行和按二级加锁协议的事务调度执行情况，见下图所示。

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

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

例：某航班剩余机票数据A的当前值为100张。现有分别来自不同售票点的两个并发事务T1和T2，其中T1事务将售出1张机票，T2事务进行机票空余数查询。以下分别给出它们在按二级加锁协议执行和按三级加锁协议的并发事务调度执行情况，见下图所示。

不同级别锁协议比较

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

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

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

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

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

例 两个事务T1和T2，它们都需要加锁访问数据库表Table1和Table2，其事务程序见图6-20a所示。当这两个事务程序调度执行时，只要不是按可串行化调度执行，则它们在执行时会出现死锁状态，见图6-20b所示。

死锁出现的必要条件

• 互斥条件

• 请求和保持条件

• 不剥夺条件

• 环路等待条件

防范死锁的策略

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

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

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

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

CREATE USER "userA" WITH
	LOGIN --登录权限
	NOSUPERUSER --不是超级用户
	NOCREATEDB --不能创建数据库
	NOCREATEROLE --没有创建角色权限
	INHERIT --继承系统权限
	NOREPLICATION --没有数据库复制权限
	CONNECTION LIMIT -1 --数据库连接数不受限
	PASSWORD '123456';
1
2
3
4
5
6
7
8
9

修改删除用户：

ALTER USER  <用户名>  [ [ WITH ] option [ ... ] ];  	--修改用户的属性
ALTER USER  <用户名>  RENAME TO <新用户名>;  		--修改用户的名称
ALTER USER  <用户名>  SET <参数项> { TO | = } { value | DEFAULT };												--修改用户的参数值  
ALTER USER  <用户名>  RESET <参数项>;			--重置用户参数值
DROP USER userA;
1
2
3
4
5

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

ALTER USER "userA" 
	CONNECTION LIMIT 10
	PASSWORD 'gres123';
1
2
3

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

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

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

CREATE  ROLE  "Role_Manager"  WITH
 LOGIN
 NOSUPERUSER
 NOCREATEDB
 NOCREATEROLE
 INHERIT
 NOREPLICATION
 CONNECTION LIMIT -1;
1
2
3
4
5
6
7
8

角色权限授予

GRANT SELECT,INSERT,UPDATE,DELETE  ON  Department  TO "Role_Manager";
1

数据库备份方法：

• 完全数据库备份
• 差异数据库备份
• 事务日志备份
• 文件备份

下面哪种备份文件是恢复数据库到故障点时刻状态必不可少的?

A．数据库完整备份文件 B．数据库差异备份文件

C．事务日志备份文件 D．数据库文件备份

C. 事务日志备份文件是恢复数据库到故障点时刻状态必不可少的。事务日志是数据库管理系统用来记录数据库中的所有修改操作的文件。在进行数据库恢复时，事务日志记录的变更可以被用来重做或者撤销，从而将数据库恢复到某个指定的时间点或者事务点的状态。因此，事务日志备份文件非常重要，是进行数据库恢复的必要条件。A. 数据库完整备份文件包含整个数据库的所有数据，但是无法恢复到某个指定的时间点或者事务点的状。B. 数据库差异备份文件只包含自上次完整备份或差异备份以来发生的数据变更，也无法恢复到某个指定的时间点或者事务点的状态，因此也不一定是恢复到故障点时刻状态必不可少的。D. 数据库文件备份只是将数据库文件复制到另一个位置，也无法恢复到某个指定的时间点或者事务点的状态。

对于意外事件导致数据库系统损坏，最可靠的技术手段是
A.数据库备份与恢复
B.采用双机容错技术
C.进行访问权限控制
D.系统异地容灾

在数据库系统安全模型中，哪种安全管理手段是DBMS系统提供的数据库安全访问机制?

A.用户身份认证
B.用户存取权限控制
C.文件安全访问
D.数据加密存储

哪种数据库备份方式可以支持系统连续运行?

A.冷备份
B.热备份
C.完整备份
D.差异备份

热备份可以支持系统连续运行。热备份是一种在系统运行时备份数据库的方法，备份期间数据库可以继续提供服务，不会中断用户对数据库的访问。在热备份期间，DBMS系统会将数据库的活动日志记录下来，并将备份与日志进行同步，以确保备份的数据与源数据库保持一致。热备份可以保证数据库系统的连续性和高可用性。相对而言，冷备份是在系统停止运行时备份数据库的方法，需要停止数据库服务，将数据库系统完全关闭，进行备份操作，备份期间数据库无法提供服务。冷备份会导致数据库系统的停机时间较长，并且备份数据与源数据库之间存在时间差，无法实时保持同步。完整备份和差异备份都是备份数据的方式，完整备份是将整个数据库备份，差异备份是只备份自上次备份后发生更改的数据，但它们并不能支持数据库系统的连续运行，需要在备份期间停止数据库服务。

在哪个粒度上对共享数据进行加锁访问，系统并发访问性能最好？行！

DBMS系统可自动恢复哪类数据？事务故障的数据错误

第六章 数据库应用编程

开放式数据库互连（Open DataBase Connectivity）实现了应用程序对多种不同DBMS的数据库的访问，实现了数据库连接方式的变革。

ODBC定义了一套基于SQL的，公共的，与数据库无关的API，从而使得应用程序与数据库管理系统之间在逻辑上独立，使应用程序与数据库无关性

ODBC应用程序接口(ODBCAPI)是一种使用ODBC技术实现应用程序与数据库互连的标准接口。

应用程序使用ODBC访问数据库的步骤：

在ODBC驱动管理器中注册一个数据源;
根据数据源提供的数据库位置、数据库名称及驱动程序等信息，驱动管理器建立起ODBC与具体数据库的联系;
应用程序使用数据源建立与相应数据库的连接;
应用程序通过驱动程序管理器与数据库管理系统交换信息;
数据库管理系统执行完相应的SQL操作后，将结果通过驱动程序管理器返回给应用程序。

JDBC

(1）加载驱动
加载JDBC驱动是通过调用方法:

Class.forName("驱动名字")
1

例PostgreSQL数据库驱动程序加载语句:

Class.forName ("org.postgresql.Driver")
1

(2）建立连接
调用DriverManager. getConnection(String urI)方法建立数据库连接。

例：PostgreSQL数据库连接程序:

String URL = "jdbc: postgresql://localhost:5432/testdb";
String userName = "myuser ";
String passWord = "sa";
connection conn =
DriverManager. getConnection(URL, userName, passWord);
1
2
3
4
5

(3)创建Statement对象
采用Connection对象的createStatement()方法创建Statement对象。其语句如下:
Statement stmt = conn. createStatement () ;
(4）执行SQL语句
在创建Statement对象后，就可以调用Statement对象的executeQuery()、executeUpdate()方法执行SQL语句。

• executeQuery()用于执行select语句
• executeUpdate()用于执行update、 insert、 delete语句

例:Statement对象的SQL执行

Statement stmt = conn.createStatement() ;
String sql = "INSERT INTO public.student (sid,sname,gender,birthday,major,phone)" + "VALUES ('2017001'，'张山','男','1998-10-10','软件工程','13602810001')";
stmt.executeUpdate(sql) ;
1
2
3

(5)ResultSet结果集
ResultSet对象作为结果集，它存放了执行SQL语句的返回结果数据。通过getXXX方法可以对结果集中的记录数据进行访问。
ResultSet中若包含了多行结果数据，可以使用记录指针访问它们。指针所指向的数据行叫做当前数据行。移动指针可以访问其他行，这需要使用ResultSet的next()方法。
示例代码如下:

rs = stmt.executeQuery (sql) ;
while(rs.next()){//依次取出数据
	String name = rs.getString("name") ; //取出name列的内容
	int age = rs.getlnt("age") ; //取出age列的内容
}
1
2
3
4
5

(6）关闭连接
作为一种好的编程风格，在不需要ResultSet对象、Statement对象和Connection对象时，应该显式地关闭它们。关闭这些对象的方法为:close() ;
示例:

rs.close();关闭结果集对象

stmt.close();关闭执行对象

conn.close();关闭连接对象

Servlet是用Java语言编写的服务器端小程序，驻留在web服务器中运行，它扩展了web服务器的动态处理功能。

1.客户机将HTTP请求发送给Web服务器

2.Web 服务器将该请求转发给Servlet

3.Servlet处理该请求

4.Servlet将响应发送给Web 服务器

5.Web服务器将响应转发给客户机

存储过程

存储过程(Stored Procedure）是数据库中的一种对象;由一组完成特定数据处理功能的SQL语句和过程语句组成的程序;它被编译后，存储在数据库中;外部程序可以调用数据库的存储过程执行。

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

CREATE [ OR REPLACE ] FUNCTION/PROCEDURE  name
    ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS retype | RETURNS TABLE ( column_name  column_type [, ...] ) ]
AS $$         //$$用于声明存储过程的实际代码的开始
DECLARE
        -- 声明段
BEGIN
        --函数体语句
END;
$$ LANGUAGE lang_name;  //$$ 表明代码的结束, LANGUAGE后面指明所用的编程语言
1
2
3
4
5
6
7
8
9
10

（1）name：要创建的存储过程名；
（2）OR REPLACE ：覆盖同名的存储过程；
（3）argmode：存储过程参数的模式可以为IN、OUT或INOUT，缺省值是IN。
（4）argname：形式参数的名字。
（5）RETURNS：返回值；RETURNS TABLE：返回二维表

例：创建一个名为countRecords()的过程函数统计STUDENT表的记录数。

CREATE OR REPLACE FUNCTION countRecords ()  
RETURNS integer AS $$  
declare  
    count integer;  
BEGIN  
   SELECT count(*) into count FROM STUDENT;  
   RETURN count;  
END;  
$$ LANGUAGE plpgsql;
1
2
3
4
5
6
7
8
9

练习:创建一个increment(i)的过程函数计算自增值。

CREATE OR REPLACE FUNCTION increment(i integer)
RETURNS integer AS $$
BEGIN
	RETURN i + 1;
END;
$$LANGUAGE plpgsql;
1
2
3
4
5
6

例：创建一个名为add_data (a, b，c）的存储过程实现a+b相加运算，并将结果放入c。

CREATE OR REPLACE PROCEDURE add_data(a integer,b integer,inout c integer)AS $$
Begin
	c=a+b;
End;
$$LANGUAGE plpgsql;
1
2
3
4
5

练习:创建一个increment_pro(i)的存储过程计算自增值。

CREATE OR REPLACE PROCEDURE increment_pro(inout i integer)AS $$
BEGIN
	i=i+1;
	Return;
END;
$$LANGUAGE plpgsql;
1
2
3
4
5
6

如果程序员需要调用函数执行，其调用方式如下:
select 函数名(参数);
或者:select * from 函数名(参数);

如果需要调用存储过程执行，其调用方式如下:
CALL存储过程名（参数)

删除存储过程
DROP FUNCTION [ IF EXISTS ] name ( [ [ argmode ] [ argname ] argtype [, …] ] ) [ CASCADE | RESTRICT ]
主要参数：
（1）IF EXISTS：如果指定的存储过程不存在，那么发出提示信息。
（2）name ：现存的存储过程名称。
（3）argmode：参数的模式：IN(缺省), OUT, INOUT, VARIADIC。请注意，实际并不注明OUT参数，因为判断存储过程的身份只需要输入参数。
（4）argname：参数的名字。请注意，实际上并不注明参数的名字，因为判断函数的身份只需要输入参数的数据类型。
（5）argtype：如果有的话，是存储过程参数的类型。
（6）CASCADE：级联删除依赖于存储过程的对象(如触发器)。
（7）RESTRICT：如果有任何依赖对象存在，则拒绝删除该函数；这个是缺省值。

删除前面定义的函数testExec()

DROP FUNCTION testExec();
1

例删除前面定义的存储过程maintenance()

DROP PROCEDURE maintenance();
1

PL/pgSQL基本语法

变量声明的语法如下：
declare
变量名 变量类型；
如果声明变量为记录类型，变量声明格式为： variable_name RECORD;
注：RECORD不是真正的数据类型，只是一个占位符。

declare 
	count intger;
    rec RECORD;
1
2
3

条件语句
在PL/pgSQL中有以下三种形式的条件语句
1)IF-THEN

IF boolean-expression THEN
    statements
END IF; 
1
2
3

2)IF-THEN-ELSE

IF boolean-expression THEN
	statements
ELSE
Statements
END IF;
1
2
3
4
5

3)IF-THEN-ELSIF-ELSE

IF boolean-expression THEN
	statements
ELSIF boolean-expression THEN
	statements
ELSIF boolean-expression THEN
     statements
ELSE
	statements
END IF; 
1
2
3
4
5
6
7
8
9

循环语句
1)LOOP 语句

LOOP
 statements
END LOOP [ label ];
1
2
3

2)EXIT

LOOP
	count=count+1;
    EXIT WHEN count >100;
END LOOP;
1
2
3
4

3)CONTINUE

LOOP
 	count=count+1;
 	EXIT WHEN count > 100;
 	CONTINUE WHEN count < 50;
 	count=count+1;
END LOOP; 
1
2
3
4
5
6

4)WHILE

 WHILE amount_owed > 0 AND  balance > 0 LOOP
     --do something
 END LOOP;
1
2
3

5)FOR

FOR i IN 1...10 LOOP
	RAISE NOTICE 'i IS %',i;
END LOOP;
FOR i IN REVERSE 10...1  LOOP
      --do something
END LOOP;
1
2
3
4
5
6

遍历查询结果集

FOR record_or_row IN query LOOP
     statements
END LOOP ;
1
2
3

FOR循环可以遍历命令的结果并操作相应的数据，例如：

编写函数实现对学生表Student的查询结果集数据输出

create function Out_Record() returns RECORD as $$
declare 
	rec RECORD;
FOR rec IN SELECT * FROM student LOOP
	raise notice '学生数据: %,%',rec.studentID,rec.studentName; 
END LOOP;
return rec;
end;
$$ language plpgsql;
1
2
3
4
5
6
7
8
9

触发器

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

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

触发器特点

• 与数据库对象相关:在表或视图上执行DML、DDL操作，其定义的事件触发
  过程程序执行。
• DML事件触发:由执行INSERT、DELETE、UPDATE操作时触发。
• DDL事件触发:由执行CRETE、ALTER、DROP、SELECT INTO操作时触发。

按DML操作语句分类: INSERT触发器、DELETE触发器、UPDATE触发器
按触发器执行次数分类:

(1）语句级触发器:由关键字FOR EACH STATEMENT声明，在触发器作用的表上执行一条SQL语句时，该触发器程序只执行一次，即使是修改了零行数据的SQL，也会导致相应的触发器执行。FOR EACH STATEMENT为默认值。

(2）行级触发器:由关键字FOR EACH ROW标记的触发器，当触发器所在表中数据发生变化时，每变化一行就会执行一次触发器程序。

例：学生成绩表上定义了行级DELETE触发器。如果该表删除了20条记录，则将导致DELETE触发器程序被执行20次。

按触发的时间分类:

(1）BEFORE触发器:在触发事件之前执行触发器程序。
(2）AFTER触发器:在触发事件之后执行触发器程序。
(3）INSTEAD OF触发器:当触发事件发生后，执行触发器中指定的过程程序，而不是执行产生触发事件的SQL语句。

触发器相关的特殊变量

1）NEW 数据类型是RECORD。对于行级触发器，它存有INSERT或UPDATE操作产生的新的数据行。对于语句级触发器，它的值是NULL。

2）OLD 数据类型是RECORD。对于行级触发器，它存有被UPDATE或DELETE操作修改或删除的旧的数据行。对于语句级触发器，它的值是NULL。

3）TG_OP 数据类型是text；是值为INSERT、UPDATE、DELETE 的一个字符串，它说明触发器是为哪个操作引发。

创建触发器的基本语法

CREATE  TRIGGER  触发器名   
    { BEFORE | AFTER | INSTEAD OF }
     ON 表名
     [ FOR [ EACH ] { ROW | STATEMENT } ]
     EXECUTE PROCEDURE 存储过程名 ( 参数列表 )
1
2
3
4
5

（1）指明所定义的触发器名
（2） BEFORE | AFTER | INSTEAD OF 指明触发器被触发的时间
（3） ON 表名 指明触发器所依附的表
（4） FOR EACH { ROW | STATEMENT } 指明触发器是行级还是列级
（5） EXECUTE PROCEDURE 存储过程名 ( 参数列表 ) 指明触发时所执行的存储过程

创建触发器的基本步骤

（1）检查数据库中将要创建的触发器所依附的表或视图是否存在，如果不存在，必须首先创建该表或视图。

（2）创建触发器被触发时所要执行的触发器函数，该函数的类型必须是Trigger型，是触发器的执行函数。

（3）创建触发器，定义触发器依附的表，触发器被触发执行的时间，触发器是行级触发器还是语句级触发器，触发器执行需要满足的条件。

为了审计Grade表的课程成绩修改，创建audit_score表记录Grade表的成绩变化，其表结构如下:

CREATE TABLE audit_score( 
    changeID serial not null,						--修改序列号
	username character(20),							--操作用户
	sid	character(13),								--学号
	cid	character(4),								--课程编号
	updatetime text,								--修改的时间
	oldscore int,									--修改前的成绩
	newscore int,									--修改后的成绩
	constraint changeID_PK primary key (changeID)
);

1
2
3
4
5
6
7
8
9
10
11

创建触发器函数

create or replace function score_audit() 
returns trigger as $score_audit$
begin
	if (TG_OP == 'DELETE') then
		insert into audit_score(username,sid,cid,updatetime,oldscore)
		select user,old.studentid,old.couseid,now(),old.score;
		return old;
	elsif (TG_OP == 'UPDATE') then 
		insert into audit_score(username,sid,cid,updatetime,oldscore,newscore)
		select user,old.studentid,old.couseid,now(),old.score,new.score
		where old.studentid = new.sid and old.courseid = new.cid;
		return new;
	elsif (TG_OP == 'INSERT') then
		insert into audit_score(username,sid,cid,updatetime,oldscore,newscore)
		select user,new.studentid,new.courseid,now(),null,new.score;
		return new;
	end if;
	return null;
end;
$score_audit$ language plpgsql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

创建触发器

CREATE TRIGGER score_audit_triger
AFTER INSERT OR UPDATE OR DELETE ON grade
FOR EACH ROW EXECUTE PROCEDURE score_audit();
1
2
3

练习:在如下雇员表emp中被插入或更新一行数据时，触发函数程序将当前用户名和时间标记在该数据行中,并且检查雇员的姓名以及薪水是否为空，若为空，输出警示信息。

CREATE TABLE emp (
	emplD char(3) primary key,
    empname varchar(20),
	salary integer,
	last_date timestamp,
    last_user varchar(20)
);
1
2
3
4
5
6
7

1.触发器函数程序

CREATE FUNCTION emp_stamp ()RETURNS trigger AS $$
	BEGIN
		--检查给出了empname以及salary
		IF NEW.empname IS NULL THEN
			RAISE EXCEPTION '雇员名不能为空';
		END IF;
		IF NEW.salary IS NULL THEN
			RAISE EXCEPTION '%薪水不能为空',NEW.empname;
		END IF;
			--记住谁在什么时候改变了工资单
		NEW.last_date := current_timestamp;
		NEW.last_user := current_user;
		RETURN NEW;
	END;
$$ LANGUAGE plpgsql ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

2.触发器定义程序

CREATE TRIGGER emp_stamp
BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE FUNCTION emp_stamp();
1
2
3

触发器修改

ALTER TRIGGER name ON table_name RENAME TO new_name
1

主要参数说明：
（1）name：现有触发器的名称。
（2）table_name：该触发器作用的表名字。
（3）new_name：触发器的新名字。

例如：将上述定义的触发器改名为score_audit_trig

ALTER TRIGGER score_audit_trigger ON stu_score  RENAME TO score_audit_trig;
1

触发器删除

DROP TRIGGER [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]
1

主要参数说明：

（1）IF EXISTS：如果指定的触发器不存在，那么发出提示而不是抛出错误。
（2）name：要删除的触发器名。
（3）table_name：触发器定义所依附的表的名称。
（5）CASCADE：级联删除依赖此触发器的对象。
（6）RESTRICT：如果有依赖对象存在，那么拒绝删除。该参数缺省是拒绝删除。

例如：将上述触发器score_audit_trig删除，同时级联删除依赖触发器的对象。

DROP TRIGGER IF EXISTS score_audit_trig ON grade CASCADE;
1

游标

• 游标（Cursor）是一种临时的数据库对象；
• 用来存放从数据库表中查询返回的数据记录；
• 提供了从结果集中提取并分别处理每一条记录的机制；
• 游标总是与一条SQL查询语句相关联；
• 游标包括：SQL语言的查询结果，指向特定记录的指针。

声明游标

使用refcursor关键词定义的游标变量

使用游标声明语句定义游标
游标名 CURSOR [ ( arguments ) ] FOR query

例：

Declare
curs	refcursor;
curStudent CURSOR FOR SELECT * FROM student;
curStudentOne CURSOR (key integer)  IS 
            SELECT * FROM student WHERE SID = key;
1
2
3
4
5

打开游标
（1）OPEN FOR:
打开未绑定的游标变量，其query查询语句是返回记录的SELECT语句。例如：

OPEN curVars1 FOR SELECT * FROM student WHERE SID = mykey;
1

（2）OPEN FOR EXECUTE

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

OPEN curVars1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
1

​ 注意：$1是指由存储过程传递的第1个参数。
（3）打开绑定游标
仅适用于绑定的游标变量，只有当该变量在声明时包含接收参数，才能以传递参数的形式打开该游标，参数将传入到游标声明的查询语句中，例如：

OPEN curStudent;
OPEN curStudentOne ('20160230302001'); 
1
2

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

FETCH curVars1 INTO rowvar;  --rowvar为行变量
FETCH curStudent INTO SID, Sname, sex;
1
2

​ --请注意：游标的属性列必须与 目标列的数量一致，并且类型兼容。
关闭游标
当游标数据不再需要时，需要关闭游标，以释放其占有的系统资源，主要是释放游标数据所占用的内存资源

 CLOSE cursorName; 
1

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

例：在函数中使用游标查询student表的学号、学生姓名和性别。

create or replace function cursorDemo()
returns boolean as $$
declare
	unbound_refcursor refcursor;
	vsid varchar;
	vsname varchar;
	vsgender varchar;
begin
	open unbound_refcursor for execute 'select sid,sname,sex from 	student';
	loop
		fetch unbound_refcursor into vsid,vsname,vsgender;
		if found then
			raise notice '%,%,%',vsid,vsname,vsgender;
		else
			exit;
		end if;
	end loop;
	close unbound_refcursor;
	raise notice '取数据循环结束...';
	return true;
end;
$$ language plpgsql;

select cursorDemo();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

例：编写带参数的游标函数，从成绩表中查询分数大于某给定值的学号和课程号。

create or replace function cusorGrade(myscore int)returns void as $$
	declare
		vstuscore Grade%ROWTYPE;		--声明与表Grade结构相同的行变量
		vstucursor cursor(invalue int)
			for select courseid,studentid,grade from Grade where 	grade>=invalue order by
studentid;								--声明带有输入参数的游标
	begin
	open vstucursor (myscore);			--打开带有参数的游标
	loop
		fetch vstucursor into vstuscore;
		exit when not found ;			--假如没有检索到记录，结束循环处理
		raise notice '%,%,%', vstuscore.studentid,vstuscore.courseid,vstuscore.grade;
		end loop;
		close vstucursor ;				--关闭游标
	end;
$$ language plpgsql;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

• 存储过程和触发器的区别有:存储过程可以采用输入参数而触发器不可以

• HTML语句和JSP语句都是下载到浏览器端来执行。X

  凡是使用到了java中的语言部分，一般都是在服务器端执行，解析后返回客户端形成标准的html

Java嵌入SQL语句执行后，返回结果存储在什么对象中：ResultSet

Java嵌入SQL语句，能传递动态参数执行SQL语句查询的是什么接口：PreparedStatement

Java嵌入SQL语句，能调用执行存储过程的是什么接口：CallableStatement

从游标中读取数据，使用什么命令：fetch

第七章 NoSQL数据库技术

关系数据库局限

不能直接管理非结构化数据

受单机服务器限制难以支持数据库高并发读写访问

受磁盘容量限制不能满足海量数据的高效存储和处理

难以实现分布式数据库的高扩展性、高可用性

大数据(big data)，指无法在一定时间范围内用常规软件工具进行捕捉、管理和处理的数据集合。

5V特征

• 大量(Volume)
• 高速性(Velocity)
• 多样性（(Variety)
• 真实性(Veracity)
• 低价值(Value)

(Not only SQL Database，NoSQL）是指一类非关系型、开源代码、具有水平扩展能力的分布式数据库。

NoSQL数据库的公共特性:

支持非结构化数据存储，不用预先定义模式
分布式集群架构，无共享结构
弹性可扩展，可动态增减数据库结点数据分区存储，各分区结点并发访问
结点之间数据异步复制，实现最终数据一致性
数据处理遵循BASE特性原则

BASE特性

Basically Available（基本可用），允许数据库系统某些节点出现故障，其余节点能够继续运行，一直提供服务。
Soft state(软状态），允许数据库系统副本节点之间存在暂时的数据不一致，经过纠错处理，系统数据最终保持一致态。
Eventual Consistency(最终一致性），系统数据在某个时刻达到最终一致性。

分布式数据库一致性

强一致性:无论更新操作在哪一个副本执行，数据复制必须同步完成。

弱一致性:数据更新后，其数据复制是异步完成，需要经过一定时间才能达到不同数据库结点数据一致。

最终一致性:弱一致性的一种特例，保证用户最终能够读取到更新数据。

NoSQL数据库典型类型：
键值数据库、图数据库、列式数据库、文档数据库。

NoSQL数据库与关系数据库比较

NoSQL数据库采用非结构化数据存储模型，关系数据库采用结构化数据存储模型
NoSQL数据库采用分布式部署，关系数据库一般采用集中式部署
NoSQL数据库编程遵循BASE原则，关系数据库编程遵循ACID事务原则
NoSQL数据库没有统一的数据操作标准，关系数据库遵循SQL数据操作标准
NoSQL数据库支持海量数据存储，关系数据库数据存储受限于TB级

CAP

一致性(Consistency） :不同结点数据库保持一致
可用性(Availability) ：数据访问请求随时可满足
分区容忍性(PartitionTolerance) ：当出现故障结点，系统仍能响应数据访问请求

CAP定理法则:

一个分布式系统不可能同时满足一致性(C:Consistency)、可用性(A: Availability)）和分区容错性(P:Partition tolerance)这三个基本需求，最多只能同时满足其中两项。

CA——放弃分区容错性，保证一致性和可用性，即传统的单机数据库处理方式。
AP——放弃强一致性，追求分区容错性和可用性，这是很多分布式系统设计时的选择。例如，一些电商系统采用此方案。
CP——放弃可用性，追求一致性和分区容错性。例如，很多NoSQL系统采用这种方案。

四种NoSQL

列存储数据库

关系数据库:按行存储，每行数据存放在一个磁盘数据块

列存储数据库:按列存储，每列数据放到一个数据块中

键值数据库(Key-Value Database）是一种按照键值数据表结构组织存储数据的内存数据库。代表Redis。

文档数据库：

图形数据库

代表：Neo4j图形数据库

NoSQL数据库的四大分类表格分析

以下哪一项不是NoSQL的共同特征?(D)

A．分区 B．异步复制 C. BASE D.CAP

在Neo4J集群中，数据的写入是通过主服务器来完成的，数据的读取可以通过集群中的任意一个Neo4J实例来完成。正确！！

数据库系统面临的挑战：数据库高并发读写需求；海量数据的高效存储和处理；数据库高扩展性和高可用性需求；数据库在大数据处理方面的要求

以下哪一项不是大数据的特征？高密集价值数据。（是的有：高速产生、规模巨大，多样性）

HBASE中表和区域的关系可以是什么：1：N

以下哪一项不是MongoDB保留数据库：system，（是的有：local、admin、config）

CAP是在分布式环境下设计和部署系统时的3个核心需求。正确！！

Redis复制主要包括RDB复制和AOF复制。正确！！

HBASE中的区域和表的关系是随着数据的增加动态变化的。正确！！

MongoDB的分片是将一个集合的数据分别存储在不同的节点上减轻单机压力。错误

Neo4j的数据物理存储主要分为节点、关系、节点或关系上属性这三类数据存储。！错误

NoSQL数据库分类的依据是对数据的操作方式。错误

Redis数据库的底层实现是字典，对于数据库的增删改查操作都是通过对字典进行操作来实现的。正确！！

HBASE中表定义中要说明行健和表中包含的列族，列不用定义。正确！！

MongoDB复杂文档模型设计可以使用内嵌和引用的方法来解决。正确！！