不用看，概念大部分不考，熟悉SQL语句就行。
目录
Chapter6-7 SQL–Data Definition & Data Manipulation
Chapter20 Security and Administration Transparencies
Chapter22 Transaction Management
Chapter4
Relation 关系：表
Attribute 属性：列
Domain 域：取值集合，必须给属性定义一个域
Tuple 元组：行
Degree 维度：属性个数
Cardinality 基数：元组个数
Relation schema 关系模式：由一组属性和域名对定义的关系
Relation database schema 关系数据库模式：关系模式集
关系的性质：
关系不能重名：Relation name is distinct from all other relation names in relational schema.
每个单元格确切包含一个原子值：Each cell of relation contains exactly one atomic (single) value.
属性不同名：Each attribute has a distinct name.
属性值取自域：Values of an attribute are all from the same domain.
元组各不相同：Each tuple is distinct; there are no duplicate tuples.
属性顺序不重要：Order of attributes has no significance.
元组顺序不重要，但会影响访问效率：Order of tuples has no significance, theoretically.
关系关键字：
Super key 唯一标志元组
Candidate Key 候选关键字：本身是超关键字，但子集不是超关键字；可唯一标识元组
Primary Key 主关键字
完整性：
实体完整性： 主关键字不能为空
引用完整性： 外键
一般性约束： 其他规则
Chapter6-7 SQL–Data Definition & Data Manipulation
DDL(数据定义语言)：
用于定义数据库对象(数据库、表、视图、索引)。如create,drop,alter;
表
CREATE TABLE Staff(staffNo VARCHAR(5),
lName VARCHAR(15),
salary DECIMAL(7,2));
CREATE TABLE PropertyForRent (
propertyNo PNumber NOT NULL, ….
rooms PRooms NOT NULL DEFAULT 4,
rent PRent NOT NULL, DEFAULT 600,
ownerNo OwnerNumber NOT NULL,
staffNo StaffNumber
Constraint StaffNotHandlingTooMuch ….
branchNo BranchNumber NOT NULL,
PRIMARY KEY (propertyNo),
FOREIGN KEY (staffNo) REFERENCES Staff
ON DELETE SET NULL ON UPDATE
CASCADE ….);
域
CREATE DOMAIN SexType AS CHAR
CHECK (VALUE IN (‘M’, ‘F’));
sex SexType NOT NULL
CREATE DOMAIN BranchNo AS CHAR(4)
CHECK (VALUE IN (SELECT branchNo
FROM Branch));
DROP DOMAIN DomainName
一般性约束
CREATE ASSERTION AssertionName
CHECK (searchCondition)
CREATE ASSERTION StaffNotHandlingTooMuch
CHECK (NOT EXISTS (SELECT staffNo
FROM PropertyForRent
GROUP BY staffNo
HAVING COUNT(*) > 100))
Alter
ALTER TABLE Staff
ALTER position DROP DEFAULT;
ALTER TABLE Staff
ALTER sex SET DEFAULT ‘F’;
ALTER TABLE PropertyForRent
DROP CONSTRAINT StaffNotHandlingTooMuch;
ALTER TABLE Client
ADD prefNoRooms PRooms;
Drop
DROP TABLE PropertyForRent;
创建视图
CREATE VIEW Manager3Staff
AS SELECT *
FROM Staff
WHERE branchNo = ‘B003’;
CREATE VIEW Staff3
AS SELECT staffNo, fName, lName, position, sex
FROM Staff
WHERE branchNo = ‘B003’;
CREATE VIEW StaffPropCnt (branchNo, staffNo, cnt)
AS SELECT s.branchNo, s.staffNo, COUNT(*)
FROM Staff s, PropertyForRent p
WHERE s.staffNo = p.staffNo
GROUP BY s.branchNo, s.staffNo;
WITH CHECK OPTION; ----禁止行迁移出视图
DML(数据操纵语言):
用于对表或视图进行添加、删除和修改等操作，如 insert,update,delete；
添加
INSERT INTO Staff VALUES (‘SG16’, ‘Brown’, 8300);
INSERT INTO Staff (staffNo, fName, lName, position, salary, branchNo)
VALUES (‘SG44’, ‘Anne’, ‘Jones’, ‘Assistant’, 8100, ‘B003’);
INSERT INTO StaffPropCount
(SELECT s.staffNo, fName, lName, COUNT(*)
FROM Staff s, PropertyForRent p
WHERE s.staffNo = p.staffNo
GROUP BY s.staffNo, fName, lName)
UNION
(SELECT staffNo, fName, lName, 0
FROM Staff
WHERE staffNo NOT IN
(SELECT DISTINCT staffNo
FROM PropertyForRent));
修改
UPDATE Staff
SET position = ‘Manager’, salary = 18000
WHERE staffNo = ‘SG14’;
删除
DELETE FROM Viewing
WHERE propertyNo = ‘PG4’;
DELETE FROM Viewing;
删除视图
DROP VIEW Manager3Staff;
更新视图，基表也变
INSERT INTO StaffPropCnt
VALUES (‘B003’, ‘SG5’, 2);
DQL (数据查询语言) :
用于从数据库中检索数据。如select；
比较
SELECT staffNo, lName, salary
FROM Staff
WHERE salary > 10000;
SELECT *
FROM Branch
WHERE city = ‘London’ OR city = ‘Glasgow’;
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary BETWEEN 20000 AND 30000;
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary>=20000 AND salary <= 30000;
SELECT staffNo, fName, lName, position
FROM Staff
WHERE position IN (‘Manager’, ‘Supervisor’);
SELECT staffNo, fName, lName, position
FROM Staff
WHERE position=‘Manager’ OR position=‘Supervisor’;
模糊查询
SELECT ownerNo, fName, lName, address, telNo
FROM PrivateOwner
WHERE address LIKE ‘%Glasgow%’;
空
SELECT clientNo, viewDate
FROM Viewing
WHERE propertyNo = ‘PG4’ AND comment IS NULL;
消除重复
SELECT DISTINCT propertyNo
FROM Viewing;
计算
SELECT staffNo, fName, lName, salary/12
FROM Staff;
设置列名
SELECT staffNo, fName, lName, salary/12 AS monthlySalary
FROM Staff;
排序（DESC降序，ASC升序，默认升序）
SELECT staffNo, fName, lName, salary
FROM Staff
ORDER BY salary DESC;
计数
SELECT COUNT(*) AS myCount
FROM PropertyForRent
WHERE rent > 350;
SELECT COUNT(DISTINCT propertyNo) AS myCount
FROM Viewing
WHERE viewDate BETWEEN ‘1-May-13’ AND ‘31-May-13’;
求和
SELECT COUNT(staffNo) AS myCount,
SUM(salary) AS mySum
FROM Staff
WHERE position = ‘Manager’;
最大值、最小值、平均值
SELECT MIN(salary) AS myMin,
MAX(salary) AS myMax,
AVG(salary) AS myAvg
FROM Staff;
分组
SELECT branchNo,
COUNT(staffNo) AS myCount,
SUM(salary) AS mySum
FROM Staff
GROUP BY branchNo
ORDER BY branchNo;
限定显示的分组
SELECT branchNo,
COUNT(staffNo) AS myCount,
SUM(salary) AS mySum
FROM Staff
GROUP BY branchNo
HAVING COUNT(staffNo) > 1
ORDER BY branchNo;
嵌入select
SELECT staffNo, fName, lName, position
FROM Staff
WHERE branchNo =
(SELECT branchNo
FROM Branch
WHERE street = ‘163 Main St’);
SELECT staffNo, fName, lName, position, salary – (SELECT AVG(salary) FROM Staff) As SalDiff
FROM Staff
WHERE salary >
(SELECT AVG(salary)
FROM Staff);
SELECT propertyNo, street, city, postcode, type, rooms, rent
FROM PropertyForRent
WHERE staffNo IN
(SELECT staffNo
FROM Staff
WHERE branchNo =
(SELECT branchNo
FROM Branch
WHERE street = ‘163 Main St’));
SOME至少一个/ANY任何一个
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary > SOME
(SELECT salary
FROM Staff
WHERE branchNo = ‘B003’);
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary > ALL
(SELECT salary
FROM Staff
WHERE branchNo = ‘B003’);
连接
内连接
SELECT c.clientNo, fName, lName, propertyNo, comment
FROM Client c, Viewing v
WHERE c.clientNo = v.clientNo;
SELECT s.branchNo, s.staffNo, fName, lName,
propertyNo
FROM Staff s, PropertyForRent p
WHERE s.staffNo = p.staffNo
ORDER BY s.branchNo, s.staffNo, propertyNo;
SELECT b.branchNo, b.city, s.staffNo, fName, lName,
propertyNo
FROM Branch b, Staff s, PropertyForRent p
WHERE b.branchNo = s.branchNo AND s.staffNo = p.staffNo
ORDER BY b.branchNo, s.staffNo, propertyNo;
SELECT s.branchNo, s.staffNo, COUNT(*) AS myCount
FROM Staff s, PropertyForRent p
WHERE s.staffNo = p.staffNo
GROUP BY s.branchNo, s.staffNo
ORDER BY s.branchNo, s.staffNo;
外连接：不匹配的行也出现在结果表中
左连接，左边无匹配的行显示
SELECT b.*, p.*
FROM Branch1 b LEFT JOIN PropertyForRent1 p
ON b.bCity = p.pCity;
右连接，右边无匹配的行显示
SELECT b.*, p.*
FROM Branch1 b RIGHT JOIN PropertyForRent1 p
ON b.bCity = p.pCity;
全连接，左右无匹配的行均显示
SELECT b.*, p.*
FROM Branch1 b FULL JOIN PropertyForRent1 p
ON b.bCity = p.pCity;
Exists，可用连接重写
SELECT staffNo, fName, lName, position
FROM Staff s
WHERE EXISTS
(SELECT *
FROM Branch b
WHERE s.branchNo = b.branchNo AND city = ‘London’);
UNION，表示有
(SELECT city FROM Branch WHERE city IS NOT NULL)
UNION
(SELECT city FROM PropertyForRent WHERE city IS NOT NULL)
Intersect相交
(SELECT city FROM Branch)
INTERSECT
(SELECT city FROM PropertyForRent);
Except除了
(SELECT city FROM Branch)
EXCEPT
(SELECT city FROM PropertyForRent);
DCL(数据控制语言):
用于控制用户对数据库的存取能力。如grant,deny,revoke。
授权
GRANT ALL PRIVILEGES
ON Staff
TO Manager WITH GRANT OPTION;
GRANT SELECT, UPDATE (salary)
ON Staff
TO Personnel, Director;
GRANT SELECT
ON Branch
TO PUBLIC;
撤销权限
REVOKE SELECT
ON Branch
FROM PUBLIC;
REVOKE ALL PRIVILEGES
ON Staff
FROM Director;
Chapter20 Security and Administration Transparencies
相关问题：
盗用和假冒 Theft and fraud
破坏机密性 Loss of confidentiality (secrecy)
破坏隐私 Loss of privacy
破坏完整性 Loss of integrity
破坏可用性 Loss of integrity
Threat威胁：
有意或无意、可能会对系统造成负面影响
对策：
授权
授予权限实现合法访问
认证
判断身份是否属实
访问控制
基于权限的授予与回收
自主访问控制
DAC，使用SQL进行权限管理
GRANT
REVOKE
强制访问控制MAC
每一个数据库对象有security class安全级别
每一位用户有对某种安全级别的clearance访问许可级别、限制读写
View视图
关系运算的动态结果，虚virtual关系，向用户隐藏部分信息
Back up备份
周期性复制存储到脱机媒介
Journaling 日志
保存维护数据库变化
Integrity完整性
防止非法数据的生成
Encryption加密
特点算法编码，密钥
RAID独立磁盘冗余阵列
具备冗余、容错
Disk磁盘最脆弱
使用RAID
数据条带化data stripe
奇偶校验码
Access的安全机制
拆分数据库
为数据库设置密码
托管（启用）数据库中禁用的内容
打包、签名并部署数据库
Oracle的安全机制
权限
系统权限
对象权限
和web安全
数据明文传输
Chapter22 Transaction Management
Transaction事务
逻辑操作单位
Success：commit
Failure：abort
还原到开始前：roll back回滚/undone撤销
已经提交的不能被撤销
被回滚的可能稍后重启
ACID事务性质
Atomicity 原子性
Consistency 一致性
Isolation 隔离性
Durability 持久性
Concurrency control并发控制
管理并发操作使其不冲突
可能导致的问题：
Lost update丢失更新问题
更新已完成，结果被另一个用户的操作结果覆盖了
禁止另一事务读取值
Uncommitted dependency未提交依赖问题
允许一个事务看见另一个未提交事务的中间结果
在这一事务结束前禁止另一事务读取值
Inconsistent analysis problem不一致分析问题
某事务读取多个值，另一事务在其读取期间修改了某些值；对同一数据项的两次读取出现不同结果，也叫不可重读问题unrepeatable read
在更新的事务完成更新前禁止另一事务读值
Serializability可串行性
识别执行时能确保一致性的事务
Schedule调度
一组并发事务操作的序列
Serial schedule串行调度
每一个事务操作按顺序执行且操作不交叉
Nonserial schedule非串行调度
交叉执行
Serializable可串行化
非串行化调度能产生和串行相同结果，该调度可串行化
读写次序
Conflict冲突
一个事务写一个数据项，另一个事务同时读或写同一个数据项
Conflict serializable schedule冲突可串行化调度
冲突操作，可串行化
所有冲突的执行次序与其在串行调度中相同
检测：precedence graph优先图，又称serialization graph串行化图，表示优先关系
有环存在则不是冲突可串行化
View serializability视图可串行化
若调度视图等价于一个串行调度，则该调度视图可串行
所有冲突可串行都是视图可串行，反之不然
盲写：不符合限定写规则，写之前未读取
视图可串行且非冲突可串行：必包含至少一个盲写
Recoverability可恢复性
Recoverable schedule可恢复调度
修改的事务提交在读取的事务前
并发控制技术
Locking加锁
当一个事务访问数据库时，可以用锁拒绝其他事务的访问请求
Shared（read）共享锁，只能读不能改
多个事务可以同时拥有
Exclusive（write）互斥锁，能读也能改
排他
共享锁可升级为互斥锁
互斥锁可降级为共享锁
2PL两段锁，保证可串行化
所有加锁都在第一个解锁之前
事务分为扩展growing阶段和收缩shrinking阶段
解决并发控制的三个问题
Cascading rollback级联回滚
由一个事务引发一连串回滚
Timestamping时间戳
确定事务顺序，越早的事务时间戳越小
读写时只有最后一次修改在较早时间才允许
数据项有读时间戳和写时间戳
ts(T) ≥ write_timestamp(x)时可以执行
索引结构的并发控制
加锁
Deadlock死锁
撤销至少一个事务
死锁处理技术
Timeout超时
有时间上限
Deadlock prevention死锁预防
提前判断
Deadlock detection and recovery死锁检测与恢复
多版本时间戳排序；乐观技术；Granularity粒度；粒度的层次；intention lock意向锁
数据库恢复
撤销undo
部分撤销、全局撤销
Log file日志
Checkpoint检查点
Three main recovery techniques:
Deferred Update 延迟修改
Immediate Update 立即修改
Shadow Paging 影子页技术
590
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
