SQL Statements for MariaDB Enterprise Server 10.5

Overview

Structured Query Language (SQL) statements enable the reading and writing of data within the database. MariaDB Enterprise 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 DATABASE COMMENT

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 SCHEMA COMMENT

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 UNIQUE INDEX

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

INSERT ... RETURNING

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

REPLACE ... RETURNING

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 REPLICA

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 REPLICAS STATUS

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 BINLOG STATUS

Provides status information on the binary log files of the Primary Server. Alias for SHOW MASTER STATUS in 10.5.

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 REPLICA HOSTS

SHOW REPLICA STATUS

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 REPLICAS

START ALL SLAVES

Initiates replication for all Replica Servers

START REPLICA

START SLAVE

Initiates replication for the current Replica Server

START TRANSACTION

Initiates a transaction

STOP ALL REPLICAS

STOP ALL SLAVES

Halts replication for all running Replica Servers

STOP REPLICA

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