华科数据库实验报告

课 程 实 验 报 告

课程名称： 数据库系统概论 专业班级： 学 号： 姓 名： 指导教师： 报告日期：

计算机科学与技术学院

1

2

目录

目录................................................................................................................................ 3 一、实验目的................................................................................................................ 5 二、实验原理................................................................................................................ 5

1.SQL Server 2008查询分析器.............................................................................................. 5 2.DDL使用方法 ..................................................................................................................... 5 3.DML使用方法 .................................................................................................................... 6 4.DCL 的使用方法 ................................................................................................................. 7 5.数据库的备份和恢复 ......................................................................................................... 8

三、实验内容................................................................................................................ 9

实验1: 基本表的创建、数据插入 ..................................................................................... 9 实验2：数据查询 ............................................................................................................... 10 实验3：数据修改、删除 ................................................................................................... 10 实验4：视图的操作 ........................................................................................................... 10 实验5：库函数，授权控制 ............................................................................................... 10 实验6：数据库的备份、恢复 ........................................................................................... 11

四、实验过程.............................................................................................................. 11

实验1: 基本表的创建、数据插入 .................................................................................... 11 实验2: 数据查询 ................................................................................................................ 14 实验3: 数据修改、删除 .................................................................................................... 16 实验4：视图的操作 ........................................................................................................... 18 实验5：库函数，授权控制 ............................................................................................... 19 实验6：数据库的备份、恢复 ........................................................................................... 20

五、心得体会.............................................................................................................. 21

3

4

一、实验目的

掌握SQL Server 2008 的工具使用 掌握DDL的使用方法 掌握DML的使用方法 掌握SELECT命令使用方法 掌握DCL的使用方法 掌握数据库的备份和恢复

二、实验原理

1.SQL Server 2008查询分析器

查询分析器是一个重要工具，实验中的所有SQL语言命令均需在查询分析器中输入、编辑运行。

2.DDL使用方法

1） 数据库创建

在查询分析器中执行下列语句即可在默认的设备上创建新的数据库ems。 CREATE DATABASE database_name 2） 基本表的建立

创建基本表的命令为：CREATE TABLE table_name，在该命令中定义主码和外码时，可以使用列约束（Column Constraint）或表约束（Table Constraint）子句。

创建基本表时，应先选择包含表的数据库。 3） 视图的建立

视图是组成数据库体系结构——三级模式两级映像结构中的外模式的基本单元，SQL-Server的视图定义命令为：

CREATE VIEW view-name AS SELECT statement

视图是用于定义终端用户数据来源的。在视图定义中可以使用复杂的SELECT命令。

5

3.DML使用方法

SQL的DML包括插入（INSERT）、删除(DELETE)、修改(UPDATE)等命令。DML命令的执行是可能造成数据库不一致的根源。因此，每一条语句在执行前，SQL-Server都要验证语句是否符合完整性要求，包括实体完整性、参照完整性、用户定义完整性。

1）INSERT命令 SQL语言的插入命令：

INSERT INTO table-name (column-list) VALUES(values-list)，可以完成数据输入功能。

2） DELETE命令 SQL的删除命令：

DELETE FROM table-name [WHERE condition-expression];

执行DELETE命令后，系统会删除满足命令中条件表达式的所有元组。这种删除只是逻辑的。因此，当再次插入一个与被删除的元组具有相同关键字的元组时，被认为违反了实体完整性。

3） UPDATE命令 SQL的修改命令为： UPDATE table-name

SET column-name = expression [WHERE condition-expression];

执行UPDATE命令后，系统会按照SET子句修改满足命令中条件表达式的所有元组。如果使用修改命令更新关键字的值，而该关键字在其他表中作为外码存在时，操作可能违反参照完整性。系统将拒绝执行。

4） SELECT命令

SELECT 命令是SQL语言中使用最频繁的命令，其变化形式繁多，灵活运用SELECT 命令可以完成任意复杂的查询要求。SELECT 命令的基本语法为:

SELECT query_expression|column-list|* FROM table_name_list|view_name_list [WHERE condition-expression]

[GROUP BY [HAVING condition-expression]]

6

[ORDER BY order_expression| column [ASC|DESC]][,?n];

4.DCL 的使用方法

1）SQL Server 登录认证 ? 新登录的创建

新的登录者可以是WINDOWS的用户或用户组，也可以是使用SQL Server认证模式的登录帐号。

设定WINDOWS的用户为SQL Server登录者时使用sp_grantlogin。创建新的使用SQL Server认证模式的登录帐号时使用sp_addlogins。

? 登录的删除

删除基于使用SQL Server认证模式的SQL Server的登录可以使用sp_droplogin。但是，如果与该登录匹配的数据库用户仍存在sysuser表中，则不能删除该登录账号。删除WINDOWS的用户或用户组时，使用sp_revokelogin，拒绝WINDOWS的用户或用户组连接到SQL Server时，使用sp_decnylogin。

? 登陆的查看

sp_helplogins可以用来显示SQL Server的所有登录者的信息。如图29是显示数据库ems 的所有登录者信息的代码。

2） 用户管理

SQL Server利用下列系统过程管理数据库用户：

sp_adduser sp_grangdbaccess sp_dropuser Sp_dropuser

sp_revokedbaccess sp_helpuser 。其中sp_adduser Sp_dropuser 是为了保持与以前版本相兼容，因此不主张使用，在SQL Server 2000中，建议使用sp_grangdbaccess sp_revokedbaccess 。

? 创建新数据库用户

所有的数据库用户（除了guest）均必须与某一登录账号相匹配，因此在使用系统过程创建新数据库用户时，不但要指出新数据库用户的名称，还必须指出一个已经存在的登录账号。系统过程sp_grantdbaccess用于为SQL Server 登录用户或NT用户或用户组建立相匹配的数据库用户帐号。

? 删除数据库用户

系统过程sp_revokedbaccess用于将数据库用户从当前数据库中删除，删除后与其匹配的登录即无法再访问该数据库了。

? 查看数据库用户信息

7

sp_helpuser可用来显示当前数据库的指定用户信息。

3） 用户授权管理

对于登录到SQL Server的合法数据库用户，必须获得对数据库操作的授权。在SQL Server中包括两种类型的权限，即对象权限和语句权限。对象权限总是针对表、视图、存储过程而言，它决定了能对这些对象执行哪些操作（如UPDATE DELETE INSERT SELECT EXECUTE）。不同类型的对象支持不同类型的操作。

语句权限指数据库用户执行某种语句的操作权，如创建数据库、表、存储过程等。这些语句虽然（如CREATE 命令）包含有操作对象，但这些对象在操作前并不存在于数据库中。

5.数据库的备份和恢复

备份是指对SQL Server 数据库或事务日志进行拷贝，如果数据库因意外而损坏，备份文件可以用来恢复数据库。SQL Server 2000 中有四种备份类型，分别是：数据库备份、事务日志备份、差异备份、文件和文件组备份。在使用是可以根据实际情况选择一种或几种的混合应用。

1)备份设备

在进行备份以前必须创建备份设备。备份设备是用来存储数据库、事务日志、文件和文件组备份的存储介质。可以是磁盘、磁带或管道。

SQL Server 2000 提供了两种创建备份的途径，即可视化方式和命令方式。 用命令备份数据库

使用BACKUP 命令进行备份操作。 备份完整数据库

/* EXEC sp_addumpdevice 'DISK','backupdevice_name', 'd:\\backupdev\\ems.bak'目标路径*/

BACKUP DATABASE database TO backupdevice_name

2)恢复数据库

8

使用命令恢复数据库

数据库恢复命令为：RESTORE DATABASE/LOG。

三、实验内容

实验1: 基本表的创建、数据插入

（1）建立教学管理中的三个基本表：

Students（S#，SNAME，AGE，SEX） 学生（学号，姓名，年龄，性别） Courses（C#，CNAME，SCORE，PC＃）课程（课程号，课程名，学分，先行课号）

SC（S#，C#，GRADE） 选修（学号，课程号，成绩） （2）用INSERT 命令输入数据。

表 1基本表Students的数据

S1 S2 S3 S4 S6 S8 LU YIN XU QU PAN DONG 20 19 18 18 14 24 M M F F M M 表 2基本表Courses的数据

C1 C2 C3 C4 C5 数学 英语 数据结构 数据库 网络 4 8 4 3.5 4 M M F F M 表 3基本表SC的数据

C1 S1 85 S2 90 S3 89 S4 84 S6 88 S8 87 9

C2 C3 C4 C5 73 88 89 73 NULL 80 85 NULL 86 82 NULL 75 90 92 85 NULL 88 87 实验2：数据查询

（1）列出选修课程号为C2的学生学号与姓名。 （2）检索选修课程名为“数学”的学生学号与姓名。 （3）检索没有选修C2课程的学生姓名与年龄。 （4）检索选修全部课程的学生姓名。

实验3：数据修改、删除

（1） 把C2课程的非空成绩提高10％。

（2） 在SC表中删除课程名为“物理”的成绩所对应的元组。 （3） 在S和SC表中删除学号为S8的所有数据。

实验4：视图的操作

（1） 建立男生学生的视图，属性包括学号，姓名，选修课程名和成绩。 （2） 在男生视图中查询平均成绩大于80分的学生学号和姓名.

实验5：库函数，授权控制

（1） 计算每个学生选修课程的门数、平均成绩。

（2） 建立一个合法的用户，将SC表的查询权限授予该用户。

（3） 使用GRANT语句，把对基本表students、Courses、SC的使用权限授予其

10

他用户。

实验6：数据库的备份、恢复

（1） 使用完全备份将你的实验数据库备份到软盘。 （2） 删除你所建立的数据库。 （3） 恢复你的数据库。

（4） 在恢复后的数据库上撤销你建立的基本表和视图。

四、实验过程

实验1: 基本表的创建、数据插入

（1）创建基本表 create database ems use ems; go

create table students(sno char(9) primary key, sname char(20) not null, age char(3), sex char(6));

create table courses(cno char(9) primary key, cname char(20) not null, score int , pc char(3));

create table sc(sno char(9) foreign key references students(sno), cno char(9), grade int,

foreign key(cno) references courses(cno));

11

（2）用INSERT命令输入数据 use ems; go

insert into students values('S1','LU',20,'M'); insert into students values('S2','YIN',19,'M'); insert into students values('S3','XU',18,'F'); insert into students values('S4','QU',18,'F'); insert into students values('S6','PAN',14,'M'); insert into students values('S8','DONG',24,'M'); insert into Courses values('C1' ,'数学' , 4 , 'M'); insert into Courses values('C2' ,'英语' , 8 , 'M'); insert into Courses values('C3' ,'数据结构' , 4 , 'F'); insert into Courses values('C4' ,'数据库' , 3.5 , 'F'); insert into Courses values('C5' ,'网络' , 4 , 'M'); insert into SC values('S1' ,'C1' , 85); insert into SC values('S2' ,'C1' , 90); insert into SC values('S3' ,'C1' , 89); insert into SC values('S4' ,'C1' , 84); insert into SC values('S6' ,'C1' , 88); insert into SC values('S8' ,'C1' , 87); insert into SC values('S1' ,'C2' , 73); insert into SC values('S2' ,'C2' , NULL); insert into SC values('S3' ,'C2' , 86); insert into SC values('S4' ,'C2' , 82); insert into SC values('S6' ,'C2' , 75); insert into SC values('S8' ,'C2' , 85); insert into SC values('S1' ,'C3' , 88); insert into SC values('S2' ,'C3' , 80); insert into SC values('S6' ,'C3' , 90); insert into SC values('S8' ,'C3' , NULL);

12

insert into SC values('S1' ,'C4' , 89); insert into SC values('S2' ,'C4' , 85); insert into SC values('S4' ,'C4' , NULL); insert into SC values('S6' ,'C4' , 92); insert into SC values('S8' ,'C4' , 88); insert into SC values('S1' ,'C5' , 73); insert into SC values('S2' ,'C5' , NULL); insert into SC values('S8' ,'C5' , 87); 插入后的三个表格

图 1学生表格

图 2课程表格

13

图 3cs表格

实验2: 数据查询

（1） 列出选修课程号为C2的学生学号与姓名 use ems; go

select sc.sno,sname from students,sc

where sc.cno='C2' and sc.sno=students.sno

图 4查询一

（2）检索选修课程名为“数学”的学生学号与姓名

14

select sc.sno,sname from students,sc,courses where courses.cname='数学' and courses.cno=sc.cno and students.sno=sc.sno

图 5查询二

(3) 检索没有选修C2课程的学生姓名与年龄。 select sname,age from students where not exists(select *

from sc where sc.cno='c2' and sno=students.sno);

图 6查询三

(4) 检索选修全部课程的学生姓名。 select sname from students where not exists(select *

from courses where not exists( select *

from sc

15

where sno=students.sno and cno=courses.cno));

图 7查询四

实验3: 数据修改、删除

（1） 把C2课程的非空成绩提高10％。 use ems; go update sc

set grade=grade*1.1

where sc.cno='c2' and sc.cno is not null

图 8修改

（2） 在SC表中删除课程名为“物理”的成绩所对应的元组。delete from SC

where cno in (select cno from courses where cname='物理')

16

图 9删除

（3） 在S和SC表中删除学号为S8的所有数据。 delete from sc where sc.sno='s8' delete from students where students.sno='s8'

图 10删除二

17

实验4：视图的操作

（1） 建立男生学生的视图，属性包括学号，姓名，选修课程名和成绩。 use ems; go

create view student_m(sno,sname,cname,grade) as

select students.sno,students.sname,cname,grade from sc,students,courses

where students.sno=sc.sno and courses.cno=sc.cno and sex='M'

图 11视图

（2） 在男生视图中查询平均成绩大于80分的学生学号和姓名。 use ems; go

select distinct students.sno,students.sname from student_m,students

where student_m.sno=students.sno and grade>80

18

图 12视图查询

实验5：库函数，授权控制

（1） 计算每个学生选修课程的门数、平均成绩。 use ems; go

select students.sno,students.sname,count(cno) 选修门数,avg(grade) 平均成绩from students,sc

where students.sno=sc.sno group by students.sno,sname

图 13平均成绩

（2） 建立一个合法的用户，将SC表的查询权限授予该用户。 use ems go

exec sp_addlogin ems,ems use ems go

exec sp_grantdbaccess 'ems','ems'

19

图 14授权

（3） 使用GRANT语句，把对基本表students、Courses、SC的使用权限授予其他用户。 use ems go

GRANT all privileges --授予权限 ON Courses TO guest;

GRANT all privileges --授予权限 ON students TO guest;

GRANT all privileges --授予权限 ON sc TO guest;

图 15授权二

实验6：数据库的备份、恢复

（1） 使用完全备份将你的实验数据库备份到软盘。 use ems go

EXEC sp_addumpdevice 'DISK','backupdevice_ems', 'd:\\backupdev\\ems.bak'

20

BACKUP DATABASE ems TO backupdevice_ems

图 16备份

（2） 删除你所建立的数据库。

（3） 恢复你的数据库。 restore database ems from beifen_ems

在目录中右键删除即可。

图 17恢复

（4） 在恢复后的数据库上撤销你建立的基本表和视图。 use ems drop table sc drop table students drop table courses drop view student_m

21

图 18撤销

五、心得体会

通过本次试验，我掌握了sql server 3008 的使用方法，如何进行创建数据库及在该数据库基础上完成相关的创建表视图等操作，以及进行数据库的查询删除 等功能。同时对于数据库的备份还原操作也基本掌握了。

实验一开始也基本上是通过参考实验题目及sql使用方法上一步步做下来，但是一开始就出现了重复创建的数据库的问题，其实原因也比较简单，在查询分 析器中，新建了查询，但是当已经创建了database之后，之后的操作不能再该代码后面继续进行，而应该使用use database-name指令在该数据库下进行其他的查询等操作。至于对于创建表等操作，必须清楚所创建的数据中那些是主键外键， 通过这次实验其实也是对于我们课程内容的一种巩固。在之后的查询操作及删除操作中偶尔也会出现一些小的问题，但在错误提示栏中也基本写明了错误的原因，例如需要group by来进行分组操作等的；同时对于查询操作中会出现一个常见的问题就是输出结果中产生的数据项常常是大部分重复的，因此在select操作中要写distinct，默认的操作是不删除重复的元组。在进行实验五的过程中遇到了较为麻烦的问题，及创建用户之后在登录时总是提示已连接但是产生错误，通过分析代码以及问同学发现可能是某处设置的问题，才发现要先登录在进行创建，即需要两步操作，同时实验过程中要时刻注意是否创建成功。

这次实验中出现的问题都比较简单解决。这次实验的目的也主要是一个熟悉sql服务器的过程，同时通过这次实验进一步掌握数据库的各种操作以及对于课堂的内容进行进一步的巩固与理解。

22

本文来源：https://www.bwwdw.com/article/h51.html