MySQL多表查询

1.多表间关系(了解)

1.1一对多关系

1.1.1定义

主表的一行数据可以同时对应从表的多行数据，反过来就是从表的多行数据指向主表的同一行数据

比如，一个用户对多张银行卡，多张银行卡对一个用户

1.1.2建表原则

将少的一方作为主表，多的一方作为从表，在从表中指定一个字段作为外键，指向主表的主键
主键：⽤于唯⼀的标识表中的某⼀条记录。
外键：在项⽬中将表的字段作为拆分，将数据分别存到多张表中，以减少冗余数据，拆分后的表之间某些字段之间存在严格的关联关系，以保证数据正确；
查询数据时引⽤别的表的某个字段作为查询结果返回，外键的功能是实现同⼀事物在不同表中的标志⼀致性。

1.2多对多关系

1.2.1定义

两张表都是多的一方，A表的一行数据可以同时对应B表的多行数据，反之B表的一行数据也可以同时对应A表的多行数据

选课系统，一个同学可以选多门课，一门课可以被多个同学选择

1.2.2建表原则

因为两张表都是多的一方，所以在两张表中都无法创建外键，所以需要新创建一张中间表，在中间表中定义两个字段，这俩字段分别作为外键指向两张表各自的主键

2.多表关联查询(以1对N为例)

多表关联查询是使用一条SQL语句，将关联的多张表的数据查询出来

2.1内连接查询

主表和从表中的数据都满足连接条件则能够查询出来，不满足连接条件则不会查询出来

2.1.1显式内连接查询

显式内连接查询里面有inner join关键字

select [字段,字段,字段] from a [inner] join b on 连接条件 [ where 其它条件]

-- inner join 查两张表的交集
select * from user_info,log_user_login
-- 查询user_info,log_user_login两张表的所有信息
select * from user_info inner join log_user_login on user_info.id = log_user_login.user_id;
/*
user_info inner join log_user_login：把user_info和log_user_login关联起来，取两张表的交集
on user_info.id = log_user_login.user_id：关联方式，user_info表的id和log_user_login表的user_id相同
*/
select ui.id,ui.user_name,ui.telephone,lul.user_id,lul.ip,lul.create_time from user_info ui 
inner join log_user_login lul 
on ui.id = lul.user_id;
-- ，给表取别名，两表关联后，只查两表交集的指定字段
select ui.id,ui.user_name,ui.telephone,lul.user_id,lul.ip,lul.create_time,ua.card_id,ua.`password` from user_info ui 
inner join log_user_login lul 
on ui.id = lul.user_id

inner join user_account ua 
on ui.id = ua.user_id 
/*
三张表的关联查询(每多一个表，就多加一个inner join 某表名 on 关联方式)
password是关键字，要用飘号引起来
*/

2.1.2隐式内连接查询

隐式内连接查询里面是没有inner join关键字

select [字段1,字段2,字段3] from a表名,b表名 where 连接条件 # b表里面的外键 = a表里面的主键

2.2外连接查询

2.2.1右外连接查询

以join右边的表为主表,展示右边表的所有数据,根据条件查询join左边表的数据,若满足则展示,若不满足则以null显示。可以理解为：在内连接的基础上保证右边表的数据全部显示

select ui.id,ui.user_name,ui.telephone,lul.user_id,lul.ip,lul.create_time from user_info ui 
right join log_user_login lul 
on ui.id = lul.user_id;
-- ui表和lul表关联，按照ui的id和lul.user的id相同的关联方式，查两表的交集+右边lul表自己存在的数据
select ui.id,ui.user_name,ui.telephone,lul.user_id,lul.ip,lul.create_time from user_info ui 
right join log_user_login lul 
on ui.id = lul.user_id where ui.id is null;
-- ui表和lul表关联，按照ui的id和lul.user的id相同的关联方式，查右边lul表自己存在且左边ui表中不存在的数据

2.2.2左外连接查询

以join左边的表为主表,展示主表的所有数据,根据条件查询连接右边表的数据,若满足条件则展示,若不满足则以null显示。可以理解为：在内连接的基础上保证左边表的数据全部显示

select ui.id,ui.user_name,ui.telephone,lul.user_id,lul.ip,lul.create_time from user_info ui 
left join log_user_login lul 
on ui.id = lul.user_id;
-- ui表和lul表关联，按照ui的id和lul.user的id相同的关联方式，查两表的交集+左边ui表自己存在的数据
select ui.id,ui.user_name,ui.telephone,lul.user_id,lul.ip,lul.create_time from user_info ui 
left join log_user_login lul 
on ui.id = lul.user_id where lul.user_id is null;
-- ui表和lul表关联，按照ui的id和lul.user的id相同的关联方式，查左边ui表自己存在且右边lul表中不存在的数据

2.3子查询

2.3.1定义

如果一个查询语句嵌套在另一个查询语句里面，那么这个查询语句就称之为子查询，（例如：将一个select语句的结果作为另一个select语句的条件）

注意：不管子查询在哪里，子查询必须使用()括起来。

2.3.2子查询分类

根据位置不同，分为：where型，from型，exists型

2.3.2.1where型

返回唯一结果或范围

select * from 表名emp where 表字段salary = (select max(表字段salary) from 表名emp)

2.3.2.2from型

作为临时表

select * from （select语句）as 别名

2.3.2.3exists型（了解）

只要⼦查询返回⼀条数据就认为是满⾜条件

# EXISTS用于检查子查询是否至少会返回一行数据，
SELECT dno,dname FROM dept WHERE EXISTS (SELECT * FROM emp WHERE emp.deptno = dept.dno);
select * from dept where not exists (select * from emp where deptno = dept.dno )

2.4自连接（了解）

递归结构，表中的一个字段指向了表中另一个记录

-- 自连接
select a.name as studentname, b.name as teachername
from student_and_teacher a,student_and_teacher b
where a.teacherno = b.studentno;
/*
select 别名A.列名A, 别名B.列名B
from 表名 as 别名A, 表名 as 别名B
where 别名A.列名C = 别名B.列名D
*/

-- 创建数据
drop table if exists `student_and_teacher`;
create table `student_and_teacher`(
`studentno` int(4) not null comment '学号',
`name` VARCHAR(255) not null COMMENT '姓名',
`teacherno`int(4) comment '前座学号'
)engine = innodb default charset = utf8;

INSERT INTO `student_and_teacher` VALUES (
1, '张三', null
), (
2, '李四', 1
), (
3, '王五', 1
), (
4, '赵六', 2
)
/*
studentno	|	name	|	teacherno
1			|	张三	|	(null)
2			|	李四	|	1
3			|	王五	|	1
4			|	赵六	|	2
*/