INFORMIX数据库操作及SQL语法

编号：TN-070101001

TIENON数据库培训教程

INFORMIX基本操作及SQL语法

2007年1月，V 1.00

目录

1、引言 (5)

1.1、读者对象 (5)

1.2、内容简介 (5)

1.3、课程时间 (5)

1.4、课程目标 (5)

2、数据库基本概念...........................................................错误！未定义书签。

2.1、从身边的例子了解数据库......................................................... 错误！未定义书签。

2.2、数据库系统概述......................................................................... 错误！未定义书签。

2.2.1、数据库的产生................................................... 错误！未定义书签。

2.2.2、数据库系统组成................................................. 错误！未定义书签。

2.2.3、与数据库相关的软件系统......................................... 错误！未定义书签。

2.2.4、数据库系统特点................................................. 错误！未定义书签。

2.2.5、数据库系统的历史............................................... 错误！未定义书签。

2.2.6、数据库系统的发展趋势........................................... 错误！未定义书签。

2.2.7、数据库的分类................................................... 错误！未定义书签。

2.3、关系数据库................................................................................. 错误！未定义书签。

2.3.1、关系数据库基本素语............................................. 错误！未定义书签。

2.3.2、数据模型....................................................... 错误！未定义书签。

2.4、数据库管理系统(DBMS) ............................................................ 错误！未定义书签。

2.4.1、DBMS功能...................................................... 错误！未定义书签。

2.4.2、DBMS组成...................................................... 错误！未定义书签。

2.4.3、数据库、表的建立............................................... 错误！未定义书签。

2.4.4、SQL............................................................ 错误！未定义书签。

3、当前流行数据库比较...................................................错误！未定义书签。

3.1.1、性能比较....................................................... 错误！未定义书签。

3.1.2、其他比较....................................................... 错误！未定义书签。

4、INFORMIX数据库安装.................................................错误！未定义书签。

4.1.1、建立INFORMIX用户组及用户...................................... 错误！未定义书签。

4.1.2、配置unix 核心参数.............................................. 错误！未定义书签。

4.1.3、修改informix 用户的下的 .profile 文件........................... 错误！未定义书签。

4.1.4、拷贝、安装informix数据库系统................................... 错误！未定义书签。

4.1.5、准备数据库空间................................................. 错误！未定义书签。

4.1.6、建立、修改informix 连接、配置文件 .............................. 错误！未定义书签。

4.1.7、配置online 并作初始化.......................................... 错误！未定义书签。

4.1.8、对要使用数据库的用户赋权或取消权限 ............................. 错误！未定义书签。

4.1.9、数据库的日常操作............................................... 错误！未定义书签。

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基本操作

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注册才能执行本命令,本命

令不但能初始化共享内存,还能初始化磁盘空间。

[语法] 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)

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显示数据库状态

本命令显示数据库在执行本命令时的状态,执行本命令时不在共享内存上加锁,故执行时不会影

响正常运行。

[语法] 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调用,这些数字是多个实际库服务器的总统计数,故不能用于监测单个数据库,除非目前只有一个活动的数据库服务器或只存在一个数据库,从左至右依次是: 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

将数据库以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

将数据库的模式输出到文件中，可用于重建数据库或表。

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：数据库名称。

用户是DBA或所有表的拥有者；删除所有文件，但不包括数据库目录；不允许删除当前数据库（须先关闭当前数据库）；事务中处理过程中不能使用此语句，通过ROLLBACK WORK 也不可将数据库恢复。

例：drop databse customerdb;

3.1.5、创建表或临时表

CREATE [TEMP] TABLE table-name (column_name datatype [NOT NULL], …)[IN“pathnam e”]

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);

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：视图名称。

用户可删除自己建立的视图；视图的后代视图也被删除；事务中处理中不要使用此语句。

例：drop view v_user;

3.1.11、创建索引

CREATE [UNIQUE/DISTINCT] [CLUSTER] INDEX index_name ON table_name ([column_na me 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语句。

例：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：表名称。

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

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、SOME

column_name：分组字段名称

condition：群聚条件

column_list：排序字段列表，缺省ASC，可指定DSC；排序时，NULL值小于非零值。

table_name：临时表名称

3.1.23、插入

INSERT INTO view_name|table_name [(column_list)] VALUES (value_list)

或 INSERT INTO view_name|table_name [(column_list)] select_statement

view_name|table_name：视图名或表名称

column_list：数据项列表。

value_list：值列表

select_statement：查询语句。

3.1.24、删除

DELETE FROM view_name|table_name WHERE search-conditions

view_name|table_name：视图名或表名称

search-conditions；删除条件

3.1.25、更新

UPDATE view_name|table_name SET column_1 = value_1ist WHERE search_conditions 或UPDATE view_name|table_name SET column_1|* = value_1ist WHERE search_conditions view_name|table_name：表名称或视图表名称

value_1ist：字段值

search_conditions：更新数据的条件

3.1.26、装入数据

LOAD FROM “file-name” INSERT INTO table_name [(co lumn_name[,…])]

例：load form “aa.txt” insert into user;

3.1.27、卸载数据

UNLOAD TO “pathname”

例：unload to “aa.txt” select * from user;

3.1.28、常用函数

(1)集合函数：

count(*)、sum(数据项/表达式)、avg(数据项/表达式)、max(数据项/表达式)、min(数据项/表达式)

count(distinct 数据项/表达式)、sum(distinct数据项/表达式)、avg(distinct数据项/表达式)

(2)代数函数和三角函数

HEX(数据项/表达式)、ROUND(数据项/表达式)、TRUNC(数据项/表达式)、TAN(数据项/表达式)、ABS(数据项/表达式)、MOD(被除数,除数)

(3)统计函数

标准差，stdev()、方差，variance()、范围，rang()

(4)时间函数

DAY(日期/时间表达式)：返回数字型

MONTH(日期/时间表达式)：返回整数

WEEKDAY(日期/时间表达式)：0

4、练习

熟悉INFORMIX数据库的操作、采用工具和脚本两种模式建库、建表、编写执行SQL脚本熟悉SQL。