MYSQL五十题思路
1.查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
首先想办法构建一张
s_id score1 score2 的这样一张表
先把课程为01的表查询出来
SELECT sid,score AS score1 FROM SC WHERE SC.cid = ‘01’
先把课程为02的表查询出来
SELECT sid,score AS score2 FROM sc WHERE sc.cid = ‘02’
利用子查询将两张表拼接在一起
--查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
SELECT * FROM student
INNER JOIN
(
SELECT Score1.s_id,Score1.s_score AS '分数1',Score2.s_score AS '分数2' FROM
( SELECT score.s_id, score.s_score FROM score WHERE score.c_id = 01 ) AS Score1
INNER JOIN
( SELECT score.s_id, score.s_score FROM score WHERE score.c_id = 02 ) AS Score2
ON Score1.s_id = Score2.s_id
WHERE Score1.s_score > Score2.s_score
)
AS T1
ON student.s_id=T1.s_id
2.-查询平均成绩大于60分的学生的学号和平均成绩(简单,第二道重点)
-- 查询平均成绩大于60分的学生的学号和平均成绩(简单,第二道重点)
--原本写法,用AVG 统计了5,6,7成绩少一门的学生成绩
SELECT score.s_id,AVG(score.s_score)
FROM
GROUP BY s_id HAVING AVG(s_score)>60
--修改写法
SELECT score.s_id,((SUM(score.s_score)/3)) AS 平均成绩
FROM score
GROUP BY score.s_id
HAVING (SUM(score.s_score)/3)>60
3、查询所有学生的学号、姓名、选课数、总成绩(不重要)
构建一张这样的表,用学生表 左连接 成绩表
-- 查询所有学生的学号、姓名、选课数、总成绩(不重要)
-- 运用子查询
SELECT student.s_id,student.s_name,T.`选课数`,T.`总分` FROM student
INNER JOIN
(
SELECT T1.s_id,T1.`选课数`,T2.`总分` FROM
(SELECT score.s_id,COUNT(score.s_id) AS '选课数' FROM score GROUP BY score.s_id) AS T1
INNER JOIN
(SELECT score.s_id,SUM(score.s_score) AS '总分' FROM score GROUP BY score.s_id) AS T2
ON T1.s_id=T2.s_id
)
AS T
ON student.s_id=T.s_id
--原本的写法
-- SELECT student.s_id,student.s_name,score.c_id,score.s_score
SELECT
Stu.s_id,
Stu.s_name,
COUNT(SC.c_id) AS '选课数',
SUM(
CASE
WHEN SC.s_score IS NULL THEN 0
ELSE SC.s_score
END
) AS '总成绩'
FROM student AS Stu
LEFT JOIN score AS SC
ON Stu.s_id=SC.s_id
GROUP BY Stu.s_id
4、查询姓“猴”的老师的个数(不重要)
SELECT COUNT(t_id)
FROM teacher
WHERE t_name LIKE '猴%';
5、查询没学过“张三”老师课的学生的学号、姓名(重点)
-- 5、查询没学过“张三”老师课的学生的学号、姓名(重点)
-- 思路
-- 1.先查询张三老师的教师号
-- 2.根据教师号去查询张三老师教授的科目名称,拿到课程id
-- 3.根据课程id,查询出选择张三老师课的学生id (因为score表是多对多,查询选张三老师的课学生是简单的)
SELECT student.s_id,student.s_name FROM student
WHERE student.s_id
NOT IN
( SELECT s_id FROM score
WHERE score.c_id=
(
SELECT course.c_id
FROM course
WHERE course.t_id=
(SELECT t_id
FROM teacher
WHERE teacher.t_name='张三')
)
)
-- 思路2 多表连接
-- 1.成绩表连接课程表,(成绩表是多对多)
-- 2.再连接老师表,获取教师名称
-- 3.查找学过张三老师课程的学生,作为student表条件过滤
SELECT student.s_id,student.s_name
FROM student
WHERE student.s_id NOT IN
( SELECT score.s_id
FROM score
INNER JOIN course
ON score.c_id=course.c_id
INNER JOIN teacher
ON course.t_id=teacher.t_id
WHERE teacher.t_name='张三'
)
6.查询学过“张三”老师所教的所有课的同学的学号、姓名(重点)
-- 查询学过“张三”老师所教的所有课的同学的学号、姓名(重点)
-- 思路1 子查询 有张三先用张三,拿到t_id 和课程表结合
-- 再跟成绩表结合
SELECT student.s_id,student.s_name FROM student
WHERE student.s_id IN
( SELECT s_id FROM score
WHERE score.c_id=
(SELECT c_id FROM course
WHERE course.t_id=
(SELECT teacher.t_id FROM teacher
WHERE teacher.t_name='张三')
)
)
7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名(重点)
-- 7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名(重点
-- 经典的错误,新手
(SELECT *
FROM score
WHERE score.c_id=01 AND score.c_id=02)
-- 7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名(重点
SELECT student.s_id,student.s_name FROM student
WHERE student.s_id IN
(
SELECT A.s_id FROM
(SELECT score.s_id,score.s_score AS '01课程分数'
FROM score
WHERE score.c_id=01) AS A
INNER JOIN
(SELECT score.s_id,score.s_score AS '02课程分数'
FROM score
WHERE score.c_id=02) AS B
ON A.s_id=B.s_id
)
9、查询所有课程成绩小于60分的学生的学号、姓名
-- 9、查询所有课程成绩小于60分的学生的学号、姓名
-- 不知道WHERE score.C_id 怎么写,因为课程数量不是固定的。
-- 所以要先查询score表中得分小于60的得分,按s_id 分组,会得到唯一的学号
-- COUNT (c_id) 得到学习了几门课
-- 使用COUNT 要用 AS !!
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.查询学全所有课的学生的学号、姓名(重点)
-- 10.查询学全所有课的学生的学号、姓名(重点)
-- 先查询所有的学生拼接成绩表 用左连接 所有成绩没有的学生会消失
-- 分组by 三门学生成绩 会变成一个成绩
-- 用having 统计 DISTINCT(去除)sc.c_id<3 COUNT 返回复合条件的行
SELECT *
FROM student
AS st
LEFT JOIN score AS sc
on st.s_id=sc.s_id
GROUP BY st.s_id
HAVING COUNT(DISTINCT sc.c_id<3)