pg_sql常用查询语句整理

#pg_sql之增删改查

#修改：
inset into table_name
(id,
name,
age,
address
)
select
replace(old_id,old_id,new_id),#old_id字段的old_id数据，替换为new_id
replace(name,name,substring(concat(‘【新名字】‘),name),1,20)),#name字段替换为【新名字】+name
20,
address
from
    table_name
where
    id =‘201901‘

#增加
inset into table_name
(id,
name,
age,
address,
date
)
values(
‘201901‘,
‘xiaoming‘,
‘23‘,
‘上海‘,
TO_DATE(‘2006-03-01‘,‘YYYY-MM-DD‘))

#更新
update table_name
    set id =3
    where id = 2 and name =‘xiaoming‘

#联表搜索 join on
mybatis注解多条件查询
@Selct({"<script>"+"查询语句"+"</script>"})
List<DemoBo> getListByForm(Page<DemoBo> page,DemoForm demoForm)
查询语句如下：

select
    A.id,
    B.name,
    A.address,
    A.age
from table_nameA as A
    left join table_nameB as B on A.id = B.id
where A.id = 0
    <if test ‘id != null‘> and A.id <![CDATA[=]]> #{id}</if>
    <if test ‘name != null‘> and A.name <![CDATA[=]]> #{name}</if>
order by A.age desc,A.id desc

#条件判断 case when
selct a.id ,a.name,a.age
    case when
        b.begindate = b.enddate
        then 1
        else 0
    end as f_date
from table_nameA as a
    inner join(
            selct id,
            count(*) as count,
            sum(case when age =2 then 1 else 0 end) as age
            from table_nameB
            group by id
        ) as b on a.id = b.id
where a.id =1232