PostgreSQL学习记录——第二天

今天准备继续学PG的高级操作，至于为什么过了一个周末才有开始学，那当然是因为上班时间拿来学习周末时间拿来玩啦~

今天差不多就是约束、JOIN、UNION、别名、索引、触发器、事务、锁这些，gogogo。

约束

在观感上感觉和MySQL的差不多，但是在查询相关资料后发现还是有些许区别。

和MySQL区别

首先明确一点，他们都支持五大约束（不知道的拖出去乱棍打死！面试经常考的都不知道！）。

PG自带了一个MySQL中没有的约束——排他约束（EXCLUDE）。同时在检查约束（CHECK）上会更加完善，能够支持更复杂的约束条件。

PG可以给约束别名，而MySQL只能使用原本的名称，这点上如果按照实际操作来看其实没什么差别，一个就是按照原本的英文去写一个就是按照自己的习惯去修改名称。直接用英文的话感觉上会更习惯日常操作。

PG支持部分约束和表达式约束（部分约束好像我之前面试的时候有被考到过，太久远了忘记了。），能够实现更复杂的操作。

PG常见约束

• NOT NULL 非空约束
• UNIQUE 唯一约束
• PREMARY key 主键约束
• FOREIGH key 外键约束
• CHECK 检查约束
• EXCLUSION 排他约束

NOT NULL

默认情况下，列可以为NULL，但如果不让这列为NULL就可以设置此约束。

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

命令中就给几个列附了非空约束

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

主键约束用于数据库中每条记录的唯一表标识，在设置时UNIQUE可以为多个，但PRIMARY KEY只能有一个，同时可以使用主键来引用表中的行，也可以通过将主键设置为其他表的外键来创建表之间的关联。

在前两个例子中，ID都是被设置为主键的。

FOREIGN KEY

外键约束就是该表的制定列中的值必须匹配到另一个表的某一行中出现的值。通常用来建立两个表中的连接。

CREATE TABLE DEPARTMENT1(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      references COMPANY2(ID)
);

这里是在建立好COMPANY2的基础上建立的DEPARTMENT1，也就是说EMP_ID依托于COMPANY2的ID，EMP_ID就是外键。

CHECK

检查约束就是用来限制该列中的值的，在给列添加值时，符合条件的就会被添加，如果为false（不符合条件）就无法输入。

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

我们这里建立的COMPANY4中给SALARY一个检查约束，条件是值大于0，也就是说只有大于0的值会被输入，反之不会。

EXCLUSION

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

CREATE TABLE COMPANY5(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT,
   AGE            INT,
   ADDRESS        CHAR(50),
   SALARY         REAL,
   EXCLUDE USING gist
   (NAME WITH =,  -- 如果满足 NAME 相同，AGE 不相同则不允许插入，否则允许插入
   AGE WITH <>)   -- 其比较的结果是如果整个表达式返回 true，则不允许插入，否则允许
);

USING gist 是用于构建和执行的索引一种类型。

这里需要注意的是，在使用这个SQL语句前记得用CREATE EXTENSION IF NOT EXISTS btree_gist;来开启扩展，不然会无法创建。

然后插入数据

INSERT INTO COMPANY5 VALUES(1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY5 VALUES(2, 'Paul', 32, 'Texas', 20000.00 );
-- 此条数据的 NAME 与第一条相同，且 AGE 与第一条也相同，故满足插入条件
INSERT INTO COMPANY5 VALUES(3, 'Allen', 42, 'California', 20000.00 );
-- 此数据与上面数据的 NAME 相同，但 AGE 不相同，故不允许插入

结果：

ERROR:  conflicting key value violates exclusion constraint "company7_name_age_excl"
DETAIL:  Key (name, age)=(Paul, 42) conflicts with existing key (name, age)=(Paul, 32).

删除约束

ALTER TABLE table_name DROP CONSTRAINT some_name;

如果不知道名称可以使用\d + table_name来查询

JOIN

常见JOIN类型

• CROSS JOIN
• INNER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN

预先准备

导入数据库

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'David', 27, 'Texas', 85000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );

INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );

然后用SELECT查看一下是否建立成功，结果如下

 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
(7 rows)

接下来插入几条记录

INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00);
INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00);
INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);

这时的结果为

 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
(10 rows)

然后创建表DEPARTMENT

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

插入三条记录

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (1, 'IT Billing', 1 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (2, 'Engineering', 2 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (3, 'Finance', 7 );

这时DEPARTMENT表中的记录为

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

CROSS JOIN

交叉连接（CROSS JOIN），把第一个表中的每一行和第二个表中的每一行进行匹配，如果两个输入表分别有x和有行，则结果有x*y行。

提示：由于CROSS JOIN可能会产生很大的表，为安全起见还是需要在合适的时候去使用。

基础语法如下：

SELECT ... FROM table1 CROSS JOIN table2 ...

基于上面的两个表可以写下面的连接

SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

退出的时候按q就可以。

INNER JOIN

内连接（INNER JOIN）根据连接谓词结合两个表（table1、table2）的列值来创建一个新的结果表。查询结果把table1的每一行和table2的每一行进行比较，找到满足条件的行进行匹配。

同时INNER JOIN也是默认的连接类型。

基本语法如下：

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

基于上面的表可以写下面的连接

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

LEFT/RIGHT OUTER JOIN

这里熟悉MySQL中JOIN的一般会有一个疑问，通常我们会直接使用LEFT/RIGHT JOIN来表示左右连接，这里为什么会有一个OUTER。都是SQL类型的命令为什么还会有差别？

实际上，通过查询相关资料发现，OUTER在这里添加是为了体现语句的可读性，因为从翻译角度看，左右连接是并不是官方名称。官方称作左右外连接，这个外就是用OUTER在语句中体现。

所以在使用过程中，我们可以直接忽略OUTER，熟悉MySQL的应该会直接忽略这个，毕竟操作没有任何影响，只是可读性的问题。

基本语法如下：

• 左连接

  SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

• 右连接

  SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ...

  基于上面的表可以写下面的两个连接

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

OUTER JOIN

返回table1和table2中所有行，如果任意一表中没有匹配的行，对应的列将返回NULL。

基本语法如下：

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

基于上面的表可以写下面的连接

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

UNION

UNION用于将两个或多个SELECT语句进行连接，合并输出其结果

UNION需要注意的是每个SELECT语句都必须有相同的列，列必须是相同的数据类型，每个SELECT语句的列顺序必须相同。

基本语法如下

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

UNION

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

UNION ALL

如果需要取重复值的话，可以使用UNION ALL语句

基本语法如下

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

UNION ALL

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

好咯，今天就先学到这里吧~该去刷leetcode了，万恶的leetcode😒