6.1. 进入lxdb

su lxdb #切换到lxdb账号
psql     #运行sql命令行

1. 建表

CREATE FOREIGN TABLE lxdb_key_val (
ukey text OPTIONS(key 'unique'),
i1 int,
i2 int,
txt1 text,
txt2 text
) SERVER lxdb options(store 'ids');

6.2. 建2个分片

注：分片数一般选择机器的数量

select create_distributed_table('lxdb_key_val', 'ukey','hash','default',2);

6.3.插入数据

INSERT INTO lxdb_key_val(ukey,i1, i2, txt1,txt2) VALUES ('1111',0, 2, 'txt2', 'txt2');
INSERT INTO lxdb_key_val(ukey,i1, i2, txt1,txt2) VALUES ('1112',0, 4, 'txt1', 'txt2');
INSERT INTO lxdb_key_val(ukey,i1, i2, txt1,txt2) VALUES ('1113',0, 6, 'txt2', 'txt2');
INSERT INTO lxdb_key_val(ukey,i1, i2, txt1,txt2) VALUES ('1114',0, 8, 'txt1', 'txt2');

6.4. 查询数据

select * from lxdb_key_val limit 10;    
select * from lxdb_key_val where txt1 in ('txt1','txt2')  limit 10;
select * from lxdb_key_val where txt1 not in ('txt1','txt2')  limit 10;
select * from lxdb_key_val where txt1 like 'txt1'  limit 10;
select * from lxdb_key_val where txt1 not like 'txt1'  limit 10;
select * from lxdb_key_val where i2 between 0 and 103  limit 10;
select * from lxdb_key_val where i2 >= 103  limit 10;
select * from lxdb_key_val where i2 < 103  limit 10;

6.5. 统计数据

select count(*) from lxdb_key_val ;
select i1,count(*) from lxdb_key_val group by i1 ;
select i1,i2,count(*) from lxdb_key_val group by i1,i2 ;
select txt1,txt2,count(*),sum(i2) from lxdb_key_val group by txt1,txt2 ;