数据库实验报告 2

1、 数据库原理及系统实验报告 数据库系统原理及应用实验报告 学生姓名： 樊昊杲 专业班级：信息管理与信息系统11级2班 学 号： 20115129 指导教师： 余 本 功 2013.10 16数据库上机实验报告一.实验目标通过“数据库系统概论”书本知识学习和“数据库原理与应用”课程教学所授知识对“教学管理系统”实验内容进行分析，建立相应的数据库、数据表、数据视图以及相应的界面程序，利用SQL结构化查询语言提供的数据操纵功能，编程设计实现对数据的定义、修改、删除、查询、更新以及控制等操作，并按照实验指导书的各项要求完成实验。 三.实验内容实验一：设计数据库、数据表和数据视图并编程实现1. 目的要求

2、（1）这是一个验证性实验。（2）利用所学E-R数据模型图概念，描述“学生管理” 系统中数据表之间以及各数据项之间的联系。并按照课程教学大纲要求，转化相关E-R图为关系模型中对应的二维表，指出每个数据表中的关键字、索引项、外码以及表中需要满足完整性约束条件；（3）对照关系模型中(1:1)、(1:n)、(m:n)实体联系，指出各数据表实体属于哪一类联系。（4）熟悉微软公司SQL Server的企业管理器（后台实验平台）和查询分析器（前台实验平台）内嵌的SQL编程环境，正确登录、退出和使用实验平台环境；（5）熟练掌握SQL环境下的程序编辑和运行命令；（6）建立一个教学管理数据库，即建立五个相关数据表

3、。练习并掌握定义、删除和修改表的数据结构操作。建立数据库和数据表、数据视图并完成对各个数据表输入一定查询要求的模拟数据（模拟数据记录尽量多输一些且数据要能满足查询要求）的内容。2.实验内容（1）.使用Microsoft SQL server 2005的对象资源管理器，右键数据库新建数据库命名为“学生管理”并保存在储存的文件夹里。例：如图 确定并在学生管理下的“表”右键编辑，并按照下列数据项描述定义数据项名缩写名称类型长度，位数学号SnoChar9姓名snameChar10出生年月sbirthdayDateTime8性别ssexChar2专业班级sclassChar20评语sremarkChar

4、100家庭住址addressChar40邮编zipcodeChar6电话phoneChar15电子邮箱emailChar40课程号cnoChar6课程名称cnameChar20先行课cpnoChar6学时数ctimeNumeric2学分creditNumeric2成绩scoreNumeric3教工号TnoChar7教师名称tnameChar10性别tsexChar2教师生日tbirthdayDateTime8职称positionChar12系别departmentChar16工作量tamountNumeric7，2简历experienceChar200授课时间tdateDataTime8教室c

5、lassroomChar10编辑下列5个表Student表：SnosnameSbirthdaySclass011110101章海潮1982.02.07信管系0101011111103王天力1982.03.04电商系0101011111204董丞悟1982.06.06电商系0102011112110马丽鹃1982.04.03工商系0101011113104杨乾坤1982.03.23会计系0101011113221刘抗日1983.01.16会计系0102011114111丘海棠1983.02.08旅游系0101011115208齐振国1982.07.22旅游系0102001011101王晓悦198

6、2.01.12材料系0001001011212程伊莲1982.03.21材料系0002course表：CnoCnameCpnoCreditCtimeC001数据库原理C005464C002高等数学472C003管理信息系统C001454C004操作系统C006332C005数据结构C007432C006计算机文化基础232C007C语言程序设计C006332score表：SnoCnoScore011110101C00190011111103C00185011111204C00173011112110C00198011113104C00196011113221C00197011114111C00

7、183011115208C00185011110101C00792011111103C00797teacher表：TnoTnameTsexDepartmentT001江承基男信息管理系T002梁其征男信息管理系T003徐茉莉女信息管理系T004吴雅云女信息管理系T005杨运铎男电子商务系T006张昌盛男电子商务系T007程力衡男电子商务系Teaching表TnocnotdateclassroomsclassT001C0051988-01-09西二405信管系0101T002C0071990-02-01西二406电商系0101T003C0011985-09-10西二504电商系0102T004C

8、0061984-07-15主401工商系0101T005C0041989-11-12主402会计系0101T006C0021991-05-21西二506会计系0102完成表的创建。create table student ( sno Char(9),snameChar(10) constraint c1 not null,sbirthdayDatetime,ssexChar(2) constraint c2 check(ssex in ('男','女'),sclassChar(20),sremarkChar(100),addressChar(40),zipcod

9、eChar(6),phoneChar(15),emailChar(40), constraint pk_student primary key(sno)create table course ( cno Char(6),cnameChar(20),cpnoChar(6),ctimeNumeric(2),creditNumeric(2), constraint pk_course primary key(cno), constraint fk_course foreign key(cpno) references course(cno)create table score( sno char(9

10、), cno char(6), score Numeric(3), constraint pk_score primary key(sno,cno), constraint fk_score foreign key(sno) references student(sno), constraint fk1_score foreign key(cno) references course(cno)create table teacher ( tno char(7), tname char(10), tsex char(2) constraint t2 check(tsex in ('男&#

11、39;,'女'), tbirthday datetime, position char(12), department char(16),tamount numeric(7,2),experience char(200),constraint pk_teacher primary key(tno)create table teaching(tno char(7),cno char(6), tdate datetime, classroom char(10),sclass char(20), constraint pk_teaching Primary key (tno, cno

12、), constraint fk_teaching Foreign key(tno) references teacher(tno), constraint fk1_teaching Foreign key(cno) references course(cno);在表中用insert语句添加数据：insert into student(sno,sname,sbirthday,sclass)values ('011110101','章海潮','1982.02.07','信管系');insert into student(sno,sn

13、ame,sbirthday,sclass)values ('011111103','王天力','1982.03.04','电商系');insert into student(sno,sname,sbirthday,sclass)values ('011111204','董丞悟','1982.06.06','电商系');insert into student(sno,sname,sbirthday,sclass)values('011112110',&#

14、39;马丽鹃','1982.04.03','工商系');insert into student(sno,sname,sbirthday,sclass)values ('011113104','杨乾坤','1982.03.23','会计系');insert into student(sno,sname,sbirthday,sclass)；Insert into score values('011114111','C001','83');Inser

15、t into score values('011115208','C001','85');Insert into score values('011110101','C007','92');Insert into score values('011111103','C007','97');Insert into teacher(tno,tname,tsex,department)values('T001','江承基',&

16、#39;男','信息管理系');Insert into teacher(tno,tname,tsex,department)values('T002','梁其征','男','信息管理系');IInsert into teaching(Tno,cno,tdate,classroom,sclass)values('T001','C005','1988-01-09','西二','信管系');Insert into teaching(T

17、no,cno,tdate,classroom,sclass)values('T002','C007','1990-02-01','西二','电商系');Insert into teaching(Tno,cno,tdate,classroom,sclass)values('T003','C001','1985-09-10','西二','电商系');Insert into teaching(Tno,cno,tdate,classroom,s

18、class)完成表中实验一的数据输入。实验二：设计数据插入、修改、删除、更新、查询和视图查询等操作并编程实现1. 目的要求（1）熟练掌握数据更新语句，灵活地操作插入数据、修改数据和删除数据；（2）熟练掌握关系数据库中的完整性概念的应用；（3）了解数据录入的方法和过程。（4）掌握单表查询的基本方法；（5）掌握连接查询的基本方法；（6）熟练掌握以下练习，并进行以下各类查询： 选择表中的若干列、查询全部列、查询经过计算的值； 选择表中的若干元组，即消除取值重复的行与查询满足指定条件的元组（包括：比较大小、确定范围、确定集合、字符匹配、涉及空值和多种条件查询）； 对查询结果排序； 使用集函数； 对查询

19、结果分组（7）熟练掌握以下练习，并进行下列各类连接查询： 等值与非等值连接查询； 自身连接； 外连接； 复合条件连接；（8）掌握嵌套查询的基本方法；（9）掌握集合查询的基本方法；（10）熟练掌握查询视图操作（11）熟练掌握更新视图操作（12）熟练关系的完整性概念，领会视图的用途（13）练习以下各类查询： 带有IN谓词的子查询； 带有比较运算符的子查询； 带有ANY或ALL谓词的子查询； 带有EXISTS谓词的子查询； 视图查询与更新操作；2. 实验内容： (3)求每门课的平均成绩，并把结果存入average表；create table average( cno char(6), average

20、 Numeric(3), );insert into averageselect cno,avg(score)from score group by cno（4） 将学生“马丽鹃”的出生日期改为“1982.8.20”；update student setSbirthday=1982-8-20where sname='马丽鹃'（5）将所有学生的zipcode属性列的值填补为“230009”；update student set zipcode = 230009（6） 将average表中的所有课程的平均成绩置零；update average set average=0（7）删除a

21、verage表中的课程号为c007的平均成绩记录；delete from average where cno='c007' （8）删除所有average表中平均成绩记录；delete from average（9） 建立一个临时学生信息表（tstudent），删除该表中的学号前六位为001011的所有学生记录。create table tstudent ( sno Char(9) ,snameChar(10) constraint c3 not null,sbirthdayDatetime,ssexChar(2) constraint c4 check(ssex in (

22、9;男','女'),sclassChar(20),sremarkChar(100),addressChar(40),zipcodeChar(6),phoneChar(15),emailChar(40), constraint pk_tstudent primary key(sno) );insert into tstudentselect * from student（10） 查询全体学生的学号与姓名；select sno,sname from student（11） 查询全体学生的学号、姓名、所属班级；select sno,sname,sclass from stud

23、ent（12）查询全体学生的详细记录；select *from student（13）查询全体学生的姓名及其年龄；select sname,2013-year(sbirthday)from student（14）查询全体学生的姓名、出生年份；select sname ,year(sbirthday) from student（15）查询所有修过课的学生的学号；select distinct sno from score（16）查询“信管系0101”班全体学生名单；select sno from student where sclass='信管系0101'（17）查询查询所有年龄

24、在27岁以下的学生姓名及其年龄；select sname,2013-year(sbirthday) sage from student where 2013-year(sbirthday)<27（18）查询考试成绩有不及格的学生的学号；select sno from score where score<60（19） 查询年龄在15至28岁之间的学生姓名、班级和年龄；select sname,sclass,2013-year(sbirthday) from student where 2013-year(sbirthday) >=15and 2013-year(sbirthda

25、y)<=28（20）查询年龄不在15至28岁之间的学生姓名、班级和年龄；select sname,sclass,2013-year(sbirthday) from student where not exists(select sname,sclass,2013-year(sbirthday) from student where 2013-year(sbirthday) >=15and 2013-year(sbirthday)<=28)（21）查询“信管系0101”和“电商系0102”班的学生的姓名和班级信息；select sname,sclass from student

26、 where sclass='信管系0101'union select sname,sclass from student where sclass='电商系0102'（22）查询既不是“信管系0101”也不是“电商系0102”班的学生的姓名和班级信息；select sname,sclass from student where sclass not in('信管系0101','电商系0102') （23）查询学号为“011113104”的学生的详细情况；select * from student where sno='0

27、11113104'（24）查询学号以“0111”打头的学生信息select * from student where sno like '0111%'（25）查询所有姓“张”学生的学号、姓名、性别、年龄；select sno,sname,ssex,2013-year(sbirthday) from student where sno like '张%'（26）查询名字中第二个字有“海”字的学生的学号、姓名、性别、年龄；select sno,sname,ssex,2013-year(sbirthday) from student where sno lik

28、e '_海%'（27）查询所有不姓“刘”学生的姓名；select sname from student where sno not like '刘%'（28）查询课程号以“C”开头的最后两个字母为“05”的课程号和课程名；select cno,cname from coursewhere cno like 'c%05'（29）某些学生选修某门课程后没有参加考试，所以有选修课记录，但没有考试成绩，试查找缺少考试成绩的学生和相应的课程号；select sno,cno from score where score like null（30） 查找全部有

29、成绩记录的学生学号、课程号；select sno,cno from score（31） 查找“电商系0101”班年龄在27岁以下的学生学号、姓名；select sno,sname from student where2013-year(sbirthday)<=27 and sclass='电商系0101'（32） 查找选修了“C001”号课程的学生学号及其成绩，查询结果按分数降序排序; select student.sno,score fromstudent,score wherestudent.sno=score.sno and cno='c001'or

30、der by score desc （33）查询全体学生情况，查询结果按所在班级升序排列，对同一班级中的学生按年龄降序排列；select * from student order by sclass,2013-year(sbirthday) desc（34） 查询学生总人数；select count(sno) from student（35） 查询选修了课程的学生人数；select count(distinct sno)from score（36） 在所有课程中查询最高分的学生学号和成绩；select sno,score from scorewhere score in (select max

31、(score)from score )（37） 查询学习“C001”课程的学生最高分数；select sno,score from scorewhere score in (select max(score)from score where cno='c001' )（38） 计算各个课程号与相应的选课人数；select cno ,count(sno)from score group by cno（39） 查询“电商系0102”班选修了两门课程以上的学生学号、姓名；select sno ,sname from student where student.sclass='电

32、商系0102'and sno in (select sno from score group by snohaving count(*)>2)（40） 自然连接student和score表；Select student.sno,sname, sbirthday,ssex,sclass,sremark,address,zipcode,phone,email,cno,score from student,scorewhere student.sno=score.sno; （41） 使用自身连接查询每一门课程的间接先行课（即先行课的先行课）select o,s1.cpno from c

33、ourse s1,course s2 where s1.cpno=o; （42）使用复合条件连接查询选修“c001”号课程且成绩在90分以上的所有同学；select student.sno from student,scorewhere student.sno=score.sno and cno='C001' and score>90;（43）使用复合条件连接查询每个学生选修的课程名及其成绩；select cname,score from course,scorewhere o=o;（44） 查询选修了全部课程的学生；select* from student where

34、not exists ( select* from course where not exists( select* from score where score.sno=student.sno and o=o)（45）查询至少选修全部学分数为4个学分的课程的学生的学号、姓名；select student.sno,sname from student,course,scorewhere student.sno=score.sno and o=o group by student.sno,snamehaving sum(credit)>=4;（46）查询所有选修了C001号课程的学生学号、

35、姓名；select student.sno,sname from student,scorewhere student.sno=score.sno and cno='C001' ;（47）查询选修了课程C001或c007的学生学号、姓名；select student.sno,sname from student,score where student.sno=score.sno and cno='C001'union select student.sno,sname from student,score where student.sno=score.sno a

36、nd cno='C007'（48）查询“会计系0102”班的学生及年龄不大于27岁（现有年龄）的学生；select sno,sname from student where sclass='会计系0102'union select sno,sname from student where 2013-year(sbirthday)<27;（49）查询既选修了课程C001又选修了课程c007的所有学生学号、姓名；select student.sno,sname from student,score where student.sno=score.sno and

37、 cno='C001'intersect select student.sno,sname from student,score where student.sno=score.sno and cno='C007'（50）查询选修了课程名为“数据库原理”的学生的学号、姓名、性别、年龄；select student.sno,sname,ssex,2013-year(sbirthday) as sage from student,course,scorewhere student.sno=score.sno and o=o and cname='数据库原理&

38、#39;（51）查询其他班中比“信管系0101”班所有学生年龄都小的学生名单；select* from student where 2013-year(sbirthday)<ALL(select 2013-year(sbirthday)from studentwhere sclass='信管系0101' );(52）查询与“齐振国”在同一个班学习的学生学号、姓名、性别、年龄；select sno,sname,ssex,2013-year(sbirthday) as sage from studentwhere sclass in(select sclass from st

39、udentwhere sname='齐振国')and sname<>'齐振国' （53）建立“信管系0101”班学生的视图，定义视图名为“info_student1”；create view info_student1 asselect* from student where sclass='信管系0101'（54）建立“信管系0101”班学生的视图，定义视图名为“info_student2”，并要求进行修改与插入时，仍须保证该视图只有“信管系0101”班学生；create view info_student2 asselect* f

40、rom student where sclass='信管系0101' with check option;（55） 建立“信管系0101”班选修了“C001”课程的学生的视图，定义视图名为“info_C001_student1”；create view info_C001_student1 asselect info_student1.sno,sname, sbirthday,ssex,sclass,sremark,address,zipcode,phone,email from info_student1,score where info_student1.sno=score

41、.sno and cno='C001'（56） 建立“信管系0101”班选修了“C001”课程且成绩在90分以上的学生的视图，定义视图名为“info_c001_student2”；create view info_C001_student2 asselect info_student2.sno,sname, sbirthday,ssex,sclass,sremark,address,zipcode,phone,email from info_student2,scorewhere info_student2.sno=score.sno and cno='C001'

42、; and score>90;（57）定义一个反映学生年龄的视图，定义视图名为“vbirthday_student”；create view vfemale_student asselect * from student where ssex='女' （58） 将学生表中所有女生记录定义为一个视图，视图名为“vfemale_student”；create view vbirthday_student asselect 2013-year(sbirthday) as sage from student;（59）将学生的学号及其平均成绩定义为一个视图，视图名为“vaverag

43、e_student”；create view average_student(sno,average) as select sno,AVG(score) from score group by sno;（60） 删除视图“info_student1”，删除后即重建；drop view info_student1; create view info_student1 as select* from student where sclass='信管系0101'（61） 在“信管系0101”班学生视图中找出年龄小于27岁（现在的年龄）的学生；select* from info_stu

44、dent1 where 2013-year(sbirthday)<27;（62） 利用视图查询“信管系0101”班选修了“C001”课程的学生；select * from info_C001_student1;（63） 通过“信管系0101”班info_student2视图中学号“011111103”的学生姓名改为“潘长江”；update info_student2 set sname='潘长江' where sno='011111103'（64） 向“信管系0101”班info_student1视图中插入一个新学生记录，其中：学号：011111136，姓

45、名：张艺谋，性别：男，出生日期：1987.11.9；insert into info_student1(sno,sname,ssex,sbirthday)values('011111136','张艺谋','男','1987.11.9');（65） 通过视图info_student1删除信管系0101班学号为“011111135”、姓名为“黄健中”的学生记录；delete from info_student1where sno='011111135'and sname='黄健中'实验三：数据库存储过程

46、、触发器的建立及编程操作的实现1. 目的要求（1）理解掌握数据库中存储过程、触发器的原理、使用场合及方法；（2）掌握触发器在关系数据库的完整性实现中的作用；（3）掌握数据库中存储过程的建立方法和调用过程；（4）掌握数据库中触发器的建立方法； *（5）理解事务的作用及定义； *（6）熟练游标的作用、定义及作用：2. 实验内容 create table table1(f1 char(20),f2 int);create table table2(f1 char(20),f2 int);create trigger insert_T1 on table1 for insert as begin de

47、clare F1 char(20), F2 int,T_F1 Char(20) Declare Slen int Select F1=F1, F2=F2,T_F1=F1 From inserted If F2=1 begin Select F2=0 while Len(RTrim(F1)>0 begin Select F2=F2+Convert(int,left(RTrim(F1),1) Select F1=Right(RTrim(F1),Len(Rtrim(F1)-1) end end Else begin Select F2=Ceiling(Len(RTrim(F1)/2.0) en

48、d Insert into Table2(F1,F2) values(T_F1,F2) end;create trigger delete_T2 on table2 for deleteas begin declare F1 char(20), F2 int,T_F2 int Select F1=a.F1, F2=a.F2,T_F2=b.F2 From table1 a, deleted b where a.F1=b.F1 If F2=1 begin Update Table1 set F1=LTrim(RTrim(F1)+LTrim(Str(T_F2)+Right(RTrim(Str(T_F

49、2),1) where F1=F1 end If F2=2 begin Update Table1 Set F1=Ltrim(Rtrim(F1)+Ltrim(Str(T_F2)+Ltrim(Str(Unicode(SubString(Ltrim(F1),T_F2,1) where F1=F1 end If F2=3 begin Update Table1 Set F1=LTrim(RTrim(F1)+LTrim(Str(T_F2)+RTrim(LTrim(Char(64+T_F2) where F1=F1 end If F2=4 begin Update Table1 Set F1=LTrim

50、(RTrim(F1)+LTrim(Str(T_F2)+RTrim(LTrim(Str(Connections) where F1=F1 endend;CREATE PROCEDURE Exam_Proc Student_ID char(10), Student_Name char(10) ASbegin If Len(LTrim(Rtrim(Student_ID)<>8 begin Print ' 学号 长度不对 ' end else begin Delete From Table2 Delete From Table1 Insert Into Table1(F1,F2) Values(Right(Student_ID,6),1) Insert Into Table1(F1,F2) Values(LTrim(RTrim(Student_Name),2) Insert Into Table1(F1,F2) Values(LTrim(RTrim(ServerName),3) Insert Into Table1(F1,F2) Values(LTrim(RTrim(ServiceName),4) declare Exam_Str Char(80), C_F1 Char(20) Declare Table1_Cursor Cu