​多表查询

表和表之间的关系

数据表中一般描述关系：
1、继承
2、关联关系（1-1,1-n,n-n）
1-1 中国大陆范围内，明面上的，合法的：夫妻关系
1-n 当前社会场景下： 血脉关系下的 父母 - 子女
n-n 当下大学系统的，学生选课；玩家和游戏；

表之间关系的维护—外键

外键

外键：就是建立一个字段，用来描述数据之间的关系，和另一个表的字段没有直接联系

外键约束

外键约束：给外键字段添加一个约束条件，让引用表的数据，在修改和删除时，需要针对 外键所在的表进行 引用判断
当然：你们在学习时，可以添加外键约束，但是在具体的生产环境下：一定要把外键约束取消
原因是：外键约束给你添加了级联操作，有的时候你的程序也控制他们之间的关系，到时就会出现：你都不知道你哪儿出错了！
例外，从性能上考虑，也请将该外键约束删除

建立1-1的表

类似夫妻关系
靠外键来进行连接，可以a的字段作为b的外键，也可以反过来

create table husband_info (id bigint primary key auto_increment,
husband_name varchar(20),
gender char(4),
fk_wife_id bigint,
foreign key (fk_wife_id) references wife_info (id)
);
//foreign key (fk_wife_id) references wife_info (id)表示在fk_wife_id 字段上，添加一个外键约束，数据引用至wife_info 表的id字段
create table wife_info (id bigint primary key auto_increment,
wife_name varchar(20),
gender char(4));
//若表已存在
alter table husband_info add foreign key (fk_wife_id) references wife_info(id);

建立1-n的表

类似的关系 明星-电影
这里只能明星的字段作为电影的外键，也就是电影来记住演员，也能理解为多的记住少的

create table star_info (id bigint primary key auto_increment,
star_name varchar(20)
);
create table movie_info (id bigint primary key auto_increment,
movie_number varchar(20),
movie_desc varchar(120),
fk_star_id bigint
);

alter table movie_info add foreign key(fk_star_id) references star_info (id);

建立n-n的表

类似玩家与游戏的关系
创建n-n的关系，需要一个中间表来连接

create table player_info(
id bigint primary key auto_increment,
player_name varchar(20)
);
create table game_info(
id bigint primary key auto_increment,
game_name varchar(20)
);
create table player_game (
id bigint primary key auto_increment,
fk_player_id bigint,
fk_game_id bigint );


//添加外键约束
alter table player_game add foreign key(fk_player_id) references player_info (id);
alter table player_game add foreign key(fk_game_id) references game_info (id);

查询

使用子查询（不推荐）

例如：查询妻子时，要求显示其丈夫的内容

select (select husband_name from husband_info where fk_wife_id = w.id) ,wife_name, gender from wife_info as w;

例如：查询丈夫时，要求显示其妻子的内容

select id,husband_name,gender,(select wife_name from wife_info where id = h.fk_wife_id) as wife_name from husband_info as h;

多表之间使用子查询，有些字段必须用写一句select语句单独进行查询，使得代码很长，可阅读性差

子查询（in/exists）

例子：查询 妻子名称是“小” 开头的，丈夫的信息

//使用in
select id,husband_name,gender from husband_info where fk_wife_id in (select id from wife_info where wife_name like '小%');


//使用exists
select id,husband_name,gender from husband_info as h where exists (select w.id from wife_info as w where h.fk_wife_id = w.id and w.wife_name like '小%');

in的执行流程：
1、先进行子查询
2、将子查询的结果和 外部查询语句 进行 笛卡尔乘积匹配 ： 300(内) X 30000（外） = 9000000
3、将匹配后的结果，使用where 去做条件筛选，将匹配的留下，将不匹配的剔除

exists 的执行流程：
1、先查询外部的SQL语句，将查询结果，逐一和 子查询的结果集根据条件进行匹配
2、如果匹配上了，就显示外表的数据；如果没匹配上，就不显示外表的数据
3、进入外部的下一条数据进行匹配

in和exists的使用场景

in适合【内部数据】<【外部数据】时使用
exists适合【外部数据】<【内部数据】时使用

面试题：in和exists谁的性能更高

1.当内部数据和外部数据数量一样时，两者性能差不多
2.当内部数据小于外部数据时，in的性能更高
3.当内部数据大于外部数据时，exists性能更高

面试题：not in和not exists谁的性能更高

任何时候，not exists的性能都高于not in，因为not in一定是全表检索，not exists比较时会使用到索引

union/union all（联合查询）

当多张表结构相同，数据不同，且需要一起显示的时候，就可以使用union和union all进行查询
区别在于：
【union】会去重，【union all】不会去重

//将2个表的数据，联合到一起，并针对重复数据去重
select id,wife_name from wife_info union select id,wife_name from wife_02_info;
//将2个表的数据，联合到一起，不会针对重复数据去重
select id,wife_name from wife_info union all select id,wife_name from wife_02_info;

连表查询（left join | right join | inner join）

外联查（left [outer] join | right [outer] join）

left [outer] join 以左表为基础，进行右表数据匹配，右边有数据则显示数据，右边没有数据，则显示为NULL
**

right [outer] join 以右表为基础，进行左表数据匹配，左边有数据则显示数据，左边没有数据，则显示为NULL
**

内联查（inner join）

内联查，显示的中间交集的数据
**

*inner join的简化写法

用【,】代替【inner join】，用【where】代替【on】

select * from husband_info as h,wife_info as w where h.fk_wife_id = w.id 
and w.wife_name like '小%';