InformixDynamicServer11.50基础考试555认证准备

Informix Dynamic Server 11 50 基础考试基础考试 555 认证准备 认证准备 第第 5 部分部分 使用使用 SQL 开始之前 本教程将全面概述结构化查询语言 SQL 的 Informix Dynamic Server IDS 实现 关于本系列 这个免费的共包含 9 篇教程的 Informix Dynamic Server 11 50 基础考试 555 认证准备系列 的目的是为了帮助您准备 Informix Dynamic Server IDS 基础认证考试 555 这个认证将考察关于 IDS 11 50 管理的入门级知识 包括基础 SQL 如何安装 IDS 11 50 如何创建数据库和数据库对象 安全性 事务隔离 备份和恢复流程 以及数据复制技术 这些教程为考试的每部分打 下了坚实的基础 不过 您不能仅使用这些教程作为唯一的考试准备材料 回页首 关于本教程 本教程将向您介绍 SQL 并帮助您理解 IDS 如何使用 SQL 操作关系数据库中的 数据 回页首 目标 本教程旨在帮助您熟悉 如何对表执行查询 如何使用 SQL 操作数据 事务的概念 存储过程和用户定义例程的使用 回页首 前提条件 本教程面向初中级水平的数据库专业人员 您应该熟悉基本的数据库概念 回页首 系统需求 要完成本教程 您并不需要 IDS 副本 但如果有一个可以使用 那么无疑可以 充分发挥本教程的作用 如果还没有副本 可以下载免费试用版本 参见 参考 资料 SQL 理解 SQL SQL 是一种用于定义和操作数据库对象的语言 您可以使用 SQL 定义数据库表 在表中插入数据 更改表中的数据以及检索表中的数据 SQL 中最重要的一种 应用是 SELECT 语句 SELECT 语句允许您检索关系数据库中的数据 而 INSERT UPDATE MERGE 和 DELETE 则可用于检索数据库中的数据 与其他任 何语言类似 SQL 有既定的语法和一组语言元素 在本教程中 我们主要讨论 如何使用 SQL 检索和操作数据 本教程中的所有 SQL 示例都基于 stores demo 数据库 它随带在 IDS 产品中 stores demo 数据库包含一组描述虚拟业务的表 您可以轻松的创建和填充 stores demo 数据库 只需要使用 log 选项运行 dbaccessdemo 脚本 dbaccessdemo log 在运行 dbaccessdemo 脚本时 您需要设置 Informix 环 境变量 dbaccessdemo 脚本位于您 Informix 安装的 bin 目录中 数据操作语言 Data Manipulation Language DML 检索数据 使用 SELECT 语句从表中检索数据 SELECT 语句用于从表或视图中检索数据 这种操作也被称为查询 您需要具备 Connect 权限才能访问关系数据库 并且需要具备 Select 权限才能使用 SELECT 检索表或视图中的数据 采用最简单的形式 SELECT 语句可用于检索 表或视图中的所有数据 以下语句是使用 SELECT 语句检索 customer 表中的 所有数据的一个示例 SELECT FROM customer SELECT 语句也可以像上述语句那样简单地使用 但也可支持多个 SQL 语句和 关键字的复杂使用 IDS 支持许多不同的 SQL 语句 本教程将介绍其中最常用 的一些 SQL 语句 您可以使用 FIRST 关键字来限制查询结果的行数 以下代码示例展示了如何检 索 customer 表中的前 10 行 SELECT FIRST 10 FROM customer 要从表中检索具体的列 可以在 SELECT 关键字后指定使用逗号分隔的列名 以下示例展示了如何从 customer 表中检索 fname 和 lname 列 SELECT fname lname FROM customer 在 SELECT 语句中使用 DISTINCT 关键字可以清除查询结果中的重复行 以下 示例展示了如何清除 orders 表中的重复 customer 数据 SELECT DISTINCT customer num FROM orders 您可以在 SELECT 语句 语句中使用 AS 子句为 SELECT 列表中的表达式或列分 配一个有意义的名称 以下示例展示了如何使用 AS 子句计算 orders 表中的 订购日期与配送日期之间的处理时间 SELECT ship date order date AS process time FROM orders 您可以在 SELECT 语句中使用许多内置 SQL 函数 表 1 列出了 SELECT 语句 常用的一些内置函数 表表 1 1 常用常用 SQLSQL 函数函数 函数函数描述描述 聚合函数函数聚合函数函数 COUNT 查询结果中的行数 SUM 数值列求和 AVG 数值列的平均值 MAX 某列的最大值 MIN 某列的最小值 时间函数时间函数 DAY 表示日的整型值 MONTH 表示月的整型值 YEAR 表示年的整型值 WEEKDAY 表示一周中的某一天的整型值 0 表示周日 DATE 返回日期值 CURRENT 返回当前日期时间值 TODAY 返回系统日期 其他函数其他函数 TRUNC 返回某表达式截断后的值 HEX 返回十六进制值 ROUND 返回四舍五入值 LENGTH 返回字符串列的长度 以下 SELECT 语句示例使用 COUNT 函数返回 customer 表中的行数 SELECT COUNT FROM customer 过滤数据 使用 WHERE 子句和谓语过滤查询返回的数据 通过指定一个或多个选择标准或搜索条件 您可以使用 WHERE 子句选择表或视 图中的具体行 搜索条件包括一个或多个谓语 谓语指定与某行相关的或真或 假的判断 在构建搜索条件时考虑以下事项 算术运算仅针对数值数据类型 仅对具有可比性的数据类型执行比较 在引号中包含字符串值 指定数据库中真实存在的字符串值 我们来看一些如何使用 WHERE 子句的示例 首先 这个示例展示了如何从 customer 表中查找编号大于 120 的客户的名称 SELECT fname lname customer num FROM customer WHERE customer num 120 以下示例将从 ustomer 表中查找居住在 Denver 且客户编号大于 120 的客户 的名称 SELECT fname lname customer num FROM customer WHERE customer num 120 AND city Denver LIKE 关键字支持在引用的字符串中使用通配符 百分比符号 是一个代表 零个或多个字符串的通配符 以下示例展示了如何查找 customer 表中以字母 L 开头的所有客户名称 SELECT fname FROM customer WHERE fname LIKE L 您可以在 WHERE 子句中使用许多关键字与运算符来定义搜索条件 表 2 提供 了最常用的关键字和运算符的列表 表表 2 2 WHEREWHERE 子句中常用的关键字和运算符列表子句中常用的关键字和运算符列表 关键字关键字 运算符运算符描述描述 关键字关键字 BETWEEN 值的范围 IN 值的子集 LIKE 变量文本搜索 MATCHES 变量文本搜索 IS NULL 搜索 NULL 字符串 IS NOT NULL 搜索非 NULL 字符串 AND OR NOT 用于连接两个或多个条件的逻辑运算符 算术运算符算术运算符 加 减 乘 除 关系运算符关系运算符 or 等于 or 不等于 大于 大于或等于 小于 小于或等于 您可以使用在 WHERE 子句中使用前置关键字和运算符来创建执行以下操作的比 较条件查询 包括某些值 排除某些值 查找某范围中的值 排除某范围中的值 查找值的某个子集 识别 NULL 值 包括某些值包括某些值 使用等号 关系运算符在 WHERE 子句中包括某些行 如以下示例所示 SELECT customer num call code call dtime res dtime FROM cust calls WHERE user id maryj 此查询将返回 cust calls 表中的所有行 下面给出了一个使用 1 1 表达式的 查询示例 SELECT customer num call code call dtime res dtime FROM cust calls WHERE 1 1 排除某些值排除某些值 使用 or 关系运算符在 WHERE 子句中排除某些值 清单清单 1 1 使用使用 和和 运算符排除某些值的示例运算符排除某些值的示例 SELECT customer num company city state FROM odin customer WHERE state CA SELECT customer num company city state FROM odin customer WHERE state CA 查找某范围中的值查找某范围中的值 清单 2 显示了在 WHERE 子句中指定行范围的两种方法 使用 BETWEEN 和 AND 关键字 使用 和 10005 AND catalog num 120 INSERT INTO TEMP temp cust 以下示例使用 LOAD FROM 子句从 customer txt 分隔文本文件向 customer 表 插入记录 文本文件可以拥有一个或多个记录 LOAD FROM customer txt INSERT INTO customer 在连续列中插入值在连续列中插入值 表可以在列中存储连续的数据类型 SERIAL SERIAL8 BIGSERIAL 数据库 服务器将为连续列生成值 在插入值时 可以为连续列指定零值 数据库服务 器将顺序生成下一个实际值 连续列不允许 NULL 值 您可以插入连续数 明确值或用于重设 SERIAL SERIAL8 或 BIGSERIAL 列中 的值的明确值 要插入连续的值 可以为 INSERT 语句中的连续列指定零值 0 在本 例中 数据库服务器将分配下一个最大的值 数据库服务器为您的生成 的值是列中大于最大值的某个值 要插入某个明确值 可以为连续列指定一个非零值 并且数据库服务器 将使用该值 如果连续列拥有唯一的索引 或者有唯一的约束 并且您 的值与表中的值重复 则会出现错误 如果连续列没有唯一约束 那么 可以将值插入到连续列中 如果值大于当前的最大值 则会在连续序列 中创建一个缺口 要在序列中创建一个缺口 即重置连续值 可以在列中指定一个大于 当前最大值的正值 NULL 值在连续列中是无效的 如果在 DB Access 中使用交互式模式编辑器定 义表 则会自动为 SERIAL 列使用唯一的索引 更新数据 使用 UPDATE 语句更改表或视图中的数据 UPDATE 语句用于修改表或视图中的数据 您可以在 UPDATE 语句中使用 WHERE 子句来指定一个条件 表或视图中满足 WHERE 子句指定的条件的各行都将被修 改 如果未指定 WHERE 子句 则会修改表中的所有行 以下示例使用 UPDATE 语句将特定订单编号的配送费用修改为 15 00 UPDATE orders SET ship charge 15 00 WHERE order num 1001 删除数据 使用 DELETE 语句清除表中的数据 DELETE 语句用于从表中删除整行数据 您可以使用 DELETE 语句结合 WHERE 子句来指定一个条件 表中满足 WHERE 子句指定的条件的所有行都将被删除 如果未指定 WHERE 子句 则会删除表中的所有行 以下示例使用 DELETE 语句 删除 orders 表中没有订单存货的所有记录 DELETE FROM orders WHERE backlog n 事务概念 理解事务 事务是作为单一操作执行的一个或多个 SQL 语句 在关系数据库世界中 事务 还表示一个工作单元 您可以把事务看作必须完全完成或根本不完成的修改序 列 事务在 SQL 语句序列中的第一条语句执行之前开始 事务完成的条件是所 有 SQL 语句成功执行 或者在出现错误的情况下 SQL 执行的更改回滚到序列 中 事务可以预防出现不可预测的故障 并且可以在检测到逻辑错误时采用可 编程的方式退出 您可以把事务看作 IDS 中由逻辑日志文件跟踪的活动 您在事务中执行的任何 更改都将记录在逻辑日志文件中 在使用事务前考虑以下事项 必须为数据库开启日志功能 必须配置足够数量的逻辑日志文件 请阅读本系列 Informix Dynamic Server 11 50 基础考试 555 认证准备 第 3 部分 DBMS 实例和存储对象 的 数据库日志模式 部分 获取关于如何 开启数据库日志功能的详细信息 在未使用 LOG MODE ANSI 选项创建的数据库中 使用 BEGIN WORK 语句发起一 个事务 并发起 COMMIT WORK 语句结束事务 您还可以使用 ROLLBACK WORK 语句取消自 BEGIN WORK 之后完成的所有修改 BEGINBEGIN WORKWORK BEGIN WORK 语句将开始一个事务 该语句仅对于支持事务日志的数据库有效 UPDATE DELETE 或 INSERT 语句在事务中影响的各行都将被锁定 并且将在整 个事务过程中保持为锁定状态 COMMITCOMMIT WORKWORK COMMIT WORK 语句用于确认和终止事务 该语句将执行自事务之初对数据库执 行的所有修改 COMMIT WORK 语句将释放在事务过程中添加的所有行和表锁定 ROLLBACKROLLBACK WORKWORK ROLLBACK WORK 语句用于取消和终止事务 通过将数据库恢复到事务开始之前 的状态 该语句将事务开始后的所有更改设置为无效 ROLLBACK WORK 语句将 释放事务过程中添加的所有行和表锁定 WORK 关键字在 BEGIN WORK COMMIT WORK 和 ROLLBACK WORK 语句中为可选 以下示例显示了 BEGIN WORK 和 COMMIT WORK 语句绑定的事务 BEGIN WORK DELETE FROM orders WHERE backlog n INSERT INTO STATE VALUES QB Quebec COMMIT WORK 在本例中 您首先从 orders 表中删除 backlog 列等于 n 的行 然后 在 state 表中插入一个新行 事务将确保两个事务同时成功 或者都不成功 如果未成功发起 COMMIT WORK 语句 则 IDS 会在会话结束时回滚事务对数据 库执行的所有修改 如果未发起 BEGIN WORK 语句启动事务 则各语句将在自 己的事务中执行 并被认为是单语句事务 这些单语句事务不需要 BEGIN WORK 语句或 COMMIT WORK 语句 数据库日志的类型将定义管理 SQL 语句中的事务的方式 如果数据库使用 ANSI 日志模式 则不用使用 BEGIN WORK 来启动事务 事务将在 ANSI 兼容的 数据库中隐式启动 您将使用 COMMIT 或 ROLLBACK 语句来结束事务 UDR 用户定义的例程 用户定义的例程 UDR 是在系统目录表中创建和注册的例程 并且将在 SQL 语句或其他例程中调用 UDR 可以是函数或者过程 作为例程的函数可以选择接受一组参数 并返回一 组值 函数可以在 SQL 表达式中使用 作为例程的过程可以选择接受一组参数 但不会返回任何值 过程不能在 SQL 表达式中使用 因为它不返回任何值 可以使用 Informix 存储过程语言 SPL 或者受支持的外部语言 比如说 C 或者 Java 技术 来编写用户定义的例程 Informix 存储过程语言 存储过程语言 SPL 例程 例程 存储过程语言 SPL 是数据库服务器的一部分 SPL 易于使用 并且不需要在数 据库服务器外部获得支持 SPL 为 SQL 提供流控制扩展 SPL 例程是一个使用 SPL 和 SQL 编写的 UDR SPL 例程的主体包含 SQL 语句和流控制语句 用于 循环和分支 SPL 例程经过了尽可能的优化和解析 然后以可执行的格式存储在 系统目录表中 如果某应用程序在执行 SQL 任务只需要很少或者不需要任何用户 交互 则执行各任务的例程可以存储在数据库服务器上 使用 SPL 例程有若干显 著的优势 使用 SPL 例程可显著降低处理开销并极大缩短处理时间 每次在执行 SQL 语句时 它都会从客户机发送到服务器 并且在实际执行其操作之前都需 要经过优化和解析过程 使用 SPL 例程后 解析和优化将成为例程创建 过程中的一部分 因此执行的速度更快 存储过程将减少网络流量 一天中可能需要重复执行复杂的 SQL 语句若干 次 这会降低网络速度 您可以将相同的 SQL 语句放在相同的存储过程 中 以便于显著减少网络流量 这样 只有调用存储过程的语句才会经过 网络 可以采用许多不同的方式来使用 SPL 过程或函数 您可以从触发器调用它们来扩展触发器的功能 它们可以嵌入在 SQL 语句 或者使用 EXECUTE PROCEDURE 语句调用它 们 您可以通过传递参数使它们变为动态 外部语言例程外部语言例程 外部语言例程是使用外部语言编写的 UDR 外部语言例程的主体包含流控制和循 环等操作语句 因此 您必须使用适当的编译工具将外部语言例程解析和编译为可 执行的格式 数据库服务器支持使用 C 和 Java 技术语言编写的 UDR 外部例程通常用于对用户定义的数据类型执行操作 本教程不会详细讨论外部语言 例程 有关详细信息 请参阅 IBM Informix Database Server 文档 回页首 创建 SPL 例程 SPL 例程是使用 SQL 和 SPL 编写的用户定义的例程 在创建存储过程之前 您必须连接到数据库 创建过程操作将执行解析 优化和转换到 ASCII 操作 然后才实际存储到 sysprocedures 系统目录表中 您可以使用 CREATE PROCEDURE 语句创建一个用户定义的例程 如果创建使用 C 或 Java 技术语言 编写的过程 则应将源代码存放在一个单独的文件中并使用 CREATE PROCEDURE FROM 语句 清单 14 是一个简单的创建存储过程的示例 它向列中的一些列中插入了一些 整型值 清单清单 14 创建创建 SPL 过程的示例过程的示例 CREATE PROCEDURE insert rows num INT DEFINE i INTEGER LET i 1 WHILE i num INSERT INTO table1 numbers VALUES i LET i i 1 END WHILE END PROCEDURE 在 IDS 中 虽然可以使用 CREATE PROCEDURE 编写和注册返回一个或多个值的 SPL 例程 也就是一个 SPL 函数 但仍然建议您使用 CREATE FUNCTION 作 为替代 要注册外部函数 必须使用 CREATE FUNCTION 要创建返回一个或多个值的 SPL 函数 使用 CREATE FUNCTION 关键字作为开 始 并使用 END FUNCTION 关键字作为结束 清单 15 显示了如何开始和结束 SPL 函数 清单清单 15 创建创建 SPL 函数的示例函数的示例 CREATE FUNCTION read address lastname CHAR 15 one argument RETURNING CHAR 15 CHAR 15 CHAR 20 CHAR 15 CHAR 2 CHAR 5 6 return values items DEFINE p lname p fname p city CHAR 15 define each routine variable DEFINE p add CHAR 20 DEFINE p state CHAR 2 DEFINE p zip CHAR 5 SELECT fname address1 city state zipcode INTO p fname p add p city p state p zip FROM customer WHERE lname lastname RETURN p fname lastname p add p city p state p zip 6 items END FUNCTION Informix Dynamic Server 允许您创建多个使用相同名称但参数不同的 SPL 例 程 此特性被称作例程重载 举例来说 您可以在数据库中创建名称为 read address 的以下数据库 清单清单 16 例程重载的示例例程重载的示例 CREATE FUNCTION read address customer id INT one argument RETURNING CHAR 15 CHAR 15 CHAR 20 CHAR 15 CHAR 2 CHAR 5 6 items DEFINE p lname p fname p lastname p city CHAR 15 define each routine variable DEFINE p add CHAR 20 DEFINE p state CHAR 2 DEFINE p zip CHAR 5 SELECT fname lname address1 city state zipcode INTO p fname p lastname p add p city p state p zip FROM customer WHERE customer num customer id RETURN p fname p lastname p add p city p state p zip 6 items END FUNCTION 由于例程过载的问题 SPL 例程不可以在 IDS 使用名称唯一标识 但是 可以 通过特定名 来唯一标识例程 特定名 是您在 CREATE PROCEDURE 或 CREATE FUNCTION 语句中除例程名称之外定义的唯一标识符 特定的名称使用 SPECIFIC 关键字定义 并且在数据库中是唯一的 相同数据库中的两个例程不 能使用相同的特定名 即使它们的所有者不同 清单 17 展示了如何在创建 read address 函数的 CREATE FUNCTION 语句中定 义特定名 我们将 read address bylastame 声明为按 lastname 查找地址的 函数的特定名 并将 read address bycustid 声明为按客户编号查找地址的函 数的特定名 清单清单 17 使用特定名的的函数的示例使用特定名的的函数的示例 CREATE FUNCTION read address lastname CHAR 15 one argument RETURNING CHAR 15 CHAR 15 CHAR 20 CHAR 15 CHAR 2 CHAR 5 6 items SPECIFIC read address bylastame END FUNCTION 清单清单 18 使用不同特定名的相同函数的示例使用不同特定名的相同函数的示例 CREATE FUNCTION read address lastname CHAR 15 one argument RETURNING CHAR 15 CHAR 15 CHAR 20 CHAR 15 CHAR 2 CHAR 5 6 items SPECIFIC read address bycustid END FUNCTION 回页首 执行 SPL 例程 您可以使用 EXECUTE PROCEDURE 或 EXECUTE FUNCTION 来执行 SPL 例程或外 部例程 通常 最好是使用 EXECUTE PROCEDURE 来执行 SPL 过程 而使用 EXECUTE FUNCTION 来执行 SPL 函数 可以从 DB Access SPL 例程或者外部 例程来调用存储过程 SPL 例程可以执行使用外部语言编写的例程 并且外部 语句例程可以执行 SPL 例程 以下示例显示了名称为 insert rows 的 SPL 过程的执行 EXECUTE PROCEDURE insert rows 10 要执行函数 您需要考虑使用 INTO 子句来存储返回值 清单 19 中的示例展 示了名称为 update customer 的 SPL 函数的执行 它接受两个输入参数并 使用 INTO 子句指定存储返回值的变量 清单清单 19 SPL 函数执行的示例函数执行的示例 CREATE PROCEDURE get customer info cust num INT DEFINE lname fname city CHAR 15 DEFINE add CHAR 20 DEFINE state CHAR 2 DEFINE zip CHAR 5 EXECUTE FUNCTION read address cust num INT INTO fname lastname add city state zip END PROCEDURE 您将使用 CALL 语句从 SPL 例程中执行 UDR CALL 语句的行为与 EXECUTE PROCEDURE 和 EXECUTE FUNCTION 语句相同 但您只能在 SPL 例程中使用 CALL 清单 20 中的示例从 SPL 例程 add numbers 中调用 read address 函 数 清单清单 20 调用调用 UDR 的示例的示例 CREATE PROCEDURE get customer info cust num INT DEFINE lname fname city CHAR 15 DEFINE add CHAR 20 DEFINE state CHAR 2 DEFINE zip CHAR 5 CALL read address cust num RETURNING fname lastname add city state zip END PROCEDURE 回页首 存储过程的必要权限 存储过程共有三种与之相关的权限水平 数据库级权限 语言级权限 例程级权限 首先 您需要具备数据库 Resource 权限才能在该数据库中创建任何种类的 SPL 过程或 SPL 函数 默认情况下 SPL 的 Usage 权限将授予 PUBLIC 以下 示例展示了如何将数据库的 Resource 权限授予程序员角色 GRANT RESOURCE TO programmers 其次 您需要 SPL 语言的 Usage 权限才能创建 SPL 过程 SPL 语言的 Usage 权限默认将授予 PUBLIC 只有用户 informix DBA 或者授予 Usage 权限 WITH GRANT OPTION 的用户才可以将 SPL 的 Usage 权限授予其他用户 以下 示例展示了如何将 SPL 语言的 Usage 权限授予程序员角色 GRANT USAGE ON LANGUAGE SPL TO programmers 最后 您还需要例程执行许可 在注册 UDR 时 您会自动接受该例程的 Execute 权限 Execute 权限允许您调用 UDR 以下示例展示了如何将 add numbers SPL 例程的 Execute 权限授予程序员角色 GRANT EXECUTE ON add numbers TO programmers 回页首 抛弃存储过程 您可以使用 DROP PROCEDURE 语句从数据库抛弃过程 但是 只有过程的所有 者和具备 DBA 权限的用户才能抛弃过程 您还可以使用 DROP ROUTINE 语句从 数据库中删除 UDR 抛弃 UDR 将从数据库中删除文本和可执行版本的 UDR 以 下示例展示了如何抛弃存储过程 DROP PROCEDURE insert rows 如果有多个使用相同名称的过