MySQL——多表查询（笔记6）

#建一个人员表，一个省份表
create table people(
id int primary key auto_increment,
name varchar(5),
age smallint(1),
sex char(3),
province_id int(5)
);
 
create table province(
id int primary key auto_increment,
pro_name char(5)
);

insert into province values(null,"四川省"),(null,"云南省"),(null,"贵州省"),(null,"湖南省");

insert into people values
(null,"王老五",18,'男',1),
(null,"张翠花",19,'女',2),
(null,"史珍香",22,'女',1),
(null,"关阴",20,'女',3),
(null,"朱逸群",20,'男',2), 
(null,"杨伟",23,'男',3), 
(null,"蔡坤",25,'男',4), 
(null,"吴凡",27,'男',null)

#1.查询用户所有信息以及所在省份名称
select * from people,province
#出现了笛卡尔积现象，只有people.province_id=province.id能够匹配
#数据才是我们想要的。
#解决：加一个people.province=province.id 条件

#一、内连接：用左表去匹配右表，查询满足条件的数据

#（1）隐式内连接 select 查询表 from 左表，右表 where 关联条件
select peo.*,pro.pro_name from people peo ,province pro where peo.province_id=pro.id;

#（2）显示内连接 select 查询列表 from 左表[inner] join 右表
on 关联条件 [where 其他条件]
select p1.*,p2.pro_name from people p1
inner join province p2
on p1.province_id=p2.id;

#二、外连接

#（1）左连接：保证左表的记录全部显示，哪怕不满足匹配条件
#格式： select 查询列表 from 左表 left[outer] join 右表 on 关联条件 [where 其他条件]
select p1.*,p2.pro_name from people p1
left join province p2
on p1.province_id=p2.id;

#(2)右连接：保证右表的记录全部显示，左表记录显示为空
#格式： select 查询列表 from 左表 right[outer] join 右表 on 关联条件 [where 其他条件]
select p1.*,p2.pro_name from people p1
right join province p2
on p1.province_id=p2.id;


#一对多 一个永和对应多个角色（用户，管理员）用户表 用户-权限中间表（id,user_id,role_id） 权限表

#三表查询 user2 role user_role

#查询张三对应的权限名称
#显示内连接方法
select u.name,r.role_name from user2 u
#连接中间表
inner join user_role ur
on u.id=ur.user_id
#连接权限表
inner join role r
on ur.role_id = r.id
where u.name="李四";


#隐式内连接方法
select u.name,r.role_name from user2 u,user_role ur,role r
where u.id=ur.user_id
and ur.role_id=r.id
and u.name="李四";