角色表
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='角色表';
用户表
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='用户表';
用户-角色关系表
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='用户-角色关系表';
查询某个用户拥有哪些角色
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
查询某个角色拥有哪些用户
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