5.25数据库建表语句

create database take_away2;

use take_away2;

## 用户表

```
create table user(
    id int auto_increment primary key comment "用户ID",
    user_phone char(11) not null unique comment '用户号码',
    user_password varchar(255) not null comment '用户密码',
    user_nickname varchar(50) not null comment '用户昵称',
    user_head varchar(255) not null comment '用户头像',

    created_at datetime not null comment "创建时间",
    updated_at datetime not null comment "更新时间",
    deleted_at datetime null comment "删除时间"

)engine=InnoDb;
```

## 店铺表

```
create table shop(
    id int auto_increment primary key comment "店铺ID",
    
    shop_name varchar(50) unique not null comment "店铺名字",
    shop_img varchar(255) not null comment "店铺图片",
    shop_dynamic varchar(255) not null comment "店铺动态",
    shop_longitude double not null comment "经度",
    shop_latitude double not null comment "纬度",

    created_at datetime not null comment "创建时间",
    updated_at datetime not null comment "更新时间",
    deleted_at datetime null comment "删除时间"

)engine=InnoDb;
```

## 店铺商品分类表

```
create table shop_goods_type(
    id int auto_increment primary key comment "分类ID",
    shop_goods_type_name varchar(50) not null comment '分类名字',
    shop_id int unsigned not null comment "店铺ID",
    created_at datetime not null comment "创建时间",
    updated_at datetime not null comment "更新时间",
    deleted_at datetime null comment "删除时间"
)engine=InnoDb;
```

## 商品

```
create table shop_goods(
    id int auto_increment primary key comment "商品ID",

    goods_name varchar(50) not null comment "商品名字",
    goods_img varchar(255) not null comment "商品图片",
    goods_score  float not null comment "商品评分",
    goods_sales int unsigned  not null comment "商品销量",
    goods_start_delivery_price double not null  comment "商品起送价格",
    goods_delivery_price double not null  comment "商品配送价格",

    shop_id int unsigned not null comment "店铺ID",
    shop_goods_type_id  int unsigned not null comment "分类ID",

    created_at datetime not null comment "创建时间",
    updated_at datetime not null comment "更新时间",
    deleted_at datetime null comment "删除时间"
)engine=InnoDb;
```

## 商品标签分类表

```
create table shop_goods_label_type(
    id int auto_increment primary key comment "商品ID",
    goods_label_type_name varchar(50) not null comment '商品标签分类',
    goods_label_type_order smallint unsigned not null default 1 comment '分类排序',
    

    created_at datetime not null comment "创建时间",
    updated_at datetime not null comment "更新时间",
    deleted_at datetime null comment "删除时间"
)engine=InnoDb;
```

## 商品标签

```
create table shop_goods_label(
    id int auto_increment primary key comment "商品ID",

    goods_label_name varchar(50) not null comment '商品标签名',
    shop_goods_label_type_id int unsigned not null  comment '分类ID',
    
    created_at datetime not null comment "创建时间",
    updated_at datetime not null comment "更新时间",
    deleted_at datetime null comment "删除时间"
)engine=InnoDb;
```

## 商品 标签 价格表

```
create table shop_goods_label_price(
    id int auto_increment primary key comment "ID",

    goods_id int unsigned not null comment '商品ID',
    shop_goods_label_id int unsigned not null comment '标签ID',
    goods_price double not null comment '商品价格',
    
    created_at datetime not null comment "创建时间",
    updated_at datetime not null comment "更新时间",
    deleted_at datetime null comment "删除时间"
)engine=InnoDb;
```

## 购物车

```
create table shopping_cart(
    id int auto_increment primary key comment "ID",

    user_id int unsigned not null comment '用户ID',
    shop_goods_label_price_id int unsigned not null comment '商品 标签 价格表ID',
    goods_num int unsigned not null comment '商品数量',

    created_at datetime not null comment "创建时间",
    updated_at datetime not null comment "更新时间",
    deleted_at datetime null comment "删除时间"
)engine=InnoDb;
```

## 首页轮播图

```
create table index_banner(
    id int auto_increment primary key comment "ID",

    banner_img varchar(255)  not null comment '图片',
    banner_url varchar(255)  not null comment '跳转地址',
    banner_order int unsigned not null comment '轮播图排序',

    created_at datetime not null comment "创建时间",
    updated_at datetime not null comment "更新时间",
    deleted_at datetime null comment "删除时间"
)engine=InnoDb;
```

## 公告类型表

```
create table notice_type(
    id int auto_increment primary key comment "ID",

    notice_type_name varchar(50)  not null comment '公告类型名字',
    
    created_at datetime not null comment "创建时间",
    updated_at datetime not null comment "更新时间",
    deleted_at datetime null comment "删除时间"
)engine=InnoDb;
```

## 公告

```
create table notice(
    id int auto_increment primary key comment "ID",

    notice_type_id int unsigned not null comment "公告类型_id",

    notice_title varchar(50)  not null comment '公告标题',
    notice_content varchar(255)  not null comment '公告内容',

    created_at datetime not null comment "创建时间",
    updated_at datetime not null comment "更新时间",
    deleted_at datetime null comment "删除时间"
)engine=InnoDb;
```

## 首页店铺推荐

```
create table index_shop(
    id int auto_increment primary key comment "ID",
    shop_id int unsigned not null comment "店铺ID",
    shop_url varchar(255)  not null comment '店铺跳转地址',
    shop_order int  unsigned not null comment '店铺的排序',
    created_at datetime not null comment "创建时间",
    updated_at datetime not null comment "更新时间",
    deleted_at datetime null comment "删除时间"
)engine=InnoDb;
```

########

业务分析

## 首页店铺推荐

id
店铺名字
店铺图片
店铺的排序
店铺跳转地址

## 公告类型表

id
公告类型名字

## 公告

id
公告类型_id
公告标题
公告内容
公告创建时间

## 首页轮播图

图片
跳转地址
轮播图排序

## 商品表

商品名字
商品图片
商品评分
商品月销量
商品起送价格
商品配送价格
店铺ID
商品分类的ID

## 店铺表

id
店铺动态
店铺名字
店铺图片
店铺经度
店铺纬度

## 商品分类表

id
商品分类名字
店铺ID

## 商品标签分类

id
分类名字
分类排序

## 商品标签

id
标签名
商品标签分类ID

## 商品 标签 价格表

id
商品ID
标签ID
商品价格

## 用户

id
用户手机号码
用户密码
用户昵称
用户头像

## 购物车

谁 买了 什么东西

id
用户ID
商品 标签 价格表ID
商品数量

insert into user values
    (null, '15627804801', 'erwe,mnjhjjjjj', '本本', now(), now(), null),
    (null, '15627804802', 'erwe,mnjhjjjjj', '静静', now(), now(), null);

select * from user;

insert into  shop values
    (null, '金拱门', 'http://www.baidu.com/1.png', '店铺动态', now(), now(), null),
    (null, '阿叔水果', 'http://www.baidu.com/2.png', '每日鲜果', now(), now(), null);

select * from shop;

insert into shop_goods_type values
    (null, "早餐早点", 1, now(), now(), null),
    (null, "超值套餐", 1, now(), now(), null),
    (null, "炸鸡啤酒", 1, now(), now(), null),
    (null, "酷暑冷饮", 1, now(), now(), null);

select * from shop_goods_type;

insert into goods values
(null, "小油条", "https://www.baidu.com/1.png", 4, 0, 17, 0, 1, now(), now(), null),
(null, "四件套", "https://www.baidu.com/1.png", 4, 0, 17, 0, 2, now(), now(), null),
(null, "炸鸡", "https://www.baidu.com/1.png", 4, 0, 16, 0, 3, now(), now(), null),
(null, "可乐", "https://www.baidu.com/1.png", 4, 0, 10, 0, 4, now(), now(), null),
(null, "奶茶", "https://www.baidu.com/1.png", 4, 0, 20, 0, 4, now(), now(), null);

select * from goods;

insert into goods_label_type values
(null, "口味", now(), now(), null),
(null, "冰量", now(), now(), null),
(null, "甜度", now(), now(), null),
(null, "小料", now(), now(), null);

select * from goods_label_type;

insert into goods_label value
(null, "不辣", 1, now(), now(), null),
(null, "微辣", 1, now(), now(), null),
(null, "变态辣", 1, now(), now(), null),

(null, "去冰", 2, now(), now(), null),
(null, "少冰", 2, now(), now(), null),
(null, "多冰", 2, now(), now(), null),

(null, "五分糖", 3, now(), now(), null),
(null, "七分糖", 3, now(), now(), null),
(null, "正常糖", 3, now(), now(), null),

(null, "珍珠", 4, now(), now(), null),
(null, "椰果", 4, now(), now(), null),
(null, "红豆", 4, now(), now(), null);

select * from goods_label;

insert into goods_label_price values
    (null, 4, 4, 4, now(), now(), null),
    (null, 4, 5, 4, now(), now(), null),
    (null, 4, 6, 4, now(), now(), null),

    (null, 5, 4, 8, now(), now(), null),
    (null, 5, 10, 9, now(), now(), null),
    (null, 5, 11, 10, now(), now(), null);

select * from goods_label_price;

insert into shopping_cart values
    (null, 1, 5, 2, now(), now(), null),
    (null, 1, 3, 1, now(), now(), null),
    (null, 2, 1, 3, now(), now(), null);

select * from shopping_cart;

insert into notice_type values
    (null, '平台公告', now(), now(), null),
    (null, '商家公告', now(), now(), null);

select * from notice_type;

insert into notice values
    (null, "今天打折了", "一律打5折， 清仓大甩卖", 2, 1 , now(), now(), null);

select * from notice;

insert into index_banner values
    (null, "https://www.baidu.com/1.png", "https://www.baidu.com/1", 1, now(), now(), null),
    (null, "https://www.baidu.com/3.png", "https://www.baidu.com/3", 3, now(), now(), null),
    (null, "https://www.baidu.com/2.png", "https://www.baidu.com/2", 2, now(), now(), null),
    (null, "https://www.baidu.com/4.png", "https://www.baidu.com/4", 4, now(), now(), null),
    (null, "https://www.baidu.com/6.png", "https://www.baidu.com/6", 5, now(), now(), null),
    (null, "https://www.baidu.com/5.png", "https://www.baidu.com/5", 5, now(), now(), null);

select * from index_banner;

insert into index_shop values 
(null, 1, 1, now(), now(), null),
(null, 2, 2, now(), now(), null);

select * from index_shop;