数据库实验报告.docx
《数据库实验报告.docx》由会员分享,可在线阅读,更多相关《数据库实验报告.docx(18页珍藏版)》请在冰豆网上搜索。
![数据库实验报告.docx](https://file1.bdocx.com/fileroot1/2022-12/17/d6c29b40-dc8d-4242-bb8d-e26863138490/d6c29b40-dc8d-4242-bb8d-e268631384901.gif)
数据库实验报告
课程实验报告
课程名称:
数据库系统概论
专业班级:
计科1207班
学号:
U201215002
姓名:
李文勇
指导教师:
江胜
报告日期:
2015年6月8日
计算机科学与技术学院
目录
1实验一基本SQL操作1
1.1实验目的1
1.2实验内容1
1.3实验环境2
1.4实验步骤2
1.5实验中遇到的问题及解决8
2实验二DBMS综合运用9
2.1实验目的9
2.2实验内容及步骤9
2.3实验中遇到的问题12
3实验心得与体会13
4附录13
1实验一基本SQL操作
1.1实验目的
1)熟悉一种DBMS软件(MicrosoftSQLServer、MySQL等)的安装和使用;
2)熟悉并掌握SQL语言,会进行基本的SQL操作,包括表的创建、数据的增加、修改以及查询等操作。
1.2实验内容
1)数据定义
参照下面的内容建立自己实验所需的关系数据创建三个关系:
商品表【商品名称、商品类型】
GOODS【GNAMEchar(20),GTYPEchar(10)】
主关键字为(商品名称),商品类型为(电器、文具、服装……)
商场表【商场名称,所在地区】
PLAZA【PNAMEchar(20),PAREAchar(20)】
主关键字为商场名称,所在地区为(洪山、汉口、汉阳、武昌……)
销售价格表【商品名称、商场名称、当前销售价格、目前举办活动类型】
SALE【GNAMEchar(20),PNAMEchar(20),PRICEFLOAT,ATYPEchar(10)】
主关键字为(商品名称、商场名称),举办活动类型为(送券、打折),也可为空值,表示当前未举办任何活动。
表中记录如(‘哈森皮靴’,‘亚贸广场’,200,‘打折’),同一商场针对不同的商品可能采取不同的促销活动。
2)数据更新
将SALE表中的打折记录插入到新表SALE_CHEAP中,并基于SALE_CHEAP表创建一个统计每个商场各自打折商品平均价格的视图。
3)用SQL语句完成下述查询需求
(1)查询价格在200~500元之间的商品名称、所在的商场名称、价格,结果按照商场名称排序;
(2)查询每种商品的最低价格、商品名称;
(3)查询以“送券”方式销售的商品总数超过30种的商场名称;
(4)查询以“送券”方式销售的商品总数超过30种的商场所在地区;
(5)查询价格为下列取值之一的商品名称、所在商场名称、目前举办活动的类型,(88、188、288、388、488、588、888);
(6)查询以“老”字开头的所有商品的名称;
(7)查询同时销售“剃须刀”和“电池”的商场名称;
(8)查询不举办任何活动的商场。
1.3实验环境
Windows8.1x64系统;
MicrosoftSQLServer2012
1.4实验步骤
1)数据更新
(1)创建三个关系表:
GOODS、PLAZA、SALE。
打开并连接SQLServer2012,依次新建数据库和新建查询,然后分别输入和执行以下语句即可创建三个关系表:
CREATETABLEGOODS(
GNAMECHAR(20)PRIMARYKEY,
GTYPECHAR(10)
);
CREATETABLEPLAZA(
PNAMECHAR(20)PRIMARYKEY,
PAREACHAR(20)
);
CREATETABLESALE(
GNAMECHAR(20),
PNAMECHAR(20),
PRICEFLOAT,
ATYPECHAR(10),
PRIMARYKEY(GNAME,PNAME),
FOREIGNKEY(GNAME)REFERENCESGOODS(GNAME),
FOREIGNKEY(PNAME)REFERENCESPLAZA(PNAME)
);
然后分别在每个表中导入Excel文件,即完成了数据的添加。
接着执行相应的插入、修改、删除语句(按照课本3.5节的格式)
(2)将SALE表中的打折记录插入到新表SALE_CHEAP中,并基于SALE_CHEAP表创建一个统计每个商场各自打折商品平均价格的视图。
首先执行下列语句,创建一个SALE_CHEAP表:
CREATETABLESALE_CHEAP(
GNAMECHAR(20),
PNAMECHAR(20),
PRICEFLOAT,
ATYPECHAR(10),
PRIMARYKEY(GNAME,PNAME),
FOREIGNKEY(GNAME)REFERENCESGOODS(GNAME),
FOREIGNKEY(PNAME)REFERENCESPLAZA(PNAME)
);
然后执行下面的语句,将SALE表中的打折记录插入新表SALE_CHEAP:
INSERTINTOSALE_CHEAP(GNAME,PNAME,PRICE,ATYPE)
SELECTGNAME,PNAME,PRICE,ATYPE
FROMSALEWHEREATYPE='打折';
结果见图1.1。
图1.1SALE_CHEAP表
最后执行下列语句,创建统计商场打折商品平均价格的视图:
GO
CREATEVIEWA_PRICE(PNAME,AVG_PRICE)
AS
SELECTPNAME,AVG(PRICE)
FROMSALE_CHEAP
GROUPBYPNAME
GO
统计结果见图1.2。
图1.2打折商品平均价格
2)数据查询
(1)查询价格在200~500元之间的商品名称、所在的商场名称、价格,结果按照商场名称排序。
执行下列语句:
SELECTGNAME,PNAME,PRICE
FROMSALE
WHEREPRICEBETWEEN200AND500
ORDERBYPNAME;
结果见图1.3。
图1.3价格在200~500元之间的商品
(2)查询每种商品的最低价格、商品名称。
执行下列语句:
SELECTGNAME,MIN(PRICE)
FROMSALE
GROUPBYGNAME;
结果见图1.4。
图1.4每种商品最低价
(3)查询以“送券”方式销售的商品总数超过30种的商场名称。
执行下列语句:
SELECTPNAME
FROMSALE
WHEREATYPE='送券'
GROUPBYPNAMEHAVINGCOUNT(*)>30;
结果如下:
(4)查询以“送券”方式销售的商品总数超过30种的商场所在地区。
执行下列语句:
SELECTPAREA
FROMPLAZA
WHEREPLAZA.PNAMEIN(
SELECTSALE.PNAME
FROMSALE
WHEREATYPE='送券'
GROUPBYSALE.PNAMEHAVINGCOUNT(*)>30);
结果如下:
(5)查询价格为下列取值之一的商品名称、所在商场名称、目前举办活动的类型,(88、188、288、388、488、588、888)。
执行下列语句:
SELECTGNAME,PNAME,ATYPE
FROMSALE
WHEREPRICEIN(88,188,288,388,488,588,888);
结果见下图:
(6)查询以“老”字开头的所有商品的名称;
执行下列语句:
SELECT*
FROMGOODS
WHEREGNAMELIKE'老%';
结果如下:
(7)查询同时销售“剃须刀”和“电池”的商场名称;
执行下列语句:
SELECTPNAME
FROMSALE
WHEREGNAME='剃须刀'ANDPNAMEIN
(SELECTPNAME
FROMSALE
WHEREGNAME='电池');
结果如下:
(8)查询不举办任何活动的商场。
执行下列语句:
SELECTDISTINCTPNAME
FROMSALE
WHEREATYPEISNULL;
结果如下:
1.5实验中遇到的问题及解决
(1)在做基于SALE_CHEAP表创建一个统计每个商场各自打折商品平均价格的视图时,虽然语句能够执行,但在语句上显示“错误的语法:
‘CREATEVIEW’必须是批处理中仅有的语句。
”
后来在网上查找原因,才知道批处理必须以CREATE语句开始。
也就是说一个查询分析器里面只有一个批处理语句才是规范的语法。
CREATEVIEW语句不能在批处理中与其他语句组合使用。
所有跟在该批处理后的其他语句将被解释为第一个CREATE语句定义的一部分。
最后解决方法是在该语句前后加上关键字GO进行分批处理。
(2)在做查询同时销售“剃须刀”和“电池”的商场名称时,刚开始用的下面这种方法:
SELECT S1.PNAME
FROM SALE S1,SALE S2
WHERE S1.PNAME=S2.PNAME AND S1.GNAME='剃须刀' AND S2.GNAME='电池';
结果也是有语法错误,后来改成了
SELECTPNAME
FROMSALE
WHEREGNAME='剃须刀'ANDPNAMEIN
(SELECTPNAME
FROMSALE
WHEREGNAME='电池');
解决了问题。
2实验二DBMS综合运用
2.1实验目的
在第1次实验的基础上,使用MicrosoftSQLServer或其他DBMS进行用户管理、权限配置等操作,进一步熟悉DBMS的运用。
2.2实验内容及步骤
1)学习系统的身份、权限配置操作。
(1)当SQLServer2012在Windows上运行时,sysadmin固定服务器角色成员可以指定下面两种身份验证模式之一:
a、Windows身份验证模式
只进行Windows身份验证。
用户不能指定SQLServer2012登录ID。
这是SQLServer2012的默认身份验证模式。
不能为在Windows98上运行的SQLServer实例指定Windows身份验证模式,因为此操作系统不支持Windows身份验证。
说明:
如果用户试图通过提供空白登录名称连接到SQLServer的实例,SQLServer将使用Windows身份验证。
此外,如果用户试图使用特定的登录连接到配置为Windows身份验证模式的SQLServer实例,则将忽略该登录并使用Windows身份验证。
b、混合验证模式
如果用户在登录时提供了SQLServer2012登录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身份验证。
(2)设置SQLServer2012的身份验证模式:
打开MicrosoftSQLServerManagementStudio,右键对象资源管理器,选择“属性”,如下图所示。
在出来的窗口点击左边的“安全性”,如下图所示:
在“服务器身份验证”选项中,第1个即为Windows身份验证模式,第2个即为混合验证模式。
(3)权限配置操作
SQLServer2012可以新建一个用户并对其配置权限,当时我新建用户后,使用SQLServer验证方式登录总是登录错误,所以不能验证是否正确实现了权限的配置。
2)了解SQLSERVER的存储过程、触发器、函数实现过程。
通过查看SQLSERVER的联机帮助文档和示例,体会存储过程、函数和触发器的原理,尝试编制具备基本功能的存储过程、触发器和函数的实际例子,查看其执行效果。
(1)创建一个存储过程,其中包含插入和删除操作,经验证存储结果,过程生效。
CREATPROCEDUREMyProcedure
AS
Begin
SETNOCOUNTON;
SETXACT_ABORTON;
BEGINTRAN
DELETEFROMGOODSWHEREGNAME='书包';
INSERTINTOPLAZAVALUES('中百仓储','洪山区');
COMMITTRAN
END
(2)创建一个级联删除触发器,使得在SALE中删除一个货物时删除GOODS表中同种货物。
CREATETRIGGERGOODS_SALEONSALE
AFTERDELETE
AS
DELETEFROMGOODS
WHEREGNAMEIN
(SELECTGNAMEFROMDELETED);
然后执行:
DELETE
FROMSALE
WHEREGNAME=’茶几’;
结果如下:
可以看到GOODS表和SALE表中的“茶几“已经被删除、
(3)创建一个函数查询GOODS表所有内容
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
CREATEFUNCTIONse_goods()
RETURNSTABLE
AS
RETURN
(
SELECT*fromgoods
);
GO
SELECT选择该函数,由于是表值函数所以用select*from[dbo].[se_goods]()调用,选择goods表内容,有结果可以看出,该函数执行成功。
再执行SELECT*FROM[dbo].[se_goods]();与直接执行函数体
SELECT*fromgoods
的结果是一样的。
2.3实验中遇到的问题
遇到的最大问题就是新建用户后以SQLServer验证方式登录总是失败,这个问题一直到最后也没解决,希望以后能更加深入的学习数据库,解决目前存在的问题。
3实验心得与体会
通过这次实验,学到了许多课堂上没有深入了解的东西。
包括在SQLServer2012下如何建表、导入数据、查询、插入等。
对SQL语言也有了更加透彻的了解,巩固了课本上学到的知识,提高了动手能力和思维能力。
SQL Server数据库的实验学习使我对数据库的学习有了新的进步,数据库是很重要的一门课程,以后会再接再厉,不只是懂得运用数据库,还要争取去优化所用的数据库。
4附录
各题的SQL语句:
1)创建关系表GOODS、PLAZA、SALE:
CREATETABLEGOODS(
GNAMECHAR(20)PRIMARYKEY,
GTYPECHAR(10)
);
CREATETABLEPLAZA(
PNAMECHAR(20)PRIMARYKEY,
PAREACHAR(20)
);
CREATETABLESALE(
GNAMECHAR(20),
PNAMECHAR(20),
PRICEFLOAT,
ATYPECHAR(10),
PRIMARYKEY(GNAME,PNAME),
FOREIGNKEY(GNAME)REFERENCESGOODS(GNAME),
FOREIGNKEY(PNAME)REFERENCESPLAZA(PNAME)
);
2)第一部分第2题数据更新第(3)小题:
CREATETABLESALE_CHEAP(
GNAMECHAR(20),
PNAMECHAR(20),
PRICEFLOAT,
ATYPECHAR(10),
PRIMARYKEY(GNAME,PNAME),
FOREIGNKEY(GNAME)REFERENCESGOODS(GNAME),
FOREIGNKEY(PNAME)REFERENCESPLAZA(PNAME)
);
INSERTINTOSALE_CHEAP(GNAME,PNAME,PRICE,ATYPE)
SELECTGNAME,PNAME,PRICE,ATYPE
FROMSALEWHEREATYPE='打折';
GO
CREATEVIEWA_PRICE(PNAME,AVG_PRICE)
AS
SELECTPNAME,AVG(PRICE)
FROMSALE_CHEAP
GROUPBYPNAME
GO
3)第一部分第3题数据查询第(3)小题:
SELECTGNAME,PNAME,PRICE
FROMSALE
WHEREPRICEBETWEEN200AND500
ORDERBYPNAME;
4)第一部分第3题数据查询第(4)小题:
SELECTGNAME,MIN(PRICE)
FROMSALE
GROUPBYGNAME;
5)第一部分第3题数据查询第(5)小题:
SELECTPNAME
FROMSALE
WHEREATYPE='送券'
GROUPBYPNAMEHAVINGCOUNT(*)>30;
6)第一部分第3题数据查询第(6)小题:
SELECTPAREA
FROMPLAZA
WHEREPLAZA.PNAMEIN(
SELECTSALE.PNAME
FROMSALE
WHEREATYPE='送券'
GROUPBYSALE.PNAMEHAVINGCOUNT(*)>30);
7)第一部分第3题数据查询第(7)小题:
SELECTGNAME,PNAME,ATYPE
FROMSALE
WHEREPRICEIN(88,188,288,388,488,588,888);
8)第一部分第3题数据查询第(8)小题:
SELECT*
FROMGOODS
WHEREGNAMELIKE'老%';
9)第一部分第3题数据查询第(9)小题:
SELECTPNAME
FROMSALE
WHEREGNAME='剃须刀'ANDPNAMEIN
(SELECTPNAME
FROMSALE
WHEREGNAME='电池');
10)第一部分第3题数据查询第(10)小题:
SELECTDISTINCTPNAME
FROMSALE
WHEREATYPEISNULL;
11)第二部分第5小题
CREATPROCEDUREMyProcedure
AS
Begin
SETNOCOUNTON;
SETXACT_ABORTON;
BEGINTRAN
DELETEFROMGOODSWHEREGNAME='书包';
INSERTINTOPLAZAVALUES('中百仓储','洪山区');
COMMITTRAN
END;创建存储过程
CREATETRIGGERGOODS_SALEONSALE
AFTERDELETE
AS
DELETEFROMGOODS
WHEREGNAMEIN
(SELECTGNAMEFROMDELETED);级联删除触发器
DELETE
FROMSALE
WHEREGNAME=’茶几’;
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
CREATEFUNCTIONse_goods()
RETURNSTABLE
AS
RETURN
(
SELECT*fromgoods
)
GO查询函数