建表建库模板

建表建库模板

CREATE DATABASE IF NOT EXISTS shop
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;

USE shop;

CREATE TABLE IF NOT EXISTS cart(
ca_id INT NOT NULL COMMENT '购物车id',
PRIMARY KEY(ca_id)
)COMMENT = '购物车表' ENGINE=INNODB CHARACTER SET utf8mb4;


CREATE TABLE IF NOT EXISTS produce(
prd_id INT NOT NULL COMMENT '商品id',
prd_name VARCHAR(50) COMMENT '商品名',
prd_class ENUM('食品','服装','电子产品','图书','家电','装饰品','其他') DEFAULT '其他' NOT NULL COMMENT '商品类别',
prd_price DECIMAL(10,2) COMMENT '商品价格',
prd_stockQty INT COMMENT '商品存量',
PRIMARY KEY(prd_id)
)COMMENT = '商品表' ENGINE=INNODB CHARACTER SET utf8mb4;


CREATE TABLE IF NOT EXISTS sys_user(
us_id INT NOT NULL COMMENT '用户id',
us_name VARCHAR(20) NOT NULL COMMENT '用户名',
us_password VARCHAR(20) NOT NULL COMMENT '用户密码',
us_sex ENUM('男','女') NOT NULL DEFAULT '男' COMMENT '用户性别',
us_birthday DATE DEFAULT NULL COMMENT '用户出生日期',
us_email VARCHAR(30) DEFAULT NULL COMMENT '用户邮箱',
us_mobile VARCHAR(11) DEFAULT NULL COMMENT '用户电话号码',
us_address VARCHAR(200) DEFAULT NULL COMMENT '用户地址',
us_identity ENUM('0','1') NOT NULL DEFAULT '0' COMMENT '用户身份',
ca_id INT NOT NULL COMMENT '用户购物车id',
PRIMARY KEY(us_id),
CONSTRAINT FOREIGN KEY(ca_id) REFERENCES cart(ca_id)
)COMMENT = '用户表' ENGINE=INNODB CHARACTER SET utf8mb4;


CREATE TABLE IF NOT EXISTS cart_produce(
ca_id INT NOT NULL COMMENT '购物车id',
prd_id INT NOT NULL COMMENT '商品id',
PRIMARY KEY(ca_id, prd_id),
CONSTRAINT FOREIGN KEY(ca_id) REFERENCES cart(ca_id),
CONSTRAINT FOREIGN KEY(prd_id) REFERENCES produce(prd_id)
)COMMENT = '购物车_商品表' ENGINE=INNODB CHARACTER SET utf8mb4;


CREATE TABLE IF NOT EXISTS _order(
od_id VARCHAR(20) NOT NULL COMMENT '订单编号',
od_count INT COMMENT '商品数量',
od_date DATETIME COMMENT '订单日期',
prd_id INT NOT NULL,
us_id INT NOT NULL,
PRIMARY KEY(od_id),
CONSTRAINT FOREIGN KEY(prd_id) REFERENCES produce(prd_id),
CONSTRAINT FOREIGN KEY(us_id) REFERENCES sys_user(us_id)
)COMMENT = '订单表' ENGINE=INNODB CHARACTER SET utf8mb4;


CREATE TABLE IF NOT EXISTS _comment(
cmt_id INT NOT NULL AUTO_INCREMENT COMMENT '评论id',
cmt_date DATE COMMENT '评论发表日期',
cmt_detail VARCHAR(100) COMMENT '评论内容',
us_id INT NOT NULL,
prd_id INT NOT NULL,
PRIMARY KEY(cmt_id),
CONSTRAINT FOREIGN KEY(us_id) REFERENCES sys_user(us_id),
CONSTRAINT FOREIGN KEY(prd_id) REFERENCES produce(prd_id)
)COMMENT = '评论表' ENGINE=INNODB CHARACTER SET utf8mb4;