WSの小屋

学习 postgresql 的笔记

常用命令行

  • ?[commands]显示反斜杠命令的帮助；变量显示对特殊变量的帮助；选项显示关于 PSQL 命令行选项的帮助
  • \h [NAME] SQL 命令语法帮助，*表示所有命令
  • \q 退出 psql
  • \d[S+]列出表、视图和序列、NAME 描述表、视图、序列或索引
  • \l[+] [PATTERN] list 数据库
  • \c[连接]{[DBNAME|-用户|-主机|-端口|-]|连接} 连接新数据库(当前为“postgres”)；显示当前连接的信息

常用数据类型

数值类型

  • smallint，2 字节，更节省磁盘空间
  • integer，4 字节，应该成为数字类型的首选，例如存储金额，可以以分为单位存储
  • bigint，8 字节，能够存储的数字范围更大

任意精度类型

  • numeric(precision, scale)，任意精度，存储任意大小的数字，但是需要占用更多的空间
    • precision，总精度，可以存储的总位数
    • scale，小数点后的位数

浮点数类型

浮点数，精确度低于 NUMERIC，浮点数的精确性问题普遍存在于各类编程语言中

  • float4，单精度，4 字节
  • float8，双精度，8 字节

序列类型

  • smallserial，2 字节
  • serial，4 字节
  • bigserial，8 字节

金额类型

  • money，8 字节，存储货币金额 精确度同 NUMERIC，默认有 2 位小数

字符类型

  • varchar(n)，有长度限制的字符串
  • char(n)，定长字符串，长度不足则向后填充空白字符
  • text，不限长度
  • todo

二进制数据类型

日期/时间类型

  • timestamp with time zone 简写为 timestamptz，存储时以 UTC 时间存储，展示时转换为当前系统时区或指定时区
  • timestamp without time zone 间歇威威 timestamp，没有时区，展示时需要指定时区
  • time with time zone 有些尴尬，它只记录的当天的时间点而不包含当天的日期，当做时区转换时，时间点可能会跨到昨天或明天，这样看起来是容易混淆的，不建议使用
  • time without time zone
  • date 日期类型，不能指定 time zone
  • interval，时间间隔，可以按年月日时分秒计算

布尔类型

  • boolean，用于表示 true 或 false

枚举类型

枚举类型在 PostgreSQL 中属于自定义类型，包含一组静态且有序的值。

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

几何类型

  • point 二维平面上的点，用(x, y)表示，x、y 分别表示 x 轴、y 轴的坐标值
  • line 线，在坐标系上用 Ax + By + C = 0 表示，也就是一个二元一次方程，A、B 表示斜率，C 表示偏移量。

文本搜索类型

  • tsvector 顾名思义，该数据类型存储的是一个 vector（向量），包含了文本中的所有词元，vector 中的元素是经过排序的。

JSON 类型（JSON types）

  • json，输入被存储为文本，不做任何处理，输出和输入完全相同。
  • jsonb：输入经过预处理，忽略一些不重要的因素，如空白字符。输出可能和输入不同

数组类型

  • 一维数组 integer[]、text[]
  • 二维数组 integer[][]、text[][]
  • 指定数组大小 text[10] 可以指定数组大小，但数据库不会对输入做任何限制，跟不设置大小没有区别

组合类型

组合类型，将简单类型组合在一起，形成自定义类型。数据表的列可以定义为组合类型。 创建组合类型，声明类型包含的字段和字段类型

复制代码 
CREATE TYPE inventory_item AS (
  name            text,
  supplier_id     integer,
  price           numeric
);

范围类型（range types）

范围类型，用于表示基本类型的范围，如

  • int4range - 4 字节类型范围
  • int8range - 8 字节类型范围
  • numrange - numeric 类型范围
  • tsrange - 不带 timezone 的时间戳范围
  • tstzrange - 带 timezone 的时间戳范围
  • daterange - 日期范围

领域类型（domain types）

领域类型，基于已经存在的类型，通常用于给类型增加约束，使已存在的类型适用于特定“领域”。

数据库

创建数据库

create database 数据库名;

选择数据库

\c 数据库名

删除数据库

drop database 数据库名;

表格

创建表格

create table 表格名(
column1 datatype,
column2 datatype

primary key (一个或多个列)
);

删除表格

drop table 表格名;

模式

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

创建模式

create schema 模式名;

添加数据

create table 模式名.表格名 (
id serial primary key not null,
content text
);

删除模式

删除一个为空的模式（其中的所有对象已经被删除）：
drop schema 模式名;
删除一个模式以及其中包含的所有对象：
drop schema 模式名 cascade;

INSERT INTO

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

我们可以插入一行也可以同时插入多行。

插入一行

insert into 表名 (column1, column2)
values (value1, value2);

插入多行

insert into 表名 (column1, column2)
values (value1, value2), (value1, value2), (value1, value2);

SELECT

select column1, column2 FROM 表名;

WHERE

select * from table where id = 1;

  • AND 与
  • OR 或
  • NOT NULL 非空
  • LIKE 模糊查询 %通配符 _占位符
  • IN 含有
  • NOT IN 不含有
  • BETWEEN 范围

LIMIT OFFSET

SELECT * FROM COMPANY LIMIT 3 OFFSET 2;

ORDER BY

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

GROUP BY

在 PostgreSQL 中，GROUP BY 语句和 SELECT 语句一起使用，用来对相同的数据进行分组。

GROUP BY 在一个 SELECT 语句中，放在 WHRER 子句的后面，ORDER BY 子句的前面。

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

HAVING

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

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

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

DISTINCT

在 PostgreSQL 中，DISTINCT 关键字与 SELECT 语句一起使用，用于去除重复记录，只获取唯一的记录。
SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]

UPDATE

update table
set column1 = value1, column2 = value2
where id = 1;

DELETE

delete from table where id = 1;

WITH

With CTE AS
(Select
ID
, NAME
, AGE
, ADDRESS
, SALARY
FROM COMPANY )
Select * From CTE;

约束

  • NOT NULL 非空
  • UNIQUE 唯一
  • PRIMARY KEY 主键
  • FOREIGN KEY 外键
  • CHECK 检查
  • EXCLUSION 排他约束

删除约束

ALTER TABLE table_name DROP CONSTRAINT some_name;

JOIN

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

Comments | 0条评论