mysql.sql

java学习的必要的资料,servlet的说明很好

# MySQL-Front 3.2  (Build 6.2)

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET CHARACTER SET 'latin1' */;

# Host: localhost    Database: wanbei
# ------------------------------------------------------
# Server version 5.0.15-nt

--DROP DATABASE IF EXISTS `wanbei`;
--CREATE DATABASE `wanbei` /*!40100 DEFAULT CHARACTER SET gbk */;
--USE `wanbei`;

drop table e_order_item;
drop table e_order;

drop table e_user_item;
drop table e_user;

drop table e_product;
drop table e_catalog;


#
# Table structure for table e_catalog
#

CREATE TABLE `e_catalog` (
  `catalogid` int(11) NOT NULL auto_increment,
  `catalogname` varchar(100) NOT NULL,
  `description` varchar(100) default NULL,
  PRIMARY KEY  (`catalogid`)
) DEFAULT CHARACTER SET utf8;

#
# Dumping data for table e_catalog
#

INSERT INTO `e_catalog` (`catalogid`,`catalogname`,`description`) VALUES (1,'c','c++');
INSERT INTO `e_catalog` (`catalogid`,`catalogname`,`description`) VALUES (2,'language','language');
INSERT INTO `e_catalog` (`catalogid`,`catalogname`,`description`) VALUES (3,'new','update');

#
# Table structure for table e_product
#
--`description` text default NULL,
CREATE TABLE `e_product` (
  `productid` int(10) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `isbn` varchar(100) default NULL,
  `printer` varchar(50) default NULL,
  `author` varchar(50) default NULL,
  `price` double default NULL,
  `booknum` int(4) default NULL,
  `catalogid` int(10) NOT NULL,
  `description` varchar(1000) default NULL,
  `imagepath` varchar(100) default NULL,
  `createtime` date default NULL,
  `state` int(2) default '0',
  PRIMARY KEY  (`productid`),
  KEY `catalogid` (`catalogid`)
) DEFAULT CHARACTER SET utf8;

#
# Dumping data for table e_product
#
INSERT INTO `e_product` (`productid`,`name`,`isbn`,`printer`,`author`,`price`,`booknum`,
                         `catalogid`,`description`,`imagepath`,`createtime`,`state`) 
VALUES (1,'Hibernate：Java','20080510001','electilc','sunweiqing',59.0,
'600',1,'sunweiqing ','','2008-05-13',0 );

#
# Table structure for table e_user
#

CREATE TABLE `e_user` (
  `userid` int(11) NOT NULL auto_increment,
  `username` varchar(30) NOT NULL,
  `password` varchar(15) default NULL,
  `createtime` date default NULL,
  `state` int(11) default '0',
  `role` int(11) default '0',
  PRIMARY KEY  (`userid`)
) DEFAULT CHARACTER SET utf8;

#
# Dumping data for table e_user
#
insert into e_user values(1,'tarena','tarena','2008-05-13',1,1);
insert into e_user values(2,'zhangsw','zhangsw','2008-05-13',1,1);

#
# Table structure for table e_user_item
#

CREATE TABLE `e_user_item` (
  `user_item_id` int(11) NOT NULL auto_increment,
  `item_name` varchar(30) default NULL,
  `item_address` varchar(100) default NULL,
  `item_postcode` varchar(10) default NULL,
  `item_officephone` varchar(50) default NULL,
  `item_mobilephone` varchar(20) default NULL,
  `item_email` varchar(50) default NULL,
  `userid` int(12) NOT NULL,
  PRIMARY KEY  (`user_item_id`),
  KEY `userid` (`userid`)
) DEFAULT CHARACTER SET utf8;

#
# Dumping data for table e_user_item
#

#
# Table structure for table e_order
#

CREATE TABLE `e_order` (
  `orderid` int(11) NOT NULL auto_increment,
  `totalprice` double default '0',
  `orderstate` int(11) default '0',
  `payinfo` int(11) default NULL,
  `createtime` date default NULL,
  `userid` int(11) NOT NULL,
  `user_item_id` int(11) NOT NULL,
  PRIMARY KEY  (`orderid`),
  KEY `user_item_id` (`user_item_id`),
  KEY `userid` (`userid`)
) DEFAULT CHARACTER SET utf8;

#
# Dumping data for table e_order
#


#
# Table structure for table e_order_item
#

CREATE TABLE `e_order_item` (
  `order_item_id` int(11) NOT NULL auto_increment,
  `productid` int(11) NOT NULL,
  `orderid` int(11) NOT NULL,
  `quality` int(11) NOT NULL,
  PRIMARY KEY  (`order_item_id`),
  KEY `productid` (`productid`),
  KEY `orderid` (`orderid`)
) DEFAULT CHARACTER SET utf8;

#
# Dumping data for table e_order_item
#

#
#  Foreign keys for table e_order


ALTER TABLE `e_order`
ADD FOREIGN KEY (`user_item_id`) REFERENCES `e_user_item` (`user_item_id`),
  ADD FOREIGN KEY (`userid`) REFERENCES `e_user` (`userid`);


#
#  Foreign keys for table e_order_item
#

ALTER TABLE `e_order_item`
ADD FOREIGN KEY (`productid`) REFERENCES `e_product` (`productid`),
  ADD FOREIGN KEY (`orderid`) REFERENCES `e_order` (`orderid`);


#
#  Foreign keys for table e_product
#

ALTER TABLE `e_product`
ADD FOREIGN KEY (`catalogid`) REFERENCES `e_catalog` (`catalogid`);


#
#  Foreign keys for table e_user_item
#

ALTER TABLE `e_user_item`
ADD FOREIGN KEY (`userid`) REFERENCES `e_user` (`userid`);

drop table e_admin;
create table e_admin(
    admin_id              integer auto_increment,				
    admin_name            varchar(32),                  	
    password              varchar(32),                  
    tel                 varchar(32),                   
    email                 varchar(32),                   
    primary key(admin_id)                        
)DEFAULT CHARACTER SET utf8;;

drop table e_module;
create table e_module(
    module_id          integer auto_increment, 
    module_name        varchar(32),
    primary key(module_id)               
)DEFAULT CHARACTER SET utf8;;

create table e_adm_rights(
    adm_id      integer references e_admin(admin_id),       
    mod_id      integer references e_module(module_id),   
    primary key(adm_id, mod_id)
);


insert into e_module values(1,'管理员管理');
insert into e_module values(2,'订单管理');
insert into e_module values(3,'产品管理');
insert into e_module values(4,'用户管理');
insert into e_module values(5,'留言管理');

insert into e_admin values(1,'cyb','1234','15902151742','[e-mail]');

insert into e_adm_rights values(1,1);

create table e_message(
    messageId      integer auto_increment,        
    tile           varchar(32),   
    content	   varchar(32),  
    primary key(messageId)
);
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;