PostgreSQL入门简介

* 安装存储库
sudo yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-redhat-repo-42.0-11.noarch.rpm
* 安装客户端
sudo yum install postgresql	  
* 安装服务端
sudo yum install postgresql-server   
* 安装拓展包
sudo yum install postgresql-devel.x86_64 
* 安装附加模块
sudo yum install postgresql-contrib.x86_64 

# rpm -qa | grep postgresql
postgresql-libs-9.2.23-3.el7_4.x86_64
postgresql-9.2.23-3.el7_4.x86_64
postgresql-server-9.2.23-3.el7_4.x86_64

* 初始化数据库
sudo /usr/bin/postgresql-setup initdb
 
* 启动postgresql服务
sudo systemctl start postgresql
  
* 设置开机自启动
sudo systemctl enable postgresql

* 登录postgresql
su - postgres
psql -U postgres

* 修改postgres用户密码
ALTER USER postgres with encrypted password 'postgres';

listen_addresses = '*'     # what IP address(es) to listen on;

# IPv4 local connections:
host  all       all       127.0.0.1/32      trust
host  all       all       192.168.9.139/32    trust

psql -d postgres -h 192.168.9.139 -p 5432 -U postgres

-- 创建用户
CREATE USER admin WITH PASSWORD '123456';	
-- 修改密码
ALTER USER admin with encrypted password 'admin';

* 普通创建
  CREATE DATABASE pgdb;

* 创建指定用户数据库
  CREATE DATABASE pgadmindb OWNER admin;	
  GRANT ALL PRIVILEGES ON DATABASE pgadmindb TO admin;

* 普通删除
  DROP DATABASE pgdb;

* 判断数据库存在后再删除
  DROP DATABASE IF EXISTS pgdb;

* 切换数据库
  \c pgdb;

* 退出数据库
  \q

CREATE TABLE numerical (
 "a" int4,
 "b" int4
);

DROP TABLE IF EXISTS numerical;

INSERT INTO numerical (SELECT i, i + 1 FROM generate_series(1, 10000) AS i);

truncate table numerical;

* 统计查询
SELECT COUNT(1) FROM numerical;

* 累计查询
SELECT SUM(a) FROM numerical;

* 平均查询
SELECT SUM(a)/COUNT(1) FROM numerical;