PostgreSQL-研究学习-介绍与安装

PostgreSQL-预研

是个很厉害的数据库的样子 ψ(*｀ー´)ψ

官方文档：http://www.postgres.cn/docs/12/

---

总的结论和备注

PgSQL 支持对JSON的支持很强大，以及提供了很多数学几何相关的数据类型【例：点，线条，几何等】

基本语法其实和MySQL差不多，具体的就要在使用的时候去适应和学习了！！！

概述

PostgreSQL数据库是目前功能最强大的开源数据库，支持丰富的数据类型（如JSON和JSONB类型、数组类型）和自定义类型。而且它提供了丰富的接口，可以很容易地扩展它的功能，如可以在GiST框架下实现自己的索引类型等，它还支持使用C语言写自定义函数、触发器，也支持使用流行的语言写自定义函数，比如其中的PL/Perl提供了使用Perl语言写自定义函数的功能，当然还有PL/Python、PL/Tcl，等等。

PostgreSQL是为特性非常齐全的对象-关系型数据库，同时还支持NoSql的文档型存储。

PostgreSQL 对比MySQL

一、事务隔离之间的比较

二、持久化之间的比较

三、复制之间的比较

四、查询相关功能的比较

五、表功能之间的比较

六、视图和安全之间的比较

安装教程【CentOS7 - 14版本】

# 官方https://www.postgresql.org/download/linux/redhat/
# Install the repository RPM:
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm# Install PostgreSQL:
sudo yum install -y postgresql14-server# Optionally initialize the database and enable automatic start:
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
sudo systemctl enable postgresql-14
sudo systemctl start postgresql-14# ================================================================
### 其他操作
# =============================================
## 自定义数据存储目录【1】
# 创建目录
mkdir -p /usr/local/postgresql/data
# 授权, postgres用户与组在第2步会自动创建好
chown -R postgres:postgres /usr/local/postgresql
# =============================
# 初始化数据库【2】
su - postgres
# 使用自定义数据存储目录【选一个】
/usr/pgsql-14/bin/initdb -D /usr/local/postgresql/data/
# 使用默认数据存储目录【选一个】
/usr/pgsql-14/bin/initdb
# =============================
## 修改启动脚本【3】
# 使用root用户
vim /usr/lib/systemd/system/postgresql-14.service
......
# Location of database directory
Environment=PGDATA=/usr/local/postgresql/data/
......
# 重新加载系统服务
systemctl daemon-reload
# =============================
## 设置监听地址【4】
# 使用postgres用户
vim /usr/local/postgresql/data/postgresql.conf
......
# - Connection Settings -
listen_addresses = '*'                  # what IP address(es) to listen on;
......
# =============================
# 新增密码认证【5】
vim /usr/local/postgresql/data/pg_hba.conf
......
。。。。。
host    replication     all             ::1/128                 trust
# 添加可通过密码对所有用户和主机进行认证【就下面这一行】
host    all             all             0.0.0.0/0               trust  
# =============================# =============================
## 修改postgres超级用户密码【6】
# 使用postgres用户
psql -p 5432 -U postgres
ALTER USER postgres WITH PASSWORD '123456';
# ============================= 完成  =============================

# docker 方案【未尝试】
docker run -d -p 5432:5432 --name postgresql -v pgdata:/var/lib/postgresql/data -e  POSTGRES_PASSWORD=123321 postgres

安装pgAdmin4-web服务

官网：https://www.pgadmin.org/
官方安装教程：https://www.pgadmin.org/download/pgadmin-4-rpm/

安装好了之后的截面图：

安装教程：（如下）

rpm -ivh https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/pgadmin4-redhat-repo-2-1.noarch.rpm --force --nodeps
sudo yum install pgadmin4-web# 设置 selinux  这个需要重启
vim /etc/selinux/config
SELINUX=disable
# 设置 selinux  临时不用重启
setenforce 0# 修改端口
vim /etc/httpd/conf.d/pgadmin4.conf
<VirtualHost *:80>
ServerName pgadmin.cn
# 自带内容
</VirtualHost># 执行
/usr/pgadmin4/bin/setup-web.sh# 完成：后续按提示初始化就可以里  

# docker方案【未尝试】
docker pull dpage/pgadmin4docker run -d -p 5433:80 --name pgadmin4 -e PGADMIN_DEFAULT_EMAIL=123@123.com -e PGADMIN_DEFAULT_PASSWORD=123456 dpage/pgadmin4

一些SQL写法-Demo参考

聚集-过滤

SELECTcount(*) AS unfiltered,count(*) FILTER (WHERE i < 5) AS filtered   # 这里的过滤
FROM generate_series(1,10) AS s(i);unfiltered | filtered
------------+----------10 |        4

高级分组统计

CUBE和ROLLUP 更高级的排列

=> SELECT * FROM items_sold;brand | size | sales
-------+------+-------Foo   | L    |  10Foo   | M    |  20Bar   | M    |  15Bar   | L    |  5
(4 rows)=> SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());brand | size | sum
-------+------+-----Foo   |      |  30Bar   |      |  20| L    |  15| M    |  35|      |  50
(5 rows)

手动构建临时Table

=> SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num,letter);num | letter
-----+--------1 | one2 | two3 | three
(3 rows)

WITH单查询内辅助查询

WITH regional_sales AS (  # 有点像临时表的查询，预处理SELECT region, SUM(amount) AS total_salesFROM ordersGROUP BY region
), top_regions AS (SELECT regionFROM regional_salesWHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,product,SUM(quantity) AS product_units,SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

中文全文检索

全文索引的实现要靠 PgSQL 的 gin 索引。分词功能 PgSQL 内置了英文、西班牙文等，但中文分词需要借助开源插件 zhparser；

参考文档：https://www.cnblogs.com/zhenbianshu/p/7795247.html

---