MySQL学习笔记
文章目录
1 查询数据库
database sysmaster;
select name, is_logging, is_case_insens from sysdatabases;
>database sysmaster;
> 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.
>
2 查询数据库字符集
database sysmaster;
select * from sysdbslocale;
> database sysmaster;
> 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.
>
3 查询表
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.
>
4 查询列
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.
>
5 查询视图
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.
>
6 查询表的索引
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.
>
7 查询触发器
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.
>
8 存储过程
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.
>
9 函数
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.
>
10 同义词
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.
>
11 约束
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);
—11.1 主键
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.
>
—11.2 外键
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.
>
—11.3 唯一索引
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.
>
—11.4 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.
>
—11.5 默认值
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.
>
—11.6 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.
>
12 查看数据库空间
database sysmaster;
select * from sysdbspaces;
database sysmaster;
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.
>
13 查看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.
>
14 查看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.
>
15 查看物理日志
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.
>
16 查看逻辑日志
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.
>
17 查看会话
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.
>
18 用户
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.
>
database sysmaster; select * from sysdbslocale;
> database sysmaster; > 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. >
3 查询表
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.
>
4 查询列
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.
>
5 查询视图
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.
>
6 查询表的索引
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.
>
7 查询触发器
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.
>
8 存储过程
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.
>
9 函数
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.
>
10 同义词
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.
>
11 约束
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);
—11.1 主键
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.
>
—11.2 外键
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.
>
—11.3 唯一索引
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.
>
—11.4 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.
>
—11.5 默认值
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.
>
—11.6 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.
>
12 查看数据库空间
database sysmaster;
select * from sysdbspaces;
database sysmaster;
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.
>
13 查看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.
>
14 查看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.
>
15 查看物理日志
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.
>
16 查看逻辑日志
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.
>
17 查看会话
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.
>
18 用户
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.
>
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. >
5 查询视图
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.
>
6 查询表的索引
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.
>
7 查询触发器
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.
>
8 存储过程
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.
>
9 函数
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.
>
10 同义词
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.
>
11 约束
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);
—11.1 主键
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.
>
—11.2 外键
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.
>
—11.3 唯一索引
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.
>
—11.4 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.
>
—11.5 默认值
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.
>
—11.6 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.
>
12 查看数据库空间
database sysmaster;
select * from sysdbspaces;
database sysmaster;
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.
>
13 查看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.
>
14 查看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.
>
15 查看物理日志
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.
>
16 查看逻辑日志
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.
>
17 查看会话
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.
>
18 用户
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.
>
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. >
7 查询触发器
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.
>
8 存储过程
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.
>
9 函数
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.
>
10 同义词
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.
>
11 约束
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);
—11.1 主键
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.
>
—11.2 外键
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.
>
—11.3 唯一索引
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.
>
—11.4 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.
>
—11.5 默认值
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.
>
—11.6 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.
>
12 查看数据库空间
database sysmaster;
select * from sysdbspaces;
database sysmaster;
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.
>
13 查看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.
>
14 查看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.
>
15 查看物理日志
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.
>
16 查看逻辑日志
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.
>
17 查看会话
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.
>
18 用户
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.
>
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. >
9 函数
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.
>
10 同义词
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.
>
11 约束
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);
—11.1 主键
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.
>
—11.2 外键
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.
>
—11.3 唯一索引
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.
>
—11.4 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.
>
—11.5 默认值
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.
>
—11.6 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.
>
12 查看数据库空间
database sysmaster;
select * from sysdbspaces;
database sysmaster;
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.
>
13 查看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.
>
14 查看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.
>
15 查看物理日志
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.
>
16 查看逻辑日志
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.
>
17 查看会话
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.
>
18 用户
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.
>
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. >
11 约束
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);
—11.1 主键
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.
>
—11.2 外键
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.
>
—11.3 唯一索引
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.
>
—11.4 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.
>
—11.5 默认值
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.
>
—11.6 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.
>
12 查看数据库空间
database sysmaster;
select * from sysdbspaces;
database sysmaster;
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.
>
13 查看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.
>
14 查看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.
>
15 查看物理日志
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.
>
16 查看逻辑日志
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.
>
17 查看会话
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.
>
18 用户
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.
>
database sysmaster; select * from sysdbspaces;
database sysmaster; 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. >
13 查看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.
>
14 查看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.
>
15 查看物理日志
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.
>
16 查看逻辑日志
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.
>
17 查看会话
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.
>
18 用户
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.
>
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.
>
15 查看物理日志
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.
>
16 查看逻辑日志
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.
>
17 查看会话
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.
>
18 用户
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.
>
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. >
17 查看会话
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.
>
18 用户
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.
>
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. >