数据库实验报告 (2)

课程设计报告题目：数据库实验上机实验报告

专业班级：计算机科学与技术1210班

学号：U201215079

姓名：候宝峰

指导教师：

报告日期：2015-06-04

计算机科学与技术学院

目录

一、基本SQL操作（部分选做） (1)

1）数据定义 (1)

2）数据更新 (3)

3）用SQL语句完成下述查询需求： (4)

二、DBMS综合运用（部分选做） (8)

1）学习sqlserver的两种完全备份方式：数据和日志文件的脱机备份、系统的备份功能（选做）。 (8)

2）学习系统的身份、权限配置操作 (9)

3）了解SQLSERVER的存储过程、触发器、函数实现过程 (14)

三、实验总结 (17)

1）实验问题及解决 (17)

2）实验心得 (18)

一、基本SQL操作（部分选做）

1）数据定义

参照下面的内容建立自己实验所需的关系数据

创建三个关系：

商品表【商品名称、商品类型】

GOODS【GNAME char（20）,GTYPE char（10）】

主关键字为（商品名称）。商品类型为（电器、文具、服装。。。）

商场【商场名称,所在地区】

PLAZA【PNAME char（20），PAREA c har（20）】

主关键字为商场名称。所在地区为（洪山、汉口、汉阳、武昌。。。）

销售价格表【商品名称、商场名称、当前销售价格、目前举办活动类型】

SALE【GNAME char（20），PNAME char（20），PRICE FLOAT，ATYPE c har（10）】

主关键字为（商品名称、商场名称）。举办活动类型为（送券、打折），也可为空值，表示当前未举办任何活动。表中记录如（‘哈森皮靴’，‘亚贸广场’，200，‘打折’），同一商场针对不同的商品可能采取不同的促销活动。

create table goods(gname char(20) primary key,gtype char(10));

create table plaza(pname char(20) primary key,parea char(20));

create table sale

(gname char(20),

pname char(20),

price FLOAT,

atype char(10)check (atype in('送券','打折','')),

primary key(gname,pname),

foreign key(gname)references goods(gname),

foreign key(pname)references plaza(pname));

图1 goods表

图2 plaza表

图3 sale表

2）数据更新

（1）向上述表格中用sql语句完成增、删、个、改的操作；

增加记录：insert into goods（gname，gtype）values（’anta’,’服装’）；

删除记录：delete from goods where gname=’南孚’and gtype=’电池’;

更新记录：update goods set gtype=’电器’ where gname=’飞科’;

（2）编写一个触发器，并测试该触发器；

当插入一个“anta”货物时不能把它售价低于350 ，如果低于350 就修改成350

create trigger [dbo].[Chang_Update_sale] on [dbo].[sale]

after insert as

if(select count(*)from sale,inserted where sale.gname='anta'and sale.price<350)=1 begin

update sale set sale.price=350

where sale.gname='anta'and sale.pname='家乐福'

end

图4 触发器执行图

如图，将原有的记录删掉后插入一条新纪录，应该是俩行受影响，由于触发器修改最小值，则会显示三行受影响，查表知，是触发器有效执行了。

（3）将SALE表中的打折记录插入到新表SALE_CHEAP中，并基于SALE_CHEAP表创建一个统计每个商场各自打折商品平均价格的视图。

create view SALE_CHEAP as

select sale.pname,sale.gname,sale.price

from sale where atype='打折';

select pname,avg(price) avg

from SALE_CHEAP

group by(pname)

图5 视图

3）用SQL语句完成下述查询需求：

（1）查询所有以“打折”方式销售的商品的当前销售情况，并按照价格的降序排列；select GNAME,PNAME,PRICE

from sale

where ATYPE='打折'

order by price desc;

图6 查询1

2）查询所有没有任何活动的商品及其所在的商场，结果按照商品排序；

select GNAME,PNAME

from sale

where ATYPE is null

order by gname;

图7 查询2

3）查询价格在200～500元之间的商品名称、所在的商场名称、价格，结果按照商场名称排序；

select GNAME,PNAME,price

from sale

where price between 200 and 500

order by pname;

图8 查询3

4）查询每种商品的最低价格、商品名称；

select GNAME,min(price) min_price

from sale

group by gname;

图9 查询4

5）查询以“送券”方式销售的商品总数超过30种的商场名称；

select distinct pNAME

from sale

where atype='送券' and gname in(

select gNAME

from sale

group by gname

having count(*)>30 );

图10 查询5

6）查询以“送券”方式销售的商品总数超过30种的商场所在地区；

select pname, PAREA

from plaza

where pname in(

select distinct pNAME

from sale

where atype='送券' and gname in(

select gNAME

from sale

group by gname

having count(*)>30 ));

图11 查询6

7）查询价格为下列取值之一的商品名称、所在商场名称、目前举办活动的类型，（88、188、288、388、488、588、888）；

select gname,pname,atype

from sale

where price in(88,188,288,388,488,588,888);

图12 查询7

8）查询以“老”字开头的所有商品的名称；

select * from goods

where gname like '老%'

图13 查询8

9）查询同时销售“剃须刀”和“电池”的商场名称；

select pname

from sale

where gname='南孚'and pname in

(select pname

from sale

where gname=’飞科');

图14 查询9

10）查询不举办任何活动的商场；

select distinct plaza.pname,PAREA

from sale,plaza

where atype is null and sale.pname=plaza.pname;

图15 查询10

11）查询所销售的商品包含了“校园超市”所销售的所有商品的商场名称。select distinct pname

from sale

where gname in(

select gname

from sale

where pname='家乐福');

图16 查询11

二、DBMS综合运用（部分选做）

1）学习sqlserver的两种完全备份方式：数据和日志文件的脱机备份、系统的备份功能（选做）。

利用企业管理器：选择工具下的备份数据库：还以利用企业管理器设置自动备份计划等；直接拷贝数据文件。

把数据库的数据文件（*.mdf）和日志文件（*.ldf）都拷贝到目的服务器，在SQL Server Query Analyzer中用语句进行恢复：RESTORE DATABASE {数据库名| @数据库名变量} [ FROM <备份设备> [ ,...n ] ]

[ WITH [ RESTRICTED_USER ]

[ [ , ] { NORECOVERY | RECOVERY | STANDBY = undo_file_name } ]

]

RESTORE LOG {数据库名| @数据库名变量}

[ FROM <备份设备> [ ,...n ] ]

[ WITH

[ RESTRICTED_USER ]

[ [ , ] { NORECOVERY | RECOVERY | STANDBY = undo_file_name } ]

]

使得数据库恢复到备份状态。

2）学习系统的身份、权限配置操作

图17 sql server安全性决策

当SQL Server 2014 在Windows 上运行时，sysadmin 固定服务器角色成员可以指定下面两种身份验证模式之一：

Windows 身份验证模式

只进行Windows 身份验证。用户不能指定SQL Server 2014 登录ID。这是SQL Server 2014 的默认身份验证模式。不能为在Windows 98 上运行的SQL Server 实例指定Windows 身份验证模式，因为此操作系统不支持Windows 身份验证。

当用户通过Windows NT 4.0 或Windows用户帐户进行连接时，SQL Server 通过回叫Windows NT 4.0 或Windows以获得信息，重新验证帐户名和密码。

SQL Server 通过使用网络用户的安全特性控制登录访问，以实现与Windows NT 4.0 或Windows的登录安全集成。用户的网络安全特性在网络登录时建立，并通过Windows 域控制器进行验证。当网络用户尝试连接时，SQL Server 使用基于Windows 的功能确定经过验证的网络用户名。SQL Server 于是验证此人是否是如其所说的那个人，然后只基于网络用户名允许或拒绝登录访问，而不要求单独的登录名和密码。

说明如果用户试图通过提供空白登录名称连接到SQL Server 的实例，SQL Server 将使用Windows 身份验证。此外，如果用户试图使用特定的登录连接到配置为Windows 身份验证模式的SQL Server 实例，则将忽略该登录并使用Windows 身份验证。

与SQL Server 身份验证相比，Windows 身份验证有某些优点，主要是由于它与Windows NT 4.0 和Windows安全系统的集成。Windows NT 4.0 和Windows安全系统提供更多的功能，如安全验证和密码加密、审核、密码过期、最短密码长度，以及在多次登录请求无效后锁定帐户。

由于Windows NT 4.0 和Windows用户和组只由Windows NT 4.0 或Windows维护，因此当用户进行连接时，SQL Server 将读取有关该用户在组中的成员资格信息。如果对已连接用户的可访问权限进行更改，则当用户下次连接到SQL Server 实例或登录到Windows NT 4.0 或Windows时（取决于更改的类型），这些更改会生效。

混合模式

如果用户在登录时提供了SQL Server 2014 登录ID，则系统将使用SQL Server 身份验证对其进行验证。如果没有提供SQL Server 2014 登录ID 或请求Windows 身份验证，则使用Windows 身份验证对其进行身份验证。

当用户用指定的登录名称和密码从非信任连接进行连接时，SQL Server 通过检查是否已设置SQL Server 登录帐户，以及指定的密码是否与以前记录的密码匹配，自己进行身份验证。如果SQL Server 未设置登录帐户，则身份验证将失败，而且用户收到错误信息。

提供SQL Server 身份验证是为了向后兼容性，因为为SQL Server 7.0 版或更早的版本编写的应用程序可能要求使用SQL Server 登录和密码。另外，当SQL Server 实例在Windows 98 上运行时，必须使用SQL Server 身份验证，因为在Windows 98 上不支持Windows 身份验证模式。因此，SQL Server 在Windows 98 上运行时使用混合模式（但只支持SQL Server 身份验证）。

尽管建议使用Windows 身份验证，但对于Windows NT 4.0 和Windows客户端以外的其它客户端连接，可能需要使用SQL Server 身份验证。

说明当使用命名管道连接到在Windows NT 4.0 或Windows上运行的SQL Server 实例时，用户必须有连接到Windows NT 命名管道IPC \\\IPC$ 的权限。如果用户没有连接权限，则不能使用命名管道连接到SQL Server 实例，除非计算机上的Windows NT 4.0 或Windowsguest 帐户已启用（默认情况下禁用），或者给用户帐户授予"从网络访问该计算机"的权限。

设置SQL Server 2014 身份验证模式

SQL Server 2014 身份验证模式可以在安装过程中指定或使用SQL Server 企业管理器指定，如下图。

图18 安全设置

也可以通过修改注册表的方式来改变，如下图，它保存在

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer 节的LoginMode 中。

LoginMode的键值：1 表示Windows 身份验证模式，2 表示混合模式。

图19 注册表更改

注：改变模式后，必须重新启动SQL Server 2014 才会生效。

SQL Server 2014 联机丛书：

若要连接到Microsoft? SQL Server? 2000 实例，只需给应用程序提供下面中的两条信息：

运行SQL Server 实例的计算机所在的网络名称。

实例名（可选，只有在连接到命名实例时才需要）。

登录标识符(ID)。

登录ID 是帐户标识符，用来控制对任何SQL Server 2014 系统的访问权限。SQL Server 2014 只有在首先验证了指定的登录ID 有效后，才完成连接。这种登录验证称为身份验证。

在登录属性中，有一个是默认数据库。当一个登录连接到SQL Server 时，这个默认数据库就变成该连接的当前数据库，除非该连接请求指定另一个数据库作为当前数据库。

登录ID 仅能使您连接到SQL Server 实例。特定数据库内的权限由用户帐户控制。数据库管理员将您的登录帐户映射到您有权访问的任何数据库中的用户帐户。

Windows 身份验证

SQL Server 2014 sysadmin 固定服务器角色成员必须首先向SQL Server 2014 指定所有允许连接到SQL Server 2014 的Microsoft Windows NT? 或Microsoft Windows? 2000 帐户或组。当使用Windows 身份验证时，在连接到SQL Server 2014 时不必指定登录ID 或密码。用户对SQL Server 2014 的访问权限由Windows NT 或Windows帐户或组控制，当登录到客户端上的Windows 操作系统时需接受身份验证。

当连接到SQL Server 2014 时，SQL Server 2014 客户端软件向SQL Server 2014 请求Windows 信任连接。直到客户端使用有效的Windows 帐户成功登录后，Windows 才打开信任连接。信任连接的属性包括打开连接的客户端的Windows NT 和Windows组及用户帐户。SQL Server 2014 从信任连接属性中得到用户的帐户信息，并将它们与定义为有效SQL Server 2014 登录的Windows 帐户相匹配。如果SQL Server 2014 找到匹配的项，则接受这个连接。当使用Windows身份验证连接到SQL Server 2014 时，用户标识即是Windows NT

或Windows组或用户帐户。

Microsoft Windows Me 和Windows 98 操作系统不支持服务器端的信任连接API。SQL Server 在Windows Me 或Windows 98 上运行时不支持Windows 身份验证。用户在连接时必须提供SQL Server 登录帐户。当SQL Server 在Windows NT 或Windows上运行时，Windows Me、Windows 98 和Windows 95 客户端可以使用Windows身份验证与其连接。

sysadmin 固定服务器角色成员首先向SQL Server 2014 指定所有有效的SQL Server 2014 登录帐户和密码。这些登录帐户和密码与用户的Microsoft Windows 帐户或网络帐户无关。当连接到SQL Server 2014 时，用户必须提供SQL Server 2014 登录帐户和密码。系统将通过用户的SQL Server 2014 登录帐户在SQL Server 2014 中标识用户。

由此可以看出，SQL Server 2014 安全控制是由登录>>用户>>权限来得到控制的。因此要连接SQL Server 2014，首先要经过身份验证，要通过身份验证，就必须拥有登录ID。

新建用户：ceshi

图20 建立新用户

分配选择goods表权限给用户ceshi ：grant select on goods to ceshi；

图21 分配select权限

分配插入删除权限给ceshi：grant insert and delete on plaza to ceshi；

图22 分配insert delete权限

使用sql server登陆验证方式登陆ceshi，展开数据库，只有goods表和plaza表，

图23 ceshi用户所有表

收回ceshi用户所有的权限：Revoke all on goods from ceshi

Revoke all on plaza from ceshi

图24 回收所有的权限

3）了解SQLSERVER的存储过程、触发器、函数实现过程通过查看SQLSERVER的联机帮助文档和示例，体会存储过程、函数和触发器的原理，

尝试编制具备基本功能的存储过程、触发器和函数的实际例子，查看其执行效果。

创建一个存储过程，其中包含插入和删除操作，经验证存储结果，过程生效。

Create Procedure MyProcedure

AS

Begin

Set NOCOUNT ON;

Set XACT_ABORT ON;

Begin Tran

Delete from goods where gname='anta';

Insert into plaza values('校园超市','武广');

Commit Tran

End

创建一个级联删除触发器，使得在sale中删除一个货物时删除goods表中同种货物。create trigger Chang_Update_sale on [dbo].[sale]

for delete

as

delete goods

from goods,sale

where sale.gname=goods.gname;

图25 级联删除结果1

图26 级联删除结果2

创建一个函数选择goods表所有内容;

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:

-- Create date:

-- Description:

-- =============================================

CREATE FUNCTION typoints()

RETURNS TABLE

AS

RETURN

(

SELECT*from goods

);

Select 选择该函数，由于是表值函数所以用select * from[dbo].[typoints]()调用，选择goods 表内容，有结果可以看出，该函数执行成功。与直接执行函数体SELECT*from goods

的结果是一样的。

图27 函数执行结果

三、实验总结

1）实验问题及解决

1、由于安装的是较新版的数据库（sql server 2014）使得有些语句与书上不符，比如建触发器语句：create trigger [dbo].[Chang_Update_sale] on [dbo].[sale]

after insert as

if(select count(*)from sale,inserted where sale.gname='anta'and sale.price<350)=1 begin

update sale set sale.price=350

where sale.gname='anta'and sale.pname='家乐福'

end

书上有before 语句，2014 版中不支持before语句但有instead of 语句，同时也不支持or连接两个操作的说明，还是要查找一些资料才能理解语句的内涵。才能知道虽然变了部分语法，但是还是换汤不换药的方式。

2、建立视图是，总是建立失败，第一次未能插入到新视图里，第二次是想直接建立一个平均后的视图，经过多次尝试失败，还使用了group语句去完善自己的视图，虽然实验简单，但是同样体会到数据库的重要性，同样操作不容马虎，不能有一点一点的失误。

3、所学标准语句与实际所用的数据库产品标准的差异问题。

虽然很多时候核心的语句是一致的，但是有一些地方不一样。比如grant时，标准语句需要在表明前加上table，但是在使用SQL Server 2014时，发现语句有问题，和同学讨论了很久，改了很多关键字都不起效，后来在网上找到实际的grant语句，进行仔细地比较才发现在SQL Server 2014里面是不需要“table”这个关键字的。

4、创建外码的问题；

一开始，随手就先创建sale关系，然后创建goods关系。

但是后来发现，由于sale关系用到了goods关系的外码而无法实现。所以提示创建失败。最后，只好先创建goods关系。这应该是一个教训，在创建有交互关系的时候，需要判断谁先创建，避免不必要的错误

2）实验心得

通过这次数据库实验（虽然没来的检查）发现很多的书上的知识用到实践中会出现各种各样的错误，也告诉我们实践是检验知识的唯一真理。总的来说，受益匪浅。在这些天中，我们学到了很多东西，包括建表，导入数据，查询，插入。没接触的时候总是觉得它比较深奥或是不可接近的新型语言，尽管自己对C语言非常熟悉，但还是有些心理上的陌生感。学习数据库就和我们平时的其它科目学习一样感觉它有永无止境的知识。我认识它是从我接触实验运作开始的，刚开始就是建立数据库，两种验证模式，没什么东西但还觉得不错。进而就是操作语言了，紧接着就是触发器的使用，进而对数据库高级的使用，等等。

学了这门课以后发现和我想的基本是一样的，老师对学生也比较和蔼可亲，对我们要求也不是很紧。让每个人都觉得轻轻松松就能把这门课程学完，没有多么紧张的作业，也没有太苛刻的要求。学习SQL Sever数据库后感觉可分两大块，一块是开发，一块是管理。开发主要是写写存储过程、触发器什么的，还有就是用Oracle的Develop工具做form。开发还需要有较强的逻辑思维和创造能力，自己没有真正做过。管理则需要对SQL Sever数据库的原理有深刻的认识，有全局操纵的能力和紧密的思维，责任较大，因为一个小的失误就会弄掉整个数据库，相对前者来说，后者更看重经验。这些东西都是从老师哪里和朋友的讨论中得到的心得，也希望其他朋友能多多向老师和朋友请教，如果是个人单独靠自己来完成一个完美的数据库我觉得比较困难，现在基本上都是团队类型的，而且他们的效率高开发的周期也快。

SQL Server数据库的实验学习使我对数据库的有了新的进步，以后再看到也就不至于什么也不懂，其实那么多数据库我觉得学好一门就行，只是他们的语言可能不大一样，学好一门后就可去认识其它的，这样应该有事半功倍的效果。因此，我感谢数据库老师给了我有用的知识，以便我在以后学习或认识更多的内容能有新的方法和思维，也能更加有效和快速的去消化吸收新的东西。希望在今后中，SQL Server能给我更多帮助。感谢学校开设这样一门优秀使用的课程，让我对数据库有了更深的了解