金仓数据库KingbaseES基础语法详解与实践指南- 惊觉

1 金仓数据库概述

金仓数据库管理系统KingbaseES是中电科金仓（北京）科技股份有限公司研发的企业级关系型数据库产品，作为国产化替代浪潮中的核心力量，已广泛应用于政府、金融等关键行业领域。该产品具有大型通用、高可靠、高性能、高安全等特点，是国家自主创新产品目录中唯一的数据库产品。

KingbaseES采用"多模合一"架构，支持Oracle、PostgreSQL、MySQL及SQL Server四种兼容模式，这一特性使得开发者无需重新学习语法即可平滑迁移现有业务系统。从部署层面看，KingbaseES提供从轻量化单机版到企业级集群版的多种解决方案，满足不同应用场景的需求。

随着信创战略的深入推进，掌握KingbaseES的基本语法和操作技巧已成为技术人员必备的技能。本文将以KingbaseES V9R1C10版本为基础，全面介绍该数据库的核心语法和实战技巧。

2 数据库与模式管理

2.1 数据库的创建与管理

在KingbaseES中，数据库集群包含多个命名的数据库，用户和用户组在集群范围内共享，但各数据库的数据相互隔离。客户端连接只能访问连接请求中指定的数据库。

创建数据库的基本语法如下：

CREATE DATABASE name
[ WITH ] [ OWNER [=] user_name ]
[ TEMPLATE [=] template ]
[ ENCODING [=] encoding ]
[ TABLESPACE [=] tablespace_name ]
[ CONNECTION LIMIT [=] connlimit ]

实际应用示例：

-- 创建基本数据库
CREATE DATABASE mydb;

-- 指定属主和编码创建数据库
CREATE DATABASE mydb 
    WITH OWNER = SYSTEM
    ENCODING = 'UTF8'
    TEMPLATE = template1
    TABLESPACE = pg_default;

创建数据库后，可以通过sys_database系统表查看数据库信息，使用sys_database_size('dbname')函数查询数据库占用空间。

KingbaseES提供三种创建数据库的方式：图形界面操作、SQL命令和命令行工具createdb。对于生产环境，建议使用SQL脚本方式以便于版本控制和自动化部署。

2.2 模式的概念与使用

模式（Schema） 是KingbaseES中重要的逻辑容器，用于组织数据库对象。每个数据库包含一个或多个模式，模式内包含表、数据类型、函数等对象。

使用模式的主要优势包括：

多用户隔离：允许多个用户使用同一数据库而不会相互干扰
逻辑组织：将数据库对象组织成逻辑组，便于管理维护
命名空间隔离：第三方应用可放在不同模式中，避免同名对象冲突

模式管理语法：

-- 创建模式
CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ];

-- 设置搜索路径
SET search_path TO kmxiao, test_schema;

-- 显示当前搜索路径
SHOW search_path;

在实际项目开发中，建议将业务表统一放置在PUBLIC模式下，这样有利于数据库迁移和SQL语句编写。

3 数据类型详解

3.1 数值类型

KingbaseES提供丰富的数值类型以满足不同场景需求：

整数类型：

Smallint：2字节，范围-32768到+32767
Integer：4字节，范围-2147483648到+2147483647
Bigint：8字节，范围-9223372036854775808到+9223372036854775807

自增序列类型：

Smallserial：1到32767
Serial：1到2147483647
Bigserial：1到9223372036854775807

精确小数类型：

Numeric/Decimal：支持用户指定精度，可存储小数点前131072位和小数点后16383位的数字

浮点数类型：

Real：4字节，6位十进制精度
Double precision：8字节，15位十进制精度

与某些数据库不同，KingbaseES的整数类型不需要指定长度，这是建表时需要注意的细节。

3.2 字符类型

KingbaseES提供多种字符类型存储文本数据：

Character varying(n)（别名Varchar）：变长字符串，有长度限制
Character(n)（别名Char）：定长字符串，不足补空白
text：变长字符串，无长度限制

字符类型支持字节或字符语义，通过系统参数nls_length_semantics控制默认行为，也可显式指定：

-- 显式指定字符语义
CREATE TABLE test_char (col char(4 char));

-- 显式指定字节语义  
CREATE TABLE test_byte (col char(4 byte));

字符类型的最大长度限制为10485760字节或字符，实际使用时应根据业务需求合理选择类型和长度。

3.3 日期时间类型

Date：4字节，只存储日期，范围从4713BC到5874897AD
Timestamp：8字节日期和时间，可指定是否包含时区
Time：8字节存储一日内时间，可指定是否包含时区
Interval：12字节存储时间间隔，范围从-178000000年到178000000年

3.4 其他数据类型

布尔类型：Boolean/Bool，存储true/false值
二进制类型：Bytea，存储二进制字符串
大文本类型：Text，最大可存储1GB数据

4 数据定义语言（DDL）

4.1 表的创建与管理

表是KingbaseES中最基本的数据存储对象，创建表时需要定义列名、数据类型及约束条件。

基本建表语法：

CREATE [ TEMPORARY | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
(column_name data_type [ column_constraint [...] ]
[, table_constraint ...])
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) ];

订单表示例：

CREATE TABLE orders (
    O_ID INT,
    O_DATE DATE NOT NULL,
    C_ID INT NOT NULL,
    I_ID INT NOT NULL,
    O_QUANTITY INT NOT NULL,
    O_TOTAL_PRICE DECIMAL(10,2) NOT NULL,
    FOREIGN KEY(C_ID) REFERENCES customers(C_ID),
    FOREIGN KEY(I_ID) REFERENCES items(I_ID)
);

常见建表错误：

-- 错误示例：KingbaseES的int类型不可定长
create table students (num int(10), name varchar(225), age int(3));

-- 正确写法
create table students (num int, name varchar(225), age int);

4.2 表类型与特性

KingbaseES支持多种表类型适应不同场景：

普通表：存放永久数据的基本表类型

临时表（TEMP）：会话级别的临时数据存储

分区表：将大表拆分为相对独立的子表，提升查询性能和管理效率

分区表示例：

CREATE TABLE items (
    I_ID INT PRIMARY KEY,
    I_NAME VARCHAR(100) NOT NULL,
    I_PRICE DECIMAL(10,2) NOT NULL,
    I_STOCK INT NOT NULL
) PARTITION BY RANGE (I_PRICE) (
    PARTITION p_low VALUES LESS THAN (200),
    PARTITION p_medium VALUES LESS THAN (500),
    PARTITION p_high VALUES LESS THAN (1000),
    PARTITION p_high_price VALUES LESS THAN (MAXVALUE)
);

分区表支持范围分区、列表分区和哈希分区三种策略，可根据业务需求选择合适的分区方式。

4.3 表结构修改

在实际应用中，经常需要调整已存在的表结构，KingbaseES提供ALTER TABLE语句实现这一功能：

-- 添加主键约束
ALTER TABLE orders ADD CONSTRAINT pk_orders PRIMARY KEY(O_ID);

-- 添加新列
ALTER TABLE orders ADD COLUMN comment varchar(50);

-- 修改列数据类型
ALTER TABLE orders ALTER COLUMN comment TYPE varchar(100);

-- 删除列
ALTER TABLE orders DROP COLUMN comment;

修改表结构时需注意数据兼容性问题，特别是修改数据类型时需确保现有数据能正确转换。

5 数据操作语言（DML）

5.1 插入数据（INSERT）

INSERT语句用于向表添加新数据，支持多种插入方式：

基本插入：

INSERT INTO customers (C_NAME, C_ADDRESS, C_PHONE) 
VALUES ('Alice Smith', '123 Main St, City A', '123-456-7890');

批量插入：

INSERT INTO customers (C_NAME, C_ADDRESS, C_PHONE) 
VALUES 
    ('Alice Smith', '123 Main St, City A', '123-456-7890'),
    ('Bob Johnson', '456 Oak Ave, City B', '234-567-8901'),
    ('Charlie Brown', '789 Pine Rd, City C', '345-678-9012');

插入查询结果：

INSERT INTO products (product_no, name, price)
SELECT product_no, name, price FROM new_products
WHERE release_date = 'today';

对于大批量数据插入，建议使用KingbaseES提供的sys_bulkload工具，相比逐条INSERT有显著性能提升。

5.2 查询数据（SELECT）

SELECT语句是SQL核心，用于从表中检索数据：

基本查询：

-- 查询所有列
SELECT * FROM customers;

-- 查询特定列
SELECT C_NAME, C_PHONE FROM customers;

-- 使用别名
SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;

条件查询：

-- 等值查询
SELECT * FROM employees WHERE last_name = 'Doe';

-- 范围查询
SELECT * FROM employees WHERE hire_date BETWEEN '2023-01-01' AND '2025-11-08';

-- 多重条件
SELECT * FROM employees WHERE last_name = 'Doe' AND hire_date > '2022-01-01';

高级查询功能：

-- 去重查询
SELECT DISTINCT empid FROM employee;

-- 排序查询
SELECT * FROM weather ORDER BY city, temp_lo;

-- 分组查询
SELECT city, max(temp_lo) FROM weather GROUP BY city;

-- 分页查询
SELECT empid, name FROM employee ORDER BY empid LIMIT 5 OFFSET 0;

5.3 更新数据（UPDATE）

UPDATE语句用于修改表中现有数据：

基本更新：

UPDATE orders
SET O_TOTAL_PRICE = 800.00
WHERE O_ID = 7;

多字段更新：

UPDATE weather 
SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2 
WHERE date > '1994-11-28';

安全更新实践：

为保证数据安全，建议先查询确认要修改的数据，再执行更新，最后验证结果：

-- 1. 查询确认要修改的订单
SELECT * FROM orders WHERE O_ID = 7;

-- 2. 执行更新
UPDATE orders SET O_TOTAL_PRICE = 800.00 WHERE O_ID = 7;

-- 3. 验证更新结果
SELECT * FROM orders WHERE O_ID = 7;

5.4 删除数据（DELETE）

DELETE语句用于从表中删除数据：

条件删除：

DELETE FROM employees WHERE age < 30;

安全删除实践：

与更新操作类似，删除前应先确认数据：

-- 先查询确认要删除的订单
SELECT * FROM orders WHERE O_ID = 5;

-- 确认后执行删除
DELETE FROM orders WHERE O_ID = 5;

清空表的多种方式：

DELETE FROM table_name：删除所有行，保留表结构，可回滚
TRUNCATE TABLE table_name：清空表内容，表结构保留，性能更好
DROP TABLE table_name：删除整个表，包括表结构和数据

6 高级查询技术

6.1 多表连接查询

连接查询是关系数据库的核心功能，KingbaseES支持多种连接类型：

内连接：

SELECT u.username, p.content
FROM users u
JOIN posts p ON u.id = p.user_id;

左外连接：

SELECT u.username, o.order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

自连接：

SELECT e1.name, e2.name as manager_name
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

6.2 子查询

子查询提供了更复杂的数据检索能力：

标量子查询：

SELECT name, price, 
       (SELECT AVG(price) FROM products) as avg_price
FROM products;

存在性检查：

SELECT name
FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

IN子查询：

SELECT name
FROM products
WHERE category_id IN (SELECT id FROM categories WHERE active = true);

6.3 窗口函数

窗口函数允许在结果集的某个窗口上执行计算，而不影响结果集的行数：

SELECT name, department, salary,
       AVG(salary) OVER (PARTITION BY department) as dept_avg_salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

7 数据库开发实践

7.1 学生选课系统示例

数据库设计：

-- 创建学生表
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INTEGER CHECK (age > 0 AND age < 120),
    gender CHAR(1) CHECK (gender IN ('M', 'F')),
    email VARCHAR(100) UNIQUE,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建课程表
CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    course_code VARCHAR(20) UNIQUE NOT NULL,
    credits INTEGER CHECK (credits > 0),
    teacher VARCHAR(50),
    description TEXT
);

-- 创建选课表
CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    grade DECIMAL(3,1) CHECK (grade >= 0 AND grade <= 100),
    enroll_date DATE DEFAULT CURRENT_DATE,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE,
    UNIQUE(student_id, course_id)
);

数据操作：

-- 插入测试数据
INSERT INTO students (name, age, gender, email) VALUES
('张三', 20, 'M', '[email protected]'),
('李四', 21, 'F', '[email protected]');

INSERT INTO courses (course_name, course_code, credits, teacher) VALUES
('数据库原理', 'DB101', 3, '张教授'),
('Java程序设计', 'JAVA201', 4, '李教授');

-- 复杂查询：查询学生选课情况
SELECT s.name, c.course_name, e.grade
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.course_id
WHERE e.grade IS NOT NULL
ORDER BY s.name, e.grade DESC;

7.2 性能优化建议

索引策略：在经常查询的列上创建索引，但避免过度索引影响写入性能
查询优化：使用EXPLAIN分析查询计划，避免全表扫描
分区策略：对大数据表采用分区技术提升查询效率
连接优化：确保连接条件上有合适的索引

8 工具与客户端使用

8.1 KStudio开发工具

KingbaseES提供KStudio图形化管理工具，支持数据库开发的全生命周期管理。主要功能包括：

对象管理：可视化创建、修改数据库对象
数据编辑：直观的数据增删改查界面
SQL编辑：语法高亮、智能提示的SQL编辑器
性能监控：数据库性能监控和调优工具

8.2 ksql命令行工具

ksql是KingbaseES的命令行客户端，支持本地和远程连接：

基本连接命令：

# 连接到本地数据库
ksql -h localhost -p 54321 -d TEST -U SYSTEM -W

# 执行SQL文件
ksql -h localhost -p 54321 -d TEST -U SYSTEM -f script.sql

# 执行单条命令
ksql -h localhost -p 54321 -d TEST -U SYSTEM -c "SELECT version();"

常用元命令：

-- 查看所有数据库
\l

-- 查看当前数据库下的表
\dt

-- 查看表结构
\d table_name

-- 切换数据库
\c database_name

-- 查看所有用户
\du

9 最佳实践与注意事项

9.1 命名规范

使用有意义的英文名词命名数据库对象
保持命名一致性，如主键字段统一命名为id
避免使用保留字和特殊字符
表名使用复数形式，列名使用单数形式

9.2 安全规范

遵循最小权限原则，合理分配用户权限
使用参数化查询防止SQL注入
敏感数据加密存储
定期备份和恢复测试

9.3 性能规范

避免SELECT *，明确指定需要的列
大量数据操作使用批量处理
合理使用事务，避免长事务
定期分析表和更新统计信息

10 总结

本文系统介绍了金仓数据库KingbaseES的基础语法和核心功能，涵盖数据库管理、数据类型、DDL、DML、高级查询等关键内容。通过实际示例演示了如何在实际项目中应用这些知识。

KingbaseES作为国产数据库的佼佼者，既兼容国际主流数据库语法，又具备国产化优势。掌握其核心技术对于从事国产化项目开发的技术人员至关重要。建议读者在学习理论的同时，多进行实践操作，逐步掌握这款强大的数据库管理系统。

随着信创产业的深入发展，KingbaseES将在更多关键领域发挥重要作用。希望本文能为您的KingbaseES学习之路提供有力支持，助力您在国产数据库技术领域取得更大成就。

转载自 CSDN-专业IT技术社区

原文链接：https://blog.csdn.net/beautifulmemory/article/details/156837094

金仓数据库KingbaseES基础语法详解与实践指南