PostgreSQL入门

一、简介

PostgreSQL是一个功能非常强大的、源代码开放的客户/服务器关系型数据库管理系统（RDBMS）。

PostgreSQL是一个非常健壮的软件包，有很多在大型商业RDBMS中所具有的特性，包括事务、子选择、触发器、视图、外键引用完整性和复杂锁定功能。

支持在多种系统下使用，如：支持WINDOWS、Linux、UNIX、MAC OS、BSD等，并且是开源免费的。

二、下载、安装

1、下载

安装包下载网址 PostgreSQL官网下载。

2、安装

我这里使用的是windows版本，直接下一步安装即可，详细可以参考其他教程。

3、基本使用

windows 版本，安装完成后会有一个命令行窗口应用。

我们可以通过这个命令行直接登录进去进行操作,若果是安装在本地就可以直接按下面步骤登录。

还可以通过 \help 命令查看帮助，比如我们需要查看 select 语句的相关参数

\help select

三、数据类型

数据类型是我们在创建表的时候为每个字段设置的类型。

PostgreSQL提 供了丰富的数据类型。用户可以使用 CREATE TYPE 命令在数据库中创建新的数据类型。PostgreSQL 的数据类型有很多种，下面介绍一下常用的数据类型吧。

详细更多的数据类型可以参考：http://www.postgres.cn/docs/9.6/datatype.html

1、数值类型

• smallint：2 字节，小范围整数，范围-32768 到 +32767

• integer：4 字节，常用整数，范围 -2147483648 到 +2147483647

• bigint：8 字节，大范围整数，范围-9223372036854775808 到 +9223372036854775807

• decimal：精确数值类型。可以指定精度和标度，通过精度指定总位数，通过标度指定小数位数。

• numeric：和 decimal 类型类似，也是精确数值类型，但是可以通过额外的选项控制舍入和截尾。

• float：存储单精度浮点数，4字节，可表示的数字范围在 -3.40282347E+38 到 +3.40282347E+38 之间。

• double precision： 存储双精度浮点数，8字节，可表示的数字范围在 -1.7976931348623157E+308 到 +1.7976931348623157E+308 之间。

2、字符串类型

• char：用于存储固定长度，不可变的字符串。
• varchar：用于存储可变长度的字符串。
• text：用于存储任意长度的文本。当内容过多时，可以使用该类型。

3、日期/时间类型

• date：用于存储日期
• time：用于存储时间
• timestamp：用于存储时间与日期

4、布尔类型

• boolean：只能存储 true/false，用于表示真或假。

5、几何类型

• point：用于存储二维平面上的点
• line：用于存储二维平面上的直线。
• polygon：用于存储二维平面上的多边形。
• circle：用于存储二维平面上的圆。

这里就列举一些常用类型，其他类型可以参考官网

四、数据定义语言（DDL）

用于创建、修改和删除表、索引、约束等数据库对象。包括CREATE、ALTER和DROP语句等。

1、创建数据库

PostgreSQL创建数据库可以使用以下几种方式：

1.1 使用 Create DATABASE 语句来创建。

create database testdb;

1.2 使用 createdb 命令创建

Createdb 是 Create Database 的封装。

语法格式：

createdb [option...] [dbname [description]]

1.3 使用图形可视化工具软件操作，如：PGAdmin、Navicat等。

2、删除数据库

PostgreSQL 删除数据库可以用以下三种方式：

2.1 使用 DROP DATABASE SQL 语句来删除。

可以使用if exists 参数，判断当前数据库是否存在。

DROP DATABASE IF EXISTS testdb;

2.2 使用 dropdb 命令来删除。

语法格式：

dropdb [connection-option...] [option...] dbname

3.3 使用图形可视化工具软件操作，如：PGAdmin、Navicat等。

3、创建表

可以使用 CREATE TABLE 来创建数据库中的表。
语法结构：

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

我们可以给指定的列添加约束，如不能为空，默认值，检查值。

约束的详情信息可以查看 http://www.postgres.cn/docs/9.6/ddl-constraints.html

当然这只是最最基础的一个创建表语句，我们以后使用中添加索引，添加自增id等。

4、删除表

可以使用 DROP TABLE 修改表结构.
语法结构：

DROP TABLE table_name;

5、修改表

我们可以使用 ALTER TABLE 修改表结构。下面介绍几个实用的。

5.1 添加列

ALTER TABLE table_name
ADD COLUMN column_name datatype column_constraint;

5.2 删除列

ALTER TABLE table_name
DROP COLUMN column_name;

5.3 重命名列

ALTER TABLE table_name 
RENAME COLUMN column_name 
TO new_column_name;

五、数据操作语言（DML）

我们可以先使用上一节学到的创建表命令创建一个表

CREATE TABLE "public"."book" (
  "book_id" SERIAL NOT NULL,
  "book_author" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "book_name" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  CONSTRAINT "book_pkey" PRIMARY KEY ("book_id")
);

SERIAL表示该字段为自增字段，会自动给我们创建一个序列用于自增。

1、插入

使用 INSERT INTO 语句用于向表中插入新记录。可以一次插入一条或者一次插入多条数据。

1.1 插入单条记录

-- id 是自增的，不需要赋值
INSERT INTO book(book_author,book_name) VALUES('吴承恩','西游记')

1.2 插入多条记录

-- 插入多条，只需要在 values 后面添加多个 () 即可
INSERT INTO book(book_author,book_name)
VALUES('test','Java从入门到放弃'),
('test','MySQL从删库到跑路'),
('test','Python从入门到入狱')

2、更新

使用 update 更新数据

UPDATE book SET book_author = 'test1',book_name = '算法从入门到放弃'
WHERE book_id = 1

3、删除

3.1 delete

delete可以用于我们删除单条或多条数据。

-- 根据id进行删除
DELETE FROM book WHERE book_id = 2

3.2 truncate

truncate是用于清空截断当前表中所有数据。

-- 截断表并重置自增主键
TRUNCATE book RESTART IDENTITY;

六、查询语句（DQL）

因为查询语句比较复杂，分类也比较多，所以单独拿出来。

参考链接：PostgreSQL9.6中文手册

七、关键字与函数

Postgresql中有许多的函数，我下面列出几个常用的，更多的可以查看PostgreSQL9.6中文手册-函数

1、常用关键字

• CURRENT_DATE：
  获取当前日期
  、
• INTERVAL:
  可以用来计算两个时间之间的间隔，或者执行日期和时间的加减运算

  -- 查询当前日期减去7天
  SELECT CURRENT_DATE - interval '7 day
  

2、常用函数

• count
  返回行数。
• sum
  返回输入值的总和。
• max/min
  返回输入值的最大及最小值。

八、索引

• string_agg
  连接拼接输入值。

PostgreSQL提供了多种索引类型： B-tree、Hash、GiST、SP-GiST 、GIN 和 BRIN。每一种索引类型使用了 一种不同的算法来适应不同类型的查询。默认情况下， CREATE INDEX命令创建适合于大部分情况的B-tree 索引。

索引可以随时被创建或删除。索引也会使带有搜索条件的UPDATE和DELETE命令受益。

1、创建索引

CREATE INDEX test1_id_index ON test1 (id);