SQL Statements in 10.2 CS¶

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 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.

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 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 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 Platform 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 PROCEDURE

Provides the CREATE PROCEDURE statement needed to recreate the given stored procedure.

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.

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 PLUGINS

Provides information on installed plugins.

SHOW PLUGINS SONAME

Provides information on all build-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