多对多关系

一：多对多关系的分析

以用户，角色，权限来分析之间的关系，用户和角色是多对多的关系，角色和权限也是多对多的关系

1.1: 先分析用户和角色的多对多关系：

• 创建用户表，并插入数据

-- 用户表
create table t_user(
	uid int primary key auto_increment,
	uname varchar(20) not null
);

insert into t_user(uname) values('wzj'),('sbt');

• 创建角色表，并插入数据

-- 角色表
create table role(
	rid int primary key auto_increment,
	rname varchar(20) not null
);

insert into role(rname) values('爸爸'),('员工'),('运动员');

• 因为两表是一个多对多的关系，所以要建立一张中间表（来维护这种关系）

-- 用户_角色中间表
create table user_role(
	uid int, -- 用户id
	rid int, -- 角色id
	constraint user_fk foreign key(uid) references t_user(uid),
	constraint role_fk foreign key(rid) references role(rid)
);

-- wzj(用户) 有(爸爸和员工)这两个角色
insert into user_role(uid,rid) values(1,1);
insert into user_role(uid,rid) values(1,2);

-- sbt(用户) 有(爸爸和运动员)这两个角色
insert into user_role(uid,rid) values(2,1);
insert into user_role(uid,rid) values(2,3);

• 各个表中的数据
  

需求：那我们想获取某个用户的角色怎么办？

分析：因为用户表和角色表之间没有直接的关系，所以需要先利用中间表作为桥梁，先让用户表和中间表进行关联
​ 生成一个表(t), 然后再让表(t)和角色表进行关联

方式一:

-- 先让用户表和中间表关联，得到用户对应的角色的id
select 
	u.*,ur.rid
from 
	t_user u
inner join 
	user_role ur
on
	ur.uid = u.uid
where
	u.uname = 'wzj'

-- 再把上面查询出来的结果集当做一张临时表和角色表进行关联
select
	t.*, r.rname
from 
	role r
inner join 
	(select u.uname,ur.rid from t_user u inner join user_role ur on ur.uid = u.uid where u.uname = 'wzj') t
on
	r.rid = t.rid;

最终就查询出来了用户（wzj）对应的角色有哪些

方式二：

思路：不在一个表和一个表进行关联了，全部拿出来进行关联

-- 将用户表，中间表，角色表进行关联
select 
	u.uid, u.uname, r.rname
from  
	t_user u
inner join
	user_role ur
on 
	u.uid = ur.uid
inner join
	role r
on
	ur.rid = r.rid
where 
	u.uname = 'wzj';

注：查询出来的效果也是一样的

• 我想把结果集中纵向的数据横向展示

select  
	t.uname '用户名称',
    max(case t.rname when '爸爸' then '爸爸' else '0' end) '角色一',
    max(case t.rname when '员工' then '员工' else '0' end) '角色二',
    max(case t.rname when '运动员' then '运动员' else '0' end) '角色三'
from 
	(select u.uid, u.uname, r.rname from t_user u inner join user_role ur on u.uid = ur.uid inner join role r on ur.rid = r.rid) t
group by t.uname

注：聚合函数max()不仅可以作用于数值类型的数据，还可以作用于字符串类型数据和日期时间类型的数据

1.2：再分析角色和权限的多对多关系

角色表上面我们已经创建过了

• 创建权限表，并插入数据

-- 创建一个权限表
create table privilege(
	pid int primary key auto_increment,
	pname varchar(20)
);

insert into privilege(pname) values('上爸爸去哪'),('里约奥运会'),('公司年会');

• 因为角色表和权限表是一个多对多的关系，所以还要创建一个中间表

create table role_privilege(
	rid int,
	pid int,
	constraint role_fk_2 foreign key(rid) references role(rid),
	constraint privilege_fk foreign key(pid) references privilege(pid)
);

insert into role_privilege(rid,pid) values (1,1),(2,3),(3,2);

需求：我怎么通过用户id, 获取它对应的角色和权限呢？

思路：①：让用户表和用户角色表(中间表)进行关联，得到角色id，然后把前面作为一个整体再去和角色关联，得 到角色名称

​ ②：基于①步骤，我们已经得到了用户对应的角色，接下来把上面作为一个整体和角色权限表进行关联得到权限id，然后再作为一个整体和权限表关联，最终就可以得到权限名称

注：感觉比较麻烦，但是我暂时没有想到更好的办法

select 
	t2.*, rp.pid, p.pname
from
	role_privilege rp
inner join
(
	select
		t.*, r.rname
	from 
		role r 
	inner join (select u.*,ur.rid from t_user u inner join user_role ur on ur.uid = u.uid where u.uid = 1) t where t.rid = r.rid
) t2
on 
	t2.rid = rp.rid
inner join
	privilege p
on 
	rp.pid = p.pid;

– 查看t_user表中的索引
show index from t_user;

– 查看t_user表，被哪些外键引用！
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME=‘t_user’;

来自：虽然帅，但是菜的cxy