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语句中between…and边界取值的问题

1
2
select * from table1 where number between 1 and 5
执行这条语句之后的结果是包含了15
1
2
select * from table1 where number not between 1 and 5
这条语句的结果是不包含15
1
2
3
4
5
6
7
8
9
SELECT * FROM table1 
WHERE date between '2018/09/01'
AND '2018/09/30 23:59:59:999'

这条语句的结果查询时间的是'2018/09/01 00:00:00.000’ 到'2018/09/30 23:59:59:999’ ,

如果and后面的跟的时间'2018/10/01’,那么查询到的时间是'2018/10/01 00:00:00.000’,

如果数据的时间是'2018/10/01 14:25:45’,那么这条数据是查询不到的

原文链接:

sql 代码参照于此

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

请我喝杯咖啡吧~

支付宝
微信