首页 > 代码库 > Cassandra 2.1 数据查询语法。

Cassandra 2.1 数据查询语法。

1,官方文档,基本类型

数据查询语言文档:
http://www.datastax.com/documentation/cql/3.1/cql/cql_reference/update_r.html

cql 支持的数据类型:
相对于 MySQL,有几个类型比较有意思,uuid类型,map,list,set类型,这个优化关联查询,直接将List存入一条记录。

CQL TypeConstantsDescription
asciistringsUS-ASCII character string
bigintintegers64-bit signed long
blobblobsArbitrary bytes (no validation), expressed as hexadecimal
booleanbooleanstrue or false
counterintegersDistributed counter value (64-bit long)
decimalintegers, floatsVariable-precision decimal

Java type

doubleintegers64-bit IEEE-754 floating point

Java type

floatintegers, floats32-bit IEEE-754 floating point

Java type

inetstringsIP address string in IPv4 or IPv6 format, used by the python-cql driver and CQL native protocols
intintegers32-bit signed integer
listn/aA collection of one or more ordered elements
mapn/aA JSON-style array of literals: { literal : literal, literal : literal ... }
setn/aA collection of one or more elements
textstringsUTF-8 encoded string
timestampintegers, stringsDate plus time, encoded as 8 bytes since epoch
timeuuiduuidsType 1 UUID only
tuplen/aCassandra 2.1 and later. A group of 2-3 fields.
uuiduuidsA UUID in standard UUID format
varcharstringsUTF-8 encoded string
varintintegersArbitrary-precision integer

Java type


java支持的数据类型:

CQL typeJava type
decimaljava.math.BigDecimal
floatjava.lang.Float
doublejava.lang.Double
varintjava.math.BigInteger
In this topic:

2,查看,创建keyspace,数据表

查看命令和MySQL类似。
desc cluster;

desc keyspaces;

desc keyspace portfoliodemo;

desc tables;

desc table stocks;
创建keyspace: 默认制定SimpleStrategy的副本类型。
Create a keyspace.
cqlsh> CREATE KEYSPACE demodb WITH REPLICATION = { ‘class‘ : ‘SimpleStrategy‘, ‘replication_factor‘ : 1 } AND durable_writes = true;
Use the keyspace.
cqlsh> USE demodb;
创建数据表:
CREATE TABLE users (
  userid uuid PRIMARY KEY,
  first_name text,
  last_name text,
  emails set<text>,
  top_scores list<int>,
  todo map<timestamp, text>,
  create_time timestamp
);

Cassandra 有一个特性就是底层做好分布式了,所以再查询排序的时候限制就比较多。
要按照用户才创建时间倒叙查询,必须再创建表的时候就写好。
CREATE TABLE users (
  userid uuid PRIMARY KEY,
  first_name text,
  last_name text,
  emails set<text>,
  top_scores list<int>,
  todo map<timestamp, text>,
  create_time timestamp
  PRIMARY KEY (userid, create_time)
)
WITH CLUSTERING ORDER BY (create_time DESC);

默认定义的时正序,倒叙需要再定义下,并且把这个字段放入到primary key 里面。
参考:http://www.datastax.com/documentation/cql/3.1/cql/cql_reference/refClstrOrdr.html

更新表结构和mysql类似:
ALTER TABLE users ALTER bio TYPE text;

3,插入数据,更新

和mysq 类似:其中emails是set类型。
INSERT INTO users (userid, first_name, last_name, emails)
  VALUES(cfd66ccc-d857-4e90-b1e5-df98a3d40cd6 , ‘Frodo‘, ‘Baggins‘, {‘f@baggins.com‘, ‘baggins@gmail.com‘});

更新数据,比较特殊的时list,map,set类型:
其他的类似,参考 http://www.datastax.com/documentation/cql/3.1/cql/cql_reference/update_r.html
增加emails数据:使用+
 UPDATE users SET emails = emails + {‘fb@friendsofmordor.org‘} WHERE userid = cfd66ccc-d857-4e90-b1e5-df98a3d40cd6;
删除emails数据:使用-
 UPDATE users SET emails = emails - {‘fb@friendsofmordor.org‘} WHERE userid = cfd66ccc-d857-4e90-b1e5-df98a3d40cd6;
清空emails数据:使用{}
 UPDATE users SET emails = {} WHERE userid = cfd66ccc-d857-4e90-b1e5-df98a3d40cd6;

4,查询数据


查询数量
SELECT COUNT(*) FROM users;
查询前10条
SELECT * FROM users LIMIT 10 ALLOW FILTERING;
按照token查询
SELECT * FROM users WHERE TOKEN(userid) >= TOKEN(cfd66ccc-d857-4e90-b1e5-df98a3d40cd6);
查询token内容,token只能是primary key。
SELECT TOKEN(userid) FROM users WHERE TOKEN(userid) >= TOKEN(cfd66ccc-d857-4e90-b1e5-df98a3d40cd6);

还支持distinct,in等查询,但不支持关联查询,毕竟不是关系型数据库。




Cassandra 2.1 数据查询语法。