Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive

DDL Syntax Overview

Updated on 2022-07-29 GMT+08:00

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

NOTE:

GaussDB(DWS) does not support DDL if its CN is unavailable. For example, if a CN in the cluster is faulty, creating a database or 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. The following table lists the related SQL statements.

Table 1 SQL statements for defining a database

Function

SQL Statement

Create a database

CREATE DATABASE

Alter database attributes

ALTER DATABASE

Delete 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. The following table lists the related SQL statements.

Table 2 SQL statements for defining a schema

Function

SQL Statement

Create a schema

CREATE SCHEMA

Alter schema attributes

ALTER SCHEMA

Delete a schema

DROP SCHEMA

Defining a Tablespace

A tablespace is used to manage data objects and corresponds to a catalog on a disk. The following table lists the related SQL statements.

Table 3 SQL statements for defining a tablespace

Function

SQL Statement

Create a tablespace

CREATE TABLESPACE

Delete 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 the relationship between data objects. The following table lists the related SQL statements.

Table 4 SQL statements for defining a table

Function

SQL Statement

Create a table

CREATE TABLE

Alter table attributes

ALTER TABLE

Delete a table

DROP TABLE

Delete all the data from a table

TRUNCATE

Defining a Partitioned Table

A partitioned table is a special data structure in a database and is used to store data objects and the relationship between data objects. The following table lists the related SQL statements.

Table 5 SQL statements for defining a partitioned table

Function

SQL Statement

Create a partitioned table

CREATE TABLE PARTITION

Create a partition

ALTER TABLE PARTITION

Alter partitioned table attributes

ALTER TABLE PARTITION

Delete a partition

ALTER TABLE PARTITION

Delete a partitioned table

DROP TABLE

Defining an Index

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

Table 6 SQL statements for defining an index

Function

SQL Statement

Create an index

CREATE INDEX

Alter index attributes

ALTER INDEX

Delete an index

DROP INDEX

Rebuild an index

REINDEX

Defining a Role

A role is used to manage rights. For database security, all management and operation rights can be assigned to different roles. The following table lists the related SQL statements.

Table 7 SQL statements for defining a role

Function

SQL Statement

Create a role

CREATE ROLE

Alter role attributes

ALTER ROLE

Delete a role

DROP ROLE

Defining a User

A user is used to log in to a database. Different rights can be assigned to users for managing data accesses and operations of users. The following table lists the related SQL statements.

Table 8 SQL statements for defining a user

Function

SQL Statement

Create a user

CREATE USER

Alter user attributes

ALTER USER

Delete a user

DROP USER

Defining a Redaction Policy

Data redaction is to protect sensitive data by masking or changing data. You can create a data redaction policy for a specific table object and specify the effective scope of the policy. You can also add, modify, and delete redaction columns. The following table lists the related SQL statements.

Table 9 SQL statements for managing redaction policies

Function

SQL Statement

Create a data redaction policy

CREATE REDACTION POLICY

Modify a data redaction policy applied to a specified table

ALTER REDACTION POLICY

Delete a data redaction policy applied to a specified table

DROP REDACTION POLICY

Defining Row-Level Access Control

Row-level access control policies control the visibility of rows in database tables. In this way, the same SQL query may return different results for different users. The following table lists the related SQL statements.

Table 10 SQL statements for row-level access control

Function

SQL Statement

Create a row-level access control policy

CREATE ROW LEVEL SECURITY POLICY

Modify an existing row-level access control policy

ALTER ROW LEVEL SECURITY POLICY

Delete a row-level access control policy from a table

DROP ROW LEVEL SECURITY POLICY

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. The following table lists the related SQL statements.

Table 11 SQL statements for defining a stored procedure

Function

SQL Statement

Create a stored procedure

CREATE PROCEDURE

Delete a stored procedure

DROP PROCEDURE

Defining a Function

In GaussDB(DWS), a function is similar to a stored procedure, which is a set of SQL statements. The function and stored procedure are used the same. The following table lists the related SQL statements.

Table 12 SQL statements for defining a function

Function

SQL Statement

Create a function

CREATE FUNCTION

Alter function attributes

ALTER FUNCTION

Delete a function

DROP FUNCTION

Defining a View

A view is a virtual table exported from one or several basic tables. The view is used to control data accesses for users. The following table lists the related SQL statements.

Table 13 SQL statements for defining a view

Function

SQL Statement

Create a view

CREATE VIEW

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

Table 14 SQL statements for defining a cursor

Function

SQL Statement

Create a cursor

CURSOR

Move a cursor

MOVE

Extract data from a cursor

FETCH

Close a cursor

CLOSE

Altering or Ending a Session

A session is a connection established between the user and the database. The following table lists the related SQL statements.

Table 15 SQL statements related to sessions

Function

SQL Statement

Alter a session

ALTER SESSION

End a session

ALTER SYSTEM KILL SESSION

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. The following table lists the related SQL statements.

Table 16 SQL statements for defining a resource pool

Function

SQL Statement

Create a resource pool

CREATE RESOURCE POOL

Change resource attributes

ALTER RESOURCE POOL

Delete a resource pool

DROP RESOURCE POOL

Defining Synonyms

A synonym is a special database object compatible with Oracle. It is used to store the mapping between a database object and another. Currently, only synonyms can be used to associate the following database objects: tables, views, functions, and stored procedures. The following table lists the related SQL statements.

Table 17 SQL statements for managing synonyms

Function

SQL Statement

Creating a synonym

CREATE SYNONYM

Modifying a synonym

ALTER SYNONYM

Deleting a synonym

DROP SYNONYM

Defining Text Search Configuration

A text search configuration specifies a text search parser that can divide a string into tokens, plus dictionaries that can be used to determine which tokens are of interest for searching. The following table lists the related SQL statements.

Table 18 SQL statements for configuring text search

Function

SQL Statement

Create a text search configuration

CREATE TEXT SEARCH CONFIGURATION

Modify a text search configuration

ALTER TEXT SEARCH CONFIGURATION

Delete a text search configuration

DROP TEXT SEARCH CONFIGURATION

Defining a Full-text Retrieval Dictionary

A dictionary is used to identify and process specific words during full-text retrieval. Dictionaries are created by using predefined templates (defined in the PG_TS_TEMPLATE system catalog). Dictionaries of the Simple, Ispell, Synonym, Thesaurus, and Snowball types can be created. The following table lists the related SQL statements.

Table 19 SQL statements for a full-text search dictionary

Function

SQL Statement

Create a full-text retrieval dictionary

CREATE TEXT SEARCH DICTIONARY

Modify a full-text retrieval dictionary

ALTER TEXT SEARCH DICTIONARY

Delete a full-text retrieval dictionary

DROP TEXT SEARCH DICTIONARY

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback