mysql的多表查询和子查询

多表查询：查询数据时，需要使用多张表来查询

多表查询分类：

1.内连接查询

2.外连接查询

3.子查询

笛卡尔积： 

create table class
(
    id int primary key auto_increment,
    name varchar(10)
);
create table student
(
    id int primary key auto_increment,
    name varchar(10),
    class_id int,
    constraint FK_student_class_id foreign key(class_id) references class(id)
);

insert into class(name) values ('清北班'),('冲刺班');
insert into student(name,class_id) values ('aaa',1),('bbb',1),('ccc',2),('ddd',2);

-- 多表查询：查询所有学生信心和所属的班级
select student.id, student.name, class_id ,class.id, class.name from student,class;

 

根据图片我们发现两张表相互查询，导致数据出现问题，这种问题叫笛卡尔积（多张表的每行数据进行了交叉查询）

解决方案：在进行多表查询时，消除笛卡尔积

查询时添加条件：主表.主表主键=从表.从表外键 

select student.id, student.name, class_id ,class.id, class.name from student,class where class_id=class.id
order by student.id ASC;

 

练习：查询id为1的学生一级班级信息

select student.id, student.name, class_id,class.id,class.name from student,class
where class_id=class.id -- 消除笛卡尔积
and student.id=1;

 内连接查询

1.内连接查询语法

内连接操作的目的是把多张表中互相关联的数据查询出来

1.隐式内连接

select 列名 from 左表,右表 where 从表.外键=主键.主键

2.显示内连接

select 列名 from 左表 [inner] join 右表 on 从表.外键=主键.主键

-- 查询 aaa 学生，并显示学生id,姓名，班级名
-- 语法1：
select student.id,student.name,class.name
from student,class
where class_id=class.id and student.name ='aaa';

-- 语法2：
select student.id,student.name,class.name
from student inner join class
on student.class_id = class.id #消除笛卡尔积
-- on的优先级高于where
where student.name='aaa';


-- 使用别名的用法
select s.id,s.name,c.name
from student AS s inner join class AS c
on s.class_id = c.id
where s.name='aaa';

 外连接查询

外连接查询有两种方式：
1.左外连接：左表中所有记录都出现在结果中，如果右表没有匹配的记录，使用null填充

select 列名 from 左表 left join 右表 on 从表.外键=主表.主键

2.右表连接：右表中所有记录都出现在结果中，如果左表没有匹配的记录，使用null填充

select 列名 from 左表 right join 右表 on 从表.外键=主表.主键

-- 这时候添加一个新班级，没有添加学生
insert into class (id, name) values (null,'补差班');
-- 查询，不会显示新班级
select c.name,s.id AS studentId,s.name
from class AS c inner join student AS s
on c.id = s.class_id;

所以我们要使用外连接 

select c.name,s.id AS studentId,s.name
from class AS c left join student AS s
on c.id = s.class_id;

 子查询

根据子查询的结果，可以分为单行单列，多行单列，多行多列

单行单列

如果子查询时单行单列，父查询使用比较运算符  ><=

练习：
 

-- 查询学生id最大的学生信息
select
id, name
from student
where id=(select max(id) from student);

子查询  select max(id) from student  结果：

结果是单行单列，使用><= 

-- 查询学生id大于bbb的id的学生信息
select
id,name
from student
where id>(select id from student where name='bbb');

 多行单列

如果子查询的结果是多行单列，可以认为是数组，父查询使用in,all.any关键字

关键字	说明
in	查询包含在in条件的所有数据
all	可以与> <号结合起来，分别表示大于或小于其中的所有数据为真
any	可以与> <号结合起来，分别表示大于或小于其中的任意一个数据为真

-- 查询 所有学生id>2的学生的班级信息
-- 1. 查询学生id>2的学生的班级id
select class_id from student where id>2;
-- 2.查询 所有学生id>2的学生的班级信息
select id, name from class
where id in (select class_id from student where id>2);

select class_id from student where id>2;的结果是多行单列

 select id, name from class
where id in (select class_id from student where id>2);的结果

-- 查询大于一班所有id的所有学生信息
-- 1.查询一班所有学生的id
select id from student where student.class_id=1;-- 结果为单列多行
-- 2.大于一班所有学生id的所有学生信息
select id, name from student where id > all (select id from student where student.class_id=1);


-- 或者直接查询大于一班最大学生id的所有学生信息
select id, name from student where id>(select max(id) from student where class_id=1);

 

-- 查询大于一班任意一名学生id的所有学生信息
-- 1.查询一班的学生id
select id from student where class_id=1;
-- 2.查询大于一班任意一名学生id的所有学生信息
select id, name from student where id > any(select id from student where class_id=1);

-- 或者或者直接查询大于一班最小学生id的所有学生信息
select id, name from student where id> (select min(id) from student where class_id=1);

多行多列

如果子查询是多行多列，子查询可以认为它是一张虚拟表，可以使用表连接再次进行多表查询

注意：如果要访问子查询表的字段，需要为子查询表取别名，否则无法访问表中的字段。 

-- 查询分数大于60的所有学生信息以及班级信息
-- 1.查询分数大于60的所有学生信息
select student.id, student.name,student.score from student where score>60;
-- 2.查询分数大于60的所有学生信息以及班级信息,使用表连接
select s.*,c.* from class AS c inner join(select * from student where score>60) AS s
on s.class_id=c.id;

-- 3.防止有的学生没有班级，使用外连接
select s.*,c.* from class AS c right join (select * from student where student.score>60) AS s
on c.id=s.class_id;
-- 把学生设置为主，没有班级则班级列为null
-- 内连接是多表外键不为null才显示

 select student.id, student.name,student.score from student where score>60;

结果是多行多列

 select s.*,c.* from class AS c inner join(select * from student where score>60) AS s
on s.class_id=c.id;

结果，内连接不会显示class_id外键为null的学生信息

 select s.*,c.* from class AS c right join (select * from student where student.score>60) AS s
on c.id=s.class_id;

结果显示所有学生，没有班级，班级就是null

另一种方法

-- 第二种方法
select student.*,class.* from class right join student
on class.id = student.class_id
where score in (select score from student where score>60);