第二章 关系数据库
关系数据模型
关系代数
SQL
完整性和安全性
关系范式
对象关系数据库
2.1 关系数据模型关系模型的理论基础是集合论，是用集合代数定义一个关系。
定义 1,域 (Domain)是一组具有相同数据类型的值的集合。
定义 2,设 D1,D2,…,Dn为一组域,D1,D2,…,Dn
上的笛卡尔积定义为：
D1? D2? …? Dn = {(d1,d2,…,dn) |
di? Di,i =1,2,…,n}
笛卡尔积是一个集合，集合中的每一个元素 (d1,d2，
…,dn) 称为一个 n元组，简称元组。元组中的每个值叫做一个分量。
定义 3,D1? D2? …? Dn笛卡尔积的子集叫做在域 D1,D2，
…,Dn上的关系 (Relation)。用 R(D1,D2,…,Dn)表示,R
是关系名。
关系是在一组域（ D1,D2,…,Dn）上的笛卡尔积的一个子集。当 n=1时，称为单元关系； 当 n=2时，称为二元关系。
关系是一个二维表，表的每一行对应一个元组，表的每列对应一个域，由于域可以相同，为了区分给每个列起一个名字，称为属性。
对关系的描述称为关系模式,该描述 包括关系名,
关系中的属性名,属性向域的映象,属性间的数据依赖关系等,可以形式化的表示为,R(U,F)或 R(U)。
其 中 R为关系名,U为组成该关系的属性名集合,F
为属性间的数据依赖关系集合。 属性向域的映象通常直接说明为属性的类型、长度、取值范围等。
有三个域：男人、女人和儿童。其取值为：男人（张三
，李四），女人（王，吴），儿童（张一，李二）。
其笛卡尔积：男人?女人?儿童共有 8个元组，可以列成一张表：
（张三，王，张一），（李四，王，张一）,
（张三，王，李二），（李四，王，李二）,
（张三，吴，张一），（李四，吴，张一）,
（张三，吴，李二），（李四，吴，李二）。
丈夫 妻子 小孩张三 王 王一李四 吴 李二家庭 （丈夫，妻子，小孩）。 该关系中的属性名就用域名,关系形成的二维表如下：
关系是笛卡尔积中有一定意义的、有限的子集。
Jones
Smith
Curry
Lindsay
customer_name
Main
North
North
Park
customer_street
Harrison
Rye
Rye
Pittsfield
customer_city
customer
属性元组
Students学生信息表
SNO 学号
Sname 姓名
Sex 性别
Age 年龄
Class 班级
DeptNO 系编号
S_C 学生选课信息表
SNO 学号
Cno 课程编号
Grade 成绩
Courses 信息表
Cno 课程编号
Cname 课程名称
credit 学分数
DEPT 系信息表
DeptNO 系编号
Deptname 系名称
Address 地址
CUSTOMERS顾客信息表
cid 顾客 ID
cname 顾客姓名
city 顾客所在城市
discnt 顾客可能会有的折扣
AGENTS 代理商信息表
aid 代理商 ID
aname 代理商名称
city 代理商所在城市
percent 代理商每笔交易所能获得的佣金百分比
PRODUCTS 商品信息表
pid 商品 ID
pname 商品名称
city 商品库存所在城市
quantity 商品库存数量
price 商品批发价
ORDERS 订单信息表
ordno 订单 ID
Month 订单月份
cid 顾客 ID
aid 代理商 ID
pid 商品 ID
qty 数量
dollars 商品总价在关系数据库中的关系有如下性质：
同一列来自同一个域。
不同的列可以出自相同的域，必须有不同的属性名。
不能有完全相同的元组存在。
关系中元组的顺序无关。
关系中列的顺序无关。
每个属性值必须是不可再分的数据项。
完整性约束规则
1 候选键：关系模式 R（ U） 的属性集合 K是候选键,满足：
（ 1） R（ U） 的任何一个关系实例的任意两个元组在属性集合 K上的值都不相同；
（ 2） K的任何真子集都不满足条件 （ 1） 。
以上被称为候选键的两性质：唯一性,最小性 。
相关概念：主键 /关键字；主属性；非主属性；
例,Student ( NO,Name,Sex,Age,Class,DeptNO ),
Course ( Cno,Cname,Dept ),
S_C (NO,Cno,Grade)
实体完整性约束：
若 A是关系模式 R（ U） 的主属性,则 R（ U） 的任何一个实例关系不存在任何元组在 A上的值为空 。
2 关联 /参照完整性约束：
设 X是关系模式 R（ U） 关于关系模式 R?(U?)的外部键 。 如果
K是 R（ U） 的关系实例的一个元组的外部键值,则 R?(U?)的实例中必存在一个元组 T,T在 X上的值为 K。
例,Student ( NO,Name,Sex,Age,Class,
DeptNO ),
Dept (DeptNO,DeptName)
更新时的参照完整性约束：
为了保持参照完整性约束必须尽心如下检查,
X (r2)K (r1)
插入,如果元组 t2 被插入到 r2,则系统必须确保 r1中存在元组 t1 使得 t1[K] = t2[X],即
t2 [X]K (r1)
删除,如果从 r1删除元组 t1,则系统必须计算 r2中引用 t1的元组集合,
X = t1[K] (r2)
如果此集合非空,则要么认为出错而拒绝删除命令,要么删除这些引用 t1的元组 (级联删除 ),
Foreign Key? reference table r1 on
delete/update [cascade/set null ]
修改,有两种情况,
如果修改关系 r2中的元组 t2 并且修改了外键 X的值,则作类似于插入情况的检查,令 t2? 表示元组 t2的新值,系统必须确保
t2?[X]K(r1)
如果修改关系 r1中的元组 t1并且修改了主键 K的值,则作类似于删除情况的检查,系统必须利用 t1在修改前的旧值计算
X = t1[K] (r2)
若此集合非空,则要么认为出错而拒绝修改,要么对此集合中的元组作级联修改,要么删除此集合中的元组
,
域约束：
域约束是完整性约束的最基本形式,
可用于检测插入到数据库中的数据的合法性,或用于查询中检测两个数据进行比较时的合法性,
check子句允许对域作限制,
利用 check 子句确保“小时工资”域只允许大于指定值的值,
create domain hourly_wage numeric(5,2)
constraint value_test check(value > = 4.00)
这个域具有约束：小时工资大于 4.00
子句 constraint value_test 使可选的 ; 可用于指示以后的更新破坏了哪一条约束,
可有更复杂的域检查条件
Create domain AccountType char(10)
constraint account_type_test
check (value in (?Checking?,?Saving?))
check (branch_name in (select branch_name from branch))
2.2 关系代数关系代数是过程化的查询语言，它包括一个运算集合。
这些运算以一个或两个关系为输入，产生一个新的关系为结果。
关系代数的基本运算有：
选择投影并集合差笛卡儿积一些附加运算：即 集合交,自然连接,除 和 赋值,附加运算用基本运算来定义。
选择
表示法,? p(r)
p 称为选择谓词
定义,
p(r) = {t | t? r and p(t)}
其中 p 是一命题演算公式,由项经连接词?
(and),? (or),? (not) 连接而成,
项形如,
<属性 >op <属性 > 或 <常量 >
其中 op 包括,=,?,>,?,<,?
student_no=10(account)
Relation r A B C D
1
5
12
23
7
7
3
10
A=B ^ D > 5 (r)
A B C D
1
23
7
10
投影
表示法,
A1,A2,…,Ak (r)
其中 A1,A2,.,是属性名,r 是关系名,
结果定义为由指定的 k 列组成的关系
删除结果中的重复行,因为关系是集合
例
student_name,sno (account)
Relation r,A B C
10
20
30
40
1
1
1
2
A C
1
1
1
2
=
A C
1
1
2
A,C
(r)
并运算
表示法,r? s
定义,
r? s = {t | t? r or t? s}
r? s 合法必须要求,
1,r,s 具有相同元数 (属性数相同 )
2,属性域必须兼容 (例如,r 的第 2列与 s 的第 2列处理相同类型的值 )
例：
customer_name (depositor)customer_name
(borrower)
r? s:
Relations r,s,A B
1
2
1
A B
2
3
r
s
A B
1
2
1
3
差运算
表示法,r – s
定义,
r – s = {t | t? r and t? s}
集合差必须在兼容关系之间进行,
r 和 s 必须具有相同元数
r 和 s 的属性域必须兼容
r – s:
Relations r,s,A B
1
2
1
A B
2
3
r
s
A B
1
1
笛卡尔乘积：
表示法,r x s
定义,
r x s = {t q | t? r and q? s}
假设 r(R) 和 s(S) 的属性是不相交的,(即 R? S =? ).
若 r(R) 和 s(S) 的属性不是不相交的,则需重命名,
r x s:
A B C D E
1
1
1
1
2
2
2
2
10
19
20
10
10
10
20
10
a
a
b
b
a
a
b
b
Relations r,s,A B
1
2
C D
10
10
20
10
E
a
a
b
b
下列附加运算并不能增强关系代数的能力,但可以简化常见查询：
交集
自然连接
除法交集运算
表示法,r? s
定义,
r? s ={ t | t? r and t? s }
假设,
r,s 具有相同元数
r 和 s 的属性兼容
注,r? s = r - (r - s)
Relation r,s,A B
1
2
1
A B
2
3
r s
A B
2
r? s
连接运算
表示法,r A?B s
r A?B s = {t q | t? r and q? s and (t[A]?q[B])}
=? A?B (r?s)
{=,?,?,?,>,<}
Relations r,s,A B
3
6
1
2
C D
1
4
7
10
2
5
8
11
E
3
6
9
12
r A>D s
A B C D E
3 1 1 2 3
6 2 1 2 3
6 2 4 5 6
自然连接：
表示法,r s
令 r 和 s 分别是模式 R 与 S 上的关系,自然连接的结果是模式 R? S 上的关系,该关系是通过考虑 r 和 s 的每一对元组 tr 及 ts 而得到的,
如果 tr 和 ts 在每个 R? S中属性上具有相同值,则向结果中加入一条元组 t,其中
t 与 tr 在 r 上的值相同
t 与 ts 在 s上的值相同
A B C D E
1
1
1
1
2
a
a
a
a
b
Relations r,s:
A B
1
2
4
1
2
C D
a
a
b
a
b
B
1
3
1
2
3
D
a
a
a
b
b
E
r s
r s
外连接
连接运算的扩展，避免丢失信息,
在计算连接的基础上再向结果中加入那些没有匹配的元组,
用到空值 null,
– null 意思是不知道或不存在的值
– 所有涉及 null 的比较粗略地说定义为 false.
以后研究涉及空值的比较的准确意义外连接 – 例关系 loan
loan-number amount
L-170
L-230
L-260
3000
4000
1700
关系 borrower
customer-name loan-number
Jones
Smith
Hayes
L-170
L-230
L-155
branch-name
Downtown
Redwood
Perryridge
内连接
loan Borrower
loan borrower
左外连接
loan-number amount
L-170
L-230
3000
4000
customer-name
Jones
Smith
branch-name
Downtown
Redwood
loan-number amount
L-170
L-230
L-260
3000
4000
1700
customer-name
Jones
Smith
null
branch-name
Downtown
Redwood
Perryridge
右外连接 loan borrower
loan-number amount
L-170
L-230
L-155
3000
4000
null
customer-name
Jones
Smith
Hayes
loan-number amount
L-170
L-230
L-260
L-155
3000
4000
1700
null
customer-name
Jones
Smith
null
Hayes
loan borrower
全外连接
branch-name
Downtown
Redwood
null
branch-name
Downtown
Redwood
Perryridge
null
适合表达带有,所有,短语的查询,
令 r 和 s 分别是模式 R 和 S 上的关系,其中
R = (A1,…,Am,B1,…,Bn)
S = (B1,…,Bn)
则 r? s 的结果是模式 R – S = (A1,…,Am)上的关系
r? s = { t | t R－ S(r) u? s ( tu? r ) }
除法运算,r? s
A B
a b
e d
Relations r,s:
A B C D
a b c d
a b e f
b c e f
e d c d
e d e f
a b d e
r
C D
c d
e f
s
r? s
R－ S( r )–? R－ S( (? R－ S( r )× s) – r)
例：
branch (branch_name,branch_city,assets)
customer (customer_name,customer_street,customer_only)
account (account_number,branch_name,balance)
loan (loan_number,branch_name,amount)
depositor (customer_name,account_number)
borrower (customer_name,loan_number)
求超过 $1200 的所有贷款
amount > 1200 (loan)
求数额超过 $1200 的所有贷款的贷款号
loan_number (?amount > 1200 (loan))
求所有在银行有贷款或账户的客户的姓名
customer_name (borrower)customer_name (depositor)
求 所有在银行有贷款且有账户的客户的姓名
customer_name (borrower)customer_name (depositor)
求在 Perryridge分行有贷款的所有客户的姓名
customer_name (?branch_name=“Perryridge”
(?borrower.loan_number = loan.loan_number(borrower x loan)))
求 在 Perryridge分行有贷款但在任何分行都没有账户的客户的姓名
customer_name (?branch_name =,Perryridge”
(?borrower.loan_number = loan.loan_number(borrower x loan)))
–?customer_name(depositor)
求所有至少在,Downtown” 与,Uptown”分行有账户的客户查询 1
CN(?BN=“Downtown”(depositor account))?
CN(?BN=“Uptown”(depositor account))
其中 CN 表示 customer_name,BN 表示
branch_name.
查询 2
customer_name,branch_name (depositor
account)
temp(branch_name) ({(“Downtown”),(“Uptown”)})
求在位于 Brooklyn的所有分行都开有账户的客户
customer_name,branch_name (depositor account)
branch_name (?branch_city =,Brooklyn” (branch))
S ( SNO,SName,Sex,Age,Class,Dept ),
C ( Cno,Cname,Dept ),
SC (SNO,Cno,Grade)
（ 1）?SNO,GRADE(?CNO=’C2’(SC))
（ 2）?SNO,SNAME(?CNO=’C2’(S SC)
（ 3）?SNO,SNAME(?CNAME=’DB’(S SC C)
（ 4）?SNO,SNAME(S)SNO,SNAME(?CNO=’C2’ (S SC)
（ 5）? SNAME (S (?SNO,CNO(SC)÷?CNO(C)))
运算符 符号 含义 键盘格式 示例集合运算符
∪ 并 UNION R∪S,或 R UNION S
∩ 交 INTERSECT R∩S,或 R INTERSECT S
- 差 MINUS R-S，或 R MINUS S
× 乘 TIMES R× S,或 R TIMES S
专门关系运算符
σ 选择 R where C σ 姓名 =“张三,(S)或
S where 姓名 =?张三 ’
π 投影 R[ ] π 考号，姓名 (S）或 S[考号,姓名 ]
∞ 连接 JOIN R∞S,或 R JOIN S
÷ 除 DIVIDEBY R÷ S，或 R DIVIDEBY S
自从 20世纪 80年代以来,SQL就是一个通用的、功能极强的关系数据库语言。现在,SQL语言正从关系形式（ ANSI SQL-92标准）
转向对象 -关系形式（ ANSI SQL-99标准,1999年颁布）。
SQL语言是 1974年由 Boyce和 Chamberlin提出。
1986年 10月美国国家标准局 (ANSl)批准了 SQL作为关系数据库语言的美国标准。同年公布了 SQL标准文本 (简称 SQL-86)。 1987年 6
月国际标准化组织 (ISO)也采纳了此标准。
1989年，美国国家标准局（ ANSI）采纳了新的规范 SQL-89标准，
取代 SQL-86，同时 SQL-89标准也被国际标准化组织 (ISO)采纳。
1992年,ANSI/ISO颁布了 SQL2版本，标准的名称为 SQL-92。
SQL-92分称几个顺序级别：从代表 SQL-89最小扩展集的,Entry”
到,Intermediate”和,Full”。完成于 1999年的 SQL-99具有更加高级的特征（包括对象 -关系特性），亦称 SQL3。
主要的几个数据库生产厂商并不可能完全遵守 SQL-99（以及更老的 SQL-92）。
2.3 SQL
SQL的特点
– 1,综合统一
– 2,高度非过程化
– 3,面向集合的操作方式
– 4,以同一种语法结构提供两种使用方法
– 5,语言简洁，易学易用
S Q L 语言的动词
S Q L 功 能 动 词数 据 定 义 CR E A T E,DR O P,A L T E R
数 据 查 询 S E L E CT
数 据 操 纵 I NSE R T,UP DA T E
DE L E T E
数 据 控 制 G RA NT,RE V O K E
基本查询语句
集合运算
聚集函数
空值
嵌套子查询
导出关系
视图
数据库更新
连接关系
DDL
Embedded SQL,ODBC及 JDBC
SQL基于集合与关系运算并作了某些修改和增强
典型的 SQL 查询形如,
select A1,A2,...,An
from r1,r2,...,rm
where P
Ai 是属性
ri 是表
P 是谓词
等价于关系代数表达式
A1,A2,...,An(?P (r1 x r2 x,.,x rm))
SQL 查询的结果是一个关系,
查询基本结构
select 子句对应于关系代数的投影运算,用于列出想要的查询结果中的属性,
从 loan 关系求所有分行的名称
select branch_name
from loan
在,纯,关系代数语法中,对应查询是,
branch_name(loan)
select 子句中用 * 表示,所有属性,
select *
from loan
SQL 是不分大小写的,
select 子句
SQL 允许关系与查询结果中出现重复元组,
可用关键字 distinct 强制删除重复元组,
从 loan 关系求所有分行名并删除重复元组
select distinct branch_name
from loan
select 子句可包含使用 +,–,?,/ 运算以及常量和属性的算术表达式,
select loan_number,branch_name,amount? 100
from loan
将 loan 关系的 amount 属性乘以 100.
WHERE 子句
where 子句对应于关系代数的选择谓词,其中的谓词涉及 from子句中出现的关系的属性,
求 Perryridge分行发出的所有额度超过 $1200 的贷款的贷款号,
select loan_number
from loan
where branch_name =?Perryridge? and amount
> 1200
比较结果可用逻辑连接词 and,or,not 组合在一起,
可对算术表达式的结果进行比较,
select loan_number
from loan
where amount between 90000 and 100000
SQL 包含串匹配运算用于比较字符串,模式用两个特殊字符描述,
百分号 (%),% 字符可与任何字符串匹配,
下划线 (_),_ 字符与任一字符匹配,
求所有街道名称包含子串,Main”的客户的姓名,
select customer_name
from customer
where customer_street like ‘%Main%’
若要匹配串,Main%”则需用
like ‘Main\%’ escape ‘\’
SQL支持许多串操作连接 (,||”)
大小写转换求串长度,取子串,etc.
按字母顺序列出在 Perryridge分行有贷款的客户的姓名
select distinct customer_name
from borrower,loan
where borrower loan_number _ loan.loan_number
and branch_name =?Perryridge?
order by customer_name
可对每个排序属性使用 desc 指定降序,asc 指定升序 ;
升序是缺省顺序,
E.g,order by customer_name desc
谓词 形式 例子比较谓词
BETWEEN谓词量化谓词
IN谓词
EXISTS谓词
IS NULL谓词
LIKE谓词
expr1θ {expr2|(subquery)}
expr1[NOT] BETWEEN expr2 and
expr3
exprθ [SOME|ANY|ALL]
(subquery)
expr [NOT] IN (subquery)
[NOT]EXISTS(subquery)
colname IS [NOT]NULL
cloname [NOT] LIKE val
[ESCAPE val]
p.price>(subquery)
c.discnt between 10.0
and 12.0
c.discnt>=all
(subquery)
pid in (select pid
from orders)
exist(select * …)
c.discnt is null
cname like ‘A%’
FROM子句
from 子句 对应于关系代数的笛卡尔积运算,它列出了查询中要扫描的关系,
求笛卡尔积 borrower x loan
select * from borrower,loan
求所有在 Perryridge分行有贷款的客户的姓名,贷款号和贷款数量,
select customer_name,borrower.loan_number,amount
from borrower,loan
where borrower.loan_number = loan.loan_number
and branch_name =?Perryridge?
元组变量是在 from子句中用 as 子句定义的,
求所有有贷款的客户的姓名和贷款号,
select customer_name,T.loan_number,S.amount
from borrower as T,loan as S
where T.loan_number = S.loan_number
求比位于 Brooklyn的某分行资产多的分行的名字,
select distinct T.branch_name
from branch as T,branch as S
where T.assets > S.assets and S.branch_city = ‘Brooklyn’
集合操作
关系的集合操作 union,intersect,except 对应于关系代数运算
以上操作自动删除重复元组 ; 为了保留所有重复元组应使用对应的多重集版本 union all,intersect all,
except all.
假如一个元组在 r 中发生 m 次,在 s 中发生 n 次,则,
在 r union all s 中发生 m + n 次在 r intersect all s 中发生 min(m,n) 次在 r except all s 中发生 max(0,m – n) 次
求有贷款或账户的客户
(select customer_name from depositor)
union
(select customer_name from borrower)
求既有贷款又有账户的客户
(select customer_name from depositor)
intersect
(select customer_name from borrower)
求只有账户没有贷款的客户
(select customer_name from depositor)
except
(select customer_name from borrower)
聚集函数
以下函数对关系中某一列值的多重集进行计算并返回单个值
avg,平均值
min,最小值
max,最大值
sum,总和
count,值的个数聚集函数经常和 Group BY一起使用：
SELECT [DISTINCT] <Attributes list>
FROM <relations list>
[WHERE < predicate >]
[GROUP BY < Attributes list >
[ HAVING < Attributes list >] ]
求 Perryridge分行的平均账户余额,
select avg (balance)
from account
where branch_name =?Perryridge?
求 customer 关系中的元组个数
select count (*)
from customer
求银行存款人数,
select count (distinct customer_name)
from depositor
求每个分行的存款人数,
select branch_name,count (distinct customer_name)
from depositor,account
where depositor.account_number = account.account_number
group by branch_name
求平均账户余额超过 $1,200的所有分行名及平均余额,
select branch_name,avg (balance)
from account
group by branch_name
having avg (balance) > 1200
注意,having 子句中的谓词是在分组形成之后起作用的,而 where 子句中的谓词是在分组形成之前起作用的嵌套子查询
SQL 提供嵌套子查询的机制,
子查询是嵌在另一个查询内部的 select_from_where
表达式,
子查询的通常用法是执行集合成员检测,集合比较,以及集合基数,
既有账户又有贷款的客户,
select distinct customer_name
from borrower
where customer_name in (select customer_name
from depositor)
求有贷款但没有账户的客户
select distinct customer_name
from borrower
where customer_name not in (select customer_name
from depositor)
求在 Perryridge分行既有账户又有贷款的客户
select distinct customer_name
from borrower,loan
where borrower.loan_number = loan.loan_number and
branch_name =,Perryridge” and
(branch_name,customer_name) in
(select branch_name,customer_name
from depositor,account
where depositor.account_number =
account.account_number)
求在位于 Brooklyn的所有分行都开了账户的客户,
select distinct S.customer-name
from depositor as S
where not exists (
(select branch-name
from branch
where branch-city =?Brooklyn?)
except
(select R.branch-name
from depositor as T,account as R
where T.account-number = R.account-
number and S.customer-name = T.customer-name))
如果子查询非空,则 exists 谓词返回 true.
exists r r
not exists r r =?
用 EXISTS/NOT EXISTS实现全称量词
– SQL语言中没有全称量词? （ For all）
– 可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：
(?x)P ≡? (? x(? P))
查询选修了全部课程的学生姓名 。
SELECT Sname
FROM Student
WHERE NOT EXISTS
（ SELECT *
FROM Course
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno= Student.Sno
AND Cno= Course.Cno） ；
用 EXISTS/NOT EXISTS实现逻辑蕴函
– SQL语言中没有蕴函 (Implication)逻辑运算
– 可以利用谓词演算将逻辑蕴函谓词等价转换为：
p? q ≡? p∨ q
查询至少选修了学生 95002选修的全部课程的学生号码。
● 用逻辑蕴函表达：查询学号为 x的学生，对所有的课程
y，只要 95002学生选修了课程 y，则 x也选修了 y。
● 形式化表示：
用 P表示谓词,学生 95002选修了课程 y”
用 q表示谓词,学生 x选修了课程 y”
则上述查询为,(?y) p? q
● 等价变换：
(?y)p? q ≡? (?y (?(p? q ))
≡? (?y (?(? p∨ q)
≡y(p∧?q)
● 变换后语义：不存在这样的课程 y，学生 95002
选修了 y，而学生 x没有选。
● 用 NOT EXISTS谓词表示,
SELECT DISTINCT Sno
FROM SC SCX
WHERE NOT EXISTS
(SELECT *
FROM SC SCY
WHERE SCY.Sno = ' 95002 ' AND
NOT EXISTS
(SELECT *
FROM SC SCZ
WHERE SCZ.Sno=SCX.Sno AND
SCZ.Cno=SCY.Cno))；
unique 谓词测试子查询的结果中是否有重复元组,
求在 Perryridge分行最多只开了一个账户的客户,
select T.customer-name
from depositor as T
where unique (
select R.customer-name
from account,depositor as R
where T.customer-name = R.customer-name and
R.account-number = account.account-number and
account.branch-name = ‘Perryridge?)
Select customer_name
From borrower
Where exists (select *
from depositor
where depositor.customer_name=borrower.customer_name)
集合操作,
in,not in,>some/all,<=some/all,=some/all,<>some/all,
exists,not exists,
unique,not unique
查询语句：
SELECT [DISTINCT] <Attributes list>
FROM <relations list>
[WHERE < predicate >]
[GROUP BY < Attributes list >
[ HAVING < Attributes list >] ]
[ORDER BY < Attribute > [ASC|DESC],…,<
Attribute > [ASC|DESC] ]；
关系模式
属性域
完整性约束
关系的索引
关系的安全性和授权信息
关系在磁盘上的物理存储结构不仅可以声明关系,还可以声明关系的其他信息,包括,
数据定义语言 DDL：
SQL 关系用 create table 命令定义,
create table r (A1 D1,A2 D2,...,An Dn,
(integrity-constraint1),
...,
(integrity-constraintk))
– r 是关系名
– Ai 是属性名
– Di 是属性 Ai 的域值的数据类型
– 例如,
create table branch
(branch-name char(15) not null,
branch-city char(30),
assets integer)
not null
primary key (A1,...,An)
Foreign Key (A1,…,An) reference table r on delete
restrict/cascade/set NULL
check (P),where P 是谓词
create table account
(account_number char(10),
branch_name char(15),
balance integer,
primary key (account_number),
foreign key (branch_name) references branch)
check (assets >= 0))
char(n),定长字符串,用户指定长度 n.
varchar(n),变长字符串,用户指定最大长度 n.
int,整数 (依赖于机器的有限整数集合 ).
smallint,小整数 (依赖于机器的有限整数集合 ).
numeric(p,d),定点数,用户指定精度为 p 位,小数点右边有 n 位,
real,double precision,浮点数和双精度浮点数,精度依赖于机器,
float(n),浮点数,用户指定精度为至少 n 位,
域类型中允许空值,声明一个属性为 not null 将禁止该属性取空值,
SQL-92中的 create domain 语句可创建用户定义的域类型
create domain person-name char(20) not null
SQL域类型：
date,日期,包含年 (4 位 ),月,日
– E.g,date?2001-7-27?
time,一天中的时间,包含小时,分钟,秒
– E.g,time?09:00:30? time?09:00:30.75?
timestamp,日期加时间
– E.g,timestamp?2001-7-27 09:00:30.75?
Interval,时间段
– E.g,Interval?1? day
– 从一个 date/time/timestamp 值减去另一个可得 interval 值
– Interval 值可以加到 date/time/timestamp 值上
可从 date/time/timestamp抽取部分值
– E.g,extract (year from r.starttime)
可将字符串转换成 date/time/timestamp
– E.g,cast <string-valued-expression> as date
视图一般以下两点原因，我们希望用户工作在视图层上：
– 出于安全上的考虑，不希望用户看到整个数据库的逻辑模式，而要隐藏掉部分数据；
– 希望产生比逻辑模式更符合特定用户习惯的关系集合，也就是我们现在常说的 个性化 服务。
提供对某些用户隐藏某些数据的机制,创建视图命令,
create view v as <query expression>
其中,
<query expression> 是任何合法查询表达式
v 是视图名基于视图的操作
定义
查询
删除
受限更新
定义基于该视图的新视图创建视图命令,
create view v as <query expression>
其中,
<query expression> 是任何合法查询表达式
v 是视图名包含分行及其客户的视图
create view all-customer as
(select branch-name,customer-name
from depositor,account
where depositor.account-number = account.account-
number)
union
(select branch-name,customer-name
from borrower,loan
where borrower.loan-number = loan.loan-number)
查询 Perryridge 分行的所有客户
select customer-name
from all-customer
where branch-name =?Perryridge?
视图更新
用户角度：更新视图与更新基本表相同
DBMS实现视图更新的方法
– 视图实体化法（ View Materialization）
– 视图消解法（ View Resolution）
一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新 (对两类方法均如此 )
－允许对行列子集视图进行更新
－对其他类型视图的更新不同系统有不同限制
DELETE
FROM <表名 >
[WHERE <条件 >]；
– 功能
删除指定表中满足 WHERE子句条件的 元组
– WHERE子句
指定要删除的元组
缺省表示要修改表中的所有元组删除数据
删除 Perryridge分行的所有账户记录
delete from account
where branch-name =?Perryridge?
删除位于 Needham的每个分行的所有账户,
delete from account
where branch-name in (select branch-name
from branch
where branch-city =?Needham?)
delete from depositor
where account-number in
(select account-number
from branch,account
where branch-city =?Needham?
and branch.branch-name = account.branch-name)
插入单个元组 语句格式,
INSERT
INTO <表名 > [(<属性列 1>[,<属性列 2 >…)]
VALUES (<常量 1> [,<常量 2>] … )
插入子查询结果语句格式,
INSERT
INTO <表名 > [(<属性列 1> [,<属性列 2>… )]
子查询插入数据
insert into account
values (?A_9732?,?Perryridge?,1200)
insert into account (branch_name,balance,
account_number)
values (?Perryridge?,1200,?A_9732?)
insert into account
select loan_number,branch_name,200
from loan
where branch_name =?Perryridge?
语句格式
UPDATE <表名 >
SET <列名 >=<表达式 >[,<列名 >=<表达式 >]…
[WHERE <条件 >]；
可以：
修改某一个元组的值
修改多个元组的值
带子查询的修改语句数据更新
update account
set balance = balance? 1.06
where balance > 10000
update account
set balance = balance? 1.05
where balance? 10000
update SC
SET Grade=0
where 'CS'=
(SELETE Sdept
FROM Student
WHERE Student.Sno = SC.Sno)；
drop table 命令从数据库删除关系的所有信息,
alter table 命令可用于向已有关系增加属性,关系的所有元组在新属性上的值为 null,命令形如
alter table r add A D
其中 A 是新增属性名,D 是 A 的域,
alter table 命令还可用于删除关系的属性
alter table r drop A
其中 A 是关系 r 的属性名
– 许多数据库系统不支持删除属性
CREATE TABLE Student
（ NO CHAR(7) NOT NULL UNIQUE,
Sname Varchar2(20) NOT NULL,
Sex Char(1) NOT NULL,
Bdate Date,
Height Numeric(3,2)
PRIMARY KEY (NO),
):
CREATE TABEL COURSE
（ CNO CHAR(7) NOT NULL UNIQUE,
LHOUR SMALLINT NOT NULL,
CREDIT SMALLINT NOT NULL,
PRIMARY KEY (CNO),
CHECK (LHOUR BETWEEN 17 AND 102)
);
CREATE TABEL SC
(NO CHAR(4) NOT NULL,
CNO CHAR(7) NOT NULL,
GRADE SMALLINT DEFAULT NULL,
PRIMARY KEY (NO,CNO),
FOREIGN KEY (NO)
REFERENCE STUDENT ON DELETE CASCADE,
FOREIGN KEY (CNO)
REFERENCE COURSE ON DELETE CASCADE
);
ALTER TABEL COURSE ADD CNAME VARCHAR(30);
ALTER TABEL COURSE DROP PRIMARY KEY;
ALTER TABEL COURSE ADD PRIMARY KEY(CNO);
DROP TABEL COURSE;
ALTER TABEL SC ADD FOREIGN KEY F1(SNO)
REFERENCE STUDNET ON DELETE CASCADE;
SELECT * FROM SC;
SELECT SNO,SNAME
FROM STUDENT WHERE SEX=‘男 ’ ;
SELECT SNAME,100*HEIGHT
FROM STUDENT WHERE extract(YEAR from
BDATE) =1974;
SELECT * FROM Student WHERE SNAME LIKE ‘D%’ ；
SELECT SNAME,C.CNO,GRADE
FROM STUDENT,COURSE,SC
WHERE STUDENT,NO=SC.NO AND COURSE.CNO
=SC.CNO;
SELECT SNAME,GRADE
FROM STUDENT,SC
WHERE STUDENT.NO=SC.NO AND CNO=‘001’ AND
GRADE>90；
SELECT NO,SNAME
FROM STUDENT
WHERE NO in
（ SELECT NO
FROM SC
WHERE CNO=
（ SELECT CNO FROM COURSE
WHERE CNO=‘CS-101’） ）
SELECT SC.NO,MAX(GRADE)
FROM STUDENT,SC
WHERE STUDENT.NO=SC.NO
GROUP BY SC.NO HAVING COUNT(CNO)>=2;
SELECT AVG(HEIGHT) FROM STUDENT;
SELECT SNAME FROM STUDENT WHERE EXISTS
(SELECT * FROM SC WHERE SNO=STUDENT.SNO
AND CNO=’CS_101’);
SELECT CNO,MAX(GRADE)
FROM SC
GROUP BY CNO
HAVING CNO NOT IN
(SELECT CNO FROM SC
WHERE GRADE IS NULL)
ORDER BY CNO;
SELECT SNO FROM STUDENT
WHERE HEIGHT>1.80
UNION
SELECT SNO FROM SC
GROUP BY SNO
Having AVG(GRADE)>90 ；
INSERT INTO
SC(SNO,CNO) VALUES(’9000’,’CS_111’);
INSERT INTO TGRADE SELECT * FROM SC ;
DELETE FROM SC WHERE GRADE IS NULL;
UPDATE STUDENT SET HEIGHT=HEIGHT*100;
CREATE VIEW TEMP1（ SNO,CCOUNT）
AS SELECT SNO,COUNT(CNO) FROM SC GROUP BY
SNO;
语句格式
CREATE [UNIQUE] [CLUSTER] INDEX < 索引 名 >
ON <表名 >(<列名 >[<次序 >][,<列名 >[<次序 >] ]…)；
– 用 <表名 >指定要建索引的基本表名字
– 索引可以建立在该表的一列或多列上,各列名之间用逗号分隔
– 用 <次序 >指定索引值的排列次序,升序,ASC,降序：
DESC。 缺省值,ASC
– UNIQUE表明此索引的每一个索引值只对应唯一的数据记录
– CLUSTER表示要建立的索引是聚簇索引建立索引索引
CREATE INDEX H_INDEX ON STUDENT(HEIGHT)
CREATE UNIQUE INDEX SC_INDEX ON
SC(SNO ASC,CNO DESC)
DROP INDEX H_INDEX
断言
断言是表达要求数据库永远满足的条件的谓词,
SQL 的断言形如
create assertion <assertion-name> check <predicate>
作了某断言之后,系统将检查它的合法性,并对每一个可能破坏该断言的数据库更新进行检测
– 这种检测会产生大量的开销 ; 因此断言的使用应非常谨慎,
象
for all X,P(X)
之类的断言是通过迂回的方式表达的
not exists X such that not P(X)
SQL语言提供了两种不同的使用方式：
– 交互式
– 嵌入式
为什么要引入嵌入式 SQL
– SQL语言是非过程性语言
– 事务处理应用需要高级语言
这两种方式细节上有差别，在程序设计的环境下，
SQL语句要做某些必要的扩充
Embedded SQL
SQL 标准定义了将 SQL嵌入到程序设计语言 (如 Pascal,
PL/I,Fortran,C,and Cobol )的内容,
SQL 查询所嵌入的语言称为宿主语言,而在宿主语言中可用的 SQL 结构即组成了 embedded SQL.
基本形式基于 System R的 SQL到 PL/I的嵌入,
EXEC SQL 语句用于向预处理器标识 embedded SQL 请求
EXEC SQL <embedded SQL 语句 > END-EXEC
注意,依语言而变,E.g,Java 嵌入使用
# SQL { …,} ;
Host Language + Embedded SQL
PreProcessor
Host Language + Procedure Calls
Host Language
Compiler
Objection
code
Embedded SQL处理过程
1,SQL通信区向主语言传递 SQL语句的执行状态信息主语言能够据此控制程序流程
2,主变量
1）主语言向 SQL语句提供参数
2）将 SQL语句查询数据库的结果交主语言进一步处理
3,游标解决集合性操作语言与过程性操作语言的不匹配工作单元之间的通信方式
SQLCA,SQL Communication Area
– SQLCA是一个数据结构
SQLCA的用途
– SQL语句执行后,DBMS反馈给应用程序信息
描述系统当前工作状态
描述运行环境
– 这些信息将送到 SQL通信区 SQLCA中
– 应用程序从 SQLCA中取出这些状态信息，据此决定接下来执行的语句
– 与所执行的 SQL语句有关
SQL通信区
– 定义 SQLCA
用 EXEC SQL INCLUDE SQLCA加以定义
– 使用 SQLCA
SQLCA中有一个存放每次执行 SQL语句后返回代码的变量 SQLCODE
如果 SQLCODE等于预定义的常量 SUCCESS，则表示 SQL语句成功，否则表示出错
应用程序每执行完一条 SQL 语句之后都应该测试一下 SQLCODE的值，以了解该 SQL语句执行情况并做相应处理
SQLCA的使用方法
– 嵌入式 SQL语句中可以使用主语言的程序变量来输入或输出数据
– 在 SQL语句中使用的主语言程序变量简称为主变量
（ Host Variable）
主变量
输入主变量
指定向数据库中插入的数据
将数据库中的数据修改为指定值
指定执行的操作
指定 WHERE子句或 HAVING子句中的条件
输出主变量
获取 SQL语句的结果数据
获取 SQL语句的执行状态
说明之后的主变量可以在 SQL语句中任何一个能够使用表达式的地方出现
为了与数据库对象名（表名、视图名、列名等）
区别,SQL语句中的主变量名前要加冒号（,）作为标志变量说明：
BEGIN DECLARE SECTION
........,
........,
.........
END DECLARE SECTION
用 SQL 写查询并为它声明一个游标
EXEC SQL
declare c cursor for
select customer-name,customer-city
from depositor,customer,account
where depositor.customer-name = customer.customer-name
and depositor account-number = account.account-
number
and account.balance >,amount
END-EXEC
从宿主语言内查询在某账户中余额大于给定变量
amount 的客户的姓名和城市,
– 游标是系统为用户开设的一个数据缓冲区，存放
SQL语句的执行结果
– 每个游标区都有一个名字
– 用户可以用 SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理游标
使用游标的步骤
– 1,说明游标
– 2,打开游标
– 3,移动游标指针，然后取当前记录
– 4,关闭游标
使用 DECLARE语句
语句格式
EXEC SQL DECLARE <游标名 > CURSOR
FOR <SELECT语句 >;
功能
– 是一条说明性语句，这时 DBMS并不执行 SELECT
指定的查询操作。
说明游标
open 语句导致查询被执行
EXEC SQL open c END-EXEC
fetch 语句可使查询结果中的一条元组的值放入宿主语言变量,
EXEC SQL fetch c into,cn,:cc END-EXEC
循环调用 fetch 可逐条取得查询结果中的元组
SQL通信区 (SQLCA)中的变量 SQLSTATE 设置成
‘ 02000?以指示不再有数据了
close 语句使数据库系统删除保存查询结果的临时关系,
EXEC SQL close c END-EXEC
注意,上述细节随语言而变,E.g,Java 嵌入定义了 Java
iterators 来遍历结果中的元组,
通过声明游标是 for update 即可更新通过游标取得的元组
declare c cursor for
select *
from account
where branch-name=?Perryridge?
for update
修改游标当前位置上的元组
update account
set balance = balance + 100
where current of c
动态 SQL
允许程序在运行时刻构造并提交 SQL 查询,
例如
char * sqlprog =,update account
set balance = balance * 1.05
where account-number =?”
EXEC SQL prepare dynprog from,sqlprog;
char account [10] =,A-101”;
EXEC SQL execute dynprog using,account;
动态 SQL 程序包含一个?,这是一个占位符,当 SQL程序执行时提供相应的值,
ODBC（开放数据库互联标准）
– 适用于客户 -服务器体系结构，定义客户程序用以连接到数据库系统和发出 SQL命令的 API
– 客户可以用同一 ODBC API来连接到任何支持 ODBC的数据库系统； ODBC允许用户同时连接到多个数据源并在这些数据源之间进行切换
– 每个数据库系统必须提供一个驱动程序，受客户端的 ODBC
驱动程序管理器控制，负责与服务器连接和通讯以及进行所有必要的数据和查询格式转换
– ODBC API定义一个 CLI(调用层接口 )、一个 SQL语法定义以及关于允许的 CLI调用序列的规则一个完整的 ODBC由下列几个部件组成：
1,ODBC应用程序 (Application)
ODBC应用程序是用一般程序设计语言 （ 如 C语言等 ） 编写的程序 。
2,ODBC API函数
3,ODBC管理器 (Administrator)
该程序位于 Windows控制面板 (Control Panel)的 32
位 ODBC内,其主要任务是管理安装的 ODBC驱动程序和管理数据源 。
4,ODBC驱动程序管理器 (Driver Manager)
驱动程序管理器包含在 ODBC32.DLL中,对用户是透明的 。 应用程序不能直接调用 ODBC 驱动程序,
只可调用 ODBC驱动程序管理器提供的 ODBC API函数,再由 ODBC驱动程序管理器负责把相应的 ODBC
驱动程序加载到内存中,同时把应用程序访问数据的请求传送给 ODBC驱动程序 。
5,ODBC 驱动程序
ODBC 驱动程序具体负责把 SQL请求传送到数据源的 DBMS中,再把操作结果返回到 ODBC驱动程序管理器 。 后者在把结果传送至客户端的应用程序 。
每种支持 ODBC 的数据库都拥有自己的驱动程序,
一种驱动程序只能固定地与对应的数据库通信,不能访问其他数据库 。
6,数据源数据源就是需要访问的数据库 。
应用程序若要通过 ODBC访问一个数据库,则首先要创建一个数据源,主要工作是指定数据源名 （ DSN，
data source name）,使其关联一个目的数据库以及相应的 ODBC 驱动程序 。 所以说,数据源实际上是一种数据连接的抽象,指定了数据库位置和数据库类型等信息 。
ODBC应用
ODBC API
ODBC驱动管理器
Oracle驱动器 SQL Server
驱动器其它数据库驱动器
SQL Server
服务器其它数据库服务器Oracle服务器工作流程：
应用程序要访问一个数据库,首先必须用 ODBC
管理器注册一个数据源 。
ODBC管理器根据数据源提供的数据库位置,数据库类型及 ODBC驱动程序等信息,建立起 ODBC与具体数据库的联系 。
应用程序将已创建好的数据源名提供给 ODBC，
ODBC就能建立起与相应数据库的连接,为访问数据库做好准备 。
在 ODBC中,ODBC API函数不能直接访问数据库的,必须通过 ODBC驱动程序管理器与数据库交换信息 。 ODBC驱动程序管理器在应用程序和数据源之间起着转换与管理的作用 。
Opens database connection using SQLConnect().
Parameters for SQLConnect:
connection handle,
the server to which to connect
the user identifier,
password
Program sends SQL commands to the database by
using SQLExecDirect
Result tuples are fetched using SQLFetch()
ODBC CODE
Java编程语言具有坚固、安全、易于使用、易于理解和易于从网络自动下载等特性，已逐渐成为编写数据库应用程序的大众编程语言。 Java通过与 JDBC的结合，提供了良好的数据库访问性能。
JDBC为数据库开发人员提供了一个标准的 API。
JDBC API目前新版本是 JDBC 3.0 API。
JDBC 3.0组成,java.sql包和 javax.sql包。 javax.sql包是
java.sql包的补充。
JDBC
JDBC驱动程序类型
Java Application
JDBC Driver Manager
JDBC
API
JDBC Driver
API
JDBC
Implementati
on
alternatives
JDBC Net
Driver
JDBC ODBC
Bridge Driver
Driver
A
ODBC and
DB Driver
Driver
B
Proprietary database access protocol
JDBC
Middle Ware
Protocol
完整性,
保证数据的一致性
域约束
实体完整性
参照完整性
断言
触发器
2.4 完整性与安全性断言：
每家分行的贷款总额必须小于该分行的账户余额总和,
create assertion sum-constraint check
(not exists (select * from branch
where (select sum(amount) from loan
where loan.branch-name =
branch.branch-name)
>= (select sum(amount) from account
where loan.branch-name =
branch.branch-name)))
触发器
触发器 是数据库更新操作引起的被系统自动执行的语句,
设计触发器必须,
– 指明触发器被执行的条件,
– 指明触发器执行时所做的具体操作,
引入触发器的 SQL标准是 SQL:1999,但多数数据库产品早已支持非标准语法的触发器,
触发事件包括 insert,delete 或 update
针对 update的触发器可以指定具体修改的属性
– E.g,create trigger overdraft-trigger after update of balance
on account
更新前后的属性值可通过下列方法被引用
– referencing old row as,对删除和修改有效
– referencing new row as,对插入和修改有效
触发器可在某事件发生之前激活,这相当于约束,
– 例如将空格改成 null.
create trigger setnull-trigger before update on r
referencing new row as nrow
for each row
when nrow.phone-number =
set nrow.phone-number = null
既可以针对受影响的每一行执行一次单独的操作,也可以针对受到一个事务影响的所有行只执行一次操作
– for each statement (语句） vs,for each row（行）
– 用 referencing old table 或 referencing new table 来引用包含受影响的行的临时表
– 对更新大量元组的 SQL语句更高效
create trigger overdraft-trigger after update on account
referencing new row as nrow
for each row
when nrow.balance < 0
begin atomic
insert into borrower
(select customer-name,account-number
from depositor
where nrow.account-number =
depositor.account-number);
insert into loan values
(n.row.account-number,nrow.branch-name,
– nrow.balance);
update account set balance = 0
where account.account-number = nrow.account-
number
end
安全性
安全性 –防止恶意更新或偷窃数据的企图,
– 数据库系统级
验证和授权机制使得特定用户存取特定数据
本章中主要讨论授权机制
– 操作系统级
操作系统超级用户可对数据库做任何事情 ! 需要有一个好的操作系统级安全机制,
– 网络级,使用加密防止
偷听 (未授权的读取信息 )
伪装 (冒充授权用户 )
– 物理级
对计算机的物理访问使得入侵者可摧毁数据 ; 需要传统的锁钥安全手段
防止洪水,火灾等对计算机的损坏,
– 参见第 17章 (恢复 )
– 人员级
审查用户以确保授权用户不会将存取权给予入侵者
训练用户选择口令与保密对数据库中部分数据的权限形式,
读权限 –允许读,但不允许更新数据,
插入权限 –允许插入新数据,但不允许更新现有数据,
修改权限 –允许修改,但不允许删除数据,
删除权限 –允许删除数据对修改数据库模式的授权形式,
索引权限 –允许创建和删除索引,
资源权限 –允许创建新关系,
修改权限 –允许增加或删除关系的属性,
删除权限 –允许删除关系,
权限的授予
权限从一个用户到另一个用户的传递可用授权图表示,
图的节点是用户,
图的根是数据库管理员,
边 Ui?Uj 表示用户 Ui 将某权限授予给了用户 Uj.
U1 U4
U2 U5
U3
DBA
授权图
要求,授权图中的所有边都必须是某条从数据库管理员出发的路径的一部分
若 DBA 从 U1收回权限,
– 必须从 U4 收回权限,因为 U1 不再有权限
– 不能从 U5 收回权限,因为 U5 还有从 DBA经 U2 的另一条授权路径
必须防止不经过根节点的循环授权,
– DBA授权给 U7
– U7 授权给 U8
– U8 授权给 U7
– DBA 从 U7收回权限
必须收回从 U7到 U8以及从 U8 到 U7 的授权,因为不再有从 DBA到 U7或 U8 的路径,
SQL中的安全性声明
grant语句用于授权
grant <权限列表 >
on <关系或视图名 > to <用户列表 >
<用户列表 > 可以是,
– 用户名
– public,代表所有合法用户
– 角色 (见后 )
授予对视图的权限并不意味着授予对定义该视图的基础关系的权限,
权限的授予者本身必须拥有相应的权限 (或者是数据库管理员 ).
SQL中的权限
select,允许读关系,或查询视图
– 例如,授予用户 U1,U2,U3 对 branch 关系的 select
权限,
grant select on branch to U1,U2,U3
insert,允许插入元组
update,允许修改元组
delete,允许删除元组
references,创建关系时允许声明外键
usage,(SQL-92)授权用户使用指定域
all privileges,所有权限授权的权限
with grant option,允许用户把被授予的权限再转授给其他用户,
– 例如,
grant select on branch to U1 with grant option
授予 U1 对 branch 的 select 权限并允许 U1 将此权限授予其他用户角色
通过创建角色可以一次性对一类用户指定其共同的权限
象对用户一样,可以对角色授予或收回权限
角色可被赋予给用户,甚至给其他角色
SQL:1999 支持角色
create role teller
create role manager
grant select on branch to teller
grant update (balance) on account to teller
grant all privileges on account to manager
grant teller to manager
grant teller to alice,bob
grant manager to avi
SQL中的权限回收
revoke 语句用于回收权限,
revoke<权限列表 >
on <关系或视图名 > from <用户列表 > [restrict|cascade]
例如,
revoke select on branch from U1,U2,U3 cascade
从一用户收回权限可能导致其他用户也失去该权限 ; 称为级联回收,
指定 restrict可以阻止级联回收,
revoke select on branch from U1,U2,U3 restrict
如果导致级联回收,则带有 restrict的 revoke 命令失败,
<权限列表 > 可以是 all 以便收回某用户拥有的所有权限,
如果同一权限被不同授予者两次授予同一用户,则该用户在回收一次后仍保持该权限,
所有依赖于被收回权限的权限也被收回,
SQL 授权的局限性
SQL不支持元组级的授权
– 例如我们不能限制学生只能看他自己的分数
某些应用 (如 web应用 )的所有最终用户可能被映射成单个数据库用户
以上情况下的授权任务只能依靠应用程序,SQL不支持
– 授权在应用程序代码中完成,并可能散布在整个应用中
– 检查是否有权限漏洞非常困难,因为需要读大量应用程序代码思考题：
１１,a)找出下面表的三个候选键,
A B C D
a1 b1 c1 d1
a2 b3 c1 d2
a3 b4 c2 d2
a4 b2 c2 d1
２关系 R,S,T如下：
R,A B C D S,B C T,B Ｅ
a b c d c d c d
a c d b e f e g
b e f a
b c d a
b c d e
c c d e
c e f e
求：（１）Ｒ Ｔ （２）Ｒ?S
３,考虑学生数据库，用关系代数和ＳＱＬ完成：
S ( SNO,SName,Sex,Age,Class,Dept ),
C ( Cno,Cname,Dept ),
SC (SNO,Cno,Grade)
１） 查询选修了课程编号为,C01”且成绩高于 85分的学生的学号、姓名和成绩；
２）查询至少选修了三门课程的学生的学号和姓名；
３）查询计算机系张红同学选修的课程名称、学分和成绩。
关系数据模型
关系代数
SQL
完整性和安全性
关系范式
对象关系数据库
2.1 关系数据模型关系模型的理论基础是集合论，是用集合代数定义一个关系。
定义 1,域 (Domain)是一组具有相同数据类型的值的集合。
定义 2,设 D1,D2,…,Dn为一组域,D1,D2,…,Dn
上的笛卡尔积定义为：
D1? D2? …? Dn = {(d1,d2,…,dn) |
di? Di,i =1,2,…,n}
笛卡尔积是一个集合，集合中的每一个元素 (d1,d2，
…,dn) 称为一个 n元组，简称元组。元组中的每个值叫做一个分量。
定义 3,D1? D2? …? Dn笛卡尔积的子集叫做在域 D1,D2，
…,Dn上的关系 (Relation)。用 R(D1,D2,…,Dn)表示,R
是关系名。
关系是在一组域（ D1,D2,…,Dn）上的笛卡尔积的一个子集。当 n=1时，称为单元关系； 当 n=2时，称为二元关系。
关系是一个二维表，表的每一行对应一个元组，表的每列对应一个域，由于域可以相同，为了区分给每个列起一个名字，称为属性。
对关系的描述称为关系模式,该描述 包括关系名,
关系中的属性名,属性向域的映象,属性间的数据依赖关系等,可以形式化的表示为,R(U,F)或 R(U)。
其 中 R为关系名,U为组成该关系的属性名集合,F
为属性间的数据依赖关系集合。 属性向域的映象通常直接说明为属性的类型、长度、取值范围等。
有三个域：男人、女人和儿童。其取值为：男人（张三
，李四），女人（王，吴），儿童（张一，李二）。
其笛卡尔积：男人?女人?儿童共有 8个元组，可以列成一张表：
（张三，王，张一），（李四，王，张一）,
（张三，王，李二），（李四，王，李二）,
（张三，吴，张一），（李四，吴，张一）,
（张三，吴，李二），（李四，吴，李二）。
丈夫 妻子 小孩张三 王 王一李四 吴 李二家庭 （丈夫，妻子，小孩）。 该关系中的属性名就用域名,关系形成的二维表如下：
关系是笛卡尔积中有一定意义的、有限的子集。
Jones
Smith
Curry
Lindsay
customer_name
Main
North
North
Park
customer_street
Harrison
Rye
Rye
Pittsfield
customer_city
customer
属性元组
Students学生信息表
SNO 学号
Sname 姓名
Sex 性别
Age 年龄
Class 班级
DeptNO 系编号
S_C 学生选课信息表
SNO 学号
Cno 课程编号
Grade 成绩
Courses 信息表
Cno 课程编号
Cname 课程名称
credit 学分数
DEPT 系信息表
DeptNO 系编号
Deptname 系名称
Address 地址
CUSTOMERS顾客信息表
cid 顾客 ID
cname 顾客姓名
city 顾客所在城市
discnt 顾客可能会有的折扣
AGENTS 代理商信息表
aid 代理商 ID
aname 代理商名称
city 代理商所在城市
percent 代理商每笔交易所能获得的佣金百分比
PRODUCTS 商品信息表
pid 商品 ID
pname 商品名称
city 商品库存所在城市
quantity 商品库存数量
price 商品批发价
ORDERS 订单信息表
ordno 订单 ID
Month 订单月份
cid 顾客 ID
aid 代理商 ID
pid 商品 ID
qty 数量
dollars 商品总价在关系数据库中的关系有如下性质：
同一列来自同一个域。
不同的列可以出自相同的域，必须有不同的属性名。
不能有完全相同的元组存在。
关系中元组的顺序无关。
关系中列的顺序无关。
每个属性值必须是不可再分的数据项。
完整性约束规则
1 候选键：关系模式 R（ U） 的属性集合 K是候选键,满足：
（ 1） R（ U） 的任何一个关系实例的任意两个元组在属性集合 K上的值都不相同；
（ 2） K的任何真子集都不满足条件 （ 1） 。
以上被称为候选键的两性质：唯一性,最小性 。
相关概念：主键 /关键字；主属性；非主属性；
例,Student ( NO,Name,Sex,Age,Class,DeptNO ),
Course ( Cno,Cname,Dept ),
S_C (NO,Cno,Grade)
实体完整性约束：
若 A是关系模式 R（ U） 的主属性,则 R（ U） 的任何一个实例关系不存在任何元组在 A上的值为空 。
2 关联 /参照完整性约束：
设 X是关系模式 R（ U） 关于关系模式 R?(U?)的外部键 。 如果
K是 R（ U） 的关系实例的一个元组的外部键值,则 R?(U?)的实例中必存在一个元组 T,T在 X上的值为 K。
例,Student ( NO,Name,Sex,Age,Class,
DeptNO ),
Dept (DeptNO,DeptName)
更新时的参照完整性约束：
为了保持参照完整性约束必须尽心如下检查,
X (r2)K (r1)
插入,如果元组 t2 被插入到 r2,则系统必须确保 r1中存在元组 t1 使得 t1[K] = t2[X],即
t2 [X]K (r1)
删除,如果从 r1删除元组 t1,则系统必须计算 r2中引用 t1的元组集合,
X = t1[K] (r2)
如果此集合非空,则要么认为出错而拒绝删除命令,要么删除这些引用 t1的元组 (级联删除 ),
Foreign Key? reference table r1 on
delete/update [cascade/set null ]
修改,有两种情况,
如果修改关系 r2中的元组 t2 并且修改了外键 X的值,则作类似于插入情况的检查,令 t2? 表示元组 t2的新值,系统必须确保
t2?[X]K(r1)
如果修改关系 r1中的元组 t1并且修改了主键 K的值,则作类似于删除情况的检查,系统必须利用 t1在修改前的旧值计算
X = t1[K] (r2)
若此集合非空,则要么认为出错而拒绝修改,要么对此集合中的元组作级联修改,要么删除此集合中的元组
,
域约束：
域约束是完整性约束的最基本形式,
可用于检测插入到数据库中的数据的合法性,或用于查询中检测两个数据进行比较时的合法性,
check子句允许对域作限制,
利用 check 子句确保“小时工资”域只允许大于指定值的值,
create domain hourly_wage numeric(5,2)
constraint value_test check(value > = 4.00)
这个域具有约束：小时工资大于 4.00
子句 constraint value_test 使可选的 ; 可用于指示以后的更新破坏了哪一条约束,
可有更复杂的域检查条件
Create domain AccountType char(10)
constraint account_type_test
check (value in (?Checking?,?Saving?))
check (branch_name in (select branch_name from branch))
2.2 关系代数关系代数是过程化的查询语言，它包括一个运算集合。
这些运算以一个或两个关系为输入，产生一个新的关系为结果。
关系代数的基本运算有：
选择投影并集合差笛卡儿积一些附加运算：即 集合交,自然连接,除 和 赋值,附加运算用基本运算来定义。
选择
表示法,? p(r)
p 称为选择谓词
定义,
p(r) = {t | t? r and p(t)}
其中 p 是一命题演算公式,由项经连接词?
(and),? (or),? (not) 连接而成,
项形如,
<属性 >op <属性 > 或 <常量 >
其中 op 包括,=,?,>,?,<,?
student_no=10(account)
Relation r A B C D
1
5
12
23
7
7
3
10
A=B ^ D > 5 (r)
A B C D
1
23
7
10
投影
表示法,
A1,A2,…,Ak (r)
其中 A1,A2,.,是属性名,r 是关系名,
结果定义为由指定的 k 列组成的关系
删除结果中的重复行,因为关系是集合
例
student_name,sno (account)
Relation r,A B C
10
20
30
40
1
1
1
2
A C
1
1
1
2
=
A C
1
1
2
A,C
(r)
并运算
表示法,r? s
定义,
r? s = {t | t? r or t? s}
r? s 合法必须要求,
1,r,s 具有相同元数 (属性数相同 )
2,属性域必须兼容 (例如,r 的第 2列与 s 的第 2列处理相同类型的值 )
例：
customer_name (depositor)customer_name
(borrower)
r? s:
Relations r,s,A B
1
2
1
A B
2
3
r
s
A B
1
2
1
3
差运算
表示法,r – s
定义,
r – s = {t | t? r and t? s}
集合差必须在兼容关系之间进行,
r 和 s 必须具有相同元数
r 和 s 的属性域必须兼容
r – s:
Relations r,s,A B
1
2
1
A B
2
3
r
s
A B
1
1
笛卡尔乘积：
表示法,r x s
定义,
r x s = {t q | t? r and q? s}
假设 r(R) 和 s(S) 的属性是不相交的,(即 R? S =? ).
若 r(R) 和 s(S) 的属性不是不相交的,则需重命名,
r x s:
A B C D E
1
1
1
1
2
2
2
2
10
19
20
10
10
10
20
10
a
a
b
b
a
a
b
b
Relations r,s,A B
1
2
C D
10
10
20
10
E
a
a
b
b
下列附加运算并不能增强关系代数的能力,但可以简化常见查询：
交集
自然连接
除法交集运算
表示法,r? s
定义,
r? s ={ t | t? r and t? s }
假设,
r,s 具有相同元数
r 和 s 的属性兼容
注,r? s = r - (r - s)
Relation r,s,A B
1
2
1
A B
2
3
r s
A B
2
r? s
连接运算
表示法,r A?B s
r A?B s = {t q | t? r and q? s and (t[A]?q[B])}
=? A?B (r?s)
{=,?,?,?,>,<}
Relations r,s,A B
3
6
1
2
C D
1
4
7
10
2
5
8
11
E
3
6
9
12
r A>D s
A B C D E
3 1 1 2 3
6 2 1 2 3
6 2 4 5 6
自然连接：
表示法,r s
令 r 和 s 分别是模式 R 与 S 上的关系,自然连接的结果是模式 R? S 上的关系,该关系是通过考虑 r 和 s 的每一对元组 tr 及 ts 而得到的,
如果 tr 和 ts 在每个 R? S中属性上具有相同值,则向结果中加入一条元组 t,其中
t 与 tr 在 r 上的值相同
t 与 ts 在 s上的值相同
A B C D E
1
1
1
1
2
a
a
a
a
b
Relations r,s:
A B
1
2
4
1
2
C D
a
a
b
a
b
B
1
3
1
2
3
D
a
a
a
b
b
E
r s
r s
外连接
连接运算的扩展，避免丢失信息,
在计算连接的基础上再向结果中加入那些没有匹配的元组,
用到空值 null,
– null 意思是不知道或不存在的值
– 所有涉及 null 的比较粗略地说定义为 false.
以后研究涉及空值的比较的准确意义外连接 – 例关系 loan
loan-number amount
L-170
L-230
L-260
3000
4000
1700
关系 borrower
customer-name loan-number
Jones
Smith
Hayes
L-170
L-230
L-155
branch-name
Downtown
Redwood
Perryridge
内连接
loan Borrower
loan borrower
左外连接
loan-number amount
L-170
L-230
3000
4000
customer-name
Jones
Smith
branch-name
Downtown
Redwood
loan-number amount
L-170
L-230
L-260
3000
4000
1700
customer-name
Jones
Smith
null
branch-name
Downtown
Redwood
Perryridge
右外连接 loan borrower
loan-number amount
L-170
L-230
L-155
3000
4000
null
customer-name
Jones
Smith
Hayes
loan-number amount
L-170
L-230
L-260
L-155
3000
4000
1700
null
customer-name
Jones
Smith
null
Hayes
loan borrower
全外连接
branch-name
Downtown
Redwood
null
branch-name
Downtown
Redwood
Perryridge
null
适合表达带有,所有,短语的查询,
令 r 和 s 分别是模式 R 和 S 上的关系,其中
R = (A1,…,Am,B1,…,Bn)
S = (B1,…,Bn)
则 r? s 的结果是模式 R – S = (A1,…,Am)上的关系
r? s = { t | t R－ S(r) u? s ( tu? r ) }
除法运算,r? s
A B
a b
e d
Relations r,s:
A B C D
a b c d
a b e f
b c e f
e d c d
e d e f
a b d e
r
C D
c d
e f
s
r? s
R－ S( r )–? R－ S( (? R－ S( r )× s) – r)
例：
branch (branch_name,branch_city,assets)
customer (customer_name,customer_street,customer_only)
account (account_number,branch_name,balance)
loan (loan_number,branch_name,amount)
depositor (customer_name,account_number)
borrower (customer_name,loan_number)
求超过 $1200 的所有贷款
amount > 1200 (loan)
求数额超过 $1200 的所有贷款的贷款号
loan_number (?amount > 1200 (loan))
求所有在银行有贷款或账户的客户的姓名
customer_name (borrower)customer_name (depositor)
求 所有在银行有贷款且有账户的客户的姓名
customer_name (borrower)customer_name (depositor)
求在 Perryridge分行有贷款的所有客户的姓名
customer_name (?branch_name=“Perryridge”
(?borrower.loan_number = loan.loan_number(borrower x loan)))
求 在 Perryridge分行有贷款但在任何分行都没有账户的客户的姓名
customer_name (?branch_name =,Perryridge”
(?borrower.loan_number = loan.loan_number(borrower x loan)))
–?customer_name(depositor)
求所有至少在,Downtown” 与,Uptown”分行有账户的客户查询 1
CN(?BN=“Downtown”(depositor account))?
CN(?BN=“Uptown”(depositor account))
其中 CN 表示 customer_name,BN 表示
branch_name.
查询 2
customer_name,branch_name (depositor
account)
temp(branch_name) ({(“Downtown”),(“Uptown”)})
求在位于 Brooklyn的所有分行都开有账户的客户
customer_name,branch_name (depositor account)
branch_name (?branch_city =,Brooklyn” (branch))
S ( SNO,SName,Sex,Age,Class,Dept ),
C ( Cno,Cname,Dept ),
SC (SNO,Cno,Grade)
（ 1）?SNO,GRADE(?CNO=’C2’(SC))
（ 2）?SNO,SNAME(?CNO=’C2’(S SC)
（ 3）?SNO,SNAME(?CNAME=’DB’(S SC C)
（ 4）?SNO,SNAME(S)SNO,SNAME(?CNO=’C2’ (S SC)
（ 5）? SNAME (S (?SNO,CNO(SC)÷?CNO(C)))
运算符 符号 含义 键盘格式 示例集合运算符
∪ 并 UNION R∪S,或 R UNION S
∩ 交 INTERSECT R∩S,或 R INTERSECT S
- 差 MINUS R-S，或 R MINUS S
× 乘 TIMES R× S,或 R TIMES S
专门关系运算符
σ 选择 R where C σ 姓名 =“张三,(S)或
S where 姓名 =?张三 ’
π 投影 R[ ] π 考号，姓名 (S）或 S[考号,姓名 ]
∞ 连接 JOIN R∞S,或 R JOIN S
÷ 除 DIVIDEBY R÷ S，或 R DIVIDEBY S
自从 20世纪 80年代以来,SQL就是一个通用的、功能极强的关系数据库语言。现在,SQL语言正从关系形式（ ANSI SQL-92标准）
转向对象 -关系形式（ ANSI SQL-99标准,1999年颁布）。
SQL语言是 1974年由 Boyce和 Chamberlin提出。
1986年 10月美国国家标准局 (ANSl)批准了 SQL作为关系数据库语言的美国标准。同年公布了 SQL标准文本 (简称 SQL-86)。 1987年 6
月国际标准化组织 (ISO)也采纳了此标准。
1989年，美国国家标准局（ ANSI）采纳了新的规范 SQL-89标准，
取代 SQL-86，同时 SQL-89标准也被国际标准化组织 (ISO)采纳。
1992年,ANSI/ISO颁布了 SQL2版本，标准的名称为 SQL-92。
SQL-92分称几个顺序级别：从代表 SQL-89最小扩展集的,Entry”
到,Intermediate”和,Full”。完成于 1999年的 SQL-99具有更加高级的特征（包括对象 -关系特性），亦称 SQL3。
主要的几个数据库生产厂商并不可能完全遵守 SQL-99（以及更老的 SQL-92）。
2.3 SQL
SQL的特点
– 1,综合统一
– 2,高度非过程化
– 3,面向集合的操作方式
– 4,以同一种语法结构提供两种使用方法
– 5,语言简洁，易学易用
S Q L 语言的动词
S Q L 功 能 动 词数 据 定 义 CR E A T E,DR O P,A L T E R
数 据 查 询 S E L E CT
数 据 操 纵 I NSE R T,UP DA T E
DE L E T E
数 据 控 制 G RA NT,RE V O K E
基本查询语句
集合运算
聚集函数
空值
嵌套子查询
导出关系
视图
数据库更新
连接关系
DDL
Embedded SQL,ODBC及 JDBC
SQL基于集合与关系运算并作了某些修改和增强
典型的 SQL 查询形如,
select A1,A2,...,An
from r1,r2,...,rm
where P
Ai 是属性
ri 是表
P 是谓词
等价于关系代数表达式
A1,A2,...,An(?P (r1 x r2 x,.,x rm))
SQL 查询的结果是一个关系,
查询基本结构
select 子句对应于关系代数的投影运算,用于列出想要的查询结果中的属性,
从 loan 关系求所有分行的名称
select branch_name
from loan
在,纯,关系代数语法中,对应查询是,
branch_name(loan)
select 子句中用 * 表示,所有属性,
select *
from loan
SQL 是不分大小写的,
select 子句
SQL 允许关系与查询结果中出现重复元组,
可用关键字 distinct 强制删除重复元组,
从 loan 关系求所有分行名并删除重复元组
select distinct branch_name
from loan
select 子句可包含使用 +,–,?,/ 运算以及常量和属性的算术表达式,
select loan_number,branch_name,amount? 100
from loan
将 loan 关系的 amount 属性乘以 100.
WHERE 子句
where 子句对应于关系代数的选择谓词,其中的谓词涉及 from子句中出现的关系的属性,
求 Perryridge分行发出的所有额度超过 $1200 的贷款的贷款号,
select loan_number
from loan
where branch_name =?Perryridge? and amount
> 1200
比较结果可用逻辑连接词 and,or,not 组合在一起,
可对算术表达式的结果进行比较,
select loan_number
from loan
where amount between 90000 and 100000
SQL 包含串匹配运算用于比较字符串,模式用两个特殊字符描述,
百分号 (%),% 字符可与任何字符串匹配,
下划线 (_),_ 字符与任一字符匹配,
求所有街道名称包含子串,Main”的客户的姓名,
select customer_name
from customer
where customer_street like ‘%Main%’
若要匹配串,Main%”则需用
like ‘Main\%’ escape ‘\’
SQL支持许多串操作连接 (,||”)
大小写转换求串长度,取子串,etc.
按字母顺序列出在 Perryridge分行有贷款的客户的姓名
select distinct customer_name
from borrower,loan
where borrower loan_number _ loan.loan_number
and branch_name =?Perryridge?
order by customer_name
可对每个排序属性使用 desc 指定降序,asc 指定升序 ;
升序是缺省顺序,
E.g,order by customer_name desc
谓词 形式 例子比较谓词
BETWEEN谓词量化谓词
IN谓词
EXISTS谓词
IS NULL谓词
LIKE谓词
expr1θ {expr2|(subquery)}
expr1[NOT] BETWEEN expr2 and
expr3
exprθ [SOME|ANY|ALL]
(subquery)
expr [NOT] IN (subquery)
[NOT]EXISTS(subquery)
colname IS [NOT]NULL
cloname [NOT] LIKE val
[ESCAPE val]
p.price>(subquery)
c.discnt between 10.0
and 12.0
c.discnt>=all
(subquery)
pid in (select pid
from orders)
exist(select * …)
c.discnt is null
cname like ‘A%’
FROM子句
from 子句 对应于关系代数的笛卡尔积运算,它列出了查询中要扫描的关系,
求笛卡尔积 borrower x loan
select * from borrower,loan
求所有在 Perryridge分行有贷款的客户的姓名,贷款号和贷款数量,
select customer_name,borrower.loan_number,amount
from borrower,loan
where borrower.loan_number = loan.loan_number
and branch_name =?Perryridge?
元组变量是在 from子句中用 as 子句定义的,
求所有有贷款的客户的姓名和贷款号,
select customer_name,T.loan_number,S.amount
from borrower as T,loan as S
where T.loan_number = S.loan_number
求比位于 Brooklyn的某分行资产多的分行的名字,
select distinct T.branch_name
from branch as T,branch as S
where T.assets > S.assets and S.branch_city = ‘Brooklyn’
集合操作
关系的集合操作 union,intersect,except 对应于关系代数运算
以上操作自动删除重复元组 ; 为了保留所有重复元组应使用对应的多重集版本 union all,intersect all,
except all.
假如一个元组在 r 中发生 m 次,在 s 中发生 n 次,则,
在 r union all s 中发生 m + n 次在 r intersect all s 中发生 min(m,n) 次在 r except all s 中发生 max(0,m – n) 次
求有贷款或账户的客户
(select customer_name from depositor)
union
(select customer_name from borrower)
求既有贷款又有账户的客户
(select customer_name from depositor)
intersect
(select customer_name from borrower)
求只有账户没有贷款的客户
(select customer_name from depositor)
except
(select customer_name from borrower)
聚集函数
以下函数对关系中某一列值的多重集进行计算并返回单个值
avg,平均值
min,最小值
max,最大值
sum,总和
count,值的个数聚集函数经常和 Group BY一起使用：
SELECT [DISTINCT] <Attributes list>
FROM <relations list>
[WHERE < predicate >]
[GROUP BY < Attributes list >
[ HAVING < Attributes list >] ]
求 Perryridge分行的平均账户余额,
select avg (balance)
from account
where branch_name =?Perryridge?
求 customer 关系中的元组个数
select count (*)
from customer
求银行存款人数,
select count (distinct customer_name)
from depositor
求每个分行的存款人数,
select branch_name,count (distinct customer_name)
from depositor,account
where depositor.account_number = account.account_number
group by branch_name
求平均账户余额超过 $1,200的所有分行名及平均余额,
select branch_name,avg (balance)
from account
group by branch_name
having avg (balance) > 1200
注意,having 子句中的谓词是在分组形成之后起作用的,而 where 子句中的谓词是在分组形成之前起作用的嵌套子查询
SQL 提供嵌套子查询的机制,
子查询是嵌在另一个查询内部的 select_from_where
表达式,
子查询的通常用法是执行集合成员检测,集合比较,以及集合基数,
既有账户又有贷款的客户,
select distinct customer_name
from borrower
where customer_name in (select customer_name
from depositor)
求有贷款但没有账户的客户
select distinct customer_name
from borrower
where customer_name not in (select customer_name
from depositor)
求在 Perryridge分行既有账户又有贷款的客户
select distinct customer_name
from borrower,loan
where borrower.loan_number = loan.loan_number and
branch_name =,Perryridge” and
(branch_name,customer_name) in
(select branch_name,customer_name
from depositor,account
where depositor.account_number =
account.account_number)
求在位于 Brooklyn的所有分行都开了账户的客户,
select distinct S.customer-name
from depositor as S
where not exists (
(select branch-name
from branch
where branch-city =?Brooklyn?)
except
(select R.branch-name
from depositor as T,account as R
where T.account-number = R.account-
number and S.customer-name = T.customer-name))
如果子查询非空,则 exists 谓词返回 true.
exists r r
not exists r r =?
用 EXISTS/NOT EXISTS实现全称量词
– SQL语言中没有全称量词? （ For all）
– 可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：
(?x)P ≡? (? x(? P))
查询选修了全部课程的学生姓名 。
SELECT Sname
FROM Student
WHERE NOT EXISTS
（ SELECT *
FROM Course
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno= Student.Sno
AND Cno= Course.Cno） ；
用 EXISTS/NOT EXISTS实现逻辑蕴函
– SQL语言中没有蕴函 (Implication)逻辑运算
– 可以利用谓词演算将逻辑蕴函谓词等价转换为：
p? q ≡? p∨ q
查询至少选修了学生 95002选修的全部课程的学生号码。
● 用逻辑蕴函表达：查询学号为 x的学生，对所有的课程
y，只要 95002学生选修了课程 y，则 x也选修了 y。
● 形式化表示：
用 P表示谓词,学生 95002选修了课程 y”
用 q表示谓词,学生 x选修了课程 y”
则上述查询为,(?y) p? q
● 等价变换：
(?y)p? q ≡? (?y (?(p? q ))
≡? (?y (?(? p∨ q)
≡y(p∧?q)
● 变换后语义：不存在这样的课程 y，学生 95002
选修了 y，而学生 x没有选。
● 用 NOT EXISTS谓词表示,
SELECT DISTINCT Sno
FROM SC SCX
WHERE NOT EXISTS
(SELECT *
FROM SC SCY
WHERE SCY.Sno = ' 95002 ' AND
NOT EXISTS
(SELECT *
FROM SC SCZ
WHERE SCZ.Sno=SCX.Sno AND
SCZ.Cno=SCY.Cno))；
unique 谓词测试子查询的结果中是否有重复元组,
求在 Perryridge分行最多只开了一个账户的客户,
select T.customer-name
from depositor as T
where unique (
select R.customer-name
from account,depositor as R
where T.customer-name = R.customer-name and
R.account-number = account.account-number and
account.branch-name = ‘Perryridge?)
Select customer_name
From borrower
Where exists (select *
from depositor
where depositor.customer_name=borrower.customer_name)
集合操作,
in,not in,>some/all,<=some/all,=some/all,<>some/all,
exists,not exists,
unique,not unique
查询语句：
SELECT [DISTINCT] <Attributes list>
FROM <relations list>
[WHERE < predicate >]
[GROUP BY < Attributes list >
[ HAVING < Attributes list >] ]
[ORDER BY < Attribute > [ASC|DESC],…,<
Attribute > [ASC|DESC] ]；
关系模式
属性域
完整性约束
关系的索引
关系的安全性和授权信息
关系在磁盘上的物理存储结构不仅可以声明关系,还可以声明关系的其他信息,包括,
数据定义语言 DDL：
SQL 关系用 create table 命令定义,
create table r (A1 D1,A2 D2,...,An Dn,
(integrity-constraint1),
...,
(integrity-constraintk))
– r 是关系名
– Ai 是属性名
– Di 是属性 Ai 的域值的数据类型
– 例如,
create table branch
(branch-name char(15) not null,
branch-city char(30),
assets integer)
not null
primary key (A1,...,An)
Foreign Key (A1,…,An) reference table r on delete
restrict/cascade/set NULL
check (P),where P 是谓词
create table account
(account_number char(10),
branch_name char(15),
balance integer,
primary key (account_number),
foreign key (branch_name) references branch)
check (assets >= 0))
char(n),定长字符串,用户指定长度 n.
varchar(n),变长字符串,用户指定最大长度 n.
int,整数 (依赖于机器的有限整数集合 ).
smallint,小整数 (依赖于机器的有限整数集合 ).
numeric(p,d),定点数,用户指定精度为 p 位,小数点右边有 n 位,
real,double precision,浮点数和双精度浮点数,精度依赖于机器,
float(n),浮点数,用户指定精度为至少 n 位,
域类型中允许空值,声明一个属性为 not null 将禁止该属性取空值,
SQL-92中的 create domain 语句可创建用户定义的域类型
create domain person-name char(20) not null
SQL域类型：
date,日期,包含年 (4 位 ),月,日
– E.g,date?2001-7-27?
time,一天中的时间,包含小时,分钟,秒
– E.g,time?09:00:30? time?09:00:30.75?
timestamp,日期加时间
– E.g,timestamp?2001-7-27 09:00:30.75?
Interval,时间段
– E.g,Interval?1? day
– 从一个 date/time/timestamp 值减去另一个可得 interval 值
– Interval 值可以加到 date/time/timestamp 值上
可从 date/time/timestamp抽取部分值
– E.g,extract (year from r.starttime)
可将字符串转换成 date/time/timestamp
– E.g,cast <string-valued-expression> as date
视图一般以下两点原因，我们希望用户工作在视图层上：
– 出于安全上的考虑，不希望用户看到整个数据库的逻辑模式，而要隐藏掉部分数据；
– 希望产生比逻辑模式更符合特定用户习惯的关系集合，也就是我们现在常说的 个性化 服务。
提供对某些用户隐藏某些数据的机制,创建视图命令,
create view v as <query expression>
其中,
<query expression> 是任何合法查询表达式
v 是视图名基于视图的操作
定义
查询
删除
受限更新
定义基于该视图的新视图创建视图命令,
create view v as <query expression>
其中,
<query expression> 是任何合法查询表达式
v 是视图名包含分行及其客户的视图
create view all-customer as
(select branch-name,customer-name
from depositor,account
where depositor.account-number = account.account-
number)
union
(select branch-name,customer-name
from borrower,loan
where borrower.loan-number = loan.loan-number)
查询 Perryridge 分行的所有客户
select customer-name
from all-customer
where branch-name =?Perryridge?
视图更新
用户角度：更新视图与更新基本表相同
DBMS实现视图更新的方法
– 视图实体化法（ View Materialization）
– 视图消解法（ View Resolution）
一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新 (对两类方法均如此 )
－允许对行列子集视图进行更新
－对其他类型视图的更新不同系统有不同限制
DELETE
FROM <表名 >
[WHERE <条件 >]；
– 功能
删除指定表中满足 WHERE子句条件的 元组
– WHERE子句
指定要删除的元组
缺省表示要修改表中的所有元组删除数据
删除 Perryridge分行的所有账户记录
delete from account
where branch-name =?Perryridge?
删除位于 Needham的每个分行的所有账户,
delete from account
where branch-name in (select branch-name
from branch
where branch-city =?Needham?)
delete from depositor
where account-number in
(select account-number
from branch,account
where branch-city =?Needham?
and branch.branch-name = account.branch-name)
插入单个元组 语句格式,
INSERT
INTO <表名 > [(<属性列 1>[,<属性列 2 >…)]
VALUES (<常量 1> [,<常量 2>] … )
插入子查询结果语句格式,
INSERT
INTO <表名 > [(<属性列 1> [,<属性列 2>… )]
子查询插入数据
insert into account
values (?A_9732?,?Perryridge?,1200)
insert into account (branch_name,balance,
account_number)
values (?Perryridge?,1200,?A_9732?)
insert into account
select loan_number,branch_name,200
from loan
where branch_name =?Perryridge?
语句格式
UPDATE <表名 >
SET <列名 >=<表达式 >[,<列名 >=<表达式 >]…
[WHERE <条件 >]；
可以：
修改某一个元组的值
修改多个元组的值
带子查询的修改语句数据更新
update account
set balance = balance? 1.06
where balance > 10000
update account
set balance = balance? 1.05
where balance? 10000
update SC
SET Grade=0
where 'CS'=
(SELETE Sdept
FROM Student
WHERE Student.Sno = SC.Sno)；
drop table 命令从数据库删除关系的所有信息,
alter table 命令可用于向已有关系增加属性,关系的所有元组在新属性上的值为 null,命令形如
alter table r add A D
其中 A 是新增属性名,D 是 A 的域,
alter table 命令还可用于删除关系的属性
alter table r drop A
其中 A 是关系 r 的属性名
– 许多数据库系统不支持删除属性
CREATE TABLE Student
（ NO CHAR(7) NOT NULL UNIQUE,
Sname Varchar2(20) NOT NULL,
Sex Char(1) NOT NULL,
Bdate Date,
Height Numeric(3,2)
PRIMARY KEY (NO),
):
CREATE TABEL COURSE
（ CNO CHAR(7) NOT NULL UNIQUE,
LHOUR SMALLINT NOT NULL,
CREDIT SMALLINT NOT NULL,
PRIMARY KEY (CNO),
CHECK (LHOUR BETWEEN 17 AND 102)
);
CREATE TABEL SC
(NO CHAR(4) NOT NULL,
CNO CHAR(7) NOT NULL,
GRADE SMALLINT DEFAULT NULL,
PRIMARY KEY (NO,CNO),
FOREIGN KEY (NO)
REFERENCE STUDENT ON DELETE CASCADE,
FOREIGN KEY (CNO)
REFERENCE COURSE ON DELETE CASCADE
);
ALTER TABEL COURSE ADD CNAME VARCHAR(30);
ALTER TABEL COURSE DROP PRIMARY KEY;
ALTER TABEL COURSE ADD PRIMARY KEY(CNO);
DROP TABEL COURSE;
ALTER TABEL SC ADD FOREIGN KEY F1(SNO)
REFERENCE STUDNET ON DELETE CASCADE;
SELECT * FROM SC;
SELECT SNO,SNAME
FROM STUDENT WHERE SEX=‘男 ’ ;
SELECT SNAME,100*HEIGHT
FROM STUDENT WHERE extract(YEAR from
BDATE) =1974;
SELECT * FROM Student WHERE SNAME LIKE ‘D%’ ；
SELECT SNAME,C.CNO,GRADE
FROM STUDENT,COURSE,SC
WHERE STUDENT,NO=SC.NO AND COURSE.CNO
=SC.CNO;
SELECT SNAME,GRADE
FROM STUDENT,SC
WHERE STUDENT.NO=SC.NO AND CNO=‘001’ AND
GRADE>90；
SELECT NO,SNAME
FROM STUDENT
WHERE NO in
（ SELECT NO
FROM SC
WHERE CNO=
（ SELECT CNO FROM COURSE
WHERE CNO=‘CS-101’） ）
SELECT SC.NO,MAX(GRADE)
FROM STUDENT,SC
WHERE STUDENT.NO=SC.NO
GROUP BY SC.NO HAVING COUNT(CNO)>=2;
SELECT AVG(HEIGHT) FROM STUDENT;
SELECT SNAME FROM STUDENT WHERE EXISTS
(SELECT * FROM SC WHERE SNO=STUDENT.SNO
AND CNO=’CS_101’);
SELECT CNO,MAX(GRADE)
FROM SC
GROUP BY CNO
HAVING CNO NOT IN
(SELECT CNO FROM SC
WHERE GRADE IS NULL)
ORDER BY CNO;
SELECT SNO FROM STUDENT
WHERE HEIGHT>1.80
UNION
SELECT SNO FROM SC
GROUP BY SNO
Having AVG(GRADE)>90 ；
INSERT INTO
SC(SNO,CNO) VALUES(’9000’,’CS_111’);
INSERT INTO TGRADE SELECT * FROM SC ;
DELETE FROM SC WHERE GRADE IS NULL;
UPDATE STUDENT SET HEIGHT=HEIGHT*100;
CREATE VIEW TEMP1（ SNO,CCOUNT）
AS SELECT SNO,COUNT(CNO) FROM SC GROUP BY
SNO;
语句格式
CREATE [UNIQUE] [CLUSTER] INDEX < 索引 名 >
ON <表名 >(<列名 >[<次序 >][,<列名 >[<次序 >] ]…)；
– 用 <表名 >指定要建索引的基本表名字
– 索引可以建立在该表的一列或多列上,各列名之间用逗号分隔
– 用 <次序 >指定索引值的排列次序,升序,ASC,降序：
DESC。 缺省值,ASC
– UNIQUE表明此索引的每一个索引值只对应唯一的数据记录
– CLUSTER表示要建立的索引是聚簇索引建立索引索引
CREATE INDEX H_INDEX ON STUDENT(HEIGHT)
CREATE UNIQUE INDEX SC_INDEX ON
SC(SNO ASC,CNO DESC)
DROP INDEX H_INDEX
断言
断言是表达要求数据库永远满足的条件的谓词,
SQL 的断言形如
create assertion <assertion-name> check <predicate>
作了某断言之后,系统将检查它的合法性,并对每一个可能破坏该断言的数据库更新进行检测
– 这种检测会产生大量的开销 ; 因此断言的使用应非常谨慎,
象
for all X,P(X)
之类的断言是通过迂回的方式表达的
not exists X such that not P(X)
SQL语言提供了两种不同的使用方式：
– 交互式
– 嵌入式
为什么要引入嵌入式 SQL
– SQL语言是非过程性语言
– 事务处理应用需要高级语言
这两种方式细节上有差别，在程序设计的环境下，
SQL语句要做某些必要的扩充
Embedded SQL
SQL 标准定义了将 SQL嵌入到程序设计语言 (如 Pascal,
PL/I,Fortran,C,and Cobol )的内容,
SQL 查询所嵌入的语言称为宿主语言,而在宿主语言中可用的 SQL 结构即组成了 embedded SQL.
基本形式基于 System R的 SQL到 PL/I的嵌入,
EXEC SQL 语句用于向预处理器标识 embedded SQL 请求
EXEC SQL <embedded SQL 语句 > END-EXEC
注意,依语言而变,E.g,Java 嵌入使用
# SQL { …,} ;
Host Language + Embedded SQL
PreProcessor
Host Language + Procedure Calls
Host Language
Compiler
Objection
code
Embedded SQL处理过程
1,SQL通信区向主语言传递 SQL语句的执行状态信息主语言能够据此控制程序流程
2,主变量
1）主语言向 SQL语句提供参数
2）将 SQL语句查询数据库的结果交主语言进一步处理
3,游标解决集合性操作语言与过程性操作语言的不匹配工作单元之间的通信方式
SQLCA,SQL Communication Area
– SQLCA是一个数据结构
SQLCA的用途
– SQL语句执行后,DBMS反馈给应用程序信息
描述系统当前工作状态
描述运行环境
– 这些信息将送到 SQL通信区 SQLCA中
– 应用程序从 SQLCA中取出这些状态信息，据此决定接下来执行的语句
– 与所执行的 SQL语句有关
SQL通信区
– 定义 SQLCA
用 EXEC SQL INCLUDE SQLCA加以定义
– 使用 SQLCA
SQLCA中有一个存放每次执行 SQL语句后返回代码的变量 SQLCODE
如果 SQLCODE等于预定义的常量 SUCCESS，则表示 SQL语句成功，否则表示出错
应用程序每执行完一条 SQL 语句之后都应该测试一下 SQLCODE的值，以了解该 SQL语句执行情况并做相应处理
SQLCA的使用方法
– 嵌入式 SQL语句中可以使用主语言的程序变量来输入或输出数据
– 在 SQL语句中使用的主语言程序变量简称为主变量
（ Host Variable）
主变量
输入主变量
指定向数据库中插入的数据
将数据库中的数据修改为指定值
指定执行的操作
指定 WHERE子句或 HAVING子句中的条件
输出主变量
获取 SQL语句的结果数据
获取 SQL语句的执行状态
说明之后的主变量可以在 SQL语句中任何一个能够使用表达式的地方出现
为了与数据库对象名（表名、视图名、列名等）
区别,SQL语句中的主变量名前要加冒号（,）作为标志变量说明：
BEGIN DECLARE SECTION
........,
........,
.........
END DECLARE SECTION
用 SQL 写查询并为它声明一个游标
EXEC SQL
declare c cursor for
select customer-name,customer-city
from depositor,customer,account
where depositor.customer-name = customer.customer-name
and depositor account-number = account.account-
number
and account.balance >,amount
END-EXEC
从宿主语言内查询在某账户中余额大于给定变量
amount 的客户的姓名和城市,
– 游标是系统为用户开设的一个数据缓冲区，存放
SQL语句的执行结果
– 每个游标区都有一个名字
– 用户可以用 SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理游标
使用游标的步骤
– 1,说明游标
– 2,打开游标
– 3,移动游标指针，然后取当前记录
– 4,关闭游标
使用 DECLARE语句
语句格式
EXEC SQL DECLARE <游标名 > CURSOR
FOR <SELECT语句 >;
功能
– 是一条说明性语句，这时 DBMS并不执行 SELECT
指定的查询操作。
说明游标
open 语句导致查询被执行
EXEC SQL open c END-EXEC
fetch 语句可使查询结果中的一条元组的值放入宿主语言变量,
EXEC SQL fetch c into,cn,:cc END-EXEC
循环调用 fetch 可逐条取得查询结果中的元组
SQL通信区 (SQLCA)中的变量 SQLSTATE 设置成
‘ 02000?以指示不再有数据了
close 语句使数据库系统删除保存查询结果的临时关系,
EXEC SQL close c END-EXEC
注意,上述细节随语言而变,E.g,Java 嵌入定义了 Java
iterators 来遍历结果中的元组,
通过声明游标是 for update 即可更新通过游标取得的元组
declare c cursor for
select *
from account
where branch-name=?Perryridge?
for update
修改游标当前位置上的元组
update account
set balance = balance + 100
where current of c
动态 SQL
允许程序在运行时刻构造并提交 SQL 查询,
例如
char * sqlprog =,update account
set balance = balance * 1.05
where account-number =?”
EXEC SQL prepare dynprog from,sqlprog;
char account [10] =,A-101”;
EXEC SQL execute dynprog using,account;
动态 SQL 程序包含一个?,这是一个占位符,当 SQL程序执行时提供相应的值,
ODBC（开放数据库互联标准）
– 适用于客户 -服务器体系结构，定义客户程序用以连接到数据库系统和发出 SQL命令的 API
– 客户可以用同一 ODBC API来连接到任何支持 ODBC的数据库系统； ODBC允许用户同时连接到多个数据源并在这些数据源之间进行切换
– 每个数据库系统必须提供一个驱动程序，受客户端的 ODBC
驱动程序管理器控制，负责与服务器连接和通讯以及进行所有必要的数据和查询格式转换
– ODBC API定义一个 CLI(调用层接口 )、一个 SQL语法定义以及关于允许的 CLI调用序列的规则一个完整的 ODBC由下列几个部件组成：
1,ODBC应用程序 (Application)
ODBC应用程序是用一般程序设计语言 （ 如 C语言等 ） 编写的程序 。
2,ODBC API函数
3,ODBC管理器 (Administrator)
该程序位于 Windows控制面板 (Control Panel)的 32
位 ODBC内,其主要任务是管理安装的 ODBC驱动程序和管理数据源 。
4,ODBC驱动程序管理器 (Driver Manager)
驱动程序管理器包含在 ODBC32.DLL中,对用户是透明的 。 应用程序不能直接调用 ODBC 驱动程序,
只可调用 ODBC驱动程序管理器提供的 ODBC API函数,再由 ODBC驱动程序管理器负责把相应的 ODBC
驱动程序加载到内存中,同时把应用程序访问数据的请求传送给 ODBC驱动程序 。
5,ODBC 驱动程序
ODBC 驱动程序具体负责把 SQL请求传送到数据源的 DBMS中,再把操作结果返回到 ODBC驱动程序管理器 。 后者在把结果传送至客户端的应用程序 。
每种支持 ODBC 的数据库都拥有自己的驱动程序,
一种驱动程序只能固定地与对应的数据库通信,不能访问其他数据库 。
6,数据源数据源就是需要访问的数据库 。
应用程序若要通过 ODBC访问一个数据库,则首先要创建一个数据源,主要工作是指定数据源名 （ DSN，
data source name）,使其关联一个目的数据库以及相应的 ODBC 驱动程序 。 所以说,数据源实际上是一种数据连接的抽象,指定了数据库位置和数据库类型等信息 。
ODBC应用
ODBC API
ODBC驱动管理器
Oracle驱动器 SQL Server
驱动器其它数据库驱动器
SQL Server
服务器其它数据库服务器Oracle服务器工作流程：
应用程序要访问一个数据库,首先必须用 ODBC
管理器注册一个数据源 。
ODBC管理器根据数据源提供的数据库位置,数据库类型及 ODBC驱动程序等信息,建立起 ODBC与具体数据库的联系 。
应用程序将已创建好的数据源名提供给 ODBC，
ODBC就能建立起与相应数据库的连接,为访问数据库做好准备 。
在 ODBC中,ODBC API函数不能直接访问数据库的,必须通过 ODBC驱动程序管理器与数据库交换信息 。 ODBC驱动程序管理器在应用程序和数据源之间起着转换与管理的作用 。
Opens database connection using SQLConnect().
Parameters for SQLConnect:
connection handle,
the server to which to connect
the user identifier,
password
Program sends SQL commands to the database by
using SQLExecDirect
Result tuples are fetched using SQLFetch()
ODBC CODE
Java编程语言具有坚固、安全、易于使用、易于理解和易于从网络自动下载等特性，已逐渐成为编写数据库应用程序的大众编程语言。 Java通过与 JDBC的结合，提供了良好的数据库访问性能。
JDBC为数据库开发人员提供了一个标准的 API。
JDBC API目前新版本是 JDBC 3.0 API。
JDBC 3.0组成,java.sql包和 javax.sql包。 javax.sql包是
java.sql包的补充。
JDBC
JDBC驱动程序类型
Java Application
JDBC Driver Manager
JDBC
API
JDBC Driver
API
JDBC
Implementati
on
alternatives
JDBC Net
Driver
JDBC ODBC
Bridge Driver
Driver
A
ODBC and
DB Driver
Driver
B
Proprietary database access protocol
JDBC
Middle Ware
Protocol
完整性,
保证数据的一致性
域约束
实体完整性
参照完整性
断言
触发器
2.4 完整性与安全性断言：
每家分行的贷款总额必须小于该分行的账户余额总和,
create assertion sum-constraint check
(not exists (select * from branch
where (select sum(amount) from loan
where loan.branch-name =
branch.branch-name)
>= (select sum(amount) from account
where loan.branch-name =
branch.branch-name)))
触发器
触发器 是数据库更新操作引起的被系统自动执行的语句,
设计触发器必须,
– 指明触发器被执行的条件,
– 指明触发器执行时所做的具体操作,
引入触发器的 SQL标准是 SQL:1999,但多数数据库产品早已支持非标准语法的触发器,
触发事件包括 insert,delete 或 update
针对 update的触发器可以指定具体修改的属性
– E.g,create trigger overdraft-trigger after update of balance
on account
更新前后的属性值可通过下列方法被引用
– referencing old row as,对删除和修改有效
– referencing new row as,对插入和修改有效
触发器可在某事件发生之前激活,这相当于约束,
– 例如将空格改成 null.
create trigger setnull-trigger before update on r
referencing new row as nrow
for each row
when nrow.phone-number =
set nrow.phone-number = null
既可以针对受影响的每一行执行一次单独的操作,也可以针对受到一个事务影响的所有行只执行一次操作
– for each statement (语句） vs,for each row（行）
– 用 referencing old table 或 referencing new table 来引用包含受影响的行的临时表
– 对更新大量元组的 SQL语句更高效
create trigger overdraft-trigger after update on account
referencing new row as nrow
for each row
when nrow.balance < 0
begin atomic
insert into borrower
(select customer-name,account-number
from depositor
where nrow.account-number =
depositor.account-number);
insert into loan values
(n.row.account-number,nrow.branch-name,
– nrow.balance);
update account set balance = 0
where account.account-number = nrow.account-
number
end
安全性
安全性 –防止恶意更新或偷窃数据的企图,
– 数据库系统级
验证和授权机制使得特定用户存取特定数据
本章中主要讨论授权机制
– 操作系统级
操作系统超级用户可对数据库做任何事情 ! 需要有一个好的操作系统级安全机制,
– 网络级,使用加密防止
偷听 (未授权的读取信息 )
伪装 (冒充授权用户 )
– 物理级
对计算机的物理访问使得入侵者可摧毁数据 ; 需要传统的锁钥安全手段
防止洪水,火灾等对计算机的损坏,
– 参见第 17章 (恢复 )
– 人员级
审查用户以确保授权用户不会将存取权给予入侵者
训练用户选择口令与保密对数据库中部分数据的权限形式,
读权限 –允许读,但不允许更新数据,
插入权限 –允许插入新数据,但不允许更新现有数据,
修改权限 –允许修改,但不允许删除数据,
删除权限 –允许删除数据对修改数据库模式的授权形式,
索引权限 –允许创建和删除索引,
资源权限 –允许创建新关系,
修改权限 –允许增加或删除关系的属性,
删除权限 –允许删除关系,
权限的授予
权限从一个用户到另一个用户的传递可用授权图表示,
图的节点是用户,
图的根是数据库管理员,
边 Ui?Uj 表示用户 Ui 将某权限授予给了用户 Uj.
U1 U4
U2 U5
U3
DBA
授权图
要求,授权图中的所有边都必须是某条从数据库管理员出发的路径的一部分
若 DBA 从 U1收回权限,
– 必须从 U4 收回权限,因为 U1 不再有权限
– 不能从 U5 收回权限,因为 U5 还有从 DBA经 U2 的另一条授权路径
必须防止不经过根节点的循环授权,
– DBA授权给 U7
– U7 授权给 U8
– U8 授权给 U7
– DBA 从 U7收回权限
必须收回从 U7到 U8以及从 U8 到 U7 的授权,因为不再有从 DBA到 U7或 U8 的路径,
SQL中的安全性声明
grant语句用于授权
grant <权限列表 >
on <关系或视图名 > to <用户列表 >
<用户列表 > 可以是,
– 用户名
– public,代表所有合法用户
– 角色 (见后 )
授予对视图的权限并不意味着授予对定义该视图的基础关系的权限,
权限的授予者本身必须拥有相应的权限 (或者是数据库管理员 ).
SQL中的权限
select,允许读关系,或查询视图
– 例如,授予用户 U1,U2,U3 对 branch 关系的 select
权限,
grant select on branch to U1,U2,U3
insert,允许插入元组
update,允许修改元组
delete,允许删除元组
references,创建关系时允许声明外键
usage,(SQL-92)授权用户使用指定域
all privileges,所有权限授权的权限
with grant option,允许用户把被授予的权限再转授给其他用户,
– 例如,
grant select on branch to U1 with grant option
授予 U1 对 branch 的 select 权限并允许 U1 将此权限授予其他用户角色
通过创建角色可以一次性对一类用户指定其共同的权限
象对用户一样,可以对角色授予或收回权限
角色可被赋予给用户,甚至给其他角色
SQL:1999 支持角色
create role teller
create role manager
grant select on branch to teller
grant update (balance) on account to teller
grant all privileges on account to manager
grant teller to manager
grant teller to alice,bob
grant manager to avi
SQL中的权限回收
revoke 语句用于回收权限,
revoke<权限列表 >
on <关系或视图名 > from <用户列表 > [restrict|cascade]
例如,
revoke select on branch from U1,U2,U3 cascade
从一用户收回权限可能导致其他用户也失去该权限 ; 称为级联回收,
指定 restrict可以阻止级联回收,
revoke select on branch from U1,U2,U3 restrict
如果导致级联回收,则带有 restrict的 revoke 命令失败,
<权限列表 > 可以是 all 以便收回某用户拥有的所有权限,
如果同一权限被不同授予者两次授予同一用户,则该用户在回收一次后仍保持该权限,
所有依赖于被收回权限的权限也被收回,
SQL 授权的局限性
SQL不支持元组级的授权
– 例如我们不能限制学生只能看他自己的分数
某些应用 (如 web应用 )的所有最终用户可能被映射成单个数据库用户
以上情况下的授权任务只能依靠应用程序,SQL不支持
– 授权在应用程序代码中完成,并可能散布在整个应用中
– 检查是否有权限漏洞非常困难,因为需要读大量应用程序代码思考题：
１１,a)找出下面表的三个候选键,
A B C D
a1 b1 c1 d1
a2 b3 c1 d2
a3 b4 c2 d2
a4 b2 c2 d1
２关系 R,S,T如下：
R,A B C D S,B C T,B Ｅ
a b c d c d c d
a c d b e f e g
b e f a
b c d a
b c d e
c c d e
c e f e
求：（１）Ｒ Ｔ （２）Ｒ?S
３,考虑学生数据库，用关系代数和ＳＱＬ完成：
S ( SNO,SName,Sex,Age,Class,Dept ),
C ( Cno,Cname,Dept ),
SC (SNO,Cno,Grade)
１） 查询选修了课程编号为,C01”且成绩高于 85分的学生的学号、姓名和成绩；
２）查询至少选修了三门课程的学生的学号和姓名；
３）查询计算机系张红同学选修的课程名称、学分和成绩。
