PostgreSQL（二）数据库操作和索引

数据库操作

创建数据库

• CREATE DATABASE 语句

  • CREATE DATABASE dbname；

• createdb命令，是一个 SQL 命令 CREATE DATABASE 的封装

  • createdb [option...] [dbname [description]]，option参数可选项...

选择数据库

• l，显示所有数据库

• c + 数据库名，进入某个数据库，如 c test

删除数据库

• DROP DATABASE 语句

  • DROP DATABASE [ IF EXISTS ] dbname;

    • IF EXISTS：如果数据库不存在则发出提示信息，而不是错误信息

• dropdb命令

  • dropdb [connection-option...] [option...] dbname，options参数可选项...

模式（SCHEMA）

PostgreSQL 模式（SCHEMA）可以看着是一个表的集合。

一个模式可以包含视图、索引、据类型、函数和操作符等。

相同的对象名称可以被用于不同的模式中而不会出现冲突，例如 schema1 和 myschema 都可以包含名为 mytable 的表。

使用模式的优势：

• 允许多个用户使用一个数据库并且不会互相干扰。

• 将数据库对象组织成逻辑组以便更容易管理。

• 第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突。

模式类似于操作系统层的目录，但是模式不能嵌套

• 创建模式：CREATE SCHEMA schema_name;

• 在模式下创建一个表格 : CREATE table schema_name.table_name(...);

• 删除一个为空的模式：DROP SCHEMA schema_name;

• 删除一个模式以及其中包含的所有对象：DROP SCHEMA myschema CASCADE;

表格操作

创建表格

CREATE TABLE table_name(
   column1 约束 类型,
   column2 约束 类型,
   ...
);

• d，查看当前库的所有表格

• d table_name，查看表结构

常用的约束

• NOT NULL：指示某列不能存储 NULL 值。

• UNIQUE：确保某列的值都是唯一的。

• PRIMARY Key：NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。。

• FOREIGN Key： 保证一个表中的数据匹配另一个表中的值的参照完整性。

• CHECK： 保证列中的值符合指定的条件。

• EXCLUSION ：排他约束，保证如果将任何两行的指定列或表达式使用指定操作符进行比较，至少其中一个操作符比较将会返回 false 或空值。

删除表格

DROP , TRUNCATE , DELETE

表格数据操作

• 新增：INSERT INTO，插入默认值使用 default

• 删除：DELETE

• 查询：SELECT

  • 运算符：+ - * / %，= != > <=，AND ,OR , NOT (NOT EXISTS, NOT BETWEEN, NOT IN, NOT NULL)

  • 表达式：avg() sum() count()

  • SELECT col FROM table_name WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT

    • LIKE 模糊查询

    • IS NULL, IS NOT NULL

    • DISTINCT 去重

  • WITH 子句，WITH 子句是在多次执行子查询时特别有用，允许我们在查询中通过它的名称(可能是多次)引用它。WITH 子句在使用前必须先定义

  定义：WITH with子句名称 AS (SELECT/INSERT INTO/UPDATE...)
  使用：SELECT * FROM with子句名称

  可以在 WITH 中使用数据 INSERT, UPDATE 或 DELETE 语句，允许在同一个查询中执行多个不 同的操作。

  • 多表查询

    • CROSS JOIN ：交叉连接，交叉连接（CROSS JOIN）把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 行，则结果表有 x*y 行。

      SELECT ... FROM table1 CROSS JOIN table2 ...

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

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

    • LEFT OUTER JOIN：左外连接

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

    • RIGHT OUTER JOIN：右外连接

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

    • FULL OUTER JOIN：全外连接

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

  • UNION 合并两个或多个 SELECT 语句的结果集

    SELECT ...
        UNION
    SELECT ...

    • UNION ALL 操作符可以连接两个有重复行的 SELECT 语句，默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

• 修改：UPDATE

索引

PostgreSQL提供了几种索引类型：B-tree，Hash，GiST，SP-GiST，GIN和BRIN。每个索引类型使用不同的算法，适合不同种类的查询。默认情况下，CREATE INDEX命令创建B-tree索引，这符合最常见的情况

• 单列索引

CREATE INDEX index_name ON table_name (column_name);

• 组合索引

CREATE INDEX index_name ON table_name (column1_name, column2_name);

• 唯一索引：唯一索引不允许任何重复的值插入到表中

CREATE INDEX index_name ON table_name (column1_name, column2_name);

• 局部索引：在表的子集上构建的索引；子集由一个条件表达式上定义。索引只包含满足条件的行

CREATE INDEX index_name on table_name (conditional_expression);

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

• d table_name：查看表的所有索引

• DEOP INDEX index_name：删除索引