1.表结构如下:
![](https://i-blog.csdnimg.cn/blog_migrate/a2b7024cb6a8cccd23244148c90016f0.gif)
2.查询:
- 查询学习课程"python"比课程 "java" 成绩高的学生的学号;
123456789
#先查询
"python"
课程和
"java"
课程的学生成绩,临时表
#让两个临时表进行比较
-- select * from course c,score s where c.c_id = s.c_id and c.c_name='python';
-- select * from course c,score s where c.c_id = s.c_id and c.c_name='java';
select
A.s_id
from
(
select
s.s_id,s.num
from
course c,score s
where
c.c_id = s.c_id
and
c.c_name=
'python'
)
AS
A ,
(
select
s.s_id,s.num
from
course c,score s
where
c.c_id = s.c_id
and
c.c_name=
'java'
)
AS
B
where
A.s_id = B.s_id
and
A.num > B.num;
- 查询平均成绩大于65分的同学的姓名和平均成绩(保留两位小数);
12
select
round(
avg
(num),2)
as
num,student.s_name
from
score s
LEFT
JOIN
student
ON
s.s_id = student.s_id
group
by
s.s_id
having
num > 65;
- 查询所有同学的姓名、选课数、总成绩;
1234
#先来分析需要哪些表:学生表/成绩表
#然后进行多表查询即可
select
s_name,
count
(*)
'选课数'
,
sum
(num)
as
'总成绩'
from
student st,score s
where
st.s_id = s.s_id
GROUP
BY
s.s_id;
- 查询所有的课程的名称以及对应的任课老师姓名;
1
select
c_name,t_name
from
course,teacher
where
course.t_id = teacher.t_id;
- 查询没学过“alex”老师课的同学的姓名;
123456789
#先看看alex教什么课程
#看看谁学了alex的课程
#最后把学了的人过滤掉就是没学过的学生
-- select c_id from teacher t, course c where t.t_id = c.t_id and t.t_name ='alex';
-- select s_id from score where c_id in(2,4);
select
s_name
from
student
where
s_id
not
in
(
select
s_id
from
score
where
c_id
in
(2,4));
- 查询学过'python'并且也学过编号'java'课程的同学的姓名;
123456
-- select * from score where score.c_id='1' and score.c_id='2'
#查询python和java课程号
-- select c_id from course where course.c_name in('python','java');
SELECT
st.s_name
from
score s ,student st
where
s.s_id = st.s_id
AND
s.c_id
in
(1,2)
GROUP
BY
s.s_id
HAVING
COUNT
(*) = 2;
- 查询学过“alex”老师所教的全部课程的同学的姓名;
12345678910
#先知道alex老师教什么课程
#然后来看看学了alex课程的学生有哪些人
#按学生分组,看看谁学的课程数 = alex老师教授的课程数
-- select c_id from teacher t, course c where t.t_id = c.t_id and t.t_name ='alex';
select
student.s_name
from
score,student
where
score.s_id =student.s_id
and
score.c_id
in
(
select
c_id
from
teacher t, course c
where
t.t_id = c.t_id
and
t.t_name =
'alex'
)
GROUP
BY
score.s_id
HAVING
count
(*) = (
select
count
(*)
from
teacher t, course c
where
t.t_id = c.t_id
and
t.t_name =
'alex'
);
- 查询挂科超过两门(包括两门)的学生姓名;
12
SELECT
student.s_name
from
score,student
where
score.s_id = student.s_id
and
score.num <60
GROUP
BY
student.s_id
HAVING
count
(*)>=2;
- 查询有课程成绩小于60分的同学的姓名;
12
SELECT
DISTINCT
student.s_name
from
score,student
where
score.s_id = student.s_id
and
score.num <60;
- 查询选修了全部课程的学生姓名;
1234
-- select count(*) from course;
select
student.s_name
from
score,student
where
score.s_id = student.s_id
GROUP
BY
score.s_id
HAVING
count
(*) = (
select
count
(*)
from
course)
- 查询至少有一门课程与“貂蝉”同学所学课程相同的同学姓名;
123456
-- SELECT c_id from score,student where score.s_id =student.s_id and student.s_name='貂蝉'
select
student.s_name
from
score,student
where
score.s_id = student.s_id
and
score.c_id
in
(
SELECT
c_id
from
score,student
where
score.s_id =student.s_id
and
student.s_name=
'貂蝉'
)
and
student.s_name <>
'貂蝉'
GROUP
BY
student.s_id;
- 查询学过'貂蝉'同学全部课程 的其他同学姓名;
123456
-- SELECT c_id from score,student where score.s_id =student.s_id and student.s_name='貂蝉'
select
student.s_name,
count
(*)
from
score,student
where
score.s_id = student.s_id
and
score.c_id
in
(
SELECT
c_id
from
score,student
where
score.s_id =student.s_id
and
student.s_name=
'貂蝉'
)
and
student.s_name <>
'貂蝉'
GROUP
BY
student.s_id
HAVING
count
(*) = (
SELECT
count
(*)
from
score,student
where
score.s_id =student.s_id
and
student.s_name=
'貂蝉'
);
- 查询和'貂蝉'同学学习的课程完全相同的,其他同学姓名;
123456789101112131415161718192021222324252627282930
解题思路:
#1. 找出与
'貂蝉'
学习课程数 相同的学生s_id (你学两门,我也学两门)
#2. 再找出学过
'貂蝉'
课程的学生,剩下的一定是至少学过一门
'貂蝉'
课程的学生
#3. 再根据学生ID进行分组,剩下学生数
count
(1) = 貂蝉学生所学课程数
#1.找出与
'貂蝉'
学习课程数 相同的学生s_id (你学两门,我也学两门)
select
*
FROM
score
where
score.s_id
in
(
select
s_id
from
score
GROUP
BY
score.s_id
HAVING
count
(*) = (
select
count
(*)
from
student,score
where
student.s_id = score.s_id
and
student.s_name=
'貂蝉'
)
)
#2.然后再找出学过
'貂蝉'
课程的学生,剩下的一定是至少学过一门
'貂蝉'
课程的学生
select
*
FROM
score
where
score.s_id
in
(
select
s_id
from
score
GROUP
BY
score.s_id
HAVING
count
(*) = (
select
count
(*)
from
student,score
where
student.s_id = score.s_id
and
student.s_name=
'貂蝉'
)
)
and
score.c_id
in
(
select
c_id
from
student,score
where
student.s_id = score.s_id
and
student.s_name=
'貂蝉'
)
#3.再根据学生ID进行分组,剩下学生数
count
(1) = 貂蝉学生所学课程数
select
*
FROM
score
where
score.s_id
in
(
select
s_id
from
score
GROUP
BY
score.s_id
HAVING
count
(*) = (
select
count
(*)
from
student,score
where
student.s_id = score.s_id
and
student.s_name=
'貂蝉'
)
)
and
score.c_id
in
(
select
c_id
from
student,score
where
student.s_id = score.s_id
and
student.s_name=
'貂蝉'
)
GROUP
BY
score.s_id
HAVING
count
(*) =(
select
count
(*)
from
student,score
where
student.s_id = score.s_id
and
student.s_name=
'貂蝉'
)
and
score.s_id !=2;
- 按平均成绩倒序显示所有学生的“python”、“java”、“linux”三门的课程成绩,按如下形式显示: 学生ID,python,java,linux,课程数,平均分
123456789101112
#1.先查询单一学生的python课程分数
select
num
from
score,course
where
score.c_id = course.c_id
AND
course.c_name =
'python'
and
score.s_id = 1;
#2.将上面查询的结果作为 列字段使用
select
s.s_id,
(
select
num
from
score,course
where
score.c_id = course.c_id
AND
course.c_name =
'python'
and
score.s_id = s.s_id )
as
'python'
,
(
select
num
from
score,course
where
score.c_id = course.c_id
AND
course.c_name =
'java'
and
score.s_id = s.s_id )
as
'java'
,
(
select
num
from
score,course
where
score.c_id = course.c_id
AND
course.c_name =
'linux'
and
score.s_id = s.s_id )
as
'linux'
,
count
(c_id)
as
'课程数'
,
avg
(num)
as
'平均分'
from
score s
GROUP
BY
s.s_id;
- 统计各科各分数段人数.显示格式:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
123456
select
score.c_id,course.c_name,
sum
(
CASE
WHEN
num
BETWEEN
85
and
100
THEN
1
ELSE
0
END
)
as
'[100-85]'
,
sum
(
CASE
WHEN
num
BETWEEN
70
and
85
THEN
1
ELSE
0
END
)
as
'[85-70]'
,
sum
(
CASE
WHEN
num
BETWEEN
60
and
70
THEN
1
ELSE
0
END
)
as
'[70-60]'
,
sum
(
CASE
WHEN
num < 60
THEN
1
ELSE
0
END
)
as
'[ <60]'
from
score,course
where
score.c_id=course.c_id
GROUP
BY
score.c_id;
- 查询每门课程被选修的次数
1
select
c_name,
count
(*)
from
course,score
where
course.c_id = score.c_id
GROUP
BY
score.c_id;
- 查询出只选修了一门课程的学生的学号和姓名
12
select
student.s_id,student.s_name
from
student,score
where
student.s_id = score.s_id
GROUP
BY
score.s_id
HAVING
count
(*)=1
- 查询学生表中男生、女生各有多少人
12345
注意:不用
group
by
分组
select
sum
(
CASE
WHEN
s_sex =
'男'
THEN
1
ELSE
0
END
)
as
'男生'
,
sum
(
CASE
WHEN
s_sex =
'女'
THEN
1
ELSE
0
END
)
as
'女生'
FROM
student
- 查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列
12
select
course.c_name,
avg
(num)
as
'平均成绩'
from
course,score
where
course.c_id = score.c_id
GROUP
BY
score.c_id
ORDER
BY
avg
(num),score.c_id
desc
;
- 查询课程名称为“python”,且分数低于60的学生姓名和分数
12
select
student.s_name,score.num
from
score,course,student
where
score.c_id = course.c_id
and
student.s_id = score.s_id
and
course.c_name =
'python'
and
score.num < 67
- 查询学习课程"python"比课程 "java" 成绩高的学生的学号;