Type of SQL Statements¶

In MatrixOne, SQL statements are classified into various categories, and each category’s definition and contents are presented in the following sections:

In MatrixOne, SQL statements are classified into various categories, and each category’s definition and contents are presented in the following sections:

DDL - Data Definition Language¶

Data Definition Language (DDL) is a subset of DBMS language used for defining data objects. In MatrixOne, DDL statements are divided into five categories:

CREATE Statements - Creating Various Objects in MatrixOne¶

DROP Statements - Deleting Various Objects in MatrixOne¶

ALTER Statements - Modifying Various Objects in MatrixOne¶

TRUNCATE Statement - Clearing Data from a Table¶

TRUNCATE TABLE

RENAME 语句¶

RENAME TABLE

RESTORE 语句¶

DML - Data Manipulation Language¶

Data Manipulation Language (DML) is used for database operations, including programming statements to work with database objects and data. In MatrixOne, DML is categorized as follows:

INSERT Statements - Inserting New Rows into a Table¶

DELETE Statement - Deleting Existing Rows from a Table¶

DELETE

UPDATE Statement - Modifying Data in Existing Rows of a Table¶

UPDATE

LOAD DATA Statement - Bulk Importing Data from Files into the Database¶

REPLACE Statement - Replacing Rows¶

REPLACE

DQL - Data Query Language¶

Data Query Language (DQL) is used to retrieve existing data in MatrixOne. It primarily consists of SELECT statements and includes the following categories:

Single-Table Query - Involving a Single Table with One-Level Hierarchy¶

SELECT

Subquery - Nested Queries Embedded in Another SQL Query¶

Apply Query - A query that applies an operation to each row of data¶

Join Query - Combining Results from Multiple Tables¶

Common Table Expressions - Temporary Results for Reuse in Queries¶

With CTE

Combination Queries - Combining Results of Multiple Queries with UNION, INTERSECT, and MINUS Operations¶

In addition to SELECT statements, DQL includes VALUES statements for constants¶

SELECT

And internal commands corresponding to the modump tool¶

Export Data

TCL - Transaction Control Language¶

Transaction Control Language (TCL) in MatrixOne provides specialized language for transaction management and includes the following categories:

START TRANSACTION - Initiating a Transaction (BEGIN can be used as a dialect in MatrixOne)¶

START TRANSACTION;
  TRANSACTION STATEMENTS

COMMIT - Committing a Transaction¶

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

ROLLBACK - Rolling Back a Transaction¶

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

DCL - Data Control Language¶

Data Control Language (DCL) includes commands for resource allocation and deallocation, user and role creation and deletion, and authorization and revocation of permissions in MatrixOne, categorized as follows:

CREATE Statements - Creating Tenants, Users, and Roles¶

DROP Statements - Deleting Accounts, Users, and Roles¶

ALTER Statements - Modifying Account or User Information¶

GRANT Statement - Granting Permissions to Users or Roles¶

GRANT

REVOKE Statement - Revoking Permissions from Users or Roles¶

REVOKE

Other - Management Language¶

Management language in MatrixOne pertains to parameters and resource allocation not directly associated with data. It includes various statement types: