PostgreSQL基础(一)

PostgreSQL基础（一）

PostgreSQL 是一个对象-关系数据库服务器（ORDBMS）。

手册地址：https://www.runoob.com/manual/PostgreSQL/index.html

关系型数据库（RDBMS）：传统的关系型数据库管理系统，是建立实体与实体之间的关系，最后将实体建立成表。

对象-关系型数据库（ORDBMS）：将实体建立成对象类进行封装，也是建立实体与实体之间的关系，对象之间的通信（外键等）通过消息进行。本质上还是关系型数据库！

ORDBMS就是在RDBMS的基础上添加了一些新特性！

PostgreSQL提供了两种可选模式。一种模式保证如果操作系统或硬件崩溃，则数据将保存到磁盘中，这种模式通常比大多数商业数据库要慢，这是因为它使用了刷新（或同步）方法；另一种模式与第一种不同，它不提供数据保证，但它通常比商业数据库运行得快。 遗憾的是，还没有一种折中的模式：既提供一定程度的数据安全性，又有较快的执行速度。今后的版本将会提供这种模式。

安装

安装教程：https://www.runoob.com/postgresql/windows-install-postgresql.html

如果遇到浏览器不允许的错误，则查看该博客：https://blog.csdn.net/qq_45681057/article/details/107239331

PostgreSQL数据类型

数值类型

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

货币类型

数据类型名字	存储长度	描述	范围
money	8B	货币金额	-92233720368547758.08 到 +92233720368547758.07

money是具有固定小数精度的货币金额类型，小数点后两位精度。

int，biging，numeric类型的值可以转换为money，不建议使用浮点数处理货币类型，因为存在舍入错误的可能！

字符类型

数据类型名字	描述
character varying(n)	变长，有长度限制
varchar(n)	定长，不足补空白
character(n)	定长，不足补空白
char(n)	定长，不足补空白
text	变长，无长度限制（其实也是有的但是特别大）

日期/时间类型

名字	存储空间	描述	最低值	最高值	分辨率
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 字节	只用于一日内时间	00:00:00	24: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 位

布尔类型

布尔类型有三种状态，true、false、unknown。第三种类型用NULL表示。

数据类型名称	存储格式	描述
boolean	1B	true/false/NULL

位串类型

位串就是01字符串。有两种位类型。

数据类型名称	描述
bit(n)	存储长度为n的位串
bit varying(n)	最大可以存储长度为n的位串

注意！bit类型的数据必须精确匹配位数（多了少了都不行）。写一个没有长度的bit等效于 bit(1)。

bit varying类型数据是最长 n 的变长类型（可以少，但不能多）。写一个没有长度的bit varying意思是没有长度限制。

枚举类型

与其他类型不同，枚举类型需要使用 CREATE TYPE 命令创建。创建后才能作为表中字段的类型！

例如：

CREATE TYPE mood AS ENUM ("SAD", "HAPPY")

CREATE TABLE person(
	name varchar(11),
    current_mood mood
);

INSERT INTO person VALUES ("Bob", "HAPPY");
SELECT * FROM person WHERE current_mood = "HAPPY";

UUID类型

UUID类型生成的数据是唯一的。UUID用于标识唯一数据。

数据类型名字	描述
UUID	标识的字段中的每个元素都是唯一的

UUID 被写成一个小写十六进制数字的序列，由分字符分成几组， 特别是一组8位数字+3组4位数字+一组12位数字，总共 32 个数字代表 128 位， 一个这种标准的 UUID 例子如下：

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

文本检索类型

全文检索即通过自然语言文档的集合来找到那些匹配一个查询的检索。

PostgreSQL 提供了两种数据类型用于支持全文检索：

数据类型名字	描述
tsvector	tsvector 的值是一个无重复值的 lexemes 排序列表， 即一些同一个词的不同变种的标准化。
tsquery	tsquery 存储用于检索的词汇，并且使用布尔操作符 &(AND)，|(OR)和!(NOT) 来组合它们，括号用来强调操作符的分组。

几何类型

数据类型名字	存储空间	说明	表现形式
point	16 字节	平面中的点	(x,y)
line	32 字节	(无穷)直线(未完全实现)	((x1,y1),(x2,y2))
lseg	32 字节	(有限)线段	((x1,y1),(x2,y2))
box	32 字节	矩形	((x1,y1),(x2,y2))
path	16+16n 字节	闭合路径(与多边形类似)	((x1,y1),…)
path	16+16n 字节	开放路径	[(x1,y1),…]
polygon	40+16n 字节	多边形(与闭合路径相似)	((x1,y1),…)
circle	24 字节	圆	<(x,y),r> (圆心和半径)

JSON类型

JSON类型的数据可以转换成字符串进行存储，但是直接存储为JSON类型读取后更好检索（不用读出来再转换成JSON类型了）

数据类型名字	描述
JSON	JSON类型的数据

PostgreSQL提供了两个函数处理JSON：

• array_to_json("{{1, 5}, {99, 100}}"::int[]) 结果：[[1, 5], [99, 100]]
• row_to_json(row(1, 'foo')) 结果：{"f1":1, "f2":"foo"}

数组类型

PostgreSQL允许将字段定义为可变长的多维数组。

数组的类型可以是任何 基本类型、用户自定义类型、枚举类型、复合类型。

创建表时声明为数组类型

CREATE TABLE testtable (
	name varchar(11),
    alias varchar(11)[3],
    matrix int[][],
    years int[3]
);

向数组类型字段中插值

INSERT INTO testtable VALUES
('Bob', '{"HaPi", "HanHan"}', '{{1,2,3}, {4,5,6}, {7,8,9}}', '{1,1,1}');

向数组类型插入值的时候，使用花括号 {} 进行分隔（类似于python中的[]），元素在 {} 中使用逗号分隔。

访问数组

访问PostgreSQL中的数组类型与平常其他语言访问数组一样，例如这里的查询语句：

SELECT * FROM testtable WHERE matrix[0][0] > matrix[0][1];

查询不出来结果。

根据数组类型数据筛选元组

根据数组类型的数据查询元组，必须对数组中的每个值进行检查！

SELECT * FROM testtable WHERE alias[0] = "HaPi" AND alias[1] = "HanHan";

2. 或使用ALL函数获取集合后筛选：

   SELECT * FROM testtable WHERE ALL(years) = 1;
   

修改数组类型

有两种方法可以修改数组类型的数据：

1. 直接使用结构化字符串修改数据：

   UPDATE testtable SET matrix = '{{1,2}, {2,3}}'
   

2. 使用 ARRAY 构造器修改数据：

   UPDATE testtable SET matrix = ARRAY[[1,2], [2,3]]
   

但是注意，结构需要相同（例如表中数组类型是二维数组，在插入、修改时也必须是二维数组）