《数据库系统原理》实验报告.doc

数据库系统原理实验实验1 表和表数据的操作一、实验目的掌握在SQL Server 2000环境下，利用SQL语言创建和管理表的方法。二、实验要求1、学会利用SQL语句建立自定义数据类型；2、掌握使用SQL语句建立数据表的方法；3、掌握数据表的修改及删除方法（界面方式及语句方式）；4、掌握TSQL中的INSERT、UPDATE及DELETE语句的使用方法；三、实验内容1、创建数据库利用“查询分析器”创建“stuscore”数据库。CREATE DATABASE stuscore2、创建数据表（1）用“查询分析器”建立stuscore数据库中的学生表（Student）、班级表（Class）、课程表（Course）及成绩表（Grade），结构如下：(a) Student表的结构为：字段名类型中文名备注SNOChar(8)学号主键SNameVarchar(10)姓名SexChar(2)性别男、女ClsNOChar(6)班级外健,班级的编号，参照表ClassStuAddrVarchar(20)住址描述性说明BirthdayChar(20)出生年月HeightDEC(4,2)身高以米为单位表示学生的身高。create table student(sno char(8) primary key,sname varchar(10),sex char(2),clsno char(6),stuaddr varchar(20),birthday char(20),height DEC(4,2),foreign key(clsno) references class(clsno);(b) Class表的结构为：字段名类型中文名备注ClsNOChar(6)班号主键ClsNameVarchar(16)班名对班级的描述信息DirectorVarchar(10)辅导员班级的辅导员SpecialtyVarchar(30)专业create table class(clsno char(6) primary key,clsname varchar(16),dorector varchar(10),specialty varchar(30);(c) Course表的结构为：字段名类型中文名备注CNOChar(4)课程号主键CNameVarchar(16)课程名课程的名称PCNOchar(4)先修课程先修课程的课程号CreditTinyint学分create table course(cno char(4) primary key,cname varchar(16),pcno char(4),credit tinyint);(d) Grade表的结构为：字段名类型中文名备注SNoChar(8)学号学号，参照Student，与Course组成主键CNOChar(4)课程号课程号，参照CourseScorceChar(20)成绩create table grade(sno char(8),cno char(4),scorce int,primary key(sno,cno);2)使用T-SQL语句修改表结构可以使用Alter语句增加、删除或修改字段信息。(1) 将表Student增加一列Department（系别）Char(2)，不能为空。alter table student add department char(2);(2) 修改学生表中的学生姓名字段的长度为20：alter table student alter column sname char(20);3、表数据的操作（1）向表（Student）中插入数据SNOSNameSexClsNOStuAddrBirthdayHeight19920101王军男CS01下关40#1976.12.211.7619920102李杰男CS01江边路96#1974.5.241.7219920103李丽女MT04新湖中路164#1979.6.41.6519920306王彤女MT04中央路94#1977.3.181.6519940107吴杪女PH08莲化小区74#1979.4.81.60插入数据之后使用命令：Select * from Student; 检查插入数据的正确性（2）向表（Class）中插入数据ClsNOClsNameDirectorSpecialtyCS01计算机一班王宁计算机应用MT04数学四班陈晨数学PH08物理八班葛格物理插入数据之后使用命令：Select * from Class; 检查插入数据的正确性（3）向表（Course ）中插入数据CNOCNamePCNOCredit0001数学Null60003计算机基础000130007物理00014插入数据之后使用命令：Select * from Course; 检查插入数据的正确性（4）向表（Grade ）中插入数据SNOCNOScore19920101000190199201010007861992010200018719920102000376199203060001871992030600039319940106000785插入数据之后使用命令：Select * from Grade; 检查插入数据的正确性实验3 关系、索引、视图和触发器一、实验目的1、掌握关系图的创建方法。2、掌握创建索引、查看索引和删除索引的操作和方法。3、掌握创建、查询、更新、修改和删除视图的操作和方法。二、实验要求1、掌握在SQL Server 2000环境下，利用查询分析器创建关系图，理解关系数据库中表间关系的参照引用完整性；2、掌握在SQL Server 2000环境下，利用查询分析器创建、查看和管理视图；3、掌握在SQL Server 2000环境下，利用查询分析器创建、查看和管理索引。三、实验内容索引、视图和触发器的创建、查询、修改和删除（1）为学生情况（student）表创建一个视图V_xsqk，以显示学生的学号、姓名、性别和出生年月等基本信息。Create view V_xsqk AsSelect sno,sname,sex,birthdayFrom student（2）创建一个视图V_XSCJ，以显示“0001”号课程的学生成绩信息，如学号、姓名、课程名称、成绩和学分等。create view V_XSCJ(sno,sname,CName,Scorce,Credit)asselect grade.sno,sname,CName,Scorce,Creditfrom student,Course,GradeWhere student.sno=grade.sno and course.cno=grade.cno and course.CNO=0001;（3）分别利用视图V_xsqk和V_XSCJ作查询和更新操作。（4）创建触发器trigger_t3，实现当修改学生情况表（student）中的某个学生的学号时，对应学生成绩表（grade）中的学号也作修改。Create trigger trigger_t3 on student for updateAsUpdate grade set grade.sno= inserted.sno from grade,deleted,insertedwhere grade.sno=(select sno from deleted)go（5）在Class表ClsName列上建立一个非聚集索引Create nonclustered index clsn on Class(ClsName)（6）在student表的SName列上建立一个唯一性索引Create unique index stuname on student (SName)实验4 简单的连接查询一、实验目的1、掌握TSQL中运算符和表达式的使用。2、通过对Select的使用，掌握Select语句的结构及其应用。3、掌握TSQL中几个常用流程控制语句的使用。4、掌握系统内置函数的概念及其应用。二、实验要求掌握在SQL Server 2000环境下，利用企业管理器和查询分析器进行SQL数据查询。学习使用Select语句进行数据库的检索，掌握在Select语句中使用where子句、order by子句、group by子句以及between、like关键字。学习TSQL语言的几个常用语句、系统内置函数和用户自定义函数的使用方法。三、实验内容1、使用SQL查询分析器，对学生成绩管理（stuscore）数据库完成如下查询：（1）在学生成绩库中查询出生年月在“1976.12.21”至“1979.4.8”之间的男生情况，并以出生年月按降序排列。（2）统计“数学”专业的人数。（3）统计每个学生每个学期的最低分、最高分及平均分。（4）查询至少被3个学生选修的课程的课程号（5）查询每个学生的平均成绩，要求查询结果中有学号和平均成绩两列。（6）查询CS01或MT04班级姓李的学生的信息。（7）求选修数学课程且成绩为90 分以上的学生学号、姓名及成绩。（8）将王军所选修的相关课程都增加5分(1)select *from Studentwhere sex=男 and Birthday between 1976.12.21 and 1979.4.8Order by Birthday desc;(2)select count(*)from student,classwhere student.clsno=class.clsno and class.specialty=数学;(3)select sno,max(Scorce), min(Scorce),avg(scorce)from gradegroup by sno(4)select cnoselect cnoFrom gradegroup by cnohaving count(*)=3;(5) select sno,avg(Scorce)from gradegroup by sno(6) select *from Studentwhere sname like 李% and clsno in(CS01,MT04);(7)Select student.sno,sname,scorceFrom student,class,gradeWhere student.clsno=class.clsno and class.specialty=数学 and student.sno=grade.sno and grade.scorce90;(8) update gradeSet Scorce=Scorce+5Where exists (select * from student where grade.sno=student.sno and sName=王军 );实验5、高级查询实验一、实验目的使学生进一步掌握SQL Server 查询分析器的使用方法，加深Transact-SQL 语言的嵌套查询语句的理解。二、实验内容在SQL Server 查询分析器中使用IN 、比较符、ANY 或ALL 和EXISTS 操作符进行嵌套查询操作。三、实验步骤用Transact-SQL语句表示，在学生选课库中实现其数据嵌套查询操作：（l）求选修了数学课程的学生学号和姓名。（2）求其他系中年龄小于计算机系年龄的学生。（3）求没有选修0003 课程的学生姓名。（1）select student.sno,snameFrom student,grade,courseWhere student.sno=grade.sno and grade.cno=course.cno and course.pcno=0001;（2）Select *From studentWhere birthday all(select birthday from student where clsno=cs01) and clsno cs01;（3）Select snameFrom studentWhere not exists(select * from grade,course,studentWhere grade.sno=student.sno and grade.cno=course.cno and course.pcno=0003) ;