簡介

• 背景介紹

  GBase 8t是基于IBM informix源代碼、編譯和測試體系自主研發(fā)的交易型數(shù)據(jù)庫產(chǎn)品。

  南大通用安全數(shù)據(jù)庫管理系統(tǒng)（簡稱 GBase 8s），該產(chǎn)品符合 SQL92/99、ODBC、OLEDB、JDBC、ADO.NET 等國際 數(shù)據(jù)庫規(guī)范和開發(fā)接口。

  8a是國內(nèi)首個基于列存的新型分析型數(shù)據(jù)庫，8a Cluster是國內(nèi)首個分布式并行數(shù)據(jù)庫集群，8t是國內(nèi)首個與世界先進技術(shù)接軌的國產(chǎn)事務(wù)型通用數(shù)據(jù)庫系統(tǒng)。

• 數(shù)據(jù)庫服務(wù)架構(gòu)

  一個實例由多個數(shù)據(jù)庫(Databases)組成，一個數(shù)據(jù)庫可以有多個模式(Schema)，經(jīng)過驗證理解模式(Schema)和用戶(user)是一對一的概念。

  

  支持跨數(shù)據(jù)庫查詢表元數(shù)據(jù)信息，如果查詢的表信息不在當(dāng)前數(shù)據(jù)庫(系統(tǒng)表systables)，需要指定查sysmaster數(shù)據(jù)庫下的相關(guān)系統(tǒng)表獲取，如獲取表名信息sysmaster:systables。

常用SQL

創(chuàng)建用戶

• 注意

  需要在服務(wù)端連接數(shù)據(jù)庫執(zhí)行

• 1. 切換到用戶gbasedbt后使用onmode開啟創(chuàng)建用戶參數(shù)

  onmode -wf USERMAPPING=ADMIN

• 2. 切換為root用戶創(chuàng)建希望創(chuàng)建的數(shù)據(jù)庫用戶名并且設(shè)置密碼

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

• 3. 切換到gbasedbt使用管理員權(quán)限創(chuàng)建用戶

  [gbasedbt@localhost ~]$ dbaccess - -Your evaluation license will expire on 2023-08-27 00:00:00# 創(chuàng)建用戶并設(shè)置密碼> create user nbsp with pass word "xxxx";

• 4. 給用戶賦權(quán)

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

創(chuàng)建數(shù)據(jù)庫及模式

• 創(chuàng)建數(shù)據(jù)庫

  -- 創(chuàng)建數(shù)據(jù)庫CREATE DATABASE dbtest;-- 語法CREATE DATABASE databasename [in dbspace] [with log|buffered log|log mode ansi] [nlscase sensitive|nlscase insensitive];

• 創(chuàng)建表并賦予權(quán)限

  須先創(chuàng)建用戶，否則無法指定表所屬用戶

  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;

• 修改表結(jié)構(gòu)

  alter table "表名" add "列名" "數(shù)據(jù)類型" | modify "列名" "數(shù)據(jù)類型" | drop column "列名";

• 創(chuàng)建存儲過程

  --  創(chuàng)建存儲過程create procedure insertdata()define i int;for i in (1 to 100)insert into test values(i,'GBase 8s');end for;end procedure;-- 執(zhí)行存儲過程execute procedure insertdata();

獲取表元數(shù)據(jù)

• 常用SQL匯總

  -- 查詢數(shù)據(jù)庫空間select * from sysmaster:sysdbspaces;-- 查詢用戶信息select * from sysusers;-- 查詢數(shù)據(jù)庫字符集select * from sysmaster:sysdbslocale;-- 查詢所有數(shù)據(jù)庫select name, is_logging, is_case_insens from sysmaster:sysdatabases;-- 查看實例名,數(shù)據(jù)庫名,用戶名select a.cf_original, DBINFO('dbname') dbname, user from sysmaster:sysconfig a where cf_name = 'dbtest';-- 查看所有數(shù)據(jù)庫名select name from sysmaster:sysdatabases;-- 查看數(shù)據(jù)庫中所有表名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';-- 查詢外鍵明細(xì)信息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 系統(tǒng)目錄表中記錄的每個表都指定一個 tabid（一個系統(tǒng)指定的順序號，它唯一地標(biāo)識數(shù)據(jù)中的每個表）。系統(tǒng)目錄表接收 2 位的 tabid 號，而用戶創(chuàng)建的表接收以 100 開頭的順序 tabid 號。

• information_schema(信息模式視圖)

  “信息模式”視圖是在您作為 DBA 運行以下 DB-Access 命令時自動生成的：

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

  因此默認(rèn)情況下，無法執(zhí)行查詢information_schema視圖下的相關(guān)信息SQL

參考鏈接

• GBase8s 創(chuàng)建用戶

• GBase 8s數(shù)據(jù)庫常用操作指南

• GBase 8s 存儲過程

• GBase 8s 元數(shù)據(jù)查詢

• GBase官網(wǎng)手冊

• informix數(shù)據(jù)庫大全(持續(xù)更新)

• Informix.Database

• GBase 8s 學(xué)習(xí)筆記 001 —— GBase 8s 數(shù)據(jù)庫產(chǎn)品介紹