一、安装mysql数据。
安装mysql数据库,请参照http://blog.csdn.net/football98/article/details/50592646,这里不做赘述;
二、建立数据库
这里使用navicat进行mysql数据库操作。
二、新建表
create table tdictionary (
tdictionaryid int (16) not null comment 'id' auto_increment,type varchar(50) not null comment '类别',
code varchar(50) not null comment '值',
value varchar(50) not null comment '含义',
primary key(tdictionaryid),
unique(type,code)
)
comment '字典表';
create table tlogin
(
tloginid varchar(36) not null comment 'id' ,
loginname varchar(50) not null comment '用户登录名',
password varchar(50) not null comment '密码',
status int(1) default 1 not null comment '用户状态 1:可用 0:不可用',
username varchar(50) not null comment '用户姓名',
logindate datetime comment '登陆时间' ,
ifonline int(1) comment '是否在线',
registrationuser varchar(50) not null comment '注册人',
registrationtime datetime not null comment '注册人时间',
primary key(tloginid),
unique(loginname)
)
comment '用户表';
create table tpermission
(
tpermissionid varchar(36) not null comment '权限id',
permissionname varchar(30) not null comment '权限名',
action int(1) default 1 not null comment '动作,1:页面2:按钮',
url varchar(256) not null comment '动作的URL',
openicon varchar(20) comment '打开图标',
closeicon varchar(20) comment '关闭图标',
isleaf int(1) not null comment '是否叶子',
parentid varchar(36) not null comment '父ID',
orders int(2) not null comment '序号',
levels int(2) comment '等级',
opentype varchar(20) default 'right' comment 'right:右侧展示,blank弹出',
primary key(tpermissionid),
unique(permissionname)
)
comment '权限表';
create table trole
(
troleid varchar(36) not null comment '角色id',
rolename varchar(50) not null comment '角色名',
primary key(troleid),
unique(rolename)
)
comment '角色表';
create table troletpermission
(
troleid varchar(36) not null comment '角色id' ,
tpermissionid varchar(36) not null comment '权限id',
troletpermissionid varchar(36) not null comment 'id',
primary key(troletpermissionid),
constraint foreign key (tpermissionid) references tpermission (tpermissionid),
constraint foreign key (troleid) references trole (troleid)
)
comment '角色权限关联表';
create table tlogintrole
(
tloginid varchar(36) not null comment '用户id',
troleid varchar(36) not null comment '角色id',
tlogintroleid varchar(36) not null comment 'id',
primary key(tlogintroleid),
constraint foreign key (tloginid) references tlogin (tloginid),
constraint foreign key (troleid) references trole (troleid)
)
comment '用户权限关联表';
三、导入数据
tdictionary :
insert into tdictionary(type,code,value)
values ('ISLEAF','0','否'),('ISLEAF','1','是'),
('ACTION','1','页面'),('ACTION','2','按钮'),
('USABLE','1','可用'),('USABLE','2','不可用');
tlogin:
insert into tlogin(tloginid,loginname,password,status,username,registrationuser,registrationtime)
values ('e143b7c3-e8ab-464c-b7d6-4b80af0e65cc','superman','ACEF76419A89220398200FAB070E63A1',1,'超级管理员','superman',NOW());tpermission:
insert into tpermission(tpermissionid,permissionname,action,url,isleaf,parentid,orders)
values ('043817c4-d2e4-4156-b8ef-2561b14f275a','字典管理',1, 'pages/security/dictionary.jsp',1,'2eab2b1e-0543-42f7-9c73-95e5e5e57f74',10),
('113c5094-29bd-49dc-8d21-700187f7c5ba','角色管理',1, 'pages/security/role.jsp', 1,'b86b4e02-42dc-44d1-958d-d0e8ba97628b',20),
('2eab2b1e-0543-42f7-9c73-95e5e5e57f74','参数管理',1, 'pages/right.jsp', 0,'d3600285-b3a8-42dc-a99e-e97ff15fcf7c',20),
('3cc638a4-eaee-4c04-ba99-ec44a3a89aa0','用户管理',1, 'pages/security/user.jsp', 1,'b86b4e02-42dc-44d1-958d-d0e8ba97628b',10),
('95d94903-731d-4738-a863-04b3bbb22cef','权限管理',1, 'pages/security/permission.jsp',1,'b86b4e02-42dc-44d1-958d-d0e8ba97628b',30),
('b86b4e02-42dc-44d1-958d-d0e8ba97628b','系统管理',1, 'pages/right.jsp', 1,'d3600285-b3a8-42dc-a99e-e97ff15fcf7c',10),
('b89e6986-3262-464e-87d4-159b914480dd','系统参数管理',1,'pages/security/parameter.jsp', 1,'2eab2b1e-0543-42f7-9c73-95e5e5e57f74',20),
('d3600285-b3a8-42dc-a99e-e97ff15fcf7c','功能菜单',1, 'pages/right.jsp', 0,'0',1);
trole :
insert into trole(troleid,rolename) values ('655e72c5-5c5b-49af-9d77-c44f2d5cf5d0','系统管理员');
troletpermission:
insert into tlogintrole(tloginid,troleid,tlogintroleid)
values ('e143b7c3-e8ab-464c-b7d6-4b80af0e65cc','655e72c5-5c5b-49af-9d77-c44f2d5cf5d0','d554703d-84c8-4a3a-b531-76a572aeba54');
tlogintrole :
insert into troletpermission(troleid,tpermissionid,troletpermissionid)
values ('655e72c5-5c5b-49af-9d77-c44f2d5cf5d0','113c5094-29bd-49dc-8d21-700187f7c5ba','0bbdfd11-b071-474d-b602-7567f4d15992'),
('655e72c5-5c5b-49af-9d77-c44f2d5cf5d0','2eab2b1e-0543-42f7-9c73-95e5e5e57f74','3e2d1c9d-cf13-4299-8d86-ade6d85a639d'),
('655e72c5-5c5b-49af-9d77-c44f2d5cf5d0','b86b4e02-42dc-44d1-958d-d0e8ba97628b','3fe9196c-553c-4fe0-a4f9-085b47c53f45'),
('655e72c5-5c5b-49af-9d77-c44f2d5cf5d0','95d94903-731d-4738-a863-04b3bbb22cef','5faadf29-11d4-4de3-a749-872637e2a812'),
('655e72c5-5c5b-49af-9d77-c44f2d5cf5d0','3cc638a4-eaee-4c04-ba99-ec44a3a89aa0','662a87a2-c359-4566-bc83-b3e2e0081953'),
('655e72c5-5c5b-49af-9d77-c44f2d5cf5d0','b89e6986-3262-464e-87d4-159b914480dd','7d7c31fb-0298-402b-bb2f-088fa6e553b2'),
('655e72c5-5c5b-49af-9d77-c44f2d5cf5d0','043817c4-d2e4-4156-b8ef-2561b14f275a','97c0f3a2-57f9-4133-959d-67657e63a74d'),
('655e72c5-5c5b-49af-9d77-c44f2d5cf5d0','d3600285-b3a8-42dc-a99e-e97ff15fcf7c','e3da028d-d0b3-41fe-bb0f-6d52fea9fc1f');
那么到此次,基于Mysql的系统管理数据库设计就完成了。