今天学习了数据表多对多,由于有图理解起来也轻松了很多,
使用联合主键进行多对多表的关系的管理(中间表)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39 |
create database auth character
set
UTF8;/*用户表*/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)) |
E-R图 (Entry-Relationship 实体关系图)
写入表中几行数据进行基本分析
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18 |
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‘); |
进行查询
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 |
/*查询某个人拥有某个角色*/select u.name,r.namefrom users u inner
join roleuser ru on
u.id=ru.uid inner
join roles r on
ru.rid=r.id; /*某角色拥有某菜单*/select r.name,m.namefrom 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; |
原文：http://www.cnblogs.com/ylfeiu/p/3612252.html