差别

这里会显示出您选择的修订版和当前版本之间的差别。

--- itwiki:postgresql [2021/09/15 11:21] – 新增部分内容 ovwx@live.io
+++ itwiki:postgresql [2021/09/20 11:18] (当前版本) – [一、POSTGRESQL-SERVER 数据库安装与初始化] ovwx@live.io
@@ 行 1: / 行 1: @@
-===== 数据库安装 =====
+====== PostgreSql 快速实践 ======
+===== 一、Postgresql-Server 数据库安装与初始化 =====
 ==== CentOS 系统 ====
 从Postgresql 官方网站下载repo源
+https://www.postgresql.org/download/
 建立YUM缓存并安装数据库
@@ 行 10: / 行 13: @@
 </code>
-===== 数据库初始化 =====
+==== 数据库初始化 ====
 初始化数据库、创建数据库文件，适合Postgresql 10.0以上版本
@@ 行 17: / 行 20: @@
 </code>
-====== 一些基础管理 ======
+===== 二、一些基础管理 =====
-==== 查看所有表格记录数 ====
+==== 备份与恢复数据库====
+**全库备份**
+<code>
+pg_dump dbname > dbname.sql
+</code>
+**不备份所有者信息**
+<code>
+pg_dump --no-owner dbname > dbname.sql
+</code>
+**恢复数据库**
+<code>
+pgsql -c 'create database dbname'
+pgsql -d dbname < dbname.sql
+</code>
+**仅备份表结构信息**
+<code>
+pg_dump -s dbname -t tbl_name > tbl_name.sql
+## 从导出的表结构中创建表格，当前用户需要具有相关权限，否则应该带上-U参数
+psql -d dbname < tbl_name.sql
+</code>
+==== 表格元数据 ====
+=== 查看所有表格记录数 ===
 <code>
 select relname,reltuples from pg_class where relkind='r' order by reltuples desc;
 </code>
-==== 查看表格的字段与数据类型 ====
+=== 查询数据库记录数 ===
+以下代码查询数据库''log''每个表格记录数
+<code>
+# psql -c "select relname,reltuples from pg_class where relkind='r' order by reltuples desc limit 20;" -d log
+</code>
+==== 命令行不输入密码的方法 ====
+<code>
+## 为环境变量PGPASSWORD设置值
+PGPASSWORD='user1_pass'
+psql -U user1 -d dbname
+</code>
+=== 查看表格的字段与数据类型 ===
 . 通过SQL语句
@@ 行 35: / 行 77: @@
 ''\d c_log''
-===== 使用技巧 =====
+=== 为表格、视图增加注释 ===
+<code>
+comment on table t2 is '表格2';
+</code>
+=== 查看表格注释 ===
+<code>
+\d+
+</code>
+==== 视图管理 ====
+可以通过命令编辑函数及视图定义
+<code>
+-- 注意编辑完成后输入分号";"并回车提交，有些视图如果无法修改，则可以通过修改视图、函数名称新建一个
+-- 然后删除旧的定义
+-- vi 全局替换命令是: %s/o_str/n_str/
+\ev view_name
+\ef func_name
+</code>
+=== 物化视图 ===
+<code>
+## 物化视图
+## 物化视图是保存有实际数据的视图
+## 不会自动更新、适合更新不频繁的数据
+## 创建物化视图
+create materialized view view_name as select_stmt;
+## 创建唯一性索引，并发更新时需要此索引
+create unique index index_name on view_name(field);
+## 刷新索引
+refresh materialized view concurrently view_name;
+## 修改名称
+alter materialized view name rename to new_name;
+</code>
+==== 数据库连接管理 ====
+<code>
+-- PostgreSQL 查看剩余连接数
+select max_conn-now_conn as resi_conn from (select setting::int8 as max_conn,(select count(*) from pg_stat_activity) as now_conn from pg_settings where name = 'max_connections') t;
+</code>
+==== 权限管理 ====
+**对表或视图授权**
+<code>
+grant select on view_name to role_name;
+## 查看定义
+\d+ view_name
+</code>
+==== 权限管理实践 ====
+<code>
+-- PostgreSQL 用户和组
+create role role_stk; #这是一个角色，不能登录
+create user user_stk; #这是一个用户，可以登录
+grant select on tbl_set1 to role_stk;
+grant select on tbl_set2 to role_stk;
+grant select on tbl_set3 to role_stk; #将一系列权限授予角色
+grant role_stk to user_stk; #为这个用户添加角色，这个用户就具有角色的权限
+</code>
+==== 数据库结构组织:SCHEMA ====
+<code>
+## 模式 Schema
+## 创建模式
+create schema s_name;
+## 授权
+grant usage on s_name to ovwx;
+## 显示模式
+\dn+
+## 查询模式下的对象
+\dt s_name.* --查询表
+\dv s_name.* --查询视图
+</code>
+===== 三、使用技巧 =====
 ==== 产生随机字符串 ====
 ''select md5(random()::text);''
-==== 查询数据库记录数 ====
+==== 选择随机的N条数据 ====
-以下代码查询数据库''log''每个表格记录数
+-- 在postgres中 选择随机的几条数据出来，sql:
+select * from TABLE_NAME order by random() limit N;
+==== 日期提取 ====
 <code>
-# psql -c "select relname,reltuples from pg_class where relkind='r' order by reltuples desc limit 20;" -d log
+-- Extract 解析时间与日期数据
+select Extract(field from timestamp)::int;
+其中，field可以是:
+) min: 时间中的分钟数
+) sec: 时间中的秒数
+) hour: 时间中的小时数
+) year: 年数
+) doy: 一年中的天数
+) dow: 一周中的天数
+) day: 一月中的天数
+) ....不常用的忽略
+</code>
+==== PostgreSQL 获取月初及月末时段 ====
+<code>
+select date_trunc('month',now()); --月初
+select date_trunc('month',now()+'1 months')+'-1 days'; --月末
+select date_trunc('week',now()); --周初
+</code>
+==== 窗口函数 ====
+<code>
+-- PostgreSQL percentile_cont窗口函数
+select percentile_cont(frac) within group (order by column [desc]) from table group by ....
+说明: percentile_cont(frac) within group (order by ...) 是一种整体表达法，frac为百分位，order by 是需要处理的数据列，后面group by为分组依据
+</code>
+==== 数据合并与联结 ====
+=== 将两个记录集合并为一个（列名相同） ===
+下列命令将t1/t2的所有行组成一个新的记录集
+<code>
+select * from t1 union select * from t2;
+</code>
+=== 联接查询 ===
+** 将两个记录集按照特定条件联接为一个**
+<code>
+select t1.*,t2.* from t1 join t2 on ?cond?
+</code>
+=== 创建运算符 ===
+<code>
+## 运算符可以重载（即参数类型不相同）
+## 自定义运算符优先级最低，要让它优先时需要加括号
+create operator // (procedure=func,leftarg=float,rightarg=float);
+</code>
+==== 重要功能 ====
+=== Pg10 分区表 ===
+## 分区表可以将大表转化为若干小表，同时提升数据库性能。
+== 创建主表 ==
+<code>
+create table tb_name(
+    ...
+    cname type,
+    other col_spec
+    ...
+) partition by range(cname);
+</code>
+表示创建基于列<cname>的范围分区表，范围格式与相应列类型一致。
+== 创建分区表（从表） ==
+<code>
+create table tb_name_s_ra_1 partition of tb_name for values from (start) to (end);
+create table tb_name_s_ra_2 partition of tb_name for values from (start2) to (end2);
+create table tb_name_s_ra_3 partition of tb_name for values from (start3) to (end3);
+</code>
+注意划分范围是包括start，不包括end的。
+=== 离散型分区表 ===
+<code>
+按照列值分区，适用于列值为离散的情况。
+-- Master table
+create table tb_name(
+    ...
+    cname type,
+    other col_spec
+    ...
+) partition by list(cname);
+-- Slave table
+create table tb_name_s_va_1 partition of tb_name for values in (a,b,c);
+</code>
+=== with 子句 ===
+<code>
+用于提高复杂查询的可读性
+with tb_name as (
+  select col_spec,.... from .....
+),
+tb2 as (
+ select .... from ....
+)
+select col1,col2,col3 from tb1, tb2 where ....;
+</code>
+=== 交叉表 ===
+<code>
+# 定义交叉表查询列(b列)为: s_col
+# 定义交叉表分组列(a列): g_col
+# 定义数据列: d_col
+select g_col,
+ sum(case when s_col = 'a' then d_col else 0 end) as a,
+ sum(case when s_col = 'b' then d_col else 0 end) as b,
+ sum(case when s_col = 'c' then d_col else 0 end) as c
+from s_tbl group by g_col;
 </code>
+这会产生一个g_col值为index, column=[a,b,c]的交叉表