mysql子查询例子

-- 子表查询
create table stus(
id int not null auto_increment ,
name varchar(10) ,
age tinyint ,
c_id int ,
height int ,
primary key (id)
);
insert into stus values(default,'xiaoming',23,1,175);
insert into stus values(default,'hongli',21,1,162);
insert into stus values(default,'susan',22,1,157);
insert into stus values(default,'aitao',25,3,178);
insert into stus values(default,'buli',26,2,172);
insert into stus values(default,'hongxue',28,3,177);
insert into stus values(default,'haolin',24,2,180);
insert into stus values(default,'qimiao',19,5,152);
insert into stus values(default,'huijuan',31,null,168);
create table classes(
id int auto_increment,
c_name varchar(15),
c_number int,
primary key(id)
);
insert into classes value(null,'php20141115',3013);
insert into classes value(null,'php20141215',3011);
insert into classes value(null,'php20141015',3010);
-- 查询一个值 ：已知班级名称是'php20141115'，要查询这个班里的学生
-- php逻辑
select id from classes where c_name = 'php20141115';
-- 再根据id在学生表里查询
select * from stus where c_id=id;
-- 子表查询方法
select * from stus where c_id= (select id from classes where c_name = 'php20141115');
select * from stus where height = (select max(height) from stus);
select * from stus where age=(select max(age) from stus);


-- 查询所有学生，并且是在已有的班级中
select * from stus where c_id is not null;
-- 上面结果不对
-- php逻辑：先查出所有班级id，根据id查询学生
select id from classes;
select * from stus where c_id in (id);
-- mysql列子表查询
select * from stus where c_id in (select id from classes);
select * from stus where c_id != all(select id from classes);


-- age,height 都是最高的学生
select * from stus where (age,height)= (select max(age),max(height) from stus);
-- 将年龄最大的学生高度改为182
update stus set height =182 where id= (select id from stus where age=31);
-- 上面语句报错，可见子查询只能用在查询语句中。


-- order by 想要在group by 之前执行，from子查询可以做到：学生按班级分组，查出每个班里最高的学生
select * from stus group by c_id order by height desc;
select * from (select * from stus order by height desc) stus2 group by c_id ;