postgreSQL 语法

命令行psql语法

• \help <command_name>: 查看某个指令的帮助
• \l: 用于查看已经存在的数据库
• \c + db_name: 切换数据库
  

postgreSQL创建数据库

• create database demo_db;在psql中使用。

• createdb [option...] dbname [description] 在C:\Program Files\PostgreSQL\11\bin>下使用。

  1. description是关于数据库的描述。

  2. option 是关于数据的参数可选项。如下：

     参数	说明
     - D tablespace	指定数据库默认的表空间
     -e	将createdb生成的命令发送到服务端
     -E encoding	指定数据库的编码
     -l locale	指定数据库的语言环境
     -T template	指定创建此数据库的模板
     --help	查看createdb的帮助
     -h host	指定服务器主机名
     -p port	指定监听端口 / socket文件
     -U username	连接数据库的用户名
     -w	忽略输入密码
     -W	连接时强制要求输入密码

• 使用pgAdmin 可视化工具创建。

LIMIT 、OFFSET

limit m offset n: 从n+1条数据开始，读取m条数据。

demodb=# select id ,name ,age from company limit 5 offset 2;

 id | name  | age
----+-------+-----
  3 | Teddy |  23
  4 | Mark  |  25
  5 | David |  27
  6 | Kim   |  22
  7 | James |  24
(5 行记录)

PostgreSQL 约束

约束类型

• not null：不为空。

• unique：唯一。

• primary key = not null + unique ：主键。

• foreign key : 外键。保证一个表中的数据匹配另一个表中的值的参照完整性。

  eg：emp_id int references another_table( pk_column )

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

  eg：SALARY REAL CHECK(SALARY > 0)

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

删除约束

alter table table_name drop CONSTRAINT constraint_name 必须事先知道约束名。

PostgreSQL JOIN

连接类型

• cross join ：交叉连接

  把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 行，则结果表有 x*y 行。

• [inner] join：内连接

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

• left [outer] join：左外连接

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

• right [outer] join：右外连接

  首先，执行内部连接。然后，对于表T2中不满足表T1中连接条件的每一行，其中T1列中的值为空也会添加一个连接行。这与左联接相反;对于T2中的每一行，结果表总是有一行。

• full outer join：外连接

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

PostgreSQL UNION

• union： 用于合并多个 select语句的结果，没有重复行。
• union 内部的每个 select 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 select 语句中的列的顺序必须相同。
• union all 操作符可以连接两个有重复行的 select语句。

PostgreSQL 索引

索引有助于加快 SELECT 查询和 WHERE 子句，但它会减慢使用 UPDATE 和 INSERT 语句时的数据输入。索引可以创建或删除，但不会影响数据。

索引类型

• 单列索引

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

• 隐式索引

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

删除索引

\di 查看当前数据库中的所有索引。

drop index index_name; 删除索引。

什么情况下要避免使用索引？

虽然索引的目的在于提高数据库的性能，但这里有几个情况需要避免使用索引:

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