MySQL：子查询

MySQL：子查询

1.演示数据

-- 学生表
create table student
(
    id   int unsigned primary key,
    name varchar(20)      not null,
    age  tinyint unsigned not null
);
-- 成绩表
create table mark
(
    id      int unsigned primary key,
    chinese int,
    math    int,
    english int
);

-- 学生表数据
insert into student
values (1, '张三', 21);
insert into student
values (2, '李四', 22);
insert into student
values (3, '王二', 23);
-- 成绩表数据
insert into mark
values (1, 80, 75, 85);
insert into mark
values (2, 90, 65, 95);
insert into mark
values (3, 40, null, null);

2.分类

2.1.in|not in子查询

2.1.1.in

查询语文及格的同学

select *
from student
where id in (select id from mark where chinese >= 60);

结果：

1,张三,21
2,李四,22

2.1.2.not in

查询语言不及格的同学

select *
from student
where id not in (select id from mark where chinese >= 60);

结果：

3,王二,23

2.2.exists|not exists

2.2.1.exists

如果有人英语达到95分显示所有学生

select *
from student
where exists(select * from mark where english >= 95);

结果：

1,张三,21
2,李四,22
3,王二,23

2.2.2.not exists

如果没有人英语达到95分显示所有学生

select *
from student
where not exists(select * from mark where english >= 95);

结果：

# 空

2.3.标量子查询

子查询返回的结果一行一列

# 查询张三的英语成绩
select english
from mark
where id = (select id from student where name = '张三');

结果：

85

2.4.列子查询

子查询结果一列多行

# 查询英语大于等于85分的人名
select name
from student
where id in (select id from mark where english >= 85);

结果：

张三
李四

2.5.行子查询

子查询返回的结果是一行多列

# 查询中文和英语成功最高的各科成绩
select *
from mark
where (chinese, english) = (select max(chinese), max(english) from mark);

结果：

2,90,65,95

2.6.表子查询

子查询返回的结果是多行多列

# 查询有旷考记录的最低分
select min(ifnull(chinese, 0) + ifnull(math, 0) + ifnull(english, 0))
from (select id, chinese, math, english from mark where chinese is null or math is null or english is null) as result;

结果：

40