GBASE南大通用GBase 8s数据库-元数据查询（1）

GBASE南大通用查询数据库

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.

> 
 

GBASE南大通用查询数据库字符集

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.

> 

GBASE南大通用查询表

database <db_name>;

select tabid, tabname, tabtype from systables where tabid >= 100 and tabtype = 'T';

[gbasedbt@train ~]$ dbaccess - -
Your evaluation license will expire on 2022-06-09 12:00:00
> database mydb;

Database selected.

> create table t_user(f_userid int, f_username varchar(20));

Table created.

> select tabid, tabname, tabtype from systables where tabid >= 100 and tabtype = 'T';



tabid    102
tabname  t_user
tabtype  T

1 row(s) retrieved.

> 

查询列

database <db_name>;

select colname from syscolumns where tabid = <tab_id>;

select colname, coltype, coltypename from syscolumnsext where tabid = <tab_id> order by colno;

> database mydb;

Database closed.


Database selected.

> select colname from syscolumns where tabid = 102;



colname  f_userid

colname  f_username

2 row(s) retrieved.

> select colname, coltype, coltypename from syscolumnsext where tabid = 102 order by colno;



colname      f_userid
coltype      2
coltypename  INTEGER 

colname      f_username
coltype      13
coltypename  VARCHAR(20) 

2 row(s) retrieved.

> 

查询视图

database <db_name>;

select tabname,tabtype from systables where tabid >= 100 and tabtype = 'V';

> database mydb;

Database closed.


Database selected.

> create view v_user as select * from t_user;

View created.

> select tabname,tabtype from systables where tabid >= 100 and tabtype = 'V';



tabname  v_user
tabtype  V

1 row(s) retrieved.

> 

查询表的索引

database <db_name>;

select tabid, idxname,tabid,idxtype from sysindexes where tabid = <tab_id>;

> database mydb;

Database closed.


Database selected.

> create index idx_user_name on t_user(f_username asc);

Index created.

> select tabid, idxname,tabid,idxtype from sysindexes where tabid = 102;



tabid    102
idxname  idx_user_name
tabid    102
idxtype  D

1 row(s) retrieved.

> 

查询触发器

database <db_name>;

select * from systriggers;

> database mydb;

Database closed.


Database selected.

> create table t_log(f_logid serial, f_message varchar(50), f_operatedate date);

Table created.

> create table t_sale(f_saleid serial, f_productname varchar(20), f_qty int);

Table created.

> create or replace trigger trg_sale_insert insert on t_sale
> referencing new as new_item
> for each row 
> (
> insert into t_log(f_message, f_operatedate) values(concat('insert:', new_item.f_productname), today)
> );

Trigger created.

> select * from systriggers;



trigid     1
trigname   trg_sale_insert
owner      gbasedbt
tabid      107
event      I
old        
new        new_item
mode       O
collation  zh_CN.57372

1 row(s) retrieved.

> 

存储过程

database <db_name>;

select procname, procid from sysprocedures where procname like '<key_word>%';

> database mydb;

Database closed.


Database selected.

> create procedure up_useradd(userid int, username varchar(20))
> insert into t_user values(userid, username);
> end procedure;

Routine created.

> select procname, procid from sysprocedures where procname like 'up_%';   

procname  up_useradd
procid    636

1 row(s) retrieved.

> 

函数

database <db_name>;

select * from sysprocedures where procname like '<key_word>%';

database mydb;

create function fn_user_add(user_num int)
returning int as userid, varchar(20) as username

define i int;
define userid int;
define username varchar(20);

for i = 1 to user_num
    let userid = i;
    let username = concat('user_', to_char(i));
    
    return userid, username with resume;
end for;
    
end function;

> database mydb;

Database closed.


Database selected.

> create function fn_user_add(user_num int)
> returning int as userid, varchar(20) as username
> 
> define i int;
> define userid int;
> define username varchar(20);
> 
> for i = 1 to user_num
>     let userid = i;
>     let username = concat('user_', to_char(i));
>     
>     return userid, username with resume;
> end for;
>     
> end function;

Routine created.

> select * from sysprocedures where procname like 'fn_%';

procname        fn_user_add
owner           gbasedbt
procid          638
mode            O
retsize         239
symsize         941
datasize        982
codesize        152
numargs         1
isproc          f
specificname    
externalname    
paramstyle      I
langid          2
paramtypes      integer 
variant         t
client          f
handlesnulls    t
iterator        t
percallcost     0
commutator      
negator         
selfunc         
internal        f
class           
stack           
parallelizable  f
costfunc        
selconst        0.00
collation       zh_CN.57372
procflags       0
type            0
belongid        0

1 row(s) retrieved.

> 

同义词

database <db_name>;

select * from syssyntable where tabname is not null;

select a.tabid, a.btabid, b.tabname as syn_name, c.tabname as tab_name
from
(select * from syssyntable where tabname is null) a
inner join systables b
on a.tabid = b.tabid
inner join systables c
on a.btabid = c.tabid;

> database mydb;

Database closed.


Database selected.

> create synonym syn_user for t_user;

Synonym created.

> select a.tabid, a.btabid, b.tabname as syn_name, c.tabname as tab_name
> from
> (select * from syssyntable where tabname is null) a
> inner join systables b
> on a.tabid = b.tabid
> inner join systables c
> on a.btabid = c.tabid;



tabid     105
btabid    102
syn_name  syn_user
tab_name  t_user

1 row(s) retrieved.

> 

约束

drop table if exists t_dept;

create table t_dept(f_deptid int primary key constraint pk_dept_deptid,
    f_deptname varchar(20) not null unique constraint uni_dept_deptname);

drop table if exists t_employee;
    
create table t_employee(f_employeeid int primary key constraint pk_employee_employeeid,
    f_deptid int references t_dept(f_deptid),
    f_employeename varchar(20) not null,
    f_showname varchar(20) not null unique constraint uni_employee_showname,
    f_age int default 18 check (f_age >0 and f_age <= 120),
    f_employeedate date default today);

主键

database <db_name>;

select * from sysconstraints where constrtype = 'P';

> 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.

> 

唯一索引