0、SQL语句的执行顺序
select语句的写法顺序为:select -- from -- where -- group by -- having -- order by
执行顺序为:from -- where -- group by -- having -- select -- order by
即,先from的一个表或多个表中创建工作表,再用where在创建的工作表的基础上保留满足条件的行,接着group by将这些行分成多个组,然后having对每个组进行条件过滤,只保留符合条件的组,最后order by对结果进行排序。
1、从学生表中,检索所有比“王华”年龄大的学生姓名、年龄和性别
#首先求出王华的年龄,然后把所有年龄与王华的年龄比较
select sname, age, sex from student
where age > (select age from student where sname = '王华');
2、统计学生表中,不同班级各自的男生、女生的人数
select class, sum(case when sex='男' then 1 else 0 end) as 男, sum(case when sex='女' then 1 else 0 end) as 女
from student group by class;
select t1.class, t1.男, t2.女 from
(select class, count(*) 男 from student where sex='男' group by class) t1 inner join
(select class, c