SQL数据操作语句全解析

### SQL数据操作语句全解析 #### 1. 简单查询示例 在处理大学数据库时，若要生成一份来自Chancellor Hall的学生列表，显示学生全名和学生ID，并按姓氏内的名字排序，可使用如下SQL语句： ```sql // Show students from Chancellor Hall, sorted by name SELECT StudLName || ' ' || StudFName AS FullName, Stud# FROM Student WHERE StudHall#= 'Chan' ORDER BY StudLName, StudFName; ``` 这里使用了连接运算符 `||` 来拼接姓氏和名字，并引入了别名 `FullName`。需注意，这里的“别名”是列别名，与其他场景下的表别名有所不同。 #### 2. 多表查询 在实际应用中，常常需要从多个表中提取数据以满足特定查询需求。此时可能涉及自然连接、theta连接、外连接或笛卡尔积等操作。Oracle支持两种处理多表查询的方法：传统方法和美国国家标准协会（ANSI）方法。 ##### 2.1 传统方法 传统方法是在 `Where` 子句中指定连接条件。以下是几个示例： **示例1：计算发货报告信息** ```sql // Prepare information for Shipment Report SELECT SH.Supp#, S.SuppName, SH.Item#, I.ItemName, SH.Qty, SH.Qty * I.ItemPrice AS Value FROM Schedule SH, Supplier S, InventoryItem I WHERE SH.Supp# = S.Supp# AND SH.Item# = I.Item#; ``` 此示例展示了自然连接，包含标量表达式 `SH.Qty * I.ItemPrice` 和列别名 `Value`。引入显式元组变量 `SH`、`S` 和 `I` 是个好习惯，特别是在查询信息来自多个表或需要进行比较时。 **示例2：查询课程和项目名称组合** ```sql // Show program-name and course-name combinations SELECT P.PgmName, C.CrsName FROM Pgm_Struct PS, AcademicProgram P, Course C WHERE PS.PSPgm# = P.Pgm# AND PS.PSCrs# = C.Crs#; ``` **示例3：查询非部门主管的系主任** ```sql // Show the names of department heads who are not division heads SELECT E.Emp#, E.EmpName FROM Employee E, Department D, Division DV WHERE D.DHead# = E.Emp# AND D.D_Div# = DV.Div# AND DV.DvHead# = E.Emp# AND D.DHead# <> DV.DvHead#; ``` 这是一个theta连接的示例，利用了部门表和分部表中存储的外键与员工表关联的事实。 **示例4：查询生日相同的学生** ```sql SELECT S1.Stud#, S1.StudDoB FROM Student S1, Student S2 WHERE S1.Stud# <> S2.Stud# AND S1.StudDoB = S2.StudDoB; ``` 这是关系自身的笛卡尔积示例。 **示例5：内连接与外连接对比** ```sql // The inner join would produce a spurious result-set SELECT StudLName, HallName FROM Student S, Hall H WHERE S. StudHall# = H.Hall#; // The outer join would produce the desired result-set SELECT StudLName, HallName FROM Student S, Hall H WHERE S. StudHall# = H.Hall#(+); ``` 内连接会排除未分配宿舍的学生，而外连接通过在可能没有对应行的表的属性上添加括号加 `+` 号来包含这些学生。具体规则如下： 1. 内连接结果集将排除所有未分配宿舍的学生。为避免这种情况，在编写连接条件时，在可能没有对应行的表的属性（如 `H.Hall#`）上添加括号加 `+` 号来指定外连接。 2. `+` 号表示隐含关系的行对于感兴趣的属性可能有null值（或不匹配的值），这些行应被包含。如果 `+` 号在右边，则为左外连接；如果在左边，则为右外连接。 3. 使用这种方法，`+` 号可以在连接条件的任一侧，但不能两侧都有。这意味着可以有左外连接、右外连接，但不能有全外连接。要获得全外连接，需要将左外连接和右外连接取并集。 4. 左外连接意味着包含左表的所有行；右外连接意味着包含右表的所有行。 以下是内连接和外连接的区别流程图： ```mermaid graph LR A[开始] --> B{选择连接类型} B -->|内连接| C[排除无匹配行] B -->|外连接| D{+号位置} D -->|右边| E[左外连接，包含左表所有行] D -->|左边| F[右外连接，包含右表所有行] C --> G[结束] E --> G F --> G ``` ##### 2.2 ANSI方法 Oracle支持新引入的ANSI语法来处理多表查询。使用这种新语法，`From` 子句被修改，要求显式指定连接。其语法有五种格式，具体如下： 1. **自然连接**：每个表中的连接列必须相同，且不允许限定列名。 2. **指定连接列**：当每个参与表中至少有一个列名相同时使用，需要指定用于连接的列。如果数据库设计合理，各表属性名唯一，这种格式使用场景较少。 3. **最常用格式**：最灵活，在 `ON` 关键字后指定连接条件。 4. **笛卡尔积**：不需要连接条件。 5. **外连接**：有三种类型，左外连接返回基于连接条件的行以及左表中未匹配的行；右外连接返回基于连接条件的行以及右表中未匹配的行；全外连接返回基于连接条件的行以及左右表中未匹配的行。当使用 `LEFT`、`RIGHT` 或 `FULL` 关键字时，`OUTER` 关键字是隐含的，可选；反之，当省略这些关键字时，`INNER` 关键字是隐含的。 以下是使用ANSI语法的示例： ```sql // Show program-name and course-name combinations SELECT P.PgmName, C.CrsName FROM PgmStruct PS JOIN AcademicProgram P ON PS.PSPgm# = P.Pgm# JOIN Course C ON PS.PSCrs# = C.Crs#; // This requires a left outer join SELECT S.StudLName, H.HallName FROM Student S LEFT JOIN Hall H ON S.StudHall# = H.Hall#; ``` ANSI语法相对于传统方法的优势在于可以将连接条件与其他条件分开，且更容易实现全外连接。 #### 3. 函数查询 SQL允许使用多种函数，为用户（通常是程序员）在指定查询时提供灵活性。主要包括以下几类函数： - 行函数 - 日期函数 - 数据转换函数 - 程序员自定义函数 - 聚合函数 - 分析函数 ##### 3.1 行函数 行函数作用于查询结果的行（元组），通常影响给定行的特定列的值。以下是一些常用行函数及其解释的表格： | 函数 | 解释 | | --- | --- | | `NVL(<ScalarExpr1>, <ScalarExpr2>)` | 用表达式2的值替换表达式1中的null值。 | | `NVL2(<ScalarExpr1>, <ScalarExpr2>, <ScalarExpr3>)` | 如果表达式1非null，则返回表达式2；否则返回表达式3。 | | `COALESCE(<ScalarExpr> {,<ScalarExpr>})` | 返回列表中第一个非null表达式；如果列表中每个表达式都是null，则返回null。 | | `CONCAT(<ScalarExpr1>, <ScalarExpr2>)` | 连接指定的两个表达式。表达式必须是字母数字