-- 创建数据库 CREATE DATABASE `crowd` DEFAULT CHARSET utf8 COLLATE utf8_unicode_ci; -- 选择数据库 USE `crowd`; -- 创建管理员表(自行插入假数据) CREATE TABLE `cf_admin` ( `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID', `account` VARCHAR(255) NOT NULL COMMENT '登录账号', `username` VARCHAR(255) NOT NULL COMMENT '昵称', -- 后期使用SpringSecurity时使用到了盐值加密算法,所以长度需要扩展至60 `password` VARCHAR(60) NOT NULL COMMENT '密码', `email` VARCHAR(255) NOT NULL COMMENT '邮箱', `create_time` DATETIME NOT NULL COMMENT '创建时间', PRIMARY KEY (`id`), UNIQUE KEY `account` (`account`) ) COMMENT '管理员表'; -- 默认管理员账户:admin-0000 INSERT INTO `cf_admin` VALUES (1, 'admin', '系统管理员', '$2a$10$f5vq3tYxcwubpH7HFYSW/OVlZbJffrMpZKenx8Fd0eOavCnv.E93q', 'admin@qq.com', '2021-07-20 02:17:59') -- 创建角色表(自行插入假数据) CREATE TABLE `cf_role` ( `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID', `name` VARCHAR(20) DEFAULT NULL COMMENT '角色名称', PRIMARY KEY (`id`) ) COMMENT '角色表'; INSERT INTO `cf_role` VALUES (1, '系统管理员'); INSERT INTO `cf_role` VALUES (2, '管理员'); -- 创建菜单表 CREATE TABLE `cf_menu` ( `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID', `pid` VARCHAR(19) DEFAULT NULL COMMENT '父节点ID', `name` VARCHAR(200) DEFAULT NULL COMMENT '菜单名称', `url` VARCHAR(200) DEFAULT NULL COMMENT '菜单对应的URL地址', `icon` VARCHAR(200) DEFAULT NULL COMMENT '菜单图标', PRIMARY KEY (`id`) ) COMMENT '菜单表'; -- 菜单表测试数据 INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(1, NULL, '系统权限菜单', 'glyphicon glyphicon-th-list', NULL); INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(2, 1, '控制面板', 'glyphicon glyphicon-dashboard', 'main.htm'); INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(3, 1, '权限管理', 'glyphicon glyphicon glyphicon-tasks', NULL); INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(4, 3, '用户维护', 'glyphicon glyphicon-user', 'user/index.htm'); INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(5, 3, '角色维护', 'glyphicon glyphicon-king', 'role/index.htm'); INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(6, 3, '菜单维护', 'glyphicon glyphicon-lock', 'permission/index.htm'); INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(7, 1, '业务审核', 'glyphicon glyphicon-ok', NULL); INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(8, 7, '实名认证审核', 'glyphicon glyphicon-check', 'auth_cert/index.htm'); INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(9, 7, '广告审核', 'glyphicon glyphicon-check', 'auth_adv/index.htm'); INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(10, 7, '项目审核', 'glyphicon glyphicon-check', 'auth_project/index.htm'); INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(11, 1, '业务管理', 'glyphicon glyphicon-th-large', NULL); INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(12, 11, '资质维护', 'glyphicon glyphicon-picture', 'cert/index.htm'); INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(13, 11, '分类管理', 'glyphicon glyphicon-equalizer', 'certtype/index.htm'); INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(14, 11, '流程管理', 'glyphicon glyphicon-random', 'process/index.htm'); INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(15, 11, '广告管理', 'glyphicon glyphicon-hdd', 'advert/index.htm'); INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(16, 11, '消息模板', 'glyphicon glyphicon-comment', 'message/index.htm'); INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(17, 11, '项目分类', 'glyphicon glyphicon-list', 'projectType/index.htm'); INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(18, 11, '项目标签', 'glyphicon glyphicon-tags', 'tag/index.htm'); INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(19, 1, '参数管理', 'glyphicon glyphicon-list-alt', 'param/index.htm'); -- 管理员关联角色的中间表 CREATE TABLE `cf_admin_role` ( `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID', `admin_id` INT(11) NOT NULL COMMENT '管理员ID', `role_id` INT(11) NOT NULL COMMENT '角色ID', PRIMARY KEY (`id`) ) COMMENT '管理员与角色的中间表'; INSERT INTO `cf_admin_role` VALUES(1, 1, 1); -- 权限表 CREATE TABLE `cf_auth` ( `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID', `title` VARCHAR(200) DEFAULT NULL COMMENT '权限名称', `name` VARCHAR(200) DEFAULT NULL COMMENT '权限标识', `category_id` INT(11) DEFAULT NULL COMMENT '父ID', PRIMARY KEY (`id`) ) COMMENT '权限表'; -- 插入测试数据 INSERT INTO `cf_auth` VALUES (1, '用户模块', '', NULL); INSERT INTO `cf_auth` VALUES (2, '查询', 'user:get', 1); INSERT INTO `cf_auth` VALUES (3, '添加', 'user:insert', 1); INSERT INTO `cf_auth` VALUES (4, '编辑', 'user:edit', 1); INSERT INTO `cf_auth` VALUES (5, '删除', 'user:delete', 1); INSERT INTO `cf_auth` VALUES (6, '角色模块', '', NULL); INSERT INTO `cf_auth` VALUES (7, '查询', 'role:get', 6); INSERT INTO `cf_auth` VALUES (8, '新增', 'role:insert', 6); INSERT INTO `cf_auth` VALUES (9, '编辑', 'role:edit', 6); INSERT INTO `cf_auth` VALUES (10, '分配', 'role:assign', 6); INSERT INTO `cf_auth` VALUES (11, '删除', 'role:delete', 6); INSERT INTO `cf_auth` VALUES (12, '菜单模块', NULL, NULL); INSERT INTO `cf_auth` VALUES (13, '查询', 'menu:get', 12); -- 创建角色与权限的中间表 CREATE TABLE `cf_role_auth`( `id` INT AUTO_INCREMENT COMMENT '主键ID', `role_id` INT NOT NULL COMMENT '角色ID', `auth_id` INT NOT NULL COMMENT '权限ID', PRIMARY KEY (`ID`) ) COMMENT '角色与权限的中间表'; -- 插入测试数据 INSERT INTO `cf_role_auth` VALUES (21, 1, 1); INSERT INTO `cf_role_auth` VALUES (22, 1, 2); INSERT INTO `cf_role_auth` VALUES (23, 1, 3); INSERT INTO `cf_role_auth` VALUES (24, 1, 4); INSERT INTO `cf_role_auth` VALUES (25, 1, 5); INSERT INTO `cf_role_auth` VALUES (26, 1, 6); INSERT INTO `cf_role_auth` VALUES (27, 1, 7); INSERT INTO `cf_role_auth` VALUES (28, 1, 8); INSERT INTO `cf_role_auth` VALUES (29, 1, 9); INSERT INTO `cf_role_auth` VALUES (30, 1, 10); INSERT INTO `cf_role_auth` VALUES (31, 1, 11); INSERT INTO `cf_role_auth` VALUES (32, 1, 12); INSERT INTO `cf_role_auth` VALUES (33, 1, 13); INSERT INTO `cf_role_auth` VALUES (34, 2, 1); INSERT INTO `cf_role_auth` VALUES (35, 2, 2); INSERT INTO `cf_role_auth` VALUES (36, 2, 3); INSERT INTO `cf_role_auth` VALUES (37, 2, 4); INSERT INTO `cf_role_auth` VALUES (38, 2, 5); INSERT INTO `cf_role_auth` VALUES (39, 2, 6); INSERT INTO `cf_role_auth` VALUES (40, 2, 7); INSERT INTO `cf_role_auth` VALUES (41, 2, 8); INSERT INTO `cf_role_auth` VALUES (42, 2, 9); INSERT INTO `cf_role_auth` VALUES (43, 2, 10); INSERT INTO `cf_role_auth` VALUES (44, 2, 11); INSERT INTO `cf_role_auth` VALUES (45, 2, 12); INSERT INTO `cf_role_auth` VALUES (46, 2, 13);