数据库系统概论实验八:综合性实验

数据库第八次实验是前七次实验的综合，题目只是在前七次题目的基础上做了一定的修改。

一 实验题目：数据库综合实验
二 实验目的：加深对本学期全内容的理解、掌握和运用，熟练进行相应的建库、建表、数据操纵等基本操作，嵌套查询、连接查询、统计查询等复杂处理，以及视图、触发器、存储过程等内容的定义和使用方法。
三 实验内容及要求：
商品（编号，品名，进价，库存，售价，厂商编号）
顾客（卡号，姓名，电话，积分）
厂商（编号，厂址，名称、电话）
销售（顾客卡号，商品编号，数量，日期）
1、使用SQL语句实现数据库的建库、建表过程。要求：定义合理的主文件和日志文件及其各项参数；基于实体完整性，参照完整性和用户自定义完整性，定义各表主键、外键等合理的字段约束。

create database 商品信息
on primary(
name='goodsinfo_data',
filename='D:\test\goodesinfo_data.mdl',
size=10,
maxsize=100,
filegrowth=5
)
log on(
name='goodsinfo_log',
filename='D:\test\goodsinfo_log.ldf',
size=10,
maxsize=100,
filegrowth=5
);

use 商品信息
create table 商品(
编号 bigint primary key not null,
品名 varchar(20) not null,
进价 float check(进价>0) null,
库存 int null,
售价 float check(售价>0)null,
厂商编号 bigint null
);

create table 顾客(
卡号 bigint primary key not null,
姓名 varchar(10) not null,
电话 varchar(11) null,
积分 int not null
);

create table 厂商(
编号 int primary key not null,
厂址 varchar(50) null,
名称 varchar(30) not null,
电话 varchar(15) not null, 
经理 varchar(10) null
);
create table 销售(
卡号 bigint not null foreign key references 顾客(卡号) ,
商品编号 bigint not null foreign key references 商品(编号),
数量 int check(数量>=0),
日期 date not null
);

执行结果：

2、使用存储过程传参方式进行各表测试数据的插入，每个表至少5条测试数据。

insert into 商品(编号,品名,进价,库存,售价,厂商编号)
values(1001,'雀巢咖啡',3.9,210,5.9,8001),
(1002,'蒙牛纯牛奶',1.9,200,2.9,8002),
(1003,'旺仔牛奶',70,150,90,8003),
(1004,'泡芙',4.8,162,6.8,8004),
(1005,'德芙巧克力',10.3,45,15.2,8005),
(1006,'伊利纯牛奶',20.3,90,30.5,8006),
(1007,'伊利甜牛奶',19.3,100,29.0,8006),
(1008,'伊利纯甄酸奶',28.8,110,35.6,8006),
(1009,'伊利金典纯牛奶',48.8,130,52.5,8006),
(1010,'伊利优酸乳',20.3,120,25.5,8006),
(1011,'伊利奶粉',18.8,130,24.6,8006);


insert into 顾客(卡号,姓名,电话,积分)
values(2001,'张三','15718888888',1000),
(2002,'罗二','15710088888',70),
(2003,'小王','15710066666',1100),
(2004,'小刘','15710077777',10),
(2005,'小陈','15988900000',0);

insert into 厂商(编号,厂址,名称,电话,经理)
values(8001,'北京市朝阳区人民路号','雀巢食品有限公司','05316888888','李经理'),
(8002,'内蒙古自治区呼和浩特','蒙牛食品有限公司','05316008888','张经理'),
(8003,'四川省成都市','旺仔食品有限公司','08736700666','陈经理'),
(8005,'四川省成都市','德芙食品有限公司','08736700777','罗经理'),
(8006,'内蒙古自治区呼和浩特','伊利乳品','05316006666','业经理');

insert into 销售(卡号,商品编号,数量,日期)
values(2001,1001,1100,getdate()),
(2002,1002,50,'2020-11-09'),
(2003,1009,650,'2020-11-11'),
(2003,1009,420,'2020-11-19'),
(2004,1007,20,'2020-12-09');

执行结果：

3、利用视图，查询比“伊利乳品”所有产品都贵的商品价格。

create view 价格比较
as
select 售价'商品价格'
from 商品
where 售价 > 
	(select MAX(售价) 
	 from 商品
	 where 厂商编号 in	
		(select 编号 
		 from 厂商
		 where 名称='伊利乳品')
	 );

select*from 价格比较;

运行结果：

4、使用嵌套查询，给出积分20以上的顾客购买的商品名称和价格。

select 品名'商品名称',售价'价格' 
from 商品
where 编号 in 
	(select 商品编号 
	 from 销售
	 where 卡号 in
		(select 卡号 
		 from 顾客
		 where 积分 > 20)
	)

执行结果：

5、建立商品的delete 触发器，只有当此商品没有人买时才可删除，若有销售记录则提示“对不起，该商品已存档”并禁止删除。

Create trigger goods_d
On 商品
for delete
as
declare @编号 bigint
declare @count int

select @编号=编号 from deleted

select @count=count(*) from 销售 where 商品编号=@编号

if(@count>0)
begin
print '对不起，该商品已存档'
rollback
end
else
print '商品删除成功'

delete 商品 where 编号=1010

执行结果：

delete 商品 where 编号=1001

商品中编号是销售中商品编号的外键，因此有冲突，在销售中的商品无法删除

取消外键后：

6、使用存储过程，通过传入商品编号参数，将价格高于20元的商品降价至15元。

create procedure ManageGoods
@编号 bigint
as
begin
if @编号 not in(select 编号 from 商品)
begin
print('没有该商品')
rollback
end

if((select 售价 from 商品 where 编号 = @编号)>20)
begin
update 商品
set 售价 = 15
where 编号 = @编号
select* from 商品 where 编号=@编号 
end
end;

售价高于20则降价

exec ManageGoods 1008

售价低于20无变化