团购网站的数据库设计（代码）

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 keynot 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 productviewasselect p_id,p_name,p_price,p_introduce,p_Tprice,p_teamfrom product生活用品：create view 生活用品asselect p_id,p_name,p_introducefrom 团购网站数据库.dbo.productwhere p_introduce='生活用品'装饰品：create view 装饰品asselect p_id,p_name,p_introducefrom 团购网站数据库.dbo.productwhere p_introduce='装饰品'电子产品create view 电子产品 asselect p_id,p_name,p_introducefrom 团购网站数据库.dbo.productwhere p_introduce='电子产品'化妆品create view 化妆品asselect p_id,p_name,p_introducefrom 团购网站数据库.dbo.productwhere p_introduce='化妆品'餐饮create view 餐饮 asselect p_id,p_name,p_introducefrom 团购网站数据库.dbo.productwhere p_introduce='餐饮'学习用具create view 学习工具asselect p_id,p_name,p_introducefrom 团购网站数据库.dbo.productwhere p_introduce='学习工具'学习用品create view 学习用品asselect p_id,p_name,p_introducefrom 团购网站数据库.dbo.productwhere p_introduce='学习用品'用于查询订单基本信息的视图定义如下 use 团购网站数据库gocreate view 订单asselect users.username,rellyname,rellyadress,rellytell,buy.p_id,p_name,product.p_Tprice,p_introducefrom users join buy on users.userid=buy.useridjoin product on buy.p_id=product.p_id用以查询某件商品被多少人所购买了use 团购网站数据库create view 限制as select buy.p_id,p_team,COUNT(userid)购买人数from buygroup by p_id,p_team

3、建立触发器1．当删除users表中某一用户基本信息时，殚朽羰钛触发buy表，删除相应的记录create trigger users_deleteon usersfor deleteasdelete from buywhere buy.userid=(select userid from deleted)delete from users where userid='25'2. 当删除product表中某一用户基本信息时，触发buy表，删除相应的记录create trigger product_deleteon productfor deleteasdelete from buywhere buy.p_id=(select p_id from deleted)3.修改触发器product_delete，实现如下功能，如果商品的价格大于或等于300 元，那么不能被删除alter trigger product_delete on productfor deleteasbeginset nocount onif(select p_price from deleted)>=200print'商品价格超过300元，不能删除！'rollback transactionEnddelete from product where p_id='1'4、当商品表中的商品编号改变，buy表中的也随之改变create trigger product_updateon productfor updateasupdate 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_updateon usersfor updateasupdate 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_inserton buyfor insertasif(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))beginprint'不符合规定，请重新购买！'rollback transactionEnd例句：insert into buy (userid,p_id,p_Tprice,p_team,数量)values('21','6','10','3','1')

4、存储过程（1）输入一个用户编号，查询有关此用户的一些系统信息create procedure al造婷用痃l_information@userid char(30)=nullasif @userid is nullbeginprint'请输入一个用户名：'returnendelse if (not exists(select * from [users ] where users.userid=@userid))print '用户名不存在,请重新输入！'elseselect distinct users.userid,users.username,users.rellyname,订单.p_namefrom users,订单where users.userid=@userid and 订单.rellyname=[users ].rellynamegroup by users.userid,users.username,users.rellyname,订单.p_name运行的例句：exec all_information @userid='11'（2）输入一个商品名，查询有关此商品的一些基本的销售信息create procedure p_name_information@p_name char(20)=nullasif @p_name is nullbeginprint'请输入一个商品名：'returnendelse if (not exists(select * from product where p_name=@p_name))print '商品不存在,请重新输入！'elseselect distinct users.userid,users.username,users.rellyname,订单.p_name,订单.p_introducefrom users,订单,productwhere product.p_name=@p_name and 订单.rellyname=[users ].rellyname and 订单.p_name=product.p_name and 订单.p_introduce=product.p_introducegroup 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)asinsert into usersvalues(@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) ,asinsert into productvalues(@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)