Help Center/ GaussDB/ Developer Guide(Centralized_8.x)/ SQL Reference/ SQL Syntax/ DDL Syntax Overview

Updated on 2024-06-03 GMT+08:00

View PDF

DDL Syntax Overview

Data definition language (DDL) is used to define or modify an object in a database, such as a table, an index, or a view.

GaussDB does not support DDL operations when the CN is incomplete. For example, if the primary node of the database is faulty, creating a database or a table will fail.

Defining a Role

A role is used to manage permissions. For database security, management and operation permissions can be granted to different roles. For details about related SQL statements, see Table 1.

**Table 1** SQL statements for defining a role
Function	SQL Statement
Creating a role	CREATE ROLE
Altering role attributes	ALTER ROLE
Dropping a role	DROP ROLE
Deleting the database objects owned by a database role	DROP OWNED

Defining a User

A user is used to log in to a database. Different permissions can be granted to users for managing data accesses and operations of the users. For details about related SQL statements, see Table 2.

**Table 2** SQL statements for defining a user
Function	SQL Statement
Creating a User	CREATE USER
Altering user attributes	ALTER USER
Dropping a user	DROP USER

Defining a CMK

Client master keys (CMKs) are used to encrypt column encryption keys (CEKs) for the encrypted database feature. CMK definition includes creating and deleting a CMK. Table 3 lists the involved SQL statements.

**Table 3** SQL statements for defining a CMK
Function	SQL Statement
Creating a CMK	CREATE CLIENT MASTER KEY
Deleting a CMK	DROP CLIENT MASTER KEY

Defining a CEK

CEKs are used to encrypt data for the encrypted database feature. You can create a CEK, change the client master key specified by a CEK, and delete a CEK. Table 3 lists the involved SQL statements.

**Table 4** SQL statements for defining a CEK
Function	SQL Statement
Creating a CEK	CREATE COLUMN ENCRYPTION KEY
Changing the CMK specified by a CEK	ALTER COLUMN ENCRYPTION KEY
Deleting a CEK	DROP COLUMN ENCRYPTION KEY

Defining a Database

A database is the warehouse for organizing, storing, and managing data. Defining a database includes: creating a database, altering the database attributes, and dropping the database. Table 5 lists the involved SQL statements.

**Table 5** SQL statements for defining a database
Function	SQL Statement
Creating a database	CREATE DATABASE
Altering database attributes	ALTER DATABASE
Dropping a Database	DROP DATABASE

Defining a Schema

A schema is the set of a group of database objects and is used to control the access to the database objects. Table 6 lists the involved SQL statements.

**Table 6** SQL statements for defining a schema
Function	SQL Statement
Creating a schema	CREATE SCHEMA
Altering schema attributes	ALTER SCHEMA
Dropping a schema	DROP SCHEMA

Defining a Tablespace

A tablespace is used to manage data objects and corresponds to a catalog on a disk. Table 7 lists the involved SQL statements.

**Table 7** SQL statements for defining a tablespace
Function	SQL Statement
Creating a tablespace	CREATE TABLESPACE
Altering tablespace attributes	ALTER TABLESPACE
Dropping a tablespace	DROP TABLESPACE

Defining a Table

A table is a special data structure in a database and is used to store data objects and relationship between data objects. Table 8 lists the involved SQL statements.

**Table 8** SQL statements for defining a table
Function	SQL Statement
Creating a table	CREATE TABLE
Altering table attributes	ALTER TABLE
Renaming one or more tables	RENAME TABLE
Dropping a table	DROP TABLE
Creating a table from the results of a query	CREATE TABLE AS

Defining a Partitioned Table

A partitioned table is a logical table used to improve query performance and does not store data (data is stored in ordinary tables). Table 9 lists the involved SQL statements.

**Table 9** SQL statements for defining a partitioned table
Function	SQL Statement
Creating a partitioned table	CREATE TABLE PARTITION
Create a partition	ALTER TABLE PARTITION
Altering partitioned table attributes	ALTER TABLE PARTITION
Deleting a partition	ALTER TABLE PARTITION
Dropping a partitioned table	DROP TABLE
Creating a level-2 partitioned table	CREATE TABLE SUBPARTITION
Modifying partitions in a level-2 partitioned table	ALTER TABLE SUBPARTITION

Defining an Index

An index indicates the sequence of values in one or more columns in a database table. It is a data structure that improves the speed of data access to specific information in a database table. Table 10 lists the involved SQL statements.

**Table 10** SQL statements for defining an index
Function	SQL Statement
Creating an index	CREATE INDEX
Altering index attributes	ALTER INDEX
Dropping an index	DROP INDEX
Rebuilding an index	REINDEX
Creating a global secondary index in a specified table	CREATE GLOBAL INDEX

Defining a Stored Procedure

A stored procedure is a set of SQL statements for achieving specific functions and is stored in the database after compiling. Users can specify a name and provide parameters (if necessary) to execute the stored procedure. Table 11 lists the involved SQL statements.

**Table 11** SQL statements for defining a stored procedure
Function	SQL Statement
Creating a stored procedure	CREATE PROCEDURE
Dropping a stored procedure	DROP PROCEDURE
Recompiling a stored procedure	ALTER PROCEDURE

Defining a Function

In GaussDB, a function is similar to a stored procedure, which is a set of SQL statements. The function and stored procedure are used the same. Table 12 lists the involved SQL statements.

**Table 12** SQL statements for defining a function
Function	SQL Statement
Creating a function	CREATE FUNCTION
Altering a function attribute	ALTER FUNCTION
Dropping a function	DROP FUNCTION

Defining a Package

A package consists of the package specification and package body. It is used to manage stored procedures and functions by class, which is similar to classes in languages such as Java and C++.

**Table 13** SQL statements for defining a package
Function	SQL Statement
Creating a package	CREATE PACKAGE
Deleting a package	DROP PACKAGE
Altering a package attribute	ALTER PACKAGE

Defining a Cursor

To process SQL statements, the stored procedure thread assigns a memory segment to store context association. Cursors are handles or pointers to context regions. With a cursor, the stored procedure can control alterations in context areas. Table 14 lists the involved SQL statements.

**Table 14** SQL statements for defining a cursor
Function	SQL Statement
Creating a cursor	CURSOR
Creating a cursor	DECLARE
Moving a cursor	MOVE
Closing a cursor	CLOSE

Defining a Resource Pool

A resource pool is a system catalog used by the resource load management module to specify attributes related to resource management, such as Cgroups. Table 15 lists the involved SQL statements.

**Table 15** SQL statements for defining a resource pool
Function	SQL Statement
Creating a resource pool	CREATE RESOURCE POOL
Altering resource attributes	ALTER RESOURCE POOL
Dropping a resource pool	DROP RESOURCE POOL

Defining an Aggregate Function

**Table 16** SQL statements for defining an aggregate function
Function	SQL Statement
Creating an aggregate function	CREATE AGGREGATE
Modifying an aggregate function	ALTER AGGREGATE
Deleting an aggregate function	DROP AGGREGATE

Defining Data Type Conversion

**Table 17** SQL statements for defining a data type
Function	SQL Statement
Creating user-defined data type conversion	CREATE CAST
Deleting user-defined data type conversion	DROP CAST

Defining a Plug-in Extension

This feature is for internal use only. You are advised not to use it.

**Table 18** SQL statements for defining a plug-in extension
Function	SQL Statement
Creating an extension	CREATE EXTENSION
Modifying an extension	ALTER EXTENSION
Deleting an extension	DROP EXTENSION

Defining an Operator

**Table 19** SQL statements for defining an operator
Function	SQL Statement
Creating an operator	CREATE OPERATOR
Modifying an operator	ALTER OPERATOR
Deleting an operator	DROP OPERATOR
Defining a new operator class	CREATE OPERATOR CLASS

Defining a Data Type

**Table 20** SQL statements for defining a data type
Function	SQL Statement
Creating a data type	CREATE TYPE
Modifying a data type	ALTER TYPE
Deleting a data type	DROP TYPE

Defining a Scheduled Task

**Table 21** SQL statements for defining a scheduled task
Function	SQL Statement
Creating a scheduled task	CREATE EVENT
Modifying a scheduled task	ALTER EVENT
Deleting a scheduled task	DROP EVENT

Defining a Database Link

DATABASE LINK can be used to remotely operate a database object. Table 22 lists the involved SQL statements.

**Table 22** Database link-related SQL statements
Function	SQL Statement
Creating a database link	CREATE DATABASE LINK
Modifying a database link	ALTER DATABASE LINK
Deleting a database link	DROP DATABASE LINK

Defining an Audit Policy

**Table 23** SQL statements for defining an audit policy
Function	SQL Statement
Creating a unified audit policy	CREATE AUDIT POLICY
Modifying a unified audit policy	ALTER AUDIT POLICY
Deleting an audit policy	DROP AUDIT POLICY

Defining a Directory

**Table 24** SQL statements for defining a directory
Function	SQL Statement
Creating a directory	CREATE DIRECTORY
Modifying attributes of a directory	ALTER DIRECTORY
Deleting a specified directory	DROP DIRECTORY

Defining a Foreign Data Wrapper

**Table 25** SQL statements related to the foreign data wrapper
Function	SQL Statement
Creating a foreign data wrapper	CREATE FOREIGN DATA WRAPPER
Modifying a foreign data wrapper	ALTER FOREIGN DATA WRAPPER
Deleting a foreign data wrapper	DROP FOREIGN DATA WRAPPER

SQL Statements Related to the gs_global_config System Catalog

**Table 26** SQL statements related to the gs_global_config system catalog
Function	SQL Statement
Adding and modifying parameter values in the gs_global_config system catalog	ALTER GLOBAL CONFIGURATION
Deleting parameter values from the gs_global_config system catalog	DROP GLOBAL CONFIGURATION
Inserting one or more weak passwords into the gs_global_config system catalog	CREATE WEAK PASSWORD DICTIONARY
Clearing all weak passwords in the gs_global_config system catalog	DROP WEAK PASSWORD DICTIONARY

Defining a User Group

**Table 27** SQL statements for defining a user group
Function	SQL Statement
Creating a user group	CREATE GROUP
Modifying attributes of a user group	ALTER GROUP
Deleting a user group	DROP GROUP

Defining a Procedural Language

**Table 28** SQL statements for defining a procedural language
Function	SQL Statement
Defining a new procedural language	CREATE LANGUAGE
Modifying the definition of a procedural language	ALTER LANGUAGE
Deleting a procedural language	DROP LANGUAGE

Defining a Masking Policy

**Table 29** SQL statements for defining a masking policy
Function	SQL Statement
Creating a masking policy	CREATE MASKING POLICY
Modifying a masking policy	ALTER MASKING POLICY
Deleting a masking policy	DROP MASKING POLICY

Defining a Materialized View

**Table 30** SQL statements for defining a materialized view
Function	SQL Statement
Creating a complete-refresh materialized view	CREATE MATERIALIZED VIEW
Creating a fast-refresh materialized view	CREATE INCREMENTAL MATERIALIZED VIEW
Modifying multiple auxiliary attributes of an existing materialized view	ALTER MATERIALIZED VIEW
Forcibly deleting an existing materialized view from the database	DROP MATERIALIZED VIEW
Refreshing a materialized view in complete refresh mode	REFRESH MATERIALIZED VIEW
Refreshing a materialized view in fast refresh mode	REFRESH INCREMENTAL MATERIALIZED VIEW

Defining a Resource Label

**Table 31** SQL statements for defining a resource label
Function	SQL Statement
Creating a resource label	CREATE RESOURCE LABEL
Modifying a resource label	ALTER RESOURCE LABEL
Deleting a resource label	DROP RESOURCE LABEL

Defining a Row-Level Security Policy

**Table 32** SQL statements for defining a row-level security policy
Function	SQL Statement
Creating a row-level security policy for a table	CREATE ROW LEVEL SECURITY POLICY
Modifying an existing row-level security policy	ALTER ROW LEVEL SECURITY POLICY
Deleting a row-level security policy from a table	DROP ROW LEVEL SECURITY POLICY

Defining a Sequence

**Table 33** SQL statements for defining a sequence
Function	SQL Statement
Adding a sequence to the current database	CREATE SEQUENCE
Modifying parameters of an existing sequence	ALTER SEQUENCE
Deleting a sequence from the current database	DROP SEQUENCE

Defining a Foreign Server

**Table 34** SQL statements for defining a foreign server
Function	SQL Statement
Defining a new foreign server	CREATE SERVER
Adding, modifying, and deleting parameters of an existing server	ALTER SERVER
Deleting a data server	DROP SERVER

Defining a Synonym Object

**Table 35** SQL statements for defining a synonym object
Function	SQL Statement
Creating a synonym object	CREATE SYNONYM
Modifying the owner of a synonym object	ALTER SYNONYM
Deleting a specified synonym object	DROP SYNONYM

Defining a Trigger

**Table 36** SQL statements for defining a trigger
Function	SQL Statement
Creating a trigger	CREATE TRIGGER
Renaming a trigger	ALTER TRIGGER
Deleting a trigger	DROP TRIGGER

Defining a Mapping

**Table 37** SQL statements for defining a mapping
Function	SQL Statement
Defining a new mapping from a user to a foreign server	CREATE USER MAPPING
Altering the definition of a user mapping	ALTER USER MAPPING
Dropping a user mapping for a foreign server	DROP USER MAPPING

Defining a View

**Table 38** SQL statements for defining a view
Function	SQL Statement
Creating a view	CREATE VIEW
Modifying the auxiliary attributes of a view	ALTER VIEW
Forcibly deleting a view from the database	DROP VIEW

Collecting Statistics

For details about how to collect statistics related to the contents of ordinary tables in the database, see ANALYZE | ANALYSE.

Creating an Encoding Conversion Task

For details about how to define a new conversion between two character set encodings, see CREATE CONVERSION.

Defining a Model

**Table 39** SQL statements for defining a model
Function	SQL Statement
Training a machine learning model and saving the model	CREATE MODEL
Deleting a model that has been trained and saved	DROP MODEL

Defining a Rewriting Rule

**Table 40** SQL statements for defining a rewriting rule
Function	SQL Statement
Defining a new rewriting rule	CREATE RULE
Deleting a rewriting rule	DROP RULE

Defining a Security Label

**Table 41** SQL statements for defining a security label
Function	SQL Statement
Creating a security label	CREATE SECURITY LABEL
Applying, updating, or canceling a security label	SECURITY LABEL ON
Deleting a security label	DROP SECURITY LABEL

Importing a Database/Table

**Table 42** SQL statements for data import
Function	SQL Statement
Preparation phase for importing a database	IMPDP DATABASE CREATE
Execution phase of importing a database	IMPDP RECOVER
Preparation phase for importing a table	IMPDP TABLE PREPARE
Execution phase of importing a table	IMPDP TABLE

Clearing a Recycle Bin

GaussDB provides statements for clearing a recycle bin. For details, see PURGE.

Clustering a Table

GaussDB supports statements for clustering a table based on an index. For details, see CLUSTER.

Defining an Object Comment

GaussDB supports statements for defining or modifying an object comment. For details, see COMMENT.

Creating a Table Based on Query Results

GaussDB supports statements for creating a table based on query results and inserting queried data into the new table. For details, see SELECT INTO.

Restoring a Table to an Earlier State

GaussDB allows you to restore a table to an earlier state in the event of a manual operation or application error. For details, see TIMECAPSULE TABLE.