PGSQL学习

一、理论知识

1.数据库

1.1 数据库概述

1.1.1 数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。数据库是以一定方式储存在一起、能与多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合，可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据进行新增、查询、更新、删除等操作。
1.1.2 类型

1.2 数据库类型

1.2.1 关系数据库:

1. 关系型数据库，存储的格式可以直观地反映实体间的关系。关系型数据库和常见的表格比较相似，关系型数据库中表与表之间是有很多复杂的关联关系的。
   常见的关系型数据库有Oracle、DB2、Microsoft SQL Server、Microsoft Access、MySQL、PostgreSQL等。在轻量或者小型的应用中，使用不同的关系型数据库对系统的性能影响不大，但是在构建大型应用时，则需要根据应用的业务需求和性能需求，选择合适的关系型数据库。

1.2.2 非关系型数据库（NoSQL）
NoSQL数据库适合追求速度和可扩展性、业务多变的应用场景。 对于非结构化数据的处理更合适，如文章、评论，这些数据如全文搜索、机器学习通常只用于模糊处理，并不需要像结构化数据一样，进行精确查询，而且这类数据的数据规模往往是海量的，数据规模的增长往往也是不可能预期的，而NoSQL数据库的扩展能力几乎也是无限的，所以NoSQL数据库可以很好的满足这一类数据的存储。NoSQL数据库利用key-value可以大量的获取大量的非结构化数据，并且数据的获取效率很高，但用它查询结构化数据效果就比较差。

目前NoSQL数据库仍然没有一个统一的标准，它现在有四种大的分类：

1. 键值对存储（key-value）：代表软件Redis，它的优点能够进行数据的快速查询，而缺点是需要存储数据之间的关系。

2. 列存储：代表软件Hbase，它的优点是对数据能快速查询，数据存储的扩展性强。而缺点是数据库的功能有局限性。

3. 文档数据库存储：代表软件MongoDB，它的优点是对数据结构要求不特别的严格。而缺点是查询性的性能不好，同时缺少一种统一查询语言。

4. 图形数据库存储：代表软件InfoGrid，它的优点可以方便的利用图结构相

2.PGSQL

概念

PostgreSQL是一种关系型数据库管理系统 （RDBMS）。这意味着它是一种用于管理存储在关系中的数据的系统。关系实际上是表的数学术语。 今天，把数据存储在表里的概念已经快成了固有的常识了， 但是还有其它的一些方法用于组织数据库。在类 Unix 操作系统上的文件和目录就形成了一种层次数据库的例子。 更现代的发展是面向对象数据库。

每个表都是一个命名的行集合。一个给定表的每一行由同一组的命名列组成，而且每一列都有一个特定的数据类型。虽然列在每行里的顺序是固定的， 但一定要记住 SQL 并不对行在表中的顺序做任何保证（但你可以为了显示的目的对它们进行显式地排序）。

表被分组成数据库，一个由单个PostgreSQL服务器实例管理的数据库集合组成一个数据库集簇。

2.1简单语法

SQL一般分为DQL，DML，DDL：

DQL：数据查询语句，SELECT

DML：数据操作语言，INSERT，UPDATE，DELETE

DDL：数据定义语言，主要用于创建 删除，以及修改表 索引等数据库对象语言
这些基础操作基本和以前学的MySQL差不多，详情可以参考官方文档：PostgreSQL 11.2 手册

3.高级特性

SQL中一些更高级的特性，这些特性有助于简化管理和防止数据丢失或损坏。

3.2. 视图，外键

3.3 事务

事务是所有数据库系统的基础概念。事务最重要的一点是它将多个步骤捆绑成了一个单一的、要么全完成要么全不完成的操作。步骤之间的中间状态对于其他并发事务是不可见的，并且如果有某些错误发生导致事务不能完成，则其中任何一个步骤都不会对数据库造成影响。

事务特性（4种）:
原子性 （atomicity）:强调事务的不可分割.
一致性 （consistency）:事务的执行的前后数据的完整性保持一致.
隔离性 （isolation）:一个事务执行的过程中,不应该受到其他事务的干扰
持久性（durability） :事务一旦结束,数据就持久到数

在PostgreSQL中，开启一个事务需要将SQL命令用BEGIN和COMMIT命令包围起来。例：

BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
-- etc etc
COMMIT;

PostgreSQL实际上将每一个SQL语句都作为一个事务来执行。如果我们没有发出BEGIN命令，则每个独立的语句都会被加上一个隐式的BEGIN以及（如果成功）COMMIT来包围它。一组被BEGIN和COMMIT包围的语句也被称为一个事务块。
也可以利用保存点来以更细的粒度来控制一个事务中的语句。保存点允许我们有选择性地放弃事务的一部分而提交剩下的部分。在使用SAVEPOINT定义一个保存点后，我们可以在必要时利用ROLLBACK TO回滚到该保存点。该事务中位于保存点和回滚点之间的数据库修改都会被放弃，但是早于该保存点的修改则会被保存。

3.5. 窗口函数，继承

-- 继承
CREATE TABLE cities (
  name       text,
  population real,
  altitude   int     -- (in ft)
);

CREATE TABLE capitals (
  state      char(2)
) INHERITS (cities);

SELECT name, altitude
    FROM ONLY cities
    WHERE altitude > 500;

中cities之前的ONLY用于指示查询只在cities表上进行而不会涉及到继承层次中位于cities之下的其他表

4.sql语法

http://www.postgres.cn/docs/11/sql-syntax.html

目录

4.1. 词法结构
4.1.1. 标识符和关键词
4.1.2. 常量
4.1.3. 操作符
4.1.4. 特殊字符
4.1.5. 注释
4.1.6. 操作符优先级
4.2. 值表达式
4.2.1. 列引用
4.2.2. 位置参数
4.2.3. 下标
4.2.4. 域选择
4.2.5. 操作符调用
4.2.6. 函数调用
4.2.7. 聚集表达式
4.2.8. 窗口函数调用
4.2.9. 类型转换
4.2.10. 排序规则表达式
4.2.11. 标量子查询
4.2.12. 数组构造器
4.2.13. 行构造器
4.2.14. 表达式计算规则
4.3. 调用函数
4.3.1. 使用位置记号
4.3.2. 使用命名记号
4.3.3. 使用混合记号

一些小点

1.关键词和不被引号修饰的标识符是大小写不敏感的
2.一个常见的习惯是将关键词写成大写，而名称写成小写，例如：UPDATE my_table SET a = 5;
3.这里还有第二种形式的标识符：受限标识符或被引号修饰的标识符。它是由双引号（"）包围的一个任意字符序列。一个受限标识符总是一个标识符而不会是一个关键字。受限标识符可以包含任何字符，除了代码为0的字符（如果要包含一个双引号，则写两个双引号）。等等

5.数据定义

更多细节参考：
http://www.postgres.cn/docs/11/ddl.html
关系型数据库中的一个表非常像纸上的一张表：它由行和列组成。列的数量和顺序是固定的，并且每一列拥有一个名字。行的数目是变化的，它反映了在一个给定时刻表中存储的数据量。SQL并不保证表中行的顺序。当一个表被读取时，表中的行将以非特定顺序出现，除非明确地指定需要排序。

5.6. 权限

无论何时创建数据库对象，都会为其分配一个所有者，所有者通常是执行 create 语句的人。对于大多数类型的对象，初始状态是只有所有者(或超级用户)才能修改或删除对象。要允许其他角色或用户使用它，必须为该用户设置权限
一旦一个对象被创建，它会被分配一个所有者。所有者通常是执行创建语句的角色。对于大部分类型的对象，初始状态下只有所有者（或者超级用户）能够对该对象做任何事情。为了允许其他角色使用它，必须分配权限。

5.8. 模式

PostgreSQL 模式（SCHEMA）可以看着是一个表的集合。

一个模式可以包含视图、索引、据类型、函数和操作符等。

相同的对象名称可以被用于不同的模式中而不会出现冲突，例如 schema1 和 myschema 都可以包含名为 mytable 的表。

使用模式的优势：

允许多个用户使用一个数据库并且不会互相干扰。

将数据库对象组织成逻辑组以便更容易管理。

第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突。

模式类似于操作系统层的目录，但是模式不能嵌套。

参考文档：
1.https://www.runoob.com/postgresql/postgresql-union.html
2.http://www.postgres.cn/v2/document

安装可能遇到的问题

https://blog.youkuaiyun.com/d960704119/article/details/106760469