天下无坑

天下无坑


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>

页面列表

ITEM_HTML