GBASE 8C——SQL参考6 sql语法（5）

1. COMMIT

功能描述

提交当前事务。

语法格式

COMMIT [ WORK | TRANSACTION ]

参数说明

1. WORK | TRANSACTION：可选的关键词，没有具体效果。

注意事项

执行COMMIT这个命令的时候，命令执行者必须是该事务的创建者或系统管理员，且创建和提交操作可以不在同一个会话中。

语法兼容

符合SQL标准。

相关语法

BEGIN, ROLLBACK, END

1. COMMIT PREPARED

功能描述

提交一个预先为两阶段提交准备好的事务。

语法格式

COMMIT PREPARED transaction_id

参数说明

1. transaction_id：待提交事务的标识符，它不能和任何当前预备事务已经使用了的标识符同名；

注意事项

1. 命令执行者必须是该事务的创建者或系统管理员，且创建和提交操作可以不在同一个会话中。
2. 事务功能由数据库自动维护，不应显式使用事务功能。

语法兼容

COMMIT PREPARED是一种 PostgreSQL扩展。

相关语法

PREPARE TRANSACTION, ROLLBACK PREPARED

1. COPY

功能描述

实现在表和文件之间拷贝数据。

语法格式

COPY table_name [ ( column_name [, ...] ) ]

    FROM { 'filename' | PROGRAM 'command' | STDIN }

    [ [ WITH ] ( option [, ...] ) ]

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }

    TO { 'filename' | PROGRAM 'command' | STDOUT }

    [ [ WITH ] ( option [, ...] ) ]

其中 option 可以是下列之一：

    FORMAT format_name

    OIDS [ boolean ]

    FREEZE [ boolean ]

    DELIMITER 'delimiter_character'

    NULL 'null_string'

    HEADER [ boolean ]

    QUOTE 'quote_character'

    ESCAPE 'escape_character'

    FORCE_QUOTE { ( column_name [, ...] ) | * }

    FORCE_NOT_NULL ( column_name [, ...] )

    FORCE_NULL ( column_name [, ...] )

    ENCODING 'encoding_name'

参数说明

1. table_name：表的名字，可以被模式修饰；
2. column_name：可选的待拷贝字段列表；
3. query：其结果将被拷贝，注意查询要用圆括号包围；
4. filename：输入或者输出文件的路径名，其中输入文件的名称可以是绝对或相对路径， 但输出文件的名称必须是绝对路径；
5. PROGRAM：将要执行的命令，由shell调用；
6. STDIN：声明输入是来自标准输入；
7. STDOUT：声明输出打印到标准输出；
8. boolean：指定选中的选项是应该被关闭还是打开，可以写TRUE、ON或1来启用选项，写 FALSE、OFF或0禁用，缺省默认为TRUE；
9. FORMAT：选择要读取或者写入的数据格式，可选的参数：text、csv或者binary，默认为text；
10. OIDS：为每行拷贝内部对象标识，OIDS只在CN和DN节点本地维护，如果将OIDS复制到其他节点可能会发生冲突；
11. FREEZE：请求复制已经完成了行冻结的数据，类似于在运行VACUUM FREEZE命令之后复制；
12. DELIMITER：指定分隔文件每行中各列的字符；文本格式中默认是一个制表符，而CSV格式中默认是一个逗号，必须是一个单一的单字节字符，使用binary格式时不允许这个选项；
13. NULL：用来指定数据文件中空值的表示；
14. HEADER：指定导出数据文件是否包含标题行，标题行一般用来描述表中每个字段的信息，只能用于CSV格式的文件中；
15. QUOTE：CSV格式文件下的引号字符，默认使用双引号；
16. ESCAPE：CSV格式下，用来指定逃逸字符，逃逸字符只能指定为单字节字符，默认值为双引号；
17. FORCE_QUOTE：CSV格式下，强制必须对每个指定列中的所有非NULL值使用引用；标*将引用所有列的非NULL值；
18. FORCE_NOT_NULL：在CSV COPY FROM模式下，指定的字段输入不能为空；
19. FORCE_NULL：在CSV COPY FROM模式下，将指定值与空值串匹配；
20. ENCODING：指定文件编码格式名称。

注意事项

COPY只能用于表，不能用于视图。

对任何要插入数据的表必须有插入权限。

如果声明了一个字段列表，COPY将只在文件和表之间拷贝已声明字段的数据。如果表中有任何不在字段列表里的字段，COPY FROM将为那些字段插入缺省值。

如果声明了数据源文件，服务器必须可以访问该文件；如果指定了STDIN，数据将在客户前端和服务器之间流动，输入时，表的列与列之间使用TAB键分隔，在新的一行中以反斜杠和句点（\.）表示输入结束。

如果数据文件的任意行包含比预期多或者少的字段，COPY FROM将抛出一个错误。

数据的结束可以用一个只包含反斜杠和句点（\.）的行表示。如果从文件中读取数据，数据结束的标记是不必要的；如果在客户端应用之间拷贝数据，必须要有结束标记。

COPY FROM中\N为空字符串，如果要输入实际数据值\N ，使用\\N。

COPY FROM不支持在导入过程中对数据做预处理（比如说表达式运算，填充指定默认值等）。如果需要在导入过程中对数据做预处理，用户需先把数据导入到临时表中，然后执行SQL语句通过运算插入到表中，但此方法会导致I/O膨胀，降低导入性能。

COPY FROM在遇到数据格式错误时会回滚事务，但没有足够的错误信息，不方便用户从大量的原始数据中定位错误数据。

COPY FROM/TO适合低并发，本地小数据量导入导出。

范例

COPY customer TO PROGRAM 'gzip > ~/data/sql/customer_data.gz';

COPY 10

1. CREATE ACCESS METHOD

功能描述

创建一种新的访问方法。

语法格式

CREATE ACCESS METHOD name

    TYPE access_method_type

    HANDLER handler_function

参数说明

1. name：要创建的访问方法的名称；
2. access_method_type：要定义的访问方法的类型，当前只支持INDEX；
3. handler_function：之前已被创建的函数的名称，该函数表示要创建的访问方法。

注意事项

访问方法名称在数据库中必须唯一。

只有系统管理员可以定义新的访问方法。

范例

CREATE ACCESS METHOD heptree TYPE INDEX HANDLER heptree_handler;

语法兼容

CREATE ACCESS METHOD是一种PostgreSQL扩展。

相关语法

DROP ACCESS METHOD, CREATE OPERATOR CLASS, CREATE OPERATOR FAMILY

1. CREATE AGGREGATE

功能描述

定义一个新的聚集函数。

语法格式

CREATE AGGREGATE name ( [ argmode ] [ argname ] arg_data_type [ , ... ] ) (

    SFUNC = sfunc,

    STYPE = state_data_type

    [ , CFUNC = cfunc ]

    [ , SSPACE = state_data_size ]

    [ , FINALFUNC = ffunc ]

    [ , FINALFUNC_EXTRA ]

    [ , COMBINEFUNC = combinefunc ]

    [ , SERIALFUNC = serialfunc ]

    [ , DESERIALFUNC = deserialfunc ]

    [ , INITCOND = initial_condition ]

    [ , INITCOLLECT = initial_collection_condition ]

    [ , MSFUNC = msfunc ]

    [ , MINVFUNC = minvfunc ]

    [ , MSTYPE = mstate_data_type ]

    [ , MSSPACE = mstate_data_size ]

    [ , MFINALFUNC = mffunc ]

    [ , MFINALFUNC_EXTRA ]

    [ , MINITCOND = minitial_condition ]

    [ , SORTOP = sort_operator ]

    [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]

)

CREATE AGGREGATE name ( [ [ argmode ] [ argname ] arg_data_type [ , ... ] ]

                        ORDER BY [ argmode ] [ argname ] arg_data_type [ , ... ] ) (

    SFUNC = sfunc,

    STYPE = state_data_type

    [ , CFUNC = cfunc ]

    [ , SSPACE = state_data_size ]

    [ , FINALFUNC = ffunc ]

    [ , FINALFUNC_EXTRA ]

    [ , INITCOND = initial_condition ]

    [ , INITCOLLECT = initial_collection_condition ]

    [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]

    [ , HYPOTHETICAL ]

)

或者旧的语法：

CREATE AGGREGATE name (

    BASETYPE = base_type,

    SFUNC = sfunc,

    STYPE = state_data_type

    [ , CFUNC = cfunc ]

    [ , SSPACE = state_data_size ]

    [ , FINALFUNC = ffunc ]

    [ , FINALFUNC_EXTRA ]

    [ , COMBINEFUNC = combinefunc ]

    [ , SERIALFUNC = serialfunc ]

    [ , DESERIALFUNC = deserialfunc ]

    [ , INITCOND = initial_condition ]

    [ , INITCOLLECT = initial_collection_condition ]

    [ , MSFUNC = msfunc ]

    [ , MINVFUNC = minvfunc ]

    [ , MSTYPE = mstate_data_type ]

    [ , MSSPACE = mstate_data_size ]

    [ , MFINALFUNC = mffunc ]

    [ , MFINALFUNC_EXTRA ]

    [ , MINITCOND = minitial_condition ]

    [ , SORTOP = sort_operator ]

)

参数说明

1. name：要创建的聚集函数的名称（可以被模式修饰）。
2. argmode：参数的模式：IN或VARIADIC，缺省情况默认IN；
3. argname：参数的名称，无意义，可省略；
4. arg_data_type：聚集函数输入的数据类型，零参数的聚集函数以*代替（count(*)）；
5. base_type：旧语法的聚集函数输入数据类型，只允许一个输入参数，零参数以“ANY”代替；
6. sfunc：要为每一个输入行调用的状态转移函数名；
7. cfunc：要为每个输入行调用的状态收集函数的名称；
8. state_data_type：聚集的状态值的数据类型；
9. state_data_size：聚集的状态值的平均大小，单位字节；
10. ffunc：最终函数的名称，该函数在所有输入行都被遍历之后被调用来计算聚集的结果；
11. combinefunc：指定聚集函数支持部分聚集；
12. serialfunc：serialfunc将聚集状态转化为一个bytea值并传送给处理进程；
13. deserialfunc：serialfunc反操作，两个输入类型分别为bytea和internal，输出为internal；
14. initial_condition：状态值的初始设置，如未指定从空值开始；
15. initial_collection_condition：状态集合值的初始设置，如未指定从空值开始；
16. msfunc：前向状态转移函数的名称，在移动聚集模式中会为每个输入行调用这个函数；
17. minvfunc：在移动聚集模式中用到的逆向状态转移函数的名称，与msfunc具有相同的参数和结果类型，用于从当前聚集状态中移除一个值；
18. mstate_data_type：使用移动聚集模式时，用于聚集状态值的数据类型；
19. mstate_data_size：使用移动聚集模式时，聚集状态值的平均值，单位字节，作用与state_data_size相同；
20. mffunc：使用移动聚集模式时用到的最终函数名称；
21. minitial_condition：使用移动聚集模式时，状态值的初始设置，作用与initial_condition相同；
22. sort_operator：MIN-或MAX-类相关聚集函数的排序操作符；
23. PARALLEL：表示该函数是否可以下推到DN上执行。PARALLEL UNSAFE表示该函数不能下推到DN上执行且强制使用顺序执行计划，为默认选项；PARALLEL RESTRICTED表示该函数能下推到DN上执行，但是要求必须在DN Leader上执行；PARALLEL SAFE表示该函数能下推到DN上执行，安全且不受限制；
24. HYPOTHETICAL：只用于有序集聚集，后面的直接参数须匹配聚集（WITHIN GROUP）参数的数据类型，HYPOTHETICAL标志在运行时没有任何效果，它只在命令解析期间对确定数据类型和聚集参数的排序规则有用。

注意事项

CREATE AGGREGATE的参数可以用任意顺序书写，而无需遵照以上说明的顺序。

GBase 8c数据库中，聚合以两种不同的模式工作：

1. 两阶段聚合：当整个聚合发生在Coordinator节点上时使用。在称为过渡阶段的第一阶段，数据库创建一个数据类型为stype的临时变量来保存聚合的当前内部状态。在每个输入行，计算聚合参数值，并使用当前状态值和新参数值调用状态转换函数以计算新的内部状态值。在处理完所有行后，在第二阶段或完成阶段，将调用一次final函数来计算聚合的返回值。如果没有最终函数，则按原样返回结束状态值。
2. 三阶段聚合：当聚合过程在协调器和数据节点之间划分时使用。在这种模式下，查询中涉及的每个Datanode节点都会执行名为过渡阶段的第一个阶段。这个阶段类似于上面讨论的两阶段聚合模式中的第一阶段，不同之处在于，每个Datanode将这个阶段应用于Datanode上可用的行。然后将过渡阶段的结果传输到协调器节点。称为收集阶段的第二阶段发生在协调器上。数据库Coordinator节点创建一个数据类型为stype的临时变量来保存当前收集阶段的内部状态。对于来自Datanode的每个输入（该节点上的转换阶段的结果），使用当前收集状态值和新转换值（从Datanode获得）调用收集函数以计算新的内部收集状态值。在处理完来自数据节点的所有转换值之后，在第三阶段或最终阶段，最终函数将被调用一次以计算聚合的返回值。如果没有final函数，则结束集合状态值按原样返回。

范例

CREATE AGGREGATE sum (integer)

 (

     sfunc = add,

     stype = integer

);

CREATE AGGREGATE

语法兼容

CREATE AGGREGATE是PostgreSQL的语言扩展。SQL 标准没有提供 用户定义的聚集函数。

相关语法

ALTER AGGREGATE, DROP AGGREGATE

1. CREATE BARRIER

功能描述

创建一个新集群节点间的同步点。该同步点可用于数据恢复。

语法格式

CREATE BARRIER barrier_name

参数说明

1. barrier_name：可选参数，同步点名称。缺省情况下默认为dummy_barrier_id。

注意事项

CREATE BARRIER在集群的每个节点上创建新的XLOG记录。可以将集群恢复到一个一致的点。需要注意的是，该操作需要手工创建，不能自动创建。

CREATE BARRIER在recovery.conf中修改名为recovery_target_barrier的恢复参数。在数据库恢复操作时，将recovery_target_barrier设置为已经创建的BARRIER值，然后将recovery.conf分发到每个节点的每个数据文件夹，一个个重启节点后生效。

范例

CREATE BARRIER;

BARRIER cn1_678265737977043

1. CREATE CAST

功能描述

定义一种新的转换。说明如何在两个类型之间进行转换。

语法格式

CREATE CAST (source_type AS target_type)

    WITH FUNCTION function_name [ (argument_type [, ...]) ]

    [ AS ASSIGNMENT | AS IMPLICIT ]

CREATE CAST (source_type AS target_type)

    WITHOUT FUNCTION

    [ AS ASSIGNMENT | AS IMPLICIT ]

CREATE CAST (source_type AS target_type)

    WITH INOUT

    [ AS ASSIGNMENT | AS IMPLICIT ]

参数说明

1. source_type：该转换的源数据类型的名称；
2. target_type：该转换的目标数据类型的名称；
3. function_name[(argument_type [, ...])]：被执行改造的函数，该函数的数据类型必须与转换的目标数据类型相同；
4. WITHOUT FUNCTION：表示源数据类型可以强制转换至目标类型，因此执行该转换不需要函数；
5. WITH INOUT：表示该转换是一种I/O类型转换，执行需要调用源数据类型的输出函数，并且把结果字符串传递给目标数据类型的输入；
6. AS ASSIGNMENT：表示该转换可以在赋值的情况下被隐式调用；
7. AS IMPLICIT：表示该转换可以在任何上下文中被隐式调用。

注意事项

使用DROP CAST移除用户定义的转换。

如果要定义双向转换类型，需要在两个方向上都进行显示声明。

GBase 8c数据库会自动提供用户定义类型和标准字符串类型转换，无需用户创建。

范例

CREATE CAST (bigint AS int4) WITH FUNCTION int4(bigint) AS ASSIGNMENT;

在系统中这种转换已经被预定义。

语法兼容

CREATE CAST命令符合 SQL标准。

相关语法

CREATE FUNCTION, CREATE TYPE, DROP CAST

1. CREATE COLLATION

功能描述

定义新排序规则。

语法格式

CREATE COLLATION [ IF NOT EXISTS ] name (

    [ LOCALE = locale, ]

    [ LC_COLLATE = lc_collate, ]

    [ LC_CTYPE = lc_ctype, ]

    [ PROVIDER = provider, ]

    [ VERSION = version ]

)

CREATE COLLATION [ IF NOT EXISTS ] name FROM existing_collation

参数说明

1. IF NOT EXISTS：如果已存在相同名称的排序规则，无需抛出错误；
2. name：排序规则的名字，可以被模式修饰，排序规则在模式中必须唯一；
3. locale：设置LC_COLLATE和LC_CTYPE；
4. lc_collate：指定新数据库使用的字符集；
5. lc_ctype：指定新数据库使用的字符分类；
6. provider：与这个排序规则相关的规则提供程序，取值范围：icu、libc，默认值libc；
7. version：指定使用该排序规则存储的字符串版本；
8. existing_collation：要复制的一种现有的排序规则的名称。新的排序规则将和现有的具有同样的属性，但是它是一个独立的对象。

注意事项

使用DROP COLLATION可移除用户定义的排序规则。

范例

从操作系统区域fr_FR.utf8创建一种排序规则（假定 当前数据库编码是UTF8）：

CREATE COLLATION french1 (locale = 'fr_FR.utf8');

CREATE COLLATION

从一个现有的排序规则创建一个新的排序规则：

CREATE COLLATION german1 FROM "de_DE";

CREATE COLLATION

语法兼容

在 SQL 标准中有一个CREATE COLLATION 语句，但是它被限制为只能复制一个现有的排序规则。创建新排序规则的 语法是一种PostgreSQL扩展。

相关语法

ALTER COLLATION, DROP COLLATION

1. CREATE CONVERSION

功能描述

定义一种新的编码转换。

语法格式

CREATE [ DEFAULT ] CONVERSION name

    FOR source_encoding TO dest_encoding FROM function_name

参数说明

1. DEFAULT：DEFAULT子句表示这个转换是从源编码到目标编码的默认转换，每个模式中的每个编码对只有一个默认转换；
2. name：转换的名称，可以被模式修饰，在一个模式中，转换名称唯一；
3. source_encoding：源编码名称；
4. dest_encoding：目标编码名称；
5. function_name：被用来执行该转换的函数，可以被模式修饰，若缺省会在路径中查找该函数；

该函数必须具有如下特征：

conv_proc(

    integer,  -- 源编码 ID

    integer,  -- 目标编码 ID

    cstring,  -- 源字符串（空值终止的 C 字符串）

    internal, -- 目标（用一个空值终止的 C 字符串填充）

    integer   -- 源字符串长度

) RETURNS void;

注意事项

使用DROP CONVERSION可以移除用户定义的转换。

范例

使用myfunc创建一个从编码UTF8到 LATIN1的转换：

CREATE CONVERSION myconv FOR 'UTF8' TO 'LATIN1' FROM myfunc;

语法兼容

CREATE CONVERSION是PostgreSQL扩展。

相关语法

ALTER CONVERSION, CREATE FUNCTION, DROP CONVERSION

1. CREATE DATABASE

功能描述

创建一个新的数据库。缺省情况下新数据库将通过复制标准系统数据库template1来创建。可以通过TEMPLATE template指定不同的模板。

语法格式

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 ] ]

参数说明

1. name：数据库名称，取值范围：字符集；
2. user_name：数据库所有者，缺省默认当前用户为所有者，取值范围：已存在的用户名；
3. template：模板名，即从哪个模板创建新数据库，取值范围：已有数据库名称，缺省默认template1；
4. encoding：指定数据库使用的字符编码，可以是字符串（如'SQL_ASCII'）、整数编号，若缺省默认使用模版数据库的编码；
5. lc_collate：指定新数据库使用的字符集。例如，通过lc_collate = 'zh_CN.gbk'设定该参数。该参数的使用会影响到对字符串的排序顺序（如使用ORDER BY执行，以及在文本列上使用索引的顺序）。默认是使用模板数据库的排序顺序；
6. lc_ctype：指定新数据库使用的字符分类。例如，通过lc_ctype = 'zh_CN.gbk'设定该参数。该参数的使用会影响到字符的分类，如大写、小写和数字。默认是使用模板数据库的字符分类。
7. tablespace_name：指定数据库对应的表空间，取值范围：已存在表空间名；
8. allowconn：为false则数据库不可连接，为true则允许连接；
9. connlimit：数据库可以接受的并发连接数，默认-l表示无限制；
10. istemplate：为true则所有具有CREATEDB权限的用户都可以克隆这个数据库，为false则只有系统管理员可以克隆这个数据库。

注意事项

可选的参数可以被写成任何顺序，不用按照上面说明的顺序。

只有拥有CREATEDB权限的用户才可以创建新数据库，系统管理员默认拥有此权限。

不能在事务块中执行创建数据库语句。

在创建数据库过程中，若出现类似“could not initialize database directory”的错误提示，可能是由于文件系统上数据目录的权限不足或磁盘满等原因引起。

范例

CREATE DATABASE gbasedbt;

CREATE DATABASE

相关语法

ALTER DATABASE, DROP DATABASE