Cassandra包中提供了cqlsh工具，cqlsh指的是CQL(Cassandra查询语言) shell。是一种命令行shell,作为客户端,用于执行Cassandra查询语言。 

1.下载和安装   

https://geminidb-community.obs.cn-north-1.myhuaweicloud.com/Gemini_Cassandra_tools_cqlsh.zip 

2. 启动cqlsh   

1）解压Cassandra包后，cqlsh脚本在bin目录下。启动命令如下：

cqlsh {host} {port} -u {username} -p {password}

2）如果您需要经常连接到特定节点，您可以使用配置文件启动, 将参数统一保存在 {cqlshrc_file} 中，执行如下命令即可启动cqlsh

cqlsh --cqlshrc {cqlshrc_file}

     cqlshrc_file中配置如下，更多配置参考附件： cqlshrc.txt

[authentication]
username = {username}
password = {password}
[connection]
hostname = {host}
port = {port}

3）想要获取更多的cqlsh的命令和参数，执行 

cqlsh --help

3. cqlsh常用命令    

1）使用HELP命令可以查看所有支持的命令

rwuser@cqlsh> HELP
Documented shell commands:
===========================
CAPTURE  CLS          COPY  DESCRIBE  EXPAND  LOGIN   SERIAL  SOURCE   UNICODE
CLEAR    CONSISTENCY  DESC  EXIT      HELP    PAGING  SHOW    TRACING
CQL help topics:
================
AGGREGATES               CREATE_KEYSPACE           DROP_TRIGGER      TEXT    
ALTER_KEYSPACE           CREATE_MATERIALIZED_VIEW  DROP_TYPE         TIME    
ALTER_MATERIALIZED_VIEW  CREATE_ROLE               DROP_USER         TIMESTAMP
ALTER_TABLE              CREATE_TABLE              FUNCTIONS         TRUNCATE
ALTER_TYPE               CREATE_TRIGGER            GRANT             TYPES   
ALTER_USER               CREATE_TYPE               INSERT            UPDATE  
APPLY                    CREATE_USER               INSERT_JSON       USE     
ASCII                    DATE                      INT               UUID    
BATCH                    DELETE                    JSON           
BEGIN                    DROP_AGGREGATE            KEYWORDS       
BLOB                     DROP_COLUMNFAMILY         LIST_PERMISSIONS
BOOLEAN                  DROP_FUNCTION             LIST_ROLES     
COUNTER                  DROP_INDEX                LIST_USERS     
CREATE_AGGREGATE         DROP_KEYSPACE             PERMISSIONS    
CREATE_COLUMNFAMILY      DROP_MATERIALIZED_VIEW    REVOKE         
CREATE_FUNCTION          DROP_ROLE                 SELECT         
CREATE_INDEX             DROP_TABLE                SELECT_JSON

2）如果需要查看特定命令的帮助。可以使用HELP {COMMAND} 去查看特定命令用法

比如查看DESC命令的使用方法 HELP DESC，使用DESC命令可以输出集群的相关信息。比如 DESC CLUSTER 可以显示集群的名称及采用的Partitioner；DESC KEYSPACES 可以显示集群中的所有keyspace,包括系统库和自己创建的keyspace。

rwuser@cqlsh> DESC CLUSTER;
Cluster: Test Cluster
Partitioner: Murmur3Partitioner
 
rwuser@cqlsh> desc KEYSPACES;
system_traces  system_schema  system_auth  system  system_distributed

3）DDL 和 DML语句演示

创建keyspace语法如下：

CREATE  KEYSPACE [IF NOT EXISTS] keyspace_name
   WITH REPLICATION = {
      'class' : 'SimpleStrategy', 'replication_factor' : N }
     | 'class' : 'NetworkTopologyStrategy',
       'dc1_name' : N [, ...]、
   }
   [AND DURABLE_WRITES =  true|false] ;

创建名为 example的keyspace； replcation选项用于指定副本位置策略为简单策略和所需副本的数量为3。

CREATE KEYSPACE IF NOT EXISTS example WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '3'};

使用 DESC example 命令来验证创建。  使用use example 命令切换到创建的keyspace

rwuser@cqlsh> DESCRIBE example;
CREATE KEYSPACE example WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '3'}  AND durable_writes = true;

rwuser@cqlsh> use example;
rwuser@cqlsh:example>

创建表语法如下：

CREATE TABLE [IF NOT EXISTS] [keyspace_name.]table_name (
   column_definition [, ...]
   PRIMARY KEY (column_name [, column_name ...])
[WITH table_options
   | CLUSTERING ORDER BY (clustering_column_name order])
   | ID = 'table_hash_tag'
   | COMPACT STORAGE]

创建一个测试表test_table，定义三个列，分别是user_id，age，user_name。user_id是int类型，表示用户的ID号；age也是int类型，代表用户的年龄；user_name 是text类型，表示用户的名称。 主键为 user_id。

CREATE TABLE test_table(user_id int, age int, user_name text, PRIMARY KEY(user_id));

同样使用DESC test_table 命令验证创建的表

rwuser@cqlsh:example> desc test_table ;
 
CREATE TABLE example.test_table (
    user_id int PRIMARY KEY,
    age int,
    user_name text
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

插入数据语法如下：

INSERT INTO [keyspace_name.] table_name (column_list)
VALUES (column_values)
[IF NOT EXISTS]
[USING TTL seconds | TIMESTAMP epoch_in_microseconds]

查询数据语法如下：

SELECT * | select_expression | DISTINCT partition
FROM [keyspace_name.] table_name
[WHERE partition_value
   [AND clustering_filters
   [AND static_filters]]]
[ORDER BY PK_column_name ASC|DESC]
[LIMIT N]
[ALLOW FILTERING]

插入4条记录后进行查询。

INSERT INTO test_table (user_id, age, user_name) VALUES (1, 17, 'ZhangXiao');
INSERT INTO test_table (user_id, age, user_name) VALUES (2, 16, 'DongXin');
INSERT INTO test_table (user_id, age, user_name) VALUES (3, 17, 'HuFang');
INSERT INTO test_table (user_id, age, user_name) VALUES (4, 15, 'WangGuoHua');
 
rwuser@cqlsh:example> select * from test_table;
 
 user_id | age | user_name
---------+-----+------------
       1 |  17 |  ZhangXiao
       2 |  16 |    DongXin
       4 |  15 | WangGuoHua
       3 |  17 |     HuFang
 
(4 rows)

更新表结构语法如下：

ALTER TABLE [keyspace_name.] table_name
[ALTER column_name TYPE cql_type]
[ADD (column_definition_list)]
[DROP column_list | COMPACT STORAGE ]
[RENAME column_name TO column_name]
[WITH table_properties];

发现创建的表结构中缺少 性别gender字段，我们现在要修改一下表结构，新增一个text类型的字段。同样可以使用DESC test_table 命令验证新增的gender列.

rwuser@cqlsh:example> ALTER TABLE test_table ADD gender text;
rwuser@cqlsh:example> desc test_table ;
 
CREATE TABLE example.test_table (
    user_id int PRIMARY KEY,
    age int,
    gender text,
    user_name text
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

更新数据语法如下：

UPDATE [keyspace_name.] table_name
[USING TTL time_value | USING TIMESTAMP timestamp_value]
SET assignment [, assignment] . . .
WHERE row_specification
[IF EXISTS | IF condition [AND condition] . . .] ;

更新每一条记录，增加gender字段的值。

UPDATE example.test_table SET gender = 'female' WHERE user_id = 1;
UPDATE example.test_table SET gender = 'male' WHERE user_id = 2;
UPDATE example.test_table SET gender = 'male' WHERE user_id = 3;
UPDATE example.test_table SET gender = 'female' WHERE user_id = 4;

删除数据语法如下：

DELETE [column_name (term)][, ...]
FROM [keyspace_name.] table_name
[USING TIMESTAMP timestamp_value]
WHERE PK_column_conditions
[IF EXISTS | IF static_column_conditions]

需要删除user_id 为4的用户的age列

rwuser@cqlsh:example> DELETE age FROM example.test_table WHERE user_id=4;
rwuser@cqlsh:example> select * from example.test_table WHERE user_id = 4;
 
 user_id | age  | gender | user_name
---------+------+--------+------------
       4 | null | female | WangGuoHua
 
(1 rows)

删除user_id 为3的 整条记录

rwuser@cqlsh:example> DELETE FROM example.test_table WHERE user_id=3;
rwuser@cqlsh:example> select * from example.test_table WHERE user_id = 3;
 
 user_id | age | gender | user_name
---------+-----+--------+-----------
(0 rows)

清空表数据语法如下:

TRUNCATE [TABLE] [keyspace_name.table_name]

清空表中的所有记录

rwuser@cqlsh:example> truncate example.test_table ;
rwuser@cqlsh:example> select * from example.test_table;
 
 user_id | age | gender | user_name
---------+-----+--------+-----------
(0 rows)

删除表语法如下：

DROP TABLE [IF EXISTS] keyspace_name.table_name

删除创建的 test_table表

rwuser@cqlsh:example> DROP TABLE example.test_table ;
rwuser@cqlsh:example> desc example.test_table ;
 
'test_table' not found in keyspace 'example'

删除keyspace语法如下：

DROP KEYSPACE [IF EXISTS] keyspace_name

删除创建的keyspace example

rwuser@cqlsh:example> drop KEYSPACE example;
rwuser@cqlsh:example> desc example;
Keyspace 'example' not found.