# **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

- [CREATE DATABASE](Data-Definition-Language/create-database.md)
- [CREATE INDEX](Data-Definition-Language/create-index.md)
- [CREATE TABLE](Data-Definition-Language/create-table.md)
- [CREATE EXTERNAL TABLE](Data-Definition-Language/create-external-table.md)
- [CREATE FUNCTION](Data-Definition-Language/create-function-sql.md)
- [CREATE PITR](Data-Definition-Language/create-pitr.md)
- [CREATE PUBLICATION](Data-Definition-Language/create-publication.md)
- [CREATE SEQUENCE](Data-Definition-Language/create-sequence.md)
- [CREATE SNAPSHOT](Data-Definition-Language/create-snapshot.md)
- [CREATE STAGE](Data-Definition-Language/create-stage.md)
- [CREATE...FROM...PUBLICATION...](Data-Definition-Language/create-subscription.md)
- [CREATE VIEW](Data-Definition-Language/create-view.md)

### DROP statement, delete various objects in MatrixOne

- [DROP INDEX](Data-Definition-Language/drop-index.md)
- [DROP TABLE](Data-Definition-Language/drop-table.md)
- [DROP FUNCTION](Data-Definition-Language/drop-function.md)
- [DROP PITR](Data-Definition-Language/drop-pitr.md)
- [DROP PUBLICATION](Data-Definition-Language/drop-publication.md)
- [DROP SEQUENCE](Data-Definition-Language/drop-sequence.md)
- [DROP SNAPSHOT](Data-Definition-Language/drop-snapshot.md)
- [DROP STAGE](Data-Definition-Language/drop-stage.md)
- [DROP VIEW](Data-Definition-Language/drop-view.md)

### ALTER statement to modify various objects in MatrixOne

- [ALTER TABLE](Data-Definition-Language/alter-table.md)
- [ALTER PUBLICATION](Data-Definition-Language/alter-publication.md)
- [ALTER PITR](Data-Definition-Language/alter-pitr.md)
- [ALTER REINDEX](Data-Definition-Language/alter-reindex.md)
- [ALTER SEQUENCE](Data-Definition-Language/alter-sequence.md)
- [ALTER STAGE](Data-Definition-Language/alter-stage.md)
- [ALTER VIEW](Data-Definition-Language/alter-view.md)

### TRUNCATE statement to clear the data in the table

- [TRUNCATE TABLE](Data-Definition-Language/truncate-table.md)

## **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

- [INSERT](Data-Manipulation-Language/insert.md)
- [INSERT INTO SELECT](Data-Manipulation-Language/insert-into-select.md)
- [INSERT IGNORE](Data-Manipulation-Language/upsert/insert-ignore.md)
- [INSERT ON DUPLICATE](Data-Manipulation-Language/upsert/insert-on-duplicate.md)

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

- [DELETE](Data-Manipulation-Language/delete.md)

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

- [UPDATE](Data-Manipulation-Language/update.md)

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

- [LOAD DATA INFILE](Data-Manipulation-Language/load-data-infile.md)
- [LOAD DATA INLINE](Data-Manipulation-Language/load-data-inline.md)

### REPLACE statement, replace line

- [REPLACE](Data-Manipulation-Language/upsert/replace.md)

## **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](Data-Query-Language/select.md)

### Subquery, also known as nested query or sub-select, is a query method in which SELECT subquery statements are embedded in another SQL query. They are divided into related subqueries and non-associated subqueries

- [SUBQUERY with ANY or SOME](Data-Query-Language/subqueries/subquery-with-any-some.md)
- [SUBQUERY with ALL](Data-Query-Language/subqueries/subquery-with-all.md)
- [SUBQUERY with EXISTS](Data-Query-Language/subqueries/subquery-with-exists.md)
- [SUBQUERY with IN](Data-Query-Language/subqueries/subquery-with-in.md)

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

- [INNER JOIN](Data-Query-Language/join/inner-join.md)
- [LEFT JOIN](Data-Query-Language/join/left-join.md)
- [RIGHT JOIN](Data-Query-Language/join/right-join.md)
- [FULL JOIN](Data-Query-Language/join/full-join.md)
- [OUTER JOIN](Data-Query-Language/join/outer-join.md)
- [NATURAL JOIN](Data-Query-Language/join/natural-join.md)

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](Data-Query-Language/with-cte.md)

### 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)

- [UNION](Data-Query-Language/union.md)
- [INTERSECT](Data-Query-Language/intersect.md)
- [MINUS](Data-Query-Language/minus.md)

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

- [SELECT](Data-Query-Language/select.md)

### 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

```text
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;
```text

### 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

- [CREATE ROLE](Data-Control-Language/create-role.md)
- [CREATE USER](Data-Control-Language/create-user.md)

### DROP statement used to delete users and roles

- [DROP USER](Data-Control-Language/drop-user.md)
- [DROP ROLE](Data-Control-Language/drop-role.md)

### ALTER statement to modify user information

- [ALTER USER](Data-Control-Language/alter-user.md)

### GRANT statement, authorizes users or roles

- [GRANT](Data-Control-Language/grant.md)

### REVOKE statement, performs recycle operations on users or roles

- [REVOKE](Data-Control-Language/revoke.md)

## **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:

- [SHOW DATABASES](Other/SHOW-Statements/show-databases.md)
- [SHOW CREATE TABLE](Other/SHOW-Statements/show-create-table.md)
- [SHOW CREATE VIEW](Other/SHOW-Statements/show-create-view.md)
- [SHOW CREATE PUBLICATION](Other/SHOW-Statements/show-create-publication.md)
- [SHOW TABLES](Other/SHOW-Statements/show-tables.md)
- [SHOW INDEX](Other/SHOW-Statements/show-index.md)
- [SHOW COLLATION](Other/SHOW-Statements/show-collation.md)
- [SHOW COLUMNS](Other/SHOW-Statements/show-columns.md)
- [SHOW FUNCTION STATUS](Other/SHOW-Statements/show-function-status.md)
- [SHOW GRANTS](Other/SHOW-Statements/show-grants.md)
- [SHOW PROCESSLIST](Other/SHOW-Statements/show-processlist.md)
- [SHOW PUBLICATIONS](Other/SHOW-Statements/show-publications.md)
- [SHOW PITR](Other/SHOW-Statements/show-pitr.md)
- [SHOW ROLES](Other/SHOW-Statements/show-roles.md)
- [SHOW SEQUENCES](Other/SHOW-Statements/show-sequences.md)
- [SHOW SNAPSHOTS](Other/SHOW-Statements/show-snapshots.md)
- [SHOW STAGES](Other/SHOW-Statements/show-stage.md)
- [SHOW SUBSCRIPTIONS](Other/SHOW-Statements/show-subscriptions.md)
- [SHOW VARIABLES](Other/SHOW-Statements/show-variables.md)

### SET statement

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

- [SET ROLE](Other/Set/set-role.md)

### KILL statement

Statement to abort a database connection:

- [KILL](Other/kill.md)

### USE statement

Used to connect to an existing database:

- [USE DATABASE](Other/use-database.md)

### Explain statement

Used to view SQL execution plan:

- [Explain Analyze](Other/Explain/explain-analyze.md)

### PREPARE statement

Prepare a SQL statement and assign it a name:

- [PREPARE](Other/Prepared-Statements/prepare.md)

### EXECUTE statement

Use the PREPARE prepared statement name and execute:

- [EXECUTE](Other/Prepared-Statements/execute.md)

### 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](Other/Prepared-Statements/deallocate.md)