GBase 8s 学习笔记 013 —— GBase 8s 元数据查询
查询数据库
[gbasedbt@localhost ~]$ dbaccess - -
Your evaluation license will expire on 2024-02-14 00:00:00
> database sysmaster;
Database selected.
> 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
name mydb2
is_logging 1
is_case_insens 0
7 row(s) retrieved.
>
查询数据库字符集
> database sysmaster;
Database closed.
Database selected.
> 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
dbs_dbsname mydb2
dbs_collate zh_CN.57372
7 row(s) retrieved.
>
查询表
database <db_name>;
select tabid, tabname, tabtype from systables where tabid >= 100 and tabtype = 'T';
> database mydb;
Database closed.
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 100
tabname t_dept
tabtype T
tabid 101
tabname t_employee
tabtype T
tabid 103
tabname t_user
tabtype T
3 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 selected.
> select colname from syscolumns where tabid = 103;
colname f_userid
colname f_username
2 row(s) retrieved.
> select colname, coltype, coltypename from syscolumnsext where tabid = 103 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 = 103;
tabid 103
idxname idx_user_name
tabid 103
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 106
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 634
1 row(s) retrieved.
>
函数
database <db_name>;
select * from sysprocedures where procname like '<key_word>%';
> 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 635
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 107
btabid 103
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);
> drop table if exists t_dept;
Table dropped.
> create table t_dept(f_deptid int primary key constraint pk_dept_deptid,
f_deptname varchar(20) not null unique constraint uni_dept_deptname);>
Table created.
> drop table if exists t_employee;
Table dropped.
> 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);> > > > >
Table created.
主键
database <db_name>;
select * from sysconstraints where constrtype = 'P';
> database mydb;
Database closed.
Database selected.
> select * from sysconstraints where constrtype = 'P';
constrid 3
constrname pk_dept_deptid
owner gbasedbt
tabid 108
constrtype P
idxname 108_3
collation zh_CN.57372
constrid 6
constrname pk_employee_employeeid
owner gbasedbt
tabid 109
constrtype P
idxname 109_6
collation zh_CN.57372
2 row(s) retrieved.
>
外键
database <db_name>;
select * from sysconstraints where constrtype = 'R';
> database mydb;
Database closed.
Database selected.
> select * from sysconstraints where constrtype = 'R';
constrid 8
constrname r109_8
owner gbasedbt
tabid 109
constrtype R
idxname 109_8
collation zh_CN.57372
1 row(s) retrieved.
>
唯一索引
database <db_name>;
select * from sysconstraints where constrtype = 'U';
> database mydb;
Database closed.
Database selected.
> select * from sysconstraints where constrtype = 'U';
constrid 4
constrname uni_dept_deptname
owner gbasedbt
tabid 108
constrtype U
idxname 108_4
collation zh_CN.57372
constrid 7
constrname uni_employee_showname
owner gbasedbt
tabid 109
constrtype U
idxname 109_7
collation zh_CN.57372
2 row(s) retrieved.
>
NULL
database <db_name>;
select * from sysconstraints where constrtype = 'N';
> database mydb;
Database closed.
Database selected.
> select * from sysconstraints where constrtype = 'N';
constrid 1
constrname n105_1
owner gbasedbt
tabid 105
constrtype N
idxname
collation zh_CN.57372
constrid 2
constrname n106_2
owner gbasedbt
tabid 106
constrtype N
idxname
collation zh_CN.57372
constrid 5
constrname n108_5
owner gbasedbt
tabid 108
constrtype N
idxname
collation zh_CN.57372
constrid 9
constrname n109_9
owner gbasedbt
tabid 109
constrtype N
idxname
collation zh_CN.57372
constrid 10
constrname n109_10
owner gbasedbt
tabid 109
constrtype N
idxname
collation zh_CN.57372
5 row(s) retrieved.
>
默认值
database <db_name>;
select * from sysdefaults where tabid = <tab_id>;
> database mydb;
Database closed.
Database selected.
> select * from sysdefaults where tabid = 109;
tabid 109
colno 5
type L
default AAAAEg 18
class T
tabid 109
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>;
> database mydb;
Database closed.
Database selected.
> select * from sysconstraints where constrtype = 'C';
constrid 11
constrname c109_11
owner gbasedbt
tabid 109
constrtype C
idxname
collation zh_CN.57372
1 row(s) retrieved.
> select * from syschecks where type = 'T' and constrid = 11;
constrid type seqno checktext
11 T 0 ((f_age > 0 ) AND (f_age <= 120
11 T 1 ) )
2 row(s) retrieved.
>
查看数据库空间
database sysmaster;
select * from sysdbspaces;
> database sysmaster;
Database closed.
Database selected.
> 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;
> database sysmaster;
Database closed.
Database selected.
> select first 3 chknum, dbsnum, pagesize, chksize, nfree, fname from syschunks;
chknum 1
dbsnum 1
pagesize 2048
chksize 102400
nfree 91514
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;
> database sysmaster;
Database closed.
Database selected.
> select vpid, pid, classname, thread_run, thread_idle from sysvplst;
vpid pid classname thread_run thread_idle
1 19175 cpu 7.489733797605 7.530148626770
2 19176 adm 0.00 0.00
3 19177 lio 0.001306621167 0.100741288911
4 19178 pio 0.003214743911 0.100588947544
5 19179 aio 0.222949840868 8.813779659604
6 19180 msc 0.002210939989 2.171980153391
7 19181 fifo 0.002870039136 0.100263392819
8 19182 aio 0.013794375191 4.922558417003
9 19183 aio 0.005494259886 2.875215095806
10 19184 aio 0.005545210019 0.684069369596
11 19185 aio 0.005824914301 0.582563825779
12 19186 aio 0.002615381372 0.100633397015
12 row(s) retrieved.
>
查看物理日志
database sysmaster;
select * from sysplog;
> database sysmaster;
Database closed.
Database selected.
> select * from sysplog;
pl_mutex 1142321768
pl_b1mutex 1142321896
pl_b1condition 1142322024
pl_b1used 7
pl_b1copied 7
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 1142321896
pl_otherbp 1142322160
pl_bufsize 1024
pl_stamp 722650
pl_chunk 3
pl_offset 53
pl_physize 99400
pl_phypos 12043
pl_phyused 7
pl_phyarch 0
1 row(s) retrieved.
>
查看逻辑日志
database sysmaster;
select * from syslogs;
> database sysmaster;
Database closed.
Database selected.
> 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 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
3 row(s) retrieved.
>
查看会话
database sysmaster;
select * from syssessions;
> database sysmaster;
Database closed.
Database selected.
> select * from syssessions;
sid 23
username gbasedbt
uid 1001
pid 19288
hostname localhost.localdomain
tty /dev/pts/0
connected 1676604081
feprogram /opt/gbase/bin/dbaccess
pooladdr 1216843840
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
sid 25
username gbasedbt
uid 1001
pid 21141
hostname localhost.localdomain
tty /dev/pts/1
connected 1676613563
feprogram /opt/gbase/bin/dbaccess
pooladdr 1230618688
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
2 row(s) retrieved.
>
用户
select * from sysusers;
> database sysmaster;
Database closed.
Database selected.
> select * from sysusers;
username gbasedbt
usertype D
priority 9
password
defrole
username public
usertype C
priority 5
password
defrole
2 row(s) retrieved.
>
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
