首页 - 资讯 - 科技 - 教育 - 汽车 - 财经 - 健康 - 房产 - 文旅 - 娱乐 - 游戏
影视 |明星 |八卦 |攻略 |评测 |单机 |网游 |手游 |游戏 |艺术 |文化 |酒店 |旅游 |美景
当前位置： 首页> 娱乐> 八卦 > SQL查询技巧--使用EXPLAIN分析查询语句对学生成绩与课程分析

SQL查询技巧--使用EXPLAIN分析查询语句对学生成绩与课程分析

时间:2025/7/15 1:43:23来源：https://blog.csdn.net/weixin_64726356/article/details/142291165 浏览次数:0次

         在教育管理系统中，对学生成绩和课程的深入分析对于优化教学策略和提升学生学习成效至关重要。通过SQL查询，我们可以从数据库中提取和分析这些数据。为了进一步提高查询效率，EXPLAIN命令成为了一个不可或缺的工具，它帮助我们理解MySQL如何执行特定的查询语句。

一、数据库表的创建

数据库结构：

        我们需要创建四个主要的数据表：学生（Student）、课程（Course）、教师（Teacher）和成绩（Score）。这些表通过不同的属性相互关联，形成了一个完整的教育管理系统数据库结构。

  1. Student - 存储学生信息，包括学生编号、姓名、出生日期和性别。
  2. Course - 存储课程信息，包括课程编号、课程名称和教师编号。
  3. Teacher - 存储教师信息，包括教师编号和姓名。
  4. Score - 存储学生成绩，包括学生编号、课程编号和分数。

创建数据表

       在进行任何查询之前，首先需要创建适当的数据库表。以下是四个基本表的创建语句：

  1. 学生表（Student）

    CREATE TABLE Student(s_id VARCHAR(20),s_name VARCHAR(20) NOT NULL DEFAULT '',s_birth VARCHAR(20) NOT NULL DEFAULT '',s_sex VARCHAR(10) NOT NULL DEFAULT '',PRIMARY KEY(s_id)
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '学生表';

  2. 课程表（Course）

    CREATE TABLE Course(c_id VARCHAR(20),c_name VARCHAR(20) NOT NULL DEFAULT '',t_id VARCHAR(20) NOT NULL,PRIMARY KEY(c_id)
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '课程表';

  3. 教师表（Teacher）

    CREATE TABLE Teacher(t_id VARCHAR(20),t_name VARCHAR(20) NOT NULL DEFAULT '',PRIMARY KEY(t_id)
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '教师表';

  4. 成绩表（Score）

    CREATE TABLE Score(s_id VARCHAR(20),c_id VARCHAR(20),s_score INT(3),PRIMARY KEY(s_id,c_id)
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '成绩表';

二、数据插入

        在表创建完成后，我我们通过插入示例数据来模拟真实的使用场景。这些数据包括学生的基本信息、课程信息、教师信息以及学生的成绩记录。以下是插入数据的 SQL 语句：

学生表数据

insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');

 课程表数据

insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

 教师表数据

insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

成绩表数据

insert into Score values('01' , '01' , '80');
insert into Score values('01' , '02' , '90');
insert into Score values('01' , '03' , '99');
insert into Score values('02' , '01' , '70');
insert into Score values('02' , '02' , '60');
insert into Score values('02' , '03' , '80');
insert into Score values('03' , '01' , '80');
insert into Score values('03' , '02' , '80');
insert into Score values('03' , '03' , '80');
insert into Score values('04' , '01' , '50');
insert into Score values('04' , '02' , '30');
insert into Score values('04' , '03' , '20');
insert into Score values('05' , '01' , '76');
insert into Score values('05' , '02' , '87');
insert into Score values('06' , '01' , '31');
insert into Score values('06' , '03' , '34');
insert into Score values('07' , '02' , '89');
insert into Score values('07' , '03' , '98');

三、SQL查询

我们进行了一系列查询操作，包括：

1.查询只选修两门课程的学生

方法一：
SELECT stu.s_id, stu.s_name 
FROM student stu
JOIN score sco ON stu.s_id = sco.s_id
GROUP BY stu.s_id, stu.s_name
HAVING COUNT(DISTINCT sco.c_id) = 2;
方法二：
SELECT * 
FROM student 
WHERE s_id IN (SELECT s_id FROM score GROUP BY s_id HAVING COUNT(1) = 2
);

2. 查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

方法一：
SELECT stu.s_id, stu.s_name, sco.s_score 
FROM student stu
JOIN score sco ON stu.s_id = sco.s_id
JOIN course cou ON sco.c_id = cou.c_id
WHERE cou.t_id = (SELECT t_id FROM teacher WHERE t_name = '张三')
ORDER BY sco.s_score DESC
LIMIT 1;
方法二：
SELECTstu.s_id,stu.s_name,sco.s_score AS max_score,cou.c_name
FROMStudent stu
JOINScore sco ON stu.s_id = sco.s_id
JOINCourse cou ON sco.c_id = cou.c_id
WHEREcou.t_id = (SELECT t_id FROM Teacher WHERE t_name = '张三')
ANDsco.s_score = (SELECT MAX(s_score) FROM Score sco_innerJOIN Course cou_inner ON sco_inner.c_id = cou_inner.c_idWHERE cou_inner.t_id = (SELECT t_id FROM Teacher WHERE t_name = '张三'));

3. 查询年龄大于30岁的学生信息

SELECT * 
FROM student 
WHERE TIMESTAMPDIFF(YEAR, s_birth, CURDATE()) > 30;

4. 查询两门及其以上不及格（小于60分）课程的同学的学号，姓名及其平均成绩

SELECT stu.s_id, stu.s_name, SUM(sco.s_score) / (SELECT COUNT(1) FROM course) 平均成绩  
FROM student stu
LEFT JOIN score sco ON sco.s_id = stu.s_id
GROUP BY stu.s_id, stu.s_name
HAVING SUM(CASE WHEN IFNULL(sco.s_score, 0) < 60 THEN 1 ELSE 0 END) >= 2
UNION
SELECT s_id, s_name, 0 
FROM student 
WHERE NOT EXISTS (SELECT * FROM score WHERE s_id = student.s_id);

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

方法一：
SELECT stu.s_id, stu.s_name, SUM(sco.s_score) / courses.total_course_count AS avgScore 
FROM student stu
JOIN score sco ON stu.s_id = sco.s_id
JOIN course cou ON sco.c_id = cou.c_id,
(SELECT COUNT(1) total_course_count  FROM course) courses
GROUP BY stu.s_id, stu.s_name
ORDER BY avgScore DESC;
方法二：
SELECT stu.s_id, stu.s_name,MAX(CASE WHEN cou.c_name = '语文' THEN sco.s_score ELSE 0 END) AS 语文成绩,MAX(CASE WHEN cou.c_name = '数学' THEN sco.s_score ELSE 0 END) AS 数学成绩,MAX(CASE WHEN cou.c_name = '英语' THEN sco.s_score ELSE 0 END) AS 英语成绩,ROUND(SUM(IFNULL(sco.s_score, 0)) / courses.total_course_count, 2) AS 平均成绩 
FROM student stu
LEFT JOIN score sco ON stu.s_id = sco.s_id
LEFT JOIN course cou ON sco.c_id = cou.c_id,
(SELECT COUNT(1) total_course_count  FROM course) courses
GROUP BY stu.s_id, stu.s_name
ORDER BY 平均成绩 DESC;

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

SELECT * 
FROM student stu 
WHERE stu.s_id != '01'
AND EXISTS (SELECT 1 FROM score sco1WHERE sco1.s_id = '01'AND EXISTS (SELECT 1 FROM score sco2WHERE sco2.s_id = stu.s_id AND sco2.c_id = sco1.c_id)
);

7. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和

SELECT stu.s_id AS 学生编号, stu.s_name AS 学生姓名, 
COUNT(sco.c_id) AS 选课总数, SUM(IFNULL(sco.s_score, 0)) AS 所有课程的成绩总和 
FROM student stu
LEFT JOIN score sco ON stu.s_id = sco.s_id
GROUP BY stu.s_id, stu.s_name;

四、使用EXPLAIN分析查询

        在 SQL 中，EXPLAIN 是一个非常重要的命令，它用于获取查询的执行路径。当你对性能进行优化时，EXPLAIN 可以帮助你理解 MySQL 是如何处理你的 SQL 语句的。它提供了查询的执行路径，包括哪些索引将会被使用，表之间的连接顺序，以及每个步骤的估计执行行数等信息。

基本用法

   EXPLAIN 可以用于 SELECT、INSERT、UPDATE、DELETE 和 REPLACE 语句。它不会实际执行 SQL 语句，而是模拟执行来获取执行计划。

EXPLAIN 的输出列

  • id：查询中每个 SELECT 语句的标识，按照从大到小的顺序排列。
  • select_type：SELECT 语句的类型，如 SIMPLE、PRIMARY、DERIVED 等。
  • table：查询的表名。
  • partitions：匹配的分区。
  • type：连接类型，如 ALL、index、range、ref、eq_ref 等。
  • possible_keys：可能使用的索引。
  • key：实际使用的索引。
  • key_len：实际使用的索引的长度。
  • ref：索引列与条件进行比较的列或常量。
  • rows：估计需要检查的行数。
  • filtered：基于表之间的条件过滤后剩余行的百分比。
  • extra：额外的信息，如 Using index、Using where、Using temporary、Using filesort 等。

        在进行复杂查询时，了解查询的执行计划对于优化性能至关重要。使用 EXPLAIN 关键字可以查看查询的执行计划，帮助我们理解查询是如何被执行的，以及如何可能进行优化。

1.查询只选修两门课程的学生

explain select stu.s_id, stu.s_name from student stujoin score sco on stu.s_id = sco.s_id
group by stu.s_id, stu.s_name
having count(distinct sco.c_id) = 2;

2. 查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

explain select stu.s_id, stu.s_name, sco.s_score from student stujoin score sco on stu.s_id = sco.s_idjoin course cou on sco.c_id = cou.c_id
where cou.t_id = (select t_id from teacher where t_name = '张三')
order by sco.s_score desc
limit 1;

3. 查询年龄大于30岁的学生信息

explain select * from student where 
timestampdiff(year, s_birth, curdate())> 30;

4. 查询两门及其以上不及格（小于60分）课程的同学的学号，姓名及其平均成绩

explain select stu.s_id, stu.s_name, 
sum(sco.s_score)/(select count(1) from course) 平均成绩  from student stuleft join score sco on sco.s_id = stu.s_id
group by stu.s_id, stu.s_name
having sum(case when ifnull(sco.s_score,0) < 60 then 1 else 0 end ) >= 2
union
select s_id,s_name,0 from student where not exists(select *from score where s_id = student.s_id);

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

explain select stu.s_id, stu.s_name, 
sum(sco.s_score)/courses.total_course_count avgScore from student stujoin score sco on stu.s_id = sco.s_idjoin course cou on sco.c_id = cou.c_id,(select count(1) total_course_count  from course) courses
group by stu.s_id, stu.s_name
order by avgScore desc;

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

explain select * from student stu where stu.s_id != 01and exists(select 1 from score sco1where sco1.s_id = 01and exists(select 1 from score sco2where sco2.s_id = stu.s_id and sco2.c_id = sco1.c_id));

7. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和

explain select stu.s_id 学生编号, stu.s_name 学生姓名, count(sco.c_id)选课总数,
sum(ifnull(sco.s_score, 0))所有课程的成绩总和 from student stu
left join score sco on stu.s_id = sco.s_id
group by stu.s_id, stu.s_name;

五、总结

     通过使用EXPLAIN分析查询，我们可以更好地理解MySQL如何处理我们的SQL语句，从而发现潜在的性能瓶颈并进行优化。这不仅提高了查询效率，也为我们提供了改进数据库设计和查询策略的依据。

        在实际应用中，我们应该根据EXPLAIN的输出结果来调整索引使用、重写查询语句或优化数据库结构，以达到最佳的性能表现。此外，定期的数据库维护和索引优化也是确保查询性能的重要措施。

        通过本文的介绍和示例，希望你能对如何使用EXPLAIN进行查询分析有了更深入的理解，并能够将其应用到实际的数据库管理和优化工作中。

关键字：SQL查询技巧--使用EXPLAIN分析查询语句对学生成绩与课程分析

版权声明:

本网仅为发布的内容提供存储空间，不对发表、转载的内容提供任何形式的保证。凡本网注明“来源：XXX网络”的作品，均转载自其它媒体，著作权归作者所有，商业转载请联系作者获得授权，非商业转载请注明出处。

我们尊重并感谢每一位作者，均已注明文章来源和作者。如因作品内容、版权或其它问题，请及时与我们联系，联系邮箱：809451989@qq.com，投稿邮箱：809451989@qq.com

责任编辑：
>> 相关文章
>> 热门图片
品牌网线有哪些_app网络开发公司_网站seo优化_手机优化软件哪个好用
品牌网线有哪些_app网络开发公司_网站seo优化_手机优化软件哪个好用
网页界面设计包括哪些_软件开发如何定价_银行营销技巧和营销方法_seo含义
网页界面设计包括哪些_软件开发如何定价_银行营销技巧和营销方法_seo含义
关键词快速排名seo怎么优化_青岛软件开发公司有哪些_seo专家招聘_苏州网站建设费用
关键词快速排名seo怎么优化_青岛软件开发公司有哪些_seo专家招聘_苏州网站建设费用
鞍山58同城二手房出售_九江疫情最新情况_yahoo搜索引擎入口_快速seo排名优化
鞍山58同城二手房出售_九江疫情最新情况_yahoo搜索引擎入口_快速seo排名优化
佛山建网站定制费用_品牌建设对企业发展的重要性_怎么网上宣传自己的产品_网站服务器ip地址查询
佛山建网站定制费用_品牌建设对企业发展的重要性_怎么网上宣传自己的产品_网站服务器ip地址查询
商丘做网站优化_品牌设计前景如何_产品推广方案模板_站长素材官网免费
商丘做网站优化_品牌设计前景如何_产品推广方案模板_站长素材官网免费
公司网站推广计划书_网站备案需要提供网站建设方案书_cba赛程_优化精灵
公司网站推广计划书_网站备案需要提供网站建设方案书_cba赛程_优化精灵
快速制作app软件_如何推广app更高效_网络推广员的日常工作_新品上市怎么推广词
快速制作app软件_如何推广app更高效_网络推广员的日常工作_新品上市怎么推广词
>> 热门搜索
评论排行
图片新闻
更多>>
SQL查询技巧--使用EXPLAIN分析查询语句对学生成绩与课程分析
Web前端知识视频教程分享(五) Bootstrap
01_02_Mybatis的配置文件与基于XML的使用
建筑类培训班_宁波seo排名费用_seo优化服务是什么_怎么让网站快速收录
点击排行

邮箱：809451989@qq.com 网站地图
未经过本站允许,请勿将本站内容传播或复制