1、基本操作

SHOW VERSION
SHOW HOST
SHOW SESSION
EXPAND ON/OFF
# 使用system键空间
USE system;

2、键空间

2.1、查看键空间

# 查看所有的键空间
DESCRIBE KEYSPACES
# 查看system键空间
DESCRIBE KEYSPACE system

2.2、创建键空间

CREATE KEYSPACE school WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 3};

{‘class’: ‘SimpleStrategy’, ‘replication_factor’: 3}: 副本放置策略

2.3、删除键空间

DROP KEYSPACE school ;

2.4、修改键空间

 ALTER KEYSPACE school WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 1};

2.5、使用键空间

USE system;

3、表（列簇）

2.1、查看表

# 查看所有表
DESCRIBE TABLES
# 查看system键空间下的peers表
DESCRIBE TABLE system.peers
# 先进入键空间，再查看表
USE system;
DESCRIBE TABLE peers

2.2、创建表

CREATE TABLE student(
    id int PRIMARY KEY,
    name text,
    age int,
    gender tinyint,
    address text,
    interest set<text>,
    phone list<text>,
    education map<text,text>
);

2.3、删除表

 DROP TABLE testtab;

2.4、修改表

# 添加字段
ALTER TABLE student ADD email text;
# 删除字段
ALTER TABLE student DROP email ;

2.5、清空表

# 清空所有的表数据
TRUNCATE student ;

3、索引

3.1、单一主键

CREATE TABLE student(
    id int PRIMARY KEY,
    name text,
    age int,
    gender tinyint,
    address text,
    interest set<text>,
    phone list<text>,
    education map<text,text>
);

3.2、组合主键

CREATE TABLE testTab(
    key_one int,
    key_two int,
    name text,
    PRIMARY KEY(key_one,key_two)
);

组合主键第一部分为分区key，第二部分为集群key，分区key进行hash计算决定将记录存放到那个分区上，集群key决定同一个分区内相同分区key数据的排序，默认为升序

3.2.1、分区键/集群键

CREATE TABLE testTab1(
    key_part_one int,
    key_part_two int,
    key_cluster_one int,
    key_cluster_two int,
    key_cluster_three int,
    name text,
    PRIMARY KEY((key_part_one,key_part_two),key_cluster_one,key_cluster_two,key_cluster_three)
);

3.3、创建索引

# 创建索引，指定名字
CREATE INDEX sname ON student (name) ;
# 创建索引不指定名字，系统默认指定名字
CREATE INDEX ON student (age);

自动创建一张表，将原始表格中索引字段作为新索引表的primary key,存储的值为原始数据的primary key

name	id
lisi	1

集合列创建索引

 # set 集合
 CREATE INDEX ON student (interest) ;
 # map集合
 CREATE INDEX ON student (keys(education));

3.4、删除索引

# 根据索引名字删除
DROP INDEX sname ;
# 查看是否删除了
DESCRIBE TABLE student;

4、数据

4.1、添加数据

INSERT INTO student (id , address , age , gender , name , interest , phone , education ) VALUES ( 1011,'中山路21号',16,1,'Tom',{'游泳','跑步'},['1234564555','1355555555'],{'小学':'城市第一小学','中学':'城市第一中学'});
 INSERT INTO student (id , address , age , gender , name , interest , phone , education ) VALUES ( 1012,'朝阳路12号',18,1,'Jack',{'游泳','跑步'},['1234564555','1355555555'],{'小学':'城市第一小学','中学':'城市第一中学'});
 # 添加过期时间，自动删除  USING TTL 30,  30s后自动删除
 INSERT INTO student (id , address , age , gender , name , interest , phone , education ) VALUES ( 1013,'xxxx',18,1,'xxx',{'游泳','跑步'},['1234564555','1355555555'],{'小学':'城市第一小学','中学':'城市第一中学'}) USING TTL 30;

4.2、删除数据

DELETE from student WHERE id=1011;

4.3、修改数据

4.3.1、普通类型

UPDATE student SET age=17 WHERE id=1011;
UPDATE student SET age=17 ,gender=2  WHERE id=1011;

4.3.2、集合类型

# set类型
UPDATE student SET interest =interest + {'xxx'} where id =1011;
UPDATE student SET interest =interest - {'xxx'} where id =1011;
UPDATE student SET interest ={'sdf','ddddd'} where id =1011;
UPDATE student SET interest ={} where id =1011;
DELETE interest FROM student WHERE id =1011;
#  list类型
UPDATE student SET phone=['111111111111'] WHERE id=1011;
UPDATE student SET phone=['111111111111'] + phone WHERE id=1011;
UPDATE student SET phone=phone+['111111111111']  WHERE id=1011;
UPDATE student SET phone=phone-['1111111'] WHERE id=1011;
UPDATE student SET phone[2]=['44454545'] WHERE id=1011;
# map集合
UPDATE student SET education['aa'] ='xxxxxxxxx'  WHERE id=1011;
UPDATE student SET education =education-{'bb','cc'}  WHERE id=1011;
DELETE education['bb'] FROM student WHERE id=1011;
UPDATE student SET education = education+{'a':'aa'} WHERE id=1011;
UPDATE student SET education = {'aa':'aaaaa','bb':'bbbbb'}  WHERE id=1011;

4.4、查询数据

select * from student;
SELECT * FROM student WHERE id=1011;

注意：

主键只能用 = 查询
第二主键支持 <=、 <、 >、>=、=
索引列只支持 =
非索引列非主键字段过滤可以使用 ALLOW FILTERING

只能学一点点

Cassandra的基本操作