百木园-与人分享，
就是让自己快乐。
当前位置：百木园 > 野生技术 > 正文

SQL语句实战学习

2022-07-25 分类：野生技术 阅读(234) 评论(0)

参考：https://zhuanlan.zhihu.com/p/38354000
再次感谢作者的整理！！

1.数据已提前准备好了，已知有如下4张表：
学生表：student 

成绩表：score（学号，课程号，成绩）

 课程表：course

教师表：teacher

 

 2.1 简单查询

2.1.1 查询姓\"曹\"的学生名单

SELECT * FROM student WHERE `name` LIKE \'曹%\'

 

 

2.1.2 查询姓名中最后一个是\"玉\"的学生名单

SELECT * FROM student WHERE `name` LIKE \'%玉\'

 

 

2.1.3 查询姓名中带\"星\"的学生名单

SELECT * FROM student WHERE `name` LIKE \'%星%\'

 

 

2.2 汇总查询

2.2.1 查询课程编号为\"0002\"的总成绩

SELECT SUM(grade) as \'总成绩\' FROM score WHERE course_id=\'0002\'

 

2.2.2 查询选了课程的学生人数

SELECT COUNT(DISTINCT stu_id)  as \'选了课程的学生人数\' FROM score # DISTINCT  去重

 

 

2.3 分组查询

2.3.1 查询各科成绩最高和最低得分

SELECT MAX(grade) as \'最高分\',MIN(grade) as \'最低分\' FROM score ORDER BY grade

 

 

2.3.2 查询每门课程被选修的学生数

SELECT course_id as \'课程号\' ,COUNT(stu_id) as \'学生人数\' FROM score GROUP BY course_id

2.3.3 查询学生中男、女人数

SELECT sex,COUNT(sex) as \'人数\' FROM student GROUP BY sex

 

 

2.4 带条件的分组查询

2.4.1 查询平均成绩大于60分学生的学号和平均成绩

SELECT stu_id as \'学号\', AVG(grade) as \'平均成绩\' FROM score GROUP BY stu_id HAVING AVG(grade) > 60

 

2.4.2 查询至少选修两门课程的学生学号以及课程数目

SELECT stu_id as \'学生学号\', COUNT(course_id) as \'课程数目\' FROM score GROUP BY stu_id HAVING COUNT(course_id) > 2

2.4.3 查询同名同姓学生名单并统计同名人数

SELECT name as \'学生名单\' ,COUNT( name) as \'同名人数\' FROM student GROUP BY `name`

 

2.4.4 查询不及格的课程并按照课程号从大到小排序

SELECT course_id,grade from score WHERE grade < 85 ORDER BY grade DESC

 

2.4.5 查询每门课程的平均成绩，结果按照平均成绩升序排序，平均成绩相同时，按照课程好降序排序

SELECT course_id as \'课程号\',AVG(grade) as \'平均成绩\' from score GROUP BY course_id ORDER BY AVG(grade) and course_id

 

2.4.6 查询其中课程编号为\"0003\"且分数小于90的学生学号，结果按照分数降序排列

SELECT course_id , stu_id, grade from score WHERE course_id=\'0003\' AND grade < 90 ORDER BY grade DESC

2.4.7 查询课程号和选修此课程人数，查询结果按照人数排序降序，若人数相同，按照课程号升序排序

SELECT course_id as \'课程号\', COUNT(course_id) as \'课程人数\' from score GROUP BY course_id ORDER BY COUNT(course_id) DESC,course_id ASC

 

2.4.8 查询两门以上成绩不满85分的同学的学号及其平均成绩

SELECT stu_id as \'学号\', AVG(grade) as \'平均成绩\' from score WHERE grade <= 85 GROUP BY stu_id HAVING COUNT(course_id) >= 2

2.4.9 查询各科成绩前两名的记录

(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);

 

2.5 汇总查询

 

2.5.1 查询学生的成绩并进行排名

 

SELECT stu_id , SUM(grade) FROM score GROUP BY stu_id ORDER BY SUM(grade)

 

2.5.2 查询平均成绩大于80分的学生的学号和平均成绩

SELECT stu_id ,AVG(grade) FROM score GROUP BY stu_id HAVING AVG(grade) > 80

 

2.5.3 查询所有课程成绩小于85分的学生的学号、姓名

SELECT id , name  FROM student WHERE  id in (SELECT stu_id  FROM score GROUP BY stu_id HAVING AVG(grade) < 85);

 

2.5.3 查询没有学全所有课程的学生的学号、姓名

SELECT id , name  FROM student WHERE  id in ( SELECT stu_id  FROM score GROUP BY stu_id HAVING COUNT(course_id) < 3);

 

2.5.4 查询1996年出生的学生名单

SELECT * FROM student WHERE year(brith) = 1996

 

2.5.5 查询各学生的年龄

SELECT id,`name`,TIMESTAMPDIFF(year,brith,now()) from student;

 

 

2.6 多表查询

2.6.1 查询所有学生的学号、姓名、选课数、总成绩

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

 

2.6.2 查询平均成绩大于85分的所有学生的学号、姓名、平均成绩

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

 

2.6.3 查询学生的选课情况：学号、姓名、课程号、课程名称

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;

 

2.6.4 查询出每门课程的大于80得人数和不大于80的人数

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

 

2.6.5 使用分段[90,100],[80-90),[70,80),[60,70)区间统计各科成绩，统计各分段人数和，课程号，课程名称

 

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;

 

 

 

2.6.6. 查询课程编号为\"0003\"且课程成绩在90分以上的学生的学号和姓名

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;

 

 

2.6.7 数据的行列如何互换？

# 使用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;

 

 

 

2.7 多表连接查询

2.7.1 查询课程号为\"0001\"的课程分数小于90的学生信息，按照分数降序排列

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

 

 

2.7.2 查询不同老师所教的不同课程的平均分从高到低显示

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

 

 

2.7.3 查询课程名称为\"数学\"，且分数低于90的学生姓名和分数

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

 

 

2.7.4 查询两门及其以上课程小于85的同学的学号，姓名及其平均成绩

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;

 

 

2.7.5 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

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

 

 

2.7.6 查询课程编号为“0002”的课程比“0001”的课程成绩高的所有学生的学号

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;

 

 

2.7.7 查询学过编号为“0001”的课程并且也学过编号为“0002”的课程的学生的学号、姓名

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

 

 

2.7.8 查询学过“陈独秀”老师所教的所有课的同学的学号、姓名

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

 

 

2.7.9 查询至少有一门课与学号为“0001”的学生所学课程相同的学生的学号和姓名`



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\';


 




 


 


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


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




 




来源：https://www.cnblogs.com/lxxduang/p/16512481.html

本站部分图文来源于网络，如有侵权请联系删除。

		

										
未经允许不得转载：百木园 » SQL语句实战学习
		
					

		
		
标签：

		
		
		            
        
				
相关推荐
  • 暂无文章
					

	

	


热门文章