数据库知识点【复试】

文件与数据库的差异：
文件存储实际上是字符流，只能做create，open，read等基本操作，其他一些操作要自己实现，针对不同的文件类型要编写不同的代码。而数据库存储是由数据库管理系统（DBMS)来实现许多操作。

数据模型包括：层次数据模型（树）、网状数据模型（set系）、关系数据模型（关系、表）。

主键、外键（在另一张表里是主键）、候选键

五大基本操作：
投影：把不需要的属性去掉
选择：选择符合条件的元组
并、差：属性个数一样，属性类型一样
笛卡尔积

连接：匹配成功的元组合并
左外连接：连接符号左边的全部保留，没有匹配的补空值。
右外连接：连接符号右边的全部保留，没有匹配的补空值。
**全外连接：**连接符号两边的元组全部保留，能匹配的匹配，没有匹配的补空值。
外并操作：两边元组都保留，缺少的补空值。

SELECT [DISTINCT] target-list FROM relation-list WHERE qualifications
[DISTINCT表示去重]
[FROM语句中做所有需要表的笛卡尔乘积]
[FROM语句中给表起别名字]

SELECT S.age,age1=S.age-5,2*S.age AS age2
FROM Sailor S
WHERE S.sname LIKE ‘B_%B’
（SELECT中写出了两种命名方式）

嵌套查询：
SELECT S.name
FROM Sailor S
WHERE S.sid IN(SELECT R.sid
FROM Reserve R
WHERE R.bid=103)

条件嵌套查询：
SELECT S.name
FROM Sailor S
WHERE EXISTS(SELECT *
FROM Reserves R
WHERE R.bid=103 AND S.sid=R.sid)

查询只被一个水手订过的船：
SELECT sid
FROM Reserves R1
WHERE sid NOT IN(
SELECT sid
FROM Reserves R2
WHERE R2.sid≠R1.sid)

查询比Hroatio分数高的水手id：
SELECT *
FROM Sailors S
WHERE S.rating > ANY(SELECT S2.rating
FROM Sailors S2
WHERE S2.sname=‘Hroatio’)

查询预定了所有船的水手：（否定之否定）
SELECT S.name
FROM Sailors S
WHERE NOT EXISTS
(SELECT B.bid
FROM Boats B
EXCEPT
(SELECT R.bid
FROM Reserves R
WHERE R.sid=S.sid))

SELECT S.name
FROM Sailors S
WHERE NOT EXISTS(SELECT B.bid
FROM Boats B
WHERE NOT EXISTS(SELECT R.bid
FROM R.bid
WHERE R.bid=B.bid
ANDR.sid=S.sid))

基本操作：
SELECT COUNT(*)
FROM Sailors S

SELECT COUNT(DISTINCT S.rating)
FROM Sailors S
WHERE S.sname=‘Bob’

SELECT AVG(S.age)
FROM Sailors S
WHERE S.rating=10

SELECT AVG(DISTINCT S.age)
FROM Sailors S
WHERE S.rating=10

SELECT S.sname
FROM Sailors S
WHERE S.rating=(SELECT MAX(S2.rating)
FROM Sailors S2)

选择年龄最大的水手的名字和年龄：
SELECT S.sname,S.age
FROM Sailors S
WHERE S.age=
(SELECT MAX(S2.age)
FROM Sailros S2)

查询年龄大于18的并且rating至少有两个的年轻的选手年纪和rating：
SELECT S.rating,MIN(S.age) AS minage
FROM Sailors S
WHERE S.age>=18
GROUP BY S.rating
HAVING COUNT() > 1

查询年龄大于18的并且rating至少有两个并且rating相同的年纪不超过60的年轻的选手年纪和rating：
SELECT S.rating,MIN(S.age) AS minage
FROM Sailors S
WHERE S.age>=18
GROUP BY S.rating
HAVING COUNT() > 1 AND EVERY(S.age<=60)

对每条红色的船，查询预定人数：
SELECT B.bid,COUNT(*) AS count
FROM Baots B , Reverses R
WHERE R.bid = B.bid AND R.color=‘red’
GROUP BY B.bid

查询rating分组的平均年龄最小的rating：
SELECT Temp.rating
FROM (SELECT S.rating, AVG(S.age) AS avgage
FROM Sailors S
GROUP BY S.rating)AS Temp
WHERE Temp.avgage=(SELECT MIN(Temp.avgage)
FROM Temp)
公共表表达式：
查询第一个部门的平均工资是第二个的两倍
WITH deptavg(deptno,avgsal) AS
(SELECT deptno,avg(salary)
FROM emp
GROUP BY deptno)
SELECT d1.deptno,d1.avgsal,d2.deptno,d2.avgsal
FROM deptavg AS d1,deptavg AS d2
WHERE d1.avgsal > 2*d2.avgsal

数据管理三个阶段：
人工管理阶段、文件系统阶段、数据库系统比较

数据结构化是数据库系统和文件系统的根本区别。
数据库系统使数据的共享性高、冗余度低、易扩充。
解释：由于数据面向整个系统，是由结构的数据，不仅可以被多个应用共享使用，而且容易增加新的应用，这就使得数据系统弹性大，易于扩充，可以适应各种用户的的要求。

数据模型：
比较真实的模拟现实世界、容易为人理解、便于在计算机上实现。包括层次模型、网状模型、关系模型。

数据模型的组成要素：
数据模型描述了系统的三个方面：静态特性、动态特性、完整性约束条件。一般由数据结构、数据操作和完整性约束三部分组成。

数据库系统的三级模式结构：
1.外模式（子模式、用户模式）用户能看到的视图。
2.模式（概念模式、逻辑模式）是数据库中全体数据的逻辑结构和特征的描述。一个数据库只有一个模式。定义模式时不仅要定义数据的逻辑结构，还有完整性约束。
3.内模式（存储模式）一个数据库只有一个内模式，是数据物理结构和存储方式的描述，是数据在数据库内部的表示。

数据库的二级映像：
为了能够在内部实现这三个抽象层次的联系和转换，在三级模式之间提供了二级映像，保证了数据库系统的数据能够具有较高的逻辑独立性和物理独立性。
1.外模式/模式映像
模式是全局逻辑结构，外模式是局部逻辑结构，模式改变时，外模式可以不变，应用程序是依赖外模式编写的，因此保证了逻辑独立性。
2.模式/内模式映像
数据库中模式和内模式都只有一个，因此该映像是唯一的，定义了数据库全局逻辑结构与存储结构之间的对应关系。当存储模式改变了，管理员可以通过操作模式/内模式映像，使模式不变，保证了物理独立性。

候选码：
若关系中的某一属性的值能唯一地标识一个元组，则称该属性组为候选码，关系至少含有一个候选码。
主码：
若一个关系有多个候选码，则选定其中一个为主码。
主属性：
候选码中的诸属性。
全码：
关系模式的所有属性组是这个关系模式的候选码，称作全码。

范式：
关系模型要求关系必须是规范化的，最基本的一条是关系的每个分量都是不可再分的数据项。规范化的关系称为范式。

关系的完整性：
关系模型中有三类完整性约束：实体完整性、参照完整性、自定义完整性。
**实体完整性：**若属性K是基本关系R的主码，则所有元组K的取值唯一，并且K中属性不能全部或部分取空值。
参照完整性：
设F是基本关系R的一个或一组属性，但不是关系R的码，如果F与基本关系S的主码相对应，则称F是基本关系R的外码，并称基本关系R为参照关系，基本关系S为被参照关系或目标关系。
主码和外码必须定义在同一个域上，参照完整性规则就是定义外码与主码之间的引用规则。
eg：
学生（学号，姓名，性别，年龄，系别号）
课程（课程号，课程名，课时）
选修（学号，课程号，成绩）
“学号，课程号”属于是选修关系的外码，学生和课程关系均为被参照关系，选修关系为参照关系。
【主码所在的为被参照关系，外码所在的为参照关系】

关系代数的运算符：
集合运算符：并、交、差
比较运算符：大于等于、大于、等于
专门的关系运算符：广义笛卡尔积、选取、投影、除、连接
逻辑运算符：与、或、非

交不是基本运算：可以用差来表示。
R∩S => R-(R-S)

连接：等值连接和自然连接。
等值连接：连接可附加要求。
自然连接：有相同属性的进行连接并去掉重复属性

关系演算：元组关系演算（ALPHA)、域关系演算
ALPHA语言主要有：GET、PUT、HOLD、UPDATE、DELETE、DROP
语句的基本格式：
操作语句 表达式 操作条件
【具体操作看2.5PPT】

元组变量又称范围变量，有两方面用途：
简化关系名、操作条件中使用量词时必须用元组变量。

查询不选C1号课程的学生名字：
RANGE SC SCX
GET W(S.SN):任意SCX(SCX.SNO≠S.SNO或SCX.CNO≠‘C1’)

更新操作：
1.把200407学生从计算机科学系转到信息系
HOLD W(S.SNO,S.DEPT):S.SNO=‘200407’
MOVE ‘IS’ TO W.DEPT
UPDATE W

2.将学号200401改为200410
HOLD W(S):S.DEPT=‘200401’
DELETE W
MOVE ‘200410’ TO W.SNO
MOVE ‘李立勇’ TO W.SN
MOVE ‘男’ TO W.SEX
MOVE ‘20’ TO W.AGE
MOVE ‘CS’ TO W,DEPT
PUT W(S)

修改主码的操作，一般要分解为先删除，再插入的方法完成操作

结构化查询语言SQL是一种介于关系代数和关系演算之间的语言，其功能包括查询、操纵、定义、控制四个方面。

SQL语言支持关系数据库三级模式结构，外模式对应视图和部分基本表，模式对应于基本表，内模式对应于存储文件。

SQL语言的主要特点：
1.综合统一（集数据定义语言DDL数据操纵语言DML数据控制语言DCL功能于一体）
2.高度非过程化（减轻用户负担，提高数据独立性）
3。面向集合的操作方式
4.以同一种语法结构提供两种使用方式
5.语言便捷，易学易用

实例：
CREATE TABLE S
( SNO CHAR(5) PRIMARY KEY
SN VARCHAR(8) NOT NULL,
SEX CHAR(2) NOT NULL CHECK (SEX IN('男‘,‘女’)),
AGE INT NOT NULL CHECK (AGE>0),
DEPT VARCHAR(20),
CONSTRAINT SN_U UNIQUE(SN)
)

CREATE TABLE C
( CNO CHAR(5) NOT NULL PRIMARY KEY
CN VARCHAR(20),
CN INT CHECK(CT>=1)
)

CREATE TABLE SC
( SNO CHAR(5) NOT NULL CONSTRAINT S_F FOREIGN KEY REFENCES S(SNO),
CNO CHAR(5) NOT NULL,
SCORE NUMERIC(3,0),
CONSTRAINT S_C_P PRIMARY KEY(SNO,CNO),
CONSTRAINT C_F FOREIGN KEY(CNO) REFENCES C(CNO))

ALTER TABLE S ADD SCOME DATETIME

ALTER TABLE S ALTER COLUMN AGE SMALLINT

ALTER TABLE S DROP COLUMN SCOME

ALTER TABLE SC NOCHECK CONSTRAINT C_F

DROP TABLE S

CREATE UNIQUE INDEX S_SNO ON S(SNO)
CREATE CLUSTERED INDEX C_CNO ON C(CNO DESC)
CREATE NOCLUSTERED INDEX SC_SNO_CNO ON SC(SNO ASC,CNO DESC)

DROP INDEX S.S_SNO

WHERE中通配符：
%：代表零个或多个自负的任意子串
WHERE title LIKE '%computer‘，表达书名任意位置包含单词computer的条件
_:代表任意单个字符
WHERE au_fname LIKE ‘_ean’，表达以ean结尾的四个字母的名字的条件
[]：指定范围
WHERE au_lname LIKE ‘[C-P]arsen’，表达以arsen结尾且介于C与P之间的任何单字符开始的作者姓氏的条件
[^]：不属于指定范围
WHERE au_lname LIKE ‘de[^l]%’，表达以de开始且其后的字母不为1的所有作者姓氏

视图的作用：
1.视图能简化用户的操作
2.视图使用户能以多种角度看待统一数据
3.视图对重构数据库提供了一定程度上的逻辑独立性
4.视图能够对机密数据提供保护
5.适当的视图可以更清晰、方便的表达查询

**SQL系统安全机制：**视图机制、权限机制
**数据库权限：**系统权限、对象权限

把创建表的权限给用户U1：
GRANT CREATE TABLE TO U1

把U1所拥有的创建表的权限收回：
REVOKE CREATE TABLE FROM U1

关系数据库设计理论包括三个方面内容：
函数依赖、范式、模式设计。

如何找出R的所有候选码：
1.定义法—通过候选码的定义来求解
2.规范求解法
【首先是P：没在右侧出现过的，然后是Q，只在右侧出现过。若P能推出U，则候选码是P。否则，P依次与U-P-Q的元素组合判断是否能推出U，能的话就是候选码。】
3.属性划分求解法
【首先L：只在左边出现的，N：只在右边出现的，LR左右都出现，N：左右都不出现。首先看L∪N能不能推出U，能的话就是候选码。接下来依次和LR中的元素组合判断是否能推出U。】

规范化：
一个低一级范式的关系模式，通过模式分解可以转换为若干个高一级范式的关系模型的集合，这种过程就叫规范化。

第一范式：是最基本的规范化形式，即关系中每个属性都是不可再分的简单项。
第二范式：在1NF的基础上，消除了非主属性对于主属性的部分函数依赖。（简单的说，是表中的非主属性必须完全依赖于主属性）
第三范式：就是指表中的所有数据元素不但要唯一地被主关键字所标识，而且它们之间还必须相互独立，不存在其他的函数关系。也就是说，在2NF的基础上消除传递函数依赖。
【符合第三范式的数据表，消除了数据冗余、更新异常、插入异常、删除异常】
BCNF:消除主属性对主键的部分函数依赖。
第四范式：消除表中的多值依赖，以解决信息冗余，达到一对一的关系。

总结：
1NF保证了原子性；
2NF去除非主属性对于主属性的部分依赖；
3NF去除非主属性对于主属性的传递依赖；（属性完全依赖于主键）
BCNF消除主属性对主键的部分与传递依赖；
4NF消除表中的多值依赖。

DBMS对数据库的安全保护功能通过四方面实现：
安全性控制、完整性控制、并发性控制、数据库恢复。
【安全性控制】：用户标识和鉴别、用户存取权限控制、视图机制、数据加密、审计
【数据库的完整性是指保护数据库中数据的正确性、有效性和相容性，防止错误的数据进入数据库造成无效操作。】

排它锁：禁止并发操作。
共享锁：允许其他用户对同一数据对象进行查询，但不能对该数据对象进行修改。

数据库恢复：
登记日志文件、数据转储。

数据库故障：
事务故障：检查日志文件，取消没有完成的事务做出的改变
系统故障：撤销所有未完成的事务，重做所有已提交的事务
介质故障：日志文件重新做所有运行事务

设计一个数据库应用系统：
需求分析、概念设计、逻辑结构设计、物理结构设计、实施与运行维护。

数据库建设是硬件、软件和干件（技术和管理的界面称为干件）的结合。

需求分析：
数据库需求分析的任务主要包括“数据或信息”和“处理“两个方面。
1）信息要求：指用户需要从数据库中获得信息的内容与性质，由信息要求可以导出各种数据要求。
2）处理要求：指用户有什么处理要求（响应时间、处理方式等），最终要实现什么功能。
3）系统需求分析说明书：是系统分析阶段的最后工作，是对需求分析阶段的一个总结。（附件中可含数据流程图、功能模块图、数据字典）
用于需求分析的方法：自顶向下（SA）、自底向上。
数据字典：数据项、数据结构、数据流、数据存储、处理过程。

概念设计：
将需求分析得到的用户需求抽象为信息结构（即概念模型）的过程就是概念结构设计。
最常用的方法是：自顶向下的进行需求分析，自底向上的进行设计概念模式设计。
对于自底向上的设计方法来说，概念结构设计的步骤分两步：
1.进行数据抽象，设计局部E-R模型
2.集成各局部E-R模型，形成全局E-R模型
注意区分实体和属性：
1.属性不能再具有需要描述的性质
2.属性不能与其他实体具有联系

物理结构设计：
包括记录存储结构的设计、存储路径的设计、记录集簇的设计。
记录存储结构设计：设计存储记录的结构形式
关系模式的存取方法选择：B+树索引方法、聚簇方法、HASH方法
聚簇：为了提高某个属性（或属性组）的查询速度，把这个或这些属性（称为聚簇码）上具有相同值的元组集中存放在连续的物理块称为聚簇。
HASH方法：
1.该关系的属性主要出现在等值连接条件中或主要出现在相等比较选择条件中
2.该关系的大小可预知且关系的大小不变

数据库实施：
确定了数据库的逻辑结构和物理结构之后，就可以选用DBMS提供的数据定义语言（DDL）来严格描述数据库结构。