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
show tables;
show table tablename;
三、
D:\firebird2\bin>isql -u sysdba -p masterkey
connect "E:\company\xmwsoft\newxmwsoft\c2\db\cts2.fdb";
四、--更新字段注释
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
(RDB$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
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
show tables;
show table tablename;
三、
D:\firebird2\bin>isql -u sysdba -p masterkey
connect "E:\company\xmwsoft\newxmwsoft\c2\db\cts2.fdb";
四、--更新字段注释
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
(RDB$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
