1、数据库原理实验报告学 院 计算机学院 专 业 计算机科学与技术 班 级 学 号 姓 名 指导教师 胡欣如 2014 年 12 月 24 日实验_一_题目_ 数据库及基本表的建立 实验_二_题目_ 查询数据库 _ 实验_三_题目_创建和使用视图、索引、存储过程 实验_四_题目_ 小型数据库规划设计_ 实验平台：MS SQL sever 2005实验一 数据库及基本表的建立一、实验目的1、掌握 SQL SERVER 的查询分析器和企业管理器的使用；2、掌握创建数据库和表的操作；二、实验内容和要求1、分别 使用 SQL 语句、企业管理器（Enterprise Manager）创建数据库；2、使用 S
2、QL 语句、企业管理器（Enterprise Manager）创建数据库表；三、实验主要仪器设备和材料1计算机及操作系统：PC 机，Windows 2000/xp；2数据库管理系统：SQL sever 2000/2005；四、实验方法、步骤及结果测试创建一个教学管理数据库 SC，其描述的信息有：学生信息、课程信息、教师信息、学生选课成绩、授课信息、班级信息、系部信息、专业信息。创建：student 表( 学生信息表)、course 表（课程信息表) 、teacher 表（教师信息表)、student _course 表（学生选课成绩表)、teacher_course 表（教师上课课表）等。题目
3、 1、创建数据库：1、创建数据库：确定数据库名称；数据库用于学生管理，命名为 SC确定数据库的位置；要求：数据文件和日志文件分别存储在 E 盘自己的目录下。确定数据库的大小；根据实际的数据量确定数据文件的初始大小为 30MB，日志文件的初始大小为3MB。确定数据库的增长；根据实际情况，确定数据文件按 20%增长，日志文件按 1MB 增长。实现代码及截图：创建数据库 SQL 语言CREATE DATABASE SC ON(NAME = NSC_Data,FILENAME = NG:/SC_Data.MDF,SIZE = 30,FILEGROWTH = 20%)LOG ON (NAME = NSC
4、_Log,实验结果截图显示FILENAME = NG:/SC_Log.LDF,SIZE = 3,FILEGROWTH = 1)题目 2、创建基本表创建各表的实现代码及截图：查询分析器执行情况：SQL 语句及执行结果显示1.创建 student 表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),class_no char(4),constraint fk_student_dno fore
5、ign key(dno) referencesdepartment(dno),constraint fk_student_spnoforeign key(spno) referencesspeciality(spno)2.创建 course 表CREATE TABLE course(cno char(10) primary key,cname char(20) not null,spno char(8) ,实验结果截图显示ctno tinyint ,lecture tinyint ,experiment tinyint ,semester tinyint ,credit tinyint ,co
6、nstraint fk_course_spno foreign key(spno) references speciality(spno)3.创建 student_course 表CREATE TABLE student_course(s_no char(8) ,tcid smallint,score tinyint ,constraint pk_student_course primary key(s_no,tcid),constraint fk_student_course_s_no foreign key(s_no)references student(s_no) on delete c
7、ascade)4.创建 teacher 表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) ,tech_title char(10),constraint fk_teacher_dnoforeign key(dno) references department(dno)5.创建 department 表CREATE TABLE department (dno char(6) primary key,dep
8、t_name char(20) not null,header char(8)6.创建 speciality 表CREATE TABLE speciality(spno char(8) primary key,dno char(6) not null ,spname char(20) not null,constraint fk_speciality_dno foreign key(dno) references department(dno)7.创建 teacher_courseCREATE TABLE teacher_course(tcid smallint primary key,t_n
9、o char(8) ,spno char(8) ,class_no char(4) ,cno char(10),semester char(6) not null,shcoolyear char(10),constraint fk_teacher_course_t_no foreign key(t_no) references teacher(t_no),constraint fk_teacher_course_spno foreign key(spno) references speciality(spno),constraint fk_teacher_course_cno foreign
10、key(cno) references course(cno)8.创建 class 表CREATE TABLE class(spno char(8) ,class_no char(4) ,header char(8) , constraint pk_class primary key (spno,class_no),constraint fk_class_spno foreign key(spno) references speciality(spno)题目 3、查看各数据表之间的关系，生成数据库关系图。题目 4、 利用查询分析器修改上述各表。(1)、用 INSERT 语句向各个表 中插入数据
11、录入 5 条记录。录入时注意体会外键约束。实现代码及截图：查询分析器执行情况：SQL 语句及执行结果显示1.插入 department 表数据INSERT INTOdepartment(dno,dept_name,header) values(xy01,计算机学院 ,张小辉)INSERT INTO department(dno,dept_name,header) values(xy02,机电学院 ,刘小华)INSERT INTO department(dno,dept_name,header)values(xy03,信息工程学院 ,梁小伟)INSERT INTO department(dno,
12、dept_name,header) values(xy04,外国语学院 ,彭小彦)INSERT INTO 实验结果截图显示（插入数据的表格）department(dno,dept_name,header) values(xy05,应用数学学院 ,吴小祖)2.插入 speciality 表数据INSERT INTO speciality(spno,dno,spname)values(zy01001,xy01,网络工程)INSERT INTO speciality(spno,dno,spname) values(zy01002,xy01,软件工程)INSERT INTO speciality(sp
13、no,dno,spname) values(zy01003,xy01,计算机科学与技术)INSERT INTO speciality(spno,dno,spname) values(zy03001,xy03,通信工程)INSERT INTO speciality(spno,dno,spname) values(zy04001,xy04,国际商务)3.插入 class 表数据INSERT INTO class(spno,class_no,header) values(zy01001,1001,黄小勃)INSERT INTO class(spno,class_no,header) values(z
14、y01001,1002,周小伦)INSERT INTO class(spno,class_no,header) values(zy01002,1001,王小宏)INSERT INTO class(spno,class_no,header) values(zy03001,1001,陈小迅)INSERT INTO class(spno,class_no,header) values(zy04001,1001,萧小腾)4.插入 student 表数据INSERT INTO student(s_no,sname,sex,sbirthday,dno,spno,class_no) values(30020
15、01,王子昂,男 ,1994-08-04,xy01,zy01001,1001)INSERT INTO student(s_no,sname,sex,sbirthday,dno,spno,class_no) values(3002002,成小龙,男 ,1994-11-08,xy01,zy01001,1002)INSERT INTO student(s_no,sname,sex,sbirthday,dno,spno,class_no) values(3002003,朴小惠,女 ,1995-08-16,xy01,zy01002,1001)INSERT INTO student(s_no,sname,
16、sex,sbirthday,dno,spno,class_no) values(3002004,李小镐,男 ,1995-12-12,xy03,zy03001,1001)INSERT INTO student(s_no,sname,sex,sbirthday,dno,spno,class_no) values(3002005,金小贤,女 ,1993-06-10,xy04,zy04001,1001)5.插入 course 表数据INSERT INTO course(cno,cname,spno,ctno,lecture,experiment,semester,credit) values(kc00
17、1,数据结构,zy01001,10,45,10,1,4)INSERT INTO course(cno,cname,spno,ctno,lecture,experiment,semester,credit) values(kc002,数据库原理,zy01001,14,48,12,2,4)INSERT INTO course(cno,cname,spno,ctno,lecture,experiment,semester,credit) values(kc003,计算机组成原理,zy01001,16,36,8,2,3)INSERT INTO course(cno,cname,spno,ctno,le
18、cture,experiment,semester,credit) values(kc009,信号与系统,zy03001,15,40,12,1,5)INSERT INTO course(cno,cname,spno,ctno,lecture,experiment,semester,credit) values(kc015,商务翻译,zy04001,18,28,10,2,2)6.插入 student_course 表数据INSERT INTO student_course(s_no,tcid,score) values(3002001,101 , 80)INSERT INTO student_c
19、ourse(s_no,tcid,score) values(3002002,102 , 90)INSERT INTO student_course(s_no,tcid,score) values(3002003,101 , 85)INSERT INTO student_course(s_no,tcid,score) values(3002004,102 , 75)INSERT INTO student_course(s_no,tcid,score) values(3002005,101 , 88)7.插入 teacher 表数据INSERT INTO teacher(t_no,t_name,t
20、_sex,t_birthday,dno,tech_title) values(2002001,郭小洁, 男 ,1976-05-10,xy01,教授)INSERT INTO teacher(t_no,t_name,t_sex,t_birthday,dno,tech_title) values(2002002,杨小米, 男 ,1968-11-17,xy01,副教授)INSERT INTO teacher(t_no,t_name,t_sex,t_birthday,dno,tech_title) values(2002003,柯小腾, 男 ,1965-08-15,xy01,博士)INSERT INTO
21、 teacher(t_no,t_name,t_sex,t_birthday,dno,tech_title) values(2002004,余小乐, 女 ,1978-10-10,xy02,副教授)INSERT INTO teacher(t_no,t_name,t_sex,t_birthday,dno,tech_title) values(2002005,郑小红, 女 ,1990-04-18,xy03,讲师)8.插入 teacher_course 表数据INSERT INTO teacher_course(tcid,t_no,spno,class_no,cno,semester,shcoolyea
22、r) values(101,2002001,zy01001,1001,kc001,5,3)INSERT INTO teacher_course(tcid,t_no,spno,class_no,cno,semester,shcoolyear) values(102,2002001,zy01001,1001,kc002,3,2)INSERT INTO teacher_course(tcid,t_no,spno,class_no,cno,semester,shcoolyear) values(103,2002002,zy01002,1001,kc002,3,2)INSERT INTO teacher
23、_course(tcid,t_no,spno,class_no,cno,semester,shcoolyear) values(104,2002003,zy01003,1002,kc003,6,3)INSERT INTO teacher_course(tcid,t_no,spno,class_no,cno,semester,shcoolyear) values(105,2002004,zy03001,1001,kc009,2,1)共 8 组（共 8 个表）(2)、用 UPDATE 语句更改 student 表中数据；实现代码：查询分析器执行情况：SQL 语句及执行结果显示UPDATE stud
24、ent SET sex=男 WHERE s_no=3002005student 表更改前后的内容截图显示(3)、用 DELETE 语句删除 student 表中数据；实现代码：查询分析器执行情况：SQL 语句及执行结果显示INSERT INTO student(s_no,sname,sex,sbirthday,dno,spno,class_no) values(3002008,王小昂,男 ,1994-08-04,xy01,zy01001,1001)DELETE FROM student WHERE s_no=3002008student 表更改前后的内容截图显示5、实验中出现的问题及解决方案1
25、.插入数据时没有注意到数据的长度，导致报错。解决方法：认真核对数据，重新建立数据库，重新插入数据。2.建这 8 个表的先后顺序问题。由于主外键的约束，建表的顺序不能随便调乱，要先理清了各表的关系后，再按照顺序建表。六、思考题1、 说明数据库中的表和数据文件的关系。答：表（table）为数据库中数据存储的基本单位，其数据按行、列存储。每个表具有一表名和列的集合。每一列有一个列名、数据类型、宽度或精度、比例。一行是对应单个记录的列信息的集合。数据文件存放着在数据库中存储的数据，且一个数据库只有一个数据文件。表作为数据库中的数据存储单位，存储在数据文件中。2、 数据库中的日志文件能否单独修改?答：不
26、能，因为日志文件记录了数据库，单独修改可能会造成日志文件与数据库的不一致性，影响工作。3、 在实验中创建表时，应该注意什么？你是如何解决的？答：应该注意主键与外键的关系，按照顺序建表。实验中创建表时，当实验中有多个表且表间存在外键约束时，应该注意创建时先建立被依赖的表，或者最后再添加各表间的外键约束。表间有外键约束时，注意外键之间的数据类型应该是一致的。4、 在向表中插入数据时，应该注意什么？你是如何解决的？答：应注意每一个数据的类型，长度。按照建表时每个数据的类型来插入数据。实验向表中插入数据时，当实验中有多个表且表间存在外键约束时，应该注意先对被依赖的表进行数据插入。注意表中各个属性的约束
27、，例如主键、非空、唯一等约束。5、 数据表中数据的完整性如何控制？答：为了数据表中的属性添加列级或表级约束条件，例如主键、非空、唯一等。如果发现违约行为就拒绝插入数据。实验向表中插入数据时，当实验中有多个表且表间存在外键约束时，应该注意先对被依赖的表进行数据插入。6、 如何对专业表 speciality 的 spname 建立唯一性约束，并对新建的约束进行检查。7、 如何对 student_course 表的 score 列设置检查约束？如何通过插入新的记录来检验检查约束？8、 怎样来实现“允许空值”？设置“允许空值”时应注意哪些问题？答：SQL sever 2005 默认为允许空值，可以通过
28、 NOT NULL 语句来设定不为空实验二、查询数据库实验项目名称：使用 SQL 语言查询管理数据实验项目性质：基础型所属课程名称：数据库原理实验计划学时：2一、实验目的熟悉 SQL 语句的基本使用方法，学习如何编写 SQL 语句来实现查询1、掌握基本的 SELECT 查询及其相关子句的使用；2、掌握复杂的 SELECT 查询，如多表查询、子查询、连接查询和嵌套查询。二、实验内容和要求使用 SQL 查询分析器查询数据，练习查询语句的使用，掌握 SELECT 语句的完整结构，包括简单查询、嵌套查询、连接查询等基本实现方法，掌握存储查询结果的方法，体会各种查询的异同及相互之间的转换，体会各种查询的
29、执行过程，为简单综合应用打下良好的基础。三、实验主要仪器设备和材料1计算机及操作系统：PC 机，Windows 2000/xp；2数据库管理系统：SQL sever 2000/2003/2005；四、实验方法、步骤及结果测试要求用 SELECT 完成以下查询， 1、对实验一建立的数据库表进行查询简单查询：（1）、查询全部学生的学号、姓名、性别和出身年月日。实现代码及查询结果截图：格式如下查询分析器执行情况：SQL 语句及执行结果显示SELECT s_no 学号,sname 姓名,sbirthday 出身年月FROM student查询结果截图显示（2）、查询全体学生的学号、姓名和年龄实现代码及
30、查询结果截图：查询分析器执行情况：SQL 语句及执行结果显示SELECT s_no 学号,sname 姓名,year(getdate()-year(sbirthday) 年龄FROM student查询结果截图显示（3）、查询院系编号为 xy01（计算机学院）的全部学生的学号、姓名、性别和出身年月日。实现代码及查询结果截图：查询分析器执行情况：SQL 语句及执行结果显示SELECT s_no 学号,sname 姓名,sex 性别,sbirthday 出身年月FROM student查询结果截图显示WHERE dno=xy01（4）、查询 “网络工程”专业（ 专业代码为zy01001）并且班级代
31、码为1001的学生的学号、姓名和出生日期。实现代码及查询结果截图：查询分析器执行情况：SQL 语句及执行结果显示SELECT s_no 学号,sname 姓名,sbirthday 出身年月FROM studentWHERE spno=zy01001查询结果截图显示（5） 、查询在1988-05-09以后出生的计算机学院（院系编号为100001）学生的学号、姓名、性别和出身年月日。实现代码及查询结果截图：查询分析器执行情况：SQL 语句及执行结果显示SELECT s_no 学号,sname 姓名,sex 性别,sbirthday 出身年月FROM studentWHERE dno=xy01AND
32、 sbirthday 1988-05-09查询结果截图显示（6）、查询全部学生的学号、姓名、性别和出身日期结果按照出生日期的升序排列。实现代码及查询结果截图：查询分析器执行情况：SQL 语句及执行结果显示SELECT s_no 学号,sname 姓名,sex 性别,sbirthday 出身年月FROM studentorder by sbirthday查询结果截图显示连接查询：（1）、查询全部学生的学号、姓名、性别、所在院系名称和专业名称实现代码及查询结果截图：查询分析器执行情况：SQL 语句及执行结果显示SELECT s_no 学号,sname 姓名,sex 性别,department.de
33、pt_name 院校, speciality.spname 专业FROM student,department,speciality查询结果截图显示WHERE student.dno = department.dnoAND student.spno = speciality.spno;（2）、 查询选修了课程 101（上课编号）的学生的学号、姓名、专业名称和这门课的成绩实现代码及查询结果截图：查询分析器执行情况：SQL 语句及执行结果显示SELECT student.s_no 学号,sname 姓名,speciality.spname 专业, score 成绩FROM student,stud
34、ent_course,specialityWHERE student.s_no = student_course.s_noAND student.spno = speciality.spno AND student_course.tcid = 101;查询结果截图显示（3）、查询学生不及格的情况列出不及格学生的学号、姓名和不及格的课程名称。实现代码及查询结果截图：查询分析器执行情况：SQL 语句及执行结果显示SELECT student.s_no 学号,student.sname 姓名,ame 不及格课程FROM student,student_course,course,teacher_co
35、urseWHERE student.s_no = student_course.s_no AND teacher_course.tcid=student_course.tcid AND o=teacher_o AND score60;查询结果截图显示没有不及格课程嵌套查询：（1）、查询没有选修了课程102的学生，列出学生的学号和姓名。实现代码及查询结果截图：查询分析器执行情况：SQL 语句及执行结果显示SELECT s_no 学号,sname 姓名FROM studentWHERE s_no not in( SELECT s_noFROM student_courseWHERE tcid =
36、102)查询结果截图显示（2）、 查询每门课都是 80分以上的学生的学号与姓名。实现代码及查询结果截图：查询分析器执行情况：SQL 语句及执行结果显示SELECT s_no 学号,sname 姓名FROM studentWHERE s_no not in( SELECT s_noFROM student_courseWHERE score = 80)查询结果截图显示2、选用 Northwind 数据库进行查询（1） 、对 NothWind. Products 表进行简单查询；在查询分析器在窗口下用 SELECT 语句完成单表查询：查询所有 Products 的详细记录；实现代码及查询结果截图：
37、查询分析器执行情况：SQL 语句及执行结果显示SELECT * FROM Products查询结果截图显示（有所省略）查询单价（UnitPrice）小于 20 的 Products；实现代码及查询结果截图：查询分析器执行情况：SQL 语句及执行结果显示SELECT * FROM Products WHERE UnitPrice 查询结果截图显示（有所省略）20;查询 Products 中最高单价（UnitPrice）是多少；实现代码及查询结果截图：查询分析器执行情况：SQL 语句及执行结果显示SELECT MAX(UnitPrice) 最高单价FROM Products ;查询结果截图显示（2） 、在查询分析器在窗口下用 SELECT 语句完成连接（嵌套）查询：查询所有被订购过的 Products 的 ProductsID 和 ProductName；实现代码及查询结果截图：
