GBase 8s 元数据查询
程序员文章站
2022-03-04 22:24:19
...
GBase 8s 元数据查询
查询数据库
database sysmaster;
select name, is_logging, is_case_insens from sysdatabases;
> select name, is_logging, is_case_insens from sysdatabases;
name sysmaster
is_logging 1
is_case_insens 0
name sysutils
is_logging 1
is_case_insens 0
name sysuser
is_logging 1
is_case_insens 0
name sysadmin
is_logging 1
is_case_insens 0
name gbasedb
is_logging 1
is_case_insens 0
name mydb
is_logging 1
is_case_insens 0
6 row(s) retrieved.
>
查询数据库字符集
database sysmaster;
select * from sysdbslocale;
> select * from sysdbslocale;
dbs_dbsname sysmaster
dbs_collate en_US.819
dbs_dbsname sysutils
dbs_collate en_US.819
dbs_dbsname sysuser
dbs_collate en_US.819
dbs_dbsname sysadmin
dbs_collate en_US.819
dbs_dbsname gbasedb
dbs_collate zh_CN.57372
dbs_dbsname mydb
dbs_collate zh_CN.57372
6 row(s) retrieved.
>
查询表
database <db_name>;
select tabid, tabname, tabtype from systables where tabid >= 100 and tabtype = 'T';
[[email protected] ~]$ 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';
> select * from sysconstraints where constrtype = 'P';
constrid 5
constrname pk_dept_deptid
owner gbasedbt
tabid 109
constrtype P
idxname 109_5
collation zh_CN.57372
constrid 8
constrname pk_employee_employeeid
owner gbasedbt
tabid 110
constrtype P
idxname 110_8
collation zh_CN.57372
2 row(s) retrieved.
>
外键
database <db_name>;
select * from sysconstraints where constrtype = 'R';
> select * from sysconstraints where constrtype = 'R';
constrid 10
constrname r110_10
owner gbasedbt
tabid 110
constrtype R
idxname 110_10
collation zh_CN.57372
1 row(s) retrieved.
>
唯一索引
database <db_name>;
select * from sysconstraints where constrtype = 'U';
> select * from sysconstraints where constrtype = 'U';
constrid 6
constrname uni_dept_deptname
owner gbasedbt
tabid 109
constrtype U
idxname 109_6
collation zh_CN.57372
constrid 9
constrname uni_employee_showname
owner gbasedbt
tabid 110
constrtype U
idxname 110_9
collation zh_CN.57372
2 row(s) retrieved.
>
NULL
database <db_name>;
select * from sysconstraints where constrtype = 'N';
> select * from sysconstraints where constrtype = 'N';
constrid 1
constrname n106_1
owner gbasedbt
tabid 106
constrtype N
idxname
collation zh_CN.57372
constrid 2
constrname n107_2
owner gbasedbt
tabid 107
constrtype N
idxname
collation zh_CN.57372
constrid 7
constrname n109_7
owner gbasedbt
tabid 109
constrtype N
idxname
collation zh_CN.57372
constrid 17
constrname n111_17
owner gbasedbt
tabid 111
constrtype N
idxname
collation zh_CN.57372
constrid 18
constrname n111_18
owner gbasedbt
tabid 111
constrtype N
idxname
collation zh_CN.57372
5 row(s) retrieved.
>
默认值
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.
>
查看数据库空间
database sysmaster;
select * from sysdbspaces;
> select first 3 dbsnum, name, pagesize, fchunk, nchunks, is_temp, is_blobspace, is_sbspace from sysdbspaces;
dbsnum 1
name rootdbs
pagesize 2048
fchunk 1
nchunks 1
is_temp 0
is_blobspace 0
is_sbspace 0
dbsnum 2
name llogdbs
pagesize 2048
fchunk 2
nchunks 1
is_temp 0
is_blobspace 0
is_sbspace 0
dbsnum 3
name plogdbs
pagesize 2048
fchunk 3
nchunks 1
is_temp 0
is_blobspace 0
is_sbspace 0
3 row(s) retrieved.
>
查看Chunk
database sysmaster;
select * from syschunks;
> select first 3 chknum, dbsnum, pagesize, chksize, nfree, fname from syschunks;
chknum 1
dbsnum 1
pagesize 2048
chksize 102400
nfree 91498
fname /opt/gbase/gbaseserver_dbs/rootdbs
chknum 2
dbsnum 2
pagesize 2048
chksize 102400
nfree 2347
fname /opt/gbase/gbaseserver_dbs/llogdbs
chknum 3
dbsnum 3
pagesize 2048
chksize 102400
nfree 2947
fname /opt/gbase/gbaseserver_dbs/plogdbs
3 row(s) retrieved.
>
查看VP
database sysmaster;
select * from sysvplst;
> select vpid, pid, classname, thread_run, thread_idle from sysvplst;
vpid pid classname thread_run thread_idle
1 46724 cpu 11.01166385829 11.39935341122
2 46725 adm 0.00 0.00
3 46726 lio 0.000877477935 0.218293923715
4 46727 pio 0.001072042145 0.218138731374
5 46728 aio 0.123853351805 43880.80047570
6 46729 msc 0.004894495352 72999.78886798
7 46730 fifo 0.002760741873 0.217946357779
8 46731 cpu 5.522930518951 87736.87219598
9 46732 aio 0.138658671953 1109.742524466
10 46733 aio 0.108724871203 1109.552496034
11 46734 aio 0.084435370625 1109.358623967
12 46735 aio 0.057563820997 1109.162046009
13 46736 aio 0.148795890732 1108.849970110
13 row(s) retrieved.
>
查看物理日志
database sysmaster;
select * from sysplog;
> select * from sysplog;
pl_mutex 1142321768
pl_b1mutex 1142321896
pl_b1condition 1142322024
pl_b1used 0
pl_b1copied 0
pl_b1buffer 1171687424
pl_b1wtlist 0
pl_b2mutex 1142322160
pl_b2condition 1142322288
pl_b2used 0
pl_b2copied 0
pl_b2buffer 1173805056
pl_b2wtlist 0
pl_curbp 1142322160
pl_otherbp 1142321896
pl_bufsize 1024
pl_stamp 403270
pl_chunk 3
pl_offset 53
pl_physize 99400
pl_phypos 14041
pl_phyused 0
pl_phyarch 0
1 row(s) retrieved.
>
查看逻辑日志
database sysmaster;
select * from syslogs;
> select first 3 * from syslogs;
number 4
uniqid 0
size 5000
used 0
is_used 0
is_current 0
is_backed_up 0
is_new 0
is_archived 0
is_temp 0
is_pre_dropped 0
flags 0
number 5
uniqid 0
size 5000
used 0
is_used 0
is_current 0
is_backed_up 0
is_new 0
is_archived 0
is_temp 0
is_pre_dropped 0
flags 0
number 6
uniqid 9
size 5000
used 1262
is_used 1
is_current 0
is_backed_up 1
is_new 0
is_archived 1
is_temp 0
is_pre_dropped 0
flags 21
3 row(s) retrieved.
>
查看会话
database sysmaster;
select * from syssessions;
> database sysmaster;
Database selected.
> select * from syssessions;
sid 47
username gbasedbt
uid 1001
pid 58243
hostname 192.168.3.6
tty /dev/pts/2
connected 1625878111
feprogram /opt/gbase/bin/dbaccess
pooladdr 1217949760
is_wlatch 0
is_wlock 0
is_wbuff 0
is_wckpt 0
is_wlogbuf 0
is_wtrans 0
is_monitor 0
is_incrit 0
state 524289
sid 48
username gbasedbt
uid 1001
pid 55990
hostname devsvr
tty /dev/pts/0
connected 1625878125
feprogram /opt/gbase/bin/dbaccess
pooladdr 1216577600
is_wlatch 0
is_wlock 0
is_wbuff 0
is_wckpt 0
is_wlogbuf 0
is_wtrans 0
is_monitor 0
is_incrit 0
state 524321
2 row(s) retrieved.
>
用户
select * from sysusers;
> select * from sysusers;
username gbasedbt
usertype D
priority 9
password
defrole
username public
usertype C
priority 5
password
defrole
2 row(s) retrieved.
>