INIX数据库操作及SQL语法.doc

编号 TN 070101001 TIENON 数据库培训教程 INFORMIX 基本操作及 SQL 语法 2007 年 1 月 V 1 00 Tienon 数据库培训教程 INFORMIX 基本操作及 SQL 语法 天用唯勤 服务至上 3 2 目 录 1 引言 4 1 1 读者对象 4 1 2 内容简介 4 1 3 课程时间 4 1 4 课程目标 4 2 数据库基本概念 5 2 1 从身边的例子了解数据库 5 2 2 数据库系统概述 6 2 2 1 数据库的产生 6 2 2 2 数据库系统组成 6 2 2 3 与数据库相关的软件系统 7 2 2 4 数据库系统特点 7 2 2 5 数据库系统的历史 8 2 2 6 数据库系统的发展趋势 8 2 2 7 数据库的分类 9 2 3 关系数据库 9 2 3 1 关系数据库基本素语 9 2 3 2 数据模型 10 2 4 数据库管理系统 DBMS 10 2 4 1 DBMS 功能 11 2 4 2 DBMS 组成 11 2 4 3 数据库 表的建立 11 2 4 4 SQL 13 3 当前流行数据库比较 14 3 1 1 性能比较 14 3 1 2 其他比较 16 4 INFORMIX 数据库安装 19 4 1 1 建立 INFORMIX 用户组及用户 19 4 1 2 配置 unix 核心参数 19 4 1 3 修改 informix 用户的下的 profile 文件 20 4 1 4 拷贝 安装 informix 数据库系统 20 4 1 5 准备数据库空间 21 Tienon 数据库培训教程 INFORMIX 基本操作及 SQL 语法 天用唯勤 服务至上 3 3 4 1 6 建立 修改 informix 连接 配置文件 21 4 1 7 配置 online 并作初始化 24 4 1 8 对要使用数据库的用户赋权或取消权限 31 4 1 9 数据库的日常操作 32 Tienon 数据库培训教程 INFORMIX 基本操作及 SQL 语法 天用唯勤 服务至上 3 4 1 引言 Tienon 是一家专业从事金融软件行业 服务于行业客户的专业软件公司 公司产品包括分行特 色业务处理平台 分行渠道接入平台 分行对外连接平台等等 我们所服务的客户都是以数据为基 础的 他们所提供给最终客户的任何一种服务都需要严格的记录 结算 报表等等 因此 我们的 任何软件都离不开数据库 都是搭建在数据库基础之上的业务软件系统 数据库系统对于任何一个有一定软件基础的人来说 都应该不是一个陌生的概念 但是 在实 际运用过程中它确又往往给我们造成相当的麻烦 比如 数据准确性 数据完整性 数据库效率等 等 所以 我们开设这门数据库培训课程 目的是让大家在知道 了解数据库基础知识的前提下 能够更深入的在数据库使用上有所收获 Tienon 所服务的客户中 绝大部分客户使用了 INFORMIX 数据库 因此 我们将以 INFORMIX 为实例来为大家讲解数据库的相关知识 1 1 读者对象 学习过数据库原理的软件开发人员 1 2 内容简介 本期课程涉及两部分内容 INFORMIX 基本操作 SQL 基本语法 INFORMIX 基本操作 了解 INFORMIX 数据库的操作命令和操作方法 SQL 基本语法 了解标准 SQL 语法以及部分 INFORMIX 特殊语法 1 3 课程时间 本期课程为期一天 讲解半天 演练半天 1 4 课程目标 本期课程属于 Tienon 数据库培训的第二课 目的是希望参训人员通过本期培训能够更进一步了 解 INFORMIX 的实际操作命令 同时对 SQL 语句有更深入了解 2 INFORMIX 基本操作 Tienon 数据库培训教程 INFORMIX 基本操作及 SQL 语法 天用唯勤 服务至上 3 5 2 1 命令类型 INFORMIX 的操作命令从表现上来说 有两种模式 菜单模式和命令模式 菜单模式即输入命令 后 展现给用户是操作菜单 用户可以移动光标操作各菜单项达到操作数据库的目的 命令模式即 与传统的 UNIX 命令类似 用户通过输入命令以及命令所带参数 来完成数据库的操作 菜单模式比较常用的操作主要有 onmonitor dbaccess isql 等 命令模式比较常见的操作很多 主要有 oninit onstat onmode 等 2 2 菜单模式命令 2 2 1 onmonitor 用来管理配置数据库的操作命令 其中包括数据库状态 参数配置 数据库空间 模式 日志 等 2 2 2 dbaccess 用来操作数据库数据的操作命令 其中包括执行 SQL 语句 建库 建表等 2 2 3 isql 用来操作数据库数据的操作命令 与 dbaccess 类似 包括执行 SQL 语句 建库 建表等 主要 有特点的地方在于其 form 的使用 2 3 命令模式命令 所有数据库命令都可以用以下方式来查看其帮助说明 命令 help 2 3 1 oninit 启动数据库 oninit 命令将在离线 off line 状态的数据库启动为在线 on line 模式 数据库有六种工作模式 它们是 离线 off line 不运行状态 静模式 quiescent 在此模式下 用户不能连接到数据库 但可 用 onstat 等命令查看数据库信息在线 on line 数据库运行状态只读 read only 只能读数据库但不 能写 恢复 recovery 是一种临时状态 存在于从离线模式到静模式之间 关闭 shutdown 是一种临 时状态 存在于从在线模式到静模式或离线模式 oninit 命令将在离线 off line 状态的数据库启动为 在线 on line 模式 并初始化共享内存 shared memory 在作初始化之前 应先设置环境变量 INFORMIXSERVER 否则数据库不建立 sysmaster 表 必须以 root 或 informix 注册才能执行本命令 本 命令不但能初始化共享内存 还能初始化磁盘空间 Tienon 数据库培训教程 INFORMIX 基本操作及 SQL 语法 天用唯勤 服务至上 3 6 语法 oninit p s 或 oninit i s 说明 第一行用法为只初始化共享内存 p 不检测临时表 s 将数据库转换成静模式 第二行用法为同时初始化磁盘空间 使用时必须慎重 因为此命令将破坏所有数据 s 将数据库转换成静模式 例子 oninit oninit s 2 3 2 onmode 改变模式和共享内存 本命令有以下功能 改变数据库工作模式 设置检测点 checkpoint 改变共享内存 shared memory 区 切换逻辑日志文件 杀死一个数据库服务期 server session 杀死一个数据库事务 transaction 设置数据复制类型 data replication type 增加一段共享内存 增加或删除一个虚 拟处理器 设置决策支持 decision support 参数 释放无用内存段 只有用 root 或 informix 注册 才能使用此命令 下面按功能说明用法 改变工作模式 onmode k m s u k 转到离线 off line 模式 m 从静模式 quiescent 转到在线 on line 模式 s 从在线模式转到静模式 允许所有正进行的业务做完 但不允许新的连接 u 从在线模式立即转到静模式 不等业务做完 设置检测点 checkpoint onmode c 改变共享内存驻留 onmode n r n 立即停止共享内存的强制驻留 r 立即开始共享内存的强制驻留 切换逻辑日志文件 切换到下一逻辑日志文件 onmode l 杀死数据库服务期 server session Tienon 数据库培训教程 INFORMIX 基本操作及 SQL 语法 天用唯勤 服务至上 3 7 onmode z 服务期号 服务期号可由 onstat n 命令获得 杀死事务 transaction onmode Z 事务地址 杀死一个分配在指定地址上的事务 地址可由 onstat x 获得 设置数据复制 data replication 类型 onmode d standard primary 数据服务名 secondary 数据服务名 使用 d standard 选项 则切断数据复制模式 将当前数据库服务器转换成标准模式 使用 d primary 选项 则试图连接指定的数据库服务器 若成功则将指定的数据库服务器设为基 本服务器 且将数据复制开启 将基本数据库服务器置为 on line 将第二数据库服务器置为 read only 使用 d secondary 选项 则试图连接指定的数据库服务器 若成功则将指定的数据库服务器设 为第二服务器 且将数据复制开启 增加共享内存 onmode a 段大小 段大小的单位为千字节 增加或删除虚拟处理器 onmode p 数字 CPU AIO SHM TLI SOC 或 onmode p 数字 CPU 上一行为增加虚拟处理器 下一行为删除虚拟处理器 一次只能增加或删除一种类型的处理器 改变决策支持 decision support 参数 onmode D 优先级数 onmode M 千字节数 onmode Q 查询数 onmode S 搜索数 以上选项设定四个环境变量 D 设定 MAX PDQPRIORITY M 设定 DS TOTAL MEMORY Q 设定 DS MAX QUERIES S 设定 DS MAX SCANS 释放无用内存 onmode F 2 3 3 onstat 显示数据库状态 本命令显示数据库在执行本命令时的状态 执行本命令时不在共享内存上加锁 故执行时不会影 响正常运行 Tienon 数据库培训教程 INFORMIX 基本操作及 SQL 语法 天用唯勤 服务至上 3 8 语法 onstat 选项 选项说明 若不带任何选项执行 onstat 则相当于执行了 onstat pu 列出本命令的所有选项 a 执行 onstat a 相当于执行了 onstat cuskbtdlp 所有小写选项 b 显示正在使用的缓冲区 B 显示所有的缓冲区 不仅是正在使用的 c 显示 ONCONFIG 文件内容 数据库首先检测你是否定义了环境变量 ONCONFIG 若有则显示 INformIX etc ONCONFIG 文件的内容 若未定义 则显示 INformIX etc onconfig 文件的内容 此文 件包含了许多系统需要的定义和设置 C 显示 B 树清除 B tree cleaner 情况 d 显示每个数据空间 dbspace 的块 chunk 信息 D 显示每个数据空间头 50 个块的页 page 读写的情况 F 显示每一种类型的写回磁盘 flush pages to disk 的统计数 h 显示哈西链 hash chain 缓冲区信息 i 进入 onstat 的交互方式 可直接敲入 onstat 命令 使用 CTRL d 退出交互方式 k 显示活动锁 active lock 的信息 l 显示物理日志和逻辑日志的信息 m 显示 20 个系统最近用到的系统信息日志 o 文件名 将当前共享内存段内容写入指定文件 缺省为 onstat out p 显示各项统计数 第一部分为显示读写情况 从左至右分为八部分 依次是 dskreads 实际读磁盘的次数 pagreads 读到的页数 bufreads 从缓冲区 共享内存中 读的次数 cached bufreads dskreads bufreads dskwrits 实际写磁盘次数 包括写日志 pagwrits 写的页数 bufwrits 写缓冲区 共享内存 的次数 cached bufwrites dskwrite bufwrits 若发现缓冲区利用率太低 则会降低数据库的速度 此时应使用 onmode 命令增加共享内存或改 进程序中的数据库访问方法 第二部分显示 ISAM 索引顺序存取 调用情况 这是一种最低级的调用 不一定和 SQL 语句一一对 应 一次 SQL 可能执行多次 ISAM 调用 这些数字是多个实际库服务器的总统计数 故不能用于监测单 Tienon 数据库培训教程 INFORMIX 基本操作及 SQL 语法 天用唯勤 服务至上 3 9 个数据库 除非目前只有一个活动的数据库服务器或只存在一个数据库 从左至右依次是 isamtot 总调用次数 open 打开表空间 tblspace 时的次数 start 在索引中定位时的次数 read 读时的次数 write 写时的次数 rewrite 更新时的次数 delete 删除时的次数 commit 执行一次 iscommit 时的次数 不一定与 COMMIT WORK 一一对应 rollbk 回滚时的次 数 第三部分显示对资源请求时不能满足的次数 从左至右依次是 ovlock 执行锁操作请求超过定义的最大值的次数 最大值由 ONCONFIG 文件中的 LOCKS 定义 ovuserthreads 用户试图超过定义的最大用户线索的次数 最大值由 ONCONFIG 文件中的 USERTHREADS 定义 ovbuff 系统试图超过定义的最大共享内存缓冲区的次数 Informix 数据库常用命令快速索引 抱 歉 必需起用浏览器的 javascript 支持才能正常浏览 2 3 4 oncheck 一致性检查 oncheck ce 检查 Chunks 和 extents oncheck cd 检查数据行 oncheck ci 检查索引值 oncheck cI 检查索引值和 rowid oncheck cr 检查系统保留页 oncheck cc 检查系统目录表 2 3 5 ontape 日志操作 ontape a 备份所有已满的逻辑日志 ontape c 启动连续逻辑日志备份 ontape s 启动数据备份 ontape r 启动数据恢复 ontape s B N U 数据库名 改变数据库日志的状态 2 3 6 dbexport Tienon 数据库培训教程 INFORMIX 基本操作及 SQL 语法 天用唯勤 服务至上 3 10 将数据库以 ASCII 方式下载 该命令常用于迁移数据库 如 命令 dbexport o informix db export stores7 将数据库 stores7 下载到 informix db export stores7 exp 目录下 数据库模式文件存放在 informix db export stores7 exp stores7 sql 下 2 3 7 dbimport 与 dbexport 配合使用 根据 dbexport 的输出创建数据库 如 命令 dbimport c i informix db export stores7 根据上例中输出结果创建数据库 2 3 8 dbschema 将数据库的模式输出到文件中 可用于重建数据库或表 Tienon 数据库培训教程 INFORMIX 基本操作及 SQL 语法 天用唯勤 服务至上 3 11 3 SQL 基本语法 使用 dbaccess 进行菜单操作时 选择 Query language 然后按 Ctrl w 便可以进入 SQL 语句帮助 信息查询 3 1 1 创建数据库 CREATE DATABASE database name WITH LOG IN pathname database name 数据库名称 pathname 事务处理日志文件 创建一 database name dbs 目录 存取权限由 GRANT 设定 无日志文件就不能使用 BEGIN WORK 等事务语句 可用 START DATABASE 语句来改变 可选定当前数据库的日志文件 如 select dirpath form systables where tabtype L 例 create databse customerdb with log in usr john log customer log 3 1 2 选择数据库 DATABASE databse name EXCLUSIVE database name 数据库名称 EXCLUSIVE 独占状态 存取当前目录和 DBPATH 中指定的目录下的数据库 事务中处理过程中不要使用此语句 例 dtabase customerdb 3 1 3 关闭当前数据库 CLOSE DATABASE database name 数据库名称 此语句之后 只有下列语句合法 CREATE DATABASE DATABASE DROP DATABSE ROLLFORWARD DATABASE 删除数 据库前必须使用此语句 例 close database 3 1 4 删除指定数据库 DROP DATABASE database name database name 数据库名称 Tienon 数据库培训教程 INFORMIX 基本操作及 SQL 语法 天用唯勤 服务至上 3 12 用户是 DBA 或所有表的拥有者 删除所有文件 但不包括数据库目录 不允许删除当前数据库 须先关闭当前数据库 事务中处理过程中不能使用此语句 通过 ROLLBACK WORK 也不可将数 据库恢复 例 drop databse customerdb 3 1 5 创建表或临时表 CREATE TEMP TABLE table name column name datatype NOT NULL IN pathname table name 表名称 column name 字段名称 data type 字段数据类型 path name 指定表的存放位置 TEMP 用于指定建立临时表 表名要唯一 字段要唯一 有 CONNECT 权限的用户可建立临时表 创建的表缺省允许 CONNECT 用户存取 但不可以 ALTER 例 create table user c0 serial not null c1 char 10 c2 char 2 c3 smallint c4 decimal 6 3 c5 date in usr john customer dbs user 3 1 6 修改表结构 ALTER TABLE table name ADD newcol name newcol type BEFORE oldcol name DROP oldcol name MODIFY oldcol name newcol type NOT NULL table name 表名称 newcol name 新字段名称 newcol type 新字段类型 oldcol name 老字段名称 可以使用单个或多个 ADD 子句 DROP 子句 MODIFY 子句 但某个字句失败 操作即中止 原 字段是 NULL 不允许 MODIFY 为 NOT NULL 除非所有 NULL 字段中均非空 反之可以 ALTER 使 用者是表的拥有者或拥有 DBA 权限 或被授权 事务中处理过程中不要使用此语句 例 alter table user add c6 char 20 before c5 Tienon 数据库培训教程 INFORMIX 基本操作及 SQL 语法 天用唯勤 服务至上 3 13 3 1 7 修改表名 RENAME TABLE oldname TO newname oldname 原名称 newname 新名称 RENAME 使用者是表的拥有者或拥有 DBA 权限 或被授权 事务中处理过程中不要使用此语句 例 rename user to bbb 3 1 8 删除表 DROP TABLE table name table name 表名称 删除表意味着删除其中所有数据 各字段上的索引及对表的赋权 视图等 用户不能删除任何 系统目录表 语句使用者是表拥有者或拥有 DBA 权限 事务中处理过程中不要使用此语句 3 1 9 创建视图 CREATE VIEW view name column list AS select statement WITH CHECK OPTION view name 视图名称 column list 字段列表 select statement SELECT 语句 以下语句不使用视图 ALTER TABLE DROP INDEX ALTER INDEX LOCK TABLE CREATE INDEX RENAME TABLE 视图将延用基表的字段名 对表达式等虚字段和多表 间字段重名必须指明标识其字段名 若对视图中某些字段命名 则所有字段都必须命名 视图中数 据类型延用基表中的数据类型 虚字段起诀于表达式 不能使用 ORDER BY 和 UNION 子句 对视图 中所有的字段要有 SELECT 权限 事务中处理过程中使用此语句 即使事务回滚 视图也将建立 不能恢复 例 create view v user as select from user where c1 B1 3 1 10 删除视图 DROP VIEW view name view name 视图名称 用户可删除自己建立的视图 视图的后代视图也被删除 事务中处理中不要使用此语句 Tienon 数据库培训教程 INFORMIX 基本操作及 SQL 语法 天用唯勤 服务至上 3 14 例 drop view v user 3 1 11 创建索引 CREATE UNIQUE DISTINCT CLUSTER INDEX index name ON table name column name ASC DESC index name 索引名称 table name 表名称 column name 字段名称 UNIQUE DISTINCT 唯一索引 CLUSTER 使表的物理存放顺序按索引排列 ASC DESC 升序或降序 缺省升序 语句执行时 将表的状态置为 EXCLUSIVE 复合索引最多包含 8 个字段 所有字段长度和不得 大于 120 字节 事务中处理过程中使用此语句 即使事务回滚 索引将建立 不能恢复 例 create cluster index ix user on user c5 3 1 12 删除索引 DROP INDEX index name index name 索引名称 语句使用者是索引的拥有者或拥有 DBA 权限 事务中处理过程中不要使用此语句 否则事务无 法恢复 例 drop index ix user 3 1 13 更新数据库的统计数字 UPDATE STATISTICS FOR TABLE table name table name 表名称 此语句仅作用于当前数据库 可提高查询效率 只有执行此语句 才改变统计数据 例 update statistics for table user 3 1 14 授权命令 GRANT TO PUBLIC user list 全部或指定的用户 三种权限居且仅居其一 事务处理过程中不要执行 GRANT 语句 Tienon 数据库培训教程 INFORMIX 基本操作及 SQL 语法 天用唯勤 服务至上 3 15 例 grant resource to pulbic GRANT tab privilege ON table name TO WITH GRANT OPTION 授表级权限 tab privilege 表级权限 table name 表名称 PUBLIC user list 全部或指定的用户 WITH GRANT OPTION 表示被授权用户有否权限进行二次授权 用户可以在自己建立表达式或被 WITH GRANT OPTION 准许的表中进行赋权 限定越多的权 限优先级越高 例 grant update c1 c6 on user to dick with grant option 附 INFORMIX 的权限 1 数据库的权限 控制对数据库的访问以及数据库中表的创建和删除 DBA 权限 全部权利 修改系统表 建立和删除表与索引 增加和恢复表数据 以及授予其他 用户数据库权限等 RESOURCE 权限 允许对数据库表中的数据进行存取 建立永久性表以及索引 CONNECT 权限 只允许对数据库表中的数据进行存取 建立和删除视图与临时表 2 表级权限 对表的建立 修改 检索和更新等权限 ALTER 更改权限 DELETE 删除权限 INDEX 索引权限 INSERT 插入权限 SELECT cols 指定字段或所有字段上的查询权限 不指明字段缺省为所有字段 UPDATE cols 指定字段或所有字段上的更新权限 不指明字段缺省为所有字段 ALL PRIVILEGES 以上所有表级权限 3 1 15 收权命令 REVOKE FROM PUBLIC user list 全部或指定的用户 三种权限居且仅居其一 事务处理过程中不要执行 GRANT 语句 例 revoke resource from john REVOKE tab privilege ON table name FROM 收表级权限 tab privilege 表级权限 table name 表名称 Tienon 数据库培训教程 INFORMIX 基本操作及 SQL 语法 天用唯勤 服务至上 3 16 PUBLIC user list 全部或指定的用户 WITH GRANT OPTION 表示被授权用户有否权限进行二次授权 用户只能取消由其本人赋予其他用户的表级存取权限 不能取消自己的权限 对 SELECT 和 UPDATE 作取消时 将取消所有表中字段的 SELECT 和 UPDATE 权限 例 revoke update on user from dick 3 1 16 记录级加锁和表级加锁或文件加锁 LOCK TABLE table name IN MODE table name 表名称 SHARE 允许读表中数据 但不允许作任何修改 EXCLUSIVE 禁止其他任何形式访问表 每次只能对表琐定一次 事务处理过程中 BEGIN WORK 后立即执行 LOCK TABLE 以取代记录 级加锁 COMMIT WORK 和 ROLLBACK WORK 语句取消所有对表的加锁 若没有事务处理 锁将保 持到用户退出或执行 UNLOCK 为止 例 lock table user in exclusive mode 3 1 17 取消记录级加锁和表级加锁或文件加锁 UNLOCK TABLE table name table name 表名称 例 unlock user 3 1 18 改变锁定状态 SET LOCK MODE TO NOT WAIT TO NOT 等待解锁 有可能被死锁或不等待并提示错误信息 表示此记录被锁 缺省值 访问一个 EXCLUSIVE 状态下的记录 将返回一个错误 3 1 19 开始事务 BEGIN WORK 3 1 20 提交事务 COMMIT WORK Tienon 数据库培训教程 INFORMIX 基本操作及 SQL 语法 天用唯勤 服务至上 3 17 3 1 21 回滚事务 ROLLBACK WORK 3 1 22 查询 SELECT select list FROM tab name view name WHERE condition GROUP BY column name HAVING condition ORDER BY column list INTO TEMP table name select list 选择表或 tab name 表名称 view name 视图名称 condition 查询条件 可使用 BETWEEN IN LIKE IS NULL LIKE MATCHES NOT AND OR 或 ALL ANY S