SQL数据库实验报告

网站建设1650 更新时间：2025-06-08 04:20:08

2023年6月21日发(作者：)

西安邮电大学

学院名称学生姓名专业名称班级学号时间数据库课内实验

报告书

计算机学院

高丹

计算机科学与技术

1106班

04111196(22)

2013.4—2013.5

：：：：

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

一、实验目的

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

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

3. 学会使用SQL Server 2000提供的数据完整性功能，并在创建表时定义表的数据完整性。通过实验进一步理解数据完整性的概念及分类。

二、实验内容

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

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

CREATE TABLE Customers

(

CustomerID int IDENTITY(1,1) PRIMARY KEY,

CName varchar(8) NOT NULL,

Address varchar(50),

City varchar(10),

Tel varchar(20) UNIQUE,

Company varchar(50),

Birthday datetime,

Type tinyint DEFAULT 1

);

CREATE TABLE Goods

(

GoodsID int CONSTRAINT C1 PRIMARY KEY,

GoodsName varchar(20) NOT NULL,

Price money,

Decription varchar(200),

Storage int,

Provider varchar(50),

Status tinyint DEFAULT(0)

);

CREATE TABLE Orders

(

OrderID int IDENTITY(1,1) CONSTRAINT C2 PRIMARY KEY,

GoodsID int NOT NULL REFERENCES Goods(GoodsID) ON DELETE CASCADE,

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

CustomerID int NOT NULL FOREIGN KEY(CustomerID)

REFERENCES Customers(CustomerID) ON DELETE NO ACTION,

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

Quantity int NOT NULL CONSTRAINT C3 CHECK(Quantity > 0), Ordersum money NOT NULL,

OrderData datetime DEFAULT(getdate())

);

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

一、实验目的

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

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

二、实验内容

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

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

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

INSERT INTO Customers(CName,Address,City,Tel,Company,Birthday)

VALUES('王伟','长安区','西安','029005','邮电大学','1995-02-13')

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

INSERT INTO

Goods(GoodsID,GoodsName,Price,Decription,Storage,Provider,Status)

VALUES('0001','书包',45.00,'双肩包','20','舒适','1')

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

INSERT INTO

Goods(GoodsID,GoodsName,Price,Decription,Storage,Provider,Status)

VALUES('0001','书包',45.00,'双肩包','20','舒适','1')

/*修改表中的数据*/ UPDATE Goods /*将Goods表中的所有价钱都增加*/

SET Price=Price+10;

UPDATE Orders

SET Quantity=5

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

UPDATE Customers

SET Tel=0911536 , Address='长安区'

WHERE CustomerID=3;

修改后的表：

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

DELETE FROM Orders

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

删除记录之后的表：

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

一、实验目的

数据查询时数据库中最基本的操作，也是使用最频繁的操作，因此必须掌握SQL的查询语句（SELECT语句）的使用方法。本实验要求掌握简单表的数据查询、数据排序和多表操作时数据连接查询的操作方法。二、实验内容

(1)简单查询操作。该实验包括投影、选择条件表达、数据排序等。

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

(2)连接查询操作。该实验包括等值连接、自然连接、一般连接(内连接) 、外连接、左外连接、右外连接和自然连接等。

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

(一)简单查询操作

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

SELECT *

FROM Customers

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

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

SELECT GoodsName,Storage,Price,0.25*Price Discount

From Goods

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

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

SELECT GoodsID,GoodsName,Price

From Goods

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

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

SELECT GoodsName,Storage,Price

From Goods

WHERE Storage>100 AND Storage<500; /*查询库存量大于小于的商品名称，库存和单价*/ （二）连接查询操作

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

/*连接查询*/

SELECT Goodsname,Cname,Price,Quantity,Ordersum

FROM Customers,Goods,Orders

WHERE erID=erID and

D=D and

Orderdata between '2011-01-1 'and '2012-1-1' and Quantity>2

实验5 数据库的复杂查询

一、实验目的

进一步掌握SELECT语句的使用方法，通过实验能够熟练地使用SELECT语句。本实验要求掌握嵌套查询和统计查询的操作方法。

二、实验内容

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

三、运行结果

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

SELECT OrderID,GoodsID,CustomerID

FROM Orders

WHERE Quantity<=30

(SELECT Orderdata

FROM Orders

group by Orderdata);

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

SELECT *

FROM Orders

WHERE CustomerID in

(SELECT CustomerID FROM Customers

WHERE City='西安');

(8)查找不是上海客户的订单信息

SELECT *

FROM Orders

WHERE CustomerID in

(SELECT CustomerID

FROM Customers

WHERE City<>'上海');

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

SELECT *

FROM Customers,Orders

WHERE erID=erID and City='西安';

实验6 索引和视图

一、实验目的

(1)理解索引和视图的概念。

(2)掌握索引的使用方法。

(3)掌握视图的定义和使用方法。

二、实验内容

在Market数据库中，完成第3章习题11中(1)的建立视图的操作，然后在该视图上完成第3章习题11中(2)的查询操作。

三、运行结果

CREATE VIEW XIAN_ORDER(OrderID,GoodsName,Quantity,CustomerID,Cname)

AS

SELECT OrderID,GoodsName,Quantity,erID,Cname

FROM Orders,Goods,Customers

WHERE erID=erID AND

D=D AND City='西安' /*创建西安客户订单的视图*/

SELECT *

FROM XIAN_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)创建存储过程的操作结果如下

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

CREATE procedure sp_shanghai

AS

SELECT *

FROM customers

WHERE city='上海';

CREATE procedure sp_Goods

@GoodsID char(20)

AS SELECT D,GoodsName,Price,Provider,Storage,Status

FROM Goods

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

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

CREATE procedure sp_goodsum @Cnumber char(10),

@Osum int output

AS

SELECT @osum=Ordersum

FROM Orders

WHERE CustomerID=@cnumber

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

CREATE procedure sp_insertGoods

@GoodsID int,@GoodsName varchar(20),@Price money,@Decription

VARCHAR(200),@Storage int,@Provider VARCHAR(50), @Status TINYINT

AS

insert into Goods

values

(@GoodsID,@GoodsName,@Price,@Decription,@Storage,@Provider,@Status);/

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

CREATE procedure Goods_Orders1

@GoodsID int,

@OrderID int output,@CName varchar(20) output,@Quantity int

output,@OrderSum money output,@Orderdata datetime output

AS

SELECT OrderID,CName,Quantity,OrderSum,Orderdata

FROM Orders,Customers WHERE erID=erID and GoodsID=@GoodsID;/*1.

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

则可以用默认参数值来实现。为此要求创建另一个存储过程Goods_Orders2来完成此功能

CREATE procedure Goods_Orders2

@GoodsID int=NULL

AS

IF @GoodsID is NULL

BEGIN

SELECT OrderID,CName,Quantity,OrderSum,Orderdata

FROM Orders,Customers

WHERE erID=erID

END

ELSE

BEGIN

SELECT OrderID,CName,Quantity,OrderSum,Orderdata

FROM Orders,Customers

WHERE erID=erID and

GoodsID=@GoodsID

END

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

CREATE procedure Goods_OrderSum

@GoodsName varchar(20),

@OrderSum money output

AS SELECT OrderSum

FROM Orders,Goods

WHERE D=D and GoodsName=@GoodsName;

实验8 触发器

一、实验目的

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

二、实验内容

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

三、运行结果

五．

代码设计技巧及体会

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

源代码：

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

CREATE TABLE Customers

(

CustomerID int IDENTITY(1,1) PRIMARY KEY,

CName varchar(8) NOT NULL,

Address varchar(50),

City varchar(10),

Tel varchar(20) UNIQUE,

Company varchar(50),

Birthday datetime,

Type tinyint DEFAULT 1

);

CREATE TABLE Goods

(

GoodsID int CONSTRAINT C1 PRIMARY KEY,

GoodsName varchar(20) NOT NULL,

Price money,

Decription varchar(200),

Storage int, Provider varchar(50),

Status tinyint DEFAULT(0)

);

CREATE TABLE Orders

(

OrderID int IDENTITY(1,1) CONSTRAINT C2 PRIMARY KEY,

GoodsID int NOT NULL REFERENCES Goods(GoodsID) ON DELETE CASCADE,

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

CustomerID int NOT NULL FOREIGN KEY(CustomerID)

REFERENCES Customers(CustomerID) ON DELETE NO ACTION,

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

Quantity int NOT NULL CONSTRAINT C3 CHECK(Quantity > 0),

Ordersum money NOT NULL,

OrderData datetime DEFAULT(getdate())

);

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

INSERT INTO Customers(CName,Address,City,Tel,Company,Birthday)

VALUES('王伟','长安区','西安','029005','邮电大学','1995-02-13')

INSERT INTO Customers(CName,Address,City,Tel,Company,Birthday)

VALUES('惠敏','海淀区','北京','010001','中国农业大学','1992-06-23')

INSERT INTO Customers(CName,Address,City,Tel,Company,Birthday)

VALUES('王二小','浦东新区','上海','021001','上海商学院','1991-05-07')

INSERT INTO Customers(CName,Address,City,Tel,Company,Birthday)

VALUES('周颖','临潼区','西安','029006','西安科技大学','1991-02-02')

INSERT INTO Customers(CName,Address,City,Tel,Company,Birthday)

VALUES('吴起','槐荫区','山东','058436','山东师范','1990-10-09')

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

INSERT INTO Goods(GoodsID,GoodsName,Price,Decription,Storage,Provider,Status)

VALUES('0001','书包',45.00,'双肩包','20','舒适','1')

INSERT INTO Goods(GoodsID,GoodsName,Price,Decription,Storage,Provider,Status)

VALUES('0002','水杯',32.00,'塑料','322','特百惠','1')

INSERT INTO Goods(GoodsID,GoodsName,Price,Decription,Storage,Provider,Status)

VALUES('0003','上衣',100.22,'长款','18','春季适用','1') INSERT INTO Goods(GoodsID,GoodsName,Price,Decription,Storage,Provider,Status)

VALUES('0004','风衣',128.22,'长款','28','秋季适用','1')

INSERT INTO Goods(GoodsID,GoodsName,Price,Decription,Storage,Provider,Status)

VALUES('0005','短裤',56.88,'超短','50','夏季适用','1')

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

INSERT INTO Orders(GoodsID,CustomerID,Quantity,Ordersum,OrderData)

VALUES('0003','1','4',100.22,'2011-05-07')

INSERT INTO Orders(GoodsID,CustomerID,Quantity,Ordersum,OrderData)

VALUES('0001','2','3',45.00,'2011-12-21')

INSERT INTO Orders(GoodsID,CustomerID,Quantity,Ordersum,OrderData)

VALUES('0002','5','10',32.00,'2012-03-02')

INSERT INTO Orders(GoodsID,CustomerID,Quantity,Ordersum,OrderData)

VALUES('0004','3','2',128.22,'2013-04-18')

INSERT INTO Orders(GoodsID,CustomerID,Quantity,Ordersum,OrderData)/*该语句错误，应为赋给OrderID的值没有参照表Orders*/

VALUES('0006','3','1',32.00,'2013-05-07')

/*修改表中的数据*/

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

SET Price=Price+10;

UPDATE Orders

SET Quantity=5

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

UPDATE Customers

SET Tel=0911536 , Address='长安区'

WHERE CustomerID=3;

UPDATE Orders

SET CustomerID=10

WHERE GoodsID=0001; /*该语句错误，没有参考完整性*/

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

DELETE FROM Orders

WHERE CustomerID=2;/*删除了CustomerID为2的订单记录*/ /*查询*/

SELECT *

FROM Customers

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

SELECT GoodsName,Storage,Price,0.25*Price Discount

From Goods

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

SELECT GoodsID,GoodsName,Price

From Goods

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

SELECT GoodsName,Storage,Price

From Goods

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

/*连接查询*/

SELECT Goodsname,Cname,Price,Quantity,Ordersum

FROM Customers,Goods,Orders

WHERE erID=erID and

D=D and

Orderdata between '2011-01-1 'and '2012-1-1' and Quantity>2

/*复杂查询*/

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

SELECT OrderID,GoodsID,CustomerID

FROM Orders

WHERE Quantity<=30

(SELECT Orderdata

FROM Orders

group by Orderdata);

/*查找北京客户的订单信息*/

SELECT *

FROM Orders

WHERE CustomerID in

(SELECT CustomerID

FROM Customers

WHERE City='西安'); /*查找不是上海客户的订单信息*/

SELECT *

FROM Orders

WHERE CustomerID in

(SELECT CustomerID

FROM Customers

WHERE City<>'上海');

/*北京客户的相关信息及订单情况*/

SELECT *

FROM Customers,Orders

WHERE erID=erID and City='西安';

CREATE VIEW XIAN_ORDER(OrderID,GoodsName,Quantity,CustomerID,Cname)

AS

SELECT OrderID,GoodsName,Quantity,erID,Cname

FROM Orders,Goods,Customers

WHERE erID=erID AND D=D AND

City='西安' /*创建西安客户订单的视图*/

SELECT *

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

/*存储过程*/

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

CREATE procedure sp_shanghai

AS

SELECT *

FROM customers

WHERE city='上海';

CREATE procedure sp_Goods

@GoodsID char(20)

AS SELECT D,GoodsName,Price,Provider,Storage,Status

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

EXECUTE sp_Goods @GoodsID=0001;

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

CREATE procedure sp_goodsum

@Cnumber char(10),

@Osum int output

AS

SELECT @osum=Ordersum FROM Orders

WHERE CustomerID=@cnumber

CREATE procedure sp_insertGoods

@GoodsID int,@GoodsName varchar(20),@Price money,@Decription

VARCHAR(200),@Storage int,@Provider VARCHAR(50), @Status TINYINT

AS

insert into Goods

values (@GoodsID,@GoodsName,@Price,@Decription,@Storage,@Provider,@Status);/*编写存储过程sp_insertGoods，向Goods中插入一条记录*/

DROP procedure sp_insertGoods

/*调用存储过程*/

EXECUTE procedure sp_insertGoods

0006,'帽子',18.00,'沙滩帽',100,'上海',1

CREATE procedure Goods_Orders1

@GoodsID int,

@OrderID int output,@CName varchar(20) output,@Quantity int output,@OrderSum money

output,@Orderdata datetime output

AS

SELECT OrderID,CName,Quantity,OrderSum,Orderdata

FROM Orders,Customers

WHERE erID=erID and GoodsID=@GoodsID;/*1.创建一个存储过程Goods_Orders1,查看任何指定货物的订单情况，包括订单号、订货客户的姓名以及订货数量等(要使用输入参数)*/

CREATE procedure Goods_Orders2

@GoodsID int=NULL

AS

IF @GoodsID is NULL

BEGIN

SELECT OrderID,CName,Quantity,OrderSum,Orderdata

FROM Orders,Customers

WHERE erID=erID

END

ELSE

BEGIN

SELECT OrderID,CName,Quantity,OrderSum,Orderdata

FROM Orders,Customers

WHERE erID=erID and GoodsID=@GoodsID

END /*2.执行存储过程Goods_Orders1时，如果没有给出参数，则系统会报错，如果希望不给出参数是能查出所有货品的订单，则可以用默认参数值来实现。为此要求创建另一个存储过程Goods_Orders2来完成此功能*/

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

CREATE procedure Goods_OrderSum

@GoodsName varchar(20),

@OrderSum money output

AS

SELECT OrderSum

FROM Orders,Goods

WHERE D=D and GoodsName=@GoodsName;

/*触发器*/

/*在Customers表上建立删除触发器，实现Customers表和Orders表的级联删除*/

CREATE trigger CustomersDelete on Customers

AFTER delete

as

delete from Orders

where CustomerID in

(select erID from deleted);

/*在Orders表上建立插入触发器，当向表中添加一条订货记录时，若订单中的商品状态为整理(Status=1),则不能插入该条记录*/

CREATE trigger Orders_insert on Orders

AFTER INSERT

AS

IF(SELECT count(*) FROM Goods, inserted

WHERE Status=0)=0

BEGIN

print'商品状态为整理(Status=1) 不能插入该条记录'

rollback transaction

END;

/*在Orders表上建立插入触发器，当添加订单时，减少Goods表中相应商品的库存量*/

CREATE trigger Orders_insert2 on Orders

AFTER INSERT

AS

IF(SELECT count(*) FROM Goods, inserted

)=1

BEGIN

update Goods

set Storage=ty

rollback transaction END;

/*在Orders表上建立触发器，不允许对订单日期进行修改*/

CREATE trigger Orders_update on Orders

AFTER update

AS

IF update(Orderdata)

BEGIN

print'不允许对订单日期进行修改'

rollback transaction

END;

/*建立触发器，实现参照完整性约束，即若在Orders表中添加一条订单记录时，则该订单中的商品必须在Goods表中存在，

否则不允许添加该记录*/

CREATE trigger Orders_insert3 on Orders

AFTER INSERT

AS

IF(SELECT count(*) FROM Goods, inserted

WHERE D=D)=0

BEGIN

print'不允许添加该记录'

rollback transaction

END;

2023年6月21日发(作者：)

西安邮电大学

学院名称学生姓名专业名称班级学号时间数据库课内实验

报告书

计算机学院

高丹

计算机科学与技术

1106班

04111196(22)

2013.4—2013.5

：：：：

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

一、实验目的

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

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

3. 学会使用SQL Server 2000提供的数据完整性功能，并在创建表时定义表的数据完整性。通过实验进一步理解数据完整性的概念及分类。

二、实验内容

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

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

CREATE TABLE Customers

(

CustomerID int IDENTITY(1,1) PRIMARY KEY,

CName varchar(8) NOT NULL,

Address varchar(50),

City varchar(10),

Tel varchar(20) UNIQUE,

Company varchar(50),

Birthday datetime,

Type tinyint DEFAULT 1

);

CREATE TABLE Goods

(

GoodsID int CONSTRAINT C1 PRIMARY KEY,

GoodsName varchar(20) NOT NULL,

Price money,

Decription varchar(200),

Storage int,

Provider varchar(50),

Status tinyint DEFAULT(0)

);

CREATE TABLE Orders

(

OrderID int IDENTITY(1,1) CONSTRAINT C2 PRIMARY KEY,

GoodsID int NOT NULL REFERENCES Goods(GoodsID) ON DELETE CASCADE,

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

CustomerID int NOT NULL FOREIGN KEY(CustomerID)

REFERENCES Customers(CustomerID) ON DELETE NO ACTION,

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

Quantity int NOT NULL CONSTRAINT C3 CHECK(Quantity > 0), Ordersum money NOT NULL,

OrderData datetime DEFAULT(getdate())

);

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

一、实验目的

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

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

二、实验内容

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

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

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

INSERT INTO Customers(CName,Address,City,Tel,Company,Birthday)

VALUES('王伟','长安区','西安','029005','邮电大学','1995-02-13')

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

INSERT INTO

Goods(GoodsID,GoodsName,Price,Decription,Storage,Provider,Status)

VALUES('0001','书包',45.00,'双肩包','20','舒适','1')

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

INSERT INTO

Goods(GoodsID,GoodsName,Price,Decription,Storage,Provider,Status)

VALUES('0001','书包',45.00,'双肩包','20','舒适','1')

/*修改表中的数据*/ UPDATE Goods /*将Goods表中的所有价钱都增加*/

SET Price=Price+10;

UPDATE Orders

SET Quantity=5

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

UPDATE Customers

SET Tel=0911536 , Address='长安区'

WHERE CustomerID=3;

修改后的表：

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

DELETE FROM Orders

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

删除记录之后的表：

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

一、实验目的

数据查询时数据库中最基本的操作，也是使用最频繁的操作，因此必须掌握SQL的查询语句（SELECT语句）的使用方法。本实验要求掌握简单表的数据查询、数据排序和多表操作时数据连接查询的操作方法。二、实验内容

(1)简单查询操作。该实验包括投影、选择条件表达、数据排序等。

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

(2)连接查询操作。该实验包括等值连接、自然连接、一般连接(内连接) 、外连接、左外连接、右外连接和自然连接等。

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

(一)简单查询操作

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

SELECT *

FROM Customers

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

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

SELECT GoodsName,Storage,Price,0.25*Price Discount

From Goods

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

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

SELECT GoodsID,GoodsName,Price

From Goods

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

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

SELECT GoodsName,Storage,Price

From Goods

WHERE Storage>100 AND Storage<500; /*查询库存量大于小于的商品名称，库存和单价*/ （二）连接查询操作

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

/*连接查询*/

SELECT Goodsname,Cname,Price,Quantity,Ordersum

FROM Customers,Goods,Orders

WHERE erID=erID and

D=D and

Orderdata between '2011-01-1 'and '2012-1-1' and Quantity>2

实验5 数据库的复杂查询

一、实验目的

进一步掌握SELECT语句的使用方法，通过实验能够熟练地使用SELECT语句。本实验要求掌握嵌套查询和统计查询的操作方法。

二、实验内容

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

三、运行结果

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

SELECT OrderID,GoodsID,CustomerID

FROM Orders

WHERE Quantity<=30

(SELECT Orderdata

FROM Orders

group by Orderdata);

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

SELECT *

FROM Orders

WHERE CustomerID in

(SELECT CustomerID FROM Customers

WHERE City='西安');

(8)查找不是上海客户的订单信息

SELECT *

FROM Orders

WHERE CustomerID in

(SELECT CustomerID

FROM Customers

WHERE City<>'上海');

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

SELECT *

FROM Customers,Orders

WHERE erID=erID and City='西安';

实验6 索引和视图

一、实验目的

(1)理解索引和视图的概念。

(2)掌握索引的使用方法。

(3)掌握视图的定义和使用方法。

二、实验内容

在Market数据库中，完成第3章习题11中(1)的建立视图的操作，然后在该视图上完成第3章习题11中(2)的查询操作。

三、运行结果

CREATE VIEW XIAN_ORDER(OrderID,GoodsName,Quantity,CustomerID,Cname)

AS

SELECT OrderID,GoodsName,Quantity,erID,Cname

FROM Orders,Goods,Customers

WHERE erID=erID AND

D=D AND City='西安' /*创建西安客户订单的视图*/

SELECT *

FROM XIAN_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)创建存储过程的操作结果如下

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

CREATE procedure sp_shanghai

AS

SELECT *

FROM customers

WHERE city='上海';

CREATE procedure sp_Goods

@GoodsID char(20)

AS SELECT D,GoodsName,Price,Provider,Storage,Status

FROM Goods

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

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

CREATE procedure sp_goodsum @Cnumber char(10),

@Osum int output

AS

SELECT @osum=Ordersum

FROM Orders

WHERE CustomerID=@cnumber

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

CREATE procedure sp_insertGoods

@GoodsID int,@GoodsName varchar(20),@Price money,@Decription

VARCHAR(200),@Storage int,@Provider VARCHAR(50), @Status TINYINT

AS

insert into Goods

values

(@GoodsID,@GoodsName,@Price,@Decription,@Storage,@Provider,@Status);/

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

CREATE procedure Goods_Orders1

@GoodsID int,

@OrderID int output,@CName varchar(20) output,@Quantity int

output,@OrderSum money output,@Orderdata datetime output

AS

SELECT OrderID,CName,Quantity,OrderSum,Orderdata

FROM Orders,Customers WHERE erID=erID and GoodsID=@GoodsID;/*1.

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

则可以用默认参数值来实现。为此要求创建另一个存储过程Goods_Orders2来完成此功能

CREATE procedure Goods_Orders2

@GoodsID int=NULL

AS

IF @GoodsID is NULL

BEGIN

SELECT OrderID,CName,Quantity,OrderSum,Orderdata

FROM Orders,Customers

WHERE erID=erID

END

ELSE

BEGIN

SELECT OrderID,CName,Quantity,OrderSum,Orderdata

FROM Orders,Customers

WHERE erID=erID and

GoodsID=@GoodsID

END

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

CREATE procedure Goods_OrderSum

@GoodsName varchar(20),

@OrderSum money output

AS SELECT OrderSum

FROM Orders,Goods

WHERE D=D and GoodsName=@GoodsName;

实验8 触发器

一、实验目的

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

二、实验内容

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

三、运行结果

五．

代码设计技巧及体会

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

源代码：

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

CREATE TABLE Customers

(

CustomerID int IDENTITY(1,1) PRIMARY KEY,

CName varchar(8) NOT NULL,

Address varchar(50),

City varchar(10),

Tel varchar(20) UNIQUE,

Company varchar(50),

Birthday datetime,

Type tinyint DEFAULT 1

);

CREATE TABLE Goods

(

GoodsID int CONSTRAINT C1 PRIMARY KEY,

GoodsName varchar(20) NOT NULL,

Price money,

Decription varchar(200),

Storage int, Provider varchar(50),

Status tinyint DEFAULT(0)

);

CREATE TABLE Orders

(

OrderID int IDENTITY(1,1) CONSTRAINT C2 PRIMARY KEY,

GoodsID int NOT NULL REFERENCES Goods(GoodsID) ON DELETE CASCADE,

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

CustomerID int NOT NULL FOREIGN KEY(CustomerID)

REFERENCES Customers(CustomerID) ON DELETE NO ACTION,

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

Quantity int NOT NULL CONSTRAINT C3 CHECK(Quantity > 0),

Ordersum money NOT NULL,

OrderData datetime DEFAULT(getdate())

);

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

INSERT INTO Customers(CName,Address,City,Tel,Company,Birthday)

VALUES('王伟','长安区','西安','029005','邮电大学','1995-02-13')

INSERT INTO Customers(CName,Address,City,Tel,Company,Birthday)

VALUES('惠敏','海淀区','北京','010001','中国农业大学','1992-06-23')

INSERT INTO Customers(CName,Address,City,Tel,Company,Birthday)

VALUES('王二小','浦东新区','上海','021001','上海商学院','1991-05-07')

INSERT INTO Customers(CName,Address,City,Tel,Company,Birthday)

VALUES('周颖','临潼区','西安','029006','西安科技大学','1991-02-02')

INSERT INTO Customers(CName,Address,City,Tel,Company,Birthday)

VALUES('吴起','槐荫区','山东','058436','山东师范','1990-10-09')

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

INSERT INTO Goods(GoodsID,GoodsName,Price,Decription,Storage,Provider,Status)

VALUES('0001','书包',45.00,'双肩包','20','舒适','1')

INSERT INTO Goods(GoodsID,GoodsName,Price,Decription,Storage,Provider,Status)

VALUES('0002','水杯',32.00,'塑料','322','特百惠','1')

INSERT INTO Goods(GoodsID,GoodsName,Price,Decription,Storage,Provider,Status)

VALUES('0003','上衣',100.22,'长款','18','春季适用','1') INSERT INTO Goods(GoodsID,GoodsName,Price,Decription,Storage,Provider,Status)

VALUES('0004','风衣',128.22,'长款','28','秋季适用','1')

INSERT INTO Goods(GoodsID,GoodsName,Price,Decription,Storage,Provider,Status)

VALUES('0005','短裤',56.88,'超短','50','夏季适用','1')

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

INSERT INTO Orders(GoodsID,CustomerID,Quantity,Ordersum,OrderData)

VALUES('0003','1','4',100.22,'2011-05-07')

INSERT INTO Orders(GoodsID,CustomerID,Quantity,Ordersum,OrderData)

VALUES('0001','2','3',45.00,'2011-12-21')

INSERT INTO Orders(GoodsID,CustomerID,Quantity,Ordersum,OrderData)

VALUES('0002','5','10',32.00,'2012-03-02')

INSERT INTO Orders(GoodsID,CustomerID,Quantity,Ordersum,OrderData)

VALUES('0004','3','2',128.22,'2013-04-18')

INSERT INTO Orders(GoodsID,CustomerID,Quantity,Ordersum,OrderData)/*该语句错误，应为赋给OrderID的值没有参照表Orders*/

VALUES('0006','3','1',32.00,'2013-05-07')

/*修改表中的数据*/

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

SET Price=Price+10;

UPDATE Orders

SET Quantity=5

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

UPDATE Customers

SET Tel=0911536 , Address='长安区'

WHERE CustomerID=3;

UPDATE Orders

SET CustomerID=10

WHERE GoodsID=0001; /*该语句错误，没有参考完整性*/

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

DELETE FROM Orders

WHERE CustomerID=2;/*删除了CustomerID为2的订单记录*/ /*查询*/

SELECT *

FROM Customers

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

SELECT GoodsName,Storage,Price,0.25*Price Discount

From Goods

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

SELECT GoodsID,GoodsName,Price

From Goods

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

SELECT GoodsName,Storage,Price

From Goods

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

/*连接查询*/

SELECT Goodsname,Cname,Price,Quantity,Ordersum

FROM Customers,Goods,Orders

WHERE erID=erID and

D=D and

Orderdata between '2011-01-1 'and '2012-1-1' and Quantity>2

/*复杂查询*/

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

SELECT OrderID,GoodsID,CustomerID

FROM Orders

WHERE Quantity<=30

(SELECT Orderdata

FROM Orders

group by Orderdata);

/*查找北京客户的订单信息*/

SELECT *

FROM Orders

WHERE CustomerID in

(SELECT CustomerID

FROM Customers

WHERE City='西安'); /*查找不是上海客户的订单信息*/

SELECT *

FROM Orders

WHERE CustomerID in

(SELECT CustomerID

FROM Customers

WHERE City<>'上海');

/*北京客户的相关信息及订单情况*/

SELECT *

FROM Customers,Orders

WHERE erID=erID and City='西安';

CREATE VIEW XIAN_ORDER(OrderID,GoodsName,Quantity,CustomerID,Cname)

AS

SELECT OrderID,GoodsName,Quantity,erID,Cname

FROM Orders,Goods,Customers

WHERE erID=erID AND D=D AND

City='西安' /*创建西安客户订单的视图*/

SELECT *

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

/*存储过程*/

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

CREATE procedure sp_shanghai

AS

SELECT *

FROM customers

WHERE city='上海';

CREATE procedure sp_Goods

@GoodsID char(20)

AS SELECT D,GoodsName,Price,Provider,Storage,Status

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

EXECUTE sp_Goods @GoodsID=0001;

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

CREATE procedure sp_goodsum

@Cnumber char(10),

@Osum int output

AS

SELECT @osum=Ordersum FROM Orders

WHERE CustomerID=@cnumber

CREATE procedure sp_insertGoods

@GoodsID int,@GoodsName varchar(20),@Price money,@Decription

VARCHAR(200),@Storage int,@Provider VARCHAR(50), @Status TINYINT

AS

insert into Goods

values (@GoodsID,@GoodsName,@Price,@Decription,@Storage,@Provider,@Status);/*编写存储过程sp_insertGoods，向Goods中插入一条记录*/

DROP procedure sp_insertGoods

/*调用存储过程*/

EXECUTE procedure sp_insertGoods

0006,'帽子',18.00,'沙滩帽',100,'上海',1

CREATE procedure Goods_Orders1

@GoodsID int,

@OrderID int output,@CName varchar(20) output,@Quantity int output,@OrderSum money

output,@Orderdata datetime output

AS

SELECT OrderID,CName,Quantity,OrderSum,Orderdata

FROM Orders,Customers

WHERE erID=erID and GoodsID=@GoodsID;/*1.创建一个存储过程Goods_Orders1,查看任何指定货物的订单情况，包括订单号、订货客户的姓名以及订货数量等(要使用输入参数)*/

CREATE procedure Goods_Orders2

@GoodsID int=NULL

AS

IF @GoodsID is NULL

BEGIN

SELECT OrderID,CName,Quantity,OrderSum,Orderdata

FROM Orders,Customers

WHERE erID=erID

END

ELSE

BEGIN

SELECT OrderID,CName,Quantity,OrderSum,Orderdata

FROM Orders,Customers

WHERE erID=erID and GoodsID=@GoodsID

END /*2.执行存储过程Goods_Orders1时，如果没有给出参数，则系统会报错，如果希望不给出参数是能查出所有货品的订单，则可以用默认参数值来实现。为此要求创建另一个存储过程Goods_Orders2来完成此功能*/

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

CREATE procedure Goods_OrderSum

@GoodsName varchar(20),

@OrderSum money output

AS

SELECT OrderSum

FROM Orders,Goods

WHERE D=D and GoodsName=@GoodsName;

/*触发器*/

/*在Customers表上建立删除触发器，实现Customers表和Orders表的级联删除*/

CREATE trigger CustomersDelete on Customers

AFTER delete

as

delete from Orders

where CustomerID in

(select erID from deleted);

/*在Orders表上建立插入触发器，当向表中添加一条订货记录时，若订单中的商品状态为整理(Status=1),则不能插入该条记录*/

CREATE trigger Orders_insert on Orders

AFTER INSERT

AS

IF(SELECT count(*) FROM Goods, inserted

WHERE Status=0)=0

BEGIN

print'商品状态为整理(Status=1) 不能插入该条记录'

rollback transaction

END;

/*在Orders表上建立插入触发器，当添加订单时，减少Goods表中相应商品的库存量*/

CREATE trigger Orders_insert2 on Orders

AFTER INSERT

AS

IF(SELECT count(*) FROM Goods, inserted

)=1

BEGIN

update Goods

set Storage=ty

rollback transaction END;

/*在Orders表上建立触发器，不允许对订单日期进行修改*/

CREATE trigger Orders_update on Orders

AFTER update

AS

IF update(Orderdata)

BEGIN

print'不允许对订单日期进行修改'

rollback transaction

END;

/*建立触发器，实现参照完整性约束，即若在Orders表中添加一条订单记录时，则该订单中的商品必须在Goods表中存在，

否则不允许添加该记录*/

CREATE trigger Orders_insert3 on Orders

AFTER INSERT

AS

IF(SELECT count(*) FROM Goods, inserted

WHERE D=D)=0

BEGIN

print'不允许添加该记录'

rollback transaction

END;

本文发布于:2023-06-21，感谢您对本站的认可！

本文链接:http://torson.com.cn/wangzhan/1687345735a3040.html

SQL数据库实验报告

发布评论取消回复

最近发表

相关推荐

标签列表

SQL数据库实验报告

发布评论 取消回复

最近发表

相关推荐

标签列表

发布评论取消回复