作者:鱼仔
博客首页: codeease.top (opens new window)
公众号:神秘的鱼仔

引言

在前面两篇博客中，我介绍了PostgreSQL的历史以及安装方式，从这篇博客开始介绍PostgreSQL的使用，首先就是最基础的库、schema、表操作。并且会和MySQL做一些比较，更方便大家了解。

数据库的操作

创建数据库

CREATE DATABASE mydatabase WITH OWNER user_test ENCODING 'UTF8' TEMPLATE template0;

这是一段创建数据库的完整命令，有两个和MySQL不一样的地方

1、编码格式UTF8：在MySQL中，建议编码格式设置为utf8mb4，主要的原因MySQL的UTF8是“阉割版”，只占用3个字节，因此在保存emoji表情，或者繁体汉字的时候就无法存储。PostgreSQL的UTF8是“满血版”，本身就使用4个字节存储，不会遇到MySQL的问题。

2、TEMPLATE：PGSQL默认有两个模板，分别是template0和template1，template0是无法修改的，template1可以修改。

新建数据库的时候，如果指定了对应的模板，该模板的结构（表、视图、函数、插件等）以及数据都会复制到新的数据库中。template0是一个干净的模板，没有任何结构和数据，并且无法被修改。template1在初始化的时候也是干净的，但是可以修改。

使用PGSQL创建库是如果不指定模板，默认都会使用template1。

列出所有数据库

select datname from pg_database;

通过这行SQL可以查询现在系统中存在的所有数据库，可以看到template0和template就在其中。

删除数据库

drop database mydatabase;

Template的操作

前面已经介绍了template的作用，现在我们来自定义一个template

创建一个数据库：

CREATE DATABASE my_template;

接着切换到这个数据库，然后创建一张表

CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    name TEXT
);

将这个数据库设置为模板：

UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'my_template';

现在这个数据库就变成模板了，模板无法直接被连接使用，现在来使用这个模板

create database test_table_2 template my_template;

可以看到新创建出来的数据库已经默认有模板中建的表了。

Schema的操作

什么是Schema

在MySQL中，database之下就是table。但是在PostgreSQL中，增加了一个Schema的中间层。这是一个逻辑的命名空间，可以理解为数据库内部的文件夹。可以隔离和管理数据库对象，还可以避免命名冲突。

创建完数据库之后，可以看到每个数据库都有一个默认的schema，叫做public。默认的表都会创建到这个public的schema中。

创建schema

create schema if not exists app_schema;

创建完成之后在数据库之下就会增加一个新的schema

后续在建表的时候，通过语句 create table app_schema.xxxtable 就可以在该schema之下创建新的表。

查询当前数据库下有哪些schema，会同时讲系统schema也查询出来。

select nspname from pg_namespace

删除schema

如果schema中没有任何的对象，可以通过下面的语句直接删除

drop schema app_schema;

如果该schema中已经有对象了，如果要强制全部删除的话就需要使用下面这段sql

drop schema app_schema cascade;

注意上面这段删除命令会同时将schema中的所有内容删除，删除前需要谨慎处理。

搜索路径

如果执行一段查询SQL：select * from user

但是不同的schema中又刚好都有一张user表，PGSQL会去查哪个schema呢？这就涉及到了一个叫做搜索路径的概念：

show search_path

发现输出了这样一段结果：

app_schema,"$user",public

说明会先去app_schema这个schema中查，再去"$user"中查，这是一个占位符，比如我当前登录用户叫做test_user，PGSQL就会去查询是否有test_user这个schema，最后到public去查询。

修改搜索路径的方式有三种，针对当前会话、针对某个用户、针对整个数据库

-- 设置搜索路径（会话级）
SET search_path TO public, app_schema;

-- 永久设置（用户级）
ALTER USER user_test SET search_path TO public, app_schema;

-- 永久设置（数据库级别）
ALTER DATABASE mydatabase SET search_path TO public, app_schema;

总结

PostgreSQL的功能相比MySQL增加了很多，从这一期讲到数据库就能大致看出来了，MySQL的结构只有数据库和数据表，PG增加了模板、Schema的概念，也让它增加了更多的功能性。下一篇我们讲解PG中的数据表--Table。