MySQL的子查询

create table player(
    id int not null auto_increment primary key,
    name varchar(20) not null,
    age int not null,
    height int not null
);

insert into player values (default,"保罗",32,183),
(default,"詹姆斯",34,206),
(default,"库里",30,198),
(default,"哈登",30,192),
(default,"威少",32,194),
(default,"利拉德",28,183);

select age
from player
order by age desc limit 0,1;

select max(age)
from player

select *
from player
where age=(select age
from player
order by age desc limit 0,1) and height=(select height
from player
order by height desc limit 0,1);

create table score(
    id int not null auto_increment primary key,
    name varchar(20) not null,
    major varchar(20) not null,
    score int not null
);

insert into score values (default,"张三","语文",84),
 (default,"李四","语文",65),
 (default,"王五","语文",77),
 (default,"小刘","语文",54);

insert into score values (default,"张三","数学",26),
 (default,"李四","数学",74),
 (default,"王五","数学",44),
 (default,"小刘","数学",30);
 
insert into score values (default,"张三","英语",65),
 (default,"李四","英语",74),
 (default,"王五","英语",54),
 (default,"小刘","英语",70);

select name
from score
WHERE score<60 and major = "数学";

select name
from score
WHERE score<60 and major = "语文" and name in(select name
from score
WHERE score<60 and major = "数学");

select * 
from score
group by name,score
ORDER BY score desc;

select * 
from(select * 
from score
group by name,score
ORDER BY score desc) as a
group by name;