mysql中的sql语句练习

# 查询"`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'))