My graduation project's Mysql script
mysql 脚本：
sql脚本
drop database if exists lampssell;
drop table if exists userinfo;
id int(11) primary key auto_increment,
username varchar(20) not null, /*用户名*/
password varchar(20) not null, /*密码*/
sex enum('男','女','保密'), /*性别*/
isBetter int(11) /*是否为高用户，1是 0不是*/
) engine=innodb default charset=utf8 row_format=dynamic;
#alter table userinfo add column phone varchar(15) after qq;
#alter table userinfo add column zip varchar(8) after phone;
id int(11) primary key auto_increment,
rolename varchar(20) not null, /*角色名*/
) engine=innodb default charset=utf8 row_format=dynamic;
id int(11) primary key auto_increment,
actionname varchar(20) not null, /*权限名称*/
path varchar(200), /*相对于主目录的路径*/
) engine=innodb default charset=utf8 row_format=dynamic;
id int(11) primary key auto_increment,
modulename varchar(765) not null, /*模块名称*/
) engine=innodb default charset=utf8 row_format=dynamic;
#alter table action drop foreign key FK_ACTION_MODULE;
###alter table action add constraint FK_ACTION_MODULE foreign key(moudleid) references module(id);
id int(11) primary key auto_increment,
menuname varchar(150) not null, /*菜单名*/
parentid int(11) not null, /*父菜单id*/
imageurl varchar(200), /*图片地址*/
) engine=innodb default charset=utf8 row_format=dynamic;
#alter table menu drop foreign key FK_MENU_PARENTID;
###alter table menu add constraint FK_MENU_PARENTID foreign key(parentid) references menu(id);
drop table if exists userrole;
userid int(11),### references user(id), /*用户id*/
roleid int(11),### references role(id), /*角色id*/
) engine=innodb default charset=utf8 row_format=dynamic;
#alter table userrole drop foreign key FK_USERROLE_USERID;
#alter table userrole drop foreign key FK_USERROLE_ROLEID;
#alter table userrole drop primary key PK_USERROLE;
#alter table userrole add constraint FK_USERROLE_USERID foreign key(userid) references user(id);
#alter table userrole add constraint FK_USERROLE_ROLEID foreign key(roleid) references role(id);
#alter table userrole add constraint PK_USERROLE primary key(userid,roleid);
drop table if exists roleaction;
roleid int(11),### references role(id), /*角色id*/
actionid int(11),### references action(id), /*权限id*/
) engine=innodb default charset=utf8 row_format=dynamic;
#alter table roleaction add constraint FK_ROLEACTION_ROLEID foreign key(roleid) references role(id);
drop table if exists rolemenu;
roleid int(11),### references role(id), /*角色id*/
menuid int(11),### references menu(id), /*菜单id*/
) engine=innodb default charset=utf8 row_format=dynamic;
#alter table rolemenu add constraint FK_ROLEMENU_ROLEID foreign key(roleid) references role(id);
#alter table rolemenu add constraint FK_ROLEMENU_MENUID foreign key(menuid) references menu(id);
drop table if exists category;
id int(11) primary key auto_increment,
parent_id int(11) not null, #references category(id), /*列级添加外键约束*/
status enum('上线','下线') default '上线', /*状态*/
product_count int(11) default '0', /*产品数量*/
/*foreign key (parent_id) references category(id)*/ /*表级添加外键约束*/
) engine=innodb default charset=utf8 row_format=dynamic;
#alter table category drop foreign key FK_CATEGORY_ID;
#alter table category add column status enum('上线','下线') default '上线' after name;
#alter table category add column orders int(11) not null default '2'
#update category set orders=id;
#alter table category modify column orders int(11) default '0';
#alter table category add column product_count int(11) default '0' after status;
id int(11) primary key auto_increment,
name varchar(50) not null, /*名称*/
category_id int(11) not null, /*行业类别id*/
status enum('上线','下线','缺货') default '上线', /*状态：0上线 1下线 2缺货*/
sales_valume int(11) default '0', /*销量*/
produce_area varchar(100), /*产地*/
small_img varchar(100), /*小图片*/
detail varchar(2048), /*详细描述*/
createtime datetime, /*生产日期:timestamp default CURRENT_TIMESTAMP*/
price double default '0.0', /*产品价格*/
num int(11) default '0', /*数量*/
min_num int(11) default '1', /*最小数量*/
/*constraint PRODUCT_CATEGORY foreign key(category_id) references category(id)*/
) engine=innodb default charset=utf8 row_format=dynamic;
#alter table product drop foreign key FK_PRODUCT_CATEGORY;
#alter table product add constraint FK_PRODUCT_STOCK foreign key(id) references stock(id);
#alter table product add column orders int not null default '2';
#alter table product drop column sort_num;
#update product set orders=id;
#alter table product add column sales_valume int(11) default '0' after produce_area;
#update product set sales_valume=1000;
#alter table product modify column orders int(11) default '0';
#alter table product modify column detail text;
drop table if exists complany;
id int(11) primary key auto_increment,
name varchar(50) not null, /*企业名称*/
short_name varchar(10), /*简称*/
type enum('有限责任','个人独资','股份有限','外商投资','国有企业','合伙企业','外国企业') default '有限责任', /*企业性质*/
logo_img varchar(100), /*企业logo*/
business_mode enum('生产/制造','贸易','服务','政府机构','组织团体','其它') default '生产/制造',/*经营模式*/
primary_business varchar(200), /*主营业务*/
address varchar(100), /*企业地址*/
business_location varchar(100), /*贸易地区*/
boss_name varchar(20), /*法人名称*/
detail varchar(2048), /*简介描述*/
) engine=innodb default charset=utf8 row_format=dynamic;
#alter table complany add column order int not null default '2';
#update complany set orders=id;
#alter table complany modify column orders int(11) default '0';
drop table if exists complanyproduct;
complany_id int(11),### references complany(id),
product_id int(11),### references product(id),
primary key(complany_id,product_id)
) engine=innodb default charset=utf8 row_format=dynamic;
#alter table complanyproduct drop foreign key FK_COMPLANYPRODUCT_COMPLANY;
#alter table complanyproduct drop foreign key FK_COMPLANYPRODUCT_PRODUCT;
drop table if exists tradInfo;
id int(11) primary key auto_increment,
name varchar(20) not null, /*信息标题*/
product_name varchar(50), /*产品id*/
product_img varchar(100), /*产品图片*/
info_type enum('求购','供应','代理','招商','合作','招标'), /*信息类型*/
status enum('上线','下线') default '上线', /*上线状态*/
orders int(11) default '0' /*排序号*/
) engine=innodb default charset=utf8 row_format=dynamic;
#alter table tradInfo drop foreign key FK_TRADINFO_COMPLANY;
#alter table tradInfo add column product_id int(11) after complany_id;
#alter table tradInfo add column orders int not null default '0';
#update tradInfo set orders=id;
#alter table tradInfo change column title name varchar(20) not null;
#alter table tradInfo modify column orders int(11) default '0';
#alter table tradInfo modify column detail text;
#delete from tradinfo where id <> '1' and id <> '2';
#alter table tradinfo modify column info_type enum('求购','供应','代理','招商','合作','招标');
id int(11) primary key auto_increment,
type enum('产品','企业') default '产品', /*留言的类型*/
status enum('上线','下线') default '上线', /*可见状态*/
) engine=innodb default charset=utf8 row_format=dynamic;
#alter table message drop foreign key FK_MESSAGE_USER;
#alter table message drop foreign key FK_MESSAGE_PRODUCT;
#alter table message drop foreign key FK_MESSAGE_COMPLANY;
###alter table message add constraint FK_MESSAGE_USER foreign key(user_id) references userinfo(id);
#alter table message add column orders int(11) default '0';
###alter table message drop column date;
#update message set orders=id;
#alter table message modify detail text;
#alter table message modify orders int(11) default '0';
#insert into message(type,date,detail,user_id,product_id,complany_id,createtime,status,orders)
# select type,date,detail,user_id,product_id,complany_id,createtime,status,orders from message;
id int(11) primary key auto_increment,
type enum('企业新闻','行业新闻','企业招聘','展会信息'), /*新闻类型*/
status enum('上线','下线') default '上线', /*可见状态*/
) engine=innodb default charset=utf8 row_format=dynamic;
#alter table news drop foreign key FK_NEWS_USER;
#alter table news drop foreign key FK_NEWS_COMPLANY;
###alter table news add constraint FK_NEWS_USER foreign key(user_id) references userinfo(id);
#alter table news add column order int(11) not null default '2';
#alter table news modify column news int(11) default '0';
#alter table news add column news text after type;
#insert into news(title,type,news,user_id,complany_id,status,createtime,orders)
# select title,type,news,user_id,complany_id,status,createtime,orders from news;
#alter table news change column news detail text;
id int(11) primary key auto_increment,
status enum('上线','下线') default '上线', /*状态*/
) engine=innodb default charset=utf8 row_format=dynamic;
#alter table ads add column order int(11) default '2';
#alter table ads modify column orders int(11) default '0';
id int(11) primary key auto_increment,
name varchar(20) not null, /*姓名*/
) engine=innodb default charset=utf8 row_format=dynamic;
#alter table contact drop foreign key FK_CONTACT_COMPLANY;
#alter table contact add column order int(11) default '2';
#update contact set orders=id;
#alter table contact modify column orders int(11) default '0';
id int(11) primary key auto_increment,
consignee varchar(20), /*用户名*/
shippingaddr varchar(200), /*收货地址*/
deliverymode varchar(20), /*配送方式*/
pay_mode enum('银行支付','第三方支付','货到付款') default '银行支付', /*付款方式*/
pay_status enum('未付款','已付款') default '未付款', /*付款状态*/
sub_price double default '0.0', /*总价*/
orders_date timestamp default current_timestamp, /*订单日期*/
product_num int(11) default '0', /*产品数量*/
orders int(11) default '0' /*排序号*/
) engine=innodb default charset=utf8 row_format=dynamic;
#alter table orders drop foreign key FK_ORDERS_USER;
###alter table orders add constraint FK_ORDERS_USER foreign key(user_id) references userinfo(id);
#alter table orders add column order int(11) not null default '2';
#alter table orders modify column orders int(11) default '0';
#alter table orders add column prodcut_num int(11) default '0';
#alter table orders change column sub_price total_Price double ;
#alter table orders add column number varchar(20);
#alter table orders change column pay_satus pay_status enum('未付款','已付款') default '未付款';
#alter table orders add column consignee varchar(20);
#alter table orders add column phone varchar(15);
#alter table orders add column zip varchar(8);
#alter table orders add column shippingaddr varchar(200);
#alter table orders add column deliverymode varchar(20);
drop table if exists orderrow;
id int(11) primary key auto_increment,
) engine=innodb default charset=utf8 row_format=dynamic;
#alter table orderrow add column vip_price double after product_num;
#alter table orderrow add column sub_price double after vip_price;
drop table if exists ordersproduct;
orders_id int(11),### references orders(id), /*订单id*/
product_id int(11),### references product(id), /*产品id*/
primary key(orders_id,product_id)
) engine=innodb default charset=utf8 row_format=dynamic;
#alter table ordersproduct drop foreign key FK_ORDERSPRODUCT_ORDER;
#alter table ordersproduct drop foreign key FK_ORDERSPRODUCT_PRODUCT;
drop table if exists userproduct;
user_id int(11),### references user(id), /*用户id*/
product_id int(11),### references product(id), /*产品id*/
primary key(user_id,product_id)
) engine=innodb default charset=utf8 row_format=dynamic;
#alter table userproduct drop foreign key FK_USERPRODUCT_USER;
#alter table userproduct drop foreign key FK_USERPRODUCT_PRODUCT;
