PostgreSQL教程

PostgreSQL的环境搭建

PostgreSQL下载官网地址：Community DL Page

PostgreSQL安装教程：Windows 10平台安装PostgreSQL 14.2详细教程

PostgreSQL数据类型

PostgreSQL提 供了丰富的数据类型。用户可以使用 CREATE TYPE 命令在数据库中创建新的数据类型。

数值类型

数值类型由 2 字节、4 字节或 8 字节的整数以及 4 字节或 8 字节的浮点数和可选精度的十进制数组成。

名字	存储长度	描述	范围
smallint	2 字节	小范围整数	-32768 到 +32767
integer	4 字节	常用的整数	-2147483648 到 [phone]
bigint	8 字节	大范围整数	-9223372036854775808 到 +9223372036854775807
decimal	可变长	用户指定的精度，精确	小数点前 131072 位；小数点后 16383 位
numeric	可变长	用户指定的精度，精确	小数点前 131072 位；小数点后 16383 位
real	4 字节	可变精度，不精确	6 位十进制数字精度
double precision	8 字节	可变精度，不精确	15 位十进制数字精度
smallserial	2 字节	自增的小范围整数	1 到 32767
serial	4 字节	自增整数	1 到 2147483647
bigserial	8 字节	自增的大范围整数	1 到 9223372036854775807

字符类型

序号	名字 & 描述
1	character varying(n)变长，有长度限制
2	character(n), char(n)f定长,不足补空白
3	text变长，无长度限制

布尔类型

boolean 有"true"(真)或"false"(假)两个状态， 第三种"unknown"(未知)状态，用 NULL 表示。

名称	存储格式	描述
boolean	1 字节	true/false

枚举类型

枚举 (enum) 类型是包含静态、有序值集的数据类型。它们相当于许多编程语言中支持的枚举类型。

与其他类型不同的是枚举类型需要使用 CREATE TYPE 命令创建。

示例：

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

CREATE TABLE person (
    name text,
    current_mood mood
);

INSERT INTO person VALUES ('Moe', 'happy');
SELECT * FROM person WHERE current_mood = 'happy';
 name | current_mood 
------+--------------
 Moe  | happy
(1 row)

日期/时间类型

名字	存储空间	描述	最低值	最高值	分辨率
timestamp [ (p) ] [ without time zone ]	8 字节	日期和时间(无时区)	4713 BC	294276 AD	1 毫秒 / 14 位
timestamp [ (p) ] with time zone	8 字节	日期和时间，有时区	4713 BC	294276 AD	1 毫秒 / 14 位
date	4 字节	只用于日期	4713 BC	5874897 AD	1 天
time [ (p) ] [ without time zone ]	8 字节	只用于一日内时间	0:00:00	0:00:00	1 毫秒 / 14 位
time [ (p) ] with time zone	12 字节	只用于一日内时间，带时区	00:00:00+1459	24:00:00-1459	1 毫秒 / 14 位
interval [ fields ] [ (p) ]	12 字节	时间间隔	-178000000 年	178000000 年	1 毫秒 / 14 位

JSON 类型

json 数据类型可以用来存储 JSON（JavaScript Object Notation）数据， 这样的数据也可以存储为 text，但是 json 数据类型更有利于检查每个存储的数值是可用的 JSON 值。

示例：

CREATE TABLE test(
        id serial NOT NULL PRIMARY KEY ,
        info json NOT NULL
);

INSERT INTO test(info) VALUES('{"stud    ent":"lmz","num":"20191110101"}');
-- 查询JSON中学生信息作为键。
select info -> 'student' from test;
 ?column?
----------
 "lmz"
(1 行记录)
-- -> 操作符表示查出的数据依旧为JSON数据
select info ->> 'student' from test;
 ?column?
----------
 lmz
(1 行记录)
-- ->> 查出的数据为具体的值，此时的值已经表述为字符串的形式了。

其他数据类型

除了以上基本常用的数据类型外，PostgreSQL 还为我们提供了非常多的数据类型，比如 枚举类型、几何类型、网络地址类型、位串类型、文本搜索类型、UUID类型、数组类型、货币类型等等。

可以在官网中查看它们的用法：Chapter 8. Data Types

这些数据类型比MySQL的数据类型更具有多样性，可以应用于更多的不同的场景，让数据库更增加了灵活性。

PostgreSQL 运算符

运算符一般用在 WHERE 语句中，作为过滤条件。

常见的运算符有：

• 算术运算符

运算符	描述	实例
+	加	a + b 结果为 5
-	减	a - b 结果为 -1
*	乘	a * b 结果为 6
/	除	b / a 结果为 1
%	模（取余）	b % a 结果为 1
^	指数	a ^ b 结果为 8
|/	平方根	|/ 25.0 结果为 5
||/	立方根	||/ 27.0 结果为 3
!	阶乘	5 ! 结果为 120
!!	阶乘（前缀操作符）	!! 5 结果为 120

• 比较运算符

运算符	描述	实例
=	等于	(a = b) 为 false。
!=	不等于	(a != b) 为 true。
<>	不等于	(a <> b) 为 true。
>	大于	(a > b) 为 false。
<	小于	(a < b) 为 true。
>=	大于等于	(a >= b) 为 false。
<=	小于等于	(a <= b) 为 true。

• 逻辑运算符

序号	运算符 & 描述
1	AND逻辑与运算符。如果两个操作数都非零，则条件为真。PostgresSQL 中的 WHERE 语句可以用 AND 包含多个过滤条件。
2	NOT逻辑非运算符。用来逆转操作数的逻辑状态。如果条件为真则逻辑非运算符将使其为假。PostgresSQL 有 NOT EXISTS, NOT BETWEEN, NOT IN 等运算符。
3	OR逻辑或运算符。如果两个操作数中有任意一个非零，则条件为真。PostgresSQL 中的 WHERE 语句可以用 OR 包含多个过滤条件。

• 按位运算符

运算符	描述	实例
&	按位与操作，按二进制位进行"与"运算。运算规则：0&0=0; 0&1=0; 1&0=0; 1&1=1;	(A & B) 将得到 12，即为 0000 1100
|	按位或运算符，按二进制位进行"或"运算。运算规则：0|0=0; 0|1=1; 1|0=1; 1|1=1;	(A | B) 将得到 61，即为 0011 1101
#	异或运算符，按二进制位进行"异或"运算。运算规则：0#0=0; 0#1=1; 1#0=1; 1#1=0;	(A # B) 将得到 49，即为 0011 0001
~	取反运算符，按二进制位进行"取反"运算。运算规则：~1=0; ~0=1;	(~A ) 将得到 -61，即为 1100 0011，一个有符号二进制数的补码形式。
<<	二进制左移运算符。将一个运算对象的各二进制位全部左移若干位（左边的二进制位丢弃，右边补0）。	A << 2 将得到 240，即为 1111 0000
>>	二进制右移运算符。将一个数的各二进制位全部右移若干位，正数左补0，负数左补1，右边丢弃。	A >> 2 将得到 15，即为 0000 1111

PostgreSQL 表达式

表达式是由一个或多个的值、运算符、PostgresSQL 函数组成的。

布尔表达式

数字表达式

日期表达式

示例：

SELECT CURRENT_TIMESTAMP;

PostgreSQL的使用

PostgreSQL创建数据库

创建数据库有其三种方式：

1. 使用 CREATE DATABASE SQL 语句来创建。

create database test;

2. 使用 createdb 命令来创建。

createdb 是一个 SQL 命令 CREATE DATABASE 的封装。

语法：

createdb [option...] [dbname [description]]

参数说明

dbname：要创建的数据库名。

description：关于新创建的数据库相关的说明。

options：参数可选项，可以是以下值：

序号	选项 & 描述
1	-D tablespace指定数据库默认表空间。
2	-e将 createdb 生成的命令发送到服务端。
3	-E encoding指定数据库的编码。
4	-l locale指定数据库的语言环境。
5	-T template指定创建此数据库的模板。
6	--help显示 createdb 命令的帮助信息。
7	-h host指定服务器的主机名。
8	-p port指定服务器监听的端口，或者 socket 文件。
9	-U username连接数据库的用户名。
10	-w忽略输入密码。
11	-W连接时强制要求输入密码。

打开命令窗口，进入到 PostgreSQL 的安装目录，并进入到 bin 目录，执行：

createdb -h localhost -p 5432 -U postgres test2  -- 创建test2数据库

3. 使用 pgAdmin 工具。

PostgreSQL 选择数据库

同样打开sql shell

使用 \l 用于查看已经存在的数据库：

接下来我们可以使用 \c + 数据库名 来进入数据库：

postgres=# \c test
您现在已经连接到数据库 "test",用户 "postgres".

使用 pgAdmin 工具来选择数据库

PostgreSQL 删除数据库

删除数据库可以使用三种方式：

1. 使用 DROP DATABASE SQL 语句来删除。

DROP DATABASE [ IF EXISTS ] 数据库名称

2. 使用 dropdb 命令来删除。

语法：

dropdb [connection-option...] [option...] dbname

参数说明：

dbname：要删除的数据库名。

options：参数可选项，可以是以下值：

序号	选项 & 描述
1	-e显示 dropdb 生成的命令并发送到数据库服务器。
2	-i在做删除的工作之前发出一个验证提示。
3	-V打印 dropdb 版本并退出。
4	--if-exists如果数据库不存在则发出提示信息，而不是错误信息。
5	--help显示有关 dropdb 命令的帮助信息。
6	-h host指定运行服务器的主机名。
7	-p port指定服务器监听的端口，或者 socket 文件。
8	-U username连接数据库的用户名。
9	-w连接时忽略输入密码。
10	-W连接时强制要求输入密码。
11	--maintenance-db=dbname删除数据库时指定连接的数据库，默认为 postgres，如果它不存在则使用 template1。

打开命令窗口，进入到 PostgreSQL 的安装目录，并进入到 bin 目录，执行：

dropdb -h localhost -p 5432 -U postgres test2

3. 使用 pgAdmin 工具。

PostgreSQL 创建表

语法：

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( 一个或多个列 )
);

示例：

-- 创建一个表名为 COMPANY 
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

使用 \d 命令来查看表格是否创建成功：

使用 \d tablename 查看表格信息：

PostgreSQL 删除表

语法：

DROP TABLE table_name; -- 如果有多张表使用,号隔开

PostgreSQL 更改表

使用ALTER TABLE 命令

语法：

-- 用 ALTER TABLE 在一张已存在的表上添加列的语法如下：
ALTER TABLE table_name ADD column_name datatype;
-- 在一张已存在的表上 DROP COLUMN（删除列），语法如下：
ALTER TABLE table_name DROP COLUMN column_name;
-- 修改表中某列的 DATA TYPE（数据类型），语法如下：
ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;

另外，还可以使用 ALTER TABLE 命令添加和删除约束。

-- 给表中某列添加 NOT NULL 约束，语法如下：
ALTER TABLE table_name ALTER column_name datatype NOT NULL;
-- DROP CONSTRAINT （删除约束），语法如下：
ALTER TABLE table_name
DROP CONSTRAINT MyUniqueConstraint;

PostgreSQL 模式创建

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

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

使用模式的优势：

• 允许多个用户使用一个数据库并且不会互相干扰。
• 将数据库对象组织成逻辑组以便更容易管理。
• 第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突。

语法：

CREATE SCHEMA myschema.mytable (
...
);

示例：

-- 创建模式 
create schema myschema;

-- 在该模式下创建一张表
create table myschema.company(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25),
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

-- 使用查询查找这张表是否创建
select * from myschema.company;

删除模式

语法：

DROP SCHEMA myschema;

删除一个模式下所有对象：

DROP SCHEMA myschema CASCADE;

PostgreSQL CRUD

INSERT 语句

语法：

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

示例：

-- 创建一张 COMPANY 表
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL,
   JOIN_DATE      DATE
);
-- 在 COMPANY 表中插入以下数据：
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) 
VALUES (1, 'Paul', 32, 'California', 20000.00,'2001-07-13');

-- 插入多行
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) 
VALUES (2, 'Mark', 25, 'Rich-Mond ', 65000.00, '2007-12-13' ), 
(3, 'David', 27, 'Texas', 85000.00, '2007-12-13');
INSERT 0 2

-- 查询
SELECT * FROM company;

SELECT 语句

语法：

SELECT column1, column2,...columnN FROM table_name;

示例：

SELECT ID,NAME FROM company;

WHERE 子句

语法：

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1]

示例：

-- 找出 AGE(年龄) 字段大于等于 25，并且 SALARY(薪资) 字段大于等于 65000 的数据：
SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
-- 找出 AGE(年龄) 字段大于等于 25，或者 SALARY(薪资) 字段大于等于 65000 的数据：
SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;
-- 在公司表中找出 AGE(年龄) 字段不为空的记录：
SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
-- 在 COMPANY 表中找出 NAME(名字) 字段中以 Pa 开头的的数据：
SELECT * FROM COMPANY WHERE NAME LIKE 'Pa%';
-- 以下 SELECT 语句列出了 AGE(年龄) 字段为 25 或 27 的数据：
SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );

子查询

--查询语句中读取 SALARY(薪资) 字段大于 65000 的数据，然后通过 EXISTS 运算符判断它是否返回行，
-- 如果有返回行则读取所有的 AGE(年龄) 字段。
runoobdb=# SELECT AGE FROM COMPANY
        WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

UPDATE 语句

语法：

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

DELETE 语句

语法：

DELETE FROM table_name WHERE [condition];

LIKE 子句

通配符：

• 百分号 %
• 下划线 _

语法：

SELECT FROM table_name WHERE column LIKE 'XXXX%';
或者
SELECT FROM table_name WHERE column LIKE '%XXXX%';
或者
SELECT FROM table_name WHERE column LIKE 'XXXX_';
或者
SELECT FROM table_name WHERE column LIKE '_XXXX';
或者
SELECT FROM table_name WHERE column LIKE '_XXXX_';

LIMIT 子句

语法：

SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]
或者
SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows] OFFSET [row num]

示例：

SELECT * FROM COMPANY LIMIT 3 OFFSET 2;

ORDER BY 语句

语法：

SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

GROUP BY 语句

语法：

SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

WITH 子句

如果一整句查询中多个子查询都需要使用同一个子查询的结果，那么就可以用with as，将共用的子查询提取出来，加个别名。后面查询语句可以直接用，对于大量复杂的SQL语句起到了很好的优化作用。

HAVING 子句

下面是 HAVING 子句在 SELECT 查询中的位置：

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
-- HAVING 子句必须放置于 GROUP BY 子句后面，ORDER BY 子句前面，下面是 HAVING 子句在 SELECT 语句中基础语法：
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

DISTINCT 关键字

用于去除重复记录的 DISTINCT 关键字的基本语法如下：

SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]

PostgreSQL的高级教程

PostgreSQL 约束

• NOT NULL：指示某列不能存储 NULL 值。
• UNIQUE：确保某列的值都是唯一的。
• PRIMARY Key：NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
• FOREIGN Key： 保证一个表中的数据匹配另一个表中的值的参照完整性。
• CHECK： 保证列中的值符合指定的条件。
• EXCLUSION ：排他约束，保证如果将任何两行的指定列或表达式使用指定操作符进行比较，至少其中一个操作符比较将会返回 false 或空值。

PostgreSQL 连接

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

PostgreSQL 事务

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

数据库事务通常包含了一个序列的对数据库的读/写操作。包含有以下两个目的：

• 为数据库操作序列提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
• 当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。

事务的ACID

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

事务控制

• BEGIN TRANSACTION：开始一个事务。
• COMMIT：事务确认，或者可以使用 END TRANSACTION 命令。
• ROLLBACK：事务回滚。

PostgreSQL 视图

PostgreSQL 视图是只读的，因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句。但是可以在视图上创建一个触发器，当尝试 DELETE、INSERT 或 UPDATE 视图时触发，需要做的动作在触发器内容中定义。

创建视图

语法：

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

您可以在 SELECT 语句中包含多个表，这与在正常的 SQL SELECT 查询中的方式非常相似。如果使用了可选的 TEMP 或 TEMPORARY 关键字，则将在临时数据库中创建视图。

临时数据库空间 是专门为临时表的存储而保留的数据库空间。 您不能为临时数据库空间建立镜像。 数据库服务器不会删除临时数据库空间，除非明确指示应这么做。 临时数据库空间是临时的指的只是数据库服务器在其不正常关闭时不保留任何数据库空间内容。

删除视图

语法：

DROP VIEW view_name;

在SpringBoot项目中集成PostgreSQL数据库

创建项目

创建名为 biobin-postgre-sql-demo 的springboot项目

引入依赖

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
        <version>2.2.1.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>42.3.8</version>
    </dependency>
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.5.5</version>
    </dependency>
</dependencies>

创建数据库

使用pgadmin创建一个数据名为biobin-demo的数据库

创建表

创建system_user表

applicatio.yml配置

server:
  port: 9090

spring:
  application:
    name: demo
  datasource:
    driver-class-name: org.postgresql.Driver
    url: jdbc:postgresql://localhost:5432/biobin-demo
    username: postgres
    password: root
mybatis-plus:
  configuration:
    cache-enabled: true
    use-generated-keys: true
    default-executor-type: REUSE
    use-actual-param-name: true

启动项目完成调试

访问 http://localhost:9090/system/user/list 查看结果

总结：

PostgreSQL与MySQL的区别

1. 数据类型支持：

1. PostgreSQL支持更多的内置数据类型，例如数组、JSON、UUID等，而MySQL相对较少。
2. PostgreSQL还支持自定义数据类型和自定义函数。

2. 性能：

1. 在大型数据集和高并发负载下，MySQL在某些情况下可能更快，因为它的设计更加简单，并且在简单查询方面表现较好。
2. PostgreSQL在复杂查询和大型数据集上可能更优秀，因为其优化器和执行引擎更为先进。

3. 存储引擎：

1. MySQL支持多种存储引擎，例如InnoDB、MyISAM等，每种引擎有不同的特性和适用场景。
2. PostgreSQL使用类似存储引擎的概念，但它的存储系统更为统一，所有数据都由一种存储引擎处理。

4. 开发社区：

1. PostgreSQL拥有一个非常活跃和强大的开发社区，更新频繁，提供更多的功能和修复。
2. MySQL由Oracle公司维护，虽然也有社区版本，但开发进度可能受到商业版本的影响。

PostgreSQL 的注意事项

• 使用navicat连接PostgreSQL的版本问题

15版本以上使用navicat连接会报错，应升级navicat版本到16.1以上