当前位置：首页 > news >正文

pgsql语法

news 2026/6/1 11:23:58

pgsql

分层

数据库+模式+表

模式：auth，public（默认），storage

\l -- 显示所有数据库\dn -- 显示所有模式\d -- 表信息显示 create schemaifnot exists storage;-- 创建模式 drop schemaifexists storage cascade;-- 级联删除

表继承

CREATETABLEcontent(titleTEXT,authorTEXT);-- 继承基础字段，添加字段createtablevideo(durationINT)inherits(content);

常见用法

-- 创建用户并且授权createuserrootwithpasssword'123456';GRANTallPRIVILEGESonDATABASEtest01toroot;-- 修改数据库名称ALTERDATABASEtest01RENAMETOfinancial_system;-- 删除数据库DROPDATABASEfinancial_system;-- 主键自增CREATETABLEUSERS(u_idserialPRIMARYKEY,-- 主键自增u_namevarchar(50),create_timeTIMESTAMPDEFAULTnow()-- 时间默认现在);-- Json JSONBCREATETABLEgoods(g_idserialPRIMARYKEY,-- 主键自增g_namevarchar(50),info jsonb);-- JSON数据存储查询INSERTINTOgoods(g_name,info)VALUES('手机','{"price":1999,"品牌":"华为"}');SELECTinfo->>'price'aspricefromgoods;-- 数组类型-- 设置标签INSERTINTOUSERS(u_name)values('张三');ALTERTABLEUSERSaddCOLUMNtagstext[];-- 添加标签UPDATEUSERSsettags='{"游戏","读书"}'whereu_id=1;-- 根据标签进行查询SELECT*FROMUSERSwhere'游戏'=any(tags);

– 数据批量导入

COPY USERSfrom'数据地址'DELIMITER',';-- 以分号隔开

defaultcurrent_timestamp--->插入数据时自动填当前系统时间references表名(主键)--->绑定另一张表的主键(外键约束)bigserial--->bigint + 自增序列 +not nulltimestamptz--->pgsql的时间类型numeric(10,2)--->总一共10 位数字,小数点后保留 2 位，无浮点精度误差check--->限制字段范围

示例：

CREATETABLEcustomers(id BIGSERIALPRIMARYKEY,nameVARCHAR(100)NOTNULL,emailVARCHAR(255),created_at TIMESTAMPTZDEFAULTCURRENT_TIMESTAMP);CREATETABLEorders(order_id BIGSERIALPRIMARYKEY,-- 关联 customers 表customer_idBIGINTNOTNULLREFERENCEScustomers(id),order_date TIMESTAMPTZDEFAULTCURRENT_TIMESTAMP,amountNUMERIC(10,2)CHECK(amount>0),statusJSONBDEFAULT'{"code": 0, "desc": "pending"}'::jsonb,tagsTEXT[]);CREATETABLEinventory(product_idBIGINTPRIMARYKEY,-- 假设与 products 表 ID 对应warehouse_locationVARCHAR(50),stockINTNOTNULLDEFAULT0,last_updated TIMESTAMPTZDEFAULTCURRENT_TIMESTAMP);CREATETABLEproducts(id BIGSERIALPRIMARYKEY,nameVARCHAR(255)NOTNULL,-- 用于存储动态属性，支持 jsonb_setattributes JSONBDEFAULT'{}',-- 用于支持 unnest(tags)tagsTEXT[]DEFAULTARRAY[]::TEXT[]);

returning—>返回

-- 条件更新（返回修改后的数据）UPDATEinventorySETstock=stock-10WHEREproduct_id=123RETURNINGproduct_id,stock;

::int—>强制类型转换

SELECTorder_data->>'customer_name'AScustomerFROMordersWHERE(order_data->>'status')::int=2;

json数据更新

UPDATEproductsSETattributes=jsonb_set(attributes,'{colors}','["red","blue","green"]'::jsonb)

数组展开为行

SELECTid,unnest(tags)AStagFROMproductsWHEREid=789;

函数

age(::timestamptz)-- 计算年龄

锁

表锁：开启事务，在语句前加LOCK

行锁：

select*fromproductswhereid=100forupdate;updateproductssetstock=stock-1whereid=100;

建议锁

备份与恢复

pg_dump：单库备份（最常用）

pg_dump-h地址-p端口-U用户名-d库名>备份文件.sqlpg_dump-h127.0.0.1-p5432-Upostgres-dtestdb>testdb_20260521.sql

备份成二进制压缩包 pg_dump-U postgres-d testdb-F c-f testdb_bak.dump

pg_dumpall：全实例所有库+用户权限备份

pg_restore：恢复pg_dump备份文件

createdb-U postgres new_db psql-U postgres-d new_db-f testdb_20260521.sql

pg_restore-U postgres-d new_db testdb_bak.dump# 清空原有数据再恢复pg_restore-c-U postgres-d new_db testdb_bak.dump

系统自带，安装PG即自带，无需额外装

只恢复单张表pg_restore -d new_db -t user_info testdb_bak.dump

全实例批量恢复psql -U postgres -f all_db_bak.sql

sql优化

执行过程：

执行sql，首先与远程数据库建立连接，用户名密码没问题就会来到mysql的服务层，先查缓存（key（sql语句），value（数据）的形式存储）再mysql8完全弃用，进入解析器解析sql语句语法是否正确，然后进入预处理器检查表，字段等是否存在，再进入优化器优化sql（优化为最左前缀法），最后操作存储引擎，返回结果。

复合索引

CREATEINDEXidx_orders_customer_dateONorders(customer_id,order_dateDESC);

条件索引

CREATEINDEXidx_users_active_emailONusers(email)WHEREis_active=TRUE;

表达式索引

CREATEINDEXidx_products_lower_nameONproducts(LOWER(name));

查询优化

-- 使用覆盖索引SELECTid,nameFROMproductsWHEREcategory='Electronics'ANDprice>500;-- 限制结果集大小（替代OFFSET）-- 方案1：使用游标分页BEGIN;DECLAREorder_cursorCURSORFORSELECT*FROMlarge_tableORDERBYid;FETCH100FROMorder_cursor;COMMIT;-- 方案2：键集分页（Keyset Pagination）SELECT*FROMordersWHEREid>1000ORDERBYidLIMIT100;

地理空间查询

GEOGRAPHY：球面计算→ 全球距离、面积（准）

GEOMETRY：平面计算→ 本地地图、投影坐标

point(经度纬度)

4326:全球标准坐标系

ST_GeomFromText（）：将文本坐标转换为几何坐标

ST_Distance（a,b）：计算距离返回米

ST_DWithin（a,b,半径）：范围筛选

在虚拟机安装拓展

sudo yum install -y postgis32_14

-- 创建扩展CREATEEXTENSION postgis;-- 创建包含地理字段的表CREATETABLEstores(idSERIALPRIMARYKEY,nameVARCHAR(100),location GEOGRAPHY(Point,4326)-- WGS84坐标系);-- 插入地理数据INSERTINTOstores(name,location)VALUES('Central Store',ST_GeomFromText('POINT(-73.935242 40.730610)',4326)::geography),('Downtown Branch',ST_GeomFromText('POINT(-74.0060 40.7128)',4326)::geography);-- 查询5公里范围内的商店SELECTname,-- 计算距离并转公里ST_Distance(location,central_point)/1000ASdistance_kmFROMstores,-- 定义中心点(SELECTST_GeomFromText('POINT(-73.935242 40.730610)',4326)::geographyAScentral_point)ASref-- 只保留5公里内的数据WHEREST_DWithin(location,central_point,5000)-- 排序ORDERBYdistance_km;

语句前模糊索引优化

反向索引：将索引数据存储一份反向的（xy–>>yx）

限制范围：限制为当天的数据，当月数据等

深分页优化

原因:从第0条数据开始查，一直查到你想要的数据。

优化：字段索引覆盖，通过id来限定范围（where id>10000），通过id分库分表等。限制可查询范围。

慢查询索引优化

8大原则：全值匹配，最左前缀法则，索引不做计算，尽量避免范围（and或or），

like的%放在最右边，尽量不要写*减少回表（二次查询），不等空值or会导致索引失效，字符串类型加单引号（不加产生隐式转化计算）。

)
– 排序
ORDER BY distance_km;

#### 语句前模糊索引优化 反向索引：将索引数据存储一份反向的（xy-->>yx） 限制范围：限制为当天的数据，当月数据等 #### 深分页优化 原因:从第0条数据开始查，一直查到你想要的数据。 优化：字段索引覆盖，通过id来限定范围（where id>10000），通过id分库分表等。限制可查询范围。 #### 慢查询索引优化 8大原则：全值匹配，最左前缀法则，索引不做计算，尽量避免范围（and或or）， like的%放在最右边，尽量不要写*减少回表（二次查询），不等空值or会导致索引失效，字符串类型加单引号（不加产生隐式转化计算）。

查看全文

http://www.gsyq.cn/news/1440178.html