广东工业大学数据库原理实验报告.doc

数据库原理实验报告 课程名称_数据库系统_学生学院_计算机学院_专业班级_学 号_学生姓名_ _指导教师_ _2012 年 12 月 3 日实验一 数据库及基本表的建立一、实验目的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、创建数据库：实现代码及截图：查询分析器执行情况：SQL语句及执行结果显示CREATE DATABASE scON PRIMARY( NAME=sc_Data,FILENAME=E:sc_Data.mdf,SIZE=30MB,MAXSIZE=100MB,FILEGROWTH=20%)LOG ON (NAME=sc_Log,FILENAME=E:sc_Log.mdf,SIZE=3MB,MAXSIZE=100MB,FILEGROWTH=1MB)实验结果截图显示题目2、创建基本表创建各表的实现代码及截图：查询分析器执行情况：SQL语句及执行结果显示- - Table structure for class- -DROP TABLE IF EXISTS class;CREATE TABLE class ( spno char(8) NOT NULL, class_no char(4) NOT NULL, header char(8) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;- - Records of class- - - Table structure for course- -DROP TABLE IF EXISTS course;CREATE TABLE course ( cno char(10) NOT NULL, cname char(20) NOT NULL, spno char(8) NOT NULL, ctno tinyint(4) DEFAULT NULL, lecture tinyint(4) DEFAULT NULL, experiment tinyint(4) DEFAULT NULL, semester tinyint(4) DEFAULT NULL, credit tinyint(4) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;- - Records of course- - - Table structure for department- -DROP TABLE IF EXISTS department;CREATE TABLE department ( dno char(6) NOT NULL, dept_name char(20) NOT NULL, header char(8) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;- - Records of department- - - Table structure for speciality- -DROP TABLE IF EXISTS speciality;CREATE TABLE speciality ( spno char(8) NOT NULL, dno char(6) NOT NULL, spname char(20) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;- - Records of speciality- - - Table structure for student- -DROP TABLE IF EXISTS student;CREATE TABLE student ( s_no char(8) NOT NULL, sname char(8) NOT NULL, sex char(2) DEFAULT NULL, sbirthday datetime DEFAULT NULL, dno char(6) NOT NULL, spno char(8) NOT NULL, class_no char(4) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;- - Records of student- - - Table structure for student_course- -DROP TABLE IF EXISTS student_course;CREATE TABLE student_course ( s_no char(8) NOT NULL, tcid smallint(6) NOT NULL, score tinyint(4) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;- - Records of student_course- - - Table structure for teacher- -DROP TABLE IF EXISTS teacher;CREATE TABLE teacher ( t_no char(8) NOT NULL, t_name char(8) NOT NULL, t_sex char(2) DEFAULT NULL, t_birthday datetime DEFAULT NULL, dno char(6) NOT NULL, tech_title char(10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;- - Records of teacher- - - Table structure for teacher_course- -DROP TABLE IF EXISTS teacher_course;CREATE TABLE teacher_course ( tcid smallint(6) NOT NULL, t_no char(8) NOT NULL, spno char(8) NOT NULL, class_no char(4) DEFAULT NULL, cno char(10) NOT NULL, semester char(6) DEFAULT NULL, schoolyear char(10) DEFAULT NULL, PRIMARY KEY (tcid) ENGINE=InnoDB DEFAULT CHARSET=utf8;- - Records of teacher_course- -实验结果截图显示题目3、查看各数据表之间的关系，生成数据库关系图。生成数据库关系图截图显示题目4、利用查询分析器修改上述各表。(1)、用INSERT语句向各个表中插入数据录入5 条记录。录入时注意体会外键约束。实现代码及截图：查询分析器执行情况：SQL语句及执行结果显示INSERT INTO student VALUES ('6079', '陈三', '男', '1991-02-20 20:32:06', '1', '1', '6'); 实验结果截图显示（插入数据的表格）实现代码及截图：查询分析器执行情况：SQL语句及执行结果显示INSERT INTO course VALUES ('1', '计算机网络', '1', '1', '20', '5', '25', '5');实验结果截图显示（插入数据的表格）实现代码及截图：查询分析器执行情况：SQL语句及执行结果显示INSERT INTO student_course VALUES ('6079', '1', '90');实验结果截图显示（插入数据的表格）实现代码及截图：查询分析器执行情况：SQL语句及执行结果显示INSERT INTO teacher VALUES ('1', '廖老师', '男', '1989-01-01 20:41:12', '1', null);实验结果截图显示（插入数据的表格）实现代码及截图：查询分析器执行情况：SQL语句及执行结果显示INSERT INTO department VALUES ('1', '计算机学院', '廖老师');INSERT INTO department VALUES ('2', '信息工程学院', '张三');实验结果截图显示（插入数据的表格）实现代码及截图：查询分析器执行情况：SQL语句及执行结果显示INSERT INTO speciality VALUES ('1', '1', '计算机网络');INSERT INTO speciality VALUES ('2', '1', 'Java');实验结果截图显示（插入数据的表格）实现代码及截图：查询分析器执行情况：SQL语句及执行结果显示INSERT INTO class VALUES ('1', '6', '廖老师');实验结果截图显示（插入数据的表格）实现代码及截图：查询分析器执行情况：SQL语句及执行结果显示INSERT INTO teacher_course VALUES ('1', '1', '1', '6', '1', '2', '2012');实验结果截图显示（插入数据的表格）共8组（共8个表）(2)、用UPDATE语句更改student表中数据；实现代码：查询分析器执行情况：SQL语句及执行结果显示UPDATE studentSET sname='李四'WHERE s_no=6079student表更改前后的内容截图显示(3)、用DELETE语句删除student表中数据；实现代码：查询分析器执行情况：SQL语句及执行结果显示DELETE FROM studentWHERE s_no=6080student表更改前后的内容截图显示五、实验中出现的问题及解决方案1. 外键的设置：外键设置不能一开始就设置，要等到所有表建完后再设置，这样才能保证外键存在。2. 删除操作：不能删除有外键连接的记录，如果要删除这样的记录，必须先把引用该外键的记录删除。六、思考题1、 说明数据库中的表和数据文件的关系。2、 数据库中的日志文件能否单独修改?答：1：数据库中的表其实就是数据文件里面的语句生成的，在数据库中对表的任何操作在数据文件都会发生相应的变化。2：不能。单独修改会造成DBMS跟Log文件的不匹配冲突问题。实验二 查询数据库一、实验目的熟悉SQL语句的基本使用方法，学习如何编写SQL语句来实现查询二、实验内容和要求使用SQL查询分析器查询数据，练习查询语句的使用，掌握SELECT 语句的完整结构，包括简单查询、嵌套查询、连接查询等基本实现方法。三、实验主要仪器设备和材料1计算机及操作系统：PC机，Windows 2000/xp；2数据库管理系统：SQL sever 2000/2005；四、实验方法、步骤及结果测试实验题目：1、对实验一建立的数据库表进行查询简单查询：题目（1）、查询全部学生的学号、姓名、性别和出身年月日。实现代码及查询结果截图：格式如下查询分析器执行情况：SQL语句及执行结果显示SELECT s_no,sname,sex,sbirthdayFROM student查询结果截图显示题目（2）、查询全体学生的学号、姓名和年龄实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示SELECT s_no,sname,2012-year(sbirthday) age FROM student查询结果截图显示题目（3）、查询院系编号为（计算机学院）的全部学生的学号、姓名、性别和出身年月日。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示SELECT s_no,sname,sex,sbirthday,department.dept_nameFROM student,departmentWHERE student.dno=1 AND department.dno=student.dno查询结果截图显示题目（4）、查询“计算机科学与技术”专业（专业代码为）并且班级代码为0201的学生的学号、姓名和出生日期。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示/*由于0201没有数据,所以这里改成1*/SELECT s_no,sname,sbirthday,speciality.spname,class_noFROM student,specialityWHERE student.spno=1 AND peciality.spno=student.spnoAND student.class_no=6查询结果截图显示题目（5）、查询在1983/01/10以后出生的计算机学院（院系编号为）学生的学号、姓名、性别和出身年月日。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示SELECT s_no,sname,sex,sbirthdayFROM studentWHERE sbirthday>000 AND dno=1查询结果截图显示题目（6）、查询全部学生的学号、姓名、性别和出身日期结果按照出生日期的升序排列。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示SELECT s_no,sname,sex,sbirthdayFROM studentORDER BY sbirthday查询结果截图显示连接查询：题目（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（上课编号）的学生的学号、姓名、专业名称和这门课的成绩实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示SELECT student.s_no,sname,spname ,scoreFROM student,student_course,specialityWHERE student.s_no=student_course.s_no AND student.spno=speciality.spnoAND student.spno=1查询结果截图显示题目（3）、查询学生不及格的情况列出不及格学生的学号、姓名和不及格的课程名称。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示/*由于没有不及格的数据,所以显示为空,但是该sql语句亲测可行*/SELECT student.s_no,sname,spname FROM student,student_course,specialityWHERE student.s_no=student_course.s_no AND student.spno=speciality.spnoAND student_course.score<60查询结果截图显示嵌套查询：题目（1）、查询没有选修了课程1的学生，列出学生的学号和姓名。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示/*由于没有不选择课程1的学生数据,所以显示为空,但是该sql语句亲测可行*/SELECT student.s_no,sname FROM studentWHERE s_no IN(SELECT s_noFROM studentWHERE spno!=1)查询结果截图显示题目（2）、查询每门课都是80分以上的学生的学号与姓名。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示SELECT student.s_no,sname FROM studentWHERE s_no IN(SELECT s_noFROM student_courseWHERE score>80)查询结果截图显示2、选用Northwind数据库进行查询题目（1）、对NothWind. Products表进行简单查询；在查询分析器在窗口下用SELECT语句完成单表查询：查询所有Products的详细记录；实现代码及查询结果截图：查询单价（UnitPrice）小于20的Products；实现代码及查询结果截图：查询Products中最高单价（UnitPrice）是多少；实现代码及查询结果截图：题目（2）、在查询分析器在窗口下用SELECT语句完成连接（嵌套）查询： 查询所有被订购过得Products的ProductsID和ProductName；实现代码及查询结果截图：查询所有被CustomerID为AROUT客和订购过的ProductsID和ProductName；实现代码及查询结果截图：查询所有被CustomerID为AROUT客和订购过，且单价在20 以上的ProductsID和ProductName；实现代码及查询结果截图：查询Products中单价（UnitPrice）最高的Products的资料；实现代码及查询结果截图：五、实验中出现的问题及解决方案六、思考题1、 进行连接查询时应注意哪些问题？实验三、创建和使用视图、索引、存储过程一、实验目的1、掌握视图、索引、存储过程的定义、索引、存储过程的工作原理；2、掌握创建视图、索引、存储过程的SQL语句的用法；并能够熟练利用视图向表中插入、删除和修改数据。3、掌握使用视图来查询数据。二、实验主要仪器设备和材料1计算机及操作系统：PC机，Windows；2数据库管理系统：SQL sever 2000/2005；三、实验方法、步骤及结果测试（一）、视图实验题目：（写出实验题目1-8）题目1：建立“计算机学院”的学生基本情况视图ies_student_view，该视图包括计算机学院所有学生的学号、姓名、性别、出生年月、专业名称。创建视图的SQL语句及执行结果显示示例：CREATE VIEW ies_student_viewasSELECT s_no,sname,sex,sbirthday,dept_nameFROM student,departmentWHERE department.dept_name='计算机学院'查询视图的SQL语句及执行结果截图示例：CREATE VIEW ies_student_viewasSELECT s_no,sname,sex,sbirthday,dept_nameFROM student,departmentWHERE department.dept_name='计算机学院'题目2：执行ies_student_view视图并观察结果。创建视图的SQL语句及执行结果显示CREATE VIEW ies_student_viewasSELECT s_no,sname,sex,sbirthday,dept_nameFROM student,departmentWHERE department.dept_name='计算机学院'查询视图的SQL语句及执行结果截图题目3：建立课程1（上课编号）的学生名册的视图，该名册包括学生的学号、姓名、专业名称和这门课的成绩；并查询结果。创建视图的SQL语句及执行结果显示CREATE view ies_course_viewASSELECT student.s_no,sname,spname,scorefrom student,student_course,teacher_course,specialityWHERE student_course.tcid=teacher_course.tcid AND teacher_course.tcid='1' AND student.s_no=student_course.s_noAND speciality.spno=student.spno查询视图的SQL语句及执行结果截图题目4+题目5：建立统计不及格情况的视图，列出不及格学生的学号、姓名及不及格的课程代码。创建视图的SQL语句及执行结果显示CREATE VIEW ies_faile_viewasSELECT student.s_no,sname,tcidfrom student,student_courseWHERE score<'60' AND student.s_no=student_course.s_no查询视图的SQL语句及执行结果截图由于本来没有不及格的数据，所以临时修改了一下student_course表如下：所以视图结果为：题目6+题目7：修改视图ies_student_view，使该视图包括所有学生的学号、姓名、性别、出生年月、学院名称、专业名称。创建视图的SQL语句及执行结果显示CREATE OR REPLACE VIEW ies_student_view ASSELECT student.s_no,sname,sex,sbirthday,department.dept_name,speciality.spnameFROM student,speciality,departmentWHERE student.dno=department.dno AND student.spno=speciality.spno查询视图的SQL语句及执行结果截图题目8：删除视图ies_student_view。并查询结果。删除视图的SQL语句及执行结果显示DROP VIEW ies_student_view运行后可看到没有该视图显示查询视图SQL语句及执行结果截图SELECT * FROM ies_student_view（通过查询语句检查该视图，系统会返回该视图不存在的信息）（二）、索引题目（1）在student_course表（学生选课表）的学生学号（s_no）列上创建索引sc_sno_index创建索引的SQL语句及执行结果显示示例：CREATE INDEX sc_sno_indexON student_course (s_no)SQL语句及运行结果截图示例：运行结果可通过执行系统的存储过程查看：Sp_helpindex student_course（三）、存储过程题目（1）创建存储过程list_student_department，该存储过程接收学院代码作为输入参数，列出数据库sc中某个院系学生的全部信息。查询分析器执行情况：SQL语句示例：CREATE PROCEDURE list_student_department(InType CHAR)BEGINSELECT *FROM studentWHERE dno=InType;END执行结果截图显示示例：题目（2）使用SQL语句执行存储过程list_student_department。查询分析器执行情况：SQL语句示例：call list_student_department('1')（由于本人用的是Navicat，所以执行语句跟查询分析器不一样，希望老师谅解）执行结果截图显示题目（3） 将某班选修某门课程的所有学生的学号和上课编号插入到student_course表中：查询分析器执行情况：SQL语句CREATE PROCEDURE insert_student_course(s_no CHAR(8),tcid SMALLINT)BEGININSERT INTO student_course(s_no,tcid)VALUES (s_no,tcid);END执行结果截图显示题目（4）使用SQL语句执行存储过程。查询分析器执行情况：SQL语句CALL insert_student_course(6079,5)（由于本人用的是Navicat，所以执行语句跟查询分析器不一样，希望老师谅解）执行结果截图显示题目（5）使用SQL语句查看存储过程代码。查询分析器执行情况：SQL语句（由于发现Navicat使用sp_helptext会报错，然后查了很多资料都找不到，所以这里不知道怎么实现）执行结果截图显示题目（6）使用SQL语句修改存储过程。查询分析器执行情况：SQL语句DROP PROCEDURE list_student_departmentCREATE PROCEDURE list_student_department(InType CHAR)BEGINSELECT student.s_no,sname,sbirthday,department.dept_name,speciality.spnameFROM student,department,specialityWHERE student.dno=InType and department.dno=student.dno AND speciality.spno=student.spno;END执行结果截图显示题目（7）使用SQL语句删除存储过程。查询分析器执行情况：SQL语句DROP PROCEDURE list_student_department执行结果截图显示四、实验中出现的问题及解决方案1.由于本人之前做过其他开发项目，接触过Navicat，所以突发奇想不用企业管理器而尝试改用Navicat实现这份实验。不过发现原来有不少地方是不一样的，例如那个sp_helptext、exec等等，这些语句在Navicat里面是识别不了的，所以也就影响到报告的结果了。2.由于设计的数据数只有两条，所以数据范围有限，对某些实验结果的表现不明显。3.视图和索引还有前面的查找都不难，最难的是数据存储，而又因为Navicat的数据存储不叫数据存储，叫函数，所以很多地方都不一样，因此这块花了不少时间去找资料理解消化了。五、思考题1、视图更新的优点A能简化用户操作；B对用户透明，更加安全；C视图对数据库重构提供了一定程度的逻辑独立性，个人觉得有点面向对象的思想在里面；2、哪些视图是可以更新的？哪些视图是不可以更新的？针对实验举例说明。行列子集视图时可更新的，除行列子集视图外，理论上有些视图也是可以更新的，但他们的确切特征还是尚待研究的课题。而其他类似聚集函数、表达式，这些视图是不可以更新的。在上述实验中，ies_student_view视图时可以更新的，其视图更新既是对student表的更新。视图ies_faile_course是由基本表组成的，此视图不能更新。