代码地址
create database springboot_db default character set utf8;
drop table user;
CREATE TABLE user(
id BIGINT(20) primary key auto_increment,
name VARCHAR(30) ,
password varchar(48) default '123456',
age INT(11) NULL DEFAULT NULL COMMENT '年龄',
email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱',
create_time timestamp default current_timestamp
) default character set utf8;
insert into user(name,age) values('张三2',18);
insert into user(name,age) values('张三3',18);
insert into user(name,age) values('张三4',18);
insert into user(name,age) values('张三5',18);
insert into user(name,age) values('张三6',18);
insert into user(name,age) values('张三7',18);
insert into user(name,age) values('张三8',18);
insert into user(name,age) values('张三9',18);
insert into user(name,age) values('张三10',18);
select * from user;
#############################################################################
create table tbl_role (
id int primary key auto_increment,
role_name varchar(36),
role_code varchar(36),
create_time timestamp not null default current_timestamp comment '创建时间'
)default character set utf8;
# select * from tbl_role;
#drop table tbl_role;
insert into tbl_role (id,role_name, role_code) VALUES (1,'管理员','admin');
# update tbl_role set role_name='李四' where id=1;
#用户表
create table tbl_user (
id int primary key auto_increment,
log_name varchar(25),
log_pwd varchar(50) default '123456',
role_id int(3),
status int(1) default 0,
create_time timestamp not null default current_timestamp comment '创建时间',
foreign key (role_id) references tbl_role(id)
)default character set utf8;
update tbl_user set status=0 where id = 1;
#drop table tbl_user2;
# select * from tbl_user2;
insert into tbl_user (log_name, role_id) values ('admin',1);
# select log_name,role_code from tbl_user2 a,tbl_role b where a.role_id = b.id;
#菜单表
create table tbl_menu(
id int(3) primary key auto_increment,
menu_name varchar(36),
url varchar(100),
parent_id int(2),
create_time timestamp not null default current_timestamp comment '创建时间'
)default character set utf8;
# select * from tbl_menu;
insert into tbl_menu (id,menu_name, url, parent_id) values (1,'系统管理',null,null);
insert into tbl_menu (id,menu_name, url, parent_id) values (2,'角色管理','/role/index',1);
#角色-菜单表
create table tbl_role_menu (
id int(3) primary key auto_increment,
role_id int(3),
menu_id int(3)
)default character set utf8;
insert into tbl_role_menu(role_id,menu_id) values(1,1),(1,2);
show tables;