数据库与模式

数据库

创建数据库

1. \l命令查看已有数据库。

shoping=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 shoping   | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(4 rows)

2. 使用 SQL 语句查看已有的数据库。

shoping=# SELECT datname FROM pg_database;
  datname  
-----------
 postgres
 shoping
 template1
 template0
(4 rows)

3. 系统默认为我们创建了 3 个数据：
   • 其中 template0 和 template1 是模板数据库，创建新的数据库时默认基于 template1 进行复制。
   • postgres 数据库是为 postgres 用户创建的默认数据库。
4. 使用 SQL 语 CREATE DATABASE 创建数据库语法：(要创建数据库，您必须是超级用户或具有特殊的CREATEDB权限)
   • name: 新数据库的名称。这是必填项，且名称必须符合PostgreSQL的命名规则。
   • [ [ WITH ] ]: WITH关键字是可选的，它用于指定数据库的额外选项。
   • [ OWNER [=] user_name ]: 指定新数据库的所有者。如果不指定，默认为执行命令的用户。
   • [ TEMPLATE [=] template ]: 指定创建数据库时要使用的模板。默认是template1。
   • [ ENCODING [=] encoding ]: 设置数据库的字符集编码。如果不指定，默认使用模板数据库的编码。
   • [ LOCALE [=] locale ]: 设置数据库的区域设置。如果不指定，默认使用模板数据库的区域设置。
   • [ LC_COLLATE [=] lc_collate ]: 设置数据库的字符串排序规则。如果不指定，默认使用模板数据库的排序规则。
   • [ LC_CTYPE [=] lc_ctype ]: 设置数据库的字符分类规则。如果不指定，默认使用模板数据库的分类规则。
   • [ TABLESPACE [=] tablespace_name ]: 指定数据库使用的表空间。如果不指定，默认使用模板数据库的表空间。
   • [ ALLOW_CONNECTIONS [=] allowconn ]: 指定是否允许连接到新数据库。默认为true。
   • [ CONNECTION LIMIT [=] connlimit ]: 设置同时连接到数据库的最大数量。默认没有限制。
   • [ IS_TEMPLATE [=] istemplate ]: 指定新数据库是否可以作为模板。默认为false。

shoping=# \h CREATE DATABASE
Command:     CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LOCALE [=] locale ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ] ]

URL: https://www.postgresql.org/docs/14/sql-createdatabase.html

5. 使用示例：
   • my_new_database 是新数据库的名称。
   • my_user 是新数据库的所有者。
   • 数据库的编码被设置为UTF8。
   • 字符串排序规则和字符分类规则都被设置为en_US.UTF-8。
   • 数据库使用的表空间是my_tablespace。
   • 同时连接到数据库的最大数量被限制为100。

CREATE DATABASE my_new_database
WITH OWNER = my_user
     ENCODING = 'UTF8'
     LC_COLLATE = 'en_US.UTF-8'
     LC_CTYPE = 'en_US.UTF-8'
     TABLESPACE = my_tablespace
     CONNECTION LIMIT = 100;

6. 创建数据库时还可以指定许多选项，例如字符集编码、拥有者、默认表空间、最大连接数等等。具体参考官方文档中完整的 CREATE DATABASE 语句。

修改数据库

1. 通过 ALTER DATABASE 语句修改数据库的属性和配置：

Command:     ALTER DATABASE
Description: change a database
Syntax:
ALTER DATABASE name [ [ WITH ] option [ ... ] ]

where option can be:

    ALLOW_CONNECTIONS allowconn
    CONNECTION LIMIT connlimit
    IS_TEMPLATE istemplate

ALTER DATABASE name RENAME TO new_name

ALTER DATABASE name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }

ALTER DATABASE name SET TABLESPACE new_tablespace

ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name SET configuration_parameter FROM CURRENT
ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL

URL: https://www.postgresql.org/docs/14/sql-alterdatabase.html

2. 修改数据库配置。
   • name: 要修改的数据库的名称。
   • [ [ WITH ] option [ … ] ]: 可选的 WITH 关键字后面跟着一个或多个选项，用于指定数据库的配置。
   • ALLOW_CONNECTIONS allowconn: 允许或禁止连接到数据库。allowconn 可以是 true 或 false。
   • CONNECTION LIMIT connlimit: 限制同时连接到数据库的并发数量。connlimit 是一个整数。
   • IS_TEMPLATE istemplate: 指定数据库是否可以作为模板来创建新数据库。istemplate 可以是 true 或 false。

ALTER DATABASE name [ [ WITH ] option [ ... ] ]

where option can be:

    ALLOW_CONNECTIONS allowconn
    CONNECTION LIMIT connlimit
    IS_TEMPLATE istemplate

-- 更改数据库连接限制(这个命令将数据库 mydatabase 的并发连接数限制为 50)
ALTER DATABASE mydatabase CONNECTION LIMIT 50;

-- 禁止连接到数据库(这个命令禁止所有用户连接到 mydatabase 数据库)
ALTER DATABASE mydatabase ALLOW_CONNECTIONS false;

3. 重命名数据库。
   • RENAME TO new_name: 将数据库重命名为 new_name。

ALTER DATABASE name RENAME TO new_name;

-- 将数据库重命名(这个命令将数据库 mydatabase 重命名为 newdatabase)
ALTER DATABASE mydatabase RENAME TO newdatabase;

4. 更改数据库所有者。
   • OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }: 更改数据库的所有者。可以指定新的所有者，或者使用特殊标识符如 CURRENT_ROLE、CURRENT_USER 或 SESSION_USER。

ALTER DATABASE name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }

-- 这个命令将数据库 mydatabase 的所有者更改为用户 myuser
ALTER DATABASE mydatabase OWNER TO myuser;

5. 更改数据库使用的表空间。
   • SET TABLESPACE new_tablespace: 将数据库使用的表空间更改为 new_tablespace。

ALTER DATABASE name SET TABLESPACE new_tablespace

-- 这个命令将数据库 mydatabase 使用的表空间更改为 mytablespace
ALTER DATABASE mydatabase SET TABLESPACE mytablespace;

6. 设置数据库参数。
   • 当用户连接数据库时，PostgreSQL 使用配置文件 postgresql.conf 或者启动命令 postgres 中设置的变量值作为默认值。

ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }

-- 这个命令将数据库 mydatabase 的时区设置为 UTC
ALTER DATABASE mydatabase SET timezone TO 'UTC';

-- 禁用数据库 newdb 中的索引扫描
ALTER DATABASE newdb SET enable_indexscan TO off;

7. 设置参数为当前会话的值。
   • SET configuration_parameter { TO | = } { value | DEFAULT }: 设置数据库的配置参数。可以使用 TO 或 = 来指定值，或者设置为 DEFAULT。

ALTER DATABASE name SET configuration_parameter FROM CURRENT

8. 重置配置参数到默认值。
   • RESET configuration_parameter: 重置配置参数到默认值。
   • RESET ALL: 重置所有配置参数到默认值。

ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL

-- 重置数据库配置参数(这个命令将数据库 mydatabase 的时区配置参数重置为默认值)
ALTER DATABASE mydatabase RESET timezone;

9. 请确保在执行这些命令时，你有足够的权限来更改数据库配置，并且替换示例中的 mydatabase、myuser、mytablespace 等占位符为实际的数据库、用户和表空间名称。

删除数据库

1. 使用 DROP DATABASE 语句删除一个数据库：
   • [ IF EXISTS ]: 这是一个可选的子句，如果加上这个子句，当指定的数据库不存在时，命令不会报错，而是静默失败。
   • name: 你想要删除的数据库的名称。
   • [ [ WITH ] ( option [, …] ) ]: 这是一个可选的子句，用于指定删除数据库时的额外选项。
   • FORCE: 这是一个选项，如果指定，即使有其他会话正在连接到该数据库，也会强制删除该数据库。这可能会导致数据丢失或未提交的事务丢失。

Command:     DROP DATABASE
Description: remove a database
Syntax:
DROP DATABASE [ IF EXISTS ] name [ [ WITH ] ( option [, ...] ) ]

where option can be:

    FORCE

URL: https://www.postgresql.org/docs/14/sql-dropdatabase.html

2. 删除一个数据库。
   • 这个命令会删除名为 mydatabase 的数据库。如果数据库不存在，命令会报错。

DROP DATABASE mydatabase;

3. 如果数据库存在则删除。
   • 这个命令会尝试删除名为 mydatabase 的数据库。如果数据库不存在，命令不会报错，而是静默失败。

DROP DATABASE IF EXISTS mydatabase;

4. 强制删除数据库。
   • 这个命令会强制删除名为 mydatabase 的数据库，即使有其他会话正在使用该数据库。

-- 请谨慎使用这个选项，因为它可能会导致数据不一致或丢失。
DROP DATABASE mydatabase WITH FORCE;

5. 注意事项：

   • 删除数据库是一个不可逆的操作，一旦数据库被删除，所有存储在该数据库中的数据都将永久丢失。
   • 在删除数据库之前，建议先备份数据库，以防万一需要恢复数据。
   • 只有数据库的超级用户或具有足够权限的用户才能删除数据库。
   • 当尝试删除一个正在被其他会话使用的数据库时，除非使用 FORCE 选项，否则命令将会失败。

6. 请确保在执行 DROP DATABASE 命令之前，你有足够的权限，并且已经仔细考虑了删除数据库的后果。替换示例中的 mydatabase 为实际的数据库名称。

模式

• 创建了数据库之后，还需要创建模式（Schema）才能够存储数据库对象。通常在创建一个新的数据库时，默认会创建一个模式 public。
• 创建一个新的数据库 testdb。
  • \c 用于连接到一个数据库；\dn 用于查看当前数据库中的模式。

shoping=# CREATE DATABASE testdb;
CREATE DATABASE
shoping=#
shoping=# \c testdb 
Password: 
You are now connected to database "testdb" as user "postgres".
testdb=#
testdb=# \conninfo
You are connected to database "testdb" as user "postgres" on host "localhost" (address "::1") at port "5432".
testdb=#
testdb=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)
testdb=#
testdb=# SELECT nspname FROM pg_namespace;
      nspname       
--------------------
 pg_toast
 pg_catalog
 public
 information_schema
(4 rows)

创建模式

1. 使用 CREATE SCHEMA 语法创建一个模式：
   • schema_name: 你想要创建的模式名称。
   • [ AUTHORIZATION role_specification ]: 这是一个可选的子句，用于指定将模式的所有权赋予哪个角色。如果没有指定，那么模式的所有权将赋予执行命令的用户。
   • role_specification: 可以是以下之一：
     • user_name: 指定一个数据库用户的名称。
     • CURRENT_ROLE: 当前角色（可能不是执行命令的用户）。
     • CURRENT_USER: 执行命令的用户。
     • SESSION_USER: 会话用户。
   • [ schema_element [ … ] ]: 在创建模式时，你可以直接指定要在新模式中创建的数据库对象。
   • CREATE SCHEMA IF NOT EXISTS: 这是一个可选的子句，如果加上这个子句，当指定的模式已经存在时，命令不会报错，而是静默失败。

testdb=# \h CREATE SCHEMA
Command:     CREATE SCHEMA
Description: define a new schema
Syntax:
CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification

where role_specification can be:

    user_name
  | CURRENT_ROLE
  | CURRENT_USER
  | SESSION_USER

URL: https://www.postgresql.org/docs/14/sql-createschema.html

2. 创建一个新模式。(这个命令会创建一个名为 my_schema 的新模式)

CREATE SCHEMA my_schema;

3. 创建一个新模式并指定所有者。(这个命令会创建一个名为 my_schema 的新模式，并将所有者设置为 my_user)

CREATE SCHEMA my_schema AUTHORIZATION my_user;

4. 如果模式不存在则创建。(这个命令会尝试创建一个名为 my_schema 的新模式。如果模式已经存在，命令不会报错)

CREATE SCHEMA IF NOT EXISTS my_schema;

5. 创建模式并直接创建表。
   • 这个命令会创建一个名为 my_schema 的新模式，并将所有者设置为 my_user，然后在 my_schema 中创建一个名为 my_table 的新表。

CREATE SCHEMA my_schema AUTHORIZATION my_user
    CREATE TABLE my_table (id int);

6. 注意事项：
   • 模式是数据库对象的组织单位，不同模式中的对象可以具有相同的名称，而不会发生冲突。
   • 创建模式需要相应的权限，通常只有超级用户或数据库管理员可以创建新模式。
   • 在指定所有者时，指定的用户必须已经存在于数据库中。
7. 请确保在执行 CREATE SCHEMA 命令之前，你有足够的权限，并且替换示例中的 my_schema 和 my_user 为实际的模式名称和用户名称。
8. \dn 查看模式列表。
   • 以 pg_开头的名称是系统保留的模式名称，用户无法创建这样的模式。

postgres=# \dn+
                           List of schemas
   Name    |  Owner   |  Access privileges   |      Description       
-----------+----------+----------------------+------------------------
 my_schema | pg2022   |                      | 
 public    | postgres | postgres=UC/postgres+| standard public schema
           |          | =UC/postgres         | 
(2 rows)

修改模式

1. 使用 ALTER SCHEMA 语法修改模式：
   • name: 你想要修改的模式名称。
   • RENAME TO new_name: 这是一个子句，用于将现有模式的名称更改为 new_name。
   • OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }: 这是一个子句，用于更改模式的所有者。你可以指定一个新的所有者，或者使用特殊的角色标识符来指定当前的角色、用户或会话用户。

postgres=# \h ALTER SCHEMA
Command:     ALTER SCHEMA
Description: change the definition of a schema
Syntax:
ALTER SCHEMA name RENAME TO new_name
ALTER SCHEMA name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }

URL: https://www.postgresql.org/docs/14/sql-alterschema.html

2. 重命名一个模式。

-- 这个命令会将名为 old_schema_name 的模式重命名为 new_schema_name。
ALTER SCHEMA old_schema_name RENAME TO new_schema_name;

3. 更改模式的所有者。

-- 这个命令会将名为 old_schema_name 的模式重命名为 new_schema_name。
ALTER SCHEMA old_schema_name OWNER TO new_owner;

4. 将模式的所有者更改为当前用户。

-- 这个命令会将名为 old_schema_name 的模式的所有者更改为执行该命令的当前用户。
ALTER SCHEMA old_schema_name OWNER TO CURRENT_USER;

5. 将模式的所有者更改为当前角色。

-- 这个命令会将名为 old_schema_name 的模式的所有者更改为当前的角色，这可能与当前用户不同。
ALTER SCHEMA old_schema_name OWNER TO CURRENT_ROLE;

6. 将模式的所有者更改为会话用户。

-- 这个命令会将名为 old_schema_name 的模式的所有者更改为会话用户。
ALTER SCHEMA old_schema_name OWNER TO SESSION_USER;

7. 注意事项：
   • 要更改模式的所有者，执行命令的用户必须拥有该模式，或者必须是超级用户。
   • 重命名模式时，模式中的所有对象（如表、视图、函数等）都会自动更新为使用新模式名称。
   • 更改模式的所有者不会影响模式内对象的权限，这些权限需要单独设置。
8. 确保在执行 ALTER SCHEMA 命令之前，你有足够的权限，并且替换示例中的 old_schema_name、new_schema_name 和 new_owner 为实际的模式名称和新所有者名称。

删除模式

1. 使用 DROP SCHEMA 语法删除模式：
   • [ IF EXISTS ]: 这是一个可选子句，如果指定的模式不存在，则使用这个子句可以避免错误。
   • name: 你想要删除的模式名称。
   • [ CASCADE | RESTRICT ]: 这两个关键字定义了删除模式时的行为：
     • CASCADE: 删除模式时，同时删除所有依赖于该模式的对象。
     • RESTRICT: 如果有任何对象依赖于该模式，则拒绝删除操作。这是默认行为。

postgres=# \h DROP SCHEMA
Command:     DROP SCHEMA
Description: remove a schema
Syntax:
DROP SCHEMA [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

URL: https://www.postgresql.org/docs/14/sql-dropschema.html

2. 删除一个模式。

-- 这个命令会删除名为 schema_name 的模式，但如果该模式中有任何依赖的对象，则操作将失败。
DROP SCHEMA schema_name;

3. 如果存在则删除模式。

-- 如果名为 schema_name 的模式存在，则删除它；如果不存在，则不执行任何操作且不返回错误。
DROP SCHEMA IF EXISTS schema_name;

4. 删除模式及其所有依赖对象。

-- 这个命令会删除名为 schema_name 的模式及其所有依赖的对象。
DROP SCHEMA schema_name CASCADE;

5. 尝试删除模式，但如果有依赖则失败。

-- 这个命令与不指定任何选项的 DROP SCHEMA 相同，如果模式中有任何依赖的对象，则操作将失败。
DROP SCHEMA schema_name RESTRICT;

6. 注意事项：
   • 执行 DROP SCHEMA 命令的用户必须拥有该模式，或者必须是超级用户。
   • 删除模式是一个不可逆的操作，所有在该模式下的对象都将被永久删除，所以在执行此命令前应确保这是你想要的操作。
   • 使用 CASCADE 选项时要特别小心，因为它会删除所有依赖对象，这可能导致意外的数据丢失。
7. 在执行 DROP SCHEMA 命令之前，请确保备份所有重要数据，并且替换示例中的 schema_name 为实际要删除的模式名称。