参考:https://zhuanlan.zhihu.com/p/38354000
再次感谢作者的整理!!
1.数据已提前准备好了,已知有如下4张表:
学生表:student
成绩表:score(学号,课程号,成绩)
课程表:course
教师表:teacher
2.1 简单查询
SELECT * FROM student WHERE `name` LIKE '曹%'
SELECT * FROM student WHERE `name` LIKE '%玉'
SELECT * FROM student WHERE `name` LIKE '%星%'
SELECT SUM(grade) as '总成绩' FROM score WHERE course_id='0002'
SELECT COUNT(DISTINCT stu_id) as '选了课程的学生人数' FROM score # DISTINCT 去重
SELECT MAX(grade) as '最高分',MIN(grade) as '最低分' FROM score ORDER BY grade
SELECT course_id as '课程号' ,COUNT(stu_id) as '学生人数' FROM score GROUP BY course_id
SELECT sex,COUNT(sex) as '人数' FROM student GROUP BY sex
SELECT stu_id as '学号', AVG(grade) as '平均成绩' FROM score GROUP BY stu_id HAVING AVG(grade) > 60
SELECT stu_id as '学生学号', COUNT(course_id) as '课程数目' FROM score GROUP BY stu_id HAVING COUNT(course_id) > 2
SELECT name as '学生名单' ,COUNT( name) as '同名人数' FROM student GROUP BY `name`
SELECT course_id,grade from score WHERE grade < 85 ORDER BY grade DESC
SELECT course_id as '课程号',AVG(grade) as '平均成绩' from score GROUP BY course_id ORDER BY AVG(grade) and course_id
SELECT course_id , stu_id, grade from score WHERE course_id='0003' AND grade < 90 ORDER BY grade DESC
SELECT course_id as '课程号', COUNT(course_id) as '课程人数' from score GROUP BY course_id ORDER BY COUNT(course_id) DESC,course_id ASC
SELECT stu_id as '学号', AVG(grade) as '平均成绩' from score WHERE grade <= 85 GROUP BY stu_id HAVING COUNT(course_id) >= 2
(SELECT * from score WHERE course_id = '0001' ORDER BY grade DESC LIMIT 2) union (SELECT * from score WHERE course_id = '0002' ORDER BY grade DESC LIMIT 2) union (SELECT * from score WHERE course_id = '0003' ORDER BY grade DESC LIMIT 2);
SELECT stu_id , SUM(grade) FROM score GROUP BY stu_id ORDER BY SUM(grade)
SELECT stu_id ,AVG(grade) FROM score GROUP BY stu_id HAVING AVG(grade) > 80
SELECT id , name FROM student WHERE id in (SELECT stu_id FROM score GROUP BY stu_id HAVING AVG(grade) < 85);
SELECT id , name FROM student WHERE id in ( SELECT stu_id FROM score GROUP BY stu_id HAVING COUNT(course_id) < 3);
SELECT * FROM student WHERE year(brith) = 1996
SELECT id,`name`,TIMESTAMPDIFF(year,brith,now()) from student;
SELECT s1.id , s1.`name`, COUNT(s2.course_id) as 'count', SUM(grade) FROM student as s1 JOIN score as s2 WHERE s1.id = s2.stu_id GROUP BY s2.stu_id
SELECT s1.id , s1.`name`, AVG(grade) FROM student as s1 JOIN score as s2 WHERE s1.id = s2.stu_id GROUP BY s2.stu_id HAVING AVG(grade) > 85
SELECT s1.id as '学号', s1.`name` as '姓名', s2.course_id as '课程号' ,c1.`name` as '课程名称' FROM student as s1 JOIN score as s2 JOIN course as c1 WHERE s1.id = s2.stu_id and s2.course_id = c1.id;
SELECT course_id, SUM(CASE WHEN grade > 80 THEN 1 ELSE 0 END) as '大于80', SUM(CASE WHEN grade <= 80 THEN 1 ELSE 0 END) as '小于80' FROM score GROUP BY course_id
SELECT s.course_id as '课程号',c1.`name` as '课程号', sum(case when s.grade >= 60 and s.grade < 70 then 1 else 0 end) as '[60,70)', sum(case when s.grade >= 70 and s.grade < 80 then 1 else 0 end) as '[70,80)', sum(case when s.grade >= 80 and s.grade < 90 then 1 else 0 end) as '[80,90)', sum(case when s.grade >= 90 and s.grade < 100 then 1 else 0 end) as '[90,100)' from score as s join course as c1 where s.course_id = c1.id group by s.course_id;
SELECT s2.id as '学号',s2.`name` as '姓名' from score as s1 join student as s2 where s1.stu_id = s2.id AND s1.course_id = '0003' AND s1.grade > 90;
# 使用max()聚合函数将三个结果中的最大的提取出
select stu_id, MAX(case course_id when '0001' then grade else 0 end ) as '课程号0001', max((case course_id when '0002' then grade else 0 end)) as '课程号0002', max((case course_id when '0003' then grade else 0 end)) as '课程号0003' FROM score group by stu_id;
select s2.id,s2.name,s1.course_id,s1.grade FROM score as s1 JOIN student as s2 WHERE s1.stu_id = s2.id AND s1.course_id = '0001' HAVING grade < 90 ORDER BY grade desc
select c1.teacher_id, s1.course_id, c1.`name`, avg(grade) FROM score as s1 JOIN course as c1 WHERE s1.course_id = c1.id GROUP BY c1.teacher_id ORDER BY AVG( s1.grade) DESC
select s2.`name` as '学生姓名', s1.grade as '分数' FROM score as s1 JOIN course as c1 JOIN student as s2 WHERE s1.course_id = c1.id AND s1.stu_id = s2.id AND c1.`name` = '数学' and s1.grade < 90
select s1.id as '学号',s1.name as '姓名',avg(s2.grade) as '平均成绩' from student as s1 join score as s2 on s1.id = s2.stu_id and s2.grade < 85 group by s2.stu_id having count(s1.id) >= 2;
SELECT DISTINCT s1.stu_id as '学生编号', s1.course_id as '课程编号', s1.grade as '学生成绩' FROM score AS s1 JOIN score AS s2 ON s1.stu_id = s2.stu_id WHERE s1.course_id != s2.course_id AND s1.grade = s2.grade
SELECT DISTINCT a.stu_id,a.grade as '0002成绩',b.grade as '0001成绩' FROM (SELECT stu_id,grade FROM score WHERE course_id = '0002' ) as a join (SELECT stu_id,grade FROM score WHERE course_id = '0001' ) as b ON a.stu_id = b.stu_id where a.grade > b.grade;
SELECT a.stu_id as '学号',s1.`name` as '姓名' FROM (SELECT stu_id FROM score WHERE course_id = '0002' ) as a join (SELECT stu_id FROM score WHERE course_id = '0001' ) as b ON a.stu_id = b.stu_id JOIN student as s1 on s1.id = b.stu_id
SELECT DISTINCT s1.stu_id as '学号',s2.`name` as '姓名' FROM (SELECT course_id,t1.`name` FROM teacher as t1 JOIN score as s1 on t1.id = s1.course_id WHERE t1.`name` = '陈独秀') as a join score as s1 JOIN student as s2 WHERE a.course_id = s1.course_id AND s1.stu_id = s2.id
SELECT
s1.id,s1.`name`
FROM student as s1
WHERE s1.id in
(SELECT DISTINCT(stu_id) from score WHERE course_id in (SELECT course_id FROM score WHERE stu_id = '0001'))
AND s1.id != '0001';
SELECT s1.stu_id as '学生号', MAX(CASE WHEN c1.`name` = '语文' THEN s1.grade ELSE 0 END) as '语文', MAX(CASE WHEN c1.`name` = '数学' THEN s1.grade ELSE 0 END) as '数学', MAX(CASE WHEN c1.`name` = '英语' THEN s1.grade ELSE 0 END) as '英语', avg(s1.grade) as '平均成绩' FROM course as c1 JOIN score as s1 ON c1.id = s1.course_id GROUP BY s1.stu_id
原文出处:SQL语句实战学习