pgsql分层数据库模式表模式authpublic默认storage\l -- 显示所有数据库\dn -- 显示所有模式\d -- 表信息显示 create schemaifnot exists storage;-- 创建模式 drop schemaifexists storage cascade;-- 级联删除表继承CREATETABLEcontent(titleTEXT,authorTEXT);-- 继承基础字段添加字段createtablevideo(durationINT)inherits(content);常见用法-- 创建用户并且授权createuserrootwithpasssword123456;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-priceaspricefromgoods;-- 数组类型-- 设置标签INSERTINTOUSERS(u_name)values(张三);ALTERTABLEUSERSaddCOLUMNtagstext[];-- 添加标签UPDATEUSERSsettags{游戏,读书}whereu_id1;-- 根据标签进行查询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(amount0),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—返回-- 条件更新返回修改后的数据UPDATEinventorySETstockstock-10WHEREproduct_id123RETURNINGproduct_id,stock;::int—强制类型转换SELECTorder_data-customer_nameAScustomerFROMordersWHERE(order_data-status)::int2;json数据更新UPDATEproductsSETattributesjsonb_set(attributes,{colors},[red,blue,green]::jsonb)数组展开为行SELECTid,unnest(tags)AStagFROMproductsWHEREid789;函数age(::timestamptz)-- 计算年龄锁表锁开启事务在语句前加LOCK行锁select*fromproductswhereid100forupdate;updateproductssetstockstock-1whereid100;建议锁备份与恢复pg_dump单库备份最常用pg_dump-h地址-p端口-U用户名-d库名备份文件.sqlpg_dump-h127.0.0.1-p5432-Upostgres-dtestdbtestdb_20260521.sql备份成二进制压缩包 pg_dump-U postgres-d testdb-F c-f testdb_bak.dumppg_dumpall全实例所有库用户权限备份pg_restore恢复pg_dump备份文件createdb-U postgres new_db psql-U postgres-d new_db-f testdb_20260521.sqlpg_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.sqlsql优化执行过程 执行sql首先与远程数据库建立连接用户名密码没问题就会来到mysql的服务层先查缓存keysql语句value数据的形式存储再mysql8完全弃用进入解析器解析sql语句语法是否正确然后进入预处理器检查表字段等是否存在再进入优化器优化sql优化为最左前缀法最后操作存储引擎返回结果。复合索引CREATEINDEXidx_orders_customer_dateONorders(customer_id,order_dateDESC);条件索引CREATEINDEXidx_users_active_emailONusers(email)WHEREis_activeTRUE;表达式索引CREATEINDEXidx_products_lower_nameONproducts(LOWER(name));查询优化-- 使用覆盖索引SELECTid,nameFROMproductsWHEREcategoryElectronicsANDprice500;-- 限制结果集大小替代OFFSET-- 方案1使用游标分页BEGIN;DECLAREorder_cursorCURSORFORSELECT*FROMlarge_tableORDERBYid;FETCH100FROMorder_cursor;COMMIT;-- 方案2键集分页Keyset PaginationSELECT*FROMordersWHEREid1000ORDERBYidLIMIT100;地理空间查询GEOGRAPHY球面计算→ 全球距离、面积准GEOMETRY平面计算→ 本地地图、投影坐标point(经度 纬度)4326:全球标准坐标系ST_GeomFromText将文本坐标转换为几何坐标ST_Distancea,b计算距离返回米ST_DWithina,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 id10000通过id分库分表等。限制可查询范围。慢查询索引优化8大原则全值匹配最左前缀法则索引不做计算尽量避免范围and或orlike的%放在最右边尽量不要写*减少回表二次查询不等空值or会导致索引失效字符串类型加单引号不加产生隐式转化计算。)– 排序ORDER BY distance_km;#### 语句前模糊索引优化 反向索引将索引数据存储一份反向的xy--yx 限制范围限制为当天的数据当月数据等 #### 深分页优化 原因:从第0条数据开始查一直查到你想要的数据。 优化字段索引覆盖通过id来限定范围where id10000通过id分库分表等。限制可查询范围。 #### 慢查询索引优化 8大原则全值匹配最左前缀法则索引不做计算尽量避免范围and或or like的%放在最右边尽量不要写*减少回表二次查询不等空值or会导致索引失效字符串类型加单引号不加产生隐式转化计算。
pgsql语法
pgsql分层数据库模式表模式authpublic默认storage\l -- 显示所有数据库\dn -- 显示所有模式\d -- 表信息显示 create schemaifnot exists storage;-- 创建模式 drop schemaifexists storage cascade;-- 级联删除表继承CREATETABLEcontent(titleTEXT,authorTEXT);-- 继承基础字段添加字段createtablevideo(durationINT)inherits(content);常见用法-- 创建用户并且授权createuserrootwithpasssword123456;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-priceaspricefromgoods;-- 数组类型-- 设置标签INSERTINTOUSERS(u_name)values(张三);ALTERTABLEUSERSaddCOLUMNtagstext[];-- 添加标签UPDATEUSERSsettags{游戏,读书}whereu_id1;-- 根据标签进行查询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(amount0),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—返回-- 条件更新返回修改后的数据UPDATEinventorySETstockstock-10WHEREproduct_id123RETURNINGproduct_id,stock;::int—强制类型转换SELECTorder_data-customer_nameAScustomerFROMordersWHERE(order_data-status)::int2;json数据更新UPDATEproductsSETattributesjsonb_set(attributes,{colors},[red,blue,green]::jsonb)数组展开为行SELECTid,unnest(tags)AStagFROMproductsWHEREid789;函数age(::timestamptz)-- 计算年龄锁表锁开启事务在语句前加LOCK行锁select*fromproductswhereid100forupdate;updateproductssetstockstock-1whereid100;建议锁备份与恢复pg_dump单库备份最常用pg_dump-h地址-p端口-U用户名-d库名备份文件.sqlpg_dump-h127.0.0.1-p5432-Upostgres-dtestdbtestdb_20260521.sql备份成二进制压缩包 pg_dump-U postgres-d testdb-F c-f testdb_bak.dumppg_dumpall全实例所有库用户权限备份pg_restore恢复pg_dump备份文件createdb-U postgres new_db psql-U postgres-d new_db-f testdb_20260521.sqlpg_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.sqlsql优化执行过程 执行sql首先与远程数据库建立连接用户名密码没问题就会来到mysql的服务层先查缓存keysql语句value数据的形式存储再mysql8完全弃用进入解析器解析sql语句语法是否正确然后进入预处理器检查表字段等是否存在再进入优化器优化sql优化为最左前缀法最后操作存储引擎返回结果。复合索引CREATEINDEXidx_orders_customer_dateONorders(customer_id,order_dateDESC);条件索引CREATEINDEXidx_users_active_emailONusers(email)WHEREis_activeTRUE;表达式索引CREATEINDEXidx_products_lower_nameONproducts(LOWER(name));查询优化-- 使用覆盖索引SELECTid,nameFROMproductsWHEREcategoryElectronicsANDprice500;-- 限制结果集大小替代OFFSET-- 方案1使用游标分页BEGIN;DECLAREorder_cursorCURSORFORSELECT*FROMlarge_tableORDERBYid;FETCH100FROMorder_cursor;COMMIT;-- 方案2键集分页Keyset PaginationSELECT*FROMordersWHEREid1000ORDERBYidLIMIT100;地理空间查询GEOGRAPHY球面计算→ 全球距离、面积准GEOMETRY平面计算→ 本地地图、投影坐标point(经度 纬度)4326:全球标准坐标系ST_GeomFromText将文本坐标转换为几何坐标ST_Distancea,b计算距离返回米ST_DWithina,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 id10000通过id分库分表等。限制可查询范围。慢查询索引优化8大原则全值匹配最左前缀法则索引不做计算尽量避免范围and或orlike的%放在最右边尽量不要写*减少回表二次查询不等空值or会导致索引失效字符串类型加单引号不加产生隐式转化计算。)– 排序ORDER BY distance_km;#### 语句前模糊索引优化 反向索引将索引数据存储一份反向的xy--yx 限制范围限制为当天的数据当月数据等 #### 深分页优化 原因:从第0条数据开始查一直查到你想要的数据。 优化字段索引覆盖通过id来限定范围where id10000通过id分库分表等。限制可查询范围。 #### 慢查询索引优化 8大原则全值匹配最左前缀法则索引不做计算尽量避免范围and或or like的%放在最右边尽量不要写*减少回表二次查询不等空值or会导致索引失效字符串类型加单引号不加产生隐式转化计算。
