shiro-test-ddl.sql
5.76 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for sys_user
-- ----------------------------
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user`
(
`user_id` bigint NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL COMMENT '登录用户名',
`name` varchar(128) DEFAULT NULL COMMENT '姓名',
`password` varchar(100) DEFAULT NULL COMMENT '密码',
`salt` varchar(20) DEFAULT NULL COMMENT '盐',
`email` varchar(100) DEFAULT NULL COMMENT '邮箱',
`mobile` varchar(100) DEFAULT NULL COMMENT '手机号',
`status` tinyint DEFAULT '1' COMMENT '状态 0:已注销 1: 正常',
`province_code` varchar(32) DEFAULT NULL COMMENT '所属省code码',
`department` varchar(200) DEFAULT NULL COMMENT '部门',
`create_by` varchar(50) DEFAULT 'system' COMMENT '创建人',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_by` varchar(50) DEFAULT 'system' COMMENT '更新人',
`updated_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`user_id`) USING BTREE,
UNIQUE KEY `username` (`username`) USING BTREE
) ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='用户表';
-- ----------------------------
-- Table structure for sys_user_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_user_role`;
CREATE TABLE `sys_user_role`
(
`id` bigint NOT NULL AUTO_INCREMENT,
`user_id` bigint DEFAULT NULL COMMENT '用户ID',
`role_id` bigint DEFAULT NULL COMMENT '角色ID',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='用户与角色对应关系';
-- ----------------------------
-- Table structure for sys_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role`
(
`role_id` bigint NOT NULL AUTO_INCREMENT,
`role_name` varchar(100) DEFAULT NULL COMMENT '角色名称',
`remark` varchar(100) DEFAULT NULL COMMENT '备注',
`create_user_id` bigint DEFAULT NULL COMMENT '创建者ID',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`role_id`) USING BTREE
) ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='角色';
-- ----------------------------
-- Table structure for sys_menu
-- ----------------------------
DROP TABLE IF EXISTS `sys_menu`;
CREATE TABLE `sys_menu`
(
`menu_id` bigint unsigned NOT NULL AUTO_INCREMENT,
`parent_id` bigint DEFAULT NULL COMMENT '父菜单ID，一级菜单为0',
`name` varchar(50) DEFAULT NULL COMMENT '菜单名称',
`url` varchar(1000) DEFAULT NULL COMMENT '菜单URL',
`perms` varchar(500) DEFAULT NULL COMMENT '授权(多个用逗号分隔，如：user:list,user:create)',
`type` int DEFAULT NULL COMMENT '类型 0：目录 1：菜单 2：按钮',
`icon` varchar(50) DEFAULT NULL COMMENT '菜单图标',
`order_num` int DEFAULT NULL COMMENT '排序',
PRIMARY KEY (`menu_id`) USING BTREE
) ENGINE = InnoDB
AUTO_INCREMENT = 5
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='菜单';
-- ----------------------------
-- Table structure for sys_role_menu
-- ----------------------------
DROP TABLE IF EXISTS `sys_role_menu`;
CREATE TABLE `sys_role_menu`
(
`id` bigint NOT NULL AUTO_INCREMENT,
`role_id` bigint DEFAULT NULL COMMENT '角色ID',
`menu_id` bigint DEFAULT NULL COMMENT '菜单ID',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
AUTO_INCREMENT = 6
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='角色与菜单对应关系';
-- ----------------------------
-- Table structure for sys_dg
-- ----------------------------
DROP TABLE IF EXISTS `sys_dg`;
CREATE TABLE `sys_dg`
(
`id` int NOT NULL AUTO_INCREMENT COMMENT '数据表格id',
`dg_key` varchar(30) NOT NULL COMMENT '数据表格唯一标识， 后端查询方法必须与此一致',
`columns` varchar(200) NOT NULL COMMENT '数据表格字段列表，以逗号分割',
PRIMARY KEY (`id`)
) ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='数据表格表';
-- ----------------------------
-- Table structure for sys_role_dg
-- ----------------------------
DROP TABLE IF EXISTS `sys_role_dg`;
CREATE TABLE `sys_role_dg`
(
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
`role_id` bigint NOT NULL COMMENT '角色id',
`dg_id` bigint NOT NULL COMMENT '数据表格id',
`columns` varchar(200) NOT NULL COMMENT '可显示的数据表格列',
PRIMARY KEY (`id`)
) ENGINE = InnoDB
AUTO_INCREMENT = 5
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='角色权限数据表格关联表';
-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '姓名',
`salary` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '薪水',
`address` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '地址',
PRIMARY KEY (`id`)
) ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci COMMENT ='雇员表';
SET FOREIGN_KEY_CHECKS = 1;