当前位置:   article > 正文

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

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

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/AllinToyou/article/detail/437571
推荐阅读
相关标签
  

闽ICP备14008679号