序言
pg的高级语法API
参考资料:
- PostgreSQL 约束 | 菜鸟教程
- 约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。
- 约束确保了数据库中数据的准确性和可靠性。
- 约束可以是列级或表级。列级约束仅适用于列，表级约束被应用到整个表。
以下是在 PostgreSQL 中常用的约束。
- NOT NULL：指示某列不能存储 NULL 值。
- UNIQUE：确保某列的值都是唯一的。
- PRIMARY Key：NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。。
- FOREIGN Key： 保证一个表中的数据匹配另一个表中的值的参照完整性。
- CHECK： 保证列中的值符合指定的条件。
- EXCLUSION ：排他约束，保证如果将任何两行的指定列或表达式使用指定操作符进行比较，至少其中一个操作符比较将会返回 false 或空值。
默认情况下，列可以保存为 NULL 值。如果您不想某列有 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
);CREATE TABLE COMPANY4(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);CREATE TABLE COMPANY6(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
CREATE TABLE DEPARTMENT1(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT references COMPANY6(ID)
);CHECK 约束保证列中的所有值满足某一条件，即对输入一条记录要进行检查。如果条件值为 false，则记录违反了约束，且不能输入到表。
CREATE TABLE COMPANY5(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL CHECK(SALARY > 0)
);CREATE TABLE COMPANY7(
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 是用于构建和执行的索引一种类型。
您需要为每个数据库执行一次 CREATE EXTENSION btree_gist 命令，这将安装 btree_gist 扩展，它定义了对纯标量数据类型的 EXCLUDE 约束。
删除约束删除约束必须知道约束名称，已经知道名称来删除约束很简单，如果不知道名称，则需要找到系统生成的名称，使用 \d 表名 可以找到这些信息。
通用语法如下：
ALTER TABLE table_name DROP CONSTRAINT some_name;
- CROSS JOIN ：交叉连接
- INNER JOIN：内连接
- LEFT OUTER JOIN：左外连接
- RIGHT OUTER JOIN：右外连接
- FULL OUTER JOIN：全外连接
交叉连接（CROSS JOIN）把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 行，则结果表有 x*y 行。
由于交叉连接（CROSS JOIN）有可能产生非常大的表，使用时必须谨慎，只在适当的时候使用它们。
下面是 CROSS JOIN 的基础语法：
SELECT ... FROM table1 CROSS JOIN table2 ...内连接（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;外部连接是内部连接的扩展。SQL 标准定义了三种类型的外部连接: LEFT、RIGHT 和 FULL, PostgreSQL 支持所有这些。
对于左外连接，首先执行一个内连接。然后，对于表 T1 中不满足表 T2 中连接条件的每一行，其中 T2 的列中有 null 值也会添加一个连接行。因此，连接的表在 T1 中每一行至少有一行。-----即主表中的记录一定存在,附表的字段会以Null的形式出现匹配cuiyaonan2000@163.com
下面是左外连接（ LEFT OUTER JOIN ）的基础语法：
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...
首先，执行内部连接。然后，对于表T2中不满足表T1中连接条件的每一行，其中T1列中的值为空也会添加一个连接行。这与左联接相反;对于T2中的每一行，结果表总是有一行。
SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ...
首先，执行内部连接。然后，对于表 T1 中不满足表 T2 中任何行连接条件的每一行，如果 T2 的列中有 null 值也会添加一个到结果中。此外，对于 T2 中不满足与 T1 中的任何行连接条件的每一行，将会添加 T1 列中包含 null 值的到结果中。
SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]UNION ALL 操作符可以连接两个有重复行的 SELECT 语句，默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION ALL
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]在查询数据时，NULL 值可能会导致一些问题，因为一个未知的值去与其他任何值比较，结果永远是未知的。
另外无法比较 NULL 和 0，因为它们是不等价的。
注意使用如下语句
-
IS NOT NULL
-
NULL --例如 Salary = Null
SQL 中 使用 AS 来创建别名。
SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];
SELECT column_name AS alias_name
FROM table_name
WHERE [condition];PostgreSQL 触发器可以在下面几种情况下触发：
- 在执行操作之前（在检查约束并尝试插入、更新或删除之前）。
- 在执行操作之后（在检查约束并插入、更新或删除完成之后）。
- 更新操作（在对一个视图进行插入、更新、删除时）。
其它关注点:
-
触发器的 FOR EACH ROW 属性是可选的，如果选中，当操作修改时每行调用一次；相反，选中 FOR EACH STATEMENT，不管修改了多少行，每个语句标记的触发器执行一次。
-
WHEN 子句和触发器操作在引用 NEW.column-name 和 OLD.column-name 表单插入、删除或更新时可以访问每一行元素。其中 column-name 是与触发器关联的表中的列的名称。
-
如果存在 WHEN 子句，PostgreSQL 语句只会执行 WHEN 子句成立的那一行，如果没有 WHEN 子句，PostgreSQL 语句会在每一行执行。
-
BEFORE 或 AFTER 关键字决定何时执行触发器动作，决定是在关联行的插入、修改或删除之前或者之后执行触发器动作。
-
要修改的表必须存在于同一数据库中，作为触发器被附加的表或视图，且必须只使用 tablename，而不是 database.tablename。
-
当创建约束触发器时会指定约束选项。这与常规触发器相同，只是可以使用这种约束来调整触发器触发的时间。当约束触发器实现的约束被违反时，它将抛出异常。-----次处的触发器,根据操作内容分为:约束触发器的类型cuiyaonan2000@163.com
语法:
CREATE TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
-- 触发器逻辑....
];
#在这里，event_name 可以是在所提到的表 table_name 上的 INSERT、DELETE 和 UPDATE 数据库操作。您可以在表名后选择指定 FOR EACH ROW。
#以下是在 UPDATE 操作上在表的一个或多个指定列上创建触发器的语法：
CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
[
-- 触发器逻辑....
];示例
CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();
#auditlogfunc() 是 PostgreSQL 一个程序，其定义如下：
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;查询现有的触发器
#你可以把从 pg_trigger 表中把当前数据库所有触发器列举出来：
SELECT * FROM pg_trigger;
#如果，你想列举出特定表的触发器，语法如下：
SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='company';drop trigger ${trigger_name} on ${table_of_trigger_dependent};
索引有助于加快 SELECT 查询和 WHERE 子句，但它会减慢使用 UPDATE 和 INSERT 语句时的数据输入。索引可以创建或删除，但不会影响数据。
语法:
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);#现在，用 \d company 命令列出 COMPANY 表的所有索引：
\d company
#你可以使用 \di 命令列出数据库中所有索引：
\diDROP INDEX index_name;
-
在 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 ALTER 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;TRUNCATE TABLE 可以立即释放表空间，而不需要后续 VACUUM 操作，这在大型表上非常有用。
TRUNCATE TABLE table_name;
PostgreSQL 视图是只读的，因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句。但是可以在视图上创建一个触发器，当尝试 DELETE、INSERT 或 UPDATE 视图时触发，需要做的动作在触发器内容中定义。
语法:
CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];其中,如果使用了可选的 TEMP 或 TEMPORARY 关键字，则将在临时数据库中创建视图。
DROP VIEW （删除视图）DROP VIEW view_name;#事务可以使用 BEGIN TRANSACTION 命令或简单的 BEGIN 命令来启动
BEGIN;
或者
BEGIN TRANSACTION;
#COMMIT 命令是用于把事务调用的更改保存到数据库中的事务命令，即确认事务。
COMMIT;
或者
END TRANSACTION;
#ROLLBACK 命令是用于撤消尚未保存到数据库的事务命令，即回滚事务。
ROLLBACK;锁主要是为了保持数据库数据的一致性，可以阻止用户修改一行或整个表，一般用在并发较高的数据库中。
- 排它锁（Exclusive Locks）: 如果数据对象加上排它锁，则其他的事务不能对它读取和修改。
- 共享锁（Share Locks）: 如果加上共享锁，则该数据库对象可以被其他事务读取，但不能修改。
一旦获得了锁，锁将在当前事务的其余时间保持。没有解锁表命令；锁总是在事务结束时释放。
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。
-
子查询必须用括号括起来。
-
子查询在 SELECT 子句中只能有一个列，除非在主查询中有多列，与子查询的所选列进行比较。
-
ORDER BY 不能用在子查询中，虽然主查询可以使用 ORDER BY。可以在子查询中使用 GROUP BY，功能与 ORDER BY 相同。
-
子查询返回多于一行，只能与多值运算符一起使用，如 IN 运算符。
-
BETWEEN 运算符不能与子查询一起使用，但是，BETWEEN 可在子查询内使用。
使用 MySQL 设置自动增长的语句如下:
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
`runoob_id` INT UNSIGNED AUTO_INCREMENT,
`runoob_title` VARCHAR(100) NOT NULL,
`runoob_author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;PostgreSQL 使用序列来标识字段的自增长：
CREATE TABLE runoob
(
id serial NOT NULL,
alttext text,
imgurl text
)
CREATE TABLE COMPANY(
ID SERIAL PRIMARY KEY,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);SMALLSERIAL、SERIAL 和 BIGSERIAL 范围：
伪类型存储大小范围SMALLSERIAL2字节1 到 32,767SERIAL4字节1 到 2,147,483,647BIGSERIAL8字节1 到 922,337,2036,854,775,807
PostgreSQL PRIVILEGES（权限）
无论何时创建数据库对象，都会为其分配一个所有者，所有者通常是执行 create 语句的人。
在 PostgreSQL 中，权限分为以下几种：
- SELECT
- INSERT
- UPDATE
- DELETE
- TRUNCATE
- REFERENCES
- TRIGGER
- CREATE
- CONNECT
- TEMPORARY
- EXECUTE
- USAGE
授权语法
GRANT privilege [, ...]
ON object [, ...]
TO { PUBLIC | GROUP group | username }取消授权
REVOKE privilege [, ...]
ON object [, ...]
FROM { PUBLIC | GROUP groupname | username }- privilege − 值可以为：SELECT，INSERT，UPDATE，DELETE， RULE，ALL。
- object − 要授予访问权限的对象名称。可能的对象有： table， view，sequence。
- PUBLIC − 表示所有用户。
- GROUP group − 为用户组授予权限。
- username − 要授予权限的用户名。PUBLIC 是代表所有用户的简短形式。
示例:
GRANT ALL ON COMPANY TO runoob;
REVOKE ALL ON COMPANY FROM runoob;age(timestamp, timestamp)interval减去参数后的"符号化"结果，使用年和月，不只是使用天age(timestamp '2001-04-10', timestamp '1957-06-13')43 years 9 mons 27 daysage(timestamp)interval从current_date减去参数后的结果（在午夜）age(timestamp '1957-06-13')43 years 8 mons 3 daysclock_timestamp()timestamp with time zone实时时钟的当前时间戳（在语句执行时变化）current_datedate当前的日期；current_timetime with time zone当日时间；current_timestamptimestamp with time zone当前事务开始时的时间戳；date_part(text, timestamp)double precision获取子域(等效于extract)；date_part('hour', timestamp '2001-02-16 20:38:40')20date_part(text, interval)double precision获取子域(等效于extract)；date_part('month', interval '2 years 3 months')3date_trunc(text, timestamp)timestamp截断成指定的精度；date_trunc('hour', timestamp '2001-02-16 20:38:40')2001-02-16 20:00:00date_trunc(text, interval)interval截取指定的精度，date_trunc('hour', interval '2 days 3 hours 40 minutes')2 days 03:00:00extract(field from timestamp)double precision获取子域；extract(hour from timestamp '2001-02-16 20:38:40')20extract(field from interval)double precision获取子域；extract(month from interval '2 years 3 months')3isfinite(date)boolean测试是否为有穷日期(不是 +/-无穷)isfinite(date '2001-02-16')trueisfinite(timestamp)boolean测试是否为有穷时间戳(不是 +/-无穷)isfinite(timestamp '2001-02-16 21:28:30')trueisfinite(interval)boolean测试是否为有穷时间间隔isfinite(interval '4 hours')truejustify_days(interval)interval按照每月 30 天调整时间间隔justify_days(interval '35 days')1 mon 5 daysjustify_hours(interval)interval按照每天 24 小时调整时间间隔justify_hours(interval '27 hours')1 day 03:00:00justify_interval(interval)interval使用justify_days和justify_hours调整时间间隔的同时进行正负号调整justify_interval(interval '1 mon -1 hour')29 days 23:00:00localtimetime当日时间；localtimestamptimestamp当前事务开始时的时间戳；make_date(year int, month int, day int)date为年、月和日字段创建日期make_date(2013, 7, 15)2013-07-15make_interval(years int DEFAULT 0, months int DEFAULT 0, weeks int DEFAULT 0, days int DEFAULT 0, hours int DEFAULT 0, mins int DEFAULT 0, secs double precision DEFAULT 0.0)interval从年、月、周、天、小时、分钟和秒字段中创建间隔make_interval(days := 10)10 daysmake_time(hour int, min int, sec double precision)time从小时、分钟和秒字段中创建时间make_time(8, 15, 23.5)08:15:23.5make_timestamp(year int, month int, day int, hour int, min int, sec double precision)timestamp从年、月、日、小时、分钟和秒字段中创建时间戳make_timestamp(2013, 7, 15, 8, 15, 23.5)2013-07-15 08:15:23.5make_timestamptz(year int, month int, day int, hour int, min int, sec double precision, [ timezone text ])timestamp with time zone从年、月、日、小时、分钟和秒字段中创建带有时区的时间戳。 没有指定timezone时，使用当前的时区。make_timestamptz(2013, 7, 15, 8, 15, 23.5)2013-07-15 08:15:23.5+01now()timestamp with time zone当前事务开始时的时间戳；statement_timestamp()timestamp with time zone实时时钟的当前时间戳；timeofday()text与clock_timestamp相同，但结果是一个text 字符串；transaction_timestamp()timestamp with time zone当前事务开始时的时间戳；
PostgreSQL 常用函数
PostgreSQL 内置函数也称为聚合函数，用于对字符串或数字数据执行处理。
下面是所有通用 PostgreSQL 内置函数的列表：
- COUNT 函数：用于计算数据库表中的行数。
- MAX 函数：用于查询某一特定列中最大值。
- MIN 函数：用于查询某一特定列中最小值。
- AVG 函数：用于计算某一特定列中平均值。
- SUM 函数：用于计算数字列所有值的总和。
- ARRAY 函数：用于输入值(包括null)添加到数组中。
- Numeric 函数：完整列出一个 SQL 中所需的操作数的函数。
- String 函数：完整列出一个 SQL 中所需的操作字符的函数。
