子查询

查询的结果可以作为第二个查询的条件，也就是说可以将多个查询条件连在一条sql语句中。

1. 查询student表中年龄最大的学生信息；
   1. 分析1 先找到最大年龄
   2. 假如我知道最大年龄是多少，可以直接根据年龄找人 select * from student where age = (最大年龄) ```sql — 1. 找到最大年龄 select age from student order by age desc limit 1;

— 2. 根据年龄找符合特征的人员 select * from student WHERE age= 40;

— 3. 合并语句

select * from student WHERE age = (select age from student order by age desc limit 1);

2. 查询student表中分数score最高的学生信息；
```sql
-- 1. 找到最高的分数 
select score from student
ORDER BY score DESC
LIMIT 1;
-- 2. 找人 
select * from  student
WHERE score = 100;
-- 3 合并
select * from  student
WHERE score = (select score from student
ORDER BY score DESC
LIMIT 1);

1. 查询student表中分数score第二高的学生信息； ```sql — 1. 找到第二高的分数 SELECT DISTINCT(score) from student ORDER BY score desc LIMIT 1,1

— 2. 找人 SELECT * from student WHERE score = ( SELECT DISTINCT(score) from student ORDER BY score desc LIMIT 1,1 );

4. 查找分数排在班级前三的学生信息；按分数排名，先去重，排 3个分数；
```sql
-- 1. 先找到排在第三的分数
select distinct(score) from student
order by score DESC
limit 2,1
-- 2. 只要大于等于 第三的分数
select * from student
where score >= (
    select distinct(score) from student
  order by score DESC
  limit 2,1
)
ORDER BY score DESC