数据库复习
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 ;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 table tableName drop column columnName;alter table tableName add column Sbirth char (20 );alter table tableName alter column columnname datatypedrop table user index role ......;
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 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; revoke all privileges on S from Rolename/Username; deny update on S to Rolename/Username;
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' create table s( ...... STAT Char (2 ) check (STAT in ('A' ,'B' ,'C' )), ...... )create table s ( ...... Sname char (20 ) constraint c1 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.%' ); Sno char(20) constraint C1 check (......); ...... 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 SCreferencing new /old row as variable for each row / statement when (......)insert into Table_name(......) values (......)drop trigger trigger_name;
规范化 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锁,代表这个事务希望读这个对象,并可能更新个别元组。