商城 mysql语句_商城sql语句.sql

叁歲伎倆 2022-11-01 10:48 240阅读 0赞

/\*

Navicat MySQL Data Transfer

Source Server : mysql

Source Server Version : 50516

Source Host : localhost:3306

Source Database : studay

Target Server Type : MYSQL

Target Server Version : 50516

File Encoding : 65001

Date: 2016-08-10 18:48:54

\*/

SET FOREIGN\_KEY\_CHECKS=0;

\-- ----------------------------

\-- Table structure for \`admininfo\`

\-- ----------------------------

DROP TABLE IF EXISTS \`admininfo\`;

CREATE TABLE \`admininfo\` (

\`AdminID\` varchar(20) NOT NULL,

\`Password\` varchar(20) NOT NULL,

\`AdminName\` varchar(20) NOT NULL,

\`IdentityID\` varchar(19) NOT NULL,

\`Email\` varchar(50) NOT NULL,

\`ProductEdit\` enum('1','0') DEFAULT '0',

\`UserEdit\` enum('1','0') DEFAULT '0',

\`OrderEdit\` enum('1','0') DEFAULT '0',

\`AddAdmin\` enum('1','0') DEFAULT '0',

PRIMARY KEY (\`AdminID\`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

\-- ----------------------------

\-- Records of admininfo

\-- ----------------------------

\-- Table structure for \`category\`

\-- ----------------------------

DROP TABLE IF EXISTS \`category\`;

CREATE TABLE \`category\` (

\`category\_id\` int(11) NOT NULL,

\`fathercategoryid\` int(11) NOT NULL,

\`name\` varchar(128) NOT NULL,

\`description\` varchar(512) DEFAULT NULL,

PRIMARY KEY (\`category\_id\`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW\_FORMAT=COMPACT;

\-- ----------------------------

\-- Records of category

\-- ----------------------------

INSERT INTO \`category\` VALUES ('1', '0', '小说', '顶级分类');

INSERT INTO \`category\` VALUES ('2', '0', '文学', '顶级分类');

INSERT INTO \`category\` VALUES ('3', '0', '商业', '顶级分类');

INSERT INTO \`category\` VALUES ('4', '0', '艺术', '顶级分类');

INSERT INTO \`category\` VALUES ('5', '1', '当代小说', '当代小说');

INSERT INTO \`category\` VALUES ('6', '1', '当代小说', '当代小说');

\-- ----------------------------

\-- Table structure for \`orderline\`

\-- ----------------------------

DROP TABLE IF EXISTS \`orderline\`;

CREATE TABLE \`orderline\` (

\`orderline\_id\` int(11) NOT NULL AUTO\_INCREMENT,

\`orders\_id\` int(11) NOT NULL,

\`product\_id\` int(11) NOT NULL,

\`amount\` decimal(20,2) NOT NULL,

PRIMARY KEY (\`orderline\_id\`),

KEY \`orderline\_orders\_id\_fk\` (\`orders\_id\`),

KEY \`orderline\_product\_id\_fk\` (\`product\_id\`),

CONSTRAINT \`orderline\_product\_id\_fk\` FOREIGN KEY (\`product\_id\`) REFERENCES \`product\` (\`product \_id\`) ON DELETE NO ACTION ON UPDATE CASCADE,

CONSTRAINT \`orderline\_orders\_id\_fk\` FOREIGN KEY (\`orders\_id\`) REFERENCES \`orders\` (\`orders\_id\`) ON DELETE NO ACTION ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

\-- ----------------------------

\-- Records of orderline

\-- ----------------------------

\-- Table structure for \`orders\`

\-- ----------------------------

DROP TABLE IF EXISTS \`orders\`;

CREATE TABLE \`orders\` (

\`orders\_id\` int(11) NOT NULL AUTO\_INCREMENT,

\`username\` varchar(20) NOT NULL,

\`payway\_id\` int(11) DEFAULT NULL,

\`orderstatus\_id\` int(11) DEFAULT NULL,

\`name\` varchar(20) DEFAULT NULL,

\`cost\` decimal(15,3) NOT NULL,

PRIMARY KEY (\`orders\_id\`),

KEY \`orders\_orderstatus\_fk\` (\`orderstatus\_id\`),

KEY \`orders\_payway\_fk\` (\`payway\_id\`),

KEY \`orders\_username\_fk\` (\`username\`),

CONSTRAINT \`orders\_username\_fk\` FOREIGN KEY (\`username\`) REFERENCES \`users\` (\`username\`) ON DELETE CASCADE ON UPDATE CASCADE,

CONSTRAINT \`orders\_orderstatus\_fk\` FOREIGN KEY (\`orderstatus\_id\`) REFERENCES \`orderstatus\` (\`orderstatus\_id\`) ON DELETE CASCADE ON UPDATE CASCADE,

CONSTRAINT \`orders\_payway\_fk\` FOREIGN KEY (\`payway\_id\`) REFERENCES \`payway\` (\`payway\_id\`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

\-- ----------------------------

\-- Records of orders

\-- ----------------------------

\-- Table structure for \`orderstatus\`

\-- ----------------------------

DROP TABLE IF EXISTS \`orderstatus\`;

CREATE TABLE \`orderstatus\` (

\`orderstatus\_id\` int(11) NOT NULL,

\`name\` varchar(20) NOT NULL,

\`description\` varchar(64) DEFAULT NULL,

PRIMARY KEY (\`orderstatus\_id\`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

\-- ----------------------------

\-- Records of orderstatus

\-- ----------------------------

\-- Table structure for \`payway\`

\-- ----------------------------

DROP TABLE IF EXISTS \`payway\`;

CREATE TABLE \`payway\` (

\`payway\_id\` int(11) NOT NULL,

\`paystyle\` varchar(64) NOT NULL,

PRIMARY KEY (\`payway\_id\`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

\-- ----------------------------

\-- Records of payway

\-- ----------------------------

\-- Table structure for \`product\`

\-- ----------------------------

DROP TABLE IF EXISTS \`product\`;

CREATE TABLE \`product\` (

\`product \_id\` int(11) NOT NULL AUTO\_INCREMENT,

\`name\` varchar(40) NOT NULL,

\`basePrice\` decimal(12,2) NOT NULL,

\`category\_id\` int(11) NOT NULL,

\`author\` varchar(128) NOT NULL,

\`publish\` varchar(256) NOT NULL,

\`stock\` int(11) NOT NULL,

\`images\` varchar(128) NOT NULL,

\`intro\` varchar(512) DEFAULT NULL,

\`url\` varchar(100) NOT NULL,

PRIMARY KEY (\`product \_id\`),

KEY \`product\_category\_id\_fk\` (\`category\_id\`),

CONSTRAINT \`product\_category\_id\_fk\` FOREIGN KEY (\`category\_id\`) REFERENCES \`category\` (\`category\_id\`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB AUTO\_INCREMENT=2 DEFAULT CHARSET=utf8;

\-- ----------------------------

\-- Records of product

\-- ----------------------------

INSERT INTO \`product\` VALUES ('1', '杜拉拉 2 华年似水(《杜拉拉升职记》第二部：超越职场的华年似水)', '28.50', '5', '李', '陕西师范大学出版', '283', 'images/product/zb\_20232215\_m.jpg', '希望这本虚构的小说，能够对人们的生活有一些超越职场规则的现', '');

\-- ----------------------------

\-- Table structure for \`users\`

\-- ----------------------------

DROP TABLE IF EXISTS \`users\`;

CREATE TABLE \`users\` (

\`username\` varchar(20) NOT NULL,

\`password\` varchar(12) NOT NULL,

\`email\` varchar(32) NOT NULL,

\`telephone\` varchar(16) NOT NULL,

\`mobtelephone\` varchar(16) DEFAULT NULL,

\`province\` varchar(32) DEFAULT NULL,

\`city\` varchar(32) DEFAULT NULL,