# 查询"`course`"02"课程成绩高的学生的信息及课程分数
SELECT st.*, sc.`s_score` AS '语文', sc2.`s_score` AS '数学' FROM student st
LEFT JOIN score sc ON sc.`s_id`= st.`s_id` AND sc.`c_id`="01"
LEFT JOIN score sc2 ON sc2.`s_id`= st.`s_id` AND sc2.`c_id`="02"
WHERE sc.`s_score`> sc2.`s_score`;# 查询01课程比02课程成绩低的学生的信息及课程分数
SELECT st.*, sc.`s_score` AS '语文', sc2.`s_score` AS '数学' FROM student st
LEFT JOIN score sc ON sc.`s_id`= st.`s_id` AND sc.`c_id`="01"
LEFT JOIN score sc2 ON sc2.`s_id`= st.`s_id` AND sc2.`c_id`="02"
WHERE sc.`s_score`< sc2.`s_score`;#查询平均成绩大于60分的同学的学生编号和学生姓名和平均成绩
SELECT st.`s_name`, st.`s_id`, ROUND(AVG(sc.`s_score`),2) FROM student st
LEFT JOIN score sc ON sc.`s_id`= st.`s_id`
GROUP BY st.s_id HAVING(AVG(sc.`s_score`))>= 60#查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
SELECT st.`s_name`, st.`s_id`, ROUND(AVG(sc.`s_score`),2) FROM student st
LEFT JOIN score sc ON sc.`s_id`= st.`s_id`
GROUP BY st.s_id HAVING(AVG(sc.`s_score`))< 60#查询所有同学的学生编号，学生姓名，选课总数，所有的课程的总成绩
SELECT st.`s_name`, st.`s_id`, COUNT(sc.`c_id`) AS '选课的总数' , SUM(CASE WHEN sc.s_score IS NULL THEN 0 ELSE sc.s_score END ) AS "总成绩" FROM student st
LEFT JOIN score sc ON sc.`s_id`= st.`s_id` GROUP BY st.`s_id`# 查询李姓老师的数量#注意点就是group by 必须与having过滤函数搭配使用
SELECT COUNT(*), t.t_name FROM teacher t GROUP BY t.t_name HAVING (t.t_name LIKE '李%');#查询学过张三老师授课的学生信息`score`
SELECT st.* FROM student st
LEFT JOIN score sc ON st.`s_id`= sc.`s_id`
LEFT JOIN course cr ON sc.`c_id`= cr.`c_id`
LEFT JOIN teacher t ON cr.`t_id`= t.`t_id`
WHERE t.`t_name`="张三"#查询没学过张三老师授课的学生信息，查询学生ID不在张三老师名单列表中
SELECT s.* FROM student s WHERE s.s_id NOT IN (SELECT st.s_id FROM student st
LEFT JOIN score sc ON st.`s_id`= sc.`s_id`
LEFT JOIN course cr ON sc.`c_id`= cr.`c_id`
LEFT JOIN teacher t ON cr.`t_id`= t.`t_id`
WHERE t.`t_name`="张三")#查询学过编号为01，并且学过02的学生信息同学的信息，直接查询出对应的课程为01与02的学生信息，对学生编号做条件过滤
SELECT st.* ,sc.`c_id`, sc2.`c_id`FROM student st
INNER JOIN score sc ON st.`s_id`= sc.`s_id` AND sc.`c_id`="01"
INNER JOIN score sc2 ON st.`s_id`= sc2.s_id AND sc2.c_id ="02"
WHERE sc.`s_id`= sc2.s_id;#查询学过编号为01，但是没有学过编号为02的课程的同学的信息,注意点就是必须使用inner join 否则会出现null的情况出现
SELECT s.* FROM student s
INNER JOIN score sc ON s.`s_id`= sc.`s_id` AND sc.`c_id`="01"
WHERE s.`s_id` NOT IN (
SELECT st.`s_id` FROM student st
INNER JOIN score sc2 ON st.`s_id`= sc2.s_id AND sc2.c_id ="02")#查询没有全所有的课程的学生信息：思路：首先查询出学完所有课程的学生信息
SELECT st.*, COUNT(cr.c_id) FROM student st
INNER JOIN score sc ON st.s_id = sc.`s_id`
INNER JOIN course cr ON sc.`c_id`= cr.c_id
GROUP BY st.s_id HAVING(COUNT(cr.c_id))< 3;#查询至少有一门课与学号为01的同学所学相同的同学的信息
SELECT DISTINCT st.* FROM student st
LEFT JOIN score sc ON st.s_id = sc.`s_id`
WHERE sc.`c_id`IN(
SELECT DISTINCT(sc.c_id) FROM student st
LEFT JOIN score sc ON st.`s_id`= sc.`s_id`
WHERE sc.`s_id`='01')#查询两门及以上不及格学生的信息，学生id, 学生姓名， 平均成绩
SELECT st.s_id AS '学生id' , st.`s_name` AS '学生姓名' , AVG(sc.s_score) AS '平均成绩' FROM student st
LEFT JOIN score sc ON st.`s_id`= sc.`s_id`
WHERE st.`s_id` IN (
SELECT s_id FROM score WHERE s_score < 60 OR s_score IS NULL GROUP BY s_id HAVING(COUNT(*)> 2))
GROUP BY st.`s_id`#检索"01"课程分数小于60,按分数降序排列的学生信息
SELECT st.`s_id` AS '学生编号', st.`s_birth`'学生生日', st.`s_name`'学生姓名', st.`s_sex`'学生分数', sc.`s_score` AS '学生分数'
FROM student st LEFT JOIN score sc ON st.`s_id`= sc.`s_id`
WHERE sc.`c_id`="01" AND sc.`s_score`< 60 OR sc.`s_score` IS NULL
ORDER BY sc.`s_score` DESC#按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT st.s_id,st.s_name,sc4.s_score "平均分",sc.s_score "语文",sc2.s_score "数学",sc3.s_score "英语" FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id AND sc.c_id="01"
LEFT JOIN score sc2 ON sc2.s_id=st.s_id AND sc2.c_id="02"
LEFT JOIN score sc3 ON sc3.s_id=st.s_id AND sc3.c_id="03"
LEFT JOIN score sc4 ON sc4.s_id=st.s_id
GROUP BY st.s_id
ORDER BY AVG(sc4.s_score) DESC#查询学生的总成绩并进行排名
SELECT st.s_id,st.s_name,SUM(sc4.s_score)"学生的总成绩",sc.s_score "语文",sc2.s_score "数学",sc3.s_score "英语" FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id AND sc.c_id="01"
LEFT JOIN score sc2 ON sc2.s_id=st.s_id AND sc2.c_id="02"
LEFT JOIN score sc3 ON sc3.s_id=st.s_id AND sc3.c_id="03"
LEFT JOIN score sc4 ON sc4.s_id=st.s_id
GROUP BY st.s_id
ORDER BY SUM(sc4.s_score) DESC#查询不同老师所教不同课程平均分从高到低显示
SELECT t.t_id , AVG(sr.s_score) FROM teacher t
LEFT JOIN course cs ON t.t_id = cs.t_id
LEFT JOIN score sr ON cs.c_id = sr.c_id
GROUP BY t.t_id ORDER BY AVG(sr.s_score) DESC#查询所有课程的成绩第二名到第三名的学生信息及该课程成绩
SELECT a.s_id,a.c_id,a.s_score FROM score a
WHERE (SELECT COUNT(1) FROM score b WHERE b.c_id=a.c_id AND b.s_score>=a.s_score OR b.`s_score` IS NULL)<=3 ORDER BY a.c_id,a.s_score DESC#查询每门课成绩最好的前两名
SELECT a.s_id,a.c_id,a.s_score FROM score a
WHERE (SELECT COUNT(1) FROM score b WHERE b.c_id=a.c_id AND b.s_score>=a.s_score)<=2 ORDER BY a.c_id#查询每门课程被选修的学生数
SELECT COUNT(1) AS '课程被选修的学生数', sr.c_id AS '课程编号' FROM student st
LEFT JOIN score sr ON st.`s_id`= sr.s_id
GROUP BY sr.c_id#查询出只选修了两门课程的学号与姓名
SELECT st.*, COUNT(1) FROM student st
LEFT JOIN score sr ON st.`s_id`= sr.s_id
GROUP BY st.s_id HAVING(COUNT(1))= 2#查询男生与女生的总数
SELECT st.`s_sex` AS '性别', COUNT(1) AS '总数' FROM student st GROUP BY st.`s_sex`#查询名字中含"风"字的学生信息
SELECT * FROM student WHERE s_name LIKE "%风%"；#查询同名同姓的学生，并且统计人数
SELECT COUNT(1), st.s_name, st.s_sex FROM student st GROUP BY st.s_name, st.s_sex HAVING COUNT(1)> 1#查询1990年出生的学生名单
SELECT st.* FROM student st WHERE st.`s_birth` LIKE '1990%'#查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT sr.c_id, AVG(s_score) AS '平均成绩' FROM score sr GROUP BY sr.c_id ORDER BY AVG(s_score) DESC, sr.c_id ASC#查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
SELECT AVG(sr.`s_score`), st.* FROM student st LEFT JOIN score sr ON st.`s_id`= sr.s_id GROUP BY st.`s_id` HAVING AVG(sr.`s_score`)>= 85#查询课程名称为"数学"，且分数低于60的学生姓名和分数
SELECT st.`s_name`'学生姓名' , sr.s_score '分数' FROM student st LEFT JOIN score sr ON st.`s_id`= sr.s_id
LEFT JOIN course cr ON sr.c_id = cr.c_id WHERE cr.c_name ="数学" AND sr.s_score < 60#查询所有学生的课程及分数情况
SELECT st.`s_id`'学生姓名', sc1.s_score '语文分数', sc2.s_score '数学分数', sc3.s_score '英语分数' FROM
student st LEFT JOIN score sc ON st.`s_id`= sc.`s_id`
LEFT JOIN score sc1 ON st.`s_id`= sc1.s_id AND sc1.c_id ='01'
LEFT JOIN score sc2 ON st.`s_id`= sc2.s_id AND sc2.c_id ='02'
LEFT JOIN score sc3 ON st.`s_id`= sc3.s_id AND sc3.c_id ='03'
GROUP BY st.`s_id`#查询不及格的课程
SELECT DISTINCT(cs.c_id) FROM score sc LEFT JOIN course cs ON sc.`c_id`= cs.c_id
WHERE sc.s_score < 60 #查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
SELECT st.*, sc.`s_score` FROM student st INNER JOIN score sc ON st.`s_id`= sc.`s_id`
AND sc.`c_id`="01" AND sc.`s_score`> 80# 求每门课程的学生人数
SELECT COUNT(1)'学生人数' FROM student st INNER JOIN score sr ON st.`s_id`= sr.s_id GROUP BY sr.c_id-- 统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，
-- 若人数相同，按课程号升序排列
SELECT cs.c_name, COUNT(1) FROM score sc LEFT JOIN course cs ON sc.`c_id`= cs.c_id
GROUP BY cs.c_id HAVING COUNT(1)> 5 ORDER BY COUNT(1) DESC, cs.c_id ASC-- 检索至少选修两门课程的学生学号
SELECT st.*, COUNT(1) FROM student st INNER JOIN score sc ON st.`s_id`= sc.`s_id`
GROUP BY st.`s_id` HAVING COUNT(1)>= 2-- 查询选修了全部课程的学生信息
SELECT st.*, COUNT(1) FROM student st INNER JOIN score sc ON st.`s_id`= sc.`s_id`
GROUP BY st.`s_id` HAVING COUNT(1)>= 3-- 查询各学生的年龄
SELECT st.`s_birth` , st.`s_id`, st.`s_name` FROM student st GROUP BY st.s_id -- 查询下周过生日的学生 主要就是查询当前的周数与
SELECT st.* FROM student st
WHERE WEEK(NOW())+1=WEEK(DATE_FORMAT(st.`s_birth`,'%Y%m%d'))-- 查询本月过生日的学生信息
SELECT st.* FROM student st WHERE MONTH(NOW())= MONTH(DATE_FORMAT(st.`s_birth`, '%Y%m%d'))-- 查询上个月过生日的学生信息
SELECT st.* FROM student st WHERE (MONTH(NOW()) - 5) MOD 12 = MONTH(DATE_FORMAT(st.s_birth,'%Y%m%d'))-- 校验
SELECT MONTH(DATE_FORMAT(st.s_birth,'%Y%m%d')) FROM student st;
-- 增加一个时间戳的验证
SELECT TIMESTAMPADD(MONTH,1,NOW()) FROM student st;
SELECT st.* FROM student st
WHERE MONTH(TIMESTAMPADD(MONTH,1,NOW()))=MONTH(DATE_FORMAT(st.s_birth,'%Y%m%d'))
