MySQL - Statements Reference

MySQL - Modern Statements Reference

This guide provides a comprehensive reference to essential MySQL statements, categorized for clarity. Each category is explained with its purpose, followed by a list of key statements and their modern usage. This reference focuses on practices for MySQL 8.0 and later.

Data Definition Language (DDL) Statements

DDL statements are used to define, modify, and remove database structures like tables, indexes, and views. These operations are typically permanent and auto-committed.

• CREATE DATABASE: Establishes a new database schema.
• ALTER DATABASE: Modifies the global characteristics of a database, such as its default character set and collation.
• DROP DATABASE: Permanently deletes an entire database, including all its tables and data. Use with extreme caution.
• CREATE TABLE: Defines a new table with its columns, data types, and constraints (e.g., PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE).
• ALTER TABLE: Modifies an existing table structure. Common uses include adding, dropping, or modifying columns, adding constraints, or renaming the table.
• DROP TABLE: Permanently deletes a table and all its data.
• TRUNCATE TABLE: Quickly deletes all rows from a table. It’s faster than DELETE for large tables and resets any AUTO_INCREMENT counters. This is a DDL operation and cannot be easily rolled back.
• CREATE INDEX: Creates an index on one or more columns to speed up query performance.
• DROP INDEX: Removes an index from a table.
• CREATE VIEW: Creates a virtual table based on the result-set of an SQL statement.
• ALTER VIEW: Modifies the definition of an existing view.
• DROP VIEW: Deletes a view.

Data Manipulation Language (DML) Statements

DML statements are used to manage data within schema objects. These are the most common SQL operations for application developers.

• SELECT: Retrieves data from one or more tables. The cornerstone of data querying.
• INSERT: Adds new rows of data into a table.
• UPDATE: Modifies existing data in a table.
• DELETE: Removes rows from a table.
• CALL: Executes a stored procedure.
• LOAD DATA INFILE: A high-performance command to bulk-load data from a text file into a table.
• WITH (Common Table Expressions - CTEs): Creates a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. Essential for breaking down complex queries.
• VALUES: Generates a row or set of rows. Useful with INSERT for multi-row inserts or as a standalone statement.

Transaction Control Language (TCL) Statements

TCL statements are used to manage transactions, ensuring data integrity by grouping a set of operations into a single logical unit. These are fundamental to ACID compliance.

• START TRANSACTION: Begins a new transaction block.
• COMMIT: Saves all changes made during the current transaction, making them permanent.
• ROLLBACK: Discards all changes made during the current transaction, reverting the database to its state before the transaction began.
• SAVEPOINT: Sets a named marker within a transaction to which you can later roll back.
• ROLLBACK TO SAVEPOINT: Rolls back a transaction to a specific savepoint without aborting the entire transaction.
• RELEASE SAVEPOINT: Removes a named savepoint.
• SET TRANSACTION: Sets characteristics for the current transaction, such as isolation level.

Data Control Language (DCL) Statements

DCL statements are used for managing user access and permissions within the database.

• CREATE USER: Creates a new user account for accessing the server.
• ALTER USER: Modifies an existing user account, commonly used to change passwords or authentication settings.
• DROP USER: Removes a user account.
• GRANT: Gives specific privileges (e.g., SELECT, INSERT) on database objects to a user or role.
• REVOKE: Removes privileges that were previously granted to a user or role.
• CREATE ROLE / DROP ROLE: Manages roles, which are named collections of privileges, simplifying permission management.
• SET ROLE: Activates roles for the current session.

Stored Program and Flow Control Statements

These statements are used within stored procedures, functions, and triggers to implement procedural logic on the database server.

• CREATE PROCEDURE / FUNCTION / TRIGGER / EVENT: Defines stored programs that encapsulate business logic.
• ALTER PROCEDURE / FUNCTION …: Modifies the characteristics of a stored program.
• DROP PROCEDURE / FUNCTION …: Deletes a stored program.
• DECLARE: Declares local variables, cursors, or conditions within a stored program block.
• SET: Assigns a value to a variable.
• IF … THEN … ELSEIF … ELSE … END IF: Conditional execution of statements.
• CASE: A more complex conditional statement, similar to a switch statement in other languages.
• LOOP, WHILE, REPEAT: Statements for creating loops to iterate over a block of code.
• LEAVE: Exits a labeled loop or program block.
• ITERATE: Skips the rest of the current loop iteration and begins the next one.
• CURSOR (DECLARE, OPEN, FETCH, CLOSE): A mechanism to iterate through a result set row by row.

Administrative & Utility Statements

These statements are used for server maintenance, diagnostics, and information retrieval.

• SHOW [DATABASES, TABLES, COLUMNS, STATUS, VARIABLES, PROCESSLIST]: Displays information about the server, databases, tables, and current operations.
• DESCRIBE / EXPLAIN: DESCRIBE (or DESC) shows a table’s structure. EXPLAIN provides the query execution plan, which is crucial for performance tuning.
• ANALYZE TABLE / OPTIMIZE TABLE: Maintenance commands to update index statistics and reclaim unused space.
• FLUSH: Clears various internal caches (e.g., FLUSH PRIVILEGES, FLUSH TABLES). Note: Modern DCL statements often make FLUSH PRIVILEGES unnecessary.
• KILL: Terminates a specific connection thread/process.
• USE: Sets the default database for subsequent queries in the current session.