SQL Statements for MariaDB Community Server 10.4

This page is part of MariaDB's Documentation.

The parent of this page is: Reference for MariaDB Community Server 10.4

Topics on this page:

Overview

Overview

Structured Query Language (SQL) statements enable the reading and writing of data within the database. MariaDB Community Server implements classic and modern SQL database features.

SQL Statement	Description
ALTER DATABASE	Modifies database characteristics, such as changing the default character-set and collation
ALTER EVENT	Modifies event characteristics, including the schedule, name, and definer
ALTER FUNCTION	Modifies stored function characteristics, including whether it contains SQL, modifies data, and its privilege requirements. Does not support modifying the stored function code.
ALTER PROCEDURE	Modifies stored procedure characteristics, including whether it contains SQL code, modifies data, and its privilege requirements. Does not support modifying the stored procedure code.
ALTER SCHEMA	See ALTER DATABASE
ALTER SEQUENCE	Modifies sequence characteristics, including its increment, minimum, maximum, and starting values
ALTER SERVER	Modifies specifications for a server definition
ALTER TABLE	Modifies table characteristics, including changing the name, reordering the columns, and changing column data types
ALTER USER	Modifies user characteristics, including changing and setting account authentication, TLS restrictions, and password expiration
ALTER VIEW	Modifies view characteristics, including the view algorithm, name, and definer
ANALYZE	Evaluates the Optimizer query plan estimates against execution time
ANALYZE TABLE	Evaluates and stores index statistics for a table. The table remains locked during analysis.
BACKUP LOCK	Sets lock on table to ensure a consistent backup, causing it to reject DDL statements
BACKUP STAGE	Performs various staging tasks to minimize lock time during backups
BACKUP UNLOCK	Removes backup locks from table
BEGIN	Initiates a transaction
BINLOG	Internal statement used by mysqlbinlog to generate printable representations of certain binary log events
CACHE INDEX	Sets a specific Key Cache for the index on MyISAM tables
CALL	Executes a stored procedure or function
CHANGE MASTER TO	Specifies the primary server to use in replication
CHECK TABLE	Evaluates table or tables for errors
CHECK VIEW	Evaluates whether the view algorithm is correct
CHECKSUM TABLE	Retrieves table checksum, which is used to determine whether two tables are the same (such as on a primary and replica server in replication)
COMMIT	Ends a transaction, saving all associated changes so that they become visible to subsequent transactions
CREATE AGGREGATE FUNCTION	Creates a function that is computed over a sequence of rows
CREATE DATABASE	Creates a database
CREATE EVENT	Creates and schedules an event
CREATE FUNCTION	Creates a stored function and specifies the code it executes
CREATE INDEX	Creates and builds indexes for the table
CREATE PROCEDURE	Creates a stored procedure and specifies the code it executes
CREATE ROLE	Creates security roles for user accounts
CREATE SEQUENCE	Creates a sequence
CREATE SERVER	Creates a server definition, for use with the Spider storage engine
CREATE SPATIAL INDEX	Creates and builds a spatial index for the table
CREATE TABLE	Creates and specifies a table for data storage
CREATE TRIGGER	Creates a trigger event and specifies the conditions in which it runs
CREATE USER	Creates a user account
CREATE VIEW	Creates view of the database as specified from the given SELECT statement
DEALLOCATE PREPARE	Deallocates a prepared statement. The PREPARE statement implicitly deallocates an already open prepared statements.
DELETE	Removes rows of data from the given table or tables
DESC	See DESCRIBE
DESCRIBE	Provides the table specification
DO	Executes the specified expression without returning a result-set
DROP DATABASE	Deletes the given database with all its tables and data
DROP EVENT	Deletes an event
DROP FUNCTION	Deletes a user-defined or stored function
DROP INDEX	Deletes an index from the table
DROP PREPARE	Deletes a prepared statement
DROP PROCEDURE	Deletes a stored procedure
DROP ROLE	Deletes a security role for user accounts
DROP SEQUENCE	Deletes a sequence
DROP SERVER	Deletes a server definition
DROP TABLE	Deletes a table and the data it contains from the database
DROP TRIGGER	Deletes a trigger from tables and database
DROP USER	Deletes a user account
DROP VIEW	Deletes a view of the database
EXECUTE	Runs a prepared statement
EXPLAIN	Provides information on table specifications or on how MariaDB Server executes the given SQL statement. See DESCRIBE.
FLUSH	Clears or reloads various internal caches
GET DIAGNOSTICS	Retrieves information from the diagnostic area on errors, warnings, and notes produced by the last SQL statement
GET DIAGNOSTICS CONDITION	Retrieves information from a condition in the diagnostic area on errors, warnings, and notes produced by the last SQL statement
GRANT	Assigns privileges to a user account or security role
HANDLER	Provides direct access to the storage engine interface for key lookups or key table scans, where the storage engine supports the operation
HELP	Used by the client to retrieve basic syntax help and a short description of commands and functions
INSERT	Adds a row or rows of data to table
INSTALL PLUGIN	Installs an individual plugin from the specified library
INSTALL SONAME	Installs all plugins in a library
KILL	Terminates a statement or thread
LOAD DATA INFILE	Copies rows from a text file into the designated table
LOAD INDEX INTO CACHE	Copies a table index into the Key Cache assigned by a CACHE INDEX statement
LOAD XML INFILE	Copies rows from an XML file into the designated table
LOCK TABLE	Explicitly acquires a table lock for the current client session
OPTIMIZE TABLE	Performs optimization on tablespaces, defragmenting files and updating InnoDB Full Text indexes
PREPARE	Creates a prepared statement and assigns it a name for later use
PURGE BINARY LOGS	Deletes all binary log files listed in the log index prior to the creation of the specified log file name or date, as well as from the log index file. The given log file becomes the first in the list.
PURGE MASTER LOGS	See PURGE BINARY LOGS
RELEASE SAVEPOINT	Removes the specified savepoint from a transaction. It does not rollback or commit any changes.
RENAME TABLE	Renames one or more tables or views, but not the associated privileges
RENAME USER	Renames user accounts
REPAIR TABLE	Repairs corrupted tables and improves compression
REPAIR VIEW	Repairs view algorithms, correcting checksums and, where necessary, adding a version field
REPLACE	Adds a new row or rows to the table, replacing the row in the event of a conflict with Primary or Unique index keys
RESET MASTER	Deletes all binary log files listed in the log index file. Use only when primary server first starts, never when replica servers are actively replication from the binary log.
RESET QUERY CACHE	Removes all query results saved in the Query Cache
RESET SLAVE	Resets the replication position in the primary server binary log, giving the replica server a clean start
RESIGNAL	Resends an error in a HANDLER for errors
REVOKE	Removes privileges granted to a user account or security role
ROLLBACK	Cancels a transaction and reverts any changes made to data, so that they never become visible to subsequent transactions
SAVEPOINT	Creates a named sub-transaction
SELECT	Retrieves data from one or more tables
SET	Assigns a value to various types of variables, which can affect server and client operations
SET CHARACTER SET	Defines the character-set and collation to use for strings sent between the current client and server
SET DEFAULT ROLE	Defines the default security role for the current or specified user account
SET GLOBAL TRANSACTION	Defines the default transaction level for all subsequent client sessions (does not include existing sessions)
SET NAMES	Defines the character-set and collation the client uses to send to the server and the server uses to send to the client
SET PASSWORD	Defines the password for the given user account
SET ROLE	Enables a security role with all associated privileges for the current session
SET STATEMENT	Assigns a value to a system variable for the duration of a statement
SET TRANSACTION	Sets the isolation level for the next transaction started in the current session
SHOW	Provides information about databases, tables, columns, or status information about the server
SHOW ALL SLAVES STATUS	Run on the replica server, lists all connections to the primary server, as well as status information on essential parameters of replication threads
SHOW AUTHORS	Lists information about the people who work on MariaDB Server, ordered by those active on MariaDB Server, followed by those involved in the legacy MySQL code as well as those who have contributed in the past
SHOW BINARY LOGS	Lists the binary log files
SHOW BINLOG EVENTS	Lists events in the given binary log. When no binary log is given, it defaults to the first.
SHOW CHARACTER SET	Lists the available character-sets
SHOW CLIENT_STATISTICS	Lists User Statistics, retrieved from information_schema.CLIENT_STATISTICS. Set the userstat system variable to `1` to use.
SHOW COLLATION	Lists the available character-set collations
SHOW COLUMNS	Lists the column specifications for the given table or view
SHOW CONTRIBUTORS	Lists information about the companies and people who financially contribute to the development of MariaDB Server
SHOW COUNT(*) ERRORS	Reports the number of errors that resulted from the last statement
SHOW COUNT(*) WARNINGS	Reports the number of warnings that resulted from the last statement
SHOW CREATE DATABASE	Provides the CREATE DATABASE statement needed to recreate the given database with its current specification
SHOW CREATE EVENT	Provides the CREATE EVENT statement needed to recreate the given event with its current specification
SHOW CREATE FUNCTION	Provides the CREATE FUNCTION statement needed to recreate the given stored or user-defined function with its current specification
SHOW CREATE PACKAGE	Provides the CREATE PACKAGE statement needed to recreate the given SQL/PL package
SHOW CREATE PACKAGE BODY	Provides the SQL/PL code for package
SHOW CREATE PROCEDURE	Provides the CREATE PROCEDURE statement needed to recreate the given stored procedure
SHOW CREATE SEQUENCE	Provides the CREATE SEQUENCE statement needed to recreate the given sequence
SHOW CREATE TABLE	Provides the CREATE TABLE statement needed to recreate the given table
SHOW CREATE TRIGGER	Provides the CREATE TRIGGER statement needed to recreate the given trigger
SHOW CREATE USER	Provides the CREATE USER statement needed to recreate the given user
SHOW CREATE VIEW	Provides the CREATE VIEW statement needed to recreate the given view
SHOW DATABASES	Lists the available databases
SHOW ENGINE	Provides operational information about the available storage engines
SHOW ENGINE INNODB STATUS	Provides status information on the InnoDB storage engine
SHOW ENGINES	Provides information on the available storage engines
SHOW ERRORS	Lists errors resulting from the last statement
SHOW EVENTS	Lists information about the Event Manager
SHOW EXPLAIN	Provides a query plan description for a running query process
SHOW FUNCTION CODE	Provides the internal representation of the given stored function
SHOW FUNCTION STATUS	Lists characteristics of the given stored function, like the database, name, type, and creator
SHOW GRANTS	Lists the privileges granted to the given user account
SHOW INDEX	Provides information on indexes for the given table
SHOW INDEX_STATISTICS	Queries the Information Schema for statistics on index usage, to aid in identifying unused indexes and generated commands to remove them
SHOW INDEXES	See SHOW INDEX
SHOW KEYS	See SHOW INDEX
SHOW LOCALES	Lists localization information available through the Locales plugin
SHOW MASTER LOGS	See SHOW BINARY LOGS
SHOW MASTER STATUS	Provides status information on the binary log files of the Primary Server. Alias for SHOW BINLOG STATUS in 10.5.
SHOW OPEN TABLES	Lists non-temporary tables currently open in the table cache with additional information, including the database and table names, number of table instances in use, and whether the table is name-locked
SHOW PACKAGE BODY CODE	Provides the internal representation of the given stored SQL/PL package
SHOW PACKAGE BODY STATUS	Lists specific characteristics of the given SQL/PL package body
SHOW PACKAGE STATUS	Lists general characteristics of the given SQL/PL package
SHOW PLUGINS	Provides information on installed plugins
SHOW PLUGINS SONAME	Provides information on all built-in and available plugins in the plugins directory
SHOW PRIVILEGES	Lists supported system privileges
SHOW PROCEDURE CODE	Provides the internal representation of the given stored procedure
SHOW PROCEDURE STATUS	Lists characteristics of the given stored procedure, like the database, name, and creator
SHOW PROCESSLIST	Lists information on the currently running threads, including their thread ID, user, command, time, and progress
SHOW PROFILE	Provides detailed profiling information on resource usage for a single statement executed during the current session
SHOW PROFILES	Provides detailed profiling information on resource usage statements executed during the current session
SHOW QUERY_RESPONSE_TIME	Lists information on response time from the QUERY_RESPONSE_TIME plugin
SHOW RELAYLOG EVENTS	Run on the Replica Server, lists the events currently in the Relay Log
SHOW SCHEMAS	See SHOW DATABASES
SHOW SLAVE HOSTS	Run on the Primary Server, lists information on the currently registers Replica Servers
SHOW SLAVE STATUS	Run on the Replica Server, lists status information on essential parameters of the replication threads
SHOW STATUS	Lists status information
SHOW STORAGE ENGINES	See SHOW ENGINES
SHOW TABLE STATUS	Lists information on the non-temporary tables, sequences, and views in the given database
SHOW TABLES	Lists the non-temporary tables, sequences, and views in the given database
SHOW TRIGGERS	Lists information on the currently defined triggers for tables in the given database
SHOW USER_STATISTICS	Lists data from the Information Schema for the USER_STATISTICS table
SHOW VARIABLES	Lists system variables and their current values
SHOW WARNINGS	Lists the errors, warnings, and notes that resulted from the last statement
SHOW WSREP_MEMBERSHIP	Lists data from the Information Schema for the WSREP_MEMBERSHIP table
SHOW WSREP_STATUS	Lists data from the Information Schema for the WSREP_STATUS table
SHUTDOWN	Shuts down the server
SIGNAL	Empties the diagnostics area, producing a custom error. Useful in stored procedures and functions.
START ALL SLAVES	Initiates replication for all Replica Servers
START SLAVE	Initiates replication for the current Replica Server
START TRANSACTION	Initiates a transaction
STOP ALL SLAVES	Halts replication for all running Replica Servers
STOP SLAVE	Halts replication for the current Replica Server
TRUNCATE	Removes all rows from the specified table
TRUNCATE TABLE	See TRUNCATE
UNINSTALL PLUGIN	Removes the specified plugin
UNINSTALL SONAME	Removes all plugins that belong to the specified plugin library
UNLOCK TABLE	Explicitly releases any locks from the given table
UPDATE	Sets new values on rows in a table or tables
USE	Sets the default database
WITH	Initiates Common Table Expression (CTE), allowing the reference to a subquery expression many times in the given query that exists only for the duration of the query
XA BEGIN	Initiates an XA transaction
XA COMMIT	Ends the prepared XA transaction, committing the changes
XA END	Ends the active XA transaction, changing its state to idle
XA PREPARE	Prepares an idle XA transaction for commit
XA RECOVER	Provides information on all prepared XA transactions
XA ROLLBACK	Ends an idle or prepared XA transaction, reverts its changes
XA START	See XA BEGIN