编写sql语句小技巧

1 为字段多的表编写select、insert、update语句

示例表

create table history.enterprises(	
	objectid bigint not null,
	name text not null,
	bank text,
	registered text,
	number text,
	address text,
	post text,
	legal text,
	contact text,
	tel text,
	fax text,
	mail text,
	describe text,
	other jsonb,
	constraint pk_enterprisess_hid primary key (hid)
)

当字段很多时，人工编写select、insert、update容易出现错漏，为避免不必要的错误，可以采用下面的方法：

1.1 使用执行查询计划

创建表后执行查询计划，输出表的所有字段。

explain (verbose)
select * from enterprises limit 1;

输出结果如下

 QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.04 rows=1 width=233)
   Output: objectid, name, bank, registered, number, address, post, legal, contact, tel, fax, mail, describe, other
   ->  Seq Scan on public.enterprises  (cost=0.00..1128825.30 rows=30002430 width=233)
         Output: objectid, name, bank, registered, number, address, post, legal, contact, tel, fax, mail, describe, other
(4 rows)

倒数第二行为表的列定义，把它复制下来再结合查找替换方法，再这个基础上编写select、insert、update语句就不容易出错了。

直接复制下来的效果

--select只要把不需要的字段删除即可
select objectid, name, bank, registered, number, address, post, legal, contact, tel, fax, mail, describe, other from enterprises
--insert和update结合查找替换重新生成sql
insert into enterprises(objectid, name, bank, registered, number, address, post, legal, contact, tel, fax, mail, describe, other)
values(objectid, name, bank, registered, number, address, post, legal, contact, tel, fax, mail, describe, other)

update enterprises set objectid, name, bank, registered, number, address, post, legal, contact, tel, fax, mail, describe, other

1.1.1 替换insert

把下面的语句复制到notepad++空白文档里,打开查找替换对话框并勾选正则表达式选项。

objectid, name, bank, registered, number, address, post, legal, contact, tel, fax, mail, describe, other

输入查找内容

([A-Za-z0-9]+)

输入替换内容

@\1

然后点击全部替换，再复制回insert values部份即可。

1.1.2 替换update

步骤和inset一样，只不过替换内容修改为

\1=@\1