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:

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

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

UPDATE Statement - Modifying Data in Existing Rows of a Table

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

REPLACE Statement - Replacing Rows

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

Subquery - Nested Queries Embedded in Another SQL Query

Join Query - Combining Results from Multiple Tables

Common Table Expressions - Temporary Results for Reuse in Queries

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

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

And internal commands corresponding to the modump tool

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)

  1. START TRANSACTION;
  2.   TRANSACTION STATEMENTS

COMMIT - Committing a Transaction

  1. START TRANSACTION;
  2.   TRANSACTION STATEMENTS
  3.   COMMIT;
  4.   OR
  5.   SET AUTOCOMMIT=0;
  6.   TRANSACTION STATEMENTS
  7.   COMMIT;

ROLLBACK - Rolling Back a Transaction

  1. START TRANSACTION;
  2.   TRANSACTION STATEMENTS
  3.   ROLLBACK;
  4.   OR
  5.   SET AUTOCOMMIT=0;
  6.   TRANSACTION STATEMENTS
  7.   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

REVOKE Statement - Revoking Permissions from Users or Roles

Other - Management Language

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

SHOW Statements

Using SHOW statements to retrieve information:

SET Statements

Using SET statements to adjust various database parameters, with results displayed via SHOW commands:

KILL Statement

Used to terminate a specific database connection:

USE Statement

Utilized for connecting to an existing database:

Explain Statement

Used to view SQL execution plans:

PREPARE Statement

Prepares a SQL statement and assigns it a name:

EXECUTE Statement

After preparing a statement using PREPARE, you can reference the precompiled statement name and execute it:

DEALLOCATE PREPARE Statement

Used to release precompiled statements generated by PREPARE. Executing the precompiled statement after deallocation will result in an error: