Sign InTry Free

SQL Statement Overview

TiDB uses SQL statements that aim to follow ISO/IEC SQL standards, with extensions for MySQL and TiDB-specific statements where necessary.

Schema management / Data definition statements (DDL)

SQL StatementDescription
ALTER DATABASEModifies a database.
ALTER SEQUENCEModifies a sequence.
ALTER TABLE ... ADD COLUMNAdds a column to an existing table.
ALTER TABLE ... ADD INDEXAdds an index to an existing table.
ALTER TABLE ... ALTER INDEXChanges an index definition.
ALTER TABLE ... CHANGE COLUMNChanges a column definition.
ALTER TABLE ... COMPACTCompacts a table.
ALTER TABLE ... DROP COLUMNDrops a column from a table.
ALTER TABLE ... MODIFY COLUMNModifies a column definition.
ALTER TABLE ... RENAME INDEXRenames an index.
ALTER TABLEChanges a table definition.
CREATE DATABASECreates a new database.
CREATE INDEXCreates a new index on a table.
CREATE SEQUENCECreates a new sequence object.
CREATE TABLE LIKECopies the definition of an existing table, without copying any data.
CREATE TABLECreates a new table.
CREATE VIEWCreates a new view.
DROP DATABASEDrops an existing database.
DROP INDEXDrops an index from a table.
DROP SEQUENCEDrops a sequence object.
DROP TABLEDrops an existing table.
DROP VIEWDrops an existing view.
RENAME TABLERenames a table.
SHOW COLUMNS FROMShows the columns from a table.
SHOW CREATE DATABASEShows the CREATE statement for a database.
SHOW CREATE SEQUENCEShows the CREATE statement for a sequence.
SHOW CREATE TABLEShows the CREATE statement for a table.
SHOW DATABASESShows a list of databases that the current user has privileges to.
SHOW FIELDS FROMShows columns of a table.
SHOW INDEXESShows indexes of a table.
SHOW SCHEMASAn alias to SHOW DATABASES, which shows a list of databases that the current user has privileges to.
SHOW TABLE NEXT_ROW_IDShows the next row ID for a table.
SHOW TABLE REGIONSShows the Region information of a table in TiDB.
SHOW TABLE STATUSShows various statistics about tables in TiDB.
SHOW TABLESShows tables in a database.
TRUNCATETruncates all data from a table.

Data manipulation statements (DML)

SQL StatementDescription
BATCHSplits a DML statement into multiple statements in TiDB for execution.
DELETEDeletes rows from a table.
INSERTInserts new rows into a table.
REPLACEReplaces existing rows or inserts new rows.
SELECTReads data from a table.
TABLERetrieves rows from a table.
UPDATEUpdates existing rows in a table.
WITHDefines common table expressions.

Transaction statements

SQL StatementDescription
BEGINBegins a new transaction.
COMMITCommits the current transaction.
ROLLBACKRolls back the current transaction.
SAVEPOINTSets a savepoint within a transaction.
SET TRANSACTIONChanges the current isolation level on a GLOBAL or SESSION basis.
START TRANSACTIONStarts a new transaction.

Prepared statements

SQL StatementDescription
DEALLOCATEDeallocates a prepared statement, freeing associated resources.
EXECUTEExecutes a prepared statement with specific parameter values.
PREPARECreates a prepared statement with placeholders.

Administrative statements

SQL StatementDescription
ADMIN CANCEL DDLCancels a DDL job.
ADMIN CHECK [TABLE\|INDEX]Checks the integrity of a table or index.
ADMIN CHECKSUM TABLEComputes the checksum of a table.
ADMIN CLEANUP INDEXCleans up indexes from a table.
ADMIN PAUSE DDLPauses DDL operations.
ADMIN RESUME DDLResumes DDL operations.
ADMIN SHOW DDL [JOBS\|JOB QUERIES]Shows DDL jobs or job queries.
ADMIN SHOW TELEMETRYShows telemetry data.
ADMINPerforms various administrative tasks.
FLUSH TABLESIncluded for MySQL compatibility. It has no effective usage in TiDB.
SET <variable>Modifies a system variable or user variable.
SET [NAMES\|CHARACTER SET]Set a character set and collation.
SPLIT REGIONSplits a Region into smaller Regions.
SQL StatementDescription
ADMIN CANCEL DDLCancels a DDL job.
ADMIN CHECK [TABLE\|INDEX]Checks the integrity of a table or index.
ADMIN CHECKSUM TABLEComputes the checksum of a table.
ADMIN CLEANUP INDEXCleans up indexes from a table.
ADMIN PAUSE DDLPauses DDL operations.
ADMIN RECOVER INDEXRecovers the consistency based on the redundant indexes.
ADMIN RESUME DDLResumes DDL operations.
ADMIN SHOW DDL [JOBS\|JOB QUERIES]Shows DDL jobs or job queries.
ADMINPerforms various administrative tasks.
FLUSH TABLESIncluded for MySQL compatibility. It has no effective usage in TiDB.
SET <variable>Modifies a system variable or user variable.
SET [NAMES\|CHARACTER SET]Set a character set and collation.
SPLIT REGIONSplits a Region into smaller Regions.

Data import and export

SQL StatementDescription
CANCEL IMPORT JOBCancels an ongoing import job.
IMPORT INTOImports data into a table via the Physical Import Mode of TiDB Lightning.
LOAD DATALoads data into a table from Amazon S3 or Google Cloud Storage.
SHOW IMPORT JOBShows the status of an import job.

Backup & restore

SQL StatementDescription
BACKUPPerforms a distributed backup of the TiDB cluster.
FLASHBACK CLUSTERRestores the cluster to a specific snapshot.
FLASHBACK DATABASERestores a database and its data deleted by the DROP statement.
FLASHBACK TABLERestore the tables and data dropped by the DROP or TRUNCATE operation.
RECOVER TABLERecovers a deleted table and the data on it.
RESTORERestores a database from a backup.
SHOW BACKUPSShows backup tasks.
SHOW RESTORESShows restore tasks.

Placement policy

SQL StatementDescription
ALTER PLACEMENT POLICYModifies a placement policy.
ALTER RANGEModifies the range of a placement policy.
CREATE PLACEMENT POLICYCreates a new placement policy.
DROP PLACEMENT POLICYDrops an existing placement policy.
SHOW CREATE PLACEMENT POLICYShows the CREATE statement for a placement policy.
SHOW PLACEMENT FORShows placement rules for a specific table.
SHOW PLACEMENT LABELSShows available placement labels.
SHOW PLACEMENTShows placement rules.

Resource groups

SQL StatementDescription
ALTER RESOURCE GROUPModifies a resource group.
CALIBRATE RESOURCEEstimates and outputs the Request Unit (RU) capacity of the current cluster.
CREATE RESOURCE GROUPCreates a new resource group.
DROP RESOURCE GROUPDrops a resource group.
QUERY WATCHManages the runaway query watch list.
SET RESOURCE GROUPSets a resource group.
SHOW CREATE RESOURCE GROUPShows the CREATE statement for a resource group.
SQL StatementDescription
ALTER RESOURCE GROUPModifies a resource group.
CREATE RESOURCE GROUPCreates a new resource group.
DROP RESOURCE GROUPDrops a resource group.
QUERY WATCHManages the runaway query watch list.
SET RESOURCE GROUPSets a resource group.
SHOW CREATE RESOURCE GROUPShows the CREATE statement for a resource group.

Utility statements

SQL StatementDescription
DESCAn alias to DESCRIBE, which shows the structure of a table.
DESCRIBEShows the structure of a table.
DOExecutes an expression but does not return any results.
EXPLAINShows the execution plan of a query.
TRACEProvides detailed information about query execution.
USESets the current database.

Show statements

SQL StatementDescription
SHOW BUILTINSLists builtin functions.
SHOW CHARACTER SETLists character sets.
SHOW COLLATIONSLists collations.
SHOW ERRORSShows errors from previously executed statements.
SHOW STATUSIncluded for compatibility with MySQL. TiDB uses Prometheus and Grafana for centralized metrics collection instead of SHOW STATUS for most metrics.
SHOW VARIABLESShows system variables.
SHOW WARNINGSShows warnings and notes from previously executed statements.
SQL StatementDescription
SHOW BUILTINSLists builtin functions.
SHOW CHARACTER SETLists character sets.
SHOW COLLATIONSLists collations.
SHOW ERRORSShows errors from previously executed statements.
SHOW STATUSIncluded for compatibility with MySQL. TiDB Cloud provides Monitoring for centralized metrics collection instead of SHOW STATUS for most metrics.
SHOW VARIABLESShows system variables.
SHOW WARNINGSShows warnings and notes from previously executed statements.

Instance management

SQL StatementDescription
ALTER INSTANCEModifies an instance.
FLUSH STATUSIncluded for compatibility with MySQL. TiDB uses Prometheus and Grafana for centralized metrics collection instead of SHOW STATUS for most metrics.
KILLKills a connection in any TiDB instance in the current TiDB cluster.
SHOW CONFIGShows the configuration of various components of TiDB.
SHOW ENGINESShows available storage engines.
SHOW PLUGINSShows installed plugins.
SHOW PROCESSLISTShows the current sessions connected to the same TiDB server.
SHOW PROFILESShows query profiles. Included for compatibility with MySQL. Currently, it only returns an empty result.
SHUTDOWNStops the client-connected TiDB instance, not the entire TiDB cluster.
SQL StatementDescription
ALTER INSTANCEModifies an instance.
FLUSH STATUSIncluded for compatibility with MySQL. TiDB Cloud provides Monitoring for centralized metrics collection instead of SHOW STATUS for most metrics.
KILLKills a connection in any TiDB instance in the current TiDB cluster.
SHOW ENGINESShows available storage engines.
SHOW PLUGINSShows installed plugins.
SHOW PROCESSLISTShows the current sessions connected to the same TiDB server.
SHOW PROFILESShows query profiles. Included for compatibility with MySQL. Currently only returns an empty result.

Locking statements

SQL StatementDescription
LOCK STATSLocks statistics of tables or partitions.
LOCK TABLESLocks tables for the current session.
UNLOCK STATSUnlocks statistics of tables or partitions.
UNLOCK TABLESUnlocks tables.

Account management / Data Control Language

SQL StatementDescription
ALTER USERModifies a user.
CREATE ROLECreates a role.
CREATE USERCreates a new user.
DROP ROLEDrops an existing role.
DROP USERDrops an existing user.
FLUSH PRIVILEGESReloads the in-memory copy of privileges from the privilege tables.
GRANT <privileges>Grants privileges.
GRANT <role>Grants a role.
RENAME USERRenames an existing user.
REVOKE <privileges>Revokes privileges.
REVOKE <role>Revokes a role.
SET DEFAULT ROLESets a default role.
SET PASSWORDChanges a password.
SET ROLEEnables roles in the current session.
SHOW CREATE USERShows the CREATE statement for a user.
SHOW GRANTSShows privileges associated with a user.
SHOW PRIVILEGESShows available privileges.

TiCDC & TiDB Binlog

SQL StatementDescription
ADMIN [SET\|SHOW\|UNSET] BDR ROLEManages BDR roles.
CHANGE DRAINERModifies the status information for Drainer in the cluster.
CHANGE PUMPModifies the status information for Pump in the cluster.
SHOW DRAINER STATUSShows the status for all Drainer nodes in the cluster.
SHOW MASTER STATUSShows the latest TSO in the cluster.
SHOW PUMP STATUSShows the status information for all Pump nodes in the cluster.
SQL StatementDescription
SHOW MASTER STATUSShows the latest TSO in the cluster.

Statistics and plan management

SQL StatementDescription
ANALYZE TABLECollects statistics about a table.
CREATE BINDINGCreates an execution plan binding for a SQL statement.
DROP BINDINGDrops an execution plan binding from a SQL statement.
DROP STATSDrops statistics from a table.
EXPLAIN ANALYZEWorks similar to EXPLAIN, with the major difference that it will execute the statement.
LOAD STATSLoads statistics into TiDB.
SHOW ANALYZE STATUSShows statistics collection tasks.
SHOW BINDINGSShows created SQL bindings.
SHOW STATS_HEALTHYShows an estimation of how accurate statistics are believed to be.
SHOW STATS_HISTOGRAMSShows the histogram information in statistics.
SHOW STATS_LOCKEDShows the tables whose statistics are locked.
SHOW STATS_METAShows how many rows are in a table and how many rows are changed in that table.

Was this page helpful?

Download PDFRequest docs changesAsk questions on DiscordEdit this page
Playground
One-stop & interactive experience of TiDB's capabilities WITHOUT registration.
Products
TiDB
TiDB Dedicated
TiDB Serverless
Pricing
Get Demo
Get Started
© 2024 PingCAP. All Rights Reserved.
Privacy Policy.