Essential SQL Commands: A Quick Reference Guide

I recently took some time to recap the SQL commands I use the most, and I decided to put together a few notes while I was at it. Nothing fancy—just a quick, practical list of the commands that come up often in my day-to-day work. Sharing them here in case they’re useful to anyone else who wants a simple reference or a refresher. Tip: If you're looking to dive deep into MySQL, I highly recommend the MySQL Crash Course by O'Reilly — it’s the one I took and found incredibly helpful. Query Filtering & Sorting Commands for filtering and sorting the results of a SELECT query. Command Description Example WHERE Filters the records based on a condition SELECT * FROM users WHERE age > 20; AND / OR Combines multiple conditions SELECT * FROM users WHERE age > 20 AND name = 'John'; ORDER BY Sorts the result set SELECT * FROM users ORDER BY age DESC; LIMIT Limits the number of rows returned SELECT * FROM users LIMIT 10; BETWEEN Filters results within a specific range SELECT * FROM users WHERE age BETWEEN 20 AND 30; IN Filters results based on a set of values SELECT * FROM users WHERE name IN ('John', 'Jane'); LIKE Searches for a pattern in a column SELECT * FROM users WHERE name LIKE 'J%'; The * wildcard in SQL selects all columns from a table without listing them individually. Returns all columns from the users table (e.g., id, name, email, age, etc.). SELECT * FROM users; In SQL, the LIKE operator is commonly used to search for a specified pattern in a column. It's especially handy when you want to filter results based on partial matches. Wildcard Description Example Matches % Matches zero or more characters name LIKE 'Jo%' John, Joanna, Joe _ Matches exactly one character name LIKE '_an' Dan, Jan, Pan Comparison Operators Used in WHERE clauses to compare values or test specific conditions in SQL queries. Operator Description Example = Equal to SELECT * FROM users WHERE age = 25; != or Not equal to SELECT * FROM users WHERE age != 25; > Greater than SELECT * FROM users WHERE age > 30; = Greater than or equal to SELECT * FROM users WHERE age >= 21;

Apr 18, 2025 - 21:43

Essential SQL Commands: A Quick Reference Guide

I recently took some time to recap the SQL commands I use the most, and I decided to put together a few notes while I was at it. Nothing fancy—just a quick, practical list of the commands that come up often in my day-to-day work. Sharing them here in case they’re useful to anyone else who wants a simple reference or a refresher.

Tip: If you're looking to dive deep into MySQL, I highly recommend the MySQL Crash Course by O'Reilly — it’s the one I took and found incredibly helpful.

Query Filtering & Sorting

Commands for filtering and sorting the results of a SELECT query.

Command	Description	Example
`WHERE`	Filters the records based on a condition	`SELECT * FROM users WHERE age > 20;`
`AND / OR`	Combines multiple conditions	`SELECT * FROM users WHERE age > 20 AND name = 'John';`
`ORDER BY`	Sorts the result set	`SELECT * FROM users ORDER BY age DESC;`
`LIMIT`	Limits the number of rows returned	`SELECT * FROM users LIMIT 10;`
`BETWEEN`	Filters results within a specific range	`SELECT * FROM users WHERE age BETWEEN 20 AND 30;`
`IN`	Filters results based on a set of values	`SELECT * FROM users WHERE name IN ('John', 'Jane');`
`LIKE`	Searches for a pattern in a column	`SELECT * FROM users WHERE name LIKE 'J%';`

The * wildcard in SQL selects all columns from a table without listing them individually.

Returns all columns from the users table (e.g., id, name, email, age, etc.).

SELECT * FROM users;

In SQL, the LIKE operator is commonly used to search for a specified pattern in a column. It's especially handy when you want to filter results based on partial matches.

Wildcard	Description	Example	Matches
`%`	Matches zero or more characters	`name LIKE 'Jo%'`	John, Joanna, Joe
`_`	Matches exactly one character	`name LIKE '_an'`	Dan, Jan, Pan

Comparison Operators

Used in WHERE clauses to compare values or test specific conditions in SQL queries.

Operator	Description	Example
`=`	Equal to	`SELECT * FROM users WHERE age = 25;`
`!=` or `<>`	Not equal to	`SELECT * FROM users WHERE age != 25;`
`>`	Greater than	`SELECT * FROM users WHERE age > 30;`
`<`	Less than	`SELECT * FROM users WHERE age < 18;`
`>=`	Greater than or equal to	`SELECT * FROM users WHERE age >= 21;`
`<=`	Less than or equal to	`SELECT * FROM users WHERE age <= 60;`
`IS`	Compares with a specific value	`SELECT * FROM users WHERE is_verified IS TRUE;`
`IS NULL`	A null value	`SELECT * FROM users WHERE phone IS NULL;`
`IS NOT NULL`	A non-null value	`SELECT * FROM users WHERE phone IS NOT NULL;`
`IN`	Matches a value in a list	`SELECT * FROM users WHERE name IN ('John', 'Jane');`
`NOT IN`	Doesn’t match a value in a list	`SELECT * FROM users WHERE age NOT IN (20, 30);`
`BETWEEN`	Within a range	`SELECT * FROM users WHERE age BETWEEN 20 AND 30;`
`NOT BETWEEN`	Not within a range	`SELECT * FROM users WHERE age NOT BETWEEN 10 AND 15;`
`LIKE`	Matches a pattern	`SELECT * FROM users WHERE name LIKE 'J%';`
`NOT LIKE`	Does not match a pattern	`SELECT * FROM users WHERE name NOT LIKE 'A%';`

MySQL Data Types (Quick Reference)

A concise guide to the different data types supported in MySQL, including numeric, string, date/time, and special types.

Data Type	Description	Example
`CHAR(n)`	Fixed-length string (exact number of characters)	`CHAR(3)` can store `IRE`, `GBR`
`VARCHAR(n)`	Variable-length string, can hold up to a specified number of characters	`VARCHAR(100)` can store "Hello, World!"
`TEXT`	Stores up to 65,535 characters (~64KB), typically used for large text	"Lorem ipsum dolor sit amet..."
`TINYTEXT`	Stores up to 255 characters	"Short text"
`MEDIUMTEXT`	Stores up to 16,777,215 characters (~16MB)
`LONGTEXT`	Stores up to 4,294,967,295 characters (~4GB), used for very large text	A very large article or file
`TINYINT`	Small integers (1 byte, range: -128 to 127), often used for booleans	`TINYINT` can store `1` for true or `0` for false
`SMALLINT`	Small integers (2 bytes)	`32767`
`MEDIUMINT`	Medium-sized integers (3 bytes)	`8388607`
`INT`	Standard integer (4 bytes)	`123456789`
`BIGINT`	Large integers (8 bytes)	`123456789012345`
`BOOLEAN`	Alias for `TINYINT(1)`, used for true/false values	`true` or `false`
`DECIMAL(m,d)`	Fixed-point number with exact precision, ideal for money (`m`, total digits - `d` digits after the decimal)	`DECIMAL(5,2)` can store `999.99`
`FLOAT`	Floating-point number with a decimal point that isn’t always in the same location	`1.234` or `12.34` or `123.4.`
`DATE`	Date only (`YYYY-MM-DD`)	`2025-04-14`
`TIME`	Time only (`HH:MM:SS`)	`12:30:00`
`DATETIME`	Date and time (`YYYY-MM-DD HH:MM:SS`)	`2025-04-14 12:30:00`
`TIMESTAMP`	Stores the current date and time	`2025-04-14 12:30:00`
`YEAR`	4-digit year format	`2025`
`JSON`	Stores JSON-formatted data (structured and queryable)	`{"name": "John", "age": 30}`
`ENUM`	A string object with a predefined list of allowed values.	`ENUM('active', 'inactive', 'suspended')` only accepts one of those values
`SET`	Similar to `ENUM`, but allows multiple values from the predefined list to be selected.	`SET('English','German','Spanish')` allows we to add any or all of the languages

This example demonstrates how various MySQL data types can be used when creating a user_profile table:

CREATE TABLE user_profile (
    user_id         INT AUTO_INCREMENT PRIMARY KEY,
    username        VARCHAR(50) NOT NULL,
    email           VARCHAR(100) UNIQUE NOT NULL,
    account_status  ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    preferences     JSON,
    is_verified     BOOLEAN DEFAULT FALSE,
    signup_date     DATETIME DEFAULT CURRENT_TIMESTAMP
);

Joins

Commands to select data from multiple tables.

Command	Description
`INNER JOIN` / `JOIN`	Returns records with matching values in both tables
`LEFT JOIN`	Returns all records from the left table, and matching records from the right table
`RIGHT JOIN`	Returns all records from the right table, and matching records from the left table
`FULL OUTER JOIN`	Returns all rows from one table and the matching rows from a second table

Example: Given two tables, one containing city names with their country codes and the other containing country names with their country codes, we can use SQL JOIN operations to select and combine data from both tables.

Cities table:

city_name	country_code
New York	US
Paris	FR
Tokyo	JP
Los Angeles	US
Sydney	AU
Dublin	IR

Countries table:

country_name	country_code
Ireland	IR
Japan	JP
Australia	AU
Italy	IT

Examples Using Different Joins:

SELECT c.city_name, co.country_name
FROM cities AS c
INNER JOIN countries AS co
ON c.country_code = co.country_code;

This uses c as the alias for cities and co for countries.

We can replace INNER JOIN in the query above with LEFT JOIN, RIGHT JOIN, or use a FULL OUTER JOIN (with a workaround in MySQL) to see different results based on the type of join.

INNER JOIN: Returns only the cities with a matching country_code in both tables.
Result: Tokyo/Japan, and Sydney/Australia and Dublin/Ireland.
LEFT JOIN: Returns all the rows from the left table (cities), and the matching rows from the right table (countries). If there is no match in the right table, the query will return NULL for the columns from the right table.
Result: All cities, but New York, Paris, and Los Angeles with NULL in the country_namecolumn.
RIGHT JOIN: Returns all rows from the right table (countries) and the matching rows from the left table (cities).
Result: Dublin, Tokyo and Sydney with their country names and NULL as the city_name for Italy.

Database Management Commands

These commands are used for creating, managing, and deleting databases and tables.

Command	Description	Example
`CREATE DATABASE`	Creates a new database	`CREATE DATABASE mydb;`
`USE`	Selects a database to use	`USE mydb;`
`DROP DATABASE`	Deletes a database	`DROP DATABASE mydb;`
`CREATE TABLE`	Creates a new table	`CREATE TABLE users (id INT, name VARCHAR(100));`
`DROP TABLE`	Deletes a table	`DROP TABLE users;`
`ALTER TABLE`	Modifies an existing table	`ALTER TABLE users ADD COLUMN age INT;`
`SHOW TABLES`	Displays a list of all tables in the current database	`SHOW TABLES;`
`DESCRIBE/EXPLAIN`	Displays structure of a table (columns, types, etc.)	`DESCRIBE users;`

Data Manipulation Commands

Commands to insert, update, delete, and select data in tables.

Command	Description	Example
`INSERT INTO`	Inserts new rows into a table	`INSERT INTO users (name, age) VALUES ('John', 25);`
`SELECT`	Retrieves data from a table	`SELECT * FROM users;`
`UPDATE`	Updates existing rows in a table	`UPDATE users SET age = 26 WHERE id = 1;`
`DELETE`	Deletes rows from a table	`DELETE FROM users WHERE id = 1;`
`TRUNCATE TABLE`	Deletes all rows in a table (without logging individual row deletions)	`TRUNCATE TABLE users;`

Inserting multiple rows at once:

INSERT INTO users (name, age)
VALUES
  ('John', 25),
  ('Jane', 30),
  ('Bob', 22),
  ('Alice', 28);

Aggregate Functions

Functions that return aggregated values such as sums, averages, counts, etc.

Command	Description	Example
`COUNT()`	Returns the number of rows that match a query	`SELECT COUNT(*) FROM users WHERE age > 20;`
`SUM()`	Returns the sum of a numeric column	`SELECT SUM(age) FROM users;`
`AVG()`	Returns the average value of a numeric column	`SELECT AVG(age) FROM users;`
`MIN()`	Returns the minimum value of a column	`SELECT MIN(age) FROM users;`
`MAX()`	Returns the maximum value of a column	`SELECT MAX(age) FROM users;`

Transactions

Commands to manage database transactions and maintain data integrity.

Command	Description	Example
`START TRANSACTION`	Begins a new transaction	`START TRANSACTION;`
`COMMIT`	Commits the current transaction	`COMMIT;`
`ROLLBACK`	Rolls back the current transaction	`ROLLBACK;`

Here’s a simple example demonstrating how transactions work:

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- If everything is fine, then run:
-- COMMIT;

-- If something goes wrong, then run:
-- ROLLBACK;

Explanation: This example simulates a money transfer between two users. The START TRANSACTION begins a transaction block. If both UPDATE statements succeed, COMMIT saves the changes. If an error occurs (e.g., user_id 2 doesn’t exist), ROLLBACK undoes both updates to maintain data integrity.

Conclusion

SQL is an incredibly powerful tool for interacting with databases, and understanding the essential commands is key to writing efficient and effective queries. By revisiting and practicing these commonly used commands, such as those for filtering, sorting, joining tables, and managing transactions, we can significantly improve our workflow and avoid common pitfalls.