Firebird的SQL语法



	 

	一、分页：

	  SELECT FIRST 10 templateid,code,name FROM template ;

	  SELECT FIRST 10 SKIP 10 templateid,code,name FROM template ;

	  SELECT * FROM shop ROWS 1 TO 10;  --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 = 0;

	 

	九、查询所有的用户表

	  SELECT RDB$RELATION_NAME

	  FROM RDB$RELATIONS

	  WHERE RDB$SYSTEM_FLAG = 0

	    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 = 0

	    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;