# SQL Statements


This page lists common SQL statements for MatrixOne instances by category. It is a **categorized index**, not a complete grammar for every statement. The console's Permissions · MatrixOne settings and instance capabilities determine whether you can execute a statement.

## DDL (data definition)

Defines or changes databases, tables, indexes, views, and MatrixOne extension objects.

| Category | Statements (summary) |
| --- | --- |
| Create | `CREATE DATABASE`, `CREATE TABLE`, `CREATE TABLE AS SELECT`, `CREATE EXTERNAL TABLE`, `CREATE VIEW`, `CREATE INDEX` / vector indexes, `CREATE SEQUENCE`, `CREATE FUNCTION`, `CREATE STAGE`, `CREATE PUBLICATION`, `CREATE ... FROM ... PUBLICATION` (subscription), `CREATE SNAPSHOT`, `CREATE PITR`, `CREATE SOURCE`, `CREATE DYNAMIC TABLE`, and others |
| Alter | `ALTER TABLE`, `ALTER VIEW`, `ALTER SEQUENCE`, `ALTER STAGE`, `ALTER PUBLICATION`, `ALTER PITR`, `ALTER REINDEX`, `RENAME TABLE`, and others |
| Drop | `DROP DATABASE`, `DROP TABLE`, `DROP VIEW`, `DROP INDEX`, `DROP SEQUENCE`, `DROP FUNCTION`, `DROP STAGE`, `DROP PUBLICATION`, `DROP SNAPSHOT`, `DROP PITR`, and others |
| Truncate | `TRUNCATE TABLE` |
| Restore | `RESTORE SNAPSHOT`, `RESTORE PITR`, `RESTORE ACCOUNT`, and others, subject to privilege and scenario restrictions |

Product-oriented descriptions of extension objects:

- **Stage**: An abstraction for an external storage location, commonly used with `LOAD DATA` and file-listing functions.
- **Publication / Subscription**: Publishes and subscribes to data across instances.
- **Snapshot / PiTR**: Capabilities related to backup and point-in-time recovery.
- **External / Dynamic table**: External and dynamic tables, subject to availability in the instance version.

## DML (data manipulation)

| Statement | Purpose |
| --- | --- |
| `INSERT` / `INSERT INTO ... SELECT` | Inserts rows |
| `INSERT IGNORE` / `INSERT ... ON DUPLICATE KEY UPDATE` | Ignores a conflict or updates on conflict |
| `REPLACE` | Replaces rows |
| `UPDATE` | Updates existing rows |
| `DELETE` | Deletes rows |
| `LOAD DATA INFILE` / `LOAD DATA INLINE` | Bulk loading. See the syntax subset in [Limitations](limitations.md). |

## DQL (data query)

Centered on `SELECT`:

| Capability | Key points |
| --- | --- |
| Single table / projection / filtering / aggregation | `SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT` |
| Joins | `INNER` / `LEFT` / `RIGHT` / `FULL` / `NATURAL` / `OUTER` JOIN |
| Subqueries | `IN` / `EXISTS` / `ANY`/`SOME` / `ALL`, and derived tables |
| CTE | `WITH` common table expressions |
| Set operations | `UNION`, `INTERSECT`, `MINUS` |

## TCL (transactions)

| Statement | Purpose |
| --- | --- |
| `START TRANSACTION` / `BEGIN` | Starts a transaction |
| `COMMIT` | Commits |
| `ROLLBACK` | Rolls back |
| `SET AUTOCOMMIT` | Controls automatic commits |

## DCL (data control)

Use with [Privilege Categories](privileges.md) and [Permissions · MatrixOne](../../guides/matrixone/permissions.md):

| Statement | Purpose |
| --- | --- |
| `CREATE USER` / `DROP USER` / `ALTER USER` | Users |
| `CREATE ROLE` / `DROP ROLE` | Roles |
| `GRANT` / `REVOKE` | Grants and revokes privileges |

## Other administrative statements

| Category | Examples |
| --- | --- |
| Switch context | `USE` a database or switch a role |
| `SHOW` | `SHOW DATABASES`, `SHOW TABLES`, `SHOW COLUMNS`, `SHOW CREATE TABLE`, `SHOW INDEX`, `SHOW VARIABLES`, `SHOW GRANTS`, `SHOW PROCESSLIST`, `SHOW ROLES`, `SHOW PUBLICATIONS`, `SHOW SUBSCRIPTIONS`, `SHOW STAGE`, `SHOW SNAPSHOTS`, `SHOW PITR`, `SHOW SEQUENCES`, `SHOW FUNCTION STATUS`, and others |
| `SET` | Sets session or global variables, uses `SET ROLE`, and more |
| Prepared statements | `PREPARE` / `EXECUTE` / `DEALLOCATE` |
| Plans | `EXPLAIN` / `EXPLAIN ANALYZE` |
| Sessions | `KILL` |

## Relationship to platform features

| Business scenario | Common statement categories |
| --- | --- |
| Creating tables and querying data in Catalog | DDL + DQL + DML |
| Sharing data across instances | Publication / Subscription DDL and `SHOW` |
| Backup and recovery | Snapshot / PiTR DDL |
| Vector search | `CREATE TABLE` with vector columns + vector indexes + distance-function queries |
| Privilege governance within an instance | DCL + `SHOW GRANTS` |