广工数据库原理实验报告.docx

数据库原理实验报告学 院_计算机_专 业 班 级 学 号 姓 名 指导教师 胡欣如 （2014年 12月）计算机 学院 专业 学号： 姓名： 协作者：_ 教师评定： 实验_一_题目 数据库及基本表的建立 实验_二_题目 查询数据库 实验_三_题目 创建和使用视图、索引、存储过程 实验_四_题目 小型数据库规划设计 实验平台：Microsoft SQL Server 2005实验一 数据库及基本表的建立一、实验目的1、掌握SQL SERVER的查询分析器和企业管理器的使用；2、掌握创建数据库和表的操作；二、实验内容和要求1、分别使用SQL语句、企业管理器（Enterprise Manager）创建数据库；2、使用SQL语句、企业管理器（Enterprise Manager）创建数据库表；三、实验主要仪器设备和材料1计算机及操作系统：PC机，Windows 2000/xp；2数据库管理系统：SQL sever 2000/2005；四、实验方法、步骤及结果测试创建一个教学管理数据库SC，其描述的信息有：学生信息、课程信息、教师信息、学生选课成绩、授课信息、班级信息、系部信息、专业信息。创建：student表(学生信息表)、course表（课程信息表)、teacher表（教师信息表)、student _course表（学生选课成绩表)、teacher_course表（教师上课课表）等。题目1、创建数据库：1、创建数据库：确定数据库名称；数据库用于学生管理，命名为SC确定数据库的位置；要求：数据文件和日志文件分别存储在E盘自己的目录下。确定数据库的大小；根据实际的数据量确定数据文件的初始大小为30MB，日志文件的初始大小为3MB。确定数据库的增长；根据实际情况，确定数据文件按20%增长，日志文件按1MB增长。实现代码及截图：查询分析器执行情况：SQL语句及执行结果显示CREATE DATABASE SCON(NAME=SC_DAT,FILENAME=E:5944SC.mdf,SIZE=30MB,FILEGROWTH=20%)LOG ON(NAME=SC_LOG,FILENAME=E:5944SC.ldf,SIZE=3MB,FILEGROWTH=1MB)实验结果截图显示题目2、创建基本表创建各表的实现代码及截图：查询分析器执行情况：SQL语句及执行结果显示1、DEPARTMENT表USE SCGOCREATE TABLE DEPARTMENT(dno char(6),dept_name char(20) not null,header char(8),PRIMARY KEY (dno)2、TEACHER表USE SCGOCREATE TABLE TEACHER(t_no char(8),t_name char(8) not null,t_sex char(2),t_birthday smalldatetime,dno char(6),teach_title char(10),PRIMARY KEY (t_no),FOREIGN KEY(dno) REFERENCES DEPARTMENT(dno)3、SPECIALITY表USE SCGOCREATE TABLE SPECIALITY(spno char(8),dno char(6) not null,spname char(20)not null,PRIMARY KEY (spno),FOREIGN KEY(dno) REFERENCES DEPARTMENT(dno)4、STUDENT表USE SCGOCREATE TABLE STUDENT(s_no char(8),sname char(8) not null,sex char(2),sbirthday Smalldatetime,dno char(6),spno char(8),class_no char(4),PRIMARY KEY (s_no),FOREIGN KEY(dno) REFERENCES DEPARTMENT(dno),FOREIGN KEY(spno) REFERENCES SPECIALITY(spno)5、COURSE表USE SCGOCREATE TABLE COURSE(cno char(10),cname char(20) not null,spno char(8),ctno tinyint,lecture tinyint,experiment tinyint,semester tinyint,credit tinyint,PRIMARY KEY (cno),FOREIGN KEY(spno) REFERENCES SPECIALITY(spno)6、STUDENT_COURSE表USE SCGOCREATE TABLE STUDENT_COURSE(s_no char(8),tcid smallint,score tinyint,PRIMARY KEY (s_no,tcid),FOREIGN KEY(s_no) REFERENCES STUDENT(s_no) )7、CLASS表USE SCGOCREATE TABLE CLASS(spno char(8),class_no char(4),header char(8),PRIMARY KEY (spno,class_no),FOREIGN KEY(spno) REFERENCES SPECIALITY(spno)8、TEACHER_COURSE表USE SCGOCREATE TABLE TEACHER_COURSE(tcid smallint,t_no char(8),spno char(8),class_no char(4),cno char(10) not null,semester char(6),schoolyear char(10),PRIMARY KEY (tcid),FOREIGN KEY(t_no) REFERENCES TEACHER(t_no),FOREIGN KEY(spno) REFERENCES SPECIALITY(spno),FOREIGN KEY(cno) REFERENCES COURSE(cno)实验结果截图显示1、DEPARTMENT表2、TEACHER表3、SPECIALITY表4、STUDENT表5、COURSE表6、STUDENT_COURSE表7、CLASS表8、TEACHER_COURSE表题目3、查看各数据表之间的关系，生成数据库关系图。生成数据库关系图截图显示题目4、利用查询分析器修改上述各表。(1)、用INSERT语句向各个表中插入数据录入5 条记录。录入时注意体会外键约束。实现代码及截图：查询分析器执行情况：SQL语句及执行结果显示1、向DEPARTMENT表添加5条记录USE SC GOINSERT INTO DEPARTMENT VALUES (510000,土木学院,薛宝钗 )INSERT INTO DEPARTMENT VALUES (510001,自动化学院,贾宝玉)INSERT INTO DEPARTMENT VALUES (510002,轻化学院,王熙凤)INSERT INTO DEPARTMENT VALUES (510003,计算机学院,林黛玉)INSERT INTO DEPARTMENT VALUES (510004,机电学院,花袭人)实验结果截图显示（插入数据的表格）1、向DEPARTMENT表添加5条记录结果：5行受影响实现代码及截图：查询分析器执行情况：SQL语句及执行结果显示2、TEACHER表INSERT INTO TEACHER VALUES (0001,贾元春,女,1978-02-25,510000,教授)INSERT INTO TEACHER VALUES (0002,贾迎春,女,1982-05-15,510001,海归讲师)INSERT INTO TEACHER VALUES (0003,贾叹春,女,1987-04-07,510002,讲师)INSERT INTO TEACHER VALUES (0004,贾琏,男,1985-07-08,510003,副教授)INSERT INTO TEACHER VALUES (0005,贾政,男,1984-11-23,510004,院士)3、SPECIALITY表USE SC GOINSERT INTO SPECIALITY VALUES (080605,510000,土木工程)INSERT INTO SPECIALITY VALUES (080606,510001,自动化)INSERT INTO SPECIALITY VALUES (080607,510002,制药工程)INSERT INTO SPECIALITY VALUES (080608,510003,计算机科学与技术)INSERT INTO SPECIALITY VALUES (080609,510004,机械电子工程)4、STUDENT表USE SC GOINSERT INTO STUDENT VALUES (5941,李纨,女,1991-05-01,510000,080605,1201)INSERT INTO STUDENT VALUES (5942,香菱,女,1991-07-10,510001,080606,1202)INSERT INTO STUDENT VALUES (5943,贾赦,男,1991-04-08,510002,080607,1203)INSERT INTO STUDENT VALUES (5944,林翠丽,女,1993-03-23,510003,080608,1204)INSERT INTO STUDENT VALUES (5945,晴雯,女,1994-03-26,510004,080609,1205)5、COURSE表USE SC GOINSERT INTO COURSE VALUES (1001,数据库,080605,1,56,18,2,5)INSERT INTO COURSE VALUES (1002,编译原理,080606,2,56,18,2,5)INSERT INTO COURSE VALUES (1003,java程序,080607,3,56,18,2,5)INSERT INTO COURSE VALUES (1004,面向对象,080608,4,56,18,2,5)INSERT INTO COURSE VALUES (1005,信息安全,080609,5,56,18,2,5)6、STUDENT_COURSE表USE SC GOINSERT INTO STUDENT_COURSE VALUES (5941,11,89)INSERT INTO STUDENT_COURSE VALUES (5942,12,50)INSERT INTO STUDENT_COURSE VALUES (5943,13,98)INSERT INTO STUDENT_COURSE VALUES (5944,14,85)INSERT INTO STUDENT_COURSE VALUES (5945,15,64)7、CLASS表USE SC GOINSERT INTO CLASS VALUES (080605,1201,贾巧姐)INSERT INTO CLASS VALUES (080606,1202,秦可卿)INSERT INTO CLASS VALUES (080607,1203,贾惜春)INSERT INTO CLASS VALUES (080608,1204,薛宝琴)INSERT INTO CLASS VALUES (080609,1205,史湘云)8、TEACHER_COURSE表USE SC GOINSERT INTO TEACHER_COURSE VALUES (11,0001,080605,1201,1001,2,3)INSERT INTO TEACHER_COURSE VALUES (12,0002,080606,1202,1002,2,3)INSERT INTO TEACHER_COURSE VALUES (13,0003,080607,1203,1002,2,3)INSERT INTO TEACHER_COURSE VALUES (14,0004,080608,1204,1004,2,3)INSERT INTO TEACHER_COURSE VALUES (15,0005,080609,1205,1005,2,3)实验结果截图显示（插入数据的表格）2、TEACHER表结果：5行受影响3、SPECIALITY表结果：5行受影响4、STUDENT表结果：5行受影响5、COURSE表结果：5行受影响6、STUDENT_COURSE表结果：5行受影响7、CLASS表结果：5行受影响8、TEACHER_COURSE表结果：5行受影响共8组（共8个表）(2)、用UPDATE语句更改student表中数据；实现代码：查询分析器执行情况：SQL语句及执行结果显示：UPDATE STUDENT SET sname=贾兰,sex=男 where s_no=5941;student表更改前后的内容截图显示更改前：更改后：(3)、用DELETE语句删除student表中数据；实现代码：查询分析器执行情况：SQL语句及执行结果显示因为student_course表中的s_no与student表中的s_no外键关联，所以先删除student_course表中的s_no：delete from STUDENT_coursewhere s_no=5942;delete from STUDENTwhere s_no=5942;student表更改前后的内容截图显示五、实验中出现的问题及解决方案1.问题：表的建立顺序解决方案：由于多张表之间有外键联系，所以必须先确定好建表顺序，先把基本表（无外键）的先建好，然后顺藤摸瓜，根据外键关联一直选下去，比如要先建student表，再建student_course表。2.问题：数据的录入时出错解决方案：数据录入的错误一般都是符号的输入错误或结束的分号未加引起，在录入的时候要小心，也有可能是你输入的数据大小的时候没注意，超过了设定大小就会报错。还要注意不同表格中相同字段名的值可能一样。六、思考题1、 说明数据库中的表和数据文件的关系。答：表（table）为数据库中数据存储的基本单位，其数据按行、列存储。每个表具有一表名和列的集合。每一列有一个列名、数据类型、宽度或精度、比例。一行是对应单个记录的列信息的集合。数据文件存放着在数据库中存储的数据，且一个数据库只有一个数据文件。表作为数据库中的数据存储单位，存储在数据文件中。2、 数据库中的日志文件能否单独修改?答：不能，因为日志文件记录了数据库，单独修改可能会造成日志文件与数据库的不一致性，影响工作。3、 在实验中创建表时，应该注意什么？你是如何解决的？答：实验中创建表时，当实验中有多个表且表间存在外键约束时，应该注意创建时先建立被依赖的表，或者最后再添加各表间的外键约束。表间有外键约束时，注意外键之间的数据类型应该是一致的。4、 在向表中插入数据时，应该注意什么？你是如何解决的？答：实验向表中插入数据时，当实验中有多个表且表间存在外键约束时，应该注意先对被依赖的表进行数据插入。注意表中各个属性的约束，例如主键、非空、唯一等约束。5、 数据表中数据的完整性如何控制？答：为了数据表中的属性添加列级或表级约束条件，例如主键、非空、唯一等。如果发现违约行为就拒绝插入数据。实验向表中插入数据时，当实验中有多个表且表间存在外键约束时，应该注意先对被依赖的表进行数据插入。6、 如何对专业表speciality的spname建立唯一性约束，并对新建的约束进行检查。答：语句如下：ALTER TABLE specialityADD CONSTRAINT UQ_spname unique(spname)insert speciality (spno,dno,spname)Values(080608,510003,计算机科学与技术);7、如何对student_course表的score列设置检查约束？如何通过插入新的记录来检验检查约束？4、5两题写出具体的实现过程及语句。答：语句如下：ALTER TABLE STUDENT_COURSEADD CONSTRAINT CK_score check(score between 0 and 100)insert into STUDENT_COURSE(s_no,tcid,score)Values(5942,11,89);8、怎样来实现“允许空值”？设置“允许空值”时应注意哪些问题？答：SQLserver2005中默认属性是“允许空值”，可以添加”notnull”来设置属性不能为空。实验二 查询数据库一、实验目的熟悉SQL语句的基本使用方法，学习如何编写SQL语句来实现查询二、实验内容和要求使用SQL查询分析器查询数据，练习查询语句的使用，掌握SELECT 语句的完整结构，包括简单查询、嵌套查询、连接查询等基本实现方法。三、实验主要仪器设备和材料1计算机及操作系统：PC机，Windows 2000/xp；2数据库管理系统：SQL sever 2000/2005；四、实验方法、步骤及结果测试实验题目：1、对实验一建立的数据库表进行查询简单查询：题目（1）、查询全部学生的学号、姓名、性别和出身年月日。实现代码及查询结果截图：格式如下查询分析器执行情况：SQL语句及执行结果显示SELECT s_no,sname,sex,sbirthdayFROM student结果：4行受影响查询结果截图显示题目（2）、查询全体学生的学号、姓名和年龄实现代码及查询结果截图：SQL语句:SELECT s_no,sname, year(getdate()-Year(sbirthday)FROM student结果：4行受影响查询结果截图显示:题目（3）、查询院系编号为510000（计算机学院）的全部学生的学号、姓名、性别和出身年月日。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示SELECT s_no,sname,sex,sbirthdayFROM student WHERE dno=510000结果：1行受影响查询结果截图显示题目（4）、查询“计算机科学与技术”专业（专业代码为080608）并且班级代码为1204的学生的学号、姓名和出生日期。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示SELECT s_no,sname,sex,sbirthdayFROM student WHERE spno=080608 AND class_no=1204结果：1行受影响查询结果截图显示题目（5）、查询在1988/01/10以后出生的计算机学院（院系编号为510000）学生的学号、姓名、性别和出身年月日。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示SELECT s_no,sname,sex,sbirthdayFROM studentWHERE student.dno=510000 AND student.sbirthday=1988-01-10结果：1行受影响查询结果截图显示题目（6）、查询全部学生的学号、姓名、性别和出身日期结果按照出生日期的升序排列。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示SELECT s_no,sname,sex,sbirthdayFROM studentORDER BY sbirthday ASC结果：4行受影响查询结果截图显示连接查询：题目（1）、查询全部学生的学号、姓名、性别、所在院系名称和专业名称实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示Select student.s_no,student.sname,student.sex,department.dept_name,speciality.spnamefrom student,department,specialitywhere student.dno = department.dno and student.spno=speciality.spno结果：4行受影响查询结果截图显示题目（2）、查询选修了课程13（上课编号）的学生的学号、姓名、专业名称和这门课的成绩实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示Select student.s_no,sname,class_no,tcid,score from student,student_course where student.s_no = student_course.s_no AND student_course.tcid=13结果：1行受影响查询结果截图显示题目（3）、查询学生不及格的情况列出不及格学生的学号、姓名和不及格的课程名称。实现代码及查询结果截图：查询分析器执行情况：由于没有不及格学生，所以查询分数小于65的学生SQL语句及执行结果显示select student.s_no,sname, cname,scorefrom student join student_course on (student.s_no=student_course.s_no ) join course on (student.spno=course.spno)select student.s_no,sname, cnamefrom student join student_course on (student.s_no=student_course.s_no ) join course on (student.spno=course.spno)where score 80 )结果：2行受影响查询结果截图显示2、选用Northwind数据库进行查询题目（1）、对NothWind. Products表进行简单查询；在查询分析器在窗口下用SELECT语句完成单表查询：查询所有Products的详细记录；实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示USE NorthwindGOSELECT * FROM Products结果：77行受 影响查询结果截图显示 查询单价（UnitPrice）小于20的Products；实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示USE NorthwindGOSELECT * FROM ProductsWHERE UnitPrice20结果：8行受影响查询结果截图显示查询Products中单价（UnitPrice）最高的Products的资料；实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示USE NorthwindGOselect * from Productswhere UnitPrice=(select max(UnitPrice)from Products)结果：1行受影响查询结果截图显示5、 实验中出现的问题及解决方案答:在本次实验中，由于只知道出生日期，但其格式是smalldatetime，不知道如何来计算年龄（通过问同学解决了）。一般的查询、连接查询和嵌套查询因为知道其的代码书写格式，没遇到多少问题就解决了。还有，因为实验一已经编了一个数据库，本次实验的内容都是查看实验一插入的数据，一些要求被我做了适当的修改，不过，只是改了相应的一些字段的代码，不影响实验。6、 思考题进行连接查询时应注意哪些问题？答：连接多个表时，可以认为它们被连成了一个表。尽管没有创建一个物理表，SQL引擎创建了很多虚拟表，当连接表时，可以在每个表中选择任何一列。关于连接表的数量问题。连接表的数量取决于具体的数据库管理系统，有的规定为最多25个，有的则没有限制。使用时，需要查看具体的数据库运行环境。但要注意，连接的表越多，响应的时间就越长。进行多表连接时，特别注意不要忘记查询条件，特别是连接多个表且记录数目较多时。因为如果不指明连接条件，系统将对多表进行笛卡尔连接，会产生巨大的记录。根据不同的应用需求，要注意连接方式的选择。实验三、创建和使用视图、索引、存储过程一、实验目的1、掌握视图、索引、存储过程的定义、索引、存储过程的工作原理；2、掌握创建视图、索引、存储过程的SQL语句的用法；并能够熟练利用视图向表中插入、删除和修改数据。3、掌握使用视图来查询数据。二、实验主要仪器设备和材料1计算机及操作系统：PC机，Windows；2数据库管理系统：SQL sever 2000/2005；三、实验方法、步骤及结果测试（一）、视图实验题目：（自己调整格式并书写实验题目，题目见后）1：建立“计算机学院”的学生基本情况视图ies_student_view，该视图包括计算机学院所有学生的学号、姓名、性别、出生年月、专业名称。创建视图的SQL语句及执行结果显示示例：Create viewUSE SCGOCREATE VIEW ies_student_viewAS SELECT s_no,sname,sex,sbirthday,spnameFROM student,specialityWHERE student.dno=510003 AND student.spno=speciality.spno查询视图的SQL语句及执行结果截图示例：SELECT*FROM iec_student_view题目2：执行ies_student_view视图并观察结果。创建视图的SQL语句及执行结果显示USE SCGOSELECT * FROM ies_student_view结果：1行受影响查询视图的SQL语句及执行结果截图SELECT *FROM ies_student_view;3、建立课程11（上课编号）的学生名册的视图，该名册包括学生的学号、姓名、专业名称和这门课的成绩；并查询结果。修改视图的SQL语句及执行结果显示示例：Alter view iec-student_viewUSE SCGOCREATE VIEW tcid1_student_viewASSELECT student.s_no,sname,spname,scoreFROM student,student_course,specialityWHERE student_course.tcid=11AND student.s_no=student_course.s_noAND student.spno=speciality.spno 查询视图的SQL语句及执行结果截图SELECT *FROM tcid1_student_view;4、建立成绩小于65的视图，列出成绩小于65学生的学号、姓名和不及格课程代码；修改视图的SQL语句及执行结果显示示例：Alter view iec-teacher_viewUSE SCGOCREATE VIEW fail_student_viewAS SELECT student.s_no,sname,cnoFROM student,student_course,courseWHERE student.s_no=student_course.s_no AND student_course.score65AND student.spno=course.spno查询视图的SQL语句及执行结果截图SELECT *FROM fail_student_view;5、建立成绩小于65的视图，列出成绩小于65学生的学号、姓名和不及格的课程名称；修改视图的SQL语句及执行结果显示示例：Alter view iec-tercher_view USE SCGOCREATE VIEW fail_student_kcmc_viewAS SELECT student.s_no,sname,cnameFROM student,student_course,courseWHERE student.s_no=student_course.s_no AND student_course.score65AND student.spno=course.spno查询视图的SQL语句及执行结果截图SELECT *FROM fail_student_kcmc_view;6、执行4、5视图并观察结果。修改视图的SQL语句及执行结果显示示例：Alter view iec-tercher_viewSELECT * FROM fail_student_viewSELECT * FROM fail_student_kcmc_view 结果：2行受影响。查询视图的SQL语句及执行结果截图7、修改视图ies_student_view，使该视图包括所有学生的学号、姓名、性别、出生年月、专业名称、院系名称。修改视图的SQL语句及执行结果显示示例：Alter view iec-tercher_view USE SCGOALTER VIEW ies_student_viewAS SELECT s_no,sname,sex,sbirthday,spno,dept_nameFROM student,departmentWHERE student.dno=department.dno查询视图的SQL语句及执行结果截图SELECT *FROM ies_student_view;修改前：修改后：8、 执行ies_student_view视图并观察结果。修改视图的SQL语句及执行结果显示示例：Alter view iec-tercher_viewSELECT * FROM ies_student_view结果：4行受影响 查询视图的SQL语句及执行结果截图SELECT *FROM ies_student_view9、删除视图ies_student_view。并查询结果。删除视图的SQL语句及执行结果显示USE SCGODROP VIEW ies_student_view查询视图SQL语句及执行结果截图SELECT *FROM ies_student_view（二）、索引1在student_course表（学生选课表）的学生学号（sno）列上创建索引sc_sno_index创建索引的SQL语句及执行结果显示示例：Create index.USE SCGOCREATE UNIQUE INDEX sc_sno_indexON student_course(s_no);SQL语句及运行结果截图示例：运行结果可通过执行系统的存储过程查看：2. 在学生表的出生年月列上创建索引stu_birthday_index创建索引的SQL语句及执行结果显示示例：Create index.USE SCGOCREATE UNIQUE INDEX stu_birthday_indexON student(sbirthday);SQL语句及运行结果截图示例：运行结果可通过执行系统的存储过程查看：3. 通过SQL语句查看运行结果创建索引的SQL语句及执行结果显示Sp_helpindex student_courseSp_helpindex studentSQL语句及运行结果截图示例：运行结果可通过执行系统的存储过程查看：Sp_helpindex student_course（三）、存储过程1：创建一个名称为“StuInfo”的存储过程，要求完成以下功能：在student表中查询xxx班学生的学号、姓名、性别、出生日期四个字段的内容。 查询分析器执行情况：SQL语句示例：Create proc list-.USE SCGOCREATE Procedure StuInfoclass_no char(4)ASSELECT s_no,sname,sex,sbirthdayFROM student WHERE student.class_no=class_no执行结果截图显示 2：创建一个存储过程StuScoreInfo，完成的功能是在表student、表course和表student_course中查询以下字段：班级、学号、姓名、性别、课程名称、学生成绩。查询分析器执行情况：SQL语句USE SCGOCREATE Procedure StuScoreInfoASSELECT class_no,student.s_no,sname,sex,cname,scoreFROM student,course,student_courseWHERE student.s_no=student_course.s_noAND student.spno= course.spno执行结果截图显示 3、创建存储过程list_famous_teachers，该存储过程列出SC库中所有年龄在35岁以下的教师的教师号、姓名、所在学院和年龄。查询分析器执行情况：SQL语句USE SCGOCREATE Procedure list_famous_teachersASSELECT t_no,t_name,dno,age=year(getdate()-Year(t_birthday)FROM teacherWHERE year(getdate()-Year(teacher.t_birthday)35执行结果截图显示 4、创建存储过程list_student_department,该存储过程接收学院代码作为输入参数，列出数据库sc中某个院系学生的全部信息。查询分析器执行情况：