初始化SQL脚本

听钱塘信起

已于 2022-12-04 21:59:11 修改

阅读量641

点赞数 3

分类专栏： springboot MYSQL数据库文章标签： sql 数据库 java

于 2022-11-28 01:54:44 首次发布

本文链接：https://blog.csdn.net/chenyunjiangNN/article/details/128072764

版权

springboot 同时被 2 个专栏收录

13 篇文章 6 订阅

订阅专栏

MYSQL数据库

3 篇文章 0 订阅

订阅专栏

整合Shiro的初始化SQL脚本

整合Security的初始化SQL脚本

整合Shiro的初始化SQL脚本

-- boot_mybatis.t_permission definition

CREATE TABLE `t_permission` (
  `p_id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(30) NOT NULL COMMENT '名称',
  `permission` varchar(255) DEFAULT NULL COMMENT '权限',
  `status` varchar(255) DEFAULT '0' COMMENT '状态 0:正常 1:删除',
  PRIMARY KEY (`p_id`)
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8mb3;


-- boot_mybatis.t_role definition

CREATE TABLE `t_role` (
  `r_id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(30) NOT NULL COMMENT '角色名称',
  `status` varchar(30) DEFAULT NULL COMMENT '状态： 0:正常 1:删除 2:未启用',
  PRIMARY KEY (`r_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb3;


-- boot_mybatis.t_user definition

CREATE TABLE `t_user` (
  `u_id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_name` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '用户登录名',
  `email` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '邮箱',
  `pass_word` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '密码',
  `birth` date DEFAULT NULL COMMENT '生日',
  `gender` bigint NOT NULL DEFAULT '2' COMMENT '性别，0：男，1：女，2：保密',
  `salt` varchar(10) DEFAULT NULL COMMENT '随机盐',
  PRIMARY KEY (`u_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=89 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC;

-- boot_security.tb_user definition

CREATE TABLE `tb_user` (
  `u_id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_name` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '用户登录名',
  `nick_name` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '用户昵称',
  `email` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '邮箱',
  `pass_word` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '密码',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标识，0：未删除，1，已删除',
  `birth` date DEFAULT NULL COMMENT '生日',
  `gender` bigint NOT NULL DEFAULT '2' COMMENT '性别，0：男，1：女，2：保密',
  `version` int NOT NULL DEFAULT '1' COMMENT '乐观锁标识',
  PRIMARY KEY (`u_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=94 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC COMMENT='用户表';


-- boot_mybatis.t_role_permission definition

CREATE TABLE `t_role_permission` (
  `r_id` int NOT NULL COMMENT '角色id',
  `p_id` int NOT NULL COMMENT '权限id',
  KEY `r_id` (`r_id`),
  KEY `p_id` (`p_id`),
  CONSTRAINT `t_role_permission_ibfk_1` FOREIGN KEY (`r_id`) REFERENCES `t_role` (`r_id`),
  CONSTRAINT `t_role_permission_ibfk_2` FOREIGN KEY (`p_id`) REFERENCES `t_permission` (`p_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;


-- boot_mybatis.t_user_role definition

CREATE TABLE `t_user_role` (
  `u_id` int NOT NULL COMMENT '用户id',
  `r_id` int NOT NULL COMMENT '角色id',
  KEY `r_id` (`r_id`),
  KEY `u_id` (`u_id`),
  CONSTRAINT `t_user_role_ibfk_1` FOREIGN KEY (`r_id`) REFERENCES `t_role` (`r_id`),
  CONSTRAINT `t_user_role_ibfk_2` FOREIGN KEY (`u_id`) REFERENCES `t_user` (`u_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;




INSERT INTO boot_mybatis.t_user (u_id, user_name, email, pass_word, birth, gender, salt) VALUES(81, '李四', '456@qq.cpm', 'e515cf2846ca5a8206e0f27150fb83c0', '2022-10-02', 0, 'fp8o7i');
INSERT INTO boot_mybatis.t_user (u_id, user_name, email, pass_word, birth, gender, salt) VALUES(82, '张三', '45679@qq.cpm', 'e515cf2846ca5a8206e0f27150fb83c0', '2022-10-02', 0, 'fp8o7i');
INSERT INTO boot_mybatis.t_user (u_id, user_name, email, pass_word, birth, gender, salt) VALUES(88, '李白', '1234@qq.com', 'e515cf2846ca5a8206e0f27150fb83c0', '2022-11-21', 1, 'fp8o7i');


INSERT INTO boot_mybatis.t_role (r_id, name, status) VALUES(11, 'admin', '1');
INSERT INTO boot_mybatis.t_role (r_id, name, status) VALUES(12, 'user', '1');


INSERT INTO boot_mybatis.t_permission (p_id, name, permission, status) VALUES(43, '新增', 'add', '0');
INSERT INTO boot_mybatis.t_permission (p_id, name, permission, status) VALUES(44, '修改', 'update', '0');


INSERT INTO boot_mybatis.t_role_permission (r_id, p_id) VALUES(11, 43);
INSERT INTO boot_mybatis.t_role_permission (r_id, p_id) VALUES(12, 44);


INSERT INTO boot_mybatis.t_user_role (u_id, r_id) VALUES(81, 11);
INSERT INTO boot_mybatis.t_user_role (u_id, r_id) VALUES(82, 12);

整合Security的初始化SQL脚本

-- boot_security.tb_permission definition

CREATE TABLE `tb_permission` (
  `p_id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `perm_name` varchar(30) NOT NULL COMMENT '权限名称',
  `perm_code` varchar(30) DEFAULT NULL COMMENT '权限编码',
  `status` varchar(30) DEFAULT '0' COMMENT '状态 0:正常 1:删除',
  `version` int DEFAULT NULL COMMENT '乐观锁标识',
  PRIMARY KEY (`p_id`)
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8mb3 COMMENT='权限表';


-- boot_security.tb_role definition

CREATE TABLE `tb_role` (
  `r_id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `role_name` varchar(30) NOT NULL COMMENT '角色名称',
  `role_code` varchar(30) NOT NULL COMMENT '角色编码',
  `status` varchar(30) DEFAULT NULL COMMENT '状态： 0:正常 1:删除 2:未启用',
  `version` int DEFAULT NULL COMMENT '乐观锁标识',
  PRIMARY KEY (`r_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb3 COMMENT='角色表';


-- boot_security.tb_user definition

CREATE TABLE `tb_user` (
  `u_id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_name` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '用户登录名',
  `nick_name` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '用户昵称',
  `email` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '邮箱',
  `pass_word` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '密码',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标识，0：未删除，1，已删除',
  `birth` date DEFAULT NULL COMMENT '生日',
  `gender` bigint NOT NULL DEFAULT '2' COMMENT '性别，0：男，1：女，2：保密',
  `version` int NOT NULL DEFAULT '1' COMMENT '乐观锁标识',
  PRIMARY KEY (`u_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=94 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC COMMENT='用户表';


-- boot_security.t_role_permission definition

CREATE TABLE `t_role_permission` (
  `r_id` int NOT NULL COMMENT '角色id',
  `p_id` int NOT NULL COMMENT '权限id',
  `version` int DEFAULT NULL COMMENT '乐观锁标识',
  KEY `r_id` (`r_id`),
  KEY `p_id` (`p_id`),
  CONSTRAINT `t_role_permission_ibfk_1` FOREIGN KEY (`r_id`) REFERENCES `tb_role` (`r_id`),
  CONSTRAINT `t_role_permission_ibfk_2` FOREIGN KEY (`p_id`) REFERENCES `tb_permission` (`p_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='角色权限表';


-- boot_security.t_user_role definition

CREATE TABLE `t_user_role` (
  `u_id` int NOT NULL COMMENT '用户id',
  `r_id` int NOT NULL COMMENT '角色id',
  `version` int DEFAULT NULL COMMENT '乐观锁标识',
  KEY `r_id` (`r_id`),
  KEY `u_id` (`u_id`),
  CONSTRAINT `t_user_role_ibfk_1` FOREIGN KEY (`r_id`) REFERENCES `tb_role` (`r_id`),
  CONSTRAINT `t_user_role_ibfk_2` FOREIGN KEY (`u_id`) REFERENCES `tb_user` (`u_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='用户角色表';





INSERT INTO boot_security.tb_user (u_id, user_name, nick_name, email, pass_word, del_flag, birth, gender, version) VALUES(88, '张三', 'zs', '123@qq.com', '$2a$10$DXRF73bzzZDO2WHT2sWS2u4cEXu/fHMSFwg55ZEOh2x6ezUkevtqm', 0, NULL, 2, 1);
INSERT INTO boot_security.tb_user (u_id, user_name, nick_name, email, pass_word, del_flag, birth, gender, version) VALUES(89, '李四', 'ls', '456@qq.com', '{noop}456', 0, NULL, 2, 1);

INSERT INTO boot_security.tb_role (r_id, role_name, role_code, status, version) VALUES(13, '管理员', 'admin', '0', 0);
INSERT INTO boot_security.tb_role (r_id, role_name, role_code, status, version) VALUES(14, '普通用户', 'user', '0', 0);

INSERT INTO boot_security.tb_permission (p_id, perm_name, perm_code, status, version) VALUES(45, '添加信息权限', 'add', '0', 0);
INSERT INTO boot_security.tb_permission (p_id, perm_name, perm_code, status, version) VALUES(46, '删除信息权限', 'delete', '0', 0);
INSERT INTO boot_security.tb_permission (p_id, perm_name, perm_code, status, version) VALUES(47, '修改信息权限', 'add', '0', 0);
INSERT INTO boot_security.tb_permission (p_id, perm_name, perm_code, status, version) VALUES(48, '查询信息权限', 'select', '0', 0);

INSERT INTO boot_security.t_user_role (u_id, r_id, version) VALUES(88, 13, 0);
INSERT INTO boot_security.t_user_role (u_id, r_id, version) VALUES(89, 14, 0);

INSERT INTO boot_security.t_role_permission (r_id, p_id, version) VALUES(13, 45, 0);
INSERT INTO boot_security.t_role_permission (r_id, p_id, version) VALUES(13, 46, 0);
INSERT INTO boot_security.t_role_permission (r_id, p_id, version) VALUES(13, 47, 0);
INSERT INTO boot_security.t_role_permission (r_id, p_id, version) VALUES(13, 47, 0);
INSERT INTO boot_security.t_role_permission (r_id, p_id, version) VALUES(14, 48, 0);