
来源:数据STUDIO
作者:云朵君
查询"01"课程比"02"课程成绩高的学生信息及课程分数
分析
第一步:根据需要查询的最终结果确认所需用到的表:"学生信息及课程分数",需要用到学生信息表与成绩表;
第二步:确认条件:"01"课程比"02"课程成绩高的学生,需要先分别查出"01"课程的成绩与"02"课程,再根据条件"成绩更高"筛选出学生;
#查询01课程的成绩 SELECT * FROM sc WHERE c_id='01';
#查询02课程的成绩 SELECT * FROM sc WHERE c_id='02';
第三步:查询:根据第二步筛选出的学生,查询出对应学生的"学生信息及课程分数"。
语句
SELECT stu.*,sc.c_id,sc.score FROM (SELECT * FROM sc WHERE c_id='01') t1 JOIN (SELECT * FROM sc WHERE c_id='02') t2 ON t1.s_id=t2.s_id JOIN stu ON t1.s_id=stu.s_id JOIN sc ON stu.s_id=sc.s_id WHERE t1.score>t2.score;
结果
s_ids_names_births_sexc_idscore02钱电1990-12-21男017002钱电1990-12-21男026002钱电1990-12-21男038004李云1990-08-06男015004李云1990-08-06男023004李云1990-08-06男0320
如果要求课程分数需要"课程名称"与"对应分数",则还需要跟课程表连接以显示课程名及分数。
语句
SELECT stu.*,co.c_name,sc.score FROM (SELECT * FROM sc WHERE c_id='01') t1 JOIN (SELECT * FROM sc WHERE c_id='02') t2 ON t1.s_id=t2.s_id JOIN stu ON t1.s_id=stu.s_id JOIN sc ON stu.s_id=sc.s_id JOIN co ON sc.c_id=co.c_id WHERE t1.score>t2.score;
结果
s_ids_names_births_sexc_namescore02钱电1990-12-21男语文7004李云1990-08-06男语文5002钱电1990-12-21男数学6004李云1990-08-06男数学3002钱电1990-12-21男英语8004李云1990-08-06男英语20
以上例子中先分别查出"01"课程的成绩与"02"课程,再根据查询出的结果去查询对应学生信息及课程成绩,即用到本节将要介绍的子查询。
在SELECT语句中,子查询总是从内向外处理。在处理上面的SELECT语句时,MySQL实际上执行了两个操作。
首先,它执行下面的查询:
SELECT t1.* FROM (SELECT * FROM sc WHERE c_id='01') t1 JOIN (SELECT * FROM sc WHERE c_id='02') t2 ON t1.s_id=t2.s_id WHERE t1.score>t2.score;
此时得到两个学生编号s_id等于02和04表格:
s_idc_idscore020170040150
然后,将得到的表格t与另外两个表格连接后再查询。
SELECT stu.*,sc.c_id,sc.score FROM t JOIN stu ON t1.s_id=stu.s_id JOIN sc ON stu.s_id=sc.s_id
其实这里也分了两步:
SELECT stu.*,sc.c_id,sc.score FROM t JOIN stu ON t1.s_id=stu.s_id
结果:
s_ids_names_births_sexc_idscore02钱电1990-12-21男017004李云1990-08-06男0150
这里只得到了这两个学生的课程1的成绩,结果需要查询到这两个学生所有课程的成绩,因此需要将上述得到的表格tt再与成绩表连接。
SELECT stu.*,sc.c_id,sc.score FROM tt JOIN sc ON stu.s_id=sc.s_id
格式化SQL 包含子查询的SELECT 语句难以阅读和调试,特别是它们较为复杂时更是如此。如上所示把子查询分解为多行并且适当地进行缩进,能极大地简化子查询的使用。
查询没学过"张三"老师授课的同学的信息
首先需要查询出"张三"老师授课信息:
SELECT stu.s_id FROM te LEFT JOIN co ON te.t_id=co.t_id LEFT JOIN sc ON co.c_id=sc.c_id LEFT JOIN stu ON sc.s_id=stu.s_id WHERE t_name='张三';
得到"张三"老师授课信息s_id为01,02,03,04,05,07。然后,这两个值以 NOT IN 操作符要求的逗号分隔的格式传递给外部查询的 WHERE 子句。
外部查询变成:
SELECT * FROM stu WHERE s_id NOT IN (01,02,03,04,05,07);
结果:
s_ids_names_births_sex06吴兰1992-03-01女08王菊1990-01-20女
可见,在 WHERE子句中使用子查询能够编写出功能很强并且很灵活的SQL语句。对于能嵌套的子查询的数目没有限制,不过在实际使用时由于性能的限制,不能嵌套太多的子查询。
列必须匹配 在WHERE子句中使用子查询(如这里所示),应该保证SELECT语句具有与WHERE 子句中相同数目的列。通常,子查询将返回单个列并且与单个列匹配,但如果需要也可以使用多个列。
使用子查询的另一方法是创建计算字段。
查询每位学生选修的课程数
首先可使用 SELECT COUNT(*)对表中的行进行计数,并且通过提供一条WHERE子句来过滤某个特定的学生,可仅对该学生的课程进行计数。
SELECT COUNT(c_id) FROM sc WHERE s_id = '01';
为了对每个学生执行COUNT(*)计算,应该将COUNT(*)作为一个子查询。
SELECT *, (SELECT COUNT(c_id)
FROM sc
WHERE sc.s_id = stu.s_id) AS cos FROM stu;
结果:
s_ids_names_births_sexcos01赵雷1990-01-01男302钱电1990-12-21男303孙风1990-05-20男304李云1990-08-06男305周梅1991-12-01女206吴兰1992-03-01女207郑竹1992-04-21女208王菊1990-01-20女0
这条SELECT语句对customers表中每个学生返回5列:s_id,s_name,s_birth,s_sex和cos。cos是一个计算字段,它是由圆括号中的子查询建立的。该子查询对检索出的每个学生执行一次。在此例子中,该子查询执行了8次,因为检索出了8个学生。
子查询中的 WHERE子句使用了完全限定列名,任何时候只要列名可能有多义性,就必须使用这种语法(表名和列名由一个句点分隔)。如果不使用完全限定的列名会与本身匹配。
逐渐增加子查询来建立查询 用子查询测试和调试查询很有技巧性,特别是在这些语句的复杂性不断增加的情况下更是如此。用子查询建立(和测试)查询的最可靠的方法是逐渐进行,这与MySQL处理它们的方法非常相同。首先,建立和测试最内层的查询。然后,用硬编码数据建立和测试外层查询,并且仅在确认它正常后才嵌入子查询。这时,再次测试它。对于要增加的每个查询,重复这些步骤。这样做仅给构造查询增加了一点点时间,但节省了以后(找出查询为什么不正常)的大量时间,并且极大地提高了查询一开始就正常工作的可能性。
数据库建立即数据导入准备
-- 创建数据库school CREATE DATABASE school; -- 选择进入school数据库 USE school; -- ------------建表导数------------- -- 创建stu CREATE TABLE stu(
s_id VARCHAR(10) PRIMARY KEY,
s_name VARCHAR(10) NOT NULL,
s_birth DATE,
s_sex VARCHAR(10)) -- 导入数据 INSERT INTO stu VALUES ('01' , '赵雷' , '1990-01-01' , '男'),
('02' , '钱电' , '1990-12-21' , '男'),
('03' , '孙风' , '1990-05-20' , '男'),
('04' , '李云' , '1990-08-06' , '男'),
('05' , '周梅' , '1991-12-01' , '女'),
('06' , '吴兰' , '1992-03-01' , '女'),
('07' , '郑竹' , '1992-04-21' , '女'),
('08' , '王菊' , '1990-01-20' , '女'); SELECT * FROM stu; -- 检查数据 SELECT COUNT(*) FROM stu; -- 检查总行数 -- 创建co CREATE TABLE co(
c_id VARCHAR(10) PRIMARY KEY,
c_name VARCHAR(10),
t_id VARCHAR(10)); -- 导入数据 INSERT INTO co VALUES ('01' , '语文' , '02'),
('02' , '数学' , '01'),
('03' , '英语' , '03'); SELECT * FROM co; -- 检查数据 SELECT COUNT(*) FROM co; -- 检查总行数 -- 创建te CREATE TABLE te(
t_id VARCHAR(10) PRIMARY KEY,
t_name VARCHAR(10)); -- 导入数据 INSERT INTO te VALUES ('01' , '张三'),
('02' , '李四'),
('03' , '王五'); SELECT * FROM te; -- 检查数据 SELECT COUNT(*) FROM te; -- 检查总行数 -- 创建sc CREATE TABLE sc(
s_id VARCHAR(10),
c_id VARCHAR(10),
score int); -- 导入数据 INSERT INTO sc VALUES ('01' , '01' , 80),
('01' , '02' , 90),
('01' , '03' , 99),
('02' , '01' , 70),
('02' , '02' , 60),
('02' , '03' , 80),
('03' , '01' , 80),
('03' , '02' , 80),
('03' , '03' , 80),
('04' , '01' , 50),
('04' , '02' , 30),
('04' , '03' , 20),
('05' , '01' , 76),
('05' , '02' , 87),
('06' , '01' , 31),
('06' , '03' , 34),
('07' , '02' , 89),
('07' , '03' , 98); SELECT * FROM sc; -- 检查数据 SELECT COUNT(*) FROM sc; -- 检查总行数
数据分析咨询请扫描二维码
若不方便扫码,搜微信号:CDAshujufenxi
剖析 CDA 数据分析师考试题型:解锁高效备考与答题策略 CDA(Certified Data Analyst)数据分析师考试作为衡量数据专业能力的 ...
2025-07-04SQL Server 字符串截取转日期:解锁数据处理的关键技能 在数据处理与分析工作中,数据格式的规范性是保证后续分析准确性的基础 ...
2025-07-04CDA 数据分析师视角:从数据迷雾中探寻商业真相 在数字化浪潮席卷全球的今天,数据已成为企业决策的核心驱动力,CDA(Certifie ...
2025-07-04CDA 数据分析师:开启数据职业发展新征程 在数据成为核心生产要素的今天,数据分析师的职业价值愈发凸显。CDA(Certified D ...
2025-07-03从招聘要求看数据分析师的能力素养与职业发展 在数字化浪潮席卷全球的当下,数据已成为企业的核心资产,数据分析师岗位也随 ...
2025-07-03Power BI 中如何控制过滤器选择项目数并在超限时报错 引言 在使用 Power BI 进行数据可视化和分析的过程中,对过滤器的有 ...
2025-07-03把握 CDA 考试时间,开启数据分析职业之路 在数字化转型的时代浪潮下,数据已成为企业决策的核心驱动力。CDA(Certified Da ...
2025-07-02CDA 证书:银行招聘中的 “黄金通行证” 在金融科技飞速发展的当下,银行正加速向数字化、智能化转型,海量数据成为银行精准 ...
2025-07-02探索最优回归方程:数据背后的精准预测密码 在数据分析和统计学的广阔领域中,回归分析是揭示变量之间关系的重要工具,而回 ...
2025-07-02CDA 数据分析师报考条件全解析:开启数据洞察之旅 在当今数字化浪潮席卷全球的时代,数据已成为企业乃至整个社会发展的核心驱 ...
2025-07-01深入解析 SQL 中 CASE 语句条件的执行顺序 在 SQL 编程领域,CASE语句是实现条件逻辑判断、数据转换与分类的重要工 ...
2025-07-01SPSS 中计算三个变量交集的详细指南 在数据分析领域,挖掘变量之间的潜在关系是获取有价值信息的关键步骤。当我们需要探究 ...
2025-07-01CDA 数据分析师:就业前景广阔的新兴职业 在当今数字化时代,数据已成为企业和组织决策的重要依据。数据分析师作为负责收集 ...
2025-06-30探秘卷积层:为何一个卷积层需要两个卷积核 在深度学习的世界里,卷积神经网络(CNN)凭借其强大的特征提取能力 ...
2025-06-30探索 CDA 数据分析师在线课程:开启数据洞察之旅 在数字化浪潮席卷全球的当下,数据已成为企业决策、创新与发展的核心驱 ...
2025-06-303D VLA新范式!CVPR冠军方案BridgeVLA,真机性能提升32% 编辑:LRST 【新智元导读】中科院自动化所提出BridgeVLA模型,通过将 ...
2025-06-30LSTM 为何会产生误差?深入剖析其背后的原因 在深度学习领域,LSTM(Long Short-Term Memory)网络凭借其独特的记忆单元设 ...
2025-06-27LLM进入拖拽时代!只靠Prompt几秒定制大模型,效率飙升12000倍 【新智元导读】最近,来自NUS、UT Austin等机构的研究人员创新 ...
2025-06-27探秘 z-score:数据分析中的标准化利器 在数据的海洋中,面对形态各异、尺度不同的数据,如何找到一个通用的标准来衡量数据 ...
2025-06-26Excel 中为不同柱形设置独立背景(按数据分区)的方法详解 在数据分析与可视化呈现过程中,Excel 柱形图是展示数据的常用工 ...
2025-06-26