CQL基础语法 #

-- 合法标识符
CREATE TABLE users (           -- 小写
    user_id UUID PRIMARY KEY,
    UserName TEXT,             -- 大小写混合
    email_address TEXT         -- 下划线分隔
);

-- 使用引号保留大小写（不推荐）
CREATE TABLE "Users" (
    "UserId" UUID PRIMARY KEY,
    "Name" TEXT
);

-- 不推荐的做法
-- CREATE TABLE user-table (id INT);  -- 包含连字符
-- CREATE TABLE 1users (id INT);      -- 以数字开头

ADD       ALLOW     ALTER     AND       APPLY     ASC       
BATCH     BEGIN     BY        CREATE    DELETE    DESC      
DROP      ENTRIES   FROM      FULL      GRANT     IF        
IN        INDEX     INFINITY  INSERT    INTO      IS         
KEY       KEYS      KEYSPACE  LIMIT     MATERIALIZED  MODIFY
NAN       NORECURSIVE  NOT    NULL      OF        ON        
OR        ORDER     PRIMARY   RENAME    REPLACE   RETRY     
REVOKE    SCHEMA    SELECT    SET       TABLE     TO        
TOKEN     TRUNCATE  TYPE      UNLOGGED  UPDATE    USE       
USER      USERS     USING     VALUES    WHERE     WITH

-- 创建键空间
CREATE KEYSPACE IF NOT EXISTS my_app
WITH replication = {
    'class': 'SimpleStrategy',
    'replication_factor': 3
};

-- 创建键空间（多数据中心）
CREATE KEYSPACE my_app_dc
WITH replication = {
    'class': 'NetworkTopologyStrategy',
    'dc1': 3,
    'dc2': 2
}
AND durable_writes = true;

-- 修改键空间
ALTER KEYSPACE my_app
WITH replication = {
    'class': 'SimpleStrategy',
    'replication_factor': 2
};

-- 删除键空间
DROP KEYSPACE IF EXISTS my_app;

-- 使用键空间
USE my_app;

-- 创建表
CREATE TABLE IF NOT EXISTS users (
    user_id UUID PRIMARY KEY,
    name TEXT,
    email TEXT,
    age INT,
    created_at TIMESTAMP
);

-- 创建表（复合主键）
CREATE TABLE orders (
    user_id UUID,
    order_id UUID,
    order_date DATE,
    amount DECIMAL,
    status TEXT,
    PRIMARY KEY (user_id, order_id)
);

-- 创建表（指定表属性）
CREATE TABLE products (
    product_id UUID PRIMARY KEY,
    name TEXT,
    price DECIMAL,
    stock INT
) WITH 
    comment = '产品信息表' AND
    gc_grace_seconds = 864000 AND
    compaction = {'class': 'SizeTieredCompactionStrategy'};

-- 修改表（添加列）
ALTER TABLE users ADD phone TEXT;

-- 修改表（添加多列）
ALTER TABLE users ADD (
    address TEXT,
    city TEXT
);

-- 修改表（修改列类型）
ALTER TABLE users ALTER age TYPE BIGINT;

-- 修改表（删除列）
ALTER TABLE users DROP phone;

-- 修改表属性
ALTER TABLE users WITH 
    comment = '用户信息表-更新' AND
    read_repair_chance = 0.2;

-- 删除表
DROP TABLE IF EXISTS users;

-- 清空表数据
TRUNCATE users;

-- 创建二级索引
CREATE INDEX IF NOT EXISTS users_email_idx 
ON users (email);

-- 创建自定义索引
CREATE CUSTOM INDEX ON users (name) 
USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = {
    'mode': 'CONTAINS',
    'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
    'case_sensitive': 'false'
};

-- 删除索引
DROP INDEX IF EXISTS users_email_idx;

-- 创建类型
CREATE TYPE address (
    street TEXT,
    city TEXT,
    state TEXT,
    zip_code TEXT
);

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

-- 修改类型
ALTER TYPE address ADD country TEXT;

-- 删除类型
DROP TYPE IF EXISTS address;

-- 基本插入
INSERT INTO users (user_id, name, email, age)
VALUES (
    uuid(), 
    '张三', 
    'zhangsan@example.com', 
    28
);

-- 指定TTL（生存时间，秒）
INSERT INTO users (user_id, name, email)
VALUES (uuid(), '李四', 'lisi@example.com')
USING TTL 3600;

-- 指定时间戳
INSERT INTO users (user_id, name, email)
VALUES (uuid(), '王五', 'wangwu@example.com')
USING TIMESTAMP 1234567890000;

-- JSON插入
INSERT INTO users JSON '{
    "user_id": "550e8400-e29b-41d4-a716-446655440000",
    "name": "赵六",
    "email": "zhaoliu@example.com",
    "age": 30
}';

-- 条件插入（轻量级事务）
INSERT INTO users (user_id, email, name)
VALUES (uuid(), 'unique@example.com', '测试用户')
IF NOT EXISTS;

-- 基本更新
UPDATE users 
SET name = '张三更新', age = 29
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- 更新并设置TTL
UPDATE users 
USING TTL 86400
SET name = '张三'
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- 条件更新
UPDATE users 
SET email = 'newemail@example.com'
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000
IF email = 'oldemail@example.com';

-- 更新集合类型
UPDATE users 
SET tags = tags + {'vip', 'active'}
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- 更新计数器
UPDATE user_counters 
SET login_count = login_count + 1
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- 删除行
DELETE FROM users 
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- 删除特定列
DELETE email, phone FROM users 
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- 条件删除
DELETE FROM users 
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000
IF name = '张三';

-- 删除集合元素
UPDATE users 
SET tags = tags - {'inactive'}
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- 查询所有数据
SELECT * FROM users;

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

-- 条件查询
SELECT * FROM users 
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- 限制结果数量
SELECT * FROM users LIMIT 10;

-- 排序（仅支持聚簇列）
SELECT * FROM orders 
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000
ORDER BY order_id DESC;

-- 分页查询
SELECT * FROM users LIMIT 100;
-- 使用上一页最后一条记录的主键作为下一次查询的起始点
SELECT * FROM users 
WHERE token(user_id) > token(上一页最后的user_id)
LIMIT 100;

-- 聚合函数
SELECT COUNT(*) FROM users;
SELECT AVG(age) FROM users;
SELECT MIN(age), MAX(age) FROM users;

-- 基本批量操作
BEGIN BATCH
    INSERT INTO users (user_id, name, email) 
    VALUES (uuid(), '用户1', 'user1@example.com');
    INSERT INTO users (user_id, name, email) 
    VALUES (uuid(), '用户2', 'user2@example.com');
    UPDATE user_stats SET total_users = total_users + 2 
    WHERE stat_id = 'global';
APPLY BATCH;

-- 条件批量操作（轻量级事务）
BEGIN BATCH
    UPDATE users SET email = 'new@example.com' 
    WHERE user_id = 550e8400-e29b-41d4-a716-446655440000
    IF email = 'old@example.com';
    INSERT INTO user_audit (user_id, action, timestamp) 
    VALUES (550e8400-e29b-41d4-a716-446655440000, 'email_change', toTimestamp(now()));
APPLY BATCH;

-- 设置批量选项
BEGIN BATCH 
    USING TTL 86400 
    AND TIMESTAMP 1234567890000
    INSERT INTO users (user_id, name) VALUES (uuid(), '临时用户');
    INSERT INTO user_logs (user_id, action) VALUES (uuid(), 'created');
APPLY BATCH;

-- 推荐：同一分区的批量操作
BEGIN BATCH
    INSERT INTO orders (user_id, order_id, amount) 
    VALUES (user_uuid, order_uuid1, 100.00);
    INSERT INTO orders (user_id, order_id, amount) 
    VALUES (user_uuid, order_uuid2, 200.00);
APPLY BATCH;

-- 不推荐：跨分区的批量操作（性能差）
BEGIN BATCH
    INSERT INTO orders (user_id, order_id, amount) 
    VALUES (user_uuid1, order_uuid1, 100.00);
    INSERT INTO orders (user_id, order_id, amount) 
    VALUES (user_uuid2, order_uuid2, 200.00);
APPLY BATCH;

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

// 这也是单行注释
SELECT name FROM users;

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

/* 单行块注释 */
SELECT name FROM users;

-- 字符串转UUID
SELECT * FROM users 
WHERE user_id = cast('550e8400-e29b-41d4-a716-446655440000' AS uuid);

-- 时间戳转换
SELECT toTimestamp(now()) FROM system.local;
SELECT toUnixTimestamp(created_at) FROM users;

-- 日期转换
SELECT toDate(created_at) FROM users;
SELECT toDateTime(created_at) FROM users;

-- 字符串转换
SELECT blobAsText(blob_column) FROM users;
SELECT textAsBlob('hello') FROM system.local;

-- 插入JSON
INSERT INTO users JSON '{
    "user_id": "550e8400-e29b-41d4-a716-446655440000",
    "name": "JSON用户",
    "email": "json@example.com"
}';

-- 查询返回JSON
SELECT toJson(name), toJson(email) FROM users;

-- 完整行JSON
SELECT toJson(users) FROM users;

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

-- 时间函数
SELECT toTimestamp(now()) FROM system.local;
SELECT currentTimestamp() FROM system.local;
SELECT currentDate() FROM system.local;
SELECT currentTime() FROM system.local;

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

-- 大小写转换
SELECT lower(name), upper(email) FROM users;

-- 字符串函数
SELECT length(name) FROM users;
SELECT substr(name, 1, 3) FROM users;

-- 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;

-- 创建用户
CREATE USER IF NOT EXISTS app_user 
WITH PASSWORD 'secure_password' NOSUPERUSER;

-- 创建超级用户
CREATE USER admin WITH PASSWORD 'admin_password' SUPERUSER;

-- 修改用户密码
ALTER USER app_user WITH PASSWORD 'new_password';

-- 删除用户
DROP USER IF EXISTS app_user;

-- 创建角色
CREATE ROLE IF NOT EXISTS read_only;
CREATE ROLE IF NOT EXISTS app_role;

-- 授予权限
GRANT SELECT ON ALL KEYSPACES TO read_only;
GRANT MODIFY ON KEYSPACE my_app TO app_role;
GRANT ALL PERMISSIONS ON TABLE my_app.users TO admin_role;

-- 授予角色
GRANT read_only TO app_user;

-- 撤销权限
REVOKE SELECT ON ALL KEYSPACES FROM read_only;

-- 撤销角色
REVOKE read_only FROM app_user;

-- 删除角色
DROP ROLE IF EXISTS read_only;