背景
项目常用的关系型数据库是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