PostgreSQL 高级

作者: 斐波那契额 | 来源:发表于2018-03-20 14:48 被阅读166次

PostgreSQL 高级
女优教会你的事之如何开展优化PostgreSQL之路
2022-07-18
postgresql with（CTE）查询
postgresql debug
Lesson-41 PostgreSQL新手入门
PostgreSQL(数据库)资料
PostgreSQL 12.2 数据库公开课视频及介绍(第3期)
PostgreSql导入导出
PostgreSQL 高级SQL(二) filter子句

postgres.jpg

1. 视图

在 PostgreSQL 中，视图(VIEW)是一个伪表。它不是物理表，而是作为普通表选择查询。
视图也可以表示连接的表。它可以包含表的所有行或来自一个或多个表的所选行。

视图便于用户执行以下操作：

它以自然和直观的方式构建数据，并使其易于查找。
它限制对数据的访问，使得用户只能看到有限的数据而不是完整的数据。
它归总来自各种表中的数据以生成报告。

1.1 PostgreSQL创建视图

可以使用CREATE VIEW语句来在 PostgreSQL 中创建视图。您可以从单个表，多个表以及另一个视图创建它。

语法:

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

PostgreSQL创建视图示例:

考虑一个表“EMPLOYEES”，具有以下数据。

现在，我们从“EMPLOYEES”表创建一个视图。此视图将仅包含EMPLOYEES表中的几个列：

执行以下查询语句：

CREATE VIEW current_employees AS  
SELECT NAME, ID, SALARY 
FROM EMPLOYEES;

1.2 PostgreSQL DROP 视图

使用DROP VIEW命令删除或删除视图。

语法:

DROP VIEW view_name;

要删除上面的例子中创建的视图，可执行以下SQL语句：

DROP VIEW current_employees;

2. 函数(存储过程)

PostgreSQL 函数也称为 PostgreSQL 存储过程。 PostgreSQL 函数或存储过程是存储在数据库服务器上并可以使用 SQL 界面调用的一组 SQL 和过程语句(声明，分配，循环，控制流程等)。它有助于您执行通常在数据库中的单个函数中进行多次查询和往返操作的操作。

您可以在许多语言(如SQL，PL/pgSQL，C，Python等)中创建 PostgreSQL 函数。

语法：

CREATE [OR REPLACE] FUNCTION function_name (arguments)   
RETURNS return_datatype AS $variable_name$  
  DECLARE  
    declaration;  
    [...]  
  BEGIN  
    < function_body >  
    [...]  
    RETURN { variable_name | value }  
  END; LANGUAGE plpgsql;

参数说明:

function_name：指定函数的名称。
[OR REPLACE]：是可选的，它允许您修改/替换现有函数。
RETURN：它指定要从函数返回的数据类型。它可以是基础，复合或域类型，或者也可以引用表列的类型。
function_body：function_body包含可执行部分。
plpgsql：它指定实现该函数的语言的名称。

例子:

CREATE OR REPLACE FUNCTION totalRecords ()  
RETURNS integer AS $total$  
declare  
    total integer;  
BEGIN  
   SELECT count(*) into total FROM EMPLOYEES;  
   RETURN total;  
END;  
$total$ LANGUAGE plpgsql;

# select totalRecords(); 执行

3. 触发器

PostgreSQL 触发器是一组动作或数据库回调函数，它们在指定的表上执行指定的数据库事件(即，INSERT，UPDATE，DELETE或TRUNCATE语句)时自动运行。触发器用于验证输入数据，执行业务规则，保持审计跟踪等。

触发器的重点知识

PostgreSQL 在以下情况下执行/调用触发器：在尝试操作之前(在检查约束并尝试INSERT，UPDATE或DELETE之前)。或者在操作完成后(在检查约束并且INSERT，UPDATE或DELETE完成后)。或者不是操作(在视图中INSERT，UPDATE或DELETE的情况下)
对于操作修改的每一行，都会调用一个标记为FOR EACH ROWS的触发器。另一方面，标记为FOR EACH STATEMENT的触发器只对任何给定的操作执行一次，而不管它修改多少行。
您可以为同一事件定义同一类型的多个触发器，但条件是按名称按字母顺序触发。
当与它们相关联的表被删除时，触发器被自动删除。

3.1 PostgreSQL 创建触发器

CREATE TRIGGER语句用于在 PostgreSQL 表中创建一个新的触发器。当表发生特定事件(即INSERT，UPDATE和DELETE)时，它被激活。

语法:

CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name  
ON table_name  
[  
 -- Trigger logic goes here....  
];

在这里，event_name可以是INSERT，UPDATE，DELETE和TRUNCATE数据库操作上提到的表table_name。您可以选择在表名后指定FOR EACH ROW。

下面来看看看如何在 INSERT 操作中创建触发器的语法。

CREATE  TRIGGER trigger_name AFTER INSERT ON column_name  
ON table_name  
[  
 -- Trigger logic goes here....  
];

触发器例子:

下面举个例子来演示 PostgreSQL 在 INSERT 语句之后创建触发器。在以下示例中，我们对每个记录插入到COMPANY表中进行审核(审计)。

使用以下查询创建一个名为COMPANY的表：

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

为了保存审计/审核，我们将创建一个名为 AUDIT 的新表，只要在 COMPANY 表中有一个新记录的条目，就会插入日志消息。

使用以下查询语句创建另一个表Audit：

CREATE TABLE AUDIT(  
    EMP_ID INT NOT NULL,  
    ENTRY_DATE TEXT NOT NULL  
);

在 COMPANY 表上创建触发器之前，首先创建一个名为 auditlogfunc() 的函数/过程。

执行以下查询语句来创建函数/过程：

CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$  
    BEGIN  
        INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);  
        RETURN NEW;   
    END;  
$example_table$ LANGUAGE plpgsql;

PostgreSQL触发器的使用

PostgreSQL触发器可用于以下目的：

验证输入数据。
执行业务规则。
为不同文件中新插入的行生成唯一值。
写入其他文件以进行审计跟踪。
从其他文件查询交叉引用目的。
访问系统函数。
将数据复制到不同的文件以实现数据一致性。

使用触发器的优点:

它提高了应用程序的开发速度。因为数据库存储触发器，所以您不必将触发器操作编码到每个数据库应用程序中。
全局执法业务规则。定义触发器一次，然后将其重用于使用数据库的任何应用程序。
更容易维护如果业务策略发生变化，则只需更改相应的触发程序，而不是每个应用程序。
提高客户/服务器环境的性能。所有规则在结果返回之前在服务器中运行。

4. 别名

PostgreSQL 别名(Alias) 用于为列或表提供临时名称。您可以使用 PostgreSQL 别名为列或表创建一个临时名称。

通常来说，当您执行自联接时，会创建一个临时表。

4.1 PostgreSQL 列别名

语法：

SELECT column_name AS alias_name  
FROM table_name  
conditions...  ;

参数说明:

column_name: 它指定要进行别名的列的原始名称。
alias_name: 它指定分配给列的临时名称。
table_name：它指定表的名称。
AS：这是可选的。大多数程序员将在对列名进行别名时指定AS关键字，但在别名表名时不指定。

注意：

如果 alias_name 包含空格，则必须将 alias_name 包含在引号中。
在别名列名时，可以使用空格。但是使用表名时，使用空格通常不是一个好习惯。
alias_name 仅在 SQL 语句的范围内有效。

4.2 PostgreSQL 表别名

语法：

SELECT column1, column2....  
FROM table_name AS alias_name  
conditions....  ;

参数说明：

table_name：它指定要进行别名的表的原始名称。
alias_name：它指定分配给表的临时名称。
AS：这是可选的。大多数程序员将在对列名进行别名时指定AS关键字，但在别名表名时不指定。

注意：

如果 alias_name 包含空格，则必须将 alias_name 包含在引号中。
在别名列名时，可以使用空格。但是，当您使用表名时，使用空格通常不是一个好习惯。
alias_name 仅在SQL语句的范围内有效。

5. 索引

什么是索引？

索引是用于加速从数据库检索数据的特殊查找表。数据库索引类似于书的索引(目录)。索引为出现在索引列中的每个值创建一个条目。

数据库索引的重要特点

索引使用SELECT查询和WHERE子句加速数据输出，但是会减慢使用INSERT和UPDATE语句输入的数据。
您可以在不影响数据的情况下创建或删除索引。
可以通过使用CREATE INDEX语句创建索引，指定创建索引的索引名称和表或列名称。
还可以创建一个唯一索引，类似于唯一约束，该索引防止列或列的组合上有一个索引重复的项。

5.1 PostgreSQL 创建索引

CREATE INDEX语句用于创建PostgreSQL 索引。

语法:

CREATE INDEX index_name ON table_name;

5.1 索引类型

PostgreSQL中有几种索引类型，如B-tree，Hash，GiST，SP-GiST和GIN等。每种索引类型根据不同的查询使用不同的算法。默认情况下，CREATE INDEX命令使用B树索引。

5.1.1 单列索引

如果仅在一个表列中创建索引，则将其称为单列索引。

语法：

CREATE INDEX index_name  
ON table_name (column_name);

5.1.2 多列索引

如果通过使用表的多个列创建索引，则称为多列索引。

语法：

CREATE INDEX index_name  
ON table_name (column1_name, column2_name);

5.1.3 唯一索引

创建唯一索引以获取数据的完整性并提高性能。它不允许向表中插入重复的值，或者在原来表中有相同记录的列上也不能创建索引。

语法：

CREATE UNIQUE INDEX index_name  
on table_name (column_name);

5.2 PostgreSQL 删除索引

DROP INDEX方法用于删除 PostgreSQL 中的索引。如果你放弃一个索引，那么它可以减慢或提高性能。

语法：

DROP INDEX index_name;

5.3 什么时候应该避免使用索引？

应该避免在小表上使用索引。
不要为具有频繁，大批量更新或插入操作的表创建索引。
索引不应用于包含大量 NULL 值的列。
不要在经常操作(修改)的列上创建索引。

6. 日期和时间函数

所有重要的日期和时间相关函数如下列表所示：

函数	描述
<strong style="color: red;">AGE()</strong>	减去参数
<strong style="color: red;">CURRENT DATE/TIME()</strong>	它指定当前日期和时间。
<strong style="color: red;">DATE_PART()</strong>	获取子字段(相当于提取)
<strong style="color: red;">EXTRACT()</strong>	获得子字段
<strong style="color: red;">ISFINITE()</strong>	测试有限的日期，时间和间隔(非+/-无穷大)
<strong style="color: red;">JUSTIFY</strong>	调整间隔

6.1 AGE(timestamp，timestamp)＆AGE(timestamp)：

函数	描述
<strong style="color: red;">age(timestamp, timestamp)</strong>	当使用第二个参数的时间戳形式调用时，`age()`减去参数，产生使用年数和月份的类型为“interval”的“符号”结果。
<strong style="color: red;">age(timestamp)</strong>	当仅使用时间戳作为参数调用时，`age()`从`current_date(午夜)`减去。

AGE()函数例子:

SELECT AGE(timestamp '2017-01-26', timestamp '1951-08-15');

函数AGE(timestamp)的示例
它用于产生当前年龄。

SELECT AGE(timestamp '1990-08-15');

6.2 当前DATE/TIME()

以下是返回与当前日期和时间相关的值的函数的列表。

函数	描述
<strong style="color: red;">CURRENT_DATE</strong>	提供当前日期
<strong style="color: red;">CURRENT_TIME</strong>	提供带时区的值
<strong style="color: red;">CURRENT_TIMESTAMP</strong>	提供带时区的值
<strong style="color: red;">CURRENT_TIME(precision)</strong>	可以选择使用precision参数，这将使结果在四分之一秒的范围内四舍五入到数位数。
<strong style="color: red;">CURRENT_TIMESTAMP(precision)</strong>	可以选择使用精度参数，这将使结果在四分之一秒的范围内四舍五入到数位数。
<strong style="color: red;">LOCALTIME</strong>	提供没有时区的值。
<strong style="color: red;">LOCALTIMESTAMP</strong>	提供没有时区的值。
<strong style="color: red;">LOCALTIME(precision)</strong>	可以选择使用精度参数，这将使结果在四分之一秒的范围内四舍五入到数位数。
<strong style="color: red;">LOCALTIMESTAMP(precision)</strong>	可以选择使用精度参数，这将使结果在四分之一秒的范围内四舍五入到数位数。

7. UNIONS 子句

PostgreSQL UNION子句/运算符用于组合两个或多个 SELECT 语句的结果，而不返回任何重复的行。

要使用UNION，每个 SELECT 必须具有相同的列数，相同数量的列表达式，相同的数据类型，并且具有相同的顺序，但不一定要相同。

语法：

UNION 的基本语法如下：

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

UNION

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

示例：
考虑以下两个表，COMPANY表如下：

yiibai_db=# 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

另一张表是DEPARTMENT如下：

yiibai_db=# SELECT * from DEPARTMENT;

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

现在使用SELECT语句和UNION子句连接这两个表，如下所示：

yiibai_db=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID
   UNION
     SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID;

这将产生以下结果：

UNION ALL子句

UNION ALL运算符用于组合两个SELECT语句(包括重复行)的结果。适用于UNION的相同规则也适用于UNION ALL运算符。

语法：

UNION ALL的基本语法如下：

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

UNION ALL

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

这里根据您的要求给出的条件表达式。

示例：
现在，我们在SELECT语句中加入上面提到的两个表，如下所示：

yiibai_db=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID
   UNION ALL
     SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID;

这将产生以下结果：

8. NULL值

PostgreSQL NULL是用于表示缺少值的术语。表中的NULL值是一个字段中的值，显示为空白。

具有NULL值的字段是没有值的字段。要知道一个NULL值与零值或包含空格的字段不同是非常重要的。

语法：

创建表时使用NULL的基本语法如下：

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

这里，NOT NULL表示该列应该始终接受给定数据类型的显式值。有两列不使用NOT NULL。因此这意味着这些列可以为NULL。

具有NULL值的字段是在创建记录期间留空的字段。

9. 修改表（ALTER TABLE语句）

PostgreSQL ALTER TABLE命令用于添加，删除或修改现有表中的列。您还可以使用ALTER TABLE命令在现有表上添加和删除各种约束。

语法：

使用ALTER TABLE语句在现有表中添加新列的基本语法如下：

ALTER TABLE table_name ADD column_name datatype;

现有表中ALTER TABLE到DROP COLUMN(删除某个字段)的基本语法如下：

ALTER TABLE table_name DROP COLUMN column_name;

ALTER TABLE更改表中列的DATA TYPE(修改字段类型)的基本语法如下：

ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;

ALTER TABLE向表中的列添加NOT NULL约束的基本语法如下：

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

ALTER TABLE添加唯一约束ADD UNIQUE CONSTRAINT到表中的基本语法如下：

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

ALTER TABLE将“检查约束”添加到表中的基本语法如下所示：

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

ALTER TABLE添加主键ADD PRIMARY KEY约束的基本语法如下：

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

使用ALTER TABLE从表中删除约束(DROP CONSTRAINT)的基本语法如下：

ALTER TABLE table_name
DROP CONSTRAINT MyUniqueConstraint;

使用ALTER TABLE从表中删除主键约束(DROP PRIMARY KEY)约束的基本语法如下：

ALTER TABLE table_name
DROP CONSTRAINT MyPrimaryKey;

9. 截断表（TRUNCATE TABLE语句）

PostgreSQL TRUNCATE TABLE命令用于从现有表中删除完整的数据。您也可以使用DROP TABLE命令删除完整的表，但会从数据库中删除完整的表结构，如果希望存储某些数据，则需要重新创建此表。

它和在每个表上使用DELETE语句具有相同的效果，但由于实际上并不扫描表，所以它的速度更快。此外，它会立即回收磁盘空间，而不需要后续的VACUUM操作。这在大表上是最有用的。

语法：

TRUNCATE TABLE的基本语法如下：

TRUNCATE TABLE  table_name;

10. 事务

事务是对数据库执行的工作单元。事务是以逻辑顺序完成的工作的单位或顺序，无论是用户手动的方式还是通过某种数据库程序自动执行。

10.1 事务性质

事务具有以下四个标准属性，一般是由首字母缩写词ACID简称：

原子性(Atomicity)：确保工作单位内的所有操作成功完成; 否则事务将在故障点中止，以前的操作回滚到其以前的状态。
一致性(Consistency)：确保数据库在成功提交的事务时正确更改状态。
隔离性(Isolation)：使事务能够独立运作并相互透明。
持久性(Durability)：确保在系统发生故障的情况下，提交的事务的结果或效果仍然存在。

10.2 事务控制

以下命令用于控制事务：

BEGIN TRANSACTION：开始事务。
COMMIT：保存更改，或者您可以使用END TRANSACTION命令。
ROLLBACK：回滚更改。

事务控制命令仅用于 DML命令INSERT，UPDATE和DELETE。创建表或删除它们时不能使用它们，因为这些操作会在数据库中自动提交。

10.2.1 BEGIN TRANSACTION命令：

可以使用BEGIN TRANSACTION或简单的BEGIN命令来开始事务。这样的事务通常会持续下去，直到遇到下一个COMMIT或ROLLBACK命令。但如果数据库关闭或发生错误，则事务也将ROLLBACK。

以下是启动/开始事务的简单语法：

BEGIN;

or

BEGIN TRANSACTION;

10.2.2 COMMIT命令

COMMIT命令是用于将事务调用的更改保存到数据库的事务命令。
COMMIT命令自上次的COMMIT或ROLLBACK命令后将所有事务保存到数据库。
COMMIT命令的语法如下：

COMMIT;

or

END TRANSACTION;

10.2.3 ROLLBACK命令

ROLLBACK命令是用于还原尚未保存到数据库的事务的事务命令。自上次发出COMMIT或ROLLBACK命令以来，ROLLBACK命令只能用于撤销事务。

ROLLBACK 命令的语法如下：

ROLLBACK;

11. 锁

锁或独占锁或写锁阻止用户修改行或整个表。在UPDATE和DELETE修改的行在事务的持续时间内被自动独占锁定。这将阻止其他用户更改行，直到事务被提交或回退。

用户必须等待其他用户当他们都尝试修改同一行时。如果他们修改不同的行，不需要等待。 SELECT查询不必等待。

数据库自动执行锁定。然而，在某些情况下，必须手动控制锁定。手动锁定可以通过使用LOCK命令完成。它允许指定事务的锁类型和范围。

11.1 OCK命令的语法

LOCK命令的基本语法如下：

LOCK [ TABLE ]
name
 IN
lock_mode

name：要锁定的现有表的锁名称(可选模式限定)。如果在表名之前指定了 ONLY，则仅该表被锁定如果未指定ONLY，则表及其所有后代表(如果有)被锁定。
lock_mode：锁模式指定此锁与之冲突的锁。如果未指定锁定模式，则使用最严格的访问模式ACCESS EXCLUSIVE。可能的值是：ACCESS SHARE，ROW SHARE，ROW EXCLUSIVE，SHARE UPDATE EXCLUSIVE，SHARE，SHARE ROW EXCLUSIVE，EXCLUSIVE，ACCESS EXCLUSIVE。

11.2 死锁

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

11.3 咨询锁

PostgreSQL 提供了创建具有应用程序定义含义的锁的方法。这些称为咨询锁(劝告锁,英文为：advisory locks)。由于系统不强制使用它，因此应用程序正确使用它们。咨询锁可用于锁定针对 MVCC 模型策略。

例如，咨询锁的常见用途是模拟所谓的“平面文件”数据管理系统的典型的悲观锁定策略。虽然存储在表中的标志可以用于相同的目的，但是建议锁更快，避免了表的膨胀，并且在会话结束时被服务器自动清除。

示例

考虑表 COMPANY 有以下记录：

testdb# select * from COMPANY;

以下示例在 ACCESS EXCLUSIVE 模式下将 COMPANY 表锁定在 yiibai_db 数据库中。 LOCK 语句仅在事务模式下工作：

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

PostgreSQL 上面的语句将产生以下结果：

LOCK TABLE

上述消息表示表被锁定，直到事务结束并完成事务，必须回滚或提交事务。

12. 子查询

子查询或内部查询或嵌套查询是一个 PostgreSQL 查询中的查询，它可以嵌入到WHERE子句中。子查询用于返回将在主查询中使用的数据作为进一步限制要检索的数据的条件。
子查询可以与SELECT，INSERT，UPDATE和DELETE语句以及运算符(如=，<，>，>=，<=，IN等)一起使用。

子查询必须遵循以下规则：

子查询必须括在括号中。
子查询在 SELECT 子句中只能有一列，除非主查询中有多个列用于比较其所选列的子查询。
ORDER BY 不能用于子查询，尽管主查询可以使用 ORDER BY。 GROUP BY 可用于执行与子查询中的 ORDER BY 相同的功能。
返回多行的子查询只能与多个值运算符一起使用，例如：IN，EXISTS，NOT IN，ANY / SOME，ALL 运算符。
BETWEEN 运算符不能与子查询一起使用; 但是，BETWEEN 可以在子查询中使用。

12.1 带 SELECT 语句的子查询

子查询最常用于SELECT语句。基本语法如下：

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
      (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])

12.2 带 INSERT 语句的子查询

子查询也可以用于INSERT语句。INSERT 语句使用从子查询返回的数据插入另一个表。可以使用任何字符，日期或数字函数修改子查询中选定的数据。

基本语法如下：

INSERT INTO table_name [ (column1 [, column2 ]) ]
           SELECT [ *|column1 [, column2 ]
           FROM table1 [, table2 ]
           [ WHERE VALUE OPERATOR ]

12.3 带 UPDATE 语句的子查询：

子查询可以与 UPDATE 语句一起使用。当使用具有 UPDATE 语句的子查询时，可以更新表中的单列或多列。

UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

12.4 带有 DELETE 语句的子查询：

子查询可以与DELETE语句一起使用，就像上面提到的任何其他语句一样。

基本语法如下：

DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

13. 自动递增

PostgreSQL 具有数据类型smallserial，serial和bigserial; 这些不是真正的类型，而只是在创建唯一标识符列的标志以方便使用。这些类似于一些其他数据库支持的AUTO_INCREMENT属性。

如果您希望某列具有唯一的约束或是主键，则必须使用其他数据类型进行指定。

类型名称serial用于创建整数列。类型名称bigserial创建一个bigint类型的列。如果您期望在表的使用期限内使用超过2^31个标识符，则应使用bigserial。类型名称smallserial创建一个smallint列。

语法：

SERIAL数据类型的基本用法如下：

CREATE TABLE tablename (
    colname SERIAL
);

14. 权限

在数据库中创建对象时，都会为其分配所有者。所有者通常是执行创建语句的用户。对于大多数类型的对象，初始状态是只有所有者(或超级用户)可以修改或删除对象。要允许其他角色或用户使用它，必须授予权限或权限。

PostgreSQL 中的不同类型的权限是：

SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE 和 USAGE。
根据对象的类型(表，函数等)，权限将应用于对象。要为用户分配权限，使用GRANT命令。

14.1 GRANT 的语法

GRANT 命令的基本语法如下：

GRANT privilege [, ...]
ON object [, ...]
TO { PUBLIC | GROUP group | username }

privilege 值可以是：SELECT，INSERT，UPDATE，DELETE，RULE，ALL。
object：要向其授予访问权限的对象的名称。可能的对象是：表，视图，序列
PUBLIC：表示所有用户的简短形式。
GROUP group：授予权限的组。
username：授予权限的用户的名称。 PUBLIC是表示所有用户的简短形式。

14.2 REVOKE 的语法

REVOKE命令的基本语法如下：

REVOKE privilege [, ...]
ON object [, ...]
FROM { PUBLIC | GROUP groupname | username }

privilege 值可以是：SELECT，INSERT，UPDATE，DELETE，RULE，ALL。
object: 授予访问权限的对象的名称。可能的对象是：表，视图，序列。
PUBLIC：表示所有用户的简短形式。
GROUP group：授予权限的组。
username：授予权限的用户的名称。 PUBLIC 是表示所有用户的简短形式。

PostgreSQL 高级
1. 视图在 PostgreSQL 中，视图(VIEW)是一个伪表。它不是物理表，而是作为普通表选择查询。...
女优教会你的事之如何开展优化PostgreSQL之路
@杏咲望今天我们来看看性能调优PostgreSQL。PostgreSQL是一个高级的开放源码SQL数据库。建立数...
2022-07-18
关于postgresql几个高级用法 -- 查询某个表的字段信息列表select a.attnum,a.attna...
postgresql with（CTE）查询
WITH查询是PostgreSQL支持的高级SQL特性之一，这一特性常称为CTE(Common Table Exp...
postgresql debug
postgresql architecture postgresql build postgresql debug...
Lesson-41 PostgreSQL新手入门
MAC 安装 postgresql brew install postgresql 创建postgreSql数据...
PostgreSQL(数据库)资料
About:PostgreSQL About 《PostgreSQL 源码分析系列》 PostgreSQL 源码分...
PostgreSQL 12.2 数据库公开课视频及介绍(第3期)
三、PostgreSQL 12.2企业级应用公开课(3)：流复制高级配置 1、PG流复制异步配置 2、PG主备切换...
PostgreSql导入导出
postgresql导入 postgresql导出
PostgreSQL 高级SQL(二) filter子句
本章所用到案例数据来自于上一章节，如果有想使用该数据的读者可以查看上一章节。这一章节我们想要了解的是PG聚合...

PostgreSQL 高级

1. 视图

1.1 PostgreSQL创建视图

1.2 PostgreSQL DROP 视图

2. 函数(存储过程)

3. 触发器

触发器的重点知识

3.1 PostgreSQL 创建触发器

PostgreSQL触发器的使用

4. 别名

4.1 PostgreSQL 列别名

4.2 PostgreSQL 表别名

5. 索引

数据库索引的重要特点

5.1 PostgreSQL 创建索引

5.1 索引类型

5.1.1 单列索引

5.1.2 多列索引

5.1.3 唯一索引

5.2 PostgreSQL 删除索引

5.3 什么时候应该避免使用索引？

6. 日期和时间函数

6.1 AGE(timestamp，timestamp)＆AGE(timestamp)：

6.2 当前DATE/TIME()

7. UNIONS 子句

UNION ALL子句

8. NULL值

9. 修改表（ALTER TABLE语句）

9. 截断表（TRUNCATE TABLE语句）

10. 事务

10.1 事务性质

10.2 事务控制

10.2.1 BEGIN TRANSACTION命令：

10.2.2 COMMIT命令

10.2.3 ROLLBACK命令

11. 锁

11.1 OCK命令的语法

11.2 死锁

11.3 咨询锁

12. 子查询

12.1 带 SELECT 语句的子查询

12.2 带 INSERT 语句的子查询

12.3 带 UPDATE 语句的子查询：

12.4 带有 DELETE 语句的子查询：

13. 自动递增

14. 权限

14.1 GRANT 的语法

14.2 REVOKE 的语法

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读