一、分页写法小例:
1 | select first 10 templateid,code, name from template ; |
2 | select first 10 skip 10 templateid,code, name from template ; |
3 | select * from shop rows 1 to 10; |
二、show
6 | D:\firebird2\bin>isql -u sysdba -p masterkey |
7 | connect "E:\company\xmwsoft ewxmwsoft\c2\db\cts2.fdb" ; |
四、--更新字段注释
1 | update RDB$RELATION_FIELDS |
2 | set RDB$DESCRIPTION = '描述信息' |
3 | where (RDB$RELATION_NAME = 'SHOP' ) and |
4 | (RDB$FIELD_NAME = 'CREDIT_BUY' ) |
五、--显示字段注释
1 | select RDB$FIELD_NAME,RDB$DESCRIPTION from RDB$RELATION_FIELDS |
2 | where (RDB$RELATION_NAME = 'SHOP' ) |
4 | (RDB$FIELD_NAME = 'CREDIT_BUY' ) |
六、--更新表注释
1 | update RDB$RELATIONS set RDB$DESCRIPTION = '描述信息' where RDB$RELATION_NAME= 'TABLE_NAME' |
七、--查询所有的表和视图(包括系统表和系统视图)
1 | SELECT RDB$RELATION_NAME FROM RDB$RELATIONS; |
八、
5 | 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
十、查所有用户表、用户视图所有字段及相关定义
01 | SELECT a.RDB$RELATION_NAME, b.RDB$FIELD_NAME, b.RDB$FIELD_ID, d.RDB$TYPE_NAME, |
02 | c.RDB$FIELD_LENGTH, c.RDB$FIELD_SCALE |
04 | INNER JOIN RDB$RELATION_FIELDS b |
05 | ON a.RDB$RELATION_NAME = b.RDB$RELATION_NAME |
06 | INNER JOIN RDB$FIELDS c |
07 | ON b.RDB$FIELD_SOURCE = c.RDB$FIELD_NAME |
09 | ON c.RDB$FIELD_TYPE = d.RDB$TYPE |
10 | WHERE a.RDB$SYSTEM_FLAG = 0 |
11 | AND d.RDB$FIELD_NAME = 'RDB$FIELD_TYPE' |
12 | ORDER BY a.RDB$RELATION_NAME, b.RDB$FIELD_ID |
十一、查找某表的所有字段及相关定义
1 | SELECT A.RDB$FIELD_NAME, B.RDB$FIELD_TYPE, B.RDB$FIELD_LENGTH, B.RDB$FIELD_PRECISION, B.RDB$FIELD_SCALE |
2 | FROM RDB$RELATION_FIELDS A, RDB$FIELDS B |
3 | WHERE A.RDB$RELATION_NAME = 'tablename' |
4 | AND A.RDB$FIELD_SOURCE = B.RDB$FIELD_NAME |
5 | ORDER BY A.RDB$FIELD_POSITION |
十二、查找某表的主键定义字段
1 | select A.RDB$FIELD_NAME FROM RDB$INDEX_SEGMENTS A, RDB$RELATION_CONSTRAINTS B |
2 | WHERE B.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY' |
3 | AND B.RDB$RELATION_NAME = 'tablename' |
4 | AND A.RDB$INDEX_NAME = B.RDB$INDEX_NAME |
5 | ORDER BY A.RDB$FIELD_POSITION |
十三、查找某表的外键定义
1 | select r1.RDB$CONSTRAINT_NAME, rind.RDB$FIELD_NAME, r2.RDB$RELATION_NAME |
2 | FROM RDB$RELATION_CONSTRAINTS r1, RDB$RELATION_CONSTRAINTS r2, RDB$REF_CONSTRAINTS ref, RDB$INDEX_SEGMENTS rind |
3 | where r1.RDB$RELATION_NAME = 'tablename' |
4 | and r1.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY' |
5 | and r1.RDB$CONSTRAINT_NAME = ref.RDB$CONSTRAINT_NAME |
6 | and ref.RDB$CONST_NAME_UQ = r2.RDB$CONSTRAINT_NAME |
7 | and r1.RDB$INDEX_NAME = rind.RDB$INDEX_NAME |
十四 获取用户表的所有字段
1 | SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = '[TABLE_NAME]' |