查询数据 #

SELECT特点：

✓ 必须指定分区键
✓ 支持聚簇列范围查询
✓ 支持聚合函数
✓ 支持分页
✓ 支持JSON输出

SELECT [JSON] select_expression [, ...]
FROM [keyspace_name.]table_name
[WHERE where_clause]
[GROUP BY column_name]
[ORDER BY column_name [ASC | DESC]]
[LIMIT n]
[ALLOW FILTERING];

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

-- 查询指定键空间的表
SELECT * FROM my_app.users;

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

-- 查询并设置别名
SELECT name AS user_name, email AS user_email FROM users;

-- 查询总行数
SELECT COUNT(*) FROM users;

-- 查询特定列的非空计数
SELECT COUNT(email) FROM users;

-- 单列分区键
SELECT * FROM users 
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- 复合分区键
SELECT * FROM sensor_data 
WHERE sensor_id = 'sensor-001' 
AND date = '2024-01-15' 
AND hour = 10;

-- 使用IN查询多个分区
SELECT * FROM users 
WHERE user_id IN (
    uuid1, 
    uuid2, 
    uuid3
);

-- 精确匹配
SELECT * FROM orders 
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000
AND order_id = 660e8400-e29b-41d4-a716-446655440001;

-- 范围查询
SELECT * FROM orders 
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000
AND order_date > '2024-01-01'
AND order_date < '2024-02-01';

-- 多条件组合
SELECT * FROM events 
WHERE device_id = 'device-001'
AND event_date = '2024-01-15'
AND event_time >= '10:00:00'
AND event_time < '12:00:00';

WHERE条件规则：

必须包含
├── 完整的分区键
└── 可以使用IN

聚簇列
├── 按顺序使用
├── 支持范围查询
└── 范围查询后不能有其他条件

限制
├── 不支持OR
├── 不支持JOIN
└── ALLOW FILTERING慎用

-- 按聚簇列排序
SELECT * FROM orders 
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000
ORDER BY order_date DESC;

-- 多列排序
SELECT * FROM events 
WHERE device_id = 'device-001'
ORDER BY event_date DESC, event_time ASC;

排序限制：

仅支持聚簇列
├── 不能按非聚簇列排序
└── 排序方向必须与表定义一致

表定义：
CREATE TABLE orders (
    user_id UUID,
    order_date DATE,
    order_id UUID,
    ...
    PRIMARY KEY (user_id, order_date, order_id)
) WITH CLUSTERING ORDER BY (order_date DESC, order_id ASC);

有效排序：
├── ORDER BY order_date DESC
├── ORDER BY order_date DESC, order_id ASC
└── ORDER BY order_id ASC（必须同时指定order_date）

无效排序：
├── ORDER BY order_date ASC（方向不一致）
└── ORDER BY amount DESC（非聚簇列）

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

-- 组合条件
SELECT * FROM orders 
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000
LIMIT 100;

-- 第一页
SELECT * FROM orders 
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000
LIMIT 100;

-- 第二页（使用上一页最后一条记录的主键）
SELECT * FROM orders 
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000
AND order_id > last_order_id_from_page1
LIMIT 100;

-- 使用Token分页
SELECT * FROM users 
WHERE token(user_id) > token(last_user_id)
LIMIT 100;

// Java驱动分页
Statement statement = QueryBuilder.select()
    .all()
    .from("orders")
    .where(QueryBuilder.eq("user_id", userId))
    .setFetchSize(100);

ResultSet resultSet = session.execute(statement);

for (Row row : resultSet) {
    // 处理每一行
    // 当达到fetchSize时，驱动会自动获取下一页
}

# Python驱动分页
query = "SELECT * FROM orders WHERE user_id = %s"
statement = SimpleStatement(query, fetch_size=100)

rows = session.execute(statement, (user_id,))
for row in rows:
    # 处理每一行
    pass

-- COUNT
SELECT COUNT(*) FROM users;
SELECT COUNT(email) FROM users;

-- SUM
SELECT SUM(amount) FROM orders 
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- AVG
SELECT AVG(age) FROM users;

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

-- 按分区键分组
SELECT user_id, COUNT(*) as order_count, SUM(amount) as total_amount
FROM orders
GROUP BY user_id;

-- 按聚簇列分组
SELECT user_id, order_date, COUNT(*) as daily_count
FROM orders
GROUP BY user_id, order_date;

聚合限制：

GROUP BY
├── 只能按主键列分组
├── 必须按顺序使用主键列
└── 分区键必须完整

不支持
├── HAVING子句
├── 嵌套聚合
└── 自定义聚合函数

-- 输出JSON格式
SELECT JSON user_id, name, email FROM users 
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- 输出示例
-- {"user_id": "550e8400-e29b-41d4-a716-446655440000", "name": "张三", "email": "zhang@example.com"}

-- 列转JSON
SELECT toJson(name), toJson(email) FROM users;

-- 整行转JSON
SELECT toJson(users) FROM users LIMIT 1;

-- 允许非主键列过滤（慎用！）
SELECT * FROM users 
WHERE email = 'zhang@example.com'
ALLOW FILTERING;

-- 允许非主键列范围查询
SELECT * FROM products 
WHERE price > 100 AND price < 500
ALLOW FILTERING;

ALLOW FILTERING影响：

全分区扫描
├── 扫描所有分区
├── 性能极差
└── 可能超时

适用场景
├── 数据量小
├── 开发测试
└── 偶尔使用

替代方案
├── 创建二级索引
├── 创建物化视图
└── 重新设计表结构

-- UUID函数
SELECT uuid() FROM system.local;
SELECT now() FROM system.local;

-- 时间函数
SELECT toTimestamp(now()) FROM system.local;
SELECT toDate(created_at), toTime(created_at) FROM users;

-- 字符串函数
SELECT lower(name), upper(email) FROM users;
SELECT length(name) FROM users;

-- Token函数
SELECT token(user_id) FROM users;

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

-- 不等于（不支持）
-- SELECT * FROM users WHERE user_id != ?;

-- 大于/小于
SELECT * FROM orders 
WHERE user_id = ? AND order_date > '2024-01-01';

-- 范围
SELECT * FROM orders 
WHERE user_id = ? AND order_date >= '2024-01-01' AND order_date <= '2024-01-31';

-- IN
SELECT * FROM users WHERE user_id IN (?, ?, ?);

-- CONTAINS（集合类型）
SELECT * FROM users WHERE tags CONTAINS 'vip';
SELECT * FROM users WHERE preferences CONTAINS KEY 'theme';

查询优化建议：

1. 使用主键查询
   └── 最高效的查询方式

2. 避免ALLOW FILTERING
   └── 使用索引或物化视图

3. 合理使用LIMIT
   └── 避免返回过多数据

4. 使用分页
   └── 大数据集分页查询

5. 选择合适的列
   └── 避免SELECT *

6. 监控查询性能
   └── 使用tracing分析

-- 启用查询追踪
TRACING ON;

-- 执行查询
SELECT * FROM users WHERE user_id = ?;

-- 查看追踪结果
-- 显示查询执行的详细信息