cassandra 2.x cql 5 語法學習(四)

學習如何在 table 上index的使用 及新增 column


1. create table

cqlsh:demo>  

CREATE TABLE playlists (
  id uuid,
  song_order int,
  song_id uuid,
  title text,
  album text,
  artist text,
  PRIMARY KEY (id, song_order ) );

2. insert data to playlists

cqlsh:demo>  

INSERT INTO playlists (id, song_order, song_id, title, artist, album)
VALUES (
62c36092-82a1-3a00-93d1-46196ee77204, 4, 7db1a490-5878-11e2-bcfd-0800200c9a66,
'Ojo Rojo', 'Fu Manchu', 'No One Rides for Free');

cqlsh:demo> 

INSERT INTO playlists (id, song_order, song_id, title, artist, album)
VALUES (
88c36092-82a1-3a00-93d1-46196ee77204, 7, 7db1a490-5878-11e2-bcfd-0800200c9a88,
'測試', '未知', 'No One Rides for Free');

cqlsh:demo> 

select * from playlists;


 id                                   | song_order | album                 | artist    | song_id                              | title
--------------------------------------+------------+-----------------------+-----------+--------------------------------------+----------
 62c36092-82a1-3a00-93d1-46196ee77204 |          4 | No One Rides for Free | Fu Manchu | 7db1a490-5878-11e2-bcfd-0800200c9a66 | Ojo Rojo
 88c36092-82a1-3a00-93d1-46196ee77204 |          7 | No One Rides for Free |      未知 | 7db1a490-5878-11e2-bcfd-0800200c9a88 |     測試

(2 rows)

3. create index on artist

cqlsh:demo> 

CREATE INDEX ON playlists(artist );

cqlsh:demo>

SELECT * FROM playlists WHERE artist = 'Fu Manchu';

 id                                   | song_order | album                 | artist    | song_id                              | title
--------------------------------------+------------+-----------------------+-----------+--------------------------------------+----------
 62c36092-82a1-3a00-93d1-46196ee77204 |          4 | No One Rides for Free | Fu Manchu | 7db1a490-5878-11e2-bcfd-0800200c9a66 | Ojo Rojo

若沒有 create index 的話， 無法直接使用 where 的條件做 query 可以 drop index 看看再做query, 看看結果會如何？

cqlsh:demo> 

DROP INDEX playlists_artist_idx ;

cqlsh:demo>
SELECT * FROM playlists WHERE artist = 'Fu Manchu';

code=2200 [Invalid query] message="No indexed columns present in by-columns clause with Equal operator"

會產生 code=2200 error, 所以記得要加上 index 才可做進階的query

cqlsh:demo> 
CREATE INDEX ON playlists(artist );

4. 基本上 order by 及 limit 都跟 sql 語法雷同, 不在此多做說明

cqlsh:demo> 

SELECT * FROM playlists WHERE id = 62c36092-82a1-3a00-93d1-46196ee77204
ORDER BY song_order DESC LIMIT 2;


 id                                   | song_order | album                 | artist    | song_id                              | title
--------------------------------------+------------+-----------------------+-----------+--------------------------------------+----------
 62c36092-82a1-3a00-93d1-46196ee77204 |          4 | No One Rides for Free | Fu Manchu | 7db1a490-5878-11e2-bcfd-0800200c9a66 | Ojo Rojo

(1 rows)

5. 在 table 中新增 Collection columns

cqlsh:demo> 

ALTER TABLE playlists ADD tags set<text>;

此時可以用 desc 來檢測一下 table 的 schema 是否有跟著改變

cqlsh:demo> 

desc TABLE playlists 

cqlsh:demo> 

CREATE TABLE demo.playlists (
    id uuid,
    song_order int,
    album text,
    artist text,
    song_id uuid,
    tags setset<text>,
    title text,
    PRIMARY KEY (id, song_order)
.
.
.

從 schema 中可以看出，table playlists 多新增了一個 colume tags
6. 更新新增欄位的 value 語法

cqlsh:demo> 

UPDATE playlists  set tags = tags + {'2013'} where id = 62c36092-82a1-3a00-93d1-46196ee77204 and song_order = 4;
UPDATE playlists  set tags = tags + {'covers'} where id = 62c36092-82a1-3a00-93d1-46196ee77204 and song_order = 4;

select * from playlists ;

 id                                   | song_order | album                 | artist    | song_id                              | tags               | title
--------------------------------------+------------+-----------------------+-----------+--------------------------------------+--------------------+----------
 62c36092-82a1-3a00-93d1-46196ee77204 |          4 | No One Rides for Free | Fu Manchu | 7db1a490-5878-11e2-bcfd-0800200c9a66 | {'2013', 'covers'} | Ojo Rojo
 88c36092-82a1-3a00-93d1-46196ee77204 |          7 | No One Rides for Free |      未知 | 7db1a490-5878-11e2-bcfd-0800200c9a88 |               null |     測試

(2 rows)

可以看到欄位多了一個 tabs, 新增的 data 也有 show 出來。

7. 更新欄位名稱語法

cqlsh:demo> 

ALTER TABLE playlists RENAME id TO uid;

cqlsh:demo>

desc TABLE playlists ;

CREATE TABLE demo.playlists (
    uid uuid,
    song_order int,
    album text,
    artist text,
    song_id uuid,
    tags set,
    title text,
    PRIMARY KEY (uid, song_order)
.
.
.