bookstore.sql

采用struts+hibernate技术实现的网上书店系统

/*!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: bookstore
# ------------------------------------------------------
# Server version 5.0.19-nt

DROP DATABASE IF EXISTS `bookstore`;
CREATE DATABASE `bookstore` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `bookstore`;

#
# Table structure for table bookinfo
#

CREATE TABLE `bookinfo` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `bookName` varchar(128) NOT NULL default '',
  `isbn` varchar(64) NOT NULL default '',
  `writer` varchar(45) NOT NULL,
  `publisher` varchar(64) NOT NULL default '',
  `intro` text,
  `price` decimal(5,2) NOT NULL default '0.00',
  `remaining` smallint(5) unsigned zerofill NOT NULL default '00000',
  `picture` varchar(32) default NULL,
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

#
# Dumping data for table bookinfo
#

INSERT INTO `bookinfo` (`id`,`bookName`,`isbn`,`writer`,`publisher`,`intro`,`price`,`remaining`,`picture`,`date`) VALUES (4,'thinking in java','12312','wer','fdg','dfg',21,20,'1145979122906.gif','2008-01-01 00:00:00');
INSERT INTO `bookinfo` (`id`,`bookName`,`isbn`,`writer`,`publisher`,`intro`,`price`,`remaining`,`picture`,`date`) VALUES (2,'sdf','1111','sadf','sdf','sdfasdf',5,100,NULL,'2008-04-01 00:00:00');
INSERT INTO `bookinfo` (`id`,`bookName`,`isbn`,`writer`,`publisher`,`intro`,`price`,`remaining`,`picture`,`date`) VALUES (3,'dfgsdfgsdf','2323','sdfrg','dsfg','dfgsdfgsdfg',33,11,NULL,'2008-01-01 00:00:00');

#
# Table structure for table order
#

CREATE TABLE `order` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `bookid` int(10) unsigned NOT NULL default '0',
  `userid` int(10) unsigned NOT NULL default '0',
  `number` tinyint(3) unsigned NOT NULL default '0',
  `address` varchar(128) NOT NULL default '',
  `postcode` varchar(6) NOT NULL default '',
  `orderdate` datetime NOT NULL default '0000-00-00 00:00:00',
  `status` tinyint(3) unsigned zerofill NOT NULL default '000',
  PRIMARY KEY  (`id`),
  KEY `FK_order_1` (`bookid`),
  KEY `FK_order_2` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Dumping data for table order
#


#
# Table structure for table rebate
#

CREATE TABLE `rebate` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `level` decimal(10,2) unsigned zerofill NOT NULL default '00000000.00',
  `rebateRate` decimal(2,1) unsigned zerofill NOT NULL default '0.0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Dumping data for table rebate
#


#
# Table structure for table remark
#

CREATE TABLE `remark` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `bookid` int(10) unsigned NOT NULL default '0',
  `userid` int(10) unsigned NOT NULL default '0',
  `grade` tinyint(3) unsigned NOT NULL default '0',
  `remark` text NOT NULL,
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`),
  KEY `FK_remark_1` (`bookid`),
  KEY `FK_remark_2` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Dumping data for table remark
#


#
# Table structure for table userinfo
#

CREATE TABLE `userinfo` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `username` varchar(15) NOT NULL,
  `pwd` varchar(32) NOT NULL,
  `email` varchar(32) NOT NULL,
  `address` varchar(100) default NULL,
  `postcode` varchar(6) default NULL,
  `level` decimal(10,2) unsigned zerofill default '00000000.00',
  `power` tinyint(3) unsigned zerofill NOT NULL default '000',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

#
# Dumping data for table userinfo
#

INSERT INTO `userinfo` (`id`,`username`,`pwd`,`email`,`address`,`postcode`,`level`,`power`) VALUES (1,'user','123','[e-mail]','sdf','d',0,0);
INSERT INTO `userinfo` (`id`,`username`,`pwd`,`email`,`address`,`postcode`,`level`,`power`) VALUES (1,'admin','123','[e-mail]','sdf','d',0,1);

#
#  Foreign keys for table order
#

ALTER TABLE `order`
ADD FOREIGN KEY (`bookid`) REFERENCES `bookinfo` (`id`),
  ADD FOREIGN KEY (`userid`) REFERENCES `userinfo` (`id`);


#
#  Foreign keys for table remark
#

ALTER TABLE `remark`
ADD FOREIGN KEY (`bookid`) REFERENCES `bookinfo` (`id`),
  ADD FOREIGN KEY (`userid`) REFERENCES `userinfo` (`id`);


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