数据库实验1~5

1、.数据库原理实验报告 实验_一_题目_ 数据库及基本表的建立_ 实验_二_题目_ 查询数据库 _ 实验_三_题目_创建和使用视图，索引，存储过程 实验_四_题目_ 小型数据库规划设计 _ 学 院 计算机学院 专 业 计算机科学与技术 班 级 级 班 学 号 姓 名 指导教师 （2012年 12 月） 计算机 学院 计算机科学与技术 专业 班 学号： 姓名： 协作者：_ 教师评定： 实验题目 数据库及基本表的建立 一、实验目的1、掌握SQL SERVER的查询分析器和企业管理器的使用；2、掌握创建数据库和表的操作；二、实验内容和要求1、分别使用SQL语句、企业管理器（Enterprise Man

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

3、教师上课课表）等。1、创建数据库：确定数据库名称；数据库用于学生管理，命名为SC确定数据库的位置；要求：数据文件和日志文件分别存储在E盘自己的目录下。确定数据库的大小；根据实际的数据量确定数据文件的初始大小为30MB，日志文件的初始大小为3MB。确定数据库的增长；根据实际情况，确定数据文件按20%增长，日志文件按1MB增长。（1）、利用查询分析器（Query Analyzer），使用SQL语句指定参数创建数据库；实现代码及截图：SQL语句:CREATE DATABASE SCON(NAME=SC_DAT,E:SC.mdf,SIZE=30MB,)LOG ON(NAME=SC_LOG,E:SC.l

4、df,SIZE=2MB,=1MB)查询分析器执行情况：SQL语句及执行结果截图显示 2、创建基本表利用查询分析器，使用SQL语句方式创建方式将下面各表建立到教学管理数据库中。(1) 创建student表字段名代码类型约束学号s_nochar(8)主键姓名snamechar(8)非空性别sexchar(2) 出生日期sbirthdaySmalldatetime学生所在院系编号dnochar(6) 外键专业代码spnochar(8)外键班级编码class_nochar(4) (2)创建Course表字段名代码类型约束课程编号cnochar(10)主键课程名称cnamechar(20)非空专业代码s

5、pnochar(8)外键课程类型编号ctnotinyint理论学时lecturetinyint实验学时experimenttinyint开课学期semestertinyint课程学分credittinyint（3）创建student _course表字段名代码类型约束学号s_nochar(8)主键,与student表中s_no 外键关联,级联删除上课编号tcidsmallint主键学生成绩score tinyint (4)创建teacher表 字段名代码类型约束教师编号t_nochar(8)主键教师姓名t_namechar(8)非空性别t_sexchar(2)出生日期t_birthdaysma

6、lldatetime教师所在院系编号dnochar(6) 外键职称tech_titlechar(10)(5)创建系部表(department) 字段名代码类型约束院系编号dnochar(6)主键院系名称dept_namechar(20)非空院系负责人headerchar(8) (6)创建专业信息表(speciality) 字段名代码类型约束专业代码spnochar(8)主键院系编号dnochar(6)外键,非空专业名称spnamechar(20)非空(7)创建teacher_course表字段名代码类型约束上课编号tcidsmallint主键教师编号t_nochar(8)外键专业代码spnoc

7、har(8)外键班级编码class_nochar(4)课程编号cnochar(10)非空,外键学期semesterChar(6)学年schoolyear Char(10)(8)创建班级表(class) 字段名代码类型约束专业代码spnochar(8)主键，与 speciality 表中spno 外键关联,班级编码class_nochar(4)主键,班负责人headerchar(8)3、 查看各数据表之间的关系，生成数据库关系图。生成数据库关系图截图显示:4、创建各表的实现代码及截图：创建department 表：USE SCcreate table department(dno char(6)

8、,dept_name char(20) not null,header char(8),PRIMARY KEY (dno)创建speciality表：USE SCcreate table speciality(spno char(8) primary key,dno char(6) not null,spname char(20) not null,foreign key (dno) references department(dno);创建teacher表：USE SCcreate table teacher(t_no char(8) primary key,t_name char(8) n

9、ot null,t_sex char(2),t_birthday smalldatetime,dno char(6),tech_title char(10),foreign key (dno) references department(dno)创建course表：USE SCCREATE 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

10、 (cno),foreign key (spno) references speciality(spno)创建teacher_course表：USE SCcreate 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 (spn

11、o) references speciality(spno),foreign key (cno) references course(cno)创建student表：USE SCcreate 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),foreign key (dno) references department(dno),foreign key (spno) r

12、eferences speciality(spno)创建student_course表：USE SCcreate table student_course(s_no char(8),tcid smallint,score tinyint,primary key (s_no,tcid),foreign key (s_no) references student(s_no)创建class表：USE SCcreate table class(spno char(8),class_no char(4),header char(8),primary key (class_no,spno),foreign

13、 key (spno) references speciality(spno)创建department 表：创建speciality表：创建teacher表：创建course表：创建teacher_course表：创建student表：创建student_course表：创建class表：5、利用查询分析器修改上述各表。(1)、用INSERT语句向各个表中插入数据录入5 条记录。录入时注意体会外键约束。实现代码及截图：查询分析器执行情况：SQL语句及执行结果显向department表添加5条记录USE SCinsert into department values (01,计算机学院,张明),

14、(02,财能学院,李清),(03,信息工程学院,陈东),(04,自动化学院,刘平芳),(05,外国语学院,李盛龙);向speciality表添加5条记录USE SCinsert into speciality values(spt001,01,计算机科学与技术),(spt002,02,热能与动力工程),(spt003,03,信息工程),(spt004,04,机械自动化),(spt005,05,外语与外贸);向teacher表添加5条记录USE SCinsert into teacher values(1001,陈一新,男,1985-7-23,02,教授),(1002,吴杰,男,1981-3-6

15、,04,讲师),(1003,秦丽红,女,1987-11-6,03,辅导员),(1004,陈惠英,女,1985-5-3,05,教授),(1005,苏明,男,1789-8-24,01,博士生导师);向course表添加5条记录USE SCinsert into course values(cr001,数据结构,spt001,001,72,28,1,5),(cr002,热力学基础,spt002,002,72,36,1,5),(cr003,信息安全概论,spt003,003,68,28,2,4),(cr004,机械自动化,spt004,004,72,28,1,5),(cr005,外贸学,spt005,

16、005,70,18,2,4);向teacher_course表添加5条记录USE SCinsert into teacher_course values(01,1005,spt001,1001,cr001,1,1),(02,1001,spt002,2002,cr002,2,1),(03,1003,spt003,3003,cr003,2,1),(04,1002,spt004,4004,cr004,2,1),(05,1004,spt005,5005,cr005,1,1);向student表添加5条记录USE SC insert into student values(s01,张炎,男,1990-1

17、2-3,01,spt001,1001),(s02,蓝易空,男,1991-3-5,02,spt002,2002),(s03,明成旭,男,1990-5-30,03,spt003,3003),(s04,刘丹,女,1992-7-27,04,spt004,4004),(s05,曾文辉,男,1991-6-13,05,spt005,5005);向student_course表添加5条记录USE SC insert into student_course values(s01,01,87),(s02,02,89),(s03,03,76),(s04,04,97),(s05,05,90);向class表添加5条记

18、录USE SC insert into class values(spt001,1001,李勇健),(spt002,2002,黄开明),(spt003,3003,方德城),(spt004,4004,邱润),(spt005,5005,廖玲);实验结果截图显示（插入数据的表格）向department表添加5条记录向speciality表添加5条记录向teacher表添加5条记录向course表添加5条记录向teacher_course表添加5条记录向student表添加5条记录向student_course表添加5条记录向class表添加5条记录共8组（共8个表）(2)、用UPDATE语句更改st

19、udent表中数据；实现代码：USE SC update student set class_no=0702 where s_no=s02update student set class_no=0801 where s_no=s01update student set class_no=0903 where s_no=s03update student set class_no=1005 where s_no=s05update student set class_no=1104 where s_no=s04student表更改前的内容截图显示查询分析student表更改后的内容截图显示(3)

20、、用DELETE语句删除student表中数据；实现代码：USE SC delete from student where s_no=s05;student表更改前的内容截图显示查询分析student表更改后的内容截图显示五、实验中出现的问题及解决方案1.创建表的时候字打错了，后用update语句纠正过来。2.删除表中数据的时候因为没有级联而出错，后通过界面操作级联了两个表解决了该问题。3.在一次插入数据时因为没看清楚约束条件而插入非法数据面出错，后通过纠正数据格式而解决此问题。插入数据时一定要看好约束的条件，不然很容易出错。六、思考题1、 说明数据库中的表和数据文件的关系。答：表（table

21、）为数据库中数据存储的基本单位，其数据按行、列存储。每个表具有一表名和列的集合。每一列有一个列名、数据类型、宽度或精度、比例。一行是对应单个记录的列信息的集合。数据文件存放着在数据库中存储的数据，且一个数据库只有一个数据文件。表作为数据库中的数据存储单位，存储在数据文件中。2、 数据库中的日志文件能否单独修改?答：可以。把对数据的修改写到数据库中和把表示这个修改的日子记录写到日志文件中是两个不同的操作。有可能在这两个操作之间发生某种故障，即这两个操作只完成了其中一个。所以，可以单独修改数据库中的日志文件而不一定会修改数据。 计算机 学院 计算机科学与技术 专业 班 学号： 姓名： 协作者：_

22、教师评定： 实验题目 查询数据库 一、实验目的熟悉SQL语句的基本使用方法，学习如何编写SQL语句来实现查询1、掌握基本的SELECT查询及其相关子句的使用；2、掌握复杂的SELECT查询，如多表查询、子查询、连接查询和嵌套查询。二、实验内容和要求使用SQL查询分析器查询数据，练习查询语句的使用，掌握SELECT 语句的完整结构，包括简单查询、嵌套查询、连接查询等基本实现方法，掌握存储查询结果的方法，体会各种查询的异同及相互之间的转换，体会各种查询的执行过程，为简单综合应用打下良好的基础。三、实验主要仪器设备和材料1计算机及操作系统：PC机，Windows 2000/xp；2数据库管理系统：S

23、QL sever 2000/2003/2005；四、实验方法、步骤及结果测试要求用SELECT完成以下查询， 1、对实验一建立的数据库表进行查询简单查询：（1）、查询全部学生的学号、姓名、性别和出身年月日。实现代码及查询结果截图：格式如下查询分析器执行情况：SQL语句及执行结果显示USE SCselect s_no,sname,sex,sbirthdayfrom student查询结果截图显示（2） 、查询全体学生的学号、姓名和年龄use SCselect s_no,sname,sage:,year(getdate()-Year(sbirthday)from student ;实现代码及查询结

24、果截图：（3）、查询院系编号为01（计算机学院）的全部学生的学号、姓名、性别和出身年月日。实现代码及查询结果截图：USE SCselect s_no,sname,sex,sbirthdayfrom student where dno=01（4）、查询“计算机科学与技术”专业（专业代码为spt001）并且班级代码为1001的学生的学号、姓名和出生日期。实现代码及查询结果截图：USE SCselect s_no,sname,sex,sbirthdayfrom student where spno=spt001 and class_no=1001（5）、查询在1990/12/10以后出生的计算机学院

25、（院系编号为01）学生的学号、姓名、性别和出身年月日。实现代码及查询结果截图：USE SCselect s_no,sname,sex,sbirthdayfrom studentwhere student.dno=01 and student.sbirthday=1990-12-10（6）、查询全部学生的学号、姓名、性别和出身日期结果按照出生日期的升序排列。实现代码及查询结果截图：USE SCselect s_no,sname,sex,sbirthdayfrom studentorder by sbirthday asc连接查询：（1）、查询全部学生的学号、姓名、性别、所在院系名称和专业名称实现

26、代码及查询结果截图：USE SCselect student.s_no,student.sname,student.sex,department.dept_name,speciality.spname from student,department,speciality where student.dno = department.dno and student.spno=speciality.spno（2）、查询选修了课程1（上课编号）的学生的学号、姓名、专业名称和这门课的成绩实现代码及查询结果截图：USE SCselect student.s_no,sname,class_no,tcid,

27、score from student,student_course where student.s_no = student_course.s_no and student_course.tcid=1（3）、查询学生不及格的情况列出不及格学生的学号、姓名和不及格的课程名称。实现代码及查询结果截图：USE SCselect student.s_no,sname, cname,scorefrom student join student_course on (student.s_no=student_course.s_no ) join course on (student.spno=course

28、.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、选用Northwind数据库进行查询（1）、对NothWind. Products表进行简单查询；在查询分析器在窗口下用SELECT语句完成单表查询：查询所有Products的详细记录；实现代码及查询结果截图：USE NorthWind select*from

29、Products查询单价（UnitPrice）小于20的Products；实现代码及查询结果截图：USE Northwind select*from Products where UnitPrice 20查询Products中单价（UnitPrice）最高的Products的资料；实现代码及查询结果截图：USE Northwind select * from Productswhere UnitPrice=(select max(UnitPrice)from Products)五、实验中出现的问题及解决方案答:在本次实验中，由于只知道出生日期，但其格式是smalldatetime，且发现年龄不

30、是我最初设置的值，而是系统自己默认的值1905年，后来通过update语句改正过来。另外，开始不知道要怎样计算年龄，后来通过上网查找解决了该问题。其他的一般的查询、连接查询和嵌套查询因为知道其的代码书写格式，基本上没遇到什么问题。六、思考题1、 连接查询分哪几类？各有什么特点？答：2、 进行连接查询时应注意哪些问题？答：进行夺标连接查询的时候，要指明查询条件。要有明确的思路 计算机 学院 计算机科学与技术 专业 班 学号： 姓名： 协作者：_ 教师评定： 实验题目 创建和使用视图、索引、存储过程 一、实验目的1、理解视图、索引、存储过程的定义、索引、存储过程的优点与、索引、存储过程的工作原理；

31、2、掌握在查询分析器和企业管理器中创建、修改及删除视图、索引、存储过程；3、掌握创建视图、索引、存储过程的SQL语句的用法；并能够熟练利用视图向表中插入、删除和修改数据。4、掌握使用视图来查询数据。二、实验主要仪器设备和材料1计算机及操作系统：PC机，Windows；2数据库管理系统：SQL Server 2000；三、实验方法、步骤及结果测试（一）、视图1、建立“计算机学院”的学生基本情况视图ies_student_view，该视图包括计算机学院所有学生的学号、姓名、性别、出身年月、专业名称。2、执行ies_student_view视图并观察结果。3、建立课程1（上课编号）的学生名册的视图，

32、该名册包括学生的学号、姓名、专业名称和这门课的成绩；并查询结果。4、建立统计不及格情况的视图，列出不及格学生的学号、姓名和不及格的课程代码；5、执行视图并观察结果。6、修改视图ies_student_view，使该视图包括所有学生的学号、姓名、性别、出身年月、学院名称、专业名称。7、执行ies_student_view视图并观察结果。8、删除视图ies_student_view。并查询结果。记录上述实验过程并截图说明。查询分析器执行情况：创建视图的SQL语句及执行结果显示CREATE VIEW ies_stu_viewAS SELECT s_no,sname,sex,sbirthday,spn

33、oFROM studentWHERE student.spno=spt001查看結果：SELECT * FROM ies_stu_view查询视图SQL语句及执行结果截图查询分析器执行情况：创建视图的SQL语句及执行结果显示CREATE VIEW course1_student_viewAS SELECT student.s_no,sname,sex,sbirthday,dno,spno,tcidFROM student,student_courseWHERE student_course.tcid=1 AND student.s_no=student_course.s_no查看結果：SELE

34、CT * FROM course1_student_view建立统计不及格情况的视图USE SCGOCREATE VIEW fail_student_viewAS SELECT student.s_no,sname,tcidFROM student,student_courseWHERE student.s_no=student_course.s_no AND student_course.score60查看结果：SELECT * FROM fail_student_view查询视图SQL语句及执行结果截图查询分析器执行情况：修改视图的SQL语句及执行结果显示USE SCGOALTER VIE

35、W ies_stu_viewAS SELECT s_no,sname,sex,sbirthday,spnoFROM student查看结果：SELECT * FROM ies_stu_view查询视图SQL语句及执行结果截图查询分析器执行情况：删除视图的SQL语句及执行结果显示USE SCGODROP VIEW ies_stu_view查询视图SQL语句及执行结果截图（二）、索引（1）、在student_course表（学生选课表）的学生学号（sno）列上创建索引sc_sno_index(2)通过SQL语句查看运行结果查询分析器执行情况：创建索引的SQL语句及执行结果显示USE SCGOCRE

36、ATE UNIQUE INDEX sc_sno_indexON student_course(s_no);查询视图SQL语句及执行结果截图（三）、存储过程题目（1）创建存储过程list_student_department,该存储过程接收学院代码作为输入参数，列出数据库SC中某个院系学生的全部信息。实验过程：（1） 启动查询分析器，选择SC为当前数据库（2） 在编辑窗口输入SQL语句（3） 按F5执行该语句查询分析器执行情况：SQL语句示例：Create proc list-.USE SCGOCREATE Procedure list_student_departmentdno char(6)

37、ASSELECT s_no,sname,sex,sbirthday,spno,class_no,student.dnoFROM student,department WHERE department.dno=dno AND student.dno=department.dno执行结果截图显示示例：EXEC list-题目（2）使用SQL语句执行存储过程list_student_department 实验过程:（1）启动查询分析器，选择SC为当前数据库（2）在编辑窗口输入SQL语句（3）按F5执行该语句查询分析器执行情况：SQL语句示例：EXEC list_student_department

38、department=51000USE SCEXEC list_student_department 01;执行结果截图显示题目（3）将某班选修某门课程的所有学生的学号和上课编号插入到student_course表中：（1）启动查询分析器，选择SC为当前数据库（2）在编辑窗口输入SQL语句（3）按F5执行该语句 查询分析器执行情况：SQL语句USE SCGOCREATE PROCEDURE apdclass(dno char(6), spno char(8),class_no char(4),tcid integer)ASINSERT INTO student_course(tcid,s_no

39、)SELECT tcid,s_noFROM studentWHERE dno=dno AND spno=spno AND class_no=class_no AND s_no not IN(SELECT s_no FROM student_course WHERE tcid=tcid)执行结果截图显示题目（4）使用SQL语句执行存储过程：（1）启动查询分析器，选择SC为当前数据库（2）在编辑窗口输入SQL语句（3）按F5执行该语句查询分析器执行情况：SQL语句UES SCGOEXEC apdclass 01,spt001,1001,1;执行结果截图显示题目（5）使用SQL语句查看存储过程代码：

40、（1）启动查询分析器，选择SC为当前数据库（2）在编辑窗口输入SQL语句（3）按F5执行该语句查询分析器执行情况：SQL语句USE SCGOEXEC sp_helptext list_student_department执行结果截图显示题目（6）使用SQL语句修改存储过程修改存储过程list_student_department，使其返回值只包括student表的部分列（学生学号、姓名、出生年月、学院名称和专业名称）（1）启动查询分析器，选择SC为当前数据库（2）在编辑窗口输入SQL语句（3）按F5执行该语句查询分析器执行情况：SQL语句USE SCGOALTER Procedure list

41、_student_departmentdno char(6)ASSELECT s_no,sname,sbirthday,dept_name,spnameFROM student,department,specialityWHERE department.dno=dno AND student.dno=department.dnoAND department.dno=speciality.dno执行结果截图显示题目（7）使用SQL语句删除存储过程实验过程:（1）启动查询分析器，选择SC为当前数据库（2）在编辑窗口输入SQL语句 （3）按F5执行该语句查询分析器执行情况：SQL语句USE SCGO

42、DROP PROC list_student_department执行结果截图显示实验方法：（1）、视图（使用企业管理器）1、 启动企业管理器，选择要创建视图的数据库SC并展开；2、 执行“工具”/“向导”命令；3、 选择数据库SC；4、 选择数据库SC，选择表student、department。5、 选择student表中的sno snamesex sbirthday字段和speciality表中的spname字段；6、 在“定义限制”对话框中输入WHERE子句来限制视图的信息；7、 命名视图：ies_sc_view8、 使用视图ies_sc_view。在查询分析器中运用SQL语句查询视图

43、。（2）、索引（使用企业管理器）1、启动企业管理器，选择要创建索引的数据库SC并展开；2、执行“工具”/“向导”命令；3、展开“数据库”文件夹，双击“创建索引向导”4、选择希望为其建立索引的数据库和对象（如数据库SC中的student表）5、查看有关当前索引的信息。选择一个或多个将被包含在索引中的列6、指定索引选项。四、实验中出现的问题及解决方案最初并不很清楚要如何来写带有输入参数的存储过程以及如何通过输入参数来显示表里对应的内容，后来通过上网查找一些资料和看一些例子之后问题就自然而然的解决了五、思考题1、视图更新的优点答：视图的作用：1. 视图能够简化用户的操作；2. 视图使用户能以多种角度

44、看待同一数据；3. 视图对重构数据库提供了一定程度的逻辑独立性；4. 视图能够对机密数据提供安全保护；5. 适当的利用视图可以更清晰地表达查询。2、哪些视图是可以更新的？哪些视图是不可以更新的？针对实验举例说明。答：一般的，行列子集视图时可更新的，除行列子集视图外，理论上有些视图也是可以更新的。还有其他的视图从理论上就是不可更新的，比如，视图的属性来自聚集函数、表达式，则该视图肯定是不可更新的。在上述实验中 ies_stu_view视图是可以更新的，其视图更新既是对student表的更新。而视图course1_student_view和fail_student_view都是由两个基本表组成的，

45、因此视图不能更新。 计算机 学院 计算机科学与技术 专业 班 学号： 姓名： 协作者：_ 教师评定： 实验题目 小型数据库规划设计 一、 实验目的学习数据库原理及应用目的就是能创建和管理一个真实的数据库，通过规划设计一个小型数据库系统。让学生掌握数据库规划设计的基本技术，熟悉数据库的设计的基本方法和步骤，明白数据库设计各阶段的任务，加深对数据库系统概念和特点的理解。二、 实验内容和要求根据你熟悉的实际情况，选一个小型的数据库应用项目，销售管理系统，进行系统分析和数据库设计。要求：1、规划设计一个小型系统的数据库，按照给定要求，对各设计阶段进行描述，要求给出数据库设计的E-R图，并将E-R图转换成相应的模型，编制SQL命令脚本。2、要求：数据库结构严谨、基本表及属性合理、各种约束条件正确。 3、涉及的内容或知识点：数据库设计的需求分析、数据模型、关系规范化理论。4、采用的方法和手段：设计数据模型、进行关系规范化处理和用软件实现数据库的