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.

NOTE: MogDB does not support DDL when the primary node of the database is incomplete. For example, if one primary database node in MogDB is faulty, creating a database or a table will fail.

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 1.

Table 1 SQL statements for defining a database

Description	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 2.

Table 2 SQL statements for defining a schema >

Description	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 3.

Table 3 SQL statements for defining a tablespace

Description	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 4.

Table 4 SQL statements for defining a table

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

Table 5 SQL statements for defining a partitioned table

Description	SQL Statement
Creating a partitioned table	CREATE TABLE PARTITION
Creating 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 6.

Table 6 SQL statements for defining an index

Description	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 7.

Table 7 SQL statements for defining a stored procedure

Description	SQL Statement
Creating a stored procedure	CREATE PROCEDURE
Dropping a stored procedure	DROP PROCEDURE

Defining a Function

In MogDB, 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 8.

Table 8 SQL statements for defining a function

Description	SQL Statement
Creating a function	CREATE FUNCTION
Altering function attributes	ALTER FUNCTION
Dropping a function	DROP FUNCTION

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 9 lists the related SQL statements.

Table 9 SQL statements for defining a view

Description	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 pointing to context regions. With a cursor, the stored procedure can control alterations in context areas. For details, see Table 10.

Table 10 SQL statements for defining a cursor

Description	SQL Statement
Creating a cursor	CURSOR
Moving a cursor	MOVE
Fetching data from a cursor	FETCH
Closing a cursor	CLOSE