多数据库学习之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后使用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 gbasedbttestCREATE 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, tabnamefrom sysmaster:systabnameswhere 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.colnameFROM sysreferences r, sysconstraints fc, sysconstraints pc, systables ft, systables pt, sysindexes fi, sysindexes pi, syscolumns fcol, syscolumns pcolWHERE fc.constrtype = 'R'AND fc.tabid = ft.tabidAND fc.constrid = r.constridAND r.ptabid = pt.tabidAND ft.tabid = fi.tabidAND pt.tabid = pi.tabidAND r.primary = pc.constridAND fc.idxname = fi.idxnameAND pc.idxname = pi.idxnameAND ft.tabid = fcol.tabidAND pt.tabid = pcol.tabidAND fi.part1 = fcol.colnoAND pi.part1 = pcol.colnoAND 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_6from sysindexes i,systables twhere i.tabid = t.tabidand i.idxtype = "U"and i.idxname in (select c.idxnamefrom sysconstraints c,systables t, outer(sysreferences r, systables t2, sysconstraints c2)where t.tabid = c.tabidand r.constrid = c.constridand t2.tabid = r.ptabidand c2.constrid = r.constridand 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

参考链接

• GBase8s 创建用户

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

• GBase 8s 存储过程

• GBase 8s 元数据查询

• GBase官网手册

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

• Informix.Database

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