PostgreSql 快速实践 [HsiaoTien's Doc]

本页面只读。您可以查看源文件，但不能更改它。如果您觉得这是系统错误，请联系管理员。
====== PostgreSql 快速实践 ======
===== 一、Postgresql-Server 数据库安装与初始化 =====
==== CentOS 系统 ====
从Postgresql 官方网站下载repo源

https://www.postgresql.org/download/

建立YUM缓存并安装数据库

<code>
# yum makecache
# yum install postgresql-server
</code>

==== 数据库初始化 ====
初始化数据库、创建数据库文件，适合Postgresql 10.0以上版本

<code>
# postgresql-setup --initdb
</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>

=== 查看表格的字段与数据类型 ===

1. 通过SQL语句
以下代码查看c_log表格下的字段名称及数据类型
<code>
  select column_name,data_type from infomation_schema.columns where table_schema='public' and table_name='c_log';
</code>

2. 数字列表项目通过pgsql命令行自带命令
''\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条数据 ====
-- 在postgres中 选择随机的几条数据出来，sql: 
select * from TABLE_NAME order by random() limit N;

==== 日期提取 ====
<code>
-- Extract 解析时间与日期数据
select Extract(field from timestamp)::int;
其中，field可以是:
1) min: 时间中的分钟数
2) sec: 时间中的秒数
3) hour: 时间中的小时数
4) year: 年数
5) doy: 一年中的天数
6) dow: 一周中的天数
7) day: 一月中的天数
8) ....不常用的忽略
</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]的交叉表