MySQL Cheat Sheet

SELECT

-- get all columns
SELECT * FROM table;
SELECT * FROM table1, table2;

-- get specific columns by name
SELECT field1, field2 FROM table1, table2;
SELECT field1, field2 FROM table1;

-- override column names in the output
SELECT field1 AS alias1, field2 AS alias2 FROM table1;

-- filtering data using WHERE clause = condition
SELECT ... FROM ... WHERE condition
SELECT ... FROM ... WHERE condition GROUP BY field;
SELECT ... FROM ... WHERE condition GROUP BY field HAVING condition2;

-- arrange rows in order
SELECT ... FROM ... WHERE condition ORDER BY field1, field2; -- ascending order - default
SELECT ... FROM ... WHERE condition ORDER BY field1, field2 DESC; -- descending order

-- limit the number of rows returned
SELECT ... FROM table_name WHERE condition LIMIT 2; -- first 2 rows
SELECT ... FROM ... WHERE condition LIMIT 10;

-- get unique values from a column
SELECT DISTINCT field1 FROM table;
SELECT DISTINCT field1 FROM ...
SELECT DISTINCT field1, field2 FROM ...

Filtering Data - WHERE Clause

```sql filename="filtering_numeric_columns.sql" linenums="1" -- filtering on numeric columns SELECT * FROM table_name WHERE column_name > value; -- greater than SELECT * FROM table_name WHERE column_name >= value; -- greater than or equal to SELECT * FROM table_name WHERE column_name <= value; -- less than or equal to SELECT * FROM table_name WHERE column_name = value; -- equal to SeLECT * FROM table_name WHERE column_name != value; -- not equal to SELECT * FROM table_name WHERE column_name <> value; -- not equal to SELECT * FROM table_name WHERE column_name BETWEEN value AND value; -- between two values

```sql filename="filtering_text_columns.sql" linenums="1"
-- equal to a specific value
SELECT * FROM table_name WHERE column_name = 'value';

-- one of several values (IN)
SELECT * FROM table_name WHERE column_name IN ('value1', 'value2', 'value3');

-- not one of several values (NOT IN)
SELECT * FROM table_name WHERE column_name NOT IN ('value1', 'value2', 'value3');

-- pattern matching (LIKE, case-insensitive by default)
SELECT * FROM table_name WHERE column_name LIKE 'pattern'; -- % = any number of characters, _ = one character
SELECT * FROM table_name WHERE column_name LIKE 'pattern%'; -- starts with pattern
SELECT * FROM table_name WHERE column_name LIKE '%pattern'; -- ends with pattern
SELECT * FROM table_name WHERE column_name LIKE '%pattern%'; -- contains pattern

Logical Operators

```sql filename="filtering_logical_operators.sql" linenums="1" -- matching multiple conditions (AND) SELECT * FROM table_name WHERE condition1 AND condition2;

-- matching alternative conditions (OR) SELECT * FROM table_name WHERE condition1 OR condition2;

### Filtering Data - `NULL` Values

```sql filename="filtering_null_values.sql" linenums="1"
-- missing values (IS NULL)
SELECT * FROM table_name WHERE column_name IS NULL;

-- not missing values (IS NOT NULL)
SELECT * FROM table_name WHERE column_name IS NOT NULL;

Aggregating Data

```sql filename="aggregate_functions.sql" linenums="1" -- get the total number of rows in the table SELECT COUNT(*) FROM table_name;

-- get the total value of a column SELECT SUM(field_name) FROM table_name;

-- get the mean value of a column SELECT AVG(field_name) FROM table_name;

-- get the minimum value of a column SELECT MIN(field_name) FROM table_name;

-- get the maximum value of a column SELECT MAX(field_name) FROM table_name;

### Grouping and Aggregation

```sql filename="grouping_aggregating_data.sql" linenums="1"
-- Group and summarize data by values in a column
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;

-- Group and aggregate with ordering
-- group summaries and order results by the summary value
SELECT column_name, AGG_FUNC(another_column) AS alias_name
FROM table_name
GROUP BY column_name
ORDER BY alias_name DESC;

-- Group and filter using HAVING (after aggregation)
SELECT column_name, AGG_FUNC(another_column) AS alias_name
FROM table_name
GROUP BY column_name
HAVING alias_name > some_value;

-- Combine WHERE and HAVING (filter rows before and after grouping)
SELECT column_name, AGG_FUNC(another_column) AS alias_name
FROM table_name
WHERE condition
GROUP BY column_name
HAVING alias_name > some_value;

Limiting Results

```sql filename="limiting_results.sql" linenums="1" -- Limit the number of rows returned and offset from the top SELECT * FROM table_name LIMIT offset, row_count; -- Example: LIMIT 5, 10 -- skip 5, then return next 10 -- Example: LIMIT 10 -- return first 10 rows SELECT * FROM table_name LIMIT 5; -- first 5 rows SELECT * FROM table_name LIMIT 5, 10; -- skip 5 rows, return next 10

### MySQL - Specific Syntax

```sql filename="mysql_specific_syntax.sql" linenums="1"
-- default - case-insensitive matching in WHERE clauses
SELECT * FROM table_name WHERE column_name = 'VALUE';

-- case-sensitive matching (MySQL specific)
SELECT * FROM table_name WHERE BINARY column_name = 'VALUE';

-- get the current date
SELECT CURDATE();

-- get the current datetime - 2 options
SELECT NOW();
SELECT CURTIME();

-- list available tables
SHOW TABLES;

Browse

SHOW DATABASES;
SHOW TABLES;
SHOW FIELDS FROM table / DESCRIBE table;
SHOW CREATE TABLE table;
SHOW PROCESSLIST;
KILL process_number;

Final Tip

sql filename="combining_clauses.sql" linenums="1" -- structure when combining clauses SELECT columns FROM table WHERE row-level condition GROUP BY grouping_column HAVING group-level condition ORDER BY column LIMIT offset, row_count;