**sql语句的执行顺序和书写顺序
0.通过where来筛选要展示的行，用select 来选择展示的字段（列）
1.语句和avg等运算结果可在括号后加临时名 （如A、B等） 在嵌套中使用
例句 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select A.Sj,B.Sname,A.dc from(select Sj,AVG(score)dc from SC group by Sj)A left join Student B on A.Sj=B.Sj where A.dc>=60;
2.not in 后可接语句
例句 查询不存在" 01 "课程但存在" 02 "课程的情况
select * from SC where Cj='02'and Sj not in(select Sj from SC where Cj='01');
3.group by ** having ** 适合用于按组筛选，先算where 后算having 可省一步迭代
例句 查询没有学全所有课程的同学的信息
select * from Student where Sj in(select Sj from SC group by Sj having COUNT(Cj)<3)
4.条件函数学习：case when A>* then B else C end
例句：各个科目，共有多少人及格/多少人考试（及格率）查询
select cj,sum(case when score>=60 then 1 else 0 end)/count(1) from sc group by cj;
5.自定义变量（sql8.0后版本支持）
例：设变量rank=1
select @rank:=1;
使用时如果忘记：只写=就等同于判断是否相等
select @rank;
按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
select sj,cj,score,@rank:=@rank+1 as rn from sc ,(select@rank:=0) as t order by score desc;
select sj,cj,score,(case when @sco=score then @rank when @sco:=score then @rank:=@rank +1 end )as rn
（因为是赋值，所以如第一句不符合，必运行,要把as的名字放在括号外，否则不运行）
from sc ,(select @rank:=0,@sco:=null) as t
order by score desc;
6.如果两表没什么关联条件（即不用on）可以不写inner join ，直接写","
7.if 判断语句 if(@a=scos,'',@b+1) 如果a=scos，返回空表，否则返回b+1;
8.sum(case when * then 1 else 0 end)常用来统计指定数目
9.concat('a','b') 可以连接两个字符串
10.至今无法理解（where嵌套没看懂）
查询各科成绩前三名的记录
select * from sc a where
(select count(1)from sc b where (a.cj=b.cj and a.score>b.score))<3
order by cj desc,score desc;
select * from sc a inner join sc b on(a.cj=b.cj and a.score>b.score) order by a.cj desc,a.score desc;
select *,count(1)as rank from sc a inner join sc b on(a.cj=b.cj and a.score>b.score) group by b.cj,b.score,b.sj order by a.cj desc,a.score desc
select c.*,rank
from
(select distinct b.*,count(1)as rank from sc a inner join sc b on(a.cj=b.cj and a.score<b.score) group by a.cj,a.score,a.sj order by b.cj desc,b.score desc)as c where rank>=2
select * from sc c where
(select count(1)from sc b,sc a where (a.cj=b.cj and a.score>b.score))<3
order by cj desc,score desc;
11.group by * having * 分组查询，组内条件在having中筛选
查询选修了全部课程的学生信息
select *, from sc group by sj having count(1)=3;
网友评论