PostgreSQL

1.安装

安装大家一定会遇到很多错误，对此，我对我遇到的所有的错误进行总结，希望能对大家有帮助。

地址：
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads。
傻瓜式安装

• 问题1：PostgreSQL查不到服务

解决：
cd 到bin目录下，输入：pg_ctl register -N PostgreSQL -D “C:\PostgreSQL\9.5\data”
//重新配置文件
pg_ctl reload -D …/data
注意：要用管理员身份运行.

• 问题2：PostgreSQL启动后自动停止

//删除服务
C:\WINDOWS\system32>sc delete PostgreSQL
[SC] DeleteService 成功

//cd 到bin目录
C:\WINDOWS\system32>cd C:\Program Files\PostgreSQL\14\bin

//注册新服务
C:\Program Files\PostgreSQL\14\bin>.\pg_ctl.exe register -D ..\data

//初始化data
C:\Program Files\PostgreSQL\14\bin>pg_ctl.exe -D "C:\Program Files\PostgreSQL\data" initdb
属于此数据库系统的文件宿主为用户 "12057".
此用户也必须为服务器进程的宿主.
数据库簇将使用本地化语言 "Chinese (Simplified)_China.936"进行初始化.
本地化隐含的编码 "GBK" 不允许作为服务器端的编码.
默认的数据库编码将采用 "UTF8" 作为代替.
initdb: 无法为本地化语言环境"Chinese (Simplified)_China.936"找到合适的文本搜索配置
缺省的文本搜索配置将会被设置到"simple"

禁止为数据页生成校验和.

修复已存在目录 C:/Program Files/PostgreSQL/14/data 的权限 ... 成功
正在创建子目录 ... 成功
选择动态共享内存实现 ......windows
选择默认最大联接数 (max_connections) ... 100
选择默认共享缓冲区大小 (shared_buffers) ... 128MB
选择默认时区 ... Asia/Shanghai
创建配置文件 ... 成功
正在运行自举脚本 ...成功
正在执行自举后初始化 ...成功
同步数据到磁盘...成功

initdb: 警告: 为本地连接启用"trust"身份验证
你可以通过编辑 pg_hba.conf 更改或你下次
执行 initdb 时使用 -A或者--auth-local和--auth-host选项.

成功。您现在可以用下面的命令开启数据库服务器：

    ^"C^:^\Program^ Files^\PostgreSQL^^\bin^\pg^_ctl^" -D ^"C^:^/Program^ Files^/PostgreSQL^/14^/data^" -l 日志文件 start

//启动服务器
C:\Program Files\PostgreSQL\14\bin>.\pg_ctl.exe start -D ..\data
等待服务器进程启动 ....2022-04-18 11:07:20.528 CST [780] 日志:  正在启动 PostgreSQL 14.2, compiled by Visual C++ build 1914, 64-bit
2022-04-18 11:07:20.540 CST [780] 日志:  正在监听IPv6地址"::1"，端口 5432
2022-04-18 11:07:20.541 CST [780] 日志:  正在监听IPv4地址"127.0.0.1"，端口 5432
2022-04-18 11:07:20.711 CST [13980] 日志:  数据库上次关闭时间为 2022-04-18 11:07:08 CST
2022-04-18 11:07:20.824 CST [780] 日志:  数据库系统准备接受连接
 完成
服务器进程已经启动

• 问题3：启动项目，显示拒绝连接。之后打开pgAdmin登录 *** 作，但失败了！并显示错误信息。

//创建用户
createuser -s -r postgres
//设置密码
C:\Program Files\PostgreSQL\14\bin>psql -U postgres
psql (14.2)
输入 "help" 来获取帮助信息.

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

postgres=# alter user postgres with password '123456';
ALTER ROLE
postgres=# \q

2.数据类型
枚举类型的使用：
枚举类型需要使用 CREATE TYPE 命令先创建，再使用

//创建，类型名为mood 值为enum('sad','fine','happy')
create type mood as enum('sad','fine','happy');
create table person(
     person_name text,
	 current_mood mood
);
insert into person values('HuangJuan','happy');
SELECT * FROM person WHERE current_mood = 'happy';

3.创建表
语法

CREATE TABLE 语法格式如下：

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

create table company(
	ID int primary key not null,
	name        text   not null,
	AGE         int    not null,
	ADDRESS     char(50),
	salary      real
);

4.删除表
语法

DROP TABLE table_name;

eg:
drop table person;

5. INSERT INTO

PostgreSQL INSERT INTO 语句用于向表中插入新记录。

我们可以插入一行也可以同时插入多行。
语法
INSERT INTO 语句语法格式如下：

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

注意：postgresql中不能使用" "，只能使用’ ’ .

eg:

insert into company(id,age,name,address,salary) values (3,20,'王琦','湖南',2000),(1,17,'杨晓静','华南',2030);

6.简单的select 语句与mysql一致

7.WHERE
其他的都与mysql一致
搭配
NOT NULL
在公司表中找出 AGE(年龄) 字段不为空的记录：

SELECT * FROM COMPANY WHERE AGE IS NOT NULL;

NULL

在公司表中找出address字段为空的记录

select * from company where address is null;

LIKE

在 COMPANY 表中找出 NAME(名字) 字段中以 Pa 开头的的数据：

runoobdb=# SELECT * FROM COMPANY WHERE NAME LIKE ‘Pa%’;

eg：

select * from company where name like '黄%';

IN

以下 SELECT 语句列出了 AGE(年龄) 字段为 25 或 27 的数据：

runoobdb=# SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );

eg:

select * from company where age in (12,23);

NOT IN

以下 SELECT 语句列出了 AGE(年龄) 字段不为 25 或 27 的数据：

runoobdb=# SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );
eg:

select * from company where age not in (12,23);

BETWEEN

以下 SELECT 语句列出了 AGE(年龄) 字段在 25 到 27 的数据：

SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;

子查询：
两种格式
SELECT 语句使用了 SQL 的子查询，子查询语句中读取 SALARY(薪资) 字段大于 10000的数据，然后通过 EXISTS 运算符判断它是否返回行，如果有返回行则读取所有的 AGE(年龄) 字段。

SELECT * FROM COMPANY WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 2500);

子查询语句中读取 SALARY(薪资) 字段大于 2500 的 AGE(年龄) 字段数据，然后用 > 运算符查询大于该 AGE(年龄) 字段数据；
注意：在这段查询语句中，（）中的查询结果必须只能有一条

8. UPDATE
修改一个或多个字段的数据

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

eg:

update company set name='朴宰灿',address='韩国' where name='黄娟';

9.DELETE 语句
使用 DELETE 语句来删除 PostgreSQL 表中的数据。

注意：delete语句只能删除整个数据行，不能只删除某个字段
delete age from company where name=‘杨晓静’; ×
delete from company where name=‘杨晓静’; √

10.LIKE 子句

在 PostgreSQL 数据库中，我们如果要获取包含某些字符的数据，可以使用 LIKE 子句。

在 LIKE 子句中，通常与通配符结合使用，通配符表示任意字符，在 PostgreSQL 中，主要有以下两种通配符：

百分号 % -----------可以包含多位字符
下划线 _ ------------只包含一位字符

11 LIMIT

PostgreSQL 中的 limit 子句用于限制 SELECT 语句中查询的数据的数量。
语法
带有 LIMIT 子句的 SELECT 语句的基本语法如下：

SELECT column1, column2, columnN FROM table_name LIMIT [no of rows]

eg:

select * from company limit 3;

LIMIT 子句与 OFFSET 子句一起使用时的语法：

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

注意：row num 为下标内容，从0开始
本来的内容

执行语句：

select * from company limit 3 offset 2;

结果：

12ORDER BY 语句

在 PostgreSQL 中，ORDER BY 用于对一列或者多列数据进行升序（ASC）或者降序（DESC）排列。
语法

ORDER BY 子句的基础语法如下：

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

可以在 ORDER BY 中使用一列或者多列，但是必须保证要排序的列必须存在。

ASC 表示升序，DESC 表示降序。

排序规律：
第一个字段优先排序，如果第一个字段相同，再比较第二个字段

eg:

select * from company where salary >2000 order by age asc;

通过年龄，升序排序

13.GROUP BY
配合聚合函数使用
查出的数据，一行为一组
在 PostgreSQL 中，GROUP BY 语句和 SELECT 语句一起使用，用来对相同的数据进行分组。

GROUP BY 在一个 SELECT 语句中，放在 WHRER 子句的后面，ORDER BY 子句的前面。
语法
下面给出了 GROUP BY 子句的基本语法:

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

GROUP BY 子句必须放在 WHERE 子句中的条件之后，必须放在 ORDER BY 子句之前。

在 GROUP BY 子句中，你可以对一列或者多列进行分组，但是被分组的列必须存在于列清单中。
group by 的作用：将数据分类。
eg:
原表：

直接查数据，不进行分组，会有冗余数据

select name,sum(salary) from company;

如果进行分组，结果：

select name,sum(salary) from company group by name;

14.HAVING

HAVING 子句可以让我们筛选分组后的各组数据。

WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。
语法

下面是 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

eg:
找出根据 NAME 字段值进行分组，并且 name(名称) 字段的计数少于 2 数据：

SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) < 2;

根据 name 字段值进行分组，并且名称的计数大于等于1 数据：

SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) > 1;

15. DISTINCT 关键字

在 PostgreSQL 中，DISTINCT 关键字与 SELECT 语句一起使用，用于去除重复记录，只获取唯一的记录。

我们平时在 *** 作数据时，有可能出现一种情况，在一个表中有多个重复的记录，当提取这样的记录时，DISTINCT 关键字就显得特别有意义，它只获取唯一一次记录，而不是获取重复记录。
语法

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

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

eg:
数据有冗余

SELECT NAME FROM COMPANY;

去除冗余

SELECT distinct name  FROM COMPANY ;

16.约束
通俗理解，就是对于数据进行一个约束之后，数据内容就有了一个规范，所有的 *** 作都要遵循这个规范。如果违反了约束，行为就会被约束终止。
范围：
约束可以在创建表的时候规定(通过create table语句)，或者在表创建后规定(通过alter table语句）
作用：
确保了数据库中数据的准确性和可靠性。
类型：
约束可以是列级或表级。列级约束仅适用于列，表级约束被应用到整个表。
常用的约束。

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

not null 约束
默认情况下，列可以保存null值，如果不想某列有null值，则需要用此约束，让列不能有null值。
null与没有数据是不一样的，null代表着未知的数据。
下面实例创建了一张新表叫 COMPANY1，添加了 5 个字段，其中三个 ID，NAME，AGE 设置不接受空置：

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

UNIQUE约束
设置列的数据都是唯一的，防止某一列出现重复的数值。
下面实例创建了一张新表叫 COMPANY3，添加了 5 个字段，其中 AGE 设置为 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

PRIMARY KEY 称为主键，是数据表中每一条记录的唯一标识。

设置 UNIQUE 的列可能有多个，但是一张表只有一列可以设置 PRIMARY KEY。

我们可以使用主键来引用表中的行，也可以通过把主键设置为其他表的外键，来创建表之间的关系。

主键是非空约束和唯一约束的组合。

一个表只能有一个主键，它可以由一个或多个字段组成，当多个字段作为主键，它们被称为复合键。

如果一个表在任何字段上定义了一个主键，那么在这些字段上不能有两个记录具有相同的值。

eg:

下面我们创建 COMAPNY4 表，其中 ID 作为主键：

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

拓展：复合键

 CREATE TABLE tags
      (
               question_id INTEGER NOT NULL,
               tag_id SERIAL NOT NULL,
               tag1 VARCHAR(20),
               tag2 VARCHAR(20),
               tag3 VARCHAR(20),
               PRIMARY KEY(question_id, tag_id)
      );

用法:
将两个字段放在primary key()中

FOREIGN KEY 约束

FOREIGN KEY 即外键约束，指定列(或一组列)中的值必须匹配另一个表的某一行中出现的值。

通常一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)，即维护了两个相关表之间的引用完整性。

主表的主键，可以作为子表的外键。要删除子表的数据，必须先删除主表的数据。

语法：
references 主表名（主表的主键名）not null

设置外键
创建person表

create type mood as enum('sad','happy','fine');
create table person(
	person_id integer primary key,
	person_name varchar(20),
	person_mood mood
);

创建thing表，设置person_id为外键

create table things(
	things_id integer primary key,
	things_name varchar(20),
	person_id integer references person(person_id) not null
);

CHECK 约束

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

实例

例如，下面实例建一个新的表 COMPANY2，增加了五列。在这里，我们为 SALARY 列添加 CHECK，所以工资不能为零：

create table company2(
	id int primary key not null,
	name text not null,
	age int not null,
	address char(50),
	salary real check(salary >0)
);

如果插入负值：

insert into company2 values(2,'黄娟',12,'湖南岳阳',-12);

报错
ERROR: 错误: 关系 “company2” 的新列违反了检查约束 “company2_salary_check”
DETAIL: 失败, 行包含(2, 黄娟, 12, 湖南岳阳

正确：

insert into company2 values(2,'黄娟',12,'湖南岳阳',300000);

EXCLUSION 约束

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

参考
https://blog.csdn.net/rudygao/article/details/50547465

//引入btree_gist扩展

create extension btree_gist;

//创建测试表

create table company7(
	id int primary key not null,
	name text,
	age int ,
	address char(50),
	salary real,
	exclude using gist
	(name with = ,
	age with<>)
)

//测试数据

insert into company7 values(4,'鹿晗',32,'北京',20000);

//如果名字一样，插入的年龄不一样，则插入报错

insert into company7 values(2,'鹿晗',31,'北京',20000);

ERROR: 错误: 互相冲突的键值违反排他约束"company7_name_age_excl"
DETAIL: 键(name, age)=(鹿晗, 31)与已存在的键(name, age)=(鹿晗, 32)冲突

//正确

insert into company7 values(2,'鹿晗',32,'北京',2000);

删除约束

删除约束必须知道约束名称，已经知道名称来删除约束很简单，如果不知道名称，则需要找到系统生成的名称，使用 \d 表名 可以找到这些信息。

通用语法如下：

ALTER TABLE table_name DROP CONSTRAINT some_name;

eg:

alter table company7 drop constraint company7_name_age_excl;

约束的名称，可以在pgadmin4中，约束查看
或者用命令行\d 表名 查看。

17.连接（JOIN）

用来将两个表或者多个表结合起来，再基于这些表的共同字段，进行 *** 作

cross join 交叉连接
概念：
把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有x和y行，则结果表有x*y行。
注意：
是第一个表的每一行先于第二个表的一行进行匹配，然后拿第二个表进行迭代。
语法：

select 字段 from table1 cross join table2

eg:
存在一个公司表company
存在一个部门表department

select company.name,department.dept from company cross join department

INNER JOIN 内连接
内连接根据连接为此结合两个表的列值来创建一个新的结果表。查询会把table1中的每一行与table2中的每一行进行比较，找到所有满足连接谓词的行的匹配对。
通俗语言：
先进行交叉连接，再进行on后的语句进行判断，输出最后的结果
语法：

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

eg:

select c.name,d.dept from company c INNER JOIN department d on c.id = d.id

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

对于左外连接，首先执行一个内连接。
然后，对于表 T1 中不满足表 T2 中连接条件的每一行，其中 T2 的列中有 null 值也会添加一个连接行。连接的表在 T1 中每一行至少有一行。
简单的理解：
拿表2的数据去找符合连接条件的表1数据，形成连接行。
且就算表2有些数据为null，也会把表1的全部行显示出来

下面是左外连接（ LEFT OUTER JOIN ）的基础语法：

SELECT 字段 FROM table1 LEFT OUTER JOIN table2 ON conditional_expression…

eg：
COMPANY 表的记录如下：

 id | name  | age | address      | salary
 ----+-------+-----+--------------+--------
   1 | Paul  |  32 | California   |  20000
   2 | Allen |  25 | Texas        |  15000
   3 | Teddy |  23 | Norway       |  20000
   4 | Mark  |  25 | Rich-Mond    |  65000
   5 | David |  27 | Texas        |  85000
   6 | Kim   |  22 | South-Hall   |  45000
   7 | James |  24 | Houston      |  10000
   8 | Paul  |  24 | Houston      |  20000
   9 | James |  44 | Norway       |   5000
  10 | James |  45 | Texas        |   5000

DEPARTMENT 表的记录如下：

 id | dept        | emp_id
----+-------------+--------
  1 | IT Billing  |  1
  2 | Engineering |  2
  3 | Finance     |  7

 SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;

 emp_id | name  |      dept
--------+-------+----------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 | James | Finance
        | James | null
        | David | null
        | Paul  | null
        | Kim   | null
        | Mark  | null
        | Teddy | null
        | James | null

右外连接

首先，执行内部连接。
然后，对于表T2中不满足表T1中连接条件的每一行，其中T1列中的值为空也会添加一个连接行。这与左联接相反;对于T2中的每一行，结果表总是有一行。
简单理解：
拿表1的数据去找符合连接条件的表2数据，形成连接行。
且就算表1有些数据为null，也会把表2的全部行显示出来。

下面是右外连接（ RIGHT OUT JOIN）的基本语法：

SELECT … FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression …

eg:

INSERT INTO public.department(
	id, dept, emp_id)
	VALUES (12, '产品', 2);

SELECT EMP_ID, NAME, DEPT FROM COMPANY RIGHT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;

 emp_id | name  |    dept
--------+-------+-----------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 | James | Finance
      2 | null  | 产品

外连接

首先，执行内部连接。然后，对于表 T1 中不满足表 T2 中任何行连接条件的每一行，如果 T2 的列中有 null 值也会添加一个到结果中。此外，对于 T2 中不满足与 T1 中的任何行连接条件的每一行，将会添加 T1 列中包含 null 值的到结果中。
简单理解：
外连接的结果就是，左外连接和右外连接的结果集，先输出右外连接的结果集再输出左外连接的结果集。

下面是外连接的基本语法：

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

eg:

SELECT EMP_ID, NAME, DEPT FROM COMPANY FULL OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;

 emp_id | name  |      dept
--------+-------+-----------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 | James | Finance
        | James | 
        | David | 
        | Paul  | 
        | Kim   | 
        | Mark  | 
        | Teddy | 
        | James | 

UNION

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

UNION

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

eg:

SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID
   UNION
SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
    ON COMPANY.ID = DEPARTMENT.EMP_ID;

UNION ALL *** 作符可以连接两个有重复行的 SELECT 语句，默认地，UNION *** 作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

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

Postgresql与springboot

依赖

<!--postgresql-->
		<dependency>
			<groupId>org.postgresql</groupId>
			<artifactId>postgresql</artifactId>
			<version>42.2.5</version>
		</dependency>

配置

spring:
  datasource:
    url: jdbc:postgresql://192.168.43.204:5432/exam?characterEncoding=UTF-8
    username: postgres
    password: 123456
    driver-class-name: org.postgresql.Driver
  jpa:
    database: Postgresql
    show-sql: true
    open-in-view: true
    properties:
      hibernate:
        enable_lazy_load_no_trans: true #使用延时加载时控制Session的生命周期
        dialect: org.hibernate.dialect.PostgreSQLDialect
        ddl-auto: create
        temp:
          use_jdbc_metadata_defaults: false

#端口号
server:
  port: 80