MogDB
Ecological Tools
Doc Menu

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.

img 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