PostgreSQL

自学教程

随便学学

看下面这个链接学习

PostgreSQL 字符串函数参考 这个是各种函数，不只是字符串

PostgreSQL 教程 | 菜鸟教程  完整的pgsql教程文档，简单易读

PGSQL简介

关系型数据库，有客户端和服务端两种，支持定义自己的数据类型、构建自定义函数，甚至无需重新编译数据库即可使用不同的编程语言编写代码。剩下的自己百度一下

几篇pgsql和mysql的对比，仅供参考

MySQL与PostgreSQL的对比 - 知乎

http://t.csdn.cn/dKmp0

PGSQL下载

官网

Community DL Page

PGSQL安装

下一步就行 

最后一步不勾选，是个附带的应用

安装完成后去win搜索pgadmin，打开 

双击这个，弹出窗口输入密码，成功就成功哩

安装完毕后 你的默认用户名为 postgres

这个软件是PGSQL提供的一个图形化界面

PGSQL创建链接

右键 

在name输入链接名字 

点击第二个connection

host name/address    本地地址或者ip地址

port   链接端口号

maintenance database 链接哪个数据库

username   用户名

password   密码

 点击save创建

创建成功

这样删除

 

创建数据库

 代码语法

CREATE DATABASE test1
    WITH
    OWNER = postgres
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1
    IS_TEMPLATE = False;

mytest是我创建的，点击里面的schemas（模式）——>public（系统给的）——>tables（数据表 ）创建的数据表就在tables里

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

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

优势

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

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

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

 

去顶部工具栏Tools——>Query Tool ，可以创建一张查询方便我们能输入语句

表中点击这个可以看，all rows是全部 

PGSQL一些特征

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

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

触发器：触发器是由SQL语句查询所触发的事件。如：一个INSERT语句可能触发一个检查数据完整性的触发器。触发器通常由INSERT或UPDATE语句触发。 多版本并发控制：PostgreSQL使用多版本并发控制（MVCC，Multiversion concurrency control）系统进行并发控制，该系统向每个用户提供了一个数据库的"快照"，用户在事务内所作的每个修改，对于其他的用户都不可见，直到该事务成功提交。

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

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

等。

全文检索：通过 Tsearch2 或 OpenFTS，8.3版本中内嵌 Tsearch2。

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

数据仓库：能平滑迁移至同属 PostgreSQL 生态的 GreenPlum，DeepGreen，HAWK 等，使用 FDW 进行 ETL。

PGSQL数据类型

基本类型：Integer, Numeric, String, Boolean
结构类型：Date/Time, Array, Range, UUID
文档类型：JSON/JSONB, XML, Key-value(Hstore)
几何类型：Point, Line, Circle, Polygon
自定义类型：Composite, Custom Types

其他作者写的好，看这些。结合起来看

http://t.csdn.cn/ofqkn  这篇总结的比较详细

PostgreSQL 数据类型 | 菜鸟教程   这个总结的比较整洁

之前提到数据类型支持自定义，可以参考这个

PostgreSQL 用户自定义类型教程

PostgreSQL和MYSQL

推荐参考这篇文章

MySQL 和 PostgreSQL，我到底选择哪个？_postgresql和mysql_wljslmz的博客-CSDN博客

PostgreSQL 语法

简单查询

/*
	多行注释
*/
-- 查询 别名要用双引号
SELECT DISTINCT	first_name "名",last_name "姓",department_id
FROM employees;

-- 快速查询
SELECT 1+1 ;

条件过滤

-- 利用查询条件过滤数据
SELECT *
FROM employees
WHERE employee_id = 100;

模糊查询

-- 模糊查询
SELECT first_name
FROM employees
WHERE first_name LIKE 'S%';

ESCAPE '字符'     可以把字符给转译，使用和\一样只是吧\换成了#

空值判断

-- 空值判断
SELECT *
FROM employees
WHERE manager_id IS null;

NULL，空字符串和mysql一样的

复杂查询

-- 复杂查询
SELECT *
FROM employees
WHERE first_name = 'Steven' AND last_name = 'King';

排序查询

null在排序的时候是最大的值

-- 复杂查询
SELECT *
FROM employees
WHERE first_name = 'Steven' AND last_name = 'King';

限制返回查询

和mysql不一样用的是offset隔开两个参数

-- 限制返回查询
SELECT employee_id,first_name,last_name,hire_date,salary
FROM employees
ORDER BY salary 
LIMIT 10 OFFSET 4;

函数返回查询

-- 限制返回查询
SELECT employee_id,first_name,last_name,hire_date,salary
FROM employees
ORDER BY salary 
LIMIT 10;

不想写了大部分都差不多

插入

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)s
VALUES (value1, value2, value3,...valueN);

修改

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

删除

DELETE FROM table_name WHERE [condition];

WITH

在 PostgreSQL 中，WITH 子句提供了一种编写辅助语句的方法，以便在更大的查询中使用。

WITH 子句有助于将复杂的大型查询分解为更简单的表单，便于阅读。这些语句通常称为通用表表达式（Common Table Express， CTE），也可以当做一个为查询而存在的临时表。

WITH 子句是在多次执行子查询时特别有用，允许我们在查询中通过它的名称(可能是多次)引用它。

WITH 子句在使用前必须先定义。

语法：

-- with  语法
WITH
   name_for_summary_data AS (
      SELECT 'Statement')
   SELECT columns
   FROM name_for_summary_data
   WHERE conditions <=> (
      SELECT 'column'
      FROM name_for_summary_data)
	  
-- 例子
With CTE AS
(Select
 ID
, NAME
, AGE
, ADDRESS
, SALARY
FROM COMPANY )
Select * From CTE;

name_for_summary_data 是 WITH 子句的名称，name_for_summary_data 可以与现有的表名相同，并且具有优先级。

可以在 WITH 中使用数据 INSERT, UPDATE 或 DELETE 语句，允许在同一个查询中执行多个不同的操作。

PostgreSQL 高级

约束

NOT NULL 约束（非空约束）

CREATE TABLE COMPANY1(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

UNIQUE 约束（唯一约束）

CREATE TABLE COMPANY3(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL UNIQUE,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
);

PRIMARY KEY约束（主键约束）

CREATE TABLE COMPANY4(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

FOREIGN KEY 约束（外键约束）

-- 表1
CREATE TABLE COMPANY6(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);
-- 表2
CREATE TABLE DEPARTMENT1(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      references COMPANY6(ID)
);

CHECK 约束

CHECK 约束保证列中的所有值满足某一条件，即对输入一条记录要进行检查。如果条件值为 false，则记录违反了约束，且不能输入到表。

例如对下面这个表插入一条salary数据小于0的数字就会插入失败，从而报错导致数据无法正常插入

CREATE TABLE COMPANY5(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    CHECK(SALARY > 0)
);

EXCLUSION 约束

EXCLUSION 约束确保如果使用指定的运算符在指定列或表达式上比较任意两行，至少其中一个运算符比较将返回 false 或 null。

CREATE TABLE COMPANY7(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT,
   AGE            INT  ,
   ADDRESS        CHAR(50),
   SALARY         REAL,
   EXCLUDE USING gist
   (NAME WITH =,  -- 如果满足 NAME 相同，AGE 不相同则不允许插入，否则允许插入
   AGE WITH <>)   -- 其比较的结果是如果整个表达式返回 true，则不允许插入，否则允许
);

这里，USING gist 是用于构建和执行的索引一种类型。

-- 例子
INSERT INTO COMPANY7 VALUES(1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY7 VALUES(2, 'Paul', 32, 'Texas', 20000.00 );
-- 此条数据的 NAME 与第一条相同，且 AGE 与第一条也相同，故满足插入条件
INSERT INTO COMPANY7 VALUES(3, 'Allen', 42, 'California', 20000.00 );
-- 此数据与上面数据的 NAME 相同，但 AGE 不相同，故不允许插入

第三条就会报错

删除约束

表名 和 约束名

ALTER TABLE table_name DROP CONSTRAINT some_name;

链接

PostgreSQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。

在 PostgreSQL 中，JOIN 有五种连接类型：

• CROSS JOIN ：交叉连接
• INNER JOIN：内连接
• LEFT OUTER JOIN：左外连接
• RIGHT OUTER JOIN：右外连接
• FULL OUTER JOIN：全外连接

交叉链接

SELECT ... FROM table1 CROSS JOIN table2 ...

会产生笛卡尔积

内连接

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field;

通过on关键字指定内容相同的字段查询，避免笛卡尔积

左右外连接

外部连接是内部连接的扩展。SQL 标准定义了三种类型的外部连接: LEFT、RIGHT 和 FULL, PostgreSQL 支持所有这些。

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

对于左外连接，首先执行一个内连接。然后，对于表 T1 中不满足表 T2 中连接条件的每一行，其中 T2 的列中有 null 值也会添加一个连接行。因此，连接的表在 T1 中每一行至少有一行。

和mysql一样

全外连接

首先，执行内部连接。然后，对于表 T1 中不满足表 T2 中任何行连接条件的每一行，如果 T2 的列中有 null 值也会添加一个到结果中。

此外，对于 T2 中不满足与 T1 中的任何行连接条件的每一行，将会添加 T1 列中包含 null 值的到结果中。

SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...

 UNION和UNION ALL 操作符

UNION 操作符合并两个或多个 SELECT 语句的结果。

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION或者UNION ALL

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION和UNION ALL的区别在于不加ALL会去重，允许重复内容出现的时候使用UNION ALL

子查询

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
      (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])

注意事项

• 子查询必须用括号括起来。

• 子查询在 SELECT 子句中只能有一个列，除非在主查询中有多列，与子查询的所选列进行比较。

• ORDER BY 不能用在子查询中，虽然主查询可以使用 ORDER BY。可以在子查询中使用 GROUP BY，功能与 ORDER BY 相同。

• 子查询返回多于一行，只能与多值运算符一起使用，如 IN 运算符。

• BETWEEN 运算符不能与子查询一起使用，但是，BETWEEN 可在子查询内使用。

AUTO INCREMENT（自动增长）

PGSQL和MYSQL中的自动增长不太一样，在MYSQL中使用AUTO_INCREMENT关键字即可对当前字段进行自增表示，但是PGSQL中并没有这样的功能。所以就需要序列来对需要自增的字段进行操作

系统给我们提供了三个为序列来便于我们操作，他们是数据类型，所以要作为数据类型去代替原本需要自增的数据类型

伪类型	存储大小	范围
SMALLSERIAL	2字节	1 到 32,767
SERIAL	4字节	1 到 2,147,483,647
BIGSERIAL	8字节	1 到 922,337,2036,854,775,807

-- 使用伪序列
create table test(
	id serial primary key  not null,
	test_name text not null
);

除了系统提供的三个伪序列，我们还可以创建序列

create sequence用于生成一个有序的数字序列。它被称为序列、序列对象或者序列生成器。一般用于主键自增，也可以使用drop sequence来删除序列

可以参考这篇文章

https://www.cnblogs.com/guohongzhi/p/16550004.html

自建序列的三个操作函数

• nextval(序列名): 获取并返回序列的下一个值。
• currval(序列名): 返回当前会话中指定序列的当前值。
• setval()序列名setval(): 重置指定序列的当前值

函数

pgsql和其他的一样有常用函数，也有自定义函数

常用函数可以参考这个

PostgreSQL 常用函数 | 菜鸟教程

自定义函数

语法

create [or replace] function name([arguments])
returns rettype
language sql
as $$
<函数体>
$$

$$代表开始和结束的指示符，<>不用写直接写函数体就行

[return]：返回类型有void（没有返回值）或者你需要返回的数据类型

[or replace]:是可选的，它允许您修改/替换现有函数。

[arguments]:可选，代表函数列表，他有in，out，inout修饰参数，和mysql一样

[language]后面代表函数的语言，例如可以写sql或者pgsql，他们在语法中有一定的区别。

例如sql，他在写返回值的时候就不需要返回，可以直接返回select结果

create or replace function addone(in res int) returns int
language sql
as $$
		--return res+1
    select 3;
    select res+1;
    $$

pgsql不同，返回值需要声明

create or replace function addone(in res int) returns int
language plpgsql
as $$
    begin
    return res+1;
    end;
    $$

抛出异常

没看懂，以后再补

触发器

PostgreSQL 触发器是数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用。

和mysql一样，有插入，删除，修改三种。

和mysql不同的是，他支持多行修改触发，也就是说，当你插入时影响了多行，他可以只触发一次，当然也可以影响多行触发多次。这区别于你使用ROW（单行）还是STATEMENT（多行）

pgsql的触发器似乎只能触发函数，所以需要吧你需要触发的内容写成一个函数，交给触发器

CREATE TRIGGER 触发器名字
AFTER INSERT或者UPDATE或DELETE
ON table_name 
FOR EACH ROW或者STATEMENT
EXECUTE PROCEDURE 
触发函数名();

视图

View（视图）是一张假表，只不过是通过相关的名称存储在数据库中的一个 PostgreSQL 语句。

View（视图）实际上是一个以预定义的 PostgreSQL 查询形式存在的表的组合。

View（视图）可以包含一个表的所有行或从一个或多个表选定行。

View（视图）可以从一个或多个表创建，这取决于要创建视图的 PostgreSQL 查询。

View（视图）是一种虚拟表，允许用户实现以下几点：

• 用户或用户组认为更自然或直观查找结构数据的方式。
• 限制数据访问，用户只能看到有限的数据，而不是完整的表。
• 汇总各种表中的数据，用于生成报告。

pgsql的视图是只读的，不可以操作增删改，但是可以给视图设置触发器

创建

CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

TEMPORARY|TEMP：视图被创建为一个临时视图。在当前会话结束时会自动删掉。当临时视图存在时，具有相同名称的已有永久视图对当前会话不可见，除非用模式限定的名称引用它们。如果视图引用的任何表是临时的，视图将被创建为临时视图（不管有没有指定TEMPORARY）。

 调用

SELECT * FROM view_name;

删除

DROP VIEW view_name;

事务

TRANSACTION（事务）是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。

数据库事务通常包含了一个序列的对数据库的读/写操作。

当事务被提交给了数据库管理系统（DBMS），则 DBMS 需要确保该事务中的所有操作都成功完成且其结果被永久保存在数据库中，如果事务中有的操作没有成功完成，则事务中的所有操作都需要回滚，回到事务执行前的状态；同时，该事务对数据库或者其他事务的执行无影响，所有的事务都好像在独立的运行。

由此引发事务的四个特性

事务属性

事务具有以下四个标准属性，通常根据首字母缩写为 ACID：

• 原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
• 一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
• 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
• 持久性（Durability）：已被提交的事务对数据库的修改应该永久保存在数据库中。

事务控制

开启：begin和bengin transaction没有区别都是开启事务的关键词

提交：有两种方法一种是commit一种是end transaction 前者提交事务后不会结束事务，后者会结束事务并提交

存档：savepoint可以在回滚时回滚到savepoint关键字的时候

/*以下是启动一个事务的简单语法：*/
BEGIN;
---或者---
BEGIN TRANSACTION;

    -- 这里写事务代码
    -- 其中如果失败可以选择回滚事务
    ROLLBACK;
    
    -- 可以通过savepoint关键字来记录一个存档点
    savepoint
 
/*COMMIT 命令的语法如下：*/
COMMIT;
---或者---
END TRANSACTION;
 

索引

索引是加速搜索引擎检索数据的一种特殊表查询。简单地说，索引是一个指向表中数据的指针。一个数据库中的索引与一本书的索引目录是非常相似的。

索引有助于加快 SELECT 查询和 WHERE 子句，但它会减慢使用 UPDATE 和 INSERT 语句时的数据输入。索引可以创建或删除，但不会影响数据。

创建索引

CREATE INDEX index_name ON table_name(列名);

一些常见的索引类型

1.主键索引（PRIMARY KEY）：它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同事创建主键索引（一个表中只能有一个主键索引）

2.唯一索引（UNIQUE）:唯一索引的值必须唯一，但允许为空值。创建：ALTER TABLE 表名 ADD UNIQUE（字段名称）

3.普通索引（INDEX）：最基本的索引，它没有任何限制。创建：ALTER TABLE 表名 ADD INDEX 索引名称（字段名称）

4.组合索引（INDEX）：一个索引包含多个列，多用于避免回表查询。ALTER TABLE 表名 ADD INDEX 索引名称（字段名称1，字段名称2，......字段名称N）

5.全文索引（FULLTEXT）：全文索引，是目前搜索引擎使用的一种关键技术。ALTER TABLE 表名 ADD FULLTEXT 索引名称（字段名称）

删除索引

DROP INDEX index_name;

不应使用索引的场景

• 索引不应该使用在较小的表上。
• 索引不应该使用在有频繁的大批量的更新或插入操作的表上。
• 索引不应该使用在含有大量的 NULL 值的列上。
• 索引不应该使用在频繁操作的列上。

存储过程

先咕咕

CREATE FUNCTION "public"."proc_h_testdemo"()
	RETURNS void AS $BODY$BEGIN
	-- Routine body goes here...
 
	RETURN;
END$BODY$
	LANGUAGE plpgsql