业务分析
## 首页店铺推荐
id
店铺名字
店铺图片
店铺的排序
店铺跳转地址
## 公告类型表
id
公告类型名字
## 公告
id
公告类型_id
公告标题
公告内容
公告创建时间
## 首页轮播图
图片
跳转地址
轮播图排序
## 商品表
商品名字
商品图片
商品评分
商品月销量
商品起送价格
商品配送价格
店铺ID
商品分类的ID
## 店铺表
id
店铺动态
店铺名字
店铺图片
店铺经度
店铺纬度
## 商品分类表
id
商品分类名字
店铺ID
## 商品标签分类
id
分类名字
分类排序
## 商品标签
id
标签名
商品标签分类ID
## 商品 标签 价格表
id
商品ID
标签ID
商品价格
## 用户
id
用户手机号码
用户密码
用户昵称
用户头像
## 购物车
谁 买了 什么东西
id
用户ID
商品 标签 价格表ID
商品数量
建表语句
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;
```
# 函数
## 聚合函数
- max()
- min()
- count()
- sum()
- avg()
## 日期时间
CURDATE(),CURRENT_DATE() 返回当前日期,精确到年月日
CURTIME(),CURRENT_TIME() 返回当前时间,时分秒
NOW(),CURRENT_TIMESTAMP(),LOCALTIME(),SYSDATE(),LOCALTIMESTAMP() 返回当前日期和时间:年月日时分秒
日期 date "2017-04-12"
时间 time "14:20:15"
日期时间 datetime "2017-04-12 14:20:15"
select current_date();
select CURRENT_TIME();
select now();
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;
insert into user values
(null, "1383838438", "sfsdfa", "笨笨", "https://s1.ax1x.com/2023/05/25/p9H7RTP.png", "2023-05-25 17:26:45", "2023-05-25 17:26:45", null);
insert into user values
(null, "1383838439", "sfsdfa", "静静", "https://s1.ax1x.com/2023/05/25/p9H7RTP.png", now(), now(), null);
select * from user;