KingbaseES 查询处理基础与业务数据检索实践

原创于 2026-06-20 09:10:10 发布 · 6k 阅读

60 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#数据库 #java #oracle

数据库专栏收录该内容

10 篇文章

订阅专栏

KingbaseES 查询处理基础与业务数据检索实践

本文是本系列第 6 篇。上一篇创建了 customer、product、customer_order、order_item 四张表，本文开始插入样例数据，并围绕业务问题练习查询。

引言

上一篇文章完成了 kb_shop 的核心表设计，客户、商品、订单和订单明细之间的关系已经建立起来。但只有表结构还不够，数据库真正服务业务，是通过数据写入和查询完成的。没有样例数据，表结构只是骨架；有了数据和查询，业务模型才开始运转。

本文承接第五篇的表结构，先插入一批客户、商品和订单样例数据，再用 SELECT 回答实际业务问题。文章会从单表查询开始，逐步过渡到多表关联、分组统计和状态转换，为后续事务、索引和执行计划分析打基础。

表建好了，如果没有数据，就只是一个空架子。

本文会往 kb_shop 中插入一批客户、商品和订单数据，然后用 SQL 回答几个实际问题：

当前有哪些客户？
哪些商品库存不足？
最近订单金额是多少？
每个客户一共消费了多少钱？
哪些商品卖得最好？

这些问题都很接近真实工作场景。我们不追求一上来写复杂 SQL，而是从简单查询逐步过渡到多表关联和分组统计。

SELECT 不是查表，而是描述结果集

其实吧，不少刚入门的朋友往往仅仅只是把 SELECT 想成是“从表里面去拿数据”。这个想法比较表面。那么在关系型数据库里面的话，查询更准确一点的说法应该是什么呢？也就是说，你是用 SQL 去描述一个你想要的结果集。这个结果集的情况呢，它可能是从单张表里面来的，也有可能是从好几张表连接出来的，或者是经过了分组统计啊，条件过滤啊，还有表达式算出来的。

通常来说啊，一条典型的查询语句，它往往包含这么几个逻辑阶段：

子句	作用	示例
`FROM`	确定数据来源	客户表、订单表、商品表
`JOIN`	描述表之间的关系	订单关联客户、明细关联商品
`WHERE`	行级过滤	只查在售商品、已支付订单
`GROUP BY`	按维度分组	按客户统计消费金额
`HAVING`	分组后过滤	只看消费额超过某值的客户
`SELECT`	定义输出列和表达式	字段、聚合结果、状态翻译
`ORDER BY`	定义结果排序	按金额、库存、时间排序
`LIMIT`	限制返回数量	查询前三名商品

做业务查询的时候呢，难点往往仅仅只是不在语法上面。语法其实好学。那真正难的地方在哪呢？其实就是“问题建模”。也就是说，你想回答一个什么问题，然后你得想清楚需要去查哪些表，过滤条件的话该怎么写，还有统计的口径到底是什么。这篇文章呢，接着就会通过客户、商品还有订单这些数据，带着大家把 SELECT 从最简单的查询，慢慢推到多表关联和聚合统计那去。这么做的话，其实也就是为了后面我们要讲的执行计划和索引优化先打个底子。

一、连接 kb_shop

进入工具目录：

cd /d D:\Tools\Kingbase\ES\Server\bin

连接数据库：

ksql -U system -d kb_shop -h localhost -p 54321

确认当前数据库：

SELECT current_database() AS current_db;

建议开启 SQL 耗时显示：

\timing

后续每条 SQL 执行后会显示耗时。虽然现在数据量很小，但先养成习惯，后面讲调优时会很自然。

二、清理旧测试数据

其实，如果你跟着上一篇文章去做了那个约束测试的话，表里面大概率是会留下一点数据的。那么为了让咱们这篇文章跑出来的结果能对得上，我建议你先把业务数据给清理掉。接着还要把那个自增序列也给重置一下。

那为什么要搞这一步呢？并不是说鼓励大家去真实的环境里面清空数据啊。这个往往仅仅只是为了保证教程的结果你能复现出来。也就是说，样例数据要是都从同样的 ID 开始往后走，那么后面咱们去弄订单、明细，还有查询出来的结果，你对着截图看就更容易看明白了。如果是正式环境的情况，清理数据那可是必须得经过确认，还得做备份的。绝对不能把咱们学习用的脚本直接拿过去跑。

那么在学习环境里面的话，你可以用下面这种方式来搞：

TRUNCATE TABLE sales.order_item,
               sales.customer_order,
               inventory.product,
               sales.customer
RESTART IDENTITY;

这么执行一下，其实有两个效果：

也就是把那 4 张表里的旧数据全给清空了。
让那个 SERIAL 自增值又重新从 1 开始往上加，这样的话，后面文章里我按 customer_id = 1、product_id = 1 来给你演示的时候，就方便多了。

这里要注意一下啊：TRUNCATE 这个东西它是会清空表数据的。所以它只适合在学习环境里面用，或者说你确认里面没啥重要数据的情况。正式环境里面可别随便去执行它。

三、插入客户数据

INSERT INTO sales.customer (customer_name, phone, email, customer_level, status)
VALUES
    ('张三', '13800000001', 'zhangsan@example.com', 'normal', 'active'),
    ('李四', '13800000002', 'lisi@example.com', 'vip', 'active'),
    ('王五', '13800000003', 'wangwu@example.com', 'normal', 'active'),
    ('赵六', '13800000004', 'zhaoliu@example.com', 'vip', 'active'),
    ('孙七', '13800000005', NULL, 'normal', 'disabled');

接着咱们来查询客户：

其实数据插完之后立马去查一下，这个在写教程的时候是个挺好的习惯。为啥这么说呢？一方面你能验证一下数据到底写进去没写成功。另一方面读者也能看着字段跟样例值是怎么对应起来的。那后续如果说某条关联查询没查出结果，你还可以跑回这里来确认一下，看看是不是基础数据压根就不存在的情况。

SELECT customer_id,
       customer_name,
       phone,
       customer_level,
       status,
       created_at
FROM sales.customer
ORDER BY customer_id;

正常的话，你应该能看到有 5 个客户的数据：

四、插入商品数据

INSERT INTO inventory.product (
    product_code,
    product_name,
    category,
    unit_price,
    stock_qty,
    status
) VALUES
    ('P1001', 'KingbaseES 入门课程', 'course', 199.00, 50, 'on_sale'),
    ('P1002', '数据库性能调优课程', 'course', 399.00, 30, 'on_sale'),
    ('P2001', 'SQL 实战手册', 'book', 79.00, 100, 'on_sale'),
    ('P2002', '数据库运维清单', 'book', 59.00, 8, 'on_sale'),
    ('P3001', '企业数据库咨询服务', 'service', 1999.00, 5, 'on_sale'),
    ('P9001', '下架测试商品', 'test', 9.90, 0, 'off_sale');

接着我们来查一下商品的数据看看：

SELECT product_id,
       product_code,
       product_name,
       category,
       unit_price,
       stock_qty,
       status
FROM inventory.product
ORDER BY product_id;

五、插入订单和订单明细

那么为了避免手写 ID 的时候搞错嘛，我们得先看看客户和商品的 ID 到底是多少：

其实在关系型数据库里面的话，订单表它是不会直接去存客户姓名还有商品名称的。那它是怎么搞的呢？也就是说，它都是通过 ID 来建立关联的。所以啊，在插入订单明细之前，你先去查清楚主键的值，这往往仅仅只是理解外键关系必须要走的一步。

SELECT customer_id, customer_name
FROM sales.customer
ORDER BY customer_id;

SELECT product_id, product_code, product_name, unit_price
FROM inventory.product
ORDER BY product_id;

通常来说呢，我们假设查询出来的结果是这样的：

接着我们就来插入订单主表的数据：

INSERT INTO sales.customer_order (order_no, customer_id, order_status, total_amount, paid_at)
VALUES
    ('SO202605270001', 1, 'paid', 278.00, CURRENT_TIMESTAMP),
    ('SO202605270002', 2, 'paid', 399.00, CURRENT_TIMESTAMP),
    ('SO202605270003', 2, 'created', 258.00, NULL),
    ('SO202605270004', 3, 'cancelled', 79.00, NULL);

然后再把订单明细也插进去：

INSERT INTO sales.order_item (order_id, product_id, quantity, unit_price, line_amount)
VALUES
    (1, 1, 1, 199.00, 199.00),
    (1, 3, 1, 79.00, 79.00),
    (2, 2, 1, 399.00, 399.00),
    (3, 1, 1, 199.00, 199.00),
    (3, 4, 1, 59.00, 59.00),
    (4, 3, 1, 79.00, 79.00);

说明一下哈，这里的话，其实是为了让大家学习的时候看得更清楚一点，所以我先手工把 order_id 给写上去了。那到了下一篇我们讲事务下单的时候呢，就会给大家演示，怎么在同一个事务里面去插入订单，接着插入明细，然后再把库存给扣减掉，这个才是实际开发里的情况。

六、基础查询：过滤、排序、分页

1. 查询所有在售商品

SELECT product_code,
       product_name,
       category,
       unit_price,
       stock_qty
FROM inventory.product
WHERE status = 'on_sale'
ORDER BY product_id;

2. 查询库存不足 10 的商品

SELECT product_code,
       product_name,
       stock_qty
FROM inventory.product
WHERE stock_qty < 10
ORDER BY stock_qty ASC;

3. 查询价格最高的 3 个商品

SELECT product_code,
       product_name,
       unit_price
FROM inventory.product
WHERE status = 'on_sale'
ORDER BY unit_price DESC
LIMIT 3;

4. 查询 VIP 客户

SELECT customer_id,
       customer_name,
       phone
FROM sales.customer
WHERE customer_level = 'vip'
  AND status = 'active'
ORDER BY customer_id;

七、多表查询：订单列表

订单主表里面其实只存了一个 customer_id 对吧。那如果你想显示出客户名称的话，这就需要去关联客户表了。

这其实也就是 JOIN 的价值所在。也就是说，数据它往往是分散在不同的表里面的，但是业务上的问题呢，又往往需要把好几个实体的信息给拼在一起看。如果你的订单列表只显示个客户 ID，业务人员看着肯定懵啊，非常不友好。那关联了客户表之后呢，就可以把那种技术上的 ID 给换成让人能看懂的客户名称了。

SELECT o.order_no,
       c.customer_name,
       o.order_status,
       o.total_amount,
       o.created_at
FROM sales.customer_order o
JOIN sales.customer c
  ON o.customer_id = c.customer_id
ORDER BY o.order_id;

那么这里的话，我们用到了表别名，给大家列一下：

别名	表
`o`	`sales.customer_order`
`c`	`sales.customer`

起别名这个操作有个好处。也就是说，它能让 SQL 语句看起来短一点。而且的话，自己读起来也会更容易明白一些。

八、多表查询：订单明细

接着咱们来看第八部分。也就是说，如果你想查看订单里面到底买了哪些商品的话，这个时候就需要把订单主表、订单明细表，还有商品表，这三张表给关联起来了。

SELECT o.order_no,
       p.product_code,
       p.product_name,
       i.quantity,
       i.unit_price,
       i.line_amount
FROM sales.customer_order o
JOIN sales.order_item i
  ON o.order_id = i.order_id
JOIN inventory.product p
  ON i.product_id = p.product_id
ORDER BY o.order_no, i.item_id;

这条 SQL 的话，其实就可以回答这么一个问题：

某个订单里包含哪些商品，每个商品买了几件，成交单价是多少。

那如果咱们只想看已经支付了的订单的情况呢？你可以这么写：

SELECT o.order_no,
       p.product_name,
       i.quantity,
       i.line_amount
FROM sales.customer_order o
JOIN sales.order_item i
  ON o.order_id = i.order_id
JOIN inventory.product p
  ON i.product_id = p.product_id
WHERE o.order_status = 'paid'
ORDER BY o.order_no, i.item_id;

九、聚合统计：每个客户消费了多少钱

再来看第九部分，聚合统计。也就是算一算每个客户到底消费了多少钱。

SELECT c.customer_id,
       c.customer_name,
       COUNT(o.order_id) AS paid_order_count,
       COALESCE(SUM(o.total_amount), 0) AS paid_amount
FROM sales.customer c
LEFT JOIN sales.customer_order o
  ON c.customer_id = o.customer_id
 AND o.order_status = 'paid'
GROUP BY c.customer_id, c.customer_name
ORDER BY paid_amount DESC;

这里的话，有两个小细节需要注意一下。

我们用了 LEFT JOIN。也就是说，哪怕这个客户他没有已经支付的订单，也能把这个客户给显示出来。
用了 COALESCE 这个函数。为什么要用它呢？原因在于，它可以把那种算出来是空的金额给变成 0 来显示，免得看到个空值心里没底。

十、聚合统计：商品销量排行

SELECT p.product_code,
       p.product_name,
       SUM(i.quantity) AS sold_qty,
       SUM(i.line_amount) AS sold_amount
FROM sales.order_item i
JOIN sales.customer_order o
  ON i.order_id = o.order_id
JOIN inventory.product p
  ON i.product_id = p.product_id
WHERE o.order_status = 'paid'
GROUP BY p.product_code, p.product_name
ORDER BY sold_qty DESC, sold_amount DESC;

这条 SQL 可以用于简单商品销售报表。后续讲视图时，可以把它封装成一个报表视图。

从这个例子也能看出，查询语句本身就是业务规则的表达：只统计已支付订单、按商品分组、分别计算销量和销售额。写 SQL 时一定要把统计口径说清楚，否则同样是“销量排行”，不同人可能写出不同结果。

十一、用 CASE WHEN 做状态翻译

数据库里存的是英文状态，例如 paid、created、cancelled。文章或报表中可以用 CASE WHEN 转成中文含义：

SELECT order_no,
       CASE order_status
           WHEN 'created' THEN '已创建'
           WHEN 'paid' THEN '已支付'
           WHEN 'cancelled' THEN '已取消'
           ELSE '未知'
       END AS order_status_name,
       total_amount,
       created_at
FROM sales.customer_order
ORDER BY order_id;

这类写法在业务报表里很常见。

十二、常见问题排查

问题 1：插入客户时报手机号重复

客户表有唯一约束：

CONSTRAINT uk_customer_phone UNIQUE (phone)

如果重复插入 13800000001，会报唯一约束错误。

可以先查：

SELECT *
FROM sales.customer
WHERE phone = '13800000001';

问题 2：插入订单明细时报外键错误

原因通常是 order_id 或 product_id 不存在。

检查订单：

SELECT order_id, order_no
FROM sales.customer_order
ORDER BY order_id;

检查商品：

SELECT product_id, product_code, product_name
FROM inventory.product
ORDER BY product_id;

问题 3：聚合查询报 GROUP BY 错误

如果 SELECT 中有普通字段，又用了 SUM、COUNT 等聚合函数，普通字段一般需要出现在 GROUP BY 中。

正确示例：

SELECT c.customer_id,
       c.customer_name,
       SUM(o.total_amount)
FROM sales.customer c
JOIN sales.customer_order o
  ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

十三、本文小结

这篇文章的话，其实就是接着咱们第五篇讲的关系建模往下说。之前建好的空表嘛，咱们得给它变成那种能查的、能统计的业务数据集，对吧。也就是说，通过客户啊、商品啊，还有订单和订单明细这些个数据，咱们也就算是把从“表结构设计”到“业务查询表达”这个过渡给走完了。

那么在本文里面呢，我们就给 kb_shop 里面去插了一些样例数据进去，接着也完成了好几类查询的操作：

INSERT 批量插入
WHERE 条件过滤
ORDER BY 排序
LIMIT 限制条数
JOIN 多表关联
LEFT JOIN 保留无订单客户
GROUP BY 分组统计
SUM / COUNT 聚合函数
COALESCE 空值处理
CASE WHEN 状态翻译

到了下一篇的话，咱们就要进入事务实战的环节了。到时候会去模拟一个“客户下单买商品”的过程。也就是说，要在同一个事务里面，去把创建订单啊、插入订单明细啊，还有扣减库存这些事给干完。而且的话，还会给大家演示一下，要是中间失败了这个情况，该怎么去回滚数据。咱们这第六篇解决的呢，其实就是“怎么去查询业务数据”的问题。那么第七篇的话，就会往前再走一步，去解决“多个步骤的业务怎么去保证一致性”的这么个问题。