SQL Statements for MariaDB Xpand 23.09

Overview

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

SQL Statement

Description

ABORT

ALTER BINARY LOG

ALTER BINLOG

ALTER CLUSTER

Contains several sub-statements that can be used to alter an Xpand cluster

ALTER CLUSTER ADD

Adds node(s) to an Xpand cluster with the UNCOORDINATED option by default

ALTER CLUSTER CHANGE REGION

ALTER CLUSTER CREATE OBSERVER REGION

ALTER CLUSTER CREATE PRIMARY REGION

ALTER CLUSTER CREATE SECONDARY REGION

ALTER CLUSTER CREATE ZONE

ALTER CLUSTER DROP

Drops a failed node from the cluster and triggers a group change

ALTER CLUSTER DROP REGION

ALTER CLUSTER DROP ZONE

ALTER CLUSTER REFORM

Reforms the cluster and triggers a group change to apply changes made by previous ALTER CLUSTER statements

ALTER CLUSTER RELOAD RSA

Reloads the RSA key pair used by the sha256_password authentication plugin that are defined by sha256_password_public_key_path and sha256_password_private_key_path

ALTER CLUSTER RELOAD SSL

Reloads the TLS/SSL certificates and keys that are defined by ssl_cert and ssl_key

ALTER CLUSTER RENAME REGION

ALTER CLUSTER RENAME ZONE

ALTER CLUSTER RESET ALERTER

ALTER CLUSTER RESIZE DEVICES

Resizes the device1 files on all nodes in the cluster

ALTER CLUSTER SET MAX_FAILURES

Modifies an Xpand cluster, configures the Rebalancer to sets the number of replicas and acceptors to maintain, specifying the maximum number of Xpand nodes that can fail simultaneously without data loss

ALTER CLUSTER SOFTFAIL

Starts a softfail process for one or more nodes, which configures the Rebalancer to move data off of the nodes so that they can be safely removed from the cluster

ALTER CLUSTER UNSOFTFAIL

Cancels a softfail process for one or more nodes

ALTER CLUSTER ZONE

Adds one or more nodes into a zone

ALTER DATABASE

Modifies database characteristics, such as changing the default character-set and collation

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 NODE

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

ANALYSE TABLE

ANALYZE TABLE

Evaluates and stores index statistics for a table. The table remains locked during analysis.

BACKUP

Creates a backup

CALL

Executes a stored procedure or function

CASE

CHANGE MASTER TO

Specifies the primary server to use in replication

CHANGE REPLICA

CHANGE SLAVE

CLOSE

COMMIT

Ends a transaction, saving all associated changes so that they become visible to subsequent transactions

CREATE BINARY LOG

CREATE BINLOG

CREATE DATABASE

Creates a database

CREATE FUNCTION

Creates a stored function and specifies the code it executes

CREATE INDEX

Creates and builds indexes for the table

CREATE OR REPLACE TABLE

CREATE OR REPLACE VIEW

CREATE PROCEDURE

Creates a stored procedure and specifies the code it executes

CREATE REPLICA

CREATE ROLE

Creates a role

CREATE SCHEMA

CREATE SLAVE

CREATE TABLE

Creates and specifies a table for data storage

CREATE TEMPORARY VIEW

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

DEALLOCATE PREPARE

Deallocates a prepared statement. The PREPARE statement implicitly deallocates an already open prepared statements.

DECLARE

DELETE

Removes rows of data from the given table or tables

DESC

See DESCRIBE

DESCRIBE

Provides the table specification

DROP BINARY LOG

DROP BINLOG

DROP DATABASE

Deletes the given database with all its tables and data

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 REPLICA

DROP ROLE

Deletes a role

DROP SCHEMA

DROP SLAVE

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 Xpand executes the given SQL statement. See DESCRIBE.

FETCH

FLUSH

Clears or reloads various internal caches

GRANT

Assigns privileges to a user account or security role

IF

INSERT

Adds a row or rows of data to table

ITERATE

KILL

Terminates a statement or thread

LOAD DATA INFILE

Copies rows from a text file into the designated table

LOCK TABLES

OPEN

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

PREPARE TRANSACTION

RELEASE

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

REPAIR LOCAL TABLE

REPAIR NO_WRITE_TO_BINLOG TABLE

REPAIR TABLE

Repairs corrupted tables and improves compression

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

REPLICA START

REPLICA STOP

RESTORE

Restores a backup

RETURN

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

ROLLBACK PREPARED

ROLLBACK TO SAVEPOINT

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

Sets the default role for the specified user

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

Activates the role for the current user's session

SET TRANSACTION ISOLATION LEVEL

SHOW

Provides information about databases, tables, columns, or status information about the server

SHOW ALL BINARY LOG FILES

SHOW ALL BINARY LOGS

SHOW ALL BINLOG FILES

SHOW ALL MASTER LOGS

SHOW ALL MASTER STATUS

SHOW BINARY LOG FILES

SHOW BINARY LOGS

Lists the binary log files

SHOW BINLOG FILES

SHOW BINLOGS

SHOW CHARACTER SET

Lists the available character-sets

SHOW CHARSET

SHOW CLUSTER STATUS

SHOW COLLATION

Lists the available character-set collations

SHOW COLUMNS

Lists the column specifications for the given table or view

SHOW CREATE DATABASE

Provides the CREATE DATABASE statement needed to recreate the given database 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 SCHEMA

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 ENGINES

Provides information on the available storage engines

SHOW FIELDS

SHOW FULL CREATE DATABASE

SHOW FULL CREATE SCHEMA

SHOW FULL CREATE TABLE

SHOW FULL CREATE VIEW

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 INDEXES

See SHOW INDEX

SHOW INNODB STATUS

SHOW KEYS

See SHOW INDEX

SHOW LOAD

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 PLUGINS

Displays information about installed plugins

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

SHOW REPLICA STATUS

SHOW REPLICA VARIABLES

SHOW SCHEMAS

See SHOW DATABASES

SHOW SIMPLE CREATE DATABASE

SHOW SIMPLE CREATE SCHEMA

SHOW SIMPLE CREATE TABLE

SHOW SIMPLE CREATE VIEW

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 SLAVE VARIABLES

SHOW STATUS

Lists status information

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 VARIABLES

Lists system variables and their current values

SHOW WARNINGS

Lists the errors, warnings, and notes that resulted from the last statement

SHUTDOWN

Shuts down the server

SHUTDOWN NODE

SIGNAL

Empties the diagnostics area, producing a custom error. Useful in stored procedures and functions.

SIGNAL SQLSTATE

SLAVE START

SLAVE STOP

START REPLICA

START SLAVE

Initiates replication for the current Replica Server

START TRANSACTION

Initiates a transaction

STOP REPLICA

STOP SLAVE

Halts replication for the current Replica Server

TRIM BINARY LOG

TRIM BINLOG

TRUNCATE

Removes all rows from the specified table

UNLOCK TABLES

UPDATE

Sets new values on rows in a table or tables

USE

Sets the default database