2017-2018_2数据库考点总结

1 绪论

1. 数据库的主要功能：数据定义功能，数据组织、存储和管理功能，数据操纵功能，数据库的事务管理和运行管理，数据库的建立和维护，其他功能

2. 数据库的三个阶段：人工管理、文件系统、数据库系统（目前已逐渐回归第二个阶段：文件系统——大数据）

3. 数据库系统的特点：数据结构化（数据库的主要特征之一），数据的共享性高、冗余度低且易扩充，数据独立性高，数据由数据库管理系统统一管理和控制（数据的安全性保护，数据的完整性检查，并发控制，数据库恢复）

4. 数据模型是数据库系统的核心和基础。两类模型：概念模型（独立于DBMS），逻辑模型和物理模型

5. 实体：客观存在并可相互区别的事务
    属性：实体所具有的某一特性
    码：唯一标识实体的属性集
    实体型：用实体名及其属性名集合来抽象和刻画同类实体
    实体集：同一类型实体的集合
    联系：实体内部的联系通常是指组成实体的各属性之间的联系，实体之间的联系通常是指不同实体集之间的联系

6. 数据模型的三部分：数据结构、数据操作和数据的完整性约束

7. 常用逻辑数据模型（4个）：层次模型、网状模型、关系模型、面向对象数据模型。前两个为非关系模型

8. 关系：一张表
    元组：表中一行
    属性：表中一列
    码：某个可以唯一确定一个元组的属性组
    域：一组具有相同数据类型的值的集合

    分量：元组中的一个属性值

9. 三级模式：外模式、模式、内模式

        模式：是数据库中全体数据的逻辑结构和特征的描述
        外模式：是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述

        内模式：是数据在数据库内部的组织方式

    二级映像：外模式/模式，模式/内模式
        外模式/模式映像：保证了数据与程序的逻辑独立性

        模式/内模式映像：保证了数据与程序的物理独立性

10. 数据库系统的组成：硬件平台及数据库、软件、人员（数据库，数据库管理系统，应用程序，数据库管理员）DBS>DBMS

2 关系数据库

1. 候选码：某一属性组的值能唯一的标识一个元组，而其子集不能
    主码：候选码中选定其中一个（组）
    主属性：候选码的诸属性
    非主（码）属性：不包含再任何候选码中的属性

2. 基本关系的6条性质（选择）：
    ① 列是同质的
    ② 不同的列可出自同一个域，称其中的每一列为一个属性，不同的属性要给予不同的属性名
    ③ 列的顺序无所谓
    ④ 任意两个元组的候选码不能取相同的值
    ⑤ 行的顺序无所谓
    ⑥ 分量必须取原子值（最基本） 

3. 关系的5种基本操作：选择，投影，并，差，笛卡儿积

4. 关系模型的三类完整性约束：实体完整性，参照完整性，用户定义的完整性

5. 关系代数：先连接（只考自然连接），再选择，最后投影

3 关系数据库标准语言SQL（使用的是SQL SERVER2012）

1. 定义数据库：

create database student

    删除数据库：

drop database student

use student --切换到student数据库

2. 定义模式：

create schema sevenZS

   删除模式：

drop schema sevenZS

3. 定义基本表：

create table sevenZS.Student(
    Sno char(9) primary key,
    Sname char(20),
    Ssex char(2),
    Sage smallint,
    Sdept char(20)
);
create table sevenZS.Course(
    Cno char(4) primary key,
    Cname char(40) not null,
    Cpno char(4),
    Ccredit smallint,
    foreign key(Cpno) references Course(Cno)
);
Create table sevenZS.SC(
    Sno char(9),
    Cno char(4),
    Grade smallint,
    primary key(Sno,Cno),   --主码由两个主属性构成，必须用表级完整性进行定义
    foreign key(Cno) references Course(Cno)  --外码用表级完整性定义
);

    修改基本表：

alter table sevenZS.Student add S_entrance date;  --增加一列
alter table sevenZS.Student drop column S_entrance;  --删除一列
alter table sevenZS.Student alter column Sage int; --修改一列的数据类型
alter table sevenZS.Student add unique(Sname);  --增加约束条件
alter table sevenZS.SC add foreign key(Sno) references sevenZS.Student(Sno);  --添加外码

    删除基本表：

drop table sevenZS.Student 

Note：SQL SERVER2012不支持对删除的修饰，因此cascade（级联）和restrict（限制）会报错

4. 建立索引：

create unique index Stusno on sevenZS.Student(Sno asc); --唯一索引，升序
create cluster index Coucno on Course(Cno desc);--聚簇索引，降序；由于默认有聚簇索引，所以实验中要删掉再新建
create unique index SCno on SC(Sno asc,Cno desc);--先按照Sno升序，再按照Cno降序

    修改索引：

alter index SCno rename to SCSno --改名字

    删除索引：

drop index sevenZS.Student.Stusno --注意！要写完整！

5. select...from...where...

select Sno,Sname
    from sevenZS.Student
    where Sdept='CS'; 
  --where Sage<20;
  --where Sage between 20 and 23
  --where Sdept (not) in('CS','MA','IS');
  --where Sname like '刘%';    --'欧阳_’  ‘_阳%’
  --where Cname like'db\_design' escape'\' --escape;  --后面的表示这个是一个符号，不是通配符
  --where Grade (not) is null;
  --where Sdept='CS' and Sage<20;
  --where Sdept='CS' or Sdept='MA' or Sdept='IS';

6. order by...

select Sno,Grade
from sevenZS.SC
where Cno='3'
order by grade desc;  --不同DBMS不同，SQL SERVER空值最小

7. 聚集函数

select count(*)
from sevenZS.Student;

select count(distinct Sno)  --无重复
from sevenZS.SC;

8. group by...

select Cno,count(Sno)
from sevenZS.SC
group by Cno;
having count(*)>3;  --having作用于组，where作用于基本表或视图

9. 自然连接

select Student.*,SC.*
from sevenZS.SC
where Student.Sno=SC.Sno;

10. 嵌套查询：形如where Sno = （select Sno from ......）一样的东西。

11. any和all

select Sname,Sage
from sevenZS.Student
where Sage<any(select Sage --任意一个，存在即可，小于最大
    from sevenZS.Student
    where Sdept='CS') 
    and Sdept<>'CS';

select Sname,Sage
from sevenZS.Student
where Sage<all(select Sage  --所有的，小于最小
    from sevenZS.Student
    where Sdept='CS') 
    and Sdept<>'CS';

12. exists

select Sname
from sevenZS.Student
where exists(
    select *
    from sevenZS.SC
    where Sno=Student.Sno and Cno='1');

13. 集合查询：同时完成，并（union），交（intersect），差（except）

14. 派生表

select Sno,Cno
from sevenZS.SC,(select Sno,avd(Grade)
    from sevenZS.SC
    group by Sno) as Avg_sc(acg_sno,avg_grade) --临时建一个表并给表和属性起名字，查询结束就删除，as可以省略
where SC.Sno=Avg_sc.avg_sno and SC.Grade>=Avg_sv.avg_grade;

15. 插入数据

insert into student(sno,sname,sdept,sage,ssex)  --如果插入项和属性一致，可以省略（）的内容
values('201215128','陈冬','IS','18','男') --顺序可以不一致

    插入数据到子查询

insert into deptage
select sdept,avg(sage)
from student
group by sdept

16. 修改数据

update student
set sage=19
where sname='陈冬'

update student
set sage = sage+1

update student
set sage = null  --不能用is，因为需要用赋值，而不是判等
where sname='李勇'

update sc
set grade = 0
where sno in
(
    select sno
    from student
    where sdept='CS'
)

update sc
set grade =100
where 'CS'=
(
    select sdept
    from student
    where student.sno=sc.sno
)


update student
set sno='201315123' --主码可以修改
where sno='201215123'


delete
from student
where sno='201215128'

17. is (not) null

select *
from sevenZS.Student
where Sname is null or Ssex is null or Sage is null or Sdept is null;

18. 建立视图

create view is_student(sno,sname,sdept)
as
select sno,sname,sdept
from student
where sdept='IS' --行列子集视图
with check option --控制上一句是否作为更新表的条件

select *
from is_student

create view is_s1(sno,sname,grade)
as
select student.sno,sname,cno
from student,sc
where sdept='IS' and cno='2' and student.sno=sc.sno


create view is_s2
as
select *
from is_s1
where grade>90

    更新视图（不是所有视图都是可更新的）

update is_student
set Sname='大佬'
where Sno='201215122';

19. 视图的作用
    ① 视图能够简化用户的操作
    ② 视图使用户能以多种角度看待同一数据
    ③ 视图对重构数据库提供了一定程度的逻辑独立性
    ④ 视图能够对机密数据提供安全保护
    ⑤适当利用视图可以更清晰的表达查询

4 数据库安全性

1. 创建登陆：

create login S1 with passwd='123456'

    创建用户：

create user U1 for login S1

2. 授权：

grant select
on sevenZS.student
to public --授权给所有用户

    批量授权：

grant all privileges --所有权限
on sevenZS.student
to U1

    定向授权：

grant select,update(sno) --修改某一列的权限
on sevenZS.student
to U1
with grant option --允许用户将此权限再授予其他用户，是可选项

  回收授权：

revoke all
on sevenZS.student
from U1 --授权几次回收几次

3. 角色创建：

create role r1
create role r2

   给角色授权：

grant select,update,delete
on sevenZS.course
to r1

   将角色授权给角色/用户：

grant r1
to r2,U1 
with admin option --角色或用户还可以继续授权，是可选项

exec sp_addrolemember r1,U1 --将U1添加到r1角色，拥有r1角色的权限

    角色权限收回：

revoke r1   --回收U1的r1角色权限
from U1

revoke select  --回收r1角色中的选择权限
on table sevenZS.Student
from r1

5 数据库完整性

1. 参照完整性

    ① 当修改不符合参照完整性时，会采取如下措施：拒绝（no action）执行，级联（cascade）操作（删除和修改参照表中导致不一致的元组），设置为空值

create table sevenZS.SC(
    Sno char(9),
    Cno char(4),
    Grade smallint,
    primary key(Sno,Cno)
    foreign key(Sno) references sevenZS.Student(Sno)
        on delete cascade
        on update cascade
    foreign key(Cno) references sevenZS.Course(Cno)
        on delete no action
        on update cascade

    ② 元组上约束条件的定义（check）

create table sevenZS.Student(
    Sno char(9),
    Sname char(8) not null,
    Ssec char(2),
    Sage smallint,
    Sdept char(20),
    primary key(Sno),
    check(Ssex='女' or Sname not like 'Ms.%')
);

2. 完整性约束命名子句

create table sevenZS.Student(
    Sno numeric(6)
        constraint C1 check(Sno between 90000 and 99999,
    Sname char(20)
        constraint C2 not null, 
      --constraint C2 check(Sname is not null)等价
    Sage numeric(3)
        constraint C3 check(Sage<30),
    Ssex char(2)
        constraint C4 check(Ssex in('男','女'),
        constraint StudentKey primary key(Sno)
);

    修改表中的完整性限制：(Note：改变具体内容就是先删除再添加）

alter table sevenZS.student
    drop constraint C4;

6 关系数据理论

1. 函数依赖：X函数确定Y 或 Y函数依赖于X。
    完全函数依赖、部分函数依赖

2. 超码：U部分函数依赖于K，则K为超码

3. 范式（规范化必考 大题）
    1NF：不允许表中套表，不满足为UNF
    2NF：1NF+不存在非主属性部分依赖于主码

    3NF：2NF+不存在非主属性传递依赖于主码

4. Armstrong是有效的、完备的，包含：自反律，增广率，传递率

5. 求属性集X关于U上的函数依赖集F的闭包XF+

    在F中寻找尚未用过的，左边是X(i)子集的函数依赖F→w，并找出w中未出现过的属性集B，再加上无法由B推导出的

    例：X→Y，Y→Z，X未出现在右侧，则为候选码，若X能得到其他所有，则X为主码

7 数据库设计

1. 数据库设计的基本步骤（考）：
    ① 需求分析：数字字典，全系统中数据项，数据结构，数据流，数据存储的描述
    ② 概念结构设计：E-R图
    ③ 逻辑结构设计：某种数据模型
    ④ 物理结构设计：存储安排，存取方法选择，存取路径建立
    ⑤ 数据库实施

    ⑥ 数据库运行和维护

2. 需求分析：调查的重点是获得用户对数据库的：信息要求，处理要求，安全性与完整性要求

3. 数据字典：关于数据库中数据的描述，即元数据，而不是数据本身。

4. 概念结构设计（E-R图→关系模型 必考大题）

8 数据库编程

1. 游标（不一定考）

--1）声明游标
declare num_cursor cursor
for select sno from student
--2）打开游标
open num_cursor
--3)读取游标中的数据
declare @sno char(9),@num int
set @num=0
fetch next from num_cursor into @sno
while @@FETCH_STATUS=0
begin
    if not exists(
        select *
	from sc
	where sno=@sno
    )
    set @num=@num+1
    fetch next from num_cursor into @sno
end
print '未选课的人数为：'+convert(char(2),@num)
--4）关闭游标
close num_sursor
--5）释放游标
deallocate num_sursor

declare @score int,@a int,@b int,@c int,@d int,@e int
select @a=0,@b=0,@c=0,@D=0,@e=0
--1)声明游标
declare level_cursor cursor
for
select grade
from sc
--2)打开游标
open level_cursor
--3）读取游标中的数据
fetch next from level_cursor into @score
while @@FETCH_STATUS=0
begin
	if @score is null
		set @e=@e+1
	else if @score<60
		set @e=@e+1
	else if @score<70
		set @d=@d+1
	else if @score<80
		set @c=@c+1
	else if @score<90
		set @b=@b+1
	else
		set @a=@a+1
	fetch next from level_cursor into @score
end
select @a 优秀,@b 良好,@c 中等,@d 及格,@e 不及格
--4）关闭游标
close level_cursor
--5）释放游标
deallocate level_cursor

------------------------------存储过程，自定义函数，触发器 大概三选二------------------------------

2. 存储过程（只考带参数的 大概率考）

create proc proc_insert_student
@sno char(9),
@sname char(10),
@ssex char(2)='男',
@sage smallint,
@sdept varchar(20)
as
begin
    insert into student(sno,sname,ssex,sage,sdept)
    values(@sno,@sname,@ssex,@sage,@sdept)
end

exec proc_insert_student'201215133','大佬','女',18,'CS'
exec proc_insert_student
  @sno='201215134',@sname='巨佬',@sage=20,@sdept='CS'

create proc proc_avergrade
  @sno varchar(10),
  @savg int out --输出参数
as
begin
  select @savg=avg(grade)
  from sc
  where sno=@sno
end
exec proc_avergrade'201215121',@savg out--执行也要说明是输出型参数
print'平均成绩为'+convert(char(2),@savg)

drop proc proc_avergrade

再练习一次

--不带参数
create proc  proc1
as
begin
  select avg(grade)
  from sc
  group by sno
end
--带输入型参数
create proc proc2
@sno char(9)
as
begin
  select avg(grade)
  from sc
  where sno=@sno
end

exec proc2 '201215121'
--带输出型参数
create proc proc3
@sno char(9)
@savg int output
as
begin
  select @savg=avg(grade)
  from sc
  where sno=@sno
end

declare @savg int
exec proc3'201215121',@savg out

create proc proc_sum
@department char(8),
@p_num int output
as
begin
  select @p_sum=count(*)
  from student
  where department=@department
end

exec proc_sum'软件学院',@p_num out

drop proc proc1

3. 自定义函数（大概率考）

create function dataonly(@date1 datetime)
returns varchar(12)
as
begin
  return convert(varchar(12),@date1,101)
end

select dbo.dataonly(getdate()) --当前日期

create function whichgeneration(@birth datetime)
returns varchar(4)
as
begin
    if year(@birth)<1980
	return 'too old';
	else if year(@birth)<1990
	return '80后'
	else
	return '90后'
end

create function fun1(@sdept varchar(20))
returns table
as
return
  select sno,sname
  from student
  where sdept=@sdept

select *
from fun1()
drop function fun1

4. 触发器（大概率考）

create trigger tr_insert_sc
on sc
after insert 
as
begin
    declare @sno char(9)
    select @sno=sno
    from inserted
    if not exists(select * 
        from student
        where sno=@sno)
    delete
    from sc
    where sno=@sno
end

create trigger tr_sc_grade
on sc
after insert
as
begin
    declare @sno char(9),@score int
    select @sno=sno,@score=grade
    from inserted
    if @score<0 or @score>100
      begin
        raiserror('成绩必须在0-100之间',16,1)
        rollback transaction
      end
end

create trigger Lr_student_update
on student
after update
as
begin
  if update(sno)
    begin
        raiserror('学号不允许修改',16,10)
        rollback transaction
    end
end

create trigger tr_sc_delete
on sc
after delete
as begin
  insert into a1
  select * from deleted
end

10 数据库恢复技术

1. 事务：用户定义的一个数据库操作序列，是一个不可分割的工作单位

2. 事务的ACID特性：原子性（Atomicity）,一致性（Consistency），隔离性（Isolation），持续性（Durability）

3. 故障种类
    ① 事物内部故障——事务撤销（undo）：如运算溢出，并发事务死锁
    ② 系统故障——系统重新启动+重做（redo）：如特定类型的硬件错误（CPU故障），操作系统故障，DBMS代码错误，系统断电
    ③ 介质故障：如磁盘损坏，磁头碰撞，瞬时强磁场干扰

4. 登记日志文件的原则：① 登记的次序严格按并发事务执行的时间次序 ② 必须先写日志文件，后写数据库

11 并发控制

1. 三种数据不一致性：丢失修改，不可重复读，读“脏”数据

2. 排他锁（写锁），共享锁（读锁）

3. 可串行化调度（考）：多个事务的并发执行是正确的，当且仅当其结果与按某一次序串行地执行这些事务时地结果相同
    可串行性：并发事务正确调度地准则

4. 两段锁协议：① 第一阶段获得封锁，扩展阶段 ② 第二阶段释放封锁，收缩阶段