Classification of SQL statements¶

In MatrixOne, SQL statements contain multiple categories, and the definition and content of each category are displayed in each section described below:

DDL - Data Definition Language¶

Data Definition Language (DDL) is a type of DBMS language used to clearly define data objects. In MatrixOne, DDL statements are divided into five categories:

CREATE statement to create various objects in MatrixOne¶

DROP statement, delete various objects in MatrixOne¶

ALTER statement to modify various objects in MatrixOne¶

TRUNCATE statement to clear the data in the table¶

TRUNCATE TABLE

DML - Data modification language¶

Data Manipulation Language (DML) is used for database operations, including programming statements that perform access to objects and data in the database. In MatrixOne, DML contains the following categories:

INSERT statement, used to insert new rows in tables¶

DELETE statement, used to delete existing rows in the table¶

DELETE

UPDATE statement to modify the data of rows that already exist in the table¶

UPDATE

LOAD DATA statement, batch import data from a file to a database¶

REPLACE statement, replace line¶

REPLACE

DQL - Data Query Language¶

Query class statements (DQL) are used to retrieve existing data in MatrixOne. With SELECT statements as the core, they are divided into the following categories:

Single table query, there is only one query level, and only involves single table query¶

SELECT

Join Query, a query method that correlates the results between multiple tables and outputs the results¶

Common Table Expressions, which treat certain queries as temporary results, can be referenced in other SQLs, such as SELECT, INSERT, UPDATE, and DELETE, which only exists during query execution.

With CTE

Combination query, combine the results of multiple queries, and present them in a set way, and are divided into union (UNION), intersection (INTERSECT), and differential (MINUS)¶

In addition to SELECT STATEMENT, it also contains query VALUES statements for constants¶

SELECT

and the internal commands corresponding to the mo-dump tool¶

Export Data

TCL - Transaction Language¶

Transaction language is a language dedicated to transaction management in MatrixOne, including the following categories:

START TRANSACTION, enables transactions, and can be replaced in MatrixOne with its dialect BEGIN¶

START TRANSACTION;
  TRANSACTION STATEMENTS

COMMIT, commit transactions, used for explicit commits to transactions in START TRANSACTION or non-automatic commit scenarios¶

START TRANSACTION;
  TRANSACTION STATEMENTS
  COMMIT;
  OR
  SET AUTOCOMMIT=0;
  TRANSACTION STATEMENTS
  COMMIT;

ROLLBACK, rollback transactions, used for explicit rollback of transactions in START TRANSACTION or non-automatic commit scenarios¶

START TRANSACTION;
  TRANSACTION STATEMENTS
  ROLLBACK;
  OR
  SET AUTOCOMMIT=0;
  TRANSACTION STATEMENTS
  ROLLBACK;

DCL - Data Control Language¶

Data Control Language (DCL) includes the allocation and recycling of resources, the creation and deletion of users and roles, and the authorization and recycling of permissions. It is divided into the following categories:

CREATE statement, used to create users and roles¶

DROP statement used to delete users and roles¶

Other - Management Language¶

The management language includes the acquisition and modification of database parameters and resource allocation that are not directly related to the data, including the following statement types:

SHOW statement¶

Get information through the SHOW statement:

SET statement¶

Through SET statements, users can adjust various database parameters and present them through SHOW commands.

SET ROLE

KILL statement¶

Statement to abort a database connection:

KILL

USE statement¶

Used to connect to an existing database:

USE DATABASE

Explain statement¶

Used to view SQL execution plan:

Explain Analyze

PREPARE statement¶

Prepare a SQL statement and assign it a name:

PREPARE

EXECUTE statement¶

Use the PREPARE prepared statement name and execute:

EXECUTE

DEALLOCATE PREPARE statement¶

Frees the precompiled statement generated using PREPARE. After releasing the precompiled statement, executing the precompiled statement again will result in an error:

DEALLOCATE