Cassandra数据库与Cql实战笔记

启动数据库

1. [zdaxctid@node3 bin]$ pwd
2. /home/database/apache-cassandra-3.11.7/bin
3. #启动数据库
4. [zdaxctid@node3 bin]$ ./cassandra

复制代码

乐成标志：

查看数据库节点启动乐成状态

1. [zdaxctid@node3 bin]$ ./nodetool status
2. Datacenter: datacenter1
3. =======================
4. Status=Up/Down
5. |/ State=Normal/Leaving/Joining/Moving
6. --  Address         Load       Tokens       Owns (effective)  Host ID                               Rack
7. DN  192.168.10.11   ?          256          100.0%            3240fa95-cc32-40f9-8c5d-ff53b9b86adc  rack1
8. UN  192.168.10.141  405.56 KiB  256          100.0%            468c9c1c-a1d4-4eda-a59a-31793df44d10  rack1
10. [zdaxctid@node3 bin]$

复制代码

关闭数据库

杀掉数据库进程相干的pid号即可！！

1. [root@node3 ~]# kill -9 106206
2. [root@node3 ~]# ps -ef | grep cassandra
3. root     119880 103558  0 10:39 pts/3    00:00:00 grep --color=auto cassandra
4. [root@node3 ~]#

复制代码

使用cqlsh工具

1. [zdaxctid@node3 bin]$ ./cqlsh 192.168.10.141 9042
2. Connected to Test Cluster at 192.168.10.141:9042.
3. [cqlsh 5.0.1 | Cassandra 3.11.7 | CQL spec 3.4.4 | Native protocol v3]
4. Use HELP for help.
5. cqlsh>

复制代码

常见命令

查看集群信息

1. cqlsh> describe cluster;Cluster: Test ClusterPartitioner: Murmur3Partitionercqlsh> DESCRIBE tables;Keyspace system_schema----------------------tables     triggers    views    keyspaces  dropped_columnsfunctions  aggregates  indexes  types      columns        Keyspace system_auth--------------------resource_role_permissons_index  role_permissions  role_members  rolesKeyspace system---------------available_ranges          peers               batchlog        transferred_rangesbatches                   compaction_history  size_estimates  hints             prepared_statements       sstable_activity    built_views   "IndexInfo"               peer_events         range_xfers   views_builds_in_progress  paxos               local         Keyspace system_distributed---------------------------repair_history  view_build_status  parent_repair_historyKeyspace system_traces----------------------events  sessionsKeyspace flowmonitoringsystem-----------------------------auth_28_tcp_alarm_value  auth_28_tcp_flow                auth_28_nodeauth_28_alarm_config     auth_28_business_number_index   user        auth_28_server           auth_ip_alarm_config          auth_28_link             auth_28_thrice_hand_shake     auth_28_second           auth_28_second_alarm_history  auth_28_mode_config      auth_28_tcp_flow_alarm_historyauth_28_second_history   ywzd_user                     cqlsh> use system_traces；Invalid syntax at line 1, char 18  use system_traces；                   ^cqlsh> use system_traces;cqlsh:system_traces> DESCRIBE tablesevents  sessionscqlsh:system_traces> describe sessionsCREATE TABLE system_traces.sessions (    session_id uuid PRIMARY KEY,    client inet,    command text,    coordinator inet,    coordinator_port int,    duration int,    parameters map<text, text>,    request text,    started_at timestamp) WITH bloom_filter_fp_chance = 0.01    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}    AND comment = 'tracing sessions'    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.0    AND default_time_to_live = 0    AND gc_grace_seconds = 0    AND max_index_interval = 2048    AND memtable_flush_period_in_ms = 3600000    AND min_index_interval = 128    AND read_repair_chance = 0.0    AND speculative_retry = '99PERCENTILE';cqlsh:system_traces> showImproper show command.cqlsh:system_traces> SHOW Improper SHOW command.cqlsh:system_traces> quit;[zdaxctid@node3 bin]$ ./cqlsh 192.168.10.141 9042
2. Connected to Test Cluster at 192.168.10.141:9042.
3. [cqlsh 5.0.1 | Cassandra 3.11.7 | CQL spec 3.4.4 | Native protocol v3]
4. Use HELP for help.
5. cqlsh>
7. showImproper show command.cqlsh> showkey   ...    ... ;SyntaxException: line 1:0 no viable alternative at input 'showkey' ([showkey]...)cqlsh> cqlsh> show hostConnected to Test Cluster at 192.168.10.141:9042.cqlsh> show sessionImproper show command.cqlsh> show version[cqlsh 5.0.1 | Cassandra 3.11.7 | CQL spec 3.4.4 | Native protocol v3]cqlsh>

复制代码

数据定义命令

数据操作命令

操作健空间

创建Keyspace

   语法

1. cqlsh> create keyspace school with replication={'class':'SimpleStrategy','replication_factor':3};
3. school         system_auth  system_distributed  flowmonitoringsystem
4. system_schema  system       system_traces     
6. cqlsh> describe school;
8. CREATE KEYSPACE school WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '3'}  AND durable_writes = true;
10. cqlsh>

复制代码

连接健空间

1. cqlsh> use school;
2. cqlsh:school> alter keyspace school with replication={'class':'SimpleStrategy','replication_factor':1};

复制代码

删除健空间

1. cqlsh:school> drop keyspace school;
2. cqlsh:school>
3. cqlsh:school>
4. cqlsh:school> describe keyspaces;
6. system_schema  system              system_traces      
7. system_auth    system_distributed  flowmonitoringsystem
9. cqlsh:school>

复制代码

创建表

创建语句类似于sql语句！

1. create table student(
2.         id int primary key,
3.     name text,
4.     age int,
5.     gender tinyint,
6.     address text,
7.     interest set<text>,
8.     phone list<text>,
9.     education map<text,text>
10. );

复制代码

1. cqlsh:test> create table student(
2.         ... id int primary key,
3.         ...     name text,
4.         ...     age int,
5.         ...     gender tinyint,
6.         ...     address text,
7.         ...     interest set<text>,
8.         ...     phone list<text>,
9.         ...     education map<text,text>
10.         ... );
11. Warning: schema version mismatch detected; check the schema versions of your nodes in system.local and system.peers.
12. cqlsh:test> describe test
14. CREATE KEYSPACE test WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '1'}  AND durable_writes = true;
16. CREATE TABLE test.student (
17.     id int PRIMARY KEY,
18.     address text,
19.     age int,
20.     education map<text, text>,
21.     gender tinyint,
22.     interest set<text>,
23.     name text,
24.     phone list<text>
25. ) WITH bloom_filter_fp_chance = 0.01
26.     AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
27.     AND comment = ''
28.     AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
29.     AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
30.     AND crc_check_chance = 1.0
31.     AND dclocal_read_repair_chance = 0.1
32.     AND default_time_to_live = 0
33.     AND gc_grace_seconds = 864000
34.     AND max_index_interval = 2048
35.     AND memtable_flush_period_in_ms = 0
36.     AND min_index_interval = 128
37.     AND read_repair_chance = 0.0
38.     AND speculative_retry = '99PERCENTILE';
40. cqlsh:test> describe tables;
42. student
44. cqlsh:test>

复制代码

主键

1. create table testTab(
2.         key_part_one int,
3.         key_part_two int,
4.         key_clust_one int,
5.         key_clust_two int,
6.         key_clust_three uuid,
7.         name text,
8.         primary key((key_part_one,key_part_two),key_clust_one,key_clust_two,key_clust_three)
9. );

复制代码

1. cqlsh:test> use test01;
2. cqlsh:test01> create table testTab(
3.           ... key_part_one int,
4.           ... key_part_two int,
5.           ... key_clust_one int,
6.           ... key_clust_two int,
7.           ... key_clust_three uuid,
8.           ... name text,
9.           ... primary key((key_part_one,key_part_two),key_clust_one,key_clust_two,key_clust_three)
10.           ... );
12. Warning: schema version mismatch detected; check the schema versions of your nodes in system.local and system.peers.
13. cqlsh:test01>
14. cqlsh:test01>
15. cqlsh:test01> describe tables;
17. testtab
19. cqlsh:test01> select * from testab
20.           ... ;
21. InvalidRequest: Error from server: code=2200 [Invalid query] message="unconfigured table testab"
22. cqlsh:test01> select * from testtab;
24. key_part_one | key_part_two | key_clust_one | key_clust_two | key_clust_three | name
25. --------------+--------------+---------------+---------------+-----------------+------
27. (0 rows)
28. cqlsh:test01>

复制代码

表修改

添加列

cqlsh:test> alter table testtab add email text;

1. cqlsh:test> describe keyspaces;
3. system_schema  system              test           test01              
4. system_auth    system_distributed  system_traces  flowmonitoringsystem
6. cqlsh:test> describe tables;
8. testtab  student
10. cqlsh:test> alter table testtab add email text;
12. Warning: schema version mismatch detected; check the schema versions of your nodes in system.local and system.peers.
13. cqlsh:test>
14. cqlsh:test> describe testtab;
16. CREATE TABLE test.testtab (
17.     key_part_one int,
18.     key_part_two int,
19.     key_clust_one int,
20.     key_clust_two int,
21.     key_clust_three uuid,
22.     email text,
23.     name text,
24.     PRIMARY KEY ((key_part_one, key_part_two), key_clust_one, key_clust_two, key_clust_three)
25. ) WITH CLUSTERING ORDER BY (key_clust_one ASC, key_clust_two ASC, key_clust_three ASC)
26.     AND bloom_filter_fp_chance = 0.01
27.     AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
28.     AND comment = ''
29.     AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
30.     AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
31.     AND crc_check_chance = 1.0
32.     AND dclocal_read_repair_chance = 0.1
33.     AND default_time_to_live = 0
34.     AND gc_grace_seconds = 864000
35.     AND max_index_interval = 2048
36.     AND memtable_flush_period_in_ms = 0
37.     AND min_index_interval = 128
38.     AND read_repair_chance = 0.0
39.     AND speculative_retry = '99PERCENTILE';
41. cqlsh:test>

复制代码

删除列

cqlsh:test> alter table testtab drop email;

1. cqlsh:test> alter table testtab drop email;
2. Warning: schema version mismatch detected; check the schema versions of your nodes in system.local and system.peers.
3. cqlsh:test> describe testtab;
5. CREATE TABLE test.testtab (
6.     key_part_one int,
7.     key_part_two int,
8.     key_clust_one int,
9.     key_clust_two int,
10.     key_clust_three uuid,
11.     name text,
12.     PRIMARY KEY ((key_part_one, key_part_two), key_clust_one, key_clust_two, key_clust_three)
13. ) WITH CLUSTERING ORDER BY (key_clust_one ASC, key_clust_two ASC, key_clust_three ASC)
14.     AND bloom_filter_fp_chance = 0.01
15.     AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
16.     AND comment = ''
17.     AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
18.     AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
19.     AND crc_check_chance = 1.0
20.     AND dclocal_read_repair_chance = 0.1
21.     AND default_time_to_live = 0
22.     AND gc_grace_seconds = 864000
23.     AND max_index_interval = 2048
24.     AND memtable_flush_period_in_ms = 0
25.     AND min_index_interval = 128
26.     AND read_repair_chance = 0.0
27.     AND speculative_retry = '99PERCENTILE';
29. cqlsh:test>

复制代码

删除表

cqlsh:test> drop table testtab;

1. cqlsh:test> drop table testtab;
3. Warning: schema version mismatch detected; check the schema versions of your nodes in system.local and system.peers.
4. cqlsh:test>
5. cqlsh:test> describe tables;
7. student
9. cqlsh:test> select * from testtab;
10. InvalidRequest: Error from server: code=2200 [Invalid query] message="unconfigured table testtab"
11. cqlsh:test>

复制代码

清空表

1. cqlsh:test> truncate student;

复制代码

添加数据

1. INSERT INTO student (id,address,age,gender,name,interest, phone,education) VALUES (1011,'中山路21号',16,1,'Tom',{'游泳', '跑步'},['010-88888888','13888888888'],{'小学' : '城市第一小学', '中学' : '城市第一中学'})

复制代码

1. INSERT INTO student (id,address,age,gender,name,interest, phone,education) VALUES (1012,'朝阳路19号',17,2,'Jerry',{'看书', '电影'},['020-66666666','13666666666'],{'小学' :'城市第五小学','中学':'城市第六中学'});

复制代码

数据过期时间

using ttl 30
数据30秒以后清空！

1. INSERT INTO student (id,address,age,gender,name,interest, phone,education) VALUES (1013,'朝阳路19号',17,2,'Linghu',{'看书', '电影'},['020-66666666','13666666666'],{'小学' :'城市第五小学','中学':'城市第六中学'})using ttl 30;

复制代码

查询数据

1. cqlsh:test> select * from student where id = 1011;

复制代码

更新数据

更新简单数据

更新表中的数据：

1. cqlsh:test> update student set gender = 0 where id = 1011;

复制代码

更新set范例数据

   在student表中，interest列是set范例
  添加一个元素

   update和+

1. cqlsh:test> update student set interest = interest+{'打太极'} where id = 1011;

复制代码

删除一个元素

   update和-

1. cqlsh:test> update student set interest = interest-{'打太极'} where id = 1011;

复制代码

删除全部元素

   update或者delete命令

1. UPDATE student SET interest = {} WHERE student_id = 1012;
2. 或
3. DELETE interest FROM student WHERE student_id = 1012;

复制代码

更新list范例数据

   在student中phone就是list范例
  使用UPDATA命令向list插入值

cqlsh:test> UPDATE student SET phone = [‘020-66666666’, ‘13666666666’,‘1714873054’] WHERE id = 1012;
在list前面插入值

cqlsh:test> UPDATE student SET phone = [ ‘030-55555555’ ] + phone WHERE id = 1012;
在list反面插入值

cqlsh:test> UPDATE student SET phone = phone + [ ‘040-33333333’ ] WHERE id = 1012;

1. #使用UPDATA命令向list插入值
2. cqlsh:test> UPDATE student SET phone = ['020-66666666', '13666666666','1714873054'] WHERE id = 1012;
3. cqlsh:test> select * from student;
5. id   | address    | age | education                                        | gender | interest         | name  | phone
6. ------+------------+-----+--------------------------------------------------+--------+------------------+-------+-----------------------------------------------
7. 1011 | 中山路21号 |  16 | {'中学': '城市第一中学', '小学': '城市第一小学'} |      0 | {'游泳', '跑步'} |   Tom |               ['010-88888888', '13888888888']
8. 1012 | 朝阳路19号 |  17 | {'中学': '城市第六中学', '小学': '城市第五小学'} |   null | {'电影', '看书'} | Jerry | ['020-66666666', '13666666666', '1714873054']
10. (2 rows)
11. #在list前面插入值
12. cqlsh:test> UPDATE student SET phone = [ '030-55555555' ] + phone WHERE id = 1012;
13. cqlsh:test> select * from student;
15. id   | address    | age | education                                        | gender | interest         | name  | phone
16. ------+------------+-----+--------------------------------------------------+--------+------------------+-------+---------------------------------------------------------------
17. 1011 | 中山路21号 |  16 | {'中学': '城市第一中学', '小学': '城市第一小学'} |      0 | {'游泳', '跑步'} |   Tom |                               ['010-88888888', '13888888888']
18. 1012 | 朝阳路19号 |  17 | {'中学': '城市第六中学', '小学': '城市第五小学'} |   null | {'电影', '看书'} | Jerry | ['030-55555555', '020-66666666', '13666666666', '1714873054']
20. (2 rows)
21. #在list后面插入值
22. cqlsh:test> UPDATE student SET phone = phone + [ '040-33333333' ]  WHERE id = 1012;
23. cqlsh:test> select * from student;
25. id   | address    | age | education                                        | gender | interest         | name  | phone
26. ------+------------+-----+--------------------------------------------------+--------+------------------+-------+-------------------------------------------------------------------------------
27. 1011 | 中山路21号 |  16 | {'中学': '城市第一中学', '小学': '城市第一小学'} |      0 | {'游泳', '跑步'} |   Tom |                                               ['010-88888888', '13888888888']
28. 1012 | 朝阳路19号 |  17 | {'中学': '城市第六中学', '小学': '城市第五小学'} |   null | {'电影', '看书'} | Jerry | ['030-55555555', '020-66666666', '13666666666', '1714873054', '040-33333333']
30. (2 rows)
31. cqlsh:test>

复制代码

更新map范例数据

map输出顺序取决于map范例。
使用insert和update命令

1. cqlsh:test> update student set education = {'中学':'桐梓一中','小学':'南天门'} where id = 1012;
2. cqlsh:test> select * from student;
3. id   | address    | age | education                                        | gender | interest         | name  | phone
4. ------+------------+-----+--------------------------------------------------+--------+------------------+-------+-------------------------------------------------------------------------------
5. 1011 | 中山路21号 |  16 | {'中学': '城市第一中学', '小学': '城市第一小学'} |      0 | {'游泳', '跑步'} |   Tom |                                               ['010-88888888', '13888888888']
6. 1012 | 朝阳路19号 |  17 |           {'中学': '桐梓一中', '小学': '南天门'} |   null | {'电影', '看书'} | Jerry | ['030-55555555', '020-66666666', '13666666666', '1714873054', '040-33333333']
8. (2 rows)
9. cqlsh:test>

复制代码

删除元素

可以用DELETE 和 UPDATE 删除Map范例中的数据

使用DELETE删除数据可以用DELETE 和 UPDATE 删除Map范例中的数据
使用DELETE删除数据

1. cqlsh:test> delete education['中学'] from student where id = 1012;
2. cqlsh:test> select * from student;
3. id   | address    | age | education                                        | gender | interest         | name  | phone
4. ------+------------+-----+--------------------------------------------------+--------+------------------+-------+-------------------------------------------------------------------------------
5. 1011 | 中山路21号 |  16 | {'中学': '城市第一中学', '小学': '城市第一小学'} |      0 | {'游泳', '跑步'} |   Tom |                                               ['010-88888888', '13888888888']
6. 1012 | 朝阳路19号 |  17 |                               {'小学': '南天门'} |   null | {'电影', '看书'} | Jerry | ['030-55555555', '020-66666666', '13666666666', '1714873054', '040-33333333']
8. (2 rows)
9. cqlsh:test>

复制代码

删除行

删除student中student_id=1012 的数据

1. cqlsh:test> delete from student where id = 1012;
2. cqlsh:test> select * from student;
4. id   | address    | age | education                                        | gender | interest         | name | phone
5. ------+------------+-----+--------------------------------------------------+--------+------------------+------+---------------------------------
6. 1011 | 中山路21号 |  16 | {'中学': '城市第一中学', '小学': '城市第一小学'} |      0 | {'游泳', '跑步'} |  Tom | ['010-88888888', '13888888888']
8. (1 rows)
9. cqlsh:test>

复制代码

create table authtype(
​ id int primary key,
​ auth_type text,
​ foreing key (auth_type) references hostinfo (id)
);
delete from student where id = 1012;
cqlsh:test> select * from student;
id | address | age | education | gender | interest | name | phone
------±-----------±----±-------------------------------------------------±-------±-----------------±-----±--------------------------------
1011 | 中山路21号 | 16 | {‘中学’: ‘城市第一中学’, ‘小学’: ‘城市第一小学’} | 0 | {‘游泳’, ‘跑步’} | Tom | [‘010-88888888’, ‘13888888888’]
(1 rows)
cqlsh:test>

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。