sql part2.docx

第七章、复杂查询(上)：多表连接技术 7.1 简单查询的解析方法： 全表扫描：指针从第一条记录开始，依次逐行处理，直到最后一条记录结束； 横向选择+纵向投影=结果集 7.2 多表连接 7.2.1多表连接的优缺点 优点： 1）减少冗余的数据，意味着优化了存储空间，降低了IO负担。 2）根据查询需要决定是否需要表连接。 3）灵活的增加字段，各表中字段相对独立（非主外键约束），增减灵活。 缺点： 1）多表连接语句可能冗长复杂，易读性差。 2）可能需要更多的CPU资源，一些复杂的连接算法消耗CPU和Memory。 3）只能在一个数据库中完成多表连接查询。 7.2.2多表连接中表的对应关系 1）一对一关系 将表一份为二，最简单的对应关系 2）一对多关系 两表通过定义主外键约束，符合第三范式标准的对应关系。 ### 第七章、复杂查询(上)：多表连接技术 #### 7.1 简单查询的解析方法 在进行数据检索时，我们首先接触到的是简单的查询操作。简单查询的基本过程通常涉及两种主要的操作：全表扫描以及横向选择与纵向投影。 - **全表扫描**：这是一种基础的检索方式，指的是从数据表的第一条记录开始，逐行地读取数据直至最后一行。这种方式简单直接，在数据量较小的情况下效率较高，但在数据量庞大的情况下可能会导致性能问题。 - **横向选择+纵向投影=结果集**：横向选择指的是根据特定条件筛选出满足需求的行；而纵向投影则是指选择这些行中的某些特定列。最终，这两个操作结合起来形成最终的查询结果集。 #### 7.2 多表连接 多表连接是SQL中一种非常重要的技术，它允许用户从多个表中检索数据，并根据一定的关联规则将这些数据合并在一起。 ##### 7.2.1 多表连接的优缺点 **优点**： 1. **减少冗余数据**：通过连接多个表来避免数据重复存储，这不仅优化了存储空间，也减轻了I/O负担。 2. **灵活性**：多表连接可以根据查询的需求来决定是否进行连接，这使得整个查询更加灵活。 3. **易于维护**：各个表中的字段相对独立，这意味着可以在不干扰其他表的情况下增加或删除字段，提高了系统的可维护性。 **缺点**： 1. **复杂性**：多表连接的SQL语句可能比较冗长且难以阅读，特别是在涉及到多个表时。 2. **资源消耗**：一些复杂的连接算法可能需要更多的CPU资源和内存，尤其是在处理大量数据时。 3. **限制性**：多表连接只能在同一数据库中执行，这对于需要跨数据库连接的情况来说是一个局限。 ##### 7.2.2 多表连接中表的对应关系 - **一对一关系**：这是最简单的对应关系，通常通过在两个表之间共享一个公共的唯一标识符来实现。例如，员工表和部门详情表可以通过员工ID进行一对一的关联。 - **一对多关系**：在这种关系中，一个表的一条记录可以对应另一个表中的多条记录。这种关系通常通过定义主键和外键来实现，确保数据的一致性和完整性。 - **多对多关系**：为了表示多对多的关系，通常会创建一个中间表来作为桥梁，这个中间表至少包含两个表的主键。通过这种方式，可以将多对多关系转换为两个一对多关系。 ##### 7.2.3 多表连接的种类和语法 **交叉连接（笛卡尔积）**：当两个表的所有行都进行连接时，会产生所有可能的行组合，即笛卡尔积。 **非等值连接**：这种类型的连接基于一个非等式的条件，如范围查询等。 **等值连接(内连)**：等值连接是最常见的连接类型，基于两个表之间的相等条件进行连接。 **外连接（内连的扩展，左外，右外，全连接）**：外连接允许返回不匹配的行，分为左外连接、右外连接和全连接三种。 **自连接**：当一个表与自身进行连接时，称为自连接，通常用于表示层次结构或递归关系。 **自然连接**：自然连接是一种特殊的内连接，它自动使用相同的列作为连接条件。 **复合连接**：复合连接是指通过并、交、差等操作将多个结果集进行合并。 ##### 7.2.1 交叉连接（笛卡尔积） 交叉连接发生在没有有效的连接条件或条件被省略的情况下，结果是两个表的所有行的笛卡尔积。 **SQL99写法**： ```sql SELECT * FROM emp CROSS JOIN dept d; ``` **Oracle写法**： ```sql SELECT * FROM emp, dept d; ``` ##### 7.2.2 非等值连接 非等值连接通常用于表示范围查询等场景。 **SQL99写法**： ```sql SELECT empno, ename, sal, grade, losal, hisal FROM emp JOIN salgrade ON sal BETWEEN losal AND hisal; ``` **Oracle写法**： ```sql SELECT empno, ename, sal, grade, losal, hisal FROM emp, salgrade WHERE sal BETWEEN losal AND hisal; ``` ##### 7.2.3 等值连接，典型的内连接 等值连接基于两个表之间的相等条件进行连接。 **SQL99写法**： ```sql SELECT e.ename, d.loc FROM emp e INNER JOIN dept d ON e.deptno = d.deptno; ``` **Oracle写法**： ```sql SELECT e.ename, d.loc FROM emp e, dept d WHERE e.deptno = d.deptno; ``` ##### 7.2.4 等值连接的using字句 使用`USING`关键字可以简化等值连接的书写。 **示例**： ```sql SELECT deptno, e.ename, d.loc FROM emp e INNER JOIN dept d USING (deptno); ``` 注意事项： 1. 如果`SELECT`子句中包含了`USING`关键字所指明的列，则不应指明该列属于哪个表。 2. `USING`中可以指定多个列名。 3. `ON`和`USING`关键字是互斥的，不能同时使用。 ##### 7.2.5 外连接（包括左外连接，右外连接，全外连接） **左外连接**：左外连接返回左表中的所有行，并尝试从右表中找到匹配的行。如果没有找到匹配的行，则在结果集中填充空值。 **SQL99语法**： ```sql SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno; ``` **Oracle语法**： ```sql SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno(+); ``` **理解左外连接的关键点**： 1. **确定左表和右表**：在SQL99写法中，通过`FROM`子句中表的顺序来确定左表和右表，左表是第一个表；而在Oracle写法中，通过`WHERE`子句中的“=”号位置来确定，左表是在“=”号左边的表。 2. **左表为主**：左外连接以左表为主，左表的每一行都会尝试与右表匹配，如果找不到匹配的行，则右表的部分用空值填充。 以上内容详细介绍了SQL中的多表连接技术，包括其基本概念、连接种类及具体的语法实现。掌握了这些知识，可以帮助数据库开发者更高效地管理和检索数据。