印刻数据库

外观

KingBaseES 数据库操作命令 ​

数据库操作命令概述 ​

KingBaseES 数据库操作命令用于管理数据库对象，如创建、修改、删除数据库、表、用户、权限等。熟练掌握这些命令对于 DBA 日常运维至关重要。本文将介绍 KingBaseES 常用的数据库操作命令，包括数据库管理、表管理、用户管理和权限管理等方面。

数据库管理命令 ​

1. 创建数据库 ​

命令格式：

sql
CREATE DATABASE database_name [WITH [OWNER [=] user_name] [TEMPLATE [=] template] [ENCODING [=] encoding] [LC_COLLATE [=] lc_collate] [LC_CTYPE [=] lc_ctype] [TABLESPACE [=] tablespace_name] [CONNECTION LIMIT [=] connlimit]];

示例：

sql
CREATE DATABASE testdb WITH OWNER = system ENCODING = 'UTF8' TABLESPACE = userspace CONNECTION LIMIT = 100;

选项说明：

  • OWNER：指定数据库所有者
  • TEMPLATE：指定模板数据库，默认为 template1
  • ENCODING：指定数据库编码，如 UTF8
  • LC_COLLATE：指定排序规则
  • LC_CTYPE：指定字符分类
  • TABLESPACE：指定默认表空间
  • CONNECTION LIMIT：指定最大连接数

2. 删除数据库 ​

命令格式：

sql
DROP DATABASE [IF EXISTS] database_name [WITH (FORCE)];

示例：

sql
DROP DATABASE IF EXISTS testdb;

选项说明：

  • IF EXISTS：如果数据库不存在，不报错
  • FORCE：强制删除，断开所有连接

3. 切换数据库 ​

在 ksql 命令行中，可以使用以下命令切换数据库：

ini

示例：

ini

4. 查看数据库列表 ​

命令格式：

sql

或使用 SQL 命令：

sql
SELECT datname FROM pg_database;

5. 查看数据库详情 ​

命令格式：

sql

或使用 SQL 命令：

sql
SELECT * FROM pg_database WHERE datname = 'testdb';

表管理命令 ​

1. 创建表 ​

命令格式：

sql
CREATE TABLE table_name (
    column_name1 data_type [constraints],
    column_name2 data_type [constraints],
    ...
    [table_constraints]
) [TABLESPACE tablespace_name];

示例：

sql
CREATE TABLE user_info (
    user_id SERIAL PRIMARY KEY,
    user_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) TABLESPACE userspace;

常用约束：

  • PRIMARY KEY：主键约束
  • NOT NULL：非空约束
  • UNIQUE：唯一约束
  • FOREIGN KEY：外键约束
  • CHECK：检查约束
  • DEFAULT：默认值约束

2. 修改表 ​

添加列：

sql
ALTER TABLE table_name ADD COLUMN column_name data_type [constraints];

修改列：

sql
ALTER TABLE table_name ALTER COLUMN column_name [SET DATA TYPE data_type] [SET DEFAULT default_value] [NOT NULL | DROP NOT NULL];

删除列：

sql
ALTER TABLE table_name DROP COLUMN [IF EXISTS] column_name [CASCADE | RESTRICT];

添加约束：

sql
ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_type (column_name);

删除约束：

sql
ALTER TABLE table_name DROP CONSTRAINT [IF EXISTS] constraint_name [CASCADE | RESTRICT];

示例：

sql
-- 添加列
ALTER TABLE user_info ADD COLUMN age INT CHECK (age > 0);

-- 修改列
ALTER TABLE user_info ALTER COLUMN age SET DEFAULT 18;

-- 删除列
ALTER TABLE user_info DROP COLUMN IF EXISTS age;

3. 删除表 ​

命令格式：

sql
DROP TABLE [IF EXISTS] table_name [CASCADE | RESTRICT];

示例：

sql
DROP TABLE IF EXISTS user_info;

选项说明：

  • CASCADE：级联删除，删除所有依赖该表的对象
  • RESTRICT：如果有依赖对象，不允许删除（默认）

4. 查看表列表 ​

命令格式：

sql

查看指定模式下的表：

sql

或使用 SQL 命令：

sql
SELECT tablename FROM pg_tables WHERE schemaname = 'public';

5. 查看表结构 ​

命令格式：

sql

查看表的详细结构：

sql

或使用 SQL 命令：

sql
SELECT * FROM information_schema.columns WHERE table_name = 'user_info';

用户管理命令 ​

1. 创建用户 ​

命令格式：

sql
CREATE USER user_name [WITH [LOGIN | NOLOGIN] [SUPERUSER | NOSUPERUSER] [CREATEDB | NOCREATEDB] [CREATEROLE | NOCREATEROLE] [INHERIT | NOINHERIT] [REPLICATION | NOREPLICATION] [CONNECTION LIMIT connlimit] [PASSWORD 'password'] [VALID UNTIL 'timestamp']];

示例：

sql
CREATE USER appuser WITH LOGIN PASSWORD 'appuser@123' CREATEDB CONNECTION LIMIT 50;

常用选项：

  • LOGIN：允许登录（默认）
  • SUPERUSER：授予超级用户权限
  • CREATEDB：允许创建数据库
  • CREATEROLE：允许创建角色
  • REPLICATION：允许复制
  • CONNECTION LIMIT：指定最大连接数
  • PASSWORD：设置密码
  • VALID UNTIL：设置密码有效期

2. 修改用户 ​

命令格式：

sql
ALTER USER user_name [WITH [LOGIN | NOLOGIN] [SUPERUSER | NOSUPERUSER] [CREATEDB | NOCREATEDB] [CREATEROLE | NOCREATEROLE] [INHERIT | NOINHERIT] [REPLICATION | NOREPLICATION] [CONNECTION LIMIT connlimit] [PASSWORD 'password'] [VALID UNTIL 'timestamp']];

示例：

sql
ALTER USER appuser WITH PASSWORD 'newpassword@123' CONNECTION LIMIT 100;

3. 删除用户 ​

命令格式：

sql
DROP USER [IF EXISTS] user_name [CASCADE];

示例：

sql
DROP USER IF EXISTS appuser;

4. 查看用户列表 ​

命令格式：

sql

或使用 SQL 命令：

sql
SELECT usename FROM pg_user;

权限管理命令 ​

1. 授予权限 ​

命令格式：

sql
GRANT privilege_type [(column_list)] ON [TABLE] table_name TO user_name [WITH GRANT OPTION];

示例：

sql
-- 授予表的 SELECT、INSERT、UPDATE 权限
GRANT SELECT, INSERT, UPDATE ON user_info TO appuser;

-- 授予数据库的 CONNECT 权限
GRANT CONNECT ON DATABASE testdb TO appuser;

-- 授予模式的 USAGE 权限
GRANT USAGE ON SCHEMA public TO appuser;

-- 授予所有表的 SELECT 权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO appuser;

常用权限类型：

  • 表权限：SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER
  • 数据库权限：CREATE、CONNECT、TEMPORARY、TEMP
  • 模式权限：CREATE、USAGE
  • 序列权限：USAGE、SELECT、UPDATE

2. 撤销权限 ​

命令格式：

sql
REVOKE [GRANT OPTION FOR] privilege_type [(column_list)] ON [TABLE] table_name FROM user_name [CASCADE | RESTRICT];

示例：

sql
REVOKE UPDATE ON user_info FROM appuser;

3. 查看权限 ​

查看用户的权限：

sql

查看指定表的权限：

sql

或使用 SQL 命令：

sql
SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name = 'user_info';

索引管理命令 ​

1. 创建索引 ​

命令格式：

sql
CREATE [UNIQUE] [INDEX [IF NOT EXISTS]] index_name ON table_name [USING method] (column_name [ASC | DESC] [NULLS {FIRST | LAST}], ...) [TABLESPACE tablespace_name] [WHERE predicate];

示例：

sql
-- 创建普通索引
CREATE INDEX idx_user_info_email ON user_info(email);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_user_info_phone ON user_info(phone);

-- 创建复合索引
CREATE INDEX idx_user_info_name_created ON user_info(user_name, created_at DESC);

-- 创建部分索引
CREATE INDEX idx_orders_user_id ON orders(user_id) WHERE status = 'active';

2. 删除索引 ​

命令格式：

sql
DROP INDEX [IF EXISTS] index_name [CASCADE | RESTRICT];

示例：

sql
DROP INDEX IF EXISTS idx_user_info_email;

3. 查看索引 ​

命令格式：

sql

查看指定表的索引：

sql

或使用 SQL 命令：

sql
SELECT indexname FROM pg_indexes WHERE tablename = 'user_info';

表空间管理命令 ​

1. 创建表空间 ​

命令格式：

sql
CREATE TABLESPACE tablespace_name [OWNER user_name] LOCATION 'directory_path';

示例：

sql
CREATE TABLESPACE userspace OWNER system LOCATION '/data/Kingbase/ES/V8/tablespaces/userspace';

2. 删除表空间 ​

命令格式：

sql
DROP TABLESPACE [IF EXISTS] tablespace_name;

示例：

sql
DROP TABLESPACE IF EXISTS oldtablespace;

3. 查看表空间 ​

命令格式：

sql

或使用 SQL 命令：

sql
SELECT spcname FROM pg_tablespace;

版本差异 ​

V8 R6 版本 ​

  • 数据库操作命令相对简单
  • 支持的约束类型相对有限
  • 权限管理功能相对基础

V8 R7 版本 ​

  • 增强了数据库操作命令的功能
  • 支持更多的约束类型
  • 加强了权限管理功能
  • 支持更多的索引类型

常见问题与解决方案 ​

1. 如何解决权限不足的问题？ ​

问题：执行命令时提示权限不足。

解决方案：

  • 确保使用具有足够权限的用户登录
  • 使用超级用户授予相应的权限
  • 检查用户的角色和权限设置

2. 如何查看对象的所有者？ ​

问题：如何查看数据库、表或其他对象的所有者？

解决方案：

  • 使用 \l+ 查看数据库所有者
  • 使用 \d+ 查看表所有者
  • 使用 SQL 命令查询系统表，如 pg_database、pg_tables 等

3. 如何批量授予权限？ ​

问题：如何批量授予用户对多个对象的权限？

解决方案：

  • 使用 GRANT privilege_type ON ALL TABLES IN SCHEMA schema_name TO user_name 授予模式下所有表的权限
  • 使用 GRANT privilege_type ON ALL SEQUENCES IN SCHEMA schema_name TO user_name 授予模式下所有序列的权限
  • 使用 GRANT privilege_type ON ALL FUNCTIONS IN SCHEMA schema_name TO user_name 授予模式下所有函数的权限

4. 如何撤销所有权限？ ​

问题：如何撤销用户的所有权限？

解决方案：

  • 使用 REVOKE ALL PRIVILEGES ON table_name FROM user_name 撤销表的所有权限
  • 使用 REVOKE ALL PRIVILEGES ON DATABASE database_name FROM user_name 撤销数据库的所有权限
  • 使用 REVOKE ALL PRIVILEGES ON SCHEMA schema_name FROM user_name 撤销模式的所有权限

总结 ​

KingBaseES 数据库操作命令是 DBA 日常运维的重要工具，熟练掌握这些命令可以提高运维效率。本文介绍了 KingBaseES 常用的数据库操作命令，包括数据库管理、表管理、用户管理和权限管理等方面。通过这些命令，DBA 可以方便地管理数据库对象，确保数据库的安全和稳定运行。

在使用数据库操作命令时，需要注意以下几点：

  • 确保使用具有足够权限的用户执行命令
  • 仔细检查命令语法，避免误操作
  • 对于重要操作，建议先在测试环境中验证
  • 定期备份数据库，以防操作失误导致数据丢失

通过合理使用这些数据库操作命令，DBA 可以有效地管理 KingBaseES 数据库，提高系统的可用性和可靠性。