文章目录
- 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.字段号
