1
2
3
4
5
6
7
8
# Using cqlsh
cqlsh [hostname] [port]
cqlsh localhost 9042  # Default connection

# Show cluster information
SHOW VERSION;
SHOW HOST;

1
2
3
-- Connect with username and password
cqlsh -u [username] -p [password]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Simple replication
CREATE KEYSPACE my_keyspace 
WITH replication = {
    'class': 'SimpleStrategy', 
    'replication_factor': 3
};

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

-- Use keyspace
USE my_keyspace;

1
2
3
4
5
6
ALTER KEYSPACE my_keyspace 
WITH replication = {
    'class': 'SimpleStrategy', 
    'replication_factor': 4
};

1
2
DROP KEYSPACE my_keyspace;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE users (
    user_id UUID PRIMARY KEY,
    username TEXT,
    email TEXT,
    age INT,
    registration_date TIMESTAMP
);

-- Composite Primary Key
CREATE TABLE user_posts (
    user_id UUID,
    post_id TIMEUUID,
    content TEXT,
    likes INT,
    PRIMARY KEY ((user_id), post_id)
) WITH CLUSTERING ORDER BY (post_id DESC);

1
2
3
4
5
6
-- Add column
ALTER TABLE users ADD phone_number TEXT;

-- Drop column
ALTER TABLE users DROP phone_number;

1
2
DROP TABLE users;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- List
CREATE TABLE example (
    id UUID PRIMARY KEY,
    tags list<text>
);

-- Set (unique elements)
tags set<text>

-- Map (key-value pairs)
preferences map<text, text>

-- Inserting collections
INSERT INTO example (id, tags) VALUES 
    (uuid(), ['tag1', 'tag2', 'tag3']);

1
2
3
4
INSERT INTO users (user_id, username, email)
VALUES (uuid(), 'johndoe', 'john@example.com')
USING TTL 86400;  # Optional time-to-live

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Basic select
SELECT * FROM users;

-- Filtering
SELECT * FROM users WHERE username = 'johndoe';

-- Pagination
SELECT * FROM users LIMIT 10;

-- Complex queries
SELECT username, email 
FROM users 
WHERE user_id IN (uuid1, uuid2)
  AND age > 25
ALLOW FILTERING;

1
2
3
4
5
6
7
8
9
10
UPDATE users 
SET email = 'new_email@example.com', 
    age = 30 
WHERE user_id = uuid();

-- Increment counter
UPDATE user_stats 
SET login_count = login_count + 1 
WHERE user_id = uuid();

1
2
3
4
5
6
7
DELETE FROM users WHERE user_id = uuid();

-- Delete specific columns
DELETE email, age 
FROM users 
WHERE user_id = uuid();

1
2
3
4
5
6
7
8
9
10
-- Composite Partition Key
CREATE TABLE sensor_data (
    datacenter text,
    rack text,
    sensor_id uuid,
    timestamp timeuuid,
    temperature float,
    PRIMARY KEY ((datacenter, rack), sensor_id, timestamp)
) WITH CLUSTERING ORDER BY (sensor_id ASC, timestamp DESC);

1
2
3
4
5
6
-- Create index
CREATE INDEX ON users (email);

-- Query with indexed column
SELECT * FROM users WHERE email = 'john@example.com';

1
2
3
4
5
CREATE MATERIALIZED VIEW users_by_email AS
    SELECT * FROM users
    WHERE email IS NOT NULL
    PRIMARY KEY (email, user_id);

1
2
3
4
5
6
7
8
-- Insert with TTL (seconds)
INSERT INTO users (user_id, username)
VALUES (uuid(), 'tempuser')
USING TTL 3600;  # Expires in 1 hour

-- Get TTL of a column
SELECT TTL(username) FROM users;

1
2
3
4
5
6
7
-- Consistency levels: ONE, QUORUM, ALL, LOCAL_QUORUM
CONSISTENCY QUORUM;

-- Per-query consistency
SELECT * FROM users 
USING CONSISTENCY QUORUM;

1
2
3
4
5
6
7
8
9
10
11
12
-- User-Defined Type
CREATE TYPE address (
    street text,
    city text,
    zip_code text
);

-- User-Defined Function
CREATE FUNCTION fahrenheit_to_celsius(f double)
RETURNS double LANGUAGE java AS
    'return (f - 32.0) * 5/9;';

1
2
3
4
5
BEGIN BATCH
    INSERT INTO users (user_id, username) VALUES (uuid1, 'user1');
    INSERT INTO user_stats (user_id, login_count) VALUES (uuid1, 1);
APPLY BATCH;

1
2
3
4
5
6
7
8
9
-- Denormalized user_timeline for faster reads
CREATE TABLE user_timeline (
    user_id uuid,
    post_id timeuuid,
    content text,
    author_name text,
    PRIMARY KEY ((user_id), post_id)
) WITH CLUSTERING ORDER BY (post_id DESC);

1
2
3
4
5
6
7
8
9
-- IoT Sensor Data Model
CREATE TABLE sensor_readings (
    device_id uuid,
    reading_time timeuuid,
    temperature float,
    humidity float,
    PRIMARY KEY ((device_id), reading_time)
) WITH CLUSTERING ORDER BY (reading_time DESC);