MySQL 基本查询1--7
Bitgeek 2022-07-10 MySQL
# 题目1:查询"01"课程比"02"课程成绩高的学生的信息、课程分数
需要输出Student表的全部信息和Score表中的s_score;
-- 方法一:使用join连接
SELECT
a.*,
b.s_score,
c.s_score
FROM
Student a
JOIN Score b ON a.s_id = b.s_id
AND b.c_id = '01'
JOIN Score c ON a.s_id = c.s_id
AND c.c_id = '02'
WHERE
b.s_score > c.s_score;
-- 方法二:采用where语句
SELECT
a.*,
b.s_score,
c.s_score
FROM
Student a,
Score b,
Score c
WHERE
a.s_id = b.s_id
AND a.s_id = c.s_id
AND b.c_id = '01'
AND c.c_id = '02'
AND b.s_score > c.s_score;
# 题目2:查询平均成绩大于等于60分且总分大于200分的同学且必须考3门的学生编号和学生姓名和平均成绩
SELECT
a.s_id,
a.s_name,
ROUND(AVG(b.s_score), 2) AS avg_score,
-- 求均值并保留2位小数
ROUND(SUM(b.s_score), 0) AS sum_score
-- 求和并保留0位小数
FROM
Student a
JOIN Score b ON a.s_id = b.s_id
GROUP BY
a.s_id
HAVING
avg_score >= 60
AND sum_score > 200
AND COUNT(b.s_score)=3;
# 题目3:查询平均成绩小于60分的同学的学生编号、学生姓名、平均成绩(包括有成绩的和无成绩)
SELECT
a.s_id,
a.s_name,
ROUND(AVG(b.s_score), 2) AS avg_score
FROM
Student a
LEFT JOIN Score b ON a.s_id = b.s_id
GROUP BY
a.s_id
HAVING
avg_score < 60;
上表所示的结果中没有"王菊",因为王菊没有成绩;
以下代码筛选出没有成绩的同学王菊;
SELECT
a.s_id,
a.s_name,
0 AS avg_score -- 将avg_score的值置为0
FROM
Student a
WHERE
a.s_id NOT IN (
-- 学生的学号不在给给定表的学号中
SELECT DISTINCT
s_id -- 查询出全部的学号
FROM
Score
);
不采用union函数,采用ifnull和null函数进行筛选计算;
-- 方法2:采用ifnull函数
SELECT
S.s_id,
S.s_name,
ROUND(AVG(IFNULL(C.s_score, 0)), 2) AS avg_score -- ifnull 函数:第一个参数存在则取它本身,不存在取第二个值0
FROM
Student S
LEFT JOIN Score C ON S.s_id = C.s_id -- 一定要采用左连接
GROUP BY
s_id
HAVING
avg_score < 60;
-- 方法3:采用null函数
SELECT
a.s_id,
a.s_name,
ROUND(AVG(b.s_score), 2) AS avg_score
FROM
Student a
LEFT JOIN Score b ON a.s_id = b.s_id
GROUP BY
a.s_id
HAVING
avg_score < 60
OR avg_score IS NULL; -- 最后的NULL判断
# 题目4:查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT
a.s_id,
a.s_name,
COUNT(b.c_id) AS count_course,
SUM(b.s_score) AS sum_socre
FROM
Student a
LEFT JOIN Score b ON a.s_id = b.s_id -- 采用left join,所以对于没有成绩的学生也可以筛选出来。
GROUP BY
a.s_id;
# 题目5:查询“李”姓老师的数量
用通配符%和like关键字来解决。
SELECT
COUNT(t_name)
FROM
Teacher
WHERE
t_name LIKE '李%'; -- 以李字开头的名字
# 题目6:查询学过张三老师授课的同学信息
-- 方法1:通过筛选出张三的id,并匹配相应的课程id,最后将学生的成绩与课程id进行匹配,最后筛选出符合条件的学生。
SELECT
a.*
FROM
Student a
JOIN Score b ON a.s_id = b.s_id
AND b.c_id =(
SELECT
c_id
FROM
Course
WHERE
t_id =(
SELECT
t_id
FROM
Teacher
WHERE
t_name = '张三'
));
-- 方法2:通过left join进行匹配
SELECT
a.*
FROM
Student a
JOIN Score b ON a.s_id = b.s_id
JOIN Course c ON b.c_id = c.c_id
JOIN Teacher d ON c.t_id = d.t_id
WHERE d.t_name='张三';
# 题目7:找出没有学过张三老师课程的学生
和上面👆的题目是互补的,考虑取反操作。
-- 方法1:通过筛选出张三的id,并匹配相应的课程id,然后将学生的成绩与张三老师课程id进行匹配,最后筛选不在次学生id范围内的学生信息。
SELECT
t3.*
FROM
Student t3
WHERE
t3.s_id NOT IN (
SELECT
t1.s_id
FROM
Score t1
JOIN Course t2 ON t1.c_id = t2.c_id
WHERE
t_id = (
SELECT
t_id
FROM
Teacher
WHERE
t_name = '张三'
)
);
-- 方法2:使用join
SELECT
a.*
FROM
Student a
WHERE
a.s_id NOT IN (
SELECT
s_id
FROM
Score a
JOIN Course b ON a.c_id = b.c_id
JOIN Teacher c ON b.t_id = c.t_id
WHERE
c.t_name = '张三'
);