深入探索SQL：中级与高级特性解析

# 深入探索 SQL：中级与高级特性解析 ## 中级 SQL 特性 ### 权限撤销与角色授予 在 SQL 中，权限撤销操作有不同的处理方式。当存在级联撤销情况时，系统可能返回错误并停止撤销操作。关键字 `cascade` 可用于表示撤销应级联进行，不过它是默认行为，可省略。例如： ```sql revoke grant option for select on department from Amit; ``` 此语句仅撤销授予选项，而非实际的 `select` 权限。但部分数据库实现不支持该语法，可先撤销权限，再重新授予但不包含授予选项。 为避免级联撤销带来的问题，SQL 允许通过角色而非用户授予权限。可通过 `set role role name` 设置会话的当前角色，前提是该角色已授予用户。在授予权限时，添加 `granted by current role` 子句，可将授予者设为当前角色。例如，若以 `dean` 角色授予 `Amit` `instructor` 角色，撤销 `Satoshi` 的 `dean` 角色不会影响 `Amit` 的 `instructor` 角色。 ### SQL 核心概念总结 SQL 支持多种连接类型，如内连接和外连接，以及多种连接条件。视图关系可定义为包含查询结果的关系，有助于隐藏不必要信息并整合多表信息。事务是一系列查询和更新操作，可提交或回滚。完整性约束确保数据库数据一致性，包括引用完整性约束、域约束、断言等。SQL 数据定义语言支持定义内置和用户定义的域类型。授权机制可区分用户对数据库数据的访问类型，角色有助于根据用户在组织中的角色分配权限。 | 核心概念 | 描述 | | ---- | ---- | | 连接类型 | 内连接、外连接（左外连接、右外连接、全外连接）、自然连接、使用连接、条件连接 | | 视图 | 隐藏信息、整合数据 | | 事务 | 提交、回滚、原子性 | | 完整性约束 | 引用完整性、域约束、断言 | | 授权 | 权限授予、撤销、角色 | ### 实践练习 #### 查询编写 - **显示所有教师信息**：显示教师的 ID、姓名和授课节数，未授课教师的节数显示为 0。可使用外连接或标量子查询实现。 - **显示 2010 年春季课程节信息**：显示课程节信息及授课教师姓名，无教师的课程节教师姓名显示为 “—”。 - **显示各系教师总数**：正确处理无教师的系。 #### 外连接重写 可在不使用 SQL 外连接操作的情况下计算外连接表达式，例如重写 `select * from student natural left outer join takes` 和 `select * from student natural full outer join takes`。 #### 关系表达式分析 对于关系 `r(A, B)`、`s(B, C)` 和 `t(B, D)`，分析 `r natural left outer join (s natural left outer join t)` 和 `(r natural left outer join s) natural left outer join t` 的结果。 #### SQL 查询测试 为确保 SQL 查询正确，需在多个测试数据库上执行查询，并人工检查结果是否符合英文规范。创建测试数据库时，需创建无匹配元组的引用关系元组和含空值的外键属性元组。 #### 视图定义 定义视图 `student grades (ID, GPA)` 计算学生的平均绩点，需正确处理 `takes` 关系中成绩属性的空值。 #### 数据库定义 完成大学数据库的 SQL DDL 定义，包括 `student`、`takes`、`advisor` 和 `prereq` 关系，并确定引用完整性约束。 #### 约束查询与断言 编写 SQL 查询返回违反 “教师不能在同一学期同一时间段在两个不同教室授课” 约束的 `(instructor, section)` 组合，并编写 SQL 断言强制执行该约束。 #### 外键依赖分析 分析 `manager` 表中删除元组时的具体操作，该表的外键依赖引用自身，且设置了 `on delete cascade`。 #### 操作符使用 使用 `coalesce` 操作符实现 `a` 和 `b` 关系的自然全外连接，确保结果关系不包含重复属性。 #### 标记空值应用 使用标记空值允许通过视图 `instructor info` 插入元组 `(99999, “Johnson”, “Music”)`。 #### 其他查询 编写查询查找无经理的员工，分析查询 `select * from student natural full outer join takes natural full outer join course` 包含 `title` 属性空值元组的情况，定义视图 `tot credits (year, num credits)` 计算学生每年的总学分，使用 `case` 操作符表达 `coalesce` 操作，提出表达特定引用完整性约束的语法并讨论系统执行该约束的操作，解释经理以角色而非用户授予权限的原因，分析授权图中是否会出现循环，讨论使用操作系统授权方案的优缺点。 ### mermaid 流程图：查询执行流程 ```mermaid graph TD; A[开始] --> B[编写 SQL 查询]; B --> C[选择执行环境]; C --> D{是否为动态 SQL}; D -- 是 --> E[动态构建查询字符串]; D -- 否 --> F[使用静态查询]; E --> G[提交查询到数据库]; F --> G; G --> H{查询是否成功}; H -- 是 --> I[处理查询结果]; H -- 否 --> J[处理错误信息]; I --> K[结束]; J --> K; ``` ## 高级 SQL 特性 ### 从编程语言访问 SQL 数据库程序员需使用通用编程语言访问 SQL，原因有二：一是 SQL 无法表达所有查询，二是 SQL 无法执行非声明性操作。有两种访问方式： - **动态 SQL**：通用程序通过函数或方法与数据库服务器通信，可在运行时构造和提交 SQL 查询。介绍了 JDBC 和 ODBC 两种标准接口。 - **嵌入式 SQL**：SQL 语句在编译时由预处理器识别，替换为宿主语言声明和过程调用，部分 SQL 相关错误可在编译时捕获。 ### JDBC #### 连接数据库 Java 程序使用 JDBC 访问数据库的第一步是打开连接，通过 `DriverManager.getConnection` 方法，需提供数据库 URL、用户标识符和密码。同时，需动态加载 JDBC 驱动，使用 `Class.forName` 方法。例如： ```java Class.forName ("oracle.jdbc.driver.OracleDriver"); Connection conn = DriverManager.getConnection( "jdbc:oracle:thin:@db.yale.edu:1521:univdb", userid, passwd); ``` #### 执行 SQL 语句 打开连接后，可通过 `Statement` 对