postgres 数据库常用sql语句

打开cmd命令窗口，进入到 postgres数据库的安装路径，例如：E:\Program Files\PostgreSQL\9.3\bin

1、导出整个数据库

格式： pg_dump -h localhost (服务器ip地址) -U postgres(用户名) 数据库名(缺省时同用户名)   >/data/dum.sql

例如：把 本机localhost上的memberMap 数据库全部导出 到 dum.sql 文件中

E:\Program Files\PostgreSQL\9.3\bin> pg_dump -h localhost -U postgres memberMap >dum.sql

2、导出某个数据库的一张表数据

格式：pg_dump -h localhost -U postgres(用户名) 数据库名(缺省时同用户名)  -t table(表名) >/data/dum.sql

例如：从 192.168.1.113服务器上导出 memberMap数据库的 t_mall表数据

E:\Program Files\PostgreSQL\9.3\bin>pg_dump -h 192.168.1.113 -U postgres -t t_mall memberMap>dum4.sql

3、导入数据文件到某个数据库

格式： psql -h(要导入库的IP地址) -U postgres(用户名)  数据库名(缺省时同用户名) < /data/dum.sql

例如：把dum4.sql文件导入到 192.168.10.48 服务器的longhutest库里

E:\Program Files\PostgreSQL\9.3\bin>psql -h 192.168.10.48 -U postgres longhutest<dum4.sql

4、postgis 空间查询

      矩形、多变形查询：格式 ST_Within(geom,ST_GeomFromText('POLYGON((多变形坐标))', 4326))

例如： select projguid,projname,x,y from loupan WHERE ST_Within(geom,ST_GeomFromText('POLYGON((99.065391 39.33249,108.190451 39.33249,108.190451 34.854516,99.065391 34.854516,99.065391 39.33249))', 4326))

 

圆形查询：格式 ST_DWithin(ST_Transform(ST_GeomFromText('POINT(中心点坐标 centers)',4326),26986),ST_Transform(geom,26986),圆半径radius)

例如：
select projguid,projname,x,y from loupan WHERE ST_DWithin(ST_Transform(ST_GeomFromText('POINT(103.22318 36.945976)',4326),26986),ST_Transform(geom,26986),331591.686399332)

5、往空间字段插入坐标值

INSERT INTO SPATIALTabLE ( THE_GEOM,　THE_NAME )   VALUES ( GeomFromText(''POINT(117.10 40.13)''), ''Mitchell'' )

6、修改空间字段的值

update t_uptown set coords=ST_GeomFromText('POINT(116.498 39.383)', 4326) where hid=22

7、查询显示出空间字段存储的坐标值

select   ST_AsText(ST_Transform(geom,4326))  from province

8、把空间字段 geom 的坐标值，赋值给属性字段 X、Y

update people set x=st_x(geom),  y=st_y(geom)  where geom is not null

9、分页查询

select * from people where address like '%望京湖光中街秀景沁园203#3105%'  limit 100 offset 0

10、字段值替换

update people set address=replace(address,'#','号')   where address is not null and address<>''

11、重命名列名：

ALTER TABLE t_uptown RENAME COLUMN "updateDate" TO updateDate

12、两表关联修改
例如： 找到  t_uptown 表中 hid 和 tmp 表中 id 相等的所有记录， 然后把district 字段的值批量修改为 tmp 表 anm字段的值

update t_uptown u set u.district=t.anm from tmp t
where u.hid=t.id

13、根据出生日期，计算出年龄的函数：
select EXTRACT(YEAR FROM age(to_timestamp('1989-02-05', 'YYYY-MM-DD')))

UPDATE public.t_member SET age=EXTRACT(YEAR FROM age(birthday));

14、postgresql 重置序列 SEQUENCE

查询得到 序列 increment_num 当前是什么值：

select setval('increment_num', 1, false)

把 序列 increment_num 重新修改为从 105 开始

ALTER SEQUENCE 'increment_numRESTART WITH 105;