123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354 |
- CREATE TABLE sys_user
- (
- id INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '用户的id',
- account VARCHAR(200) NOT NULL COMMENT '用户帐号',
- password VARCHAR(64) NOT NULL COMMENT '用户密码',
- email VARCHAR(64) NOT NULL COMMENT '用户邮箱',
- iphone VARCHAR(64) NULL DEFAULT NULL COMMENT '用户手机号',
- lastloginip VARCHAR(64) NULL DEFAULT NULL COMMENT '登陆IP',
- isactive INT NOT NULL DEFAULT '0' COMMENT '是否激活',
- IsDeleted INT NOT NULL DEFAULT '0' COMMENT '假删除',
- lastloginTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最后一次登陆时间',
- createtime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '用户创建时间',
- updatetime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
- expirationtime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '用户的到期时间',
- INDEX account_index (account)
- ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci;
- CREATE TABLE sys_role
- (
- id INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '角色的id',
- role_name VARCHAR(50) NOT NULL COMMENT '角色的名称',
- description VARCHAR(200) NOT NULL COMMENT '角色的描述',
- user_id INT NOT NULL COMMENT '用户id',
- INDEX user_index (user_id),
- FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE
- ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci;
- CREATE TABLE sys_permission
- (
- id INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '权限的id',
- permission_name VARCHAR(50) NOT NULL COMMENT '权限的名称',
- description VARCHAR(200) NOT NULL COMMENT '权限的描述',
- role_id INT NOT NULL COMMENT '角色id',
- INDEX role_index (role_id),
- FOREIGN KEY (role_id) REFERENCES role (id) ON DELETE CASCADE ON UPDATE CASCADE
- ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci;
- INSERT INTO role (role_name, description, user_id)
- VALUES ('admin', '管理员', 1),
- ('editor', '编辑员', 2),
- ('viewer', '观察员', 3),
- ('operator', '操作员', 4),
- ('guest', '游客', 5);
- INSERT INTO permission (permission_name, description, role_id)
- VALUES ('read', '读取权限', 1),
- ('write', '写入权限', 2),
- ('delete', '删除权限', 3),
- ('execute', '执行权限', 4),
- ('view', '查看权限', 5);
|