当前位置： 首页 > article >正文

二、SQL语言，《数据库系统概念》，原书第7版

article 2025/1/4 7:24:06

文章目录

    • 一、概览SQL语言
      • 1.1 SQL 语言概述
        • 1.1.1 SQL语言的提出和发展
        • 1.1.2 SQL 语言的功能概述
      • 1.2 利用SQL语言建立数据库
        • 1.2.1 示例
        • 1.2.2 SQL-DDL
          • 1.2.2.1 CREATE DATABASE
          • 1.2.2.2 CREATE TABLE
        • 1.2.3 SQL-DML
          • 1.2.3.1 INSERT INTO
      • 1.3 用SQL 语言进行简单查询
        • 1.3.1 单表查询 SELECT FROM WHERE
        • 1.3.2 检索条件的书写
        • 1.3.3 检索结果去重-SELECT DISTINCT FROM WHERE
        • 1.3.4 检索结果排序-SELECT FROM WHERE ORDER BY
        • 1.3.5 模糊查询-SELECT FROM WHERE * LIKE *
      • 1.4 利用 SQL 语言进行多表联合查询
        • 1.4.1 θ-连接之等值连接
        • 1.4.2 重名处理
      • 1.5 SQL-更新操作
        • 1.5.1 INSERT
        • 1.5.2 DELETE
        • 1.5.3 UPDATE
      • 1.6 SQL- 撤销与修改
      • 1.7 SQL- 数据库指定与关闭命令
    • 二、复杂查询与视图
      • 2.1 子查询
        • 2.1.1 (NOT) IN-子查询
          • 2.1.1.1 非相关子查询
          • 2.1.1.2 相关子查询
        • 2.1.2 θ some / θ all 子查询
        • 2.1.3 EXISTS 和 NOT EXISTS 谓词子查询
      • 2.2 利用 SQL 语言进行结果运算与聚集运算
        • 2.2.1 结果计算
        • 2.2.2 聚集函数
        • 2.2.3 分组查询与分组过滤
          • 2.2.3.1 分组查询
          • 2.2.3.2 分组过滤
          • 2.2.3.3 HAVING 子句 和 WHERE 子句 表达条件的区别
      • 2.3 利用SQL 语言实现关系代数操作
        • 2.3.1 并-交-差的处理
        • 2.3.2 空值处理
        • 2.3.3 内连接、外连接
      • 2.4 回顾 SQL-SELECT
        • 2.4.1 SQL-SELECT 的完整语法
        • 2.4.2 SQL-SELECT 的完整语法
        • 2.4.3 SQL-SELECT 的扩展
      • 2.5 视图及其应用
        • 2.5.1 视图的概念与结构
        • 2.5.2 视图的定义
        • 2.5.3 视图的使用
        • 2.5.4 视图的更新
        • 2.5.5 视图的撤销
    • 三、数据库的完整性
      • 3.1 概念和分类
        • 3.1.1 概念
        • 3.1.2 分类
      • 3.2 SQL语言实现数据库的静态完整性
        • 3.2.1 SQL语言支持的约束类别
        • 3.2.2 Create Table
          • 3.2.2.1 Col_constr
          • 3.2.2.2 table_constr
          • 3.2.2.3 断言ASSERTION
      • 3.3 SQL 语言实现数据库的动态完整性
        • 3.3.1 触发器 Trigger
    • 四、数据库的安全性
      • 4.1 概念
      • 4.2 数据库安全性的分类
      • 4.3 数据库管理员的责任和义务
      • 4.4 数据库的自主安全性机制
        • 4.4.1 自主安全性机制
        • 4.4.2 数据库自主安全性访问规则
        • 4.4.3 自主安全性的实现方式
      • 4.5 利用 SQL 语言实现数据库自主安全性
        • 4.5.1 SQL 语言的用户与权利
        • 4.5.2 SQL-DCL 的命令及应用
        • 4.5.3 SQL-DCL 控制安全性示例
      • 4.6 安全性控制的其他方面
        • 4.6.1 自主安全性的授权过程及其问题
        • 4.6.2 强制安全性
    • 五、嵌入式SQL
      • 5.1 嵌入式SQL语言概述
        • 5.1.1 交互式SQL语言的局限
        • 5.1.2 嵌入式SQL语言
        • 5.1.3 嵌入式SQL语言和交互式SQL语言的对比
        • 5.1.4 高级语言中使用嵌入式SQL语言需要解决的问题
      • 5.2 变量声明与数据库连接
        • 5.2.1 变量的声明与使用
        • 5.2.2 程序与数据库的连接与断开
        • 5.2.3 SQL执行的提交与撤销
        • 5.2.4 事务的概念与特性
        • 5.2.5 示例
      • 5.3 数据集与游标
        • 5.3.1 如何读取单行数据和多行数据
        • 5.3.2 游标的使用和概览
        • 5.3.3 示例
        • 5.3.4 游标的使用方法
      • 5.4 可滚动游标及数据库的增删改
        • 5.4.1 可滚动游标的概念
        • 5.4.2 可滚动游标的定义和使用
        • 5.4.3 数据的删除与更新
        • 5.4.4 示例
      • 5.5 状态捕获及错误处理机制
        • 5.5.1 基本机制
        • 5.5.2 状态信息
        • 5.5.3 程序处理
    • 六、动态SQL
      • 6.1 动态SQL的概念和作用
      • 6.2 SQL语句的动态构造
        • 6.2.1 示例1
        • 6.2.2 示例2
      • 6.3 动态SQL语句的执行方式
        • 6.3.1 概述
        • 6.3.2 示例
      • 6.4 数据字典与SQLDA
        • 6.4.1 数据字典
        • 6.4.2 数据字典的内容构成
        • 6.4.3 数据字典的表结构或视图
        • 6.4.4 X/Open 标准的系统目录
        • 6.4.5 Oracle 的数据字典
        • 6.4.6 更为复杂的动态SQL
        • 6.4.7 SQLDA
      • 6.5 ODBC简介
        • 6.5.1 什么是 ODBC？
        • 6.5.2 应用程序如何通过ODBC连接一个数据库服务器
        • 6.5.3 应用程序如何通过ODBC与数据库服务器进行通讯
        • 6.5.4 ODBC 的其他功能
      • 6.6 JDBC
        • 6.6.1 JDBC是什么
        • 6.6.2 JDBC的功能
        • 6.6.3 应用程序使用JDBC API访问数据库的过程
      • 6. 7 嵌入式语言-ODBC-JDBC 比较

一、概览SQL语言

1.1 SQL 语言概述

1.1.1 SQL语言的提出和发展
  1. 1974年，由Boyce和Chamber提出
  2. 1975-1979年，由IBM的SanJose研究室在System R上首次实现，称为Sequel(Structured English QUEry Language)→SQL(Structured Query Language)。
  3. 1986年ANSI/ISO推出SQL标准：SQL-86
  4. 1989年ANSI/ISO推出SOL标准：SOL-89
  5. 1992年进一步推出了SQL标准：SQL-92，也称为SQL2
    1. 是SQL-89的超集
    2. 增加了新特性，如新数据类型，更丰富数据操作，完整性支持等
    3. 原SQL-89被称为entry-SQL,扩展的被称为Intermediate级和Full级
  6. 1999年进一步推出了SQL标准：SOL-99，也称为SQL3
    1. 对面向对象的一些特征予以支持，支持抽象数据类型
    2. 支持行对象和列对象等
    3. 对递归、触发等复杂操作也予以规范化定义
    4. 废弃了SQL2的分级，但定义了core-SQL及扩展的SQL
  7. SQL 2003; SQL 2006; SQL 2008。
  8. SQL还有一个标准是SQ_XOpen标准，主要强调各厂商产品的可移植性，只包含被各厂商广泛认可的操作
  • “标准”：主要用于衡量一个软件商的产品是否符合共同的约定。
  • “标准”使得用户可以学习“标准”规定的语言，而无需关注具体的软件产品。但也应注意不同软件商的数据库产品满足的标准可能是不一样的，具体应用还是略有差异。
1.1.2 SQL 语言的功能概述
  • SQL 是 集DDL，DML，DCL 于一体的数据库语言。
  • SQL语言主要由以下9个单词引导的操作语句来构成，但每一种语句都能表达复杂的操作请求
    • DDL 语句引导词：CREATE(建立)，ALTER(修改)，DROP(撤销)
      • 模式的定义和删除，包括定义Database, Table, View, Index, 完整性约束条件等, 也包括定义对象(RowType行对象, Type列对象)
    • DML 语句引导词：INSERT，DELETE，UPDATE，SELECT
      • 各种方式的更新与检索操作，如直接输入记录，从其他Table(由SubQuery建立)输入
      • 各种复杂条件的检索，如连接查找，模糊查找，分组查找，嵌套查找等
      • 各种聚集操作，求平均、求和、…等，分组聚集，分组过滤等
    • DCL 语句引导词：GRANT，REVOKE
      • 安全性控制：授权和撤销授权

SQL 还可以按照使用方式分类：

交互式SQL ->. 嵌入式SQL -> 动态 SQL 等

  • 交互式SQL，主要通过DMS 提供的服务来操纵数据库
  • 嵌入式SQL，支持程序员在高级语言中使用SQL

1.2 利用SQL语言建立数据库

1.2.1 示例

我们以学生选课数据库SCT为例，我们建立五张表：

学生：学号S#, 姓名Sname, 性别Ssex, 年龄Sage, 所属系别D#，班级Sclass

Student(S# char(8), Sname char(10), Ssex char(2), Sage integer, D# char(2), Sclass char(6))

院系：系别D#，系名Dname, 系主任Dean

Dept(D# char(2)，Dname char(10), Dean char(10))

课程：课号C#，课名Cname，教师编号T#，学时Chours，学分Credit

Course(C# char(3)，cname char(12)，Chours integer，Credit float(1)，T# char(3))

教师：教师编号T#，教师名Tname,所属院系D#，工资Salary

Teacher( T# char(3)，Tname char(10)，D# char(2)，Salary float(2))

选课：学号S#,课号C#,成绩Score

SC($# char(8), C# char(3), Score float(1))
在这里插入图片描述

在这里插入图片描述

1.2.2 SQL-DDL

建立数据库

  • 包括两件事：定义数据库和表(使用DDL)，向表中追加元组（使用DML）
  • DDL：Data Definition Language
    • 创建数据库(DB)-Create Database
    • 创建DB中的Table(定义关系模式)–Create Table
    • 定义Table及其各个属性的约束条件(定义完整性约束)
    • 定义View(定义外模式及E-C映像)
    • 定义Index、Tablespace…等(定义物理存储参数)
    • 上述各种定义的撤消与修正
  • DDL 通常由DBA来使用，也有经DBA授权后由应用程序员来使用
1.2.2.1 CREATE DATABASE

  • 数据库(Database) 是若干具有相互关联关系的Table/Relation 的集合

  • 数据库可以看作是一个集中存放若干Table的大型文件

  • CREATE DATABASE 的简单语法形式

    CREATE DATABASE 数据库名;

示例：创建课程学习数据库SCT

CREATE DATABASE SCT;
1.2.2.2 CREATE TABLE

CREATE TABLE 简单语法形式:

  • [] 代表可选，即我们一个表至少要有一列，| 表示隔开的两项可选
  • Primary key: 主键约束。每个表只能创建一个主键约束。
  • Unique唯束: (即候选键)。可以有多个唯一性约束
  • Not null：非空约束。是指该列允许不允许有空值出现，如选择了Not null 表明该列不允许有空值出现
  • 语法中的数据类型在SQL标准中有定义
Create table 表名(列名 数据类型
[Primary key | Unique] [Not null]
[, 列名 数据类型
[Not null] , ... ]);

  • 在SQL-92标准中定义的数据类型

    • char(n):固定长度的字符串
    • varchar(n):可变长字符串
    • int: 整数 //有时不同系统也写作integer
    • numeric(p，q): 固定精度数字，小数点左边p位，右边p-q位
    • real: 浮点精度数字 //有时不同系统也写作float(n), 小数点后保留n位
    • date: 日期(如 2003-09-12)
    • time: 时间(如 23:15:003)
    • …

  • 现行商用DBMS的数据类型有时和上面有些差异，请注意;

  • 和高级语言的数据类型，总体上是一致的，但也有些差异。

我们尝试定义如下这张表：

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

CREATE TABLE Student(S\# char(8) not null, Sname char(10), Ssex char(2), Sage integer, D\# char(2), Sclass char(6));

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

CREATE TABLE Course(C\# char(3) not null, Cname char(12), Chours integer, Credit float(1), T\# char(3));
1.2.3 SQL-DML
  • DML: Data Manipulation Language
    • 向Table中追加新的元组: Insert
    • 修改Table中某些元组中的某些属性的值: Update
    • 删除Table中的某些元组: Delete
    • 对Table中的数据进行各种条件的检索: Select
  • DML通常由用户或应用程序员使用，访问经授权的数据库
1.2.3.1 INSERT INTO

INSERT INTO简单语法形式:

INSERT INTO 表名[(列名[,列名] ...]
	VALUES(值[, 值], ...);
  • values后面值的排列，须与INTO子句后面的列名排列一致
  • 若表名后的所有列名省略，则values 后的值的排列，须与该表存储中的列名排列一致

示例：追加学生表中的元组

INSERT INTO Student
VALUES  ('114514', 'sam', '男', 20, '03', '98301')

INSERT INTO Student (S\#, Sname, Ssex, Sage, D\#, Sclass)
VALUES  ('114514', 'sam', '男', 20, '03', '98301')

1.3 用SQL 语言进行简单查询

1.3.1 单表查询 SELECT FROM WHERE

SELECT 的简单语法形式：

SELECT 列名 [[, 列名]...]
FROM 表名
[WHERE 检索条件];
  • 语义: 从表名所给出的表中，查询出满足检索条件的元组，并按给定的列名及顺序进行投影显示。
  • 相当于: ∏ 列名 , . . . , 列名 ( σ 检索条件 ( 表名 ) ) \prod_{列名},...,列名(\sigma_{检索条件}(表名)) ∏列名​,...,列名(σ检索条件​(表名))
  • Select语句中的select，…，from…，where…，等被称为子句，在以上基本形式基础上会增加许多构成要素，也会增加许多新的子句，满足不同的需求。

示例: 检索学生表中所有学生的信息

SELECT S\#, Sname, Ssex, Sage, Sclass, D\#
FROM Student;
# 等价于
SELECT *
FROM Student;

示例: 检索学生表中所有学生的姓名及年龄

SELECT Sname, Sage
FROM Student;

示例: 检索学生表中所有年龄小于等于19岁的学生的年龄及姓名

SELECT Sname, Sage
FROM Student
WHERE Sage <= 19;
1.3.2 检索条件的书写

与关系代数中的条件书写一样，只是其逻辑运算符用and, or, not来表示, 同时也要注意运算符的优先次序及括弧的使用。书写要点是注
意对自然语言检索条件的正确理解。

示例: 检索教师表中所有工资少于1500元或者工资大于2000元 并且是03系的教师姓名?

SELECT Tname
FROM Teacher
WHERE (Salary < 1500 or Salary > 2000) and D\# = '03';

如果要查询即学过001号课程又学过002号课程的学生的学号, 该如何做?

下面是一个错误做法, 等到了连接运算再给出正确做法

SELECT S\# FROM SC
WHERE C\#='001' AND C#='002'
1.3.3 检索结果去重-SELECT DISTINCT FROM WHERE

关系模型不允许出现重复元组。但现实DBMS，却允许出现重复元组，但也允许无重复元组。

  • 在 Table 中要求无重复元组是通过定义Primary key或Unique来保证的; 而在检索结果中要求无重复元组，是通过DISTINCT保留字的使用来实现的。

比如我们要选择选课表中成绩大于80的学号(因为可能一个人不同课程都会>80,会有重复),我们就可以用DISTINCT来去重

SELECT DISTINCT C\#
FROM SC
WHERE Score > 80
1.3.4 检索结果排序-SELECT FROM WHERE ORDER BY

DBMS可以对检索结果进行排序，可以升序排列，也可以降序排列

  • SELECT 语句中结果排序是通过增加ORDER BY子句实现的
ORDER BY 列名 [ASC|DESC]

  • 意义为检索结果按指定列名进行排序，若后跟asc或省略，则为升序; 若后跟desc,则为降序。

  • 示例: 按学号由小到大的顺序显示出所有学生的学号及姓名

    SELECT S\#, Sname FROM Student
ORDER BY S\# ASC;

  • 示例: 检索002号课大于80分的所有同学学号并按成绩由高到低顺序显示

    SELECT S\# FROM SC WHERE C\# = '002'AND Score > 80
ORDER BY Score DESC;
1.3.5 模糊查询-SELECT FROM WHERE * LIKE *

模糊査询问题

比如检索姓张的学生，检索张某某: 这类查询问题，SELECT 语句是通过在检索条件中引入运算符 like 来表示的

  • 含有 like 运算符的表达式

    列名 [not] like "字符串"

  • 找出匹配给定字符串的字符串。其中给定字符串中可以出现%，等匹配符.

  • 匹配规则:

    • ‘%’: 匹配零个或多个字符
    • ‘_’: 匹配任意单个字符
    • ‘\’: 转义字符，用于去掉一些特殊字符的特定含义，使其被作为普通字符看待, 如用“%”去匹配字符%，用\_去匹配字符_

示例: 检索所有姓张的学生学号及姓名

SELECT S\#, Sname, FROM Student
WHERE Sname LIKE '张%';

示例: 检索名字为张某某的所有同学姓名

因为一个汉字是两个字符, 所以用了四个下划线

SELECT Sname FROM Student
WHERE Sname Like '张____'

示例: 检索名字不姓张的所有同学姓名

SELECT Sname FROM Student
WHERE Sname NOT Like '张____'

1.4 利用 SQL 语言进行多表联合查询

多表联合检索可以通过连接运算来完成，而连接运算又可以通过广义笛卡尔积后再进行选择运算来实现。

SELECT 的多表联合检索语句

SELECT 列名 [[, 列名]...]
FROM 表1, 表2, ...
WHERE 检索条件;
  • 相当于 ∏ 列名 , . . . , 列名 ( σ 检索条件 ( 表名 1 × 表名 2 × . . . ) ) \prod_{列名,...,列名}(\sigma_{检索条件}(表名1 \times 表名2\times ...)) ∏列名,...,列名​(σ检索条件​(表名1×表名2×...))
  • 检索条件中要包含连接条件，通过不同的连接条件可以实现等值连接、不等值连接及各种θ-连接
1.4.1 θ-连接之等值连接

示例: 按 “001” 号课成绩由高到低顺序显示所有学生的姓名(二表连接)

SELECT Sname FROM Student, SC
WHERE Student.S\# = SC.S\# AND SC.C\# = '001'
ORDER BY Score DESC;
  • 多表连接时, 如两个表的属性名相同, 则需采用 表名.属性名 方式来限定该属性是属于哪一个表

示例: 按’数据库’课成绩由高到低顺序显示所有同学姓名(三表连接)

SELECT Sname FROM Student, SC, Course
WHERE Student.S\# = SC.S\# AND SC.C\# = Course.C\#
AND Cname = '数据库'
ORDER BY Score DESC;
1.4.2 重名处理
SELECT 列名 as 列别名 [[, 列名 as  列别名]]
FROM 表名1 as 表别名1, 表名2 as 表别名2, ...
WHERE 检索条件 ;
  • 上述的as 可以省略
  • 当定义了别名后，在检索条件中可以用别名来限定属性

示例：求有薪水差额的任意两位教师

SELECT T1.Tname as Teacher1, T2.Tname as Teacher2
FROM Teacher T1, Teacher T2
WHERE T1.Salary > T2.Salary

示例：求年龄有差异的任意两位同学的姓名

SELECT S1.Sname AS Stud1, S2.Sname AS Stud2
FROM Student S1, Student, S2
WHERE S1.Sage > S2.Sage

1.5 SQL-更新操作

  • 元组新增INSERT: 新增一个或一些元组到数据库的Table中
  • 元组更新UPDATE: 对某些元组中的某些属性值进行重新设定
  • 元组删除DELETE: 删除某些元组
1.5.1 INSERT

元组新增INSERT 命令有两种形式：

单一元组新增命令形式: 插入一条指定元组值的元组

INSERT INTO 表名[(列名[，列名]...)]
VALUES (值[，值]..);

示例：

指定列顺序

INSERT INTO Teacher(T#, Tname, D#, Salary)
VALUES ("005", "阮小七", "03", "1250")

缺省列顺序（则值的给出需要和表一致）

INSERT INTO Teacher
VALUES ("005", "阮小七", "03", "1250")

批数据新增命令形式: 插入子查询结果中的若干条元组。待插入的元组由子查询给出。

INSERT INTO 表名 [(列名[, 列名]...)]
子查询

示例：

新建立Table: St(S#, Sname),将检索到的满足条件的同学新增到
表中

INSERT INTO St (S#, Sname)
SELECT S#, Sname From Student
WHERE Sname like '%伟' ;

INSERT INTO St(S#, Sname)
SELECT S#, Sname FROM Student ORDER BY Sname,

注意: 当新增元组时，DBMS会检查用户的完整性约束条件等，如不符合完整性约束条件，则将不会执行新增动作(将在后面介绍)。

示例:

新建Table: SCt(S#,C#,Score),将检索到的成绩及格同学的记录新增到该表中

INSERT INTO SCt (S#, C#, Score)
SELECT S#, C#, Score From SC
WHERE Score>=60;
1.5.2 DELETE

元组删除DELETE命令: 删除满足指定条件的元组

DELETE FROM 表名 [WHERE 条件表达式];

如果Where条件省略，则删除所有的元组。

示例：删除SC表中的所有元组

DELETE FROM SC;

示例: 删除98030101号同学所选的所有课程

DELETE FROM SC WHERE S#='98030101';

示例: 删除自动控制系的所有同学

DELETE FROM Student WHERE D# IN
(SELECT D# From Dept WHERE Dname='自动控制’);

示例: 删除有四门不及格课程的所有同学

DELETE FROM Student WHERE S# in
(Select S# FROM WHERE Score < 60
 GROUP BY $# Having COUNT(*)>= 4 );

注意: 当删除元组时，DBMS会检查用户的完整性约束条件等，如不符合完整性约束条件，则将不会执行删除动作。

1.5.3 UPDATE

元组更新Update命令:用指定要求的值更新指定表中满足指定条件的元组的指定列的值

UPDATE 表名
SET 列名=表达式|(子查询)
[[, 列名=表达式|(子查询)]...]
[Where 条件表达式];

如果Where条件省略，则更新所有的元组.

示例: 将所有教师工资上调5%

UPDATE Teacher
SET Salary = Salary * 1.05

示例: 当某同学001号课的成绩低于该课程平均成绩时，将该同学该门课成绩提高5%

UPDATE SC
SET Score = Score * 1.5
WHERE C#='001' AND Score < some
	(SELECT AVG(Score) FROM SC
    WHERE C#='001');

1.6 SQL- 撤销与修改

修正数据库: 修正数据库的定义，主要是修正表的定义

修正基本表的定义

ALTER TABLE tablename
[ADD {colname datatype, ...}]	增加新列
[DROP{完整性约束名}]	删除完整性约束
[MODIFY {colname datatype, ...}]	修改列定义

示例：

在学生表Student(S#,Sname,Ssex,Sage,D#,Sclass)基础上增加二列Saddr, PID

ALTER TABLE Student ADD Saddr CHAR(40), PID CHAR(18);

撤销基本表

DROP TABLE 表名

注意，SQL-delete语句只是删除表中的元组, 而撤消基本表 drop table 的操作是撤消包含表格式、表中所有元组、由该表导出的视图等相关的所有内容，所以使用要特别注意。

撤销数据库

DROP DATABASE 数据库名称;

1.7 SQL- 数据库指定与关闭命令

有些DBMS提供了操作多个数据库的能力，此时在进行数据库操作时需要指定待操作数据库与关闭数据库的功能。

指定当前数据库

USE 数据库名;

关闭当前数据库

CLOSE 数据库名

二、复杂查询与视图

2.1 子查询

为什么需要子查询 ?

现实中，很多情况需要进行下述条件的判断

  • 集合成员资格
    • 某一元素是否是某一个集合的成员
  • 集合之间的比较
    • 某一个集合是否包含另一个集合等
  • 集合基数的测试
    • 测试集合是否为空
    • 测试集合是否存在重复元组

子查询: 出现在WHERE子句中的SELECT语句被称为子查询(subquery)，子查询返回了一个集合，可以通过与这个集合的比较来确定另一个查询集合.

三种类型的子查询：(NOT) IN-子查询；θ-Some / θ-ALL子查询; (NOT)EXISTS子查询

2.1.1 (NOT) IN-子查询

基本语法

表达式 [NOT] IN (子查询)
  • 语法中，表达式的最简单形式就是列名或常数。
  • 语义:判断某一表达式的值是否在子查询的结果中。

比如 我们从 actor 表中找出 first_name 为 CUBA 或者 BELA 的元组

mysql> SELECT * FROM ACTOR
    -> WHERE FIRST_NAME IN ("CUBA", "BELA");
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|       15 | CUBA       | OLIVIER   | 2006-02-15 04:34:33 |
|      118 | CUBA       | ALLEN     | 2006-02-15 04:34:33 |
|      189 | CUBA       | BIRCH     | 2006-02-15 04:34:33 |
|      196 | BELA       | WALKEN    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
4 rows in set (0.00 sec)

示例: 列出选修了001号课程的学生的学号和姓名

SELECT S#, Sname FROM Student
WHERE S# IN (SELECT S# FROM SC WHERE C#='001');
2.1.1.1 非相关子查询

带有子查询的Select语句区分为内层和外层

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

非相关子查询：内层查询独立进行，没有涉及任何外层查询相关信息的子查询

2.1.1.2 相关子查询

相关子查询: 内层查询需要依靠外层查询的某些参量作为限定条件才能进行的子查询

  • 外层向内层传递的参量需要使用外层的表名或表别名来限定

示例：求学过001号课程的同学的姓名

SELECT Sname
FROM Student Stud
WHERE S# IN (SELECT S#
FROM SC
WHERE S#= Stud.S# and C# = '001'
);

注意: 相关子查询只能由外层向内层传递参数，而不能反之; 这也称为变量的作用域原则。

2.1.2 θ some / θ all 子查询

基本语法：

表达式 θ some (子查询)
表达式 θ all (子查询)

语法中，θ是比较运算符：<, >, >=, <=, =，<>。

语义: 将表达式的值与子查询的结果进行比较:

  • 如果表达式的值至少与子查询结果的某一个值相比较满足θ关系，则“表达式 θ some(子查询)”的结果便为真;
  • 如果表达式的值与子查询结果的所有值相比较都满足θ关系，则“表达式 θ all (子查询)”的结果便为真;

示例：找出工资最低的教师姓名

SELECT Tname FROM Teacher
WHERE Salary <= all(Select Salary FROM Teacher);

示例：找出001号课成绩最高的所有学生的学号

SELECT S# FROM Student
WHERE C#="001" AND
	Score >= all(SELECT Score FROM SC WHERE C#="001");

示例：找出98030101号同学成绩最低的课程号

SELECT C# FROM SC
WHERE S#="98030101" AND
	Score <= all(SELECT Score FROM SC WHERE S#="98030101");

示例：求工资小于任一教师工资的教师姓名

SELECT Tname FROM Teacher
WHERE Salary < any(SELECT Salary FROM Teacher);

等价性变换

表达式 = some(子查询)
表达式 in some(子查询)

但是下面两种确是不等价的：

表达式 not in (子查询)
# 二者不等价！！！
表达式 <> some (子查询)

事实上，和 not in 等价的是：

表达式 not in (子查询)
表达式 <> all (子查询)
2.1.3 EXISTS 和 NOT EXISTS 谓词子查询

(NOT) EXISTS 子查询

基本语法：

[not] EXISTS (子查询)

语义：子查询结果中有无元组存在

示例：检索选修了赵三老师主讲课程的所有同学的姓名

SELECT DISTINCT Sname FROM Student
WHERE EXISTS (SELECT * FROM SC, Course, Teacher 
WHERE SC.C# = Course.C and SC.S# = Student.S# 
AND Course.T# = Teacher.T# AND Tname = "赵三");

当然我们也可以不用EXISTS 谓词

SELECT DISTINCT Sname FROM Student, SC, Course, Teacher
WHERE SC.C# = Course.C# AND SC.S# = Student.S# 
AND Course.T# = Teacher.T# AND Tname = '赵三';

但是 NOT EXISTS 却可以实现很多功能

示例：检索学过001号教师主讲的所有课程的所有同学的姓名

SELECT Sname FROM Student
WHERE NOT EXISTS
(SELECT * FROM Course
WHERE Course.T# = '001' AND NOT EXISTS
(SELECT * FROM SC
WHERE S# = Student.S# AND C# = Course.C#));

上述语句的意思：不存在有一门001号教师主讲的课程该同学没学过

示例: 列出至少学过98030101号同学学过所有课程的同学的学号

SELECT DISTINCT S# FROM SC SC1
WHERE NOT EXISTS
(SELECT * FROM SC SC2
WHERE SC2.S# = '98030101' AND NOT EXISTS
 (SELECT * FROM SC
 	WHERE C#=SC2.C# AND S# = SC1.S#)
)

怎么用关系代数表达？
∏ S # , C # ( S C ) ÷ ∏ C # ( σ S # = ′ 9803010 1 ′ ( S C ) ) \prod_{S\#, C\#} (SC) \div \prod_{C\#} (\sigma_{S\#='98030101'}(SC)) S#,C#∏​(SC)÷C#∏​(σS#=′98030101′​(SC))
示例：已知SPJ(Sno，Pno，Jno，Qty)，其中Sno供应商号，Pno零件号，Jno工程号，Qty数量，列出至少用了供应商S1供应的全部零件的工程号

SELECT DISTINCT Jno FROM SPJ SPJ1
WHERE NOT EXISTS
	(SELECT * FROM SPJ SPJ2
    	WHERE SPJ2.Sno = 'S1' AND NOT EXISTS(
        SELECT * FROM SPJ3.Pno = SPJ2.Pno
        AND SPJ3.Jno = SPJ1.Jno));

2.2 利用 SQL 语言进行结果运算与聚集运算

2.2.1 结果计算

Select-From-Where语句中，Select子句后面不仅可是列名，而且可是一些计算表达式或聚集函数，表明在投影的同时直接进行一些运算

SELECT 列名 | expr | agfunc(列名)[[,列名|expr|agfunc(列名)]...]
FROM 表名1[,表名2...]
[WHERE 检索条件];
  • expr可以是常量、列名、或由常量、列名、特殊函数及算术运算符构成的算术运算式。特殊函数的使用需结合各自DBMS的说明书
  • agfunc()是一些聚集函数

示例：求有差额(差额>0)的任意两位教师的薪水差额

SELECT T1.Tname AS TR1, T2.Tname AS TR2, T1.Salary - T2.Salary
FROM Teacher T1, Teacher T2
WHERE T1.Salary > T2.Salary
2.2.2 聚集函数
  • SQL提供了五个作用在简单列值集合上的内置聚集函数agfunc，分别是COUNT、SUM、AVG、MAX、MIN
  • SQL聚集函数的参数类型、结果类型与作用如下

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

示例：求教师工资总额

SELECT SUM(Salary) From Teacher;

示例：求 交易总额（mysql下）

mysql> SELECT SUM(AMOUNT) FROM PAYMENT;
+-------------+
| SUM(AMOUNT) |
+-------------+
|    67406.56 |
+-------------+
1 row in set (0.01 sec)

mysql>
2.2.3 分组查询与分组过滤

下面的查询请求该如何表达呢 ?

  • 求每一门课程的平均成绩
  • 求每一个学生的平均成绩
2.2.3.1 分组查询

分组：SQL可以将检索到的元组按照某一条件进行分类，具有相同条件值的元组划到一个组或一个集合中，同时处理多个组或集合的聚集运算。

分组的基本语法：

SELECT 列名|expr|agfunc(列名)[[,列名|expr|agfunc(列名)]...]
FROM 表名1[,表名2...]
[WHERE 检索条件]
[GROUP BY 分组条件];

  • 分组条件可以是

    列名1、列名2……

示例：求每一个学生的平均成绩

SELECT S#, AVG(Score), FROM SC
GROUP BY S#;
  • 上例是按学号进行分组，即学号相同的元组划到一个组中并求平均值
2.2.3.2 分组过滤
  • 聚集函数是不允许用于Where子句中的：Where子句是对每一元组进行条件过滤，而不是对集合进行条件过滤

分组过滤：若要对集合(即分组)进行条件过滤，即满足条件的集合/分组留下，不满足条件的集合/分组剔除。

Having子句，又称分组过滤子句。需要有Group by子句支持，换句话说，没有Group by子句，便不能有Having子包

SELECT 列名 | expr | agfunc(列名) [[, 列名 | expr | agfunc(列名)]...]
FROM 表名1 [, 表名2]
[WHERE 检索条件]
[GROUP BY 分组条件 [HAVING 分组过滤条件]];

示例：求不及格课程超过两门的同学的学号

SELECT S# FROM SC
WHERE Score < 60
GROUP BY S# HAVING COUNT(*) > 2;

示例：求交易额大于11的顾客id(mysql)

mysql> SELECT CUSTOMER_ID FROM PAYMENT
    -> GROUP BY CUSTOMER_ID HAVING MAX(AMOUNT) > 11;
+-------------+
| CUSTOMER_ID |
+-------------+
|          13 |
|         116 |
|         195 |
|         196 |
|         204 |
|         237 |
|         305 |
|         362 |
|         591 |
|         592 |
+-------------+
10 rows in set (0.04 sec)

mysql>

示例：求有两门以上不及格课程同学的学号及其平均成绩

SELECT S#, AVG(Score) FROM SC
WHERE S# IN
    (SELECT S# FROM SC
    WHERE Score < 60
    GROUP BY S# HAVING COUNT(*) > 2)
GROUP BY S#;
2.2.3.3 HAVING 子句 和 WHERE 子句 表达条件的区别
  • 每一行都要检查满足与否的条件要用WHERE子句表达
  • 每一分组检查满足与否的条件要用 HAVING 子句表达。
    注意: 不是每一行都检查，所以使用HAVING子句一定要有GROUP BY子句

2.3 利用SQL 语言实现关系代数操作

2.3.1 并-交-差的处理

SQL语言：并运算 UNION，交运算INTERSECT，差运算EXCEPT

基本语法形式：

子查询 {Union [ALL]|Intersect [ALL]|Except [ALL]子查询 }

通常情况下自动删除重复元组：不带ALL。若要保留重复的元组，则要带ALL。

假设子查询1的一个元组出现m次，子查询2的一个元组出现n次，则该元组在：

  • 子查询1 UNION ALL 子查询2，出现 m + n 次
  • 子查询1 INTERSET ALL 子查询2，出现 min(m, n) 次
  • 子查询1 EXCEPT ALL 子查询2，出现 max(0, m - n) 次

SQL 并运算

示例：求学过002号课的同学或学过003号课的同学学号

SELECT S# FROM SC WHERE C#='002'
UNION
SELECT S# FROM SC WHERE C#='003';

示例：

已知两个表
Customers(CID, Cname, City, Discnt)
Agents(AID, Aname, City, Percent)

求客户所在的或者代理商所在的城市

SELECT City FROM Customers
UNION
SELECT City FROM Agents;

SQL 交运算

示例：求既学过002号课，又学过003号课的同学学号

SELECT S# FROM SC WHERE C#='002'
INTERSECT
SELECT S# FROM SC WHERE C#='003';

当然，也可以不用 INTERSECT

SELECT S# FROM SC WHERE C# = '002' AND S# IN 
(SELECT S# FROM SC WHERE C#='003');

交运算符Intersect并没有增强SQL的表达能力，没有Intersect，SQL也可以用其他方式表达同样的查询需求。只是有了Intersect更容易表达一些，但增加了SQL语言的不唯一性。

SQL 差运算

示例:假定所有学生都有选课，求没学过002号课程的学生学号

SELECT DISTINCT S# FROM SC
EXCEPT 
SELECT S# FROM SC WHERE C# = '002';

等价查询：

SELECT DISTINCT S# FROM SC SC1
WHERE NOT EXISTS (SELECT * FROM SC
      WHERE C#='002' AND S#=SC1.S#);

差运算符Except也没有增强SQL的表达能力，没有Except，SQL也可以用其他方式表达同样的查询需求。只是有了Except更容易表达一些，但增加了SQL语言的不唯一性。

  • UNION运算符是Entry-SQL92的一部分
  • INTERSECT、EXCEPT运算符是FuII-SQL92的一部分
  • 它们都是Core-SQL99的一部分，但有些DBMS并不支持这些运算，使用时要注意
2.3.2 空值处理

  • 空值是其值不知道、不确定、不存在的值

  • 数据库中有了空值，会影响许多方面，如影响聚集函数运算的正确性，不能参与算术、比较或逻辑运算等

  • 例如：下图所示表SC，如果有某一记录为空值，则求001号课程的平均成绩?会是多少呢?外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

  • 以前，很多DBMS将空值按默认值处理，如字符串类型则以空格来表示而如数值类型则以0来表示，这也将会引起统计、计算上的不正确性。

  • 在SQL标准中和许多现流行的DBMS中，空值被用一种特殊的符号NuIl来标记，使用特殊的空值检测函数来获得某列的值是否为空值。

  • 空值检测

    IS [NOT] NULL

    测试 指定列的值 是否为空值

示例：找出年龄值为空的学生姓名

SELECT Sname FROM Student
WHERE Sage IS NULL;

不能写成 WHERE Sage = NULL，空值不能进行计算

现行DBMS的空值处理小结

  • 除IS [NOT] NULL之外，空值不满足任何查找条件
  • 如果NULL参与算术运算，则该算术表达式的值为NULL
  • 如果NULL参与比较运算，则结果可视为false。在SOL-92中可看成 unknown
  • 如果NULL参与聚集运算，则除count(*)之外其它聚集函数都忽略NULL

示例：

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

SELECT AVG(Score) FROM SC;

结果为 73.5

2.3.3 内连接、外连接

SQL的高级语法中引入了内连接与外连接运算，具体形式：

SELECT 列名 [[, 列名]...]
	FROM 表名1 [NATURAL]
				[INNER | {LEFT | RIGHT | FULL} [OUTER]] JOIN 表名2
			{ON 连接条件 | USING (Colname {, Colname...})}
	[WHERE 检索条件]...;

上例的连接运算由两部分构成：连接类型和连接条件

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

Inner join: 即 关系代数中的 θ-连接运算

Left Outer Join，Right Outer Join，Full Outer Join：即关系代数中的外连接运算

  • 如**“表 1 Left Outer Join 表2”**，则连接后，表1的任何元组t 都会出现在结果表中，如表2中有满足连接条件的元组s，则t 与 s连接；否则 t 和 空值元组连接
  • 如**“表1 Right Outer Join 表2”**，则连接后，表2的任何元组s 都会出现在结果表中，如表1中有满足连接条件的元组t，则t与s连接；否则 s 与空值元组连接
  • 如**“表1 Full Outer Join 表2”**，是前两者的并。

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

  • 连接中使用 natural
    • 出现在结果关系中的两个连接关系的元组在公共属性上取值相等，且公共属性只出现一次
  • 连接中使用 on <连接条件>
    • 出现在结果关系中的两个连接关系的元组取值满足连接条件，且公共属性出现两次
  • 连接中使用 using(Col1, Col2, …, Coln)
    • (Col1, Col2, …Coln) 是两个连接关系的公共属性的子集，元组在 (Col1，Col2，…Coln) 上取值相等，且 (Col1，Col2，…Coln) 只出现一次

Inner Join

示例: 求所有教师的任课情况并按教师号排序(没有任课的教师也需列在表中)

SELECT Teacher.T#, Tname, Cname
FROM Teacher Inner JOIN Course
ON Teacher.T# = Course.T#
ORDER BY Teacher.T# ASC;

2.4 回顾 SQL-SELECT

2.4.1 SQL-SELECT 的完整语法

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

2.4.2 SQL-SELECT 的完整语法

Subquery ::==

SELECT [ALL|DISTINCT]{* | expr [[AS] c_alias]{,...}}
FROM tableref {,...}
[WHERE search_condition]
[GROUP BY column {,... }[HAVING search_condition]]
| Subquery [UNION [ALL] | INTERSECT [ALL] | EXCEPT [ALL]]
[CORRESPONDING「BY](colname {,... })] subquery;
Tableref ::== tablename [corr_name]

SELECT statement ::==

Subquery [ORDER BY result_column [ASC | DESC] {,...}]
2.4.3 SQL-SELECT 的扩展

基本的SQL：另一SELECT-FROM-WHERE只能出现在WHERE子句;

新标准：引入对象概念，可以在能使用聚集(collection)的任何位置使用SELECT FROM WHERE

  • FROM 子句中使用
  • SELECT 子句中使用
  • WHERE 子句中使用

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

2.5 视图及其应用

2.5.1 视图的概念与结构

对应概念模式的数据在SQL中被称为基本表(Table)，而对应外模式的数据称为视图(View)。视图不仅包含外模式，而且包含其E-C映像。

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

  • 基本表是实际存储于存储文件中的表，基本表中的数据是需要存储的
  • 视图在SQL中只存储其由基本表导出视图所需要的公式，即由基本表产生视图的映像信息，其数据并不存储，而是在运行过程中动态产生与维护的
  • 对视图数据的更改最终要反映在对基本表的更改上。

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

2.5.2 视图的定义

定义视图

CREATE view view_name [(列名,[, 列名]...)]
AS 子查询 [WITH CHECK OPTION]
  • 如果视图的属性名缺省，则默认子查询结果中的属性名；也可以显式指明其所拥有的列名。
  • with check option指明当对视图进行insert，update，delete时，要检査进行insert / update / delete的元组是否满足视图定义中子查询中定义的条件表达式

**示例：**定义一个视图 CompStud 为计算机系的学生，通过该视图可以 将Student表中其他系的学生屏蔽掉

CREATE VIEW CompStud AS 
(SELECT * FROM Student
WHERE D# IN (SELECT D# FROM Dept
		WHERE Dname = '计算机'));

**示例：**定义一个视图Teach为教师任课的情况，把Teacher表中的个人隐私方面的信息，如工资等屏蔽掉，仅反映其教哪门课及其学分等。

CREATE VIEW Teach AS
(SELECT T.Tname, C.Cname, Credit
 	FROM Teachr T, Course C
 	WHERE T.T#=C.C#
);
2.5.3 视图的使用

使用视图：定义好的视图，可以像Table一样，在SQL各种语句中使用

示例：检索主讲数据库课程的教师姓名，我们可使用Teach

SELECT T.Tname FROM Teach T
WHERE T.Cname = '数据库';

定义视图有时候方便用户操作

CREATE View Studstat(S#, Sname, AvgS, MinS, MaxS, CNT)
as( Select S#, Sname, AVG(Score), MIN(Score), Max(Score), Count(*)
FROM student S,Sc WHERE s.$#= sc.S#
GROUP BY s.s#);

示例：基于视图StudStat检索某一学生平均成绩

SELECT Sname, AvgS FROM StudStat WHERE Sname ='张三’;
2.5.4 视图的更新

SOL视图更新：是比较复杂的问题，因视图不保存数据，对视图的更新最终要反映到对基本表的更新上，而有时，视图定义的映射不是可逆的。

示例：

CREATE VIEW S_G(S#, Savg) 
AS (SELECT S#, AVG(Score)
   FROM SC GROUP BY S#);

如要进行下述更新操作 ?

UPDATE S_G
SET Savg = 85
WHERE s#='98030101

能否由视图S_G的更新，而更新SC呢?

不能，聚集函数当然无法更新

示例

CREATE VIEW ClassStud(Sname, Sclass)
AS(SELECT Sname, Sclass
FROM student );

如要进行下述更新操作 ?

INSERT INTO ClassStud
VALUES('张三’, '980301’);

能否由视图ClassStud的更新，而更新Student呢?

不能，因为缺少S#，而S#是Student的主键。

视图更新的可执行性

  • 如果视图的select目标列包含聚集函数，则不能更新
  • 如果视图的select子句使用了 unique 或 distinct，则不能更新
  • 如果视图中包括了group by子句，则不能更新
  • 如果视图中包括经算术表达式计算出来的列，则不能更新
  • 如果视图是由单个表的列构成，但并没有包括主键，则不能更新

对于由单一Table子集构成的视图，即如果视图是从单个基本表使用选择、投影操作导出的，并且包含了基本表的主键，则可以更新

可更新视图示例：

CREATE VIEW CStud(S#, Sname, Sclass)
AS (SELECT S#, Sname, Sclass
   FROM Student WHERE D#='03');

上例是可以更新的：

INSERT INTO CStud
VALUES ("98030104", "张三丰", "980301");
2.5.5 视图的撤销

撤消基本表

DROP TABLE 表名;

三、数据库的完整性

3.1 概念和分类

3.1.1 概念

数据库完整性(DB Integrity)是指DBMS应保证的DB的一种特性——在任何情况下的正确性、有效性和一致性。

  • 广义完整性：语义完整性、并发控制、安全控制、DB故障恢复等
  • 狭义完整性：专指语义完整性，DBMS通常有专门的完整性管理机制与程序来处理语义完整性问题。(本讲专指语义完整性)

完整性涉及到的：

  • 实体完整性
  • 参照完整性
  • 用户自定义完整性

数据库设计中，在E-R图 / IDEF1X图中有很多的完整性约束条件，如何施加到数据库的定义中，如何起作用呢?

为什么会引发数据库完整性的问题呢?

  • 不正当的数据库操作，如输入错误、操作失误、程序处理失误等

数据库完整性管理的作用

  • 防止和避免数据库中不合理数据的出现
  • DBMS应尽可能地自动防止DB中语义不合理现象
  • 如DBMS不能自动防止，则需要应用程序员和用户在进行数据库操作时处处加以小心，每写一条SQL语句都要考虑是否符合语义完整性，这种工作负担是非常沉重的，因此应尽可能多地让DBMS来承担。

DBMS 怎样自动保证完整性呢？

  • DBMS允许用户定义一些完整性性约束规则(用SQL-DDL来定义)
  • 当有DB更新操作时，DBMS自动按照完整性约束条件进行检查，以确保更新操作符合语义完整性

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

完整性约束条件(或称完整性约束规则)的一般形式

Integrity Constraint ::= (O, P, A, R)

  • O：数据集合：约束的对象？
    • 列、多列(元组)、元组集合
  • P：谓词条件：什么样的约束？
  • A：触发条件：什么时候检查？
  • R：响应动作：不满足时怎么办？
3.1.2 分类

域完整性约束条件

  • 施加于某一列上，对给定列上所要更新的某一候选值是否可以接受进行约束条件判断，这是孤立进行的

关系完整性约束条件

  • 施加于关系 / table上，对给定table上所要更新的某一候选元组是否可以接受进行约束条件判断，或是对一个关系中的若干元组和另一个关系中的若干元组间的联系是否可以接受进行约束条件判断

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

我们也可以按照约束来源来分类

结构约束

  • 来自于模型的约束，例如函数依赖约束、主键约束(实体完整性)、外键约束(参照完整性)，只关心数值相等与否、是否允许空值等

内容约束

  • 来自于用户的约束，如用户自定义完整性，关心元组或属性的取值范围

    例如Student表的Sage属性值在15岁至40岁之间等。

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

我们也可以按照约束状态来分类

静态约束

  • 要求DB在任一时候均应满足的约束；例如Sage在任何时候都应满足大于0而小于150(假定人活最大年龄是150)。

动态约束

  • 要求DB从一状态变为另一状态时应满足的约束；例如工资只能升，不能降：工资可以是800元，也可以是1000元；可以从800元更改为1000元，但不能从1000元更改为800元。

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

3.2 SQL语言实现数据库的静态完整性

3.2.1 SQL语言支持的约束类别
  • 静态约束
    • 列完整性——域完整性约束
    • 表完整性——关系完整性约束
  • 动态约束
    • 触发器

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

3.2.2 Create Table
  • Create Table有三种功能：定义关系模式、定义完整性约束和定义物理存储特性
  • 定义完整性约束条件
    • 列完整性
    • 表完整性
CREATE TABLE tablename
((colname datatype [DEFAULT {default_constant | NULL}]
 	[col_constr {col_constr...}]
 |, table_constr
 {,{colname datatype [DEFAULT {default_constant | NULL}]
  		[col_constr {col_constr...}]
  	|, table_constr}
  ...}))
3.2.2.1 Col_constr

一种域约束类型，对单一列的值进行约束

{NOT NULL |						// 列值非空
[CONSTRAINT constraintname ]	  // 为约束命名，便于后续撤销
{ UNIQUE 						// 列值唯一
	| PRIMARY KEY				// 主键
	| CHECK(search_cond)			// 列值满足约束，条件只能使用当前列值
	| REFERENCES tablename [(colname)]		
		[ON DELETE {CASCADE | SET NULL}]
}}
//引用另一表tablename的列colname的值，如有ON DELETE CASCADE 或ON DELETE SET NULL语句，则删除被引用表的某列值v时，要将本表
//该列值为v 的记录删除或列值更新为null；缺省为无操作

示例

CREATE TABLE Student(S# char(8) NOT NULL UNIQUE, Sname char(10),
Ssex CHAR(2) CONSTRAINT ctssex CHECK (Ssex='男' or Ssex='女'), Sage INTEGER CHECK (Sage >= 1 and Sage < 150),
D# CHAR(2) REFERENCES Dept(D#) ON DELETE CASCADE,
Sclass CHAR(6));

解释：Ssex 只能为男 / 女，1 <= Sage < 150，D#是外键

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

当 Dept 中的 某个D#被删除，那么Student 中所有对应的D# 都会被删除

3.2.2.2 table_constr

一种关系约束类型，对多列或元组的值进行约束

[CONSTRAINT constraintname]
{UNIQUE (colname {, colname...})
| PRIMARY KEY (colname {, colname...})
| CHECK (search_condition)
| FOREIGN KEY (colname {, colname...})
	REFERENCES tablename [(colname {, colname...})]
	[ON DELETE CASCADE]};

table_constr表约束：是应用在关系上，即对关系的多列或元组进行约束，列约束是其特例

CREATE TABLE SC(S# CHAR(8), C# CHAR(3),
Score FLOAT(1) CONSTRAINT cstore CHECK (Score >= 0.0 and
Score <= 100.0),
FOREIGN KEY(S#) REFERENCES student(S#) ON DELETE CASCADE, 
FOREIGN KEY(C#) REFERENCES student(C#) ON DELETE CASCADE);

CHECK中的条件可以是SELECT FROM WHERE 内任何WHERE 后的语句，包含子查询。

示例：

CREATE TABLE SC(S# CHAR(8) CHECK(S# IN (SELECT S# FROM STUDENT)),
               C# CHAR(3) CHECK (C# IN (SELECT C# FROM course)),
               Score FLOAT(1) CONSTRAINT ctscore CHECK (Score >= 0.0 and
                                                    	Score <= 100.0)),

Create Table 中定义的表约束或列约束可以在以后根据需要进行撤消或追加。撤消或追加约束的语句是Alter Table(不同系统可能有差异)

ALTER TABLE tblname
[ADD ({colname datatype [DEFAULT {default_constant | NULL}]
      [col_constr{col_constr}] | , table_constr}
     {, colname...})]
[DROP {COLUMN columnname | (columnname {, columnnmae...})}]
[MODIFY (columnname data-type 
        [DEFAULT {default_const | NULL}][[NOT] NULL]
        {, columnname ...})]
[ADD CONSTRAINT constr_name]
[DROP CONSTRAINT constr_name]
[DROP PRIMARY KEY];

示例：撤消SC表的ctscore约束(由此可见，未命名的约束是不能撤消)

ALTER TABLE SC
DROP CONSTRAINT ctscore;

示例：若要再对SC表的score进行约束，比如分数在0~150之间，则可新增加一个约束。在Oracle中增加新约束，需要通过修改列的定义来完成

ALTER TABLE SC
MODIFY (Score FLOAT(1) CONSTRAINT nctscore CHECK(Score >= 0.0 AND Score <= 150.0));
3.2.2.3 断言ASSERTION

  • 一个断言就是一个谓词表达式，它表达了希望数据库总能满足的条件

  • 表约束和列约束就是一些特殊的断言

  • SQL还提供了复杂条件表达的断言。其语法形式为：

    CREATE ASSERTION <assertion-name> CHECK <predicate>

  • 一个断言创建后，系统将检测其有效性，并在每一次更新中测试更新是否违反该断言。

  • 断言测试增加了数据库维护的负担，要小心使用复杂的断言。

    • “每位教师不能在同一个学期的同一时间段在两个不同的教室上课"

示例

每笔贷款,要求至少一位借款者账户中存有最低数目的余额，例如1000元

CREATE ASSERTION balance_constraint CHECK
(NOT EXISTS (
SELECT * FROM loan
    WHERE NOT EXISTS(
    SELECT * FROM borrower, depositor, account
    WHERE loan.loan_number = borrower.loan_number 
	AND borrower.customer name=depositor.customer name
	AND depositor.account number= account.account number
	AND account.balance >= 1000)))

3.3 SQL 语言实现数据库的动态完整性

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

3.3.1 触发器 Trigger

CREATE TABLE 中的表约束和列约束基本上都是静态的约束，也基本上都是对单一列或单一元组的约束(尽管有参照完整性)，为实现动态约束以及多个元组之间的完整性约束，就需要触发器技术Trigger

Trigger 是一种过程完整性约束(相比之下，CREATE TABLE中定义的都是非过程性约束)，是一段程序，该程序可以在特定的时刻被自动触发执行，比如在一次更新操作之前执行，或在更新操作之后执行。

基本语法

CREATE TRIGGER trigger_name BEFORE | AFTER
{ INSERT | DELETE | UPDATE [OF colname {, colname...}]}
ON tablename [REFERENCING corr_name def {, corr_name def...} ]
[FOR EACH ROW | FOR EACH STATEMENT]
//对更新操作的每一条结果(前者)，或整个更新操作完成(后者)
[WHEN(search_condition)]
//检查条件，如满足执行下述程序
{statement
//单行程序直接书写，多行程序要用下行方式
| BEGIN ATOMIC statement; statement;...} END }

触发器Trigger意义：当某一事件发生时(Before | After)，对该事件产生的结果(或是每一元组，或是整个操作的所有元组)，检查条件search_condition，如果满足条件，则执行后面的程序段。条件或程序段中引用的变量可用 corr_name_ def 来限定。

事件：BEFORE | AFTER { INSERT | DELETE | UPDATE … }

  • 当一个事件(Insert,Delete,或Update)发生之前Before 或发生之后After触发
  • 操作发生，执行触发器操作需处理两组值：更新前的值和更新后的值，这两个值由 corr_name_def 的使用来区分

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

corr_name_def 的定义

{
  OLD [ROW] [AS] old_row_corr_name //更新前的旧元组命别名为
| NEW [ROW] [AS] new_row_corr_name //更新后的新元组命别名为
| OLD TABLE [AS] old_table_corr_name //更新前的旧Table命别名为
| NEW TABLE [AS] new_table_corr_name //更新后的新Table命别名为
}

corr_name_def将在检测条件或后面的动作程序段中被引用处理

示例一：设计一个触发器当进行Teacher表更新元组时，使其工资只能升不能降

CREATE TRIGGER teacher_chgsal BEFORE UPDATE OF salary
ON teacher
REFERENCING NEW x, OLD y
FOR EACH ROW WHEN (x.salary < y.salary)
BEGIN
raise_application_error(-20003, 'invalid salary on update'); // Oracle 的错误处理函数
END;

示例二：假设student(S#, Sname, SumCourse), SumCourse为该同学已学习课程的门数，初始值为0，以后每选修一门都要对其增1。设计一个触发器自动完成上述功能。

CREATE TRIGGER sumc AFTER INSERT ON sc
REFERENCING NEW ROW newi
FOR EACH ROW
BEGIN
	UPDATE student SET SumCourse = SumCourse + 1;
	WHERE S#=:newi.S#;
END;

示例三：假设student(S#,Sname,Sage,Ssex,Sclass)中某一学生要变更其主码S#的值，如使其原来的98030101变更为99030131,此时sc表中该同学已选课记录的S#也需自动随其改变。设计一个触发器完成上述功能

CREATE TRIGGER updS# AFTER UPDATE OF S# ON student
REFERENCING OLD oldi, NEW newi
FOR EACH ROW 
BEGIN
UPDATE sc SET S# = new.S# WHERE S# =: oldi.S#;
END;

示例四：假设student(S#, Sname, SumCourse), 当删除某一同学S#时，该同学的所有选课也都要删除。设计一个触发器完成上述功能

CREATE TRIGGER delS# AFTER DELETE ON Student
REFERENCING OLD oldi
FOR EACH ROW
BEGIN
DELETE sc WHERE S# =: oldi.S#;
END;

示例五 假设student(S#, Sname, SumCourse), 当删除某一同学S#时，该同学的所有选课中的S#都要置为空值。设计一个触发器完成上述功能

CREATE TRIGGER delS# AFTER DELETE ON Student
REFERENCING OLD oldi
FOR EACH ROW
BEGIN
UPDATE sc SET S# = NULL WHERE S#=:oldi.S#;
END;

示例六 假设Dept(D#, Dname, Dean), 而Dean一定是该系教师Teacher(T#, Tname, D#, Salary)中工资最高的教师。设计一个触发器完成上述功能

CREATE TRIGGER upddean BEFORE UPDATE OF Dean ON Dept
REFERENCINIG OLD oldi, NEW newi
FOR EACH ROW WHEN(dean NOT IN (
SELECT Tname FROM Teacher WHERE D#=:newish.D#
AND salary >=
    all (SELECT salary FROM Teacher WHERE D#=:newi.D#)
    BEGIN
         raise_application_error(-200003, 'invalid Dean on update');
    END
))

四、数据库的安全性

4.1 概念

数据库安全性是指DBMS应该保证的数据库的一种特性(机制或手段)：免受非法、非授权用户的使用、泄漏、更改或破坏

数据库安全性管理涉及许多方面

  • 社会法律及伦理方面：私人信息受到保护，未授权人员访问私人信息会违法
  • 公共政策/制度方面：例如，政府或组织的信息公开或非公开制度
  • 安全策略：政府、企业或组织所实施的安全性策略，如集中管理和分散管理，需者方知策略(也称最少特权策略)
  • 数据的安全级别: 绝密(Top Secret),机密(Secret),可信(Confidential)和无分类(Unclassified)
  • 数据库系统DBS的安全级别：物理控制、网络控制、操作系统控制、DBMS控制

4.2 数据库安全性的分类

  • 自主安全性机制：存取控制(Access Control)
    • 通过权限在用户之间的传递，使用户自主管理数据库安全性
  • 强制安全性机制：
    • 通过对数据和用户强制分类，使得不同类别用户能够访问不同类别的数据
  • 推断控制机制：(可参阅相关文献)
    • 防止通过历史信息，推断出不该被其知道的信息下
    • 防止通过公开信息(通常是一些聚集信息)推断出私密信息(个体信息)，通常在一些由个体数据构成的公共数据库中此问题尤为重要
  • 数据加密存储机制：(可参阅相关文献)
    • 通过加密、解密保护数据，密钥、加密/解密方法与传输

4.3 数据库管理员的责任和义务

DBA的责任和义务

  • 熟悉相关的法规、政策，协助组织的决策者制定好相关的安全策略
  • 规划好安全控制保障措施，例如，系统安全级别、不同级别上的安全控制措施，对安全遭破坏的响应
  • 划分好数据的安全级别以及用户的安全级别
  • 实施安全性控制：DBMS专门提供一个DBA账户，该账户是一个超级用户或称系统用户。DBA利用该账户的特权可以进行用户账户的创建以及权限授予和撤消、安全级别控制调整等

4.4 数据库的自主安全性机制

4.4.1 自主安全性机制
  • 通常情况下，自主安全性是通过授权机制来实现的。
  • 用户在使用数据库前必须由DBA处获得一个账户，并由DBA授予该账户一定的权限，该账户的用户依据其所拥有的权限对数据库进行操作；同时，该帐户用户也可将其所拥有的权利转授给其他的用户(账户)，由此实现权限在用户之间的传播和控制。
    • 授权者：决定用户权利的人
    • 授权：授予用户访问的权利

DBMS怎样自动实现自主安全性呢?

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

  • DBMS允许用户定义一些安全性控制规则(用SQL-DCL来定义)
  • 当有DB访问操作时，DBMS自动按照安全性控制规则进行检查，检查通过则允许访问，不通过则不允许访问
4.4.2 数据库自主安全性访问规则

DBMS将权利和用户(账户)结合在一起，形成一个访问规则表，依据该规则表可以实现对数据库的安全性控制

AccessRule：(S，O，t，P)

  • S：请求主体（用户）

  • O：访问对象

  • t：访问权利

  • P：谓词

  • { AccessRule } 通常存放在数据字典或称系统目录中，构成了所有用户对DB的访问权利;

  • 用户多时，可以按用户组建立访问规则

  • 访问对象可大可小(目标粒度Object granularity)：属性/字段、记录/元组、关系、数据库

  • 权利：包括创建、增、删、改、查等

  • 谓词：拥有权利需满足的条件

一个自主安全性控制的例子

员工管理数据库的安全性控制示例

Employee(P#, Pname, Page, Psex, Psalary, D#, HEAD)

有如下的安全性访问要求：

  • 员工管理人员：能访问该数据库的所有内容，便于维护员工信息
  • 收发人员：访问该数据库以确认某员工是哪一个部门的，便于收发工只能访问基本信息，其他信息不允许其访问
  • 每个员工：允许其访问关于自己的记录，以便查询自己的工资情况不能修改
  • 部门领导：能够查询其所领导部门人员的所有情况
  • 高层领导：能访问该数据库的所有内容，但只能读

两种控制示例

  • 按名控制安全性：存储矩阵
  • 按内容控制安全性：？

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

4.4.3 自主安全性的实现方式

第一种：存储矩阵

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

请同学们给出一个算法：已知如上形式的一个存储矩阵，某用户提交了一条SQL语句(Select-From-Where)，设计一个算法，判断是否允许该用户检索到相应的结果 ?

很简单，我们从Where 后面可以拿到访问的表，我们先查表是否能访问，然后再检查Select 后的数据项是否能访问

第二种：视图

  • 视图是安全性控制的重要手段
  • 通过视图可以限制用户对关系中某些数据项的存取，例如：
    • 视图1：Create EmpVl as select *from Employee
    • 视图2：Create EmpV2 as select Pname, D# from Employee
  • 通过视图可将数据访问对象与谓词结合起来，限制用户对关系中某些元组的存取，例如：
    • 视图1：Create EmpV3 as select * from Employee where P# = :Userld
    • 视图2：Create EmpV4 as select * from Employee where Head = :Userld
  • 用户定义视图后，视图便成为一新的数据对象，参与到存储矩阵与能力表中进行描述

4.5 利用 SQL 语言实现数据库自主安全性

4.5.1 SQL 语言的用户与权利
  • SQL语言包含了DDL，DML 和 DCL。数据库安全性控制是属于DCL范畴
  • 授权机制——自主安全性；视图的运用
  • 关系级别(普通用户)← 账户级别(程序员用户)← 超级用户(DBA)
  • (级别1)Select：读(读DB, Table, Record,Attribute,…)
  • (级别2)Modify：更新
    • Insert：插入(插入新元组，……)
    • Update：更新(更新元组中的某些值,….)
    • Delete：删除(删除元组，……)
  • (级别3)Create：创建(创建表空间、模式、表、索引、视图等)
    • Create：创建
    • Alter：更新
    • Drop：删除
  • 级别高的权利自动包含级别低的权利。如某人拥有更新的权利，它也自动拥有读的权利。在有些DBMS中，将级别3的权利称为账户级别的权利，而将级别1和2称为关系级别的权利。
4.5.2 SQL-DCL 的命令及应用

授权命令

GRANT {all PRIVILEGSE | privilege {, privilege...}}
ON [TABLE] tablename | viewname
TO {public | user-id {, user-id}}
[WITH GRANT OPTION];
  • user-id ，某一个用户账户，由DBA创建的合法账户
  • public，允许所有有效用户使用授予的权利
  • privilege 是下面的权利
    • SELECT | INSERT | UPDATE | DELETE | ALL PRIVILEDGES
  • WITH GRANT OPTION 选项是允许被授权者传播这些权利
4.5.3 SQL-DCL 控制安全性示例

假定高级领导为Emp0001，部门领导为Emp0021，员工管理员为Emp2001，收发员为Emp5001(均为Userld，也即员工的P#)

GRANT ALL Privileges ON Employee TO Emp2001;
GRANT SELECT ON EmpV2 TO Emp5001;
GRANT SELECT ON EmpV3 TO public;
GRANT SELECT ON EmpV4 TO Emp0021;
  • 授予视图访问的权利，并不意味着授予基本表访问的权利(两个级别：基本关系级别 和 视图级别)
  • 授权者授予的权利必须是授权者已经拥有的权利

收回授权命令

REVOKE {all privilEges | pri {, pri}} ON tablename | viewname
FROM {public | user {, user...}};

示例

REVOKE SELECT ON employee FROM UserB;

4.6 安全性控制的其他方面

4.6.1 自主安全性的授权过程及其问题

授权过程

  • 第一步：DBA创建DB，并为每一个用户创建一个账户
    • 假定建立了五个用户：UserA，UserB，UserC，UserD，UserE
  • 第二步：DBA授予某用户账户级别的权利
    • 假定授予UserA
  • 第三步：具有账户级别的用户可以创建基本表或视图,他也自动成为该表或该视图的属主账户，拥有该表或该视图的所有访问权利
    • 假定UserA创建了Employee,则UserA就是Employee表的属主账户
  • 第四步：拥有属主账户的用户可以将其中的一部分权利授予另外的用户，该用户也可将权利进一步授权给其他的用户…
    • 假定UserA将读权限授予UserB，而userB又将其拥有的权限授予UserC如此将权利不断传递下去。
GRANT SELECT ON Employee TO UserB WITH GRANT OPTION;
GRANT SELECT ON Employee TO UserC WITH GRANT OPTION;

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

注意授权的传播范围

  • 传播范围包括两个方面：水平传播数量和垂直传播数量

    • 水平传播数量是授权者的再授权用户数目(树的广度)
    • 垂直传播数量是授权者传播给被授权者，再被传播给另一个被授权者,…传播的深度(树的深度)

  • 有些系统提供了传播范围控制，有些系统并没有提供，SQL标准中也并没有限制。

  • 当一个用户的权利被收回时，通过其传播给其他用户的权利也将被收回

  • 如果一个用户从多个用户处获得了授权，则当其中某一个用户收回授权时该用户可能仍保有权利。例如UserC从UserB和UserE处获得了授权，当UserB收回时，其还将保持UserE赋予其的权利。

  • 可以思考如何设计来解决上述问题呢？

4.6.2 强制安全性

强制安全性通过对数据对象进行安全性分级

  • 绝密(Top Secret)，机密(Secret)，可信(Confidantal)和无分类(Unclassified)
  • 同时对用户也进行上述的安全性分级
  • 从而强制实现不同级别用户访问不同级别数据的一种机制

访问规则

  • 用户S，不能读取数据对象O，除非Level(S) >= Level(O)
  • 用户S，不能写数据对象，除非Level(S) <= Level(O)

强制安全性的实现

DBMS 引入强制安全性机制，可以通过拓展关系模式来实现

  • 关系模式：R(A1: D1, A2: D2, …, An: Dn)
  • 对属性和元组引入安全性分级特性或称分类特性
    • R(A1: D1, C1, A2: D2, C2…, An: Dn, Cn, TC)
      其中 C1, C2, …., Cn 分别为属性D1, D2,…, Dn的安全分类特性；TC为元组的分类特性

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

  • 强制安全性机制使得关系形成为多级关系(不同级别用户所能看到的关系的子集)，也出现多重实例、多级关系完整性等许多新的问题或新的处理技巧，在使用中需注意仔细研究。
    • 根据安全性分级，T，S，C，U 来使得不同用户看到的关系的子集不同
  • 关于强制安全性机制的内容，可参看其他有关的文献，

五、嵌入式SQL

5.1 嵌入式SQL语言概述

5.1.1 交互式SQL语言的局限
  • 交互式SQL语言有很多优点
    • 记录集合操作
    • 非过程性操作：指出要做什么，而不需指出怎样做
    • 一条语句就可实现复杂查询的结果
  • 然而，交互式SQL本身也有很多局限.….
SELECT S\#, AVG(Score) FROM SC
WHERE S\# IN
(SELECT S\# FROM SC
 WHERE Score < 60
 GROUP BY S\# HAVING COUNT(*) > 2 )
GROUP BY S#;

从使用者角度

专业人员可熟练写出SQL语句，但大部分的普通用户没有这个能力

特别复杂的检索结果难以用一条交互式SQL语句完成，此时需要结合高级语言中经常出现的顺序、分支和循环结构来帮助处理

例如：依据不同条件执行不同的检索操作等

IF some-condition THEN 
	SQL-QUERY1
ELSE
	SQL-QUERY2
END IF

再如：循环地进行检索操作

DO WHILE some-condition
	SQL-QUERY
END DO

再如：在SQL语句检索结果之上再进行处理

SQL-QUERY1
FOR Every-Record-By-SQL-Query1 DO
	PROCESS THE RECORD
NEXT

SQL-QUERY2
IF Record-By-SQL-Query2 Satisfy some-condition Then
	Process the Record (condition true)
ELSE
	Process the Record (condition false)
END IF
5.1.2 嵌入式SQL语言
  • 因此，高级语言+SQL语言
    • 既继承高级语言的过程控制性
    • 又结合SQL语言的复杂结果集操作的非过程性口 同时又为数据库操作者提供安全可靠的操作方式:通过应用程序进行操作
  • 嵌入式SQL语言
  • 将SQL语言嵌入到某一种高级语言中使用
  • 这种高级语言，如C/C++, Java, PowerBuilder等，又称宿主语言(Host Language)
  • 嵌入在宿主语言中的SQL与前面介绍的交互式SQL有一些不同的操作方式
5.1.3 嵌入式SQL语言和交互式SQL语言的对比

示例：交互式SQL语言

SELECT Sname, Sage FROM Student WHERE Sname='张三';

示例：嵌入式SQL语言

以宿主语言C语言为例

exec sql select Sname, Sage into :vSname, :vSage from Student
where Sname='张三';

典型特点

  • exec sql 引导SQL语句：提供给C编译器，以便对SQL语句预编译成C编译器可识别的语句
  • 增加一 into 子句：该子句用于指出接收SQL语句检索结果的程序变量
  • 由冒号引导的程序变量，如：‘:vSname’, ‘:vSage’
5.1.4 高级语言中使用嵌入式SQL语言需要解决的问题

高级语言(语句)->嵌入式SQL语句->DBMS<->DB

5.2 变量声明与数据库连接

即：

  • 问题1：如何与数据库连接和断开连接
  • 问题2：如何将宿主程序的变量传递给SQL 语句
5.2.1 变量的声明与使用

在嵌入式SQL语句中可以出现宿主语言语句所使用的变量：

exec sql select Sname, Sage into :vSname, :vSage from
Student where Sname= :specName;

这些变量需要特殊的声明：

exec sql begin declare section;
	char vSname[10], specName[10] = "张三";
	int vSage;
exec sql end declare section;

变量声明和赋值中，要注意:

  • 宿主程序的字符串变量长度应比字符型字段的长度多1个。因宿主程序的字符串尾部多一个终止符为 ‘\0’，而程序中用双引号来描述。
  • 宿主程序变量类型与数据库字段类型之间有些是有差异的，有些DBMS可支持自动转换，有些不能。

声明的变量，可以在宿主程序中赋值，然后传递给SQL语句的where等子句中，以使SQL语句能够按照指定的要求(可变化的)进行检索。

exec sql begin declare section;
	char vSname[10], specName[10]="张三";
	int vSage;
exec sql end declare section;
// 用户可在此处基于键盘输入给specName赋值
exec sql select Sname, Sage into :vSanme, :vSage from
Student where Sname = :specName

比较相应的交互式SQL语句：

SELECT Sname, Sage FROM Student WHERE Sname ='张三'

嵌入式比交互式SQL语句灵活了一些：只需改一下变量值，SQL语句便可反复使用，以检索出不同结果。

5.2.2 程序与数据库的连接与断开

在嵌入式SQL程序执行之前，首先要与数据库进行连接，不同DBMS，具体连接语句的语法略有差异

SQL标准中建议的连接语法为：

exec sql connect to target-server as connect-name user user-name;
// 或者
exec sql connect to default;

Oracle 中的数据库连接：

exec sql connect :user_name identified by :user_pwd;

DB2 UDB中的数据库连接：

exec sql connect to mydb user :user_name using :user_pwd;

在嵌入式SQL程序执行之后，需要与数据库断开连接

SQL标准中建议的断开连接的语法为：

exec sql disconnect connect-name;
// 或
exec sql disconnect current;

Oracle 中断开连接：

exec sql commit release;
// 或
exec sql rollback release;

DB2 UDB中断开连接：

exec sql connect reset;
exec sql disconnect current;
5.2.3 SQL执行的提交与撤销

SQL 语句在执行过程中，必须有提交和撤销语句才能确认其操作结果。

SQL 执行的提交：

exec sql commit work;

SQL 执行的撤销：

exec sql rollback work;

为此，很多DBMS都设计了捆绑提交/撤消与断开连接在一起的语句，以保证在断开连接之前使用户确认提交或撤消先前的工作，例如Oracle中：

exec sql commit release;
或
exec sql rollback release;
5.2.4 事务的概念与特性

事务：(从应用程序员角度)是一个存取或改变数据库内容的程序的一次执行，或者说一条或多条SQL语句的一次执行被看作一个事务。

  • 事务一般是由应用程序员提出，因此有开始和结束，结束前需要提交或撤消。
Begin Transaction
	exec sql...
	...
	exec sql
	exec sql commit work | exec sql rollback work
End Transaction
  • 在嵌入式SQL程序中，任何一条数据库操纵语句(如exec sql select等)都会引发一个新事务的开始，只要该程序当前没有正在处理的事务。而事务的结束是需要应用程序员通过 commit 或 rollback 确认的。因此 Begin Transaction 和 End Transaction 两行语句是不需要的。

换个角度：

事务：(从微观角度，或者从DBMS角度)是数据库管理系统提供的控制数据操作的一种手段，通过这一手段，应用程序员将一系列的数据库操作组合在一起作为一个整体进行操作和控制，以便数据库管理系统能够提供一致性状态转换的保证。

示例：“银行转帐”事务T：从帐户A过户5000RMB到帐户B上

read(A);
A := A - 5000;
write(A);
read(B);
B := B + 5000;
write(B);
  • 注：read(X)是从数据库传送数据项X到事务的工作区中；write(X)是从事务的工作区中将数据项X写回数据库。

事务的特性：ACID

  • 原子性Atomicity：DBMS能够保证事务的一组更新操作是原子不可分的，即对DB而言，要么全做。要么全不做
  • 一致性Consistency：DBMS保证事务的操作状态是正确的，符合一致性的操作规则，它是进一步由隔离性保证的
  • 隔离性Isolation：DBMS保证并发执行的多个事务之间互相不受影响。例如两个事务T1，T2，即使并发执行，也相当于先执行了T1，再执行T2或者先执行T2，再执行T1
  • 持久性Durability：DBMS保证已提交事务的影响是持久的，被撤销事务的影响是可恢复的。
    • 换句话说：具有ACID特性的若干数据库基本操作的组合体被称为事务
5.2.5 示例
#include <stdio.h>
#include "prompt.h"
exec sql include sqlca;         // SQLCA: SQL Communication Area
char cid_prompt[] = "Please enter customer id: ";
int main() {
    /****The Declare Section****/
    exec sql begin declare section;
    char cust_id[5], cust_name[14];
    float cust_discnt;
    char user_name[20], user_pwd[20];
    exec sql end declare section;
    /***************************/

    /****错误捕获语句****/
    exec sql whenever sqlerror goto report_error;
    exec sql whenever not found goto notfound;
    strcpy(user_name, "poneilsql");
    strcpy(user_pwd, "XXXX");

    /****sql connect****/
    exec sql connect :user_name identified by :user_pwd;
    /*******************/

    /****SQL commit work****/
    while((prompt(cid_prompt, 1, cust_id)) >= 0) {
        exec sql select cname, discnt
            into :cust_name, :cust_discnt
            from customers where cid =: cust_id;
        exec sql commit work;               // SQL commit work
        printf("Customer's name is %s and discount is %5.1f\n", cust_name, cust_discnt);
        continue;
        notfound: printf("Can't find customer %s, continuing\n", cust_id);
    }
    /*******************/

    exec sql commit release;
    return 0;

    /****SQL Rollback Work and Disconnect****/
    report_error:
        print_dberror();
        exec sql rollback release;
    return 1;
    /*******************/
}

5.3 数据集与游标

即：

  • 问题3：SQL语句如何执行
  • 问题4：如何将SQL检索到的结果传递回宿主程序进行处理
  • 问题5：静态SQL，SQL语句中的常量更换为变量
5.3.1 如何读取单行数据和多行数据

单行结果处理与多行结果处理的差异（Into子句与游标(Cursor)）

检索单行结果，可将结果直接传送到宿主程序的变量中

EXEC SQL SELECT [ALL | DISTINCT] expression [, expression...]
	INTO host-variable, [host-variable, ...]
	FROM tableref [corr_name], [host-variable, ...]
	WHERE search_condition;

示例：

exec sql select Sname, Sage into :vSname, :vSage from Student where Sname = :specName;

检索多行结果，则需使用游标(Cursor)

  • 游标是指向某检索记录集的指针
  • 通过这个指针的移动，每次读一行，处理一行，再读一行……，直至处理完毕
  • 读一行操作是通过Fetch…into语句实现的：每一次Fetch，都是先向下移动指针，然后再读取
  • 记录集有结束标识EOF，用来标记后面已没有记录了

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

5.3.2 游标的使用和概览

游标（Cursor）的使用

游标(Cursor)的使用需要先定义、再打开(执行)、接着一条接一条处理，最后再关闭

exec sql declare cur_student cursor for
select Sno, Sname, Sclass from Student where Sclass='035101';

exec sql open cur_student;

exec sql fetch cur_student into :vSno, :vSname, :vSclass;
......

exec sql close cur_student;

游标可以定义一次，多次打开(多次执行)，多次关闭

5.3.3 示例

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

#define TRUE 1
#include <stdio.h>
#include "prompt.h"
exec sql include sqlca;         // SQLCA: SQL Communication Area
exec sql begin declare section;
char cust_id[5], agent_name[14];
double dollar_sum;
exec sql end declare section;

int main() {
    char cid_prompt[] = "Please enter customer ID:";
    exec sql declare agent_dollars cursor for select aid, sum(dollars)
        from orders where cid = :cust_id group by aid;

    exec sql whenever sqlerror goto report_error;

    exec sql connect to testdb;

    exec sql whenever not found goto finish;

    while((prompt(cid_prompt, 1, cust_id, 4)) >= 0) {
        exec sql open agent_dollars;
        while(TRUE){
            exec sql fetch agent_dollars into :agent_id, :dollar_sum;
            printf("%s %11.2f\n", agent_id, dollar_sum);
        }
        finish:
            exec sql close agent_dollars;
            exec sql commit work;
    }

    exec sql disconnect current;
    return 0;
    report_error:
        print_dberror();
        exec sql rollback;
        exec sql disconnect current;
        return 1;
}
5.3.4 游标的使用方法

Cursor 的 定义：declare cursor

EXEC SQL DECLARE cursor_name CURSOR FOR
Subquery
[ORDER BY result_column [ASC | DESC]][, result_column ...]
[FOR [READ ONLY | UPDATE [OF columnname [, columnname...]]]];

示例

exec sql declare cur_student cursor for
select Sno, Sname, Sclass from Student where Sclass=:vClass
order by Sno
for read only;

Cursor 的打开和关闭：open cursor / close cursor

EXEC SQL OPEN cursor_name;
EXEC SQL CLOSE cursor_name;

Cursor 的数据读取：Fetch

EXEC SQL FETCH cursor_name
	INTO host-variable, [host-variable, ...];

示例

exec sql declare cur_student cursor for
select Sno, Sname, Sclass from Student where Sclass =: vClass
order by Sno for read only;

exec sql open cur_student;
...
exec sql fetch cur_student into :vSno, :vSname, :vSage
...
exec sql close cur_student;

5.4 可滚动游标及数据库的增删改

5.4.1 可滚动游标的概念

ODBC支持的可滚动Cursor

  • 标准的游标始终是自开始向结束方向移动的，每fetch一次，向结束方向移动一次；一条记录只能被访问一次；再次访问该记录只能关闭游标后重新打开

    exec sql declare cur_student cursor for
	select Sno, Sname, Sclass from Student where Sclass =: vClass
	order by Sno for read only;
exec sql open cur_student;
...
exec sql fetch cur_student
into :vSno, :vSname, :vSage;
...
exec sql close cur_student;
    • ODBC(DataBase Connectivity)是一种跨DBMS的DB操作平台，它在应用程序与的DBMS之间提供了一种通用接口
    • 许多实际的DMS并不支持可滚动游标，但通过ODBC可以使用该功能
5.4.2 可滚动游标的定义和使用

可滚动游标是可使游标指针在记录集之间灵活移动、使每条记录可以反复被访问的一种游标。

EXEC SQL DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR
	[WITH HOLD] FOR Subquery
	[ORDER BY result_column [ASC | DESC][, result_column ...]]
	[FOR READ ONLY | FOR UPDATE OF columnname] [, columnname]...];

EXEC SQL FETCH
	[NEXT | PRIOR | FIRST | LAST
    | [ABSOLUTE | RELATIVE] value_spec]
    FROM cursor_name INTO host-variable [, host-variable ...];
  • NEXT向结束方向移动一条；PRIOR向开始方向移动一条；FIRST回到第一条；LAST移动到最后一条；ABSOLUT value _spec定向检索指定位置的行，value spec由1至当前记录集最大值；RELATIVE value_spec相对当前记录向前或向后移动，value_spec为正数向结束方向移动，为负数向开始方向移动

可滚动游标移动时需判断是否到结束位置，或到起始位置

  • 可通过判断是否到EOF位置(最后一条记录的后面)，或BOF位置(起始记录的前面)
  • 如果不需区分，可通过whenever not found语句设置来检测
5.4.3 数据的删除与更新

数据库记录的删除

  • 一种是查找删除（与交互式DELETE 语句相同），是一种定位删除

    EXEC SQL DELETE FROM tablename [corr_name]
	WHERE search_condition | WHERE CURRENT OF cursor_name;

    示例：查找删除

    exec sql delete from customers c where c.city = 'Harbin' and
	not exists (select from orders o where o.cid = c.cid);

    示例：定位删除

    exec sql declare delcust cursor for
	select cid from customers c where c.city = 'harbin' and
	not exists (select * from orders o where o.cid = c.cid)
	for update of cid;
exec sql open delcust
While (TRUE){
	exec sql fetch delcust into :cust_id;
	exec sql delete from customers where current if delcust;
}

    数据库记录的更新

  • 一种是查找更新(与交互式Update语句相同)，一种是定位更新

    EXEC SQL UPDATE tablename [corr_name]
	SET columnname = expr [, columnname = expr ...]
	[WHERE search_condition] | WHERE CURRENT OF cursor_name;

    示例：查找更新

    exec sql update student s set sclass = '035102'
	where s.sclass = '034101'

    示例：定位更新

exec sql declare stud cursor for
	select * from student s where s.sclass = '034101'
	for update of sclaas;
exec sql open stud
While(TRUE){
	exec sql fetch stud into :vSno, :vSname, :vSclass
	exec sql update student set sclass = '035102' where current of stud;
}

数据库记录的插入

  • 只有一种类型的插入语句

    EXEC SQL INSERT INTO tablename [(columnname[
	columnname, ...    
])] [VALUES (expr [, expr [, expr , ...]]) | subquery];

    示例：插入语句

    exec sql insert into student (sno, sname, sclass)
	values('03510128', '张三', '035101');

    示例：插入语句

    exec sql insert into masterstudent (sno, sname, sclass)
	select sno, sname, sclass from student;
5.4.4 示例

示例：宿主语言与SQL结合的过程性控制求数据库中某一列位于中值的那一行

#include <stdio.h>
#include "prompt.h"
exec sql include sqlca;
char custprompt[] = "Please enter a customer ID: ";

int main() {
    exec sql begin declare section;
    	char cid[5], use_name[20], user_pwd[10];
    	double dollars; int ocount;
    exec sql declare dollars_cursor cursor for
        select dollars from orders where cid = :cid and dollars is not null
            order by dollars;
   	exec sql whenever sqlerror goto report_error;
    strcpy(user_name, "poneilsql");
    strcpy(user_pwd, "xxx");
    exec sql connect :user_name identified by :user_pwd;
    While (prompt(custprompt, 1, cid, 4) >= 0) {
        exec sql select count(dollars) into :ocount from orders where cid = :cid;
    	if (ocount == 0) {
            printf("No record reviewed for cid value %s\n", cid);
            continue;
        }
        exec sql open dollars_cursor;
        for (int i = 0; i < (ocount + 1) / 2; ++ i) {
            exec sql fetch dollars_cursor into :dollars;
        }
        exec sql close dollars_cursor;
        exec sql commit work;
        printf("Median dollar amount = %f\n", dollars);
    }
}

5.5 状态捕获及错误处理机制

问题6: 宿主程序如何知道SQL语句的执行状态，是否发生错误

5.5.1 基本机制

  • 状态，是嵌入式SQL语句的执行状态，尤其指一些出错状态；有时程序需要知道这些状态并对这些状态进行处理

  • 嵌入式 SQL程序中，状态捕获及处理有三部分构成

    • 设置SQL通信区：一般在嵌入式SQL程序的开始处便设置

      exec sql inlcude sqlca;

    • 设置状态捕获语句：在嵌入式SQL程序的任何位置都可设置；可多次设置；但有作用域

      exec sql whenever sqlerror goto report_error;

    • 状态处理语句：某一段程序以对应SQL操作的某种状态

      report_error: exec sql rollback;

SQL通信区：SQLCA

  • SQLCA是一个已被声明过的具C语言的结构形式的内存信息区，其中的成员变量用来记录SQL语句执行的状态，便于宿主程序读取与处理口
  • SQLCA是DBMS(执行SQL语句)与宿主程序之间交流的桥梁之一
  • 具体使用可查手册

状态捕获语句

exec sql whenever condition action;
  • Whenever 语句的作用是设置一个“条件陷阱”，该条语句会对其后面的所有由 Exec SQL 语句所引起的对数据库系统的调用自动检查它是否满足条件(由condition指出).
    • SQLERROR：检测是否有SQL语句出错。其具体意义依赖于特定的DBMS
    • NOT FOUND：执行某一SQL语句后，没有相应的结果记录出现
    • SQLWARNING：不是错误，但应引起注意的条件
  • 如果满足condition，则要采取一些动作(由action指出)
    • CONTINUE：忽略条件或错误，继续执行
    • GOTO 标号：转移到标号所指示的语句，去进行相应的处理
    • STOP：终止程序运行、撤消当前的工作、断开数据库的连接
    • DO函数或CALL函数：调用宿主程序的函数进行处理，函数返回后从引发该condition的Exec SQL语句之后的语句继续进行

状态捕获语句Whenever的作用范围是其后的所有 Exec SQL 语句，一直到程序中出现另一条相同条件的Whenever语句为止，后面的将覆盖前面的。

int main(){
    exec sql whenever sqlerror stop;
    ......
    goto s1
    ......
   	exec sql whenever sqlerror continue;
    s1: exec sql update agents set percent = percent + 1;
}
  • S1标号指示的语句受第二个Whenever语句约束
  • 注意：作用域是语句在程序中的位置，而不是控制流程（因为是预编译程序处理条件陷阱）

接下来看一段会无线循环的代码

int main(){
    exec sql whenever sqlerror goto handle_error;
    exec sql create table customers(cid char(4) not null, cname varchar(13), ... ...);
    ... ...
    handle_error:
    	exec sql drop customers;
        exec sql disconnect;
        fprintf(stderr, "could not create customers table\n");
        return -1;
}
  • handle_error：内 的 exec sql drop customers; 如果出错，又会回到 handle_error，有着无限循环的风险

改进：

int main(){
    exec sql whenever sqlerror goto handle_error;
    exec sql create table customers(cid char(4) not null, cname varchar(13), ... ...);
    ... ...
    handle_error:
    	exec sql whenever sqlerror continue;
    	exec sql drop customers;
        exec sql disconnect;
        fprintf(stderr, "could not create customers table\n");
        return -1;
}
  • exec sql whenever sqlerror continue; 保证即使下面出错了，也会continue
5.5.2 状态信息

典型DBMS系统记录状态信息的三种方法

状态记录

  • sqlcode：典型DBMS都提供一个sqlcode变量来记录其执行sql语句的状态，但不同DBMS定义的sqlcode值所代表的状态意义可能是不同的，需要查阅相关的DBMS资料来获取其含义

    • sqlcode=0，successful call
    • sqlcode<0，error, e.g., from connect, database does not exist, -16
    • sqlcode>0，warning, e.g., no rows retrieved from fetch

  • sqlca.sqlcode：支持SQLCA的产品一般要在SQLCA中填写sqlcode来记录上述信息；除此而外，sqlca还有其他状态信息的记录

  • sqlstate：有些DBMS提供的记录状态信息的变量是sqlstate或sqlca.sqlstate

  • 当我们不需明确知道错误类型，而只需知道发生错误与否，则我们只要使用前述的状态捕获语句即可，而无需关心状态记录变量**(隐式状态处理)**

  • 但我们程序中如要自行处理不同状态信息时，要知道以上信息，但也需知道正确的操作方法**(显式状态处理)**

5.5.3 程序处理

程序自身进行错误信息的处理

不正确的显式状态处理示例

exec sql begin declare section;
	char SQLSTATE[6];
exec sql end declare section;
exec sql whenever sqlerror goto handle_error;
......
exec sql create table custs
    (cid char(4) not null, cname varchar(13), ... ...);
if (strcmp(SQLSTATE, "82100") == 0)
    <处理82100错误的程序>

上述的if 语句是不能被执行的，因为create table发生错误时会跳到sqlerror。

正确的显式状态处理示例

exec sql begin declare section;
	char SQLSTATE[6];
exec sql end declare section;
exec sql whenever sqlerror goto handle_error;
......
exec sql whenever sqlerror continue;
exec sql create table custs
    (cid char(4) not null, cname varchar(13), ... ...);
if (strcmp(SQLSTATE, "82100") == 0)
    <处理82100错误的程序>

上述的if 语句是不能被执行的，因为create table发生错误时是继续向下执行的。

六、动态SQL

问题7：动态SQL，依据条件动态构造SQL语句，但欲访问的表名和字段名对编程者是已知的

6.1 动态SQL的概念和作用

动态SQL是相对于静态SQL而言的

静态SQL示例

SpecName = '张三';
exec sql select Sno, Sname, Sclass into :vSno, :vSname, :vSclass from
    Student where Sname = :SpecName;
或
exec sql declare cur_student cursor for
    	select Sno, Sname, Sclass from Student where Sclass =:vClass
            order by Sno for read only;	// 定义
exec sql open cur_student;	// 执行

静态SQL特点：SQL语句在程序中已经按要求写好，只需要把一些参数通过变量(高级语言程序语句中不带冒号)传送给嵌入式SQL语句即可(嵌入式SQL语句中带冒号)

动态SQL示例

#include<stdio.h>
exec sql include sqlca;

exec sql begin declare section;
char user_name[] = "Scott";
char user_pwd[] = "tiger";
char sqltext[] = "delete from customer where cid = \'c006\'";
exec sql end declare section;

int main() {
    exec sql whenever sqlerror goto report_error;
    exec sql connect :user_name identified by :uer_pwd;
    exec sql execute immediate :sqltext;
    exec sql commit release;
    return 0;
    report_error: print_dberror();
    exec sql rollback release;
    return 1;
}

动态SQL特点：SQL 语句可以在程序中动态构造，形成一个字符串，如上例sqltext，然后再交给DBMS执行，交给DBMS执行时仍旧可以传递变量。

动态构造SQL语句是应用程序员必须掌握的重要手段。

比如，编写由用户确定检索条件的应用程序

那么，如何设计用户的操作界面呢？

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

6.2 SQL语句的动态构造

6.2.1 示例1
  • 已知关系：Customers(Cid, Cname, City, discnt)
  • 从 Customers 表中删除满足条件的行

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

假设：用户界面上的输入存在下面的变量中

  • char Vcname[];
  • char Vcity[];
  • double range_from, range_to;
  • int Cname_chose, City_chose, Discnt_chose;

请按用户输入构造相应的SQL语句

逻辑还是很简单的

#include <stdio.h>
#include "prompt.h"
char Vcname[];
char Vcity[];
double range_from, range_to;
int Cname_chose = 0, City_chose = 0, Discnt_chose = 0;
int sql_sign = 0;
char continue_sign[];

exec sql include sqlca;
exec sql begin declare section;
	char user_name[20], user_pwd[20];
	char sqltext[] = "delete from customers where ";
exec sql end declare section;

int main() {
    exec sql whenever sqlerror goto report_error;
    strcpy(user_name, "poneilsql");
    strcpy(user_pwd, "XXXX");
    exec sql connect :user_name identified
        by :user_pwd;
   	
    while (1) {
        memset(Vcname, '\0', 20);
        memset(Vcity, '\0', 20);
        if (GetCname(Vcname)) {
			Cname_chose = 1;
        }
        if (GetCity(Vcity)) {
			City_chose = 1;
        }
        if (GetDiscntRange(&range_from, &range_to)) {
            Discnt_chose = 1;
        }
        
        if (Cname_chose) {
            sql_sign = 1;
            strcat(sqltext, "Cname = \'");
            strcat(sqltext, Vcname);
            strcat(sqltext, "\'");
        }
        
        if (City_chose) {
            sql_sign = 1;
            if (Cname_chose) {
                strcat(sqltext, "and City = \'");
            } else {
                strcat(sqltext, "City = \'");
            }
            strcat(sqltext, Vcity);
            strcat(sqltext, "\'");
        }
        
        if (Discnt_chose) {
            sql_sign = 1;
            if (Cname_chose || City_chose) {
                strcat(sqltext, "and discnt > ");
            } else {
                strcat(sqltext, "discnt > ");
            }
            strcat(sqltext, dtoa(range_from));
            strcat(sqltext, "and discnt < ");
            strcat(sqltext, dtoa(range_to));
            strcat(sqltext, ")");
        }
        
        if (sql_sign) {
            exec sql execute immediate = :sqltext;
            exec sql commit work;
        }
        scanf("continue (y/n) % 1s", continue_sign);
        if (continue_sign == "n") {
            exec sql commit release;
            return 0;
        }
    }
    
    report_error:
    	print_dberror();
    	exec sql rollback release;
    	return 1;
}
6.2.2 示例2

示例：编写程序，依据用户输入条件构造SQL语句并执行

界面要素以及背后的变量简介

SQL字符串的构造：

  • trim：去除字符串两端空格
  • len：字符串长度

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

动态SQL语句的执行结果

示例结果1

示例结果2

6.3 动态SQL语句的执行方式

6.3.1 概述

动态SQL的两种执行方式

如SQL语句已经被构造在host-variable字符串变量中，则：

  • **立即执行语句：**运行时编译时运行
    • EXEC SQL EXECUTE IMMEDIATE :host-variable;
  • **Prepare-Execute-Using语句： ** PREPARE语句先编译，编译后的SQL语句允许动态参数，EXECUTE语句执行，用USING语句将动态参数值传给编译好的SQL语句
    • EXEC SQL PREPARE sql_temp FROM :host-variable;
    • … …
    • EXEC SQL EXECUTE sql_temp USING :cond-variable;
6.3.2 示例

Prepare-Execute-Using 的例子

#include <stdio.h>
#include "prompt.h"
exec sql include sqlca;
exec sql begin declare section;
char cust_id[5], sqltext[256], user_name[20], user_pwd[10];
exec sql end declare section;
char cid_prompt[] = "Name cutsomer cid to be deleted: ";
int main() {
    strcpy(sqltext, "delete from customers where cid =: dcid");
    ... ...
    while(prompt(cid_prompt, 1, cust_id, 4) >= 0) {
        exec sql whenever not found goto no_such_cid;
        exec sql prepare delcust from :sqltext;			// prepare statement
        exec sql execute delcust using :cust_id;		// using clause ... replaces ":n" above
        exec sql commit work;	continue;
        not_such_id: printf("No cust %s in table\n", cust_id);
        continue;
    }
}

6.4 数据字典与SQLDA

问题8：动态SQL，依据条件动态构造SQL语句，但欲访问的表名和字段名对编程者是未知的。

6.4.1 数据字典

数据字典(Data dictionary)，又称为系统目录(system catalogs)

  • 是系统维护的一些表或视图的集合，这些表或视图存储了数据库中各类对象的定义信息，这些对象包括用Create语句定义的表、列、索引、视图、权限、约束等，这些信息又称数据库的元数据——关于数据的数据
  • 不同DBMS术语不一样：数据字典(Data Dictionary(Oracle))、目录表(DB2 UDB)、系统目录(INFORMIX)、系统视图(X/Open)
  • 不同DBMS中系统目录存储方式可能是不同的，但会有一些信息对DBA公开。这些公开的信息，DBA可以使用一些特殊的SQL命令来检索。
6.4.2 数据字典的内容构成

数据字典的内容构成

  • 数据字典通常存储的是数据库和表的元数据，即模式本身的信息：
    • 与关系相关的信息
      • 关系名字
      • 每一个关系的属性名及其类型
      • 视图的名字及其定义
      • 完整性约束
    • 用户与账户信息，包括密码
    • 统计与描述性数据：如每个关系中元组的数目
    • 物理文件组织信息：
      • 关系是如何存储的（顺序/无序/散列）
      • 关系的物理位置
    • 索引相关的信息
6.4.3 数据字典的表结构或视图

数据字典的结构

  • 也是存储在磁盘上的关系

  • 专为内存高效访问设计的特定的数据结构

  • 可能的字典数据结构

    • Relation_metadata = (relation_name, number_of_attributes, storage_organization, location)
    • Attribute_metadata = (attribute_name, relation_name, domain_type, position, length)
    • User_metadata = (user_name, encrypted_password, group)
    • Index_metadata = (index_name, relation_name, index_type, index_attributes)
    • View_metadata = (view_name, definition)
6.4.4 X/Open 标准的系统目录

X/Open标准中有一个目录表Info_Schem.Tables，该表中的一行是一个已经定义的表的有关信息

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

  • 模式的含义是指某一用户所设计和使用的表、索引及其他与数据库有关的对象的集合，因此表的完整名应是模式名.表名。这样做可允许不同用户使用相同的表名，而不混淆。

  • 一般而言，一个用户有一个模式。"可以使用Create Schema语句来创建模式(用法略参见相关文献)，在Create Table等语句可以使用所定义的模式名称。

  • 可以用SQL语句来访问这个表中的信息，比如了解已经定义了哪些表，可如下进行：

    Select Table_Name From Tables;

目录-模式-对象（如具体的表等）示例

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

6.4.5 Oracle 的数据字典
  • Oracle 数据字典由视图组成，分成三种不同形式，由不同的前缀标识
    • USER_：用户视图，用户所拥有的对象吗，在用户模式中
    • ALL_：扩展的用户视图，用户可访问的对象
    • DBA_：DBA视图（所有用户都可访问的DBA对象的子集）
  • Oracle数据字典中定义了三个视图USER_Tables，ALL_Tbales，和 DBA_Tables供DBA和用户使用数据字典中关于表的信息

同样，Oracle数据字典中也定义了三个视图USER_TAB_Columns，ALL_TAB_Columns(Accessible Columns)，和DBA_TAB_Columns供DBA和用户使用数据字典中关于表的列的信息

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

  • 可以使用SQL语句来访问这些表的信息：

    Select Column_Name From ALL_TAB_Columns

    Where Table_Name = ‘STUDENT’

  • Oracle 数据字典中还定义了其他视图

    • TABLE_PRIVILEDGE(或ALL_TAB_GRANTS)
    • COLUMN_PRIVILEDGE(或ALL_COL_GRANTS)
      • 可访问表的权限，列的权限
    • CONSTRAINT_DEFS(或ALL_CONSTRAINTS)
      • 可访问表的各种约束
    • 还有其他视图……

  • 可以使用下述命令获取Oracle定义的所有视图信息

    Select view_name from all_views where owners = ‘SYS’ and view_name like ‘ALL_%’ or view_name like ‘USER_%’;

  • 如果用户使用Oracle，可使用其提供的SQL*PLUS进行交互式访问

6.4.6 更为复杂的动态SQL

示例：针对一组表，编写由用户确定检索表和检索条件的查询程序

只知道表的集合，但具体检索哪个Table，以及检索该Table中的哪些列都是可构造的，检索条件也是用户临时设置的，可构造的。

6.4.7 SQLDA

构造复杂的动态SQL需要了解数据字典及SQLDA，以获知关系模式信息。

SOLDA：SQL Descriptor Area，SQL描述符区域。

  • SQLDA是一个内存数据结构，内可装载关系模式的定义信息，如列的数目，每一列的名字和类型等等
  • 通过读取SQLDA信息可以进行更为复杂的动态SQL的处理
  • 不同DBMS提供的SQLDA格式并不是一致的。

6.5 ODBC简介

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

6.5.1 什么是 ODBC？

ODBC：Open DataBase Connection

ODBC是一种标准——不同语言的应用程序与不同数据库服务器之间通讯的标准。

  • 一组API(应用程序接口)，支持应用程序与数据库服务器的交互
  • 应用程序通过调用ODBC API，实现
    • 与数据服务器的连接
    • 向数据库服务器发送SQL命令
    • 一条一条的提取数据库检索结果中的元组传递给应用程序的变量
  • 具体的DBMS提供一套驱动程序，即Driver库函数，供ODBC调用，以便实现数据库与应用程序的连接。
  • ODBC可以配合很多高级语言来使用，如C,C++，C#，Visual Basic，Power-Builder等等;
6.5.2 应用程序如何通过ODBC连接一个数据库服务器
  • ODBC应用前，需要确认具体DBMS Driver被安装到ODBC环境中
  • 当应用程序调用ODBC API时，ODBC API会调用具体DBMS Driver库函数，DBMS Driver库函数则与数据库服务器通讯，执行相应的请求动作并返回检索结果
  • ODBC应用程序首先要分配一个SQL环境，再产生一个数据库连接句柄
  • 应用程序使用SQLConnect()，打开一个数据库连接，SQLConnect()的具体参数：
    • connection handle，连接句柄
    • the server，要连接的数据库服务器
    • the user identifier，用户
    • password，密码
    • SQL_NTS 类型说明前面的参数是空终止的字符串

示例

int ODBCexample() {
    RETCODE error;	// 返回状态码
    HENV env;			// 环境变量
    HDBC conn;				// 连接句柄
    SQLAllocEnv(&env);
    SQLAllocConnect(env, &conn);
    // 分配数据库连接环境
    SQLConnect(conn, "aura.bell-labs.com", SQL_NTS, "avi", SQL_NTS, avipassword, SQL_NTS);
    // 打开一个数据库连接
    
    { ... Do actual work ... }
    // 与数据库通讯
    
    SQLDisconnect(conn);
    SQLFreeConnect(conn);
    SQLFreeEnv(env);
	// 断开连接与释放环境
}
6.5.3 应用程序如何通过ODBC与数据库服务器进行通讯
  • 应用程序使用**SQLExecDirect()**向数据库发送SQL命令
  • 使用**SQLFetch()**获取产生的结果元组
  • 使用**SQLBindCol()**绑定C语言变量与结果中的属性
    • 当获取一个元组时，属性值会自动地传送到相应的C语言变量中
    • **SQLBindCol()**的参数：
      • ODBC定义的stmt变量，查询结果中的属性位置
      • SQL到C的类型检查，变量的地址
      • 对于类似字符数组一样的可变长度类型，应给出
        • 变量的最大长度
        • 当获取到一个元组后，实际长度的存储位置
        • 注：当返回实际长度为负数，说明是一个空值。

示例

char branchname[80];
float balance;
int lenOut1, lenOut2;
HSTMT stmt;
SQLAllocStmt(conn, &stmt);
//分配一个与指定数据库连接的新的语句句柄
char *sqlquery = "select branch_name, sum(balance) from account group by branch_name";
error = SQLExecDirect(stmt, sqlquery, SQL_NTS);
//执行查询，stmt句柄指向结果集合
if (error == SQL_SUCCESS) {
    SQLBindCol(stmt, 1, SQL_C_CHAR, branchname, 80, &lenOut1);
    SQLBindCol(stmt, 2, SQL_C_FLOAT, &balance, 0, &lenOut2);
// 绑定高级语言变量与stmt句柄中的属性
    while(SQLFetch(stmt) >= SQL_SUCCESS) {
// 提取一条记录，结果数据被存入高级语言变量中
        printf("%s $g\n", branchname, balance);
    }
}
SQLFreeStmt(stmt, SQL_DROP);
// 释放语句句柄
6.5.4 ODBC 的其他功能
  • 动态SQL语句的预编译-动态参数传递功能
  • 获取元数据特性
    • 发现数据库中的所有关系的特性
    • 发现每一个查询结果的列的名字和类型等:
  • 默认，每一条SQL语句都被作为一个独立的能够自动提交的事务来处理。
    • 应用程序可以关闭一个连接的自动提交特性
      • SQLSetConnectOption(conn, SQL_AUTOCOMMIT, 0);
    • 此时事务要显式地给出提交和撤销的命令
      • SQLTransact(conn, SQL_COMMIT) or
      • SQLTransact(conn, SQL_ROLLBACK);

6.6 JDBC

6.6.1 JDBC是什么

JDBC：Java DataBase Connection

JDBC 是一组Java版的应用程序接口API，提供了Java应用程序与数据库服务器连接和通讯能力

JDBC API

JDBC API 分成两个程序包：

  • Java.sql 核心API–J2SE(java2标准版)的一部分。使用java.sql.DriverManager类、java.sql.Driver和 java.sql.connection 接囗连接到数据库
  • Javax.sql可选扩展API --J2EE(ava2企业版)的一部分。包含了基于JNDI(Java Naming and Directory Interface, java命名和目录接口)的资源，以及管理连接池、分布式事务等，使用Datasource接口连接到数据库。
6.6.2 JDBC的功能

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

  • java.sql.DriverManager——处理驱动的调入并且对产生新数据库连接提供支持
  • Java.sql.Driver——通过驱动进行数据库访问，连接到数据库的应用程序必须具备该数据库的特定驱动。
  • java.sql.Connection——代表对特定数据库的连接。
  • Try {…} Catch {…}——异常捕获及其处理
  • java.sql.Statement——对特定的数据库执行SQL语句
  • java.sql.Preparedstatement——用于执行预编译的SQL语句
  • java.sql.CallableStatement——用于执行对数据库内过程的调用
  • java.sql.ResultSet——从当前执行的SQL语句中返回结果数据,
6.6.3 应用程序使用JDBC API访问数据库的过程

概念性的基本过程

打开一个连接：创建“Statement”对象，并设置查询语句；使用Statement对象执行查询，发送查询给数据库服务器和返回结果给应用程序；处理错误的例外机制

具体实施过程

  • 传递一个Driver给DriverManager，加载数据库驱动
    • Class.forName()
  • 通过URL得到一个Connection对象，建立数据库连接
    • DriverManager.getConnection(sDBUrl)
    • DriverManager.getConnection(sDBUrl, sDBUserlD, sDBPassword)
  • 接着创建一个Statement对象(PreparedStatement或CallableStatement)，用来查询或者修改数据库。
    • Statement stmt=con.createStatement()
  • 查询返回一个ResultSet
    • ResultSet rs=stmt.executeQuery(sSQL)

示例

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

如何更新/查询数据库？

完整的示例程序

6. 7 嵌入式语言-ODBC-JDBC 比较

嵌入式SQL的思维模式

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

ODBC的思维模式

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

JDBC的思维模式

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

三者：

基于语句——基于函数——基于对象

基于ODBC/JDBC 的数据库访问

DBMS对操作系统的文件读写进行了封装

基于 ODBC/JDBC 则以对象为单位来操纵数据

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传


http://www.kler.cn/a/460084.html

相关文章：

  • Echarts+vue电商平台数据可视化——webSocket改造项目
  • 免登录游客卡密发放系统PHP网站源码
  • 家政上门小程序如何创建?家政服务怎么能少了小程序帮手
  • 集线器，交换机，路由器，mac地址和ip地址知识记录总结
  • 安卓播放器TVbox或影视仓软件如何链接到xiaoya小雅超集？很详细的教程
  • Centos中常见的几个问题及其解决办法
  • 【Redis】万字整理 Redis 非关系型数据库的安装与操作
  • Android笔试面试题AI答之Android基础（4）
  • elementui的默认样式修改
  • 备战春招 | 数字IC FPGA笔试题
  • 【从零开始入门unity游戏开发之——C#篇32】C#其他不常用的泛型数据结构类、顺序存储和链式存储
  • Python 爬虫中的反爬策略及详细应对方法
  • 【LLM】Langflow 的简单使用
  • SQL实现新年倒计时功能
  • golang标准库SSH操作示例
  • PHP语言laravel框架中基于Redis的异步队列使用实践与原理
  • 【每日学点鸿蒙知识】中间产物版本号问题、Toast应用外不生效、Dialog键盘问题、WebView cookie、本地缓存
  • 如何判断服务器是否被网络攻击？
  • 【C++】九九乘法表编程题详解与多角度对比分析
  • 整合版canal ha搭建--基于1.1.4版本
  • CSS系列（39）-- Shapes详解
  • 服务器反应慢，秒杀设计
  • 实验七 函数2
  • Flutter-插件 scroll-to-index 实现 listView 滚动到指定索引位置
  • 第7章：基于EfficientNet v2网络实现的迁移学习图像分类任务：人体皮肤病识别
  • C语言自定义类型与文件操作