Mysql练习(二)

-- 6、查询"李"姓老师的数量

SELECT COUNT(*) FROM teacher WHERE t_name LIKE '李%'

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

SELECT st.* FROM student st WHERE EXISTS (SELECT 1 FROM score s INNER JOIN course c ON s.c_id = c.c_id 
INNER JOIN teacher t ON t.t_id = c.t_id WHERE t.t_name = '张三' AND st.`s_id` = s.`s_id`);

-- 8、查询没学过"张三"老师授课的同学的信息

SELECT st.* FROM student st WHERE NOT EXISTS(SELECT 1 FROM score s INNER JOIN course c ON s.c_id = c.c_id 
INNER JOIN teacher t ON t.t_id = c.t_id WHERE t.t_name = '张三' AND st.`s_id` = s.`s_id`);

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

SELECT * FROM student stu WHERE EXISTS(SELECT 1 FROM score s WHERE s.`s_id` = stu.`s_id` AND s.`c_id` = '01')AND EXISTS(SELECT 1 FROM score s1 WHERE s1.`s_id`
=stu.`s_id` AND s1.`c_id` = '02');

-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

SELECT * FROM student stu WHERE EXISTS(SELECT 1 FROM score s WHERE s.`s_id` = stu.`s_id` AND s.`c_id` = '01')AND NOT EXISTS(SELECT 1 FROM score s1 WHERE s1.`s_id`
=stu.`s_id` AND s1.`c_id` = '02');

-- 11、查询没有学全所有课程的同学的信息

SELECT st.`s_name`,st.`s_sex`,cc.课程数 FROM student st,(SELECT COUNT(1) 课程数 ,s.`s_id`FROM score s INNER JOIN course c ON s.`c_id` =c.`c_id` GROUP BY s.`s_id`) AS cc WHERE st.`s_id`
= cc.s_id AND 课程数 < 3 ;

-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

SELECT  st.* FROM student st WHERE st.`s_id`
IN(SELECT DISTINCT s.`s_id` FROM score s WHERE s.`c_id` IN (SELECT score.`c_id` FROM student INNER JOIN score ON 
student.`s_id` = score.`s_id` AND student.`s_id`='01'))

-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

SELECT st.* FROM
(SELECT GROUP_CONCAT(s.`c_id` ORDER BY c_id) group1,s_id FROM score s  WHERE s.`s_id` > '01' GROUP BY `s_id`) t1 INNER JOIN
(SELECT GROUP_CONCAT(s.`c_id` ORDER BY c_id) group2,s_id FROM score s  WHERE s.`s_id` = '01' GROUP BY `s_id`) t2 ON t1.group1 = t2.group2
INNER JOIN student st ON st.`s_id` = t1.s_id

-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT st.* FROM student st WHERE NOT EXISTS(SELECT 1 FROM score s INNER JOIN course c ON s.`c_id` =c.`c_id` INNER JOIN 
teacher t ON t.`t_id` = c.`t_id` AND t.`t_name` = '张三' WHERE st.`s_id` = s.`s_id`);

-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT st.* FROM student st LEFT JOIN score s ON st.`s_id` = s.`s_id` WHERE IFNULL(s.`s_score`,0) < 60 
GROUP BY st.`s_id` HAVING COUNT(1) >=2;

-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息

SELECT st.* FROM student st LEFT JOIN score s ON s.`s_id`= st.`s_id` AND s.`c_id` = '01' WHERE s.`s_score` < 60 ORDER BY s.`s_score`

-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT st.*,IFNULL(score1.s_score,0) 科目一成绩,IFNULL(score2.s_score,0) 科目二成绩,IFNULL(score3.s_score,0) 科目三成绩,average.平均成绩 FROM student st LEFT JOIN(SELECT s.`s_id`, s.`s_score` FROM score s WHERE s.`c_id` = '01') AS score1
ON st.`s_id` = score1.`s_id` LEFT JOIN 
(SELECT s.`s_id`,s.`s_score` FROM score s WHERE s.`c_id` = '02') AS score2 ON st.`s_id` = score2.`s_id`
LEFT JOIN(SELECT s.`s_id`,s.`s_score`FROM score s WHERE s.`c_id` = '03') AS score3 ON st.`s_id` = score3.`s_id`
LEFT JOIN (SELECT s_id,AVG(s_score)平均成绩 FROM score s GROUP BY s_id) AS average ON st.`s_id` = average.s_id
ORDER BY 平均成绩 DESC;