SQL数据库实验报告.docx

SQL数据库实验报告.docx

《SQL数据库实验报告.docx》由会员分享，可在线阅读，更多相关《SQL数据库实验报告.docx（27页珍藏版）》请在冰豆网上搜索。

SQL数据库实验报告

西安邮电大学

数据库课内实验

报告书

学院名称

：

计算机学院

学生姓名

：

高丹

专业名称

：

计算机科学与技术

班级

：

1106班

学号

：

（22）

时间

：

2013.4—2013.5

实验2定义表和数据库完整性

一、实验目的

1．了解SQLServer2000的基本数据类型、空值的概念，以及表的结构特点。

2.学会使用T_SQL语句和企业管理器创建表结构和修改表结构。

3.学会使用SQLServer2000提供的数据完整性功能，并在创建表时定义表的数据完整性。

通过实验进一步理解数据完整性的概念及分类。

二、实验内容

用T_SQL语句在Market数据库中创建客户基本信息表Cuetomers、货品信息表Goods、订单信息表Orders，它们的定义如下

/*基本表的定义与创建*/

CREATETABLECustomers

（

CustomerIDintIDENTITY（1,1）PRIMARYKEY,

CNamevarchar（8）NOTNULL,

Addressvarchar（50）,

Cityvarchar（10）,

Telvarchar（20）UNIQUE,

Companyvarchar（50）,

Birthdaydatetime,

TypetinyintDEFAULT1

）;

CREATETABLEGoods

（

GoodsIDintCONSTRAINTC1PRIMARYKEY,

GoodsNamevarchar（20）NOTNULL,

Pricemoney,

Decriptionvarchar（200）,

Storageint,

Providervarchar（50）,

StatustinyintDEFAULT（0）

）;

CREATETABLEOrders

（

OrderIDintIDENTITY（1,1）CONSTRAINTC2PRIMARYKEY,

GoodsIDintNOTNULLREFERENCESGoods（GoodsID）ONDELETECASCADE,

/*表级完整性约束条件，参照约束为Goods（GoodID），删除时进行级联删除*/

CustomerIDintNOTNULLFOREIGNKEY（CustomerID）

REFERENCESCustomers（CustomerID）ONDELETENOACTION,

/*表级完整性约束条件，参照约束为Customers（CustomerID），删除时不删除参照表中的被引用行*/

QuantityintNOTNULLCONSTRAINTC3CHECK（Quantity>0）,

OrdersummoneyNOTNULL,

OrderDatadatetimeDEFAULT（getdate（））

）;

实验3表数据的插入、修改和删除

一、实验目的

掌握使用T_SQL语句和企业管理器对数据进行插入、修改和删除数据的操作，并体会数据完整性约束的作用，加深对数据完整性及其约束的理解。

通过本实验，要熟练掌握INSERT、UPDATE、DELETE语句的语法和使用方法。

二、实验内容

（1）向数据库Market的各张表中输入一些记录。

（2）使用T_SQL语句进行插入、修改和删除记录的操作。

/*在Customers表中已有数据的基础上向各个表中插入数据*/

INSERTINTOCustomers（CName,Address,City,Tel,Company,Birthday）

VALUES（'王伟','长安区','西安','029005','邮电大学',''）

/*向Goods表中插入数据*/

INSERTINTOGoods（GoodsID,GoodsName,Price,Decription,Storage,Provider,Status）

VALUES（'0001','书包',45.00,'双肩包','20','舒适','1'）

/*向Goods表中插入数据*/

INSERTINTOGoods（GoodsID,GoodsName,Price,Decription,Storage,Provider,Status）

VALUES（'0001','书包',45.00,'双肩包','20','舒适','1'）

/*修改表中的数据*/

UPDATEGoods/*将Goods表中的所有价钱都增加*/

SETPrice=Price+10;

UPDATEOrders

SETQuantity=5

WHERECustomerID=2;/*将Orders表中CustomerID为的客户订单数量修改为*/

UPDATECustomers

SETTel=,Address='长安区'

WHERECustomerID=3;

修改后的表：

/*删除表中的一些数据*/

DELETEFROMOrders

WHERECustomerID=2;/*删除了CustomerID为的订单记录*/

删除记录之后的表：

实验4数据的简单查询和连接查询

一、实验目的

数据查询时数据库中最基本的操作，也是使用最频繁的操作，因此必须掌握SQL的查询语句（SELECT语句）的使用方法。

本实验要求掌握简单表的数据查询、数据排序和多表操作时数据连接查询的操作方法。

二、实验内容

（1）简单查询操作。

该实验包括投影、选择条件表达、数据排序等。

在数据库Market的客户表Customers、货品表Goods、订单表Orders中完成第3章习题6中

（1）-（4）的查询。

（2）连接查询操作。

该实验包括等值连接、自然连接、一般连接（内连接）、外连接、左外连接、右外连接和自然连接等。

在数据库Market的客户表Customers、货品表Goods、订单表Orders中完成第3章习题6中（5）的查询。

（一）简单查询操作

1,查找所有西安客户的信息

/*查询*/

SELECT*

FROMCustomers

WHERECity='西安';/*查找所有西安客户的信息*/

2.查找所有商品的名称，库存，价格以及表示打折价，按升序排列

SELECTGoodsName,Storage,Price,0.25*PriceDiscount

FromGoods

ORDERBYPriceASC;/*查找所有商品的名称，库存，价格以及表示打折价，按升序排列*/

3.查找商品名中包含“衣”的商品编号，名称及价格

SELECTGoodsID,GoodsName,Price

FromGoods

WHEREGoodsNameLIKE'%衣%';/*查找商品名中包含“衣”的商品编号，名称及价格*/

4.查询库存量大于小于的商品名称，库存和单价

SELECTGoodsName,Storage,Price

FromGoods

WHEREStorage>100ANDStorage<500;/*查询库存量大于小于的商品名称，库存和单价*/

（二）连接查询操作

查找2011年1月1日至2012年1月1日期间,订货金额大于2的所有订单的客户姓名,商品名称,单价,订货数量和订货金额

/*连接查询*/

SELECTGoodsname,Cname,Price,Quantity,Ordersum

FROMCustomers,Goods,Orders

WHERECustomers.CustomerID=Orders.CustomerIDand

Goods.GoodsID=Orders.OrderIDand

Orderdatabetween''and''andQuantity>2

实验5数据库的复杂查询

一、实验目的

进一步掌握SELECT语句的使用方法，通过实验能够熟练地使用SELECT语句。

本实验要求掌握嵌套查询和统计查询的操作方法。

二、实验内容

在数据库Market的客户表Customers、货品表Goods、订单表Orders中完成第3章习题6中（6）-（9）的查询。

三、运行结果

（1）查找订单编号，商品编号，客户编号，按日期对订单分组，显示订货数量不超的订单信息

SELECTOrderID,GoodsID,CustomerID

FROMOrders

WHEREQuantity<=30

（SELECTOrderdata

FROMOrders

groupbyOrderdata）;

（7）查找所有西安客户的订单信息，要求用不相关子查询完成

SELECT*

FROMOrders

WHERECustomerIDin

（SELECTCustomerID

FROMCustomers

WHERECity='西安'）;

（8）查找不是上海客户的订单信息

SELECT*

FROMOrders

WHERECustomerIDin

（SELECTCustomerID

FROMCustomers

WHERECity<>'上海'）;

（9）查找所有西安客户的相关信息以及他们的订单情况

SELECT*

FROMCustomers,Orders

WHERECustomers.CustomerID=Orders.CustomerIDandCity='西安';

实验6索引和视图

一、实验目的

（1）理解索引和视图的概念。

（2）掌握索引的使用方法。

（3）掌握视图的定义和使用方法。

二、实验内容

在Market数据库中，完成第3章习题11中

（1）的建立视图的操作，然后在该视图上完成第3章习题11中

（2）的查询操作。

三、运行结果

CREATEVIEWXIAN_ORDER（OrderID,GoodsName,Quantity,CustomerID,Cname）

AS

SELECTOrderID,GoodsName,Quantity,Orders.CustomerID,Cname

FROMOrders,Goods,Customers

WHERECustomers.CustomerID=Orders.CustomerIDANDGoods.GoodsID=Orders.OrderIDANDCity='西安'/*创建西安客户订单的视图*/

SELECT*

FROMXIAN_ORDER/*查找西安客户的订单信息*/

实验7存储过程

一、实验目的

理解存储过程的概念，掌握存储过程的使用方法。

二、实验内容

（一）在Market数据库中，完成第4章习题5中

（1）-（4）创建存储过程的操作，并使用EXCEL语句调用这些存储过程执行，观察他们的执行过程。

（二）在Market数据库中，进一步完成以下操作。

1．创建一个存储过程Goods_Orders1,查看任何指定货物的订单情况，包括订单号、订货客户的姓名以及订货数量等（要使用输入参数）。

2.执行存储过程Goods_Orders1时，如果没有给出参数，则系统会报错，如果希望不给出参数是能查出所有货品的订单，则可以用默认参数值来实现。

为此要求创建另一个存储过程Goods_Orders2来完成此功能。

3.创建一个存储过程Goods_OrderSum，来获得某个货品的订单总额（要使用输入以及输出参数）。

三、运行结果

（一）

（1）第4章习题5中

（1）-（4）创建存储过程的操作结果如下

/*为上海客户建立存储过程*/

CREATEproceduresp_shanghai

AS

SELECT*

FROMcustomers

WHEREcity='上海';

CREATEproceduresp_Goods

@GoodsIDchar（20）

ASSELECTGoods.GoodsID,GoodsName,Price,Provider,Storage,Status

FROMGoods

/*创建存储过程sp_Goods，查看制定商品信息，商品编号作为输入参数*/

/*以客户编号作为输入参数，订单总金额作为输出函数，建立存储过程*/

CREATEproceduresp_goodsum

@Cnumberchar（10）,

@Osumintoutput

AS

SELECT@osum=Ordersum

FROMOrders

WHERECustomerID=@cnumber

*编写存储过程sp_insertGoods，向Goods中插入一条记录*/

CREATEproceduresp_insertGoods

@GoodsIDvarchar（20）VARCHAR（200）VARCHAR（50）,@StatusTINYINT

AS

insertintoGoods

values（@@@@Status）;/

创建一个存储过程Goods_Orders1,查看任何指定货物的订单情况，包括订单号、订货客户的姓名以及订货数量等（要使用输入参数）*/

CREATEprocedureGoods_Orders1

@GoodsIDint,

@OrderIDintvarchar（20）intmoneydatetimeoutput

AS

SELECTOrderID,CName,Quantity,OrderSum,Orderdata

FROMOrders,Customers

WHERECustomers.CustomerID=Orders.CustomerIDandGoodsID=@GoodsID;/*1.

.执行存储过程Goods_Orders1时，如果没有给出参数，则系统会报错，如果希望不给出参数是能查出所有货品的订单，

则可以用默认参数值来实现。

为此要求创建另一个存储过程Goods_Orders2来完成此功能

CREATEprocedureGoods_Orders2

@GoodsIDint=NULL

AS

IF@GoodsIDisNULL

BEGIN

SELECTOrderID,CName,Quantity,OrderSum,Orderdata

FROMOrders,Customers

WHERECustomers.CustomerID=Orders.CustomerID

END

ELSE

BEGIN

SELECTOrderID,CName,Quantity,OrderSum,Orderdata

FROMOrders,Customers

WHERECustomers.CustomerID=Orders.CustomerIDandGoodsID=@GoodsID

END

/*3.创建一个存储过程Goods_OrderSum，来获得某个货品的订单总额（要使用输入以及输出参数）*/

CREATEprocedureGoods_OrderSum

@GoodsNamevarchar（20）,

@OrderSummoneyoutput

AS

SELECTOrderSum

FROMOrders,Goods

WHEREGoods.GoodsID=Orders.GoodsIDandGoodsName=@GoodsName;

实验8触发器

一、实验目的

学习触发器的使用，体会触发器执行的时机，加深对触发器功能和作用的理解。

二、实验内容

（1）在Market数据库中，完成第4章习题5中（5）-（9）创建触发器的操作，然后在相关的表上执行INSERT、UPDATE或DALETE语句，观察他们的执行结果。

三、运行结果

五．代码设计技巧及体会

首先，我觉得自己使用SQL就比较生疏，刚开始不是很了解，可是通过几次上机试验后对于程序运行环境有了进一步的了解，为写代码和使用SQL打下了基础，第一次写了这么多的语句，遇到了很多困难，有些在书上找不到，需要自己认真思考，根据书上的例子进行变通，不会的时候就请教了老师或者同学，解决了一大部分的问题，自己对于SQL语句也有更进一步的了解，到后来能够自己去写代码而不用参考教材，这其中真的学到了不少东西，不足的地方还是有的，有一小部分的问题还等待在日后的学习中去解决。

源代码：

/*基本表的定义与创建*/

CREATETABLECustomers

（

CustomerIDintIDENTITY（1,1）PRIMARYKEY,

CNamevarchar（8）NOTNULL,

Addressvarchar（50）,

Cityvarchar（10）,

Telvarchar（20）UNIQUE,

Companyvarchar（50）,

Birthdaydatetime,

TypetinyintDEFAULT1

）;

CREATETABLEGoods

（

GoodsIDintCONSTRAINTC1PRIMARYKEY,

GoodsNamevarchar（20）NOTNULL,

Pricemoney,

Decriptionvarchar（200）,

Storageint,

Providervarchar（50）,

StatustinyintDEFAULT（0）

）;

CREATETABLEOrders

（

OrderIDintIDENTITY（1,1）CONSTRAINTC2PRIMARYKEY,

GoodsIDintNOTNULLREFERENCESGoods（GoodsID）ONDELETECASCADE,

/*表级完整性约束条件，参照约束为Goods（GoodID），删除时进行级联删除*/

CustomerIDintNOTNULLFOREIGNKEY（CustomerID）

REFERENCESCustomers（CustomerID）ONDELETENOACTION,

/*表级完整性约束条件，参照约束为Customers（CustomerID），删除时不删除参照表中的被引用行*/

QuantityintNOTNULLCONSTRAINTC3CHECK（Quantity>0）,

OrdersummoneyNOTNULL,

OrderDatadatetimeDEFAULT（getdate（））

）;

/*在Customers表中已有数据的基础上向各个表中插入数据*/

INSERTINTOCustomers（CName,Address,City,Tel,Company,Birthday）

VALUES（'王伟','长安区','西安','029005','邮电大学',''）

INSERTINTOCustomers（CName,Address,City,Tel,Company,Birthday）

VALUES（'惠敏','海淀区','北京','010001','中国农业大学',''）

INSERTINTOCustomers（CName,Address,City,Tel,Company,Birthday）

VALUES（'王二小','浦东新区','上海','021001','上海商学院',''）

INSERTINTOCustomers（CName,Address,City,Tel,Company,Birthday）

VALUES（'周颖','临潼区','西安','029006','西安科技大学',''）

INSERTINTOCustomers（CName,Address,City,Tel,Company,Birthday）

VALUES（'吴起','槐荫区','山东','058436','山东师范',''）

/*向Goods表中插入数据*/

INSERTINTOGoods（GoodsID,GoodsName,Price,Decription,Storage,Provider,Status）

VALUES（'0001','书包',45.00,'双肩包','20','舒适','1'）

INSERTINTOGoods（GoodsID,GoodsName,Price,Decription,Storage,Provider,Status）

VALUES（'0002','水杯',32.00,'塑料','322','特百惠','1'）

INSERTINTOGoods（GoodsID,GoodsName,Price,Decription,Storage,Provider,Status）

VALUES（'0003','上衣',100.22,'长款','18','春季适用','1'）

INSERTINTOGoods（GoodsID,GoodsName,Price,Decription,Storage,Provider,Status）

VALUES（'0004','风衣',128.22,'长款','28','秋季适用','1'）

INSERTINTOGoods（GoodsID,GoodsName,Price,Decription,Storage,Provider,Status）

VALUES（'0005','短裤',56.88,'超短','50','夏季适用','1'）

/*向Orders表中插入数据，其中有错误数据的测试*/

INSERTINTOOrders（GoodsID,CustomerID,Quantity,Ordersum,OrderData）

VALUES（'0003','1','4',100.22,''）

INSERTINTOOrders（GoodsID,CustomerID,Quantity,Ordersum,OrderData）

VALUES（'0001','2','3',45.00,''）

INSERTINTOOrders（GoodsID,CustomerID,Quantity,Ordersum,OrderData）

VALUES（'0002','5','10',32.00,''）

INSERTINTOOrders（GoodsID,CustomerID,Quantity,Ordersum,OrderData）

VALUES（'0004','3','2',128.22,''）

INSERTINTOOrders（GoodsID,CustomerID,Quantity,Ordersum,OrderData）/*该语句错误，应为赋给OrderID的值没有参照表Orders*/

VALUES（'0006','3','1',32.00,''）

/*修改表中的数据*/

UPDATEGoods/*将Goods表中的所有价钱都增加10*/

SE