团购网站的数据库设计（代码）
1、 database 团购网站数据库；
(图片为步骤中的其中一个的运行结果)
建表：
用户类别信息表的建立：
create table users(
userid char(30) primary key not null,
username char(20) not null,
userpassword char(20) not null,
rellyname char(20),
rellytell char(12) not null,
rellyadress char(50) not null,
）
商品类别信息表的建立：
create table product(
p_id char(10) primary key not null,
p_name char(20) not null,
p_price char(10)not null,
p_introduce char (50) not null,
p_team int not null,
p_Tprice char(10) not null,
)
管理员类别信息表的建立：
create table manage(
ma_id int primary key not null,
ma_name char(20) not null,
ma_password char(20) not null,
）
购买类别信息表的建立（订单）：
create table buy(
userid int not null,
p_id char(10) not null,
username char(20) not null,
)
2、建立视图：
用于查询用户基本信息的视图定义如下：
create view userview
as
select userid，username，userpassword
from users
用于查询商品基本信息的视图定义如下：
create view productview
as
select p_id,p_name,p_price,p_introduce,p_Tprice,p_team
from product
生活用品：
create view 生活用品
as
select p_id,p_name,p_introduce
from 团购网站数据库.dbo.product
where p_introduce='生活用品'
装饰品：
create view 装饰品
as
select p_id,p_name,p_introduce
from 团购网站数据库.dbo.product
where p_introduce='装饰品'
电子产品
create view 电子产品 as
select p_id,p_name,p_introduce
from 团购网站数据库.dbo.product
where p_introduce='电子产品'
化妆品
create view 化妆品
as
select p_id,p_name,p_introduce
from 团购网站数据库.dbo.product
where p_introduce='化妆品'
餐饮
create view 餐饮 as
select p_id,p_name,p_introduce
from 团购网站数据库.dbo.product
where p_introduce='餐饮'
学习用具
create view 学习工具
as
select p_id,p_name,p_introduce
from 团购网站数据库.dbo.product
where p_introduce='学习工具'
学习用品
create view 学习用品
as
select p_id,p_name,p_introduce
from 团购网站数据库.dbo.product
where p_introduce='学习用品'
用于查询订单基本信息的视图定义如下
use 团购网站数据库
go
create view 订单
as
select users.username,rellyname,rellyadress,rellytell,
buy.p_id,p_name,product.p_Tprice,p_introduce
from users join buy on users.userid=buy.userid
join product on buy.p_id=product.p_id
用以查询某件商品被多少人所购买了
use 团购网站数据库
create view 限制
as
select buy.p_id,p_team,COUNT(userid)购买人数
from buy
group by p_id,p_team
3、建立触发器
1． 当删除users表中某一用户基本信息时，触发buy表，删除相应的记录
create trigger users_delete
on users
for delete
as
delete from buy
where buy.userid=(select userid from deleted)
delete from users where userid='25'
2. 当删除product表中某一用户基本信息时，触发buy表，删除相应的记录
create trigger product_delete
on product
for delete
as
delete from buy
where buy.p_id=(select p_id from deleted)
3. 修改触发器product_delete，实现如下功能，如果商品的价格大于或等于300 元，那么不能被删除
alter trigger product_delete on product
for delete
as
begin
set nocount on
if(select p_price from deleted)>=200
print'商品价格超过300元，不能删除！'
rollback transaction
End
delete from product where p_id='1'
4、当商品表中的商品编号改变，buy表中的也随之改变
create trigger product_update
on product
for update
as
update buy set buy.p_id=(select p_id from inserted)
where buy.p_id=(select p_id from deleted)
update product set p_id='34' where p_id='1'
5、当用户表中的商品编号改变，buy表中的也随之改变
create trigger users_update
on users
for update
as
update buy set buy.userid=(select userid from inserted)
where buy.userid=(select userid from deleted)
update users set userid='25' where userid='26'
6、当团购人数不符合规定，则不能插入到buy表中
create trigger buy_insert
on buy
for insert
as
if(select count(数量) from buy where p_id =(select p_id from inserted))!=(select p_team from product where p_id=(select p_id from inserted))
begin
print'不符合规定，请重新购买！'
rollback transaction
End
例句：insert into buy (userid,p_id,p_Tprice,p_team,数量)
values('21','6','10','3','1')
4、存储过程
（1）输入一个用户编号，查询有关此用户的一些系统信息
create procedure all_information
@userid char(30)=null
as
if @userid is null
begin
print'请输入一个用户名：'
return
end
else if (not exists(select * from [users ] where users.userid=@userid))
print '用户名不存在,请重新输入！'
else
select distinct users.userid,users.username,users.rellyname,订单.p_name
from users,订单
where users.userid=@userid and 订单.rellyname=[users ].rellyname
group by users.userid,users.username,users.rellyname,订单.p_name
运行的例句：exec all_information @userid='11'
（2）输入一个商品名，查询有关此商品的一些基本的销售信息
create procedure p_name_information
@p_name char(20)=null
as
if @p_name is null
begin
print'请输入一个商品名：'
return
end
else if (not exists(select * from product where p_name=@p_name))
print '商品不存在,请重新输入！'
else
select distinct users.userid,users.username,users.rellyname,订单.p_name,订单.p_introduce
from users,订单,product
where product.p_name=@p_name and 订单.rellyname=[users ].rellyname and 订单.p_name=product.p_name and 订单.p_introduce=product.p_introduce
group by users.userid,users.username,users.rellyname,订单.p_name,订单.p_introduce
运行的例句：exec p_name_information @p_name='耳机'
（3）用户增加
CREATE PROCEDURE users_Insert
@userid char(30),
@username char(20),
@userpassword char(20) ,
@rellyname char(20),
@rellytell char(12) ,
@rellyadress char(50)
as
insert into users
values(@userid,@username ,@userpassword ,@rellyname ,@rellytell ,@rellyadress );
exec users_Insert '26','qqq','qqq','aaa','24134','5654444'
（4）商品增加
CREATE PROCEDURE product_Insert
@p_id char(10) ,
@p_name char(20),
@p_price char(10),
@p_introduce char (50) ,
@p_team int ,
@p_Tprice char(10) ,
as
insert into product
values(@p_id,@p_name,@p_price ,@p_introduce ,@p_team ,@p_Tprice);
exec product_Insert 'q','q','q','q','4','q'
5、索引
create clustered index i_product on product(p_name)
