Cassandra数据库与Cql实战笔记
启动数据库$ pwd
/home/database/apache-cassandra-3.11.7/bin
#启动数据库
$ ./cassandra
乐成标志：
https://img-blog.csdnimg.cn/img_convert/09bd8bf678bd70865a5034bc31f69367.png
https://img-blog.csdnimg.cn/img_convert/1ca70fb33741c84c0a6bd398585b34f6.png
查看数据库节点启动乐成状态
$ ./nodetool status
Datacenter: datacenter1
=======================
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
--Address Load Tokens Owns (effective)Host ID Rack
DN192.168.10.11 ? 256 100.0% 3240fa95-cc32-40f9-8c5d-ff53b9b86adcrack1
UN192.168.10.141405.56 KiB256 100.0% 468c9c1c-a1d4-4eda-a59a-31793df44d10rack1
$
https://img-blog.csdnimg.cn/img_convert/323671b954e70038f705f85afade1171.png
关闭数据库
杀掉数据库进程相干的pid号即可！！
https://img-blog.csdnimg.cn/img_convert/de32e7ce76f461e0f3833c20f58566d8.png
# kill -9 106206
# ps -ef | grep cassandra
root 119880 1035580 10:39 pts/3 00:00:00 grep --color=auto cassandra
#
使用cqlsh工具
https://img-blog.csdnimg.cn/img_convert/c621174d271d11654c188d4ea209775b.png
$ ./cqlsh 192.168.10.141 9042
Connected to Test Cluster at 192.168.10.141:9042.
Use HELP for help.
cqlsh>
常见命令
查看集群信息
cqlsh> describe cluster;Cluster: Test ClusterPartitioner: Murmur3Partitionercqlsh> DESCRIBE tables;Keyspace system_schema----------------------tables triggers views keyspacesdropped_columnsfunctionsaggregatesindexestypes columns Keyspace system_auth--------------------resource_role_permissons_indexrole_permissionsrole_membersrolesKeyspace system---------------available_ranges peers batchlog transferred_rangesbatches compaction_historysize_estimateshints prepared_statements sstable_activity built_views "IndexInfo" peer_events range_xfers views_builds_in_progresspaxos local Keyspace system_distributed---------------------------repair_historyview_build_statusparent_repair_historyKeyspace system_traces----------------------eventssessionsKeyspace flowmonitoringsystem-----------------------------auth_28_tcp_alarm_valueauth_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_historyauth_28_mode_config auth_28_tcp_flow_alarm_historyauth_28_second_history ywzd_user cqlsh> use system_traces；Invalid syntax at line 1, char 18use system_traces； ^cqlsh> use system_traces;cqlsh:system_traces> DESCRIBE tableseventssessionscqlsh: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;$ ./cqlsh 192.168.10.141 9042
Connected to Test Cluster at 192.168.10.141:9042.
Use HELP for help.
cqlsh>
showImproper show command.cqlsh> showkey ... ... ;SyntaxException: line 1:0 no viable alternative at input 'showkey' (...)cqlsh> cqlsh> show hostConnected to Test Cluster at 192.168.10.141:9042.cqlsh> show sessionImproper show command.cqlsh> show versioncqlsh> 数据定义命令
https://img-blog.csdnimg.cn/img_convert/3eca487fbfe9ef36dd12f3289ebcebd8.png
数据操作命令
https://img-blog.csdnimg.cn/img_convert/5a43c734b2e1002f294b8f08a676b132.png
操作健空间
创建Keyspace
语法
cqlsh> create keyspace school with replication={'class':'SimpleStrategy','replication_factor':3};
school system_authsystem_distributedflowmonitoringsystem
system_schemasystem system_traces
cqlsh> describe school;
CREATE KEYSPACE school WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '3'}AND durable_writes = true;
cqlsh>
连接健空间
cqlsh> use school;
cqlsh:school> alter keyspace school with replication={'class':'SimpleStrategy','replication_factor':1};
删除健空间
cqlsh:school> drop keyspace school;
cqlsh:school>
cqlsh:school>
cqlsh:school> describe keyspaces;
system_schemasystem system_traces
system_auth system_distributedflowmonitoringsystem
cqlsh:school>
创建表
创建语句类似于sql语句！
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>
);
cqlsh:test> 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>
... );
Warning: schema version mismatch detected; check the schema versions of your nodes in system.local and system.peers.
cqlsh:test> describe test
CREATE KEYSPACE test WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '1'}AND durable_writes = true;
CREATE TABLE test.student (
id int PRIMARY KEY,
address text,
age int,
education map<text, text>,
gender tinyint,
interest set<text>,
name text,
phone list<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';
cqlsh:test> describe tables;
student
cqlsh:test>
主键
create table testTab(
key_part_one int,
key_part_two int,
key_clust_one int,
key_clust_two int,
key_clust_three uuid,
name text,
primary key((key_part_one,key_part_two),key_clust_one,key_clust_two,key_clust_three)
);
cqlsh:test> use test01;
cqlsh:test01> create table testTab(
... key_part_one int,
... key_part_two int,
... key_clust_one int,
... key_clust_two int,
... key_clust_three uuid,
... name text,
... primary key((key_part_one,key_part_two),key_clust_one,key_clust_two,key_clust_three)
... );
Warning: schema version mismatch detected; check the schema versions of your nodes in system.local and system.peers.
cqlsh:test01>
cqlsh:test01>
cqlsh:test01> describe tables;
testtab
cqlsh:test01> select * from testab
... ;
InvalidRequest: Error from server: code=2200 message="unconfigured table testab"
cqlsh:test01> select * from testtab;
key_part_one | key_part_two | key_clust_one | key_clust_two | key_clust_three | name
--------------+--------------+---------------+---------------+-----------------+------
(0 rows)
cqlsh:test01>
表修改
添加列
cqlsh:test> alter table testtab add email text;
cqlsh:test> describe keyspaces;
system_schemasystem test test01
system_auth system_distributedsystem_tracesflowmonitoringsystem
cqlsh:test> describe tables;
testtabstudent
cqlsh:test> alter table testtab add email text;
Warning: schema version mismatch detected; check the schema versions of your nodes in system.local and system.peers.
cqlsh:test>
cqlsh:test> describe testtab;
CREATE TABLE test.testtab (
key_part_one int,
key_part_two int,
key_clust_one int,
key_clust_two int,
key_clust_three uuid,
email text,
name text,
PRIMARY KEY ((key_part_one, key_part_two), key_clust_one, key_clust_two, key_clust_three)
) WITH CLUSTERING ORDER BY (key_clust_one ASC, key_clust_two ASC, key_clust_three ASC)
AND 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';
cqlsh:test>
删除列
cqlsh:test> alter table testtab drop email;
cqlsh:test> alter table testtab drop email;
Warning: schema version mismatch detected; check the schema versions of your nodes in system.local and system.peers.
cqlsh:test> describe testtab;
CREATE TABLE test.testtab (
key_part_one int,
key_part_two int,
key_clust_one int,
key_clust_two int,
key_clust_three uuid,
name text,
PRIMARY KEY ((key_part_one, key_part_two), key_clust_one, key_clust_two, key_clust_three)
) WITH CLUSTERING ORDER BY (key_clust_one ASC, key_clust_two ASC, key_clust_three ASC)
AND 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';
cqlsh:test>
删除表
cqlsh:test> drop table testtab;
cqlsh:test> drop table testtab;
Warning: schema version mismatch detected; check the schema versions of your nodes in system.local and system.peers.
cqlsh:test>
cqlsh:test> describe tables;
student
cqlsh:test> select * from testtab;
InvalidRequest: Error from server: code=2200 message="unconfigured table testtab"
cqlsh:test>
清空表
cqlsh:test> truncate student;
添加数据
INSERT INTO student (id,address,age,gender,name,interest, phone,education) VALUES (1011,'中山路21号',16,1,'Tom',{'游泳', '跑步'},['010-88888888','13888888888'],{'小学' : '城市第一小学', '中学' : '城市第一中学'})
https://img-blog.csdnimg.cn/img_convert/813e3c5d091e54f03d749132476af921.png
INSERT INTO student (id,address,age,gender,name,interest, phone,education) VALUES (1012,'朝阳路19号',17,2,'Jerry',{'看书', '电影'},['020-66666666','13666666666'],{'小学' :'城市第五小学','中学':'城市第六中学'});
https://img-blog.csdnimg.cn/img_convert/c8cbb503966d44143bd394609f662889.png
数据过期时间
using ttl 30
数据30秒以后清空！
INSERT INTO student (id,address,age,gender,name,interest, phone,education) VALUES (1013,'朝阳路19号',17,2,'Linghu',{'看书', '电影'},['020-66666666','13666666666'],{'小学' :'城市第五小学','中学':'城市第六中学'})using ttl 30;
https://img-blog.csdnimg.cn/img_convert/3fcf27884312d7c314d49d110ab83235.png
查询数据
cqlsh:test> select * from student where id = 1011;
https://img-blog.csdnimg.cn/img_convert/91bfbcca4f1e1d7789b661bd851d83ac.png
更新数据
更新简单数据
更新表中的数据：
cqlsh:test> update student set gender = 0 where id = 1011;
https://img-blog.csdnimg.cn/img_convert/6245a57e22a3252c152ad1d53538e04a.png
更新set范例数据
在student表中，interest列是set范例
添加一个元素
update和+
cqlsh:test> update student set interest = interest+{'打太极'} where id = 1011;
https://img-blog.csdnimg.cn/img_convert/a7bb0a68972c66ce07ab80fdc7b6d35d.png
删除一个元素
update和-
cqlsh:test> update student set interest = interest-{'打太极'} where id = 1011;
https://img-blog.csdnimg.cn/img_convert/697e0299bb569541edcebd1769ff77b8.png
删除全部元素
update或者delete命令
UPDATE student SET interest = {} WHERE student_id = 1012;
或
DELETE interest FROM student WHERE student_id = 1012;
https://img-blog.csdnimg.cn/img_convert/03aac35d0a8340671649b190cf0ce246.png
更新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;
#使用UPDATA命令向list插入值
cqlsh:test> UPDATE student SET phone = ['020-66666666', '13666666666','1714873054'] WHERE id = 1012;
cqlsh:test> select * from student;
id | address | age | education | gender | interest | name| phone
------+------------+-----+--------------------------------------------------+--------+------------------+-------+-----------------------------------------------
1011 | 中山路21号 |16 | {'中学': '城市第一中学', '小学': '城市第一小学'} | 0 | {'游泳', '跑步'} | Tom | ['010-88888888', '13888888888']
1012 | 朝阳路19号 |17 | {'中学': '城市第六中学', '小学': '城市第五小学'} | null | {'电影', '看书'} | Jerry | ['020-66666666', '13666666666', '1714873054']
(2 rows)
#在list前面插入值
cqlsh:test> UPDATE student SET phone = [ '030-55555555' ] + phone WHERE id = 1012;
cqlsh:test> select * from student;
id | address | age | education | gender | interest | name| phone
------+------------+-----+--------------------------------------------------+--------+------------------+-------+---------------------------------------------------------------
1011 | 中山路21号 |16 | {'中学': '城市第一中学', '小学': '城市第一小学'} | 0 | {'游泳', '跑步'} | Tom | ['010-88888888', '13888888888']
1012 | 朝阳路19号 |17 | {'中学': '城市第六中学', '小学': '城市第五小学'} | null | {'电影', '看书'} | Jerry | ['030-55555555', '020-66666666', '13666666666', '1714873054']
(2 rows)
#在list后面插入值
cqlsh:test> UPDATE student SET phone = phone + [ '040-33333333' ]WHERE id = 1012;
cqlsh:test> select * from student;
id | address | age | education | gender | interest | name| phone
------+------------+-----+--------------------------------------------------+--------+------------------+-------+-------------------------------------------------------------------------------
1011 | 中山路21号 |16 | {'中学': '城市第一中学', '小学': '城市第一小学'} | 0 | {'游泳', '跑步'} | Tom | ['010-88888888', '13888888888']
1012 | 朝阳路19号 |17 | {'中学': '城市第六中学', '小学': '城市第五小学'} | null | {'电影', '看书'} | Jerry | ['030-55555555', '020-66666666', '13666666666', '1714873054', '040-33333333']
(2 rows)
cqlsh:test>
更新map范例数据
map输出顺序取决于map范例。
使用insert和update命令
cqlsh:test> update student set education = {'中学':'桐梓一中','小学':'南天门'} where id = 1012;
cqlsh:test> select * from student;
id | address | age | education | gender | interest | name| phone
------+------------+-----+--------------------------------------------------+--------+------------------+-------+-------------------------------------------------------------------------------
1011 | 中山路21号 |16 | {'中学': '城市第一中学', '小学': '城市第一小学'} | 0 | {'游泳', '跑步'} | Tom | ['010-88888888', '13888888888']
1012 | 朝阳路19号 |17 | {'中学': '桐梓一中', '小学': '南天门'} | null | {'电影', '看书'} | Jerry | ['030-55555555', '020-66666666', '13666666666', '1714873054', '040-33333333']
(2 rows)
cqlsh:test>
删除元素
可以用DELETE 和 UPDATE 删除Map范例中的数据
使用DELETE删除数据可以用DELETE 和 UPDATE 删除Map范例中的数据
使用DELETE删除数据
cqlsh:test> delete education['中学'] 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']
1012 | 朝阳路19号 |17 | {'小学': '南天门'} | null | {'电影', '看书'} | Jerry | ['030-55555555', '020-66666666', '13666666666', '1714873054', '040-33333333']
(2 rows)
cqlsh:test>
删除行
删除student中student_id=1012 的数据
cqlsh:test> 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>
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企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]