数据库试验报告

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

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

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

4、长度为8非空Tname教师名普通编码定长字符串，长度为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 nva

5、rchar( 20) not null,Credit int ,Semester tinyint ,Primary key (Cno)createtablesc (Sno char(7)notnull,Cno char (10)notn ull,Gradetinyint5primarykey(Sno, Cnc),foreig nkey(Sno )refere ncesStudent (Snc),foreig nkey (Cno )refere ncesCourse (Cno),)create table teacher (Tno char (8) not null,Tname char (10

6、) not null,Dept nvarchar (20),Salary numeric (6, 2),Birthery smalldatetime)执行结果:2. 写出实现如下操作的SQL语句，并查看执行结果：(1) 在SC表中添加一个新的修课类别列：列名为：XKLB类型为char。alter table scadd XKLB char ( 4)(2) 将Course表中的Credit列的类型改为：tinyint( 微整型，取值范围在0255)。alter table coursealter colu mn Credit tinyint(3) 删除Student表的spec列。alter t

7、able studentdrop column spec(4) 为Teacher表添加主码约束，其主码列为：Tno。alter table teacheradd constrainty_sprimary key (Tno)3. 执行下列语句，能否成功为什么INSERT INTO SC VALUES(S001 , C01 , 88 ,必修)答：不成功，因为错误显示sc表的sno学号列有外键约束，它是引用的 student学生表里面的学号，所以执行前面的语句时s001 不存在于学生表中，所以执行不成功。若在student学生表中插入学号s001 的学 生信息，如：insert into stude

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

9、sage, sdept from studentwhere sdept = 计算机系 order by sage desc2. 统计每个学生的选课门数，并按选课门数的递增顺序显示结果。select sno , count (*) from scgroup by snoorder by count (*)3. 列出总成绩超过 200 分的学生，要求列出学号、总成绩。select sno , sum( grade ) 总成绩 from scgroup by snohaving sum( grade )2004. 查询成绩 80 分以上的学生的姓名、课程号和成绩，并将结果按成绩的 降序排列。sele

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

11、join course c on =where cname =VBorder by grade desc7. 查询哪些课程没有人选，要求列出课程号和课程名。select , Cname from Course c left join scon =where is null8查询计算机系学生考试成绩高于全体学生的总平均成绩的学生的姓名、 考试的课程名和考试成绩。select sname , cname, grade from student sjoin sc on =join course c on =where grade all( select avg( grade ) from sc )a

12、nd sdept = 计算机系9. 查询VB考试成绩最低的学生的姓名、所在系和VB成绩select top 1 sname , sdept , grade from student sjoin sc on =join course c on =where cname =VBorder by grade10. 查询VB课程考试成绩高于VB平均成绩的学生的姓名select sname from student sjoin sc on =join course c on =where cname =VBand grade all( select avg( grade ) from scjoin co

13、urse c on =where cname =VB)11. 查询平均成绩大于 80分的课程的名字和学分select cname , semester from course c where cno in (select from sc group byhaving avg( grade ) 80)12. 统计每门课程的选课人数，包括有人选的课程和没有人选的课程，列 出课程号，选课人数及选课情况，其中选课情况为：如果此门课程的 选课人数超过 100 人，则显示“人多”；如果此门课程的选课人数在 40100,则显示“一般”；如果此门课程的选课人数在140,则显示“人少”；如果此门课程没有人选，则

14、显示“无人选”select, count 选课人数 ,casewhencount (*)100 then 人多 whencount (*)between40and 100 then 一般 whencount (*)between1and 40 then 人少whencount (*)=0 then 无人选 end as 选课情况 from sc group by13查询至少选修了“ 9512102”号学生选修的全部课程的学生，列出学 号和所选的课程号。select sno , cno from sc sc1where not exists (select * from course c joi

15、n sc on =where sno =9512102and not exists (select * from scwhere = and =)14查询至少选修了第 2 学期开设的全部课程的学生的姓名和所在系。select sname , sdept from student swhere not exists (select * from course cwhere = 2and not exists ( select * from sc where = and = )15. 修改“VB课程的考试成绩，如果是计算机系的学生，则增加 4分;如果是信息系的学生则增加 8 分，其他系的学生增加

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

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

18、on = where cname = 计算机文化学 and sdept = 计算机系 - 子查询update sc set grade =grade +10where sno in(select sno from studentwhere sdept = 计算机系 and cno in( select cno from course where cname = 计算机文化学 ) )20. 删除“ VB考试成绩最低的学生的 VB修课记录。Delete from scwhere cno =( select cno from course where cname =VB )Andgrade=( se

19、lect min ( grade ) from scwhere cno =( select cno from course where cname =VB )21. 修改高等数学的考试成绩，修改规则如下：如果是计算机系学生，则 加 10 分，如果是信息系学生则加 5 分，如果是数学系学生则分数不变。update sc set grade =grade +case sdeptwhen 计算机系 then 10when 信息系 then 5else 0endfrom student s join sc on =join course c on =where cname = 高等数学 四实验小结这次

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

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

22、码定长类型，长度为 6，非空，书店编号： 统一字符编码定长类型，长度为 6，非空，销售日期： 小日期时间型，非空，默认值为系统当前日期，销售数量： 微整型，取值大于 0，主码(书号，书店编号，销售日期) ； 书号为引用“图书表”的外码；书店编号为引用“书店表”的外码。create table 图书表 ( 书号 nchar ( 6) PRIMARYKEY, 书名 nvarchar (30) not null, 第一作者 char (10) not null, 出版日期 smalldatetime check ( 出版日期 0),primary key(书号，书店编号，销售日期),foreign

23、key (书号)references图书表(书号),foreignkey (书店编号)references 书店表(书店编号)2. 执行下列两条数据插入语句，是否都能执行成功为什么INSERT INTO 图书表VALUES( T0001, 数据库系统基础 , 张三 , 2010-2- 1, 3000 ,成功INSERT INTO 图书表VALUES（ T0002, 计算机网络 , 张三 , 2010-1- 1, 500,不成功，因为印刷数量是 500，不满足规定的 1000到 50002. 执行下列两条数据插入语句，是否执行成功为什么INSERT INTO 书店表VALUES（ S001, 新

24、华书店 ,12345678,北京市海淀区）不成功，电话不满足格式。修改后的电话：3. 执行下列两条数据插入语句，是否执行成功为什么INSERT INTO 图 书 销 售 表 （ 书 号 ， 书 店 编 号 ， 销 售 数 量 ） VALUES（ T0001, S001,20）不成功，因为书号为引用“图书表”的外码4. 编写实现如下功能的触发器：(1)限制考试成绩必须在 0100 分之间。create trigger tri_aon sc after insert , updateasif exists( select * from scwhere grade not between 0 and

25、 100 ) rollback2）限制不能将不及格的考试成绩改为及格create trigger tri_bon sc afterupdateasif exists( select *from inserted join deleted on = and = where =60 and =60)rollback(3)限制不能删除选修课程考试成绩不及格学生的该门课程的选课及 考试记录。create trigger tri_con sc after delete asif exists( select * from deleted where grade 20) rollback(5) 限制每个学

26、期开设的课程总学分必须在2025 之间。create trigger tri_eon course after insert , update , deleteasif exists( select sum( credit )from coursegroup by semesterhaving sum( credit ) not between 20 and 25 )rollback五实验小结通过这次上机实验，把上课中所讲的数据完整性约束的相关知识得到了复习，通过上机更直观的理解了完整性约束的功能，通过添加删除约束的操作更好的掌握了有关知识，收获很大。实验四安全管理一、实验目的1. 掌握安全管

27、理的过程。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的查 询权限，授予