概述
今天主要对PG数据库的一些基本SQL语句用法做个介绍,做个简单了解,也做备忘!
下面主要用例子来说明。
1、建表语句
create table test ( id int8 primary key, info text, crt_time timestamp );
注意保留字
2、select into & create table as
postgres=# select * into table new_tbl from pg_class; postgres=# create table tbl_1 as select * from pg_class;
3、插入\更新\删除\查询
insert into tbl (xx,xx) values (xx,xx); update tbl set xx=xx where xxx; delete from tbl where xxx=xxx; select xx from xx where xx...;
如果是delete|update limit,则:
update tbl set xx=xx where ctid = any ( array (select ctid from tbl where xx limit ? for update)); delete from tbl where ctid = any ( array (select ctid from tbl where xx limit ? for update));
4、批量DML
insert into xx values (),(),...(); copy xx from stdin; copy xx from 'file'; pg_bulkload update t set info=t1.info,crt_time=t1.crt_time from t1,t2 where (t.id=t1.id) and t1.id=t2.id; update tbl_1 set relname=tmp.rel from (values (1,'test1'),(2,'test2')) tmp (id, rel) where tmp.id=tbl_1.id; delete from t using t1 where t.id=t1.id; delete from tbl_1 using (values (1),(2)) tmp (rel) where tmp.rel=tbl_1.reltype;
注意update , delete 批量操作,JOIN不是一一对应时,更新目标可能会随机匹配。
5、DB端copy+客户端copy
? https://github.com/digoal/blog/blob/master/201805/20180516_03.md
? https://github.com/digoal/blog/blob/master/201805/20180510_01.md
5.1、copy为什么快?
协议:
5.2、DB 端copy
copy tbl to 'file'; copy (SQL) to 'file'; copy tbl from 'file';
5.3、客户端copy
copy tbl from stdin;c opy (SQL) to stdout; copy tbl to stdout; psql (\copy to | from); -- copy协议
6、排序+ offset limit
select * from tbl_1 order by relname nulls first; select * from tbl_1 order by relname nulls last; select * from tbl_1 order by relname; select * from tbl_1 order by relname limit 10 offset 10; select * from tbl_1 order by relname::text collate "C";
7、聚合+解耦合
select string_agg(relname,',' order by xx) from tbl_1; select g,avg(c1) from tbl group by g;
8、distinct
select distinct relname,relnamespace from pg_class;SELECT id, COUNT_DISTINCT(val) FROM test_table GROUP BY 1; select count(distinct (relname,relnamespace)) from pg_class;select distinct on (c3) c2,c3 from tbl;
9、INNER|OUTER JOIN
?inner
select * from t1 join t2 on (t1.x=t2.x) where xxxx;
? left
1)scan filter
select t1.*,t2.* from t1 left join t2 on (t1.x=t2.x) where t1.x=x;
2)join filter
select t1.*,t2.* from t1 left join t2 on (t1.x=t2.x and t1.x=x);
? right
把上面的left join改成right join即可,这里就不多说了。
篇幅有限,这块内容就介绍到这了,后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注一下~