E-R图(Entry-Relationship 实体关系图)

/*用户表*/

create table users(

id varchar(32) primary key,

name varchar(30),

pwd varchar(32)

);

/*创建角色表*/

create table roles(

id varchar(32) primary key,

name varchar(30),

des varchar(100)

);

/*通过一个中间表映射多对多的关系,多对多就是多个一对多,联合主键的特点是：两个列不能同时重复*/

create table roleuser(

uid varchar(32),

rid varchar(32),

constraint ru_pk primary key(uid,rid),

constraint ru_fk1 foreign key(uid) references users(id),

constraint ru_fk2 foreign key(rid) references roles(id)

);

/*创建菜单表*/

create table menus(

id varchar(32) primary key,

name varchar(50),

url varchar(100)

);

/*关联角色到菜单*/

create table rolemenu(

mid varchar(32),

rid varchar(32),

constraint rm_pk primary key(mid,rid),

constraint rm_fk1 foreign key(mid) references menus(id),

constraint rm_fk2 foreign key(rid) references roles(id)

);

/*添加测试数据*/

insert into users values('U001','Jack','1234');

insert into users values('U002','张三','4321');

insert into users values('U003','Tom','1111');

insert into roles values('R001','管理员','');

insert into roles values('R002','教师','');

insert into roleuser values('U001','R001');

insert into roleuser values('U002','R002');

insert into menus values('M001','系统管理','/sys.jsp');

insert into menus values('M002','用户管理','/user.jsp');

insert into menus values('M003','角色管理','/role.jsp');

insert into rolemenu values('M001','R001');

insert into rolemenu values('M002','R001');

insert into rolemenu values('M003','R001');

insert into rolemenu values('M003','R002');

/*查询某个拥有某个角色*/

select u.name,r.name

from users u inner join roleuser ru on u.id=ru.uid

inner join roles r on ru.rid=r.id;

/*某角色拥有某菜单*/

select r.name,m.name

from roles r inner join rolemenu rm on r.id=rm.rid

inner join menus m on rm.mid=m.id;

/*查询某人拥有某个菜单*/

select m.*

from users u inner join roleuser ru on u.id=ru.uid

inner join roles r on ru.rid=r.id

inner join rolemenu rm on r.id=rm.rid

inner join menus m on rm.mid=m.id;