5.1sql語(yǔ)句
gulimall-oms.sql
drop table if exists oms_order;
drop table if exists oms_order_item;
drop table if exists oms_order_operate_history;
drop table if exists oms_order_return_apply;
drop table if exists oms_order_return_reason;
drop table if exists oms_order_setting;
drop table if exists oms_payment_info;
drop table if exists oms_refund_info;
/*==============================================================*/
/* Table: oms_order */
/*==============================================================*/
create table oms_order
(
id bigint not null auto_increment comment 'id',
member_id bigint comment 'member_id',
order_sn char(32) comment '訂單號(hào)',
coupon_id bigint comment '使用的優(yōu)惠券',
create_time datetime comment 'create_time',
member_username varchar(200) comment '用戶名',
total_amount decimal(18,4) comment '訂單總額',
pay_amount decimal(18,4) comment '應(yīng)付總額',
freight_amount decimal(18,4) comment '運(yùn)費(fèi)金額',
promotion_amount decimal(18,4) comment '促銷優(yōu)化金額（促銷價(jià)、滿減、階梯價(jià)）',
integration_amount decimal(18,4) comment '積分抵扣金額',
coupon_amount decimal(18,4) comment '優(yōu)惠券抵扣金額',
discount_amount decimal(18,4) comment '后臺(tái)調(diào)整訂單使用的折扣金額',
pay_type tinyint comment '支付方式【1->支付寶；2->微信；3->銀聯(lián)； 4->貨到付款；】',
source_type tinyint comment '訂單來(lái)源[0->PC訂單；1->app訂單]',
status tinyint comment '訂單狀態(tài)【0->待付款；1->待發(fā)貨；2->已發(fā)貨；3->已完成；4->已關(guān)閉；5->無(wú)效訂單】',
delivery_company varchar(64) comment '物流公司(配送方式)',
delivery_sn varchar(64) comment '物流單號(hào)',
auto_confirm_day int comment '自動(dòng)確認(rèn)時(shí)間（天）',
integration int comment '可以獲得的積分',
growth int comment '可以獲得的成長(zhǎng)值',
bill_type tinyint comment '發(fā)票類型[0->不開(kāi)發(fā)票；1->電子發(fā)票；2->紙質(zhì)發(fā)票]',
bill_header varchar(255) comment '發(fā)票抬頭',
bill_content varchar(255) comment '發(fā)票內(nèi)容',
bill_receiver_phone varchar(32) comment '收票人電話',
bill_receiver_email varchar(64) comment '收票人郵箱',
receiver_name varchar(100) comment '收貨人姓名',
receiver_phone varchar(32) comment '收貨人電話',
receiver_post_code varchar(32) comment '收貨人郵編',
receiver_province varchar(32) comment '省份/直轄市',
receiver_city varchar(32) comment '城市',
receiver_region varchar(32) comment '區(qū)',
receiver_detail_address varchar(200) comment '詳細(xì)地址',
note varchar(500) comment '訂單備注',
confirm_status tinyint comment '確認(rèn)收貨狀態(tài)[0->未確認(rèn)；1->已確認(rèn)]',
delete_status tinyint comment '刪除狀態(tài)【0->未刪除；1->已刪除】',
use_integration int comment '下單時(shí)使用的積分',
payment_time datetime comment '支付時(shí)間',
delivery_time datetime comment '發(fā)貨時(shí)間',
receive_time datetime comment '確認(rèn)收貨時(shí)間',
comment_time datetime comment '評(píng)價(jià)時(shí)間',
modify_time datetime comment '修改時(shí)間',
primary key (id)
);
alter table oms_order comment '訂單';
/*==============================================================*/
/* Table: oms_order_item */
/*==============================================================*/
create table oms_order_item
(
id bigint not null auto_increment comment 'id',
order_id bigint comment 'order_id',
order_sn char(32) comment 'order_sn',
spu_id bigint comment 'spu_id',
spu_name varchar(255) comment 'spu_name',
spu_pic varchar(500) comment 'spu_pic',
spu_brand varchar(200) comment '品牌',
category_id bigint comment '商品分類id',
sku_id bigint comment '商品sku編號(hào)',
sku_name varchar(255) comment '商品sku名字',
sku_pic varchar(500) comment '商品sku圖片',
sku_price decimal(18,4) comment '商品sku價(jià)格',
sku_quantity int comment '商品購(gòu)買(mǎi)的數(shù)量',
sku_attrs_vals varchar(500) comment '商品銷售屬性組合（JSON）',
promotion_amount decimal(18,4) comment '商品促銷分解金額',
coupon_amount decimal(18,4) comment '優(yōu)惠券優(yōu)惠分解金額',
integration_amount decimal(18,4) comment '積分優(yōu)惠分解金額',
real_amount decimal(18,4) comment '該商品經(jīng)過(guò)優(yōu)惠后的分解金額',
gift_integration int comment '贈(zèng)送積分',
gift_growth int comment '贈(zèng)送成長(zhǎng)值',
primary key (id)
);
alter table oms_order_item comment '訂單項(xiàng)信息';
/*==============================================================*/
/* Table: oms_order_operate_history */
/*==============================================================*/
create table oms_order_operate_history
(
id bigint not null auto_increment comment 'id',
order_id bigint comment '訂單id',
operate_man varchar(100) comment '操作人[用戶；系統(tǒng)；后臺(tái)管理員]',
create_time datetime comment '操作時(shí)間',
order_status tinyint comment '訂單狀態(tài)【0->待付款；1->待發(fā)貨；2->已發(fā)貨；3->已完成；4->已關(guān)閉；5->無(wú)效訂單】',
note varchar(500) comment '備注',
primary key (id)
);
alter table oms_order_operate_history comment '訂單操作歷史記錄';
/*==============================================================*/
/* Table: oms_order_return_apply */
/*==============================================================*/
create table oms_order_return_apply
(
id bigint not null auto_increment comment 'id',
order_id bigint comment 'order_id',
sku_id bigint comment '退貨商品id',
order_sn char(32) comment '訂單編號(hào)',
create_time datetime comment '申請(qǐng)時(shí)間',
member_username varchar(64) comment '會(huì)員用戶名',
return_amount decimal(18,4) comment '退款金額',
return_name varchar(100) comment '退貨人姓名',
return_phone varchar(20) comment '退貨人電話',
status tinyint(1) comment '申請(qǐng)狀態(tài)[0->待處理；1->退貨中；2->已完成；3->已拒絕]',
handle_time datetime comment '處理時(shí)間',
sku_img varchar(500) comment '商品圖片',
sku_name varchar(200) comment '商品名稱',
sku_brand varchar(200) comment '商品品牌',
sku_attrs_vals varchar(500) comment '商品銷售屬性(JSON)',
sku_count int comment '退貨數(shù)量',
sku_price decimal(18,4) comment '商品單價(jià)',
sku_real_price decimal(18,4) comment '商品實(shí)際支付單價(jià)',
reason varchar(200) comment '原因',
description述 varchar(500) comment '描述',
desc_pics varchar(2000) comment '憑證圖片，以逗號(hào)隔開(kāi)',
handle_note varchar(500) comment '處理備注',
handle_man varchar(200) comment '處理人員',
receive_man varchar(100) comment '收貨人',
receive_time datetime comment '收貨時(shí)間',
receive_note varchar(500) comment '收貨備注',
receive_phone varchar(20) comment '收貨電話',
company_address varchar(500) comment '公司收貨地址',
primary key (id)
);
alter table oms_order_return_apply comment '訂單退貨申請(qǐng)';
/*==============================================================*/
/* Table: oms_order_return_reason */
/*==============================================================*/
create table oms_order_return_reason
(
id bigint not null auto_increment comment 'id',
name varchar(200) comment '退貨原因名',
sort int comment '排序',
status tinyint(1) comment '啟用狀態(tài)',
create_time datetime comment 'create_time',
primary key (id)
);
alter table oms_order_return_reason comment '退貨原因';
/*==============================================================*/
/* Table: oms_order_setting */
/*==============================================================*/
create table oms_order_setting
(
id bigint not null auto_increment comment 'id',
flash_order_overtime int comment '秒殺訂單超時(shí)關(guān)閉時(shí)間(分)',
normal_order_overtime int comment '正常訂單超時(shí)時(shí)間(分)',
confirm_overtime int comment '發(fā)貨后自動(dòng)確認(rèn)收貨時(shí)間（天）',
finish_overtime int comment '自動(dòng)完成交易時(shí)間，不能申請(qǐng)退貨（天）',
comment_overtime int comment '訂單完成后自動(dòng)好評(píng)時(shí)間（天）',
member_level tinyint(2) comment '會(huì)員等級(jí)【0-不限會(huì)員等級(jí)，全部通用；其他-對(duì)應(yīng)的其他會(huì)員等級(jí)】',
primary key (id)
);
alter table oms_order_setting comment '訂單配置信息';
/*==============================================================*/
/* Table: oms_payment_info */
/*==============================================================*/
create table oms_payment_info
(
id bigint not null auto_increment comment 'id',
order_sn char(32) comment '訂單號(hào)（對(duì)外業(yè)務(wù)號(hào)）',
order_id bigint comment '訂單id',
alipay_trade_no varchar(50) comment '支付寶交易流水號(hào)',
total_amount decimal(18,4) comment '支付總金額',
subject varchar(200) comment '交易內(nèi)容',
payment_status varchar(20) comment '支付狀態(tài)',
create_time datetime comment '創(chuàng)建時(shí)間',
confirm_time datetime comment '確認(rèn)時(shí)間',
callback_content varchar(4000) comment '回調(diào)內(nèi)容',
callback_time datetime comment '回調(diào)時(shí)間',
primary key (id)
);
alter table oms_payment_info comment '支付信息表';
/*==============================================================*/
/* Table: oms_refund_info */
/*==============================================================*/
create table oms_refund_info
(
id bigint not null auto_increment comment 'id',
order_return_id bigint comment '退款的訂單',
refund decimal(18,4) comment '退款金額',
refund_sn varchar(64) comment '退款交易流水號(hào)',
refund_status tinyint(1) comment '退款狀態(tài)',
refund_channel tinyint comment '退款渠道[1-支付寶，2-微信，3-銀聯(lián)，4-匯款]',
refund_content varchar(5000),
primary key (id)
);
alter table oms_refund_info comment '退款信息';
gulimall-pms.sql
drop table if exists pms_attr;
drop table if exists pms_attr_attrgroup_relation;
drop table if exists pms_attr_group;
drop table if exists pms_brand;
drop table if exists pms_category;
drop table if exists pms_category_brand_relation;
drop table if exists pms_comment_replay;
drop table if exists pms_product_attr_value;
drop table if exists pms_sku_images;
drop table if exists pms_sku_info;
drop table if exists pms_sku_sale_attr_value;
drop table if exists pms_spu_comment;
drop table if exists pms_spu_images;
drop table if exists pms_spu_info;
drop table if exists pms_spu_info_desc;
/*==============================================================*/
/* Table: pms_attr */
/*==============================================================*/
create table pms_attr
(
attr_id bigint not null auto_increment comment '屬性id',
attr_name char(30) comment '屬性名',
search_type tinyint comment '是否需要檢索[0-不需要，1-需要]',
icon varchar(255) comment '屬性圖標(biāo)',
value_select char(255) comment '可選值列表[用逗號(hào)分隔]',
attr_type tinyint comment '屬性類型[0-銷售屬性，1-基本屬性，2-既是銷售屬性又是基本屬性]',
enable bigint comment '啟用狀態(tài)[0 - 禁用，1 - 啟用]',
catelog_id bigint comment '所屬分類',
show_desc tinyint comment '快速展示【是否展示在介紹上；0-否 1-是】，在sku中仍然可以調(diào)整',
primary key (attr_id)
);
alter table pms_attr comment '商品屬性';
/*==============================================================*/
/* Table: pms_attr_attrgroup_relation */
/*==============================================================*/
create table pms_attr_attrgroup_relation
(
id bigint not null auto_increment comment 'id',
attr_id bigint comment '屬性id',
attr_group_id bigint comment '屬性分組id',
attr_sort int comment '屬性組內(nèi)排序',
primary key (id)
);
alter table pms_attr_attrgroup_relation comment '屬性&屬性分組關(guān)聯(lián)';
/*==============================================================*/
/* Table: pms_attr_group */
/*==============================================================*/
create table pms_attr_group
(
attr_group_id bigint not null auto_increment comment '分組id',
attr_group_name char(20) comment '組名',
sort int comment '排序',
descript varchar(255) comment '描述',
icon varchar(255) comment '組圖標(biāo)',
catelog_id bigint comment '所屬分類id',
primary key (attr_group_id)
);
alter table pms_attr_group comment '屬性分組';
/*==============================================================*/
/* Table: pms_brand */
/*==============================================================*/
create table pms_brand
(
brand_id bigint not null auto_increment comment '品牌id',
name char(50) comment '品牌名',
logo varchar(2000) comment '品牌logo地址',
descript longtext comment '介紹',
show_status tinyint comment '顯示狀態(tài)[0-不顯示；1-顯示]',
first_letter char(1) comment '檢索首字母',
sort int comment '排序',
primary key (brand_id)
);
alter table pms_brand comment '品牌';
/*==============================================================*/
/* Table: pms_category */
/*==============================================================*/
create table pms_category
(
cat_id bigint not null auto_increment comment '分類id',
name char(50) comment '分類名稱',
parent_cid bigint comment '父分類id',
cat_level int comment '層級(jí)',
show_status tinyint comment '是否顯示[0-不顯示，1顯示]',
sort int comment '排序',
icon char(255) comment '圖標(biāo)地址',
product_unit char(50) comment '計(jì)量單位',
product_count int comment '商品數(shù)量',
primary key (cat_id)
);
alter table pms_category comment '商品三級(jí)分類';
/*==============================================================*/
/* Table: pms_category_brand_relation */
/*==============================================================*/
create table pms_category_brand_relation
(
id bigint not null auto_increment,
brand_id bigint comment '品牌id',
catelog_id bigint comment '分類id',
brand_name varchar(255),
catelog_name varchar(255),
primary key (id)
);
alter table pms_category_brand_relation comment '品牌分類關(guān)聯(lián)';
/*==============================================================*/
/* Table: pms_comment_replay */
/*==============================================================*/
create table pms_comment_replay
(
id bigint not null auto_increment comment 'id',
comment_id bigint comment '評(píng)論id',
reply_id bigint comment '回復(fù)id',
primary key (id)
);
alter table pms_comment_replay comment '商品評(píng)價(jià)回復(fù)關(guān)系';
/*==============================================================*/
/* Table: pms_product_attr_value */
/*==============================================================*/
create table pms_product_attr_value
(
id bigint not null auto_increment comment 'id',
spu_id bigint comment '商品id',
attr_id bigint comment '屬性id',
attr_name varchar(200) comment '屬性名',
attr_value varchar(200) comment '屬性值',
attr_sort int comment '順序',
quick_show tinyint comment '快速展示【是否展示在介紹上；0-否 1-是】',
primary key (id)
);
alter table pms_product_attr_value comment 'spu屬性值';
/*==============================================================*/
/* Table: pms_sku_images */
/*==============================================================*/
create table pms_sku_images
(
id bigint not null auto_increment comment 'id',
sku_id bigint comment 'sku_id',
img_url varchar(255) comment '圖片地址',
img_sort int comment '排序',
default_img int comment '默認(rèn)圖[0 - 不是默認(rèn)圖，1 - 是默認(rèn)圖]',
primary key (id)
);
alter table pms_sku_images comment 'sku圖片';
/*==============================================================*/
/* Table: pms_sku_info */
/*==============================================================*/
create table pms_sku_info
(
sku_id bigint not null auto_increment comment 'skuId',
spu_id bigint comment 'spuId',
sku_name varchar(255) comment 'sku名稱',
sku_desc varchar(2000) comment 'sku介紹描述',
catalog_id bigint comment '所屬分類id',
brand_id bigint comment '品牌id',
sku_default_img varchar(255) comment '默認(rèn)圖片',
sku_title varchar(255) comment '標(biāo)題',
sku_subtitle varchar(2000) comment '副標(biāo)題',
price decimal(18,4) comment '價(jià)格',
sale_count bigint comment '銷量',
primary key (sku_id)
);
alter table pms_sku_info comment 'sku信息';
/*==============================================================*/
/* Table: pms_sku_sale_attr_value */
/*==============================================================*/
create table pms_sku_sale_attr_value
(
id bigint not null auto_increment comment 'id',
sku_id bigint comment 'sku_id',
attr_id bigint comment 'attr_id',
attr_name varchar(200) comment '銷售屬性名',
attr_value varchar(200) comment '銷售屬性值',
attr_sort int comment '順序',
primary key (id)
);
alter table pms_sku_sale_attr_value comment 'sku銷售屬性&值';
/*==============================================================*/
/* Table: pms_spu_comment */
/*==============================================================*/
create table pms_spu_comment
(
id bigint not null auto_increment comment 'id',
sku_id bigint comment 'sku_id',
spu_id bigint comment 'spu_id',
spu_name varchar(255) comment '商品名字',
member_nick_name varchar(255) comment '會(huì)員昵稱',
star tinyint(1) comment '星級(jí)',
member_ip varchar(64) comment '會(huì)員ip',
create_time datetime comment '創(chuàng)建時(shí)間',
show_status tinyint(1) comment '顯示狀態(tài)[0-不顯示，1-顯示]',
spu_attributes varchar(255) comment '購(gòu)買(mǎi)時(shí)屬性組合',
likes_count int comment '點(diǎn)贊數(shù)',
reply_count int comment '回復(fù)數(shù)',
resources varchar(1000) comment '評(píng)論圖片/視頻[json數(shù)據(jù)；[{type:文件類型,url:資源路徑}]]',
content text comment '內(nèi)容',
member_icon varchar(255) comment '用戶頭像',
comment_type tinyint comment '評(píng)論類型[0 - 對(duì)商品的直接評(píng)論，1 - 對(duì)評(píng)論的回復(fù)]',
primary key (id)
);
alter table pms_spu_comment comment '商品評(píng)價(jià)';
/*==============================================================*/
/* Table: pms_spu_images */
/*==============================================================*/
create table pms_spu_images
(
id bigint not null auto_increment comment 'id',
spu_id bigint comment 'spu_id',
img_name varchar(200) comment '圖片名',
img_url varchar(255) comment '圖片地址',
img_sort int comment '順序',
default_img tinyint comment '是否默認(rèn)圖',
primary key (id)
);
alter table pms_spu_images comment 'spu圖片';
/*==============================================================*/
/* Table: pms_spu_info */
/*==============================================================*/
create table pms_spu_info
(
id bigint not null auto_increment comment '商品id',
spu_name varchar(200) comment '商品名稱',
spu_description varchar(1000) comment '商品描述',
catalog_id bigint comment '所屬分類id',
brand_id bigint comment '品牌id',
weight decimal(18,4),
publish_status tinyint comment '上架狀態(tài)[0 - 下架，1 - 上架]',
create_time datetime,
update_time datetime,
primary key (id)
);
alter table pms_spu_info comment 'spu信息';
/*==============================================================*/
/* Table: pms_spu_info_desc */
/*==============================================================*/
create table pms_spu_info_desc
(
spu_id bigint not null comment '商品id',
decript longtext comment '商品介紹',
primary key (spu_id)
);
alter table pms_spu_info_desc comment 'spu信息介紹';
gulimall_sms.sql
drop table if exists sms_coupon;
drop table if exists sms_coupon_history;
drop table if exists sms_coupon_spu_category_relation;
drop table if exists sms_coupon_spu_relation;
drop table if exists sms_home_adv;
drop table if exists sms_home_subject;
drop table if exists sms_home_subject_spu;
drop table if exists sms_member_price;
drop table if exists sms_seckill_promotion;
drop table if exists sms_seckill_session;
drop table if exists sms_seckill_sku_notice;
drop table if exists sms_seckill_sku_relation;
drop table if exists sms_sku_full_reduction;
drop table if exists sms_sku_ladder;
drop table if exists sms_spu_bounds;
/*==============================================================*/
/* Table: sms_coupon */
/*==============================================================*/
create table sms_coupon
(
id bigint not null auto_increment comment 'id',
coupon_type tinyint(1) comment '優(yōu)惠卷類型[0->全場(chǎng)贈(zèng)券；1->會(huì)員贈(zèng)券；2->購(gòu)物贈(zèng)券；3->注冊(cè)贈(zèng)券]',
coupon_img varchar(2000) comment '優(yōu)惠券圖片',
coupon_name varchar(100) comment '優(yōu)惠卷名字',
num int comment '數(shù)量',
amount decimal(18,4) comment '金額',
per_limit int comment '每人限領(lǐng)張數(shù)',
min_point decimal(18,4) comment '使用門(mén)檻',
start_time datetime comment '開(kāi)始時(shí)間',
end_time datetime comment '結(jié)束時(shí)間',
use_type tinyint(1) comment '使用類型[0->全場(chǎng)通用；1->指定分類；2->指定商品]',
note varchar(200) comment '備注',
publish_count int(11) comment '發(fā)行數(shù)量',
use_count int(11) comment '已使用數(shù)量',
receive_count int(11) comment '領(lǐng)取數(shù)量',
enable_start_time datetime comment '可以領(lǐng)取的開(kāi)始日期',
enable_end_time datetime comment '可以領(lǐng)取的結(jié)束日期',
code varchar(64) comment '優(yōu)惠碼',
member_level tinyint(1) comment '可以領(lǐng)取的會(huì)員等級(jí)[0->不限等級(jí)，其他-對(duì)應(yīng)等級(jí)]',
publish tinyint(1) comment '發(fā)布狀態(tài)[0-未發(fā)布，1-已發(fā)布]',
primary key (id)
);
alter table sms_coupon comment '優(yōu)惠券信息';
/*==============================================================*/
/* Table: sms_coupon_history */
/*==============================================================*/
create table sms_coupon_history
(
id bigint not null auto_increment comment 'id',
coupon_id bigint comment '優(yōu)惠券id',
member_id bigint comment '會(huì)員id',
member_nick_name varchar(64) comment '會(huì)員名字',
get_type tinyint(1) comment '獲取方式[0->后臺(tái)贈(zèng)送；1->主動(dòng)領(lǐng)取]',
create_time datetime comment '創(chuàng)建時(shí)間',
use_type tinyint(1) comment '使用狀態(tài)[0->未使用；1->已使用；2->已過(guò)期]',
use_time datetime comment '使用時(shí)間',
order_id bigint comment '訂單id',
order_sn bigint comment '訂單號(hào)',
primary key (id)
);
alter table sms_coupon_history comment '優(yōu)惠券領(lǐng)取歷史記錄';
/*==============================================================*/
/* Table: sms_coupon_spu_category_relation */
/*==============================================================*/
create table sms_coupon_spu_category_relation
(
id bigint not null auto_increment comment 'id',
coupon_id bigint comment '優(yōu)惠券id',
category_id bigint comment '產(chǎn)品分類id',
category_name varchar(64) comment '產(chǎn)品分類名稱',
primary key (id)
);
alter table sms_coupon_spu_category_relation comment '優(yōu)惠券分類關(guān)聯(lián)';
/*==============================================================*/
/* Table: sms_coupon_spu_relation */
/*==============================================================*/
create table sms_coupon_spu_relation
(
id bigint not null auto_increment comment 'id',
coupon_id bigint comment '優(yōu)惠券id',
spu_id bigint comment 'spu_id',
spu_name varchar(255) comment 'spu_name',
primary key (id)
);
alter table sms_coupon_spu_relation comment '優(yōu)惠券與產(chǎn)品關(guān)聯(lián)';
/*==============================================================*/
/* Table: sms_home_adv */
/*==============================================================*/
create table sms_home_adv
(
id bigint not null auto_increment comment 'id',
name varchar(100) comment '名字',
pic varchar(500) comment '圖片地址',
start_time datetime comment '開(kāi)始時(shí)間',
end_time datetime comment '結(jié)束時(shí)間',
status tinyint(1) comment '狀態(tài)',
click_count int comment '點(diǎn)擊數(shù)',
url varchar(500) comment '廣告詳情連接地址',
note varchar(500) comment '備注',
sort int comment '排序',
publisher_id bigint comment '發(fā)布者',
auth_id bigint comment '審核者',
primary key (id)
);
alter table sms_home_adv comment '首頁(yè)輪播廣告';
/*==============================================================*/
/* Table: sms_home_subject */
/*==============================================================*/
create table sms_home_subject
(
id bigint not null auto_increment comment 'id',
name varchar(200) comment '專題名字',
title varchar(255) comment '專題標(biāo)題',
sub_title varchar(255) comment '專題副標(biāo)題',
status tinyint(1) comment '顯示狀態(tài)',
url varchar(500) comment '詳情連接',
sort int comment '排序',
img varchar(500) comment '專題圖片地址',
primary key (id)
);
alter table sms_home_subject comment '首頁(yè)專題表【jd首頁(yè)下面很多專題，每個(gè)專題鏈接新的頁(yè)面，展示專題商品信息】';
/*==============================================================*/
/* Table: sms_home_subject_spu */
/*==============================================================*/
create table sms_home_subject_spu
(
id bigint not null auto_increment comment 'id',
name varchar(200) comment '專題名字',
subject_id bigint comment '專題id',
spu_id bigint comment 'spu_id',
sort int comment '排序',
primary key (id)
);
alter table sms_home_subject_spu comment '專題商品';
/*==============================================================*/
/* Table: sms_member_price */
/*==============================================================*/
create table sms_member_price
(
id bigint not null auto_increment comment 'id',
sku_id bigint comment 'sku_id',
member_level_id bigint comment '會(huì)員等級(jí)id',
member_level_name varchar(100) comment '會(huì)員等級(jí)名',
member_price decimal(18,4) comment '會(huì)員對(duì)應(yīng)價(jià)格',
add_other tinyint(1) comment '可否疊加其他優(yōu)惠[0-不可疊加優(yōu)惠，1-可疊加]',
primary key (id)
);
alter table sms_member_price comment '商品會(huì)員價(jià)格';
/*==============================================================*/
/* Table: sms_seckill_promotion */
/*==============================================================*/
create table sms_seckill_promotion
(
id bigint not null auto_increment comment 'id',
title varchar(255) comment '活動(dòng)標(biāo)題',
start_time datetime comment '開(kāi)始日期',
end_time datetime comment '結(jié)束日期',
status tinyint comment '上下線狀態(tài)',
create_time datetime comment '創(chuàng)建時(shí)間',
user_id bigint comment '創(chuàng)建人',
primary key (id)
);
alter table sms_seckill_promotion comment '秒殺活動(dòng)';
/*==============================================================*/
/* Table: sms_seckill_session */
/*==============================================================*/
create table sms_seckill_session
(
id bigint not null auto_increment comment 'id',
name varchar(200) comment '場(chǎng)次名稱',
start_time datetime comment '每日開(kāi)始時(shí)間',
end_time datetime comment '每日結(jié)束時(shí)間',
status tinyint(1) comment '啟用狀態(tài)',
create_time datetime comment '創(chuàng)建時(shí)間',
primary key (id)
);
alter table sms_seckill_session comment '秒殺活動(dòng)場(chǎng)次';
/*==============================================================*/
/* Table: sms_seckill_sku_notice */
/*==============================================================*/
create table sms_seckill_sku_notice
(
id bigint not null auto_increment comment 'id',
member_id bigint comment 'member_id',
sku_id bigint comment 'sku_id',
session_id bigint comment '活動(dòng)場(chǎng)次id',
subcribe_time datetime comment '訂閱時(shí)間',
send_time datetime comment '發(fā)送時(shí)間',
notice_type tinyint(1) comment '通知方式[0-短信，1-郵件]',
primary key (id)
);
alter table sms_seckill_sku_notice comment '秒殺商品通知訂閱';
/*==============================================================*/
/* Table: sms_seckill_sku_relation */
/*==============================================================*/
create table sms_seckill_sku_relation
(
id bigint not null auto_increment comment 'id',
promotion_id bigint comment '活動(dòng)id',
promotion_session_id bigint comment '活動(dòng)場(chǎng)次id',
sku_id bigint comment '商品id',
seckill_price decimal comment '秒殺價(jià)格',
seckill_count decimal comment '秒殺總量',
seckill_limit decimal comment '每人限購(gòu)數(shù)量',
seckill_sort int comment '排序',
primary key (id)
);
alter table sms_seckill_sku_relation comment '秒殺活動(dòng)商品關(guān)聯(lián)';
/*==============================================================*/
/* Table: sms_sku_full_reduction */
/*==============================================================*/
create table sms_sku_full_reduction
(
id bigint not null auto_increment comment 'id',
sku_id bigint comment 'spu_id',
full_price decimal(18,4) comment '滿多少',
reduce_price decimal(18,4) comment '減多少',
add_other tinyint(1) comment '是否參與其他優(yōu)惠',
primary key (id)
);
alter table sms_sku_full_reduction comment '商品滿減信息';
/*==============================================================*/
/* Table: sms_sku_ladder */
/*==============================================================*/
create table sms_sku_ladder
(
id bigint not null auto_increment comment 'id',
sku_id bigint comment 'spu_id',
full_count int comment '滿幾件',
discount decimal(4,2) comment '打幾折',
price decimal(18,4) comment '折后價(jià)',
add_other tinyint(1) comment '是否疊加其他優(yōu)惠[0-不可疊加，1-可疊加]',
primary key (id)
);
alter table sms_sku_ladder comment '商品階梯價(jià)格';
/*==============================================================*/
/* Table: sms_spu_bounds */
/*==============================================================*/
create table sms_spu_bounds
(
id bigint not null auto_increment comment 'id',
spu_id bigint,
grow_bounds decimal(18,4) comment '成長(zhǎng)積分',
buy_bounds decimal(18,4) comment '購(gòu)物積分',
work tinyint(1) comment '優(yōu)惠生效情況[1111（四個(gè)狀態(tài)位，從右到左）;0 - 無(wú)優(yōu)惠，成長(zhǎng)積分是否贈(zèng)送;1 - 無(wú)優(yōu)惠，購(gòu)物積分是否贈(zèng)送;2 - 有優(yōu)惠，成長(zhǎng)積分是否贈(zèng)送;3 - 有優(yōu)惠，購(gòu)物積分是否贈(zèng)送【狀態(tài)位0：不贈(zèng)送，1：贈(zèng)送】]',
primary key (id)
);
alter table sms_spu_bounds comment '商品spu積分設(shè)置';
gulimall_ums.sql
drop table if exists ums_growth_change_history;
drop table if exists ums_integration_change_history;
drop table if exists ums_member;
drop table if exists ums_member_collect_spu;
drop table if exists ums_member_collect_subject;
drop table if exists ums_member_level;
drop table if exists ums_member_login_log;
drop table if exists ums_member_receive_address;
drop table if exists ums_member_statistics_info;
/*==============================================================*/
/* Table: ums_growth_change_history */
/*==============================================================*/
create table ums_growth_change_history
(
id bigint not null auto_increment comment 'id',
member_id bigint comment 'member_id',
create_time datetime comment 'create_time',
change_count int comment '改變的值（正負(fù)計(jì)數(shù)）',
note varchar(0) comment '備注',
source_type tinyint comment '積分來(lái)源[0-購(gòu)物，1-管理員修改]',
primary key (id)
);
alter table ums_growth_change_history comment '成長(zhǎng)值變化歷史記錄';
/*==============================================================*/
/* Table: ums_integration_change_history */
/*==============================================================*/
create table ums_integration_change_history
(
id bigint not null auto_increment comment 'id',
member_id bigint comment 'member_id',
create_time datetime comment 'create_time',
change_count int comment '變化的值',
note varchar(255) comment '備注',
source_tyoe tinyint comment '來(lái)源[0->購(gòu)物；1->管理員修改;2->活動(dòng)]',
primary key (id)
);
alter table ums_integration_change_history comment '積分變化歷史記錄';
/*==============================================================*/
/* Table: ums_member */
/*==============================================================*/
create table ums_member
(
id bigint not null auto_increment comment 'id',
level_id bigint comment '會(huì)員等級(jí)id',
username char(64) comment '用戶名',
password varchar(64) comment '密碼',
nickname varchar(64) comment '昵稱',
mobile varchar(20) comment '手機(jī)號(hào)碼',
email varchar(64) comment '郵箱',
header varchar(500) comment '頭像',
gender tinyint comment '性別',
birth date comment '生日',
city varchar(500) comment '所在城市',
job varchar(255) comment '職業(yè)',
sign varchar(255) comment '個(gè)性簽名',
source_type tinyint comment '用戶來(lái)源',
integration int comment '積分',
growth int comment '成長(zhǎng)值',
status tinyint comment '啟用狀態(tài)',
create_time datetime comment '注冊(cè)時(shí)間',
primary key (id)
);
alter table ums_member comment '會(huì)員';
/*==============================================================*/
/* Table: ums_member_collect_spu */
/*==============================================================*/
create table ums_member_collect_spu
(
id bigint not null comment 'id',
member_id bigint comment '會(huì)員id',
spu_id bigint comment 'spu_id',
spu_name varchar(500) comment 'spu_name',
spu_img varchar(500) comment 'spu_img',
create_time datetime comment 'create_time',
primary key (id)
);
alter table ums_member_collect_spu comment '會(huì)員收藏的商品';
/*==============================================================*/
/* Table: ums_member_collect_subject */
/*==============================================================*/
create table ums_member_collect_subject
(
id bigint not null auto_increment comment 'id',
subject_id bigint comment 'subject_id',
subject_name varchar(255) comment 'subject_name',
subject_img varchar(500) comment 'subject_img',
subject_urll varchar(500) comment '活動(dòng)url',
primary key (id)
);
alter table ums_member_collect_subject comment '會(huì)員收藏的專題活動(dòng)';
/*==============================================================*/
/* Table: ums_member_level */
/*==============================================================*/
create table ums_member_level
(
id bigint not null auto_increment comment 'id',
name varchar(100) comment '等級(jí)名稱',
growth_point int comment '等級(jí)需要的成長(zhǎng)值',
default_status tinyint comment '是否為默認(rèn)等級(jí)[0->不是；1->是]',
free_freight_point decimal(18,4) comment '免運(yùn)費(fèi)標(biāo)準(zhǔn)',
comment_growth_point int comment '每次評(píng)價(jià)獲取的成長(zhǎng)值',
priviledge_free_freight tinyint comment '是否有免郵特權(quán)',
priviledge_member_price tinyint comment '是否有會(huì)員價(jià)格特權(quán)',
priviledge_birthday tinyint comment '是否有生日特權(quán)',
note varchar(255) comment '備注',
primary key (id)
);
alter table ums_member_level comment '會(huì)員等級(jí)';
/*==============================================================*/
/* Table: ums_member_login_log */
/*==============================================================*/
create table ums_member_login_log
(
id bigint not null auto_increment comment 'id',
member_id bigint comment 'member_id',
create_time datetime comment '創(chuàng)建時(shí)間',
ip varchar(64) comment 'ip',
city varchar(64) comment 'city',
login_type tinyint(1) comment '登錄類型[1-web，2-app]',
primary key (id)
);
alter table ums_member_login_log comment '會(huì)員登錄記錄';
/*==============================================================*/
/* Table: ums_member_receive_address */
/*==============================================================*/
create table ums_member_receive_address
(
id bigint not null auto_increment comment 'id',
member_id bigint comment 'member_id',
name varchar(255) comment '收貨人姓名',
phone varchar(64) comment '電話',
post_code varchar(64) comment '郵政編碼',
province varchar(100) comment '省份/直轄市',
city varchar(100) comment '城市',
region varchar(100) comment '區(qū)',
detail_address varchar(255) comment '詳細(xì)地址(街道)',
areacode varchar(15) comment '省市區(qū)代碼',
default_status tinyint(1) comment '是否默認(rèn)',
primary key (id)
);
alter table ums_member_receive_address comment '會(huì)員收貨地址';
/*==============================================================*/
/* Table: ums_member_statistics_info */
/*==============================================================*/
create table ums_member_statistics_info
(
id bigint not null auto_increment comment 'id',
member_id bigint comment '會(huì)員id',
consume_amount decimal(18,4) comment '累計(jì)消費(fèi)金額',
coupon_amount decimal(18,4) comment '累計(jì)優(yōu)惠金額',
order_count int comment '訂單數(shù)量',
coupon_count int comment '優(yōu)惠券數(shù)量',
comment_count int comment '評(píng)價(jià)數(shù)',
return_order_count int comment '退貨數(shù)量',
login_count int comment '登錄次數(shù)',
attend_count int comment '關(guān)注數(shù)量',
fans_count int comment '粉絲數(shù)量',
collect_product_count int comment '收藏的商品數(shù)量',
collect_subject_count int comment '收藏的專題活動(dòng)數(shù)量',
collect_comment_count int comment '收藏的評(píng)論數(shù)量',
invite_friend_count int comment '邀請(qǐng)的朋友數(shù)量',
primary key (id)
);
alter table ums_member_statistics_info comment '會(huì)員統(tǒng)計(jì)信息';
gulimall_wms.sql
/*
SQLyog Ultimate v11.25 (64 bit)
MySQL - 5.7.27 : Database - gulimall_wms
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`gulimall_wms` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `gulimall_wms`;
/*Table structure for table `undo_log` */
DROP TABLE IF EXISTS `undo_log`;
CREATE TABLE `undo_log` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`branch_id` bigint(20) NOT NULL,
`xid` varchar(100) NOT NULL,
`context` varchar(128) NOT NULL,
`rollback_info` longblob NOT NULL,
`log_status` int(11) NOT NULL,
`log_created` datetime NOT NULL,
`log_modified` datetime NOT NULL,
`ext` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `ux_undo_log` (`xid`,`branch_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `undo_log` */
/*Table structure for table `wms_purchase` */
DROP TABLE IF EXISTS `wms_purchase`;
CREATE TABLE `wms_purchase` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`assignee_id` bigint(20) DEFAULT NULL,
`assignee_name` varchar(255) DEFAULT NULL,
`phone` char(13) DEFAULT NULL,
`priority` int(4) DEFAULT NULL,
`status` int(4) DEFAULT NULL,
`ware_id` bigint(20) DEFAULT NULL,
`amount` decimal(18,4) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='采購(gòu)信息';
/*Data for the table `wms_purchase` */
/*Table structure for table `wms_purchase_detail` */
DROP TABLE IF EXISTS `wms_purchase_detail`;
CREATE TABLE `wms_purchase_detail` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`purchase_id` bigint(20) DEFAULT NULL COMMENT '采購(gòu)單id',
`sku_id` bigint(20) DEFAULT NULL COMMENT '采購(gòu)商品id',
`sku_num` int(11) DEFAULT NULL COMMENT '采購(gòu)數(shù)量',
`sku_price` decimal(18,4) DEFAULT NULL COMMENT '采購(gòu)金額',
`ware_id` bigint(20) DEFAULT NULL COMMENT '倉(cāng)庫(kù)id',
`status` int(11) DEFAULT NULL COMMENT '狀態(tài)[0新建，1已分配，2正在采購(gòu)，3已完成，4采購(gòu)失敗]',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*Data for the table `wms_purchase_detail` */
/*Table structure for table `wms_ware_info` */
DROP TABLE IF EXISTS `wms_ware_info`;
CREATE TABLE `wms_ware_info` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(255) DEFAULT NULL COMMENT '倉(cāng)庫(kù)名',
`address` varchar(255) DEFAULT NULL COMMENT '倉(cāng)庫(kù)地址',
`areacode` varchar(20) DEFAULT NULL COMMENT '區(qū)域編碼',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='倉(cāng)庫(kù)信息';
/*Data for the table `wms_ware_info` */
/*Table structure for table `wms_ware_order_task` */
DROP TABLE IF EXISTS `wms_ware_order_task`;
CREATE TABLE `wms_ware_order_task` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`order_id` bigint(20) DEFAULT NULL COMMENT 'order_id',
`order_sn` varchar(255) DEFAULT NULL COMMENT 'order_sn',
`consignee` varchar(100) DEFAULT NULL COMMENT '收貨人',
`consignee_tel` char(15) DEFAULT NULL COMMENT '收貨人電話',
`delivery_address` varchar(500) DEFAULT NULL COMMENT '配送地址',
`order_comment` varchar(200) DEFAULT NULL COMMENT '訂單備注',
`payment_way` tinyint(1) DEFAULT NULL COMMENT '付款方式【 1:在線付款 2:貨到付款】',
`task_status` tinyint(2) DEFAULT NULL COMMENT '任務(wù)狀態(tài)',
`order_body` varchar(255) DEFAULT NULL COMMENT '訂單描述',
`tracking_no` char(30) DEFAULT NULL COMMENT '物流單號(hào)',
`create_time` datetime DEFAULT NULL COMMENT 'create_time',
`ware_id` bigint(20) DEFAULT NULL COMMENT '倉(cāng)庫(kù)id',
`task_comment` varchar(500) DEFAULT NULL COMMENT '工作單備注',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='庫(kù)存工作單';
/*Data for the table `wms_ware_order_task` */
/*Table structure for table `wms_ware_order_task_detail` */
DROP TABLE IF EXISTS `wms_ware_order_task_detail`;
CREATE TABLE `wms_ware_order_task_detail` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`sku_id` bigint(20) DEFAULT NULL COMMENT 'sku_id',
`sku_name` varchar(255) DEFAULT NULL COMMENT 'sku_name',
`sku_num` int(11) DEFAULT NULL COMMENT '購(gòu)買(mǎi)個(gè)數(shù)',
`task_id` bigint(20) DEFAULT NULL COMMENT '工作單id',
`ware_id` bigint(20) DEFAULT NULL COMMENT '倉(cāng)庫(kù)id',
`lock_status` int(1) DEFAULT NULL COMMENT '1-已鎖定 2-已解鎖 3-扣減',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='庫(kù)存工作單';
/*Data for the table `wms_ware_order_task_detail` */
/*Table structure for table `wms_ware_sku` */
DROP TABLE IF EXISTS `wms_ware_sku`;
CREATE TABLE `wms_ware_sku` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`sku_id` bigint(20) DEFAULT NULL COMMENT 'sku_id',
`ware_id` bigint(20) DEFAULT NULL COMMENT '倉(cāng)庫(kù)id',
`stock` int(11) DEFAULT NULL COMMENT '庫(kù)存數(shù)',
`sku_name` varchar(200) DEFAULT NULL COMMENT 'sku_name',
`stock_locked` int(11) DEFAULT '0' COMMENT '鎖定庫(kù)存',
PRIMARY KEY (`id`),
KEY `sku_id` (`sku_id`) USING BTREE,
KEY `ware_id` (`ware_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品庫(kù)存';
/*Data for the table `wms_ware_sku` */
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;