数据库总结三

以角色、用户、权限、模块为例。理解他们之间的关系。

一，目的

--针对大型企业中不同用户登录系统时，所显示的模块也是根据该用户的权限决定。

--每个用户含有不同角色，同理一个角色可以被多个用户共有

--每个用户，含有的角色的权限并不一定要相同，所以不同用户相同角色的权限可以完全不同

二，模型结构

--角色表rose

roseid  number（14）

rosename  varchar2（50）

--用户表user

userid number（14）

username  varchar2（50）

--角色与用户中间表rose2user（角色与用户之间是多对多的关系，这里通过中间表，来建立）

rose2userid  number(14)

roseid number(14)

userid number（14）

--权限表rights

rightid number(14)

rightname varchar2(50)

righturl varchar2(50)

--用户与权限的中间表

user2rightid number(14)

userid number(14)

rightid number(14)

--模块表module(模块与权限之间是一对多的关系)

moduleid number(14)

modulename varchar2(50)

moduleurl varchar2(50)

rightid number(14)

三，数据库表结构（这里用的是mysql环境下的表）

---创建数据库
create database liucg default charset GBK;
use liucg;
set names gbk;
---角色表
create table rose(
roseid int(14) primary key,
rosename varchar(50) not null unique
);
---用户表
create table user(
userid int(14) primary key,
username varchar(50) not null
)
DEFAULT CHARSET=gbk;
---角色与用户中间表
create table rose2user(
r2uid int(14) primary key,
roseid int(14),
userid int(14)
);
alter table rose2user add constraint rosefkrose2user foreign key(roseid) references rose(roseid);
alter table rose2user add constraint userfkrose2user foreign key(userid) references user(userid);
---权限表
create table qright(
rightid int(14) primary key,
rightname varchar(50) not null,
righturl varchar(200) not null default'http://www.baicu.com'
);
---权限与用户中间表
create table user2right(
u2rid int(14) primary key,
userid int(14),
rightid int(14)
);
alter table user2right add constraint userfkuser2right foreign key(userid) references user(userid);
alter table user2right add constraint rightfkuser2right foreign key(userid) references qright(rightid);
---模块表
create table module(
moduleid int(14),
modulename varchar(50) not null,
moduleurl varchar(200) not null default'http:\\www.google.com',
rightid int(14)
);
alter table module add constraint modulefkright foreign key(rightid) references qright(rightid);

四，插入数据

-- 管理员表数据
insert into user values(1,'张三');
insert into user values(2,'李四');
insert into user values(3,'王五');
insert into user values(4,'钱六');
insert into user values(5,'赵大');
insert into user values(6,'孙二');
insert into user values(7,'李八');
insert into user values(8,'周五');
insert into user values(9,'吴二');
insert into user values(10,'郑久');
insert into user values(11,'冯三');
insert into user values(12,'楚六');
commit;

-- 角色表数据
insert into rose values(1,'管理员');
insert into rose values(2,'财务总监');
insert into rose values(3,'人事总监');
insert into rose values(4,'审计总监');
insert into rose values(5,'办公室秘书');
insert into rose values(6,'普通员工');
commit;

---角色与用户中间表
insert into rose2user(r2uid,roseid,userid) values(1,1,5);
insert into rose2user(r2uid,roseid,userid) values(2,2,4);
insert into rose2user(r2uid,roseid,userid) values(3,3,3);
insert into rose2user(r2uid,roseid,userid) values(4,4,1);
insert into rose2user(r2uid,roseid,userid) values(5,5,12);
insert into rose2user(r2uid,roseid,userid) values(6,6,11);
insert into rose2user(r2uid,roseid,userid) values(7,6,10);
insert into rose2user(r2uid,roseid,userid) values(8,5,9);
insert into rose2user(r2uid,roseid,userid) values(9,4,8);
insert into rose2user(r2uid,roseid,userid) values(10,3,7);
insert into rose2user(r2uid,roseid,userid) values(11,3,6);
insert into rose2user(r2uid,roseid,userid) values(12,2,5);
insert into rose2user(r2uid,roseid,userid) values(13,1,4);
insert into rose2user(r2uid,roseid,userid) values(14,2,3);
insert into rose2user(r2uid,roseid,userid) values(15,4,2);
insert into rose2user(r2uid,roseid,userid) values(16,6,1);
commit;

---权限表
-- 添加8个基本权限。
insert into qright (rightid,rightname,righturl) values(1,'权限浏览','/listrights.do?type=1');
insert into qright (rightid,rightname,righturl) values(2,'权限增加','/toaddrights.do');
insert into qright (rightid,rightname,righturl) values(3,'权限修改','/listrights.do?type=2');
insert into qright (rightid,rightname,righturl) values(4,'权限删除','/listrights.do?type=3');
insert into qright (rightid,rightname,righturl) values(5,'角色浏览','/listroles.do?type=1');
insert into qright (rightid,rightname,righturl) values(6,'角色增加','/toaddrole.do');
insert into qright (rightid,rightname,righturl) values(7,'角色修改','/listroles.do?type=2');
insert into qright (rightid,rightname,righturl) values(8,'角色删除','/listroles.do?type=3');

-- 添加管理员管理权限
insert into qright (rightid,rightname,righturl) values(9,'个人信息修改','/updself.do');
insert into qright (rightid,rightname,righturl) values(10,'管理员浏览','/listadmins.do?type=1');
insert into qright (rightid,rightname,righturl) values(11,'管理员增加','/toaddadmin.do');
insert into qright (rightid,rightname,righturl) values(12,'管理员修改','/listadmins.do?type=2');
insert into qright (rightid,rightname,righturl) values(13,'管理员删除','/listadmins.do?type=3');

-- 资费管理权限
insert into qright (rightid,rightname,righturl) values(14,'资费浏览','/listpricing.do?type=1');
insert into qright (rightid,rightname,righturl) values(15,'资费增加','/toaddpricing.do');
insert into qright (rightid,rightname,righturl) values(16,'资费修改','/listpricing.do?type=2');
insert into qright (rightid,rightname,righturl) values(17,'资费删除','/listpricing.do?type=3');
-- 用户管理权限
insert into qright (rightid,rightname,righturl) values(18,'帐务帐号查询','/listuser.do?type=1');
insert into qright (rightid,rightname,righturl) values(19,'帐务帐号增加','/toadduser.do');
insert into qright (rightid,rightname,righturl) values(20,'帐务帐号修改','/listuser.do?type=2');
insert into qright (rightid,rightname,righturl) values(21,'帐务帐号删除','/listuser.do?type=3');
insert into qright (rightid,rightname,righturl) values(22,'业务帐号查询','/listus.do?type=1');
insert into qright (rightid,rightname,righturl) values(23,'业务帐号增加','/toaddus.do');
insert into qright (rightid,rightname,righturl) values(24,'业务帐号修改','/listus.do?type=2');
insert into qright (rightid,rightname,righturl) values(25,'业务帐号删除','/listus.do?type=3');

-- 帐务管理权限
insert into qright (rightid,rightname,righturl) values(26,'帐务年查询','/toqryacctyear.do');
insert into qright (rightid,rightname,righturl) values(27,'帐务月查询','/toqryacctmonth.do');

-- 账单管理权限
insert into qright (rightid,rightname,righturl) values(28,'帐单查询','/toqryreckon.do');

--用户自服务
insert into qright (rightid,rightname,righturl) values(29,'用户帐单查询','/toqrybill');
insert into qright (rightid,rightname,righturl) values(30,'个人信息修改','/toupduserinfo');
commit;

-- 角色权限，超级管理员所有权限
insert into user2right(u2rid,userid,rightid) values(1,1,29);
insert into user2right(u2rid,userid,rightid) values(2,1,30);
insert into user2right(u2rid,userid,rightid) values(3,1,18);
insert into user2right(u2rid,userid,rightid) values(4,1,22);
insert into user2right(u2rid,userid,rightid) values(5,1,26);
insert into user2right(u2rid,userid,rightid) values(6,1,27);
insert into user2right(u2rid,userid,rightid) values(7,2,18);
insert into user2right(u2rid,userid,rightid) values(8,2,22);
insert into user2right(u2rid,userid,rightid) values(9,2,26);
insert into user2right(u2rid,userid,rightid) values(10,2,27);
insert into user2right(u2rid,userid,rightid) values(11,3,19);
insert into user2right(u2rid,userid,rightid) values(12,3,20);
insert into user2right(u2rid,userid,rightid) values(13,3,21);
insert into user2right(u2rid,userid,rightid) values(14,3,28);
insert into user2right(u2rid,userid,rightid) values(15,3,1);
insert into user2right(u2rid,userid,rightid) values(16,3,2);
insert into user2right(u2rid,userid,rightid) values(17,3,3);
insert into user2right(u2rid,userid,rightid) values(18,3,4);
insert into user2right(u2rid,userid,rightid) values(19,3,5);
insert into user2right(u2rid,userid,rightid) values(20,3,6);
insert into user2right(u2rid,userid,rightid) values(21,3,7);
insert into user2right(u2rid,userid,rightid) values(22,3,8);
insert into user2right(u2rid,userid,rightid) values(23,3,9);
insert into user2right(u2rid,userid,rightid) values(24,3,10);
insert into user2right(u2rid,userid,rightid) values(25,3,11);
insert into user2right(u2rid,userid,rightid) values(26,3,12);
insert into user2right(u2rid,userid,rightid) values(27,3,13);
insert into user2right(u2rid,userid,rightid) values(28,4,1);
insert into user2right(u2rid,userid,rightid) values(29,4,2);
insert into user2right(u2rid,userid,rightid) values(30,4,3);
insert into user2right(u2rid,userid,rightid) values(31,4,4);
insert into user2right(u2rid,userid,rightid) values(32,4,5);
insert into user2right(u2rid,userid,rightid) values(33,4,6);
insert into user2right(u2rid,userid,rightid) values(34,4,7);
insert into user2right(u2rid,userid,rightid) values(35,4,8);
insert into user2right(u2rid,userid,rightid) values(36,4,9);
insert into user2right(u2rid,userid,rightid) values(37,4,10);
insert into user2right(u2rid,userid,rightid) values(38,4,11);
insert into user2right(u2rid,userid,rightid) values(39,4,12);
insert into user2right(u2rid,userid,rightid) values(40,4,13);
insert into user2right(u2rid,userid,rightid) values(41,4,14);
insert into user2right(u2rid,userid,rightid) values(42,4,15);
insert into user2right(u2rid,userid,rightid) values(43,4,16);
insert into user2right(u2rid,userid,rightid) values(44,4,17);
insert into user2right(u2rid,userid,rightid) values(45,4,18);
insert into user2right(u2rid,userid,rightid) values(46,4,19);
insert into user2right(u2rid,userid,rightid) values(47,4,20);
insert into user2right(u2rid,userid,rightid) values(48,4,21);
insert into user2right(u2rid,userid,rightid) values(49,4,22);
insert into user2right(u2rid,userid,rightid) values(50,4,23);
insert into user2right(u2rid,userid,rightid) values(51,4,24);
insert into user2right(u2rid,userid,rightid) values(52,4,25);
insert into user2right(u2rid,userid,rightid) values(53,4,26);
insert into user2right(u2rid,userid,rightid) values(54,4,27);
insert into user2right(u2rid,userid,rightid) values(55,4,28);
insert into user2right(u2rid,userid,rightid) values(56,4,29);
insert into user2right(u2rid,userid,rightid) values(57,4,30);
insert into user2right(u2rid,userid,rightid) values(58,5,1);
insert into user2right(u2rid,userid,rightid) values(59,5,2);
insert into user2right(u2rid,userid,rightid) values(60,5,3);
insert into user2right(u2rid,userid,rightid) values(61,5,4);
insert into user2right(u2rid,userid,rightid) values(62,5,5);
insert into user2right(u2rid,userid,rightid) values(63,5,6);
insert into user2right(u2rid,userid,rightid) values(64,5,7);
insert into user2right(u2rid,userid,rightid) values(65,5,8);
insert into user2right(u2rid,userid,rightid) values(66,5,9);
insert into user2right(u2rid,userid,rightid) values(67,5,10);
insert into user2right(u2rid,userid,rightid) values(68,5,11);
insert into user2right(u2rid,userid,rightid) values(69,5,12);
insert into user2right(u2rid,userid,rightid) values(70,5,13);
insert into user2right(u2rid,userid,rightid) values(71,5,14);
insert into user2right(u2rid,userid,rightid) values(72,5,15);
insert into user2right(u2rid,userid,rightid) values(73,5,16);
insert into user2right(u2rid,userid,rightid) values(74,5,17);
insert into user2right(u2rid,userid,rightid) values(75,5,18);
insert into user2right(u2rid,userid,rightid) values(76,5,19);
insert into user2right(u2rid,userid,rightid) values(77,5,20);
insert into user2right(u2rid,userid,rightid) values(78,5,21);
insert into user2right(u2rid,userid,rightid) values(79,5,22);
insert into user2right(u2rid,userid,rightid) values(80,5,23);
insert into user2right(u2rid,userid,rightid) values(81,5,24);
insert into user2right(u2rid,userid,rightid) values(82,5,25);
insert into user2right(u2rid,userid,rightid) values(83,5,26);
insert into user2right(u2rid,userid,rightid) values(84,5,27);
insert into user2right(u2rid,userid,rightid) values(85,5,28);
insert into user2right(u2rid,userid,rightid) values(86,5,29);
insert into user2right(u2rid,userid,rightid) values(87,5,30);
insert into user2right(u2rid,userid,rightid) values(88,6,1);
insert into user2right(u2rid,userid,rightid) values(89,6,2);
insert into user2right(u2rid,userid,rightid) values(90,6,3);
insert into user2right(u2rid,userid,rightid) values(91,6,4);
insert into user2right(u2rid,userid,rightid) values(92,6,5);
insert into user2right(u2rid,userid,rightid) values(93,6,6);
insert into user2right(u2rid,userid,rightid) values(94,6,7);
insert into user2right(u2rid,userid,rightid) values(95,6,8);
insert into user2right(u2rid,userid,rightid) values(96,6,9);
insert into user2right(u2rid,userid,rightid) values(97,6,10);
insert into user2right(u2rid,userid,rightid) values(98,6,11);
insert into user2right(u2rid,userid,rightid) values(99,6,12);
insert into user2right(u2rid,userid,rightid) values(100,6,13);
insert into user2right(u2rid,userid,rightid) values(101,7,1);
insert into user2right(u2rid,userid,rightid) values(102,7,2);
insert into user2right(u2rid,userid,rightid) values(103,7,3);
insert into user2right(u2rid,userid,rightid) values(104,7,4);
insert into user2right(u2rid,userid,rightid) values(105,7,5);
insert into user2right(u2rid,userid,rightid) values(106,7,6);
insert into user2right(u2rid,userid,rightid) values(107,7,7);
insert into user2right(u2rid,userid,rightid) values(108,7,8);
insert into user2right(u2rid,userid,rightid) values(109,7,9);
insert into user2right(u2rid,userid,rightid) values(110,7,10);
insert into user2right(u2rid,userid,rightid) values(111,7,11);
insert into user2right(u2rid,userid,rightid) values(112,7,12);
insert into user2right(u2rid,userid,rightid) values(113,7,13);
insert into user2right(u2rid,userid,rightid) values(114,8,18);
insert into user2right(u2rid,userid,rightid) values(115,8,22);
insert into user2right(u2rid,userid,rightid) values(116,8,26);
insert into user2right(u2rid,userid,rightid) values(117,8,27);
insert into user2right(u2rid,userid,rightid) values(118,9,14);
insert into user2right(u2rid,userid,rightid) values(119,9,15);
insert into user2right(u2rid,userid,rightid) values(120,9,16);
insert into user2right(u2rid,userid,rightid) values(121,9,17);
insert into user2right(u2rid,userid,rightid) values(122,9,22);
insert into user2right(u2rid,userid,rightid) values(123,9,23);
insert into user2right(u2rid,userid,rightid) values(124,9,24);
insert into user2right(u2rid,userid,rightid) values(125,9,25);
insert into user2right(u2rid,userid,rightid) values(126,10,29);
insert into user2right(u2rid,userid,rightid) values(127,10,30);
insert into user2right(u2rid,userid,rightid) values(128,11,29);
insert into user2right(u2rid,userid,rightid) values(129,11,30);
insert into user2right(u2rid,userid,rightid) values(130,12,14);
insert into user2right(u2rid,userid,rightid) values(131,12,15);
insert into user2right(u2rid,userid,rightid) values(132,12,16);
insert into user2right(u2rid,userid,rightid) values(133,12,17);
insert into user2right(u2rid,userid,rightid) values(134,12,22);
insert into user2right(u2rid,userid,rightid) values(135,12,23);
insert into user2right(u2rid,userid,rightid) values(136,12,24);
insert into user2right(u2rid,userid,rightid) values(137,12,25);
commit;
-- 系统模块数据
insert into module(moduleid,modulename,moduleurl,rightid) values(1,"权限管理","/rights/index.do",1);
insert into module(moduleid,modulename,moduleurl,rightid) values(1,"权限管理","/rights/index.do",2);
insert into module(moduleid,modulename,moduleurl,rightid) values(1,"权限管理","/rights/index.do",3);
insert into module(moduleid,modulename,moduleurl,rightid) values(1,"权限管理","/rights/index.do",4);
insert into module(moduleid,modulename,moduleurl,rightid) values(1,"权限管理","/rights/index.do",5);
insert into module(moduleid,modulename,moduleurl,rightid) values(1,"权限管理","/rights/index.do",6);
insert into module(moduleid,modulename,moduleurl,rightid) values(1,"权限管理","/rights/index.do",7);
insert into module(moduleid,modulename,moduleurl,rightid) values(1,"权限管理","/rights/index.do",8);

insert into module(moduleid,modulename,moduleurl,rightid) values(2,"管理员管理","/admins/index.do",9);
insert into module(moduleid,modulename,moduleurl,rightid) values(2,"管理员管理","/admins/index.do",10);
insert into module(moduleid,modulename,moduleurl,rightid) values(2,"管理员管理","/admins/index.do",11);
insert into module(moduleid,modulename,moduleurl,rightid) values(2,"管理员管理","/admins/index.do",12);
insert into module(moduleid,modulename,moduleurl,rightid) values(2,"管理员管理","/admins/index.do",13);

insert into module(moduleid,modulename,moduleurl,rightid) values(3,"资费管理","/fees/index.do",14);
insert into module(moduleid,modulename,moduleurl,rightid) values(3,"资费管理","/fees/index.do",15);
insert into module(moduleid,modulename,moduleurl,rightid) values(3,"资费管理","/fees/index.do",16);
insert into module(moduleid,modulename,moduleurl,rightid) values(3,"资费管理","/fees/index.do",17);

insert into module(moduleid,modulename,moduleurl,rightid) values(4,"用户管理","/users/index.do",18);
insert into module(moduleid,modulename,moduleurl,rightid) values(4,"用户管理","/users/index.do",19);
insert into module(moduleid,modulename,moduleurl,rightid) values(4,"用户管理","/users/index.do",20);
insert into module(moduleid,modulename,moduleurl,rightid) values(4,"用户管理","/users/index.do",21);
insert into module(moduleid,modulename,moduleurl,rightid) values(4,"用户管理","/users/index.do",22);
insert into module(moduleid,modulename,moduleurl,rightid) values(4,"用户管理","/users/index.do",23);
insert into module(moduleid,modulename,moduleurl,rightid) values(4,"用户管理","/users/index.do",24);
insert into module(moduleid,modulename,moduleurl,rightid) values(4,"用户管理","/users/index.do",25);

insert into module(moduleid,modulename,moduleurl,rightid) values(5,"帐务管理","/accounts/index.do",26);
insert into module(moduleid,modulename,moduleurl,rightid) values(5,"帐务管理","/accounts/index.do",27);

insert into module(moduleid,modulename,moduleurl,rightid) values(6,"帐单管理","/reckons/index.do",28);

insert into module(moduleid,modulename,moduleurl,rightid) values(7,"用户自务","/selfservices/index.do",29);
insert into module(moduleid,modulename,moduleurl,rightid) values(7,"用户自务","/selfservices/index.do",30);
commit;

五，查询练习（结果完全正确）

--select u.userid,u.username,r.rightid,r.rightname from user u,qright r,user2right u2r
where (u.userid=3)and(u.userid=u2r.userid)and(u2r.rightid=r.rightid);

-- select u.userid,u.username,r.rightid,r.rightname,m.modulename from user u,qright r,user2right u2r,module m where (u.userid=3)and(u.userid=u2r.userid)and(u2r.rightid=r.rightid)and(m.rightid=r.rightid);
--select u.userid,u.username,ro.rosename,r.rightname,m.modulename from user u,rose ro,qright r,user2right u2r,module m,rose2user r2u where (u.userid=3)and(u.userid=u2r.userid)and(u2r.rightid=r.rightid)and(m.rightid=r.rightid)and((ro.roseid=r2u.roseid)and(r2u.userid=u.userid));