PostgreSQL -- 高级操作基础使用
1. 约束
PostgreSQL 约束用于规定表中的数据规则。
如果存在违反约束的数据行为，行为会被约束终止。
规定约束时机：
创建表时规定（通过 CREATE TABLE 语句）；
表创建之后规定（通过 ALTER TABLE 语句）。
约束确保了数据库中数据的准确性和可靠性。
约束可以是列级或表级。列级约束仅适用于列，表级约束被应用到整个表。
| 约束名 | 描述 |
|---|---|
| NOT NULL | 指示某列不能存储 NULL 值。 |
| UNIQUE | 确保某列的值都是唯一的。 |
| PRIMARY KEY | NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。 |
| FOREIGN KEY | 保证一个表中的数据匹配另一个表中的值的参照完整性。 |
| CHECK | 保证列中的值符合指定的条件。 |
| EXCLUSION | 排他约束，保证如果将任何两行的指定列或表达式使用指定操作符进行比较，至少其中一个操作符比较将会返回 false 或空值。 |
1.1 NOT NULL 约束
- 默认情况下，列可以保存为 NULL 值。
- NULL 与没有数据是不一样的，NULL 代表着未知的数据。
- 下面创建了一张新表叫 COMPANY，添加了 5 个字段，其中三个 ID，NAME，AGE 设置不接受空置：
pgtestdb=# CREATE TABLE COMPANY(
pgtestdb(# ID INT NOT NULL,
pgtestdb(# NAME TEXT NOT NULL,
pgtestdb(# AGE INT NOT NULL,
pgtestdb(# ADDRESS CHAR(50),
pgtestdb(# SALARY REAL,
pgtestdb(# PRIMARY KEY(ID)
pgtestdb(# );
CREATE TABLE1.2 UNIQUE 约束
- UNIQUE 约束可以设置列是唯一的，避免同一列出现重复值。
- 下面创建了一张新表叫 COMPANY3，添加了 5 个字段，其中 AGE 设置为 UNIQUE，因此你不能添加两条有相同年龄的记录：
pgtestdb=# CREATE TABLE COMPANY3(
pgtestdb(# ID INT NOT NULL,
pgtestdb(# NAME TEXT NOT NULL,
pgtestdb(# AGE INT NOT NULL,
pgtestdb(# SALARY REAL DEFAULT 50000.00,
pgtestdb(# ADDRESS CHAR(50),
pgtestdb(# PRIMARY KEY(ID),
pgtestdb(# UNIQUE(AGE)
pgtestdb(# );
CREATE TABLE1.3 PRIMARY KEY 约束
- PRIMARY KEY 称为主键，是数据表中每一条记录的唯一标识。
- 设置 UNIQUE 的列可能有多个，但是一张表只有一列可以设置 PRIMARY KEY。
- 我们可以使用主键来引用表中的行，也可以通过把主键设置为其他表的外键，来创建表之间的关系。
- 主键是非空约束和唯一约束的组合。
- 一个表只能有一个主键，它可以由一个或多个字段组成，当多个字段作为主键，它们被称为复合键。
- 如果一个表在任何字段上定义了一个主键，那么在这些字段上不能有两个记录具有相同的值。
1.4 FOREIGN KEY 约束
- FOREIGN KEY 即外键约束，指定列(或一组列)中的值必须匹配另一个表的某一行中出现的值。
- 通常一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)，即维护了两个相关表之间的引用完整性。
- 下面创建了一张 COMPANY6 表，并添加了5个字段：
pgtestdb=# CREATE TABLE COMPANY6(
pgtestdb(# ID INT NOT NULL,
pgtestdb(# NAME TEXT NOT NULL,
pgtestdb(# AGE INT NOT NULL,
pgtestdb(# ADDRESS CHAR(50),
pgtestdb(# SALARY REAL,
pgtestdb(# PRIMARY KEY(ID)
pgtestdb(# );
CREATE TABLE- 下面创建一张 DEPARTMENT1 表，并添加 3 个字段，EMP_ID 就是外键，参照 COMPANY6 的 ID：
pgtestdb=# CREATE TABLE DEPARTMENT1(
pgtestdb(# ID INT NOT NULL,
pgtestdb(# DEPT CHAR(50) NOT NULL,
pgtestdb(# EMP_ID INT REFERENCES COMPANY6(ID)
pgtestdb(# );
CREATE TABLE1.5 CHECK 约束
- CHECK 约束保证列中的所有值满足某一条件，即对输入一条记录要进行检查。
- 如果条件值为 false，则记录违反了约束，且不能输入到表。
- 下面建一个新的表 COMPANY5，增加了 5 个字段。在这里，我们为 SALARY 列添加 CHECK，所以工资不能为零：
pgtestdb=# CREATE TABLE COMPANY5(
pgtestdb(# ID INT NOT NULL,
pgtestdb(# NAME TEXT NOT NULL,
pgtestdb(# AGE INT NOT NULL,
pgtestdb(# ADDRESS CHAR(50),
pgtestdb(# SALARY REAL CHECK(SALARY >0),
pgtestdb(# PRIMARY KEY(ID)
pgtestdb(# );
CREATE TABLE1.6 EXCLUSION 约束
- EXCLUSION 约束确保如果使用指定的运算符在指定列或表达式上比较任意两行，至少其中一个运算符比较将返回 false 或 null。
- 例如当我们表中某两列值要求是一对一的，即其中一列的值确定后，另一列的值也确定下来了，则可以使用exclude约束。
- 语法：
CREATE TABLE circles(
c circle,
EXCLUDE USING gist (c WITH &&)
);- 增加一个排他约束将在约束声明所指定的类型上自动创建索引。
- 使用 exclude 约束前需要先创建 btree_gist 扩展，原因后面说明。
pgtestdb=# CREATE EXTENSION btree_gist;
错误: 无法打开扩展控制文件 "/usr/pgsql-13/share/extension/btree_gist.control": 没有那个文件或目录- 百度一番无果，我这是个阉割版的 PostgreSQL，据说一般下载的都自带，没找到下载的地方；
- 后续教程：EXCLUDE
1.7 删除约束
删除约束必须知道约束名称：
已经知道名称来删除约束很简单；
如果不知道名称，则需要找到系统生成的名称，使用
\d 表名可以找到这些信息。语法：
ALTER TABLE table_name DROP CONSTRAINT some_name;- 删除 company 的主键：
pgtestdb=# \d company
数据表 "public.company"
栏位 | 类型 | 校对规则 | 可空的 | 预设
---------+---------------+----------+----------+------
id | integer | | not null |
name | text | | not null |
age | integer | | not null |
address | character(50) | | |
salary | real | | |
索引：
"company_pkey" PRIMARY KEY, btree (id)
pgtestdb=# ALTER TABLE COMPANY DROP CONSTRAINT company_pkey;
ALTER TABLE
pgtestdb=# \d company
数据表 "public.company"
栏位 | 类型 | 校对规则 | 可空的 | 预设
---------+---------------+----------+----------+------
id | integer | | not null |
name | text | | not null |
age | integer | | not null |
address | character(50) | | |
salary | real- 其中，查询表时，结果中
"company_pkey" PRIMARY KEY, btree (id)，company_pkey就为语法中的some_name。
2. 连接(JOIN)
- JOIN 子句基于这些表之间的共同字段把来自多个表的行结合起来。
| 连接类型 | 描述 |
|---|---|
| CROSS JOIN | 交叉连接 |
| INNER JOIN | 内连接 |
| LEFT OUTER JOIN | 左外连接 |
| RIGHT OUTER JOIN | 右外连接 |
| FULL OUTER JOIN | 全外连接 |
2.1 创建表
- 创建 COMPANY 表：
pgtestdb=# CREATE TABLE COMPANY(
pgtestdb(# ID INT NOT NULL,
pgtestdb(# NAME TEXT NOT NULL,
pgtestdb(# AGE INT NOT NULL,
pgtestdb(# ADDRESS CHAR(50),
pgtestdb(# SALARY REAL,
pgtestdb(# PRIMARY KEY(ID)
pgtestdb(# );
CREATE TABLE- 插入数据：
pgtestdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
pgtestdb-# VALUES (1, 'Paul', 32, 'California', 20000.00 );
INSERT 0 1
pgtestdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
pgtestdb-# VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
INSERT 0 1
pgtestdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
pgtestdb-# VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
INSERT 0 1
pgtestdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
pgtestdb-# VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
INSERT 0 1
pgtestdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
pgtestdb-# VALUES (5, 'David', 27, 'Texas', 85000.00 );
INSERT 0 1
pgtestdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
pgtestdb-# VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );
INSERT 0 1
pgtestdb=# INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );
INSERT 0 1
pgtestdb=# INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00);
INSERT 0 1
pgtestdb=# INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00);
INSERT 0 1
pgtestdb=# INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);
INSERT 0 1- 查看表 COMPANY:
pgtestdb=# 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
8 | Paul | 24 | Houston | 20000
9 | James | 44 | Norway | 5000
10 | James | 45 | Texas | 5000
(10 行记录)- 创建 DEPARTMENT 表，添加 3 个字段：
pgtestdb=# CREATE TABLE DEPARTMENT(
pgtestdb(# ID INT NOT NULL,
pgtestdb(# DEPT CHAR(50) NOT NULL,
pgtestdb(# EMP_ID INT NOT NULL,
pgtestdb(# PRIMARY KEY(ID)
pgtestdb(# );
CREATE TABLE- 添加数据：
pgtestdb=# INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (1, 'IT Billing', 1 );
INSERT 0 1
pgtestdb=# INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (2, 'Engineering', 2 );
INSERT 0 1
pgtestdb=# INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (3, 'Finance', 7 );
INSERT 0 1- 查看表 DEPARTMENT：
pgtestdb=# SELECT * FROM DEPARTMENT;
id | dept | emp_id
----+----------------------------------------------------+--------
1 | IT Billing | 1
2 | Engineering | 2
3 | Finance | 7
(3 行记录)2.2 交叉连接（CROSS JOIN）
- 把第一个表的每一行与第二个表的每一行进行匹配；
- 如果两个输入表分别有 x 和 y 行，则结果表有 x/*y 行。
- 基础语法：
SELECT ... FROM table1 CROSS JOIN table2 ...- 实例：
pgtestdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
emp_id | name | dept
--------+-------+----------------------------------------------------
1 | Paul | IT Billing
1 | Allen | IT Billing
1 | Teddy | IT Billing
1 | Mark | IT Billing
1 | David | IT Billing
1 | Kim | IT Billing
1 | James | IT Billing
1 | Paul | IT Billing
1 | James | IT Billing
1 | James | IT Billing
2 | Paul | Engineering
2 | Allen | Engineering
2 | Teddy | Engineering
2 | Mark | Engineering
2 | David | Engineering
2 | Kim | Engineering
2 | James | Engineering
2 | Paul | Engineering
2 | James | Engineering
2 | James | Engineering
7 | Paul | Finance
7 | Allen | Finance
7 | Teddy | Finance
7 | Mark | Finance
7 | David | Finance
7 | Kim | Finance
7 | James | Finance
7 | Paul | Finance
7 | James | Finance
7 | James | Finance
(30 行记录)2.3 内连接（INNER JOIN）
根据连接谓词结合两个表（table1 和 table2）的列值来创建一个新的结果表。
*
查询会把 table1 中的每一行与 table2 中的每一行进行比较，找到所有满足连接谓词的行的匹配对。
*
当满足连接谓词时，A 和 B 行的每个匹配对的列值会合并成一个结果行。
*
内连接（INNER JOIN）是最常见的连接类型，是默认的连接类型。
语法：
SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field;- 基于上面的表，写一个内连接，如下所示：
pgtestdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
emp_id | name | dept
--------+-------+----------------------------------------------------
1 | Paul | IT Billing
2 | Allen | Engineering
7 | James | Finance
(3 行记录)2.4 左外连接（ LEFT OUTER JOIN ）
外部连接是内部连接的扩展。
*
左外连接：
- 首先执行一个内连接。
- 然后，对于表 T1 中不满足表 T2 中连接条件的每一行添加一个连接行，其中 T2 的列中有 null 值也会添加一个连接行。
- 因此，连接的表在 T1 中每一行至少有一行。
语法：
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...- 基于上面两张表，写个左外连接，如下：
pgtestdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
emp_id | name | dept
--------+-------+----------------------------------------------------
1 | Paul | IT Billing
2 | Allen | Engineering
7 | James | Finance
| James |
| David |
| Paul |
| Kim |
| Mark |
| Teddy |
| James |
(10 行记录)2.5 右外连接（ RIGHT OUTER JOIN ）
首先，执行内部连接。
*
然后，对于表 T2 中不满足表 T1 中连接条件的每一行添加一个连接行，其中 T1 列中的值为空也会添加一个连接行。
*
这与左联接相反。
*
对于T2中的每一行，结果表总是有一行。
语法：
SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ...- 基于上面两张表，建立一个右外连接：
pgtestdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY RIGHT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
emp_id | name | dept
--------+-------+----------------------------------------------------
1 | Paul | IT Billing
2 | Allen | Engineering
7 | James | Finance
(3 行记录)2.6 外连接（FULL OUTER JOIN）
首先，执行内部连接。
*
然后，对于表 T1 中不满足表 T2 中任何行连接条件的每一行，如果 T2 的列中有 null 值也会添加一个到结果中。
*
此外，对于 T2 中不满足与 T1 中的任何行连接条件的每一行，将会添加 T1 列中包含 null 值的到结果中。
语法：
SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...- 实例：
pgtestdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY FULL OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
emp_id | name | dept
--------+-------+----------------------------------------------------
1 | Paul | IT Billing
2 | Allen | Engineering
7 | James | Finance
| James |
| David |
| Paul |
| Kim |
| Mark |
| Teddy |
| James |
(10 行记录)总结：
- 外连接：获得两表中的全部数据；
- 左连接：返回包括左表中的所有记录和右表中连接字段相等的记录；
- 右连接：返回包括右表中的所有记录和左表中连接字段相等的记录。
3. UNION 操作符
- UNION 操作符合并两个或多个 SELECT 语句的结果。
- UNION 内部的每个 SELECT 语句必须拥有相同数量的列。
- 列也必须拥有相似的数据类型。
- 同时，每个 SELECT 语句中的列的顺序必须相同。
- 语法：
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]3.1 创建表
- COMPANY 表：
pgtestdb=# CREATE TABLE COMPANY(
pgtestdb(# ID INT PRIMARY KEY NOT NULL,
pgtestdb(# NAME TEXT NOT NULL,
pgtestdb(# AGE INT NOT NULL,
pgtestdb(# ADDRESS CHAR(50),
pgtestdb(# SALARY REAL
pgtestdb(# );
CREATE TABLE
pgtestdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
pgtestdb-# VALUES (1, 'Paul', 32, 'California', 20000.00 );
INSERT 0 1
pgtestdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
pgtestdb-# VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
INSERT 0 1
pgtestdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
pgtestdb-# VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
INSERT 0 1
pgtestdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
pgtestdb-# VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
INSERT 0 1
pgtestdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
pgtestdb-# VALUES (5, 'David', 27, 'Texas', 85000.00 );
INSERT 0 1
pgtestdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
pgtestdb-# VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );
INSERT 0 1
pgtestdb=# INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );
INSERT 0 1
pgtestdb=# 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 行记录)- DEPARTMENT 表：
pgtestdb=# CREATE TABLE DEPARTMENT(
pgtestdb(# ID INT PRIMARY KEY NOT NULL,
pgtestdb(# DEPT CHAR(50) NOT NULL,
pgtestdb(# EMP_ID INT NOT NULL
pgtestdb(# );
CREATE TABLE
pgtestdb=# INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
pgtestdb-# VALUES (1, 'IT Billing', 1 );
INSERT 0 1
pgtestdb=# INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
pgtestdb-# VALUES (2, 'Engineering', 2 );
INSERT 0 1
pgtestdb=# INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
pgtestdb-# VALUES (3, 'Finance', 7 );
INSERT 0 1
pgtestdb=# INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
pgtestdb-# VALUES (4, 'Engineering', 3 );
INSERT 0 1
pgtestdb=# INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
pgtestdb-# VALUES (5, 'Finance', 4 );
INSERT 0 1
pgtestdb=# INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
pgtestdb-# VALUES (6, 'Engineering', 5 );
INSERT 0 1
pgtestdb=# INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
pgtestdb-# VALUES (7, 'Finance', 6 );
INSERT 0 1
pgtestdb=# 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
(7 行记录)3.2 UNION 子句
pgtestdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID
pgtestdb-# UNION
pgtestdb-# SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID
pgtestdb-# ;
emp_id | name | dept
--------+-------+----------------------------------------------------
7 | James | Finance
6 | Kim | Finance
2 | Allen | Engineering
3 | Teddy | Engineering
5 | David | Engineering
4 | Mark | Finance
1 | Paul | IT Billing
(7 行记录)3.3 UNION ALL 子句
- UNION ALL 操作符可以连接两个有重复行的 SELECT 语句。
pgtestdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID
pgtestdb-# UNION ALL
pgtestdb-# SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID
pgtestdb-# ;
emp_id | name | dept
--------+-------+----------------------------------------------------
1 | Paul | IT Billing
2 | Allen | Engineering
7 | James | Finance
3 | Teddy | Engineering
4 | Mark | Finance
5 | David | Engineering
6 | Kim | Finance
1 | Paul | IT Billing
2 | Allen | Engineering
7 | James | Finance
3 | Teddy | Engineering
4 | Mark | Finance
5 | David | Engineering
6 | Kim | Finance
(14 行记录)4. NULL 值
- NULL 值代表遗漏的未知数据。
- 默认地，表的列可以存放 NULL 值。
- 无法比较 NULL 和 0，因为它们是不等价的。
- IS NOT NULL 操作符把某一字段值不为空的记录列出来。
- IS NULL 操作符把某一字段值为空的记录列出来。
pgtestdb=# SELECT * FROM COMPANY WHERE SALARY IS NOT NULL;
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 行记录)5. 别名
- 用 SQL 重命名一张表或者一个字段的名称，这个名称就叫着该表或该字段的别名。
- 创建别名是为了让表名或列名的可读性更强。
- SQL 中 使用 AS 来创建别名。
- 表的别名：
SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];- 列的别名：
SELECT column_name AS alias_name
FROM table_name
WHERE [condition];- 和 3 UNION 中的表一样，用 C 和 D 分别表示 COMPANY 和 DEPARTMENT 表；
pgtestdb=# SELECT C.ID, C.NAME, C.AGE, D.DEPT FROM COMPANY AS C, DEPARTMENT AS D WHERE C.ID = D. EMP_ID;
id | name | age | dept
----+-------+-----+----------------------------------------------------
1 | Paul | 32 | IT Billing
2 | Allen | 25 | Engineering
7 | James | 24 | Finance
3 | Teddy | 23 | Engineering
4 | Mark | 25 | Finance
5 | David | 27 | Engineering
6 | Kim | 22 | Finance
(7 行记录)- 用 COMPANY_ID 表示 ID 列，COMPANY_NAME 表示 NAME 列，来展示列别名的用法：
pgtestdb=# SELECT C.ID AS COMPANY_ID, C.NAME AS COMPANY_NAME, C.AGE, D.DEPT FROM COMPANY AS C, DEPARTMENT AS D WHERE C.ID = D.EMP_ID;
company_id | company_name | age | dept
------------+--------------+-----+----------------------------------------------------
1 | Paul | 32 | IT Billing
2 | Allen | 25 | Engineering
7 | James | 24 | Finance
3 | Teddy | 23 | Engineering
4 | Mark | 25 | Finance
5 | David | 27 | Engineering
6 | Kim | 22 | Finance
(7 行记录)6. 触发器
触发器是数据库的回调函数，它会在指定的数据库事件(即，INSERT，UPDATE，DELETE或TRUNCATE语句)发生时自动执行/调用。
*
触发器用于验证输入数据，执行业务规则，保持审计跟踪等。
*
触发器触发情况：
- 在执行操作之前（在检查约束并尝试插入、更新或删除之前）。
- 在执行操作之后（在检查约束并插入、更新或删除完成之后）。
- 更新操作（在对一个视图进行插入、更新、删除时）。
对于操作修改的每一行，都会调用一个标记为FOR EACH ROWS的触发器。 另一方面，标记为FOR EACH STATEMENT的触发器只对任何给定的操作执行一次，而不管它修改多少行。
*
可以为同一事件定义同一类型的多个触发器，但条件是按名称按字母顺序触发。
*
当与它们相关联的表被删除时，触发器被自动删除。
*
详细内容在这里：Free AI-Hub 触发器
7. 索引
- 索引是加速搜索引擎检索数据的一种特殊表查询。
- 简单地说，索引是一个指向表中数据的指针。
- 一个数据库中的索引与一本书的索引目录是非常相似的。
- 索引有助于加快 SELECT 查询和 WHERE 子句，但它会减慢使用 UPDATE 和 INSERT 语句时的数据输入。
- 索引可以创建或删除，但不会影响数据。
- 使用 CREATE INDEX 语句创建索引，它允许命名索引，指定表及要索引的一列或多列，并指示索引是升序排列还是降序排列。
- 索引也可以是唯一的，与 UNIQUE 约束类似，在列上或列组合上防止重复条目。
7.1 创建索引
- 语法：
CREATE INDEX index_name ON table_name;| 索引类型 | 描述 | 语法 |
|---|---|---|
| 单列索引 | 只基于表的一个列上创建的索引 | CREATE INDEX index_name ON table_name (column_name); |
| 组合索引 | 基于表的多列上创建的索引 | CREATE INDEX index_name ON table_name (column1_name, column2_name); |
| 唯一索引 | 不仅是为了性能，同时也为了数据的完整性,唯一索引不允许任何重复的值插入到表中。 | CREATE UNIQUE INDEX index_name on table_name (column_name); |
| 局部索引 | 在表的子集上构建的索引；子集由一个条件表达式上定义。索引只包含满足条件的行。 | CREATE INDEX index_name on table_name (conditional_expression); |
| 隐式索引 | 在创建对象时，由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束。 |
- 在 COMPANY 表的 SALARY 列上创建索引：
pgtestdb=# CREATE INDEX salary_index ON COMPANY (SALARY);
CREATE INDEX
pgtestdb=# \d COMPANY
数据表 "public.company"
栏位 | 类型 | 校对规则 | 可空的 | 预设
---------+---------------+----------+----------+------
id | integer | | not null |
name | text | | not null |
age | integer | | not null |
address | character(50) | | |
salary | real | | |
索引：
"company_pkey" PRIMARY KEY, btree (id)
"salary_index" btree (salary)- 使用
\di命令列出数据库中所有索引：
pgtestdb=# \di
关联列表
架构模式 | 名称 | 类型 | 拥有者 | 数据表
----------+-----------------+------+----------+------------
public | company_pkey | 索引 | postgres | company
public | department_pkey | 索引 | postgres | department
public | salary_index | 索引 | postgres | company
(3 行记录)7.2 删除索引
- 语法：
DROP INDEX index_name;- 删除之前的索引：
pgtestdb=# \di
关联列表
架构模式 | 名称 | 类型 | 拥有者 | 数据表
----------+-----------------+------+----------+------------
public | company_pkey | 索引 | postgres | company
public | department_pkey | 索引 | postgres | department
public | salary_index | 索引 | postgres | company
(3 行记录)
pgtestdb=# DROP INDEX salary_index;
DROP INDEX
pgtestdb=# \di
关联列表
架构模式 | 名称 | 类型 | 拥有者 | 数据表
----------+-----------------+------+----------+------------
public | company_pkey | 索引 | postgres | company
public | department_pkey | 索引 | postgres | department
(2 行记录)7.3 不推荐使用索引的场景
- 索引不应该使用在较小的表上；
- 索引不应使用在有频繁的大批量的更新或插入操作的表上；
- 索引不应该使用在含有大量的 NULL 值的列上；
- 索引不应该使用在频繁操作的列上。
8. ALTER TABLE 命令
- ALTER TABLE 命令用于添加，修改，删除一张已经存在表的列。
- 也可以用 ALTER TABLE 命令添加和删除约束。
| 操作 | 描述 | 语法 |
|---|---|---|
| ADD | 在一张已存在的表上添加列 | ALTER TABLE table_name ADD column_name datatype; |
| DROP COLUMN | 在一张已存在的表上删除列 | ALTER TABLE table_name DROP COLUMN column_name; |
| ALTER COLUMN … TYPE | 修改表中某列的数据类型 | ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype; |
| ALTER … NOT NULL | 给表中某列添加 NOT NULL 约束 | ALTER TABLE table_name ALTER column_name datatype NOT NULL; |
| ADD CONSTRAINT … UNIQUE | 给表中某列添加 UNIQUE 约束 | ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...); |
| ADD CONSTRAINT … CHECK | 给表中添加 CHECK 约束 | ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION); |
| ADD CONSTRAINT … PRIMARY KEY | 给表添加主键 | ALTER TABLE table_name ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...); |
| DROP CONSTRAINT | 删除约束 | ALTER TABLE table_name DROP CONSTRAINT MyUniqueConstraint; |
| DROP INDEX | MYSQL 删除约束 | ALTER TABLE table_name DROP INDEX MyUniqueConstraint; |
| DROP CONSTRAINT … | 删除主键 | ALTER TABLE table_name DROP CONSTRAINT MyPrimaryKey; |
| DROP PRIMARY KEY | MySQL 删除主键 | ALTER TABLE table_name DROP PRIMARY KEY; |
- 对 COMPANY 表添加新的列 gender：
pgtestdb=# ALTER TABLE COMPANY ADD GENDER char(1);
ALTER TABLE
pgtestdb=# SELECT * FROM COMPANY;
id | name | age | address | salary | gender
----+-------+-----+----------------------------------------------------+--------+--------
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 行记录)- 删除添加的 gender 列：
pgtestdb=# ALTER TABLE COMPANY DROP GENDER;
ALTER TABLE
pgtestdb=# 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 行记录)9. TRUNCATE TABLE
TRUNCATE TABLE 用于删除表的数据，但不删除表结构。
*
DROP TABLE 删除表，但是这个命令会连表的结构一起删除，如果想插入数据，需要重新建立这张表。
*
TRUNCATE TABLE 与 DELETE 具有相同的效果，但是由于它实际上并不扫描表，所以速度更快。
*
RUNCATE TABLE 可以立即释放表空间，而不需要后续 VACUUM 操作，这在大型表上非常有用。
*
VACUUM 操作用于释放、再利用更新/删除行所占据的磁盘空间。
*
语法：
TRUNCATE TABLE table_name;- 使用 TRUNCATE TABLE 来清除 COMPANY 表：
pgtestdb=# TRUNCATE TABLE COMPANY;
TRUNCATE TABLE
pgtestdb=# SELECT * FROM COMPANY;
id | name | age | address | salary
----+------+-----+---------+--------
(0 行记录)10. 视图（View）
View（视图）是一张假表，只不过是通过相关的名称存储在数据库中的一个 PostgreSQL 语句。
*
View（视图）实际上是一个以预定义的 PostgreSQL 查询形式存在的表的组合。
*
View（视图）可以包含一个表的所有行或从一个或多个表选定行。
*
View（视图）可以从一个或多个表创建，这取决于要创建视图的 PostgreSQL 查询。
*
View（视图）是一种虚拟表，允许用户实现以下几点：
- 用户或用户组认为更自然或直观查找结构数据的方式。
- 限制数据访问，用户只能看到有限的数据，而不是完整的表。
- 汇总各种表中的数据，用于生成报告。
视图是只读的，无法在视图上执行 DELETE、INSERT 或 UPDATE 语句。
*
语法：
CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];- 创建数据表 COMPANY：
pgtestdb=# CREATE TABLE COMPANY(
pgtestdb(# ID INT PRIMARY KEY NOT NULL,
pgtestdb(# NAME TEXT NOT NULL,
pgtestdb(# AGE INT NOT NULL,
pgtestdb(# ADDRESS CHAR(50),
pgtestdb(# SALARY REAL
pgtestdb(# );
CREATE TABLE
pgtestdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
pgtestdb-# VALUES (1, 'Paul', 32, 'California', 20000.00 );
INSERT 0 1
pgtestdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
pgtestdb-# VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
INSERT 0 1
pgtestdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
pgtestdb-# VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
INSERT 0 1
pgtestdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
pgtestdb-# VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
INSERT 0 1
pgtestdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
pgtestdb-# VALUES (5, 'David', 27, 'Texas', 85000.00 );
INSERT 0 1
pgtestdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
pgtestdb-# VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );
INSERT 0 1
pgtestdb=# INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );
INSERT 0 1
pgtestdb=# 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 行记录)- 创建视图：
pgtestdb=# CREATE VIEW COMPANY_VIEW AS
pgtestdb-# SELECT ID, NAME, AGE
pgtestdb-# FROM COMPANY;
CREATE VIEW
pgtestdb=# SELECT * FROM COMPANY_VIEW;
id | name | age
----+-------+-----
1 | Paul | 32
2 | Allen | 25
3 | Teddy | 23
4 | Mark | 25
5 | David | 27
6 | Kim | 22
7 | James | 24
(7 行记录)- 删除视图：
pgtestdb=# \d
关联列表
架构模式 | 名称 | 类型 | 拥有者
----------+--------------+--------+----------
public | company | 数据表 | postgres
public | company_view | 视图 | postgres
public | department | 数据表 | postgres
(3 行记录)
pgtestdb=# DROP VIEW company_view;
DROP VIEW
pgtestdb=# \d
关联列表
架构模式 | 名称 | 类型 | 拥有者
----------+------------+--------+----------
public | company | 数据表 | postgres
public | department | 数据表 | postgres
(2 行记录)11. TRANSACTION（事务）
TRANSACTION（事务）是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。
数据库事务通常包含了一个序列的对数据库的读/写操作。
事务包含有以下两个目的：
为数据库操作序列提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。
当事务被提交给了数据库管理系统（DBMS）：
则 DBMS 需要确保该事务中的所有操作都成功完成且其结果被永久保存在数据库中；
如果事务中有的操作没有成功完成，则事务中的所有操作都需要回滚，回到事务执行前的状态；
同时，该事务对数据库或者其他事务的执行无影响，所有的事务都好像在独立的运行。
11.1 事务的属性
- 事务具有以下四个标准属性，通常根据首字母缩写为 ACID：
| 属性 | 描述 |
|---|---|
| 原子性（Atomicity） | 事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。 |
| 一致性（Consistency） | 事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。 |
| 隔离性（Isolation） | 多个事务并发执行时，一个事务的执行不应影响其他事务的执行。 |
| 持久性（Durability） | 已被提交的事务对数据库的修改应该永久保存在数据库中。 |
11.2 栗子简要说明事务
某人要在商店使用电子货币购买100元的东西，当中至少包括两个操作：
该人账户减少 100 元。
商店账户增加100元。
支持事务的数据库管理系统就是要确保以上两个操作（整个"事务"）都能完成，或一起取消，否则就会出现 100 元平白消失或出现的情况。
11.3 事务控制
使用以下命令来控制事务：
- BEGIN TRANSACTION：开始一个事务。
- COMMIT：事务确认，或者可以使用 END TRANSACTION 命令。
- ROLLBACK：事务回滚。
事务控制命令只与 INSERT、UPDATE 和 DELETE 一起使用。他们不能在创建表或删除表时使用，因为这些操作在数据库中是自动提交的。
*
开始一个事务，并从表中删除 age = 25 的记录，最后，我们使用 ROLLBACK 命令撤消所有的更改。
pgtestdb=# BEGIN;
BEGIN
pgtestdb=*# DELETE FROM COMPANY WHERE AGE = 25;
DELETE 2
pgtestdb=*# SELECT * FROM COMPANY;
id | name | age | address | salary
----+-------+-----+----------------------------------------------------+--------
1 | Paul | 32 | California | 20000
3 | Teddy | 23 | Norway | 20000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall | 45000
7 | James | 24 | Houston | 10000
(5 行记录)
pgtestdb=*# ROLLBACK;
ROLLBACK
pgtestdb=# 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 行记录)- 现在，让开始另一个事务，从表中删除 age = 25 的记录，最后我们使用 COMMIT 命令提交所有的更改。
pgtestdb=# BEGIN;
BEGIN
pgtestdb=*# DELETE FROM COMPANY WHERE AGE = 25;
DELETE 2
pgtestdb=*# SELECT * FROM COMPANY;
id | name | age | address | salary
----+-------+-----+----------------------------------------------------+--------
1 | Paul | 32 | California | 20000
3 | Teddy | 23 | Norway | 20000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall | 45000
7 | James | 24 | Houston | 10000
(5 行记录)
pgtestdb=*# COMMIT;
COMMIT
pgtestdb=# SELECT * FROM COMPANY;
id | name | age | address | salary
----+-------+-----+----------------------------------------------------+--------
1 | Paul | 32 | California | 20000
3 | Teddy | 23 | Norway | 20000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall | 45000
7 | James | 24 | Houston | 10000
(5 行记录)12. LOCK（锁）
锁主要是为了保持数据库数据的一致性，可以阻止用户修改一行或整个表，一般用在并发较高的数据库中。
在多个用户访问数据库的时候若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。
数据库中有两种基本的锁：
排它锁（Exclusive Locks）：如果数据对象加上排它锁，则其他的事务不能对它读取和修改。
共享锁（Share Locks）：如果加上共享锁，则该数据库对象可以被其他事务读取，但不能修改。
LOCK 语句只在事务模式下工作。
LOCK 命令基础语法如下：
LOCK [ TABLE ]
name
IN
lock_mode- name：要锁定的现有表的名称（可选模式限定）。如果只在表名之前指定，则只锁定该表。如果未指定，则锁定该表及其所有子表（如果有）。
- lock_mode：锁定模式指定该锁与哪个锁冲突。如果没有指定锁定模式，则使用限制最大的访问独占模式。
- 一旦获得了锁，锁将在当前事务的其余时间保持。没有解锁表命令；锁总是在事务结束时释放。
- 死锁：当两个事务彼此等待对方完成其操作时，可能会发生死锁。
- 咨询锁：创建具有应用程序定义含义的锁的方法，这些被称为咨询锁。
13. 子查询
子查询或称为内部查询、嵌套查询，指的是在 PostgreSQL 查询中的 WHERE 子句中嵌入查询语句。
一个 SELECT 语句的查询结果能够作为另一个语句的输入值。
子查询可以与 SELECT、INSERT、UPDATE 和 DELETE 语句一起使用，并可使用运算符如 =、<、>、>=、<=、IN、BETWEEN 等。
以下是子查询必须遵循的几个规则：
子查询必须用括号括起来。
子查询在 SELECT 子句中只能有一个列，除非在主查询中有多列，与子查询的所选列进行比较。
ORDER BY 不能用在子查询中，虽然主查询可以使用 ORDER BY。可以在子查询中使用 GROUP BY，功能与 ORDER BY 相同。
子查询返回多于一行，只能与多值运算符一起使用，如 IN 运算符。
BETWEEN 运算符不能与子查询一起使用，但是，BETWEEN 可在子查询内使用。
13.1 SELECT 语句中的子查询使用
- 子查询通常与 SELECT 语句一起使用。基本语法如下：
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])- 使用子查询：
pgtestdb=# SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY WHERE SALARY > 45000);
id | name | age | address | salary
----+-------+-----+----------------------------------------------------+--------
5 | David | 27 | Texas | 85000
4 | Mark | 25 | Rich-Mond | 65000
(2 行记录)13.2 INSERT 语句中的子查询使用
- 子查询也可以与 INSERT 语句一起使用。
- INSERT 语句使用子查询返回的数据插入到另一个表中。
- 在子查询中所选择的数据可以用任何字符、日期或数字函数修改。
- 基本语法：
INSERT INTO table_name [ (column1 [, column2 ]) ]
SELECT [ *|column1 [, column2 ] ]
FROM table1 [, table2 ]
[ WHERE VALUE OPERATOR ]- 假设 COMPANY_COPY 的结构与 COMPANY 表相似，且可使用相同的 CREATE TABLE 进行创建，只是表名改为 COMPANY_COPY。现在把整个 COMPANY 表复制到 COMPANY_COPY，语法如下：
pgtestdb=# CREATE TABLE COMPANY_COPY(
pgtestdb(# ID INT PRIMARY KEY NOT NULL,
pgtestdb(# NAME TEXT NOT NULL,
pgtestdb(# AGE INT NOT NULL,
pgtestdb(# ADDRESS CHAR(50),
pgtestdb(# SALARY REAL
pgtestdb(# );
CREATE TABLE
pgtestdb=# SELECT * FROM COMPANY_COPY;
id | name | age | address | salary
----+------+-----+---------+--------
(0 行记录)
pgtestdb=# INSERT INTO COMPANY_COPY SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY);
INSERT 0 7
pgtestdb=# SELECT * FROM COMPANY_COPY;
id | name | age | address | salary
----+-------+-----+----------------------------------------------------+--------
1 | Paul | 32 | California | 20000
3 | Teddy | 23 | Norway | 20000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall | 45000
7 | James | 24 | Houston | 10000
2 | Allen | 25 | Texas | 15000
4 | Mark | 25 | Rich-Mond | 65000
(7 行记录)13.3 UPDATE 语句中的子查询使用
- 子查询可以与 UPDATE 语句结合使用。
- 当通过 UPDATE 语句使用子查询时，表中单个或多个列被更新。
- 语法：
UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]- 假设，我们有 COMPANY_COPY 表，是 COMPANY 表的备份。
- 下面的实例把 COMPANY_COPY 表中所有 AGE 大于 27 的客户的 SALARY 更新为原来的 0.50 倍：
pgtestdb=# UPDATE COMPANY_COPY SET SALARY = SALARY * 0.50 WHERE AGE IN (SELECT AGE FROM COMPANY WHERE AGE >= 27);
UPDATE 2
pgtestdb=# SELECT * FROM COMPANY_COPY;
id | name | age | address | salary
----+-------+-----+----------------------------------------------------+--------
3 | Teddy | 23 | Norway | 20000
6 | Kim | 22 | South-Hall | 45000
7 | James | 24 | Houston | 10000
2 | Allen | 25 | Texas | 15000
4 | Mark | 25 | Rich-Mond | 65000
1 | Paul | 32 | California | 10000
5 | David | 27 | Texas | 42500
(7 行记录)13.4 DELETE 语句中的子查询使用
- 子查询可以与 DELETE 语句结合使用，就像上面提到的其他语句一样。
- 语法：
DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]- 假设，我们有 COMPANY_COPY 表，是 COMPANY 表的备份。
- 下面的实例删除 COMPANY_COPY 表中所有 AGE 大于或等于 27 的客户记录：
pgtestdb=# DELETE FROM COMPANY_COPY WHERE AGE IN (SELECT AGE FROM COMPANY WHERE AGE >= 27);
DELETE 2
pgtestdb=# SELECT * FROM COMPANY_COPY;
id | name | age | address | salary
----+-------+-----+----------------------------------------------------+--------
3 | Teddy | 23 | Norway | 20000
6 | Kim | 22 | South-Hall | 45000
7 | James | 24 | Houston | 10000
2 | Allen | 25 | Texas | 15000
4 | Mark | 25 | Rich-Mond | 65000
(5 行记录)14. AUTO INCREMENT（自动增长）
- AUTO INCREMENT（自动增长） 会在新记录插入表中时生成一个唯一的数字。
- PostgreSQL 使用序列来标识字段的自增长，数据类型有 smallserial、serial 和 bigserial 。
- PostgreSQL 使用序列来标识字段的自增长：
CREATE TABLE runoob
(
id serial NOT NULL,
alttext text,
imgurl text
)| 伪类型 | 存储大小 | 范围 |
|---|---|---|
| SMALLSERIAL | 2字节 | 1 到 32,767 |
| SERIAL | 4字节 | 1 到 2,147,483,647 |
| BIGSERIAL | 8字节 | 1 到 922,337,2036,854,775,807 |
- 假定我们要创建一张 COMPANY 表，创建下面几个字段，并插入数据：
pgtestdb=# CREATE TABLE COMPANY(
pgtestdb(# ID SERIAL PRIMARY KEY,
pgtestdb(# NAME TEXT NOT NULL,
pgtestdb(# AGE INT NOT NULL,
pgtestdb(# ADDRESS CHAR(50),
pgtestdb(# SALARY REAL
pgtestdb(# );
CREATE TABLE
pgtestdb=# INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
pgtestdb-# VALUES ( 'Paul', 32, 'California', 20000.00 );
INSERT 0 1
pgtestdb=# INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
pgtestdb-# VALUES ('Allen', 25, 'Texas', 15000.00 );
INSERT 0 1
pgtestdb=# INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
pgtestdb-# VALUES ('Teddy', 23, 'Norway', 20000.00 );
INSERT 0 1
pgtestdb=# INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
pgtestdb-# VALUES ( 'Mark', 25, 'Rich-Mond ', 65000.00 );
INSERT 0 1
pgtestdb=# INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
pgtestdb-# VALUES ( 'David', 27, 'Texas', 85000.00 );
INSERT 0 1
pgtestdb=# INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
pgtestdb-# VALUES ( 'Kim', 22, 'South-Hall', 45000.00 );
INSERT 0 1
pgtestdb=# INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
pgtestdb-# VALUES ( 'James', 24, 'Houston', 10000.00 );
INSERT 0 1
pgtestdb=# 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 行记录)15. PRIVILEGES（权限）
无论何时创建数据库对象，都会为其分配一个所有者，所有者通常是执行 create 语句的人。
对于大多数类型的对象，初始状态是只有所有者(或超级用户)才能修改或删除对象。
要允许其他角色或用户使用它，必须为该用户设置权限。
在 PostgreSQL 中，权限分为以下几种：
SELECT;
INSERT;
UPDATE;
DELETE;
TRUNCATE;
REFERENCES;
TRIGGER;
CREATE;
CONNECT;
TEMPORARY;
EXECUTE;
USAGE;
根据对象的类型(表、函数等)，将指定权限应用于该对象。
要向用户分配权限，可以使用 GRANT 命令。
语法：
GRANT privilege [, ...]
ON object [, ...]
TO { PUBLIC | GROUP group | username }
- privilege − 值可以为：SELECT，INSERT，UPDATE，DELETE， RULE，ALL。
- object − 要授予访问权限的对象名称。可能的对象有： table， view，sequence。
- PUBLIC − 表示所有用户。
- GROUP group − 为用户组授予权限。
- username − 要授予权限的用户名。PUBLIC 是代表所有用户的简短形式。- 使用 REVOKE 命令取消权限，REVOKE 语法：
REVOKE privilege [, ...]
ON object [, ...]
FROM { PUBLIC | GROUP groupname | username }- 创建一个用户：
pgtestdb=# CREATE USER test_privileges WITH PASSWORD '123456';
CREATE ROLE- 现在给用户 “test_privileges” 分配权限：
pgtestdb=# GRANT ALL ON COMPANY TO test_privileges;
GRANT- 撤销用户 “test_privileges” 的权限：
pgtestdb=# REVOKE ALL ON COMPANY FROM test_privileges;
REVOKE- 删除用户 “test_privileges” ：
pgtestdb=# DROP USER test_privileges;
DROP ROLE版权说明 : 本文为转载文章, 版权归原作者所有 版权申明
原文链接 : https://blog.csdn.net/S_numb/article/details/120655213
内容来源于网络，如有侵权，请联系作者删除！
相关文章
热门标签
更多最新文章
更多- 浏览(999) 发布于 5个月前
- 浏览(924) 发布于 5个月前
- 浏览(965) 发布于 5个月前
- 浏览(801) 发布于 5个月前
- 浏览(800) 发布于 5个月前