sql语句练习

sc分数表
 SId   CId   score 
 01    01     80.0 
 01    02     90.0 
 01    03     99.0 
 02    01     70.0 
 02    02     60.0 
 02    03     80.0 
学生表student（SId Sname Sage Ssex），老师表teacher（TId Tname），课程表course（CId Cname TId）

1、查询 01 课程比 02 课程成绩高的学生信息
同一个表里面同一个列是无法比较的。只能将这个表join表本身。如果join的key有多个相同值，那么每一个相同的行都会进行join。这样才能形成一张有两个score的表，才能比较两个score值

select * from student where sid in 
(select a.sid from sc a join sc b on a.sid=b.sid and a.cid=01 and b.cid=02 and a.score  > b.score)

2、查询存在 01 课程但可能不存在 02 课程的情况(不存在时显示为 null )

select * from 
(select * from sc where cid = 01)a 
left join
(select * from sc where cid = 02)b
on a.sid = b.sid

3、查询不存在 01 课程但存在 02 课程的情况

select * from sc
where sc.sid not in
(select a.sid from sc a where a.cid = 01)    先把存在01课程的sid选出来
and sc.cid =02                               再选存在02课程的

4、查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

select * from 
(select sid,avg(score) s from sc group by sid having s>60) a 
join student b on a.sid=b.sid;    先group by再having

5、查询在SC表存在成绩的学生信息

方法一： select * from student where sid in (select distinct(sid) from sc);
方法二： select * from ( select distinct(sid) from sc) a join student b on a.sid=b.sid
不能写成 select distinct(sid) from sc join student b on sc.sid=b.sid; 因为这个的结构其实是select distinct(c.sid) from (sc join student b on sc.sid=b.sid) c;

6、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

方法一：一种条件先写一段sql，先写总数，总成绩这段sql。再关联
select *  from student a left join 
(select sid,count(),sum(score) from sc group by sid) b on a.sid=b.sid;
方法二：直接关联。注意，from和group by针对的表都是关联后的表, 也就是关联操作是首先执行的
select s.sid, s.sname, count(sc.cid), sum(sc.score)
from student s left join sc
on s.sid = sc.sid
group by s.sid

7、 select distinct c.cid from course c join ( select from teacher where tname=‘张三’ ) a on a.tid=c.tid where（这里也可以是and） c.cid=02;
执行顺序：
FORM： 对FROM的前两个表（c,a）计算笛卡尔积。产生虚表VT1；
ON： 对虚表VT1进行ON筛选，只有那些符合a.tid=c.tid 的行才会被记录在虚表VT2中；
JOIN：如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, 如果 from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止（所以，将大数据量的表最后处理性能最好，因为前面处理时虚拟表最小）；但如果指定的是INNER JOIN，则不会添加外部行；
WHERE： 对虚拟表VT3进行WHERE条件过滤。只有符合where-condition的记录才会被插入到虚拟表VT4中；
SELECT： 执行select操作，从VT4中选择指定的列，插入到虚拟表VT5中。所以select是在join，筛选等操作完成后才进行的；
DISTINCT： 对VT5中的记录进行去重。产生虚拟表VT9,并将结果返回

8、查询没学过张三老师讲授的任一门课程的学生姓名
写sql要一个条件一个条件的写：

select *  from sc  where sid not in (     3）最后not in即可
select sid from sc where cid in  		2）再反向思维，选出学过张三老实课程的同学id
(select cid from course where tid=( select tid from teacher where tname='张三'))           1）先选出张三老师的课程
);

9、一般join关联查询都可以用in代替，比如a join b on a.id=b.sid可以替换为select * from a where id in (select sid from b) 但是in性能可能会不好，一般选择join，但有时候也用in，in内的元素不多的话，效率还可以
10、sum不仅可以用来求和，还可以将分组里面的某条记录提出来：按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select sid,
sum(case when sc.cid = 01 then sc.score else null end) as score_01,		先进行分组，再对每组里面满足条件的提出来
sum(case when sc.cid = 02 then sc.score else null end) as score_02,
sum(case when sc.cid = 03 then sc.score else null end) as score_03,
avg(score) from sc
group by sid
order by avg(score) desc

11、查询各科成绩优秀率：

select sc.cid,
sum(case when score >= 90 then 1 else 0 end)count(sc.score) as 优秀
from sc 
group by sc.cid 

12、按各科成绩进行排序

笨方法： 
select * from (select * from sc where cid=01 order by score) a 
union all 
select * from sc where cid=02 order by score;  
注意由于union all前面有order by，所以union all前面的表必须加括号

13、select * from sc a where sc.sid=01是错误写法，既然给sc赋予了别名，就要用别名取字段：select from sc a where a.sid=01
14、查询各科成绩前三名的记录
就是成绩比自己大的有多少人，小于3就是前三的0 ，1 ，2

SELECT * FROM sc a
	LEFT JOIN sc b ON a.cid = b.cid 
	AND a.score = b.score 
GROUP BY
	a.sid,
	a.cid,
	a.score 
HAVING
	COUNT( b.sid ) = 3 
ORDER BY
	a.cid,
	a.score DESC

15、order by id desc,time desc
先是按 id 降序排列 （优先）
如果 id 字段是一样的话，再按time 降序排列 (前提是满足id降序排列)
16、查询课程不同，成绩相同的学生的学生编号、课程编号、学生成绩

select distinct a.cid,a.sid,a.score from sc a join sc b on a.sid = b.sid where a.score = b.score and a.cid != b.cid;

17、sum(case when…)

t1:
name item score
01	 数学   50
01   英语   80
t2:
name math english
01   50   80

将t1转换为t2
Select
name,
sum(case when item=数学 then score end) as math,
sum(case when item=英语 then score end) as english,
From table
Group by name

18、窗口函数over()

mysql> select * from test;
+-------+--------+-------+
| class | name   | score |
+-------+--------+-------+
| 1     | lao    | 30    |
| 1     | lao1   | 40    |
| 2     | huang  | 50    |
| 2     | huang2 | 90    |
+-------+--------+-------+

//as可用可不用；r不能用rank否则报错；row_number() over()就是当作一个字段，因为和*是在同一层次
mysql> select *, row_number() over(partition by class order by score desc) as r from test;
+-------+--------+-------+---+
| class | name   | score | r |
+-------+--------+-------+---+
| 1     | lao1   | 40    | 1 |
| 1     | lao    | 30    | 2 |
| 2     | huang2 | 90    | 1 |
| 2     | huang  | 50    | 2 |
+-------+--------+-------+---+
mysql> select *,max(score) over(partition by class order by score desc) as r from test;
+-------+--------+-------+------+
| class | name   | score | r    |
+-------+--------+-------+------+
| 1     | lao1   | 40    | 40   |
| 1     | lao    | 30    | 40   |
| 2     | huang2 | 90    | 90   |
| 2     | huang  | 50    | 90   |
+-------+--------+-------+------+
mysql> select *, first_value(score) over(partition by class order by score desc) as r from test;
+-------+--------+-------+------+
| class | name   | score | r    |
+-------+--------+-------+------+
| 1     | lao1   | 40    | 40   |
| 1     | lao    | 30    | 40   |
| 2     | huang2 | 90    | 90   |
| 2     | huang  | 50    | 90   |
+-------+--------+-------+------+