【学习笔记】一些postgreSQL常用sql语句

1、序列自增id

postgre中自增id方式通过构建序列进行实现，序列名一般为表名_字段名_sql，创建序列通用语句如下：

create sequence [序列名] increment by 1 minvalue 1 no maxvalue start with 1;

例如：

create  sequence t_users_id_seq increment by 1 minvalue 1 no maxvalue start with 1;

以上语句表示新建一个名为t_users_id_seq的序列，最小值为1，无最大值，并从1开始。

之后，在设计表中，对需要进行自增的字段中添加nextval(t_users_id_seq::regclass)，表示对该字段应用序列。

如果想要重置序列，使用alert语句：

alter sequence [序列名] restart with 1

例如：

alter sequence t_users_id_seq restart with 33

应用：

在项目中使用JDBC进行数据插入时，设置了自增id后不需要指定id值，自增序列会预先生成一个id主键再进行插入。需要获取插入的结果，通过Statement.RETURN_GENERATED_KEYS的方式返回主键id，并通过stmt.getGeneratedKeys拿到ResultSet。

PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

ResultSet rs = stmt.getGeneratedKeys();

2、将从一个表中查出来的数据插入到另一个表中

insert into "T_FeatureUpdate" select * from "T_FeatureModify" where state = 10

3、sql更新替换字段中某个字

update [表名] set [字段1] = REPLACE([字段1],[原值],[替换后的值])

其中原值为字段中现有的值。例如：字段word中值为生产作物，其中需要将作物一词替换为粮食作物，变为生产粮食作物，使用下面语句：

update my_table set word = REPLACE(word,作物,粮食作物) where county = beijing

如果需要模糊匹配，即字段1中存在xx值就进行替换，例子如下：

update my_table set word = REPLACE(word,LQYD,林区用地)where word like %LQYD%

若将表中某一字段中的一个值完全替换为另一个值：

UPDATE my_table SET county = 北京市 WHERE county = beijing;

即：将county字段中的beijing替换为北京市；

4、统计一个字段多个值的次数

county字段中有多个值，想统计每个值出现的次数，用以下语句：

SELECT county as 县,COUNT(*) as 次数 FROM "my_table" GROUP BY county

5、统计某个字段重复项：

统计my_table中BSM字段为重复的记录：

select BSM,count(*) from "my_table" group by BSM having(count(*)) >1

6、用id标识删除重复字段

确保id为主键唯一且不重复，但是该方法删除之后id不连续了，对于除了id其他完全一样的记录，保留id大的那个；

delete from "my_table" where id in (select * from (select max(id) from "my_table" group by BSM having count(BSM) > 1) as b);

但是如果遇到完全相同的两条记录，没有id进行区分，则该方法会同时删除。

7、if not exists

当表中如果不存在某行数据，则插入；

INSERT [表名] ([字段1], [字段2], [字段3]) SELECT 值1, 值2, 值3 WHERE NOT EXISTS (SELECT * FROM [表名] WHERE [字段1] = 值1 AND [字段2] = 值2)

8、数据库执行完update或者insert之后数据库体积增长

排除中文字段储存大问题的以外，可能还有数据库内存不释放的原因。

解决方法：sqlite操作全部完成后，执行VACUUM命令。

9、获取当前时间

在sql 语句中，将字段类型为TIMESMAP的字段值设置为current_timestamp，例如：

INSERT INTO "my_table"(id,name,create_time) VALUES (1,张三,current_timestamp)

同理，若为Date类型字段，值为current_date.