文章目录
1, 查询课程编号'01'比课程编号'02'成绩高的所有学生学号
2, 查询平均成绩大于 60 分得学生的学号和平均成绩
3, 查询所有学生的学号, 姓名, 选课数, 总成绩
4, 查询姓 "猴" 的老师的个数
5, 查询没有学过张三老师课的学生的学号和姓名
6, 查询学过张三老师所教的所有课的同学的学号和姓名
7, 查询学过编号为 01 的课程并且也学过编号为 02 的课程的学生的学号和姓名
8, 查询课程编号为 02 的总成绩
9, 查询所有课程成绩小于 60 分的学生的学号, 姓名
10, 查询没有学全所有课程的学生的学号, 姓名
1, 查询课程编号'01'比课程编号'02'成绩高的所有学生学号
- select a.s_id from
- (select * from score where c_id ='01') as a
- inner JOIN
- (select * from score where c_id ='02') as b on a.s_id = b.s_id
- where a.s_score> b.s_score
扩展, 同时查询出该学生的姓名和得分
- select a.s_id,c.s_name,a.s_score from
- (
- (select * from score where c_id ='01') as a
- inner JOIN
- (select * from score where c_id ='02') as b on a.s_id = b.s_id
- ) INNER JOIN student as c on c.s_id = b.s_id
- where a.s_score> b.s_score
2, 查询平均成绩大于 60 分得学生的学号和平均成绩
- select s_id,avg(s_score)
- from score
- group by s_id
- having avg(s_score)>60
3, 查询所有学生的学号, 姓名, 选课数, 总成绩
- select a.s_id,a.s_name,COUNT(b.c_id),sum(s_score)
- from student as a
- left JOIN score as b on a.s_id=b.s_id
- group by a.s_id
改进 null
- select a.s_id,a.s_name,COUNT(b.c_id),
- sum(case when b.s_score is null then 0 else b.s_score END)
- from student as a
- left JOIN score as b on a.s_id=b.s_id
- group by a.s_id
4, 查询姓 "猴" 的老师的个数
- select count(t.t_id)
- from teacher as t
- where t.t_name like '猴 %'
扩展 含有猴的老师的个数
==%== 表示多个字符串
- select count(t.t_id)
- from teacher as t
- where t.t_name like '% 猴 %'
扩展二, 几个以 "张" 开头的不重复姓名. 需要去重
- select count(distinct t.t_name)
- from teacher as t
- where t.t_name like '张 %'
5, 查询没有学过张三老师课的学生的学号和姓名
- select s_id,s_name
- from student
- where s_id not in(
- select s_id
- from score
- where c_id =(
- select c_id
- from course
- where t_id=(
- select t_id
- from teacher
- where t_name ='张三'
- )
- )
- )
使用连接
- select s_id,s_name from student where s_id not in(
- select score.s_id from teacher as t
- inner join course as c on t.t_id = c.t_id
- inner join score on c.c_id=score.c_id
- where t.t_name='张三'
- )
6, 查询学过张三老师所教的所有课的同学的学号和姓名
- select s_id,s_name
- from student
- where s_id in(
- select s_id
- from score
- where c_id =(
- select c_id
- from course
- where t_id=(
- select t_id
- from teacher
- where t_name ='张三'
- )
- )
- )
扩展
- select s_id,s_name from student where s_id in(
- select score.s_id from teacher as t
- inner join course as c on t.t_id = c.t_id
- inner join score on c.c_id=score.c_id
- where t.t_name='张三'
- )
7, 查询学过编号为 01 的课程并且也学过编号为 02 的课程的学生的学号和姓名
- select s_id,s_name from student
- where s_id in(
- select a.s_id from
- (select * from score where c_id = '01') as a
- INNER JOIN
- (select * from score where c_id = '02') as b
- on a.s_id = b.s_id
- )
8, 查询课程编号为 02 的总成绩
select sum(s_score) from score group by c_id having c_id='02'
扩展 avg sum count
每门课的选课人数, 平均数, 总分数
select c_id, sum(s_score),avg(s_score),count(s_score) from score group by c_id
9, 查询所有课程成绩小于 60 分的学生的学号, 姓名
- select s_id,s_name from student
- where s_id in
- (
- select a.s_id from
- (
- select s_id,count(c_id) as cnt
- from score
- where s_score <60
- group by s_id
- ) as a
- INNER JOIN
- (
- select s_id ,count(c_id) as cnt
- from score
- group by s_id
- ) as b on a.s_id=b.s_id
- where a.cnt = b.cnt
- )
10, 查询没有学全所有课程的学生的学号, 姓名
- select a.s_id from
- (select * from score where c_id ='01') as a
- inner JOIN
- (select * from score where c_id ='02') as b on a.s_id = b.s_id
- where a.s_score> b.s_score
补充知识
inner join(等值连接) 只返回两个表中联结字段相等的行
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
INNER JOIN 语法:
INNER JOIN 连接两个数据表的用法:
SELECT * FROM 表 1 INNER JOIN 表 2 ON 表 1. 字段号 = 表 2. 字段号
INNER JOIN 连接三个数据表的用法:
SELECT * FROM (表 1 INNER JOIN 表 2 ON 表 1. 字段号 = 表 2. 字段号) INNER JOIN 表 3 ON 表 1. 字段号 = 表 3. 字段号
来源: https://blog.csdn.net/weixin_43304253/article/details/122378005