Phoenix的shell简单操作

1、查询所有表

0: jdbc:phoenix:master,slaves1,slaves2:2181> !tables

2、新建一张表

注意：表名和列族名以及表名如果需要小写都得加双引号，否则创建出来的表名、列族名称和列名都是大写的。

0: jdbc:phoenix:master,slaves1,slaves2:2181> create table "student"(id integer not null primary key,"cf"."name" varchar,"cf"."age" integer);

0: jdbc:phoenix:master,slaves1,slaves2:2181> !tables
+------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+----+
| TABLE_CAT  | TABLE_SCHEM  | TABLE_NAME  |  TABLE_TYPE   | REMARKS  | TYPE_NAME  | SELF_REFERENCING_COL_NAME  | REF_GENERATION  | IN |
+------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+----+
|            | SYSTEM       | CATALOG     | SYSTEM TABLE  |          |            |                            |                 |    |
|            | SYSTEM       | FUNCTION    | SYSTEM TABLE  |          |            |                            |                 |    |
|            | SYSTEM       | SEQUENCE    | SYSTEM TABLE  |          |            |                            |                 |    |
|            | SYSTEM       | STATS       | SYSTEM TABLE  |          |            |                            |                 |    |
|            |              | student     | TABLE         |          |            |                            |                 |    |
+------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+----

0: jdbc:phoenix:master,slaves1,slaves2:2181> select * from "student";
+-----+-------+------+
| ID  | name  | age  |
+-----+-------+------+
+-----+-------+------+
No rows selected (0.039 seconds)

0: jdbc:phoenix:master,slaves1,slaves2:2181> create table student(id integer not null primary key,cf.name varchar,cf.age varchar,cf.sex varchar);

0: jdbc:phoenix:master,slaves1,slaves2:2181> !tables
+------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+----+
| TABLE_CAT  | TABLE_SCHEM  | TABLE_NAME  |  TABLE_TYPE   | REMARKS  | TYPE_NAME  | SELF_REFERENCING_COL_NAME  | REF_GENERATION  | IN |
+------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+----+
|            | SYSTEM       | CATALOG     | SYSTEM TABLE  |          |            |                            |                 |    |
|            | SYSTEM       | FUNCTION    | SYSTEM TABLE  |          |            |                            |                 |    |
|            | SYSTEM       | SEQUENCE    | SYSTEM TABLE  |          |            |                            |                 |    |
|            | SYSTEM       | STATS       | SYSTEM TABLE  |          |            |                            |                 |    |
|            |              | STUDENT     | TABLE         |          |            |                            |                 |    |
|            |              | student     | TABLE         |          |            |                            |                 |    |
+------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+----+

0: jdbc:phoenix:master,slaves1,slaves2:2181> select * from student;
+-----+-------+------+------+
| ID  | NAME  | AGE  | SEX  |
+-----+-------+------+------+
+-----+-------+------+------+
No rows selected (0.042 seconds)

3、删除table

0: jdbc:phoenix:master,slaves1,slaves2:2181> drop table "tableNmae"

4、修改表结构

0: jdbc:phoenix:master,slaves1,slaves2:2181> alter table "student" add "cf"."address" varchar;

5、查看表结构

0: jdbc:phoenix:master,slaves1,slaves2:2181> !describe "student";

0: jdbc:phoenix:master,slaves1,slaves2:2181> !describe "student";
+------------+--------------+-------------+--------------+------------+------------+--------------+----------------+------------------+
| TABLE_CAT  | TABLE_SCHEM  | TABLE_NAME  | COLUMN_NAME  | DATA_TYPE  | TYPE_NAME  | COLUMN_SIZE  | BUFFER_LENGTH  | DECIMAL_DIGITS   |
+------------+--------------+-------------+--------------+------------+------------+--------------+----------------+------------------+
|            |              | student     | ID           | 4          | INTEGER    | null         | null           | null             |
|            |              | student     | name         | 12         | VARCHAR    | null         | null           | null             |
|            |              | student     | age          | 4          | INTEGER    | null         | null           | null             |
|            |              | student     | address      | 12         | VARCHAR    | null         | null           | null             |
+------------+--------------+-------------+--------------+------------+------------+--------------+----------------+------------------

6、创建索引

0: jdbc:phoenix:master,slaves1,slaves2:2181> create index "stu_index" on "student"("cf"."name");

7、删除索引

0: jdbc:phoenix:master,slaves1,slaves2:2181> drop index "stu_index" on "student";

8、向表中插入一条数据

注意：插入的字符串使用单引号

0: jdbc:phoenix:master,slaves1,slaves2:2181> upsert into "student" values(1,'lihong',22,'zhengzhou');

0: jdbc:phoenix:master,slaves1,slaves2:2181> select * from "student";
+-----+---------+------+------------+
| ID  |  name   | age  |  address   |
+-----+---------+------+------------+
| 1   | lihong  | 22   | zhengzhou  |
+-----+---------+------+------------+

9、修改数据

注意：修改时必须带上id

0: jdbc:phoenix:master,slaves1,slaves2:2181> upsert into "student"(id,"cf"."age") values(1,25);

0: jdbc:phoenix:master,slaves1,slaves2:2181> select * from "student";
+-----+---------+------+------------+
| ID  |  name   | age  |  address   |
+-----+---------+------+------------+
| 1   | lihong  | 25   | zhengzhou  |
+-----+---------+------+------------+

10、删除一条记录

0: jdbc:phoenix:master,slaves1,slaves2:2181> delete from "student" where "name"='lihong';

0: jdbc:phoenix:master,slaves1,slaves2:2181> select * from "student";
+-----+-------+------+----------+
| ID  | name  | age  | address  |
+-----+-------+------+----------+
+-----+-------+------+----------+