SQL语句的语法顺序
select * from 表名1 left/right/full join 表名2 on 条件 where 条件 group by 列 having 条件 order by 列 asc/desc
SQL的执行顺序
先创建两个表
create table students(
stu_id number(4) primary key,
stu_name varchar2(20),
class_id number(6)
)
select * from students;
insert into students values (9527,'唐伯虎',170101);
insert into students values (9528,'春香',170102);
insert into students values (9529,'秋香',170102);
insert into students values (9530,'白展堂',170103);
insert into students (stu_id,stu_name) values (9531,'杨过');
insert into students values (9532,'NPC',170104);
create table classes(
class_id number(6) primary key,
class_name varchar2(20)
)
select * from classes;
insert into classes values (170101,'一年级1班');
insert into classes values (170102,'一年级2班');
insert into classes values (170103,'一年级3班');
以如下SQL为例
select s.class_id,c.class_name,count(stu_id) from students s left join classes c on s.class_id=c.class_id
where s.class_id in (170101,170102,170103,170104) group by s.class_id,c.class_name order by count(stu_id) desc
从from开始
加载左表
from students s
查询students表中的所有数据
join 这里是先join再left join
join classes c
将两个表的笛卡尔集合存入虚拟表vt1
on 筛选器
on s.class_id=c.class_id
从上一步的笛卡尔集合中删除不匹配的项,等到如下结果,存入虚表vt2
添加外部行
left (outer) join classes c
左连接,students表为保留表,将剩余的数据重新添加到上一部的vt2,生成虚拟表vt3
where 阶段
where s.class_id in (170101,170102,170103,170104)
根据where条件进行筛选,删除不匹配的,生成虚拟表vt4
这里where的删除是永久的,而上面的on删除是暂时的,因为可能有外连接需要添加外部行,重新把数据加载回来,而where则不能
group by 分组
group by s.class_id,c.class_name
将上一步的结果根据group by 来分组,存入vt5
having 筛选
如果group by 后面有having,则分组后根据条件筛选,删除不匹配的项
select 查询挑拣计算列
计算表达式
select s.class_id,c.class_name,count(stu_id)
从分组筛选后的数据中计算每组中stu_id不为null的个数,列出要筛选显示的列
distinct过滤重复
order by
order by count(stu_id) desc
根据指定项进行排序,得到最终结果。
OVER!!希望这篇文章对大家有所帮助!