網(wǎng)站免費建設(shè)seo外鏈優(yōu)化培訓(xùn)
多數(shù)據(jù)庫學(xué)習(xí)之GBase8s查詢數(shù)據(jù)庫表元信息常用SQL
- 簡介
- 常用SQL
- 創(chuàng)建用戶
- 創(chuàng)建數(shù)據(jù)庫及模式
- 獲取表元數(shù)據(jù)
- 其他
- 參考鏈接
簡介
-
背景介紹
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í)行
-
- 切換到用戶gbasedbt后使用onmode開啟創(chuàng)建用戶參數(shù)
onmode -wf USERMAPPING=ADMIN
-
- 切換為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
-
- 切換到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";
-
- 給用戶賦權(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)品介紹