MySQL 基本查询8--14
Bitgeek 2018-12-15 tag1
# 题目8:查询学过编号为01,并且学过编号为02课程的学生信息
-- 方法1:通过自连接实现
SELECT
a.*
FROM
Student a
WHERE
a.s_id IN (
SELECT
b.s_id
FROM
Score b
JOIN Score c ON b.s_id = c.s_id
WHERE
b.c_id = '01'
AND c.c_id = '02'
);
--方法2:通过where实现
SELECT
s1.*
FROM
Student s1,
Score s2,
Score s3
WHERE
s1.s_id = s2.s_id
AND s1.s_id = s3.s_id
AND s2.c_id = 01
AND s3.c_id = 02;
# 题目9:查询学过01课程,但是没有学过02课程的学生信息
SELECT
s1.*
FROM
Student s1
WHERE
s1.s_id IN (
SELECT
s_id
FROM
Score
WHERE
c_id = '01'
) -- 修过01课程,要保留
AND s1.s_id NOT IN (
SELECT
s_id
FROM
Score
WHERE
c_id = '02'
);-- 哪些人修过02,需要排除
# 题目10:查询没有学完全部课程的同学的信息
解题思路:在Course表中先计算总的课程数,后对学生进行分组,筛选出每组中课程数量少于总的课程数的学生。
-- 方法1
select s.*
from Student s -- 学生表
left join Score s1 -- 成绩表
on s1.s_id = s.s_id
group by s.s_id -- 学号分组
having count(s1.c_id) < ( -- 分组后学生的课程数<3
select count(*) from Course -- 全部课程数=3
)
-- 方法2
select s.*
from Student s
where s_id not in (
select s_id
from Score s1
group by s_id
having count(*) = (select count(*) from Course)
);
# 题目11:查询至少有一门课与学号为01的同学所学相同的同学的信息
SELECT
b.*
FROM
student b
LEFT JOIN score c ON b.s_id = c.s_id
WHERE
c.c_id IN ( SELECT a.c_id FROM score a WHERE a.s_id = '01' )
AND b.s_id != '01' --排除01学生自己
GROUP BY
b.s_id;
# 题目12:查询和01同学学习的课程完全相同的同学的信息
方法1:因为总课程数3,而01号同学的课程数刚好是3,所以我们只要找出在Score表中课程也修满3门的同学即可。
SELECT
b.*
FROM
student b
JOIN score c ON b.s_id = c.s_id
WHERE
b.s_id != '01'
GROUP BY
b.s_id
HAVING
COUNT( c.c_id )=(
SELECT
COUNT( c_id )
FROM
score
WHERE
s_id = '01')
方法2:使用group_concat函数(分组合并,同时排序) 1)group_concat的使用方法为:
group_concat([DISTINCT] 字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
SELECT
b.s_id,
b.s_name,
b.s_sex,
GROUP_CONCAT( c.c_id ORDER BY c.c_id ) AS concat_course
FROM
student b
JOIN score c ON b.s_id = c.s_id
GROUP BY
b.s_id
HAVING
GROUP_CONCAT( c.c_id ORDER BY c.c_id )=(
SELECT
GROUP_CONCAT( c_id ORDER BY c_id )
FROM
score
WHERE
s_id = '01'
);
# 题目13:查询没有修过张三老师讲授的任何一门课程的学生姓名
-- 方法1
SELECT
*
FROM
student
WHERE
s_id NOT IN (
SELECT DISTINCT
t1.s_id
FROM
score t1
JOIN course t2 ON t1.c_id = t2.c_id
WHERE
t2.t_id = (
SELECT
t_id
FROM
teacher
WHERE
t_name = '张三'
)
)
-- 方法2
SELECT
* -- 4、学号取反找到学生信息
FROM
Student
WHERE
s_id NOT IN (
SELECT DISTINCT
(s_id) -- 3、课程号找到对应的学号
FROM
Score
WHERE
c_id = (
SELECT
c_id -- 2、教师编号找到对应的课程号
FROM
Course
WHERE
t_id = (
SELECT
t_id -- 1、姓名找到教师编号
FROM
Teacher
WHERE
t_name = '张三'
)
)
);
# 题目14:查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
SELECT
s.s_id,
s.s_name,
t.avg_score
FROM
Student s
JOIN (
SELECT
s_id,
ROUND(AVG(s_score)) avg_score --求均值再取整
FROM
Score
WHERE
s_score < 60
GROUP BY
s_id
HAVING
COUNT(s_score) >= 2
) t ON s.s_id = t.s_id