PostgreSQL入门基本语法之DDL-(user、database、schema)

目录

 

前言

1、用户对象操作

1.1新增用户

1.1.1 语法

1.1.2 常用例子

1.2修改用户

1.2.1 语法

1.2.2 常用例子

1.3删除用户

1.3.1 语法

1.3.2 常用例子

2.数据库对象操作

2.1创建数据库

2.1.1 语法

2.1.2 常用例子

2.2 修改数据库

2.2.1 语法

2.2.2 常用例子

2.3 删除数据库

2.3.1 语法

2.3.2 常用例子

3.模式对象操作

3.1 创建模式

3.1.1 语法

3.1.2 常用例子

3.2 修改模式

3.2.1 语法

3.2.2 常用例子

3.3 删除模式

3.3.1 语法

3.3.2 常用例子

---

前言

数据库中所有使用的sql命令，都可以使用psql 登录数据库后，使用\h  命令  进行查看语法。如我们需要查看创建用户的语法（create user）

 

第一步：psql 登录数据库

psql -h localhost -p 5432 -U postgres -d postgres

 

第二步：查看create  user 语法

\h create user

1、用户对象操作

1.1新增用户

1.1.1 语法

命令：       CREATE USER

描述：       定义一个新数据库角色

语法：

CREATE USER 名称 [ [ WITH ] 选项 [ ... ] ]

 

选项可以是

 

      SUPERUSER | NOSUPERUSER

    | CREATEDB | NOCREATEDB

    | CREATEROLE | NOCREATEROLE

    | INHERIT | NOINHERIT

    | LOGIN | NOLOGIN

    | REPLICATION | NOREPLICATION

    | BYPASSRLS | NOBYPASSRLS

    | CONNECTION LIMIT 连接限制

    | [ ENCRYPTED ] PASSWORD '口令'

    | VALID UNTIL '时间戳'

    | IN ROLE 角色名称 [, ...]

    | IN GROUP 角色名称 [, ...]

    | ROLE 角色名称 [, ...]

    | ADMIN 角色名称 [, ...]

    | USER 角色名称 [, ...]

    | SYSID uid

1.1.2 常用例子

例一：新建用户及设置用户密码

create user test_user password 'test_user';

创建test_user用户并设置密码为test_user。

例二：新建用户并设置用户权限

create user test1_user superuser password 'test1_user';

创建test1_user用户并设置为超级用户且设置密码

1.2修改用户

1.2.1 语法

命令：       ALTER USER

描述：       更改数据库角色

语法：

ALTER USER role_specification [ WITH ] 选项 [ ... ]

 

选项可以是

 

      SUPERUSER | NOSUPERUSER

    | CREATEDB | NOCREATEDB

    | CREATEROLE | NOCREATEROLE

    | INHERIT | NOINHERIT

    | LOGIN | NOLOGIN

    | REPLICATION | NOREPLICATION

    | BYPASSRLS | NOBYPASSRLS

    | CONNECTION LIMIT 连接限制

    | [ ENCRYPTED ] PASSWORD '口令'

    | VALID UNTIL '时间戳'

 

ALTER USER 名称 RENAME TO 新的名称

 

ALTER USER { role_specification | ALL } [ IN DATABASE 数据库名称 ] SET 配置参数 { TO | = } { 值 | DEFAULT }

ALTER USER { role_specification | ALL } [ IN DATABASE 数据库名称 ] SET 配置参数 FROM CURRENT

ALTER USER { role_specification | ALL } [ IN DATABASE 数据库名称 ] RESET 配置参数

ALTER USER { role_specification | ALL } [ IN DATABASE 数据库名称 ] RESET ALL

 

这里role_specification可以是：

 

    角色名称

  | CURRENT_USER

  | SESSION_USER

1.2.2 常用例子

例一：修改用户密码

alter user test_user password 'test123';

将用户test_user的密码修改为test123。

例二：修改用户权限

alter user test_user superuser;

将用户test_user的权限修改为超级用户权限

例三：修改用户密码有效期

alter user test_user valid until '2020-04-17 00:00:00';

修改用户test_user密码的有效时间为 2020-04-17 00:00:00。

1.3删除用户

1.3.1 语法

命令：       DROP USER

描述：       移除一个数据库成员

语法：

DROP USER [ IF EXISTS ] 名称 [, ...]

1.3.2 常用例子

drop user if exists test1_user;

若存在用户test1_user则删除该用户。

2.数据库对象操作

2.1创建数据库

2.1.1 语法

命令：       CREATE DATABASE

描述：       建立新的数据库

语法：

CREATE DATABASE 名称

    [ [ WITH ] [ OWNER [=] 用户名 ]

           [ TEMPLATE [=] 模版 ]

           [ ENCODING [=] 字符集编码 ]

           [ LC_COLLATE [=] 排序规则 ]

           [ LC_CTYPE [=] 字符分类 ]

           [ TABLESPACE [=] 表空间的名称 ]

           [ ALLOW_CONNECTIONS [=] allowconn ]

           [ CONNECTION LIMIT [=] 连接限制 ]

           [ IS_TEMPLATE [=] istemplate ] ]

2.1.2 常用例子

create database test_db owner test_user;

创建数据库test_db 并指定数据库的所属者用户为test_user;(当然，前提是创建了用户test_user)

2.2 修改数据库

2.2.1 语法

命令：       ALTER DATABASE

描述：       更改一个数据库

语法：

ALTER DATABASE 名称 [ [ WITH ] 选项 [ ... ] ]

 

选项可以是

 

    ALLOW_CONNECTIONS allowconn

    CONNECTION LIMIT 连接限制

    IS_TEMPLATE istemplate

 

ALTER DATABASE 名称 RENAME TO 新的名称

ALTER DATABASE 名称 OWNER TO { 新的属主 | CURRENT_USER | SESSION_USER }

ALTER DATABASE 名称 SET TABLESPACE 新的表空间

ALTER DATABASE 名称 SET 配置参数 { TO | = } { 值 | DEFAULT }

ALTER DATABASE 名称 SET 配置参数 FROM CURRENT

ALTER DATABASE 名称 RESET 配置参数

ALTER DATABASE 名称 RESET ALL

2.2.2 常用例子

例一：修改数据库名

alter database test_db rename to t_db;

将名为test_db的数据库重命名为t_db;

例二：修改数据库属主

alter database t_db owner to postgres;

将数据库t_db的属主修改为postgres（对比例一查看，属主由test_user重置为postgres）

2.3 删除数据库

2.3.1 语法

命令：       DROP DATABASE

描述：       移除数据库

语法：

DROP DATABASE [ IF EXISTS ] 名称

2.3.2 常用例子

drop database if exists t_db;

删除数据库t_db(对比2.2.2中的例二我们可以看到数据库t_db已被删除)

3.模式对象操作

准备测试用户及数据库

创建了用户test_user和数据库test_db,并且test_db的属主是test_user。

图中\c test_db test_user 为在psql命令行中切换数据库及登录用户

命令详情：\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}

 \c 数据库名  用户名       同时切换数据库及登录用户

 \c  数据库名              只切换数据库

 \c - 用户名               只切换登录用户

 

3.1 创建模式

3.1.1 语法

命令：       CREATE SCHEMA

描述：       建立新的架构模式

语法：

CREATE SCHEMA 模式名称 [ AUTHORIZATION role_specification ] [ 模式中对象 [ ... ] ]

CREATE SCHEMA AUTHORIZATION role_specification [ 模式中对象 [ ... ] ]

CREATE SCHEMA IF NOT EXISTS 模式名称 [ AUTHORIZATION role_specification ]

CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification

 

这里role_specification可以是：

 

    用户名

  | CURRENT_USER

  | SESSION_USER

3.1.2 常用例子

例一：创建一个模式

create schema if not exists my_schema;

创建一个名为my_schema的模式

例二：创建一个与当前用户同名的模式

create schema if not exists authorization current_user;

如下图，当前用户为test_user，使用该语法创建同名模式为test_user;

3.2 修改模式

3.2.1 语法

命令：       ALTER SCHEMA

描述：       更改架构模式的定义

语法：

ALTER SCHEMA 名称 RENAME TO 新的名称

ALTER SCHEMA 名称 OWNER TO { 新的属主 | CURRENT_USER | SESSION_USER }

3.2.2 常用例子

例一：修改模式名

alter schema my_schema rename to test_schema;

将名为my_schema的模式名重命名为 test_schema。

例二：修改模式属主

alter schema test_schema owner to postgres;

将模式test_schema的属主修改为postgres用户。

3.3 删除模式

3.3.1 语法

命令：       DROP SCHEMA

描述：       移除一个模式

语法：

DROP SCHEMA [ IF EXISTS ] 名称 [, ...] [ CASCADE | RESTRICT ]

3.3.2 常用例子

drop schema if exists test_schema;