DDL Syntax Overview

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

M-compatible does not support DDL operations when the primary database node is incomplete. For example, if the primary database node in 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. Table 1 lists the involved SQL statements.

**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. Table 2 lists the involved SQL statements.

**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 Database or Schema

In M compatibility, database and schema are synonyms. A database or schema 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 3 lists the involved SQL statements.

**Table 3** SQL statements for defining a database
Function	SQL Statement
Creating a database or schema	CREATE DATABASE and CREATE SCHEMA
Modifying a database or schema attribute	ALTER DATABASE and ALTER SCHEMA
Deleting a database or schema	DROP DATABASE and DROP SCHEMA
Specifying the current mode	USE

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 4 lists the involved SQL statements.

**Table 4** SQL statements for defining a table
Function	SQL Statement
Creating a table	CREATE TABLE
Creating a table based on a query result	CREATE TABLE SELECT
Altering table attributes	ALTER TABLE
Changing one or more tables to be flashed back	RENAME 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). Table 5 lists the involved SQL statements.

**Table 5** 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 6 lists the involved SQL statements.

**Table 6** 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 Plug-in Extension

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

**Table 7** 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 a User Group

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

**Table 9** 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 View

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

Defining a Function

**Table 11** SQL statements for defining a function
Function	SQL Statement
Creating a function	CREATE FUNCTION
Deleting a function	DROP FUNCTION

Defining a Resource Label

**Table 12** SQL statements for defining a resource label
Function	SQL Statement
Creating a resource label	CREATE RESOURCE LABEL
Altering a resource label	ALTER RESOURCE LABEL
Dropping a resource label	DROP RESOURCE LABEL

Defining an Audit Policy

**Table 13** SQL statements for defining an audit policy
Function	SQL Statement
Creating a unified audit policy	CREATE AUDIT POLICY
Altering a unified audit policy	ALTER AUDIT POLICY
Dropping an audit policy	DROP AUDIT POLICY

Collecting Statistics

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

Clearing a Recycle Bin

M-compatibility provides statements for clearing a recycle bin. For details, see PURGE.

Defining an Object Comment

M-compatibility supports statements for defining or modifying an object comment. For details, see COMMENT.

Creating a Table Based on Query Results

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