Postgresql数据库基本操作_postgresql的check选择

视图
================================================
创建视图

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

eg.

    CREATE VIEW COMPANY_VIEW AS
    SELECT ID, NAME, AGE
    FROM  COMPANY;

通过

    SELECT * FROM COMPANY_VIEW;    查询

*类似将表中的部分数据拿出来*

删除视图

    DROP VIEW view_name;

索引
==========================================
创建索引

    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);

不管是单列索引还是组合索引，该索引必须是在 WHERE 子句的过滤条件中使用非常频繁的列。

如果只有一列被使用到，就选择单列索引，如果有多列就使用组合索引。

**唯一索引**

使用唯一索引不仅是为了性能，同时也为了数据的完整性。唯一索引不允许任何重复的值插入到表中。基本语法如下：

    CREATE UNIQUE INDEX index_name
    on table_name (column_name);

**局部索引**

局部索引 是在表的子集上构建的索引；子集由一个条件表达式上定义。索引只包含满足条件的行。基础语法如下：

    CREATE INDEX index_name
    on table_name (conditional_expression);

**隐式索引**

隐式索引 是在创建对象时，由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束。

删除索引

    DROP INDEX index_name;

-   索引不应该使用在较小的表上。
-   索引不应该使用在有频繁的大批量的更新或插入操作的表上。
-   索引不应该使用在含有大量的 NULL 值的列上。
-   索引不应该使用在频繁操作的列上。

触发器
================================================
[PostgreSQL 触发器 | 菜鸟教程 (runoob.com)](https://www.runoob.com/postgresql/postgresql-trigger.html)
-   PostgreSQL 触发器可以在下面几种情况下触发：
    
    -   在执行操作之前（在检查约束并尝试插入、更新或删除之前）。
    -   在执行操作之后（在检查约束并插入、更新或删除完成之后）。
    -   更新操作（在对一个视图进行插入、更新、删除时）。

-   触发器的 FOR EACH ROW 属性是可选的，如果选中，当操作修改时每行调用一次；相反，选中 FOR EACH STATEMENT，不管修改了多少行，每个语句标记的触发器执行一次。

        CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
        ON table_name
        [
         -- 触发器逻辑....
        ];

以下是在 UPDATE 操作上在表的一个或多个指定列上创建触发器的语法：

    CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
    ON table_name
    [
     -- 触发器逻辑....
    ];

eg.

    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;

    CREATE OR REPLACE FUNCTION my_function_name() RETURNS TRIGGER AS $anyting$ 
    BEGIN
     -- 函数主体 
     END; 
    $anything$ LANGUAGE sql;

PostgreSQL GROUP BY 语句
======================================
**GROUP BY** 语句和 SELECT 语句一起使用，用来对相同的数据进行分组。
GROUP BY 在一个 SELECT 语句中，(if have)放在 WHRER 子句的后面，ORDER BY 子句的前面。

eg.
    SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;

PostgreSQL ORDER BY 语句
==============================
**ASC** 表示升序，**DESC** 表示降序。

PostgreSQL INSERT INTO 语句
===========================================
我们可以插入一行也可以同时插入多行。

    INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)       *指定列，不写默认全部*
    VALUES (value1, value2, value3,...valueN);

插入多行

    INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00, '2007-12-13' ), (5, 'David', 27, 'Texas', 85000.00, '2007-12-13');
中间拿逗号分隔

PostgreSQL SELECT 语句
=======
PostgreSQL SELECT 语句用于从数据库中选取数据。

结果被存储在一个结果表中，称为结果集。

    SELECT column1, column2,...columnN FROM table_name;

PostgreSQL 运算符
================
运算符是一种告诉编译器执行特定的数学或逻辑操作的符号。

PostgreSQL 运算符是一个保留关键字或字符，一般用在 <mark style="background: #FFF3A3A6;">WHERE </mark>语句中，作为过滤条件。

-   算术运算符
-   比较运算符
-   逻辑运算符
-   按位运算符

PostgreSQL 表达式
=========================
-   avg() ： 返回一个表达式的平均值
-   sum() ： 返回指定字段的总和
-   count() ： 返回查询的记录总数

        SELECT COUNT(*) AS "RECORDS" FROM COMPANY;

PostgreSQL WHERE 子句
=============================
WHERE 子句不仅可以用于 SELECT 语句中，同时也可以用于 UPDATE，DELETE 等等语句中。

    SELECT column1, column2, columnN
    FROM table_name
    WHERE [condition1]

    ### AND
    ### OR
    ### NOT NULL             SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
    ### LIKE              SELECT * FROM COMPANY WHERE NAME LIKE 'Pa%';    %为通配符， _为占位1的通配符_
    ### IN                 SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );  25-27
    ### NOT IN               SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );
    ### BETWEEN             SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;
    ### 子查询（EXIST）                SELECT AGE FROM COMPANY   WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

PostgreSQL UPDATE 语句
============================================
如果我们要更新在 PostgreSQL 数据库中的数据，我们可以用 **UPDATE** 来操作。

    UPDATE table_name
    SET column1 = value1, column2 = value2...., columnN = valueN
    WHERE [condition];

PostgreSQL DELETE 语句
=======================================
你可以使用 DELETE 语句来删除 PostgreSQL 表中的数据

    DELETE FROM table_name WHERE [condition];

PostgreSQL HAVING 子句
=======================================
HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。

    SELECT
    FROM
    WHERE
    GROUP BY
    HAVING
    ORDER BY

    SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) < 2;

PostgreSQL DISTINCT 关键字
=====================
在 PostgreSQL 中，DISTINCT 关键字与 SELECT 语句一起使用，用于去除重复记录，只获取唯一的记录。

我们平时在操作数据时，有可能出现一种情况，在一个表中有多个重复的记录，当提取这样的记录时，DISTINCT 关键字就显得特别有意义，它只获取唯一一次记录，而不是获取重复记录。

    SELECT DISTINCT name FROM COMPANY;

PostgreSQL 约束
=======
约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

-   **NOT NULL**：指示某列不能存储 NULL 值。
-   **UNIQUE**：确保某列的值都是唯一的。
-   PRIMARY Key：NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。。
-   FOREIGN Key： 保证一个表中的数据匹配另一个表中的值的参照完整性。
-   CHECK： 保证列中的值符合指定的条件。
-   EXCLUSION ：排他约束，保证如果将任何两行的指定列或表达式使用指定操作符进行比较，至少其中一个操作符比较将会返回 false 或空值。

unique：值唯一

 删除约束

    ALTER TABLE table_name DROP CONSTRAINT some_name;

PostgreSQL 连接(JOIN)
=====================================
PostgreSQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段

-   CROSS JOIN ：交叉连接
-   INNER JOIN：内连接
-   LEFT OUTER JOIN：左外连接
-   RIGHT OUTER JOIN：右外连接
-   FULL OUTER JOIN：全外连接

##交叉连接
--
交叉连接（CROSS JOIN）把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 行，则结果表有 x*y 行。（会产生非常大的表）

    SELECT ... FROM table1 CROSS JOIN table2 ...         SELECT的元素可以来自两个表的任意元素

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

当满足连接谓词时，A 和 B 行的每个匹配对的列值会合并成一个结果行。

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

eg.

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

SELECT中的是展示出来的，ON后的是条件

##左外连接
--
对于左外连接，首先执行一个内连接。然后，对于表 T1 中不满足表 T2 中连接条件的每一行，其中 T2 的列中有 null 值也会添加一个连接行。因此，连接的表在 T1 中每一行至少有一行。

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

行数以T1为准，没匹配到的相应位置上为NULL

##右外连接
---
与左外连接相反，行数以T2为准。

左右外连接对数据产生乱序 ------*如果输出的是大表*

##外连接
--
首先，执行内部连接。然后，对于表 T1 中不满足表 T2 中任何行连接条件的每一行，如果 T2 的列中有 null 值也会添加一个到结果中。此外，对于 T2 中不满足与 T1 中的任何行连接条件的每一行，将会添加 T1 列中包含 null 值的到结果中。

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

表项数是ON后的对应条件的所有表项（无论是T1或T2 都要输出），如果相应位置没有匹配到，就NULL

eg

全外连接后
SELECT A.ID, A.Name, B.Age FROM A FULL OUTER JOIN B ON A.ID = B.ID;
 

PostgreSQL UNION 操作符
==========================================
PostgreSQL UNION 操作符合并两个或多个 SELECT 语句的结果。

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会将相同的表项合并

PostgreSQL NULL 值
===========================
当创建表时，NULL 的基本语法如下：

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

##IS NOT NULL
--
    SELECT  ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NOT NULL;

##IS NULL
--
    SELECT  ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NULL;

PostgreSQL 别名
====================
我们可以用 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];

    SELECT C.ID, C.NAME, C.AGE, D.DEPT FROM COMPANY AS C, DEPARTMENT AS D WHERE  C.ID = D.EMP_ID;

PostgreSQL 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 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;

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

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

    TRUNCATE TABLE  table_name;

PostgreSQL View（视图）
=========================
View（视图）是一张假表
View（视图）可以从一个或多个表创建
视图创建可以从一张表，多张表或者其他视图。

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

多个表中选取可以用JOIN语句

删除

    DROP VIEW view_name;

PostgreSQL TRANSACTION（事务）
======
## BEGIN TRANSACTION 命令

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

    BEGIN;

    或者

    BEGIN TRANSACTION;

## COMMIT 命令

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

COMMIT 命令的语法如下：

    COMMIT;

    或者

    END TRANSACTION;

### ROLLBACK 命令

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

ROLLBACK 命令的语法如下：

    ROLLBACK;

eg

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

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

PostgreSQL 子查询
=============
[PostgreSQL 子查询 | 菜鸟教程 (runoob.com)](https://www.runoob.com/postgresql/postgresql-sub-queries.html)
指的是在 PostgreSQL 查询中的 WHERE 子句中嵌入查询语句。

子查询可以与 SELECT、INSERT、UPDATE 和 DELETE 语句一起使用，并可使用运算符如 =、<、>、>=、<=、IN、BETWEEN 等。

以下是子查询必须遵循的几个规则：

-   子查询必须用括号括起来。
    
-   子查询在 SELECT 子句中只能有一个列，除非在主查询中有多列，与子查询的所选列进行比较。
    
-   ORDER BY 不能用在子查询中，虽然主查询可以使用 ORDER BY。可以在子查询中使用 GROUP BY，功能与 ORDER BY 相同。
    
-   子查询返回多于一行，只能与多值运算符一起使用，如 IN 运算符。
    
-   BETWEEN 运算符不能与子查询一起使用，但是，BETWEEN 可在子查询内使用。

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

好多where后面跟的OPERATOR都是IN

PostgreSQL PRIVILEGES（权限）
=============================
在 PostgreSQL 中，权限分为以下几种：

-   SELECT
-   INSERT
-   UPDATE
-   DELETE
-   TRUNCATE
-   REFERENCES
-   TRIGGER
-   CREATE
-   CONNECT
-   TEMPORARY
-   EXECUTE
-   USAGE
要向用户分配权限，可以使用 GRANT 命令。

### GRANT 语法

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 }

创建一个用户eg.

    CREATE USER runoob WITH PASSWORD 'password';
    CREATE ROLE(反馈的)
信息 CREATE ROLE 表示创建了一个用户 "runoob"。

授予COMPANY表中所有对象的权限和所有操作给用户
 
     GRANT ALL ON COMPANY TO runoob;

撤销

    REVOKE ALL ON COMPANY FROM runoob;

删除用户

    DROP USER runoob;

IN的使用
=============================

主要是用于where子句中
eg.

    SELECT
     customer_id,
    rental_id,
    return_date
    FROM
    rental
    WHERE
     customer_id IN (10, 12)
    ORDER BY
    return_date DESC;

    SELECT
    first_name,
    last_name
    FROM
    customer
    WHERE
    customer_id IN (
        SELECT
            customer_id
        FROM
            rental
        WHERE
            CAST (return_date AS DATE) = '2005-05-27'
    );