【PostgreSQL 小课专栏】PostgreSQL 基础 SQL

在本章中，我们将讨论 PostgreSQL 的基本 SQL 命令；这些是数据定义语言（DDL）命令和数据操作语言（DML）命令。基本上，DDL 命令用于管理数据库和表，DML 命令用于在数据库中插入、删除、更新和查看数据。

基础语句基本上就是：增、删、改，查等四大类型。

创建及管理数据库

在本节中，我们将从创建第一个数据库开始，接着学习如何删除数据库，最后，如何从现有数据库创建新数据库。我们还会从 DBA 的视角来分析：当我们创建新数据库时，我们将看到幕后会发生什么，并学习一些对 DBA 有用的基本功能，以了解数据库的真实大小。

创建数据库

创建一个名为 databasename 库的语法为：

CREATE DATABASE databasename;

现在让我们看看当我们创建新数据库时，背后发生了什么。PostgreSQL 会执行以下步骤：

1. 从模板数据库 template1 进行一个物理复制
2. 将数据库名称指定给刚刚复制的数据库

从前面的章节中我们知道，template1 数据库是由 initdb 进程在 PostgreSQL 集群初始化期间创建的数据库。

Schema 介绍

我们简单回顾下之前的关于模式的内容：数据库可以组织成命名空间，称为模式。模式是一个助记符名称，用户可以将其分配给将数据库对象（如表）组织成更结构化的集合，模式不能嵌套。前面我们了解到有两种用户，普通用户和超级用户：

• 超级用户可以跨数据库和模式进行任何操作
• 普通用户可以根据他们的特定授权进行一些特定的操作

public 模式介绍

从 PostgreSQL 15 开始，PostgreSQL 改变了管理 public 模式的方式。在本节中，我们将看到它是如何运作的。在 PostgreSQL 15 之前，任何用户都可以在 public 模式上执行任何 DDL 操作。PostgreSQL 15 引入了从 public 模式中删除全局特权的概念。

那么从 PostgreSQL 15 版本开始就有如下限制：

• 普通用户无法在 public 模式执行 DDL 语句
• 普通用户无法在自己的权限之外执行 DML 语句

让我们用一个例子来更好地解释这个新功能是如何工作的。以下是我们将要执行的步骤：

1. 我们首先创建一个名为 myuser 的角色（或用户）
2. 以 myuser 角色连接 forumdb 数据库
3. 以 myuser 角色，执行创建一个名为 mytable 的表

$ psql -U postgres -d forumdb

forumdb=# CREATE ROLE myuser WITH LOGIN PASSWORD 'dbuser';
forumdb=# SET ROLE TO myuser;

-- 注意，下面的命令提示符变成了 =>
forumdb=> create table mytable(id integer);
ERROR: permission denied for schema public
LINE 1: create table mytable(id integer);

正如我们所看到的，普通用户无法在 public 模式上创建表（DDL）。

search_path 变量介绍

PostgreSQL 有许多系统变量。其中一个叫做 search_path。search_path 变量包含 PostgreSQL 用于查找表的模式列表；search_path 默认值为 $user,public。这意味着首先它将搜索用户模式中具有该名称的所有表，然后再搜索 public 模式。

也就说用户模式比 public 模式的优先级要高。

例如，如果我们有一个名为 forum 的用户，并且我们想查询名为 cities 表中存在的所有记录，首先 PostgreSQL 将在 forum 模式中搜索 cities 表，如果在 forum 模式中找不到 cities 表，PostgreSQL 将在 public 模式中搜索 cities 表。

正确的打开方式

让我们从头开始，执行以下步骤：

1. 使用超级用户，让我们创建一个名为 myforumdb 的新数据库并连接它。
2. 使用超级用户，让我们创建一个名为 myforum 的新用户。
3. 使用超级用户，让我们为 myforum 用户创建一个名为 myforum 的新模式。
4. 让我们以 myforum 用户的身份连接到数据库：

$ psql -U postgres -d forumdb

postgres=# create database myforumdb;

postgres=# \c myforumdb 
You are now connected to database "myforumdb" as user "postgres".

myforumdb=# create user myforum with login password 'dbuser';
CREATE ROLE

myforumdb=# create schema myforum authorization myforum;
CREATE SCHEMA

现在让我们尝试以 myforum 用户的身份连接到 myforumdb 数据库：

$ psql -U myforum myforumdb
myforumdb=>

让我们尝试创建一个名为 mytable 的新表：

myforumdb=> create table mytable(id integer);
CREATE TABLE

myforumdb=> \d
          List of relations
 Schema  │  Name   │ Type  │  Owner  
═════════╪═════════╪═══════╪═════════
 myforum │ mytable │ table │ myforum
(1 row)

myforumdb=> show search_path;
   search_path   
-----------------
 "$user", public
(1 row)

从上面的输出可以看到，mytable 表是在 myforum 模式中创建的。

查看所有的表

现在让我们以 forum 用户连接到 forumdb 数据库：

$ psql -U forum forumdb

要列出 forumdb 数据库中存在的所有表，我们必须使用 psql 的 \dt 命令。\dt 命令列出了 forumdb 数据库中的所有表：

forumdb=> \dt
           List of relations
 Schema |     Name     | Type  | Owner  
--------+--------------+-------+--------
 forum  | categories   | table | forum
 forum  | j_posts_tags | table | forum
 forum  | posts        | table | forum
 forum  | tags         | table | forum
 forum  | users        | table | forum
 public | mytable      | table | myuser
(6 rows)

使用 \d 命令则会显示表及序列，如下：

forumdb=> \d
               List of relations
 Schema |       Name        |   Type   | Owner  
--------+-------------------+----------+--------
 forum  | categories        | table    | forum
 forum  | categories_pk_seq | sequence | forum
 forum  | j_posts_tags      | table    | forum
 forum  | posts             | table    | forum
 forum  | posts_pk_seq      | sequence | forum
 forum  | tags              | table    | forum
 forum  | tags_pk_seq       | sequence | forum
 forum  | users             | table    | forum
 forum  | users_pk_seq      | sequence | forum
 public | mytable           | table    | myuser
(10 rows)

从已修改的模板创建数据库

现在我们已经学会了如何查看数据库中的所有表，让我们确保对 template1 数据库所做的任何变更都会被所有后续创建的数据库看到。我们将执行以下步骤进行演示：

1. 以 postgres 用户连接到 template1 数据库。
2. 在 template1 数据库中创建一个名为 dummytable 的表。
3. 创建一个名为 dummydb 的新数据库。

步骤如下：

-- 连接到 template1 数据库：
$ psql -U postgres template1

template1=# 

-- 创建一个名为 dummytable 的表
-- 关于建表语句我们后续会有说明
template1=# create table dummytable (dummyfield integer not null primary key);
CREATE TABLE

-- 使用 \dt 命令查看 template1 中的表
template1=# \dt
           List of relations
 Schema │    Name    │ Type  │  Owner   
════════╪════════════╪═══════╪══════════
 public │ dummytable │ table │ postgres
(1 row)

-- 接下来让我们创建一个名为 dummydb 的新数据库
-- 创建完成后，执行 \dt 命令进行验证，是不是存在 dummytable 表
template1=# create database dummydb;
CREATE DATABASE

template1=# \c dummydb
You are now connected to database "dummydb" as user "postgres".

-- 可以看到 dummydb 库中已经包含了 dummytable 的表了
dummydb=# \dt
           List of relations
 Schema │    Name    │ Type  │  Owner   
════════╪════════════╪═══════╪══════════
 public │ dummytable │ table │ postgres
(1 row)

我们需要记住，对 template1 数据库所做的任何更改都将出现在后续创建的所有数据库中。

接下来，我们将删除 template1 数据库中的 dummydb 库和 dummytable 表。

删除表及库

DROP TABLE：用于在数据库中删除表格。

DROP DATABASE：用于在集群中删除数据库。

要删除某个表，我们首先要连接表所在的库：

dummydb=# \c template1
You are now connected to database "template1" as user "postgres".

-- 删除表
template1=# drop table if exists dummytable;
DROP TABLE

-- 删除库
template1=# drop database if exists dummydb;
DROP DATABASE

创建数据库副本

接下来我们演示如何从模板数据库中创建新数据库：

通过执行以下命令，在同一 PostgreSQL 集群上复制 forumdb 数据库：

template1=# create database forumdb2 template forumdb;
CREATE DATABASE

通过使用此命令，我们只需告诉 PostgreSQL 使用 forumdb 数据库作为模板创建一个名为 forumdb2 的新数据库。

接下来让我们以 forum 用户连接到 forumdb2 库：

$ psql -U forum forumdb2

forumdb2=> \dt
          List of relations
 Schema │    Name    │ Type  │ Owner 
════════╪════════════╪═══════╪═══════
 forum  │ categories │