informix的常用SQL语句

1、创建数据库
      eg1. 创建不记录日志的库testdb，参考语句如下：

CREATE   DATABASE  testdb;

      eg2. 创建带缓冲式的记录日志的数据库testdb（SQL语句不一定在事务之中，拥有者名字不被用于对象的解析），参考语句如下：

CREATE   DATABASE  testdb  WITH  BUFFERED  LOG ;

      eg3. 创建无缓冲式的记录日志的数据库testdb（SQL语句不一定在事务之中，拥有者名字不被用于对象的解析），参考语句如下：

CREATE   DATABASE  testdb  WITH   LOG ;

      eg4. 创建ANSI的数据库（记录日志时无缓冲，SQL总在事务之中，拥有者名字被用于对象的解析），参考语句如下：

CREATE   DATABASE  testdb  WITH   LOG  MODE ANSI;

2、创建普通数据表
       普通数据表又被称为持久数据表，它在system catalog里注册。一个普通数据表可对多个session和connection。创建时可以指定dbspace。
       eg1、如下语句创建了一个集团信息表cti_vccinfo：

create   table  cti_vccinfo(
  vccid      CHAR ( 6 )      not   null ,
  vccname    VARCHAR ( 255 ),
  effective  INTEGER       default   0   not   null ,
  agentmax   INTEGER       default   0   not   null ,
  ivrmax     INTEGER       default   0   not   null ,
  updatekey  VARCHAR ( 30 ),
   primary   key  (vccid)  constraint  PK_CTI_VI
);

3、创建临时数据表
      临时数据表不在system catalog里注册。一个临时数据表只对对应的某个session或connection可见，在对应的session或connection结束时被自动清除。如果dbspace存在的话，临时数据表将建于临时dbspace中。缺省情况下，是没有日志的。临时数据表支持索引。
      eg1：如下创建一个customer_temp的表，语句如下：

CREATE   TEMP   TABLE  customer_temp (
    num SERIAL  NOT   NULL ,
    name  CHAR ( 15 ),
    create_time  DATETIME   YEAR   TO  FRACTION( 3 )
);

     eg2：也可以将正式表中customer中的数据通过select......into temp语句将数据导入到临时表中，如下实例创建了一个正式的表customer，并插入了三条数据，接着通过select....into temp语句将这个正式表中的数据导入到临时表customer_temp。
      首先，创建customer普通数据表，建表语句如下：

CREATE   TABLE  customer (
    num SERIAL  NOT   NULL ,
    name  CHAR ( 15 ),
    create_time  DATETIME   YEAR   TO  FRACTION( 3 )
);

    接着，在普通数据表customer中插入三条记录，语句如下：

insert   into  customer (name, create_time)  values ( ' amigo ' ,  ' 2010-11-17 15:41:00 ' );
insert   into  customer (name, create_time)  values ( ' xiexingxing ' ,  ' 2010-11-17 15:42:00 ' );
insert   into  customer (name, create_time)  values ( ' amigoxie ' ,  ' 2010-11-17 15:43:00 ' );

     最后，通过select......into temp语句将普通数据表customer中的数据导入到临时表customer_temp中（注意：需要保证customer_temp表不存在，操作成功后，customer_temp中的字段为select出的字段），参考语句如下所示：

SELECT  num, name, create_time  FROM  customer  into   TEMP  customer_temp;

4、创建主键约束
     1）主键约束定义在一个数据列或一组数据列上；
     2）主键的值是不允许重复的；
     3）主键的值不允许为NULL。
     在2中的实例，创建了cti_vccinfo表，并指定了vccid为其主键，并将其取名为PK_CTI_VI，以方便进行删除操作。
     接下来看一个使用复合主键的实例，如下语句创建了cti_humantaskgroup表，该表的serviceid和agentid组成联合主键，首先看下该表的建表语句：

create   table  cti_humantaskgroup (
    serviceid   VARCHAR ( 30 )   not   null ,
    agentid     VARCHAR ( 30 )   not   null ,
    priority    INTEGER        default   0   not   null ,
    updatekey   VARCHAR ( 30 )
);

     如下的语句为该表的serviceid和agentid创建了唯一索引：

create   unique   index  Index_CTI_HTG  on  cti_humantaskgroup(
    serviceid   ASC ,
    agentid     ASC
);

5、创建引用约束
   　1）一个数据表的主键可以被同一个数据表或其它数据库表使用。主键被引用的数据表被称为父表，引用了附表的主键的数据表被称为子表；
　   2）如果在定义引用约束时使用了ON DELETE CASCADE，当把父表的数据行删除时，子表的相关数据行也会被自动删除。
       在4中的实例中，cti_humantaskgroup表中的serviceid为cti_humantask中的主键，引用约束可在创建表的时候指明，也可以创建完成后通过alter语句创建，参考语句如下：

alter   table  cti_humantaskgroup
    add   constraint   foreign   key  (serviceid)
       references  cti_humantask (serviceid)  on   delete   cascade
       constraint  FK_CTI_HTG_HT;

      读者可以注意点，如上语句加上了on delete cascade，表示在删除cti_humantask表时，数据库系统会自动删除子表cti_humantaskgroup中serviceid与之相同的数据。
6、检查约束
      定义了检查约束后，数据库将数据赋给一个数据列之前将根据检查约束检查数据是否满足条件。
      例如创建一个student表，该表有id（学号）、name（姓名）、age（年龄）和birthday（出生日期）4个字段，age必须在5到35之间，则在创建该表时需要添加检查约束，建表语句参考如下：

create   table  student  (
    id         VARCHAR ( 10 )      not   null ,
    name       VARCHAR ( 10 )      not   null ,
    age        INTEGER           default   0   not   null   check  (age  between   5   and   35 ),
    birthday   VARCHAR ( 8 )
);

     若通过如下语句插入一条不满足age的检查约束的数据：

insert   into  student  values ( ' 1234 ' ,  ' amigo ' ,  40 ,  ' 19821121 ' );

     运行后会出现如下提示信息：

530 :  Check   constraint  (ines.c2209_13601) failed.  

7、创建视图
     1）创建视图时使用select语句；
     2）视图在system catalog里注册；
     3）视图数据不被存储在磁盘上；
     4）对于一些数据表，可为不同的用户建立不同的视图；
     5）可配置存取权限。
     例如，创建一个student_age的视图，查出age在20～25的学生，语句如下：

CREATE   VIEW  student_age
(id, name, age, birthday)
      AS   SELECT  id, name, age, birthday  FROM  student  WHERE  age >= 20   and  age <= 25

     若要查询视图中的数据，例如得到student_age视图中的数据，可通过select语句进行查询，参考如下：

select   *   from  student_age;   

     若要删除student_age视图，语句如下：

drop   view  student_age;

8、查询语句
     我们使用select语句从数据库中查询数据，select语句的使用语法如下所示：

SELECT  字段列表(各个字段之间用英文逗号隔开)
    FROM  表列表(多个表之间用英文逗号隔开)
       [ WHERE 查询条件 ]
          [ GROUP BY 字段列表 ]
          [ HAVING 条件 ]
          [ ORDER BY 字段列表 ]
          [ INTO TEMP 临时表的名称 ]

      例如查询student表中的所有数据，语句参考如下：

select   *   from  student;

     查询student表中的记录，语句参考如下：

select   count ( * )  from  student;

     查询student表中的name和age字段，语句参考如下：

select  name, age  from  student;

     在查询语句中，可以使用关系运算符，可使用的关系运算符如下：
     1）=
     例如查询出student表中姓名为amigo的字段，语句参考如下：

select   *   from  student  where  name = ' amigo ' ;

     2）!=或<>
     例如查询出年龄不为23的记录，语句参考如下：

select   *   from  student  where  age != 23 ;

     3）> 
     例如查询出年龄大于23的记录，语句参考如下：

select   *   from  student  where  age > 23 ;

     4）>=
     大于等于，与大于使用方法类似。
     5）<
     小于，与大于使用方法类似。
     6）<=
     小于等于，与大于使用方法类似。
     在where语句中，可使用的关键字如下所示：
     1）AND（逻辑与）
     例如，当需要在student表中查出name为amigo，并且学号为1000的记录，此时可以使用AND，参考语句如下：

select   *   from  student  where  name = ' amigo '   and  id = ' 1000 ' ;

      2）OR（逻辑或）
      例如，需要查询name为amigo，或者name为xingxing的记录，因为是或的关系，所以可以使用OR，参考语句如下：

select   *   from  student  where  name = ' amigo '   or  name = ' xingxing ' ;

      3）[NOT] BWTWEEN（[不]在......之间）
      例如，查找student表中age在24和30之间的记录的id、name和age字段，参考语句如下：

select  id, name, age  from  student  where  age  between   24   and   30 ;

      4）[NOT] IN（[不]在....中）
      [NOT] IN后可以是一个具体的值，也可以是一个子查询。
      例如，查找student表中的name不在“amigo”和“xingxing”的记录的id和name字段，参考语句如下：

select  id, name  from  student  where  name  not   in  ( ' amigo ' ,  ' xingxing ' );

      5）IS [NOT] NULL：[不]是NULL
      例如需要查询出student表中birthday不为空的记录，参考语句如下：

select   *   from  student  where  birthday  is   not   null ;

      6）[NOT] MATCHES：[不]匹配
      “?”表示匹配单个字符，“*”表示0到正整数个字符。
      例如，查找总共为5个字符，而且后4个字符为migo的记录，参考语句如下：

select  id, name  from  student  where  name matches  ' ?migo ' ;

      例如，查找student表中以go结尾的任意长度的记录，参考语句如下：

select   *   from  student  where  name matches  ' *go ' ;

       7）[NOT] LIKE：[不]匹配
       使用方法与[NOT] MATCHES类似，但是是使用“_”表示单个字符，“%”表示0到正整数个字符。
       GROUP BY
       我们可以使用group by对查询结果进行分组。分组后我们可以得到各个分组的统计消息，例如平均值、总和、数据行数等。
       例如，需要根据detail（详细情况分类）分组查询CTI_CallStat表中taskid为000001200002111864的记录，并将每种detail的数量显示出来，语句参考如下：

select  detail,  count ( * )  as  ratio  from  CTI_CallStat  where  taskid = ' 000001200002111864 '   group   by  detail

       CASE子句
       我们可以使用CASE表达式对返回值进行转换，CASE表达式的语法如下：

CASE  (expr)
WHEN  expr1  THEN  result1
WHEN  expr2  THEN  result2

ELSE  result_else
END

       上面的CASE表达式的意思是：
       当expr为expr1时，返回result1；
       当expr为expr2时，返回result2；
       ...
       当expr为其它情况时，返回result_else.
       例如查询student表，当age为1时，显示为too little，100时，显示为too old，其余的情况显示为normal，参考语句如下：

select  id, name, age, 
         case  age
     when   1   then   ' too little '
     when   100   then   ' too old '
     else   ' normal '
         end
        ageinfo
from  student;

      DECODE
      我们可以使用DECODE函数对返回值进行转换，DECODE函数的语法如下：

DECODE (expr,
expr1, result1,
expr2, result2,
…
result_else)

      上面的DECODE函数的意思搜：
      当expr为expr1时，返回result1；
      当expr为expr2时，返回result2；
      ...
      当expr为其它情况时，返回result_else。
      该函数能达到CASE子句类似的功能，例如达到前面的功能，可使用如下的DECODE语句：

SELECT  id, name, age, 
       DECODE (age,
           1 ,  ' too little ' ,
           100 ,  ' too old ' ,
            ' normal ' )
     ageinfo
     FROM  student;

      UNION和UNION ALL
      如果两个或多个select语句的结果相似，我们可以用“union”或“union all”把这些select语句合并起来。“union”和“union all”的区别是：“union”将去掉结果中的非第一次出现的值，而“union all”将保留结果中的非第一次出现的值。
      表连接的语法
      我们可以使用两种方式进行数据表连接：
      1）数据表之间使用逗号，连接条件前使用WHERE；
      2）数据表之间使用JOIN，连接条件前使用ON。
      第一种方式的参考实例如下：

SELECT  order_num, order_time, c.customer_num
         FROM  customer c , orders o
         WHERE  c.customer_num  =  o.customer_num;

       第二种方式的参考实例如下：

SELECT  order_num, order_time, c.customer_num
       FROM  customer c  JOIN  orders o
       ON  c.customer_num  =  o.customer_num;

       外连接
       例如，有两个表，员工表和项目表，员工可以负责项目，项目也可以有负责人（员工）。
       若想知道：那个员工负责哪个项目，哪些员工不负责项目，可以使用左外连接，参考语句如下：

select  e.employee_num, employee_name, project_num, project_name
       from  employee e  LEFT   OUTER   JOIN  project p  ON  e.employee_num = p.employee_num

       若想知道：哪个员工负责哪个项目，哪些项目没有人负责，可以使用右外连接，参考语句如下：

select  e.employee_num, employee_name, project_num, project_name
       from  employee e  RIGHT   OUTER   JOIN  project p  ON  e.employee_num = p.employee_num

       若想知道：哪个员工负责哪个项目，哪些员工不负责项目，哪些项目没有人负责，可以使用全连接，参考语句如下：

select  e.employee_num, employee_name, project_num, project_name
       from  employee e  FULL   OUTER   JOIN  project p  ON  e.employee_num = p.employee_num

      子查询
      子查询分两种：相关子查询和不相关子查询。在相关子查询中，子查询中涉及到父查询的数据列；在不相关子查询中，子查询中不涉及到父查询的数据列。
      相关子查询实例：

select   *   from  customer
       where   exists
           ( select   *   from  vip 
                 where  vip.customer_num  =  customer.customer_num);

      不相关子查询实例：

select   *   from  project
       where  employee_num =
           ( select  employee_num  from  employee  where  employee_name = ' amigo ' );

      在很多情况下，我们可以将相关字查询转换为表连接，这样数据库引擎有可能更方便的得到更优的查询计划，从而使SQL语句的执行时间更少。例如可将上面的相关子查询实例转换为：

select  customer. *   FROM  customer, vip
       where  customer.customer_num = vip.customer_num;

9、插入语句
      我们可以使用insert语句往数据表中插入数据行。
      如果各值按序赋给数据表中的所有数据列，则不需要指明数据列，例如往student表中插入数据，参考语句如下：

insert   into  student  values ( ' 1000 ' ,  ' amigo ' ,  27 ,  ' 19821121 ' );

      如果是将值赋给指定数据列，则需指定数据列，例如只插入student表中的前三个字段，若使用如下的语句：

insert   into  student  values ( ' 1005 ' ,  ' amigo ' ,  27 );

     此时会报错提示值的个数不正确，错误提示如下所示：

236 :  Number   of  columns  in   INSERT  does  not  match  number   of   VALUES .  

     这时，需要指定数据列，参考语句如下：

insert   into  student (id, name, age)  values ( ' 1005 ' ,  ' amigo ' ,  27 );

     可以在insert语句中嵌入select语句，从而将从一个或多个数据表查询来的数据插入到目标数据表。
     例如将student_bak表中age大于25的数据插入到student表中，参考语句如下：

insert   into  student
      select  id, name, age, birthday
          from  student_bak  where  age > 25 ;

10、更新语句
      可以使用update语句为数据表更新数据行。
      例如想将student中的id为1000的记录的name字段更新为amigo，age字段更新为28，语句参考如下：

update  student  set  name = ' amigoxie ' , age = 28   where  id = ' 1000 ' ;

     使用如下的写法也是等效的：

update  student  set  (name, age) = ( ' amigoxie ' ,  28 )  where  id = ' 1000 ' ;

11、删除语句
     可以使用delete语句从数据表中删除数据行。
     例如，删除student表中的所有数据，参考语句如下：

delete   from  student;

     例如，删除student表中id为1001的数据，参考语句如下：

delete   from  student  where  id = ' 1001 ' ;

     例如，删除student表中以go结尾的记录，参考语句如下：

delete   from  student  where  name matches  ' *go ' ;