Updated on 2024-05-07 GMT+08:00

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 if its CN is unavailable. For example, if the primary node of the database is faulty, creating a database or a table will fail.

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. For details about related SQL statements, see Table 1.

Table 1 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. For details about related SQL statements, see Table 1.

Table 2 SQL statements for defining a CEK

Function

SQL Statement

Creating a CEK

CREATE COLUMN ENCRYPTION KEY

Changing the client master key specified by a CEK

8.14.191-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. For details about related SQL statements, see Table 3.

Table 3 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. For details about related SQL statements, see Table 4.

Table 4 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. For details about related SQL statements, see Table 5.

Table 5 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. For details about related SQL statements, see Table 6.

Table 6 SQL statements for defining a table

Function

SQL Statement

Creating a table

CREATE TABLE

Altering table attributes

ALTER TABLE

Dropping a table

DROP TABLE

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). For details about related SQL statements, see Table 7.

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

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. For details about related SQL statements, see Table 8.

Table 8 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

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. For details about related SQL statements, see Table 9.

Table 9 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. For details about related SQL statements, see Table 10.

Table 10 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 11 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 View

A view is a virtual table exported from one or more basic tables. It is used to control data accesses of users. Table 12 lists the related SQL statements.

Table 12 SQL statements for defining a view

Function

SQL Statement

Creating a view

CREATE VIEW

Dropping a view

DROP VIEW

Defining a Cursor

To process SQL statements, the stored procedure process 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. For details, see Table 13.

Table 13 SQL statements for defining a cursor

Function

SQL Statement

Creating a cursor

CURSOR

Moving a cursor

MOVE

Fetching data from a cursor

FETCH

Closing a cursor

CLOSE

Defining an Aggregate Function

Table 14 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 15 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

The extended function is for internal use only. You are advised not to use it.

Table 16 SQL statements for defining a plug-in extension

Function

SQL Statement

Creating a plug-in extension

CREATE EXTENSION

Modifying a plug-in extension

ALTER EXTENSION

Deleting a plug-in extension

DROP EXTENSION

Defining an Operator

Table 17 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 Data Type

Table 18 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 19 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

A database link can be used to remotely operate a database object. For details about corresponding SQL statements, see Table 20.

Table 20 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 a Foreign Data Wrapper

Table 21 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