SELECT * FROM myTable
WHERE t > maxTimeuuid(‘2013-01-01 00:05+0000‘)
AND t < minTimeuuid(‘2013-02-02 10:00+0000‘)

CREATE TABLE sample_times (a int, b timestamp, c timeuuid, d bigint, PRIMARY KEY (a,b,c,d));
INSERT INTO sample_times (a,b,c,d) VALUES (1, toUnixTimestamp(now()), 50554d6e-29bb-11e5-b345-feff819cdc9f, toTimestamp(now()));
SELECT a, b, toDate(c), toDate(d) FROM sample_times

-- 开始工作：
bin/cqlsh localhost
-- 查看所有的键空间：
DESCRIBE keyspaces
-- 使用创建的键空间：
USE myks;
-- 查看已有表：
describe tables;
-- 查看表结构：
describe table users;
-- 删除已有表：
drop table users;

-- 首先，先建表
CREATE TABLE "user_status_updates" (
"username" text,
"id" timeuuid,
"body" text,
PRIMARY KEY ("username", "id")
);

CREATE TABLE "users" (
"username" text,
"email" text,
"encrypted_password" blob,
PRIMARY KEY ("username")
);

-- 对主表users进行数据插入
INSERT INTO "users"
("username", "email", "encrypted_password")
VALUES (
‘alice‘,
‘alice@gmail.com‘,
0x8914977ed729792e403da53024c6069a9158b8c4
);

INSERT INTO "users"
("username", "encrypted_password")
VALUES (
‘bob‘,
0x10920941a69549d33aaee6116ed1f47e19b8e713
);

-- 对从表user_status_updates进行数据插入
INSERT INTO "user_status_updates"
("username", "id", "body")
VALUES (
‘alice‘,
76e7a4d0-e796-11e3-90ce-5f98e903bf02,
‘Learning Cassandra!‘
);

INSERT INTO "user_status_updates"
("username", "id", "body")
VALUES (
‘bob‘,
97719c50-e797-11e3-90ce-5f98e903bf02,
‘Eating a tasty sandwich.‘
);

-- 前面我们加了2条从表记录，现在重点关注下id（即timeuuid）的查询
SELECT * FROM "user_status_updates";

SELECT "username", "id", "body", DATEOF("id")
FROM "user_status_updates";

SELECT "username", "id", "body", UNIXTIMESTAMPOF("id")
FROM "user_status_updates";

SELECT * FROM "user_status_updates"
WHERE "username" = ‘alice‘
AND "id" = 76e7a4d0-e796-11e3-90ce-5f98e903bf02;

-- 我们加了2条自制的uuid，那么从时间函数能自动产生uuid吗？会不会产生排序异常？
-- 再插入6条测试数据

INSERT INTO "user_status_updates" ("username", "id", "body")
VALUES (‘alice‘, NOW(), ‘Alice Update 1‘);
INSERT INTO "user_status_updates" ("username", "id", "body")
VALUES (‘bob‘, NOW(), ‘Bob Update 1‘);
INSERT INTO "user_status_updates" ("username", "id", "body")
VALUES (‘alice‘, NOW(), ‘Alice Update 2‘);
INSERT INTO "user_status_updates" ("username", "id", "body")
VALUES (‘bob‘, NOW(), ‘Bob Update 2‘);
INSERT INTO "user_status_updates" ("username", "id", "body")
VALUES (‘alice‘, NOW(), ‘Alice Update 3‘);
INSERT INTO "user_status_updates" ("username", "id", "body")
VALUES (‘bob‘, NOW(), ‘Bob Update 3‘);

-- 看看时间戳就知道了
SELECT "username", "id", "body",toTimestamp("id"), UNIXTIMESTAMPOF("id")
FROM "user_status_updates";

-- 结果：

-- 下面来看看复合主键
-- 建表

CREATE TABLE "user_status_updates_by_datetime" (
"username" text,
"status_date" date,
"status_time" time,
"body" text,
PRIMARY KEY ("username", "status_date", "status_time")
);

INSERT INTO "user_status_updates_by_datetime" ("username", 
"status_date", "status_time", "body")
VALUES (‘alice‘, ‘2019-11-18‘, ‘08:30:55.123‘, ‘Alice Update 1‘);
INSERT INTO "user_status_updates_by_datetime" ("username", 
"status_date", "status_time", "body")
VALUES (‘alice‘, ‘2019-11-18‘, ‘14:40:25.123456789‘, ‘Alice Update 2‘);
INSERT INTO "user_status_updates_by_datetime" ("username", 
"status_date", "status_time", "body")
VALUES (‘alice‘, ‘2019-11-19‘, ‘08:25:25‘, ‘Alice Update 3‘);
INSERT INTO "user_status_updates_by_datetime" ("username", 
"status_date", "status_time", "body")
VALUES (‘alice‘, ‘2019-11-21‘, ‘08:35:55.123456‘, ‘Alice Update 4‘);
INSERT INTO "user_status_updates_by_datetime" ("username", 
"status_date", "status_time", "body")
VALUES (‘alice‘, ‘2019-11-21‘, ‘14:30:15.123‘, ‘Alice Update 5‘);
INSERT INTO "user_status_updates_by_datetime" ("username", 
"status_date", "status_time", "body")
VALUES (‘alice‘, ‘2019-11-23‘, ‘14:50:45.123456‘, ‘Alice Update 6‘);

INSERT INTO "user_status_updates_by_datetime" ("username", 
"status_date", "status_time", "body")
VALUES (‘alice‘, ‘2019-14-23‘, ‘14:50:45.123456‘, ‘Alice Update 7‘);

INSERT INTO "user_status_updates_by_datetime" ("username", 
"status_date", "status_time", "body")
VALUES (‘alice‘, ‘2019-11-23‘, ‘14:65:45.123456‘, ‘Alice Update 8‘);

SELECT * FROM "user_status_updates_by_datetime";
-- 试试组合查询
SELECT * FROM "user_status_updates_by_datetime"
WHERE "username" = ‘alice‘ AND "status_date" < ‘2019-11-20‘;

-- 试试组合日期和时间查询，要查大于某天及大于几点的数据
SELECT * FROM "user_status_updates_by_datetime"
WHERE "username" = ‘alice‘ AND "status_date" > ‘2019-11-20‘ AND 
"status_time" > ‘12:00:00‘;
-- 报了个错：InvalidRequest: Error from server: code=2200 [Invalid query] message="Clustering column "status_time" cannot be restricted (preceding column "status_date" is restricted by a non-EQ relation)"
-- 原因Cassandra 支持的查询语句很严格，首先 partition key 必须精确查询，最后一个查询才能范围查询。
-- 改成下面这样就合规了
SELECT * FROM "user_status_updates_by_datetime"
WHERE "username" = ‘alice‘ AND "status_date" = ‘2019-11-21‘ AND 
"status_time" > ‘12:00:00‘;

-- 创建表
-- 我们将 email 和 encrypted_password 两个字段设置为 STATIC 了，这意味着同一个 username 只会有一个 email 和 encrypted_password

CREATE TABLE "users_with_status_updates" (
"username" text,
"id" timeuuid,
"email" text STATIC,
"encrypted_password" blob STATIC,
"body" text,
PRIMARY KEY ("username", "id")
);

INSERT INTO "users_with_status_updates"
("username", "id", "email", "encrypted_password", "body")
VALUES (
‘alice‘,
76e7a4d0-e796-11e3-90ce-5f98e903bf02,
‘alice@gmail.com‘,
0x8914977ed729792e403da53024c6069a9158b8c4,
‘Learning Cassandra!‘
);

SELECT * FROM "users_with_status_updates";
-- 验证插入
INSERT INTO "users_with_status_updates"
("username", "id", "body")
VALUES (‘alice‘, NOW(), ‘Another status update‘);

SELECT * FROM "users_with_status_updates";

SELECT "username", "email", "encrypted_password"
FROM "users_with_status_updates"
WHERE "username" = ‘alice‘;

SELECT DISTINCT "username", "email", "encrypted_password"
FROM "users_with_status_updates"
WHERE "username" = ‘alice‘;

-- 验证另一个主键bob

INSERT INTO "users_with_status_updates"
("username", "email", "encrypted_password")
VALUES (
‘bob‘,
‘bob@gmail.com‘,
0x10920941a69549d33aaee6116ed1f47e19b8e713
);

INSERT INTO "users_with_status_updates"
("username", "id", "body")
VALUES (‘bob‘, NOW(), ‘Bob status update‘);