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

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,)

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')

5、索引create clustered index i_product on product(p_name)