PostgreSQL学习笔记(1)
PostgreSQL 是一个免费的对象-关系数据库服务器(ORDBMS)，在灵活的BSD许可证下发行。
PostgreSQL 开发者把它念作 post-gress-Q-L。
PostgreSQL 的 Slogan 是 "世界上最先进的开源关系型数据库"。
什么是数据库？
数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。
每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。
我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。
所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理的大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。
ORDBMS（对象关系数据库系统）是面向对象技术与传统的关系数据库相结合的产物，查询处理是 ORDBMS 的重要组成部分，它的性能优劣将直接影响到DBMS 的性能。
ORDBMS在原来关系数据库的基础上，增加了一些新的特性。
RDBMS 是关系数据库管理系统,是建立实体之间的联系,最后得到的是关系表。
OODBMS 面向对象数据库管理系统,将所有实体都看着对象,并将这些对象类进行封装,对象之间的通信通过消息 OODBMS 对象关系数据库在实质上还是关系数据库 。
ORDBMS 术语
在我们开始学习 PostgreSQL 数据库前，让我们先了解下 ORDBMS 的一些术语：
- 数据库: 数据库是一些关联表的集合。
- 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
- 列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
- **行：**一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
- 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
- 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
- **外键：**外键用于关联两个表。
- 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
- **索引：**使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
- 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。
PostgreSQL 特征
-
函数：通过函数，可以在数据库服务器端执行指令程序。
-
索引：用户可以自定义索引方法，或使用内置的 B 树，哈希表与 GiST 索引。
-
触发器：触发器是由SQL语句查询所触发的事件。如：一个INSERT语句可能触发一个检查数据完整性的触发器。触发器通常由INSERT或UPDATE语句触发。 多版本并发控制：PostgreSQL使用多版本并发控制（MVCC，Multiversion concurrency control）系统进行并发控制，该系统向每个用户提供了一个数据库的"快照"，用户在事务内所作的每个修改，对于其他的用户都不可见，直到该事务成功提交。
-
规则：规则（RULE）允许一个查询能被重写，通常用来实现对视图（VIEW）的操作，如插入（INSERT）、更新（UPDATE）、删除（DELETE）。
-
数据类型：包括文本、任意精度的数值数组、JSON 数据、枚举类型、XML 数据
等。
-
全文检索：通过 Tsearch2 或 OpenFTS，8.3版本中内嵌 Tsearch2。
-
NoSQL：JSON，JSONB，XML，HStore 原生支持，至 NoSQL 数据库的外部数据包装器。
-
数据仓库：能平滑迁移至同属 PostgreSQL 生态的 GreenPlum，DeepGreen，HAWK 等，使用 FDW 进行 ETL。
Windows 上安装 PostgreSQL
PostgreSQL 语法
默认情况下 PostgreSQL 安装完成后，自带了一个命令行工具 SQL Shell(psql)。
Linux 系统可以直接切换到 postgres 用户来开启命令行工具：
# sudo -i -u postgres
Windows 系统一般在它的安装目录下：
Program Files → PostgreSQL 11.3 → SQL Shell(psql)
进入命令行工具，我们可以使用 \help 来查看各个命令的语法 ：
postgres-# \help <command_name>
例如，我们查看下 select 语句的语法：
postgres=# \help SELECT
Command: SELECT
Description: retrieve rows from a table or view
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
[ * | expression [ [ AS ] output_name ] [, ...] ]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition [, ...] ]
[ WINDOW window_name AS ( window_definition ) [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
from_item 可以是以下选项之一：
[ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
SQL 语句
一个 SQL 语句通常包含了关键字、标识符（字段）、常量、特殊符号等，下面是一个简单的 SQL 语句：
SELECT id, name FROM runoob
| SELECT | id, name | FROM | runoob | |
|---|---|---|---|---|
| 符号类型 | 关键字 | 标识符（字段） | 关键字 | 标识符 |
| 描述 | 命令 | id 和 name 字段 | 语句，用于设置条件规则等 | 表名 |
PostgreSQL 命令
https://www.runoob.com/postgresql/postgresql-syntax.html
PostgreSQL 数据类型
PostgreSQL提 供了丰富的数据类型。用户可以使用 CREATE TYPE 命令在数据库中创建新的数据类型。PostgreSQL 的数据类型有很多种，下面我们具体来说明。
https://www.runoob.com/postgresql/postgresql-data-type.html
数值类型
数值类型由 2 字节、4 字节或 8 字节的整数以及 4 字节或 8 字节的浮点数和可选精度的十进制数组成。
下表列出了可用的数值类型。
| 名字 | 存储长度 | 描述 | 范围 |
|---|---|---|---|
| smallint | 2 字节 | 小范围整数 | -32768 到 +32767 |
| integer | 4 字节 | 常用的整数 | -2147483648 到 +2147483647 |
| bigint | 8 字节 | 大范围整数 | -9223372036854775808 到 +9223372036854775807 |
| decimal | 可变长 | 用户指定的精度，精确 | 小数点前 131072 位；小数点后 16383 位 |
| numeric | 可变长 | 用户指定的精度，精确 | 小数点前 131072 位；小数点后 16383 位 |
| real | 4 字节 | 可变精度，不精确 | 6 位十进制数字精度 |
| double precision | 8 字节 | 可变精度，不精确 | 15 位十进制数字精度 |
| smallserial | 2 字节 | 自增的小范围整数 | 1 到 32767 |
| serial | 4 字节 | 自增整数 | 1 到 2147483647 |
| bigserial | 8 字节 | 自增的大范围整数 | 1 到 9223372036854775807 |
常用类型：
- 数值型：
- integer(int)
- real
- serial
- 文字型：
- char
- varchar
- text
- 布尔型：
- boolean
- 日期型：
- date
- time
- timestamp
- 特色类型：
- Array
- 网络地址型(inet)
- JSON型
- XML型
PostgreSQL 创建数据库
PostgreSQL 创建数据库可以用以下三种方式：
- 1、使用 CREATE DATABASE SQL 语句来创建。
- 2、使用 createdb 命令来创建。
- 3、使用 pgAdmin 工具。
CREATE DATABASE 创建数据库
CREATE DATABASE 命令需要在 PostgreSQL 命令窗口来执行，语法格式如下：
CREATE DATABASE dbname;
例如，我们创建一个 runoobdb 的数据库：
postgres=# CREATE DATABASE runoobdb;
createdb 命令创建数据库
createdb 是一个 SQL 命令 CREATE DATABASE 的封装。
createdb 命令语法格式如下：
createdb [option...] [dbname [description]]
参数说明：
dbname：要创建的数据库名。
description：关于新创建的数据库相关的说明。
options：参数可选项，可以是以下值：
| 序号 | 选项 & 描述 |
|---|---|
| 1 | -D tablespace指定数据库默认表空间。 |
| 2 | -e将 createdb 生成的命令发送到服务端。 |
| 3 | -E encoding指定数据库的编码。 |
| 4 | -l locale指定数据库的语言环境。 |
| 5 | -T template指定创建此数据库的模板。 |
| 6 | --help显示 createdb 命令的帮助信息。 |
| 7 | -h host指定服务器的主机名。 |
| 8 | -p port指定服务器监听的端口，或者 socket 文件。 |
| 9 | -U username连接数据库的用户名。 |
| 10 | -w忽略输入密码。 |
| 11 | -W连接时强制要求输入密码。 |
接下来我们打开一个命令窗口，进入到 PostgreSQL 的安装目录，并进入到 bin 目录，createdb 命令位于 PostgreSQL安装目录/bin 下，执行创建数据库的命令：
$ cd /Library/PostgreSQL/11/bin/
$ createdb -h localhost -p 5432 -U postgres runoobdb
password ******
以上命令我们使用了超级用户 postgres 登录到主机地址为 localhost，端口号为 5432 的 PostgreSQL 数据库中并创建 runoobdb 数据库。
pgAdmin 工具创建数据库
pgAdmin 工具提供了完整操作数据库的功能：
PostgreSQL 选择数据库
上一章节我们讲了如何创建数据库，接下来我们来讨论如何去选择我们创建的数据库。
数据库的命令窗口
PostgreSQL 命令窗口中，我们可以命令提示符后面输入 SQL 语句：
postgres=#
使用 \l 用于查看已经存在的数据库：
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
postgres | postgres | UTF8 | C | C |
runoobdb | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
接下来我们可以使用 \c + 数据库名 来进入数据库：
postgres=# \c runoobdb
You are now connected to database "runoobdb" as user "postgres".
runoobdb=#
系统命令行窗口
在系统的命令行查看，我么可以在连接数据库后面添加数据库名来选择数据库：
$ psql -h localhost -p 5432 -U postgres runoobdb
Password for user postgres: ****
psql (11.3)
Type "help" for help.
You are now connected to database "runoobdb" as user "postgres".
runoobdb=#
pgAdmin 工具
pgAdmin 工具更简单了，直接点击数据库选择就好了，还可以查看一些数据库额外的信息：
PostgreSQL 删除数据库
PostgreSQL 删除数据库可以用以下三种方式：
- 1、使用 DROP DATABASE SQL 语句来删除。
- 2、使用 dropdb 命令来删除。
- 3、使用 pgAdmin 工具。
**注意：**删除数据库要谨慎操作，一旦删除，所有信息都会消失。
DROP DATABASE 删除数据库
DROP DATABASE 会删除数据库的系统目录项并且删除包含数据的文件目录。
DROP DATABASE 只能由超级管理员或数据库拥有者执行。
DROP DATABASE 命令需要在 PostgreSQL 命令窗口来执行，语法格式如下：
DROP DATABASE [ IF EXISTS ] name
参数说明：
- IF EXISTS：如果数据库不存在则发出提示信息，而不是错误信息。
- name：要删除的数据库的名称。
例如，我们删除一个 runoobdb 的数据库：
postgres=# DROP DATABASE runoobdb;
dropdb 命令删除数据库
dropdb 是 DROP DATABASE 的包装器。
dropdb 用于删除 PostgreSQL 数据库。
dropdb 命令只能由超级管理员或数据库拥有者执行。
dropdb 命令语法格式如下：
dropdb [connection-option...] [option...] dbname
参数说明：
dbname：要删除的数据库名。
options：参数可选项，可以是以下值：
| 序号 | 选项 & 描述 |
|---|---|
| 1 | -e显示 dropdb 生成的命令并发送到数据库服务器。 |
| 2 | -i在做删除的工作之前发出一个验证提示。 |
| 3 | -V打印 dropdb 版本并退出。 |
| 4 | --if-exists如果数据库不存在则发出提示信息，而不是错误信息。 |
| 5 | --help显示有关 dropdb 命令的帮助信息。 |
| 6 | -h host指定运行服务器的主机名。 |
| 7 | -p port指定服务器监听的端口，或者 socket 文件。 |
| 8 | -U username连接数据库的用户名。 |
| 9 | -w连接时忽略输入密码。 |
| 10 | -W连接时强制要求输入密码。 |
| 11 | --maintenance-db=dbname删除数据库时指定连接的数据库，默认为 postgres，如果它不存在则使用 template1。 |
接下来我们打开一个命令窗口，进入到 PostgreSQL 的安装目录，并进入到 bin 目录，dropdb 名位于 PostgreSQL安装目录/bin 下，执行删除数据库的命令：
$ cd /Library/PostgreSQL/11/bin/
$ dropdb -h localhost -p 5432 -U postgres runoobdb
password ******
以上命令我们使用了超级用户 postgres 登录到主机地址为 localhost，端口号为 5432 的 PostgreSQL 数据库中并删除 runoobdb 数据库。
PostgreSQL 创建表格
PostgreSQL 使用 CREATE TABLE 语句来创建数据库表格。
语法
CREATE TABLE 语法格式如下：
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( 一个或多个列 )
);
CREATE TABLE 是一个关键词，用于告诉数据库系统将创建一个数据表。
表名字必需在同一模式中的其它表、 序列、索引、视图或外部表名字中唯一。
CREATE TABLE 在当前数据库创建一个新的空白表，该表将由发出此命令的用户所拥有。
表格中的每个字段都会定义数据类型，如下：
实例
以下创建了一个表，表名为 COMPANY 表格，主键为 ID，NOT NULL 表示字段不允许包含 NULL 值：
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
接下来我们再创建一个表格，在后面章节会用到：
CREATE TABLE DEPARTMENT(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT NOT NULL
);
我们可以使用 \d 命令来查看表格是否创建成功：
runoobdb=# \d
List of relations
Schema | Name | Type | Owner
--------+------------+-------+----------
public | company | table | postgres
public | department | table | postgres
(2 rows)
\d tablename 查看表格信息：
runoobdb=# \d company
Table "public.company"
Column | Type | Collation | Nullable | Default
---------+---------------+-----------+----------+---------
id | integer | | not null |
name | text | | not null |
age | integer | | not null |
address | character(50) | | |
salary | real | | |
Indexes:
"company_pkey" PRIMARY KEY, btree (id)
PostgreSQL 删除表格
PostgreSQL 使用 DROP TABLE 语句来删除表格，包含表格数据、规则、触发器等，所以删除表格要慎重，删除后所有信息就消失了。
语法
DROP TABLE 语法格式如下：
DROP TABLE table_name;
实例
上一章节中我们创建了 COMPANY 和 DEPARTMENT 两个表格，我们可以先使用 \d 命令来查看表格是否创建成功：
runoobdb=# \d
List of relations
Schema | Name | Type | Owner
--------+------------+-------+----------
public | company | table | postgres
public | department | table | postgres
(2 rows)
从以上结果可以看出，我们表格已经创建成功，接下来我们删除这两个表格：
runoobdb=# drop table department, company;
DROP TABLE
再使用 \d 命令来查看就找不到表格了：
testdb=# \d
Did not find any relations.
PostgreSQL 模式（SCHEMA）
PostgreSQL 模式（SCHEMA）可以看着是一个表的集合。
一个模式可以包含视图、索引、数据类型、函数和操作符等。
相同的对象名称可以被用于不同的模式中而不会出现冲突，例如 schema1 和 myschema 都可以包含名为 mytable 的表。
使用模式的优势：
- 允许多个用户使用一个数据库并且不会互相干扰。
- 将数据库对象组织成逻辑组以便更容易管理。
- 第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突。
模式类似于操作系统层的目录，但是模式不能嵌套。
语法
我们可以使用 CREATE SCHEMA 语句来创建模式，语法格式如下：
CREATE SCHEMA myschema.mytable (
...
);
实例
接下来我们连接到 runoobdb 来创建模式 myschema：
runoobdb=# create schema myschema;
CREATE SCHEMA
输出结果 "CREATE SCHEMA" 就代表模式创建成功。
接下来我们再创建一个表格：
runoobdb=# create table myschema.company(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);
以上命令创建了一个空的表格，我们使用以下 SQL 来查看表格是否创建：
runoobdb=# select * from myschema.company;
id | name | age | address | salary
----+------+-----+---------+--------
(0 rows)
删除模式
删除一个为空的模式（其中的所有对象已经被删除）：
DROP SCHEMA myschema;
删除一个模式以及其中包含的所有对象：
DROP SCHEMA myschema CASCADE;
评论