informix_sql数据库常用命令汇总

informix sql 日常应用积累 (2009-09-09 09:22)分类：Informix#创建数据库CREATE DATABASE database_name ;#修改数据库名rename DATABASE database_name01 To database_name02;#删除数据库drop database database_name;#修复数据库日志模式ontape -s -U database_name01#================================================================ALTER TABLE 修改表结构语法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权限，或被授权；事务中处理过程中不要使用此语句。

#表操作===============================================================#创建表：Create table mytable01(Ino integer unique,Serno char(50),Name char(50),BType char(1),Blnc decimal(16,2),claimamt float,InputDate date);#修改表名alter table mytable01 rename to mytable02;#删除表drop table 表名#字段操作=============================================================#末尾增加字段alter table mytable01add(ChkOpt char(6),ChkDate date);#中间插入字段：alter table mytable01 add (InputOpt date before InputDate);#修改字段名称：RENAME COLUMN table.oldcolumn， TO newcolumnALTER TABLE mytable01 RENAME COLUMN ChkOpt TO po_ChkOpt;ALTER TABLE mytable01 ALTER COLUMN po_ChkOpt char(250) not null;#修改字段类型：alter table mytable01 modify (BType char(2));alter table mytable01 modify (InputOpt char(6));#删除字段：alter table mytable01 drop (BType,InputOpt);#增加表的唯一约束：ALTER TABLE mytable01 ADD CONSTRAINT UNIQUE (Serno, Name);#要声明约束的名称，请更改上述语句：ALTER TABLE customerADD CONSTRAINT UNIQUE (lname, fname) CONSTRAINT u_cust#当您未指定新约束的名称时，数据库服务器会提供一个名称。

您可以在 sysconstraints 系统目录表中找到约束的名称。

#删除唯一约束：ALTER TABLE manufact DROP CONSTRAINT con_name;#如果在创建约束时没有指定名称，则数据库服务器会生成名称.SELECT constrname FROM sysconstraintsWHERE tabid = (SELECT tabid FROM systablesWHERE tabname = 'items');#更改表的锁定粒度ALTER TABLE manufact LOCK MODE SIZE (PAGE|ROW);#索引=================================================================#创建索引create unique index u_mytable01_001 on mytable01(Serno);create index i_mytable01_001 on mytable01(Name);create index i_mytable01_002 on mytable01(Name,BType);#删除索引drop index u_mytable01_001;drop index i_mytable01_002;#CLUSTER：使表的物理存放顺序按索引排列。

复合索引最多包含8个字段create CLUSTER index i_mytable01_003 on mytable01(BType);#去掉或加上CLUSTER属性alter index i_mytable01_003 to not CLUSTER;alter index i_mytable01_001 to CLUSTER;#视图＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝#创建视图create view v_user01 as select * from mytable01 where Ino >= 1;#删除视图DROP VIEW v_user01#创建删除同义名表=========================================================== CREATE SYNONYM synonym FOR table-name;DROP SYNONYM synonym;#synonym：同义名#table-name：表名称#事务＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝BEGIN WORKCOMMIT WORKROLLBACK WORK#镜像库Trigger 的操作=================================================#在Trigger 库中创建镜像用表，注意字段类型与原始库一致Create table ti_mytable01(Ino integer unique,Serno char(50),Name char(50),BType char(1),Blnc decimal(16,2),claimamt float,InputDate date,mir_alter_time datetime year to second,mir_alter_serial serial unique,mir_alter_type char(1)) extent size 16 next size 16 lock mode row;#创建原数据库中的Trigger 关联#创建(INS)插入Trigger数据库create trigger ti_mytable01_01insert on mytable01referencing new as newfor each row(insert into Trigger_clannt : ti_mytable01values(new.Ino,new.Serno,new.BType,new.Blnc,new.claimamt,new.InputDate,CURRENT,0,'I'));#创建(UPD)更新Trigger数据库create trigger ti_mytable01_02update on mytable01referencing old as old new as newfor each row(insert into Trigger_clannt : ti_mytable01 values(old.Ino,old.Serno,,old.BType,old.Blnc,old.claimamt,old.InputDate,CURRENT, 0, 'D'),insert into Trigger_clannt : ti_mytable01 values(new.Ino,new.Serno,,new.BType,new.Blnc,new.claimamt,new.InputDate,CURRENT, 0, 'U'));#创建(DEL)更新Trigger数据库create trigger ti_mytable01_03delete on mytable01referencing old as oldfor each row(insert into Trigger_clannt : ti_mytable01 values(old.Ino,old.Serno,,old.BType,old.claimamt,old.InputDate,CURRENT,0,'D'));drop trigger ti_mytable01_01;drop trigger ti_mytable01_02;drop trigger ti_mytable01_03;drop table mytable01;drop table ti_mytable01;#锁模式修改alter table tablename lock mode {row |page};{ TABLE "yw".ls row size = 2459 number of columns = 93 index size = 354 }额~~也没认真看~~你的表大于2KB了。

用行锁跟页锁使用的锁的的差距应该不大。

如果是IDS10的话，锁就不需要过多的考虑了。

9.40的话。

这个表最大是20万的锁（10万数据）不建议建大于1页的表dbaccess sysmaster<<!unload to database_name.txt select name from sysdatabases;!#查询===================================================================SELECTSELECT select_list FROM tab_name|view_nameWHERE conditionGROUP BY column_nameHAVING conditionORDER BY column_listINTO TEMP table_name查询语句。