数据库实验报告

数据库原理与应用

实验报告

专业：

班级：

学号：

姓名：

浙江传媒学院

电子信息学院

2014-2

实验1 SQL Server2000使用

一、实验目的

1、熟悉SQL Server 2000工具的各项功能和组件。

2、掌握使用企业管理器创建与管理数据库。

3、掌握使用企业管理器进行数据表的创建与管理。

二、实验环境

已安装SQL Server 2000的计算机，使用企业管理器进行实验。

三、实验内容及步骤

1、根据软、硬件环境的要求，安装SQL Server 2000。

2、通过“开始”→Microsoft SQL Server→“服务管理器”，打开“SQL Server 服务管理器”窗口，选择SQL Server服务。并且通过该窗口“启动”和“停止”SQL Server服务。

3、通过“开始”→Microsoft SQL Server→“企业管理器”打开企业管理器窗口，展开服务器“（LOCAL）（Windows NT）”。

4、利用企业管理器访问系统自带的pubs数据库。

（1）在企业管理器的树形目录中展开“数据库”，找到pubs数据库并展开，查看该数据库的所有对象，如表、视图、存储过程、默认和规则等。

（2）选择pubs数据库的“表”选项，在右窗口中将列出pubs数据库的所有表（包括系统表和用户表），选择用户表titles，右击鼠标，弹出快捷菜单，从中选择“打开表”→“返回所有行”菜单项，打开titles表，查看其内容。

5、利用查询分析器访问pubs数据库的表。

（1）通过“开始”→Microsoft SQL Server→“查询分析器”方式运行SQL Server 查询分析器，或者在企业管理器中，选择“工具”→“SQL 查询分析器”菜单项，运行SQL Server查询分析器。

（2）在查询分析器窗口中，选择“查询”→“更改数据库…”菜单项，或

者单击常用工具栏中的“更改数据库”下拉按钮，选择所要操作的pubs数据库。

（3）在查询分析器的编辑窗口中，输入以下代码：

SELECT type,avg(price)

FROM titles

WHERE royalty = 10

GROUP BY type

（4）选择查询分析器的“查询”→“分析”菜单项，或者单击常用工具栏

上“分析查询”按钮，查询分析器将对输入的代码进行语法分析，并由消息窗格给出分析结果报告。

（5）选择查询分析器的“查询”→“执行”菜单项，或者单击常用工具栏

上“执行查询”按钮，SQL Server将编译窗口中的代码，并在结果窗格中显示查询结果，如图1-1所示。

图1-1 查询结果

6、新建数据库

（1）通过“开始”→Microsoft SQL Server→“企业管理器”打开企业管理器窗口，右击“数据库”项，在快捷菜单中选择“新建数据库”菜单项。在新建数据库对话框的名称文本框中输入学生管理数据库名studentsdb（注意此时不要

按确定）。在“数据文件”选项卡中点击，更改数据文件的位置为“D:\Myfile”。

在“事务日志”选修卡中点击，更改日志文件的位置为“D:\Myfile”。（预先在电脑D盘新建文件夹Myfile）。然后点击“确定”。

（2）设置数据库属性。右击所要设置属性的数据库，从弹出的快捷菜单中选择“属性”，可点击相应的选项卡修改相关属性。

7、使用企业管理器在Studentsdb数据库中定义3个表。

点击studentsdb数据库展开，右击表，然后选择“新建表”，根据以下各表内容创建Studentsdb数据库中的三张表。

（1）学生基本情况表student（学号，姓名，性别，出生日期，家庭住址，备注），详细结构如表1-1所示。

表1-1 student表结构

（2）课程信息表course（课程编号，课程名称，学分），详细结构如表1-2所示。

表1-2 course表结构

（3）学生成绩表grade（学号，课程编号，分数），详细表结构如表1-3所示。

表1-3 grade表结构

（4）在企业管理器中，将student表的学号列设置为主键，非空。选中student 表，右击，选择设计表，右击学号，选择“设置主键”。

（5）在企业管理器中，将course表的课程编号设置为主键，非空。

（6）在企业管理器中，将grade表的学号和课程编号设置为主键，同时选中学号和课程编号，选择“设置主键”。将学号设置为外键，参照student表。右击学号，选择关系，在属性选项卡中点击“新建”，主键表选择student，外键表选择grade，分别在下拉框中选择“学号”，然后点击“关闭”。如图1-2所示。将课程编号设置为外键，参照course表。

如图1-2 外键设置表

8、在企业管理器中为student、course、grade三个表添加数据。

（1）分别右击student、course、grade表，然后选择“打开表”输入各表数据。student、course和SC表中的数据如图1-1 、图1-2、图1-3所示。

图1-1 student的数据

图1-2 course的数据

图1-3 grade的数据

9、使用企业管理器查看已创建的Studentsdb数据库和3个表的内容。

10、停止数据库服务器，将D:\myfile中的.mdf和.ldf两个文件拷贝至U盘中以备下一次实验使用。

11、重新启动数据库服务器，在企业管理器中删除studentsdb数据库。

实验思考：

1、查询分析器的作用是什么？可以有几种启动查询分析器的方法？

答：查询分析器的作用：对数据进行查询、定义、操纵、控制。

启动查询分析器的方法：一、打开企业管理器后，选中你的数据库，在“工具”菜单里，选查询分析器即可。二、打开SQL Server管理平台（management studio）后，新建查询，即可输入命令。

2、改变查询分析器的当前数据库，使用什么方法？

答：USE DATABASE 数据库名

GO

查询分析器工具栏选择所要的数据库就可以了。

3、在企业管理器中，studentsdb数据库的student表的数据输入时，如果输入相同学号的记录将出现什么现象？怎样避免该情况的发生？

答：出现学号重复记录，导致插入删除冲突。避免方式就是将student_no设为主键。

实验2 数据库的定义和操作

一、实验目的

1、掌握查询编辑器的使用方法，包括创建SQL查询和SQL脚本，执行SQL 语句并以表格或文本形式显示结果，或保存为报表文件输出；

2、掌握用T-SQL语句创建数据表、修改表结构

3．掌握用T-SQL语句向数据库中输入数据、修改数据、删除数据

二、实验环境

已安装SQL Server 2000的计算机，使用查询分析器进行实验。

三、实验内容及步骤

1、附加数据库

将上一次备份到U盘的包含studentsdb.mdf和studentsdb_log.mdf的文件夹myfile拷入D盘中。通过附加数据库，可以将没有加入SQL Server服务器的数据库文件加到服务器中。

通过“开始”→Microsoft SQL Server→“企业管理器”打开企业管理器窗口，右击“数据库”，选择“附加”命令，打开“附加数据库”窗口。单击“添加”按钮，选择D盘myfile中的studentsdb.mdf，并单击“确定”按钮，回到“附加数据库”窗口，再单击“确定”按钮，即可完成附加数据库的工作。

2、启动“查询分析器”。

3、使用SQL语句创建、修改表

（1）使用CREATE TABLE在studentsdb数据库中创建系表Dept。

Dept表

说明数据类型约束

系号系名整数

字符串，长度为20

主键

非空

SQL语句：

create table Dept(

DeptNo int primary key, DeptName char(20)not null )

（2）使用CREATE TABLE在studentsdb数据库中创建教师表Teacher。

Teacher表

列名数据类型约束

教师编号教师名系号整数

字符串，长度为8

整数

主键

非空

外键，引用Dept表

SQL语句：

create table Teacher(

TeacherNo int primary key,

TeacherName char(8)not null,

DeptNo int

foreign key references Dept(DeptNo)

)

（3）使用SQL语句ALTER TABLE修改course表的“课程编号”列，使之为非空。

alter table course

alter column课程编号char(4)not null

（4）使用SQL语句ALTER TABLE修改grade表的“分数”列，使其数据类型为real。

alter table grade

alter column分数int

（5）使用SQL语句ALTER TABLE在Student表中加入属性系号（INT型），设置为外键，引用Dept表。

alter table student

add系号int

foreign key references Dept(DeptNo)

（6）使用SQL语句ALTER TABLE在course表中增加属性先修课（数据类型为char(4)）和任课教师（引用Teacher表）。

alter table course

add先修课char(4)

alter table course

add任课老师int

foreign key references Teacher

4、采用SQL语句INSERT INTO…V ALUES向studentsdb数据库中的数据表

插入数据。

（1）向grade表中插入以下数据：

学号课程编号分数

0004 0001 80

0005 0002 90

insert into grade(学号,课程编号,分数)values('0004','0001','80')

insert into grade(学号,课程编号,分数)values('0005','0002','90')

（2）向Dept表中插入下列数据：

系号系名

10 计算机科学与技术

20 信息

insert into Dept(DeptNo,DeptName)values('10','计算机科学与技术')

insert into Dept(DeptNo,DeptName)values('20','信息')

（3）向Teacher表中插入下列数据

教师编号教师名系号

101 张星10

102 李珊10

103 赵天应10

104 刘田20

insert into Teacher(TeacherNo,TeacherName,DeptNo)values('101','张星','10')

insert into Teacher(TeacherNo,TeacherName,DeptNo)values('102','李珊','10')

insert into Teacher(TeacherNo,TeacherName,DeptNo)values('103','赵天应','10')

insert into Teacher(TeacherNo,TeacherName,DeptNo)values('104','刘田','20')

5、采用SQL语句UPDATE 对studentsdb数据库中修改数据表的数据

（1）将0005号学生的出生日期改为2000-8-1。

update student

set出生日期='2000-8-1'where学号='0005'

（2）将成绩在60分以下的学生的成绩置零。

update grade

set分数='0'where分数<60

（3）将grade表中刘卫平同学的课程编号为“0004”的记录的分数修改为90分。

update grade

set分数='90'where

课程编号='0004'

and

学号=(select学号from student where姓名='刘卫平')

（4）将course表中的“英语”课程的任课教师为张星老师。

update course

set任课老师=

(select TeacherNo from Teacher where TeacherName='张星')

where课程名称='英语'

（5）将张星老师“英语”课程的学生成绩全部加2分

update grade

set分数=分数+2

where课程编号=

(select课程编号from course

where任课老师=(select TeacherNo from Teacher

where TeacherName='张星')

and课程名称='英语')

6、采用SQL语句DELETE 删除studentsdb数据库中数据表的数据

（1）删除grade表中学号为“0004”的成绩记录。

delete from grade where学号='0004'

（2）删除年龄小于20岁的所有学生的成绩记录

alter table grade

drop constraint FK_grade_student

go

alter table grade

add constraint FK_grade_student

foreign key(学号)references student(学号)

on delete cascade

go

delete from student where(year(getdate())-year(出生日期))<20

实验3 数据库查询

一、实验目的

掌握各种查询的SQL脚本写法，包括SELECT、FROM、WHERE、GROUP BY、HA VING子句的用法，特别要求比较内连接、外连接的结果。

二、实验环境

安装SQL Server2000的计算机，数据库中已经建立了Studentsdb数据库，数据库中建立了Student、Course、grade、Teacher和Dept五个数据表，并在数据表中输入了一些数据。

三、实验内容及完成情况

在studentsdb数据库中，用SQL进行下列查询操作

1．使用SELECT语句进行基本查询。

（1）在student表中，查询每个学生的学号、姓名、出生日期信息。SELECT学号,姓名,出生日期

FROM student

（2）查询学号为0002的学生的姓名和家庭住址。

select姓名,家庭住址

from student

where学号="0002"

（3）找出所有男同学的学号和姓名。

SELECT学号,姓名

FROM student

WHERE性别="男"

2、使用SELECT语句进行条件查询

（1）在grade表中查找分数在80~90范围内的学生的学号和分数。SELECT学号,分数

FROM grade

WHERE BETWEEN 80 AND 90

（2）在grade表中查询学号为0003的学生的平均分。

SELECT AVG(分数)

FROM gade

WHERE学号="0003"

（3）在grade表中查询学习各门课程的人数。

SELECT课程编号,COUNT(*)

FROM grade

GROUP BY课程编号

（4）将学生按出生日期由大到小排序。

SELECT出生日期

FROM student_info

ORDER BY出生日期DESC

（5）查询所有姓“张”的学生的学号和姓名。

SELECT学号,姓名

FROM student_info

WHERE姓名LIKE'张%'

3、嵌套查询

（1）在student表中查找与“刘卫平”性别相同的所有学生的姓名、出生日期。

SELECT姓名,出生日期

FROM student_info

WHERE性别=

(SELECT性别

FROM student_info

WHERE姓名='刘卫平')

（2）使用IN子查询查找所修课程编号为0002、0005的学生学号、姓名、性别。

SELECT学号,姓名,性别

FROM student_info

WHERE student_info.学号IN

(SELECT学号

FROM grade

WHERE课程编号IN('0002','0005'))

（3）列出学号为0001的学生的分数比0002号的学生的最低分数高的课程编号和分数。

SELECT课程编号,分数

FROM grade

WHERE学号='0001'AND分数>ANY

(SELECT分数

FROM grade

WHERE学号='0002')

（4）列出学号为0001的学生的分数比学号为0002的学生的最高成绩还要高的课程编号和分数。

SELECT课程编号,分数

FROM grade

WHERE学号='0001'AND分数>ALL

(SELECT分数

FROM grade

WHERE学号='0002')

4、多表查询

（1）查询分数在80~90范围内的学生的学号、姓名、分数。

SELECT student_info.学号,姓名,分数

FROM student_info,grade

WHERE student_info.学号=grade.学号AND分数BETWEEN 80 AND 90

（2）查询学习“C语言程序设计”课程的学生的学号、姓名、分数。SELECT student_info.学号,姓名,分数

FROM student_info

INNER JOIN grade ON student_info.学号=grade.学号

INNER JOIN curriculum ON课程名称='C语言程序设计'

（3）查询所有男同学的选课情况，要求列出学号、姓名、课程名称、分数。

SELECT student_info.学号,student_info.姓名,curriculum.课程名称,grade.分数FROM student_info,curriculum,grade

WHERE student_info.学号=grade.学号AND curriculum.课程编号=grade.课程编号AND student_info.性别='男'

（4）查询每个学生的所选课程的最高成绩，要求列出学号、姓名、课程编号、分数。

SELECT student_info.学号,姓名,课程名称,分数

FROM student_info

INNER JOIN grade ON student_info.学号=grade.学号

INNER JOIN curriculum ON grade.课程编号=curriculum.课程编号

WHERE分数=(SELECT MAX(grade.分数)

FROM grade

WHERE grade.学号=student_info.学号)

（5）查询所有学生的总成绩，要求列出学号、姓名、总成绩，没有选修课程的学生的总成绩为空。

SELECT student_info.学号,学生姓名,总成绩

FROM student_info LEFT OUTER JOIN

(SELECT学号,SUM(分数)AS总成绩

FROM grade

GROUP BY学号)grade

ON student_info.学号=grade.学号