多数据库学习之GBase8s查询数据库表元信息常用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
  
  

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

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

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

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

• 4. 给用户赋权

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

创建数据库及模式

• 创建数据库

  
      -- 创建数据库
      CREATE DATABASE dbtest;
  
      -- 语法
      CREATE DATABASE databasename [in dbspace] [with log|buffered log|log mode ansi] [nlscase sensitive|nlscase insensitive];
  
  
  
  

• 创建表并赋予权限

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

  
      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;
  
  
  

• 修改表结构

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

• 创建存储过程

  
  
      --  创建存储过程
      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();
  
  
  

获取表元数据

• 常用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");
  
  

其他

• systables

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

• information_schema(信息模式视图)

  “信息模式”视图是在您作为 DBA 运行以下 DB-Access 命令时自动生成的：

  
      dbaccess database-name $GBASEDBTDIR/etc/xpg4_is.sql
  
  

  因此默认情况下，无法执行查询information_schema视图下的相关信息SQL

• 数据库(informix)相关概念

  1. 用户(user)等同于模式(schema)、一个数据库(database)下可以有多个用户(user)。

  2. 数据库名和schema的格式：数据库名:schema.表名。

  3. 同一数据库下不允许创建相同表名的表结构：

  (1) 如果表名前不加用户名.的形式查询表，默认查询当前数据库下的表信息；
  (2) 如果表名前添加用户名.的形式查询表，只查询当前数据库下用户下的表结构信息，若不存在则报错。

  4. 支持跨数据库查询

  支持跨数据库查询表元信息，但是只能限定查询通过数据库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（命名）
  
  

  如果列不允许使用 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));
    }
  
  
  
  

• 自增标识

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

参考链接

• GBase8s 创建用户

• GBase 8s数据库常用操作指南

• GBase 8s 存储过程

• GBase 8s 元数据查询

• GBase官网手册

• informix数据库大全(持续更新)

• Informix.Database

• GBase 8s 学习笔记 001 —— GBase 8s 数据库产品介绍