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：删除索引