mysql学习

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SELECT
t1.s_name,
t2.s_score AS 01_score,
t3.s_score AS 02_score

FROM
Student t1
JOIN Score t2 ON t1.s_id = t2.s_id
AND t2.c_id = '01'
LEFT JOIN Score t3 ON t1.s_id = t3.s_id
AND t3.c_id = '02'
WHERE
t2.s_score > t3.s_score;

 

解决问题的思路：本质上是查询一张表两次，分别去判断这张表的信息

 

至于为什么先是join和left join的区别：因为查询01的课程成绩高的所以01成绩必须有，所以用join，取score和student的交集，但是取02就不需要一定有值了，所以使用left join 按照student去取

------------------------------------------------------------------------------------

2、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

我的解决方案

SELECT
t1.s_id AS id,
t2.s_name AS name,
AVG( s_score ) AS avg_score
FROM
Score t1
LEFT JOIN Student t2 ON t1.s_id = t2.s_id
GROUP BY
t1.s_id
HAVING
AVG(t1.s_score) >= 60 ;

group by 分组之后的条件使用having进行判断

----------------------------------------------------

3、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT
t1.*,
count( t2.c_id ),
sum( t2.s_score )
FROM
Student t1
LEFT JOIN ( SELECT * FROM Score ) AS t2 ON t1.s_id = t2.s_id
GROUP BY
t1.s_id
ORDER BY t1.s_id ASC;

 ---------------------------------------------------

4、查询学过"张三"老师授课的同学的信息SELECT

 

select

t1.s_name as name,
t1.s_id as id,
t1.s_birth as birth
FROM
Student t1
JOIN Score t2 ON t1.s_id = t2.s_id
LEFT JOIN Course t3 on t2.c_id = t3.c_id
LEFT JOIN Teacher t4 ON t3.t_id = t4.t_id
WHERE t4.t_name = '张三';

这个有个问题，可能张三老师交了不止一门课，所以可能会出现错误。

 

 -------------------------------------------------

5、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

SELECT DISTINCT (t1.s_name) FROM Student t1
JOIN Score t2 ON t1.s_id = t2.s_id
WHERE t2.c_id in ('01','02');

------------------------------------------------

6.查询没有学全所有课程的同学的信息

SELECT
*
FROM
Student t2
WHERE
t2.s_id IN (
SELECT
s_id
FROM
( SELECT s_id, count( DISTINCT c_id ) AS c_count FROM Score GROUP BY s_id ) AS t1
WHERE
t1.c_count < ( SELECT count( DISTINCT c_id ) FROM Course )
)
OR
t2.s_id
NOT IN (SELECT s_id FROM Score);

------------------------------------------------

SELECT
s_name,
id,
avg_score
FROM
Student t3
RIGHT JOIN (
SELECT
t1.s_id AS id,
t1.avg_score AS avg_score
FROM
( SELECT count( * ) AS count, AVG( s_score ) AS avg_score, s_id FROM Score GROUP BY s_id ) AS t1
WHERE
t1.count >= 2
AND t1.avg_score < 60
) AS t2 ON t3.s_id = t2.id;