MySQL第十四周

## 首页店铺推荐表

店铺ID
店铺名字
店铺图片
店铺排序

## 公告表

ID

公告类型

公告标题
公告内容

公告发布时间

## 首页轮播图

ID
轮播图图片
轮播图跳转地址
轮播图排序

## 标签分类表

标签ID
标签分类名

## 标签表

标签分类ID
标签名

## 商品标签分类标签价格表

id
商品ID
标签ID
价格

## 店铺

店铺ID
店铺名字
店铺动态
店铺图片

## 分类店铺表

商品分类ID
商品分类名字

店铺ID

## 商品表

商品ID
商品名字

商品图片
商品评分
商品月销量

商品起送价格
商品配送价格
商品分类ID

## 用户表

用户ID
用户手机号码
用户密码

## 购物车

谁 买了 什么东西？

ID
用户ID
商品ID
商品数量

## 创建数据库

create database take_away;

use take_away;

## 用户表

```sql
create table user(
    id int auto_increment primary key comment "用户表",
    user_phone char(11) unique not null comment "用户手机号码",
    user_password varchar(255) not null comment "用户密码",
    user_nickname varchar(50) not null comment "昵称",
    created_at datetime not null comment "创建时间",
    updated_at datetime not null comment "更新时间",
    deleted_at datetime null comment "删除时间"
)engine=Innodb;
```

## 店铺表

```sql
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 "店铺动态",
    created_at datetime not null comment "创建时间",
    updated_at datetime not null comment "更新时间",
    deleted_at datetime null comment "删除时间"
)engine=Innodb;
```

## 店铺商品分类表

```sql
create table shop_goods_type(

    id int auto_increment primary key comment "店铺商品分类ID",
    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;
```

## 商品表

```sql
create table 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 unsigned not null default 4 comment '商品评分',
    goods_sales int unsigned not null default 0  comment '商品销量',

    goods_start_delivery_price double unsigned not null comment '商品起送价格',
    goods_delivery_price double unsigned not null comment '商品配送价格',
    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;
```

## 商品标签分类表

```sql
create table goods_label_type(

    id int auto_increment primary key comment "标签分类ID",
    label_type_name varchar(50) not null unique comment "标签分类名" ,

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

## 商品标签表

```sql
create table goods_label(

    id int auto_increment primary key comment "标签ID",
    googs_label_name varchar(50) not null unique comment "标签名" ,

    googs_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;
```

## 商品 商品标签 商品价格表

```sql
create table goods_label_price(

    id int auto_increment primary key comment "ID",

    goods_id int unsigned not null comment '商品ID',
    goods_label_id int unsigned  not null comment '标签ID',
    goods_price double  unsigned not null comment '商品价格',

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

## 购物车

```sql
create table shopping_cart(

    id int auto_increment primary key comment "ID",

    user_id int unsigned not null comment '商品ID',

    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;
```

## 公告类型表

```sql
create table notice_type(

    id int auto_increment primary key comment "ID",
    notice_type_name varchar(50)  unique not null comment "公告类型名",
    
    created_at datetime not null comment "创建时间",
    updated_at datetime not null comment "更新时间",
    deleted_at datetime null comment "删除时间"
)engine=Innodb;
```

## 公告表 notice

```sql
create table notice(

    id int auto_increment primary key comment "ID",

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

    notice_type_id int unsigned not null comment '公告分类ID',
    shop_id int unsigned not null default 0 comment '商家ID',

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

## 首页轮播图

```sql
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_sort 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;
```

## 首页店铺推荐表

```sql
create table index_shop(

    id int auto_increment primary key comment "ID",
    shop_id int unsigned not null comment '店铺ID',
    shop_sort 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;
```
 

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;