SQL server 学校课程

考试前学一学

Posted by Elli0t on 2020-06-29

实验

实验一

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create table sc(
sno char(9),
grade decimal(5,1) check(grade >=0 and grade <= 100),
primary key(sno,cno),
foreign key(sno) references student(sno)
);

--将表sc中的grade列的取值范围改为小于150的正数
alter table sc
drop constraint CK__sc__grade check(grade >0 and grade < 150);

--为Student表的“Sname”字段增加一个唯一性约束
alter table students add constraint uq_sname unique(sname);

--为SC表建立外键,依赖于Student表,约束名为fk_s_c
alter table sc add constraint fk_s_c foreign key(sno) references stuents(sno);

decimal(p ,s )

p(精度)
要存储的最大十进制数字总数。该数字包括小数点的左侧和右侧。精度必须是1到最大精度38之间的值。默认精度是18。

s(刻度)
存储在小数点右边的小数位数。从p减去此数字,以确定小数点左边的最大位数。

实验二 SQL查询操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
select * from course where ccredit>2 and cno<5;
select * from course where ccredit between 2 and 8;
--从course表中查询出课程号为1或4或7的课程的课程号、课程名称、以及学分
select cno,cname,ccredit from course where cno in('1','4','7');
--从course表中查询学分大于3的课程信息,并按课程号升序排列
select * from course where ccredit>3 order by cno;
--查询每个学生及其课程的平均分情况,显示学号和平均分
select sno,avg(grade) from sc group by sno;
--查询选修2号课程且成绩在80分以上的学生信息
select student.sno,sname,cno,grade from student,sc
where student.sno=sc.sno and cno='2' and grade>80;
--查询每个学生的学号、姓名、选修的课程及成绩。(要求显示student表中所有学生信息)
select student.sno,sname,cno,grade from student left outer join sc on student.sno=sc.sno;

select sno,cno,grade from sc where sno in
(select sno from student where sdept='CS');

select sno,sname from student where sno in (select sno from sc
where cno=(select cno from course where cname='操作系统') );

实验三 SQL数据定义与数据操纵

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
--在实验一建立的数据库STUDENTDB的三个表中各插入一条记录
Insert into students (sno,sname,ssex,sage,sdept)
values('201201005','ZhangSan','男',18,'计算机系');
--创建一个表,保存学生的学号、姓名和年龄,把students表中‘CS’系的全部同学插入到此表中
CREATE TABLE student_1 (sno char(10),sname char(16),sage smallint);
INSERT INTO student_1 select sno,sname,sage from students where sdept='计算机系';

--将学号为“200215121”的学生的年龄增加一岁
Update student set sage=sage+1 where sno='201201005';
--将CS系全体学生的1号课程成绩加5分
Update sc set grade=grade+5 where cno= '01' and sno in
(select sno from students where sdept='计算机系');
--将2号课程中低于该课程平均分的同学的分数加5分
UPDATE sc SET grade=grade+5 WHERE cno='04' AND grade<
(SELECT AVG(grade) FROM sc WHERE cno='04');
--删除3号课程还未登记分数的记录
DELETE FROM sc WHERE cno='05' AND grade is NULL;
--建立视图view_CS,内容为CS系全体同学
CREATE VIEW view_cs AS (SELECT * FROM students WHERE sdept='计算机系');
--建立视图view_gf,内容为1号课程80分以上同学选课信息
CREATE VIEW view_gf
AS
SELECT * FROM sc WHERE cno='01' and grade>80;

实验四 数据库的安全性管理

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
--创建两个登录帐号Test1和Test2,密码自定,默认登录数据库s_t
Exec sp_addlogin Test1,'123456',s_t;
Exec sp_addlogin Test2,'654321',s_t;
--建立与登录帐号Test1和Test2相应的数据库用户,分别为Test1和Test2
Exec sp_adduser Test1,Test1;
Exec sp_adduser Test2,Test2;

--Test1可以对表students和sc进行查询、插入、修改的操作;
--Test2可以对表sc进行查询,对sc的grade列进行修改操作;
Grant select,insert,update on students, sc to Test1;
Grant select on sc to Test2;
Grant update on sc(grade) to Test2;

--Test2可对students表查询但仅能查询CS系的学生信息
--分两步,第1步建立视图:
Create view view_CS
As
Select * from students where sdept=’CS’;
--第2步:
Grant select on view_CS to Test2;

--Test1可以创建表和视图
Grant create table,create view to Test1;

--创建角色role_delsc,设置该角色具有删除sc表的记录权限,将Test1用户添加到此角色
Exec Sp_addrole ‘role_delsc’;
Grant delete on sc to role_delsc;
Exec sp_addrolemember ‘role_delsc’,’Test1’;

实验五 数据库的备份与恢复

1、SQL Server的三种备份形式

SQL Server具有三种备份形式:完全备份、事务日志和差异备份。

⑴ 完全备份:将数据库完全复制到备份文件中。

⑵ 事务日志备份:备份发生在数据库上的事务。

⑶ 差异备份:备份最近一次完全备份以后数据库发生变化的数据。

2、数据库进行备份和恢复操作的方式

⑴ 静态的备份和恢复方式。该方式在进行数据备份或恢复操作时,SQL服务器不接受任何应用程序的访问请求,只执行备份或恢复操作。

⑵ 动态的备份和恢复方式。该方式在进行数据备份或恢复操作时,SQL服务器同时接受应用程序的访问请求。

事务日志备份 事务日志是一个单独的文件,它记录数据库的改变,备份的时候只需要复制自上次备份以来对数据库所做的改变,所以只需要很少的时间。为了使数据库具有鲁棒性,推荐每小时甚至更频繁的备份事务日志。

差异备份 也叫增量备份。它是只备份数据库一部分的另一种方法,它不使用事务日志,相反,它使用整个数据库的一种新映象。它比最初的完全备份小,因为它只包含自上次完全备份以来所改变的数据库。

备份操作
1
2
3
4
5
6
7
8
9
10
11
12
--对数据库Studinfo做完整备份,生成:stud_full.bak
backup database Studinfo to disk = 'D:/stud_full.bak'
--对数据库Studinfo进行更新操作,例如:新建一个表tbtest1
--然后对数据库Studinfo做第一次差异备份,生成:stud_diff1.bak
backup database Studinfo to disk = 'D:/stud_diff1.bak' with differential
--再次对数据库Studinfo进行更新操作,例如:新建一个表tbtest2
--然后对数据库Studinfo做第二次差异备份,生成:stud_diff2.bak
backup database Studinfo to disk = 'D:/stud_diff2.bak' with differential;
--继续对数据库Studinfo进行更新操作,例如:新建一个表tbtest3
--然后对数据库Studinfo做事务日志备份,生成:stud_log.bak
BACKUP LOG Studinfo to disk = 'D:/stud_log.bak';
GO

实验六 存储过程与程序设计

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
41
42
43
44
45
46
47
--创建存储过程score90,执行该存储过程时,显示成绩表中分数超过90的同学情况
CREATE PROC score90
As
Select * from sc where grade>=90;

--创建带参数的存储过程disp_xs,执行该存储过程时可附加一个参数——系的名称,结果可显示student表中此系的学生信息
CREATE PROC disp_xs @tdept char(16)
As
Select * from students where sdept=@tdept;


--创建带参数的存储过程p_b2a,执行该存储过程时可附加两个参数--金额和项目号
--结果指定项目从b转账指定金额到a,建立事务的方式,当b的金额不够转时就不执行
--Step1:创建实验表counter
create table counter(
id char(8),
a int,
b int
);
Insert into counter values(‘c1’,1000,500);
Insert into counter values(‘c2’,2000,800);
--Step2:创建存储过程
create procedure p_b2a @amount int,@idname char(8)
as
begin transaction
update counter set b=b-@amount where id=@idname
if (select b from counter where id=@idname)<0
rollback
else
begin
update counter set a=a+@amount where id=@idname
commit
End

--在student表上创建触发器trig_welcome,当在student表中插入记录后,显示“welcome”
Create trigger trig_welcome
on students
after insert
as
print ‘welcome’;

--在sc表上创建触发器trig_delete,当删除了sc表中的记录时,显示sc表的所有记录
Create trigger trig_delete
on sc
after delete
as
select * from sc

分别在students表执行插入操作,在成绩表执行删除操作,观察结果

image-20200705172106188

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
--在SC表创建触发器,当更新了sc表的记录时,把更新前后的值记录到日志表中
--Step1:先创建日志表sc_log
create table sc_log(
sno_old char(8),
cno_old char(8),
grade_old smallint,
sno_new char(8),
cno_new char(8),
grade_new smallint,
optype char(8),
username char(10),
hostname char(20),
date_time datetime
);
--Step2:建立触发器
create trigger t_sc_update
on sc
after update
as
begin
declare @sno1 char(8),@cno1 char(8),@grade1 smallint
declare @sno2 char(8),@cno2 char(8),@grade2 smallint
select @sno1=sno,@cno1=cno,@grade1=grade from deleted
select @sno2=sno,@cno2=cno,@grade2=grade from inserted
insert into sc_log values(@sno1,@cno1,@grade1,@sno2,@cno2,@grade2,'update', suser_name(),host_name(),current_timestamp)
end

知识点

一、绪论

\1. 数据库,是相互关联的数据的集合,它用综合的方法组织数据,具有较小的数据冗余,可供多个用户共享,具有较高的数据独立性,具有安全控制机制,能够保证数据的安全、可靠,允许并发地使用数据库,能有效、及时地处理数据,并能保证数据的一致性和完整性。

\2. 数据管理的三个阶段:人工管理,文件系统,数据库系统。

\3. 数据库系统包括:数据库,数据库管理系统,应用程序,各类人员;其核心是数据库管理系统。

\4. 两大类数据模型:概念模型和(组织)数据模型

(1)概念模型:实体-联系模型

实体:实体是具有公共性质的可相互区别的现实世界对象的集合。

属性:属性就是描述实体或者联系的性质或特征的数据项。

联系:实体内部的联系通常是指组成实体的各属性之间的联系,实体之间的联系通常是指不同实体之间的联系。联系分为三类:一对一联系、一对多联系、多对多联系。

(2)(组织)数据模型

常见的数据模型,三类:层次模型(用树型结构组织数据)、网状模型(用图型结构组织数据)、关系模型(用简单二维表结构组织数据)。

层次模型无法直接表示多对多联系。

5.数据库系统的三级模式结构

(1)数据库划分为三层结构:内模式、模式和外模式。

(2)数据库的二级映像功能:模式和内模式间的映象以及外模式和模式间的映象。

(3)同一个模式可以有任意多个外模式,外模式/模式映象,保证了数据与程序的逻辑独立性

(4)模式/内模式映象是唯一的,保证了数据与程序的物理独立性

二、关系数据库

1.关系模型中的基本术语

(1)关系

​ 关系就是二维表,它满足如下条件:

​ 关系表中的每一列都是不可再分的基本属性。

​ 表中各属性不能重名。

​ 表中的行、列次序并不重要,即交换列的前后顺序不影响其表达的语义。

(2)元组

​ 表中的每一行数据称作是一个元组,它相当于一个记录值。

(3)属性

​ 表中的每一列是一个属性值集,列可以命名,称为属性名。

(4)主码

​ 主码(Primary key)也译为主键或主关键字,是表中的属性或属性组,用于惟一地确定一个元组。

2.数据完整性,是指数据库中存储的数据是有意义的或正确的。

数据完整性约束主要包括三大类:

(1) 实体完整性,关系模型中使用主码作为记录的惟一标识。

(2)参照完整性,参照完整性是描述实体之间的联系的。参照完整性一般是指多个实体或表之间的关联关系。

(3)用户定义的完整性,用户定义的完整性就是针对某一具体应用领域定义的数据库约束条件。

3.关系运算

(1)集合运算

并、交、差、笛卡尔积

(2)关系运算

​ 选择、投影、连接、除(不考)

其中,关系运算中五种基本操作:并、差、笛卡儿积、选择、投影,其他运算可由其推导.

三、关系数据库标准语言SQL

image-20200705175320059

1、数据定义

CREATE TABLE <表名>

(<列名> <数据类型>[ <列级完整性约束条件> ]

[,<列名> <数据类型>[ <列级完整性约束条件>] ] …

[,<表级完整性约束条件> ] );

掌握:

用CREATE TABLE创建Student、Course、SC表及相关约束。

2、数据查询

语句格式

​ SELECT [ALL|DISTINCT] <目标列表达式>

​ [,<目标列表达式>] …

FROM <表名或视图名>[, <表名或视图名> ] …

[ WHERE <条件表达式> ]

[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]

[ ORDER BY <列名2> [ ASC|DESC ] ];

掌握:

(1)DISTINCT、BETWEEN … AND …、IN、LIKE (%、_)、IS NULL的含义和用法。

(2)ORDER BY的用法

(3)5个聚集函数的用法

(4)GROUP BY的用法

(5)连接查询,内连接和外连接的区别

(6)嵌套查询,掌握最基本的使用IN实现内外层不相关的嵌套查询。

3、数据操纵

掌握Insert、Update、Delete语句的用法。

4、视图

​ 掌握Create View的用法,掌握基于视图的查询。

四、 数据库安全性

1、自主存取控制和强制存取控制的区别

​ 自主存取控制方法:定义各个用户对不同数据对象的存取权限。当用户对数据库访问时首先检查用户的存取权限。防止不合法用户对数据库的存取。

强制存取控制方法:每一个数据对象被(强制地)标以一定的密级,每一个用户也被(强

制地)授予某一个级别的许可证。系统规定只有具有某一许可证级别的用户才能存取某一个密级的数据对象。

2、掌握Grant语句

GRANT语句的一般格式:

​ GRANT <权限>[,<权限>]…

​ [ON <对象类型> <对象名>]

​ TO <用户>[,<用户>]…

​ [WITH GRANT OPTION];

3、掌握SQL Server中访问数据库中数据的三个步骤。

(1)创建登录账号;

Create Login 或 exec sp_addlogin

(2)创建数据库用户,并关联到相应的登录账号;

Create User 或 exec sp_adduser

(2)为用户授权。

Grant

4、掌握创建角色,及角色的使用方法。

(1)创建角色;

​ Create role

(2)给角色授权

​ Grant

(3)把用户加入到角色

​ Exec sp_addrolemember

五、数据库完整性

1、数据库的完整性是指:数据的正确性和相容性

2、掌握完整性约束命名的方法,即:CONSTRAINT的使用。

3、触发器

​ (1)当对表进行insert,update,delete操作,可定义触发器.

(2)创建触发器的格式:

CREATE TRIGGER trigger_name

ON { table | view }

{ FOR | AFTER | INSTEAD OF }

{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }

AS

  sql_statement [ …n ]

触发器举例:

(1)在student表中插入记录后显示学生总数

create trigger t_insert_st

on student

for insert

as

begin

declare @totalnum int

set @totalnum=(select count(*) from student)

print ‘共有:’+str(@totalnum)+’名学生’

End

(2)在SC表创建触发器,当更新了sc表的记录时,把更新前后的值记录到日志表中。

(a)先创建日志表sc_log

create table sc_log(

sno_old char(8),

cno_old char(8),

grade_old smallint,

sno_new char(8),

cno_new char(8),

grade_new smallint,

optype char(8),

username varchar(50),

hostname varchar(50),

date_time datetime

)

(b)建立触发器

create trigger t_sc_update

on sc

after update

as

begin

declare @sno1 char(8),@cno1 char(8),@grade1 smallint

declare @sno2 char(8),@cno2 char(8),@grade2 smallint

select @sno1=sno,@cno1=cno,@grade1=grade from deleted

select @sno2=sno,@cno2=cno,@grade2=grade from inserted

insert into sc_log values(@sno1,@cno1,@grade1,@sno2,@cno2,@grade2,’update’, suser_name(),host_name(),current_timestamp)

end

六、关系规范化理论

七、数据库设计**

1、数据库设计分6个阶段

(1)需求分析 — 构造数据字典和数据流图

(2)概念结构设计 — 形成一个独立于具体DBMS的概念模型,最常用:E-R图

E-R图:实体->矩形框, 属性->椭圆形框, 联系->菱形框

例:

image-20200705175532613

关系模式: 货物(货号,单价,仓库号)

​ 仓库(仓库号,面积)

​ 存放(货号,仓库号,存量)

例:

image-20200705181801858

关系模式:职工(职工号,姓名,年龄,职称)

​ 职称(职称代码,职称名,工资,住房标准,附加福利)

(3)逻辑结构设计 — 将概念结构转换为某个DBMS所支持的数据模型。

将E-R图转换为关系模型:每个实体转换为一个关系;一个1:1联系可以转换为一个独立的关系模式,也可以与任意一端对应的关系模式合并;一个1:n联系可以转换为一个独立的关系模式,也可以与n端对应的关系模式合并;一个m:n联系必须转换为一个关系模式。

(4)物理结构设计 — 为逻辑数据模型选取一个最适合应用环境的物理结构(包括存储结构和存取方法)

(5)数据库实施 — 运用DBMS提供的数据库语言(如SQL)及宿主语言,根据逻辑设计和物理设计的结果,建立数据库、编制与调试应用程序、组织数据入库、进行试运行

(6)数据库运行和维护

2、需求分析和概念设计独立于任何数据库管理系统,逻辑设计和物理设计与选用的DBMS密切相关。

八、数据库编程

1、存储过程

SQL Server存储过程

简化格式:

创建存储过程:

CREATE PROC[EDURE] procedure_name
[{@parameterdata_type}

AS

sql_statement

执行存储过程

EXEC[UTE] procedure_name

[@parameter=]{value|@variable}

存储过程举例:

(1)创建存储过程,检索指定系的学生信息

create procedure display_stud @dept char(8)

as

select * from student where sdept=@dept

执行

execute display_stud ‘CS’

(2)创建带参数的存储过程p_b2a,执行该存储过程时可附加两个参数–金额和项目号,结果指定项目从b转账指定金额到a,建立事务的方式,当b的金额不够转时就不执行。

create procedure p_b2a @amount int,@idname char(8)

as

begin transaction

update counter set b=b-@amount where id=@idname

if (select b from counter where id=@idname)<0

rollback

else

begin

update counter set a=a+@amount where id=@idname

commit

End

九、查询处理与优化

十、数据库恢复技术

1、事务

一个数据库操作序列,一个不可分割的工作单位,恢复和并发控制的基本单位。

事务的ACID特性:

原子性(Atomicity)

表示的是事务的所有操作在数据库中要么都做,要么都不做。

一致性(Consistency)

事务在执行前如果数据库是一致性的那么执行后也是一致性的。

隔离性(Isolation)

事务在执行过程中不受其他事务的干扰

持续性(Durability )

一个事务成功完成后,它对数据库的改变必须是永久的,即使是系统出现故障时也如此

2、例:

​ begin tran

update counter set bb = bb - 100 where id = ‘c1’

waitfor delay ‘00:00:30.000’

update counter set aa = aa + 100 where id = ‘c1’

commit tran

运行结束前,停止SQL SERVER服务,然后重启服务查看aa和bb的值,aa和bb的值均没变。

3、数据库恢复的基础是利用转储的冗余数据。这些转储的冗余数据包括:数据库后备副本、日志文件。

4、SQL Server中的备份主要有:完整备份、差异备份和事务日志备份。

5、系统故障恢复的基本策略

(1)发生系统故障时,事务未提交

恢复策略:强行撤消(UNDO)所有未完成事务

(2)发生系统故障时,事务已提交,但缓冲区中的信息尚未完全写回到磁盘上。

恢复策略:重做(REDO)所有已提交的事务

6、具有检查点(checkpoint)的恢复技术

image-20200705181839174

十一、并发控制

1、并发控制机制的任务:对并发操作进行正确调度,保证事务的隔离性,保证数据库的一致性。

2、并发操作带来的数据不一致性:丢失修改(Lost Update),不可重复读(Non-repeatable Read),读“脏”数据(Dirty Read)。

3、丢失修改例:

image-20200705181853430

4、不可重复读例:

image-20200705181909448

5、读“脏”数据例:

image-20200705182118194

6、为解决并发操作带来的问题,商用的DBMS一般都采用封锁方法。

7、基本封锁类型

​ 排它锁(Exclusive Locks,简记为X锁)

​ 共享锁(Share Locks,简记为S锁)

8、死锁例

image-20200705182137701

9、DBMS在解决死锁的问题上普遍采用的是诊断并解除死锁的方法。