-- 用户
drop table if exists t_user;
create table t_user(
  user_id int(11) comment '用户Id' primary key auto_increment,
  username varchar(55) comment '用户名',
  password varchar(55) comment '密码',
  nicename varchar(55) comment '昵称',
  tel varchar(20) comment '手机号',
  email varchar(55) comment '邮箱',
  avatar varchar(255) comment '头像'
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='用户表';
insert into t_user (username,password,nicename,tel,email,avatar) values ('root','8848!abc','root','15366489361','15366489361@qq.com',null);
insert into t_user (username,password,nicename,tel,email,avatar) values ('manager','8846!abc','manager','15366489352','15366489352@qq.com',null);
insert into t_user (username,password,nicename,tel,email,avatar) values ('behind','8853!abc','behind','15366489311','15366489311@qq.com',null);
insert into t_user (username,password,nicename,tel,email,avatar) values ('brfore','8894!abc','brfore','15366489307','15366489307@qq.com',null);
select user_id,username,password,nicename,tel,email,avatar from t_user;

-- 角色表
drop table if exists t_role;
create table t_role(
  role_id int(11) comment '角色Id' primary key auto_increment,
  role_name varchar(55) comment '角色名称'
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='角色表';
insert into t_role (role_name) values ('老板');
insert into t_role (role_name) values ('项目经理');
insert into t_role (role_name) values ('开发');
select role_id,role_name from t_role;

-- 用户角色表
drop table if exists t_user_role;
create table t_user_role(
  id int(11) comment '用户角色表Id' primary key auto_increment,
  user_id int(11) comment '用户Id',
  role_id int(11) comment '角色Id'
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT = '用户角色表';
insert into t_user_role (user_id,role_id) values (1,1);
insert into t_user_role (user_id,role_id) values (2,2);
insert into t_user_role (user_id,role_id) values (3,3);
insert into t_user_role (user_id,role_id) values (4,3);
select id,user_id,role_id from t_user_role;

-- 用户角色
select
  u.username,
  u.tel,
  u.email,
  tr.role_name
from
  t_user u
left join t_user_role tur on u.user_id = tur.user_id
left join t_role tr on tur.role_id = tr.role_id;

-- 类别
drop table if exists t_category;
create table t_category(
  category_id int(11) comment '类别Id' primary key auto_increment,
  category_name varchar(55) comment '类别名称',
  parent_id int(11) comment '父级Id'
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT = '类别表';
insert into t_category (category_name, parent_id) values ('北单平胜负',0);
insert into t_category (category_name, parent_id) values ('五大联赛专题系列活动',0);
insert into t_category (category_name, parent_id) values ('竞彩篮彩',0);
insert into t_category (category_name, parent_id) values ('单场公告',1);
insert into t_category (category_name, parent_id) values ('焦点赛事',1);
insert into t_category (category_name, parent_id) values ('分析推荐',2);
insert into t_category (category_name, parent_id) values ('活动公告',2);
insert into t_category (category_name, parent_id) values ('论坛交流',2);
select category_id,category_name,parent_id from t_category;

-- 频道
drop table if exists t_channel;
create table t_channel(
  channel_id int(11) comment '频道Id' primary key auto_increment,
  channel_name varchar(55) comment '频道名称'
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT = '频道表';
insert into t_channel (channel_name) values ('web新闻');
insert into t_channel (channel_name) values ('版块设置');
insert into t_channel (channel_name) values ('频道1');
insert into t_channel (channel_name) values ('频道2');
select channel_id,channel_name from t_channel;


-- 新闻
drop table if exists t_items;
create table t_items(
  item_id int(11) comment '新闻Id' primary key auto_increment,
  item_title varchar(55) comment '新闻标题',
  author varchar(55) comment '作者',
  publish_date datetime comment '发表日期',
  item_content text comment '新闻内容'
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT = '新闻表';
insert into t_items (item_title, author, publish_date, item_content) values ('贫困妇女中一亿英镑大奖 彩票却被丈夫弄丢','张三','2022-1-1 9:00','舒服点高合金钢复活甲干哈干哈航海士航海士都不晓得版本方法');
insert into t_items (item_title, author, publish_date, item_content) values ('比分直播 彩果:皇马胜米兰 销3549万 奖金预测','李四','2022-1-1 10:10','阿是挂号费活佛济公哈哈估计是他金卡古法个根据该');
insert into t_items (item_title, author, publish_date, item_content) values ('胜负彩10099期媒体推荐总汇 即时赔率 澳门盘','王五','2022-1-1 11:02','阿萨法工会经费复健科干哈的双腿腿与要他湿度卡hi');
insert into t_items (item_title, author, publish_date, item_content) values ('单场: 曼联巴撒狂让2球 彩票 超市 高手合买','赵六','2022-1-1 11:46','啊舒服点钢化膜第十九届,合法打撒色染合计好,看,就');
select item_id,item_title, author, publish_date, item_content from t_items;

-- 版块
drop table if exists t_plate;
create table t_plate(
  plate_id int(11) comment '版块Id' primary key auto_increment,
  plate_name varchar(55) comment '版块名称',
  plate_content varchar(255) comment '版块内容'
);
insert into t_plate (plate_name, plate_content) values ('全部',null);
insert into t_plate (plate_name, plate_content) values ('首页双色球',null);
insert into t_plate (plate_name, plate_content) values ('版块1',null);
insert into t_plate (plate_name, plate_content) values ('不选择版块',null);
select plate_id, plate_name, plate_content from t_plate;

-- 模版
drop table if exists t_stencil;
create table t_stencil(
  stencil_id int(11) comment '模版Id' primary key auto_increment,
  stencil_name varchar(55) comment '模版名称',
  stencil_content varchar(255) comment '模版内容'
);
select stencil_id, stencil_name, stencil_content from t_stencil;

-- 日志
drop table if exists t_logs;
create table t_logs(
  log_id int(11) comment '日志Id' primary key auto_increment,
  uri varchar(55) comment '功能',
  method varchar(55) comment '请求方式',
  create_by varchar(55) comment '操作人',
  create_time datetime comment '操作时间',
  update_by varchar(55) default null comment '修改人',
  update_time datetime default null comment '修改时间'
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT = '新闻表';
insert into t_logs (uri, method, create_by, create_time) values ('/login','post','before',now());
select log_id,uri,method, create_by, create_time,update_by,update_time from t_logs;