-- PostgreSQL 特色功能示例

-- 1. 递归CTE（查询组织架构）
WITH RECURSIVE org_tree AS (
    SELECT id, name, manager_id, 1 as level
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, ot.level + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree;

-- 2. 窗口函数（排名统计）
SELECT 
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
    SUM(salary) OVER (PARTITION BY department) as dept_total
FROM employees;

-- 3. JSONB 操作
SELECT data->>'name' FROM orders
WHERE data @> '{"status": "completed"}';

-- 4. 数组类型
CREATE TABLE tags (
    id INT,
    name VARCHAR(50),
    tags TEXT[]
);
INSERT INTO tags VALUES(1, 'PostgreSQL', ARRAY['database', 'open source', 'relational']);

-- 5. PostGIS 空间查询
SELECT name FROM locations
WHERE ST_DWithin(location, ST_MakePoint(116.4, 39.9)::geography, 5000);

// MongoDB 特色操作示例

// 1. 插入文档（无需预定义结构）
db.users.insertOne({
    username: "john",
    email: "john@example.com",
    profile: {
        age: 30,
        city: "Beijing",
        interests: ["coding", "music"]
    },
    createdAt: new Date()
});

// 2. 灵活查询
db.users.find({
    "profile.age": { $gt: 25 },
    "profile.city": "Beijing"
});

// 3. 聚合管道
db.orders.aggregate([
    { $match: { status: "completed" } },
    { $group: {
        _id: "$customer_id",
        totalAmount: { $sum: "$amount" },
        orderCount: { $sum: 1 }
    }},
    { $sort: { totalAmount: -1 } },
    { $limit: 10 }
]);

// 4. 更新数组元素
db.users.updateOne(
    { username: "john" },
    { $push: { "profile.interests": "travel" } }
);

// 5. 事务操作（4.0+）
const session = db.getMongo().startSession();
session.startTransaction();
try {
    session.getDatabase("shop").orders.insertOne({ ... });
    session.getDatabase("shop").inventory.updateOne({ ... });
    session.commitTransaction();
} catch (e) {
    session.abortTransaction();
}

# Redis 特色操作示例

# 1. 字符串操作
SET user:1001 '{"name":"John","age":30}'
GET user:1001
INCR page:views:2024
DECR product:stock:500

# 2. 哈希操作（存储对象）
HSET user:1001 name John age 30 city Beijing
HGET user:1001 name
HGETALL user:1001

# 3. 列表操作（消息队列）
RPUSH queue:tasks "task1" "task2" "task3"
LPOP queue:tasks
LRANGE queue:tasks 0 -1

# 4. 集合操作（标签、共同好友）
SADD user:1001:tags mysql postgres redis
SADD user:1002:tags mysql python
SINTER user:1001:tags user:1002:tags  # 共同标签

# 5. 有序集合（排行榜）
ZADD leaderboard 2500 player1 1800 player2 3000 player3
ZREVRANGE leaderboard 0 9 WITHSCORES
ZINCRBY leaderboard 500 player1

# 6. 发布/订阅
PSUBSCRIBE news:*
PUBLISH news:sports "New match today!"

# 7. 事务
MULTI
SET key1 value1
SET key2 value2
EXEC

# 8. Lua脚本（原子操作）
EVAL "return redis.call('get', KEYS[1])" 1 mykey

-- MariaDB 特色功能示例

-- 1. 窗口函数支持
SELECT 
    name, department, salary,
    SUM(salary) OVER (PARTITION BY department) as dept_total
FROM employees;

-- 2. JSON支持
SELECT JSON_VALUE(info, '$.name') FROM users;

-- 3. 动态列
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    attributes DYNAMIC
);
INSERT INTO products VALUES(1, 'Shirt', COLUMNS('color'='red', 'size'='L'));

-- 4.  Invisible 列
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100) INVISIBLE
);

-- 5. 并行复制
SET GLOBAL slave_parallel_threads = 4;
SET GLOBAL slave_parallel_mode = 'optimistic';

-- 6. 序列引擎
SELECT * FROM seq_1_to_100;

-- TiDB 特色功能示例

-- 1. 自动分片和分布式事务
BEGIN;
INSERT INTO users (name, age) VALUES('John', 30);
INSERT INTO orders (user_id, amount) VALUES(LAST_INSERT_ID(), 100);
COMMIT;

-- 2. 智能选择（TiFlash列式存储）
SELECT /*+ READ_FROM_STORAGE(tiflash[orders]) */ 
    SUM(amount) as total
FROM orders
WHERE date > '2024-01-01';

-- 3. 热点小表合并
SPLIT TABLE users BETWEEN (0) AND (1000000) REGIONS 10;

-- 4. 查看分布式执行计划
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 100;

-- 5. 跨区域复制 (DR 集群)
-- 配置主集群和灾备集群

-- 6. 在线DDL
ALTER TABLE users ADD COLUMN phone VARCHAR(20);