MENU


C. MySQL Statement, Function and Operator Reference

The SQL Chapter 3 runs on MySQL 9.7.2 LTS in its default strict sql_mode, grouped by what each statement does. Keywords are case-insensitive and statements end with ;; inside a stored routine, switch the client's delimiter first with DELIMITER // (Section 3.15.1). Statement Purpose Note § CREATE DATABASE New schema IF NOT EXISTS 3.2.1 CREATE TABLE Define a table LIKE, AS SELECT 3.4.1 ALTER TABLE Change a table ALGORITHM=INSTANT 3.4.6 RENAME TABLE Atomic rename or swap 3.4.7 TRUNCATE TABLE Empty, reset counter Implicit commit 3.2.5 CREATE INDEX Add an index ((expr)) functional 3.11.3 CREATE VIEW Named SELECT WITH CHECK OPTION 3.15.7 CREATE PROCEDURE Routine run by CALL SQL SECURITY DEFINER 3.15.1 CREATE FUNCTION Routine in expressions DETERMINISTIC 3.15.2 CREATE TRIGGER Per-row reaction BEFORE, AFTER 3.15.5 CREATE EVENT Scheduled statement EVERY 1 DAY 3.15.6 INSERT Add rows Multi-row, IGNORE 3.2.2 ON DUPLICATE KEY Upsert Row alias AS new 3.2.6 UPDATE, DELETE Change, remove rows --safe-updates 3.2.4 REPLACE Delete, then insert Fires delete triggers 3.2.5 LEFT JOIN Keep unmatched rows Filter in ON 3.5.7 UNION, EXCEPT Set operators ALL keeps duplicates 3.5.10 WITH RECURSIVE CTE, tree walks Stops at depth 1000 3.6.8 LOAD DATA INFILE Bulk load a file secure_file_priv 3.17.6 Table C.1: Definition (DDL) and data (DML) statements Statement Purpose Note § START TRANSACTION Open a transaction An error keeps it open 3.14.1 SAVEPOINT Partial rollback ROLLBACK TO 3.14.2 ISOLATION LEVEL Set per session REPEATABLE READ default 3.14.4 FOR UPDATE Locking read NOWAIT, SKIP LOCKED 3.14.6 CREATE USER New account 'app'@'localhost' 3.16.2 ALTER USER Password, lock ACCOUNT UNLOCK 3.16.3 GRANT, REVOKE Privileges No FLUSH needed 3.16.4 CREATE ROLE Named privilege set SET DEFAULT ROLE 3.16.6 PREPARE Placeholder statement EXECUTE ... USING 3.16.8 EXPLAIN Show the plan ANALYZE, FORMAT=JSON 3.12.5 ANALYZE TABLE Refresh statistics UPDATE HISTOGRAM 3.17.2 SET PERSIST Survives a restart RESET PERSIST 3.1.8 KILL End a session KILL QUERY 3.17.3 SHOW REPLICA STATUS Replication health Was SLAVE 3.17.8 Table C.2: Transaction (TCL), account (DCL) and administration statements SHOW CREATE TABLE prints what the server stored, and DESCRIBE a table's columns (Section 3.17.1). MySQL 9.7 prints EXPLAIN as a tree unless you ask for FORMAT=TRADITIONAL. Family Functions the chapter uses § String CONCAT_WS, CHAR_LENGTH, SUBSTRING_INDEX, LPAD, FIELD 3.7.5 Regular expression REGEXP_LIKE, REGEXP_SUBSTR, REGEXP_REPLACE 3.7.4 Numeric, conversion ROUND, TRUNCATE, FLOOR, FORMAT, CAST 3.7.6 Date and time NOW, DATE_FORMAT, TIMESTAMPDIFF, YEARWEEK 3.7.7 NULL and flow COALESCE, IFNULL, NULLIF, GREATEST, IF 3.7.8 Aggregate COUNT, SUM, AVG, GROUP_CONCAT, JSON_ARRAYAGG 3.8.1 Window ROW_NUMBER, RANK, DENSE_RANK, LAG, NTILE 3.8.5 JSON ->>, JSON_SET, JSON_CONTAINS, MEMBER OF, JSON_TABLE 3.9.2 Full-text, spatial MATCH ... AGAINST, ST_Distance_Sphere, ST_AsGeoJSON 3.9.7 Hashing, ids SHA2, AES_ENCRYPT, UUID_TO_BIN, LAST_INSERT_ID 3.7.9 Table C.3: Built-in functions by family Warning: MD5(), SHA1() and SHA() were deprecated in 9.4.0 and moved out of the server in 9.6.0, so on 9.7 they fail with ERROR 1305 unless an administrator runs INSTALL COMPONENT 'file://component_classic_hashing'. PASSWORD() went in 8.0.11, ENCRYPT() and DES_ENCRYPT() in 8.0.3, and the mysql_native_password plugin in 9.0. Still accepted with warning 1287: VALUES(col) in an upsert, &&, ||, !, SQL_CALC_FOUND_ROWS and JSON_MERGE(). Operators, tightest first Meaning § BINARY, COLLATE Binary cast, named collation 3.7.1 !, unary -, ~ Negation, bitwise NOT 3.7.1 ^ Bitwise XOR 3.7.1 *, /, DIV, %, MOD Multiply, divide, remainder 3.7.1 +, - Add, subtract 3.7.1 <<, >>, then &, then | Shifts, bitwise AND, OR 3.7.1 =, <=>, <, <>, IS, LIKE, IN Comparison; <=> NULL-safe 3.7.2 BETWEEN, CASE Inclusive range, conditional 3.7.2 NOT, then AND, then XOR, then OR AND binds tighter than OR 3.5.3 :=, = Assignment, = only in SET 3.7.1 Table C.4: Operator precedence