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

《数据库系统原理》实验

实验1  表和表数据的操作

一、实验目的

掌握在SQL Server 2000环境下，利用SQL语言创建和管理表的方法。

二、实验要求

1、学会利用SQL语句建立自定义数据类型；

2、掌握使用SQL语句建立数据表的方法；

3、掌握数据表的修改及删除方法（界面方式及语句方式）；

4、掌握T－SQL中的INSERT、UPDATE及DELETE语句的使用方法；

三、实验内容

1、创建数据库

利用“查询分析器”创建“stuscore”数据库。

CREATE DATABASE stuscore

2、创建数据表

（1）用“查询分析器”建立stuscore数据库中的学生表（Student）、班级表（Class）、课程表（Course）及成绩表（Grade），结构如下：

(a) Student表的结构为：

字段名	类型	中文名	备注
SNO	Char(8)	学号	主键
SName	Varchar(10)	姓名
Sex	Char(2)	性别	男、女
ClsNO	Char(6)	班级	外健,班级的编号，参照表Class
StuAddr	Varchar(20)	住址	描述性说明
Birthday	Char(20)	出生年月
Height	DEC(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表的结构为：

字段名	类型	中文名	备注
ClsNO	Char(6)	班号	主键
ClsName	Varchar(16)	班名	对班级的描述信息
Director	Varchar(10)	辅导员	班级的辅导员
Specialty	Varchar(30)	专业

create table class

(clsno char(6) primary key,

clsname varchar(16),

dorector varchar(10),

specialty varchar(30)

);

(c) Course表的结构为：

字段名	类型	中文名	备注
CNO	Char(4)	课程号	主键
CName	Varchar(16)	课程名	课程的名称
PCNO	char(4)	先修课程	先修课程的课程号
Credit	Tinyint	学分

create table course

(cno char(4) primary key,

cname varchar(16),

pcno char(4),

credit tinyint

);

(d) Grade表的结构为：

字段名	类型	中文名	备注
SNo	Char(8)	学号	学号，参照Student，与Course组成主键
CNO	Char(4)	课程号	课程号，参照Course
Scorce	Char(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）中插入数据

SNO	SName	Sex	ClsNO	StuAddr	Birthday	Height
19920101	王军	男	CS01	下关40#	1976.12.21	1.76
19920102	李杰	男	CS01	江边路96#	1974.5.24	1.72
19920103	李丽	女	MT04	新湖中路1#	1979.6.4	1.65
19920306	王彤	女	MT04	路94#	1977.3.18	1.65
19940107	吴杪	女	PH08	莲化小区74#	1979.4.8	1.60

插入数据之后使用命令：Select * from Student; 检查插入数据的正确性

（2）向表（Class）中插入数据

ClsNO	ClsName	Director	Specialty
CS01	计算机一班	王宁	计算机应用
MT04	数学四班	陈晨	数学
PH08	物理八班	葛格	物理

插入数据之后使用命令：Select * from Class; 检查插入数据的正确性

（3）向表（Course ）中插入数据

CNO	CName	PCNO	Credit
0001	数学	Null	6
0003	计算机基础	0001	3
0007	物理	0001	4

插入数据之后使用命令：Select * from Course; 检查插入数据的正确性

（4）向表（Grade ）中插入数据

SNO	CNO	Score
19920101	0001	90
19920101	0007	86
19920102	0001	87
19920102	0003	76
19920306	0001	87
19920306	0003	93
19940106	0007	85

插入数据之后使用命令：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 

As

Select sno,sname,sex,birthday

From student

（2）创建一个视图V_XSCJ，以显示“0001”号课程的学生成绩信息，如学号、姓名、课程名称、成绩和学分等。

create view V_XSCJ(sno,sname,CName,Scorce,Credit)

as

select grade.sno,sname,CName,Scorce,Credit

from student,Course,Grade

Where 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 update

As

Update grade set grade.sno= inserted.sno from grade,deleted,inserted

where 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、掌握T－SQL中运算符和表达式的使用。

2、通过对Select的使用，掌握Select语句的结构及其应用。

3、掌握T－SQL中几个常用流程控制语句的使用。

4、掌握系统内置函数的概念及其应用。

二、实验要求

掌握在SQL Server 2000环境下，利用企业管理器和查询分析器进行SQL数据查询。学习使用Select语句进行数据库的检索，掌握在Select语句中使用where子句、order by子句、group by子句以及between、like关键字。

学习T－SQL语言的几个常用语句、系统内置函数和用户自定义函数的使用方法。

三、实验内容

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 Student

where sex='男' and 

Birthday between '1976.12.21' and '1979.4.8'

Order by Birthday desc;

(2)

select count(*)

from student,class

where student.clsno=class.clsno and class.specialty='数学';

(3)

select sno,max(Scorce), min(Scorce),avg(scorce)

from grade

group by sno

(4)

select cno

select cno

From grade

group by cno

having count(*)>=3;

(5) 

select sno,avg(Scorce)

from grade

group by sno

(6) 

select *

from Student

where sname like '李%' and clsno in('CS01','MT04');

(7)

Select student.sno,sname,scorce

From student,class,grade

Where student.clsno=class.clsno and class.specialty='数学'

and student.sno=grade.sno and grade.scorce>'90';

(8) 

update grade

Set Scorce=Scorce+5

Where 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,sname

From student,grade,course

Where student.sno=grade.sno and grade.cno=course.cno and course.pcno='0001';

（2）

Select *

From student

Where birthday > all

(select birthday from student where clsno='cs01') and clsno <> 'cs01';

（3）

Select sname

From student

Where not exists

(select * from grade,course,student

Where grade.sno=student.sno and grade.cno=course.cno and course.pcno='0003') ;