Mysql语句商城系统表案例

show tables;
create database if not exists myshop;
use myshop;
create table w_goods_category
(
id int unsigned primary key auto_increment comment '分类id',
parent_id int unsigned not null default 0 comment '上级分类id',
name varchar(100) not null default '' comment '名称',
sort int unsigned not null default 0 comment '排序',
is_show tinyint unsigned not null default 0 comment '是否显示',
create_time datetime not null default current_timestamp comment
'创建时间',
update_time timestamp not null default current_timestamp on
update current_timestamp comment '更新时间'
);
insert into w_goods_category(id, parent_id, name)
values (1, 0, '办公'),
(2, 1, '耗材'),
(3, 2, '文具'),
(4, 0, '电子产品'),
(5, 4, '通讯'),
(6, 5, '手机'),
(7, 4, '影音'),
(8, 7, '音箱'),
(9, 7, '耳机'),
(10, 4, '电脑'),
(11, 10, '台式电脑'),
(12, 10, '笔记本'),
(13, 0, '服装'),
(14, 13, '女装'),
(15, 14, '风衣'),
(16, 14, '毛衣');
create table w_goods
(
id int unsigned primary key auto_increment comment '商品
id',
category_id int unsigned not null default 0 comment '分类
id',
spu_id int unsigned not null default 0 comment 'SPU
id',
sn varchar(20) not null default '' comment '编
号',
name varchar(120) not null default '' comment '名
称',
keyword varchar(255) not null default '' comment '关
键字',
picture varchar(255) not null default '' comment '图
片',
tips varchar(255) not null default '' comment '提
示',
description varchar(255) not null default '' comment '描
述',
content text not null comment '详情',
price decimal(10, 2) unsigned not null default 0 comment '价
格',
stock int unsigned not null default 0 comment '库
存',
score decimal(3, 2) unsigned not null default 0 comment '评
分',
is_on_sale tinyint unsigned not null default 0 comment '是否
上架',
is_del tinyint unsigned not null default 0 comment '是否
删除',
is_free_shipping tinyint unsigned not null default 0 comment '是否
包邮',
sell_count int unsigned not null default 0 comment '销量
计数',
comment int unsigned not null default 0 comment '评论
计数',
on_sale_time datetime default null comment
'上架时间',
create_time datetime not null default
current_timestamp comment '创建时间',
update_time datetime not null default
current_timestamp on update current_timestamp comment '更新时间'
);
insert into w_goods(id, category_id, name, keyword, content, price, stock,
score, comment)
values (1, 3, '2B铅笔', '文具', '考试专用', 0.5, 500, 4.9, 40000),
(2, 3, '钢笔', '文具', '练字必不可少', 15, 300, 3.9, 500),
(3, 3, '碳素笔', '文具', '平时使用', 1, 500, 5, 98000),
(4, 12, '超薄笔记本', '电子产品', '轻小便携', 5999, 0, 2.5, 200),
(5, 6, '智能手机', '电子产品', '人人必备', 1999, 0, 5, 98000),
(6, 8, '桌面音箱', '电子产品', '扩音装备', 69, 750, 4.5, 1000),
(7, 9, '头戴耳机', '电子产品', '独享个人世界', 109, 0, 3.9, 500),
(8, 10, '办公电脑', '电子产品', '适合办公', 2000, 0, 4.8, 6000),
(9, 15, '收腰风衣', '服装', '春节潮流单品', 299, 0, 4.9, 40000),
(10, 16, '薄毛衣', '服装', '居家旅行必备', 48, 0, 4.8, 98000);
select *
from w_goods;
#创建表user
create table user
(
id int unsigned auto_increment key,
device_id int unsigned,
gender varchar(6),
age tinyint,
university varchar(4),
province varchar(10)
);
#插入数据
insert into user(device_id, gender, age, university, province)
values (2138, 'male', 21, '北京大学', 'beijing'),
(3214, 'male', null, '复旦大学', 'shanghai'),
(6543, 'female', 20, '北京大学', 'beijing'),
(2315, 'female', 23, '浙江大学', 'zhejiang'),
(5432, 'male', 25, '山东大学', 'shandong');
#创建商品类别
create table category
(
id int unsigned auto_increment key,
name varchar(20),
pid int unsigned default 0,
sorts tinyint unsigned
);
#创建商品表
create table goods
(
id int unsigned auto_increment key,
name varchar(20),
content text,
price decimal(10, 2),
stock mediumint default 0,
cid int unsigned,
categorypath varchar(200),
create_time timestamp default current_timestamp,
update_time timestamp on update current_timestamp
);
#商品购买表
create table goods_sale
(
id int unsigned auto_increment key,
gid int unsigned,
name varchar(20),
num mediumint default 0,
price decimal(10, 2),
create_time timestamp default current_timestamp
);

粘贴收藏在这里，方便之后使用。