人大金仓常用命令(kingbase)

背景

项目常用的关系型数据库是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
posted @   海yo  阅读(2651)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤
点击右上角即可分享
微信分享提示