【数据库】数据库的学习

create schema <模式名> authorization <用户名>;

例：为用户WANG定义一个学生-课程模式S-T

create schema "S-T" authorization WANG;

create schema <模式名> authorization <用户名> [<表定义子句>|<视图定义子句>|<授权定义子句>];

例：为用户ZHANG创建一个模式TEST，并且在其中定义一个表TAB1

create schema test authorization ZHANG

create table tab1(col1 smallint,

col2 int,

col3 char(20),

col4 numeric(10,3),

col5 decimal(5,2)

);

drop schema <模式名> <cascade|restrict>;

cascade(级联)---删除模式的同时把该模式中所有的数据库对象全部删除

restrict(限制)---该模式中已经定义了下属的数据库对象，则拒绝该删除语句的执行。只有当该模式中没有任何下属的对象时才能执行

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

create table <表名> (<列名><数据类型> [列级完整性约束条件]

[,<列名><数据类型> [列级完整性约束条件]]

…

[,[<表级完整性约束条件>]] );

例：建立一个“学生”表Student

create table Student

(Sno char(9) primary key,

Sname char(20) unique,

Ssex char(2),

Sage smallint,

Sdept char(20)

);

建立一个“课程”表Course

create table Course

(Cno char(4) primary key,

Cname char(40) not null,

Cpno char(4),

Ccredit smallint,

foreign key(Cpno) references Course(Cno)

/*表级完整性约束条件，Cpno是外码，被参照表式Course，被参照列是Cno*/

);

建立学生选课表SC

create table sc

(Sno char(9),

Cno char(4),

Grade smallint,

primary key(Sno,Cno),

foreign key (Sno) references Student(Sno),

/*表级完整性约束条件，Sno是外码，被参照表式Student*/

foreign key (Cno) references Course(Cno)

);

模式与表

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

create table "S-T".Student(…);

create table "S-T".Course(…);

create table "S-T".SC(…);

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

方法三，设置所属的模式，当用户创建基本表时若没有指定模式，系统根据搜索路径（search path）来确定该对象所属的模式

show search_path;

修改基本表

alter table<表名>

[add [column] <新列名><数据类型> [完整性约束]]

[add <表级完整性约束>]

[drop [column] <列名> [cascade|restrict]]

[drop constraint<完整性约束名> [restrict|cascade]]

[alter column <列名><数据类型>];

例：向Student表中增加“入学时间列”，其数据类型为日期型

alter table Student add S_entrance date;

将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数

alter table Student alter column Sage int;

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

alter table Course add unique(Cname);

删除基本表

drop table <表名> [restrict|cascade];

索引的建立与删除

建立索引

create [unique] [cluster] index <索引名> on <表名>(<列名> [<次序>] [,<列名> [<次序>]] …);

例：为学生-课程数据库中的Student、Course和SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引

create unique index Stusno on Student(Sno);

create unique index Coucno on Course(Cno);

create unique index SCno on SC(Sno ASC,Cno DESC);

修改索引

alter index <旧索引名> rename to <新索引名>;

例：将SC表的SCno索引名改为SCSno

alter index SCno rename to SCSno;

删除索引

drop index <索引名>;

数据查询

select [all|distinct] <目标表达式> [,<目标表达式>]…

from <表名或视图名> [,<表名或视图名>] | (<select语句>) [as] <别名>

[where <条件表达式>]

[group by <列名1> [having<条件表达式>]]

[order by <列名2> [asc|desc]];

单表查询

选择表中的若干列

（1）查询指定列

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

select Sno,Sname from Student;

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

select Sname,Sno,Sdept from Student;

（2）查询全部列

select * from Student;

等同于查询所有列

（3）查询经过计算的值

目标列表达式不仅可以是表中的属性列，也可以是表达式

例：查询全体学生的姓名以及出生年份

select Sname,2014-Sage from Student;

目标表达式不仅可以是算术表达式，还可以是字符串常量、函数等

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

select Sname,'Year of Birth:' ,2014-Sage,lower(Sdept) from Student;

可以通过指定别名来改变查询结果的列标题

select Sname name,'Year of Birth:' bith,2014-Sage bithday,lower(Sdept) department from Student;

选择表中的若干元组

消除取值重复的行

distinct

查询满足条件的元组

比较大小

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

select Sname,Sage from Student where age<20;

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

select distinct Sno from SC where Grade<60;

确定范围

between and

not between and

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

select Sname,Sdept,Sage from Student where Sage between 20 and 23;

确定集合

谓词in可以用来查找属性值属于指定集合的元组

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

select Sname,Ssex from Student where Sdept in('CS','MA','IS');

字符匹配

谓词like可以用来字符串的匹配

[not] like '<匹配串>' [escape ''<换码字符>]

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

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

_代表任意单个字符

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

select * from Student where Sno like '201215121';

等价于

select * from Student where Sno = '201215121';

如果like后面的匹配串中不含通配符，可以用=取代like

用！或<> （不等于）取代not like

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

select Sname,Sno,Ssex from Student where Sname like '刘%';

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

select Sname from Student where Sname like '欧阳_';

注：数据库字符集为ASCII时，一个汉字需要两个__

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

select Sname,Sno from Student where Sname like '_阳%';

如果用户要查询的字符串本身就含有通配符%和_，这时就要使用escape ‘<换码字符>’ 短语对通配进行转义

例3.34：查询DB_Design课程的课程号和学分

select Cno,Ccredit from Course where Cname like 'DB \ _Design' escape '\';

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

select * from Course where Cname like 'DB \ _%i__' escape '\';

这里的匹配串为'DB \ _%i__' 。第一个_前面有换码字符\，所以被转义为普通的_字符。而i后面的两个_的前面均没有换码字符\，所以它们仍作为通配符

涉及空值的查询

某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号

select Sno,Cno from SC where Grade is null;

注意这里的is不能用等号=代替

多重条件查询

逻辑运算符and和or可用来连接多个查询条件，and的优先级高于or，可以用括号改变优先级

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

select Sname from Student where Sdept='CS' and Sage<20;

in谓词相当于多个or运算符的缩写

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

select Sname,Ssex from Student where Sdept in('CS','MA','IS');

select Sname,Ssex from Student where Sdept='CS' or Sdept='MA' or Sdept='IS';

order by子句

对查询结果按照一个或多个属性列的升序/降序排列，默认值为升序

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

select Sno,Grade from SC where Cno='3' order by Grade desc;

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

select * from Student order by Sdept,Sage desc;

聚集函数

count(*)

count( [distinct|all] <列名>)

sum( [distinct|all] <列名>)

avg( [distinct|all] <列名>)

max( [distinct|all] <列名>)

min( [distinct|all] <列名>)

例：查询学生总人数

select count(*) from Student;

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

select avg(Grade) from SC where Cno='1';

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

select max(Grade) from SC where Cno='1';

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

select sun(Ccredit) from SC,Course where Sno='201215012' and SC.Cno=Course.Cno;

group by子句

分组后聚集函数将作用于每一个组

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

select Cno,count(Sno) from SC group by Cno;

使用having对分组结果进行选择

例3.47：查询选修了三门以上课程的学生学号

select Sno from SC group by Sno having count(*)>3;

连接查询

等值与非等值查询

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

[<表名1>.]<列名1>between [<表名2>.]<列名2>and [<表名3>.]<列名3>

例3.49查询每个学生及其选课的情况

select Student.*,SC.*

from Student,SC

where Student.Sno=SC.Sno;

对例3.49用自然连接完成

select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade

from Student,SC

where Student.Sno=SC.Sno;

由于Sname,Ssex,Sage,Sdept,Cno和Grade属性列在Student表与SC表中是唯一的，因此引用时可以去掉表名前缀；而Sno在两个表中都出现了，因此引用时必须加上表名前缀

例3.51

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

select Student.Sno,Sname

from Student,SC

where Student.Sno=SC.Sno and

SC.Cno='2' and SC.Grade>90;

自身连接

一个表与其自己进行连接

例3.52查询每一门课的间接先修课

要为Course表取两个别名，一个是first，另一个是second

select first.Cno,second.Cpno

from Course first,Course second

where first.Cpno=second.Cno;

外连接

select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade

from Studnt left outer join SC on(Student.Sno=SC.Sno);

左外连接

右外连接

多表连接

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

select Student.Sno,Sname,Cname,Grade

from Student,SC,Course

where Student.Sno=SC.Sno and SC.Cno=Course.Cno;

嵌套查询

select Sname

from Student

where Sno in

(select Sno from SC where Cno='2');

选了2号课程的学号姓名

注意：子查询的select语句不能使用order by子句（只能对最终查询结果排序）

带有in谓词的子查询

例3.55查询与“刘晨”在同一个系学生的学生------解法1

1.确定“刘晨”所在系名

select Sdept from Student where Sname='刘晨';

结果为CS

2.查找所有在CS系学习的学生

select Sno,Sname,Sdept from Student where Sdept='CS';

综合

select Sno,Sname,Sdept

from Student

where Sdept in

(select Sdept from where Sname='刘晨');

注：这里子查询的结果只有一个CS，in就等价于=

用自身连接完成------解法2

select S1.Sno，S1.Sname,S1.Sdept

from Student S1,Student S2

where S1.Sdept=S2.Sdept and S2.Sname='刘晨';

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

分析：涉及学号、姓名和课程名，学号和姓名存放在Student表中，课程名存放在Course表中，但这两个表中没有直接联系，必须通过SC表建立它们二者之间的联系

Course 课程号-->SC 学号---->Student 学号姓名

select Sno,Sname

from Student

where Sno in

(select Sno

from Sc

where Cno in

(select Cno

from Course

where Cname='信息系统'));

用连接查询实现

select Student.Sno,Sname

from Student,SC,Course

where Student.Sno=SC.Sno and

SC.Cno=Course.Cno and

Course.Cname='信息系统';

带有比较运算符的子查询

确切知道内层查询返回的是单个值时

例3.55查询与“刘晨”在同一个系学生的学生------解法3

select Sno,Sname,Sdept

from Student

where Sdept =

(select Sdept from where Sname='刘晨');

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

select Sno,Cno

from SC x

where Grade >= (select avg(Grade)

from SC y

where y.Sno=x.Sno);

带有any或all谓词的子查询

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

select Sname,Sage

from Student

where Sage< any(select Sage

from Student

where Sdept='CS')

and Sdept <> 'CS';

用聚集函数实现

select Sname,Sage

from Student

where Sage <

(select max(Sage)

from Student

where Sdept='CS')

and Sdept <> 'CS';

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

select Sname,Sage

from Student

where Sage < add

(select Sage

from Student

where Sdept='CS')

and Sdept <> 'CS';

用聚集函数实现

select Sname,Sage

from Student

where Sage <

(select min(Sage)

from Student

where Sdept='CS')

and Sdept <> 'CS';

=any等价于in

<any等价于<max

<>all等价于not in

<all 等价于<min

带有exists谓词的子查询

exists代表存在量词

带有exists谓词的子查询不返回任何数据，只产生逻辑真与假

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

select Sname

from Student

where exists

(select *

from SC

where Sno=Student.Sno and Cno='1');

由exists引出的子查询，其目标列表达式通常用*

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

select Sname

from Student

where not exists

(select *

from SC

where Sno=Student.Sno and Cno='1');

例3.55查询与“刘晨”在同一个系学生的学生------解法4

select Sno,Sname,Sdept

from Student S1

where exists

(select *

from Student S2

where S2.Sdept=S1.Sdept and

S2.name='刘晨');

例3.62查询选修了全部课程的学生姓名

没有一门课程是他不选修的

select Sname

from Student

where not exists

(select *

from Course

where not exists

(select *

from SC

where Sno=Student.Sno

and Cno=Course.Cno));

例3.63查询至少选修了学生201215122选修的全部课程的学生学号

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

select distinct Sno

from SC SCX

where not exists

(select *

from SC SCY

where SCY.Sno='201215122' and

not exists

from SC SCZ

wher SCZ.Sno=SCX.Sno and

SCZ.Cno=SCY,Cno));

集合查询

并操作union

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

select *

from Student

where Sdept='CS'

union

select *

from Student

where Sage <=19;

union将多个查询结果合并起来时，系统会自动去掉重复元组，如果要保留重复元组用union all

例3.65查询选修了课程1或者选修了课程2的学生-------并集

select Sno

from SC

where Cno='1'

union

select Sno

from SC

where Cno='2';

另一种

select Sno

from SC

where Cno='1' or Cno='2';

交操作intersect

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

select *

from Student

where Sdept='CS'

intersect

select *

from Student

where Sage<=19;

也就是

select *

from Student

where Sdept='CS' and

Sage<=19;

例3.67查询既选修了课程1又选修了课程2的学生

select Sno

from SC

where Cno='1'

intersect

select Sno

from SC

where Cno='2';

也可以表示为

select Sno

from SC

where Cno='1' and Sno in

(select Sno

from SC

where Cno='2');

注：不能用where Cno='1' and Cno='2'，执行过程是逐元组，选2的不一定选1

差操作except

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

select *

from Student

where Sdept='CS'

except

select *

from Student

where Sage<=19;

也就是

select *

from Student

where Sdept='CS' and Sage>19;

基于派生表的查询

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

select Sno,Cno

from SC x

where Grade >= (select avg(Grade)

from SC y

where y.Sno=x.Sno);

用派生表实现

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.60查询所有选修1号课程的学生姓名

select Sname

from Student

where exists

(select *

from SC

where Sno=Student.Sno and Cno='1');

用派生表实现

select Sname

from Student,(select Sno from SC where Cno='1') as SC1

where Student.Sno=SC1.Sno;

其中，SC1是Cno=1,只有一属性列Sno

数据更新

插入数据

插入元组

insert

into <表名> [(<属性列1> [,<属性列2>] …]

values (<常量1> [,<常量2>] …);

into子句中没有出现的属性列，新元组在这些列上将取空值（若在表定义时说明了not null的属性别难过取空值）

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

insert

into student(sno,sname,ssex,sdept,sage)

values('201215128','陈冬','男','IS',18);

插入子查询结果

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

create table dept_age

(sdept char(15)

avg_avg smallint);

insert

into dept_age(sdept,avg_avg)

select sdept.avg(sage)

from student

group by sdept;

修改数据

update <表名>

set <列名>=<表达式> [,<列名>=<表达式>] …

[where <条件>];

修改某一个元组的值

例3.73将学生201215121的年龄改为22岁

update student

set sage=22

where sno='201215121';

修改多个元组的值

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

update student

set sage=sage+1;

带子查询的修改语句

例3.75将计算机科学系全体学生的成绩置0

update sc

set grade=0

where sno in

(select sno

from student

where sdept-'cs');

删除数据

delete

from <表名>

[where <条件>];

删除某一个元组的值

例3.76删除学号为201215128的学生记录

delete

from student

where sno='201215128';

删除多个元组的值

例3.77删除所有学生的选课记录

delete

from sc;

带子查询的删除语句

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

delete

from sc

where sno in

(select sno

from student

where sdept='cs');

空值的处理

空值的产生

插入时没有赋值

空值的判断

is null

is not null

视图*

定义视图

建立视图

create view <视图名> [<列名> [,<列名>]…)]

as <子查询>

[with check option];

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

省略视图的各个属性列名，则隐含该视图由子查询select子句目标列中的诸字段组成

但在下列情况必须指定组成视图的所有列名：

某个目标列不是单纯的属性名，而是聚集函数或列表达式
多表连接时选出了几个同名列作为视图的字段
需要再视图中为某个列启用新的更合适的名字

删除视图

drop view <视图名> [cascade];

查询视图

和基本表一样

更新视图

insert

update

delete

第四章数据库安全性

数据安全性概述

数据的不安全因素

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

DBMS提供的安全措施主要包括用户身份鉴别、存取控制

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

DBMS提供的主要技术有强制存取控制、数据加密存储和加密传输等。

此外，在安全性要求较高的部门提供审计功能，通过分析审计日志，可以对潜在的威胁提采取措施加以防范，对非授权用户的入侵行为以及信息破坏情况能够进行跟踪，防止对数据库安全责任的否认。

D级：最低级

C1级：能够实现对用户和数据的分离，自主存取控制（DAC），保护或限制用户权限的传播

C2级：将C1级的DAC进一步细化，以个人身份注册负责，并实施审计和资源隔离

B1级：标记安全保护。对系统的数据加以标记，并对标记的主体和客体实施强制存取控制（MAC）以及审计等安全机制。B1级别的产品才被认为是真正意义上的安全产品

B2级：结构化保护。建立形式化的安全策略模型，并对系统内所有主体和客体实施DAC和MAC

B3级：安全域。该级的TCB必须满足访问监控器的要求，审计跟踪能力更强，并提供系统恢复过程。

A1级：验证设计，即提供B3级保护的同时给出系统的形式化设计说明和验证，以确信各安全保护真正实现。

CC（通用准则）的文本由三部分组成，第一部分是简介和一般模型，第二部分是安全功能要求，第三部分是安全保证要求（评估保证级EAL1至EAL7共7级）

数据库安全性控制

用户身份鉴别

是DBMS提供的最外层安全保护措施

静态口令鉴别

动态口令鉴别

智能卡鉴别

存取控制

定义用户权限

并将用户权限登记到数据字典中

用户对某一数据对象的操作权利称为权限

合法权限检查

C2级----自主存取控制DAC

B1级-----强制存取控制MAC

自主存取控制方法

用户权限是由两个要素组成：数据库对象和操作类型

定义存取操作权限称为授权

授权grant

grant语句一般格式：

grant <权限> [,<权限>]……

on <对象类型> <对象名> [,<对象类型><对象名>]

to <用户> [,<用户>]

[with grant option];

例4.1把查询Student表的权限授权给用户U1

grant select

on table Student

例4.2把对Student表和Course表的全部操作授予用户U2和U3

grant all privileges

on table Student,Course

to U2,U3;

例4.3把对表SC的查询权限授予所有用户

grant select

on table sc

to public;

例4.4把查询Student表和修改学生学号的权限授给用户U4

grant update(Sno),select

on table Student

to U4;

对属性列授权时必须明确指出相应的属性名

例4.5把对表SC的insert权限授予U5用户，并允许将此权限再授予其他用户

grant insert

on table sc

to U5

with grant option;

例4.6 U5将权限授予U6

grant insert

on table sc

to U6

with grant option;

例4.7 U7不能再传播此权限

grant insert

on table sc

to U7;

收回revoke

revoke语句的一般格式为

revoke <权限> [,<权限>]……

on <对象类型> <对象名> [,<对象类型><对象名>]……

from <用户> [,<用户>]…[cascade | restrict];

例4.8把用户U4修改学生学号的权限收回

revoke update(Sno)

on table Student

from U4;

例4.9收回所有用户对表SC的查询权限

revoke select

on table sc

from public;

例4.10把用户U5对SC表的insert权限收回

revoke insert

on table sc

from U5 cascade;

数据库角色

1.角色的创建

create role <角色名>;

2.给角色授权

grant <权限> [,<权限>]……

on <对象类型> 对象名

to <角色> [,<角色>]……

3.将一个角色授予其他的角色或用户

grant <角色1> [,<角色2>]……

to <角色3> [,<用户1>]……

[with admin option];

4.角色权限收回

revoke <权限> [,<权限>]……

on <对象类型><对象名>

from <角色> [,<角色>]……

例4.11通过角色来实现将一组权限授予一个用户

首先，创建一个角色

create role r1;

然后使用grant语句，使角色r1拥有Student表的select、update、insert权限

grant select,update,insert

on table Student

to r1;

将这个角色授予王平、张明、赵玲，使他们具有角色r1所包含的全部权限

grant r1

to 王平、张明、赵玲;

当然，也可以一次性地通过r1来回收王平的这三个权限

revoke r1

from 王平;

例4.12角色的权限修改

grant delete

on table Student

to r1;

例4.13使r1减少select权限

revoke select

on table Student

from r1;

强制存取控制方法

主体是系统中的活动实体，既包括DBMS所管理的实际用户，也包括代表用户的各进程。客体是系统中的被动实体，是受主体操纵的，包括文件、基本表、索引、视图等

每个实例（值）指派敏感性标记

绝密TS

机密S

可信C

公开P

TS>=S>=C>=P

主体的敏感度标记称为许可证级别

客体的敏感度标记称为密级

（1）仅当主体的许可证级别大于或等于客体的密级，该主体才能读取相应的客体

（2）仅当主体的许可证级别小于或等于客体的密级时，该主体才能写相应的客体

视图机制

审计

审计功能就是数据库管理系统达到C2以上安全级别必不可少的一项指标

审计事件

审计功能

设置审计audit

例4.15对修改sc表结构或修改sc表数据的操作进行审计

audit alter,update

on sc;

取消审计noaudit

例4.16取消对sc表的一切审计

noaudit alter,update

on sc;

数据加密

加密的基本思想是根据一定的算法将原始数据----明文变换为不可直接识别的格式------密文，从而使得不知道解密算法党的人无法获知数据的内容。

存储加密

透明---内核级加密保护方式，对用户完全透明

非透明----通过多个加密函数实现

透明存储加密是数据在写到磁盘时对数据进行加密，授权用户读取数据时再对其进行解密

传输加密

客户/服务器结构中，若采用明文传输数据，容易被网络恶意用户截取或篡改，存在安全隐患

链路加密和端对端加密

基于安全套接协议SSL，采用端到端的传输加密方式

第五章数据库完整性

数据库的完整性是指数据的正确性和相容性

数据的正确性是指数据是否符合现实世界语义、反映当前实际状况的；数据的相容性是指数据库同一对象在不同关系表中的数据是符合逻辑的

例如：学生的学号必须唯一，性别只能是男或女，本科学生年龄取值范围为14~50的整数，学生所选的课程必须是学校开设的课程，学生所在的院系必须是学校已成立的院系等

数据的完整性和安全性是两个既有联系又不尽相同的概念。数据完整性是为了防止数据库中存在不符合语义的数据，也就是防止数据库中存在不正确的数据。数据的安全性是保护数据库防止恶意破坏和非法存取。

实体完整性

定义实体完整性

在create table中用primary key定义。对单属性构成的码有两种说明方法，一种是定义为列级约束条件，另一种是定义为表级约束条件。对于多属性构成的码只有一种说明方法，即定义为表级约束条件。

主属性不能为空

例5.1将Student表中的Sno属性定义为码

create table Student
    (Sno char(9) primary key,     /*在列级定义主码*/
    Sname char(20) not null,
    Ssex char(2),
    Sage smallint,
    Sdept char(20)
    );

create table Student
    (Sno char(9),
    Sname char(20) not null,
    Ssex char(2),
    Sage smallint,
    Sdept char(20),
    primary key(Sno)       /*在表级定义主码*/
    );

例5.2将SC表中Sno、Cno属性组定义为码

create table sc
    (Sno char(9) not null,
    Cno char(4) not null.
    Grade smallint,
    primary key(Sno,Cno)       /*只能在表级定义主码*/
    );

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

用primary key短语定义了关系的主码后，每当用户程序对基本表插入一条记录或对主码列进行更新操作时，关系数据库管理系统按照实体完整性规则自动进行检查：

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

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

检查记录中主码值是否唯一的一种方法是进行全表扫描，一次判断是否相同。

还有就是在主码上自动建立一个索引（B+树索引），通过索引查找

参照完整性

定义参照完整性

在create table中用foreign key短语定义哪些列为外码，用references短语指明这些外码参照哪些表的主码。

例5.3定义SC中的参照完整性

create table SC
    (Sno char(9) not null,
    Cno char(4) not null,
    Grade smallint,
    primary key(Sno,Cno),                      /*在表级定义实体完整性*/
    foreign key(Sno) references Student(Sno),  /*在表级定义参照完整性*/
    foreign key(Cno) references Course(Cno)    /*在表级定义参照完整性*/
    );

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

当上述的发生不一致时，系统可以采用以下策略加以处理：

（1）拒绝（no action）执行

（2）级联（cascade）操作

（3）设置为空值

例5.4 显式说明参照完整性的违约处理实例

create table SC
    (Sno char(9),
    Cno char(4),
    Grade smallint,
    primary key(Sno,Cno),        /*在表级定义实体完整性，Sno、Cno都不能取空值*/
    foreign key(Sno) references Student(Sno)   /*在表级定义参照完整性*/
        on delete cascade
                        /*当删除Student表中的元组时，级联删除SC表中相应的元组*/
        on update csscade
                        /*当更新Student表中的sno时，级联更新SC表中相应的元组*/
    foreign key(Cno) references Course(Cno)    /*在表级定义参照完整性*/
        on delete no action
                        /*当删除Course表中的元组造成与SC不一致时，拒绝删除*/
        on update cascade
                        /*当更新Course表中的cno时，级联更新SC表中相应的元组*/

可以对delete和update采用不同的策略

用户定义的完整性

属性上的约束条件

1.属性上约束条件的定义

在create table中定义属性的同时，可以根据应用要求定义属性上的约束条件，即属性值限制，包括：

列值非空（not null）
列值唯一（unique）
检查列值是否满足一个条件表达式（check短语）

（1）不允许取空值

例5.5 在定义SC表时，说明Sno、Cno、Grade属性不允许取空值

create table SC
    (Sno char(9) not null,   /*Sno属性不允许取空值*/
    Cno char(4) not null,     /*Cno属性不允许取空值*/
    Grade smallint not null,   /*Grade属性不允许取空值*/
    primary key(Sno,Cno),     /*在表级定义实体完整性，隐含了Sno、Cno不允许取空值，在列级不允许去空值的定义可不写*/
    ……
    );

（2）列值唯一

例5.6 建立部门表DEPT，要求部分名称Dname取值唯一，部门编号Deptno列为主码
create table DEPT
    (Deptno numeric(2),
    Dname char(9) unique not null,    /*要求Dname列值唯一，且不能取空*/
    Location char(10),
    primary key(Deptno)
    );

（3）用check短语指定列应该慢则的条件

例5.7 Student表的Ssex只允许取“男”或“女”

create table Student
    (Sno char(9) primary key,              /*在列级定义主码*/
    Sname char(8) not null,                /*Sname属性不允许取空值*/
    Ssex char(2) check(Ssex in('男','女')),
                                  /*性别属性Ssex只允许取'男'或'女'*/
    Sage smallint,
    Sdept char(20)
    );

例5.8 SC表的Grade的值应该在0和100之间

create table SC
    (Sno char(9),
    Cno char(4),
    Grade smallint check(Grade>=0 and Grade <=100),
    primary key(Sno,Cno),
    foreign key(Sno) references Student(Sno),
    foreign key(Cno) references Course(Cno)
    );

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

当往表中插入元组或修改属性的值时，关系数据库管理系统将检查属性上的约束条件是否被满足，如果不满足则操作被拒绝执行

元组上的约束条件

元组上约束条件的定义

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

例5.9 当学生的性别是男时，其名字不能以Ms.打头
create table Student
    (Sno char(9),
    Sname char(8) not null,
    Ssex char(2),
    Sage smallint,
    Sdept char(20),
    primary key(Sno),
    check(Ssex='女' or Sname not like 'Ms.%')
    );
性别是女性的元组都能通过该项check检查，因为Ssex='女'成立；当性别是男性时，要通过检查则名字一定不能以Ms.打头，因为Ssex='男'时，条件要想为真值，Sname not like 'Ms.%'必须为真值

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

当往表中插入元组或修改属性的值时，关系数据库管理系统将检查元组上的约束条件是否被满足，如果不能满足则操作数被拒绝执行

完整性约束命名子句

constraint，用来对完整性约束条件命名

完整性约束命名子句

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

<完整性约束条件>包括not null、unique、primary key、foreign key、check短语等

例5.10 建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”
create table Student
    (Sno numeric(6)
        constraint C1 check(Sno between 90000 and 99999),
    Sname char(20)
        constraint C2 not null,
    Sage numeric(3)
        constraint C3 check(Sage<30),
    Ssex char(2)
        constraint C4 check(Ssex in('男','女')),
        constraint StudentKey primary key(Sno)
    );
在Student表上建立了5个约束条件，包括主码约束（命名为StudentKey）以及C1、C2、C3、C4这4个列级约束

例5.11建立教师表teacher，要求每个教师的应发工资不低于3000元

应发工资是工资列Sal与扣除项Deduct
create table teacher
    (Eno numeric(4) primary key,
    Ename char(10),
    Job char(8),
    Sal numeric(7,2),
    Deduct numeric(7,2),
    Deptno numeric(2),
    constraint teacherKey foreign key(Deptno)
    references DEPT(Deptno),
    constraint C1 check(Sal+Deduct >= 3000)
    );

修改表中的完整性限制

可以使用alter table语句修改表中的完整性限制

例5.12去掉例5.10 Student表中对性别的限制

alter table Student

drop constraint C4;

例5.13 修改表中的约束条件，要求学号改为在900 000~999 999之间，年龄由小于30改为小于40

可以先删除原来的约束条件，再增加新的约束条件
alter table Student
    drop constraint C1;
alter table Student
    add constraint C1 check(Sno between 900000 and 999999);
alter table Student
    drop constraint C3;
alter table Student
    add constraint C3 check(Sage < 40);

断言

创建断言的语句格式

create assertion <断言名> <check 子句>

每个断言都赋予一个名字，<check 子句>中的约束条件与where子句的条件表达式类似

例5.18 限制数据库课程最多60名学生选修
create assertion asse_sc_db_num
    check(60 >= (select count(*)
                    from Course,SC
                    where SC.Cno=Course.Cno and Course.Cname = '数据库')
    );
每当学生选修课程时，将在SC表中插入一条元组（Sno,Cno,NULL），asse_sc_db_num断言被触发检查。如果选修数据库课程的人数已经超过60人，check子句返回值为“假”，对SC表的插入操作被拒绝

例5.19 限制每一门课程最多60名学生选修
create assertion asse_sc_cnum1
    check(60 >= ALL(select count(*)
                        from SC
                        group by Cno)
                        );
例5.20限制每学期每一门课最多60名学生选修

首先修改SC表的模式，增加一个“学期（term）”的属性

alter table SC add term date; /*先修改SC表，增加term属性，它的类型是DATE*/

然后定义断言：

create assertion asse_sc_cnum2

check(60 >= all(select count(*) from SC group by Cno,term));

删除断言的语句格式

drop assertion <断言名>

触发器

trigger是用户定义在关系表上的一类由事件驱动的特殊过程。一旦定义，触发器将被保存在数据库服务器中。（某一时刻什么事发生了，之前怎么样，之后要怎么样，“我”要怎么样）

触发器类似于约束，但是比约束更加灵活

定义触发器

触发器又叫做事件-条件-动作（event-condition-action）规则。当特定的系统事件（对表的增删改操作，事务的结束等）发生时，对规则的条件进行检查，如果条件成立则执行规则中的动作，否则不执行该动作。规则中的动作体可以很复杂，可以涉及其他表和其他数据库对象，通常是一段SQL存储过程。

SQL使用create trigger命令建立触发器，其一般格式为

create trigger <触发器名> /*每当触发事件发生时，该触发器被激活*/

{before | after} <触发事件> on <表名>

/*指明触发器激活的时间是在执行触发事件前或后*/

referencing new | old row as <变量> /*referencing指出引用的变量*/

for each {row | statement} /*定义触发器的类型，指明动作体执行的频率*/

[when <触发器>] <触发动作体> /*仅当触发条件为真时才执行触发动作体*/

（1）只有表的拥有者，即创建表的用户才可以在表上创建触发器，并且一个表上只能创建一定数量的触发器

（2）触发器名。可以包含模式名，也可以不包含模式名。同一模式下，触发器名必须是唯一的，并且触发器名和表名必须在同一模式下

（3）表名。触发器只能定义在基本表上，不能定义在视图上。当基本表的数据发生变化时，将激活定义在该表上相应触发事件的触发器

（4）触发事件。可以是insert、delete或update，也可以是这几个事件的组合，如insert or delete等，还可以是update of <触发列,…>，即进一步指明修改哪些列时激活触发器。after/before是触发的时机

（5）触发器类型。按照所触发动作的间隔尺寸可以分成行级触发器（for each row）和语句级触发器（for each statement）

例如，假设在例5.11的teacher表上创建了一个after update触发器，触发事件时update语句：

update teacher set Deptno=5;

假设teacher有1000行，如果定义的触发器为语句级触发器，那么执行完update语句之后触发动作体执行一次；如果是行级触发器，触发动作体将执行1000次

（6）触发条件。触发器被激活时，只有当触发条件为真时触发动作体才执行，否则触发动作提不执行，如果省略when触发条件，则触发动作体在触发器激活后立即执行

（7）触发动作体。既可以是一个匿名PL/SQL过程块。也可以是对已创建存储过程的调用。如果是行级触发器，用户可以在过程体中使用new和old引用update/insert事件之后的新值和update/delete事件之前的旧值；如果是语句级触发器，则不能在触发器动作体中使用new和old进行引用

如果触发动作体执行失败，激活触发器的事件就会终止执行

例5.21 当对表SC的Grade属性进行修改时，若分数增加了10%，则将此次操作记录到另一个表SC_U(Sno,Cno,Oldgrade,Newgrade)中，其中Oldgrade是修改前的分数，Newgrade是修改后的分数

create trigger SC_T              /*SC_T是触发器的名字*/
after update of Grade on SC      /*update of Grade on SC是触发事件*/
    /*after是触发的时机，表示当对SC的Grade属性修改完后再触发下面的规则*/
referencing
    oldrow as OldTuple,
    newrow ad NewTuple
for each row   /*行级触发器，即每执行一次Grade的更新，下面的规则就执行一次*/
when (NewTuple.Grade >= 1.1*OldTuple.Grade)     /*触发条件，只有该条件为真时才执行*/
    insert into SC_U(Sno,Cno,OldGrade,NewGrade)     /*下面的insert操作*/
    values(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade)

例5.22 将每次对表Student的插入操作所增加的学生个数记录到表Student-InsertLog中

create trigger Student Count
after insert on Student     /*指明触发器激活的时间是在执行insert之后*/
referencing
    new table as DELTA
for each statement        /*语句级触发器，即执行完insert语句后下面的触发动作体才执行一次*/
    insert into StudentInsertLog(Numbers)
    select count(*) from DELTA

例5.23 定义一个before行级触发器，为教师Teacher定义完整性规则“教授的工资不得低于4000元，如果低于40000元，自动改为4000元”

create trigger Insert_Or_Update_Sal   /*对教师表插入或更新时激活触发器*/
before insert or update on Teacher    /*before触发事件*/
referencing new row as newTuple
for each row         /*这是行级触发器*/
begin      /*定义触发动作体，这是一个PL/SQL过程块*/
    if(newtuple.Job='教授') and (newtuple.Sal < 4000)
                                           /*因为是行级触发器，可在过程体中*/
        then newtuple.Sal:=4000;       /*使用插入或更新操作后的新值*/
    end if;
end;

激活触发器

触发器的执行是由触发事件激活，并由数据库服务器自动执行的。一个数据表上可能定义了多个触发器，如多个before触发器、多个after触发器等，同一个表上的多个触发器激活时遵循如下的执行顺序：

（1）执行该表上的before触发器

（2）激活触发器的SQL语句

（3）执行该表上的after触发器

对于同一个表上的多个before（after）触发器，遵循“谁先创建谁先执行”的原则

删除触发器

drop trigger <触发器名> on <表名>

第六章关系数据理论

R(U,D,DOM,F)

关系名R是符号化的元组语义

U为一组属性

D为属性组U中属性所来自的域

DOM为属性到域的映射

F为属性组U上的一组数据依赖

R<U,F>

当且仅当U上的一个关系r满足F时，r称为关系模式R<U,F>的一个关系

作为一个二维表，关系要符合一个最基本的条件：每一个分量必须是不可分的数据项。满足了这个条件的关系模式就属于第一范式（1NF）

数据依赖是一个关系内部属性与属性之间的一种约束关系。这种约束关系是通过属性间值的相等与否体现出来的数据间的相关联系。它是现实世界属性间相互联系的抽象，是数据内在的性质，是语义的体现。

其中有函数依赖，多值依赖

y=f(x)

Sname=f(Sno)，Sdept=f(Sno)

Sno-->Sname,Sno-->Sdept

例6.1

建立一个学校教务系统的数据库，该数据库涉及的对象包括学生的学号（Sno）、所在系（Sdept）、系主任姓名（Mname）、课程号（Cno）和成绩（Grade）。假设用一个单一的关系模式Student来表示，则该关系模式的属性集合为

U={Sno,Sdept,Mname,Cno,Grade}

现实世界的已知事实（语义）告诉我们：

（1）一个系有若干学生，但一个学生只属于一个系

（2）一个系只有一名（正职）负责人

（3）一个学生可以选修多门课程，每门课程有若干学生选修

（4）每个学生学习每一门课程有一个成绩

得到属性组U上的一组函数依赖F

F={Sno-->Sdept,Sdept-->Mname,(Sno,Cno)-->Grade}

这个关系模式存在一下问题：

1.数据冗余

比如，每个系的系主任姓名重复，重复次数与该系所有的所有课程成绩出现次数相同，这将浪费大量的存储空间

2.更新异常

比如，某系统更换系主主任，必须修改与该系学生有关的每一个元组

3.插入异常

如果一个系刚成立，尚无学生，则无法把这个系及其系主任的信息存入数据库

4.删除异常

如果某个系的学生全部毕业了，则在删除该系学生的同时，这个系及其系主任的信息也丢掉了

因为这个模式中的函数依赖存在某些不好性质

分成三个关系模式：

S(Sno,Sdept,Sno-->Sdept);

SC(Sno,Cno,Grade,(Sno,Cno)-->Grade);

DEPT(Sdept,Mname,Sdept-->Mname);

规范化

函数依赖

y=f(x) x1=x2 f(x1)=f(x2)

函数依赖不是指关系模式R的某个或某些关系满足的约束条件，而是指R的一切关系均要满足的约束条件

非平凡的函数依赖

平凡的函数依赖

完全函数依赖

P181

码

候选码

范式

1NF

2NF

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

3NF

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

关系模式中不存在飞主属性对码的传递依赖

BCNF

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

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

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

R属于3NF，R未必属于BCNF

第七章数据库设计

数据库设计概述

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

高效的运行环境------数据的存取效率、数据库存储空间的利用率、数据库系统运行管理的效率

数据库设计的特点

P206

三分技术，七分管理，十二分基础数据

数据库设计的基本步骤

P211

需求分析

需求分析的方法

结构化分析AA，自顶向下、逐层分解

数据字典

数据项、数据结构、数据流（路）、数据存储（车站）、处理过程（工厂）

概念结构设计

概念模型

E-R模型

两个实体之间的联系

一对一

一对多

多对多

两个以上的实体型之间的联系

单个实体型内的联系

第八章数据库编程

嵌入式SQL

交互式、嵌入式

嵌入式SQL的处理过程

将SQL嵌入程序设计语言中，被嵌入的程序设计，如C、C++、Java等称为宿主语言，简称主语言

对嵌入式SQL，数据库管理系统一般采用预编译方法处理

区分SQL语句与主语句，所有SQL语句都必须加前缀

当主语言为C语言时，语法格式为：

exec sql <SQL语句>;

嵌入式SQL语言与主语言之间的通信

SQL语言负责操纵数据库；高级语言语句负责控制逻辑流程

（1）向主语言传递SQL语句的执行状态信息，是主语言能够据此信息控制程序流程，主要用SQL通信区（SQLCA）实现

（2）主语言向SQL语句提供参数，主要用主变量实现

（3）将SQL语句查询数据库的结果交主语言处理，主要用主变量和游标实现

SQL通信区(SQL Communication Area)

定义exec sql include sqlca

SQL通信区中有一个变量SQLCODE，用来存放每次执行SQL语句后返回的代码

应用程序没执行完一条SQL语句之后都应该测试一下SQLCODE的值，以了解该SQL语句执行情况并做相应处理。如果SQLCODE等于预定义的常量SUCCESS，则表示SQL语句成功，否则在SQLCODE存放错误代码。

主变量

SQL语句中使用的主语言程序变量

输入主变量：由应用程序赋值，SQL语句使用

输出主变量：由SQL语句对其赋值或设置状态信息，返回给应用程序

一个主变量可以附带一个任选的指示变量（一个整型变量，用来“指示”所指主变量的值或条件）

指示变量可以指示输入主变量是否为空值，可以检测输出主变量是否为空值，值是否被截断

所有主变量和指示变量必须在SQL语句（begin declare section 与 end declare section之间进行说明），说明之后，主变量可以在SQL语句中任何一个能够使用表达式的地方（where、having）出现，为了与数据库对象名（表名、视图名、列名等）区分，SQL语句中的主变量名和指示变量前要加冒号（：）作为标志

游标

SQL是面向集合的，一条SQL语句可以产生或处理多条记录；而主语言是面向记录的，一组主变量一次只能存放一条记录。所以仅使用主变量并不能完全满足SQL语句向应用程序输出数据的要求，为此嵌入式SQL引入了游标的概念，用游标来协调这两种不同的处理方式。

游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果，每个游标区都有一个名字。用户可以通过游标逐一获取记录并赋值给主变量，交由主语言进一步处理。

建立和关闭数据库连接

（1）建立数据库连接

exec sql connect to target[as connection-name][user user-name];

target是要连接的数据库服务器，它可以是一个常见的服务器标识串，如<dbname>@<hostname>:<port>，可以是包含服务器标识的SQL串常量，也可以是default

connection-name是可选的连接名（有效的标识符），主要用来识别一个程序内同时建立的多个连接，如果在整个程序内只有一个连接，也可以是不指定连接名。

如果程序运行过程中建立了多个连接，执行的所有数据库单元的工作都在该操作提交时所选择的当前连接上。程序运行过程中可以修改当前连接，对应的嵌入式SQL语句为：

exec sql set connection connection-name|default;

（2）关闭数据库连接

当某个连接上的所有数据库操作完成后，应用程序应该主动释放所占用的连接资源。关闭数据库的嵌入式SQL语句是：

exec sql disconnect [connection];

例8.1依次检查某个系的学生记录，交互式更新某些学生年龄

exec sql begin declare section;  /*主变量说明开始*/
    char deptname[20];
    char hsno[9];
    char hsname[20];
    char hssex[2];
    int HSage;
    int NEWAGE;
exec sql end declare section;   /*主变量说明结束*/
long sqlcode;
exec sql include sqlcode;       /*定义SQL通信区*/
int main(void)                  /*C语言主程序开始*/
{
    int count=0
    char yn;                    /*变量yn代表yes或no*/
    printf("Please choose the department name(CS/MA/IS):");
    scanf("%s,&deptname");      /*为主变量deptname赋值*/
    exec sql connect to test@localhost:54321 user "system"/"manager";  /*连接数据库test*/
    exex sql declare sx cursor for      /*定义游标sx*/
        select Sno,Sname,Ssex,Sage      /*sx对应的语句*/
        from Student
        where SDept=:deptname;
    exec sql open sx;              /*打开游标sx，指向查询结果的第一行*/
    for(;;)                        /*用循环结构逐条处理结果集中的记录*/
    {
        exec sql fetch sx into:HSno,:HSname,:HSsex,:HSage;  /*推进游标，将当前数据放入主变量*/
        if(sqlca.sqlcode!=0)      /*sqlcode！=0，表示操作不成功*/
            break;                /*利用sqlcode中的状态信息决定何时退出循环*/
        if(count++==0)            /*如果是第一行的话，先打出行头*/
            printf("\n%-10s %-20s %-10s %-10s\n","Sno","Sname","Ssex","Sage");
            printf("%-10s %-20s %-10s %-10d\n",HSno,HSname,HSsex,HSage);  /*打印查询结果*/
            printf("uopdate age(y/n)?");     /*询问用户是否要更新该学生的年龄*/
            do{scanf("%c",&yn);}
            while(yn!='N'&&yn!='n'&&yn!='Y'&&yn!='y');
            if(yn=='y'||yn=='Y')            /*如果选择更新操作*/
                {
                printf("input new age:");
                scanf("%d",&NEWAGE);        /*用户输入新年龄到主变量中*/
                exec sql update Student     /*嵌入式SQL更新语句*/
                    set Sage=:NEWAGE
                    where current of sx;    /*对当前游标指向的学生年龄进行更新*/

                }
        exec sql close sc;                  /*关闭游标sx，不再和查询结果对应*/
        exec sql commit work;                /*提交更新*/
        exec sql disconnect test;             /*断开数据库连接*/
    }
}

不用游标的SQL语句

说明性语句、数据定义语句、数据控制语句、查询结果为单记录的select语句、非current形式的增删改查语句

查询结果为单记录的select语句

因为查询结果只有一个，只需用into子句指定存放查询结果的主变量，不需要使用游标

例8.2根据学生号码查询学生信息

exec sql select Sno,Sname,Ssex,Sage,Sdept
    into :Hsno,:Hname,:Hsex,:Hage,:Hdept
    from Student
    where Sno=:givesno;    /*把要查询的学生的学号赋给了主变量givesno*/

使用查询结果为单记录的select语句需要注意以下几点：

（1）into子句、where子句和having短语的条件表达式中均可以使用主变量

（2）查询结果为空值的处理。查询返回的记录中可能某些列为空值NULL。为了表示空值，在into子句中的主变量后面跟有指示变量，当查询得出的某个数据项为空值时，系统会自动将相应主变量后面的指示变量置为负值，而不再是向该主变量赋值。所以当指示变量值为负值时，不管主变量为何值，均认为主变量值为NULL

（3）如果查询结果实际上并不是单条记录，而是多条记录，则程序出错，关系数据库管理系统会在SQL通信区中返回错误信息

例8.3查询某个学生选修某门课程的成绩。假设已经要把查询的学生的学号赋给了主变量givesno，将课程号赋给了主变量givesno

exec sql select Sno,Cno,Grade
    into :Hsno,:Hcno,:Hgrade:Gradeid        /*指示变量Gradeid */
    from sc
    where Sno=:givesno and Cno=:givecno;

如果Gradeid<0，则不论Hgrade为何值均认为该学生成绩为空值

非current形式的增删改语句

有些非current形式的增删改查语句不需要使用游标。在update的set子句和where子句中可以使用主变量，set子句还可以使用指示变量

例8.4修改某个学生选修1号课程的成绩

exec sql update sc
    set Grade=:newgrade              /*修改的成绩已赋值给主变量：newgrade*/
    where Sno=:givensno and Cno=1;    /*学号已赋给主变量：givensno*/

例8.5某个学生新选修了某门课程，将有关记录插入sc表中。假设插入的学号已赋给主变量stdno，课程号已赋给主变量couno，由于该学生刚选修课程，成绩应为空，所以要把指示变量赋为负值

gradeid=-1;
exec sql insert
    into sc(Sno,Cno,Grade)
    values(:stdno,:couno,:gradeid);

使用游标的SQL语句

必须使用游标的SQL语句有查询结果为多条记录的select语句、current形式的update和delete语句

查询结果为多条记录的select语句

一般情况下，select语句查询结果是多条记录，因此需要用游标机制将多条记录一次一条地送主程序处理，从而把对集合的操作转换为对单个记录的处理。使用游标的步骤为：

（1）说明游标

用declare语句为一条select语句定义游标：

exec sql declare <游标名> cursor for <select语句>;

定义游标仅仅是一条说明性语句，这时关系数据库管理系统并不执行select语句

（2）打开游标

用open语句将定义的游标打开

exec sql open <游标名>;

打开游标实际上是执行相应的select语句，把查询结果取到缓冲区中。这时游标处于活动状态，指针指向查询结果集中的第一条记录

（3）推进游标指针并取当前记录

exec sql fetch <游标名>

into <主变量> [<指示变量>] [,<主变量>[<指示变量>]]…;

其中主变量必须与select语句中目标列表达式具有一一对应关系

用fetch语句把游标指针向前推进一条记录，同时将缓冲区中的当前记录取出来送至主变量供主语言进一步处理。通过循环执行fetch语句逐条取出结果集中的行进行处理。

（4）关闭游标

用close语句关闭游标，释放结果集占用的缓冲区以及其他资源

exec sql close <游标名>;

游标被关闭后就不再和原来的查询结果集相联系。但关闭的游标可以再次被打开，与新的查询结果相联系。

current形式的update和delete语句

update语句和delete语句中要用子句where current of <游标名> 来表示修改或删除的是最近一次取出的记录，即游标指针指向的记录

动态SQL

SQL语句变量

程序主变量包含的内容是SQL语句的内容，而不是原来保存数据的输入或输出变量

例8.7创建基本表test

exec sql begin declare section;
    const char* stmt="create table test(a int);";  /*SQL语句主变量，内容是创建表的SQL语句*/
exec sql execute immediate :stmt;    /*执行动态SQL语句*/

动态参数

动态参数是SQL语句中的可变元素，使用参数符号（？）表示该位置的数据在运行时设定。和前面使用的主变量不同，动态参数的输入不是编译时完成绑定，而是通过prepare语句准备主变量和执行语句execute绑定数据或主变量来完成。使用动态参数的步骤如下：

（1）声明SQL语句主变量

SQL语句主变量的值包含动态参数（？）

（2）准备SQL语句（prepare）

prepare将分析含主变量的SQL语句内容，建立语句中包含的动态参数的内部描述符，并用<语句名>标识它们的整体

exec sql prepare <语句名> from <sql语句主变量>

执行准备好的语句（execute）

execute将SQL语句中分析出的动态参数和主变量或数据常量绑定，作为语句的输入或输出变量

exec sql execute <语句名> [into <主变量名>] [using <主变量或常量>];

例8.8 向test中插入元组

exec sql begin declare section;
    const char*stmt="insert into test values(?);";  /*声明SQL主变量内容是insert语句*/

exec sql end declare section;
……
exec sql prepare mystmt from :stmt;    /*准备语句*/
……
exec sql execute mystmt using 100;    /*执行语句，设定insert语句插入值100*/
exec sql execute mystmt using 200;

过程化SQL

过程化SQL的块结构

过程化SQL程序的基本结构是块。块之间可以互相嵌套，每个块完成一个逻辑操作

变量和常量的定义

变量定义

变量名数据类型 [[not null]:=初值表达式] 或者 [[not null] 初值表达式]

常量的定义

常量名数据类型 constant ;=常量表达式

赋值语句

变量名:=表达式

流程控制

条件控制语句

if-then语句

if-then-else

嵌套的if语句

循环控制语句

loop

while-loop

for-loop

错误处理

异常处理

存储过程和函数

命名块：过程和函数是命名块，它们被编译在数据库中

匿名块：每次执行时都要进行编译，它不能被存储器存储到数据库中，也不能再其他过程化SQL块中调用

存储过程

存储过程是由过程化SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，因此称它为存储过程，使用时只要调用即可

1.存储过程的优点

运行效率高
降低了客户机和服务器之间的通信量
方便实施企业规则

2.存储过程的用接口

用户通过下面的SQL语句创建、执行、修改和删除存储过程

创建存储过程

create or replace procedure 过程名 ([参数1,参数2,…])

as <过程化SQL块>;

参数值必须指定值的数据类型，可以定义输入参数、输出参数或输入/输出参数，默认是输入参数，也可以是无参数

过程体是一个<过程化SQL>，包括声明部分和可执行语句部分，<过程化SQL>在“过程化SQL”中体现

例8.9利用存储过程实现下面的应用：从账户1转指定的款到账户2中。假设账户关系表为Account(Accountnum,Total)

/*定义存储过程transfer，其参数为转入账户、转储账户、转账额度*/
create or replace procedure transfer(inAccount int,outAccount int,amount float)
as declare     /*定义变量*/
    totalDepositOut float;
    totalDepositIn float;
    inAccountnum int;
begin
    select Total into totalDepositOut from Account where accountnum=outAccount;
    if totalDepositOut is null then
        rollback;
        return;
    end if;
    if totalDepositOut < amout then
        rollback;
        return;
    end if;
    select Accountnum into inAccountnum from Account where inAccountnum=inAccount;
    if inAccountnum is null then
        rollback;
        return;
    end if;
    update Account set total=total-amount where accountnum=outAccount;
    /*修改转出账户的余额，减去转出额*/
    update Account set total=total+amount where accountnum=inAccount;
    /*修改转入账户的余额，增加转入额*/
    commit;    /*提交转账事务*/
end;

执行存储过程

call/perform procedure 过程名([参数1,参数2],…)

使用call/perform等方式激活存储过程的执行。在过程化SQL中，数据库服务器支持在过程体中调用其他存储过程

例8.10从账户01003815868转10 000元到01003813828账户中

call procedure transfer(01003813828,01003815868,10000);

修改存储过程

可以使用alter procedure重命名一个存储过程：

alter procedure 过程名1 rename to 过程名2;

可以使用alter procedure重新编译一个存储过程：

alter procedure 过程名 compile;

删除存储过程

drop procedure 过程名();

函数

函数的定义和存储过程也类似，不同之处是函数必须指定返回的类型

1.函数的定义语句格式

create or replace function 函数名 ([参数1,参数2,…]) return <类型>

as <过程化SQL块>;

2.函数的执行语句格式

call/select 函数名([参数1,参数2,…]);

3.修改函数

可以使用alter function重命名一个自定义函数：

alter function 函数名1 rename 函数名2；

可以使用alter function重新编译一个函数：

alter function 函数名 compile;

ODBC编程

ODBC驱动程序管理器

包含在ODBC32.DLL中

第十章　数据库恢复技术

事务的基本概念

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

在关系数据库中，一个事务可以是一条SQL语句、一组SQL语句或是整个程序

事务和程序是两个概念。一般的讲，一个程序包含多个事务
begin transaction;
commit;
rollback;
如果用户没有显示地定义事务，则由数据库管理系统默认规定自动划分事务

commit表示提交，提交事务的所有操作，将事务中所有对数据的更新写回到磁盘上的物理数据库中去，事务正常结束

rollback表示回滚，在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成操作全部撤销，回滚到事务开始时的状态

事务的ACID特性

Atomicity（原子性）

事务是数据库的逻辑工作单位，事务中包括的诸操作要么都做，要么都不做

Consistency（一致性）

事务的执行结果必须是使数据库从一个一致性状态变到另一个一致性状态

银行两个账户存取

Isolation（隔离性）

一个事务的执行不能被其他事务干扰。一个事务的内部操作及使用的数据对其他并发事务是隔离的，并发执行的各个事务之间不能互相干扰。

Durability（持续性）

永久性，一个事务一旦提交，它对数据库中数据的改变就应该是永久性的

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

事务ACID特性可能遭到破坏的因素有：

（1）多个事务并行运行时，不同事务的操作交叉执行；

（2）事务在运行过程中被强行停止

数据库恢复概述

硬件的故障、软件的错误、操作员的失误以及恶意的破坏仍是不可避免的，这些故障轻则造成运行事务非正常中断，影响数据库中数据的正确性，重则破坏数据库，使数据库中全部或部分数据丢失，因此数据库管理系统必须具有把数据库从错误状态恢复到某一已知的正确状态的功能。

故障的种类

事务内部的故障

事务内部的故障是非预期的，是不能由应用程序处理的。如运算溢出、并发事务发生死锁而被选中撤销该事物、违反了某些完整性限制而被终止等。

事务撤销UNDO

系统故障

系统故障是指造成系统停止运转的任何事件，使得系统要重新启动

例如，特定类型的硬件错误（CPU故障）、操作系统故障、DBMS代码错误、系统断电等。这类故障影响正在运行的所有的事务，但不破坏数据库。

此时主存内容，尤其是数据库缓冲区（在内存）中内容都被丢失，所有运行事务都非正常终止。

恢复子系统必须在系统重新启动时让所有非正常终止的事务回滚，强行撤销所有未完成事务。

另一方面，发生系统故障是，有些已经完成的事务可能有一部分甚至全部留在缓冲区，尚未写回到磁盘上的物理数据库中，系统故障使得这些事务对数据库的修改部分或全部丢失，这也会使数据库处于不一致状态，因此应将这些事务已提交的结果重新写入数据库。所有系统重新启动后，恢复子系统除需要撤销所有未完成的事务外，还需要重做（REDO）所有已提交的事务，以将数据库真正恢复到一致状态。

介质故障

系统故障---软故障，介质故障---硬故障，指外存故障，如磁盘损坏、磁头碰撞，瞬时强磁场干扰等

计算机病毒

总结各类故障对数据库的影响有两种可能：

一是数据库本身被破坏

二是数据库没有被破坏，但数据可能不正确

恢复原理------冗余

恢复的实现技术

建立冗余数据最常用的技术是数据转储和登记日志文件

数据转储

转储：数据库管理员定期地将整个数据库复制到磁带、磁盘或其他存储介质上保存起来的过程。这些备用的数据称为后备副本或者后援副本。

当数据遭到破坏后可以将后备副本重新装入，但重装后备副本只能将数据库恢复到转储的状态，要想恢复到故障发生时的状态，必须重新运行自转储以后的所有更新事务。

静态转储

无运行事务时进行的转储操作。转储操作开始的时刻数据库处于一致性状态，而转储期间不允许对数据库的任何存取、修改活动。

简单，但转储必须等待正运行的用户事务结束才能进行，新事物必须等待转储结束才能执行

动态转储

转储期间允许对数据库进行存取或修改。转储和用户事务可以并发执行

后援副本+日志文件，恢复到某一时刻的正确状态

海量：每次转储全部数据库

增量：每次只转储上一次转储后更新后的数据

登记日志文件

日志文件时用来记录事务对数据库的更新操作的文件。

以记录为单位

日志文件中登记的内容：

事务的开始、结束

事务的所有更新操作

每个日志记录的内容：

事务标识（标明是哪个事务）

操作的类型（插入、删除或修改）

操作对象（记录内部标识）

更新前数据的旧值（插入的话为空）

更新后数据的新值（删除的话为空）

以数据块为单位

事务标识

被更新的数据块

日志文件的作用

（1）事务故障恢复和系统故障恢复必须用日志文件

（2）在动态转储方式中必须建立日志文件，后备副本和日志文件结合起来才能有效地恢复数据库

（3）在静态转储方式中也可以建立日志文件，当数据库毁坏后可重新装入后援副本把数据库恢复到转储结束时刻的正确状态，然后利用日志文件把已完成的事务进行重做处理，对故障发生尚未完成的事务进行撤销处理

登记日志文件

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

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

恢复策略

事务故障的恢复

系统的恢复步骤：

（1）反向扫描日志文件，查找该事务的更新操作

（2）对该事务的更新操作执行逆操作，即将日志记录中“更新前的值”写入数据库

插入---相当于删除操作（更新前的值为空）

删除---相当于插入操作

修改---相当于用修改前值代替修改后值

（3）继续反向扫描日志文件，查找该事务的其他更新操作，并做同样处理

（4）如此处理下去，直至读到此事务的开始标记

系统故障的恢复

系统的恢复步骤：

（1）正向扫描日志文件

（2）对撤销队列的各个事务进行撤销（UNDO）处理

（3）对重做队列中各个事务进行重做处理

介质故障的恢复

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

具有检查点的恢复技术

检查点记录包括：

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

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

动态维护日志文件的方法是，周期性地执行建立检查点、保存数据库状态的操作

使用检查点方法可以改善恢复效率

数据库镜像

用于数据库恢复，根据数据库管理员的要求，自动把整个数据库或其中的关键数据复制到另一个磁盘上，每当主数据库更新时，数据库管理系统自动把更新后的数据复制过去，自动保证镜像数据与主数据库的一致性。

第十一章并发控制

当多个用户并发地存取数据库时就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会存取和存储不正确的数据，破坏事务的隔离性和数据库的一致性，所以数据库管理系统必须提供并发控制机制。

并发控制概述

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

为保证事务的隔离性和一致性，数据库管理系统需要对并发操作进行正确调度。

并发事务带来的数据不一致性包括丢失修改、不可重复读和读“脏”数据

事务读数据x记为R(x)，写数据x记为W(x)

1.丢失修改

两个事物T1和T2读入同一数据并修改，T2提交的结果破坏了T1提交的结果，导致T1的修改被丢失

2.不可重复读

事务T1读取某一数据后，事务T2执行更新（修改、删除、插入）操作，使T1无法再现前一次读取结果

3.读“脏”数据

事务T1修改某一数据并将其写回磁盘，事务T2读取同一数据后，T1由于某种原因被撤销，这时被T1修改过的数据恢复原值，T2读到的数据就与数据库中的数据不一致，则T2读到的数据就为“脏数据”。

上面三类数据不一致性的主要原因是并发操作破坏了事务的隔离性。并发控制机制就是要用正确的方式调度并发操作，使一个用户事务的执行不受其他事务的干扰，从而避免造成数据的不一致性

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

封锁

加锁

排他锁（写锁、X锁）

若事务T对数据对象A加上X锁，则只允许T读取和修改A，其他任何事物都不能再对A加任何类型的锁，直到T释放A上的锁为止。这就保证了其他事务在T 释放A上的锁之前不能读取和修改A。

共享锁（读锁、S锁）

若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能在对A加S锁，而不能加X锁，直到T释放A上的S锁为止。这就保证了其他事务可以读A，但在T释放A上的锁之前不能对A做任何修改。

封锁协议

何时申请X锁或S锁、池锁时间、何时释放等

一级封锁协议

事务T在修改数据R之前必修西安对其加X锁，直到事务结束才释放。

一级封锁协议可防止丢失修改，并保证事务T是可恢复的，不能保证可重复读和不读“脏”数据

二级封锁协议

在一级封锁协议的基础上增加事务T在读取数据R之前必修现对其加S锁，读完后即可释放S锁

二级封锁协议除防止了丢失数据，还可进一步防止读“脏”数据，不能保证可重复读

三级封锁协议

在一级封锁协议的基础上增加了事务T在读取数据R之前必须先对其加S锁，直到事务结束才释放

三级封锁协议除了防止丢失修改和读“脏”数据外，还进一步防止了不可重复读

封锁协议级别越高，一致性越高。

活锁和死锁

活锁

避免活锁的简单方法是先来先服务的策略

死锁

死锁的预防

一次封锁法

每个事务必须一次将所有使用的数据全部加锁

顺序封锁法

预先对数据对象规定一个封锁顺序

死锁的诊断与解除

超时法

一个事务的等待时间超过了规定的期限，就认为发生了死锁

等待图法

存在回路，表示出现了死锁

并发调度的可串行性

可串行化调度

多个事务的并发执行是正确的，当且仅当其结果与按某一次串行地执行这些事务的结果相同，称这种调度为可串行化调度

冲突可串行化调度

判断可串行化调度的充分条件

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

一个调度Sc在保证冲突操作的次序不变的情况下，通过交换两个事务不冲突操作的次序得到另一个调度Sc'，如果Sc'是串行的，称调度Sc为冲突可串行化的调度。若一个调度是冲突可串行化，则一定是可串行化的调度，用这种方法来判断一个调度是否是冲突可串行化的

例11.3 今有调度Sc1=r1(A)w1(A)r2(A)w2(A)r1(B)w1(B)r2(B)w2(B)

把w2(A)与r1(B)w1(B)交换，得到

r1(A)w1(A)r2(A)r1(B)w1(B)w2(A)r2(B)w2(B)

再把r2(A)与r1(B)w1(B)交换

Sc2=r1(A)w1(A)r1(B)w1(B)r2(A)w2(A)r2(B)w2(B)

Sc2等价于一个串行调度T1、T2，所以Sc1为冲突可串行化的调度

冲突可串行化调度是可串行化调度的充分条件，不是必要条件