PostgreSql简介

1. 数据库的概念

        数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。

2. 数据库分类

通过数据的保存格式进行分类，现阶段流行的数据库主要分为以下两种：

• 关系型数据库

  关系型数据库采用表格的存储方式，数据以行和列的方式进行存储。存储的格式可以直观地反映实体间的关系。使用SQL(Structured Query Language,结构化查询语言) 对数据进行操作。

  常见的有Oracle、MySQL、微软的SQL Server、PostgreSQL、IBM的DB2等。

• 非关系型数据库

  伴随着互联网技术快速发展，传统关系型数据库在应对大数据量，比如大规模和高并发动态网页时，已经有些力不从心，曝露了许多难以克服的难题。因此出现了针对大规模数据量场景，以性能卓越和应用便捷为目的的的数据库产品——NOSQL数据库。

  非关系型数据库主要是根据“非关系实体模型”的数据库，也称之为NoSQL数据库，NOSQL的原意是“Not only SQL”，NoSQL的出现并不是要完全否认关系型数据库，只是做为传统关系型数据库的一个合理补充。NoSQL数据库在特殊的情景下能够充分发挥出高效率和卓越性能。

  非关系型数据库分类：

  • 键值型数据库：Redis

  • 列储存数据库：Hbase

  • 文档型数据库：MongoDB

  • 图型数据库: Neo4j

3. PostgreSQL

3.1. 简介

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

3.2. 特性

• 复杂查询

• 外键

• 触发器

• 可更新视图

• 事务完整性

• 多版本并发控制

• 数据类型：

• 函数：

• 操作符：

• 聚集函数：

• 索引方法

• 过程语言

3.3. PostgreSQL的特征

• 多版本并发控制：PostgreSQL使用多版本并发控制（MVCC，Multiversion concurrency control）系统进行并发控制，该系统向每个用户提供了一个数据库的"快照"，用户在事务内所作的每个修改，对于其他的用户都不可见，直到该事务成功提交。

• 数据类型：包括文本、任意精度的数值数组、JSON 数据、枚举类型、XML 数据等。

• 全文检索：通过 Tsearch2 或 OpenFTS。

• NoSQL：JSON，JSONB，XML，HStore 原生支持，甚至 NoSQL 数据库的外部数据包装器。

• 数据仓库：能平滑迁移至同属 PostgreSQL 生态的 GreenPlum，DeepGreen等，使用 FDW（Foreign data wrappers） 进行 ETL（Extract-Transform-Load）。

• 函数：通过函数，可以在数据库服务器端执行指令程序。

• 索引：用户可以自定义索引方法，或使用内置的 B 树，哈希表与 GiST（Generalized Search Tree） 索引。

• 触发器：触发器是由SQL语句查询所触发的事件。如：一个INSERT语句可能触发一个检查数据完整性的触发器。触发器通常由INSERT或UPDATE语句触发。

• 规则：规则（RULE）允许一个查询能被重写，通常用来实现对视图（VIEW）的操作，如插入（INSERT）、更新（UPDATE）、删除（DELETE）。

• 继承：PostgreSQL实现了表继承，一个表可以从0个或者多个其他表继承，而对一个表的查询则可以引用一个表的所有行或者该表的所有行加上它所有的后代表。

3.4. 约定：

下面的约定被用于命令的大纲：方括弧（[和]）表示可选的部分（在 Tcl 命令里，使用的是问号 （?），就像通常的 Tcl 一样）。 花括弧（{和}）和竖线（|）表示你必须选取一个候选。 点（...）表示它前面的元素可以被重复。

PostgreSQL使用一种客户端/服务器的模型

服务器进程（postgre）：管理数据库文件、接受来自客户端应用与数据库的联接并且代表客户端在数据库上执行操作

客户端应用：可以是一个面向文本的工具， 也可以是一个图形界面的应用，或者是一个通过访问数据库来显示网页的网页服务器，或者是一个特制的数据库管理工具

高并发：

为每个连接启动一个新的进程

主服务器进程总是在运行并等待着客户端联接

而客户端和相关联的服务器进程则是起起停停

// 创建数据库
createdb mydb
// 删除数据库（物理的删除所有相关文件）
dropdb mydb
// 为mydb数据库启动psql   psql：运行PostgreSQL的交互式终端程序，允许交互地输入、编辑和执行SQL命令
psql mydb
// 查询pgsql的安装版本
SELECT version();
// 查询系统当前时间
SELECT current_date;
// 内部命令：获取sql命令帮助语法
\h
// 内部命令：退出psql
\q
// 获取更多内部命令的信息
\?
​
-- SQL通常使用PostgreSQL的交互式终端psql输入，但是其他具有相似功能的程序也可以被使用。
    
// 创建数据库表--与主流数据库表的创建语法上并没有区别（大小写不敏感），real是一种用于存储单精度浮点数的类型
    CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- 最低温度
    temp_hi         int,           -- 最高温度
    prcp            real,          -- 湿度
    date            date
    );
// point是pgsql特有的数据类型
CREATE TABLE cities (
    name            varchar(80),
    location        point
);
// 删除表
DROP TABLE tablename;
// 表中添加行
INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
INSERT INTO weather (date, city, temp_hi, temp_lo) VALUES ('1994-11-29', 'Hayward', 54, 37);
COPY weather FROM '/home/user/weather.txt';
// 查询语法不变
// 外键的使用
CREATE TABLE cities (
        city     varchar(80) primary key,
        location point
);
​
CREATE TABLE weather (
        city      varchar(80) references cities(city),
        temp_lo   int,
        temp_hi   int,
        prcp      real,
        date      date
);
​
// 开启一个事务--开启一个事务需要将SQL命令用BEGIN和COMMIT命令包围起来
BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
-- etc etc
COMMIT;
​
//也可以利用保存点来以更细的粒度来控制一个事务中的语句。保存点允许我们有选择性地放弃事务的一部分而提交剩下的部分。在使用SAVEPOINT定义一个保存点后，我们可以在必要时利用ROLLBACK TO回滚到该保存点。该事务中位于保存点和回滚点之间的数据库修改都会被放弃，但是早于该保存点的修改则会被保存。
​
// savepoint的使用
BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Bob';
-- oops ... forget that and use Wally's account
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Wally';
COMMIT;
​
// 窗口函数的使用
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
-- 最开始的三个输出列直接来自于表empsalary，并且表中每一行都有一个输出行。第四列表示对与当前行具有相同depname值的所有表行取得平均值（这实际和非窗口avg聚集函数是相同的函数，但是OVER子句使得它被当做一个窗口函数处理并在一个合适的窗口帧上计算。）
​
SELECT depname, empno, salary,
       rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;
       --这里有一个与窗口函数相关的重要概念：对于每一行，在它的分区中的行集被称为它的窗口帧。 一些窗口函数只作用在窗口帧中的行上，而不是整个分区。默认情况下，如果使用ORDER BY，则帧包括从分区开始到当前行的所有行，以及后续任何与当前行在ORDER BY子句上相等的行。如果ORDER BY被忽略，则默认帧包含整个分区中所有的行。
       --sql的执行顺寻  group by、having、where》聚集函数》窗口函数
       SELECT depname, empno, salary, enroll_date
FROM
  (SELECT depname, empno, salary, enroll_date,
          rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
     FROM empsalary
  ) AS ss
WHERE pos < 3;
​
SELECT sum(salary) OVER w, avg(salary) OVER w
  FROM empsalary
  WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
  
-- 继承的使用
CREATE TABLE cities (
  name       text,
  population real,
  elevation  int     -- (in ft)
);
​
CREATE TABLE capitals (
  state      char(2) UNIQUE NOT NULL
) INHERITS (cities);
-- 列name的类型是text，一种用于变长字符串的本地PostgreSQL类型
-- SELECT、UPDATE 和DELETE — 都支持这个ONLY记号
-- 尽管继承很有用，但是它还未与唯一约束或外键集成，这也限制了它的可用性。
SELECT name, elevation
  FROM cities
  WHERE elevation > 500;
  SELECT name, elevation
    FROM ONLY cities
    WHERE elevation > 500;

PostgreSQL支持标准的SQL类型int、smallint、real、double precision、char(*N*)、varchar(*N*)、date、time、timestamp和interval，还支持其他的通用功能的类型和丰富的几何类型。

多表查询 多个表名并列

inner join

left outer join

right outer join

支持聚集函数的使用：count（计数）、sum（和）、avg（均值）、max（最大值）和min（最小值）的函数

继承：继承是面向对象数据库中的概念

3.5.sql的词法结构

• 系统中一个标识符的长度不能超过 NAMEDATALEN-1 字节，在命令中可以写超过此长度的标识符，但是它们会被截断。默认情况下，NAMEDATALEN 的值为64，因此标识符的长度上限为63字节。如果这个限制有问题，可以在src/include/pg_config_manual.h中修改 NAMEDATALEN 常量。

• 关键词和不被引号修饰的标识符是大小写不敏感的

• 受限标识符或被引号修饰的标识符。它是由双引号（"）包围的一个任意字符序列。一个受限标识符总是一个标识符而不会是一个关键字。因此"select"可以用于引用一个名为“select”的列或者表，而一个没有引号修饰的select则会被当作一个关键词，从而在本应使用表或列名的地方引起解析错误

• 引用标识符也使其区分大小写，而未引用的名称总是折叠成小写。例如，标识符FOO、foo和"foo"在PostgreSQL中被认为是相同的，但是"Foo" 和 "FOO"与这三个不同，并且彼此不同。(在PostgreSQL中，将不带引号的名称折叠为小写与SQL标准不兼容，SQL标准规定不带引号的名称应折叠为大写。因此，根据标准，foo应等同于"FOO"而不是"foo"。如果您想编写可移植应用程序，建议您始终引用某个特定的名称，或者永远不要引用它。）

• 一种受限标识符的变体允许包括转义的用代码点标识的Unicode字符。这种变体以U&（大写或小写U跟上一个花号）开始，后面紧跟双引号修饰的名称，两者之间没有任何空白，如U&"foo"（注意这里与操作符&似乎有一些混淆，但是在&操作符周围使用空白避免了这个问题） 。在引号内，Unicode字符可以以转义的形式指定：反斜线接上4位16进制代码点号码或者反斜线和加号接上6位16进制代码点号码。例如，标识符"data"可以写成：

  U&"d\0061t\+000061"
  U&"\0441\043B\043E\043D"
  U&"d!0061t!+000061" UESCAPE '!'
  --转义字符可以是除了16进制位、加号、单引号、双引号、空白字符之外的任意单个字符。请注意，转义字符在 UESCAPE 之后用单引号而不是双引号书写。
  ​
  -- 为了在标识符中包括转义字符本身，将其写两次即可。
  ​
  --4位或6位转义形式都可以被用来定义UTF-16代理对来组成代码点大于U+FFFF的字符，尽管6位形式的存在使得这种做法变得不必要（代理对并不被直接存储，而是绑定成一个单独的代码点）。
  ​
  --如果服务器编码不是UTF-8，则由其中一个转义序列标识的Unicode代码点转换为实际的服务器编码；如果不可能，则报告错误。

• 在SQL中，一个字符串常量是一个由单引号（'）包围的任意字符序列，例如'This is a string'。为了在一个字符串中包括一个单引号，可以写两个相连的单引号，例如'Dianne''s horse'。注意这和一个双引号（"）*不*同。

• PostgreSQL也接受“转义”字符串常量，这也是SQL标准的一个扩展。一个转义字符串常量可以通过在开单引号前面写一个字母E（大写或小写形式）来指定，例如E'foo'（当一个转义字符串常量跨行时，只在第一个开引号之前写E）。在一个转义字符串内部，一个反斜线字符（\）会开始一个 C 风格的反斜线转义序列，在其中反斜线和后续字符的组合表示一个特殊的字节值.

• 如果配置参数standard_conforming_strings为off，那么PostgreSQL对常规字符串常量和转义字符串常量中的反斜线转义都识别。不过，从PostgreSQL 9.1 开始，该参数的默认值为on，意味着只在转义字符串常量中识别反斜线转义。这种行为更兼容标准，但是可能打断依赖于历史行为（反斜线转义总是会被识别）的应用。作为一种变通，你可以设置该参数为off，但是最好迁移到符合新的行为。如果你需要使用一个反斜线转义来表示一个特殊字符，为该字符串常量写上一个E。

• 在standard_conforming_strings之外，配置参数escape_string_warning和backslash_quote也决定了如何对待字符串常量中的反斜线。