firebird常用语句

分页写法小例

 SELECT FIRST  templateid,code,name FROM template ;

 SELECT FIRST  SKIP  templateid,code,name FROM template ;

 SELECT * FROM shop ROWS  TO ;  --firebird2.0支持这种写法

显示表名和表结构

　SHOW TABLES;

  SHOW TABLE tablename;

使用ISQL连接数据库

 firebird%92bin>isql -u sysdba -p masterkey

  SQL>CONNECT 'E:companyxmwsoftnewxmwsoftc2dbcts2.fdb';

  或

  SQL>CONNECT 'E:%92'

  CON>USER 'sysdba'

  CONT>PASSWORD 'masterkey';

更新字段注释

UPDATE RDB$RELATION_FIELDS

SET RDB$DESCRIPTION = '描述信息'

WHERE (RDB$RELATION_NAME = 'SHOP')

AND (RDB$FIELD_NAME = 'CREDIT_BUY')

显示字段注释

 SELECT RDB$FIELD_NAME,RDB$DESCRIPTION

 FROM RDB$RELATION_FIELDS

 WHERE (RDB$RELATION_NAME = 'SHOP')

 AND (DB$FIELD_NAME = 'CREDIT_BUY')

更新表注释

UPDATE RDB$RELATIONS

SET RDB$DESCRIPTION = '描述信息'

WHERE RDB$RELATION_NAME = 'TABLE_NAME';

查询所有的表和视图（包括系统表和系统视图）

SELECT RDB$RELATION_NAME

FROM RDB$RELATIONS;

查询所有的用户表和用户视图

 SELECT RDB$RELATION_NAME

 FROM RDB$RELATIONS

 WHERE RDB$SYSTEM_FLAG = ;

查询所有的用户表

  SELECT RDB$RELATION_NAME

  FROM RDB$RELATIONS

  WHERE RDB$SYSTEM_FLAG =

  AND RDB$VIEW_BLR IS NULL;

查所有用户表、用户视图所有字段及相关定义

  SELECT

    a.RDB$RELATION_NAME,

    b.RDB$FIELD_NAME,

    b.RDB$FIELD_ID,

    d.RDB$TYPE_NAME,

    c.RDB$FIELD_LENGTH,

    c.RDB$FIELD_SCALE

  FROM RDB$RELATIONS a

    INNER JOIN RDB$RELATION_FIELDS b

      ON a.RDB$RELATION_NAME = b.RDB$RELATION_NAME

    INNER JOIN RDB$FIELDS c

      ON b.RDB$FIELD_SOURCE = c.RDB$FIELD_NAME

    INNER JOIN RDB$TYPES d

      ON c.RDB$FIELD_TYPE = d.RDB$TYPE

  WHERE a.RDB$SYSTEM_FLAG =

    AND d.RDB$FIELD_NAME = 'RDB$FIELD_TYPE'

  ORDER BY a.RDB$RELATION_NAME, b.RDB$FIELD_ID;

查找某表的所有字段及相关定义

  SELECT

    A.RDB$FIELD_NAME,

    B.RDB$FIELD_TYPE,

    B.RDB$FIELD_LENGTH,

    B.RDB$FIELD_PRECISION,

    B.RDB$FIELD_SCALE

  FROM RDB$RELATION_FIELDS A, RDB$FIELDS B

  WHERE A.RDB$RELATION_NAME = 'tablename'

    AND A.RDB$FIELD_SOURCE = B.RDB$FIELD_NAME

  ORDER BY A.RDB$FIELD_POSITION;

查找某表的主键定义字段

  SELECT A.RDB$FIELD_NAME

  FROM RDB$INDEX_SEGMENTS A, RDB$RELATION_CONSTRAINTS B

  WHERE B.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY'

    AND B.RDB$RELATION_NAME = 'tablename'

    AND A.RDB$INDEX_NAME = B.RDB$INDEX_NAME

  ORDER BY A.RDB$FIELD_POSITION;

查找某表的外键定义

  SELECT

    r1.RDB$CONSTRAINT_NAME,

    rind.RDB$FIELD_NAME,

    r2.RDB$RELATION_NAME

  FROM

    RDB$RELATION_CONSTRAINTS r1,

    RDB$RELATION_CONSTRAINTS r2,

    RDB$REF_CONSTRAINTS ref,

    RDB$INDEX_SEGMENTS rind

  WHERE r1.RDB$RELATION_NAME = 'tablename'

    AND r1.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY'

    AND r1.RDB$CONSTRAINT_NAME = ref.RDB$CONSTRAINT_NAME

    AND ref.RDB$CONST_NAME_UQ = r2.RDB$CONSTRAINT_NAME

    AND r1.RDB$INDEX_NAME = rind.RDB$INDEX_NAME;