sql练习笔记

最近在做公司的项目,由于基本使用 ECharts 做可视化分析,查数据库居多。有时候 sql 就写的不太顺手,遂最近抽时间想多练习练习。

1. 查询” 01 “课程比” 02 “课程成绩高的学生的信息及课程分数

查出” 01 “课程、” 02 “课程,再找出01课程成绩更高的

1
2
3
select *
from (SELECT SId, score from sc where sc.CId = '01') t1, (SELECT SId, score from sc where sc.CId = '02') t2
where t1.SId = t2.SId

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

以01课程为基准连接,则01的必有

1
2
3
select * from 
(SELECT SId,score from sc where sc.CId = '01') t1 LEFT JOIN (SELECT SId,score from sc where sc.CId = '02') t2
on t1.SId = t2.SId

1.3 查询不存在” 01 “课程但存在” 02 “课程的情况

不存在:not in

1
2
3
4
select * 
from sc
where sc.sid not in (SELECT SId from sc where sc.CId = '01')
and sc.cId = '02'

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

HAVING随着Group by一起使用,有过滤作用,即需要符合后续的条件

1
2
3
4
select sc.SId ,AVG(sc.score)as avgscore
from sc
GROUP BY sc.SId
HAVING AVG(sc.score)>=60

3.查询在 SC 表存在成绩的学生信息

1
2
3
select * from sc  where sc.score >= 0

select * from student,(select * from sc where sc.score >= 0) t1 where t1.sid = student.SId

1
2
3
4
5
select DISTINCT student.* from student,(select * from sc  where sc.score >= 0) t1 where t1.sid = student.SId

select DISTINCT student.*
from student ,sc
where student.SId=sc.SId

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

1
2
3
4
5
6
7
8
9
-- 查出每个学生的总成绩、选课总数
select sc.SId,sum(sc.score) sumScore, COUNT(sc.SId) courseCount from sc GROUP BY sc.SId

select student.SId,student.Sname,t1.sumscore,t1.coursecount
from student ,
(select SC.SId,sum(sc.score)as sumscore ,count(sc.CId) as coursecount
from sc
GROUP BY sc.SId) as t1
where student.SId = t1.SId

4.1查有成绩的学生信息

1
2
3
select *
from student
where EXISTS(select * from sc where student.SId=sc.SId)

5. 查询「李」姓老师的数量

1
2
select count(*) from teacher
where teacher.Tname like '李%'

6.查询学过「张三」老师授课的同学的信息

1
2
3
4
5
6
select student.*
from teacher, course, student, sc
where teacher.Tname='张三'
and teacher.TId=course.TId
and course.CId=sc.CId
and sc.SId=student.SId

sql 代码参照于此

打赏
  • 版权声明: 本博客所有文章除特别声明外,均采用 Apache License 2.0 许可协议。转载请注明出处!
  1. © 2020-2021 Lauy    湘ICP备20003709号

请我喝杯咖啡吧~

支付宝
微信