CREATE TABLE kes01.db_info (
id INT,
name VARCHAR(20)
);
INSERT INTO kes01.db_info VALUES (1, 'kingbase');
SELECT * FROM kes01.db_info;
✔️ 修改模式名称
ALTER SCHEMA db01 RENAME TO db02;
SELECT * FROM sys_namespace WHERE nspname = 'db02';
✔️ 删除模式
DROP SCHEMA db02 CASCADE;
三、表与分区：实体建模与性能优化
🛠 创建关系表
CREATE TABLE customers (
C_ID INT AUTO_INCREMENT PRIMARY KEY,
C_NAME VARCHAR(50) NOT NULL,
C_ADDRESS TEXT NOT NULL,
C_PHONE VARCHAR(15) NOT NULL
);
👉 分区表示例：商品表（items）
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)
);
📦 订单表及外键关联
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)
);
🔧 修改表结构
ALTER TABLE orders ADD CONSTRAINT pk_orders PRIMARY KEY (O_ID);
ALTER TABLE orders ADD COLUMN comment VARCHAR(20);
提示：
使用 AUTO_INCREMENT 或 序列 实现自增主键；
分区表适用于大数据量、性能优化场景。
四、示例数据：插入 & 查看
插入业务测试数据
customers
items
orders
INSERT INTO customers (...) VALUES (...); -- 10 条示例
INSERT INTO items (...) VALUES (...);
INSERT INTO orders (...) VALUES (...);
查看数据
SELECT * FROM customers;
SELECT * FROM items;
SELECT * FROM orders;
五、索引：查询性能提升器
常用索引操作
✔️ 创建索引
CREATE INDEX idx_c_name ON customers (C_NAME);
CREATE INDEX idx_i_price ON items (I_PRICE) LOCAL;
CREATE INDEX idx_o_i_d ON orders (O_ID, O_DATE);
CREATE UNIQUE INDEX o_u_id ON orders (O_ID);
📊 查看索引
SELECT * FROM sys_indexes WHERE schemaname = 'public';
🛠 修改索引
ALTER INDEX o_u_id RENAME TO indx_rename_u_id;
ALTER INDEX indx_rename_u_id SET (fillfactor = 60);
🔄 重建索引
REINDEX INDEX idx_c_name;
REINDEX TABLE orders;
❌ 删除索引
DROP INDEX indx_rename_u_id;
六、查询：SELECT、GROUP BY、JOIN、子查询、WITH
-- 简单查询
SELECT * FROM orders;
-- 聚合与分组
SELECT I_NAME, I_STOCK FROM items GROUP BY I_NAME, I_STOCK;
-- JOIN 查询
SELECT c.C_NAME, COUNT(o.O_ID) AS order_count
FROM customers c
JOIN orders o ON c.C_ID = o.C_ID
GROUP BY c.C_NAME;
-- 子查询
SELECT * FROM orders
WHERE O_TOTAL_PRICE > (
SELECT AVG(O_TOTAL_PRICE) FROM orders
);
-- WITH 子句（临时视图）
WITH customer_total_amount AS (
SELECT c.C_NAME, SUM(o.O_TOTAL_PRICE) AS total_amount
FROM customers c
JOIN orders o ON c.C_ID = o.C_ID
GROUP BY c.C_NAME
)
SELECT * FROM customer_total_amount
ORDER BY total_amount DESC
LIMIT 5;
七、视图：逻辑封装与重复查询重用
创建示例视图
CREATE OR REPLACE VIEW sales_summary AS
SELECT DATE(o.O_DATE) AS order_date, COUNT(o.O_ID) AS order_count,
SUM(o.O_TOTAL_PRICE) AS total_sales
FROM orders o
GROUP BY DATE(o.O_DATE);
CREATE OR REPLACE VIEW low_stock_items AS
SELECT i.I_ID, i.I_NAME, i.I_STOCK
FROM items i
WHERE i.I_STOCK < 60;
CREATE OR REPLACE VIEW customer_purchase_history AS
SELECT c.C_NAME, o.O_DATE, i.I_NAME, o.O_QUANTITY, o.O_TOTAL_PRICE
FROM customers c
JOIN orders o ON c.C_ID = o.C_ID
JOIN items i ON o.I_ID = i.I_ID;
查询与查看
SELECT * FROM sales_summary WHERE order_date = '2023-10-01';
SELECT * FROM low_stock_items;
SELECT * FROM customer_purchase_history WHERE C_NAME = 'Alice Smith';
SELECT * FROM sys_views WHERE schemaname = 'public';
删除视图
DROP VIEW customer_purchase_history;
八、函数：封装逻辑与复用
CREATE OR REPLACE FUNCTION calculate_discount_amount(_o_id INT)
RETURNS DECIMAL(8,2) AS $$
DECLARE total_price DECIMAL(8,2);
BEGIN
SELECT O_TOTAL_PRICE INTO total_price FROM orders WHERE O_ID = _o_id;
RETURN total_price * 0.10;
END$$ LANGUAGE plpgsql;
SELECT O_TOTAL_PRICE FROM orders WHERE O_ID = 1;
SELECT calculate_discount_amount(1) AS discount_amount;
九、触发器：自动化 & 数据完整性
示例：订单插入后自动扣库存
CREATE TRIGGER update_stock_AFTER_INSERT
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
UPDATE items
SET I_STOCK = I_STOCK - NEW.O_QUANTITY
WHERE I_ID = NEW.I_ID;
END;
-- 测试触发器
INSERT INTO orders VALUES (11, '2023-11-01', 1, 1, 1, 200);
SELECT * FROM items WHERE I_ID = 1; -- 库存应减：49
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
照妖镜
