sql心得926

**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;