Postgresql学习笔记之——逻辑结构管理之数据库操作

Postgresql数据库服务下可以管理多个数据库，当应用链接到一个数据库时，一般只能访问这个数据库中的各个模式的数据，而不能访问其他数据库中的内容（除非使用了dblink等手段）。

一、数据库的基本操作

数据库的基本操作包括创建数据库、删除数据库、修改数据库等。

1.创建数据库。

语法如下：

CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ] ]
           

参数解释：
name：创建的数据库的名字。

OWNER [=] user_name：将拥有要创建的新数据库的用户的角色名，或者默认使用默认值(即执行命令的用户)。如果你要创建属于另一个角色的数据库，你必须是该角色的直接或间接成员，或者是超级用户。

TEMPLATE [=] template：用于创建新数据库的模板的名称，或者默认使用默认模板(template1)

ENCODING [=] encoding：在新数据库中使用的字符集编码。指定一个字符串常量(例如，‘SQL_ASCII’)或一个整数编码号，或者默认使用默认编码(即模板数据库的编码)。

LC_COLLATE [=] lc_collate：在新数据库中使用的排序顺序(LC_COLLATE)。这会影响应用于字符串的排序顺序，例如在order BY查询中，以及在文本列上的索引中使用的顺序。默认是使用模板数据库的排序顺序。

LC_CTYPE [=] lc_ctype：要在新数据库中使用的字符分类(LC_CTYPE)。这将影响字符的分类，如下、上和数字。默认是使用模板数据库的字符分类。

TABLESPACE [=] tablespace_name：将与新数据库关联的表空间的名称，或者默认使用模板数据库的表空间。这个表空间将是用于在这个数据库中创建的对象的默认表空间。

ALLOW_CONNECTIONS [=]allowconn：如果为假，则没有人可以连接到此数据库。默认值为true，允许连接(除非受到其他机制的限制，如GRANT/REVOKE CONNECT)。

CONNECTION LIMIT [=] connlimit：此数据库可以建立多少个并发连接。-1(默认值)表示没有限制。

IS_TEMPLATE [=] istemplate：如果为真，则任何具有CREATEDB特权的用户都可以克隆此数据库;如果为false(默认值)，那么只有超级用户或数据库所有者可以克隆它。

PS：在日常使用中会在安装Postgresql数据库时已经指定了整个服务的字符集，一般都是使用 ”UTF8“ 字符集来支持中文。

创建数据库时注意：

1.CREATE DATABASE命令不能在事务内执行，例如：

postgres=# begin;
BEGIN
postgres=# create database test;
ERROR:  CREATE DATABASE cannot run inside a transaction block

2.创建数据库报错提示 ”could not initialize database directory“ ，通常问题原因时对数据目录或者磁盘没有足够权限。

3.移除（删除）数据库使用命令：DROP DATABASE name;，（后面会详细说明使用方式）。

4.在shell命令行可以通过 ”createdb“ 命令直接创建数据库（具体使用方式：https://blog.csdn.net/qq_32838955/article/details/104587601 ） 。

5.数据库级别的配置参数不会直接从 template 数据库中拷贝出来。需要手动操作。
Database-level configuration parameters (set via ALTER DATABASE) are not copied from the template database.

6.虽然可以通过指定template1的名称作为模板来复制template1以外的数据库，但是这还不是一个通用的“复制数据库”工具。主要的限制是在复制模板数据库时不能连接其他会话。创建数据库将失败，如果任何其他连接存在时，它启动;否则，到模板数据库的新连接将被锁定，直到创建数据库完成。
Although it is possible to copy a database other than template1 by specifying its name as the template, this is not (yet) intended as a general-purpose “COPY DATABASE” facility. The principal limitation is that no other sessions can be connected to the template database while it is being copied. CREATE DATABASE will fail if any other connection exists when it starts; otherwise, new connections to the template database are locked out until CREATE DATABASE completes. See Section 22.3 for more information.

7.为新数据库指定的字符集编码必须与所选的语言环境设置(LC_COLLATE和LC_CTYPE)兼容。如果语言环境是C(或者等同于POSIX)，那么所有的编码都是允许的，但是对于其他语言环境设置，只有一种编码可以正常工作。(但在Windows上，UTF-8编码可以用于任何语言环境。)CREATE DATABASE允许超级用户指定SQL_ASCII编码，而不管语言环境设置如何，但是这种选择是不赞成的，如果在数据库中存储与语言环境不兼容的数据，可能会导致字符字符串函数的错误行为。
The character set encoding specified for the new database must be compatible with the chosen locale settings (LC_COLLATE and LC_CTYPE). If the locale is C (or equivalently POSIX), then all encodings are allowed, but for other locale settings there is only one encoding that will work properly. (On Windows, however, UTF-8 encoding can be used with any locale.) CREATE DATABASE will allow superusers to specify SQL_ASCII encoding regardless of the locale settings, but this choice is deprecated and may result in misbehavior of character-string functions if data that is not encoding-compatible with the locale is stored in the database.

8.编码和地区设置必须与模板数据库的设置相匹配，除非使用template0作为模板。这是因为其他数据库可能包含与指定编码不匹配的数据，或者可能包含受LC_COLLATE和LC_CTYPE影响排序顺序的索引。复制这些数据将导致数据库根据新的设置而损坏。但是，template0已知不包含任何可能受到影响的数据或索引。
The encoding and locale settings must match those of the template database, except when template0 is used as template. This is because other databases might contain data that does not match the specified encoding, or might contain indexes whose sort ordering is affected by LC_COLLATE and LC_CTYPE. Copying such data would result in a database that is corrupt according to the new settings. template0, however, is known to not contain any data or indexes that would be affected.

9.连接限制选项（CONNECTION LIMIT）并不是绝对的，如果两个新会话几乎同时开始，而数据库只剩下一个连接“插槽”，那么两个会话都可能失败。此外，对于超级用户或后台工作进程，不强制执行此限制。

安装示例：

1.默认创建数据库：

CREATE DATABASE lusiadas;

2.使用salesspace的默认表空间创建用户salesapp拥有的数据库sales:

CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;

3.创建一个数据库music，与默认不同的地区：

CREATE DATABASE music
    LC_COLLATE 'sv_SE.utf8' LC_CTYPE 'sv_SE.utf8'
    TEMPLATE template0;

在本示例中，如果指定的地区与template1中的地区不同，则需要TEMPLATE template0子句。(如果不是，那么显式地指定语言环境是多余的。）

4.创建一个数据库music2有不同地区和不同的字符集编码：

CREATE DATABASE music2
    LC_COLLATE 'sv_SE.iso885915' LC_CTYPE 'sv_SE.iso885915'
    ENCODING LATIN9
    TEMPLATE template0;

注意：指定的区域设置和编码设置必须匹配，否则将报告错误。请注意，语言环境名称是特定于操作系统的，因此上面的命令可能不会在所有地方以相同的方式工作。

2.修改数据库

ALTER DATABASE — 更改一个数据库

修改数据库必须登录到数据库中才可以修改，没有类似createdb这种客户端命令工具。

具体语法：

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

option选项可以是如下:

    ALLOW_CONNECTIONS allowconn
    CONNECTION LIMIT connlimit
    IS_TEMPLATE istemplate

参数解析：
1.name：需要更改属性的数据库名称。

2.ALLOW_CONNECTIONS allowconn：如果为 false ，则修改的数据库无法再进行链接。

3.CONNECTION LIMIT connlimit：修改数据库的链接数，如果设置为 -1 表示不限制。

4.IS_TEMPLATE istemplate：如果为真，则任何具有CREATEDB特权的用户都可以克隆此数据库;如果为假，则只有超级用户或数据库所有者可以克隆它。

ALTER DATABASE name RENAME TO new_name

ALTER DATABASE name OWNER TO { new_owner | 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

参数解析：

1.new_name：更改数据库为新的名称。

2.new_owner：更改数据库新的owner。

3.new_tablespace：更改数据库新的表空间（这种形式的命令不能在事务块中执行）。

4.configuration_parameter
value：
将指定配置参数的此数据库的会话默认设置为给定值。如果值是默认值，或者使用重置，那么特定于数据库的设置将被删除，因此系统范围内的默认设置将在新会话中继承。使用RESET ALL清除所有数据库特定的设置。SET FROM CURRENT将会话参数的当前值保存为特定于数据库的值。

数据库修改注意事项：

1.只有超级用户或者数据库拥有者可以修改数据库参数。

2.只有超级用户和数据库拥有者可以重命名数据库，非超级用户或者数据库拥有者有CREATEDB权限也可以进行重命名操作（当前链接的数据库不能重命名）。

3.要更改数据库的owner，您必须拥有数据库，并且是新拥有角色的直接或间接成员，并且必须拥有CREATEDB特权。(注意，超级用户自动拥有所有这些特权。)

4.只有数据库所有者或超级用户可以这样做;您还必须有新表空间的create权限。此命令将数据库中旧的默认表空间中的任何表或索引物理移动到新表空间。对于这个数据库，新的默认表空间必须是空的，并且没有客户端连接到数据库。非默认表空间中的表和索引不受影响。

5.其余的表单更改了PostgreSQL数据库运行时配置变量的会话缺省值。每当随后在该数据库中启动一个新会话时，指定的值将成为会话默认值。特定于数据库的默认设置将覆盖postgresql.conf中提供的设置或从postgres命令行接收的设置。只有数据库所有者或超级用户可以更改数据库的会话缺省值。某些变量不能这样设置，或者只能由超级用户设置。

示例：

在数据库test禁用索引扫描:

ALTER DATABASE test SET enable_indexscan TO off;

PS:修改数据库操作中，除了更改表空间，其他操作可以在事务进行中进行操作。

三、删除数据库

基本语法：

DROP DATABASE [ IF EXISTS ] name

参数解析：

1.IF EXISTS：如果删除的数据库不存在也不会报错。

2.name：要删除的数据库名称。

注意事项：

1.DROP DATABASE 命令不能在事务进行中执行。

2.删除命令不能在有客户端链接时删除正在被链接的库。

示例：

1.删除数据库 test：

DROP DATABASE test;

2.如果一个数据存在将其删除，如果不存在，删除命令也不会报错：

DROP DTABASE IF EXISTS test;

dtopdb工具：

和createdb一样，dropdb时客户端shell命令工具，在shell下执行删除数据库命令。

语法：
dropdb [connection-option…] [option…] dbname

dropdb工具接受一下参数进行指定的删除命令:

1.dbname：指定要被删除的数据库名称。

2.-e 或 --echo：显示dropdb生成的并发送给数据库服务的命令详情。

3.-i 或 --interactive：在删除前显示一些验证提示。

4.-V 或 --version：打印dropdb工具的版本号。

5.–if-exists：删除的数据库不存在也不会提示错误

6.-? 或 --help：查看帮助手册。

dropdb 同样接受指定的链接参数:

1.-h host 或 --host=host：指定运行数据库服务的机器的主机名或IP地址。如果该值以斜线开头，则将其用作Unix域套接字的目录。

2.-p port 或 --port=port：指定数据库服务运行的服务器监听的TCP端口或本地Unix域套接字文件扩展名

3.-U username 或 --username=username ：链接的数据库用户名。

4.-w 或 --no-password ：永远不要发出密码提示。如果服务器需要口令身份验证，而通过.pgpass文件等其他方式无法获得口令，则连接尝试将失败。此选项在没有用户输入密码的批处理作业和脚本中非常有用。
Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password.

5.-W 或 --password：强制dropdb在连接数据库之前提示输入密码。

This option is never essential, since dropdb will automatically prompt for a password if the server demands password authentication. However, dropdb will waste a connection attempt finding out that the server wants a password. In some cases it is worth typing -W to avoid the extra connection attempt.

6.–maintenance-db=dbname：指定要连接的数据库的名称，以便删除目标数据库。如果没有指定，将使用postgres数据库;如果不存在(或正在删除数据库)，则使用template1。
Specifies the name of the database to connect to in order to drop the target database. If not specified, the postgres database will be used; if that does not exist (or is the database being dropped), template1 will be used.

示例：

1.在默认的数据库服务上删除数据库 demo：

$ dropdb demo

2.删除数据库demo，并指定端口5000，主机名 eden，并进行删除前的提示，并打印出删除命令详细：

$ dropdb -p 5000 -h eden -i -e demo
Database “demo” will be permanently deleted.
Are you sure? (y/n) y
DROP DATABASE demo;