rbac数据表设计
<blockquote>
<p>知识点: 联合主键</p>
</blockquote>
<h5>E-R图(Entry-Relationship 实体关系图)</h5>
<p><img src="https://www.showdoc.cc/server/api/common/visitfile/sign/3144b6c3b329688fc0a91bd575b6ff9f?showdoc=.jpg" alt="" /></p>
<pre><code class="language-sql">/*用户表*/
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)
);</code></pre>
<pre><code class="language-sql">/*通过一个中间表映射多对多的关系,多对多就是多个一对多,联合主键的特点是：两个列不能同时重复*/
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)
);</code></pre>
<pre><code class="language-sql">/*创建菜单表*/
create table menus(
id varchar(32) primary key,
name varchar(50),
url varchar(100)
);</code></pre>
<pre><code class="language-sql">/*关联角色到菜单*/
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)
);
</code></pre>
<pre><code class="language-sql">/*添加测试数据*/
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');
</code></pre>
<pre><code class="language-sql">/*查询某个拥有某个角色*/
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;
</code></pre>
<pre><code class="language-sql">/*某角色拥有某菜单*/
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;</code></pre>
<pre><code class="language-sql">/*查询某人拥有某个菜单*/
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;</code></pre>