数据库课设&JavaEE大作业.zip

create table users( --会员表 u_id varchar(30) primary key, --用户账号 u_pwd varchar(30) not null, --密码 u_acount money not null, --账户余额 u_address varchar(30) not null, --收货地址 u_phone char(11) not null --电话 ) alter table users add constraint ck1 check(len(u_pwd)>=6) alter table users add constraint ck2 check(len(u_phone)=11 and u_phone like '1%') create table users_detail( --个人信息 u_card varchar(30) primary key, --身份证号 u_name varchar(30) not null, --姓名 u_sex varchar(2) not null, --性别 u_age int not null, --年龄 u_birthday date, --生日 u_email varchar(30) not null, --电子邮箱 u_id varchar(30) not null, --用户账号 constraint fk1 foreign key(u_id) references users(u_id) on update cascade, constraint fk foreign key(u_id) references users(u_id) on delete cascade, check(u_sex in ('男','女')),check(u_age>=0), check(u_email like '%@%') ) create table complain( --投诉表 com_num int primary key, --投诉编号 u_id varchar(30) not null, --用户账号 com_sell varchar(30) not null, --投诉商家 com_date date default(getdate()), --投诉日期 com_status int not null, --处理状态 constraint fk2 foreign key(u_id) references users(u_id) on update cascade, foreign key(u_id) references users(u_id) on delete cascade ) create table adminstrator( --管理员 adm_id varchar(30) primary key, --账号 adm_pwd varchar(30) not null, --密码 adm_name varchar(30) not null --姓名 ) create table users_point( --会员积分表 u_id varchar(30) primary key, --账号 u_point int not null --积分数 foreign key (u_id) references users(u_id) on update cascade, foreign key (u_id) references users(u_id) on delete cascade ) create table present( --兑品表 p_id varchar(30) primary key, --兑品编码 p_name varchar(30) not null, --兑品名字 p_point int not null, --所需积分 p_num int not null --库存量 ) create table act( --参与兑换活动表 u_id varchar(30) not null, --用户账号 p_id varchar(30) not null, --兑品编码 a_num int not null, --兑换数量 primary key(u_id,p_id), foreign key(u_id) references users_point(u_id) on update cascade, foreign key(u_id) references users_point(u_id) on delete cascade, foreign key(p_id) references present(p_id) on update cascade, foreign key(p_id) references present(p_id) on delete cascade ) create table store( --商家 s_id varchar(30) primary key, --商家编号 s_sname varchar(30) not null, --店名 s_pname varchar(30) not null, --店主名 s_grade int , --店铺等级 s_rate float --好评率 ) alter table store add constraint UN2 unique(s_sname) create table category( --商品类型 c_id varchar(30) primary key, --类型编号 c_name varchar(30) not null --类型名 ) alter table category add constraint UN1 unique(c_name) create table goods( --商品 goo_no varchar(30) primary key, --商品编号 goo_name varchar(30) not null, --商品名 goo_place varchar(30) not null, --发货地 goo_price money not null, --单价 goo_num int not null, --数量 photo varchar(150), --图片地址 s_id varchar(30) not null foreign key(s_id) references store(s_id) on update cascade, foreign key(s_id) references store(s_id) on delete cascade, c_id varchar(30) not null foreign key(c_id) references category(c_id) on update cascade, foreign key(c_id) references category(c_id) on delete cascade ) alter table goods add constraint UN unique(goo_name) create table car( --购物车 u_id varchar(30) not null, --用户账号 goo_no varchar(30) not null,--商品编号 car_num int not null, --数量 car_price money, --总金额 primary key(u_id,goo_no), foreign key (u_id) references users(u_id) on update cascade, foreign key (u_id) references users(u_id) on delete cascade, foreign key (goo_no) references goods(goo_no) on update cascade, foreign key (goo_no) references goods(goo_no) on delete cascade ) create table booking( --订单 b_id int primary key, --订单编号 b_price money not null, --订单总价 b_date date default(getdate()),--下单日期 u_id varchar(30) --用户账号 ) create table booking_detail( --订单详细 goo_no varchar(30) not null, --商品编号 goo_name varchar(30) not null, --商品名称 place varchar(30) not null, --发货地点 det_num int not null, --数量 b_id int not null primary key , --订单号 foreign key(b_id) references booking(b_id) on update cascade, foreign key(b_id) references booking(b_id) on delete cascade ) create table appraise( --订单评价 b_id int primary key, --订单编号 content varchar(150) null, --评价内容 a_date date default(getdate()), --评价日期 foreign key(b_id) references booking(b_id) on update cascade, foreign key(b_id) references booking(b_id) on delete cascade ) --测试数据 insert into users values('zc','123456',50,'福建厦门','13400650621') insert into users_point values('zc',100) insert into present values('abc','王老吉',50,5) --存储过程： --1、更新用户积分表 create proc update_userspoint @u_id varchar(30),@point int as begin update users_point set u_point=u_point-@point where u_id=@u_id end --2、更新兑品表 create proc update_present @p_id varchar(30),@num int as begin update present set p_num=p_num-@num where p_id=@p_id end --3、购物车触发更新商品表(用户添加购物车,减少商品) create proc goo_decrease @goo_no varchar(30),@num int as begin update goods set goo_num=goo_num-@num where goo_no=@goo_no end --4、购物车触发更新商品表(用户取消购物车,增加商品) create proc goo_increase @goo_no varchar(30),@num int as begin update goods set goo_num=goo_num+@num where goo_no=@goo_no end --5、购物车生成订单 create proc to_booking @u_id varchar(30),@goo_no varchar(30),@num int,@price money,@output int output as begin declare @account money declare @goo_name varchar(30) declare @place varchar(30) select @goo_name=goo_name from goods where goo_no=@goo_no select @place=goo_place from goods where goo_no=@goo_no select @account=u_acount from users where u_id=@u_id if(@account<@price) begin print'余额不足' set @output=0 end else begin delete from car where u_id=@u_id and goo_no=@goo_no --清空购物车的一条记录 exec goo_decrease @goo_no,@num --下单后触发器使商品又增加了，因此要减去 exec insert_booking @price,@u_id,@goo_no,@num,@goo_name,@place --插入订单 exec update_account @u_id,@price --更新用户余额 exec pro_insertpoint @u_id,@price --更新用户积分 set @output=1 end end drop proc to_booking --6、插入订单 create proc insert_booking @price money,@u_id varchar(30),@goo_no varchar(30),@num int,@goo_name varchar(30),@place varchar(30) as begin declare @id int,@flag int set @flag=0 --0表示未找到合适的订单号 while(@flag=0) begin set @id=cast(rand()*1000000 as int) if(@id not in(select b_id from booking)) set @flag=1 --若生成的订单在原订单表中不存在则符合 end insert into booking values(@id,@price,getdate(),@u_id) --插入的同时触发器将信息插入销售表 exec insert_detail @id,@goo_no,@num,@goo_name,@place --插入订单详细 end drop proc insert_booking --7、插入订单详细 create proc insert_detail @b_id int,@goo_no varchar(30),@num int,@goo_name varchar(30),@place varchar(30) as begin insert into booking_detail values(@goo_no,@goo_name,@place,@num,@b_id) end drop proc insert_detail --8、用户账号余额更新 create proc update_account @u_id varchar(30),@price money as begin update users set u_acount=u_acount-@price where u_id=@u_id end drop proc update_account --9、作出评价 create proc insert_appraise @b_id int,@content varchar(150) as begin insert into appraise values(@b_id,@content,getdate()) end --10、插入投诉表 create proc do_complain @u_id varchar(30),@name varchar(30),@output int output as begin declare @id int,@flag int set @flag=0 set @output=0 if(exists(select * from complain where u_id=@u_id and @name=com_sell)) return 0 else begin while(@flag=0) begin set @id=