建表
CREATE TABLE sys_user (
id text,
add_time timestamp,
username text,
email list<text>,
phone set<text>,
pwd text,
PRIMARY KEY (id, add_time, username)
) WITH CLUSTERING ORDER BY (add_time DESC,username ASC)
AND caching={'keys':'ALL', 'rows_per_partition':'NONE'}
AND compaction={'class':'xxxxx', 'max_threshold':'32', 'min_threshold':'4'}
AND compression={'chunk_length_in_kb':'64', 'class':'xxxx'}
AND dclocal_read_repair_chance=0.1
AND speculative_retry='xxxx';
PS:
- PRIMARY KEY (id, add_time, username) :id为分区键,add_time和username为集群键
- 主键=分区键+集群键
- 创建主键的时候,第一个字段是分区键
- WITH CLUSTERING ORDER BY (add_time DESC,username ASC)对集群键进行排序,后面查询排序的时候必须按照此顺序和排序方式
增
insert into sys_user(id,add_time,username) values ('1','2023-01-07T02:06:40.725+0000','ccc')
PS:在执行增加操作时,主键(分区键和集群键)必须添加上,否则会报错
删
delete from sys_user where id='1' and add_time ='2023-01-07T02:06:40.725+0000' and username='ccc'
PS:注意where后的顺序,分区键 ——> 集群键,键与键直接不可跨越
错误示例:
delete from sys_user where id='1' and username='ccc'
改
update sys_user
set pwd = 'ddd'
where id='1' and add_time ='2023-01-07T02:06:40.725+0000' and username='ccc'
PS:set后不可带主键(分区键+集群键)
查:
select * from sys_user where id='1' and add_time='2023-01-07T02:06:40.725+0000' and username='ace'
PS:在进行order by 的时候注意select * from sys_user where id=‘1’ order by add_time desc,username
order by后字段的排序方式必须按照表定义的那个顺序
使用like模糊查询
配置需要模糊查询的列
create custom index phone_idx ON staff (phone) USING 'org.apache.xxx.index.sasi.SASIIndex' WITH OPTIONS = {'analyzer_class': 'org.apache.xxx.index.sasi.analyzer.StandardAnalyzer', 'case_sensitive': 'false'};
xxx:cassandra
输入以上语法后,才可针对phone进行like查询