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