【SpringBoot+Vue】全网最简单但实用的前后端分离项目实战笔记 - 数据库设计

配套视频地址：https://www.bilibili.com/video/BV1dG4y1T7yp/

数据库xdb

1. 用户表

CREATETABLE`x_user`(`id`int(11)NOTNULLAUTO_INCREMENT,`username`varchar(50)NOTNULL,`password`varchar(100)DEFAULTNULL,`email`varchar(50)DEFAULTNULL,`phone`varchar(20)DEFAULTNULL,`status`int(1)DEFAULTNULL,`avatar`varchar(200)DEFAULTNULL,`deleted`INT(1)DEFAULT0,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=3DEFAULTCHARSET=utf8;

insertinto`x_user`(`id`,`username`,`password`,`email`,`phone`,`status`,`avatar`,`deleted`)values('1','admin','123456','[email protected]','18677778888','1','https://wpimg.wallstcn.com/f778738c-e4f8-4870-b634-56703b4acafe.gif','0');insertinto`x_user`(`id`,`username`,`password`,`email`,`phone`,`status`,`avatar`,`deleted`)values('2','zhangsan','123456','[email protected]','13966667777','1','https://wpimg.wallstcn.com/f778738c-e4f8-4870-b634-56703b4acafe.gif','0');insertinto`x_user`(`id`,`username`,`password`,`email`,`phone`,`status`,`avatar`,`deleted`)values('3','lisi','123456','[email protected]','13966667778','1','https://wpimg.wallstcn.com/f778738c-e4f8-4870-b634-56703b4acafe.gif','0');insertinto`x_user`(`id`,`username`,`password`,`email`,`phone`,`status`,`avatar`,`deleted`)values('4','wangwu','123456','[email protected]','13966667772','1','https://wpimg.wallstcn.com/f778738c-e4f8-4870-b634-56703b4acafe.gif','0');insertinto`x_user`(`id`,`username`,`password`,`email`,`phone`,`status`,`avatar`,`deleted`)values('5','zhaoer','123456','[email protected]','13966667776','1','https://wpimg.wallstcn.com/f778738c-e4f8-4870-b634-56703b4acafe.gif','0');insertinto`x_user`(`id`,`username`,`password`,`email`,`phone`,`status`,`avatar`,`deleted`)values('6','songliu','123456','[email protected]','13966667771','1','https://wpimg.wallstcn.com/f778738c-e4f8-4870-b634-56703b4acafe.gif','0');

2. 角色表

CREATETABLE`x_role`(`role_id`int(11)NOTNULLAUTO_INCREMENT,`role_name`varchar(50)DEFAULTNULL,`role_desc`varchar(100)DEFAULTNULL,PRIMARYKEY(`role_id`))ENGINE=InnoDBAUTO_INCREMENT=4DEFAULTCHARSET=utf8mb4;

insertinto`x_role`(`role_id`,`role_name`,`role_desc`)values('1','admin','超级管理员');insertinto`x_role`(`role_id`,`role_name`,`role_desc`)values('2','hr','人事专员');insertinto`x_role`(`role_id`,`role_name`,`role_desc`)values('3','normal','普通员工');

3. 菜单表

CREATETABLE`x_menu`(`menu_id`int(11)NOTNULLAUTO_INCREMENT,`component`varchar(100)DEFAULTNULL,`path`varchar(100)DEFAULTNULL,`redirect`varchar(100)DEFAULTNULL,`name`varchar(100)DEFAULTNULL,`title`varchar(100)DEFAULTNULL,`icon`varchar(100)DEFAULTNULL,`parent_id`int(11)DEFAULTNULL,`is_leaf`varchar(1)DEFAULTNULL,`hidden`tinyint(1)DEFAULTNULL,PRIMARYKEY(`menu_id`))ENGINE=InnoDBAUTO_INCREMENT=12DEFAULTCHARSET=utf8mb4;

insertinto`x_menu`(`menu_id`,`component`,`path`,`redirect`,`name`,`title`,`icon`,`parent_id`,`is_leaf`,`hidden`)values(1,'Layout','/user','/user/list','userManage','用户管理','userManage',0,'N',0),(2,'user/user','list',NULL,'userList','用户列表','userList',1,'Y',0),(3,'user/role','role',NULL,'roleList','角色列表','role',1,'Y',0),(4,'user/permission','permission',NULL,'permissionList','权限列表','permission',1,'Y',0);

4. 用户角色映射表

CREATETABLE`x_user_role`(`id`int(11)NOTNULLAUTO_INCREMENT,`user_id`int(11)DEFAULTNULL,`role_id`int(11)DEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=5DEFAULTCHARSET=utf8mb4;

insertinto`x_user_role`(`id`,`user_id`,`role_id`)values('1','1','1');

5. 角色菜单映射表

CREATETABLE`x_role_menu`(`id`int(11)NOTNULLAUTO_INCREMENT,`role_id`int(11)DEFAULTNULL,`menu_id`int(11)DEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=5DEFAULTCHARSET=utf8mb4;