145 lines
5.0 KiB
SQL
145 lines
5.0 KiB
SQL
CREATE DATABASE IF NOT EXISTS maternal_mall DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
USE maternal_mall;
|
||
|
||
CREATE TABLE IF NOT EXISTS users (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
username VARCHAR(50) UNIQUE NOT NULL,
|
||
password VARCHAR(100) NOT NULL,
|
||
role VARCHAR(20) NOT NULL,
|
||
nickname VARCHAR(50),
|
||
phone VARCHAR(20),
|
||
address VARCHAR(255),
|
||
token VARCHAR(100),
|
||
enabled BIT NOT NULL DEFAULT b'1',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS product (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
name VARCHAR(120) NOT NULL,
|
||
category VARCHAR(50),
|
||
description VARCHAR(1000),
|
||
price DECIMAL(10,2) NOT NULL,
|
||
stock INT NOT NULL,
|
||
image_url VARCHAR(255),
|
||
merchant_id BIGINT NOT NULL,
|
||
approved BIT NOT NULL DEFAULT b'0',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS cart_item (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
customer_id BIGINT NOT NULL,
|
||
product_id BIGINT NOT NULL,
|
||
quantity INT NOT NULL,
|
||
UNIQUE KEY uk_customer_product(customer_id, product_id)
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS favorite (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
customer_id BIGINT NOT NULL,
|
||
product_id BIGINT NOT NULL,
|
||
UNIQUE KEY uk_f_customer_product(customer_id, product_id)
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS orders (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
order_no VARCHAR(50) UNIQUE NOT NULL,
|
||
customer_id BIGINT NOT NULL,
|
||
merchant_id BIGINT NOT NULL,
|
||
total_amount DECIMAL(10,2) NOT NULL,
|
||
status VARCHAR(30) NOT NULL,
|
||
address VARCHAR(255),
|
||
logistics_info VARCHAR(255),
|
||
refund_reason VARCHAR(255),
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS order_item (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
order_id BIGINT NOT NULL,
|
||
product_id BIGINT NOT NULL,
|
||
product_name VARCHAR(120) NOT NULL,
|
||
quantity INT NOT NULL,
|
||
unit_price DECIMAL(10,2) NOT NULL
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS review (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
order_id BIGINT NOT NULL,
|
||
product_id BIGINT NOT NULL,
|
||
customer_id BIGINT NOT NULL,
|
||
rating INT NOT NULL,
|
||
content VARCHAR(1000),
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS logistics_record (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
order_id BIGINT NOT NULL,
|
||
merchant_id BIGINT NOT NULL,
|
||
status VARCHAR(255) NOT NULL,
|
||
note VARCHAR(255),
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS inventory_record (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
product_id BIGINT NOT NULL,
|
||
merchant_id BIGINT NOT NULL,
|
||
change_qty INT NOT NULL,
|
||
note VARCHAR(255),
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS merchant_application (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
user_id BIGINT NOT NULL,
|
||
qualification VARCHAR(255),
|
||
status VARCHAR(30) NOT NULL,
|
||
remark VARCHAR(255),
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS banner (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
image_url VARCHAR(255) NOT NULL,
|
||
link_url VARCHAR(255),
|
||
sort_no INT NOT NULL DEFAULT 0,
|
||
enabled BIT NOT NULL DEFAULT b'1'
|
||
);
|
||
|
||
INSERT INTO users(username,password,role,nickname,enabled)
|
||
SELECT 'admin','123456','ADMIN','平台管理员',b'1' FROM dual
|
||
WHERE NOT EXISTS (SELECT 1 FROM users WHERE username='admin');
|
||
|
||
INSERT INTO users(username,password,role,nickname,enabled)
|
||
SELECT 'merchant1','123456','MERCHANT','示例商家',b'1' FROM dual
|
||
WHERE NOT EXISTS (SELECT 1 FROM users WHERE username='merchant1');
|
||
|
||
INSERT INTO users(username,password,role,nickname,enabled)
|
||
SELECT 'customer1','123456','CUSTOMER','示例顾客',b'1' FROM dual
|
||
WHERE NOT EXISTS (SELECT 1 FROM users WHERE username='customer1');
|
||
|
||
INSERT INTO product(name,category,description,price,stock,image_url,merchant_id,approved)
|
||
SELECT '婴儿纸尿裤L码','尿裤湿巾','高吸收防漏，适合10-14kg','89.00',200,'https://picsum.photos/400/260?diaper',
|
||
(SELECT id FROM users WHERE username='merchant1' LIMIT 1), b'1'
|
||
FROM dual WHERE NOT EXISTS (SELECT 1 FROM product WHERE name='婴儿纸尿裤L码');
|
||
|
||
INSERT INTO product(name,category,description,price,stock,image_url,merchant_id,approved)
|
||
SELECT '孕妇营养奶粉','奶粉辅食','富含叶酸和DHA','168.00',80,'https://picsum.photos/400/260?milk',
|
||
(SELECT id FROM users WHERE username='merchant1' LIMIT 1), b'1'
|
||
FROM dual WHERE NOT EXISTS (SELECT 1 FROM product WHERE name='孕妇营养奶粉');
|
||
|
||
INSERT INTO banner(image_url,link_url,sort_no,enabled)
|
||
SELECT 'https://picsum.photos/1200/220?maternity1','#',1,b'1' FROM dual
|
||
WHERE NOT EXISTS (SELECT 1 FROM banner WHERE sort_no=1);
|
||
|
||
INSERT INTO banner(image_url,link_url,sort_no,enabled)
|
||
SELECT 'https://picsum.photos/1200/220?maternity2','#',2,b'1' FROM dual
|
||
WHERE NOT EXISTS (SELECT 1 FROM banner WHERE sort_no=2);
|