Updated on 2024-06-03 GMT+08:00

DML Syntax Overview

Data manipulation language (DML) is used to perform operations on data in database tables, such as inserting, updating, querying, or deleting data.

Inserting Data

Inserting data refers to adding one or multiple records to a database table. For details, see INSERT.

Updating Data

Updating data refers to modifying one or multiple records in a database table. For details, see UPDATE.

Modifying or Inserting Data

GaussDB provides statements for matching data in a target table with that in a source table based on join conditions. If data matches, UPDATE is executed on the target table; if data does not match, INSERT is executed. For details, see MERGE INTO.

Querying Data

The database query statement SELECT is used to search required information in a database. For details, see SELECT.

Deleting Data

GaussDB provides statements for deleting data that meets specified conditions from a table. For details, see DELETE.

Copying Data

GaussDB provides a statement for copying data between tables and files. For details, see COPY.

Locks

GaussDB provides multiple lock modes to control concurrent accesses to table data. For details, see LOCK.

GaussDB provides bucket-level locks. For details, see LOCK BUCKETS.

Calling a Function

GaussDB provides three statements for calling functions. These statements are the same in the syntax structure. For details, see CALL.

Prepared Statements

Table 1 SQL statements related to prepared statements

Function

SQL Statement

Executing a prepared statement

EXECUTE

Deallocating a prepared statement

DEALLOCATE

Session Management

A session is a connection established between the user and the database. Table 2 lists the related SQL statements.

Table 2 SQL statements related to sessions

Function

SQL Statement

Altering a session

ALTER SESSION

Killing a session

ALTER SYSTEM KILL SESSION

Executing an Anonymous Code Block

GaussDB provides statements for executing an anonymous code block. For details, see DO.

Exporting Files

Table 3 SQL statements for exporting files

Function

SQL Statement

Exporting all physical files in a database

EXPDP DATABASE

Exporting all files related to the table

EXPDP TABLE

Retrieving Data Using a Cursor

GaussDB provides statements for retrieving data using a created cursor. For details, see FETCH.

Inserting or Replacing Data

GaussDB provides statements for inserting or replacing data in a table. For details, see REPLACE.

Calculating a Value based on an Expression

GaussDB provides statements for calculating a value of a row or a group of rows based on a given value expression. For details, see VALUES.

Importing Data

GaussDB provides a statement for importing data from a file to a specified table in the database. For details, see LOAD DATA.