小白学习MySQL Day9-10 20240823-0824

小白stepbystep

已于 2024-10-01 17:50:10 修改

阅读量507

点赞数 9

文章标签：学习

于 2024-08-24 21:47:54 首次发布

本文链接：https://blog.csdn.net/stepbystep_0/article/details/141493654

版权

一、笛卡尔积 Cartesian Product

1、概念

笛卡尔积是指两个或多个表中的每一行与另一表中的每一行都进行组合。

是表之间未经明确连接的全面组合。 Combination of multiple tables.

示例：

无条件的笛卡尔积的结果集：大小通常是参与查询的各个表行数的乘积。

例如，如果表A有m行，表B有n行，那么笛卡尔积结果集将包含m*n行。

2、解决歧义

如果两个或多个表中有相同名称的列，直接使用列名会导致歧义。

If the tables have columns with the same name, ambiguity(歧义) will result.

在列名前加上表名来区分。 This can be resolved by referencing columns with the table name:

TableName.ColumnName

例如Student.ID和Grade.ID。

E.g. 正确地查询列：

SELECT Student.ID FROM Student, Grade

WHERE Student.ID = Grade.ID;

涉及到多个具有相同列名的表，需要使用表名或别名来明确指定列的来源。

3、避免不需要的数据

如何避免笛卡尔积中不需要的数据？

示例：（1）

Step1 确定数据来源：哪几个表？

Step2 通过WHERE子句，过滤出满足条件的组合：组合需满足什么条件？

Step3 指定SELECT筛选的列，得到想要的列：需要组合的哪几列？

（2）从三个表中查询：

SELECT * FROM

Student, Grade, Course

WHERE

(Student.ID = Grade.ID)

AND

(Course.Code = Grade.Code);

4、例题

（1）Produce a list of all student names and all their(限制条件） enrolments (module codes).

SELECT sName, mCode

FROM Student, Enrolment

WHERE Student.sID = Enrolment.sID;

（2）Find a list of module titles being taken by the student named “Harrison”.

1）解1：笛卡尔积加条件过滤

SELECT mTitle

FROM Module, Student, Enrolment

WHERE(Module.mCode = Enrolment.mCode)

AND(Student.sID = Enrolment.sID)

AND Student.sName = 'Harrison';

Harrison名字有唯一确定的sID，通过sID确定多个mCode，通过mCode确定mTitle.

数据来源： Module, Student, Enrolment 三个表

所求： module titles存于Module表的mTitle列。

显式限制条件：组合中 the student named “Harrison”即Student.sName = 'Harrison'

隐式限制条件：如何连接这些表？三个表通过两个列sID和mCode连接。

2）解2：可以通过JOIN语句连接表，满足隐式限制条件。此时，数据来源仅为Module表。

SELECT mTitle

FROM Module

JOIN Enrolment ON Module.mCode = Enrolment.mCode

JOIN Student ON Student.sID = Enrolment.sID

WHERE Student.sName = 'Harrison';

（3）Find a list of module codes and titles for all modules currently being taken by first year students.

1）解1：笛卡尔积加条件过滤

数据来源： Module, Student, Enrolment 三个表

所求：存于Module表的mTitle列和mCode列

显式限制条件：组合中Student.sYear = 1

隐式限制条件：组合中信息需有意义，每个人对应自己的个人信息。

如何连接这些表？三个表通过两个列sID和mCode连接。

SELECT Module.mCode, mTitle

FROM Enrolment, Module, Student

WHERE (Module.mCode = Enrolment.mCode)

AND (Student.sID = Enrolment.sID)

AND sYear = 1;

2）解2：可以通过JOIN语句连接表，满足隐式限制条件。此时，数据来源仅为Module表。

SELECT Module.mCode, mTitle

FROM Module

JOIN Enrolment ON Module.mCode = Enrolment.mCode

JOIN Student ON Student.sID = Enrolment.sID

WHERE sYear = 1;

二、语句的执行顺序

SQL语句的执行顺序与编写顺序并不完全一致，主要是为了优化查询性能。

一般来说，逻辑上SQL语句的序执行顺序如下：

FROM子句：首先，DBMS（数据库管理系统）会处理FROM子句，确定要从哪些表中检索数据。这包括表之间的连接（如果有的话），如JOIN操作。在这一步，DBMS会生成一个包含所有需要查询的数据的虚拟表（或称为中间结果集）。
ON子句（如果使用了JOIN）：在JOIN操作中，ON子句用于指定连接条件。DBMS会根据这些条件来合并表中的数据，生成一个新的虚拟表。
JOIN子句（如果使用了JOIN）：JOIN子句指定了表之间的连接类型（如INNER JOIN、LEFT JOIN等）。DBMS会根据JOIN类型和ON子句中的条件来执行连接操作。
WHERE子句：接着，DBMS会应用WHERE子句中的条件来过滤FROM子句（或JOIN操作后）生成的虚拟表中的行。只有满足WHERE子句条件的行才会被保留在虚拟表中，用于后续的处理。
GROUP BY子句（如果存在）：然后，如果查询中包含了GROUP BY子句，DBMS会将过滤后的虚拟表中的行分组。每个分组将包含具有相同分组列值的行。这一步是为了准备对分组后的数据进行聚合计算。
聚合函数（如SUM、AVG、MAX、MIN、COUNT等）：在GROUP BY子句之后（如果没有GROUP BY子句，则在所有行上），DBMS会对分组后的数据进行聚合计算。这些聚合函数会根据需要在分组内或整个结果集上计算统计值。
HAVING子句（如果存在）：HAVING子句用于对分组后的结果进行过滤。与WHERE子句不同，HAVING子句可以引用聚合函数的结果。只有满足HAVING子句条件的分组才会被保留在虚拟表中。
SELECT子句：在这一步，DBMS会处理SELECT子句，选择要在最终结果集中显示的列。如果SELECT子句中包含了列的别名，那么这些别名将在这一步被赋予。
DISTINCT子句（如果存在）：如果查询中使用了DISTINCT关键字，DBMS会去除最终结果集中的重复行，只保留唯一的行。
ORDER BY子句（如果存在）：最后，如果查询中包含了ORDER BY子句，DBMS会根据指定的列对最终结果集进行排序。排序可以在一个或多个列上进行，可以是升序或降序。
LIMIT子句（如果存在）：LIMIT子句用于限制查询结果的数量，通常与OFFSET子句一起使用以实现分页功能。

需要注意的是，虽然上述顺序是逻辑上的执行顺序，但DBMS在实际执行查询时可能会根据查询优化器的决策来调整这个顺序，以便更高效地执行查询。

三、别名 Alias

1、概念

为表或列提供一个临时的、简短的或更具描述性的名称。

这个名称只在当前查询的上下文中有效，不会改变表或列在数据库中的实际名称。

2、功能

简化查询；解决列名冲突（当连接JOIN的多个表中包含具有相同名称的列）；

支持聚合函数和复杂表达式；在子查询中使用；在ORDER BY和GROUP BY子句中使用。

注：不能在WHERE子句中直接使用列别名。

从逻辑执行顺序的角度来看，WHERE子句是在SELECT子句之前执行的。

因此在WHERE子句中引用在SELECT子句中定义的别名是不被允许的。

在WHERE子句中引用列时，必须使用列的实际名称，而不是在SELECT子句中定义的别名。

3、分类

（1）列别名 Column alias

SELECT column [AS] new-col-name

（2）表别名 Table alias

SELECT * FROM table [AS] new-table-name

注：AS关键字可省略。The AS keyword is optional.

4、示例

1）

2）假设我们有两个表：employees表和departments表。

employees表包含员工的ID、姓名和部门ID。

departments表包含部门ID和部门名称。

想要查询每个员工的姓名以及他们所属的部门名称。

（1）思路

第一步：写FROM子句，确定查询的起始表

第二步：写JOIN语句，扩大实际查询范围，通过ON子句连接两个表

第三步：AS关键字，指定表别名，在后续查询中简化引用。

第四步：写SELECT语句，获取指定列

第五步：AS关键字，指定列别名，显示为别名。

（2）代码

SELECT e.name AS EmployeeName,

d.department_name AS Department

FROM employees AS e

JOIN departments AS d ON e.department_id = d.id;

（3）解析

e.name AS EmployeeName：

选择employees表中的name列，并在结果集中将其显示为EmployeeName。

d.department_name AS Department：

选择departments表中的department_name列，并在结果集中将其显示为Department。

JOIN departments AS d ON e.department_id = d.id：

JOIN关键字：根据两个或多个表之间的共同字段，来合并这些表的行。

JOIN用于将employees表和departments表连接起来。

departments AS d 指定别名：将departments表指定了一个别名d。

在查询的剩余部分中，可以使用d来引用departments表，从而使查询更加简洁。

ON子句：ON e.department_id = d.id是JOIN操作的关键部分，它指定了连接两个表的条件。

这里，它表明我们想要：将e表中的行与 d表中的行连接起来，

条件是：e表中的department_id列的值与d表中的id列的值相匹配。

（4）FROM子句后接对象

SELECT也选出了departments表中的department_name列。

但为什么FROM之后只接employees表？

FROM子句：仅仅指定查询的起始表。

FROM employees AS e指定了查询将从employees表开始，并通过别名e来引用它。

JOIN子句：用于将其他表（如departments表），根据指定的条件，与起始表连接起来。

通过JOIN departments AS d ON e.department_id = d.id，

JOIN子句实际上将departments表也纳入了查询的范围。

SELECT子句：指定最终查询结果中应包含的列，即希望从查询结果中获取的列。

尽管FROM子句只明确提到了employees表，但查询的结果将是一个临时表，

它包含了employees表和departments表中满足连接条件的所有行的组合。

然后，SELECT子句从这个临时表中选择指定的列（e.name和d.department_name），

并将它们分别重命名为EmployeeName和Department。

执行一个JOIN查询时，数据库系统会：

1. 识别要连接的表：

这通过FROM子句以及后续的JOIN子句来完成，其中每个表都可以被赋予一个别名以简化引用。

2. 确定连接条件：

这通过ON子句来完成，它指定了用于连接两个表的条件。

连接条件通常涉及两个表中的列，这些列包含了可用于匹配的行的数据。

3. 查找匹配的行：

数据库系统会根据连接条件查找每个表中匹配的行。

对于内连接（INNER JOIN，如果未明确指定则默认为此类型），只有当两个表中都存在满足连接条件的行时，这些行才会被包含在结果集中。

4. 合并结果：

一旦找到了匹配的行，数据库系统就会将它们组合起来形成一个新的结果集。

在这个结果集中，每行都包含了来自每个原始表的相关列的值。

这些列可以按照在SELECT子句中指定的顺序和别名来显示。

5. 返回结果：

最后，数据库系统将这个合并后的结果集返回，作为查询的输出。

需要注意的是，虽然“合并”这些表的行，但实际上并没有修改原始表中的数据。

JOIN操作只是创建了一个临时的结果集，用于展示两个或多个表中相关行的组合。

这个结果集可以在查询中被进一步处理，比如用于聚合函数、过滤（使用WHERE子句）或排序（使用ORDER BY子句），但它本身不会持久化存储在数据库中。

此外，还有其他类型的JOIN操作，如左连接（LEFT JOIN）、右连接（RIGHT JOIN）和全外连接（FULL OUTER JOIN），它们提供了更灵活的方式来合并表的行，并允许您指定在连接条件不满足时如何显示数据。

四、自连接 Self-Joins

1、概念

将同一个表视为两个不同的表，可用于比较或组合这些虚拟表中的行。

允许在同一个查询中两次引用同一个表，而不会导致列名冲突。

2、应用场景

（1）层级和树形结构

在处理具有层级结构（如员工-经理关系、分类目录等）的数据时，自连接特别有用。

（2）比较同一表中的行

当需要比较表中相同列的不同行时，可以使用自连接。例如，查找与特定员工在同一部门的所有员工。

3、实现方式

使用别名 + 连接条件

Aliases and ‘Self-Joins’:

Aliases can be used to copy a table, so that it can be combined with itself

通过为一个表指定不同的别名（如A和B），可以将同一个表视为两个不同的表进行连接。

定义连接条件时，需要确保它涉及不同别名下的列，以区分表的不同部分。

4、示例

假设有一个名为employee的表，包含id列、name列和dept列。

要查询与名为'Andy'的员工在同一部门的所有员工，可以使用自连接：

（1）思路

确定查询起始表
指定表别名
确定连接条件

需要基于dept列，将employee表与其自身连接起来，以找到与'Andy'在同一部门的所有员工。

确定过滤条件

需要一个条件来指定我们正在查找的是名为'Andy'的员工。通过在WHERE子句中使用表别名来实现的。

（2) 代码

SELECT A.name

FROM employee A, employee B

WHERE (A.dept = B.dept)

AND (B.name = ‘Andy’);

（3）解析

别名 A 和 B 是通过在表名employee后面直接指定来命名的，没有显式使用。

查询连接了这两个虚拟表，通过dept列进行比较，并筛选出名为'Andy'的员工的部门中的所有员工。

（4）另解

使用显式的JOIN语法（如INNER JOIN），它更清晰、更易于理解。

SELECT A.name

FROM employee A

INNER JOIN employee B ON A.Dept = B.Dept

WHERE B.Name = 'Andy';

小白stepbystep

关注

9
点赞
踩
18

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫