informix SQL汇总(包括效率分析)

1、informix SQL汇总每个数据库管理系统（DBMS）都有其自己的数据处理语言（DML），但所有DML都基于一种语言SQL语言结构化查询语言（SQL），其发音为“sequel”或“S-Q-L”。目前SQL的前身是E.F.Codd博士70年代发明的。第一个实现是在76年，称为sequel。而SQL首先被采用是在IBM的System R项目中。86年10月由ANSI确定为正式的关系查询语言标准。ISO在对其修改后在90年制定为国际工业标准。无论进行何种数据库学习，SQL语言都是必学内容。在我国数据库语言SQL标准（GB12991）中规定了两个数据库语言的语法与语义：模式定义语言（SQL-DDL

2、），描述SQL数据库的结构与完整性的约束； 数据操纵语言（SQL-DML），描述操作数据库的执行语句 在本文章中涉及以上两个中的主要部分，（为叙述方便，以下对其统称为SQL），由于SQL在嵌入C时表现略有不同，所以下面均以非嵌入时的SQL进行。另外本文章也不是SQL的入门教材，阅读者应学习并实际用SQL操作过某种数据库。如果你系统学习过数据库理论，又能熟练操作INFORMIX关系数据库，甚至从事过有关数据库程序的设计，还那么作者在此恭喜你了。如果你尚未系统学习过数据库理论，并对INFORMIX数据库了解甚少，建议你先阅读有关的文档。inforrmixx SQQL汇总总【主要SQQL语句句详解】

3、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/

4、log/customer.log”;DATABASE databse-name EXCLUSIVE 选择数据库。database_name：数据库名称。EXCLUSIVE：独占状态。存取当前目录和DBPATH中指定的目录下的数据库，事务中处理过程中不要使用此语句。例：dtabase customerdb;CLOSE DATABASE关闭当前数据库。database_name：数据库名称。此语句之后，只有下列语句合法：CREATE DATABASE； DATABASE； DROP DATABSE； ROLLFORWARD DATABASE；删除数据库前必须使用此语句。例：close databa

5、se;DROP DATABASE database_name删除指定数据库。database_name：数据库名称。 用户是DBA或所有表的拥有者；删除所有文件，但不包括数据库目录；不允许删除当前数据库（须先关闭当前数据库）；事务中处理过程中不能使用此语句，通过ROLLBACK WORK 也不可将数据库恢复。例：drop databse customerdb;CREATE TEMP TABLE table-name (column_name datatype NOT NULL, )IN “pathname”创建表或临时表。table-name ：表名称。column_name：字段名称。dat

6、a-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;ALTER TABLEALTER TABLE table-name ADD (newcol_name newcol_t

7、ype 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权限，或被授权；事务中处理过程中不要使用

8、此语句。例：alter table useradd ( c6 char(20) before c5);RENAME TABLE oldname TO newname修改表名。oldname：原名称。newname：新名称。RENAME使用者是表的拥有者或拥有DBA权限，或被授权；事务中处理过程中不要使用此语句。 例：rename user to bbb;DROP TABLE table-name删除表。table-name：表名称。删除表意味着删除其中所有数据、各字段上的索引及对表的赋权、视图等；用户不能删除任何系统目录表；语句使用者是表拥有者或拥有DBA权限，事务中处理过程中不要使用此语句。

9、RENAME COLUMN table.oldcolumn， TO newcolumn修改字段名。table.oldcolumn：表名及原字段名称newcolumn：新字段名称。语句使用者是表的拥有者或拥有DBA权限或有ALTER权限的用户，事务中处理过程中不要使用此语句。例：rename column user.c6 to c7;CREATE VIEW view-name column-listCREATE VIEW view-name column-list AS select_statement WITH CHECK OPTION创建视图。view-name：视图名称。column-li

10、st：字段列表。select_statement：SELECT语句。以下语句不使用视图：ALTER TABLE，DROP INDEX，ALTER INDEX，LOCK TABLE，CREATE INDEX， RENAME TABLE；视图将延用基表的字段名，对表达式等虚字段和多表间字段重名必须指明标识其字段名；若对视图中某些字段命名，则所有字段都必须命名；视图中数据类型延用基表中的数据类型，虚字段起诀于表达式；不能使用ORDER BY和UNION子句；对视图中所有的字段要有SELECT权限；事务中处理过程中使用此语句，即使事务回滚，视图也将建立，不能恢复。例：create view v_use

11、r as select * from user where c1 = “B1”;DROP VIEW view-name删除视图。view-name：视图名称。用户可删除自己建立的视图；视图的后代视图也被删除；事务中处理中不要使用此语句。例：drop view v_user;CREATE INDEX CREATE UNIQUE/DISTINCT CLUSTER INDEX index_name ON table_name(column_name ASC/DESC,) 创建索引。index_name：索引名称。table_name：表名称。column_name：字段名称。UNIQUE/DISTI

12、NCT：唯一索引。CLUSTER：使表的物理存放顺序按索引排列。ASC/DESC：升序或降序，缺省升序。语句执行时，将表的状态置为EXCLUSIVE；复合索引最多包含8个字段，所有字段长度和不得大于120字节；事务中处理过程中使用此语句，即使事务回滚，索引将建立，不能恢复。例：create cluster index ix_user on user(c5);ALTER INDEX index-name TO NOT CLUSTER修改索引性质。index-name：索引名称。TO NOT CLUSTER：去掉或加上CLUSTER属性。语句执行时，将表的状态置为EXCLUSIVE；事务中处理过程

13、中使用此语句，即使事务回滚，索引性质将改变，不能恢复。例：alter index ix_user to not cluster;DROP INDEX index-name删除索引。index-name：索引名称。语句使用者是索引的拥有者或拥有DBA权限，事务中处理过程中不要使用此语句，否则事务无法恢复。例：drop index ix_user;CREATE SYNONYM synonym FOR table-name创建同义名。synonym：同义名table-name：表名称数据库的创建者可以使用同义名；没有赋予同义名权限的用户不能使用同义名；同义名不能和表名相同；事务中处理过程中不要使用此

14、语句。例：create synonym user_alias for user;DROP SYNONYM synonym删除同义名。synonym：同义名可以删除自己建立的同义名；事务中处理过程中不要使用此语句，否则无法恢复。例：drop synonym user_alias;UPDATE STATISTICS FOR TABLE table-name更新数据库的统计数字。table-name：表名称此语句仅作用于当前数据库；可提高查询效率；只有执行此语句，才改变统计数据。例：update statistics for table user;GRANTT DDBA|RESSOURRCE|CON

15、NNECCT TO PUUBLIIC|uuserr-liist 授权权命令。PUBLIC|user-list：全部或指定的用户。三种权限居且仅居其一，事务处理过程中不要执行GRANT语句。例：grant resource to pulbic;GRANT tab-privilege ON table-name TO PUBLIC|user-list WITH GRANT OPTION 授表级权限。tab-privilege：表级权限。table-name：表名称。PUBLIC|user-list：全部或指定的用户。WITH GRANT OPTION：表示被授权用户有否权限进行二次授权。用户可以在

16、自己建立表达式或被WITH GRANT OPTION准许的表中进行赋权；限定越多的权限优先级越高。例：grant update(c1,c6) on user to dick with grant option;附（INFORMIX的权限）(1) 数据库的权限（控制对数据库的访问以及数据库中表的创建和删除） DBA权限：全部权利，修改系统表，建立和删除表与索引、增加和恢复表数据，以及授予其他用户数据库权限等；RESOURCE权限：允许对数据库表中的数据进行存取，建立永久性表以及索引。CONNECT权限：只允许对数据库表中的数据进行存取，建立和删除视图与临时表。(2)表级权限（对表的建立、修改、检

17、索和更新等权限） ALTER：更改权限DELETE：删除权限INDEX：索引权限INSERT：插入权限SELECT (cols)：指定字段或所有字段上的查询权限，不指明字段缺省为所有字段。UPDATE (cols) ：指定字段或所有字段上的更新权限，不指明字段缺省为所有字段。ALL PRIVILEGES：以上所有表级权限REVOKE DBA|RESOURCE|CONNECT FROM PUBLIC|user-list 收权命令。PUBLIC|user-list：全部或指定的用户。三种权限居且仅居其一，事务处理过程中不要执行GRANT语句。例：revoke resource from john;

18、REVOKE tab-privilege ON table-name FROM PUBLIC|user-list收表级权限。tab-privilege：表级权限。table-name：表名称。PUBLIC|user-list：全部或指定的用户。WITH GRANT OPTION：表示被授权用户有否权限进行二次授权。用户只能取消由其本人赋予其他用户的表级存取权限；不能取消自己的权限，对SELECT和UPDATE作取消时，将取消所有表中字段的SELECT 和UPDATE权限。例；revoke update on user from dick;LOCK TABLE table-name IN SHA

19、RE|EXCLUSIVE MODE 记录级加锁和表级加锁或文件加锁。table-name：表名称。SHARE：允许读表中数据，但不允许作任何修改EXCLUSIVE：禁止其他任何形式访问表每次只能对表琐定一次；事务处理过程中，BEGIN WORK后立即执行LOCK TABLE以取代记录级加锁，COMMIT WORK和ROLLBACK WORK语句取消所有对表的加锁；若没有事务处理，锁将保持到用户退出或执行UNLOCK为止。例：lock table user in exclusive mode;UNLOCK TABLE table-name 取消记录级加锁和表级加锁或文件加锁。table-name

20、：表名称。例：unlock user;SET LOCK MODE TO NOT WAIT 改变锁定状态。TO NOT：等待解锁，有可能被死锁或不等待并提示错误信息，表示此记录被锁，缺省值。访问一个EXCLUSIVE状态下的记录，将返回一个错误。START DATABSE db_name WITH LOG IN “pathname” 启动事务处理。“pathname”：事务处理日志文件。执行该语句前，需要先关闭当前数据库。例；clost database;start databse customer with log in “/usr/john/log/customer.log”;BEGIN W

21、ORK 开始事务。例：begin work;COMMIT WORK 提交（正常结束）事务。例：commit work;ROLLBACK WORK 回滚（非正常结束）事务。例：rollback work;SELECT 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：视图名称。condi

22、tion：查询条件，可使用BETWEEN、IN、LIKE、IS NULL、LIKE、MATCHES、NOT、AND、OR、=、!=或;、;、;= 、=、;= this/date AND datecol ;&2 exit 1 fi isql $DATABASE - ;= 100 order by tabname; EOFexit 0 也许你已经注意到exit的返回值对不同的isql不是都相同，因此这样作不是很可靠，代替通过$?来检查返回值的更好的主意是将标准错误重定向到一个文件中，然后在这个文件中grep “error。例如：# Generate the data isql -qr ;stage

23、.rep 2;$stage.err database $database; select . ! # Check for errors if grep -i error $stage.err ;/dev/null then.error_handler. fi为什么不能对一个计算产生的字段创建视图？问题：为什么我不能创建视图：CREATE VIEW tst AS SELECT ship_charge - totval cout FROM orders WHERE ship_charge ; 0; 回答：你应该这样写：CREATE VIEW tst (cout) AS SELECT ship_ch

24、arge - totval FROM orders WHERE ship_charge ; 0;如何只select 出数据库中的部分数据（例如10）。问题：如果你想要得到一个select 语句正常返回的数据的一部分，例如：SELECT firstname, lastname, city, state FROM bigdatabase WHERE state = TX回答：有一个方法可以返回一个近似值，只需要在where后加上：AND rowid=(trunc(rowid/x)*x) 其中的x代表你想要返回的总的记录的1/x。需要说明的是，这种方法只能返回一个近似的值，并且表中的数据在物理上分布

25、的连续性。如何创建一个表结构和永久表完全一致的临时表。 例如：CREATE TEMP TABLE mytemp (prodno LIKE dno desc LIKE product.desc) 你可以使用如下的语句：SELECT prodno, desc FROM product WHERE ROWID = -1 INSERT INTO TEMP mytemp如何更改sseriial类类型下一一次插入入操作产产生的值值？我们们知道sseriial类类型的字字段是系系统自动动增加的的整数字字段，那那么怎样样能控制制下一个个serriall类型字字段的值值。想要要下一个个插入

26、的的serriall类型的的值比默默认值大大，可以以用：AALTEER TTABLLE ttabnnamee MOODIFFY( serr_cool_nnamee SEERIAAL(neww_sttartt_nuumbeer)想要下下一个插插入的sseriial类类型的值值比默认认的值要要小，首首先需要要将seeriaal类型型重新置置为1：INSSERTT INNTO tabble (seeriaal_ccoluumn) VAALUEES (2144748836447); INNSERRT IINTOO taablee (sseriial_collumnn) VVALUUES (0); -

27、重重新从11开始! .然然后执行行ALTTER TABBLE(就像上上面的做做法一样样）。如如何在发发生错误误的时候候终止ssql脚脚本的执执行？如如果你创创建了一一个sqql脚本本，并且且在UNNIX命命令行中中使用以以下的方方式来执执行这个个脚本：$ ddbacccesss ; ;这时时，脚本本中的所所有的ssql语语句都会会被执行行，即使使其中的的一个ssql语语句发生生了错误误。例如如，如果果你脚本本中为如如下的语语句：BBEGIIN WWORKK; IINSEERT INTTO hhisttoryy SEELECCT * FRROM currrennt WWHERRE mmontth

28、 = 111; DDELEETE FROOM ccurrrentt WHHEREE moonthh = 11; COOMMIIT WWORKK; 如如果INNSERRT语句句失败了了，DEELETTE语句句仍旧会会继续执执行。直直到coommiit wworkk。这样样的后果果可能会会很严重重。你可可以通过过设置一一个环境境变量来来防止这这种情况况的发生生。DBBACCCNOIIGN=1如何何设置ddeciimall字段运运算结果果的精度度？假定定你使用用dbaacceess或或者issql，设设置环境境变量DDBFLLTMAASK=6 就就可以设设置为小小数点后后面6位位，比如如：CRREA

29、TTE TTEMPP TAABLEE t ( ccol_a DDECIIMALL(8,4) NOTT NUULL, cool_bb DEECIMMAL(8,44) NNOT NULLL, coll_c DECCIMAAL(88,4) NOOT NNULLL ); INNSERRT IINTOO t VALLUESS(1.23445, 3.445677, 55.67789); SSELEECT (cool_aa + coll_b) / coll_c AS vallue FROOM tt; vvaluue 00.82260775 如如果DBBFLTTMASSK=77vallue 0.8826007

30、555为什么么我们有有时会遇遇到syysprrocpplann表被锁锁的提示示？syysprrocpplann表是ssysmmastter库库中的一一个表，其其中记录录存储过过程经过过优化的的查询计计划。每每当查询询树中的的数据库库对象有有任何结结构上的的变化，这这个查询询计划就就会自动动更新。如如果对查查询树中中存在的的任何表表有uppdatte sstattistticss操作，也也会自动动更新查查询计划划。在查查询计划划更新的的时候，会会对syyspoorcpplann表中的的相关记记录加锁锁。注意意：每次次你对一一个表更更新统计计的时候候，也同同时会更更新于这这个表相相关的存存储过程程

31、，即UUPDAATE STAATISSTICCS FFOR PROOCEDDUREE 。你你可以作作的另外外一件事事情就是是：在存存储过程程中使用用SETT OPPTIMMIZAATIOON LLOW，这这会让优优化器在在存储过过程运行行的时候候不会试试图去重重新优化化它。否否则存储储过程通通常都会会被重新新优化一一次。如如何删除除掉表中中重复的的记录？假设“kkeyccol”字字段的值值唯一，而而且没有有对表进进行分片片，并且且没有其其它的人人正在删删除ssomeetabble中的记记录，你你可以执执行如下下的SQQL：ddeleete froom ssomeetabble as a wwh

32、erre rrowiid ; (seelecct mmin(rowwid) frrom sommetaablee whheree keeycool = a.keyycoll)如果果这个表表使用表表分片，rrowiid不存存在，你你还可以以用如下下的方法法：BEEGINN WOORK; SEELECCT DDISTTINCCT * FRROM Tabble INTTO TTEMPP Teemp11; DDELEETE FROOM TTablle WWHERRE 11 = 1; INSSERTT INNTO Tabble SELLECTT * FROOM TTempp1; COMMMITT WOO

33、RK; 对于于规模较较小或中中等的表表，并且且你有足足够的存存储空间间来存储储整个的的临时表表的时候候，这种种方法通通常十分分有效。如何加快SELECT COUNT(DISTINCT)的速度。通常“SELECT COUNT(DISTINCT)”这样的操作要花费比较长的时间，如果你这样作：SELECT UNIQUE xxx INTO TEMP XXX 然后再SELECT COUNT(*) FROM TEMP XXXNULL值值的使用用 RDDSQLL中字段段缺省值值为空；并且对对数值型型的0和和空值，以以及字符符型的空空白和空空值区别别对待。数值表达式中某个变为空，则整个表达式值为空；聚合函数中

34、，对空值忽略不计，若全部为空值，除COUNT（*）返回0外，其余返回空值。布尔表达式中，结果可能为“未知”（见下表）。如TRUE AND NULL 结果为 “未知”，对“未知”结果在RDSQL中看作不符合查询条件。and T F ? or T F ? not T T F ? T T T T T F F F F ? F T F ? F T ? ? F ? ? T ? ? ? ? 结合上表，分析下列子句 ，其中n1=20；n2为空；n3=30。结果如右。where n1*n2 1000 and n3 = 30; 结果：不符合查询条件where n1*n2 ;1。表一：id d1 fee费用（分）1

35、 2000-01-24 1001 2000-04-24 1002 2000-02-24 2002 2000-06-24 2003 2000-04-24 4004 2000-04-24 4005 2000-05-24 5006 2000-06-24 6007 2000-09-24 9008 2000-11-24 1100表二：id 1月份费用 2月份费用 12月份费用1 100 0 0 100 0 0 0 0 0 0 0 0 2 0 200 0 0 0 200 0 0 0 0 0 0 3 0 0 0 400 0 0 0 0 0 0 0 0 4 0 0 0 400 0 0 0 0 0 0 0 0

36、5 0 0 0 0 500 0 0 0 0 0 0 0 6 0 0 0 0 0 600 0 0 0 0 0 0 7 0 0 0 0 0 0 0 0 900 0 0 0 8 0 0 0 0 0 0 0 0 0 0 1100 0 SQL语句：create table t3(id smallint,d1 datetime year to day,fee int);insert into t3 values(1,2000-01-24, 100);insert into t3 values(1,2000-04-24, 100);insert into t3 values(2,2000-02-24, 20

37、0);insert into t3 values(2,2000-06-24, 200);insert into t3 values(3,2000-04-24, 400);insert into t3 values(4,2000-04-24, 400);insert into t3 values(5,2000-05-24, 500);insert into t3 values(6,2000-06-24, 600);insert into t3 values(7,2000-09-24, 900);insert into t3 values(8,2000-11-24, 1100);create table t4 旋转矩阵(m_code smallint,y1 smallint,y2 smallint,y3 smallint,y4 smallint,y5 smallint,y6 smallint,y7 smallint,y8 smallint,y9 smallint,y10 smallint,y11 smallint,y12 smallint);insert into t4 values(1, 1,0,0,