OpenGauss入门到实战【这次高斯不是数学家】

把之前整理的openGauss的笔记与大家一起分享，
就算对数据库不熟悉，有这么详细的笔记，也可以上手gaussdb~~
通过个人总结的一些习题和案例，和大家一起学习gsdb

创建数据库

背景信息

• 初始时，openGauss 包含两个模板数据库template0、template1，以及一个默认的用户数据库 postgres。
• CREATE DATABASE 实际上通过拷贝模板数据库来创建新数据库。默认情况下，拷贝 template0。需要避免使用客户端或其他手段连接及操作两个模板数据库。
• 模板 template0 不允许用户连接；模板 template1 只允许数据库初始用户和系统管理员连接，普通用户无法连接。
• openGauss建议创建的数据库总数目上限为128个。
• 数据库系统中会有多个数据库，但是客户端程序一次只能连接一个数据库。也不能在不同的数据库之间相互查询。一个openGauss中存在多个数据库时，需要通过-d参数指定相应的数据库实例进行连接。

注意事项

• 只有拥有CREATEDB权限的用户才可以创建新数据库，系统管理员默认拥有此权限。
• 不能在事务块中执行创建数据库语句。
• 在创建数据库过程中，出现类似“Permission denied”的错误提示，可能是由于文件系统上数据目录的权限不足。出现类似“No space left on device”的错误提示，可能是由于磁盘满引起的。

语法格式

CREATE DATABASE database_name      [ [ WITH ] { [ OWNER [=] user_name ] |                 
								[ TEMPLATE [=] template ] |                 
								[ ENCODING [=] encoding ] |                 
								[ LC_COLLATE [=] lc_collate ] |                 
								[ LC_CTYPE [=] lc_ctype ] |                 
								[ DBCOMPATIBILITY [=] compatibilty_type ] |                 
								[ TABLESPACE [=] tablespace_name ] |                 
                                   [ CONNECTION LIMIT [=] connlimit ]}[...] ];

参数说明

• database_name

  数据库名称。

  取值范围：字符串，要符合标识符的命名规范。

• OWNER [ = ] user_name

  数据库所有者。缺省时，新数据库的所有者是当前用户。

  取值范围：已存在的用户名。

• TEMPLATE [ = ] template

  模板名。即从哪个模板创建新数据库。openGauss采用从模板数据库复制的方式来创建新的数据库。初始时，openGauss包含两个模板数据库template0、template1，以及一个默认的用户数据库postgres。

  取值范围：仅template0。

• ENCODING [ = ] encoding

  指定数据库使用的字符编码，可以是字符串（如’SQL_ASCII’）、整数编号。

  不指定时，默认使用模版数据库的编码。模板数据库template0和template1的编码默认与操作系统环境相关。template1不允许修改字符编码，因此若要变更编码，请使用template0创建数据库。

  常用取值：GBK、UTF8、Latin1。

• LC_COLLATE [ = ] lc_collate

  指定新数据库使用的字符集。例如，通过lc_collate = 'zh_CN.gbk’设定该参数。

  该参数的使用会影响到对字符串的排序顺序（如使用ORDER BY执行，以及在文本列上使用索引的顺序）。默认是使用模板数据库的排序顺序。

  取值范围：有效的排序类型。

• LC_CTYPE [ = ] lc_ctype

  指定新数据库使用的字符分类。例如，通过lc_ctype = 'zh_CN.gbk’设定该参数。该参数的使用会影响到字符的分类，如大写、小写和数字。默认是使用模板数据库的字符分类。

  取值范围：有效的字符分类。

• DBCOMPATIBILITY [ = ] compatibility_type

  指定兼容的数据库的类型，默认兼容O。

  取值范围：A、B、C、PG。分别表示兼容O、MY、TD和POSTGRES。

• TABLESPACE [ = ] tablespace_name

  指定数据库对应的表空间。

  取值范围：已存在表空间名。

• CONNECTION LIMIT [ = ] connlimit

  数据库可以接受的并发连接数。

  取值范围：>=-1的整数。默认值为-1，表示没有限制。

示例语句

--创建一个GBK编码的数据库music（本地环境的编码格式必须也为GBK）。  
openGauss=# CREATE DATABASE music ENCODING 'GBK' template = template0;
--创建数据库music2，并指定所有者为jim。
openGauss=# CREATE DATABASE music2 OWNER jim;
--用模板template0创建数据库music3，并指定所有者为jim。  
openGauss=# CREATE DATABASE music3 OWNER jim TEMPLATE template0;
--创建兼容TD格式的数据库。  
openGauss=# CREATE DATABASE td_compatible_db DBCOMPATIBILITY 'C';
--创建兼容A格式的数据库。  
openGauss=# CREATE DATABASE ora_compatible_db DBCOMPATIBILITY 'A';

示例

根据提示，在右侧命令行进行相关操作。首先点击命令行右上角的重置命令行，使得环境恢复为初始状态。
然后用模板 template0 创建一个名为 accessdb 的数据库，并指定所有者为 gaussdb。

su omm
gsql -d postgres -p 5432  
CREATE DATABASE accessdb OWNER gaussdb;

修改数据库

相关知识

1.openGauss 数据库的基本操作，2.SQL 数据库修改语法。

注意事项

• 不能重命名当前使用的数据库，如果需要重新命名，须连接至其他数据库上。
• 只有数据库的所有者或者被授予了数据库ALTER权限的用户才能执行ALTER DATABASE命令，系统管理员默认拥有此权限。针对所要修改属性的不同，还有以下权限约束：
  • 修改数据库名称，必须拥有CREATEDB权限。
  • 修改数据库所有者，当前用户必须是该 database 的所有者或者系统管理员，必须拥有 CREATEDB 权限，且该用户是新所有者角色的成员。
  • 修改数据库默认表空间，必须拥有新表空间的 CREATE 权限。这个语句会从物理上将一个数据库原来缺省表空间上的表和索引移至新的表空间。注意不在缺省表空间的表和索引不受此影响。

语法格式

ALTER TABLE语句用于修改数据库的属性，包括它的名称、所有者、连接数限制、对象隔离属性等。

• 修改数据库的最大连接数。

  ALTER DATABASE database_name       [ [ WITH ] CONNECTION LIMIT connlimit ];  
  

• 修改数据库名称。

  ALTER DATABASE database_name       RENAME TO new_name;  
  

• 修改数据库所属者。

  ALTER DATABASE database_name       OWNER TO new_owner;  
  

• 修改数据库默认表空间。

  ALTER DATABASE database_name       SET TABLESPACE new_tablespace;  
  

• 修改数据库指定会话参数值。

  ALTER DATABASE database_name       SET configuration_parameter { { TO | = } { value | DEFAULT } | FROM CURRENT };  
  

• 数据库配置参数重置。

  ALTER DATABASE database_name RESET       { configuration_parameter | ALL };  
  

• 修改数据库对象隔离属性。

  ALTER DATABASE database_name [ WITH ] { ENABLE | DISABLE } PRIVATE OBJECT;  
  

  说明：

  • 修改数据库的对象隔离属性时须连接至该数据库，否则无法更改。

参数说明

• database_name

  需要修改属性的数据库名称。

  取值范围：字符串，要符合标识符的命名规范。

• connlimit

  数据库可以接收的最大并发连接数（管理员用户连接除外）。

  取值范围：整数，建议填写1~50的整数。-1（缺省）表示没有限制。

• new_name

  数据库的新名称。

  取值范围：字符串，要符合标识符的命名规范。

• new_owner

  数据库的新所有者。

  取值范围：字符串，有效的用户名。

• new_tablespace

  数据库新的默认表空间，该表空间为数据库中已经存在的表空间。默认的表空间为 pg_default。

  取值范围：字符串，有效的表空间名。

• configuration_parameter

  value

  把指定的数据库会话参数值设置为给定的值。如果 value 是 DEFAULT 或者 RESET，则在新的会话中使用系统的缺省设置。 OFF 关闭设置。

  取值范围：字符串，

  • DEFAULT
  • OFF
  • RESET

• FROM CURRENT

  根据当前会话连接的数据库设置该参数的值。

• RESET configuration_parameter

  重置指定的数据库会话参数值。

• RESET ALL

  重置全部的数据库会话参数值。

示例语句

--设置music数据库的连接数为10。  
openGauss=# ALTER DATABASE music CONNECTION LIMIT= 10;
--将music名称改为music4。  
openGauss=# ALTER DATABASE music RENAME TO music4;
--将数据库music2的所属者改为tom。  
openGauss=# ALTER DATABASE music2 OWNER TO tom;
--设置music3的表空间为PG_DEFAULT。  
openGauss=# ALTER DATABASE music3 SET TABLESPACE PG_DEFAULT;
--关闭在数据库music3上缺省的索引扫描。  
openGauss=# ALTER DATABASE music3 SET enable_indexscan TO off;
--重置enable_indexscan参数。  
openGauss=# ALTER DATABASE music3 RESET enable_indexscan;  

示例

将accessdb数据库重命名为human_tpcds。

su omm
gsql -d postgres -p 5432  
ALTER DATABASE accessdb RENAME TO human_tpcds;

删除数据库

相关知识

1.openGauss 数据库的基本操作，2.SQL 数据库删除语法。

注意事项

• 只有数据库所有者或者被授予了数据库 DROP 权限的用户有权限执行 DROP DATABASE 命令，系统管理员默认拥有此权限。
• 不能对系统默认安装的三个数据库（POSTGRES、TEMPLATE0和TEMPLATE1）执行删除操作，系统做了保护。如果想查看当前服务中有哪几个数据库，可以用 gsql 的 \l 命令查看。
• 如果有用户正在与要删除的数据库连接，则删除操作失败。
• 不能在事务块中执行 DROP DATABASE 命令。
• 如果执行 DROP DATABASE 失败，事务回滚，需要再次执行一次 DROP DATABASE IF EXISTS。

语法格式

删除数据库的语法格式比较简单：

DROP DATABASE [ IF EXISTS ] database_name ;  

注意：
DROP DATABASE一旦执行将无法撤销，请谨慎使用。

参数说明

• IF EXISTS

  如果指定的数据库不存在，则发出一个notice而不是抛出一个错误。

• database_name

  要删除的数据库名称。

  取值范围：字符串，已存在的数据库名称。

示例语句

--删除数据库。  
openGauss=# DROP DATABASE music2;  
openGauss=# DROP DATABASE music3;  
openGauss=# DROP DATABASE music4;  

示例

删除human_tpcds数据库。

su omm
gsql -d postgres -p 5432  
DROP DATABASE human_tpcds;

创建数据表

相关知识

1.openGauss 的常用操作，2.SQL 创建数据表相关语法。

背景知识

创建数据表命令在当前数据库中创建一个新的空白表，该表由命令执行者所有。

表是建立在数据库中的，在不同的数据库中可以存放相同的表。甚至可以通过使用模式在同一个数据库中创建相同名称的表。此处先学习最基本的情况。

注意事项

• 列存表不支持数组。
• 创建列存表的数量建议不超过1000个。
• 列存表的表级约束只支持PARTIAL CLUSTER KEY、UNIQUE、PRIAMRY KEY，不支持外键等表级约束。
• 列存表的字段约束只支持NULL、NOT NULL、DEFAULT常量值、UNIQUE和PRIMARY KEY。
• 每张表的列数最大为1600，具体取决于列的类型，所有列的大小加起来不能超过8192 byte，text、varchar、char等长度可变的类型除外。

语法格式

CREATE [ [ GLOBAL | LOCAL ] [ TEMPORARY | TEMP ] | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name   
    ({ column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]  
        | table_constraint  
        | LIKE source_table [ like_option [...] ] }  
        [, ... ])  
    [ WITH ( {storage_parameter = value} [, ... ] ) ]  
    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]  
    [ COMPRESS | NOCOMPRESS ]  
    [ TABLESPACE tablespace_name ];  

• 其中列约束column_constraint为：

  [ CONSTRAINT constraint_name ]  
  { NOT NULL |  
    NULL |  
    CHECK ( expression ) |  
    DEFAULT default_expr |  
    UNIQUE index_parameters |  
    ENCRYPTED WITH ( COLUMN_ENCRYPTION_KEY = column_encryption_key, ENCRYPTION_TYPE = encryption_type_value ) |  
    PRIMARY KEY index_parameters |  
    REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]  
        [ ON DELETE action ] [ ON UPDATE action ] }  
  [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]  
  
  

• 其中列的压缩可选项compress_mode为：

  { DELTA | PREFIX | DICTIONARY | NUMSTR | NOCOMPRESS }  
  

• 其中表约束table_constraint为：

  [ CONSTRAINT constraint_name ]  
  { CHECK ( expression ) |  
    UNIQUE ( column_name [, ... ] ) index_parameters |  
    PRIMARY KEY ( column_name [, ... ] ) index_parameters |  
    FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ (refcolumn [, ... ] ) ]  
        [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] |  
    PARTIAL CLUSTER KEY ( column_name [, ... ] ) }  
  [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ] 
  

• 其中like选项like_option为：

  { INCLUDING | EXCLUDING } { DEFAULTS | GENERATED | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | PARTITION | RELOPTIONS | ALL }  
  

• 其中索引参数index_parameters为：

  [ WITH ( {storage_parameter = value} [, ... ] ) ]  
  [ USING INDEX TABLESPACE tablespace_name ]  
  参数说明
  

参数说明

由于该语句参数以及选项比较多，此处仅介绍部分常用选项以及参数的意义，完整内容读者可参阅其他资料。

• GLOBAL | LOCAL

  创建临时表时可以在TEMP或TEMPORARY前指定GLOBAL或LOCAL关键字。如果指定GLOBAL关键字，openGauss会创建全局临时表，否则openGauss会创建本地临时表。

• TEMPORARY | TEMP

  如果指定TEMP或TEMPORARY关键字，则创建的表为临时表。临时表分为全局临时表和本地临时表两种类型。创建临时表时如果指定GLOBAL关键字则为全局临时表，否则为本地临时表。

  全局临时表的元数据对所有会话可见，会话结束后元数据继续存在。会话与会话之间的用户数据、索引和统计信息相互隔离，每个会话只能看到和更改自己提交的数据。全局临时表有两种模式：一种是基于会话级别的(ON COMMIT PRESERVE ROWS), 当会话结束时自动清空用户数据；一种是基于事务级别的(ON COMMIT DELETE ROWS), 当执行commit或rollback时自动清空用户数据。建表时如果没有指定ON COMMIT选项，则缺省为会话级别。与本地临时表不同，全局临时表建表时可以指定非pg_temp_开头的schema。

  本地临时表只在当前会话可见，本会话结束后会自动删除。因此，在除当前会话连接的数据库节点故障时，仍然可以在当前会话上创建和使用临时表。由于临时表只在当前会话创建，对于涉及对临时表操作的DDL语句，会产生DDL失败的报错。因此，建议DDL语句中不要对临时表进行操作。TEMP和TEMPORARY等价。

• IF NOT EXISTS

  如果已经存在相同名称的表，不会报出错误，而会发出通知，告知通知此表已存在。

• table_name

  要创建的表名。

• column_name

  新表中要创建的字段名。

• data_type

  字段的数据类型。

• compress_mode

  表字段的压缩选项。该选项指定表字段优先使用的压缩算法。行存表不支持压缩。

  取值范围：DELTA、PREFIX、DICTIONARY、NUMSTR、NOCOMPRESS

• COLLATE collation

  COLLATE子句指定列的排序规则（该列必须是可排列的数据类型）。如果没有指定，则使用默认的排序规则。排序规则可以使用“select * from pg_collation;”命令从pg_collation系统表中查询，默认的排序规则为查询结果中以default开始的行。

• LIKE source_table [ like_option … ]

  LIKE子句声明一个表，新表自动从这个表中继承所有字段名及其数据类型和非空约束。

  新表与源表之间在创建动作完毕之后是完全无关的。在源表做的任何修改都不会传播到新表中，并且也不可能在扫描源表的时候包含新表的数据。

  被复制的列和约束并不使用相同的名称进行融合。如果明确的指定了相同的名称或者在另外一个LIKE子句中，将会报错。

• WITH ( { storage_parameter = value } [, … ] )

  这个子句为表或索引指定一个可选的存储参数。

• ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP }

  ON COMMIT选项决定在事务中执行创建临时表操作，当事务提交时，此临时表的后续操作。有以下三个选项，当前支持PRESERVE ROWS和DELETE ROWS选项。

  • PRESERVE ROWS（缺省值）：提交时不对临时表做任何操作，临时表及其表数据保持不变。
  • DELETE ROWS：提交时删除临时表中数据。
  • DROP：提交时删除此临时表。只支持本地临时表，不支持全局临时表。

• COMPRESS | NOCOMPRESS

  创建新表时，需要在CREATE TABLE语句中指定关键字COMPRESS，这样，当对该表进行批量插入时就会触发压缩特性。该特性会在页范围内扫描所有元组数据，生成字典、压缩元组数据并进行存储。指定关键字NOCOMPRESS则不对表进行压缩。行存表不支持压缩。

  缺省值：NOCOMPRESS，即不对元组数据进行压缩。

• TABLESPACE tablespace_name

  创建新表时指定此关键字，表示新表将要在指定表空间内创建。如果没有声明，将使用默认表空间。

• CONSTRAINT constraint_name

  列约束或表约束的名称。可选的约束子句用于声明约束，新行或者更新的行必须满足这些约束才能成功插入或更新。

  定义约束有两种方法：

  • 列约束：作为一个列定义的一部分，仅影响该列。
  • 表约束：不和某个列绑在一起，可以作用于多个列。

• NOT NULL

  字段值不允许为NULL。

• NULL

  字段值允许为NULL ，这是缺省值。

  这个子句只是为和非标准SQL数据库兼容。不建议使用。

• CHECK ( expression )

  CHECK约束声明一个布尔表达式，每次要插入的新行或者要更新的行的新值必须使表达式结果为真或未知才能成功，否则会抛出一个异常并且不会修改数据库。

  声明为字段约束的检查约束应该只引用该字段的数值，而在表约束里出现的表达式可以引用多个字段。

• DEFAULT default_expr

  DEFAULT子句给字段指定缺省值。该数值可以是任何不含变量的表达式(不允许使用子查询和对本表中的其他字段的交叉引用)。缺省表达式的数据类型必须和字段类型匹配。

  缺省表达式将被用于任何未声明该字段数值的插入操作。如果没有指定缺省值则缺省值为NULL 。

• UNIQUE index_parameters

  UNIQUE ( column_name [, … ] ) index_parameters

  UNIQUE约束表示表里的一个字段或多个字段的组合必须在全表范围内唯一。

  对于唯一约束，NULL被认为是互不相等的。

• PRIMARY KEY index_parameters

  PRIMARY KEY ( column_name [, … ] ) index_parameters

  主键约束声明表中的一个或者多个字段只能包含唯一的非NULL值。

  一个表只能声明一个主键。

• FOREIGN KEY ( column_name [, … ] ) REFERENCES reftable [ ( refcolumn [, … ] ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (table constraint)

  外键约束要求新表中一列或多列构成的组应该只包含、匹配被参考表中被参考字段值。若省略refcolum，则将使用reftable的主键。被参考列应该是被参考表中的唯一字段或主键。外键约束不能被定义在临时表和永久表之间。

  参考字段与被参考字段之间存在三种类型匹配，分别是：

  • MATCH FULL：不允许一个多字段外键的字段为NULL，除非全部外键字段都是NULL。
  • MATCH SIMPLE（缺省）：允许任意外键字段为NULL。
  • MATCH PARTIAL：目前暂不支持。

  另外，当被参考表中的数据发生改变时，某些操作也会在新表对应字段的数据上执行。ON DELETE子句声明当被参考表中的被参考行被删除时要执行的操作。ON UPDATE子句声明当被参考表中的被参考字段数据更新时要执行的操作。对于ON DELETE子句、ON UPDATE子句的可能动作：

  • NO ACTION（缺省）：删除或更新时，创建一个表明违反外键约束的错误。若约束可推迟，且若仍存在任何引用行，那这个错误将会在检查约束的时候产生。
  • RESTRICT：删除或更新时，创建一个表明违反外键约束的错误。与NO ACTION相同，只是动作不可推迟。
  • CASCADE：删除新表中任何引用了被删除行的行，或更新新表中引用行的字段值为被参考字段的新值。
  • SET NULL：设置引用字段为NULL。
  • SET DEFAULT：设置引用字段为它们的缺省值。

• USING INDEX TABLESPACE tablespace_name

  为UNIQUE或PRIMARY KEY约束相关的索引声明一个表空间。如果没有提供这个子句，这个索引将在default_tablespace中创建，如果default_tablespace为空，将使用数据库的缺省表空间。

示例语句

创建一个名为 customer_t1 的表：

CREATE TABLE customer_t1  
(  
    c_customer_sk             integer,  
    c_customer_id             char(5),  
    c_first_name              char(6),  
    c_last_name               char(8)  
);  

当结果显示为如下信息是表示创建成功：

CREATE TABLE  

其中 c_customer_sk 、c_customer_id、c_first_name 和 c_last_name 是表的字段名，integer、char(5)、char(6)和char(8)分别是这四字段名称的类型。

示例

创建一个名为 testdb 的数据库，数据库所有者为 gaussdb，在该数据库内创建数据表test_table，表的字段有 test_id (非空)和 tset_info，数据类型分别为 integer 和 char(36)。

su omm
gsql -d postgres -p 5432  
CREATE DATABASE testdb OWNER gaussdb; 
\q
gsql -d testdb -p 5432;
CREATE TABLE test_table  
(  
    test_id             integer     NOT NULL,
    test_info           char(36)  
);  

修改数据表

示例

向刚刚中新建的 test_table 表中添加一个字段 timestamp，类型为 integer，并添加非空约束；再取消 test_id 字段的非空约束。

su omm
gsql -d testdb -p 5432;
alter table test_table  modify test_id NULL;
ALTER TABLE test_table  ADD timestamp integer NOT NULL; 

删除数据表

相关知识

1.openGauss 的常用操作，2.SQL 删除数据表相关语法。

注意事项

• DROP TABLE会强制删除指定的表，删除表后，依赖该表的索引会被删除，而使用到该表的函数和存储过程将无法执行。删除分区表，会同时删除分区表中的所有分区。
• 只有表的所有者或者被授予了表的DROP权限的用户才能执行DROP TABLE，系统管理员默认拥有该权限。

语法格式

DROP TABLE [ IF EXISTS ]       { [schema.]table_name } [, ...] [ CASCADE | RESTRICT ];  

参数说明

• IF EXISTS

  如果指定的表不存在，则发出一个notice而不是抛出一个错误。

• schema

  模式名称。

• table_name

  表名称。

• CASCADE | RESTRICT

  • CASCADE：级联删除依赖于表的对象（比如视图）。
  • RESTRICT（缺省项）：如果存在依赖对象，则拒绝删除该表。这个是缺省。

示例语句

DROP TABLE IF EXISTS user_table;  

以上语句的执行效果为：如果当前数据库中存在 user_table 数据表，则将其删除。

执行成功会提示以下信息：

DROP TABLE  

示例

删除 testdb 中的 test_table表。

su omm
gsql -d testdb -p 5432;
DROP TABLE IF EXISTS test_table; 

向数据表中插入数据

相关知识

1.openGauss 的常用操作，2.SQL 插入数据项相关语法。

注意事项

• 只有拥有表 INSERT 权限的用户，才可以向表中插入数据。
• 如果使用 RETURNING 子句，用户必须要有该表的 SELECT 权限。
• 如果使用 ON DUPLICATE KEY UPDATE，用户必须要有该表的 SELECT、UPDATE 权限，唯一约束（主键或唯一索引）的 SELECT 权限。
• 如果使用 query 子句插入来自查询里的数据行，用户还需要拥有在查询里使用的表的 SELECT 权限。

语法格式

[ WITH [ RECURSIVE ] with_query [, ...] ]  
INSERT INTO table_name [ ( column_name [, ...] ) ]  
    { DEFAULT VALUES  
    | VALUES {( { expression | DEFAULT } [, ...] ) }[, ...]   
    | query }  
    [ ON DUPLICATE KEY UPDATE {{ column_name = { expression | DEFAULT } } [, ...] | NOTHING }]  
    [ RETURNING {* | {output_expression [ [ AS ] output_name ] }[, ...]} ];  

参数说明

• WITH [ RECURSIVE ] with_query [, …]

  用于声明一个或多个可以在主查询中通过名称引用的子查询，相当于临时表。

  如果声明了RECURSIVE，那么允许SELECT子查询通过名称引用它自己。

  其中with_query的详细格式为：with_query_name [ ( column_name [, …] ) ] AS

  ( {select | values | insert | update | delete} )

  – with_query_name指定子查询生成的结果集名称，在查询中可使用该名称访问

  子查询的结果集。

  – column_name指定子查询结果集中显示的列名。

  – 每个子查询可以是SELECT，VALUES，INSERT，UPDATE或DELETE语句。

• table_name

  要插入数据的目标表名。

  取值范围：已存在的表名。

• column_name

  目标表中的字段名：

  • 字段名可以有子字段名或者数组下标修饰。
  • 没有在字段列表中出现的每个字段，将由系统默认值，或者声明时的默认值填充，若都没有则用NULL填充。例如，向一个复合类型中的某些字段插入数据的话，其他字段将是NULL。
  • 目标字段（column_name）可以按顺序排列。如果没有列出任何字段，则默认全部字段，且顺序为表声明时的顺序。
  • 如果value子句和query中只提供了N个字段，则目标字段为前N个字段。
  • value子句和query提供的值在表中从左到右关联到对应列。

  取值范围：已存在的字段名。

• expression

  赋予对应column的一个有效表达式或值：

  • 如果是INSERT ON DUPLICATE KEY UPDATE语句下，expression可以为VALUES(column_name)或EXCLUDED.column_name用来表示引用冲突行对应的column_name字段的值。需注意，其中VALUES(column_name)不支持嵌套在表达式中（例如VALUES(column_name)+1），但EXCLUDED不受此限制。
  • 向表中字段插入单引号 " ’ "时需要使用单引号自身进行转义。
  • 如果插入行的表达式不是正确的数据类型，系统试图进行类型转换，若转换不成功，则插入数据失败，系统返回错误信息。

• DEFAULT

  对应字段名的缺省值。如果没有缺省值，则为NULL。

• query

  一个查询语句（SELECT语句），将查询结果作为插入的数据。

• RETURNING

  返回实际插入的行，RETURNING列表的语法与SELECT的输出列表一致。注意：INSERT ON DUPLICATE KEY UPDATE不支持RETURNING子句。

• output_expression

  INSERT命令在每一行都被插入之后用于计算输出结果的表达式。

  取值范围：该表达式可以使用table的任意字段。可以使用*返回被插入行的所有字段。

• output_name

  字段的输出名称。

  取值范围：字符串，符合标识符命名规范。

• ON DUPLICATE KEY UPDATE

  对于带有唯一约束（UNIQUE INDEX或PRIMARY KEY）的表，如果插入数据违反唯一约束，则对冲突行执行UPDATE子句完成更新，对于不带唯一约束的表，则仅执行插入。UPDATE时，若指定NOTHING则忽略此条插入，可通过"EXCLUDE." 或者 “VALUES()” 来选择源数据相应的列。

示例语句

首先创建一个数据表：

CREATE TABLE tb1  
(  
    c_sk             integer,  
    c_id             char(5),  
    c_name              char(6),  
    c_sex               char(8)  
);  

使用以下 INDSERT 语句即可向表 tb1 中插入一条数据：

INSERT INTO tb1(c_sk, c_id, c_name，c_sex) VALUES (3769, 'a', 'Grace'，'women');  

以上语句中，数据值是按照这些字段在表中出现的顺序列出的，并且用逗号分隔。

如果我们已经知道表中字段的顺序，也可无需列出表中的字段。例如以下命令与上面的命令效果一样:

INSERT INTO tb1 VALUES (3769, 'a', 'Grace'，'women');  

如果用户不知道所有字段的数值，可以忽略其中的一些。没有数值的字段将被填充为字段的缺省值。例如：

INSERT INTO tb1 (c_sk, c_name) VALUES (3769, 'Grace');  

用户也可以对独立的字段或者整个行明确缺省值：

INSERT INTO tb1(c_sk, c_id, c_name，c_sex) VALUES (3769, 'a', 'Grace'，DEFAULT);  

或者：

INSERT INTO tb1 DEFAULT VALUES;  

在表中插入多行，可以使用以下命令：

INSERT INTO tb1(c_sk, c_id, c_name,c_sex) VALUES (3769, 'a', 'Grace','women'),(3777, 'b', 'bob','women');  

执行成功会提示类似以下信息：

INSERT 0 2  

编程要求

在右侧命令行中操作数据库，完成如下任务：

• 在数据库 postgres 中创建表 student;

  表字段信息：

• 往表 student 插入数据;

  数据：

编程示例

• 在数据库 postgres 中创建表 student;

  表字段信息：

• 往表 student 插入数据;

  数据：

su omm
gsql -d postgres -p 5432  
CREATE TABLE student  
(  
    id             integer,
    name           char(20),
    age			  integer
);  
INSERT INTO student(id,name,age) VALUES (1, 'bob', 20);  
INSERT INTO student(id,name,age) VALUES (2, 'lily', 21);  
INSERT INTO student(id,name,age) VALUES (3, 'marry', 19);  
#或者
INSERT INTO student VALUES (1, 'lily', 21);  
INSERT INTO student VALUES (2, 'marry', 19);  

在数据表中查询数据

相关知识

1.openGauss 的常用操作，2.SQL 查询数据项相关语法。

背景知识

SELECT 语句用于从表或视图中取出数据。

SELECT 语句就像叠加在数据库表上的过滤器，利用 SQL 关键字从数据表中过滤出用户需要的数据。

查询语句可以非常复杂，为降低学习成本，本实训只介绍一些基础的写法。

注意事项

• 必须对每个在 SELECT 命令中使用的字段有 SELECT 权限。
• 使用 FOR UPDATE 或 FOR SHARE 还要求 UPDATE 权限。

语法格式

• 查询数据

[ WITH [ RECURSIVE ] with_query [, ...] ]  
SELECT [/*+ plan_hint */] [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]  
{ * | {expression [ [ AS ] output_name ]} [, ...] }  
[ FROM from_item [, ...] ]  
[ WHERE condition ]  
[ GROUP BY grouping_element [, ...] ]  
[ HAVING condition [, ...] ]  
[ WINDOW {window_name AS ( window_definition )} [, ...] ]  
[ { UNION | INTERSECT | EXCEPT | MINUS } [ ALL | DISTINCT ] select ]  
[ ORDER BY {expression [ [ ASC | DESC | USING operator ] | nlssort_expression_clause ] [ NULLS { FIRST | LAST } ]} [, ...] ]  
[ LIMIT { [offset,] count | ALL } ]  
[ OFFSET start [ ROW | ROWS ] ]  
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]  
[ {FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ]} [...] ];  

• 其中子查询with_query为：

  with_query_name [ ( column_name [, ...] ) ]      AS ( {select | values | insert | update | delete} )  
  

• 其中指定查询源from_item为：

  {[ ONLY ] table_name [ * ] [ partition_clause ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]  
  [ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]  
  [TIMECAPSULE {TIMESTAMP|CSN} expression]  
  |( select ) [ AS ] alias [ ( column_alias [, ...] ) ]  
  |with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]  
  |function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]  
  |function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )  
  |from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]}  
  
  

• 其中group子句为：

  ( )  
  | expression  
  | ( expression [, ...] )  
  | ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )  
  | CUBE ( { expression | ( expression [, ...] ) } [, ...] )  
  | GROUPING SETS ( grouping_element [, ...] )  
  

• 其中指定分区partition_clause为：

  PARTITION { ( partition_name ) |   
          FOR (  partition_value [, ...] ) }  
  

  说明：
  指定分区只适合普通表。

• 其中设置排序方式nlssort_expression_clause为：

  NLSSORT ( column_name, ' NLS_SORT = { SCHINESE_PINYIN_M | generic_m_ci } ' )  
  

• 简化版查询语法，功能相当于select * from table_name。

  TABLE { ONLY {(table_name)| table_name} | table_name [ * ]};  
  

参数说明

SELECT 语句的参数相对繁杂，此处仅介绍一些常用的基本参数。

• ALL

  声明返回所有符合条件的行，是默认行为，可以省略该关键字。

• DISTINCT [ ON ( expression [, …] ) ]

  从SELECT的结果集中删除所有重复的行，使结果集中的每行都是唯一的。

  ON ( expression [, …] ) 只保留那些在给出的表达式上运算出相同结果的行集合中的第一行。

  须知：
  DISTINCT ON表达式是使用与ORDER BY相同的规则进行解释的。除非使用了ORDER BY来保证需要的行首先出现，否则，“第一行” 是不可预测的。

• SELECT列表

  指定查询表中列名，可以是部分列或者是全部（使用通配符*表示）。

  通过使用子句AS output_name可以为输出字段取个别名，这个别名通常用于输出字段的显示。支持关键字name、value和type作为列别名。

  列名可以用下面几种形式表达：

  • 手动输入列名，多个列之间用英文逗号（,）分隔。
  • 可以是FROM子句里面计算出来的字段。

• FROM子句

  为SELECT声明一个或者多个源表。

  FROM子句涉及的元素如下所示。

  • table_name

    表名或视图名，名称前可加上模式名，如：schema_name.table_name。

  • alias

    给表或复杂的表引用起一个临时的表别名，以便被其余的查询引用。

    别名用于缩写或者在自连接中消除歧义。如果提供了别名，它就会完全隐藏表的实际名称。

  • column_alias

    列别名。

  • PARTITION

    查询分区表的某个分区的数据。

  • partition_name

    分区名。

  • partition_value

    指定的分区键值。在创建分区表时，如果指定了多个分区键，可以通过PARTITION FOR子句指定的这一组分区键的值，唯一确定一个分区。

  • subquery

    FROM子句中可以出现子查询，创建一个临时表保存子查询的输出。

  • with_query_name

    WITH子句同样可以作为FROM子句的源，可以通过WITH查询的名称对其进行引用。

  • function_name

    函数名称。函数调用也可以出现在FROM子句中。

  • join_type

    有5种类型。

• WHERE子句

  WHERE 子句构成一个行选择表达式，用来缩小 SELECT 查询的范围。condition 是返回值为布尔型的任意表达式，任何不满足该条件的行都不会被检索。

• GROUP BY子句

  将查询结果按某一列或多列的值分组，值相等的为一组。

• HAVING子句

  与GROUP BY子句配合用来选择特殊的组。HAVING子句将组的一些属性与一个常数值比较，只有满足HAVING子句中的逻辑表达式的组才会被提取出来。

• UNION子句

  UNION计算多个SELECT语句返回行集合的并集。

• INTERSECT子句

  INTERSECT计算多个SELECT语句返回行集合的交集，不含重复的记录。

• ORDER BY子句

  对SELECT语句检索得到的数据进行升序或降序排序。对于ORDER BY表达式中包含多列的情况：

  • 首先根据最左边的列进行排序，如果这一列的值相同，则根据下一个表达式进行比较，依此类推。
  • 如果对于所有声明的表达式都相同，则按随机顺序返回。
  • 在与DISTINCT关键字一起使用的情况下，ORDER BY中排序的列必须包括在SELECT语句所检索的结果集的列中。
  • 在与GROUP BY子句一起使用的情况下，ORDER BY中排序的列必须包括在SELECT语句所检索的结果集的列中。

示例语句

使用系统表pg_tables查询数据库所有表的信息:

SELECT * FROM pg_tables;  

执行如下命令查询表customer_t1的数据量:

SELECT count(*) FROM customer_t1;  

执行如下命令查询表customer_t1的所有数据：

SELECT * FROM customer_t1;  

执行如下命令只查询字段c_customer_sk的数据：

SELECT c_customer_sk FROM customer_t1;  

执行如下命令过滤字段c_customer_sk的重复数据:

SELECT DISTINCT( c_customer_sk ) FROM customer_t1;  

执行如下命令查询字段c_customer_sk为3869的所有数据:

SELECT * FROM customer_t1 WHERE c_customer_sk = 3869;  

执行如下命令按照字段c_customer_sk进行排序:

SELECT * FROM customer_t1 ORDER BY c_customer_sk;  

编程示例

写查询语句，要求查询 student 表中 age 字段大于或等于 20 的所有名字（name字段）。

SELECT name FROM student WHERE  age>= 20;  

在数据表中删除数据

相关知识

1.openGauss 的常用操作，2.SQL 删除数据项相关语法。

背景知识

在使用表的过程中，可能会需要删除已过期的数据，删除数据必须从表中整行的删除。

SQL 不能直接访问独立的行，只能通过声明被删除行匹配的条件进行。如果表中有一个主键，用户可以指定准确的行。用户可以删除匹配条件的一组行或者一次删除表中的所有行。

DELETE 语句从指定的表里删除满足 WHERE 子句的行。如果 WHERE 子句不存在，将删除表中所有行，结果只保留表结构。

注意事项

• 要删除表中的数据，用户必须对它有 DELETE 权限。同样也必须有 USING 子句引用的表以及 condition 上读取的表的 SELECT 权限。
• 对于列存表，暂时不支持 RETURNING 子句。

语法格式

[ WITH [ RECURSIVE ] with_query [, ...] ]  
DELETE [/*+ plan_hint */] FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]  
    [ USING using_list ]  
    [ WHERE condition | WHERE CURRENT OF cursor_name ]  
    [ RETURNING { * | { output_expr [ [ AS ] output_name ] } [, ...] } ];  

参数说明

• WITH [ RECURSIVE ] with_query [, …]

  用于声明一个或多个可以在主查询中通过名称引用的子查询，相当于临时表。

  如果声明了RECURSIVE，那么允许SELECT子查询通过名称引用它自己。

  其中with_query的详细格式为：

  with_query_name [ ( column_name [, ...] ) ] AS  ( {select | values | insert | update | delete} )  
  

  – with_query_name指定子查询生成的结果集名称，在查询中可使用该名称访问

  子查询的结果集。

  – column_name指定子查询结果集中显示的列名。

  – 每个子查询可以是SELECT、VALUES、INSERT、UPDATE或DELETE语句。

• plan_hint子句

  以/*+ */的形式在DELETE关键字后，用于对DELETE对应的语句块生成的计划进行hint调优。

• ONLY

  如果指定ONLY则只有该表被删除；如果没有声明，则该表和它的所有子表将都被删除。

• table_name

  目标表的名称（可以有模式修饰）。

  取值范围：已存在的表名。

• alias

  目标表的别名。

  取值范围：字符串，符合标识符命名规范。

• using_list

  using子句。

• condition

  一个返回Boolean值的表达式，用于判断哪些行需要被删除。

• WHERE CURRENT OF cursor_name

  当前不支持，仅保留语法接口。

• output_expr

  DELETE命令删除行之后计算输出结果的表达式。该表达式可以使用表的任意字段。可以使用*返回被删除行的所有字段。

• output_name

  一个字段的输出名称。

  取值范围：字符串，符合标识符命名规范。

示例语句

使用DELETE命令删除行，如果删除表customer_t1中所有c_customer_sk为3869的记录：

DELETE FROM customer_t1 WHERE c_customer_sk = 3869;  

如果执行如下命令之一，会删除表中所有的行：

DELETE FROM customer_t1;  
TRUNCATE TABLE customer_t1;  

全表删除的场景下，建议使用truncate，不建议使用delete。

删除成功会提示类似如下信息：

DELETE 1  

编程示例

删除所创建的表 student 中 name 字段值为 lily 的数据项。

su omm
gsql -d postgres -p 5432  
DELETE FROM student WHERE name = 'lily';  

在数据表中修改数据

相关知识

1.openGauss 的常用操作，2.SQL 修改数据项的相关语法。

背景知识

修改已经存储在数据库中数据的行为叫做更新。用户可以更新单独一行，所有行或者指定的部分行。还可以独立更新每个字段，而其他字段则不受影响。

使用 UPDATE 命令更新现有行，需要提供以下三种信息：

• 表的名称和要更新的字段名
• 字段的新值
• 要更新哪些行

SQL 通常不会为数据行提供唯一标识，因此无法直接声明需要更新哪一行。但是可以通过声明一个被更新的行必须满足的条件来更新数据行。只有在表里存在主键的时候，才可以通过主键指定一个独立的行。

注意事项

• 要修改表，用户必须对该表有UPDATE权限。
• 对 expression 或 condition 条件里涉及到的任何表要有 SELECT 权限。
• 对于列存表，暂时不支持 RETURNING 子句。
• 列存表不支持结果不确定的更新(non-deterministic update)。试图对列存表用多行数据更新一行时会报错。
• 列存表的更新操作，旧记录空间不会回收，需要执行 VACUUM FULL table_name 进行清理。
• 对于列存复制表，暂不支持 UPDATE 操作。

语法格式

UPDATE [/*+ plan_hint */] [ ONLY ] table_name [ * ] [ [ AS ] alias ]  
SET {column_name = { expression | DEFAULT }   
    |( column_name [, ...] ) = {( { expression | DEFAULT } [, ...] ) |sub_query }}[, ...]  
    [ FROM from_list] [ WHERE condition ]  
    [ RETURNING {*   
                | {output_expression [ [ AS ] output_name ]} [, ...] }];
where sub_query can be:  
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]  
{ * | {expression [ [ AS ] output_name ]} [, ...] }  
[ FROM from_item [, ...] ]  
[ WHERE condition ]  
[ GROUP BY grouping_element [, ...] ]  
[ HAVING condition [, ...] ]  

参数说明

• table_name

  要更新的表名，可以使用模式修饰。

  取值范围：已存在的表名称。

• alias

  目标表的别名。

  取值范围：字符串，符合标识符命名规范。

• column_name

  要修改的字段名。

  支持使用目标表的别名加字段名来引用这个字段。例如：

  `UPDATE foo AS f SET f.col\_name = 'namecol';`  
  

  取值范围：已存在的字段名。

• expression

  赋给字段的值或表达式。

• DEFAULT

  用对应字段的缺省值填充该字段。

  如果没有缺省值，则为NULL。

• sub_query

  子查询。

  使用同一数据库里其他表的信息来更新一个表可以使用子查询的方法。其中SELECT子句具体介绍请参考后续实训。

• from_list

  一个表的表达式列表，允许在WHERE条件里使用其他表的字段。与在一个SELECT语句的FROM子句里声明表列表类似。

  须知：
  目标表绝对不能出现在from_list里，除非在使用一个自连接（此时它必须以from_list的别名出现）。

• condition

  一个返回Boolean类型结果的表达式。只有这个表达式返回true的行才会被更新。

• output_expression

  在所有需要更新的行都被更新之后，UPDATE命令用于计算返回值的表达式。

  取值范围：使用任何table以及FROM中列出的表的字段。*表示返回所有字段。

• output_name

  字段的返回名称。

示例语句

更新表中的数据。UPDATE 修改满足条件的所有行中指定的字段值，WHERE 子句声明条件，SET 子句指定的字段会被修改，没有出现的字段则保持它们的原值。

比如将表 customer_t1 中 c_customer_sk 为 9527 的地域重新定义为 9876：

UPDATE customer_t1 SET c_customer_sk = 9876 WHERE c_customer_sk = 9527;  

这里的表名称也可以使用模式名修饰，否则会从默认的模式路径找到这个表。 SET 后面紧跟字段和新的字段值。新的字段值不仅可以是常量，也可以是变量表达式。

再比如，把所有 c_customer_sk 的值增加100：

UPDATE customer_t1 SET c_customer_sk = c_customer_sk + 100;  

在这里省略了 WHERE 子句，表示表中的所有行都要被更新。如果出现了 WHERE 子句，那么只有匹配其条件的行才会被更新。

在SET子句中的等号是一个赋值，而在 WHERE 子句中的等号是比较。WHERE 条件不一定是相等测试，许多其他的操作符也可以使用。

用户可以在一个 UPDATE 命令中更新更多的字段，方法是在 SET 子句中列出更多赋值，比如：

UPDATE customer_t1 SET  c_customer_id = 'Admin', c_first_name = 'Local' WHERE c_customer_sk = 4421;   

批量更新或删除数据后，会在数据文件中产生大量的删除标记，查询过程中标记删除的数据也是需要扫描的。故多次批量更新/删除后，标记删除的数据量过大会严重影响查询的性能。建议在批量更新/删除业务会反复执行的场景下，定期执行 VACUUM FULL 以保持查询性能。

动手体验

以下命令提供一个完整的示例，读者可以在右侧命令行动手实验：

--创建表student1。  
openGauss=# CREATE TABLE student1  
(  
   stuno     int,  
   classno   int   
);
--插入数据。  
openGauss=# INSERT INTO student1 VALUES(1,1);  
openGauss=# INSERT INTO student1 VALUES(2,2);  
openGauss=# INSERT INTO student1 VALUES(3,3);
--查看数据。  
openGauss=# SELECT * FROM student1;
--直接更新所有记录的值。  
openGauss=# UPDATE student1 SET classno = classno*2;
--查看数据。  
openGauss=# SELECT * FROM student1;
--删除表。  
openGauss=# DROP TABLE student1;  

编程示例

将 postgres 数据库 student 表中 id 为 3 数据项的 age 字段值改为 18。

su omm
gsql -d postgres -p 5432  
UPDATE student SET age=18 WHERE id = 3;

创建用户

相关知识

1.openGauss 的常用操作，2.SQL 创建用户相关语法。

背景知识

使用 CREATE USER 和 ALTER USER 可以创建和管理数据库用户（本实训介绍 CREATE USER）。openGauss 包含一个或多个已命名数据库。用户和角色在整个 openGauss 范围内是共享的，但是其数据并不共享。即用户可以连接任何数据库，但当连接成功后，任何用户都只能访问连接请求里声明的那个数据库。

非三权分立下，openGauss 用户帐户只能由系统管理员或拥有 CREATEROLE 属性的安全管理员创建和删除。三权分立时，用户帐户只能由初始用户和安全管理员创建。

在用户登录 openGauss 时会对其进行身份验证。用户可以拥有数据库和数据库对象（例如表），并且可以向用户和角色授予对这些对象的权限以控制谁可以访问哪个对象。除系统管理员外，具有 CREATEDB 属性的用户可以创建数据库并授予对这些数据库的权限。

注意事项

• 通过 CREATE USER 创建的用户，默认具有 LOGIN 权限。
• 通过 CREATE USER 创建用户的同时，系统会在执行该命令的数据库中，为该用户创建一个同名的 SCHEMA。
• 系统管理员在普通用户同名 schema 下创建的对象，所有者为 schema 的同名用户（非系统管理员）。

后续实训会介绍 schema 相关内容。

语法格式

CREATE USER user_name [ [ WITH ] option [ ... ] ] [ ENCRYPTED | UNENCRYPTED ] { PASSWORD | IDENTIFIED BY } { 'password' [EXPIRED] | DISABLE };  

其中option子句用于设置权限及属性等信息。

{SYSADMIN | NOSYSADMIN}  
    | {MONADMIN | NOMONADMIN}  
    | {OPRADMIN | NOOPRADMIN}  
    | {POLADMIN | NOPOLADMIN}  
    | {AUDITADMIN | NOAUDITADMIN}  
    | {CREATEDB | NOCREATEDB}  
    | {USEFT | NOUSEFT}  
    | {CREATEROLE | NOCREATEROLE}  
    | {INHERIT | NOINHERIT}  
    | {LOGIN | NOLOGIN}  
    | {REPLICATION | NOREPLICATION}  
    | {INDEPENDENT | NOINDEPENDENT}  
    | {VCADMIN | NOVCADMIN}  
    | CONNECTION LIMIT connlimit  
    | VALID BEGIN 'timestamp'  
    | VALID UNTIL 'timestamp'  
    | RESOURCE POOL 'respool'  
    | PERM SPACE 'spacelimit'  
    | TEMP SPACE 'tmpspacelimit'  
    | SPILL SPACE 'spillspacelimit'  
    | IN ROLE role_name [, ...]  
    | IN GROUP role_name [, ...]  
    | ROLE role_name [, ...]  
    | ADMIN role_name [, ...]  
    | USER role_name [, ...]  
    | SYSID uid  
    | DEFAULT TABLESPACE tablespace_name  
    | PROFILE DEFAULT  
    | PROFILE profile_name  
    | PGUSER  

参数说明

• user_name

  用户名称。

  取值范围：字符串，要符合标识符的命名规范。且最大长度不超过63个字符。

• password

  登录密码。

  密码规则如下：

  • 密码默认不少于8个字符。
  • 不能与用户名及用户名倒序相同。
  • 至少包含大写字母（A-Z）、小写字母（a-z）、数字（0-9）、非字母数字字符（限定为~!@#$%^&*()-_=+|[{}];:,<.>/?）四类字符中的三类字符。
  • 密码也可以是符合格式要求的密文字符串，这种情况主要用于用户数据导入场景，不推荐用户直接使用。如果直接使用密文密码，用户需要知道密文密码对应的明文，并且保证明文密码复杂度，数据库不会校验密文密码复杂度，直接使用密文密码的安全性由用户保证。
  • 创建用户时，应当使用双引号或单引号将用户密码括起来。

  取值范围：字符串。

CREATE USER 的其他参数值会在后续实训再介绍。

示例语句

--创建用户jim，登录密码为xxxxxxxxx。  
openGauss=# CREATE USER jim PASSWORD 'xxxxxxxxx';
--下面语句与上面的等价。  
openGauss=# CREATE USER kim IDENTIFIED BY 'xxxxxxxxx';
--如果创建有“创建数据库”权限的用户，则需要加CREATEDB关键字。  
openGauss=# CREATE USER dim CREATEDB PASSWORD 'xxxxxxxxx';  

执行成功会提示以下信息：

CREATE ROLE  

编程示例

创建用户 jackson ，密码设置为 jackson@123。

su omm
gsql -d postgres -p 5432 
CREATE USER jackson PASSWORD 'jackson@123';

修改用户密码

相关知识

1.openGauss 的常用操作，2.SQL 修改和删除用户相关语法。

注意事项

• ALTER USER 中修改的会话参数只针对指定的用户，且在下一次会话中有效。
• 须使用 CASCADE 级联删除依赖用户的对象（除数据库外）。当删除用户的级联对象时，如果级联对象处于锁定状态，则此级联对象无法被删除，直到对象被解锁或锁定级联对象的进程被杀死。
• 在数据库中删除用户时，如果依赖用户的对象在其他数据库中或者依赖用户的对象是其他数据库，请用户先手动删除其他数据库中的依赖对象或直接删除依赖数据库，再删除用户。即 drop user 不支持跨数据库进行级联删除。
• 如果该用户被 DATA SOURCE 对象依赖时，无法直接级联删除该用户，需要手动删除对应的 DATA SOURCE 对象之后再删除该用户。

语法格式

• 修改用户的权限等信息。

  ALTER USER user_name [ [ WITH ] option [ ... ] ];  
  

  其中option子句为。

  { CREATEDB | NOCREATEDB }  
      | { CREATEROLE | NOCREATEROLE }  
      | { INHERIT | NOINHERIT }  
      | { AUDITADMIN | NOAUDITADMIN }  
      | { SYSADMIN | NOSYSADMIN }  
      | { USEFT | NOUSEFT }  
      | { LOGIN | NOLOGIN }  
      | { REPLICATION | NOREPLICATION }  
      | {INDEPENDENT | NOINDEPENDENT}  
      | {VCADMIN | NOVCADMIN}  
      | CONNECTION LIMIT connlimit  
      | [ ENCRYPTED | UNENCRYPTED ] PASSWORD { 'password' [EXPIRED] | DISABLE | EXPIRED }  
      | [ ENCRYPTED | UNENCRYPTED ] IDENTIFIED BY { 'password' [ REPLACE 'old_password' | EXPIRED ] | DISABLE }  
      | VALID BEGIN 'timestamp'  
      | VALID UNTIL 'timestamp'  
      | RESOURCE POOL 'respool'  
      | PERM SPACE 'spacelimit'  
      | ACCOUNT { LOCK | UNLOCK }  
      | PGUSER  
  

• 修改用户名。

  ALTER USER user_name       RENAME TO new_name;  
  

• 删除用户。

DROP USER [ IF EXISTS ] user_name [, ...] [ CASCADE | RESTRICT ];  

参数说明

• IF EXISTS

  如果指定的用户不存在，发出一个notice而不是抛出一个错误。

• user_name

  现有用户名。

  取值范围：已存在的用户名。

• new_password

  新密码。

  密码规则如下：

  • 不能与当前密码相同。
  • 密码默认不少于8个字符。
  • 不能与用户名及用户名倒序相同。
  • 至少包含大写字母（A-Z）、小写字母（a-z）、数字（0-9）、非字母数字字符（限定为~!@#$%^&*()-_=+|[{}];:,<.>/?）四类字符中的三类字符。

  取值范围：字符串。

• old_password

  旧密码。

• ACCOUNT LOCK | ACCOUNT UNLOCK

  • ACCOUNT LOCK：锁定帐户，禁止登录数据库。
  • ACCOUNT UNLOCK：解锁帐户，允许登录数据库。

• PGUSER

  当前版本不允许修改用户的PGUSER属性。

示例语句

--创建用户jim，登录密码为xxxxxxxxx。  openGauss=# CREATE USER jim PASSWORD 'xxxxxxxxx';--下面语句与上面的等价。  openGauss=# CREATE USER kim IDENTIFIED BY 'xxxxxxxxx';--如果创建有“创建数据库”权限的用户，则需要加CREATEDB关键字。  openGauss=# CREATE USER dim CREATEDB PASSWORD 'xxxxxxxxx';--将用户jim的登录密码由xxxxxxxxx修改为Abcd@123。  openGauss=# ALTER USER jim IDENTIFIED BY 'Abcd@123' REPLACE 'xxxxxxxxx';--为用户jim追加CREATEROLE权限。  openGauss=# ALTER USER jim CREATEROLE;--将enable_seqscan的值设置为on， 设置成功后，在下一会话中生效。  openGauss=# ALTER USER jim SET enable_seqscan TO on;--重置jim的enable_seqscan参数。  openGauss=# ALTER USER jim RESET enable_seqscan;--锁定jim帐户。  openGauss=# ALTER USER jim ACCOUNT LOCK;--删除用户。  openGauss=# DROP USER kim CASCADE;  openGauss=# DROP USER jim CASCADE;  openGauss=# DROP USER dim CASCADE;  

编程示例

将上次创建的 jackson 用户的密码修改为 Abcd@123。

su omm
gsql -d postgres -p 5432 
ALTER USER jackson IDENTIFIED BY 'Abcd@123' REPLACE 'jackson@1