java department_department.sql

最新推荐文章于 2022-08-13 22:34:24 发布

南风寺山

最新推荐文章于 2022-08-13 22:34:24 发布

阅读量220

点赞数

CC 4.0 BY-SA版权

文章标签： java department

本文链接：https://blog.youkuaiyun.com/weixin_42602726/article/details/115020299

本文详细介绍了数据库中商品信息及用户权限的管理方式，包括商品表、权限表、角色表等的设计，并展示了如何通过角色权限分配实现不同用户的操作限制。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Navicat Premium Data Transfer

Source Server : mysql

Source Server Type : MySQL

Source Server Version : 80016

Source Host : localhost:3306

Source Schema : department

Target Server Type : MySQL

Target Server Version : 80016

File Encoding : 65001

Date: 02/10/2019 15:41:11

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------

-- Table structure for item

-- ----------------------------

DROP TABLE IF EXISTS `item`;

CREATE TABLE `item` (

`id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

`item_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商品名称',

`item_count` int(11) NULL DEFAULT 0 COMMENT '商品数量',

`item_price` double NULL DEFAULT NULL COMMENT '商品价格',

`item_desc` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '商品描述',

PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of item

-- ----------------------------

INSERT INTO `item` VALUES ('3df5fc2bd6114ac0ad884e568ade7cfc', '马桶', 10, 1000, '全友家私马桶,陶瓷');

INSERT INTO `item` VALUES ('ff2da08dfd29425cabce8cc2d6064df0', '花洒', 10, 200, '全友家私花洒，全铜');

-- ----------------------------

-- Table structure for permission

-- ----------------------------

DROP TABLE IF EXISTS `permission`;

CREATE TABLE `permission` (

`id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

`permission` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '权限',

PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '权限表' ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of permission

-- ----------------------------

INSERT INTO `permission` VALUES ('0b31eb21e04911e98122d8c497336b46', 'edit');

INSERT INTO `permission` VALUES ('0e1b41dbe04911e98122d8c497336b46', 'delete');

INSERT INTO `permission` VALUES ('f153c298e04811e98122d8c497336b46', 'add');

-- ----------------------------

-- Table structure for role

-- ----------------------------

DROP TABLE IF EXISTS `role`;

CREATE TABLE `role` (

`id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

`role_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '角色名',

PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of role

-- ----------------------------

INSERT INTO `role` VALUES ('13d46c00e04911e98122d8c497336b46', 'admin');

INSERT INTO `role` VALUES ('15a3fcd9e04911e98122d8c497336b46', 'user');

-- ----------------------------

-- Table structure for role_permission

-- ----------------------------

DROP TABLE IF EXISTS `role_permission`;

CREATE TABLE `role_permission` (

`id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'id',

`role_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '角色id',

`permission_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '权限id',

PRIMARY KEY (`id`) USING BTREE,

UNIQUE INDEX `role_permission_id_uk`(`role_id`, `permission_id`) USING BTREE,

INDEX `role_permission_perm_id_fk`(`permission_id`) USING BTREE,

CONSTRAINT `role_permission_perm_id_fk` FOREIGN KEY (`permission_id`) REFERENCES `permission` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,

CONSTRAINT `role_permission_role_id_fk` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of role_permission

-- ----------------------------

INSERT INTO `role_permission` VALUES ('c5eaddbce0df11e98122d8c497336b46', '13d46c00e04911e98122d8c497336b46', '0b31eb21e04911e98122d8c497336b46');

INSERT INTO `role_permission` VALUES ('d2d3c2cce0df11e98122d8c497336b46', '13d46c00e04911e98122d8c497336b46', '0e1b41dbe04911e98122d8c497336b46');

INSERT INTO `role_permission` VALUES ('da747527e0df11e98122d8c497336b46', '13d46c00e04911e98122d8c497336b46', 'f153c298e04811e98122d8c497336b46');

-- ----------------------------

-- Table structure for user_info

-- ----------------------------

DROP TABLE IF EXISTS `user_info`;

CREATE TABLE `user_info` (

`id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

`user_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',

`password` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '密码',

`question` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

`answer` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

PRIMARY KEY (`id`) USING BTREE,

UNIQUE INDEX `user_name_unqiue`(`user_name`) USING BTREE,

UNIQUE INDEX `user_name_unique`(`user_name`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of user_info

-- ----------------------------

INSERT INTO `user_info` VALUES ('10e1850867b24096a838257e2989a9b2', 'user', '5cc32e366c87c4cb49e4309b75f57d64', NULL, NULL);

INSERT INTO `user_info` VALUES ('4a13ae92153f4ddcb1a9f7a3e9e6362d', 'admin', 'f6fdffe48c908deb0f4c3bd36c032e72', NULL, NULL);

INSERT INTO `user_info` VALUES ('f45fbe0944e64a47853954ec4e5304ab', 'wujiacong', '92be8927145b52370a3dce4987a76f83', '问题1', 'aaaa');

-- ----------------------------

-- Table structure for user_role

-- ----------------------------

DROP TABLE IF EXISTS `user_role`;

CREATE TABLE `user_role` (

`id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'id',

`user_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户id',

`role_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '角色id',

PRIMARY KEY (`id`) USING BTREE,

UNIQUE INDEX `user_role_id_uk`(`user_id`, `role_id`) USING BTREE,

INDEX `role_id`(`role_id`) USING BTREE,

CONSTRAINT `user_role_role_id_fk` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,

CONSTRAINT `user_role_user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `user_info` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of user_role

-- ----------------------------

INSERT INTO `user_role` VALUES ('6bc739f5e0df11e98122d8c497336b46', '10e1850867b24096a838257e2989a9b2', '15a3fcd9e04911e98122d8c497336b46');

INSERT INTO `user_role` VALUES ('800b6855e0df11e98122d8c497336b46', '4a13ae92153f4ddcb1a9f7a3e9e6362d', '13d46c00e04911e98122d8c497336b46');

SET FOREIGN_KEY_CHECKS = 1;

一键复制

编辑

Web IDE

原始数据

按行查看

历史