访问函数
postgres=# select md5(random()::text);
结果:
md5----------------------------------3eb6c0c8f8355f0b0f0cad7a8f0f7491
数据排序
按某一列排序
postgres=# INSERT into tbase (nickname) VALUES('TBase 好');postgres=# INSERT into tbase (id,nickname) VALUES(1,'TBase 分布式数据库的时代来了');postgres=# select * from tbase order by id;
结果:
id | nickname----+-----------------------------1 | hello TBase1 | TBase 分布式数据库的时代来了2 | TBase 好(3 rows)
按第一列排序
postgres=# select * from tbase order by 1;
按 id 升序排序,再按 nickname 降序排序
postgres=# select * from tbase order by id, nickname desc;
随机排序
postgres=# select * from tbase order by random();
计算排序
postgres=# select * from tbase order by md5(nickname);
排序使用子查询
postgres=# select * from tbase order by (select id from tbase order by random() limit 1);
null 值排序结果处理
postgres=# insert into tbase values(4,null);postgres=# select * from tbase order by nickname nulls first;postgres=# select * from tbase order by nickname nulls last;
按拼音排序
postgres=# select * from (values ('张三'), ('李四'),('陈五')) t(myname) order by myname;postgres=# select * from (values ('张三'), ('李四'),('陈五')) t(myname) order by convert(myname::bytea,'UTF-8','GBK');postgres=# select * from (values ('张三'), ('李四'),('陈五')) t(myname) order by convert_to(myname,'GBK');postgres=# select * from (values ('张三'), ('李四'),('陈五')) t(myname) order by myname collate "zh_CN.utf8";
where 条件使用
单条件查询
postgres=# select * from tbase where id=1;
多条件 and
postgres=# select * from tbase where id=1 and nickname like '%h%';
多条件 or
postgres=# select * from tbase where id=2 or nickname like '%h%';
ilike 不区分大小写匹配
postgres=# create table t_ilike(id int,mc text);postgres=# insert into t_ilike values(1,'tbase'),(2,'TBase');postgres=# select * from t_ilike where mc ilike '%tb%';
where 条件支持子查询
postgres=# select * from tbase where id=(select (random()*2)::integer from tbase order by random() limit 1);
null 值查询方法
postgres=# select * from tbase where nickname is null;postgres=# select * from tbase where nickname is not null;
exists,只要有记录返回就为真
postgres=# create table t_exists1(id int,mc text);postgres=# insert into t_exists1 values(1,'tbase'),(2,'TBase');postgres=# create table t_exists2(id int,mc text);postgres=# insert into t_exists2 values(1,'tbase'),(1,'TBase');postgres=# select * from t_exists1 where exists(select 1 from t_exists2 where t_exists1.id=t_exists2.id);
exists 等价写法
postgres=# select t_exists1.* from t_exists1, (select distinct id from t_exists2) as t where t_exists1.id=t.id;
分页查询
默认从第一条开始,返回一条记录:
postgres=# select * from tbase limit 1;
结果:
id | nickname----+-------------1 | hello TBase(1 row)
使用
offset
指定从第几条开始,0 表示第一条开始,返回 1 条记录:postgres=# select * from tbase limit 1 offset 0;
结果:
id | nickname----+-------------1 | hello TBase(1 row)
从第 3 条开始,返回两条记录:
postgres=# select * from tbase limit 1 offset 2;
结果:
id | nickname----+-----------------1 | TBase 分布式数据库的时代来了(1 row)
使用
order by
可以获得一个有序的结果:postgres=# select * from tbase order by id limit 1 offset 2;
结果:
id | nickname----+-----------2 | TBase 好(1 row)
合并多个查询结果
不过滤重复的记录:
postgres=# select * from tbase union all select * from t_appoint_col;
结果:
id | nickname----+-----------------1 | hello TBase2 | TBase 好1 | TBase 分布式数据库的时代来了1 | hello TBase(4 rows)
过滤重复的记录:
postgres=# select * from tbase union select * from t_appoint_col;
结果:
id | nickname----+-----------------1 | TBase 分布式数据库的时代来了1 | hello TBase2 | TBase 好(3 rows)
返回两个结果的交集
postgres=# select * from t_intersect1 INTERSECT select * from t_intersect2;
结果:
id | mc----+-------1 | tbase(1 row)
返回两个结果的差集
postgres=# select * from t_except1 except select * from t_except2;
结果:
id | mc----+-------2 | tbase(1 row)
any 用法
只需要大于其中一个值即为真:
postgres=# select * from t_any where id > any (select 1 union select 3);
结果:
id | mc----+-------2 | TBase(1 row)
all 用法
需要大于所有值才为真:
postgres=# select * from t_all where id > all (select 1 union select 2);
结果:
id | mc----+-------3 | TBase(1 row)
聚集查询
统计记录数
postgres=# select count(1) from tbase;
结果:
count-------3(1 row)
统计不重复值的记录表
postgres=# select count(distinct id) from tbase;
结果:
count-------2(1 row)
求和
postgres=# select sum(id) from tbase;
结果:
sum-----4(1 row)
求最大值
postgres=# select max(id) from tbase;
结果:
max-----2(1 row)
求最小值
postgres=# select min(id) from tbase;
结果:
min-----1(1 row)
求平均值
postgres=# select avg(id) from tbase;
结果:
avg--------------------1.3333333333333333(1 row)
分组字段合并成一个字符串
postgres=# create table t1(f1 int, f2 text, f3 text);postgres=# insert into t1 values(1,'a','a');postgres=# insert into t1 values(1,'b','b');postgres=# insert into t1 values(2,'a','a');postgres=# select f1, string_agg(f2, ',') from t1 group by f1;
结果:
f1 | string_agg----+------------1 | a,b2 | a(2 rows)
去重和自定义聚合函数
在聚合函数中使用
DISTINCT
和 ORDER BY
需要满足特定条件,可以自定义函数来实现更灵活的聚合。多表关联
内连接
postgres=# select * from tbase inner join t_appoint_col on tbase.id=t_appoint_col.id;
结果:
id | nickname | id | nickname----+-----------------------------+----+-------------1 | hello TBase | 1 | hello TBase1 | TBase 分布式数据库的时代来了 | 1 | hello TBase(2 rows)
左外连接
postgres=# select * from tbase left join t_appoint_col on tbase.id=t_appoint_col.id;
结果:
id | nickname | id | nickname----+------------+----+-------------1 | hello TBase | 1 | hello TBase2 | TBase 好 | |1 | TBase 分布式数据库的时代来了 | 1 | hello TBase(3 rows)
右外连接
postgres=# select * from tbase right join t_appoint_col on tbase.id=t_appoint_col.id;
结果:
id | nickname | id | nickname----+-------------------------------+----+-------------1 | TBase 分布式数据库的时代来了 | 1 | hello TBase1 | hello TBase | 1 | hello TBase| 5 | Power TBase(3 rows)
全连接
postgres=# select * from tbase full join t_appoint_col on tbase.id=t_appoint_col.id;
结果:
id | nickname | id | nickname----+------------+----+-------------1 | hello TBase | 1 | hello TBase2 | TBase 好 | |1 | TBase 分布式数据库的时代来了 | 1 | hello TBase| | 5 | Power TBase(4 rows)
聚合函数并发计算
单核计算
postgres=# set max_parallel_workers_per_gather to 0;postgres=# select count(1) from t_count;
二核并行
postgres=# set max_parallel_workers_per_gather to 2;postgres=# select count(1) from t_count;
四核并行
postgres=# set max_parallel_workers_per_gather to 4;postgres=# select count(1) from t_count;
not in 子句包含 null 的情况
postgres=# create table t_not_in(id int, mc text);NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.CREATE TABLEpostgres=# insert into t_not_in values(1, 'tbase'), (2, 'pgxz');INSERT 0 2postgres=# select * from t_not_in where id not in (3, 5);
结果:
id | mc----+-------1 | tbase2 | pgxz(2 rows)
postgres=# select * from t_not_in where id not in (3, 5, null);
结果:无输出行
只查询特定数据节点(dn)的数据
postgres=# create table t_direct(id int, mc text);NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.CREATE TABLEpostgres=# insert into t_direct values(1, 'tbase'), (3, 'pgxz');INSERT 0 2postgres=# EXECUTE DIRECT ON (dn001) 'select * from t_direct';
结果:
id | mc----+-------1 | tbase(1 row)
postgres=# EXECUTE DIRECT ON (dn002) 'select * from t_direct';
结果:
id | mc----+------3 | pgxz(1 row)
查询所有节点的数据:
postgres=# select * from t_direct;
结果:
id | mc----+-------1 | tbase3 | pgxz(2 rows)
特殊应用
多行变成单行
postgres=# create table t_mulcol_tosimplecol(id int, mc text);NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.CREATE TABLEpostgres=# insert into t_mulcol_tosimplecol values(1, 'tbase'), (2, 'TBase');INSERT 0 2postgres=# select array_to_string(array(select mc from t_mulcol_tosimplecol), ',');
结果:
array_to_string-----------------tbase,TBase(1 row)
一列变成多行
postgres=# create table t_col_to_mulrow(id int, mc text);NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.CREATE TABLEpostgres=# insert into t_col_to_mulrow values(1, 'tbase,TBase');INSERT 0 1postgres=# select regexp_split_to_table((select mc from t_col_to_mulrow where id=1 limit 1), ',');
结果:
regexp_split_to_table-----------------------tbaseTBase(2 rows)
查询记录所在数据节点
postgres=# select xc_node_id, * from t1;
结果:
xc_node_id | f1 | f2------------+----+----2142761564 | 1 | 32142761564 | 1 | 3(2 rows)
查询并映射节点名称:
postgres=# select t1.xc_node_id, pgxc_node.node_name, t1.* from t1, pgxc_node where t1.xc_node_id=pgxc_node.node_id;
结果:
xc_node_id | node_name | f1 | f2------------+-----------+----+----2142761564 | dn001 | 1 | 32142761564 | dn001 | 1 | 3(2 rows)
grouping sets/rollup/cube 用法
group by 用法
创建销售明细表并插入数据:
create table t_grouping(id int, dep varchar(20), product varchar(20), num int);insert into t_grouping values(1, '业务 1 部', '手机', 90);-- 更多插入数据 ...
按部门和产品进行分组汇总:
postgres=# select dep, product, sum(num) from t_grouping group by dep, product order by dep, product;
结果:
dep | product | sum-----------+-----------+-----业务 1 部 | 电脑 | 80业务 1 部 | 手机 | 160业务 2 部 | 电脑 | 120业务 2 部 | 手机 | 50业务 3 部 | 电脑 | 80业务 3 部 | 手机 | 160
使用 grouping sets 进行分组:
postgres=# select dep, product, sum(num) from t_grouping group by grouping sets((dep), (product), ());
结果:
dep | product | sum-----------+-----------+-----业务 1 部 | (空) | 240业务 2 部 | (空) | 170业务 3 部 | (空) | 240电脑 | (空) | 280手机 | (空) | 370(空) | (空) | 650
使用 rollup 和 cube:
postgres=# select dep, product, sum(num) from t_grouping group by rollup((dep), (product));postgres=# select dep, product, sum(num) from t_grouping group by cube((dep), (product));
结果与 grouping sets 相同。
PREPARE 预备使用
创建一个预备语句
postgres=# create table t1(f1 int, f2 int);CREATE TABLEpostgres=# insert into t1 values(1, 1), (2, 2);COPY 2postgres=# PREPARE usrrptplan (int) AS SELECT * FROM t1 WHERE f1=$1;PREPAREpostgres=# EXECUTE usrrptplan(1);
结果:
f1 | f2----+----1 | 1(1 row)
释放一个预备语句
postgres=# DEALLOCATE usrrptplan;DEALLOCATEpostgres=# EXECUTE usrrptplan(1);ERROR: prepared statement "usrrptplan" does not exist