Statements

ASQL takes the MySQL and MariaDB statements below. Statements specific to ASQL are listed at the end.

Queries

SELECT ..., including SELECT ... INTO @a, @b
WITH [RECURSIVE] ... SELECT ...
VALUES (...), (...) as a statement
UNION [ALL | DISTINCT], INTERSECT, EXCEPT
DO expr, ...

See Operators and expressions for joins, GROUP BY, LIMIT and locking clauses.

Changing data

INSERT [IGNORE] [INTO] t (cols) VALUES (...), (...), INSERT ... SET col = value, INSERT ... SELECT ...
INSERT ... ON DUPLICATE KEY UPDATE col = VALUES(col)
INSERT ... ON CONFLICT [(cols)] DO NOTHING | DO UPDATE SET ... (PostgreSQL)
REPLACE [INTO] ...
UPDATE t SET ... WHERE ..., and multi-table UPDATE a JOIN b ON ... SET ...
DELETE FROM t WHERE ..., multi-table DELETE a FROM a JOIN b ..., and DELETE FROM a USING b WHERE ...
INSERT, REPLACE and DELETE ... RETURNING cols
TRUNCATE [TABLE] t
LOAD DATA [LOCAL] INFILE ...; see Import and export

Databases, tables, views and indexes

CREATE DATABASE [IF NOT EXISTS] db, DROP DATABASE [IF EXISTS] db, USE db
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] t (...), CREATE TABLE t LIKE other, CREATE TABLE t AS SELECT ...
DROP [TEMPORARY] TABLE [IF EXISTS] a, b
ALTER TABLE t ...:
- ADD [COLUMN], MODIFY, CHANGE [COLUMN], DROP [COLUMN], RENAME COLUMN a TO b
- ALTER [COLUMN] c SET DEFAULT ... | DROP DEFAULT
- ADD PRIMARY KEY, ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX, ADD [CONSTRAINT] CHECK (...), ADD [CONSTRAINT n] FOREIGN KEY (...) REFERENCES t (...) [ON DELETE ...] [ON UPDATE ...]
- DROP INDEX | PRIMARY KEY | FOREIGN KEY | CHECK | CONSTRAINT, RENAME INDEX a TO b, RENAME TO t2
- table options such as ENGINE, CHARSET, COMMENT and AUTO_INCREMENT = n are accepted
- PARTITION BY ... and partition changes that only lay rows out (ADD, REORGANIZE, COALESCE PARTITION, REMOVE PARTITIONING) are accepted and change nothing: a table is held whole. DROP, TRUNCATE and EXCHANGE PARTITION return an error; delete the rows with a WHERE instead
RENAME TABLE a TO b, c TO d
CREATE [UNIQUE] INDEX n ON t (cols), DROP INDEX n ON t
CREATE [OR REPLACE] VIEW v AS SELECT ..., ALTER VIEW, DROP VIEW
CREATE TYPE name AS ENUM (...), DROP TYPE name
CREATE [OR REPLACE] SEQUENCE [IF NOT EXISTS] s [START WITH n] [INCREMENT BY n] [MINVALUE n] [MAXVALUE n] [CACHE n | NOCACHE] [CYCLE | NOCYCLE], ALTER SEQUENCE s RESTART [WITH n] (and the same options), DROP SEQUENCE [IF EXISTS] s, SHOW CREATE SEQUENCE s. Values come from NEXTVAL(s) or NEXT VALUE FOR s, and can be a column's default. A restart continues after the last block of CACHE values reserved, so values are never repeated

Stored programs

CREATE [OR REPLACE] PROCEDURE | FUNCTION | TRIGGER | EVENT ..., DROP ... [IF EXISTS]
CALL proc(...)
ALTER EVENT e ENABLE | DISABLE
Inside a body: BEGIN ... END, DECLARE (variables, cursors, handlers), IF, CASE, WHILE, REPEAT, LOOP, LEAVE, ITERATE, RETURN, OPEN/FETCH/CLOSE, SIGNAL, RESIGNAL, GET DIAGNOSTICS
SET GLOBAL event_scheduler = ON | OFF
Event schedules take one unit (EVERY 5 MINUTE); a function or trigger cannot call itself.

Transactions

BEGIN, START TRANSACTION [READ ONLY], COMMIT, ROLLBACK
SAVEPOINT s, ROLLBACK TO [SAVEPOINT] s, RELEASE SAVEPOINT s
SELECT ... FOR UPDATE | FOR SHARE [NOWAIT | SKIP LOCKED], LOCK IN SHARE MODE
SET TRANSACTION ISOLATION LEVEL ... is accepted; reads behave as described in Support and differences
LOCK TABLES and UNLOCK TABLES are accepted and do nothing

Prepared statements and variables

PREPARE p FROM 'SELECT ... ?', EXECUTE p USING @a, DEALLOCATE PREPARE p or DROP PREPARE p
SET @a = expr, SET @a := expr
SET [SESSION] sql_mode | foreign_key_checks | autocommit | time_zone | transaction_isolation = ...
SET NAMES ... and other SET forms MySQL clients send are accepted

Information

SHOW DATABASES, SHOW [FULL] TABLES, SHOW TABLE STATUS, SHOW [FULL] COLUMNS FROM t, SHOW INDEX FROM t
SHOW CREATE TABLE | VIEW | DATABASE | PROCEDURE | FUNCTION | TRIGGER | EVENT
SHOW TRIGGERS, SHOW PROCEDURE STATUS, SHOW FUNCTION STATUS, SHOW EVENTS
SHOW VARIABLES, SHOW STATUS, SHOW PROCESSLIST, SHOW WARNINGS, SHOW ERRORS, SHOW GRANTS
SHOW ENGINES, SHOW CHARSET, SHOW COLLATION
SHOW REPLICA STATUS, SHOW MASTER STATUS, SHOW BINARY LOGS
DESCRIBE t, EXPLAIN [ANALYZE] SELECT ...
information_schema tables: SCHEMATA, TABLES, COLUMNS, STATISTICS, KEY_COLUMN_USAGE, VIEWS, ROUTINES, PARAMETERS, TRIGGERS, EVENTS, REFERENTIAL_CONSTRAINTS, PROCESSLIST, ENGINES, CHARACTER_SETS, COLLATIONS

Administration

KILL [QUERY] id
CHECK TABLE t [, t2 ...] [QUICK | MEDIUM | EXTENDED]: checks that every column holds every row; by default also that NOT NULL columns hold no NULL and no unique key repeats a value; EXTENDED also checks CHECK constraints
FLUSH TABLES (a checkpoint now), FLUSH LOGS
OPTIMIZE TABLE t, ... (compacts storage)
CHANGE REPLICATION SOURCE TO ..., START REPLICA, STOP REPLICA
SET GLOBAL max_connections = n

ASQL statements

Statement	See
`CREATE THING`, `SHOW THING(S)`, `DROP THING`, `EXPLAIN THING`, `WAIT THING`, `SET THING`, `PAUSE`/`RESUME`/`REFRESH THING`	Things
`WHAT IF <statements>`	History, previews and waiting
`CREATE CELLS`, `SHOW CELLS`, `DROP CELLS`	Cells
`CREATE SECRET`, `SHOW SECRETS`, `DROP SECRET`; `CREATE MAILER`, `SHOW MAILERS`, `DROP MAILER`	Cells
`CREATE USER ... TABLES ... RIGHTS ...`, `SHOW USERS`, `CAPABILITY MINT`, `CAPABILITY REVOKE`	Security
`SHOW ASQL MIRROR [table]`	Replication
`SHOW ASQL STORAGE [table]`	memory and encoding per table and column
`SHOW ASQL QUERIES`, `SHOW ASQL SLOW`, `SHOW ASQL ERRORS` `[SINCE n \| LIMIT n]`	recent statements
`SHOW ENGINE ASQL STATUS`	engine counters
`EXPORT SNAPSHOT 'name'`	the consistent copy `asql-export.sh` uses
`FLUSH MEMORY`	return free memory to the system
`DELETE FROM t MATCHING (v1, ...), (...)`	delete one row per tuple matching every column

Not supported

HANDLER, CHECKSUM TABLE, HELP, LOAD XML and temporary sequences. They return an error.