游戏点卡，ssh框架，毕业项目

drop database gamecard; create database gamecard; --修改 userinfo, templativecard use gamecard; --可扩展性，设置用户是否被屏蔽的状态 create table userstate( id int not null primary key, userstateid varchar(50), userstatename varchar(50) ) type=innoDB; --存放用户基本信息 create table userinfo( id Integer primary key AUTO_INCREMENT, userid varchar (50) not null, username varchar (20) not null, password varchar (15) not null, userrole int not null, gender varchar(50) not null, passquestion varchar (50) not null, passanswer varchar (50) not null, email varchar (50) not null, telno bigint not null, address varchar (50), idcardno bigint not null, money bigint, userstate int not null ) type=innoDB; --存放角色基本信息 create table roleinfo( roleid Integer not null primary key AUTO_INCREMENT, rolename varchar (50) not null, roledesc varchar (50) not null, discount Integer ) type=innoDB; --存放菜单功能基本信息 create table sysfun( noteid Integer not null primary key AUTO_INCREMENT, displayname varchar(50) not null, nodeurl varchar (50) not null, displayorder Integer not null, parentnodeid Integer not null ) type=innoDB; --存放所有角色权限 create table roleright( rolerightid Integer not null primary key AUTO_INCREMENT, roleid Integer not null, nodeid Integer not null ) type=innoDB; --卡片类型信息表 create table cardtype( cardtypeid Integer not null primary key AUTO_INCREMENT, cardtypename nvarchar (50) not null, cardprice Integer not null, cardimage nvarchar (50) ) type=innoDB; --可扩展，卡片是否被售出的状态 create table cardstate( id Integer not null primary key AUTO_INCREMENT, cardstateid Integer not null, cardstatename varchar(50) not null ) type=innoDB; --具体卡片表 create table card( cardid Integer not null primary key AUTO_INCREMENT, cardtypeid Integer not null, cardno bigint not null, cardpassword varchar(50) not null, carddesc varchar(50) , cardtime datetime not null, cardstate Integer, cardcount Integer ) type=innoDB; --购物车功能，存放用户已经选择的卡片信息 create table shoppingcart ( shoppingcartitemid Integer not null primary key AUTO_INCREMENT, userid Integer not null, cardtypeid int not null, num Integer not null ) type=innoDB; --用户购物历史记录 create table shoppinghistory( ShopHistoryId Integer not null primary key AUTO_INCREMENT, userid Integer not null, cardid Integer not null, shoptime datetime not null ) type=innoDB; --根据用户选择的卡片，给出相关的选择 create table temprelativecard( id Integer primary key AUTO_INCREMENT, temprelativecardid varchar(50) not null , userid Integer not null, cardtypeid Integer not null ) type=innoDB; --可扩展，用户汇款被审核状态 create table approvestate ( approvestateid Integer not null primary key AUTO_INCREMENT, approvestatename varchar(50) not null ) type=innoDB; --用户汇款历史记录 create table posthistory( posthistory Integer not null primary key AUTO_INCREMENT, userid Integer not null, bank varchar(50) not null, money Integer null, posttime datetime not null, postdesc varchar(50), approvestate Integer not null ) type=innoDB; --存放汇款失败时，系统发送的信息 create table postfailedinfo( postfaildinfold Integer not null primary key AUTO_INCREMENT, userid Integer not null, posthistoryid Integer not null, readstate Integer not null ) type=innoDB; --存放系统公告信息 create table news ( newsid Integer not null primary key AUTO_INCREMENT, title varchar(50) not null, content varchar(500), newstime datetime not null, newsstate Integer not null ) type=innoDB; --存放会员的投诉建议信息 create table advice ( adviceid Integer not null primary key AUTO_INCREMENT, userid Integer not null, content varchar(20000) not null ) type=innoDB; alter table userinfo add constraint fk_userinfo_userstate foreign key (userstate) references userstate(id); alter table userinfo add constraint fk_userinfo_roleinfo foreign key (userrole) references roleinfo(roleid); alter table roleright add constraint fk_roleright_nodeid foreign key (nodeid) references sysfun(noteid ); alter table roleright add constraint fk_roleright_roleid foreign key (roleid) references roleinfo(roleid ); alter table card add constraint fk_card_cardtypeid foreign key (cardtypeid) references cardtype(cardtypeid ); alter table card add constraint fk_card_cardstate foreign key (cardstate) references cardstate(id ); alter table shoppingcart add constraint fk_shoppingcart_userid foreign key (userid) references userinfo(id ); alter table shoppingcart add constraint fk_shoppingcart_cardtypeid foreign key (cardtypeid) references cardtype (cardtypeid ); alter table temprelativecard add constraint fk_temprelativecard_userid foreign key (userid) references userinfo(id ); alter table temprelativecard add constraint fk_temprelativecard_cardtypeid foreign key (cardtypeid) references cardtype (cardtypeid ); alter table shoppinghistory add constraint fk_shoppinghistory_cardid foreign key (cardid) references cardtype (cardtypeid ); alter table shoppinghistory add constraint fk_shoppinghistory_userid foreign key (userid) references userinfo(id ); alter table posthistory add constraint fk_posthistory_userid foreign key (userid) references userinfo(id ); alter table posthistory add constraint fk_posthistory_approvestate foreign key (approvestate) references approvestate(approvestateid ); alter table postfailedinfo add constraint fk_postfailedinfo_userid foreign key (userid) references userinfo(id ); alter table postfailedinfo add constraint fk_postfailedinfo_posthistoryid foreign key (posthistoryid) references posthistory(posthistory ); alter table advice add constraint fk_advice_userid foreign key (userid) references userinfo(id ); --userstate 可扩展性，设置用户是否被屏蔽的状态 insert into userstate values ( 1,1,'正常'); insert into userstate values ( 2,0,'屏蔽'); --RoleInfo 存放角色基本信息 insert into roleinfo values ( 1,'超级管理员','拥有最高权限',0); insert into roleinfo values ( 2,'VIP会员','享受更多优惠',85); insert into roleinfo values ( 3,'普通会员','普通会员',95); insert into roleinfo values ( 4,'待审核人员','待审核人员',100); --userinfo 存放用户基本信息 -- 超级用户 insert into userinfo values (1,'admin','超级用户','admin',1,'男','待定', '待定','group4@163.com',8888888,'长沙',456789123456,1234564,1 ); --vip会员 insert into userinfo values (2,'sulianzhi','经理','123456',2,'女','待定', '待定','sulianzhi@163.com',8888888,'长沙',456789123456,1234564,1); --普通用户 insert into userinfo values (3,'java1','软1','123456',3,'男','生日', '1987-10-12','java1@163.com',8888888,'长沙',456789123456,1234564,1); -- 普通用户 insert into userinfo values (4,'pengrunfei','software','123456',3,'男','baby', 'baby','pengrunfei@163.com',8888888,'长沙',4232423423,1234564,1); --屏蔽用户 insert into userinfo values (5,'liulang','流浪','123456',4,'男','待定', '待定','liulang@163.com',8888888,'岳阳',234234324324,1234564,2); --sysfun 存放菜单功能基本信息 insert into sysfun values ( 1,'个人管理','youself.jsp',1,2); insert into sysfun values ( 2,'系统管理','system.jsp',2,6); insert into sysfun values ( 3,'综合信息','colligation.jsp',3,2); insert into sysfun values ( 4,'修改资料','update.jsp',4,1); insert into sysfun values ( 5,'用户管理','usermanager.jsp',5,1); insert into sysfun values ( 6,'角色管理','rolemanager.jsp',6,1); insert into sysfun values ( 7,'分类管理','classmanager.jsp',7,1); insert into sysfun values ( 8,'游戏卡管理','gamemanager.jsp',8,1); insert into sysfun values ( 9,'汇款审核','remit.jsp',9,1); insert into sysfun values ( 10,'公告管理','newsmanager.jsp',10,1); insert into sysfun values ( 11,'销售统计','sellsate.jsp',11,1); insert into sysfun values ( 12,'浏览投诉','scan.jsp',12,1); insert into sysfun values ( 13,'商品列