多对多三表关联
<h4>角色表</h4>
<pre><code class="language-sql">DROP TABLE IF EXISTS `roles`;
CREATE TABLE `roles` (
`id` int(9) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`description` varchar(255) NOT NULL COMMENT '描述',
`name` varchar(32) NOT NULL,
`router_power` text NOT NULL,
`api_power` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='角色表';</code></pre>
<h4>用户表</h4>
<pre><code class="language-sql">DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`uid` int(9) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`nickname` varchar(32) NOT NULL,
`username` varchar(32) NOT NULL,
`password` varchar(32) NOT NULL,
PRIMARY KEY (`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='用户表';</code></pre>
<h4>用户-角色关系表</h4>
<pre><code class="language-sql">DROP TABLE IF EXISTS `users_roles`;
CREATE TABLE `users_roles` (
`uid` int(9) unsigned NOT NULL,
`rid` int(9) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='用户-角色关系表';</code></pre>
<h4>查询某个用户拥有哪些角色</h4>
<pre><code class="language-sql">SELECT u.uid,u.nickname,
group_concat(r.name Separator ',') as role_names,
group_concat(r.id Separator ',') as role_ids
FROM `users` u
LEFT JOIN users_roles ur ON ur.uid = u.uid
LEFT JOIN roles r ON ur.rid = r.id
WHERE u.uid = 2
GROUP by ur.uid
LIMIT 0 , 30</code></pre>
<h4>查询某个角色拥有哪些用户</h4>
<pre><code class="language-sql">SELECT u.uid, u.nickname,
r.id AS role_id,
r.name AS role_name
FROM `users` u
LEFT JOIN users_roles ur ON ur.uid = u.uid
LEFT JOIN roles r ON ur.rid = r.id
WHERE r.id =1
LIMIT 0 , 30</code></pre>