133 lines
6.7 KiB
SQL
133 lines
6.7 KiB
SQL
-- 用户
|
|
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;
|