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
- CREATE DATABASE
- CREATE INDEX
- CREATE TABLE
- CREATE EXTERNAL TABLE
- CREATE PUBLICATION
- CREATE SEQUENCE
- CREATE STAGE
- CREATE...FROM...PUBLICATION...
- CREATE VIEW
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)
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
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:
- SHOW DATABASES
- SHOW CREATE TABLE
- SHOW CREATE VIEW
- SHOW CREATE PUBLICATION
- SHOW TABLES
- SHOW INDEX
- SHOW COLLATION
- SHOW COLUMNS
- SHOW FUNCTION STATUS
- SHOW GRANT
- SHOW PROCESSLIST
- SHOW PUBLICATIONS
- SHOW ROLES
- SHOW SEQUENCES
- SHOW STAGE
- SHOW SUBSCRIPTIONS
- SHOW VARIABLES
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: