数据库实验报告 2.docx
《数据库实验报告 2.docx》由会员分享，可在线阅读，更多相关《数据库实验报告 2.docx（22页珍藏版）》请在冰豆网上搜索。
数据库实验报告2
课程设计报告
题目：
数据库实验上机实验报告
专业班级：
计算机科学与技术1210班
学号：
U201215079
姓名：
候宝峰
指导教师：
报告日期：
2015-06-04
计算机科学与技术学院
一、基本SQL操作（部分选做）
1）数据定义
参照下面的内容建立自己实验所需的关系数据
创建三个关系：
商品表【商品名称、商品类型】
GOODS【GNAMEchar（20）,GTYPEchar（10）】
主关键字为（商品名称）。
商品类型为（电器、文具、服装。
。
。
）
商场【商场名称,所在地区】
PLAZA【PNAMEchar（20），PAREAchar（20）】
主关键字为商场名称。
所在地区为（洪山、汉口、汉阳、武昌。
。
。
）
销售价格表【商品名称、商场名称、当前销售价格、目前举办活动类型】
SALE【GNAMEchar（20），PNAMEchar（20），PRICEFLOAT，ATYPEchar（10）】
主关键字为（商品名称、商场名称）。
举办活动类型为（送券、打折），也可为空值，表示当前未举办任何活动。
表中记录如（‘哈森皮靴’，‘亚贸广场’，200，‘打折’），同一商场针对不同的商品可能采取不同的促销活动。
createtablegoods（gnamechar（20）primarykey,gtypechar（10））;
createtableplaza（pnamechar（20）primarykey,pareachar（20））;
createtablesale
（gnamechar（20）,
pnamechar（20）,
priceFLOAT,
atypechar（10）check（atypein（'送券','打折',''））,
primarykey（gname,pname）,
foreignkey（gname）referencesgoods（gname）,
foreignkey（pname）referencesplaza（pname））;
图1goods表
图2plaza表
图3sale表
2）数据更新
（1）向上述表格中用sql语句完成增、删、个、改的操作；
增加记录：
insertintogoods（gname，gtype）values（’anta’,’服装’）；
删除记录：
deletefromgoodswheregname=’南孚’andgtype=’电池’;
更新记录：
updategoodssetgtype=’电器’wheregname=’飞科’;
（2）编写一个触发器，并测试该触发器；
当插入一个“anta”货物时不能把它售价低于350，如果低于350就修改成350
createtrigger[dbo].[Chang_Update_sale]on[dbo].[sale]
afterinsertas
if（selectcount（*）fromsale,insertedwheresale.gname='anta'andsale.price<350）=1
begin
updatesalesetsale.price=350
wheresale.gname='anta'andsale.pname='家乐福'
end
图4触发器执行图
如图，将原有的记录删掉后插入一条新纪录，应该是俩行受影响，由于触发器修改最小值，则会显示三行受影响，查表知，是触发器有效执行了。
（3）将SALE表中的打折记录插入到新表SALE_CHEAP中，并基于SALE_CHEAP表创建一个统计每个商场各自打折商品平均价格的视图。
createviewSALE_CHEAPas
selectsale.pname,sale.gname,sale.price
fromsalewhereatype='打折';
selectpname,avg（price）avg
fromSALE_CHEAP
groupby（pname）
图5视图
3）用SQL语句完成下述查询需求：
（1）查询所有以“打折”方式销售的商品的当前销售情况，并按照价格的降序排列；
selectGNAME,PNAME,PRICE
fromsale
whereATYPE='打折'
orderbypricedesc;
图6查询1
2）查询所有没有任何活动的商品及其所在的商场，结果按照商品排序；
selectGNAME,PNAME
fromsale
whereATYPEisnull
orderbygname;
图7查询2
3）查询价格在200～500元之间的商品名称、所在的商场名称、价格，结果按照商场名称排序；
selectGNAME,PNAME,price
fromsale
wherepricebetween200and500
orderbypname;
图8查询3
4）查询每种商品的最低价格、商品名称；
selectGNAME,min（price）min_price
fromsale
groupbygname;
图9查询4
5）查询以“送券”方式销售的商品总数超过30种的商场名称；
selectdistinctpNAME
fromsale
whereatype='送券'andgnamein（
selectgNAME
fromsale
groupbygname
havingcount（*）>30）;
图10查询5
6）查询以“送券”方式销售的商品总数超过30种的商场所在地区；
selectpname,PAREA
fromplaza
wherepnamein（
selectdistinctpNAME
fromsale
whereatype='送券'andgnamein（
selectgNAME
fromsale
groupbygname
havingcount（*）>30））;
图11查询6
7）查询价格为下列取值之一的商品名称、所在商场名称、目前举办活动的类型，（88、188、288、388、488、588、888）；
selectgname,pname,atype
fromsale
wherepricein（88,188,288,388,488,588,888）;
图12查询7
8）查询以“老”字开头的所有商品的名称；
select*fromgoods
wheregnamelike'老%'
图13查询8
9）查询同时销售“剃须刀”和“电池”的商场名称；
selectpname
fromsale
wheregname='南孚'andpnamein
（selectpname
fromsale
wheregname=’飞科'）;
图14查询9
10）查询不举办任何活动的商场；
selectdistinctplaza.pname,PAREA
fromsale,plaza
whereatypeisnullandsale.pname=plaza.pname;
图15查询10
11）查询所销售的商品包含了“校园超市”所销售的所有商品的商场名称。
selectdistinctpname
fromsale
wheregnamein（
selectgname
fromsale
wherepname='家乐福'）;
图16查询11
二、DBMS综合运用（部分选做）
1）学习sqlserver的两种完全备份方式：
数据和日志文件的脱机备份、系统的备份功能（选做）。
利用企业管理器：
选择工具下的备份数据库：
还以利用企业管理器设置自动备份计划等；直接拷贝数据文件。
把数据库的数据文件（*.mdf）和日志文件（*.ldf）都拷贝到目的服务器，在SQLServerQueryAnalyzer中用语句进行恢复：
RESTOREDATABASE{数据库名|@数据库名变量}
[FROM<备份设备>[,...n]]
[WITH[RESTRICTED_USER]
[[,]{NORECOVERY|RECOVERY|STANDBY=undo_file_name}]
]
RESTORELOG{数据库名|@数据库名变量}
[FROM<备份设备>[,...n]]
[WITH
[RESTRICTED_USER]
[[,]{NORECOVERY|RECOVERY|STANDBY=undo_file_name}]
]
使得数据库恢复到备份状态。
2）学习系统的身份、权限配置操作
图17sqlserver安全性决策
当SQLServer2014在Windows上运行时，sysadmin固定服务器角色成员可以指定下面两种身份验证模式之一：
Windows身份验证模式
只进行Windows身份验证。
用户不能指定SQLServer2014登录ID。
这是SQLServer2014的默认身份验证模式。
不能为在Windows98上运行的SQLServer实例指定Windows身份验证模式，因为此操作系统不支持Windows身份验证。
当用户通过WindowsNT4.0或Windows用户帐户进行连接时，SQLServer通过回叫WindowsNT4.0或Windows以获得信息，重新验证帐户名和密码。
SQLServer通过使用网络用户的安全特性控制登录访问，以实现与WindowsNT4.0或Windows的登录安全集成。
用户的网络安全特性在网络登录时建立，并通过Windows域控制器进行验证。
当网络用户尝试连接时，SQLServer使用基于Windows的功能确定经过验证的网络用户名。
SQLServer于是验证此人是否是如其所说的那个人，然后只基于网络用户名允许或拒绝登录访问，而不要求单独的登录名和密码。
说明如果用户试图通过提供空白登录名称连接到SQLServer的实例，SQLServer将使用Windows身份验证。
此外，如果用户试图使用特定的登录连接到配置为Windows身份验证模式的SQLServer实例，则将忽略该登录并使用Windows身份验证。
与SQLServer身份验证相比，Windows身份验证有某些优点，主要是由于它与WindowsNT4.0和Windows安全系统的集成。
WindowsNT4.0和Windows安全系统提供更多的功能，如安全验证和密码加密、审核、密码过期、最短密码长度，以及在多次登录请求无效后锁定帐户。
由于WindowsNT4.0和Windows用户和组只由WindowsNT4.0或Windows维护，因此当用户进行连接时，SQLServer将读取有关该用户在组中的成员资格信息。
如果对已连接用户的可访问权限进行更改，则当用户下次连接到SQLServer实例或登录到WindowsNT4.0或Windows时（取决于更改的类型），这些更改会生效。
混合模式
如果用户在登录时提供了SQLServer2014登录ID，则系统将使用SQLServer身份验证对其进行验证。
如果没有提供SQLServer2014登录ID或请求Windows身份验证，则使用Windows身份验证对其进行身份验证。
当用户用指定的登录名称和密码从非信任连接进行连接时，SQLServer通过检查是否已设置SQLServer登录帐户，以及指定的密码是否与以前记录的密码匹配，自己进行身份验证。
如果SQLServer未设置登录帐户，则身份验证将失败，而且用户收到错误信息。
提供SQLServer身份验证是为了向后兼容性，因为为SQLServer7.0版或更早的版本编写的应用程序可能要求使用SQLServer登录和密码。
另外，当SQLServer实例在Windows98上运行时，必须使用SQLServer身份验证，因为在Windows98上不支持Windows身份验证模式。
因此，SQLServer在Windows98上运行时使用混合模式（但只支持SQLServer身份验证）。
尽管建议使用Windows身份验证，但对于WindowsNT4.0和Windows客户端以外的其它客户端连接，可能需要使用SQLServer身份验证。
说明当使用命名管道连接到在WindowsNT4.0或Windows上运行的SQLServer实例时，用户必须有连接到WindowsNT命名管道IPC\\\IPC$的权限。
如果用户没有连接权限，则不能使用命名管道连接到SQLServer实例，除非计算机上的WindowsNT4.0或Windowsguest帐户已启用（默认情况下禁用），或者给用户帐户授予"从网络访问该计算机"的权限。
设置SQLServer2014身份验证模式
SQLServer2014身份验证模式可以在安装过程中指定或使用SQLServer企业管理器指定，如下图。
图18安全设置
也可以通过修改注册表的方式来改变，如下图，它保存在
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer节的LoginMode中。
LoginMode的键值：
1表示Windows身份验证模式，2表示混合模式。
图19注册表更改
注：
改变模式后，必须重新启动SQLServer2014才会生效。
SQLServer2014联机丛书：
若要连接到Microsoft©SQLServer™2000实例，只需给应用程序提供下面中的两条信息：
运行SQLServer实例的计算机所在的网络名称。
实例名（可选，只有在连接到命名实例时才需要）。
登录标识符（ID）。
登录ID是帐户标识符，用来控制对任何SQLServer2014系统的访问权限。
SQLServer2014只有在首先验证了指定的登录ID有效后，才完成连接。
这种登录验证称为身份验证。
在登录属性中，有一个是默认数据库。
当一个登录连接到SQLServer时，这个默认数据库就变成该连接的当前数据库，除非该连接请求指定另一个数据库作为当前数据库。
登录ID仅能使您连接到SQLServer实例。
特定数据库内的权限由用户帐户控制。
数据库管理员将您的登录帐户映射到您有权访问的任何数据库中的用户帐户。
Windows身份验证
SQLServer2014sysadmin固定服务器角色成员必须首先向SQLServer2014指定所有允许连接到SQLServer2014的MicrosoftWindowsNT®或MicrosoftWindows®2000帐户或组。
当使用Windows身份验证时，在连接到SQLServer2014时不必指定登录ID或密码。
用户对SQLServer2014的访问权限由WindowsNT或Windows帐户或组控制，当登录到客户端上的Windows操作系统时需接受身份验证。
当连接到SQLServer2014时，SQLServer2014客户端软件向SQLServer2014请求Windows信任连接。
直到客户端使用有效的Windows帐户成功登录后，Windows才打开信任连接。
信任连接的属性包括打开连接的客户端的WindowsNT和Windows组及用户帐户。
SQLServer2014从信任连接属性中得到用户的帐户信息，并将它们与定义为有效SQLServer2014登录的Windows帐户相匹配。
如果SQLServer2014找到匹配的项，则接受这个连接。
当使用Windows身份验证连接到SQLServer2014时，用户标识即是WindowsNT或Windows组或用户帐户。
MicrosoftWindowsMe和Windows98操作系统不支持服务器端的信任连接API。
SQLServer在WindowsMe或Windows98上运行时不支持Windows身份验证。
用户在连接时必须提供SQLServer登录帐户。
当SQLServer在WindowsNT或Windows上运行时，WindowsMe、Windows98和Windows95客户端可以使用Windows身份验证与其连接。
sysadmin固定服务器角色成员首先向SQLServer2014指定所有有效的SQLServer2014登录帐户和密码。
这些登录帐户和密码与用户的MicrosoftWindows帐户或网络帐户无关。
当连接到SQLServer2014时，用户必须提供SQLServer2014登录帐户和密码。
系统将通过用户的SQLServer2014登录帐户在SQLServer2014中标识用户。
由此可以看出，SQLServer2014安全控制是由登录>>用户>>权限来得到控制的。
因此要连接SQLServer2014，首先要经过身份验证，要通过身份验证，就必须拥有登录ID。
新建用户：
ceshi
图20建立新用户
分配选择goods表权限给用户ceshi：
grantselectongoodstoceshi；
图21分配select权限
分配插入删除权限给ceshi：
grantinsertanddeleteonplazatoceshi；
图22分配insertdelete权限
使用sqlserver登陆验证方式登陆ceshi，展开数据库，只有goods表和plaza表，
图23ceshi用户所有表
收回ceshi用户所有的权限：
Revokeallongoodsfromceshi
Revokeallonplazafromceshi
图24回收所有的权限
3）了解SQLSERVER的存储过程、触发器、函数实现过程
通过查看SQLSERVER的联机帮助文档和示例，体会存储过程、函数和触发器的原理，尝试编制具备基本功能的存储过程、触发器和函数的实际例子，查看其执行效果。
创建一个存储过程，其中包含插入和删除操作，经验证存储结果，过程生效。
CreateProcedureMyProcedure
AS
Begin
SetNOCOUNTON;
SetXACT_ABORTON;
BeginTran
Deletefromgoodswheregname='anta';
Insertintoplazavalues（'校园超市','武广'）;
CommitTran
End
创建一个级联删除触发器，使得在sale中删除一个货物时删除goods表中同种货物。
createtriggerChang_Update_saleon[dbo].[sale]
fordelete
as
deletegoods
fromgoods,sale
wheresale.gname=goods.gname;
图25级联删除结果1
图26级联删除结果2
创建一个函数选择goods表所有内容;
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
--=============================================
--Author:
--Createdate:
--Description:
--=============================================
CREATEFUNCTIONtypoints（）
RETURNSTABLE
AS
RETURN
（
SELECT*fromgoods
）;
Select选择该函数，由于是表值函数所以用select*from[dbo].[typoints]（）调用，选择goods表内容，有结果可以看出，该函数执行成功。
与直接执行函数体SELECT*fromgoods
的结果是一样的。
图27函数执行结果
三、实验总结
1）实验问题及解决
1、由于安装的是较新版的数据库（sqlserver2014）使得有些语句与书上不符，比如建触发器语句：
createtrigger[dbo].[Chang_Update_sale]on[dbo].[sale]
afterinsertas
if（selectcount（*）fromsale,insertedwheresale.gname='anta'andsale.price<350）=1
begin
updatesalesetsale.price=350
wheresale.gname='anta'andsale.pname='家乐福'
end
书上有before语句，2014版中不支持before语句但有insteadof语句，同时也不支持or连接两个操作的说明，还是要查找一些资料才能理解语句的内涵。
才能知道虽然变了部分语法，但是还是换汤不换药的方式。
2、建立视图是，总是建立失败，第一次未能插入到新视图里，第二次是想直接建立一个平均后的视图，经过多次尝试失败，还使用了group语句去完善自己的视图，虽然实验简单，但是同样体会到数据库的重要性，同样操作不容马虎，不能有一点一点的失误。
3、所学标准语句与实际所用的数据库产品标准的差异问题。
虽然很多时候核心的语句是一致的，但是有一些地方不一样。
比如grant时，标准语句需要在表明前加上table，但是在使用SQL Server 2014时，发现语句有问题，和同学讨论了很久，改了很多关键字都不起
升级会员 