cassandra和mysql_Cassandra连接和查询

cassandra开发

图形界面-NoSQL Manager for Cassandra

登陆：

创建keyspace

Cassandra的存储抽象结构和数据库一样，keyspace对应关系数据库的database或schema，column family对应于table。

示例如下：

CREATE KEYSPACE iotstp WITH replication = {'class': 'SimpleStrategy','replication_factor': 1};

其它操作

ALTER KEYSPACE iotstp WITH replication = {'class': 'SimpleStrategy', 'replication_factor' : 4};

use iotstp;

DROP KEYSPACE iotstp;

查看当前有哪些keyspace使用命令desc keyspaces。

连接Cassandra数据库

private Session session;

public UbuDSCassandraFactory(CassandraDSProperties poperties) {

this.session = generSession(poperties);

}

public Session generSession(CassandraDSProperties properties) {

Cluster cluster =

Cluster.builder()

.addContactPoint("192.168.243.22")

.withPort(9042)

//.withCredentials(cassandraUsername, cassandraPassword)

//.withSSL(sslOptions)

.build();

return cluster.connect("dev_test");

}

创建Table

创建示例如下：

CREATE TABLE IF NOT EXISTS iotstp.user (

id timeuuid,

tenant_id timeuuid,

email text,

additional_info text,

PRIMARY KEY (id, tenant_id)

);

修改示例：

ALTER TABLE iotstp.user ADD address varchar;

ALTER TABLE iotstp.user

WITH comment = 'A most excellent and useful table'

AND read_repair_chance = 0.2;

删除表

DROP TABLE [ IF EXISTS ] table_name

清空表

TRUNCATE [ TABLE ] table_name

查看当前有哪些table使用命令desc tables。

数据操作

select查询

SELECT name, occupation FROM users WHERE userid IN (199, 200, 207);

SELECT JSON name, occupation FROM users WHERE userid = 199;

SELECT name AS user_name, occupation AS user_occupation FROM users;

SELECT time, value

FROM events

WHERE event_type = 'myEvent'

AND time > '2011-02-03'

AND time <= '2012-01-01'

SELECT COUNT (*) AS user_count FROM users;

insert插入

INSERT INTO NerdMovies (movie, director, main_actor, year)

VALUES ('Serenity', 'Joss Whedon', 'Nathan Fillion', 2005)

USING TTL 86400;

INSERT INTO NerdMovies JSON '{"movie": "Serenity",

"director": "Joss Whedon",

"year": 2005}';

insert into stuff(uid，name)values(now()，'my name') //id的类型为timeuuid的

update更新

UPDATE NerdMovies USING TTL 400

SET director = 'Joss Whedon',

main_actor = 'Nathan Fillion',

year = 2005

WHERE movie = 'Serenity';

UPDATE UserActions

SET total = total + 2

WHERE user = B70DE1D0-9908-4AE3-BE34-5573E5B09F14

AND action = 'click';

delete 删除

DELETE FROM NerdMovies USING TIMESTAMP 1240003134

WHERE movie = 'Serenity';

DELETE phone FROM Users

WHERE userid IN (C73DE1D3-AF08-40F3-B124-3FF3E5109F22, B70DE1D0-9908-4AE3-BE34-5573E5B09F14);

更新与删除只支持按主键进行，意思是where关键字后面必须携带主键字段。

建表

create table demo_student ( id int primary key, name varchar, age int );

查询字段

SELECT keyspace_name, table_name, column_name, clustering_order, column_name_bytes, kind, position, "type"

FROM system_schema.columns where keyspace_name = 'dev_test' and table_name = 'demo_student';

各种查询

查询所有表

select table_name FROM system_schema.tables where keyspace_name ='%s'

查询表信息

select table_name, engine, row_format, table_rows, avg_row_length, IFNULL(auto_increment,'') auto_increment, table_collation, table_comment from information_schema.tables

where table_schema='%s' and table_name='%s' and table_type='BASE TABLE'

查询表字段

select table_name, column_name, type as column_type, clustering_order, column_name_bytes, kind, position

from system_schema.columns where keyspace_name ='%s' and table_name = '%s'

查询索引

SELECT

index_name ,

kind,

options

FROM system_schema.indexes

WHERE keyspace_name='%s' AND table_name = '%s'

查询主键

select table_name, column_name, type as column_type, clustering_order, column_name_bytes, position

from system_schema.columns where keyspace_name ='%s' and table_name = '%s' and kind = 'partition_key' ALLOW FILTERING;

查询外键

select table_name,column_name,constraint_name, referenced_table_name, referenced_column_name, referenced_table_schema

from information_schema.key_column_usage where constraint_schema ='%s' and table_name = '%s' and referenced_table_name is not null

修改表名称

alter table `%s` rename %s;

删除表

DROP TABLE IF EXISTS `%s`