postgres sql语句总结_pgsq but type character varying

创建表：

create table tablename 
    (field1 integer not null peimary key,    //int，不为空，主键
     field2 character varying(10) not null,    //char，10个字节，不为空
     field3 serial not null,    //int,自增，不为空
     field4 text,    //char，文本
     field5 numeric,    //int，数字
     field6 timestamp without time zone,    //不带时区的时间
     field7 numeric default 0)    //int，数字，默认0

插入数据：

insert into tablename(field1,field2,field3) values(1,'%2',3,'%4',5,'2060-06-06','7')

查询某字段不为空(为空)：

//不为空
 
SELECT * FROM "tablename" WHERE "fieldname" IS NOT NULL
 
//为空
 
SELECT * FROM "tablename" WHERE "fieldname" IS NULL

计算某长度字段长度：

select sum("field") from "tablename"

统计某字段个数：

select count("field") from "tablename"

查询某字段范围内数据：

select "filed" from "tablename" where "field" between '' and ''

查询某字段符合条件：

select "fieldname" from "tablename" where "field" ~* '^%1$'//全匹配%1
select "fieldname" from "tablename" where "field" ~* '%1'//不匹配%1
select "fieldname" from "tablename" where "field" ~* '%1$'//后匹配%1
select "fieldname" from "tablename" where "field" ~* '^%1'//前匹配%1
//多条件查询
select "fieldname" from "tablename" where "field1" ~* '%1' and "field2" ~* '%2'
select "fieldname" from "tablename" where "field" ~* '%1' or "field" ~* '%2'
//跨表查询
select "fieldname1" from "tablename1" where "field1" in (select "fieldname2" from "tablename2" where "field2" ~* '%1')
 
//通过多条件查询跨表统计某字段个数
select count(*) from "tablename1" where "fieldname1" in (select "fieldname2" from "tablename2" where ("field1" ~* '%1' or "field1" ~* '%2') and ("filed2" between '%3' and '%4') ) or "fieldname3" in (select "fieldname4" from "tablename3" where ("filed3" ~* '%5'))

查询某字段中的数据存在且不重复

select distinct "field" from "tablename" where "field" is not null

同表拼接多字段去重查询

最终查询的字段必须在group by里出现
select concat("fieldname1","fieldname2"),"fieldname1" from "tablename" group by concat("fieldname1","fieldname2")//查询拼接fieldname1，filedname2字段不重复数据
 
 
select "fieldname1" from (select concat("fieldname2","fieldname3"),"fieldname1" from "tablename" group by concat("fieldname2","fieldname3"),"fieldname1") name
//查询字段fieldname1，条件是满足拼接fieldname2，fieldname3后不重复的数据
//name是from需求的别名
 
实例:查询圆柱体表的直径与高数据，每个圆柱体的唯一标识符是头尾结点，条件是圆柱体不重复
select "d_s","length" from (select concat("s_point","e_point"),"d_s","length" from table
group by concat("s_point","e_point"),"d_s","length") sum