启动数据库
[zdaxctid@node3 bin]$ pwd /home/database/apache-cassandra-3.11.7/bin #启动数据库 [zdaxctid@node3 bin]$ ./cassandra
成功标志：
查看数据库节点启动成功状态
[zdaxctid@node3 bin]$ ./nodetool status Datacenter: datacenter1 ======================= Status=Up/Down |/ State=Normal/Leaving/Joining/Moving -- Address Load Tokens Owns (effective) Host ID Rack DN 192.168.10.11 ? 256 100.0% 3240fa95-cc32-40f9-8c5d-ff53b9b86adc rack1 UN 192.168.10.141 405.56 KiB 256 100.0% 468c9c1c-a1d4-4eda-a59a-31793df44d10 rack1 [zdaxctid@node3 bin]$
关闭数据库
杀掉数据库进程相关的pid号即可！！
[root@node3 ~]# kill -9 106206 [root@node3 ~]# ps -ef | grep cassandra root 119880 103558 0 10:39 pts/3 00:00:00 grep --color=auto cassandra [root@node3 ~]#
使用cqlsh工具
[zdaxctid@node3 bin]$ ./cqlsh 192.168.10.141 9042 Connected to Test Cluster at 192.168.10.141:9042. [cqlsh 5.0.1 | Cassandra 3.11.7 | CQL spec 3.4.4 | Native protocol v3] Use HELP for help. cqlsh>
常见命令
查看集群信息
cqlsh> describe cluster; Cluster: Test Cluster Partitioner: Murmur3Partitioner cqlsh> DESCRIBE tables; Keyspace system_schema ---------------------- tables triggers views keyspaces dropped_columns functions aggregates indexes types columns Keyspace system_auth -------------------- resource_role_permissons_index role_permissions role_members roles Keyspace system --------------- available_ranges peers batchlog transferred_ranges batches 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_history Keyspace system_traces ---------------------- events sessions Keyspace flowmonitoringsystem ----------------------------- auth_28_tcp_alarm_value auth_28_tcp_flow auth_28_node auth_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_history auth_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 tables events sessions cqlsh:system_traces> describe sessions CREATE 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> show Improper show command. cqlsh:system_traces> SHOW Improper SHOW command. cqlsh:system_traces> quit; [zdaxctid@node3 bin]$ ./cqlsh 192.168.10.141 9042 Connected to Test Cluster at 192.168.10.141:9042. [cqlsh 5.0.1 | Cassandra 3.11.7 | CQL spec 3.4.4 | Native protocol v3] Use HELP for help. cqlsh> show Improper show command. cqlsh> showkey ... ... ; SyntaxException: line 1:0 no viable alternative at input 'showkey' ([showkey]...) cqlsh> cqlsh> show host Connected to Test Cluster at 192.168.10.141:9042. cqlsh> show session Improper show command. cqlsh> show version [cqlsh 5.0.1 | Cassandra 3.11.7 | CQL spec 3.4.4 | Native protocol v3] cqlsh>
数据定义命令
数据操作命令
操作健空间
创建Keyspace
语法
cqlsh> create keyspace school with replication={'class':'SimpleStrategy','replication_factor':3}; school system_auth system_distributed flowmonitoringsystem system_schema system 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_schema system system_traces system_auth system_distributed flowmonitoringsystem 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 [Invalid query] 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_schema system test test01 system_auth system_distributed system_traces flowmonitoringsystem cqlsh:test> describe tables; testtab student 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 [Invalid query] 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'],{'小学' : '城市第一小学', '中学' : '城市第一中学'})
INSERT INTO student (id,address,age,gender,name,interest, phone,education) VALUES (1012,'朝阳路19号',17,2,'Jerry',{'看书', '电影'},['020-66666666','13666666666'],{'小学' :'城市第五小学','中学':'城市第六中学'});
数据过期时间
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;
查询数据
cqlsh:test> select * from student where id = 1011;
更新数据
更新简单数据
更新表中的数据：
cqlsh:test> update student set gender = 0 where id = 1011;
更新set类型数据
在student表中，interest列是set类型
添加一个元素
update和+
cqlsh:test> update student set interest = interest+{'打太极'} where id = 1011;
删除一个元素
update和-
cqlsh:test> update student set interest = interest-{'打太极'} where id = 1011;
删除所有元素
update或者delete命令
UPDATE student SET interest = {} WHERE student_id = 1012; 或 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;
#使用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>
