CQL基础语法 #

-- CQL语法示例
SELECT * FROM users WHERE user_id = 123;

命名规则：
├── 可包含字母、数字、下划线
├── 必须以字母或下划线开头
├── 不区分大小写（默认）
├── 使用双引号可区分大小写
└── 最大长度48字符

-- 合法的标识符
CREATE TABLE users (...);
CREATE TABLE user_profiles (...);
CREATE TABLE _temp_data (...);

-- 区分大小写（使用双引号）
CREATE TABLE "Users" (...);
CREATE TABLE "UserProfile" (...);

-- 不推荐但合法
CREATE TABLE "user-table" (...);  -- 包含特殊字符

-- 需要转义的保留字
SELECT "select", "from", "where" FROM my_table;

-- 常见保留字
-- ADD, ALL, ALLOW, ALTER, AND, ANY, APPLY, AS, ASC, AUTHORIZE
-- BATCH, BEGIN, BY
-- COLUMNFAMILY, CREATE
-- DELETE, DESC, DISTINCT, DROP
-- ...

-- List - 有序列表
CREATE TABLE example (
    id INT PRIMARY KEY,
    tags LIST<TEXT>
);

INSERT INTO example (id, tags) 
VALUES (1, ['tag1', 'tag2', 'tag3']);

-- Set - 无序集合
CREATE TABLE example_set (
    id INT PRIMARY KEY,
    emails SET<TEXT>
);

INSERT INTO example_set (id, emails) 
VALUES (1, {'a@example.com', 'b@example.com'});

-- Map - 键值对
CREATE TABLE example_map (
    id INT PRIMARY KEY,
    attributes MAP<TEXT, TEXT>
);

INSERT INTO example_map (id, attributes) 
VALUES (1, {'key1': 'value1', 'key2': 'value2'});

-- 创建UDT
CREATE TYPE address (
    street TEXT,
    city TEXT,
    zip_code TEXT
);

-- 使用UDT
CREATE TABLE users (
    user_id UUID PRIMARY KEY,
    name TEXT,
    address FROZEN<address>
);

INSERT INTO users (user_id, name, address)
VALUES (
    uuid(), 
    '张三', 
    {street: '朝阳路100号', city: '北京', zip_code: '100020'}
);

-- 基本语法
CREATE KEYSPACE [IF NOT EXISTS] keyspace_name
WITH replication = {
    'class': 'replication_strategy',
    'replication_factor': number
};

-- SimpleStrategy示例
CREATE KEYSPACE my_keyspace
WITH replication = {
    'class': 'SimpleStrategy',
    'replication_factor': 3
};

-- NetworkTopologyStrategy示例
CREATE KEYSPACE my_keyspace
WITH replication = {
    'class': 'NetworkTopologyStrategy',
    'datacenter1': 3,
    'datacenter2': 2
};

-- 完整选项
CREATE KEYSPACE my_keyspace
WITH replication = {
    'class': 'SimpleStrategy',
    'replication_factor': 3
}
AND durable_writes = true;

-- 切换键空间
USE my_keyspace;

-- 完全限定名
SELECT * FROM my_keyspace.users;

-- 修改复制因子
ALTER KEYSPACE my_keyspace
WITH replication = {
    'class': 'SimpleStrategy',
    'replication_factor': 5
};

-- 修改持久化写入
ALTER KEYSPACE my_keyspace
WITH durable_writes = false;

-- 删除键空间
DROP KEYSPACE [IF EXISTS] my_keyspace;

-- 基本语法
CREATE TABLE [IF NOT EXISTS] table_name (
    column_name data_type [PRIMARY KEY],
    column_name data_type,
    ...
);

-- 简单主键
CREATE TABLE users (
    user_id UUID PRIMARY KEY,
    name TEXT,
    email TEXT
);

-- 复合主键（分区键+聚簇列）
CREATE TABLE orders (
    user_id UUID,
    order_id UUID,
    order_date TIMESTAMP,
    amount DECIMAL,
    PRIMARY KEY (user_id, order_id)
);

-- 复合分区键
CREATE TABLE events (
    event_type TEXT,
    event_date DATE,
    event_id UUID,
    data TEXT,
    PRIMARY KEY ((event_type, event_date), event_id)
);

-- 带表选项
CREATE TABLE users (
    user_id UUID PRIMARY KEY,
    name TEXT,
    email TEXT
) WITH comment = '用户表'
  AND compaction = {'class': 'SizeTieredCompactionStrategy'}
  AND compression = {'sstable_compression': 'LZ4Compressor'}
  AND gc_grace_seconds = 864000
  AND default_time_to_live = 0;

-- 常用表选项
CREATE TABLE example (
    id INT PRIMARY KEY,
    data TEXT
) WITH 
    -- 注释
    comment = '示例表'
    -- 压缩策略
    AND compression = {
        'sstable_compression': 'LZ4Compressor',
        'chunk_length_kb': 64
    }
    -- 压实策略
    AND compaction = {
        'class': 'SizeTieredCompactionStrategy',
        'min_threshold': 4,
        'max_threshold': 32
    }
    -- GC宽限时间
    AND gc_grace_seconds = 864000
    -- 默认TTL
    AND default_time_to_live = 0
    -- 布隆过滤器
    AND bloom_filter_fp_chance = 0.01
    -- 缓存设置
    AND caching = {
        'keys': 'ALL',
        'rows_per_partition': 'NONE'
    };

-- 添加列
ALTER TABLE users ADD age INT;
ALTER TABLE users ADD (
    phone TEXT,
    address TEXT
);

-- 删除列
ALTER TABLE users DROP phone;

-- 重命名列
ALTER TABLE users RENAME user_id TO id;

-- 修改表选项
ALTER TABLE users WITH comment = '更新后的用户表';

-- 修改压缩策略
ALTER TABLE users WITH compression = {
    'sstable_compression': 'ZstdCompressor'
};

-- 删除表
DROP TABLE [IF EXISTS] users;

-- 清空表数据
TRUNCATE TABLE users;

-- 这是单行注释
SELECT * FROM users;  -- 行尾注释

/*
 * 这是多行注释
 * 可以跨越多行
 */
SELECT * FROM users;

-- 批量插入
BEGIN BATCH
    INSERT INTO users (user_id, name) VALUES (uuid(), '张三');
    INSERT INTO users (user_id, name) VALUES (uuid(), '李四');
    INSERT INTO users (user_id, name) VALUES (uuid(), '王五');
APPLY BATCH;

-- 批量更新
BEGIN BATCH
    UPDATE users SET name = '张三新' WHERE user_id = uuid();
    UPDATE users SET name = '李四新' WHERE user_id = uuid();
APPLY BATCH;

-- 混合操作
BEGIN BATCH
    INSERT INTO users (user_id, name) VALUES (uuid(), '新用户');
    UPDATE users SET name = '更新' WHERE user_id = uuid();
    DELETE FROM users WHERE user_id = uuid();
APPLY BATCH;

-- 使用时间戳
BEGIN BATCH USING TIMESTAMP 1234567890
    INSERT INTO users (user_id, name) VALUES (uuid(), '张三');
APPLY BATCH;

-- 批处理类型
BEGIN LOGGED BATCH  -- 默认，记录日志
    INSERT INTO users (user_id, name) VALUES (uuid(), '张三');
APPLY BATCH;

BEGIN UNLOGGED BATCH  -- 不记录日志，性能更好
    INSERT INTO users (user_id, name) VALUES (uuid(), '张三');
APPLY BATCH;

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

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

-- 转换函数
SELECT blobAsInt(0x00000001) FROM system.local;
SELECT intAsBlob(1) FROM system.local;

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

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

-- SUM
SELECT SUM(amount) FROM orders;

-- AVG
SELECT AVG(amount) FROM orders;

-- MIN/MAX
SELECT MIN(amount), MAX(amount) FROM orders;

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

-- 不等于
SELECT * FROM users WHERE user_id != 123;

-- 大于/小于
SELECT * FROM orders WHERE amount > 100;
SELECT * FROM orders WHERE amount < 100;
SELECT * FROM orders WHERE amount >= 100;
SELECT * FROM orders WHERE amount <= 100;

-- IN操作符
SELECT * FROM users WHERE user_id IN (1, 2, 3);

-- AND
SELECT * FROM users 
WHERE user_id = 1 AND name = '张三';

-- OR（需要使用ALLOW FILTERING）
SELECT * FROM users 
WHERE name = '张三' OR name = '李四' 
ALLOW FILTERING;

-- CONTAINS
SELECT * FROM users WHERE tags CONTAINS 'vip';

-- CONTAINS KEY
SELECT * FROM users WHERE attributes CONTAINS KEY 'email';

-- 集合相等
SELECT * FROM users WHERE tags = ['tag1', 'tag2'];

-- 使用JSON插入
INSERT INTO users JSON '{
    "user_id": "123e4567-e89b-12d3-a456-426614174000",
    "name": "张三",
    "email": "zhangsan@example.com"
}';

-- 使用JSON插入（带默认值）
INSERT INTO users JSON '{
    "user_id": "123e4567-e89b-12d3-a456-426614174000",
    "name": "张三"
}' DEFAULT UNSET;

-- 查询结果为JSON
SELECT JSON * FROM users;

-- 输出示例
{"user_id": "...", "name": "张三", "email": "..."}