数据库实验报告.docx

1、数据库实验报告 实验一创建数据库及关系表一、实验目的1.掌握SQL Server数据库管理系统的使用，能够中该环境中进行日常数据库操作；2.掌握在SQL Server中使用图形化工具创建数据库的方法；3.掌握建立关系表的语句，掌握定义主码约束及外码约束的语句；4.掌握修改表结构的语句。二、实验要求 1了解SQL Server数据库的组成，会使用图形化工具创建数据库。2编写建立表及主、外码约束的T-SQL语句，并执行这些语句，在数据库中建立符合要求的关系表。3编写修改表结构的语句。三、实验内容和步骤1创建符合如下条件的数据库：数据库的名字为：Students数据文件的逻辑文件名为：Student

2、s_dat，存放在D:Test录下（若D:盘中无此子目录，可先建立此目录，然后再创建数据库。）；文件的初始大小为：5MB； 增长方式为自动增长，每次增加1MB。日志文件的逻辑文件名字为：Students_log，也存放在D:Test目录下；日志文件的初始大小为：2MB；日志文件的增长方式为自动增长，每次增加10%。2在已建立的Students数据库中，写出创建满足下述条件的四张表的SQL语句，并查看执行结果。Student表结构列名说明数据类型约束Sno学号普通编码定长字符串，长度为7主码Sname姓名普通编码定长字符串，长度为10非空Ssex性别普通编码定长字符串，长度为2Sage年龄微整型

3、（tinyint）Sdept所在系普通编码不定长字符串，长度为20Spec专业普通编码定长字符串，长度为10Course表结构列名说明数据类型约束Cno课程号普通编码定长字符串，长度为10主码Cname课程名普通编码不定长字符串，长度为20非空Credit学时数整型Semester学分小整型SC表结构列名说明数据类型约束Sno学号普通编码定长字符串，长度为7主码，引用Student的外码Cno课程号普通编码定长字符串，长度为10主码，引用Course的外码Grade成绩小整型Teacher表结构列名说明数据类型约束Tno教师号普通编码定长字符串，长度为8非空Tname教师名普通编码定长字符串，

4、长度为10非空Dept所在部门普通编码不定长字符串，长度为20Salary工资定点小数，小数点前4位，小数点后2位Birthday出生日期小日期时间型（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

5、 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 nvarcha

6、r(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(微整型，取值范围在0255)。alter table course alter column Credit tinyint （3）删除Student表的spec列。 alter table student drop colu

7、mn 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,数学系)

8、则再执行该语句时成功。四实验小结：通过这次实验，学习了SQL Server数据库管理系统的使用的方法，通过自己创建数据库和表，熟悉了上课中讲到的相关知识，在完成实验的同时对自己所编写的代码进行调试和查错，并且自己发现错误并改正， 对知识的理解和掌握都有很大的帮助。实验二数据操作语句一、 实验目的理解数据操作语句的功能。 二、实验要求 1根据实验一建立的数据库和关系表，导入部分数据；2编写实现数据操作功能的SQL语句。三、实验内容及步骤1. 查询计算机系年龄最大的三个学生的姓名和年龄。 select top 3 sname,sage,sdept from student where sdept=

9、计算机系 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)2004. 查询成绩80分以上的学生的姓名、课程号和成绩，并将结果按成绩的降序排列。select Sname ,cno,Grade from sc join student s on =

10、where grade 80 order by grade desc5. 查询学生的选课情况，要求列出每位学生的选课情况（包括未选课的学生），并列出学生的学号、姓名、课程号和考试成绩。select ,Sname,Cno,Gradefrom student left outer join sc on =6. 列出“VB”课程考试成绩前三名的学生的学号、姓名、所在系和考试成绩。select top 3 ,sname,sdept,grade from student s join sc on = join course c on = where cname=VB order by grade des

11、c7. 查询哪些课程没有人选，要求列出课程号和课程名。select ,Cname from Course c left join sc on = where is null8查询计算机系学生考试成绩高于全体学生的总平均成绩的学生的姓名、考试的课程名和考试成绩。select sname,cname,grade from student s join sc on = join course c on = where gradeall (select avg(grade) from sc) and sdept =计算机系9查询VB考试成绩最低的学生的姓名、所在系和VB成绩。select top 1

12、sname,sdept,grade from student s join sc on = join course c on = where cname =VB order by grade10. 查询VB课程考试成绩高于VB平均成绩的学生 的姓名。select sname from student s join sc on = join course c on = where cname=VB and gradeall (select avg(grade) from sc join course c on = where cname=VB )11. 查询平均成绩大于80分的课程的名字和学分。

13、select cname,semester from course c where cno in ( select from sc group by having avg(grade)80) 12. 统计每门课程的选课人数，包括有人选的课程和没有人选的课程，列出课程号，选课人数及选课情况，其中选课情况为：如果此门课程的选课人数超过100人，则显示“人多”；如果此门课程的选课人数在40100，则显示“一般”；如果此门课程的选课人数在140，则显示“人少”；如果此门课程没有人选，则显示“无人选”。select ,count选课人数, case when count(*) 100 then 人多 w

14、hen count(*) between 40 and 100 then 一般 when count(*) between 1 and 40 then 人少 when count(*) =0 then 无人选 end as 选课情况 from sc group by 13查询至少选修了“9512102”号学生选修的全部课程的学生，列出学号和所选的课程号。select sno,cno from sc sc1 where not exists ( select * from course c join sc on = where sno=9512102 and not exists ( selec

15、t * from sc where = and =)14查询至少选修了第2学期开设的全部课程的学生的姓名和所在系。select sname,sdept from student s where not exists ( select * from course c where = 2 and not exists ( select * from sc where = and = )15修改“VB”课程的考试成绩，如果是计算机系的学生，则增加4分；如果是信息系的学生则增加8分，其他系的学生增加10分。update sc set grade =grade + case sdept when 计算机

16、系 then 4 when 信息系 then 8 else 10 end from student s join sc on = join course c on =16. 删除修课成绩小于50分的学生的修课记录delete from sc where grade 5017. 删除信息系修课成绩小于50分的学生的修课纪录，分别用子查询和多表连接形式实现。-子查询 delete from sc where sno in( select sno from student where sdept =信息系) and grade 50 -多表连接 delete from sc from sc join

17、 student s on = where sdept=信息系and grade 50 18. 将所有选修了“c01”课程的学生的成绩加10分。 update sc set grade =grade+10 from sc where cno =c0119. 将计算机系所有选修了计算机文化学课程的学生的成绩加10分，分别用子查询和多表连接形式实现。 -多表连接 update sc set grade =grade+10 from sc join student s on = join course c on = where cname=计算机文化学 and sdept =计算机系 - 子查询 u

18、pdate 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

19、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 = join course c on = where cname =高等数学四实验小结 这次上机实验，主要对数据操作语句的基本运用和理解，在实验中从简单到复杂的问题中，一点点的掌握了SQL语言的功能，

20、在实验中通过自己对语句的检验分析，还有老师的指导，基本掌握了数据库语言的运用，课后会多多巩固。实验三 数据完整性约束一、 实验目的理解数据完整性约束的功能。 二、实验要求 1掌握建立声明性数据完整性约束的SQL语句；2掌握建立过程完整性约束的SQL语句。三、实验内容1在实验一建立的Students数据库中，编写建立满足完整性要求的定义表的SQL语句，执行并观察执行结果。（说明：表名、列名均用给出的中文）（1）图书表，结构如下： 书号： 统一字符编码定长类型，长度为6，主码， 书名： 统一字符编码可变长类型，长度为30，非空， 第一作者：普通编码定长字符类型，长度为10，非空， 出版日期： 小日

21、期时间型，小于等于当前系统日期， 印刷数量： 小整型，取值范围：10005000，默认为4000， 价格：定点小数，小数部分一位，整数部分3位。（2）书店表，结构如下： 书店编号： 统一字符编码定长类型，长度为6，主码， 店名：统一字符编码可变长类型，长度为30，非空， 电话： 普通编码定长字符类型，12位长，取值形式：010-8位数字 地址： 普通编码可变长字符类型，40位长。前两个字符必须是：北京。（3）图书销售表（XSB），结构如下： 书号： 统一字符编码定长类型，长度为6，非空， 书店编号： 统一字符编码定长类型，长度为6，非空， 销售日期： 小日期时间型，非空，默认值为系统当前日期，

22、 销售数量： 微整型，取值大于0， 主码（书号，书店编号，销售日期）； 书号为引用“图书表”的外码；书店编号为引用“书店表”的外码。create table 图书表(书号 nchar(6) PRIMARY KEY,书名nvarchar(30) not null,第一作者 char(10) not null,出版日期 smalldatetime check(出版日期0),primary key(书号,书店编号,销售日期),foreign key(书号) references 图书表(书号),foreign key(书店编号) references 书店表(书店编号)2.执行下列两条数据插入语句，

23、是否都能执行成功为什么INSERT INTO 图书表 VALUES(T0001, 数据库系统基础, 张三, 2010-2-1, 3000,成功。INSERT INTO 图书表 VALUES(T0002, 计算机网络, 张三, 2010-1-1, 500,不成功，因为印刷数量是500，不满足规定的1000到5000。2.执行下列两条数据插入语句，是否执行成功为什么INSERT INTO 书店表 VALUES(S001,新华书店,12345678,北京市海淀区)不成功，电话不满足格式。修改后的电话：3.执行下列两条数据插入语句，是否执行成功为什么INSERT INTO 图书销售表(书号，书店编号，

24、销售数量) VALUES(T0001,S001,20)不成功，因为书号为引用“图书表”的外码。4.编写实现如下功能的触发器：（1） 限制考试成绩必须在0100分之间。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_bon sc after update as if exists(select * from inserte

25、d join deleted on = and = where =60 and =60)rollback（3） 限制不能删除选修课程考试成绩不及格学生的该门课程的选课及考试记录。 create trigger tri_c on sc after delete as if exists(select * from deleted where grade20)rollback（5）限制每个学期开设的课程总学分必须在2025之间。create trigger tri_e on course after insert,update,delete as if exists(select sum(cred

26、it) 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. 编写给用户授权的

27、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 u24用u1建立一个新的连接，并在students数据库中执行下述语句，能否成功为什么INSERT INTO course VALUES(C011，数据仓库技术，2，7)能成功，已经授予u2对course表的插入权限 再执行下述语句，能