常见的SQL DDL子句

分配扩展子句

语法：

ALLOCATE EXTENT   [ ( { SIZE size_clause       | DATAFILE 'filename'       | INSTANCE integer       } ...     )   ]

例如：创建employees表后，改变表的分配扩展size为10M。

SQL> CREATE TABLE employees(EMPLOYEE_ID  NUMBER(38), JOB_ID NUMBER(38),  SALARY NUMBER(38),  LAST_NAME VARCHAR2(16));

Table created.

SQL> ALTER TABLE employees ALLOCATE EXTENT (SIZE 10M);

Table altered.

不支持。

-

约束

语法：

{ inline_constraint | out_of_line_constraint | inline_ref_constraint | out_of_line_ref_constraint }

例如：创建表staff，约束子句中指定ID列、NAME列不为空。

SQL> CREATE TABLE staff(ID INT NOT NULL, NAME char(8) NOT NULL, AGE INT, ADDRESS CHAR(50), SALARY REAL);

Table created.

支持。

-

取消分配未使用子句

语法：

DEALLOCATE UNUSED [ KEEP size_clause ]

例如：创建employees表，进行了一些插入、删除操作后，希望使用取消分配未使用子句释放employees表未使用的空间。

SQL> CREATE TABLE employees(EMPLOYEE_ID  NUMBER(38), JOB_ID NUMBER(38),  SALARY NUMBER(38),  LAST_NAME VARCHAR2(16));

Table created.

- 进行一些插入、删除操作

SQL> ALTER TABLE employees DEALLOCATE UNUSED;

Table altered.

不支持。

-

文件规范

语法：

{[ 'filename' | 'ASM_filename' ] [ SIZE size_clause ] [ REUSE ] [ autoextend_clause ]} 
|
{[ 'filename | ASM_filename' | ('filename | ASM_filename'    [, 'filename | ASM_filename' ]...) ] [ SIZE size_clause ] [ BLOCKSIZE size_clause [ REUSE ]}

例如：创建一个临时表空间tbs_temp_01，SQL语句的文件规范子句中指定在表空间中创建一个临时数据库文件temp01.dbf，可以自动扩展，并将表空间分配给表空间组tbs_grp_01。

SQL> CREATE TEMPORARY TABLESPACE tbs_temp_01 TEMPFILE 'temp01.dbf' AUTOEXTEND ON TABLESPACE GROUP tbs_grp_01;

Tablespace created.

不支持。

-

日志记录子句

语法：

{ LOGGING | NOLOGGING |  FILESYSTEM_LIKE_LOGGING }

部分支持，有差异。

• GaussDB不支持LOGGING约束子句和FILESYSTEM_LIKE_LOGGING约束子句。

  例如：

  GaussDB创建表，带LOGGING约束子句，语法报错。

  gaussdb=# CREATE LOGGING TABLE my_tab(id int, name char(16));
  ERROR:  syntax error at or near "LOGGING"
  LINE 1: CREATE LOGGING TABLE my_tab(id int, name char(16));
                 ^

  GaussDB创建表，带FILESYSTEM_LIKE_LOGGING约束子句，语法报错。

  gaussdb=# CREATE FILESYSTEM_LIKE_LOGGING TABLE my_tab(id int, name char(16));
  ERROR:  syntax error at or near "FILESYSTEM_LIKE_LOGGING"
  LINE 1: CREATE FILESYSTEM_LIKE_LOGGING TABLE my_tab(id int, name cha...
                 ^

• GaussDB仅支持表级的UNLOGGED约束，不支持列级的UNLOGGED约束。

  例如：GaussDB创建表，带列级的UNLOGGED约束子句，语法报错。

  gaussdb=# CREATE UNLOGGED TABLE my_tab(id int UNLOGGED, name char(16));
  ERROR:  syntax error at or near "UNLOGGED"
  LINE 1: CREATE UNLOGGED TABLE my_tab(id int UNLOGGED, name char(16))...
                                              ^

• GaussDB仅支持在CREATE TABLE、CREATE TABLE AS、SELECT INTO语句中使用日志记录子句。

  例如：GaussDB创建TABLESPACE，带UNLOGGED约束子句，语法报错。

  gaussdb=# CREATE UNLOGGED TABLESPACE tbs1 RELATIVE LOCATION 'tablespace1/tablespace_1';
  ERROR:  syntax error at or near "TABLESPACE"
  LINE 1: CREATE UNLOGGED TABLESPACE tbs1 RELATIVE LOCATION 'tablespac...
                          ^

并行子句

语法：

{ NOPARALLEL | PARALLEL [ integer ] }

例如：创建表t1，并在并行子句中指定PARALLEL 4，意为查询和更新表t1时最多使用4个并行进程操作。

SQL> CREATE TABLE t1 (id NUMBER, name VARCHAR2(50)) PARALLEL 4;

Table created.

不支持。

-

物理属性子句

语法：

[ { PCTFREE integer   | PCTUSED integer   | INITRANS integer   | storage_clause   }... ]

部分支持，有差异。

• GaussDB不支持PCTUSED。

  例如：执行在表tbl1中创建一个tbl1_ind的索引的SQL语句，并在该语句的物理属性子句中指定索引的空间利用率PCTUSED为20%，GaussDB执行该SQL语句语法报错。

  gaussdb=# CREATE INDEX tbl1_ind ON tbl1 (name) PCTUSED 20;
  ERROR:  syntax error at or near "PCTUSED"
  LINE 1: CREATE INDEX tbl1_ind ON tbl1 (name) PCTUSED 20;
                                               ^

• GaussDB 仅支持在CREATE TABLE、CREATE INDEX语句中使用物理属性子句。

  例如：尝试从表tbl1中获取数据，创建物化视图tbl1_mv，并在物理属性子句中指定该视图的初始化事务数为30，GaussDB执行该SQL语句语法报错。

  gaussdb=# CREATE MATERIALIZED VIEW tbl1_mv INITRANS 30 as select * from tbl1;
  ERROR:  syntax error at or near "INITRANS"
  LINE 1: CREATE MATERIALIZED VIEW tbl1_mv INITRANS 30 as select * fro...
                                           ^

大小子句

语法：

integer [ K | M | G | T | P | E ]

例如：创建一个临时表空间tbs_temp_01，并在表空间中创建一个临时数据库文件temp01.dbf，SQL语句的大小子句中指定初始大小是5M，可以自动扩展，并将表空间分配给表空间组tbs_grp_01。

SQL> CREATE TEMPORARY TABLESPACE tbs_temp_01 TEMPFILE 'temp01.dbf' SIZE 5M AUTOEXTEND ON TABLESPACE GROUP tbs_grp_01;

Tablespace created.

不支持。

-

存储子句

语法：

STORAGE ({ INITIAL size_clause  | NEXT size_clause  | MINEXTENTS integer  | MAXEXTENTS { integer | UNLIMITED }  | maxsize_clause  | PCTINCREASE integer  | FREELISTS integer  | FREELIST GROUPS integer  | OPTIMAL [ size_clause | NULL ]  | BUFFER_POOL { KEEP | RECYCLE | DEFAULT }  | FLASH_CACHE { KEEP | NONE | DEFAULT }   | ( CELL_FLASH_CACHE ( KEEP | NONE | DEFAULT ) )  | ENCRYPT  } ... )

部分支持，有差异。

• Oracle中由STORAGE子句指定存储参数，而GaussDB中由WITH子句指定存储参数。

  例如：

  Oracle中创建表my_tab1，在存储子句中指定表初始大小为10M，需要更多空间时每次增加5M的SQL语句如下：

  SQL> CREATE TABLE my_tab1 (id NUMBER(10) PRIMARY KEY, name VARCHAR2(50)) STORAGE (INITIAL 10M NEXT 5M);
  
  Table created.
  

  GaussDB中创建表my_tab2，在存储子句中指定存储引擎类型为USTORE的SQL语句如下：

  gaussdb=# CREATE TABLE my_tab2 (id NUMBER(10) PRIMARY KEY, name VARCHAR2(50)) with (storage_type=ustore);
  NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "my_tab2_pkey" for table "my_tab2"
  CREATE TABLE

• GaussDB中可选的存储参数和Oracle存在很大差异。GaussDB具体可参见CREATE TABLE的参数说明部分，“WITH ({storage_parameter = value} [, ...])”中描述了CREATE TABLE语句支持的存储参数。

聚集函数嵌套

例如：创建由sales表的sales_amount列嵌套聚集函数MIN()、SUM()生成的表revenue。

SQL> CREATE TABLE sales(ID INT, SALES_AMOUNT INT);

Table created.

SQL> INSERT INTO sales VALUES(1, 100);

1 row created.

SQL> INSERT INTO sales VALUES (3, 200);

1 row created.

SQL> CREATE TABLE revenue as SELECT SUM(MIN(sales_amount)) as total from sales group by sales_amount;

Table created.

支持。

-

删除系统schema

语法：

DROP USER schema_name CASCADE;

例如：在SYS用户下删除SYS schema。

SQL> DROP USER SYS;
DROP USER SYS
*
ERROR at line 1:
ORA-28050: specified user or role cannot be dropped

支持。

-

删除表的主键约束

语法：

DROP PRIMARY KEY [ CASCADE ] [( KEEP | DROP ) INDEX ] [ ONLINE ]

部分支持，有差异。

• GaussDB仅支持DROP PRIMARY KEY语法，不支持其他选项。
• 当进行该操作时，Oracle会同时将创建主键时自动为创建的主键列添加的NOT NULL约束进行删除；GaussDB不会删除，仍然保留NOT NULL约束。

  -- GaussDB:
  gaussdb=# CREATE TABLE t1 (id int PRIMARY KEY);
  NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
  CREATE TABLE
  gaussdb=# ALTER TABLE t1 DROP PRIMARY KEY;
  ALTER TABLE
  gaussdb=# INSERT INTO t1 VALUES(null);
  ERROR:  The null value in column "id" violates the not-null constraint.
  DETAIL:  Failing row contains (null).
  gaussdb=# DROP TABLE t1;
  
  -- Oracle:
  SQL> CREATE TABLE t1 (id int PRIMARY KEY);
  SQL> ALTER TABLE t1 DROP PRIMARY KEY;
  SQL> INSERT INTO t1 VALUES(null);
  -- 该操作可以插入成功，即主键列的NOT NULL约束也同时被删除。
  SQL> DROP TABLE t1;

创建序列

语法：

CREATE SEQUENCE [ IF NOT EXISTS ] [ schema. ] sequence   [ SHARING = { METADATA | DATA | NONE } ]   [ { INCREMENT BY | START WITH } integer   | { MAXVALUE integer | NOMAXVALUE }   | { MINVALUE integer | NOMINVALUE }   | { CYCLE | NOCYCLE }   | { CACHE integer | NOCACHE }   | { ORDER | NOORDER }   | { KEEP | NOKEEP }   | { SCALE {EXTEND | NOEXTEND} | NOSCALE }   | { SHARD {EXTEND | NOEXTEND} | NOSHARD }   | { SESSION | GLOBAL }   ]...

部分支持，有差异。

• GaussDB不支持SHARING、CACHE、NOCACHE、ORDER、NOORDER、KEEP、NOKEEP、SCALE、NOSCALE、SHARD、NOSHARD、SESSION以及GLOBAL选项。
• 对于步长的绝对值是否小于最大值和最小值的差值的场景，GaussDB不进行检查。

  -- GaussDB:
  gaussdb=# CREATE SEQUENCE seq_test MINVALUE 1 MAXVALUE 10 INCREMENT BY 10;
  CREATE SEQUENCE
  
  -- Oracle:
  SQL> CREATE SEQUENCE seq_test MINVALUE 1 MAXVALUE 10 INCREMENT BY 10;
  CREATE SEQUENCE seq_test MINVALUE 1 MAXVALUE 10 INCREMENT BY 10
  *
  ERROR at line 1:
  ORA-04005: INCREMENT must be less than MAXVALUE minus MINVALUE

修改序列

语法：

ALTER SEQUENCE [ schema. ] sequence   { { (INCREMENT BY) | ( START WITH ) } integer   | { MAXVALUE integer | NOMAXVALUE }   | { MINVALUE integer | NOMINVALUE }   |  RESTART    | { CYCLE | NOCYCLE }   | { CACHE integer | NOCACHE }   | { ORDER | NOORDER }   | { KEEP | NOKEEP }   | { SCALE {EXTEND | NOEXTEND} | NOSCALE }   | { SHARD {EXTEND | NOEXTEND} | NOSHARD }   | { SESSION | GLOBAL }   } ...

部分支持，有差异。

• GaussDB不支持INCREMENT BY、MINVALUE、NOMINVALUE、RESTART、CYCLE、NOCYLE、CACHE、NOCACHE、ORDER、NOORDER、KEEP、NOKEEP、SCALE、NOSCALE、SHARD、NOSHARD、SESSION以及GLOBAL选项。
• GaussDB修改最大值时，不检查步长的绝对值是否小于最大值和最小值的差值。

  -- GaussDB:
  gaussdb=# CREATE SEQUENCE seq_test MINVALUE 1 MAXVALUE 100 INCREMENT BY 10;
  CREATE SEQUENCE
  gaussdb=# ALTER SEQUENCE seq_test MAXVALUE 10;
  ALTER SEQUENCE
  
  -- Oracle:
  SQL> CREATE SEQUENCE seq_test MINVALUE 1 MAXVALUE 100 INCREMENT BY 10;
  
  Sequence created.
  
  SQL> ALTER SEQUENCE seq_test MAXVALUE 10;
  ALTER SEQUENCE seq_test MAXVALUE 10
  *
  ERROR at line 1:
  ORA-04005: INCREMENT must be less than MAXVALUE minus MINVALUE

• GaussDB修改最大值时，检查序列的起始值是否在最大值和最小值范围内。

  -- GaussDB:
  gaussdb=# CREATE SEQUENCE seq_test MINVALUE 1 MAXVALUE 10 START WITH 10 INCREMENT BY -1;
  CREATE SEQUENCE
  gaussdb=# SELECT seq_test.nextval FROM DUAL;
   nextval 
  ---------
        10
  (1 row)
  
  gaussdb=# SELECT seq_test.nextval FROM DUAL;
   nextval 
  ---------
         9
  (1 row)
  
  gaussdb=# SELECT seq_test.nextval FROM DUAL;
   nextval 
  ---------
         8
  (1 row)
  
  gaussdb=# ALTER SEQUENCE seq_test MAXVALUE 9;
  ERROR:  START value (10) cannot be greater than MAXVALUE (9).
  
  -- Oracle:
  SQL> CREATE SEQUENCE seq_test MINVALUE 1 MAXVALUE 10 START WITH 10 INCREMENT BY -1;
  
  Sequence created.
  
  SQL> SELECT seq_test.nextval FROM DUAL;
  
     NEXTVAL
  ----------
  	10
  
  SQL> SELECT seq_test.nextval FROM DUAL;
  
     NEXTVAL
  ----------
  	 9
  
  SQL> SELECT seq_test.nextval FROM DUAL;
  
     NEXTVAL
  ----------
  	 8
  
  SQL> ALTER SEQUENCE seq_test MAXVALUE 9;
  
  Sequence altered.

• GaussDB首次执行nextval前内部位置是起始值，修改最大值时，检查序列的起始值是否在最大值和最小值范围内。

  -- GaussDB:
  gaussdb=# CREATE SEQUENCE seq_test MINVALUE 1 MAXVALUE 10 START WITH 6 INCREMENT BY -5;
  CREATE SEQUENCE
  gaussdb=# ALTER SEQUENCE seq_test MAXVALUE 9;
  ALTER SEQUENCE
  
  -- Oracle:
  SQL> CREATE SEQUENCE seq_test MINVALUE 1 MAXVALUE 10 START WITH 6 INCREMENT BY -5;
  
  Sequence created.
  
  SQL> ALTER SEQUENCE seq_test MAXVALUE 9;
  ALTER SEQUENCE seq_test MAXVALUE 9
  *
  ERROR at line 1:
  ORA-04009: MAXVALUE cannot be made to be less than the current value