数据库实验报告

课 程 实 验 报 告

课程名称：

数据库系统概论

专业班级：

计科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软件（Microsoft SQL Server、MySQL等）的安装和使用； 2）熟悉并掌握SQL语言，会进行基本的SQL操作，包括表的创建、数据的增加、修改以及查询等操作。

1.2 实验内容

1）数据定义

参照下面的内容建立自己实验所需的关系数据创建三个关系： 商品表【商品名称、商品类型】

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

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

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

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

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

销售价格表【商品名称、商场名称、当前销售价格、目前举办活动类型】 SALE【GNAME char（20），PNAME char（20），PRICE FLOAT，ATYPE char（10）】

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

2）数据更新

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

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

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

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

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

（4）查询以“送券”方式销售的商品总数超过30种的商场所在地区； （5）查询价格为下列取值之一的商品名称、所在商场名称、目前举办活动的类型，（8

8、18

8、28

8、38

8、48

8、58

8、888）；

（6）查询以“老”字开头的所有商品的名称； （7）查询同时销售“剃须刀”和“电池”的商场名称； （8）查询不举办任何活动的商场。

1.3 实验环境

Windows8.1 x64系统； Microsoft SQL Server 2012 1.4 实验步骤

1）数据更新

（1）创建三个关系表：GOODS、PLAZA、SALE。

打开并连接SQL 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 (GNAME) 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,

ATYPE 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,ATYPE FROM SALE WHERE ATYPE=\'打折\';

结果见图1.1。

图 1.1 SALE_CHEAP表

最后执行下列语句，创建统计商场打折商品平均价格的视图：

GO CREATE VIEW A_PRICE(PNAME,AVG_PRICE) AS SELECT PNAME,AVG(PRICE) FROM SALE_CHEAP GROUP BY PNAME GO

统计结果见图1.2。

图 1.2 打折商品平均价格

2）数据查询

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

执行下列语句：

SELECT GNAME,PNAME,PRICE FROM SALE WHERE PRICE BETWEEN 200 AND 500 ORDER BY PNAME;

结果见图1.3。

图 1.3 价格在200~500元之间的商品

（2）查询每种商品的最低价格、商品名称。 执行下列语句：

SELECT GNAME, MIN(PRICE) FROM SALE GROUP 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 (

SELECT SALE.PNAME FROM SALE

WHERE ATYPE=\'送券\'

GROUP BY SALE.PNAME HAVING COUNT(*)>30);

结果如下：

（5）查询价格为下列取值之一的商品名称、所在商场名称、目前举办活动的类型，（8

8、18

8、28

8、38

8、48

8、58

8、888）。

执行下列语句：

SELECT GNAME,PNAME,ATYPE FROM SALE WHERE PRICE IN(88,188,288,388,488,588,888);

结果见下图：

（6）查询以“老”字开头的所有商品的名称； 执行下列语句：

SELECT* FROM GOODS WHERE GNAME LIKE \'老%\';

结果如下：

（7）查询同时销售“剃须刀”和“电池”的商场名称； 执行下列语句：

SELECT PNAME FROM SALE WHERE GNAME=\'剃须刀\' AND PNAME IN (SELECT PNAME FROM SALE WHERE GNAME=\'电池\');

结果如下：

（8）查询不举办任何活动的商场。 执行下列语句：

SELECT DISTINCT PNAME FROM SALE WHERE ATYPE IS NULL;

结果如下：

1.5 实验中遇到的问题及解决

（1）在做基于SALE_CHEAP表创建一个统计每个商场各自打折商品平均价格的视图时，虽然语句能够执行，但在语句上显示“错误的语法：‘CREATE VIEW’必须是批处理中仅有的语句。”

后来在网上查找原因，才知道批处理必须以CREATE语句开始。也就是说一个查询分析器里面只有一个批处理语句才是规范的语法。CREATE VIEW 语句不能在批处理中与其他语句组合使用。所有跟在该批处理后的其他语句将被解释为第一个 CREATE 语句定义的一部分。

最后解决方法是在该语句前后加上关键字GO进行分批处理。

（2）在做查询同时销售“剃须刀”和“电池”的商场名称时，刚开始用的下面这种方法：

SELECT S1.PNAME FROM SALE S1,SALE S2 WHERE S1.PNAME=S2.PNAME AND S1.GNAME=\'剃须刀\' AND S2.GNAME=\'电池\'; 结果也是有语法错误，后来改成了

SELECT PNAME FROM SALE WHERE GNAME=\'剃须刀\' AND PNAME IN (SELECT PNAME FROM SALE WHERE GNAME=\'电池\'); 解决了问题。

2 实验二 DBMS综合运用

2.1 实验目的

在第1次实验的基础上，使用Microsoft SQL Server或其他DBMS进行用户管理、权限配置等操作，进一步熟悉DBMS的运用。

2.2 实验内容及步骤

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

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

a、Windows 身份验证模式

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

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

b、混合验证模式

如果用户在登录时提供了SQL Server 2012登录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身份验证。

（2）设置SQL Server 2012的身份验证模式：

打开Microsoft SQL Server Management Studio，右键对象资源管理器，选择“属性”，如下图所示。

在出来的窗口点击左边的“安全性”，如下图所示：

在“服务器身份验证”选项中，第1个即为Windows身份验证模式，第2个即为混合验证模式。

（3）权限配置操作

SQL Server 2012可以新建一个用户并对其配置权限，当时我新建用户后，使用SQL Server验证方式登录总是登录错误，所以不能验证是否正确实现了权限的配置。

2）了解SQLSERVER的存储过程、触发器、函数实现过程。

通过查看SQLSERVER的联机帮助文档和示例，体会存储过程、函数和触发器的原理，尝试编制具备基本功能的存储过程、触发器和函数的实际例子，查看其执行效果。

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

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 SALE AFTER DELETE AS DELETE FROM GOODS WHERE GNAME IN (SELECT GNAME FROM DELETED);

然后执行：

DELETE FROM SALE WHERE GNAME=‟茶几‟;

结果如下：

可以看到GOODS表和SALE表中的“茶几“已经被删除、

（3）创建一个函数查询GOODS表所有内容 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION se_goods() RETURNS TABLE AS RETURN (

SELECT *from goods ); GO

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

再执行SELECT *FROM [dbo].[se_goods](); 与直接执行函数体 SELECT *from goods 的结果是一样的。

2.3 实验中遇到的问题

遇到的最大问题就是新建用户后以SQL Server验证方式登录总是失败，这个问题一直到最后也没解决，希望以后能更加深入的学习数据库，解决目前存在的问题。

3 实验心得与体会

通过这次实验，学到了许多课堂上没有深入了解的东西。包括在SQL Server 2012下如何建表、导入数据、查询、插入等。对SQL语言也有了更加透彻的了解，巩固了课本上学到的知识，提高了动手能力和思维能力。

SQL Server数据库的实验学习使我对数据库的学习有了新的进步，数据库是很重要的一门课程，以后会再接再厉，不只是懂得运用数据库，还要争取去优化所用的数据库。

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),

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) REFERENCES GOODS(GNAME),

FOREIGN KEY (PNAME) REFERENCES PLAZA(PNAME) );

INSERT INTO SALE_CHEAP(GNAME,PNAME,PRICE,ATYPE) SELECT GNAME,PNAME,PRICE,ATYPE FROM SALE WHERE ATYPE=\'打折\'; GO CREATE VIEW A_PRICE(PNAME,AVG_PRICE) AS SELECT PNAME,AVG(PRICE) FROM SALE_CHEAP GROUP BY PNAME GO

3）第一部分第3题数据查询第（3）小题：

SELECT GNAME,PNAME,PRICE FROM SALE WHERE PRICE BETWEEN 200 AND 500 ORDER BY PNAME;

4）第一部分第3题数据查询第（4）小题：

SELECT GNAME, MIN(PRICE) FROM SALE GROUP 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 SALE.PNAME FROM SALE

WHERE ATYPE=\'送券\'

GROUP BY SALE.PNAME HAVING COUNT(*)>30);

7）第一部分第3题数据查询第（7）小题：

SELECT GNAME,PNAME,ATYPE FROM SALE WHERE PRICE IN(88,188,288,388,488,588,888);

8）第一部分第3题数据查询第（8）小题：

SELECT* FROM GOODS WHERE GNAME LIKE \'老%\';

9）第一部分第3题数据查询第（9）小题：

SELECT PNAME FROM SALE

WHERE GNAME=\'剃须刀\' AND PNAME IN (SELECT PNAME FROM SALE WHERE GNAME=\'电池\');

10）第一部分第3题数据查询第（10）小题：

SELECT DISTINCT PNAME FROM SALE

WHERE ATYPE IS NULL;

11）第二部分第5小题

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; 创建存储过程

CREATE TRIGGER GOODS_SALE ON SALE AFTER DELETE AS DELETE FROM GOODS WHERE GNAME IN (SELECT GNAME FROM DELETED); 级联删除触发器

DELETE

15 FROM SALE WHERE GNAME=’茶几’;

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION se_goods() RETURNS TABLE AS RETURN (

SELECT *from goods ) GO 查询函数

数据库实验报告

数据库实验报告

数据库实验报告

数据库实验报告

数据库实验报告

数据库实验报告

数据库实验报告

数据库实验报告

数据库实验报告

数据库实验报告

《数据库实验报告.doc》

将本文的Word文档下载到电脑，方便收藏和打印

推荐度：

点击下载文档