多数据库学习之GBase8s查询数据库表元信息常用SQL_gbase8s查询所有库

作者：小丑西瓜9 | 2024-05-12 16:32:29

踩

gbase8s查询所有库

多数据库学习之GBase8s查询数据库表元信息常用SQL

简介
常用SQL
其他
参考链接

简介

背景介绍

GBase 8t是基于IBM informix源代码、编译和测试体系自主研发的交易型数据库产品。

南大通用安全数据库管理系统（简称 GBase 8s），该产品符合 SQL92/99、ODBC、OLEDB、JDBC、ADO.NET 等国际数据库规范和开发接口。

8a是国内首个基于列存的新型分析型数据库，8a Cluster是国内首个分布式并行数据库集群，8t是国内首个与世界先进技术接轨的国产事务型通用数据库系统。
数据库服务架构

一个实例由多个数据库(Databases)组成，一个数据库可以有多个模式(Schema)，经过验证理解模式(Schema)和用户(user)是一对一的概念。

支持跨数据库查询表元数据信息，如果查询的表信息不在当前数据库(系统表systables)，需要指定查sysmaster数据库下的相关系统表获取，如获取表名信息sysmaster:systables。

常用SQL

创建用户

注意

需要在服务端连接数据库执行
1. 以 gbasedbt 用户身份登录后；
2. 输入 onmode -ky，可停止数据库服务；
3. 输入 oninit -vy，启动数据库服务；
4. 输入 dbaccess - - 命令进入数据库命令行模式
1. 切换到用户gbasedbt后使用onmode开启创建用户参数
```
    onmode -wf USERMAPPING=ADMIN

1
2
3
```

切换为root用户创建希望创建的数据库用户名并且设置密码


    [root@localhost ~]# groupadd  nbsp
    [root@localhost ~]# useradd -g nbsp -d /home/nbsp -s /bin/bash -m nbsp
    [root@localhost ~]# passwd nbsp


1
2
3
4
5
6

切换到gbasedbt使用管理员权限创建用户


    [gbasedbt@localhost ~]$ dbaccess - -
    Your evaluation license will expire on 2023-08-27 00:00:00
    
    # 创建用户并设置密码
    > create user nbsp with pass word "xxxx";

1
2
3
4
5
6
7

给用户赋权


    > grant dba to nbsp;

    Permission granted.
    
    > grant connect to nbsp;
    
    Permission granted.
    
    > grant resource to nbsp;
    
    Permission granted.
    
    > database nbsp;


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

创建数据库及模式

创建数据库


    -- 创建数据库
    CREATE DATABASE dbtest;

    -- 语法
    CREATE DATABASE databasename [in dbspace] [with log|buffered log|log mode ansi] [nlscase sensitive|nlscase insensitive];



1
2
3
4
5
6
7
8
9

创建表并赋予权限

须先创建用户，否则无法指定表所属用户


    CREATE SCHEMA AUTHORIZATION gbasedbttest
    CREATE TABLE customer
    (
        customer_num SERIAL(101),
        fname        CHAR(15),
        lname        CHAR(15),
        company      CHAR(20),
        address1     CHAR(20),
        address2     CHAR(20),
        city         CHAR(15),
        state        CHAR(2),
        zipcode      CHAR(5),
        phone        CHAR(18)
    )
    GRANT ALTER, ALL ON customer TO gbasedbttest WITH GRANT OPTION;


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

修改表结构


    alter table "表名" add "列名" "数据类型" | modify "列名" "数据类型" | drop column "列名";


1
2
3
4

创建存储过程



    --  创建存储过程
    create procedure insertdata()
        define i int;
    for i in (1 to 100)
    insert into test values(i,'GBase 8s');
    end for;
    end procedure;

    -- 执行存储过程
    execute procedure insertdata();


1
2
3
4
5
6
7
8
9
10
11
12
13
14

获取表元数据

常用SQL汇总


    -- 查询数据库空间
    select * from sysmaster:sysdbspaces;

    -- 查询用户信息
    select * from sysusers;


    -- 查询数据库字符集
    select * from sysmaster:sysdbslocale;

    -- 查询所有数据库
    select name, is_logging, is_case_insens from sysmaster:sysdatabases;

    -- 查看实例名,数据库名,用户名
    select a.cf_original, DBINFO('dbname') dbname, user from sysmaster:sysconfig a where cf_name = 'dbtest';

    -- 查看所有数据库名
    select name from sysmaster:sysdatabases;

    -- 查看数据库中所有表名
    select dbsname, tabname
    from sysmaster:systabnames
    where dbsname='dbtest';

    -- 查询用户自定义表名信息
    database sysadmin;
    select tabid, tabname, tabtype from systables where tabid >= 100 and tabtype = 'T';

    -- 查询表列信息
    select colname, coltype, coltypename from syscolumnsext where tabid = '100' order by colno;

    -- 查询视图
    select tabname,tabtype from systables where tabid >= 100 and tabtype = 'V';

    -- 查询表索引信息
    select tabid, idxname,tabid,idxtype from sysindexes where tabid = '100';

    -- 查询存储过程
    select procname, procid from sysprocedures where procname like '<key_word>%';

    -- 查询唯一索引
    select * from sysconstraints where constrtype = 'U';

    -- 查询主键信息
    select * from sysconstraints where constrtype = 'P';

    -- 查询外键信息
    select * from sysconstraints where constrtype = 'R';

            
    -- 查询外键明细信息
    SELECT fc.constrname fk_name,
        ft.tabname fk_tabname,
        fcol.colname fk_colname,
        pc.constrname pk_name,
        pt.tabname pt_tabname,
        pcol.colname
    FROM sysreferences r, sysconstraints fc, sysconstraints pc, systables ft, systables pt, sysindexes fi, sysindexes pi, syscolumns fcol, syscolumns pcol
    WHERE fc.constrtype = 'R'
    AND fc.tabid = ft.tabid
    AND fc.constrid = r.constrid
    AND r.ptabid = pt.tabid
    AND ft.tabid = fi.tabid
    AND pt.tabid = pi.tabid
    AND r.primary = pc.constrid
    AND fc.idxname = fi.idxname
    AND pc.idxname = pi.idxname
    AND ft.tabid = fcol.tabid
    AND pt.tabid = pcol.tabid
    AND fi.part1 = fcol.colno
    AND pi.part1 = pcol.colno
    AND ft.tabname = 't12';


    -- 查询库下所有字段的主键信息
    select unique t.tabname
        , (select c.colname from syscolumns c where c.tabid = i.tabid and c.colno = i.part1) as pk_1
        , (select c.colname from syscolumns c where c.tabid = i.tabid and c.colno = i.part2) as pk_2
        , (select c.colname from syscolumns c where c.tabid = i.tabid and c.colno = i.part3) as pk_3
        , (select c.colname from syscolumns c where c.tabid = i.tabid and c.colno = i.part4) as pk_4
        , (select c.colname from syscolumns c where c.tabid = i.tabid and c.colno = i.part5) as pk_5
        , (select c.colname from syscolumns c where c.tabid = i.tabid and c.colno = i.part6) as pk_6
    from sysindexes i,
        systables t
    where i.tabid = t.tabid
    and i.idxtype = "U"
    and i.idxname in (select c.idxname
                        from sysconstraints c,
                            systables t, outer(sysreferences r, systables t2, sysconstraints c2)
                        where t.tabid = c.tabid
                        and r.constrid = c.constrid
                        and t2.tabid = r.ptabid
                        and c2.constrid = r.constrid
                        and c.constrtype = "P");

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96

其他

systables

对 systables 系统目录表中记录的每个表都指定一个 tabid（一个系统指定的顺序号，它唯一地标识数据中的每个表）。系统目录表接收 2 位的 tabid 号，而用户创建的表接收以 100 开头的顺序 tabid 号。
information_schema(信息模式视图)

“信息模式”视图是在您作为 DBA 运行以下 DB-Access 命令时自动生成的：
```
    dbaccess database-name $GBASEDBTDIR/etc/xpg4_is.sql

1
2
3
```
因此默认情况下，无法执行查询information_schema视图下的相关信息SQL
数据库(informix)相关概念
1. 用户(user)等同于模式(schema)、一个数据库(database)下可以有多个用户(user)。
2. 数据库名和schema的格式：数据库名:schema.表名。
3. 同一数据库下不允许创建相同表名的表结构：
(1) 如果表名前不加用户名.的形式查询表，默认查询当前数据库下的表信息；
(2) 如果表名前添加用户名.的形式查询表，只查询当前数据库下用户下的表结构信息，若不存在则报错。
1. 支持跨数据库查询
支持跨数据库查询表元信息，但是只能限定查询通过数据库sysmaster查询，且存在一定的局限性，如查询表名无法确认是否为用户自建表。

字段NOT NULL 约束

可以根据表syscolumns(视图syscolumnsext)中字段coltype的值判断：


  指示该列的数据类型的代码：
    0 = CHAR
    1 = SMALLINT
    2 = INTEGER
    3 = FLOAT
    4 = SMALLFLOAT
    5 = DECIMAL
    6 = SERIAL
    7 = DATE
    8 = MONEY
    9 = NULL
    10 = DATETIME
    11 = BYTE
    12 = TEXT
    13 = VARCHAR
    14 = INTERVAL
    15 = NCHAR
    16 = NVARCHAR
    17 = INT8
    18 = SERIAL81
    19 = SET
    20 = MULTISET
    21 = LIST
    22 = ROW（未命名）
    23 = COLLECTION
    40 = 可变长度不透明类型 2
    41 = 固定长度不透明类型 2
    43 = LVARCHAR（仅适用于客户机端）
    45 = BOOLEAN
    52 = BIGINT
    53 = BIGSERIAL 1
    2061 = IDSSECURITYLABEL 2
    4118 = ROW（命名）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

如果列不允许使用 NULL 值，那么 coltype 值将增加 256。要确定这种列的数据类型，根据可能的 coltype 值，从值中减去 256 并求余数。例如，如果 coltype 值为262，减去 256 得到余数 6，那么这表示列具有 SERIAL 数据类型。

字段精度

定点数据类型

MONEY 或 DECIMAL(p,s) 列的 collength 值可使用以下公式来计算：(precision * 256) + scale

其实就是为了节约额外的2个字段，使用一个字段来存储精度。可以通过代码逆向算出精度和小数位数



  @Test
  public void test() {
    // decimal(32,5)
    System.out.println( 32 << 8 | ( 5 & 0xff ));
    
    
    System.out.println("m = " + (8197 >> 8));
    System.out.println("n = " + (8197 & 255));
  }



1
2
3
4
5
6
7
8
9
10
11
12
13
14

自增标识

GBase 8s 使用序列来标识字段的自增长，数据类型有 serial、serial8 和 bigserial。SERIAL、SERIAL8 和 BIGSERIAL 范围：

参考链接

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/小丑西瓜9/article/detail/559778