数据库实验报告

实验一 创建数据库及关系表

一、实验目的

1. 掌握SQL Server数据库管理系统的使用，能够中该环境中进行日常数据库操作；

2. 掌握在SQL Server中使用图形化工具创建数据库的方法； 3.掌握建立关系表的语句，掌握定义主码约束及外码约束的语句； 4.掌握修改表结构的语句。

二、实验要求

1．了解SQL Server数据库的组成，会使用图形化工具创建数据库。 2．编写建立表及主、外码约束的T-SQL语句，并执行这些语句，在数据库中建立符合要求的关系表。

3．编写修改表结构的语句。

三、实验内容和步骤

1．创建符合如下条件的数据库： 数据库的名字为：Students

数据文件的逻辑文件名为：Students_dat，存放在D:\\Test录下（若D:盘中无此子目录，可先建立此目录，然后再创建数据库。）； 文件的初始大小为：5MB；

增长方式为自动增长，每次增加1MB。

日志文件的逻辑文件名字为：Students_log，也存放在D:\\Test目录下； 日志文件的初始大小为：2MB；

日志文件的增长方式为自动增长，每次增加10%。

2．在已建立的Students数据库中，写出创建满足下述条件的四张表的SQL语句，并查看执行结果。

Student表结构 列名 Sno Sname Ssex Sage Sdept Spec 说明 学号 姓名 性别 年龄 所在系 专业 数据类型 普通编码定长字符串，长度为7 普通编码定长字符串，长度为10 普通编码定长字符串，长度为2 微整型（tinyint） 普通编码不定长字符串，长度为20 普通编码定长字符串，长度为10 Course表结构

列名 Cno Cname Credit Semester 说明 课程号 课程名 学时数 学分 数据类型 普通编码定长字符串，长度为10 普通编码不定长字符串，长度为20 整型 小整型 SC表结构 列名 Sno Cno Grade 说明 学号 课程号 成绩 数据类型 约束 约束 主码 非空 约束 主码 非空 普通编码定长字符串，长度为7 主码，引用Student的外码 普通编码定长字符串，长度为10 主码，引用Course的外码 小整型 Teacher表结构

列名 Tno 说明 教师号 数据类型 普通编码定长字符串，长度为8 约束 非空 Tname Dept Salary Birthday 教师名 所在部门 工资 出生日期 普通编码定长字符串，长度为10 普通编码不定长字符串，长度为20 定点小数，小数点前4位，小数点后2位 小日期时间型（smallldatetime） 非空 Create table student ( Sno char(7) primary key, Sname nchar(10) not null, Ssex nchar (2), Sage tinyint , Sdept nvarchar(20), Spec char(10) )

create table course( Cno char(10),

Cname nvarchar (20) not null, Credit int, Semester tinyint, Primary key(Cno) )

create table sc( Sno char(7) not null, Cno char(10) not null, Grade tinyint ,

primary key (Sno,Cno),

foreign key(Sno ) references Student(Sno), foreign key(Cno ) references Course(Cno), )

create table teacher( Tno char(8) not null, Tname char(10) not null, Dept nvarchar(20) , Salary numeric(6,2), Birthery smalldatetime )

执行结果：

2.写出实现如下操作的SQL语句，并查看执行结果：

（1）在SC表中添加一个新的修课类别列：列名为：XKLB，类型为char(4)。

alter table sc

add XKLB char (4)

（2）将Course表中的Credit列的类型改为：tinyint(微整型，取值范围在0～255)。

alter table course

alter column Credit tinyint

（3）删除Student表的spec列。

alter table student drop column spec

（4）为Teacher表添加主码约束，其主码列为：Tno。

alter table teacher

add constraint y_s primary key (Tno)

3.执行下列语句，能否成功？为什么？

INSERT INTO SC VALUES(‘S001’, ’C01’, 88,’必修’) 答：不成功，因为错误显示sc表的sno学号列有外键约束，它是引用的student学生表里面的学号，所以执行前面的语句时’s001’不存在于学生表中，所以执行不成功。若在student学生表中插入学号’s001’的学生信息，如：insert into student values('s001','王盼','女',19,'数学系') 则再执行该语句时成功。

四．实验小结：

通过这次实验，学习了SQL Server数据库管理系统的使用的方法，通过自己创建数据库和表，熟悉了上课中讲到的相关知识，在完成实验的同时对自己所编写的代码进行调试和查错，并且自己发现错误并改正， 对知识的理解和掌握都有很大的帮助。

实验二 数据操作语句

一、 实验目的

理解数据操作语句的功能。

二、实验要求

1．根据实验一建立的数据库和关系表，导入部分数据； 2．编写实现数据操作功能的SQL语句。

三、实验内容及步骤

1. 查询计算机系年龄最大的三个学生的姓名和年龄。

select top 3 sname,sage,sdept from student

where sdept='计算机系' order by sage desc

2. 统计每个学生的选课门数，并按选课门数的递增顺序显示结果。

select sno,count(*) from sc group by sno order by count(*)

3. 列出总成绩超过200分的学生，要求列出学号、总成绩。

select sno,sum(grade) 总成绩from sc group by sno

having sum(grade)>200

4. 查询成绩80分以上的学生的姓名、课程号和成绩，并将结果按成绩的降序排列。

select Sname ,cno,Grade from sc join student s on s.sno =sc.sno where grade >80 order by grade desc

5. 查询学生的选课情况，要求列出每位学生的选课情况（包括未选课的

学生），并列出学生的学号、姓名、课程号和考试成绩。

select student.Sno ,Sname,Cno,Grade from student left outer join sc on student.sno =sc.sno

6. 列出‚VB‛课程考试成绩前三名的学生的学号、姓名、所在系和考试成绩。

select top 3 s.sno,sname,sdept,grade from student s join sc on s.sno=sc.sno

join course c on c.cno=sc.cno where cname='VB' order by grade desc

7. 查询哪些课程没有人选，要求列出课程号和课程名。

select c.Cno,Cname from Course c left join sc on c.Cno=sc.Cno where sc.Cno is null

8．查询计算机系学生考试成绩高于全体学生的总平均成绩的学生的姓名、

考试的课程名和考试成绩。

select sname,cname,grade from student s

join sc on s.sno=sc.sno join course c on sc.cno=c.cno where grade>all

(select avg(grade) from sc) and sdept ='计算机系'

9．查询VB考试成绩最低的学生的姓名、所在系和VB成绩。

select top 1 sname,sdept,grade from student s join sc on s.sno=sc.sno

join course c on c.cno=sc.cno where cname ='VB' order by grade

10. 查询VB课程考试成绩高于VB平均成绩的学生 的姓名。

select sname from student s

join sc on s.sno=sc.sno join course c on sc.cno=c.cno where cname='VB' and grade>all

(select avg(grade) from sc join course c on sc.cno=c.cno where cname='VB' )

11. 查询平均成绩大于80分的课程的名字和学分。

select cname,semester from course c

where cno in (

select sc.cno from sc group by sc.cno

having avg(grade)>80)

12. 统计每门课程的选课人数，包括有人选的课程和没有人选的课程，列

出课程号，选课人数及选课情况，其中选课情况为：如果此门课程的选课人数超过100人，则显示‚人多‛；如果此门课程的选课人数在40～100，则显示‚一般‛；如果此门课程的选课人数在1～40，则显示‚人少‛；如果此门课程没有人选，则显示‚无人选‛。

select sc.cno,count(sc.cno)选课人数,

case

when count(*) >100 then '人多'

when count(*) between 40 and 100 then '一般' when count(*) between 1 and 40 then '人少'

when count(*) =0 then '无人选' end as 选课情况 from sc

group by sc.cno

13．查询至少选修了‚9512102‛号学生选修的全部课程的学生，列出学

号和所选的课程号。

select sno,cno from sc sc1

where not exists (

select * from course c join sc on c.cno=sc.cno where sno='9512102' and not exists ( select * from sc

where sc.cno=c.cno and sc1.sno=sc.sno))

14．查询至少选修了第2学期开设的全部课程的学生的姓名和所在系。

select sname,sdept from student s where not exists ( select * from course c where c.semester = 2 and not exists ( select * from sc

where sc.cno=c.cno and sc.sno=s.sno ))

15．修改‚VB‛课程的考试成绩，如果是计算机系的学生，则增加4分；

如果是信息系的学生则增加8分，其他系的学生增加10分。

update sc set grade =grade +

case sdept

when '计算机系' then 4 when '信息系' then 8 else 10 end

from student s join sc on s.sno=sc.sno join course c on c.cno=sc.cno

16. 删除修课成绩小于50分的学生的修课记录

delete from sc

where grade <50

17. 删除信息系修课成绩小于50分的学生的修课纪录，分别用子查询和多表连接形式实现。

--子查询

delete from sc where sno in(

select sno from student where sdept ='信息系')

and grade <50

--多表连接

delete from sc

from sc join student s on sc.sno=s.sno where sdept='信息系'and grade <50

18. 将所有选修了‚c01‛课程的学生的成绩加10分。 update sc set grade =grade+10

from sc

where cno ='c01'

19. 将计算机系所有选修了‘计算机文化学’课程的学生的成绩加10分，

分别用子查询和多表连接形式实现。 --多表连接

update sc set grade =grade+10

from sc join student s on sc.sno=sc.sno join course c on c.cno =sc.cno where cname='计算机文化学' and sdept ='计算机系' -- 子查询

update sc set grade =grade+10 where sno in(

select sno from student where sdept ='计算机系' and cno in(

select cno from course

where cname='计算机文化学' ) )

20．删除‚VB‛考试成绩最低的学生的VB修课记录。 Delete from sc

where cno=(select cno from course where cname='VB') And

grade =(select min(grade) from sc where cno=

(select cno from course where cname='VB') )

21．修改高等数学的考试成绩，修改规则如下：如果是计算机系学生，则

加10分，如果是信息系学生则加5分，如果是数学系学生则分数不变。

update sc set grade =grade +

case sdept

when '计算机系' then 10 when '信息系' then 5 else 0 end

from student s join sc on s.sno=sc.sno join course c on c.cno=sc.cno where cname ='高等数学'

四．实验小结

这次上机实验，主要对数据操作语句的基本运用和理解，

在实验中从简单到复杂的问题中，一点点的掌握了SQL语言的功能，在实验中通过自己对语句的检验分析，还有老师的指导，基本掌握了数据库语言的运用，课后会多多巩固。

实验三 数据完整性约束

一、 实验目的

理解数据完整性约束的功能。

二、实验要求

1．掌握建立声明性数据完整性约束的SQL语句； 2．掌握建立过程完整性约束的SQL语句。

三、实验内容

1．在实验一建立的Students数据库中，编写建立满足完整性要求的定义表的SQL语句，执行并观察执行结果。（说明：表名、列名均用给出的中文）

（1）图书表，结构如下：

书号： 统一字符编码定长类型，长度为6，主码， 书名： 统一字符编码可变长类型，长度为30，非空， 第一作者：普通编码定长字符类型，长度为10，非空， 出版日期： 小日期时间型，小于等于当前系统日期， 印刷数量： 小整型，取值范围：1000～5000，默认为4000， 价格：定点小数，小数部分一位，整数部分3位。

（2）书店表，结构如下：

书店编号： 统一字符编码定长类型，长度为6，主码， 店名：统一字符编码可变长类型，长度为30，非空，

电话： 普通编码定长字符类型，12位长，取值形式：010-8位数字 地址： 普通编码可变长字符类型，40位长。前两个字符必须是：北京。

（3）图书销售表（XSB），结构如下：

书号： 统一字符编码定长类型，长度为6，非空， 书店编号： 统一字符编码定长类型，长度为6，非空， 销售日期： 小日期时间型，非空，默认值为系统当前日期， 销售数量： 微整型，取值大于0， 主码（书号，书店编号，销售日期）； 书号为引用“图书表”的外码； 书店编号为引用“书店表”的外码。

create table 图书表( 书号 nchar(6) PRIMARY KEY, 书名nvarchar(30) not null, 第一作者 char(10) not null,

出版日期 smalldatetime check(出版日期<=getdate()), 印刷数量 smallint default 4000, 价格 numeric (4,1),

check ( 印刷数量 between '1000' and '5000')

create table 书店表(

书店编号 nchar(6) primary key, 店名 nvarchar(30) not null, 电话 char(12) check( 电话like

'010-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), 地址 nvarchar(40) check ( 地址like '北京%') )

create table 图书销售表(

书号 nchar(6) not null, 书店编号 nchar(6) not null,

销售日期 smalldatetime default getdate() not null, 销售数量 tinyint check (销售数量>0), primary key(书号,书店编号,销售日期),

foreign key(书号) references 图书表(书号), foreign key(书店编号) references 书店表(书店编号) )

2.执行下列两条数据插入语句，是否都能执行成功？为什么？

INSERT INTO 图书表

VALUES(‘T0001’, ‘数据库系统基础’, ‘张三’, ‘2010-2-1’, 3000,28.0)

成功。

INSERT INTO 图书表

VALUES(‘T0002’, ‘计算机网络’, ‘张三’, ‘2010-1-1’, 500,36.0)

不成功，因为印刷数量是500，不满足规定的1000到5000。

2.执行下列两条数据插入语句，是否执行成功？为什么？

INSERT INTO 书店表

VALUES(‘S001’,’新华书店’,’12345678’,’北京市海淀区’)

不成功，电话不满足格式。

修改后的电话：

3.执行下列两条数据插入语句，是否执行成功？为什么？

INSERT INTO 图书销售表(书号，书店编号，销售数量) VALUES(‘T0001’,’S001’,20)

不成功，因为书号为引用‚图书表‛的外码。

4.编写实现如下功能的触发器：

（1）考试成绩必须在0~100分之间。

create trigger tri_a

on sc after insert ,update as

if exists(select * from sc

where grade not between 0 and 100) rollback

（2）不能将不及格的考试成绩改为及格。

create trigger tri_b on sc after update as

if exists(select *

from inserted join deleted on inserted.sno=deleted.sno and inserted.cno=deleted.cno

where inserted.grade>=60 and deleted.grade<=60)

rollback

（3）不能删除选修课程考试成绩不及格学生的该门课程的选课及

考试记录。

create trigger tri_c

on sc after delete

as

if exists(select * from deleted where grade<60) rollback

（4） 每个学生总的选课门数不能超过20门。

create trigger tri_d

on sc after insert,update

as

if exists(select count(cno)

from sc group by sno

having count(cno)>20) rollback

（5）每个学期开设的课程总学分必须在20~25之间。

create trigger tri_e

on course after insert,update,delete

as

if exists(select sum(credit)

from course group by semester

having sum(credit) not between 20 and 25 )

rollback

五．实验小结

通过这次上机实验，把上课中所讲的数据完整性约束的相关知识得到了复习，通过上机更直观的理解了完整性约束的功能，通过添加删除约束的操作更好的掌握了有关知识，收获很大。

实验四 安全管理

一、实验目的

1. 掌握安全管理的过程。

2. 掌握在SQL Server环境中实现安全管理的操作。

二、实验要求

1. 能够在SQL Server环境中建立SQL Server身份验证的登录账户。

2. 能够建立数据库用户。 3. 编写给用户授权的SQL语句。 4. 验证安全管理的效果。

三、实验内容及步骤

1．建立SQL Server认证的登录帐户：u1、u2、u3，并将u1、u2、u3映

射为实验一建立的Students数据库的用户。

2．用u1建立一个新的连接，并在students数据库中执行下述语句，是

否能成功？为什么？SELECT * FROM Course

不成功，因为u1没有被授予对象'Course'的查询权限。

3．用系统管理员登录建立一个连接，并授予u1具有对course和SC的查询权限，授予u2具有对course的插入权限。 GRANT SELECT ON COURSE TO u1 GRANT SELECT ON SC TO u1 GRANT INSERT ON COURSE TO u2

4．用u1建立一个新的连接，并在students数据库中执行下述语句，能否成功？为什么？

INSERT INTO course VALUES('C011'，'数据仓库技术'，2，7) 能成功，已经授予u2对course表的插入权限

再执行下述语句，能否成功？为什么？ SELECT * FROM course

不能成功，u2没有对course的查询权限

5．在u1建立的连接中，再次执行语句： SELECT * FROM course

这次能否成功？

能成功，因为授予了u1对course表的查询权限 但如果让u1执行下列语句：

INSERT INTO course VALUES('C012'，'软件工程'，4，6)

能否成功？为什么？

不能成功，因为没有授予u1对course表的插入权限

6．用u3建立一个新的连接，并在Students数据库中执行如下语句，能

否成功？

CREATE TABLE NEW_TABLE( C1 int PRIMARY KEY, C2 char(4)) 不能，没有授予u3建表权限。

7．在系统管理员建立的连接中，授予u3在students数据库中具有建表权限。

GRANT CREATE TABLE TO u3 GO

CREATE SCHEMA u3 AUTHORIZATION u3 GO

ALTER USER u3 WITH DEFAULT_SCHEMA = u3

8．在u3建立的连接中，在Students数据库中执行第6题的建表语句，这次是否成功？

能成功，因为授予了u3建表的权限。

如果将所使用的数据库换为pubs，再次执行建表语句，是否还能成功？为什么？

不能成功，因为没有授予u3对数据库pubs的建表权限。

四．实验小结

通过这次实验，基本掌握安全管理的过程，掌握了在SQL Server环

境中实现安全管理的操作，通过亲自动手实验，熟悉了安全管理的内容，更加印象深刻，对老师上课所讲的内容也通过自己的操作得到了很好的巩固。