PostgreSQL学习笔记-6.基础知识：ALTER、TRUNCATE 、View（视图）、TRANSACTION 事务、LOCK 锁

ALTER TABLE 命令

在 PostgreSQL 中，ALTER TABLE 命令用于添加，修改，删除一张已经存在表的列。

另外你也可以用 ALTER TABLE 命令添加和删除约束。

语法

用 ALTER TABLE 在一张已存在的表上添加列的语法如下：

ALTER TABLE table_name ADD column_name datatype;

在一张已存在的表上 DROP COLUMN（删除列），语法如下：

ALTER TABLE table_name DROP COLUMN column_name;

修改表中某列的 DATA TYPE（数据类型），语法如下：

ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;

给表中某列添加 NOT NULL 约束，语法如下：

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

给表中某列 ADD UNIQUE CONSTRAINT（ 添加 UNIQUE 约束），语法如下：

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);

给表中 ADD CHECK CONSTRAINT（添加 CHECK 约束），语法如下：

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION

给表 ADD PRIMARY KEY（添加主键），语法如下：

ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

DROP CONSTRAINT （删除约束），语法如下：

ALTER TABLE table_name
DROP CONSTRAINT MyUniqueConstraint;

如果是 MYSQL ，代码是这样：

ALTER TABLE table_name
DROP INDEX MyUniqueConstraint;

DROP PRIMARY KEY （删除主键），语法如下：

ALTER TABLE table_name
DROP CONSTRAINT MyPrimaryKey;

如果是 MYSQL ，代码是这样：

ALTER TABLE table_name
DROP PRIMARY KEY;

TRUNCATE TABLE

PostgreSQL 中 TRUNCATE TABLE 用于删除表的数据，但不删除表结构。

也可以用 DROP TABLE 删除表，但是这个命令会连表的结构一起删除，如果想插入数据，需要重新建立这张表。

TRUNCATE TABLE 与 DELETE 具有相同的效果，但是由于它实际上并不扫描表，所以速度更快。 此外，TRUNCATE TABLE 可以立即释放表空间，而不需要后续 VACUUM 操作，这在大型表上非常有用。

PostgreSQL VACUUM 操作用于释放、再利用更新/删除行所占据的磁盘空间。

语法

TRUNCATE TABLE 基础语法如下：

TRUNCATE TABLE  table_name;

View（视图）

View（视图）是一张假表，只不过是通过相关的名称存储在数据库中的一个 PostgreSQL 语句。

View（视图）实际上是一个以预定义的 PostgreSQL 查询形式存在的表的组合。

View（视图）可以包含一个表的所有行或从一个或多个表选定行。

View（视图）可以从一个或多个表创建，这取决于要创建视图的 PostgreSQL 查询。

View（视图）是一种虚拟表，允许用户实现以下几点：

• 用户或用户组认为更自然或直观查找结构数据的方式。
• 限制数据访问，用户只能看到有限的数据，而不是完整的表。
• 汇总各种表中的数据，用于生成报告。
• PostgreSQL 视图是只读的，因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句。但是可以在视图上创建一个触发器，当尝试 DELETE、INSERT 或 UPDATE 视图时触发，需要做的动作在触发器内容中定义。

CREATE VIEW（创建视图）

在 PostgreSQL 用 CREATE VIEW 语句创建视图，视图创建可以从一张表，多张表或者其他视图。

CREATE VIEW 基础语法如下：

CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

您可以在 SELECT 语句中包含多个表，这与在正常的 SQL SELECT 查询中的方式非常相似。如果使用了可选的 TEMP 或 TEMPORARY 关键字，则将在临时数据库中创建视图。

DROP VIEW （删除视图）

要删除视图，只需使用带有 view_name 的 DROP VIEW 语句。DROP VIEW 的基本语法如下：

shulanxtdb=# DROP VIEW view_name;

 

TRANSACTION 事务

TRANSACTION（事务）是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。

数据库事务通常包含了一个序列的对数据库的读/写操作。包含有以下两个目的：

为数据库操作序列提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。

• 为数据库操作序列提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
• 当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。

当事务被提交给了数据库管理系统（DBMS），则 DBMS 需要确保该事务中的所有操作都成功完成且其结果被永久保存在数据库中，如果事务中有的操作没有成功完成，则事务中的所有操作都需要回滚，回到事务执行前的状态；同时，该事务对数据库或者其他事务的执行无影响，所有的事务都好像在独立的运行。

事务的属性

事务具有以下四个标准属性，通常根据首字母缩写为 ACID：

• 原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
• 一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
• 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
• 持久性（Durability）：已被提交的事务对数据库的修改应该永久保存在数据库中。

例子

某人要在商店使用电子货币购买100元的东西，当中至少包括两个操作：

• 该人账户减少 100 元。
• 商店账户增加100元。

支持事务的数据库管理系统就是要确保以上两个操作（整个”事务”）都能完成，或一起取消，否则就会出现 100 元平白消失或出现的情况。

事务控制

使用下面的命令来控制事务：

BEGIN TRANSACTION：开始一个事务。

• COMMIT：事务确认，或者可以使用 END TRANSACTION 命令。
• ROLLBACK：事务回滚。

事务控制命令只与 INSERT、UPDATE 和 DELETE 一起使用。他们不能在创建表或删除表时使用，因为这些操作在数据库中是自动提交的。

BEGIN TRANSACTION 命令

事务可以使用 BEGIN TRANSACTION 命令或简单的 BEGIN 命令来启动。此类事务通常会持续执行下去，直到遇到下一个 COMMIT 或 ROLLBACK 命令。不过在数据库关闭或发生错误时，事务处理也会回滚。以下是启动一个事务的简单语法：

BEGIN;

或者

BEGIN TRANSACTION;

COMMIT 命令

COMMIT 命令是用于把事务调用的更改保存到数据库中的事务命令，即确认事务。

COMMIT 命令的语法如下：

COMMIT;
或者
END TRANSACTION;

ROLLBACK 命令

ROLLBACK 命令是用于撤消尚未保存到数据库的事务命令，即回滚事务。

ROLLBACK 命令的语法如下：

ROLLBACK;

实例

让我们开始一个事务，并从表中删除 age = 25 的记录，最后，我们使用 ROLLBACK 命令撤消所有的更改。

shulanxtdb=# BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
ROLLBACK;

让我们开始另一个事务，从表中删除 age = 25 的记录，最后我们使用 COMMIT 命令提交所有的更改。

shulanxtdb=# BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
COMMIT;

检查 COMPANY 表，记录已被删除。

LOCK 锁

锁主要是为了保持数据库数据的一致性，可以阻止用户修改一行或整个表，一般用在并发较高的数据库中。
在多个用户访问数据库的时候若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。
数据库中有两种基本的锁：排它锁（Exclusive Locks）和共享锁（Share Locks）。
如果数据对象加上排它锁，则其他的事务不能对它读取和修改。
如果加上共享锁，则该数据库对象可以被其他事务读取，但不能修改。

LOCK 命令语法

LOCK 命令基础语法如下：

LOCK [ TABLE ]
name
 IN
lock_mode

• name：要锁定的现有表的名称（可选模式限定）。如果只在表名之前指定，则只锁定该表。如果未指定，则锁定该表及其所有子表（如果有）。
• lock_mode：锁定模式指定该锁与哪个锁冲突。如果没有指定锁定模式，则使用限制最大的访问独占模式。可能的值是：ACCESS SHARE，ROW SHARE， ROW EXCLUSIVE， SHARE UPDATE EXCLUSIVE， SHARE，SHARE ROW EXCLUSIVE，EXCLUSIVE，ACCESS EXCLUSIVE。

一旦获得了锁，锁将在当前事务的其余时间保持。没有解锁表命令；锁总是在事务结束时释放。

死锁

当两个事务彼此等待对方完成其操作时，可能会发生死锁。尽管 PostgreSQL 可以检测它们并以回滚结束它们，但死锁仍然很不方便。为了防止应用程序遇到这个问题，请确保将应用程序设计为以相同的顺序锁定对象。

咨询锁

PostgreSQL 提供了创建具有应用程序定义含义的锁的方法。这些被称为咨询锁。由于系统不强制使用它们，所以正确使用它们取决于应用程序。咨询锁对于不适合 MVCC 模型的锁定策略非常有用。
例如，咨询锁的一个常见用途是模拟所谓”平面文件”数据管理系统中典型的悲观锁定策略。虽然存储在表中的标志可以用于相同的目的，但是通知锁更快，避免了表膨胀，并且在会话结束时由服务器自动清理。

实例

创建 COMPANY 表，数据内容如下：

shulanxtdb# select * from 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
(7 rows)

下面的示例将 shulanxtdb 数据库中的 COMPANY 表锁定为 ACCESS EXCLUSIVE 模式。
LOCK 语句只在事务模式下工作。

shulanxtdb=#BEGIN;
LOCK TABLE company1 IN ACCESS EXCLUSIVE MODE;

上面操作将得到下面结果：

LOCK TABLE

上面的消息指示表被锁定，直到事务结束，并且要完成事务，您必须回滚或提交事务。

 

参考链接：https://www.shulanxt.com/database/postgresql/postgresql-truncate-table