哪个专业是学网站开发的,wordpress08影视源码,阳谷企业做网站推广,公司内部管理软件叫什么需求#xff1a; 登录不同用户 显示不同的菜单
思路#xff1a;根据用户id 左关联表 查询出对应的菜单选项
查询SQL
select distinct-- 菜单表 去除重复记录sys_menu.id,sys_menu.parentId, sys_menu.name
from
-- 权限表sys_menu-- 角色与权限表 菜单表id 角色菜…需求 登录不同用户 显示不同的菜单
思路根据用户id 左关联表 查询出对应的菜单选项
查询SQL
select distinct-- 菜单表 去除重复记录sys_menu.id,sys_menu.parentId, sys_menu.name
from
-- 权限表sys_menu-- 角色与权限表 菜单表id 角色菜单表的菜单id
left join sys_menu_role ON sys_menu.id sys_menu_role.mid-- 角色表 角色菜单表的uid 角色表id
left join sys_role ON sys_menu_role.uid sys_role.id-- 用户角色表 角色表id 用户角色表role_id
left join sys_user_role ON sys_role.id sys_user_role.rid-- 用户表 用户角色表user_id 用户表的id
left join sys_user ON sys_user_role.uid sys_user.id
wheresys_user.id1;
需要五张表
用户表sys_user角色表sys_role菜单表(权限表)sys_menu用户角色关联表sys_user_role角色菜单关联表sys_menu_role
新建表
sys_user sys_role sys_menu
CREATE TABLE sys_user (id bigint NOT NULL AUTO_INCREMENT,name longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,nick_name longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,password longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,mobile longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,status varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT 1 COMMENT \用户账号状态\,create_time datetime(3) NULL DEFAULT (now()) COMMENT \创建账号时间\,recent_login datetime(3) NULL DEFAULT (now()) COMMENT \最近登录时间\,user_name longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,PRIMARY KEY (id) USING BTREE
) ENGINE InnoDB AUTO_INCREMENT 6 CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ROW_FORMAT Dynamic;CREATE TABLE sys_role (id int NOT NULL AUTO_INCREMENT,name varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,nameZh varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT 角色名称,PRIMARY KEY (id) USING BTREE
) ENGINE InnoDB AUTO_INCREMENT 15 CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci ROW_FORMAT DYNAMIC;CREATE TABLE sys_menu (id int NOT NULL AUTO_INCREMENT,url varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,path varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,component varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,name varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,iconCls varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,keepAlive tinyint(1) NULL DEFAULT NULL,requireAuth tinyint(1) NULL DEFAULT NULL,parentId int NULL DEFAULT NULL,enabled tinyint(1) NULL DEFAULT 1,PRIMARY KEY (id) USING BTREE,INDEX parentId(parentId ASC) USING BTREE,CONSTRAINT sys_menu_ibfk_1 FOREIGN KEY (parentId) REFERENCES sys_menu (id) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE InnoDB AUTO_INCREMENT 31 CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci ROW_FORMAT DYNAMIC;-- ----------------------------
-- Records of sys_menu
-- ----------------------------
INSERT INTO sys_menu VALUES (1, /, NULL, NULL, 所有, NULL, NULL, NULL, NULL, 1);
INSERT INTO sys_menu VALUES (2, /, /home, Home, 服务器组1, fa fa-user-circle-o, NULL, 1, 1, 1);
INSERT INTO sys_menu VALUES (3, /, /home, Home, 服务器组2, fa fa-address-card-o, NULL, 1, 1, 1);
INSERT INTO sys_menu VALUES (4, /, /home, Home, 服务器组3, fa fa-money, NULL, 1, 1, 1);
INSERT INTO sys_menu VALUES (5, /, /home, Home, 服务器组4, fa fa-bar-chart, NULL, 1, 1, 1);
INSERT INTO sys_menu VALUES (6, /, /home, Home, 服务器组5, fa fa-windows, NULL, 1, 1, 1);
INSERT INTO sys_menu VALUES (7, /, /home, Home, 服务器组6, fa-solid fa-circle-arrow-up, NULL, 1, 1, 1);
INSERT INTO sys_menu VALUES (8, /, /home, Home, 服务器组7, NULL, NULL, 1, 1, 1);
INSERT INTO sys_menu VALUES (9, /personnel/emp/**, /per/emp, PerEmp, 主机资料, NULL, NULL, 1, 3, 1);
INSERT INTO sys_menu VALUES (10, /personnel/ec/**, /per/ec, PerEc, 待开发, NULL, NULL, 1, 3, 1);
INSERT INTO sys_menu VALUES (11, /personnel/train/**, /per/train, PerTrain, 待开发, NULL, NULL, 1, 3, 1);
INSERT INTO sys_menu VALUES (12, /personnel/salary/**, /per/salary, PerSalary, 待开发, NULL, NULL, 1, 3, 1);
INSERT INTO sys_menu VALUES (13, /personnel/remove/**, /per/mv, PerMv, 待开发, NULL, NULL, 1, 3, 1);
INSERT INTO sys_menu VALUES (14, /salary/sob/**, /sal/sob, SalSob, 待开发, NULL, NULL, 1, 4, 1);
INSERT INTO sys_menu VALUES (15, /salary/sobcfg/**, /sal/sobcfg, SalSobCfg, 待开发, NULL, NULL, 1, 4, 1);
INSERT INTO sys_menu VALUES (16, /salary/table/**, /sal/table, SalTable, 待开发, NULL, NULL, 1, 4, 1);
INSERT INTO sys_menu VALUES (17, /salary/month/**, /sal/month, SalMonth, 待开发, NULL, NULL, 1, 4, 1);
INSERT INTO sys_menu VALUES (18, /salary/search/**, /sal/search, SalSearch, 待开发, NULL, NULL, 1, 4, 1);
INSERT INTO sys_menu VALUES (19, /statistics/all/**, /sta/all, StaAll, 待开发, NULL, NULL, 1, 5, 1);
INSERT INTO sys_menu VALUES (20, /statistics/score/**, /sta/score, StaScore, 待开发, NULL, NULL, 1, 5, 1);
INSERT INTO sys_menu VALUES (21, /statistics/personnel/**, /sta/pers, StaPers, 待开发, NULL, NULL, 1, 5, 1);
INSERT INTO sys_menu VALUES (22, /statistics/recored/**, /sta/record, StaRecord, IP记录, NULL, NULL, 1, 5, 1);
INSERT INTO sys_menu VALUES (23, /system/basic/**, /sys/basic, SysBasic, 基本资料, NULL, NULL, 1, 6, 1);
INSERT INTO sys_menu VALUES (24, /system/cfg/**, /sys/cfg, SysCfg, 系统配置, fa fa-windows, NULL, 1, 6, 1);
INSERT INTO sys_menu VALUES (25, /system/log/**, /sys/log, SysLog, 操作日志管理, NULL, NULL, 1, 6, 1);
INSERT INTO sys_menu VALUES (26, /system/hr/**, /sys/hr, SysHr, 操作员管理, NULL, NULL, 1, 6, 1);
INSERT INTO sys_menu VALUES (27, /system/data/**, /sys/data, SysData, 备份恢复数据库, NULL, NULL, 1, 6, 1);
INSERT INTO sys_menu VALUES (28, /system/site/**, /sys/site, SysSite, 配置文件, NULL, NULL, 1, 6, 1);
INSERT INTO sys_menu VALUES (29, /system/term/**, /sys/term, SysTerm, 命令终端, fa fa-terminal, NULL, 1, 6, 1);
INSERT INTO sys_menu VALUES (30, /system/deploy/**, /sys/deploy, SysDeploy, 应用管理, fa fa-ad, NULL, 1, 6, 1);
中间关联表
CREATE TABLE sys_user_role (id int NOT NULL AUTO_INCREMENT,uid int NULL DEFAULT NULL,rid int NULL DEFAULT NULL,PRIMARY KEY (id) USING BTREE,INDEX rid(rid ASC) USING BTREE,INDEX hr_role_ibfk_1(uid ASC) USING BTREE
) ENGINE InnoDB AUTO_INCREMENT 76 CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci COMMENT 权限id ROW_FORMAT DYNAMIC;CREATE TABLE sys_menu_role (id int NOT NULL AUTO_INCREMENT,mid int NULL DEFAULT NULL,uid int NULL DEFAULT NULL,PRIMARY KEY (id) USING BTREE,INDEX mid(mid ASC) USING BTREE,INDEX rid(uid ASC) USING BTREE
) ENGINE InnoDB AUTO_INCREMENT 288 CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci ROW_FORMAT DYNAMIC;-- ----------------------------
-- Records of sys_menu_role
-- ----------------------------
INSERT INTO sys_menu_role VALUES (1, 1, 1);
INSERT INTO sys_menu_role VALUES (2, 2, 1);
INSERT INTO sys_menu_role VALUES (3, 3, 1);
INSERT INTO sys_menu_role VALUES (4, 4, 1);
INSERT INTO sys_menu_role VALUES (5, 5, 1);
INSERT INTO sys_menu_role VALUES (6, 6, 1);
INSERT INTO sys_menu_role VALUES (7, 7, 1);
INSERT INTO sys_menu_role VALUES (8, 8, 1);
中间表进行权限关联 user_role 用户角色表 uid为 用户表中的id 其中uid1 是 超级管理员 uid 1 对应的 rid1 对应的 角色菜单表 对应关系 menu_role
sys_menu_role 代表 超级管理员对应7个菜单 选项 uid1 表示用户id是1 mid {1,...7} 对应 菜单表的 查询结果为 uid1 对应7个菜单项 新增一个用户 需要依赖parentID 即子菜单的父菜单
