多表连接查询和子查询

Xx震zZ

已于 2024-06-25 14:27:39 修改

阅读量1k

点赞数 10

文章标签：数据库 oracle sql

于 2024-06-05 17:55:05 首次发布

本文链接：https://blog.csdn.net/weixin_49668551/article/details/139478642

版权

一、连接查询

连接查询是SQL语言最强大的功能之一，它可以执行查询时动态的将表连接起来，然后从中查询数据。

1.1、连接两表的方法

在SQL中连接两表可以有两种方法，一种是无连接规则连接，另一种是有连接规则连接。

无连接规则连接

无连接规则连接后得到的结果是两个表中的每一行都互相连接，即结果为笛卡尔积（笛卡尔积（Cartesian Product）是数据库和集合论中的一个概念。它是指将两个集合中的每一个元素进行配对，形成一个新的集合。在数据库的上下文中，笛卡尔积是指在 SQL 查询中将两个表的所有可能的行组合在一起，生成所有可能的行对组合）。

SELECT *(或字段列表)

FROM 表名1,表名2;

FROM子句中的表名1和表名2是要连接的两个表的名称，用逗号（，）将其隔开；如果SELECT子句中使用星号（*），则查询结果中显示两个表的所有字段。

SELECT * FROM t1,t2;

运行结果显示了t1表的所有记录与t2表的所有记录进行了连接，即得到了笛卡尔积；但实际上，这并不是用户想要的结果，因为用户需要需要的是正确的连接，而并不是每行都连接起来，所以应该给连接设定连接规则。

多表无连接规则连接和两表无连接规则连接基本相同，只是在FROM子句中需要列出更多的表名，表名之间用逗号隔开，连接得到的结果同样也是笛卡尔积。

有连接规则连接

有连接规则连接其实就是在无连接规则的基础上，加上WHERE子句指定连接规则的连接方法。

SELECT *(或字段列表)

FROM 表名1,表名2

WHERE 连接规则;

示例：

SELECT * FROM t1,t2 WHERE t1.职工号=t2.职工号;

其中，连接规则是：t1.职工号=t2.职工号

这种使用等于号组成的连接，实际上叫等值连接；只有两表有共同的字段时才可以使用等值连接，例如，t1和t2表有共同的字段—职工号，只有这样才可以使用等值连接的方法连接两表。

在上面的连接规则表达式中，字段名前加上了数据表的名称，并用英文中的句号（.）将其隔开，这是因为两个表中有相同的字段名，如果不加以修饰说明，DBMS将无法辩认是哪个表的字段；所以在多表连接时，如果使用表中相同名称的字段，则应当在其前面加上表名。

Tips：在多表连接时，即使不要求在表独有的字段前加表名，但建议还是加上表名；因为这样会很清楚地表示哪个字段属于哪个表，这将对以后的维护起到很好的作用。

1.2、使用笛卡尔积解决录入难题

示例：使用stu_info表和stu_course表的笛卡尔积，生成一个必修课成绩表（bxk_score）的内容，要求是每个学生都应该选择所有的必修课。

如果SQL运行环境为MySQL或Oracle，则其查询语句如下

CREATE TABLE bxk_score AS SELECT stu_info.id as 学号,stu_info.name AS 姓名,stu_course.ID AS 课号,stu_course.course AS 课名

FROM stu_info,stu_course WHERE stu_course.type='必修' ORDER BY 学号,课号;

CREATE TABLE bxk_score AS这里的 AS 关键字表示“使用以下 SELECT 语句的结果作为新表的数据来源”。

如果SQL 运行环境为SQL Server，则其查询语句如下：

SELECT stu_info.id as 学号,stu_info.name AS 姓名,stu_course.ID AS 课号,stu_course.course AS 课名

INTO bxk_score

FROM stu_info,stu_course WHERE stu_course.type='必修' ORDER BY stu_info.id,stu_course.ID;

SELECT

s.id AS 学号,

s.name AS 姓名,

c.ID AS 课号,

c.course AS 课名

INTO bxk_score

FROM

stu_info s,

stu_course c

WHERE

c.type = '必修'

ORDER BY

s.id,

c.ID;

执行顺序

FROM：首先执行 FROM 子句。这里使用了表别名 s 和 c，表示 stu_info 表和 stu_course 表。
WHERE：根据 WHERE 子句中的条件筛选出满足条件的行，即 stu_course 表中 type 列值为 '必修' 的行。
SELECT：选择 stu_info 表中的 id 列并命名为学号，选择 stu_info 表中的 name 列并命名为姓名，选择 stu_course 表中的 ID 列并命名为课号，选择 stu_course 表中的 course 列并命名为课名。
ORDER BY：对结果集按照学号和课号进行排序。
INTO（CREATE TABLE AS SELECT）：最后，将查询结果存储到名为 bxk_score 的新表中。

本例使用了多数人觉得没用的求笛卡尔积的方法很好地解决了一个录入上的难题。

1.3、使用两表连接查询数据

数据库操作中，比起使用笛卡尔积，使用有连接规则的连接查询会更频繁一些。

示例：查询名叫“张三”的学生的所有课程的平时成绩和考试成绩

分析：stu_info表中有学生姓名，但没有成绩，而存储成绩的score表中有成绩，但没有姓名，不过两个表都有一个共享字段—学号，所以可以将这两个表连接起来进行查询：

SELECT s.id as 学号,s.name AS 姓名,c.c_id AS 课号,c.result2 AS 平时成绩,c.result1 AS 考试成绩

FROM stu_info s,score c WHERE s.name='张三' AND s.id=c.s_id

ORDER BY c.result1 DESC,c.result2 DESC;

其中WHERE子句中的条件表达式使用逻辑运算符AND，将查询条件（s.name=’张三’）和连接规则（s.id=c.s_id）整合为一体。

1.4、多表连接查询

示例：查询名叫“张三”的学生的所有课程的平时成绩和考试成绩。

分析：已经知道sut_info和score表可以用共同拥有的学号字段进行连接，接下来的问题是将stu_course表连接到上述两个表上。由于stu_info表和stu_course表没有共同字段所以不能连接，但是score表和stu_course表有共同字段—课号，因此score表和stu_course表可以连接。如此一来，经过score表的搭桥，上述三个表就可以连接了。

SELECT

s.id AS 学号,

s.name AS 姓名,

u.course AS 课名,

c.result2 AS 平时成绩,

c.result1 AS 考试成绩

FROM

stu_info s

INNER JOIN

score c ON s.id = c.s_id

INNER JOIN

stu_course u ON c.c_id = u.ID

WHERE

s.name = '张三'

ORDER BY

c.result1 DESC, c.result2 DESC;

#使用显式的 INNER JOIN 语法来明确表示只选择匹配的记录

使用INNER JOIN进行多表连接，关键字ON之后是连接表的规则；通过 INNER JOIN，查询结果只包括在所有相关表中都有匹配记录的行。

当使用 INNER JOIN 连接两个表时，SQL 引擎会：

逐行扫描每个表。
比较两个表中符合 ON 子句中条件的记录。
只返回那些在两个表中都符合连接条件的记录。

二、高级连接查询

2.1、自连接查询

表自身与自进行连接。

示例：从stu_info表中，查询“张三”所在院系的所有学生的信息。

分析：首先查询“张三”所在的院系是哪个院系，其次才能查询属于该院系的所有学生的信息。

查询“张三”所在的院系名称

SELECT institute AS 所属院系 FROM stu_info WHERE name = '张三';

根据查询，“张三”在“中文系”，下面查询中文系

SELECT * FROM stu_info WHERE institute = '中文系';

虽然通过两次查询得到了正确的结果，但是这对于提高查询效率是不利的；因为在DBMS中，通常执行两条SELECT语句的时间，总会比执行一条SELECT语句的时间要长；遇到类似本例的查询任务，应当首选自连接查询，因为自连接查询可以用一条SELECT语句完成本例的查询任务。

SELECT st1.* FROM stu_info AS st1,stu_info AS st2 WHERE st1.institute = st2.institute AND st2.name = '张三';

其中，FROM子句后要连接的两个表都是stu_info表，只是给stu_info表分别取了两个不同的别名而已；这是为了让DBMS能够区别开查询语句中引用的字段是属于第一个stu_info表还是第二个stu_info表。

“st1.*”：显示st1表的所有字段，如果将其改为“*”，则会显示st1和st2表的所有字段。

使用自连接查询时应特别注意，WHERE子句中表的连接规则，本例中连接规则是st1.institute=st2.institute，如果改为其他字段连接则会出错。例如，改为st1.id=st2.id，则运行结果中只有“张三”的一条记录。

示例：从stu_info表中，查询与“吴用”来源地相同的所有学生的学号、姓名和所属院系。

SELECT st1.id 学号,st1.name 姓名,st1.institute 所属院系 FROM stu_info AS st1,stu_info AS st2 WHERE st2.name = '吴用' AND st1.origin=st2.origin;

2.2、内连接查询

有连接规则的连接都属于内连接，内连接包括等值连接、自然连接和不等值连接三种。内连接最大的特点是只返回两个表中互相匹配的记录，而那些不能匹配的记录就被自动去除了。

等值连接

连接规则由等于号（=）组合而成，如st1.institute=st2.institute，并且列出两个表中所有字段的连接，即SELECT子句中使用（*）通配符的连接就属于等值连接。

自然连接

在等值连接的基础上稍加改动即可得到自然连接，等值连接将两个表中的所有字段全部列出，而自然连接则不将相同的字段显示两次，即在SELECT子句中列出需要显示的字段列表。

不等值连接

不等值连接的连接规则由等于号以外的运算符组成，如由>、>=、<、<=、<>或BETWEEN等。

示例1：从stu_info表中，查询所有学生的出生年代

分析：需要将stu_info表和nddzb连接起来，但是这两个表没有共同字段，所以没办法使用等值连接，而根据题意可以使用不等值连接。连接规则是如果stu_info表的出生日期在nddzb的起始年份和终止年份之间就可以连接。

SELECT st.name AS 姓名,st.birthday AS 出生日期,n.年代 FROM stu_info AS st,nddzb AS n WHERE st.birthday BETWEEN n.起始年份 AND n.终止年份;

2.3、外连接查询

在多表连接查询时，有时希望表的所有记录都被包含进去，即使没有匹配的记录也包含在查询结果集内，这时采用另一种连接查询方法—外连接查询；外连接有左外连接、右外连接和全外连接三种。

左外连接

这种连接的规则是将左外连接符号（LEFT OUTER JOIN或LEFT JOIN）左边的表的所有记录都包含到结果集中，而只将右边表中有匹配的记录包含进结果集。

SELECT * FROM stu_info LEFT OUTER JOIN user_info ON stu_info.name=user_info.name;

左外连接时左边表的所有记录都会包含到查询结果中，那些没有匹配的左边表的记录会与全部是NULL值的记录连接。

右外连接

这种连接的规则是将右外连接符号（RIGHT OUTER JOIN或RIGHT JOIN）右边表的所有记录都包含到结果集中，而只将左边表中有匹配的记录才包含进结果集。

SELECT * FROM stu_info RIGHT OUTER JOIN user_info ON stu_info.name=user_info.name;

全外连接

这种连接的规则是将两个表的所有记录都包含到结果集中，这种连接只有一种FULL OUTER JOIN连接符。

SELECT *

FROM stu_info

FULL OUTER JOIN user_info

ON stu_info.name = user_info.name;

在 MySQL 中，FULL OUTER JOIN 语法实际上是不支持的。然而，可以通过使用 LEFT JOIN 和 RIGHT JOIN 的组合来实现 FULL OUTER JOIN 的效果。

SELECT stu_info.*, user_info.*

FROM stu_info

LEFT JOIN user_info ON stu_info.name = user_info.name

UNION

SELECT stu_info.*, user_info.*

FROM stu_info

RIGHT JOIN user_info ON stu_info.name = user_info.name;

这个查询首先执行一个 LEFT JOIN，然后再执行一个 RIGHT JOIN，最后使用 UNION 操作符将两个结果集合并，从而实现 FULL OUTER JOIN 的效果。如果希望结果集中包含重复的行（即，左连接和右连接中都匹配到的行），则可以使用 UNION ALL 而不是 UNION。

2.4、交叉连接查询

交叉连接查询其实就是无连接规则的连接。

用逗号隔开表名或用CROSS JOIN关键字连接表名

SELECT stu_info.*, user_info.*

FROM stu_info,user_info;

SELECT stu_info.*, user_info.*

FROM stu_info

CROSS JOIN user_info;

交叉连接的返回结果是一个笛卡尔积，即两个表中的每一行都互相连接，例如，一个表有10行记录，另一表有20行记录记录时，对其进行交叉连接后得到的是200行记录的查询结果。因此，当两个表很大时，要谨慎对其进行交叉连接，这样会得到一个庞大的结果集。

对a、b两个表进行交叉连接操作，并将结果保存到kcb表。

#运行环境为MySQL或Oracle

CREATE TABLE kcb AS SELECT * FROM a CROSS JOIN b;

#运行环境为SQL Server

SELECT * INTO kcb FROM a CROSS JOIN b;

AS 关键字的主要作用是为列或表指定别名，增强 SQL 语句的可读性和简洁性。它还用于 CREATE TABLE ... AS SELECT ... 语法中，将查询结果用于创建和填充新表。

2.5、连接查询中使用聚合函数

聚合函数不仅可以用于单表查询中，还可以用在多表连接查询中。

示例：统计没有考过任何考试的学生人数

分析：stu_info表中存放的是所有学生的记录，score表中存放的是考过试的学生的成绩。要完成本例的要求，则应当使用stu_info表左外连接score表，这样stu_info表中没有考过任何考试的学生就与全部是NULL值的记录连接，而后统计score表部分“学号”为NULL值的记录个数就能得到没有考过任何考试的学生人数。

SELECT st.id AS 学号,st.name AS 姓名,s.s_id AS 学号,s.c_id AS 课号,s.result1 AS 考试成绩

FROM stu_info AS st

LEFT OUTER JOIN score AS s

ON st.id=s.s_id

ORDER BY s.s_id;

SELECT COUNT(*) AS 未考试的学生人数

FROM stu_info AS st

LEFT OUTER JOIN score AS s

ON st.id=s.s_id

WHERE s.s_id IS NULL;

LEFT OUTER JOIN 从左表（stu_info）中返回所有的行，即使右表（score）中没有匹配的行。如果右表中没有匹配的行，则结果集中右表的列为 NULL。

ON st.id = s.s_id：这是连接条件，将 stu_info 表的 id 列与 score 表的 s_id 列进行匹配。

由于 WHERE s.s_id IS NULL 条件的存在，COUNT(*) 只会计算那些没有在 score 表中出现的学生。

三、组合查询

SQL中还有一种组合查询，这种查询使用UNION关键字将多个SELECT语句组合起来，将多个SELECT语句的查询结果显示到一个结果集中；组合查询与连接查询不同的是，前者将多个表的查询结果竖着组合，而后者是将查询结果横着连接。

下图为组合查询与连接查询区别示意图：

3.1、使用组合查询

有时需要将多个查询语句的结果放到一起，以一个查询结果集的形式将其显示出来；这时可以使用组合查询，组合查询是使用UNION关键字将多个SELECT查询语句组合起来查询的一种查询方法。

SELECT 列1, 列2, 列3

FROM 表1

WHERE 条件1

UNION

SELECT 列1, 列2, 列3

FROM 表2

WHERE 条件2

UNION

SELECT 列1, 列2, 列3

FROM 表3

WHERE 条件3

...

UNION

SELECT 列1, 列2, 列3

FROM 表n

WHERE 条件n;

要注意以下几点：

使用 UNION 时，默认会去除重复的记录。如果希望保留所有记录（包括重复记录），可以使用 UNION ALL。
各 SELECT 语句的列数和列的数据类型必须相同。

组合查询将每一个查询语句的结果集，竖着合并组成一个新的结果集；组合查询结果集的行数，最大时等于所有单个查询的结果集之和。

示例：从stu_info表中，查询来源地为“北京市”或者所属院系为“计科系”或者年龄大于20岁的学生的信息。（MySQL环境/SQL Server环境）

#MySQL环境

SELECT * FROM stu_info WHERE origin='北京市'

UNION

SELECT * FROM stu_info WHERE institute='计科系'

UNION

SELECT * FROM stu_info WHERE TIMESTAMPDIFF(YEAR,birthday,CURDATE())>20;

#SQL Server环境

SELECT * FROM stu_info WHERE origin='北京市'

UNION

SELECT * FROM stu_info WHERE institute='计科系'

UNION

SELECT * FROM stu_info WHERE DATEDIFF(YEAR,birthday,GETDATE())>20;

TIMESTAMPDIFF 函数用于MySQL环境计算两个日期或日期时间值之间的差异；使用 TIMESTAMPDIFF(YEAR, birthday, CURDATE()) 计算了学生生日 (birthday) 到当前日期 (CURDATE()) 之间的年份差异，并选取大于 20 的记录。
在 SQL Server 环境中，DATEDIFF 函数用于计算两个日期之间的差异，GETDATE() 函数返回当前的日期和时间。

实际上，使用OR运算符也可以达到使用UNION运算符的效果，但是使用UNION和使用OR得到的结果有一点差别，就是UNION会将结果集中相同的记录自动去掉，而OR则保留相同记录。

3.2、使用UNION的规则

使用组合查询可以组合多个查询语句的结果集，不管这些结果集是来自同个数据表还是来自不同的数据表；但是使用UNION组合查询语句时，应当注意两条最重要的规则：

每个查询语句应当有相同数量的字段

在使用UNION组合查询语句时，一定要注意每个单独的SELECT子句内的字段个数一个要相同，如果不同则会出现错误。

当独立查询语句的字段个数不同时，可以在字段个数不够的地方使用常量补位；例如，在上面的第一个SELECT子句中补上一个NULL值，就可以避免错误。

每个查询语句中相应的字段的类型必须相互兼容

在每个查询语句字段个数相等的前提下，相应的字段的类型应当互相兼容。

SELECT id,name,institute FROM stu_info WHERE origin='北京市'

UNION

SELECT id,name,birthday FROM stu_info WHERE institute='计科系';

在 SQL Server 中，使用 UNION 操作符要求每个 SELECT 语句返回的列数和数据类型必须完全相同。在你的查询中，第一个 SELECT 返回的列是 id, name, institute，而第二个 SELECT 返回的列是 id, name, birthday。因此，这两个 SELECT 语句的列数不匹配，会导致 SQL Server 报错。

在这个示例中，第二个 SELECT 查询将 birthday 转换为 VARCHAR 类型，这样两个 SELECT 查询返回的列数和数据类型就一致了。

SELECT id, name, institute

FROM stu_info

WHERE origin = '北京市'

UNION

SELECT id, name, CONVERT(VARCHAR(10), birthday, 120) -- 将birthday转换为字符型

FROM stu_info

WHERE institute = '计科系';

CONVERT(VARCHAR(10), birthday, 120) 的含义如下：

VARCHAR(10) 是目标数据类型，表示将 birthday 转换为字符串，并指定字符串的最大长度为 10。
birthday 是要转换的表达式或列名，假设 birthday 是一个日期或时间类型的列。
120 是样式参数，用于指定日期和时间的格式。在 SQL Server 中，样式 120 表示 ISO 8601 格式的日期时间，具体格式为 yyyy-mm-dd hh:mi:ss。

所以，CONVERT(VARCHAR(10), birthday, 120) 的作用是将 birthday 列从日期或时间类型转换为字符串类型，并且格式为 yyyy-mm-dd 的日期字符串，字符串长度为 10。

由于MySQL中自动将日期时间类型转变为字符串类型输出，因此在MySQL环境下执行以上SQL语句，则不会出现错误。

3.3、使用UNION解决不支持全外连接的问题

UNION很重要的一个作用是通过组合左外连接和右外连接来实现全外连接的功能，因为有些DBMS不支持全外连接。

SELECT * FROM stu_info s LEFT JOIN user_info u ON s.id=u.id

UNION

SELECT * FROM stu_info s RIGHT JOIN user_info u ON s.id=u.id;

3.4、使用UNION得到复杂的统计汇总格式

联合UNION、GROUP BY和聚合函数三者会得到具有很棒的统计汇总样式的查询结果，这也是OR所不能替代的。

SELECT s_id AS 学号, c_id AS 课号, result1 AS 考试成绩 FROM score

UNION

SELECT s_id AS 学号, CAST('总分:' AS CHAR(10)), SUM(result1) FROM score GROUP BY s_id

UNION

SELECT s_id, CAST('平均分:' AS CHAR(10)), AVG(result1) FROM score GROUP BY s_id

ORDER BY 学号, 课号;

在 UNION 操作符的使用中，所有合并的 SELECT 语句必须返回相同数量的列，并且相应列的数据类型必须匹配。在此查询中，通过 CAST('总分:' AS CHAR(10)) 和 CAST('平均分:' AS CHAR(10)) 确保这两列的数据类型与 c_id 列的数据类型一致，避免因数据类型不匹配而引发的错误。

3.5、排序组合查询的结果

虽然组合查询中可以有多个单独的SELECT语句，而且每个独立的SELECT语句又都可以拥有自己的WHERE子句、GROUP BY子句和HAVING子句，但是整个语句中却只能出现一个ORDER BY子句，而且它的位置必须在整个语句的末尾，就是说只能对组合查询最后的结果进行排序，而并不能只对某个单独的SELECT语句的结果进行排序。

示例：从stu_info表中，查询来源地为“北京市”或者所属院系为“计科系”或者年龄大于20岁的学生的信息，并按照出生日期进行升序排序。

SELECT * FROM stu_info WHERE origin='北京市'

UNION

SELECT * FROM stu_info WHERE institute='计科系'

UNION

SELECT * FROM stu_info WHERE TIMESTAMPDIFF(YEAR,birthday,CURDATE())>20

ORDER BY birthday;

当没有ORDER BY子句时，查询结果会根据第一个SELECT子句中字段名列表升序排序。

四、子查询

嵌入另一个SELECT语句中的SELECT语句称为子查询。目前子查询能完成的工作，通过表连接几乎也都可以完成。

4.1、返回单值的子查询

子查询可能返回一个单值，也可能会返回一列值，但它不能返回一个几行几列的表，这是由于子查询的结果要用在主查询语句中，所以必须适合主查询语句。

4.1.1、使用返回单值的子查询

如果子查询返回单值，则可以使用关系运算符，如等于（=）、不等于（<>）等，将其与主查询结合起来。

示例：查询所有学生“心理学”的考试成绩，并以考试成绩降序进行排序。

分析：考试成绩在score表中，而该表中没有课名只有课号，所以必须先从stu_course表中查询“心理学”的课号，然后再从score表中根据查到的课号查询考试成绩。

SELECT s_id AS 学号,result1 AS 考试成绩 FROM score

WHERE c_id=(SELECT c_id FROM stu_course WHERE course='心理学')

ORDER BY result1 DESC;

执行步骤

解析和优化查询:
- DBMS 首先会解析整个 SQL 查询，检查语法是否正确。
- 然后，DBMS 会优化查询，决定如何最有效地执行它。优化器可能会考虑索引、统计信息以及数据分布等因素。
执行子查询:
- DBMS 会首先执行子查询部分，即：

SELECT c_id FROM stu_course WHERE course = '心理学'

- 这个子查询会在 stu_course 表中查找 course 列等于 '心理学' 的所有行，并返回相应的 c_id 值。
- 如果子查询返回多个结果，DBMS 会将这些结果用于主查询中的 WHERE 条件。
执行主查询:
- 在得到子查询的结果后，DBMS 会使用这些结果来执行主查询：

SELECT s_id AS 学号, result1 AS 考试成绩

FROM score

WHERE c_id = 子查询结果

ORDER BY result1 DESC;

具体步骤为：

从 score 表中查找所有 c_id 等于子查询结果的行。
选择符合条件的行的 s_id 和 result1 列。
别名：将 s_id 列命名为学号，将 result1 列命名为考试成绩。

排序结果:

对上述查询结果按照 result1（即考试成绩）列进行降序排序。

返回结果:

将最终排序后的结果集返回给客户端。

4.1.2、子查询与聚合函数的配合使用

子查询和聚合函数配合使用，其实是当前子查询的最大用途。因为聚合函数通常都在SELECT子句字段列表处出现，而WHERE子句中又不能包含聚合函数，所以通常是使用子查询获得聚合函数的返回值，然后将该返回值放到主查询中，最后再执行结合好后的查询语句。

示例1：查询出生日期最早（小）的学生的所有信息

SELECT * FROM stu_info WHERE birthday=(SELECT MIN(birthday) FROM stu_info);

这个子查询会在 stu_info 表中查找 birthday 列的最小值（最早的生日）。

示例2：查询“心理学”考试成绩大于其考试成绩平均分的所有学生的学号、平时成绩和考试成绩。

SELECT s_id AS 学号,result2 AS 平时成绩,result1 AS 考试成绩 FROM score

WHERE c_id=(SELECT id FROM stu_course WHERE course='心理学')

AND

result1>(SELECT AVG(result1) FROM score WHERE c_id=(SELECT id FROM stu_course WHERE course='心理学'))

ORDER BY result1 DESC;

上面的查询语句使用了三个子查询，求心理学考试成绩平均分的子查询为嵌套子查询。

ps：子查询内部还有其他子查询存在时，将这种子查询称为嵌套子查询；例如，上面求“心理学“考试成绩平均分的子查询

SELECT AVG(result1) FROM score WHERE c_id=(SELECT id FROM stu_course WHERE course='心理学')

即为嵌套子查询，该嵌套子查询的层数为为2，SQL标准没有限制嵌套子查询的最大层数，但是嵌套子查询的层数越多越影响运行效率，而且不易阅读维护。

 解析和优化:

解析整个查询，生成查询计划。

 子查询1:

查找 stu_course 表中 course 为 '心理学' 的 id，假设为 101。

 子查询2:

计算 score 表中 c_id 为 101 的记录中 result1 的平均值，假设为 75。

 主查询:

使用子查询结果执行主查询：
1. 从 score 表中选择 c_id 为 101 且 result1 大于 75 的记录。
2. 选择这些记录的 s_id、result2 和 result1 列。
3. 对选定的列重命名：s_id 为学号，result2 为平时成绩，result1 为考试成绩。
4. 按 result1 降序排序。

 返回结果:

返回最终排序后的结果集。

4.2、返回一列值的子查询

子查询除了可以返回单值以外，也可以返回一列值，即返回某个字段的所有值或几个值，可以使用IN和NOT IN两个运算符。

4.2.1、使用IN的子查询

如果子查询返回的不是单值，而是一列值，则可以使用IN运算符将该子查询与主查询语句结合起来。

示例：查询所有课程类型为“必修“的学生的考试成绩，并按课号进行升序排序。

SELECT * FROM score WHERE c_id IN (SELECT id FROM stu_course WHERE type='必修') ORDER BY c_id,s_id;

执行顺序总结

执行子查询:
- SELECT id FROM stu_course WHERE type='必修':
  - 查找 stu_course 表中 type 列等于 '必修' 的所有记录，并提取对应的 id 列值。
主查询执行:
- SELECT * FROM score WHERE c_id IN (...):
  - 使用子查询返回的 id 列值列表，在 score 表中筛选出符合条件的记录。
  - 这些记录的 c_id 值必须存在于子查询返回的 id 值列表中。
排序结果:
- 根据 ORDER BY 子句指定的 c_id 和 s_id 列对查询结果进行排序。
返回结果:
- 将排序后的结果集返回给用户或应用程序。

这条查询语句的目的是从 score 表中选择出与 stu_course 表中类型为 '必修' 的课程关联的成绩记录，并按照课程 id (c_id) 和学生 id (s_id) 进行排序。

4.2.2、使用NOT IN的子查询

在子查询中，除使用IN运算符以外，有时也可能需要使用其反运算符—NOT IN运算符。

查询所有课程类型为非必修课的学生的考试成绩。

SELECT * FROM score WHERE c_id NOT IN (SELECT id FROM stu_course WHERE type='必修') ORDER BY c_id,s_id;

使用NOT IN运算符其实可以很好的找出两个表的区别：

SELECT id AS 学号,name AS 姓名,institute AS 所属院系 FROM stu_info

WHERE id NOT IN (SELECT DISTINCT s_id FROM score) ORDER BY id;

执行顺序总结

执行子查询:
- SELECT DISTINCT s_id FROM score:
  - 在 score 表中查找所有不重复的 s_id 值。
主查询执行:
- SELECT id AS 学号, name AS 姓名, institute AS 所属院系 FROM stu_info WHERE id NOT IN (...):
  - 使用子查询返回的 s_id 值列表，在 stu_info 表中筛选出 id 列值不在该列表中的记录。
排序结果:
- 根据 ORDER BY 子句指定的 id 列对查询结果进行排序。
返回结果:
- 将排序后的结果集返回给用户或应用程序。

这条查询的目的是从 stu_info 表中选择出那些在 score 表中没有相关成绩记录的学生信息，并按照学号 (id) 进行排序。

Xx震zZ

关注

10
点赞
踩
9

收藏

觉得还不错? 一键收藏
0
评论
多表连接查询和子查询

在上面的连接规则表达式中，字段名前加上了数据表的名称，并用英文中的句号（.）将其隔开，这是因为两个表中有相同的字段名，如果不加以修饰说明，DBMS将无法辩认是哪个表的字段；CREATE TABLE bxk_score AS SELECT stu_info.id as 学号,stu_info.name AS 姓名,stu_course.ID AS 课号,stu_course.course AS 课名。有连接规则连接其实就是在无连接规则的基础上，加上WHERE子句指定连接规则的连接方法。
复制链接

扫一扫