PostgreSQL学习记录——第一天

昨天已经完成了PostgreSQL（后续和其他平台一样把这个简称为PG）的安装，今天就来逐步学吧。至于为什么从MySQL转移到PG，主要是因为以下几点：

1. PG支持更多的数据类型
2. PG在所有配置上均支持ACID，而MySQL只能在部分情况下支持
3. PostgreSQL使用了多版本并发控制（MVCC）减少了出现死锁的概率
4. PostgreSQL支持更多的索引类型

说完上面的就不得不提到简历上写了会MySQL然后被面试官无数次拷打的经历了（MongoDB我也写了，没人问，我还准备了蛮多的嘞）。

首先就是ACID是哪四个，莫名感觉是所有面试官都会想问。

其次是锁相关，S锁、X锁、IS锁、IX锁、未提交读、提交读、可重复读、串行化，呕。

然后就说索引B树B+树，前段时间面试才被问到过这个。

然后正式进入学习记录。

start PG

在开启SQL Shell（psql）之前记得在cmd里打开PG服务。

数据类型

这部分内容太多太杂，就把比MySQL支持的更多的数据类型大概记一下吧（我自己按照资料写的，如果有缺少或者多了麻烦各位补充啊）

PostgreSQL数据类型	描述
SERIAL	自增整数
BIGSERIAL	大范围自增整数
MONEY	货币金额
UUID	通用唯一标识符
CIDR	IPv4或IPv6网络地址
INET	IPv4或IPv6主机地址
MACADDR	MAC地址
BIT	定长位串
BIT VARYING	变长位串
BOX	平面中的矩形框
LINE	无限长的线
LSEG	线段
PATH	闭合路径
POINT	平面中的点
POLYGON	闭合的平面多边形
CIRCLE	平面中的圆
INTERVAL	时间间隔
TSVECTOR	文本搜索文档
TSQUERY	文本搜索查询
XML	XML数据
JSON	JSON数据
JSONB	二进制JSON数据，可索引
HSTORE	键值对存储

数据库

创建数据库

CREATE DATABASE dbname;

选择数据库

\l 查看数据库（莫名想到了Linux的ls -l）

\c + dbname 进入数据库

删除数据库

DROP DATABASE [IF EXISTS] name;

删除需要root权限，普通用户无法删除

表格

创建表格

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

例子

table EX

\d是查看当前已经存在的表格

\d tablename是查看表格详情

查看详情

删除表格

DROP TABLE table_name;

PG模式（SCHEMA）

第一眼看感觉有点像事务，但是事务是遵循ACID原则的并且通过MVCC来建立隔离，两者的逻辑不一样。

模式中可以包括视图、索引、数据类型、函数和操作等。

相同的名称可以运用于不同的模式而不报错，for example schema1和myschema都包含mytable的表。

模式的优势：

• 允许多个用户操作数据库而不相互干扰
• 将数据库对象组织成逻辑便于管理（我的理解是我在做某个东西的时候我将一张表三个视图组织在一起，这个组织就可以一起被操作同时还可以单独操作，然后也可以对整个模式里的对象进行批量操作）
• 第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突。

创建模式

语法如下

CREATE SCHEMA myschema (
...
);

创建表的语法如下：

CREATE TABLE myschema.mytable (
    column1 datatype1,
    column2 datatype2,
    ...
);

上述语法是在一个叫myschema的模式下创建了一个叫mytable的表

例子

create schema

这里我们建立了一个模式和一个表，中间由于手误少了一个’,’。

删除模式

删除一个空的模式（里面的对象已经全部被删除了）

DROP SCHEMA myschema;

删除一个模式及其下面的对象

DROP SCHEMA myschema CASSADE;

INSERT INTO 语句

语法如下

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

需要注意的是column和value需要数量一样，一一对应

对所有字段均添加值的语法如下

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

序号	输出信息&描述
1	INSERT oid 1 直插入一行且表中具备OID的返回信息，那么oid就分配给被插入行
2	INSERT 0 # 插入多行，#为插入行数

例子

这里先创建一个表

create table

insert into

这里我们从上到下分别来看

• 给表里插入数据
• 给表里插入忽略SALARY的数据
• 插入语句 JOIN_DATE 字段使用 DEFAULT 子句来设置默认值，而不是指定值
• 插入多行
• 查询数据

SELECT语句

这里就不多说了，不会的拖出去乱棍打死。和MySQL之类的一摸一样。

运算符

PG的运算符一般用在WHERE中
有四个

• 算术运算符
• 比较运算符
• 逻辑运算符
• 按位运算符

算术运算符

算术运算符

比较运算符

运算符	描述	实例
=	等于	(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 包含多个过滤条件。

三值逻辑系统

a	b	a AND b	a OR b
TRUE	TRUE	TRUE	TRUE
TRUE	FALSE	FALSE	TRUE
TRUE	NULL	NULL	TRUE
FALSE	FALSE	FALSE	FALSE
FALSE	NULL	FALSE	NULL
NULL	NULL	NULL	NULL

a	NOT a
TRUE	FALSE
FALSE	TRUE
NULL	NULL

位运算符

位运算符

按位运算符

按位运算符

表达式

基本语法如下

SELECT column1, column2, columnN
FROM table_name
WHERE [CONDITION | EXPRESSION];

布尔表达式

根据一个指定条件来读取数据

SELECT column1, column2, columnN
FROM table_name
WHERE SINGLE VALUE MATCHTING EXPRESSION;

数学表达式

SELECT numerical_expression as  OPERATION_NAME
[FROM table_name WHERE CONDITION] ;

PG还有内置计算函数（这个好像都有吧sum、avg这些）

日期表达式

SELECT CURRENT_TIMESTAMP;

返回当前日期

WHERE子句

这部分内容和MySQL差不多
WHERE的语法如下：

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

还可以使用比较运算符或逻辑运算符
AND, OR, NOT NULL, LIKE, NOT IN, BETWEEN, 子查询

UPDATE

基本语法如下：

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

DELETE

基本语法如下：

DELETE FROM table_name WHERE [condition];

LIKE

和MySQL一样，都是进行匹配
基本语法如下：

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]

和OFFSET一起用的语法如下：

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

ORDER BY

ORDER BY是用来对一列或多列进行排序的（升序或降序）

基本语法如下：

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

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

GROUP BY

GROUP BY用来和SELECT语句一起使用，对数据进行分组。

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

基本语法如下：

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

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)
   [ORDER BY columns]

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

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

WITH 递归

在 WITH 子句中可以使用自身输出的数据。

公用表表达式 (CTE) 具有一个重要的优点，那就是能够引用其自身，从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。

HAVING

HAVING子句用来筛选分组后的各组数据。

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

HAVING在SELECT语句中的位置

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

基本语法如下：

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

DISTINCT

和SELECT一起用，用于去重

基本语法如下：

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

好了，基本的操作到这里也就结束了，完结撒花~