SQL Cheat Sheet

This cheat sheet provides a quick reference for essential SQL commands, categorized by their function. It's designed for developers and database administrators to quickly look up syntax and usage.

Data Definition Language (DDL) Commands

DDL statements are used to define and manage database structures.

Command	Description
CREATE DATABASE <db>	Create a new database.
DROP DATABASE <db>	Delete a database.
CREATE TABLE <table> (<column_1> <column_1_type>, <column_2> <column_2_type>)	Create a new table with specified columns and data types.
DROP TABLE <table>	Delete a table and all its data.
ALTER TABLE <table> ADD COLUMN <column> <column_type>	Add a new column to an existing table.
ALTER TABLE <table> DROP COLUMN <column>	Remove a column from a table.
ALTER TABLE <table> RENAME COLUMN <column> TO <new_column>	Rename a column in a table.
ALTER TABLE <table> RENAME TO <new_table_name>	Rename a table.
TRUNCATE TABLE <table>	Remove all rows from a table quickly, often resetting auto-increment counters.
CREATE INDEX INDEXNAME ON <table> (<column>)	Create an index on a table to speed up data retrieval.
DROP INDEX INDEXNAME ON <table>	Remove an index from a table.

Data Manipulation Language (DML) Commands

DML statements are used to manage data within database objects.

Command	Description
INSERT INTO <table> (<column_1>, <column_2>) VALUES (<value_1>, <value_2>)	Insert a new row with specified values into a table.
SELECT * FROM <table>	Retrieve all rows and columns from a table.
UPDATE <table> SET <column_1> = <value_1> WHERE <condition>	Update existing rows in a table that meet a specified condition.
DELETE FROM <table> WHERE <condition>	Delete rows from a table that meet a specified condition.

Data Query Language (DQL) Commands

DQL is primarily used for retrieving data from the database. The most common DQL command is SELECT.

Command	Description
SELECT <column_1>, <column_2> FROM <table>	Retrieve specific columns from a table.
SELECT * FROM <table> WHERE <column> = VALUE	Retrieve rows that match a specific condition.
SELECT * FROM <table> ORDER BY <column> [ASC|DESC]	Retrieve rows sorted by a column in ascending (ASC) or descending (DESC) order.
SELECT * FROM <table> LIMIT N	Retrieve the first N rows from a table.
SELECT * FROM <table> OFFSET N	Retrieve rows starting from the Nth row, often used with LIMIT for pagination.
SELECT * FROM <table_1> JOIN <table_2> ON <table_1>.<column_1> = <table_2>.<column_2>	Retrieve rows by joining two tables based on a matching column.
SELECT * FROM <table_1> UNION SELECT * FROM <table_2>	Combine the result sets of two SELECT statements, removing duplicate rows. Use UNION ALL to include duplicates.
SELECT <column>, COUNT(*) FROM <table> GROUP BY <column>	Group rows that have the same value in a specified column and perform aggregate functions (like COUNT).
SELECT <column>, COUNT(*) FROM <table> GROUP BY <column> HAVING COUNT(*) > N	Filter grouped rows based on a condition applied to the aggregate function.
SELECT * FROM <table> WHERE <column> IN (<value_1>, <value_2>)	Retrieve rows where a column's value matches any value in a specified list.
SELECT * FROM <table> WHERE <column> BETWEEN <value_1> AND <value_2>	Retrieve rows where a column's value falls within a specified range (inclusive).
SELECT * FROM <table> WHERE <column> LIKE '<pattern>%'	Retrieve rows where a column's value matches a specified pattern (e.g., '%' for any sequence of characters, '_' for a single character).
SELECT * FROM <table> WHERE <column> IS NULL	Retrieve rows where a column's value is NULL.
SELECT * FROM <table> WHERE <column> IS NOT NULL	Retrieve rows where a column's value is not NULL.

Data Control Language (DCL) Commands

DCL commands are used to manage user access and permissions within the database.

Command	Description
GRANT PERMISSIONS ON <object> TO <user>	Grant specific privileges (e.g., SELECT, INSERT) on a database object to a user.
REVOKE PERMISSIONS ON <object> FROM <user>	Revoke previously granted privileges from a user.

Transaction Control Language (TCL) Commands

TCL commands manage transactions, ensuring data integrity.

Command	Description
BEGIN TRANSACTION	Start a new transaction.
COMMIT	Save all changes made within the current transaction to the database.
ROLLBACK	Discard all changes made within the current transaction.
SAVEPOINT <savepoint_name>	Create a named savepoint within a transaction, allowing partial rollbacks.
ROLLBACK TO SAVEPOINT <savepoint_name>	Rollback the transaction to a specific savepoint.
RELEASE SAVEPOINT <savepoint_name>	Remove a savepoint from a transaction.

System and User Management Commands

These commands are used for database administration and user management.

Command	Description
SHOW DATABASES	List all available databases on the server.
USE <db>	Select a database to work with.
SHOW TABLES	List all tables in the current database.
SHOW COLUMNS FROM <table>	List all columns and their properties for a specific table.
SHOW INDEXES FROM <table>	List all indexes defined on a table.
SHOW CREATE TABLE <table>	Display the SQL statement used to create a specific table.
DESCRIBE <table>	Show the structure (columns, data types, constraints) of a table.
EXPLAIN SELECT ...	Show the execution plan for a SELECT query, helping to optimize performance.
SET autocommit = 0	Disable automatic committing of transactions.
SET autocommit = 1	Enable automatic committing of transactions.
SET FOREIGN_KEY_CHECKS = 0	Temporarily disable foreign key constraint checks.
CREATE USER '<username>'@'<host>' IDENTIFIED BY '<password>'	Create a new database user account.
DROP USER '<username>'@'<host>'	Delete a database user account.
ALTER USER '<username>'@'<host>' IDENTIFIED BY '<new_password>'	Change the password for an existing user.
GRANT ALL PRIVILEGES ON <database>.* TO '<username>'@'<host>'	Grant all possible privileges on a specific database to a user.
REVOKE ALL PRIVILEGES ON <database>.* FROM '<username>'@'<host>'	Revoke all privileges on a specific database from a user.
FLUSH PRIVILEGES	Reload the grant tables, making privilege changes effective immediately.

This SQL cheat sheet covers the fundamental commands for managing databases, manipulating data, querying information, controlling access, and handling transactions. Mastering these commands is crucial for anyone working with relational databases.