基础查询 #

-- 基本查询语法
SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column
LIMIT n;

-- 示例表
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    age INT,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO users (name, email, age, status) VALUES
    ('Alice', 'alice@example.com', 25, 'active'),
    ('Bob', 'bob@example.com', 30, 'active'),
    ('Carol', 'carol@example.com', 28, 'inactive'),
    ('David', 'david@example.com', 35, 'active'),
    ('Eve', 'eve@example.com', 22, 'pending');

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

-- 查询指定列
SELECT name, email FROM users;

-- 使用 AS 设置列别名
SELECT 
    name AS user_name,
    email AS email_address,
    age AS user_age
FROM users;

-- 别名可以省略 AS
SELECT name user_name, email email_address
FROM users;

-- 使用表达式别名
SELECT 
    name,
    age,
    age * 2 AS double_age,
    UPPER(name) AS upper_name
FROM users;

-- DISTINCT 去重
SELECT DISTINCT status FROM users;

-- DISTINCT 多列
SELECT DISTINCT status, age FROM users;

-- DISTINCT ON (PostgreSQL风格)
SELECT DISTINCT ON (status) *
FROM users
ORDER BY status, age DESC;

-- 等于
SELECT * FROM users WHERE status = 'active';

-- 不等于
SELECT * FROM users WHERE status != 'inactive';
SELECT * FROM users WHERE status <> 'inactive';

-- 大于、小于
SELECT * FROM users WHERE age > 25;
SELECT * FROM users WHERE age >= 25;
SELECT * FROM users WHERE age < 30;
SELECT * FROM users WHERE age <= 30;

-- BETWEEN
SELECT * FROM users WHERE age BETWEEN 25 AND 30;

-- NULL 判断
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

-- AND
SELECT * FROM users
WHERE status = 'active' AND age > 25;

-- OR
SELECT * FROM users
WHERE status = 'active' OR status = 'pending';

-- NOT
SELECT * FROM users
WHERE NOT status = 'inactive';

-- 组合使用
SELECT * FROM users
WHERE (status = 'active' OR status = 'pending')
  AND age >= 25;

-- IN
SELECT * FROM users
WHERE status IN ('active', 'pending');

-- NOT IN
SELECT * FROM users
WHERE status NOT IN ('inactive', 'deleted');

-- 子查询
SELECT * FROM users
WHERE id IN (
    SELECT user_id FROM orders
);

-- LIKE 模式匹配
SELECT * FROM users WHERE email LIKE '%@example.com';
SELECT * FROM users WHERE name LIKE 'A%';
SELECT * FROM users WHERE name LIKE '%a%';

-- ILIKE 不区分大小写
SELECT * FROM users WHERE email ILIKE '%@EXAMPLE.COM';

-- 通配符说明
-- %: 匹配任意字符序列
-- _: 匹配单个字符
SELECT * FROM users WHERE name LIKE '_lice';

-- 正则表达式
SELECT * FROM users WHERE name ~ '^A';
SELECT * FROM users WHERE name ~* '^a';  -- 不区分大小写
SELECT * FROM users WHERE name !~ '^A';  -- 不匹配

-- 升序排序 (默认)
SELECT * FROM users ORDER BY age;
SELECT * FROM users ORDER BY age ASC;

-- 降序排序
SELECT * FROM users ORDER BY age DESC;

-- 多列排序
SELECT * FROM users
ORDER BY status ASC, age DESC;

-- 使用表达式排序
SELECT * FROM users
ORDER BY LENGTH(name) DESC;

-- 使用列号排序
SELECT name, age FROM users
ORDER BY 2 DESC;  -- 按第二列排序

-- NULL 排序
SELECT * FROM users
ORDER BY email NULLS FIRST;

SELECT * FROM users
ORDER BY email NULLS LAST;

排序性能优化
┌─────────────────────────────────────────────────────────────┐
│                                                             │
│   1. 使用索引                                               │
│   ├── ORDER BY 列有索引                                    │
│   └── 避免排序操作                                         │
│                                                             │
│   2. 限制结果集                                             │
│   ├── 使用 LIMIT                                           │
│   └── 减少排序数据量                                       │
│                                                             │
│   3. 避免 SELECT *                                          │
│   ├── 只选择需要的列                                       │
│   └── 减少数据传输                                         │
│                                                             │
│   4. 使用覆盖索引                                           │
│   ├── 索引包含所有查询列                                   │
│   └── 避免回表                                             │
│                                                             │
└─────────────────────────────────────────────────────────────┘

-- LIMIT 限制返回行数
SELECT * FROM users LIMIT 5;

-- LIMIT + OFFSET 分页
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;   -- 第1页

SELECT * FROM users
ORDER BY created_at DESC
LIMIT 10 OFFSET 10;  -- 第2页

SELECT * FROM users
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;  -- 第3页

-- 键集分页 (更高效)
-- 第一页
SELECT * FROM users
ORDER BY created_at DESC, id
LIMIT 10;

-- 下一页 (使用上一页最后一条记录的值)
SELECT * FROM users
WHERE (created_at, id) < ('2024-01-01 00:00:00', 'last-uuid')
ORDER BY created_at DESC, id
LIMIT 10;

-- 键集分页优点:
-- 1. 不需要扫描跳过的行
-- 2. 性能稳定，不受偏移量影响
-- 3. 适合大数据量分页

分页最佳实践
┌─────────────────────────────────────────────────────────────┐
│                                                             │
│   LIMIT/OFFSET:                                             │
│   ├── 简单易用                                             │
│   ├── 适合小数据量                                         │
│   └── 大偏移量性能差                                       │
│                                                             │
│   键集分页:                                                 │
│   ├── 性能稳定                                             │
│   ├── 适合大数据量                                         │
│   └── 需要唯一排序列                                       │
│                                                             │
│   建议:                                                     │
│   ├── 小数据量: LIMIT/OFFSET                               │
│   ├── 大数据量: 键集分页                                   │
│   └── 避免大偏移量                                         │
│                                                             │
└─────────────────────────────────────────────────────────────┘

-- COUNT
SELECT COUNT(*) FROM users;
SELECT COUNT(email) FROM users;  -- 不计算NULL
SELECT COUNT(DISTINCT status) FROM users;

-- SUM
SELECT SUM(age) FROM users;

-- AVG
SELECT AVG(age) FROM users;

-- MAX/MIN
SELECT MAX(age), MIN(age) FROM users;

-- 组合使用
SELECT 
    COUNT(*) AS total,
    COUNT(email) AS with_email,
    AVG(age) AS avg_age,
    MAX(age) AS max_age,
    MIN(age) AS min_age
FROM users;

-- 基本分组
SELECT status, COUNT(*) as count
FROM users
GROUP BY status;

-- 多列分组
SELECT status, age, COUNT(*) as count
FROM users
GROUP BY status, age;

-- 使用表达式分组
SELECT 
    CASE 
        WHEN age < 25 THEN 'young'
        WHEN age < 35 THEN 'middle'
        ELSE 'senior'
    END AS age_group,
    COUNT(*) as count
FROM users
GROUP BY 
    CASE 
        WHEN age < 25 THEN 'young'
        WHEN age < 35 THEN 'middle'
        ELSE 'senior'
    END;

-- HAVING 过滤分组结果
SELECT status, COUNT(*) as count
FROM users
GROUP BY status
HAVING COUNT(*) > 1;

-- HAVING vs WHERE
-- WHERE: 过滤行 (分组前)
-- HAVING: 过滤分组 (分组后)

SELECT status, AVG(age) as avg_age
FROM users
WHERE email IS NOT NULL  -- 先过滤行
GROUP BY status
HAVING AVG(age) > 25;    -- 再过滤分组

-- 算术运算符
SELECT 
    age,
    age + 5 AS age_plus_5,
    age - 5 AS age_minus_5,
    age * 2 AS age_double,
    age / 2 AS age_half,
    age % 3 AS age_mod
FROM users;

-- 数学函数
SELECT 
    ABS(-5) AS abs_val,
    ROUND(3.14159, 2) AS rounded,
    FLOOR(3.9) AS floored,
    CEIL(3.1) AS ceiled,
    POWER(2, 3) AS power_val,
    SQRT(16) AS sqrt_val;

-- 字符串函数
SELECT 
    name,
    UPPER(name) AS upper_name,
    LOWER(name) AS lower_name,
    LENGTH(name) AS name_length,
    LEFT(name, 3) AS first_3,
    RIGHT(name, 3) AS last_3,
    SUBSTRING(name, 1, 3) AS substr,
    CONCAT(name, ' - ', email) AS full_info,
    name || ' (' || email || ')' AS formatted
FROM users;

-- 字符串处理
SELECT 
    TRIM('  hello  ') AS trimmed,
    LTRIM('  hello  ') AS ltrimmed,
    RTRIM('  hello  ') AS rtrimmed,
    REPLACE('hello world', 'world', 'CockroachDB') AS replaced,
    SPLIT_PART('a,b,c', ',', 2) AS part;

-- 当前时间
SELECT 
    NOW() AS current_time,
    CURRENT_DATE AS current_date,
    CURRENT_TIMESTAMP AS current_timestamp;

-- 日期提取
SELECT 
    created_at,
    EXTRACT(YEAR FROM created_at) AS year,
    EXTRACT(MONTH FROM created_at) AS month,
    EXTRACT(DAY FROM created_at) AS day,
    EXTRACT(HOUR FROM created_at) AS hour,
    EXTRACT(DOW FROM created_at) AS day_of_week
FROM users;

-- 日期计算
SELECT 
    created_at,
    created_at + INTERVAL '1 day' AS tomorrow,
    created_at - INTERVAL '1 week' AS last_week,
    created_at + INTERVAL '1 month' AS next_month,
    AGE(created_at) AS age
FROM users;

-- 日期格式化
SELECT 
    created_at,
    TO_CHAR(created_at, 'YYYY-MM-DD') AS date_str,
    TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI:SS') AS datetime_str
FROM users;

-- CASE 表达式
SELECT 
    name,
    age,
    CASE 
        WHEN age < 25 THEN 'Young'
        WHEN age < 35 THEN 'Middle'
        ELSE 'Senior'
    END AS age_group
FROM users;

-- 简单 CASE
SELECT 
    status,
    CASE status
        WHEN 'active' THEN 'Active User'
        WHEN 'inactive' THEN 'Inactive User'
        ELSE 'Other'
    END AS status_desc
FROM users;

-- COALESCE 返回第一个非NULL值
SELECT 
    name,
    email,
    COALESCE(email, 'No email') AS email_or_default
FROM users;

-- NULLIF 如果相等返回NULL
SELECT 
    name,
    NULLIF(age, 0) AS age_non_zero
FROM users;

-- GREATEST/LEAST
SELECT 
    GREATEST(1, 2, 3) AS max_val,
    LEAST(1, 2, 3) AS min_val;

-- 标量子查询 (返回单个值)
SELECT 
    name,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;

-- 在WHERE中使用
SELECT * FROM users
WHERE age > (SELECT AVG(age) FROM users);

-- IN 子查询
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders);

-- EXISTS 子查询
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id
);

-- NOT EXISTS
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id
);

-- 派生表 (FROM中的子查询)
SELECT u.name, o.order_count
FROM users u
JOIN (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    GROUP BY user_id
) o ON u.id = o.user_id;

-- WITH 子句 (CTE)
WITH user_orders AS (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    GROUP BY user_id
)
SELECT u.name, uo.order_count
FROM users u
LEFT JOIN user_orders uo ON u.id = uo.user_id;

-- 基本执行计划
EXPLAIN SELECT * FROM users WHERE status = 'active';

-- 详细执行计划
EXPLAIN ANALYZE SELECT * FROM users WHERE status = 'active';

-- 执行计划示例
EXPLAIN SELECT name, email FROM users WHERE age > 25;

-- 输出示例:
--                                              │
--        • scan                               │
--          table: users@primary               │
--          spans: FULL SCAN                   │
--          filter: age > 25                   │

查询优化建议
┌─────────────────────────────────────────────────────────────┐
│                                                             │
│   1. 使用索引                                               │
│   ├── WHERE 条件使用索引列                                 │
│   └── ORDER BY 使用索引列                                  │
│                                                             │
│   2. 避免 SELECT *                                          │
│   ├── 只选择需要的列                                       │
│   └── 减少数据传输                                         │
│                                                             │
│   3. 使用覆盖索引                                           │
│   ├── 索引包含所有查询列                                   │
│   └── 避免回表                                             │
│                                                             │
│   4. 合理使用 LIMIT                                         │
│   ├── 限制结果集大小                                       │
│   └── 提高查询效率                                         │
│                                                             │
│   5. 避免 OR                                                │
│   ├── 使用 UNION 或 IN 代替                                │
│   └── 提高索引利用率                                       │
│                                                             │
└─────────────────────────────────────────────────────────────┘