bash.sql 2.5 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354
  1. CREATE TABLE sys_user
  2. (
  3. id INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '用户的id',
  4. account VARCHAR(200) NOT NULL COMMENT '用户帐号',
  5. password VARCHAR(64) NOT NULL COMMENT '用户密码',
  6. email VARCHAR(64) NOT NULL COMMENT '用户邮箱',
  7. iphone VARCHAR(64) NULL DEFAULT NULL COMMENT '用户手机号',
  8. lastloginip VARCHAR(64) NULL DEFAULT NULL COMMENT '登陆IP',
  9. isactive INT NOT NULL DEFAULT '0' COMMENT '是否激活',
  10. IsDeleted INT NOT NULL DEFAULT '0' COMMENT '假删除',
  11. lastloginTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最后一次登陆时间',
  12. createtime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '用户创建时间',
  13. updatetime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  14. expirationtime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '用户的到期时间',
  15. INDEX account_index (account)
  16. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci;
  17. CREATE TABLE sys_role
  18. (
  19. id INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '角色的id',
  20. role_name VARCHAR(50) NOT NULL COMMENT '角色的名称',
  21. description VARCHAR(200) NOT NULL COMMENT '角色的描述',
  22. user_id INT NOT NULL COMMENT '用户id',
  23. INDEX user_index (user_id),
  24. FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE
  25. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci;
  26. CREATE TABLE sys_permission
  27. (
  28. id INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '权限的id',
  29. permission_name VARCHAR(50) NOT NULL COMMENT '权限的名称',
  30. description VARCHAR(200) NOT NULL COMMENT '权限的描述',
  31. role_id INT NOT NULL COMMENT '角色id',
  32. INDEX role_index (role_id),
  33. FOREIGN KEY (role_id) REFERENCES role (id) ON DELETE CASCADE ON UPDATE CASCADE
  34. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci;
  35. INSERT INTO role (role_name, description, user_id)
  36. VALUES ('admin', '管理员', 1),
  37. ('editor', '编辑员', 2),
  38. ('viewer', '观察员', 3),
  39. ('operator', '操作员', 4),
  40. ('guest', '游客', 5);
  41. INSERT INTO permission (permission_name, description, role_id)
  42. VALUES ('read', '读取权限', 1),
  43. ('write', '写入权限', 2),
  44. ('delete', '删除权限', 3),
  45. ('execute', '执行权限', 4),
  46. ('view', '查看权限', 5);