广工数据库实验报告

1、数据库应用实验报告学 院 计算机学院 专 业 计算机科学与技术 班 级 _ 学 号 姓 名 指导教师 顾国生 ( 2015 年 1 月)51 / 51文档可自由编辑 计算机 学院 班 学号： 姓名： 协作者：_ 教师评定： 实验_一_题目_ 数据库及基本表的建立 实验 二 题目 查询数据库 实验 三 题目 创建和使用视图、索引 实验平台：sql server 2005 Win7 计算机 学院 班 学号： 姓名： 协作者：_ 教师评定： 实验题目 数据库及基本表的建立 _ 实验项目名称：数据库及基本表的建立实验项目性质：验证型所属课程名称：数据库应用实验计划学时：3一、实验目的1、掌握SQL S

2、ERVER的查询分析器和企业管理器的使用；2、掌握创建数据库和表的操作；二、实验内容和要求1、分别使用SQL语句、企业管理器（Enterprise Manager）创建数据库；2、使用SQL语句、企业管理器（Enterprise Manager）创建数据库表；三、实验主要仪器设备和材料1计算机及操作系统：PC机，Windows；2数据库管理系统：SQL sever 2005；四、实验方法、步骤及结果测试创建一个教学管理数据库SC，其描述的信息有：学生信息、课程信息、教师信息、学生选课成绩、授课信息、班级信息、系部信息、专业信息。创建：student表(学生信息表)、course表（课程信息表)

3、、teacher表（教师信息表)、student _course表（学生选课成绩表)、teacher_course表（教师上课课表）等。1、创建数据库：确定数据库名称；数据库用于学生管理，命名为SC确定数据库的位置；要求：数据文件和日志文件分别存储在E盘自己的目录下。确定数据库的大小；根据实际的数据量确定数据文件的初始大小为30MB，日志文件的初始大小为3MB。确定数据库的增长；根据实际情况，确定数据文件按20%增长，日志文件按1MB增长。要求：（1）、利用查询分析器（Query Analyzer），使用SQL语句指定参数创建数据库；实现代码及截图：SQL语句CRETE DATABASE SC

4、ON(NAME = SC_DB;FILENAME = E:DATASC_DATA.mdf,SIZE = 30MB,FILENAME = 20%)LOG ON(NAME = SC_LOG,FILENAME = E:DATASC_DATA.ldf,SIZE = 3MB,FILEGROWTH = 1MB)查询分析器执行情况：SQL语句及执行结果截图显示2、创建基本表利用查询分析器，使用SQL语句方式创建方式将下面各表建立到教学管理数据库中。(1) 创建student表字段名代码类型约束学号s_nochar(8)主键姓名snamechar(8)非空性别sexchar(2) 出生日期sbirthdayS

5、malldatetime学生所在院系编号dnochar(6) 外键专业代码spnochar(8)外键班级编码class_nochar(4) (2)创建Course表字段名代码类型约束课程编号cnochar(10)主键课程名称cnamechar(20)非空专业代码spnochar(8)外键课程类型编号ctnotinyint理论学时lecturetinyint实验学时experimenttinyint开课学期semestertinyint课程学分credittinyint（3）创建student _course表字段名代码类型约束学号s_nochar(8)主键,与student表中s_no 外键关

6、联,级联删除上课编号tcidsmallint主键学生成绩score tinyint (4)创建teacher表 字段名代码类型约束教师编号t_nochar(8)主键教师姓名t_namechar(8)非空性别t_sexchar(2)出生日期t_birthdaysmalldatetime教师所在院系编号dnochar(6) 外键职称tech_titlechar(10)(5)创建系部表(department) 字段名代码类型约束院系编号dnochar(6)主键院系名称dept_namechar(20)非空院系负责人headerchar(8) (6)创建专业信息表(speciality) 字段名代码类

7、型约束专业代码spnochar(8)主键院系编号dnochar(6)外键,非空专业名称spnamechar(20)非空(7)创建teacher_course表字段名代码类型约束上课编号tcidsmallint主键教师编号t_nochar(8)外键专业代码spnochar(8)外键班级编码class_nochar(4)课程编号cnochar(10)非空,外键学期semesterChar(6)学年schoolyear Char(10)(8)创建班级表(class) 字段名代码类型约束专业代码spnochar(8)主键，与 speciality 表中spno 外键关联,班级编码class_nocha

8、r(4)主键,班负责人headerchar(8)3、 查看各数据表之间的关系，生成数据库关系图。生成数据库关系图截图显示4、创建各表的实现代码及截图：SQL语句CREATE TABLE Department(DNo CHAR(6) PRIMARY KEY,Dept_Name CHAR(20) NOT NULL,Header CHAR(8);CREATE TABLE Speciality(SpNo CHAR(8) PRIMARY KEY,DNo CHAR(6) NOT NULL,FOREIGN KEY (DNo)REFERENCES Department(DNo) ,SpName char(20

9、) NOT NULL);CREATE TABLE Student(S_no CHAR(8) PRIMARY KEY,Sname CHAR(8) NOT NULL,Sex CHAR(2),SBirthday Smalldatetime,DNo CHAR(6),SpNO CHAR(8),FOREIGN KEY (DNo) REFERENCES Department(DNo),FOREIGN KEY (SpNO) REFERENCES Speciality(SpNo),Class_no CHAR(4);CREATE TABLE Course(Cno CHAR(10) PRIMARY KEY,Cnam

10、e CHAR(20) NOT NULL,SpNo CHAR(8),FOREIGN KEY (SpNo) REFERENCES Speciality(SpNo),CtNO TINYINT,Lecture TINYINT,Experiment TINYINT,Semester TINYINT,Credit TINYINT);CREATE TABLE Student_course(S_no CHAR(8),PRIMARY KEY(S_no, TcId),FOREIGN KEY (S_no) REFERENCES Student(S_no) ON DELETE CASCADE,TcId SMALLIN

11、T ,Score TINYINT);CREATE TABLE Teacher(T_NO CHAR(8) PRIMARY KEY,T_NAME CHAR(8) NOT NULL,T_SEX CHAR(2),T_BIRTHDAY smalldatetime,DNo CHAR(6),FOREIGN KEY (DNo) REFERENCES Department(DNo),Tech_Title CHAR(10);CREATE TABLE Teacher_course(TcId SMALLINT PRIMARY KEY,T_No CHAR(8),SpNo CHAR(8),FOREIGN KEY (T_N

12、o) REFERENCES Teacher(T_NO),FOREIGN KEY (SpNo) REFERENCES Speciality(SpNo),Class_No CHAR(4),CNo CHAR(10) NOT NULL,FOREIGN KEY (CNo) REFERENCES Course(CNo) ,Semester CHAR(6),Schoolyear CHAR(10);CREATE TABLE Class(SpNo CHAR(8),Class_No CHAR(4) ,PRIMARY KEY(Class_No,SpNo),FOREIGN KEY (SpNo) REFERENCES

13、Speciality(SpNo) ,Header CHAR(8); 查询分析器执行情况：SQL语句及执行结果截图显示5、利用查询分析器修改上述各表。(1)、用INSERT语句向各个表中插入数据录入5 条记录。录入时注意体会外键约束。实现代码及截图：查询分析器执行情况：SQL语句及执行结果显示insert into department(dno,dept_name,header) values(50012,外国语,sharon)insert into department(dno,dept_name,header) values(50017,机电工程,steve)insert into depa

14、rtment(dno,dept_name,header) values(50015,自动化,jackson)insert into department(dno,dept_name,header) values(50025,电子商务,janet)insert into department(dno,dept_name,header) values(50001,计算机,peter);insert into speciality(spno,dno,spname)values(KB001,50001,软件工程) insert into speciality(spno,dno,spname)value

15、s(KB011,50025,电子商务) insert into speciality(spno,dno,spname)values(KB079,50012,德语) insert into speciality(spno,dno,spname)values(KB045,50017,微电子工程) insert into speciality(spno,dno,spname)values(KB036,50015,机械与自动化);insert into teacher(t_no,t_name,t_sex,t_birthday,dno,tech_title)values(WG001,Lily,女,198

16、5-11-08,50012,实习讲师)insert into teacher(t_no,t_name,t_sex,t_birthday,dno,tech_title)values(JD015,Smith,男,1970-2-23,50017,高级讲师) insert into teacher(t_no,t_name,t_sex,t_birthday,dno,tech_title)values(CS079,Jobs,男,1980-1-20,50001,系主任) insert into teacher(t_no,t_name,t_sex,t_birthday,dno,tech_title)value

17、s(ZD012,Sam,男,1972-11-04,50015,副教授) insert into teacher(t_no,t_name,t_sex,t_birthday,dno,tech_title)values(DZ030,Janet,女,1982-10-18,50025,教授);insert into class(spno ,class_no,header)values(KB079,1307,王红)insert into class(spno ,class_no,header)values(KB045,1205,张强) insert into class(spno ,class_no,he

18、ader)values(KB036,1103,李彪) insert into class(spno ,class_no,header)values(KB011,1206,曾江) insert into class(spno ,class_no,header)values(KB001,1310,国彬);insert into student(s_no ,sname,sex,sbirthday,dno,spno,class_no)values(130721,王红,女,1994-05-10,50012,KB079,1307)insert into student(s_no ,sname,sex,sb

19、irthday,dno,spno,class_no)values(120518,张强,男,1993-10-05,50017,KB045,1205) insert into student(s_no ,sname,sex,sbirthday,dno,spno,class_no)values(110329,李彪,男,1993-10-05,50015,KB036,1103) insert into student(s_no ,sname,sex,sbirthday,dno,spno,class_no)values(120622,曾江,男,1993-10-05,50025,KB011,1206) in

20、sert into student(s_no ,sname,sex,sbirthday,dno,spno,class_no)values(131025,国彬,男,1993-10-05,50001,KB001,1310);insert into course(cno,cname,spno,ctno,lecture,experiment,semester,credit)values(CSC701,JAVA程序设计,KB001,2,50,15,2,4)insert into course(cno,cname,spno,ctno,lecture,experiment,semester,credit)v

21、alues(ZDC812,自动控制原理,KB036,2,50,15,2,3)insert into course(cno,cname,spno,ctno,lecture,experiment,semester,credit)values(WGC011,德语入门,KB079,1,56,10,1,4)insert into course(cno,cname,spno,ctno,lecture,experiment,semester,credit)values(JDC025,机械设计,KB045,2,30,20,1,3)insert into course(cno,cname,spno,ctno,l

22、ecture,experiment,semester,credit)values(DZC701,电子商务概论,KB011,1,30,5,2,5)insert into student_course(s_no,tcid,score)values(130721,1,90)insert into student_course(s_no,tcid,score)values(120518,2,75)insert into student_course(s_no,tcid,score)values(110329,5,80)insert into student_course(s_no,tcid,score

23、)values(120622,3,55)insert into student_course(s_no,tcid,score)values(131025,4,96)insert into teacher_course(tcid,t_no,spno,class_no,cno,semester,schoolyear)values(4,CS079,KB001,1310,CSC701,2,2013-2014)insert into teacher_course(tcid,t_no,spno,class_no,cno,semester,schoolyear)values(5,ZD012,KB036,11

24、03,ZDC812,2,2013-2014)insert into teacher_course(tcid,t_no,spno,class_no,cno,semester,schoolyear)values(1,WG001,KB079,1307,WGC011,1,2013-2014)insert into teacher_course(tcid,t_no,spno,class_no,cno,semester,schoolyear)values(2,JD015,KB045,1205,JDC025,1,2013-2014)insert into teacher_course(tcid,t_no,s

25、pno,class_no,cno,semester,schoolyear)values(3,DZ030,KB011,1206,DZC701,2,2013-2014)实验结果截图显示（插入数据的表格）共8组（共8个表）(2)、用UPDATE语句更改student表中数据；实现代码：student表更改前的内容截图显示查询分析update student set sbirthday = 1994-10-10 where s_no = 130721student表更改后的内容截图显示修改前：修改后：(3)、用DELETE语句删除student表中数据；实现代码：student表更改前的内容截图显示查

26、询分析delete from student_course where s_no = 130721delete from student where s_no = 130721student表更改后的内容截图显示删除前：student_course表student表删除后：student_course表student表五、实验中出现的问题及解决方案删除student表的时候出现以下情况：消息547，级别16，状态0，第102 行DELETE 语句与REFERENCE 约束FK_student_co_s_no_0EA330E9冲突。该冲突发生于数据库SC，表dbo.student_course,

27、 column s_no。语句已终止。因为没有级联删除导致违反了参照完整性。student_course表的s_no充当外码，它是与student的s_no主码相对应的。当用户删除了student表当中某个元组时，应该将student_course里面与被删除学生相关的选课信息也删除掉，否则的话student_course里的外码在student表中找不到与之对应的元组，因而违反参照完整性。修改前的代码：delete from student where s_no = 130721修改后的代码： delete from student_course where s_no = 130721del

28、ete from student where s_no = 130721六、思考题1.说明数据库中的表和数据文件的关系。数据文件是数据库的存放位置，数据库是数据表的存放位置，数据文件与数据表之间是需要数据库来做承接的。表为数据库中数据存储的基本单位，其数据按行、列存储。每个表具有一表名和列的集合。每一列有一个列名、数据类型、宽度或精度、比例。一行是对应单个记录的列信息的集合。 数据文件存放着在数据库中存储的数据，且一个数据库只有一个数据文件。 2.数据库中的日志文件能否单独修改? 可以。把对数据的修改写到数据库中和把表示这个修改的日子记录写到日志文件中是两个不同的操作。有可能在这两个操作之间发

29、生某种故障，即这两个操作只完成了其中一个。所以，可以单独修改数据库中的日志文件而不一定会修改数据。 计算机 学院 班 学号： 姓名： 协作者：_ 教师评定： 实验题目 查询数据库 _ 实验项目名称：使用SQL语言查询管理数据实验项目性质：基础型所属课程名称：数据库应用实验计划学时：2一、实验目的熟悉SQL语句的基本使用方法，学习如何编写SQL语句来实现查询1、掌握基本的SELECT查询及其相关子句的使用；2、掌握复杂的SELECT查询，如多表查询、子查询、连接查询和嵌套查询。二、实验内容和要求使用SQL查询分析器查询数据，练习查询语句的使用，掌握SELECT 语句的完整结构，包括简单查询、嵌套

30、查询、连接查询等基本实现方法，掌握存储查询结果的方法，体会各种查询的异同及相互之间的转换，体会各种查询的执行过程，为简单综合应用打下良好的基础。三、实验主要仪器设备和材料1计算机及操作系统：PC机，Windows；2数据库管理系统：SQL sever 2005；四、实验方法、步骤及结果测试要求用SELECT完成以下查询， 1、对实验一建立的数据库表进行查询简单查询：（1）、查询全部学生的学号、姓名、性别和出身年月日。实现代码及查询结果截图：格式如下查询分析器执行情况：SQL语句及执行结果显示select s_no,sname,sex,sbirthday from student查询结果截图显示

31、（2）、根据学号=“你的学号”查询对应元组的姓名和年龄。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示 select s_no,sname,year(getdate() - year(sbirthday) from student where s_no = 110329查询结果截图显示 （3）、查询院系编号为50017（计算机学院）的全部学生的学号、姓名、性别和出身年月日。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示select s_no,sname,sex,sbirthday from student where dno = 50017查询结果截

32、图显示（4）、查询“计算机科学与技术”专业（专业代码为50001）并且班级代码为1310的学生的学号、姓名和出生日期。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示select s_no,sname,sbirthday from student where spno= 50001 and class_no =1310查询结果截图显示 （5）、查询在1996-11-11以后出生的计算机学院（院系编号为510000）学生的学号、姓名、性别和出身年月日。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示为了检验，又插入了一条学生记录到student表inser

33、t into student(s_no ,sname,sex,sbirthday,dno,spno,class_no)values(131027,合子,男,1996-11-11,51000,KB001,1310);select s_no,sname,sbirthday from student where dno = 510000 and sbirthday 1996-11-11查询结果截图显示 （6）、查询全部学生的学号、姓名、性别和出身日期结果按照出生日期的升序排列。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示select s_no,sname,sex,sbirth

34、day from student order by sbirthday asc查询结果截图显示连接查询：（1）、查询全部学生的学号、姓名、性别、所在院系名称和专业名称实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示select s_no,sname,sex,dept_name,spname from student,department,specialitywhere student.dno = department.dno and student.spno = speciality.spno查询结果截图显示（2）、查询选修了课程1（上课编号）的学生的学号、姓名、专业名称和

35、这门课的成绩实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示select student_course.s_no,sname,dept_name,score from student,student_course,departmentwhere student.s_no = student_course.s_no and student.dno = department.dno and tcid = 1查询结果截图显示（3）、查询学生不及格的情况列出不及格学生的学号、姓名和不及格的课程名称。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示select s

36、tudent.s_no,sname,cname from student,course,student_course where score 80)查询结果截图显示 五、实验中出现的问题及解决方案答：1.这次实验出现的问题主要是一些题目要求的数据本身在实验一的时候是没有的，所以必须去插入一些测试记录，一边测试。 2.where后面不能有聚集函数，改用group by .having. ，前者是作用于基本表和视图，选出满足条件的元组，后者是作用于分组，用于选择满足条件的组。六、思考题1、 连接查询分哪几类？各有什么特点？一个查询同时涉及多个表，则称为连接查询。连接查询分为等值连接查询、自然连接查

37、询、非等值连接查询、自身连接查询、外连接查询和复合条件查询等。连接查询的where字句中用来连接两个表的条件成为连接条件和连接谓词。格式为： “表1.列1 比较运算符 表2.列2” ，列名就是连接字段（1） 等值与非等值连接查询 当比较运算符为“=”时就是等值连接，使用其他运算符就是非等值连接 （2）自然连接查询 特殊的等值连接，两个关系中进行比较的分量必须是同名的属性组，在结果中把重复的属性列去掉 （3）外连接查询自然连接的时候，两个关系R,S在公共属性上值相等的元组构成新的关系。此时，关系R中某些元组有可能在S中不存在公共属性上值相等的元组，被舍弃，同样S中的某些元组也会被舍弃，这些被舍弃

38、的元组就是悬浮元组。把悬浮元组也保存在结果关系中，而在悬浮元组的其他属性上填NULL，就叫做外连接。只保留左边关系R的悬浮元组叫做左外连接，只保留右边关系S的悬浮元组叫做右外连接。 （4）复合条件查询涉及多个表或者多个查询条件的查询2、 进行连接查询时应注意哪些问题？（1）根据问题判断这个查询最少需要几张表，可以根据查询的字段和要筛选的条件是进行判断；（2）判断要连接的表之间是否存在公共字段，如果有就可以连接，如果没有那么就要考虑需要一个中间表了 ； （3）实施连接，要明确2表关联的公共字段是哪个字段 ； （4）连接成功之后的查询结果是一张大表，同样可以完成筛选，排序，分组，嵌入聚合函数等操作

39、。 计算机 学院 班 学号： 姓名： 协作者：_ 教师评定： 实验题目 创建和使用视图、索引 _ 实验项目名称：创建和使用视图实验项目性质：基础型所属课程名称：数据库应用实验计划学时：3一、实验目的1、理解视图、索引的定义、索引的优点与、索引的工作原理；2、掌握在查询分析器和企业管理器中创建、修改及删除视图、索引；3、掌握创建视图、索引的SQL语句的用法；并能够熟练利用视图向表中插入、删除和修改数据。4、掌握使用视图来查询数据。二、实验主要仪器设备和材料1计算机及操作系统：PC机，Windows7；2数据库管理系统：SQL Server 2005；三、实验方法、步骤及结果测试（一）、视图1、建

40、立“计算机学院”的学生基本情况视图ies_student_view，该视图包括计算机学院所有学生的学号、姓名、性别、出身年月、专业名称。2、执行ies_student_view视图并观察结果。3、建立课程1（上课编号）的学生名册的视图，该名册包括学生的学号、姓名、专业名称和这门课的成绩；并查询结果。4、建立统计不及格情况的视图，列出不及格学生的学号、姓名和不及格的课程代码；5、执行视图并观察结果。6、修改视图ies_student_view，使该视图包括所有学生的学号、姓名、性别、出身年月、学院名称、专业名称。7、执行ies_student_view视图并观察结果。8、删除视图ies_stud

41、ent_view。并查询结果。记录上述实验过程并截图说明。创建视图的SQL语句及执行结果显示create view ies_student_viewas select s_no,sname,sex,sbirthday,spname from student,department,specialitywhere student.spno = speciality.spno and student.dno = department.dno and department.dept_name = 计算机with check option查询视图的SQL语句及执行结果截图创建视图的SQL语句及执行结果显

42、示create view sc_2_view as select student.s_no,sname,spname,scorefrom student,speciality,student_coursewhere student.s_no = student_course.s_no and student.spno = speciality.spno and tcid =1with check optioncreate view no_pass_student_cno_viewas select student.s_no,sname,ofrom student,student_course,coursewhere student.s_no = student_course.s_no and score 60 and student.spno = course.spno with check option查询视图的SQL语句及执行结果截图select * from ies_student_view修改视图的SQL语句及执行结果显示alter vie