软考中级数据库工程师下午题知识点个人归纳

有多值依赖，X→→Y，X和Y在一个关系模式，且只有X和Y不能有其他属性。如果有则不满足4NF，可以回答原因：“X多值决定Y，由于存在嵌入式的多值依赖，所以不满足4NF”。C和T可以决定多个B，称多值依赖

创建表create table;  指定主键primary key();  外键foreign key... references 修改表alter table 删除表drop table 索引index，视图view，触发器trigger存储过程procedure;

with check option用在视图里表示要满足where后的谓词条件。

数据库查询select...from..where;

分组查询group by，分组时要注意select后的列名要适应分组，having为分组查询附加条件;排序order by，默认为升序ASC，降序要加关键字DESC。

更名运算as;

字符串匹配like，%匹配多个字符串，_匹配任意一个字符串;distinct:过滤重复的选项，只保留一条记录。

INTERSECT:交，对两个sQL语句的查询结果做与运算，即值同时存在于两个语句才被选出EXCEPT:差，满足第一个sQL语句但不满足第二个sQL语句的结果被选出。

聚合函数:sum,count,avg,max,min,all,anv

数据库插入insert into...values();数据库删除delete from...where;数据库修改update...set...where;

授权grant...on...to，允许其将权限再赋给另一用户with grant option;收回权限revoke...on...from:

创建触发器

CREATE TRIGGER<触发器名>[{BEFORE |AFTER},

{[DELETE [ INSERT[UPDATEOF[列名清单]]}ON表名

[REFERENCING<临时视图名>习][WHEN<触发条件>]

BEGIN

<触发动作>END [触发器名]

创建存储过程

CREATE PROCEDURE ScrambleRed (IN BatchNo VARCHAR(20),IN RecvrNo VARCHAR(20)

定义变量：DECLARE NonRecvedNo VARCHAR(30);

创建视图：create view 视图名（属性）

游标

EXEC SQL

END_EXEC

（1）定义游标 DECLEARE 游标名 CURSOR FOR

（2）打开游标 OPEN 游标名

（3）推进游标 FETCH FROM 游标名 INTO 变量表

（4）关闭游标 CLOSE 游标名事务：BEGIN TRANSACTION;END TRANSACTION;

循环：while条件end while

设置事务隔离级别：SET Transaction lsolation Level

READ_UNCOMMITTED:读未提交，这是事务最低的隔离级别，它允许另外一个事务可以看到这个事务未提交的数据。

READ_COMMITTED:读提交，保证一个事务修改的数据提交后才能被另外一个事务读取。另外-个事务不能读取该事务未提交的数据。

REPEATABLE_READ:重复读，在开始读事务时,不允许修改操作。

SERIALIZABLE:不考虑事务的并发性。事务被处理为串行化执行。

做题归纳

实体完整性约束：Alter table 表 add 属性 类型

参照完整性约束：Alter table 表 add constraint FK_表 foreign key(列) references 表（列）

触发器的where表看函数，列看referencing的新列名（row,nrow）

触发器题中，referencing后有nrow，则where的关联表一定有nrow,一般是函数名.Eno=nrow.Eno

触发器题中，自动修改用update，添加用insert

Where 后跟表1.列=表2.列,表看from后面跟着的表，列看where and后的条件，看这几个表的共同属性

如果前面as新表名，后面的where 一定用上新表名

统计、查询…的A，B，则group by A,B

查询数量、提及“多次”：select count(*)

Order by sum(金额) desc  看题目提及“同一个XX”，往往按XX排序

Group by A,B(照抄select后跟的两列，不包括count) having sum(金额)>=1000

不以A结尾：not like ‘%A’/

不在xx部门工作的工号：where 工号 not in (select 工号 where 部门号)

没有安排过、一件都没有：where 属性 not in

建表指定外键：foreign key(本表的列) references 关联的表名(关联表的列名)

创建新表，指定外码：新表的列（类型）references 关联的表名(关联表的列名)

          DATETIME default GetDate

创建关系的最后两个空是让填外键，找上面的关系实体，填foreign key(编号) references 实体（编号）

员工、次数、销量最多：having count(*)>=ALL或 having sum(数量)>=ALL

只有触发器题用referencing,填空只填references

有两个select，中间用union连接；select嵌套用in

不足不计入：int(…)

外键约束级联：references 表（属性）on delete cascade

每添加一个学生，自动修改相应学院的人数：

if inserting then update school set schstunum= schstunum+1 where school.schno=nrow.schno

if deleting then update school set schstunum= schstunum-1 where school.schno=nrow.schno

上题查询count(入住时间)，本题问可以加什么索引？    表:住宿 属性:入住日期  类型:聚簇索引或cluster

原因:表中记录的物理顺序与索引项的顺序一致，根据索引访间数据时，一次读取操作可以获取多条记录数据，因而可减少查询时间

check约束：

1. 金额大于0：check(金额>0) 
2. 性别：Sex char check(stusex in (‘F’,’M’))
3. 余额：number check（余额>=1.00）
4. 关系模式选择check values in（…）
5. check  in(‘状态’)

两段锁协议

调度是否正确：不正确，该调度结果与任何一个串行结果都不同

并发正确准则：可串行化调度 保证并发：2PL

见rollback直接找对应语句修改为commit

Where跟的往往是加主键ID

（背）三级封锁协议

一级：事务T读取资源前加X锁，读完释放 二级：一级基础上读前加S锁，读完释放 三级：…事务结束释放

ER图

数据存储一般为文件、表、数据库。

属性：圆形

加工（菱形）：必须有输入又有输出

看是否缺数据流：父子图平衡（简化图和详细图的数据流一样），子图内平衡（圆形实体两端都有箭头指向）

填关系模式：第一个是XX号，看描述中“每个…多个”涉及的实体，“系统”记录…；看表，表中有xxx号的特别留意

补充关系模式：圈出描述的实体，一定要将提及的实体连接上

弱实体：必须依赖于其他实体而存在的，如员工电话与员工

关系规范化

平凡多值依赖：A→C，D    非平凡多值依赖：A→B→C，D

传递依赖（2NF）：（时间，学生）→教室  （时间，教室）→课程

传递依赖：学号→学院→电话  学号→学院编号→学院名称 校编→班级→院系，入学年份

看到电话的都可能是2NF，会是多值依赖

BCNF（不存在传递依赖）：（学生，课程）→成绩  学生&课程不能→成绩

2NF、3NF：消除非主 BCNF：消除主的传递和部分依赖

判断候选键：L一定是、R一定不是、NLR一定是

第1，2，3范式都可能数据冗余、删插改异常，但BC范式不存在这些问题，消除了函数依赖。

将一对多的2个关系合为1个关系→非主属性对码的部分依赖→存在插删异常、数据冗余

部分依赖：编号→A，（编号，条码）→B，由此可推（编号，条码）→A

         即两个候选码中，其中一个主键可以直接决定某一非主属性

消除部分依赖的分解，仅看子集

1NF：存在部分依赖，存在主键A和B，A→A’  B→B’， A’与B’无交集，即主键各自决定各自的

判断2NF: ①找关系中的非主（编号以外的属性）②看非主之间是否存在依赖关系，如有，即为部分依赖，不满足

判断3NF:看主键决定的属性能不能再决定其他属性

判断4NF：消除非平凡多值的依赖关系，看是否存在多值依赖注意“多个””若干”，手机号要另分ex:（手机号，工号）

主键不可以取空值，违反实体完整性约束，记录无法插入

ER图

双实线实体：多值属性

虚线实体：派生属性（年龄与身份证号、职工与家属、总价与单价和数量）

依赖关系(1:n,N:M)是虚线，关联关系（1:1）是实线(?)

2PL事务题

先将用户1 和用户2的事务分开写出，在读前加锁XLOCK，在写后解锁Unlock，且解锁指令之间不可加锁，AB解锁放到最后一起。要修改数据（置空等），先将S锁升级为X锁

If error then{ROLLBACK;return}commit

只有wirte后,数据才更改,read的数据才和前面不同

Redo:在检查点之前未提交 undo:只有开始没有结束

当写入订单表，无法修改库存表，解决：将写入订单表和修改库存表作为完整的事务处理；当修改库存表遇到违反事务无法执行的问题时，回滚事务，则会撤销写入的订单记录，数据库保持一致

磁盘损坏，数据库无法启动。 解决：①装入最新的数据库后备副本，使数据库恢复到最近一次转储时的一致性状态。②转入相应的日志文件副本，重做已完成的事务。

连续写入可能出现的问题：客1购买写入的库存值被覆盖，不能体现客1已买，属于丢失修改，造成数据的不一致

原因：并发操作破坏了事务的隔离性

给出起始和结束日期，则where 日期 BETWEEN :Bdate AND :Edate

不考虑并发性能：serializable   保证信息不丢失前提下，保证最大并发度：repetable read

要么全部执行，要么全部不执行：将该操作作为一个完整的业务

在checkpoint和crash之间已经commit的事务redo(满足持久性)，未commit的事务undo

存款：隔离级别设置为READ UNCOMMITTED，未实现加锁控制，不能达到串行化调度。

      如果两个客户同时请求存款，其中一个会存款值丢失，造成数据不一致

转账:扣除A的余额和增加B的余额是一个完整的事务，一个事务只能有一个commit