database <db_name>;
select tabid, tabname, tabtype from systables where tabid >= 100 and tabtype = 'T';
[gbasedbt@train ~]$ dbaccess - -
Your evaluation license will expire on 2022-06-09 12:00:00
> database mydb;
Database selected.
> create table t_user(f_userid int, f_username varchar(20));
Table created.
> select tabid, tabname, tabtype from systables where tabid >= 100 and tabtype = 'T';
tabid 102
tabname t_user
tabtype T
1 row(s) retrieved.
>
查询列
database <db_name>;
select colname from syscolumns where tabid = <tab_id>;
select colname, coltype, coltypename from syscolumnsext where tabid = <tab_id> order by colno;
> database mydb;
Database closed.
Database selected.
> select colname from syscolumns where tabid = 102;
colname f_userid
colname f_username
2 row(s) retrieved.
> select colname, coltype, coltypename from syscolumnsext where tabid = 102 order by colno;
colname f_userid
coltype 2
coltypename INTEGER
colname f_username
coltype 13
coltypename VARCHAR(20)
2 row(s) retrieved.
>
查询视图
database <db_name>;
select tabname,tabtype from systables where tabid >= 100 and tabtype = 'V';
> database mydb;
Database closed.
Database selected.
> create view v_user as select * from t_user;
View created.
> select tabname,tabtype from systables where tabid >= 100 and tabtype = 'V';
tabname v_user
tabtype V
1 row(s) retrieved.
>
查询表的索引
database <db_name>;
select tabid, idxname,tabid,idxtype from sysindexes where tabid = <tab_id>;
> database mydb;
Database closed.
Database selected.
> create index idx_user_name on t_user(f_username asc);
Index created.
> select tabid, idxname,tabid,idxtype from sysindexes where tabid = 102;
tabid 102
idxname idx_user_name
tabid 102
idxtype D
1 row(s) retrieved.
>
查询触发器
database <db_name>;
select * from systriggers;
> database mydb;
Database closed.
Database selected.
> create table t_log(f_logid serial, f_message varchar(50), f_operatedate date);
Table created.
> create table t_sale(f_saleid serial, f_productname varchar(20), f_qty int);
Table created.
> create or replace trigger trg_sale_insert insert on t_sale
> referencing new as new_item
> for each row
> (
> insert into t_log(f_message, f_operatedate) values(concat('insert:', new_item.f_productname), today)
> );
Trigger created.
> select * from systriggers;
trigid 1
trigname trg_sale_insert
owner gbasedbt
tabid 107
event I
old
new new_item
mode O
collation zh_CN.57372
1 row(s) retrieved.
>
存储过程
database <db_name>;
select procname, procid from sysprocedures where procname like '<key_word>%';
> database mydb;
Database closed.
Database selected.
> create procedure up_useradd(userid int, username varchar(20))
> insert into t_user values(userid, username);
> end procedure;
Routine created.
> select procname, procid from sysprocedures where procname like 'up_%';
procname up_useradd
procid 636
1 row(s) retrieved.
>
函数
database <db_name>;
select * from sysprocedures where procname like '<key_word>%';
database mydb;
create function fn_user_add(user_num int)
returning int as userid, varchar(20) as username
define i int;
define userid int;
define username varchar(20);
for i = 1 to user_num
let userid = i;
let username = concat('user_', to_char(i));
return userid, username with resume;
end for;
end function;
> database mydb;
Database closed.
Database selected.
> create function fn_user_add(user_num int)
> returning int as userid, varchar(20) as username
>
> define i int;
> define userid int;
> define username varchar(20);
>
> for i = 1 to user_num
> let userid = i;
> let username = concat('user_', to_char(i));
>
> return userid, username with resume;
> end for;
>
> end function;
Routine created.
> select * from sysprocedures where procname like 'fn_%';
procname fn_user_add
owner gbasedbt
procid 638
mode O
retsize 239
symsize 941
datasize 982
codesize 152
numargs 1
isproc f
specificname
externalname
paramstyle I
langid 2
paramtypes integer
variant t
client f
handlesnulls t
iterator t
percallcost 0
commutator
negator
selfunc
internal f
class
stack
parallelizable f
costfunc
selconst 0.00
collation zh_CN.57372
procflags 0
type 0
belongid 0
1 row(s) retrieved.
>
同义词
database <db_name>;
select * from syssyntable where tabname is not null;
select a.tabid, a.btabid, b.tabname as syn_name, c.tabname as tab_name
from
(select * from syssyntable where tabname is null) a
inner join systables b
on a.tabid = b.tabid
inner join systables c
on a.btabid = c.tabid;
> database mydb;
Database closed.
Database selected.
> create synonym syn_user for t_user;
Synonym created.
> select a.tabid, a.btabid, b.tabname as syn_name, c.tabname as tab_name
> from
> (select * from syssyntable where tabname is null) a
> inner join systables b
> on a.tabid = b.tabid
> inner join systables c
> on a.btabid = c.tabid;
tabid 105
btabid 102
syn_name syn_user
tab_name t_user
1 row(s) retrieved.
>
约束
drop table if exists t_dept;
create table t_dept(f_deptid int primary key constraint pk_dept_deptid,
f_deptname varchar(20) not null unique constraint uni_dept_deptname);
drop table if exists t_employee;
create table t_employee(f_employeeid int primary key constraint pk_employee_employeeid,
f_deptid int references t_dept(f_deptid),
f_employeename varchar(20) not null,
f_showname varchar(20) not null unique constraint uni_employee_showname,
f_age int default 18 check (f_age >0 and f_age <= 120),
f_employeedate date default today);
主键
database <db_name>;
select * from sysconstraints where constrtype = 'P';
database <db_name>;
select * from sysdefaults where tabid = <tab_id>;
> select * from sysdefaults where tabid = 111;
tabid 111
colno 5
type L
default AAAAEg 18
class T
tabid 111
colno 6
type T
default
class T
2 row(s) retrieved.
>
Check
database <db_name>;
select * from sysconstraints where constrtype = 'C';
select * from syschecks where type = 'T' and constrid = <constr_id>;
> select * from sysconstraints where constrtype = 'C';
constrid 13
constrname c110_13
owner gbasedbt
tabid 110
constrtype C
idxname
collation zh_CN.57372
1 row(s) retrieved.
> select * from syschecks where type = 'T' and constrid = 13;
constrid type seqno checktext
13 T 0 ((f_age > 0 ) AND (f_age <= 120
13 T 1 ) )
2 row(s) retrieved.
>
2496
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
