数据库实验报告.docx

1、数据库实验报告课 程 实 验 报 告课程名称： 数据库系统概论 专业班级： 计科1207班 学 号： U201215002 姓 名： 李文勇 指导教师： 江胜 报告日期： 2015年6月8日 计算机科学与技术学院目 录1 实验一 基本SQL操作 11.1 实验目的 11.2 实验内容 11.3 实验环境 21.4 实验步骤 21.5 实验中遇到的问题及解决 82 实验二 DBMS综合运用 92.1 实验目的 92.2 实验内容及步骤 92.3 实验中遇到的问题 123 实验心得与体会 134 附录 131 实验一 基本SQL操作1.1 实验目的1）熟悉一种DBMS软件（Microsoft SQ

2、L Server、MySQL等）的安装和使用；2）熟悉并掌握SQL语言，会进行基本的SQL操作，包括表的创建、数据的增加、修改以及查询等操作。1.2 实验内容1）数据定义参照下面的内容建立自己实验所需的关系数据创建三个关系：商品表【商品名称、商品类型】GOODS【GNAME char（20），GTYPE char（10）】主关键字为（商品名称），商品类型为（电器、文具、服装）商场表【商场名称,所在地区】PLAZA【PNAME char（20），PAREA char（20）】主关键字为商场名称，所在地区为（洪山、汉口、汉阳、武昌）销售价格表【商品名称、商场名称、当前销售价格、目前举办活动类型】S

3、ALE【GNAME char（20），PNAME char（20），PRICE FLOAT，ATYPE char（10）】主关键字为（商品名称、商场名称），举办活动类型为（送券、打折），也可为空值，表示当前未举办任何活动。表中记录如（哈森皮靴，亚贸广场，200，打折），同一商场针对不同的商品可能采取不同的促销活动。2）数据更新将SALE表中的打折记录插入到新表SALE_CHEAP中，并基于SALE_CHEAP表创建一个统计每个商场各自打折商品平均价格的视图。3）用SQL语句完成下述查询需求（1）查询价格在200500元之间的商品名称、所在的商场名称、价格，结果按照商场名称排序；（2）查询每种商

4、品的最低价格、商品名称；（3）查询以“送券”方式销售的商品总数超过30种的商场名称；（4）查询以“送券”方式销售的商品总数超过30种的商场所在地区；（5）查询价格为下列取值之一的商品名称、所在商场名称、目前举办活动的类型，（88、188、288、388、488、588、888）；（6）查询以“老”字开头的所有商品的名称；（7）查询同时销售“剃须刀”和“电池”的商场名称；（8）查询不举办任何活动的商场。1.3 实验环境Windows8.1 x64系统；Microsoft SQL Server 20121.4 实验步骤1）数据更新（1）创建三个关系表：GOODS、PLAZA、SALE。打开并连接S

5、QL Server 2012，依次新建数据库和新建查询，然后分别输入和执行以下语句即可创建三个关系表：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), PRIMARY KEY (GNAME,PNAME), FOREIGN KEY (GNA

6、ME) REFERENCES GOODS(GNAME), FOREIGN KEY (PNAME) REFERENCES PLAZA(PNAME);然后分别在每个表中导入Excel文件，即完成了数据的添加。接着执行相应的插入、修改、删除语句（按照课本3.5节的格式）（2）将SALE表中的打折记录插入到新表SALE_CHEAP中，并基于SALE_CHEAP表创建一个统计每个商场各自打折商品平均价格的视图。首先执行下列语句，创建一个SALE_CHEAP表：CREATE TABLE SALE_CHEAP( GNAME CHAR(20), PNAME CHAR(20), PRICE FLOAT, ATY

7、PE CHAR(10), PRIMARY KEY (GNAME,PNAME), FOREIGN KEY (GNAME) REFERENCES GOODS(GNAME), FOREIGN KEY (PNAME) REFERENCES PLAZA(PNAME);然后执行下面的语句，将SALE表中的打折记录插入新表SALE_CHEAP：INSERT INTO SALE_CHEAP(GNAME,PNAME,PRICE,ATYPE)SELECT GNAME,PNAME,PRICE,ATYPEFROM SALE WHERE ATYPE=打折;结果见图1.1。图 1.1 SALE_CHEAP表最后执行下列语

8、句，创建统计商场打折商品平均价格的视图：GOCREATE VIEW A_PRICE(PNAME,AVG_PRICE)ASSELECT PNAME,AVG(PRICE)FROM SALE_CHEAPGROUP BY PNAMEGO统计结果见图1.2。图 1.2 打折商品平均价格 2）数据查询（1）查询价格在200500元之间的商品名称、所在的商场名称、价格，结果按照商场名称排序。执行下列语句：SELECT GNAME,PNAME,PRICEFROM SALEWHERE PRICE BETWEEN 200 AND 500ORDER BY PNAME;结果见图1.3。图 1.3 价格在200500元

9、之间的商品（2）查询每种商品的最低价格、商品名称。执行下列语句：SELECT GNAME, MIN(PRICE)FROM SALEGROUP BY GNAME;结果见图1.4。图 1.4 每种商品最低价（3）查询以“送券”方式销售的商品总数超过30种的商场名称。执行下列语句：SELECT PNAME FROM SALE WHERE ATYPE=送券 GROUP BY PNAME HAVING COUNT(*)30;结果如下：（4）查询以“送券”方式销售的商品总数超过30种的商场所在地区。执行下列语句：SELECT PAREA FROM PLAZA WHERE PLAZA.PNAME IN (

10、SELECT SALE.PNAME FROM SALE WHERE ATYPE=送券 GROUP BY SALE.PNAME HAVING COUNT(*)30);结果如下：（5）查询价格为下列取值之一的商品名称、所在商场名称、目前举办活动的类型，（88、188、288、388、488、588、888）。执行下列语句：SELECT GNAME,PNAME,ATYPEFROM SALEWHERE PRICE IN(88,188,288,388,488,588,888);结果见下图： （6）查询以“老”字开头的所有商品的名称；执行下列语句：SELECT* FROM GOODSWHERE GNAME

11、 LIKE 老%;结果如下：（7）查询同时销售“剃须刀”和“电池”的商场名称；执行下列语句：SELECT PNAMEFROM SALEWHERE GNAME=剃须刀 AND PNAME IN(SELECT PNAMEFROM SALEWHERE GNAME=电池);结果如下：（8）查询不举办任何活动的商场。执行下列语句：SELECT DISTINCT PNAMEFROM SALEWHERE ATYPE IS NULL;结果如下：1.5 实验中遇到的问题及解决（1）在做基于SALE_CHEAP表创建一个统计每个商场各自打折商品平均价格的视图时，虽然语句能够执行，但在语句上显示“错误的语法：CRE

12、ATE VIEW必须是批处理中仅有的语句。”后来在网上查找原因，才知道批处理必须以CREATE语句开始。也就是说一个查询分析器里面只有一个批处理语句才是规范的语法。CREATE VIEW 语句不能在批处理中与其他语句组合使用。所有跟在该批处理后的其他语句将被解释为第一个 CREATE 语句定义的一部分。最后解决方法是在该语句前后加上关键字GO进行分批处理。（2）在做查询同时销售“剃须刀”和“电池”的商场名称时，刚开始用的下面这种方法：SELECTS1.PNAMEFROMSALES1,SALES2WHERES1.PNAME=S2.PNAMEANDS1.GNAME=剃须刀ANDS2.GNAME=电

13、池;结果也是有语法错误，后来改成了SELECT PNAMEFROM SALEWHERE GNAME=剃须刀 AND PNAME IN(SELECT PNAMEFROM SALEWHERE GNAME=电池);解决了问题。2 实验二 DBMS综合运用2.1 实验目的在第1次实验的基础上，使用Microsoft SQL Server或其他DBMS进行用户管理、权限配置等操作，进一步熟悉DBMS的运用。2.2 实验内容及步骤1）学习系统的身份、权限配置操作。（1）当SQL Server 2012在Windows 上运行时，sysadmin固定服务器角色成员可以指定下面两种身份验证模式之一：a、Win

14、dows 身份验证模式只进行Windows身份验证。用户不能指定SQL Server 2012登录ID。这是 SQL Server 2012的默认身份验证模式。不能为在 Windows 98上运行的SQL Server实例指定Windows身份验证模式，因为此操作系统不支持Windows身份验证。说明：如果用户试图通过提供空白登录名称连接到SQL Server的实例，SQL Server将使用Windows身份验证。此外，如果用户试图使用特定的登录连接到配置为Windows身份验证模式的SQL Server实例，则将忽略该登录并使用 Windows身份验证。b、混合验证模式如果用户在登录时提供

15、了SQL Server 2012登录ID，则系统将使用SQL Server身份验证对其进行验证。如果没有提供SQL Server 2014登录ID或请求 Windows身份验证，则使用Windows身份验证对其进行身份验证。当用户用指定的登录名称和密码从非信任连接进行连接时，SQL Server通过检查是否已设置SQL Server登录帐户，以及指定的密码是否与以前记录的密码匹配，自己进行身份验证。如果SQL Server未设置登录帐户，则身份验证将失败，而且用户收到错误信息。提供SQL Server身份验证是为了向后兼容性，因为为SQL Server 7.0版或更早的版本编写的应用程序可能要

16、求使用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身份验证。（2）设置SQL Server 2012的身份验证模式：打开Microsoft SQL Server Management S

17、tudio，右键对象资源管理器，选择“属性”，如下图所示。在出来的窗口点击左边的“安全性”，如下图所示：在“服务器身份验证”选项中，第1个即为Windows身份验证模式，第2个即为混合验证模式。（3）权限配置操作SQL Server 2012可以新建一个用户并对其配置权限，当时我新建用户后，使用SQL Server验证方式登录总是登录错误，所以不能验证是否正确实现了权限的配置。2）了解SQLSERVER的存储过程、触发器、函数实现过程。通过查看SQLSERVER的联机帮助文档和示例，体会存储过程、函数和触发器的原理，尝试编制具备基本功能的存储过程、触发器和函数的实际例子，查看其执行效果。（1）

18、创建一个存储过程，其中包含插入和删除操作，经验证存储结果，过程生效。CREAT PROCEDURE MyProcedure AS Begin SET NOCOUNT ON; SET XACT_ABORT ON; BEGIN TRAN DELETE FROM GOODS WHERE GNAME=书包; INSERT INTO PLAZA VALUES(中百仓储,洪山区); COMMIT TRAN END（2）创建一个级联删除触发器，使得在SALE中删除一个货物时删除GOODS表中同种货物。CREATE TRIGGER GOODS_SALE ON SALEAFTER DELETEASDELETE

19、FROM GOODSWHERE GNAME IN(SELECT GNAME FROM DELETED);然后执行：DELETEFROM SALEWHERE GNAME=茶几;结果如下： 可以看到GOODS表和SALE表中的“茶几“已经被删除、（3）创建一个函数查询GOODS表所有内容SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE FUNCTION se_goods()RETURNS TABLE ASRETURN ( SELECT *from goods);GOSELECT 选择该函数，由于是表值函数所以用 select * fromdbo

20、.se_goods()调用，选择goods表内容，有结果可以看出，该函数执行成功。再执行SELECT *FROM dbo.se_goods(); 与直接执行函数体SELECT *from goods的结果是一样的。2.3 实验中遇到的问题 遇到的最大问题就是新建用户后以SQL Server验证方式登录总是失败，这个问题一直到最后也没解决，希望以后能更加深入的学习数据库，解决目前存在的问题。3 实验心得与体会 通过这次实验，学到了许多课堂上没有深入了解的东西。包括在SQL Server 2012下如何建表、导入数据、查询、插入等。对SQL语言也有了更加透彻的了解，巩固了课本上学到的知识，提高了动

21、手能力和思维能力。SQLServer数据库的实验学习使我对数据库的学习有了新的进步，数据库是很重要的一门课程，以后会再接再厉，不只是懂得运用数据库，还要争取去优化所用的数据库。4 附录各题的SQL语句： 1）创建关系表GOODS、PLAZA、SALE：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

22、), PRICE FLOAT, ATYPE CHAR(10), PRIMARY KEY (GNAME,PNAME), FOREIGN KEY (GNAME) REFERENCES GOODS(GNAME), FOREIGN KEY (PNAME) REFERENCES PLAZA(PNAME);2）第一部分第2题数据更新第（3）小题：CREATE TABLE SALE_CHEAP( GNAME CHAR(20), PNAME CHAR(20), PRICE FLOAT, ATYPE CHAR(10), PRIMARY KEY (GNAME,PNAME), FOREIGN KEY (GNAME)

23、 REFERENCES GOODS(GNAME), FOREIGN KEY (PNAME) REFERENCES PLAZA(PNAME);INSERT INTO SALE_CHEAP(GNAME,PNAME,PRICE,ATYPE)SELECT GNAME,PNAME,PRICE,ATYPEFROM SALE WHERE ATYPE=打折;GOCREATE VIEW A_PRICE(PNAME,AVG_PRICE)ASSELECT PNAME,AVG(PRICE)FROM SALE_CHEAPGROUP BY PNAMEGO3）第一部分第3题数据查询第（3）小题：SELECT GNAME,P

24、NAME,PRICEFROM SALEWHERE PRICE BETWEEN 200 AND 500ORDER BY PNAME;4）第一部分第3题数据查询第（4）小题：SELECT GNAME, MIN(PRICE)FROM SALEGROUP BY GNAME;5）第一部分第3题数据查询第（5）小题：SELECT PNAME FROM SALE WHERE ATYPE=送券 GROUP BY PNAME HAVING COUNT(*)30;6）第一部分第3题数据查询第（6）小题：SELECT PAREA FROM PLAZA WHERE PLAZA.PNAME IN ( SELECT SA

25、LE.PNAME FROM SALE WHERE ATYPE=送券 GROUP BY SALE.PNAME HAVING COUNT(*)30);7）第一部分第3题数据查询第（7）小题：SELECT GNAME,PNAME,ATYPEFROM SALEWHERE PRICE IN(88,188,288,388,488,588,888);8）第一部分第3题数据查询第（8）小题：SELECT* FROM GOODSWHERE GNAME LIKE 老%;9）第一部分第3题数据查询第（9）小题：SELECT PNAMEFROM SALE WHERE GNAME=剃须刀 AND PNAME IN(SE

26、LECT PNAMEFROM SALEWHERE GNAME=电池);10）第一部分第3题数据查询第（10）小题：SELECT DISTINCT PNAMEFROM SALE WHERE ATYPE IS NULL;11）第二部分第5小题CREAT PROCEDURE MyProcedureASBeginSET NOCOUNT ON;SET XACT_ABORT ON;BEGIN TRANDELETE FROM GOODS WHERE GNAME=书包;INSERT INTO PLAZA VALUES(中百仓储,洪山区);COMMIT TRANEND; 创建存储过程CREATE TRIGGER GOODS_SALE ON SALEAFTER DELETEASDELETE FROM GOODSWHERE GNAME IN(SELECT GNAME FROM DELETED); 级联删除触发器DELETEFROM SALEWHERE GNAME=茶几;SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE FUNCTION se_goods()RETURNS TABLE ASRETURN ( SELECT *from goods)GO 查询函数