基础使用

权限设置

• 为数据库设置权限：

  -- 授予用户 admin_name 对数据库 database_name 的所有权限
  grant all privileges on database database_name to admin_name;
  
  -- 示例
  grant all privileges on database atgv_db_app to atgv_admin;

• 为数据库中所有表设置权限：

  -- 授予用户 admin_name 对模式 public 中所有表的所有权限
  grant all privileges on all tables in schema public to admin_name;
  
  -- 示例
  grant all privileges on all tables in schema public to atgv_admin;

• 更新数据库中表权限：

  -- 将表 table_name 的所有者修改为 admin_name
  alter table table_name owner to admin_name;
  
  -- 示例
  alter table basic_airport owner to admin;

复制数据库

在 PostgreSQL 中，有两种方法复制数据库：

1. 使用 create database 从 旧库（模板数据库）复制到 新库，此方法仅适用于在同一个 PostgreSQL 服务器内操作；

   警示

   1. 为了保障数据的安全性，在进行此操作前做好将数据库进行备份；
   2. 要复制之前，必要要将旧库先设置为 template 模板数据库；

   -- 设置为模板数据库
   alter database old_db with is_template true;
   -- 迁移数据库
   create database new_db with template old_db;

2. 手动备份现有数据库，并将其恢复到另一个新数据库；

删除数据库

在删除 postgres 数据库时，提示下面的报错：

这是因为当前数据库在被别的用户使用，此时删除数据库千万要慎重，确保该数据库是可被删除的。

-- 断开要删除数据库的所有连接
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname='ccm_zbsn_app' AND pid<>pg_backend_pid();

-- 删除数据库
drop DATABASE ccm_zbsn_app;

参数	作用
pg_terminate_backend	用来终止数据库正在连接使用的所有函数
pg_stat_activity	是一个系统表，用于存储服务进程的属性和状态
pg_backend_pid()	是一个系统函数，获取附加到当前会话的服务器进程的I

WKT 转 geojson

WKT 在线预览：https://wktmap.com/

WKT 在线转 geojson：[WKT在线转换为geojson](https://www.zaixianjisuan.com/dilicesuan/wkt _ wkb _ geojson convert online.html)

WKT简介

1. WKT 是用于描述地理空间数据的文本格式，它可以表示点、线、面等几何对象，每个点都是一个经纬度，点与点之间用空格分割；

2. EWKT 是带有 空间参考坐标系的 WKT；

-- 语法
SELECT ST_AsGeoJSON('WKT'::geometry) AS geojson;

-- WKT 转 geojson
SELECT ST_AsGeoJSON('MULTIPOLYGON(((117.990302548244 39.7223643095277, 117.990483660197 39.7223533408427, 117.990475103726 39.7221208043105, 117.990302548244 39.7223643095277)))'::geometry) AS geojson;

geojson 转 WKT

-- 语法
SELECT ST_AsText(ST_GeomFromGeoJSON('geojson')) AS wkt;
SELECT ST_AsEwkt(ST_GeomFromGeoJSON('geojson')) As ewkt;

-- geojson 转 WKT
SELECT ST_AsText(ST_GeomFromGeoJSON('{"type":"MultiPolygon","coordinates":[[[[117.990302548,39.72236431],[117.99048366,39.722353341],[117.990475104,39.722120804],[117.990302548,39.72236431]]]]}')) AS wkt;

-- geojson 转 EWKT
SELECT ST_AsEwkt(ST_GeomFromGeoJSON('{"type":"MultiPolygon","coordinates":[[[[117.990302548,39.72236431],[117.99048366,39.722353341],[117.990475104,39.722120804],[117.990302548,39.72236431]]]]}')) AS ewkt;

从WKT中提取经纬度

需求：有个一 WKT 格式的点 POINT(114.202806 30.771083)，想要提取它的经度和纬度，并重新命名为 lon 和 lat，该怎么做？

可以使用 PostGIS 的扩展：

• ST_X(geom::geometry)：从几何对象中提取经度（X坐标）；
• ST_Y(geom::geometry)：从几何对象中提取纬度（Y坐标）；

SELECT id,
	st_x ( geom :: geometry ) as lon,
	st_y ( geom :: geometry ) as lat 
from
	geo_vec_standpoint;

SQL增加时间

需求：给了一个时间 2024-12-11 21:44:25，我想要往后增加 10 天怎么做？

-- 往后增加天数
update app_test set update_time = update_time + INTERVAL '10 days'

-- 往后增加小时
update app_test set update_time = update_time + INTERVAL '1 hours'

-- 往后增加分钟
update app_test set update_time = update_time + INTERVAL '10 minutes'

-- 往后增加天数 + 小时 + 分钟
update app_test set update_time = update_time + INTERVAL '1 days, 1 hours, 10 minutes'

特别的，可以往前倒退时间：

-- 往前增加天数
update app_test set update_time = update_time + INTERVAL '-10 days'

SQL时间转字符串

需求：给了一个时间 2024-12-11 21:44:25，怎么把他转为字符串类型？

-- 查询某个字符串，转为字符串
SELECT to_char(update_time, 'YYYYMMDDHH24MISS') as string_time from app_test;

-- 使用时间字段给其他字段赋值
UPDATE app_test set string_time = to_char(update_time, 'YYYYMMDDHH24MISS');

参数	作用
YYYY	年
MM	月
DD	日
HH24	时（24小时制）
MI	分
SS	秒

拓展：

-- 设置时间为long类型
UPDATE app_test set long_time = CAST(to_char(update_time, 'YYYYMMDDHH24MISS') AS BIGINT)