【数据库】实验七~十三

实验七 数据基本查询

所有的查询全部用 Transact-SQL 语句实现。此部分查询包括投影、选择条件表达、数
据排序、使用临时表等。
对 EDUC 数据库实现以下查询：
（可根据数据表中的数据，对要求中的条件作出调整！）

1. 查询“计算机应用”专业的学生学号和姓名；
2. 查询选修了课程的学生学号；
3. 查询选修课程 0001 且成绩在 80－90 之间的学生学号和成绩，并将成绩乘以系数
   0.75 输出；
4. 查询“计算机应用”和“数学”专业的姓“张”的学生的信息。
5. 查询“0001”课程的成绩高于张三的学生学号、姓名和成绩；
6. 查询没有选修“0002”课程的学生姓名。
7. 查询每一门课的间接先修课。
   

1)查询“计算机应用”专业的学生学号和姓名；

select Sno,Sname from student,class
where student.ClsNO = class.ClsNo and Specialty = '计算机应用';

2 查询选修了课程的学生学号；

select distinct Sno from sc;

3 查询选修课程 0001 且成绩在 80－90 之间的学生学号和成绩，并将成绩乘以系数0.75 输出；

select Sno,score*0.75
from sc
where Cno = '0001' and score between 80 and 90;

4 查询“计算机应用”和“数学”专业的姓“张”的学生的信息。

select student.*,class.Specialty
from student,class
where student.ClsNO = class.ClsNO
and Specialty in ('计算机应用','数学')
and Sname like '张%'

5 查询“0001”课程的成绩高于张三的学生学号、姓名和成绩；

select student.Sno,Sname,Score from sc,student 
where sc.Sno = student.Sno and sc.Cno = '0001' 
and Score > (select Score from sc,student where sc.Sno = student.sno and Sname = '张三' and Cno = '0001');

6查询没有选修“0002”课程的学生姓名。

select Sname from student
where Sno not in (select Sno from sc where Cno = '0002');

7)查询每一门课的间接先修课。

select c1.Cno,c2.Cpno from course c1,course c2 where c1.Cpno = c2.Cno;

实验八 使用聚集函数的 SELECT 语句

在数据库 EDUC 中用 SQL 语句实现如下查询：

1. 查询学生的总人数。
2. 查询选修了课程的学生人数。
3. 查询课程的课程号和选修该课程的人数。
4. 查询选修课程超过 2 门课的学生学号和姓名。
5. 查询没有选修“0001”号课程的学生姓名。
6. 查询选修了全部课程的学生姓名。
7. 查询至少选修了学号为“20170102”的学生选修的全部课程的学生学号。
8. 查询“王军”和“李杰”都选修的课程编号。

1查询学生的总人数。

select count(Sno) from student;

2 查询选修了课程的学生人数。

select count(distinct Sno) from sc;

3 查询课程的课程号和选修该课程的人数。

select Cno,count(Sno)人数 from sc
group by Cno;

4 查询选修课程超过 2 门课的学生学号和姓名。

select Sno,Sname from student
where  Sno in 
(select Sno from sc
group by Sno having count(Cno) > 2);

5查询没有选修“0001”号课程的学生姓名。

select Sname from student where Sno not in (select Sno from sc where Cno = '0001')

最后三题，挺难的

6查询选修了全部课程的学生姓名。

insert into sc
values('20190009','0001',56),
('20190009','0002',78),
('20190009','0003',86),
('20190009','0004',95),
('20190009','0007',64),
('20190009','0009',83),
('20190009','0010',72);

select Sname from student
where not exists
(select * from course where not exists
(select * from sc where sc.Cno = course.cno and student.sno = sc.Sno));

7查询至少选修了学号为“20170102”的学生选修的全部课程的学生学号。

select distinct Sno from sc q
where not exists
(select * from sc p where p.Sno = '20170102'and not exists
(select * from sc y where p.Cno = y.cno and y.Sno = q.sno));

8 查询“王军”和“李杰”都选修的课程编号。

select cno from student,sc
where student.Sno = sc.Sno and Sname = '王军'
intersect
select cno from student,sc

实验九 视图的定义与使用

1.定义视图 在 EDUC 数据库中，已 Student、Course 和 SC 表为基础完成一下视图定义:

1. 定义视图 V_SC_G：该视图包含 Student、 Course 和 SC 表中学生的学号、姓 名、课程号、课程名和成绩；
   

create view V_SC_G (Sno,Sname,Cno,Cname,Score)
as
select student.Sno,Sname,course.Cno ,course.Cname,Score 
from student,course,sc
where student.sno = sc.sno and course.Cno = sc.Cno;

2. 定义一个反映学生出生年份的视图 V_YEAR，该视图要求使用系统函数来获取 当前日期及转换；
   

create view V_YEAR(Sno, Sname,Birth) as 
select Sno,Sname, year(getdate())-Sage from student

3. 定义视图 V_AVG_S_G：该视图将反映学生选修课程的门数及平均成绩；
   

create view V_AVG_S_G(Sno,Count,avg_Score)
as 
select Sno,count(Cno),avg(Score)
from sc group by Sno;

4. 定义视图 V_AVG_C_G：该视图将统计各门课程的选修人数及平均成绩。
   

create view V_AVG_C_G(Cno,Count,avg_Score)
as
select Cno,count(Sno),avg(Score)
from sc group by Cno;

2.使用视图
1） 查询以上所建的视图结果。

select * from V_SC_G;
select * from V_YEAR;
select * from V_AVG_S_G;
select * from V_AVG_C_G;

2） 查询平均成绩为 90 分及以上的学生学号、姓名和成绩；

select student.Sno,Sname,avg_Score from student,V_AVG_S_G
where student.Sno = V_AVG_S_G.Sno and avg_Score > 90

3） 查询科目成绩大于平均成绩的学生学号、课程号、成绩和平均成绩；

select V_SC_G.Sno,V_SC_G.Cno,Score ,avg_Score from V_SC_G,V_AVG_C_G
where V_SC_G.Cno = V_AVG_C_G.Cno and Score > avg_Score;

4） 查询 1995 年出生的学生学号和姓名。

select Sno,Sname from V_YEAR
where Birth = 1995;

实验十 触发器的创建和使用

1. 在班级表 class 中增加班级人数（c_total）字段。
   
   

2. 为学生表(student)创建 INSERT 触发器 t_inst_stu：新增一名学生时，若其班级编
   号非空，则将班级表（class）中相应班级的人数（c_total）自动加 1。
   
   

3. 为学生表(student)创建 DELETE 触发器 t_dele_stu：删除一名学生时，若其班级编
   号非空，则将班级表（class）中相应班级的人数（c_total）自动减 1。
   

4. 为学生表(student)创建 UPDATE 触发器 t_update_stu: 当某学生所在班号发生变化
   时（即调到另一班级后），将其原先所在班级的人数（c_total）减 1，将新调入的班级班级
   的人数（c_total）加 1。
   

alter table class add c_total int default 0;
go

update class set c_total = (select count(Sno) from student 
where student.ClsNO = class.ClsNO);
go

create trigger t_inst_stu
on student after insert --当对学生表执行插入元组时，触发触发器
as 
begin
	declare @clsNo char(6);--声明一个局部变量
	select @clsNo = ClsNo from inserted; --查询结果只有一个,不需要用游标
	if(@clsNo is not NULL)		--判断是否为空，不要用C++判断方法	
	begin
		update class set c_total += 1
		where ClsNO = @clsNo;
	end
end
go

create trigger t_dele_stu
on student after delete	--当对学生表执行删除元组时，触发触发器
as 
begin
	declare @clsNo char(6);--声明一个局部变量
	select @clsNo = ClsNo from deleted;--查询结果只有一个不需要用游标
	if(@clsNo is not NULL)
		begin
			update class set c_total -= 1
			where ClsNO = @clsNo;
		end
end
go

create trigger t_update_stu
on student after update 
as 
begin
	declare @clsNo char(6);

	select @clsNo = ClsNo from deleted;
	if(@clsNo is not NULL)
	begin
		update class set c_total -= 1
		where ClsNo = @clsNo;
	end

	select @clsNo = ClsNo from inserted;
	if(@clsNo is not NULL)
	begin
		update class set c_total += 1
		where ClsNo = @clsNo;
	end

	

end
go

--drop trigger t_inst_stu;删除触发器
insert into student
values('20190020','洛璃','女','GL01','大主宰#','20','1.72','GL');
go

insert into student
values('20190021','黄烟尘','女','CS01','万古神帝#','20','1.72','CS');
go

update student set ClsNo = 'CS02' where Sno = '20190020';

update student set ClsNo = 'MT04' where Sno = '20190021';

delete from student where Sno = '20190020'; 

delete from student where Sno = '20190021';

实验十一 存储过程的创建与使用

1、创建一个不带参数的存储过程 p_stu_info1，实现对满足要求的学生基本信息的查询。
要求：所有年龄<21 岁的男同学

2、创建一个带有参数的存储过程 p_stu_info2，实现对满足要求的学生基本信息的查
询。
要求：输入参数为学号，与指定学号的学生同龄的所有同学。

3、创建一个存储过程 p_stu_info3,根据输入的学号，查询某学生的基本信息。
要求：输入参数为学号。

4、创建一个存储过程 p_stu_grade,根据输入的学号，返回其选课及其成绩。
要求：输入参数为学号。

create procedure p_stu_infol
as
select * from student where Sage < 21 and Sex = '男';
go

create procedure p_stu_info2(@sno char(8))
as
select s2.* from student s1,student s2
where s1.Sno = @sno and s2.Sage = s1.Sage; 
go

create procedure p_stu_info3(@sno char(8))
as
select * from student where Sno = @sno;
go

create procedure p_stu_grade(@sno Char(8))
as
select * from sc where Sno = @sno;
go



exec p_stu_infol;

exec p_stu_info2 '20170107';--注意：定义过程时形参用括号，调用时不需要

exec p_stu_info3 '20190002';

exec p_stu_grade '20190005';

实验十二 用户管理操作

SQL Server 创建登录名和用户名【详细介绍】

针对 EDUC 数据库，使用自主存取机制设计一个具体的权限分配方案。
1、 创建登录帐号

2、 创建用户

3、 创建角色

4、 授权
（1） 给角色 EducQueryRole 分配查询权限
GRANT SELECT on TABLE student to EducQueryRole

（2） 给角色 EducQueryRole 分配对 course 表的查看、插入和修改权限

（3） 给角色 EducQueryRole 分配对 Class 表的完全控制权限

（4） 给用户分配权限

（5） 给用户分配某个角色权限

5、验证授权操作

6、回收角色或用户权限

use EDUC
go

create login YS with password = '1211',default_database = EDUC;
go


use EDUC
go

create user YS for login YS;
go

 use EDUC
 go

 create role EducQueryRole
 go

 grant select on student to EducQueryRole;
 go

 grant select,insert,update on course to EducQueryRole;
 go

 grant select,insert,update,delete on class to EducQueryRole
 go

 grant select on student to YS;
 go

 grant select,insert,update on course to YS;
 go

 grant select,insert,update,delete on class to YS
 go

 exec sp_addrolemember 'EducQueryRole','YS';
 go

 select * from student;
go

select * from sc;
go

insert into student
values('20190022','池瑶','女','CS01','万古神帝#','23','1.75','CS');
go

delete from student where Sno = '20190002';
go

revoke select on student from EducQueryRole;
go

revoke select,insert,update on course from EducQueryRole;
go

revoke select,insert,update,delete on class from EducQueryRole;
go

revoke select on student from YS;
go

revoke select,insert,update on course from YS;
go

revoke select,insert,update,delete on class from Ys

实验十三 数据库的备份与恢复

SQL Server 2008数据库备份与恢复

1.使用 SSMS 规划和执行备份操作。

建议先做第三小题，再做第二小题

2.使用 Transact-SQL 语句执行备份操作。

backup database EDUC to disk = 'E:\database\EDUC2.bak';

3.使用 SSMS 执行恢复操作。
1）打开 EDUC 数据库，删除其中一个表（如 SC 表），即当前的学生选课数据库 EDUC 中没有选课表。

2）恢复 EDUC
步骤如下：在对象资源管理器中扩展服务器，在数据库文件夹中选中备份的数据库文件夹（EDUC 数据库）。用鼠标右键单击，在弹出的菜单中选择“任务”项，在随之出现的级联菜单中选择“还原”-“数据库”项，则出现还原数据库对话框。在此例中按“确定”按钮即可完成恢复工作。

3）再打开学生选课数据库，看一看当前的学生选课数据库中有没有选课表。

4.使用 Transact-SQL 语句执行恢复操作。

restore database tt from disk = 'E:\database\EDUC2.bak'with
move 'EDUC' to 'E:\tt.mdf',
move 'EDUC_log' to 'E:\tt_log.ldf';

restore database EDUC from disk = 'E:\database\EDUC2.bak'
with replace

5.创建维护数据库的备份设备
第一小题的时候就已经创建备份设备了。