k@M0me’s Humble Abode      博  客   时 间 线   归  档 



数据库复习
Published on Thu Jul 01 2021 21:00:01 GMT+0000

SQL语句

1、创建与删除

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
#数据库
create database 数据库;
drop database 数据库;

#表
create table table1 (
学号 char(4) primary key,
姓名 char(20) not null
)

#索引
create unique index idx on S(Sno DESC);#降序排列

#视图
create view view_name as
select * from s where city = 'New York';
drop view view_name;

#角色
create role role_name;
grant all privileges on S to role_name with grant option;
drop role role_name;

#索引:
create [unique|cluster(聚集)]index Index_name on S(Sno);
drop index index_name;

2、查询与修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
#基本查询
select Sno from S;

#带别名
select Sno 学号 , Grade 成绩 , joinGroup 加入社团 from S;

#带条件
select Sno from S where Sage > 18;
select Sno from S where Sage between 18 and 20;
select Sno from S where name not in ('Yukinoshita','Yui');
select Sno from S where name like 'Yukinoshita%';
select Sno from S where joinGroup is not null;

#带排序
select Sno from S order by Grade DESC;
select top 10 Sno from S order by Grade DESC;#选择前10名的学生。

#聚集
select avg(Grade) from S;
select count(*) from S;
select count(distinct joinGroup) from S;#相同内容只计数一次
select Sno from S group by Sno having count(distinct chooseClass) > 3;

#连接
select S.* , SC.* from S,SC where S.Cno = SC.Cno;
select S.Sno , S.name , S.city , SPJ.qty from s , SPJ
where S.Sno = SPJ.Sno and SPJ.Jno = 'J001' and SPJ.qty >300;

#嵌套
select Sname from S where Sno in (select Sno from SC where Cno = '1001');

3、修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
#插入
insert into S values('114514','李田所','C','回家部','24');
select * into S from 下北泽高 where Sname like '李田所';#把信息复制到大学

#修改
update S set Grade = 'B' where Sno = 114514;

#删除
delete from S where Sno = 114514;

#批量
update S set Sage = Sage +1 ;

#带子查询
update SPJ set QTY = QTY - 50 where JNO in( select JNO from J
where City = '天津');

#alter
alter table tableName drop column columnName;#删除列
alter table tableName add column Sbirth char(20);#添加列
alter table tableName alter column columnname datatype# 改变数据类型

#drop
drop table user index role ......;#drop一般删除数据结构或对象(包括属性列) ,
#而delete则倾向于删除对象中的元组。

4、视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
#创建
create view S_BJ_VIEW as
select * from S where city = '北京' with check option;

#删除
drop view ViewName;

#查询
select Sno,Sname from S_BJ_VIEW where STAT > 'B';
select * from S_AVGQTY where QAVG >=300;

#视图消解法:
#定义的视图:
create view S_AVGQTY(SNO,PNO,QAVG)
as select SNO,PNO,AVG(QTY) from SPJ group by SNO,PNO;
#查询语句:select * from S_AVGQTY where QAVG >=300;
select * from S_AVGQTY where QAVG >=300;
#转换后:
select SNO,PNO,AVG(QTY) from SPJ group by SNO,PNO having AVG(QTY)>=300;

#更新数据:
insert into S_BJ_VIEW value('S7','北京114514电子厂','B','北京');

#删除:
delete from S_BJ_VIEW where SNAME = '北京114514电子厂';

5、安全性

1
2
3
4
5
6
7
8
9
10
11
12
#创建用户
create user userName for login loginName with default_schema = schName;
create login loginName with password = ..., default_database = DB_NAME;

#创建角色
create role RoleName;
grant RoleName to UserName with admin option;

#权限管理
grant all privileges on S to Rolename/Username; #with grant option:可以传递权限
revoke all privileges on S from Rolename/Username;
deny update on S to Rolename/Username;#拒绝这一权限,使其加入其他有update权限组后仍然不能执行该操作。

6、完整性:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
#主码定义:
create table s(
Sno char(4) primary key;
......
)

create table s(
......
primary key (Sno);
)
create table s (
......
primary key(Sno,Pno,Jno);
)

#参照完整性:
create table s(
......
foreign key (SNO) references s(SNO);
)

#其他完整性:
not null #非空
unique # 唯一
default 'defaultValue' #默认值

#check约束
create table s(
......
STAT Char(2) check(STAT in ('A','B','C')),
......
)

#constraint命令
create table s (
......
Sname char(20) constraint c1 not null # 此时c1这一完整性约束(constraint)的值为not null
)
alter table s drop constraint c1;
alter table s add constraint c2 check(......);

数据库概念复习

关系运算

1、选择

$\sigma(R)_F={t|t \in R\wedge F(t) = true}$

2、投影

$\Pi_A(R)={t[A]|t \in R}$

3、连接

等值连接:选取笛卡尔级中等值的那些元组

自然连接:在等值连接的基础上去掉重复的属性列

4、除运算

R÷S:

用于选出以下内容:

属性:在R不在S的属性

元组:删去的属性对得上在R中的值

比如:

R:

Sno Jno

a 1

b 2

c 3

S:

1

3

R÷S:

Sno

a

c

其他较为重要的运算:交并减、笛卡尔积

数据库安全

自主:

授予权限的类型:

基本表、视图 属性列
select insert update delete references all privileges select insert update references all privileges

一个简单的例子:

grant update(Sno),select on table SC to User1;

对属性列:权限后面加括号,表明该权限生效的属性列

可传递:with grant option;

收回权限:同grant,但有需要注意的地方:

最后加 cascade/restrict:是否级联操作或约束操作。

角色:

创建:create role rolename ;

授予权限同用户

将角色权限授予用户:grant Rolename to User;

解除权限:revoke Rolename from User;

撤销授权同用户

强制:

设定若干级别:

TS>=S>=C>=P

访问规则:

主体许可证等级大于等于客体,可以读。

主体许可证等级小于等于客体,可以写。

第二条解释:高许可证主体没有办法写出一个更低等级的客体。

视图:

create view as(select……);

grant select on view to ……;

完整性:

实体完整性:

1
2
3
4
5
6
7
8
9
10
create table ...(
......
Sname char(20) primary key;#在列级

......
primary key (Sname);#在表级

primary key (Sname,Sno);#在表级,多列

)

参照完整性:

1
2
3
4
5
create table S2(
......
foreign key (Sno) references S(Sno)#表级定义参照完整性
on delete cascade on update cascade,#级联删除、更新
)

约束语句:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create table R1(
Sno char(20) not null,#非空
...... unique,#列值唯一
......check( ......in(......集合))

check (Ssex = 'female'or Sname not like 'Ms.%');


#constraint
Sno char(20) constraint C1 check(......);#形成一个名为C1的约束条件

......
constraint C2 check(......)#在表级定义约束条件
)

alter table R1 drop constraint C1;#删除约束条件
alter table R1 create constraint c3 check(......);#创建约束条件,表级。

断言

在操作时,使断言不为真的操作拒绝执行。

创建断言:

1
2
3
4
create assertion assertion_name check (......);

create assertion A1 check(60>=select count(*) from SC where C = 'Cource1');
create assertion A2 check(60>=all(select count(*) from SC group by ...));

触发器:

触发器用于在某条件由于操作成立时触发其他操作。

1
2
3
4
5
6
7
create trigger trigger_name before/after update on SC
referencing new/old row as variable#触发器内设变量:原值和新值
for each row / statement#每行触发还是每条语句触发
when(......)#触发条件,比如new.grade>=old.grade
insert into Table_name(......) values (......)

drop trigger trigger_name;#删除(删除对象用drop)

规范化

1NF:不可分割

2NF:属性由一码确定

3NF:没有不完全函数依赖,没有传递函数依赖

BCNF:每个决定因素都有码

4NF:不存在非平凡、非函数依赖的多值依赖

规范化的实质:概念的单一化

数据库设计

E-R图

联系:菱形,两侧写是1-1、1-n还是n-1的依赖。

属性:圆形,属性不能有属性(不可再分)

实体:矩形

实体描述:

实体{实体的主码(下划线),属性1,属性2,属性3}

数据库的故障恢复、数据库的并发控制:

事务

事务:一个数据库操作序列,具有ACID特性,即原子性、一致性、隔离性、持续性(永久性)

数据库的日志文件

日志文件记录:

事务标识、操作类型、操作对象、操作前值、操作后值。

故障处理

事务故障:反向扫描日志文件,撤销已经完成的操作

系统故障:视事务完成情况选择是撤销还是重做,撤销:根据日志写更新前值;重做:根据日志写更新后值。

介质故障:从恢复介质重装数据,然后重做已经完成的事务。

并发控制

封锁

X锁(写锁):上X锁后,其他事务不能读写。

S锁(读锁):上S锁后,其他事务可以再上S锁,可以读,但是不能写。

封锁协议

一级封锁协议:事务在修改数据前必须加X锁,直至事务结束释放。

二级封锁协议:在一级的基础上,事务读要上S锁,读完释放。

三级封锁协议:在一级的基础上,事务读要上S锁,且事务结束时才释放(解决了不可重复读问题)。

活锁与死锁

活锁:事务由于等待其他事务结束无法完成(解决:先来先服务)。

死锁:事务间循环等待导致数个事务不能结束(解决:采取措施避免或每隔一段时间诊断死锁并清除)。

意向锁(Intention Lock)

IS锁:对一个对象加IS锁,意味着拟对其后裔结点加S锁。如果要对某个元组加S锁,必须先给关系加IS锁。

IX锁:类似IS锁

SIX锁:对这个对象加S锁,然后加IX锁,代表这个事务希望读这个对象,并可能更新个别元组。