Spanner基础查询 #

SELECT [DISTINCT] expression [, ...]
FROM table_name
[WHERE condition]
[GROUP BY expression [, ...]]
[HAVING condition]
[ORDER BY expression [ASC|DESC] [, ...]]
[LIMIT count [OFFSET skip_count]];

CREATE TABLE users (
    user_id INT64 NOT NULL,
    name STRING(100) NOT NULL,
    email STRING(255),
    age INT64,
    status STRING(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT (CURRENT_TIMESTAMP())
) PRIMARY KEY (user_id);

CREATE TABLE orders (
    order_id INT64 NOT NULL,
    user_id INT64 NOT NULL,
    product_name STRING(200),
    quantity INT64,
    price FLOAT64,
    order_date DATE
) PRIMARY KEY (order_id);

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

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

-- 查询常量
SELECT 1, 'hello', TRUE;

-- 查询表达式
SELECT user_id, name, age * 2 AS double_age FROM users;

-- 使用AS设置别名
SELECT 
    user_id AS id,
    name AS user_name,
    age AS user_age
FROM users;

-- 别名可以省略AS
SELECT user_id id, name user_name FROM users;

-- 表达式别名
SELECT 
    UPPER(name) AS upper_name,
    age + 10 AS age_plus_10
FROM users;

-- 查询不重复的值
SELECT DISTINCT status FROM users;

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

-- 去重计数
SELECT COUNT(DISTINCT user_id) FROM orders;

-- 等于
SELECT * FROM users WHERE user_id = 1;

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

-- 大于、小于
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE age >= 18;
SELECT * FROM users WHERE age < 60;
SELECT * FROM users WHERE age <= 60;

-- 组合比较
SELECT * FROM users WHERE age BETWEEN 18 AND 60;

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

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

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

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

-- IN列表
SELECT * FROM users WHERE user_id IN (1, 2, 3, 4, 5);

-- IN子查询
SELECT * FROM users 
WHERE user_id IN (SELECT user_id FROM orders WHERE quantity > 10);

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

-- 模糊匹配
SELECT * FROM users WHERE name LIKE 'John%';     -- 以John开头
SELECT * FROM users WHERE name LIKE '%Doe';      -- 以Doe结尾
SELECT * FROM users WHERE name LIKE '%mith%';    -- 包含mith

-- 单字符匹配
SELECT * FROM users WHERE name LIKE 'J_hn';      -- J后任意字符后接hn

-- 转义特殊字符
SELECT * FROM users WHERE name LIKE '%\%%' ESCAPE '\';  -- 包含%

-- IS NULL
SELECT * FROM users WHERE email IS NULL;

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

-- IS TRUE / IS FALSE
SELECT * FROM users WHERE is_active IS TRUE;
SELECT * FROM users WHERE is_active IS NOT TRUE;

-- 日期比较
SELECT * FROM orders WHERE order_date = DATE '2024-03-27';

-- 日期范围
SELECT * FROM orders 
WHERE order_date BETWEEN DATE '2024-01-01' AND DATE '2024-12-31';

-- 时间戳比较
SELECT * FROM users 
WHERE created_at > TIMESTAMP '2024-01-01 00:00:00 UTC';

-- 时间函数
SELECT * FROM orders 
WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY);

SELECT * FROM users 
WHERE created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY);

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

-- 降序
SELECT * FROM users ORDER BY user_id DESC;

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

-- 使用列别名排序
SELECT user_id, name, age * 2 AS double_age
FROM users
ORDER BY double_age DESC;

-- 使用列位置排序
SELECT user_id, name, age
FROM users
ORDER BY 3 DESC;  -- 按第3列(age)排序

-- NULL默认排在最后(ASC)或最前(DESC)
SELECT * FROM users ORDER BY email ASC;  -- NULL在最后

-- 显式指定NULL位置(不直接支持NULLS FIRST/LAST)
-- 使用CASE表达式实现
SELECT * FROM users 
ORDER BY 
    CASE WHEN email IS NULL THEN 1 ELSE 0 END,
    email;

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

-- 使用函数排序
SELECT * FROM users ORDER BY UPPER(name);

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

-- 配合ORDER BY
SELECT * FROM users ORDER BY user_id DESC LIMIT 10;

-- 跳过前10行，返回接下来的10行
SELECT * FROM users ORDER BY user_id LIMIT 10 OFFSET 10;

-- 分页查询
-- 第1页: LIMIT 10 OFFSET 0
-- 第2页: LIMIT 10 OFFSET 10
-- 第3页: LIMIT 10 OFFSET 20
SELECT * FROM users ORDER BY user_id LIMIT 10 OFFSET 0;

-- 传统分页(大数据量时性能差)
SELECT * FROM users ORDER BY user_id LIMIT 10 OFFSET 10000;

-- 游标分页(推荐)
-- 记住上一页最后一条记录的主键
SELECT * FROM users 
WHERE user_id > 10000  -- 上一页最后的user_id
ORDER BY user_id 
LIMIT 10;

-- 计数
SELECT COUNT(*) FROM users;
SELECT COUNT(email) FROM users;           -- 非NULL计数
SELECT COUNT(DISTINCT status) FROM users; -- 去重计数

-- 求和
SELECT SUM(quantity) FROM orders;
SELECT SUM(quantity * price) AS total FROM orders;

-- 平均值
SELECT AVG(age) FROM users;

-- 最大最小值
SELECT MAX(age), MIN(age) FROM users;

-- 组合使用
SELECT 
    COUNT(*) AS total_users,
    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 < 18 THEN 'minor'
        WHEN age < 60 THEN 'adult'
        ELSE 'senior'
    END AS age_group,
    COUNT(*) AS count
FROM users
GROUP BY age_group;

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

-- HAVING与WHERE组合
SELECT status, AVG(age) AS avg_age
FROM users
WHERE email IS NOT NULL
GROUP BY status
HAVING AVG(age) > 25;

-- 加减乘除
SELECT age + 10, age - 5, age * 2, age / 10 FROM users;

-- 取模
SELECT age % 10 FROM users;

-- 一元运算
SELECT -age FROM users;

-- 拼接
SELECT CONCAT(name, ' - ', email) FROM users;
SELECT name || ' (' || email || ')' FROM users;

-- 长度
SELECT name, LENGTH(name) FROM users;

-- 大小写
SELECT UPPER(name), LOWER(name) FROM users;

-- 子字符串
SELECT SUBSTR(name, 1, 3) FROM users;

-- 替换
SELECT REPLACE(name, 'John', 'Jane') FROM users;

-- 去空格
SELECT TRIM(name), LTRIM(name), RTRIM(name) FROM users;

-- CASE WHEN
SELECT 
    name,
    CASE 
        WHEN age < 18 THEN 'minor'
        WHEN age < 60 THEN 'adult'
        ELSE 'senior'
    END AS age_group
FROM users;

-- CASE表达式(简单形式)
SELECT 
    status,
    CASE status
        WHEN 'active' THEN 'Active User'
        WHEN 'inactive' THEN 'Inactive User'
        ELSE 'Unknown'
    END AS status_desc
FROM users;

-- COALESCE
SELECT COALESCE(email, 'no-email') FROM users;

-- IFNULL
SELECT IFNULL(email, 'no-email') FROM users;

-- NULLIF
SELECT NULLIF(status, 'active') FROM users;

-- 当前日期时间
SELECT CURRENT_DATE();
SELECT CURRENT_TIMESTAMP();

-- 提取部分
SELECT 
    EXTRACT(YEAR FROM created_at) AS year,
    EXTRACT(MONTH FROM created_at) AS month,
    EXTRACT(DAY FROM created_at) AS day
FROM users;

-- 日期运算
SELECT 
    DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY) AS tomorrow,
    DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH) AS last_month
FROM users;

-- 格式化
SELECT FORMAT_DATE('%Y-%m-%d', CURRENT_DATE());
SELECT FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', CURRENT_TIMESTAMP());

# 执行查询
gcloud spanner databases execute-sql my-database \
    --instance=my-instance \
    --sql="SELECT * FROM users LIMIT 10"

# 从文件执行
gcloud spanner databases execute-sql my-database \
    --instance=my-instance \
    --sql-file=query.sql

// Java查询
DatabaseClient client = spanner.getDatabaseClient(databaseId);

try (ResultSet resultSet = client.singleUse().executeQuery(
    Statement.of("SELECT * FROM users WHERE age > 18"))) {
    
    while (resultSet.next()) {
        long userId = resultSet.getLong(0);
        String name = resultSet.getString(1);
        System.out.println(userId + ": " + name);
    }
}

# Python查询
def query_users(database):
    with database.snapshot() as snapshot:
        results = snapshot.execute_sql(
            "SELECT * FROM users WHERE age > 18"
        )
        
        for row in results:
            print(row)

// Go查询
func queryUsers(ctx context.Context, client *spanner.Client) error {
    iter := client.Single().Query(ctx, spanner.Statement{
        SQL: "SELECT * FROM users WHERE age > @age",
        Params: map[string]interface{}{
            "age": 18,
        },
    })
    defer iter.Stop()
    
    for {
        row, err := iter.Next()
        if err == iterator.Done {
            break
        }
        if err != nil {
            return err
        }
        
        var userID int64
        var name string
        if err := row.Columns(&userID, &name); err != nil {
            return err
        }
        fmt.Printf("%d: %s\n", userID, name)
    }
    return nil
}

-- 使用EXPLAIN查看执行计划
EXPLAIN SELECT * FROM users WHERE user_id = 1;

-- 使用EXPLAIN ANALYZE查看实际执行统计
EXPLAIN ANALYZE SELECT * FROM users WHERE user_id = 1;

执行计划示例:
┌─────────────────────────────────────────────────────────────┐
│ Distributed Union on users                                  │
│ └── Distributed Cross Apply                                 │
│     ├── Table Scan on users                                 │
│     │   └── Filter: user_id = 1                             │
│     └── Index Scan on idx_users_email                       │
└─────────────────────────────────────────────────────────────┘

关键指标:
├── rows: 返回行数
├── bytes: 返回字节数
├── latency: 执行延迟
└── scan: 扫描方式

-- 创建索引
CREATE INDEX idx_users_status ON users(status);

-- 使用索引的查询
SELECT * FROM users WHERE status = 'active';

-- 强制使用索引
SELECT * FROM users@{FORCE_INDEX=idx_users_status} 
WHERE status = 'active';

-- 不推荐: 全表扫描
SELECT * FROM users;

-- 推荐: 使用主键或索引
SELECT * FROM users WHERE user_id = 1;

-- 不推荐: 使用函数导致索引失效
SELECT * FROM users WHERE UPPER(name) = 'JOHN';

-- 推荐: 避免在条件列上使用函数
SELECT * FROM users WHERE name = 'John';

-- 使用查询提示优化
SELECT * FROM users@{FORCE_INDEX=idx_users_status}
WHERE status = 'active';

-- 设置查询超时
SELECT * FROM users@{QUERY_TIMEOUT=10s}
WHERE status = 'active';

查询性能建议:
├── 只查询需要的列
├── 使用索引加速查询
├── 避免SELECT *
├── 使用LIMIT限制结果
└── 使用分页避免大量数据

查询安全建议:
├── 使用参数化查询防止SQL注入
├── 验证用户输入
├── 设置查询超时
└── 限制返回数据量

查询可读性建议:
├── 使用有意义的别名
├── 格式化SQL语句
├── 添加注释说明
└── 复杂查询分解为简单查询

1. 使用索引
   └── 加速查询

2. 限制结果
   └── 使用LIMIT

3. 参数化查询
   └── 防止SQL注入

4. 查看执行计划
   └── 优化查询

5. 分页查询
   └── 使用游标分页