背景
專案常用的關係型資料庫是mysql或者oracle，現在甲方很多都開始資料庫國產化，而我們也跟著開始學習國產資料庫的知識。
透過架構部選型，暫定人大金倉作為mysql及oracle的平替。
實驗環境
相容mysql模式
常用命令
ksql----連線資料庫的客戶端，類似於mysql命令或者sqlplus命令。
找到ksql命令，並登入資料庫
[root@mail ~]# find / -name ksql
/app/kingbase/ES/V8/KESRealPro/V008R006C008M020B0025/Server/bin/ksql
/app/kingbase/ES/V8/KESRealPro/V008R006C008M020B0025/ClientTools/bin/ksql
[root@mail ~]# cd /app/kingbase/ES/V8/KESRealPro/V008R006C008M020B0025/ClientTools/bin/
[root@mail bin]# ./ksql -U system -d test
Password for user system:
Type "help" for help.
test=#
列出資料庫
\l+
連線資料庫
\c {資料庫} {使用者}
列出模式和許可權
\dn+
檢視使用者
\du+
查某個schema下的表
\dt {schema}.*
或者
\dt #有遇到過表的owner是system，而schema的owner是新建的使用者，就會導致列不出表來，所以強烈建議，用資料庫、模式所屬的使用者來建表！滿足許可權最小化原則！下圖就是這種情況。
建立表空間
CREATE TABLESPACE {tablespace_name} dasspace LOCATION '{directory_path}';
如：
test=# CREATE TABLESPACE abc_ts LOCATION '/app/kingbase/space/abc_ts';
CREATE TABLESPACE
建立使用者
create user abc with password 'Abc#123';
建立資料庫
create database abcdb owner=abc encoding=utf8 tablespace=abc_ts;
也可以不指定表空間，用預設表空間
create database abcdb owner=abc encoding=utf8；
建立schema（模式）
如果對模式不熟的新手，建議配置資料庫、使用者、模式都用同一個名字。以下示例只是為了好區分這三者
\c abcdb system
create schema abc_schema authorization abc;
常用授權
GRANT CONNECT ON DATABASE abcdb TO abc; #授權連線許可權
grant USAGE on SCHEMA abc_schema to abc; #授權對模式的使用權
--
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA abc_schema TO abc; #授予對現有表的所有許可權（包括索引）
ALTER DEFAULT PRIVILEGES IN SCHEMA abc_schema GRANT ALL PRIVILEGES ON TABLES TO abc; #設定預設許可權，使未來建立的表也具有相同許可權
--
GRANT REFERENCES ON ALL TABLES IN SCHEMA abc_schema TO abc; #授予 REFERENCES 許可權以管理外來鍵約束
--
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA abc_schema TO abc; #授予對現有序列的所有許可權
ALTER DEFAULT PRIVILEGES IN SCHEMA abc_schema GRANT ALL PRIVILEGES ON SEQUENCES TO abc; #設定預設許可權，使未來建立的序列也具有相同許可權
--
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA abc_schema TO abc; #授予對現有函式的所有許可權
ALTER DEFAULT PRIVILEGES IN SCHEMA abc_schema GRANT ALL PRIVILEGES ON FUNCTIONS TO abc; #設定預設許可權，使未來建立的函式也具有相同許可權
匯入sql
abcdb=> \c abcdb abc #切成abc使用者來訪問abcdb
abcdb=> \i /root/abc.sql #匯入sql檔案
部分sql語句
附錄，資料庫命令列幫助
abcdb=# \?
General
\crosstabview [COLUMNS] execute query and display results in crosstab
\errverbose show most recent error message at maximum verbosity
\g [FILE] or [|COMMAND] execute query (and send results to file or |pipe)
\gdesc describe result of query, without executing it
\gexec execute query, then execute each value in its result
\gset [PREFIX] execute query and store results in ksql variables
\gx [FILE] as \g, but forces expanded outPut mode
\q quit ksql
\watch [SEC] execute query every SEC seconds
Help
\? [commands] show help on backslash commands
\? options show help on ksql command-line options
\? variables show help on special variables
Query Buffer
\e [FILE] [LINE] edit the query buffer (or file) with external editor
\ef [FUNCNAME [LINE]] edit function definition with external editor
\ev [VIEWNAME [LINE]] edit view definition with external editor
\p show the contents of the query buffer
\r reset (clear) the query buffer
\s [FILE] display history or save it to file
\w FILE write query buffer to file
Input/Output
\copy ... perform SQL COPY with data stream to the client host
\echo [STRING] write string to standard outPut
\i FILE execute commands from file
\ir FILE as \i, but relative to location of current script
\o [FILE] send all query results to file or |pipe
\qecho [STRING] write string to query outPut stream (see \o)
Conditional
\if EXPRESSION begin conditional block
\elif EXPRESSION alternative within current conditional block
\else final alternative within current conditional block
\endif end conditional block
Informational
(options: S = show system objects, + = additional detail)
\d[S+] list tables, views, and sequences
\d[S+] NAME describe table, view, sequence, or index
\da[S] [PATTERN] list aggregates
\dA[+] [PATTERN] list access methods
\db[+] [PATTERN] list tablespaces
\dc[S+] [PATTERN] list conversions
\dC[+] [PATTERN] list casts
\dd[S] [PATTERN] show object descriptions not displayed elsewhere
\dD[S+] [PATTERN] list domains
\ddp [PATTERN] list default privileges
\dE[S+] [PATTERN] list foreign tables
\det[+] [PATTERN] list foreign tables
\des[+] [PATTERN] list foreign servers
\deu[+] [PATTERN] list user mappings
\dew[+] [PATTERN] list foreign-data wrappers
\df[anptw][S+] [PATRN] list [only agg/normal/procedures/trigger/window] functions
\dF[+] [PATTERN] list text search configurations
\dFd[+] [PATTERN] list text search dictionaries
\dFp[+] [PATTERN] list text search parsers
\dFt[+] [PATTERN] list text search templates
\dg[S+] [PATTERN] list roles
\di[S+] [PATTERN] list indexes
\dl list large objects, same as \lo_list
\dL[S+] [PATTERN] list procedural languages
\dm[S+] [PATTERN] list materialized views
\dn[S+] [PATTERN] list schemas
\do[S+] [PATTERN] list operators
\dO[S+] [PATTERN] list collations
\dp [PATTERN] list table, view, and sequence access privileges
\dpkg[S+] [PATTERN] list packages
\dP[itn+] [PATTERN] list [only index/table] partitioned relations [n=nested]
\drds [PATRN1 [PATRN2]] list per-database role settings
\dRp[+] [PATTERN] list replication publications
\dRs[+] [PATTERN] list replication subscriptions
\ds[S+] [PATTERN] list sequences
\dt[S+] [PATTERN] list tables
\dT[S+] [PATTERN] list data types
\du[S+] [PATTERN] list roles
\dv[S+] [PATTERN] list views
\dx[+] [PATTERN] list extensions
\dy [PATTERN] list event triggers
\l[+] [PATTERN] list databases
\sf[+] FUNCNAME show a function's definition
\sv[+] VIEWNAME show a view's definition
\z [PATTERN] same as \dp
Formatting
\a toggle between unaligned and aligned outPut mode
\C [STRING] set table title, or unset if none
\f [STRING] show or set field separator for unaligned query outPut
\H toggle HTML outPut mode (currently off)
\pset [NAME [VALUE]] set table outPut option
(border|columns|csv_fieldsep|expanded|fieldsep|
fieldsep_zero|footer|format|linestyle|null|
numericlocale|pager|pager_min_lines|recordsep|
recordsep_zero|tableattr|title|tuples_only|
unicode_border_linestyle|unicode_column_linestyle|
unicode_header_linestyle)
\t [on|off] show only rows (currently off)
\T [STRING] set HTML <table> tag attributes, or unset if none
\x [on|off|auto] toggle expanded outPut (currently off)
Connection
\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
connect to new database (currently "abcdb")
\conninfo display information about current connection
\encoding [ENCODING] show or set client encoding
\password [USERNAME] securely change the password for a user
Operating System
\cd [DIR] change the current working directory
\setenv NAME [VALUE] set or unset environment variable
\timing [on|off] toggle timing of commands (currently off)
\! [COMMAND] execute command in shell or start interactive shell
Variables
\prompt [TEXT] NAME prompt user to set internal variable
\set [NAME [VALUE]] set internal variable, or list all if no parameters
\unset NAME unset (delete) internal variable
Large Objects
\lo_export LOBOID FILE export the LOBOID number largeobject to FILE
\lo_import FILE [COMMENT] import from FILE as a largeobject, else add COMMENT
\lo_list list all largeobjects
\lo_unlink LOBOID remove the LOBOID number largeobject