通信工程专业 SQL数据库综合实验报告

1、实验一用SQL Server实现数据库设计实验目的：1 掌握使用企业管理器创建和管理SQL Server数据库及相关数据库对象。2 掌握使用SQL语言中的SELECT命令实现查询功能。上机准备：1 复习有关关系数据库的基本知识和概念；2 复习有关SQL语言中SELECT命令的使用；3 了解有关SQL Server系统的组成；4 复习有关SQL Server服务器的使用和管理；5 复习有关企业管理器的基本操作；6 复习有关查询分析器的基本操作；7 了解有关SQL Server服务器的登陆账号，密码；实验内容：本实验将建立一个学校教学管理数据库，在这个数据库中，存储以下信息：1 有关学生的信息，包

2、括学生的学号，姓名，班级，性别，出生时间，政治面貌，籍贯，入学时间，联系 ，简历；Student(Sno,Sname, Ssex,Classno,Sbirth,Status,Hometown,Enrollment,Telephone,Resume) 2 有关班级的信息，包括班级的班名，专业，年级，人数，班主任； SClass(ClassNo,ClassName,Major,Grade,Number,Advisor) 3 有关课程的信息，包括课程编号，课程名称，课程类别（是指该课程为必修，指选还是任选）， 适用专业，适用年级，开课时间（是指在每个学年的第一学期，还是第二学期），学分，周学时； C

3、ourse(CourseNo,CourseName,Category,Major,Grade,StartTM,Credits, WeekHours) 4 有关学年课程安排的信息，包括课程编号，开设课程的学年（例如：2002学年），开设课程的班级，任课教师； Schedule(CourseNo,StartYear,ClassNo,Teacher) 5 有关学生选课的信息，包括课程编号，学生的学号，成绩； SC(CourseNo,Sno,Score) 二、 使用企业管理器完成数据库及数据库对象的创建和管理 实验步骤： 1 创建数据库 （1）要求参数： 数据库名称：teaching 数据库逻辑文件名

4、：teaching_data 日志逻辑文件名：teaching_log 操作系统数据文件名：e:xxxteaching.mdf 操作系统日志文件名：e:xxxteaching.ldf 数据文件初始大小：5MB 日志文件初始大小：1MB 数据文件最大大小：10MB 日志文件最大大小：2MB 数据文件增长增量：10% 日志文件增长增量：10% （2）创建后在database节点查看是否已包含了刚创建的数据库teaching； （3）展开teaching 节点，以下的所有对象均创建在teaching 数据库中； 2 创建用户定义的数据类型 （1）练习按要求创建以下数据类型数据类型名称 数据描述 st

5、udent_no 长度为10且不允许空值的字符型； 3 创建表 （1） 根据上述数据库存储的信息，决定创建几个表，并给每一个表指定一个有意义的表名； （2） 根据每个表中每列存储的数据的情况，为每个列指定列名，数据类型，数据的长度，是否允许为空等列的属性； （3） 使用企业管理器建立所有的表； （4） 创建之后，还可以根据具体情况，再对表的结构进行修改；（包括添加列，删除列，修改已存在的列） 4 实现数据完整性 （1） 针对每一个表分析并定义主码（Primary Key）SC：Schedule：Course：Sclass：Student：（2） 定义UIQUE约束用来规定一个列中的两行不能有相

6、同的值； 例如：希望学生的姓名是唯一的； （3） 针对每一个表分析外部码，并利用“关系图”定义外部码（Foreign Key），建立表之间的参照关系； Create table SC(Sno char(9),CourseNo char(4),Score smallint, Primary key(Sno,CourseNo), Foreign key(Sno) refrences Student(Sno), Foreign key(CourseNo) refrences Course(CourseNo) 关系图：（4） 定义缺省值 方法1：直接在表设计时，定义列的default属性； 例如：练习

7、在定义“性别”列时，定义它的缺省值为“男”； 方法2：创建一个缺省值对象，然后绑定到任何一个需要的列； 例如：练习创建并绑定一个缺省值到学生的联系 ，缺省值为“unknown”； Create defult telephone_defult as 'unknown' （5） 创建以上约束后，练习修改约束的操作（包括增加，修改和删除以上约束）； 5 根据自己所在班级的情况，对已经创建的表自己输入一些相关示例数据，在输入的过程中，特地输入一些违反上述约束条件的数据，观察处理的效果； （1）使用企业管理器，完成一些记录的插入、修改和删除； （2）练习使用INSERT、UPDATE、D

8、ELETE命令完成记录的插入、修改和删除；如果对该命令的使用不是很清楚，请练习查找“帮助系统”完成上述命令的学习； insert into student values('200815126','张一','男','2008123','1990-04-05','团员','北京无') insert into course values('1','数据库技术','指选','通信工程','第2学期',2,2) i

9、nsert into SC(Sno,CourseNo) values('10210444','1') update course set StartTM= '每年第2学期' where CourseNo = '1' update SC set Score = 90 where Sno ='10210444' and CourseNo ='1' 执行指令：SC表中出现10210444的学科1分数：执行删除指令：可看出10210444的学科1分数已被删除：（3）练习使用TRUNCATE TABLE命令删

10、除表数据； truncate table delete此为新建的表：执行删除指令：该表已消失：（4）在练习使用命令删除数据之前，可以使用SELECT INTO命令把数据保存到一个新建的表中； Select sno,sname,ssex into stu from student三、 使用企业管理器创建和管理索引 1 利用表的属性对话框，观察每个表已经自动创建的索引； 2 针对学生选课信息表创建如下索引 （1） 按学号+课程编号建立主键索引，索引组织方式为聚簇索引； create clustered index PK_SC ON SC(Sno,CourseNo) 执行指令：创建成功：（2） 按学

11、号建立索引，考虑是否需要创建唯一索引，索引组织方式为非聚簇索引； （3） 按课程编号建立索引，考虑是否需要创建唯一索引，索引组织方式为非聚簇索引； （4） 再针对学生选课信息表输入一些数据，观察表中记录位置的变化；通过观察比较非聚簇索引和聚簇索引的不同之处； 3 针对其它表，练习创建索引；（考虑：是否对每个列都应创建索引？应该从哪些方面考虑是否应为此列创建索引？） 不需要每一列都创建索引，应该从我们对于数据库的管理和排序的方便的和简单性出发对于某些列创建索引4 练习索引的修改和删除操作四、 使用查询分析器实现以下查询 1练习课堂上举例介绍的几类查询； 2实现以下查询(1) 检索选修了课程号为C

12、1或C2课程，且成绩高于或等于70分的学生的姓名，课程名和成绩。SELECT sname,coursename,score FROM student,sc,course WHERE student.sno = sc.sno and sc.courseno = course.courseno and (course.courseno='C1' or course.courseno='C2') and score >= 70(2) 检索姓“王”的所有学生的姓名和年龄。SELECT sname, year(GETDATE() - YEAR(sbirth) FRO

13、M student WHERE sname LIKE '王%' (3) 检索没有考试成绩的学生姓名和课程名。SELECT sname,coursename,score FROM student,sc,course WHERE student.sno = sc.sno and sc.courseno = course.courseno and (course.courseno='C1' or course.courseno='C2') and score >= 70(4) 检索年龄大于女同学平均年龄的男学生姓名和年龄。SELECT sname

14、,year(GETDATE() - YEAR(sbirth) age FROM student WHERE ssex ='男' and year(GETDATE() - YEAR(sbirth) > (SELECT AVG(year(GETDATE() - YEAR(sbirth) FROM student WHERE ssex ='女')注：上述查询中所用的课程号和学号的值，可以根据自己表中的数据作修改；为了验证查询的正确，可能还需要输入或修改表中的示例数据； 五、 创建和管理视图 1 创建视图： 使用企业管理器或使用CREATE VIEW命令例1：创建

15、视图，包含所有通信工程专业的学生的信息； CREATE VIEW TX_Student AS SELECT sno,sname,ssex,student.classno,sbirth,status FROM Student,SClass WHERE Student.ClassNo = SClass.ClassNo and Major = '通信工程' WITH CHECK OPTION 例2：创建视图，包含所有学生的学号，姓名，选课的课程名和成绩； CREATE VIEW SC_Grade WITH ENCRYPTION AS SELECT Student.sno,sname,

16、coursename,score FROM Student,Course,SC WHERE Student.sno=SC.sno and Course.courseno=SC.courseno 例3：创建视图，包含所有课程的课程号，名，班级名称及每班选课的人数；CREATE VIEW SC_CLASS AS SELECT course.courseno,coursename,classname,total FROM course,sclass, (SELECT course.courseno,classno,count(*) total FROM course,student,sc WHERE

17、 course.courseno=sc.courseno and student.sno=sc.sno GROUP BY course.courseno,student.classno) temp WHERE course.courseno=temp.courseno and sclass.classno=temp.classno 2 练习修改视图的定义 第一个视图，包括with check option 选项 第二个视图，包括with encryption选项；（查看视图的属性，观察和以前有和不同） 3利用已经创建的视图进行查询； 4利用已经创建的视图修改数据（观察是否所有通过视图的修改都能

18、实现？） 完全可以5练习删除视图可以直接删除 实验二 SQL Server数据库设计高级内容一、使用查询分析器实现以下查询 (1) 统计有学生选修的课程门数。SELECT COUNT(DISTINCT cno) FROM sc (2) 求选课在四门以上的学生所选课程的平均成绩(不统计不及格的课程)。最后按降序列出平均成绩名次名单来。 SELECT sno,AVG(score) avg_grade FROM sc WHERE score >= 60 GROUP BY sno HAVING count(courseno)>=4 ORDER BY avg_grade DESC (3) 统

19、计每门指选课程的学生选修人数(超过4人的课程才统计),要求输出课程号,课程名和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列。 SELECT courseno,COUNT(sno) snum FROM sc GROUP BY courseno HAVING COUNT(sno)>=4ORDER BY COUNT(sno) DESC, courseno ASC (4) 检索所学课程包含了s3所选所有课程的学生姓名。 SELECT DISTINCT sno FROM sc sc_1 WHERE NOT EXISTS( SELECT * FROM sc sc_2 WHERE s

20、no='10210477' AND NOT EXISTS( SELECT * FROM sc sc_3 WHERE sc_3.sno = sc_1.sno AND sc_3.courseno = sc_2.courseno ) ) 注：上述查询中所用的课程号和学号的值，可以根据自己表中的数据作修改；为了验证查询的正确，可能还需要输入或修改表中的示例数据； 二、 实现数据完整性 （1） 定义check约束check约束用来限制用户输入的某一列数据； 例如：成绩输入的值应该限制为0-100之间的数值 alter table sc add constraint ck_sc CHECK

21、( score >= 0 AND score <= 100) 删除约束 alter table sc drop constraint ck_sc （2） 定义规则（rule）rule也可以用来限制用户输入的数据，但它只定义一次，可以绑定到一列或多列； 例如：创建一个规则，保证只允许输入指定的课程类别：“必修”，“任选”，“指选”，然后把此规则绑定到“课程类别”； 创建规则 create rule coursetype as coursetype in ("必修","任选","指选") 绑定规则 sp_bindrule &#

22、39;coursetype','Course.Category' 解除绑定 sp_unbindrule 'Course.Category'删除规则 删除规则：drop rule coursetype （3） 创建以上约束后，练习修改约束的操作（包括增加，修改和删除以上约束）；三、 在企业管理器中利用数据导入，导出向导练习数据的导入导出；把每个表中的数据导出到指定的文本文件中； 四、 在企业管理器中，练习数据库的完整性备份和恢复； 五、 存储过程和触发器的实现 1 存储过程 （1）使用CREATE PROCEDURE命令创建存储过程例1：定义存储过程，实现

23、学生学号，姓名，课程名和成绩的查询； CREATE PROC student_query AS SELECT student.sno,sname,coursename,score FROM student,course,sc WHERE student.sno = sc.sno and course.courseno = sc.courseno EXEC student_query 例2：定义存储过程，实现按某人指定课程的成绩； CREATE PROC student_query1 sname varchar(20), cname varchar(30) AS SELECT student.s

24、no,sname,coursename,score FROM student,course,sc WHERE student.sno = sc.sno AND course.courseno = sc.courseno AND sname = sname AND coursename = cname exec student_query1 '张三','数据库技术' 例3：定义存储过程，在查询某人所选修的课程和成绩，指定姓名时，可以只给出姓； CREATE PROC student_query2 sname varchar(20) = '%' AS

25、 SELECT student.sno,sname,coursename,score FROM student,course,sc WHERE student.sno = sc.sno AND course.courseno = sc.courseno and sname LIKE sname EXEC student_query2 '张%'例4：定义存储过程，计算并查看指定学生的总学分alter table course alter column Credits float CREATE PROC student_query3 sname varchar(20), total

26、 integer OUTPUT AS SELECT total = SUM(Credits) FROM student,course,sc WHERE student.sno = sc.sno AND course.courseno = sc.courseno and sname = sname declare total float exec student_query3 '王五',total OUTPUT select total （2）使用EXEC命令执行上述存储过程2触发器（1）使用CREATE TRIGGER命令对学生选课信息表创建插入触发器，实现的功能是：当向学生选课信息表中插入一记录时，检查该记录的学号在学生表中是否存在，检查该记录的课程编号是否在课