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、创建普通数据表

create table test1 (name varchar(20),age integer);

// informix 获取用户下的所有database
select name from sysmaster:sysdatabases;

// informix 获取test这个database下的表
select dbsname,tabname from sysmaster:systabnames where dbsname='testdb';

// informix 获取所有database下的表
select * from sysmaster:systabnames ;

// informix 获取当前连接的database下用户自己创建的表
select * from  systables where  tabtype='T' and tabid>99;

// informix 获取所有的表
select * from systables   

// 切换库
DATABASE testmc; 
DATABASE sysmaster; 


DATABASE;

// 查看库
SELECT name FROM sysdatabases;

// 查看当前的库
SELECT DBINFO('dbname') AS current_database;

// 删除库
DROP DATABASE testdb;

       普通数据表又被称为持久数据表，它在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';