In the world of database management, MySQL stands out as one of the most popular and powerful relational database systems. Whether you’re a seasoned database administrator or just starting your journey in the world of databases, having a reliable cheat sheet at your disposal can be invaluable. This comprehensive MySQL cheat sheet is designed to be your go-to resource for quickly accessing essential commands and tips to streamline your database operations.
Feel free to bookmark this page or share it with your peers. Dive in and make the most of this MySQL cheat sheet!
Download Options:
Table of Contents
- What is MySQL?
- Sample Data
- Querying tables
- Filtering Data
- Filtering on numeric columns
- Filtering on text columns
- Filtering on multiple columns
- Filtering on missing data
- Aggregating Data
- Simple aggregations
- Grouping, filtering, and sorting
- MySQL-Specific Syntax
What is MySQL?
MySQL is an open-source relational database management system (RDBMS) known for its fast performance and reliability. Developed by Oracle Corporation, it’s widely used for web applications and online publishing.
Sample Data
The dataset contains details of the world’s highest valued media employees by gross sales. Each row contains one department, and the table is named employees.
Querying tables
- Get all the columns from a table using SELECT *
SELECT * FROM employees
- Get a column from a table by name using SELECT col
SELECT department FROM employees
- Get multiple columns from a table by name using SELECT col1, col2
SELECT department, inception_year FROM employees
- Override column names with SELECT col AS new_name
SELECT department, inception_year AS creation_year FROM employees
- Arrange the rows in ascending order of values in a column with ORDER BY col
SELECT department, inception_year FROM employees ORDER BY inception_year
- Arrange the rows in descending order of values in a column with ORDER BY col DESC
SELECT department, incentive FROM employees ORDER BY incentive DESC
- Limit the number of rows returned with LIMIT n
SELECT * FROM employees LIMIT 2
- Get unique values with SELECT DISTINCT
SELECT DISTINCT client_name FROM employees
Filtering Data
Filtering on numeric columns
- Get rows where a number is greater than a value with WHERE col > n
SELECT department, inception_year FROM employees WHERE inception_year > 1928
- Get rows where a number is greater than or equal to a value with WHERE col >= n
SELECT department, inception_year FROM employees WHERE inception_year >= 1928
- Get rows where a number is less than a value with WHERE col < n
SELECT department, inception_year FROM employees WHERE inception_year < 1977
- Get rows where a number is less than or equal to a value with WHERE col <= n
SELECT department, inception_year FROM employees WHERE inception_year <= 1977
- Get rows where a number is equal to a value with WHERE col = n
SELECT department, inception_year FROM employees WHERE inception_year = 1996
- Get rows where a number is not equal to a value with WHERE col <> n or WHERE col != n
SELECT department, inception_year FROM employees WHERE inception_year <> 1996
- Get rows where a number is between two values (inclusive) with WHERE col BETWEEN m AND n
SELECT department, inception_year FROM employees WHERE inception_year BETWEEN 1928 AND 1977
Filtering on text columns
- Get rows where text is equal to a value with WHERE col = ‘x’
SELECT department, original_medium FROM employees WHERE original_medium = ‘book‘
- Get rows where text is one of several values with WHERE col IN (‘x’, ‘y’)
SELECT department, original_medium FROM employees WHERE original_medium IN (‘movie‘, ‘video game‘)
- Get rows where text contains specific letters with WHERE col LIKE ‘%abc%’ (% represents any characters)
SELECT department, original_medium FROM employees WHERE original_medium LIKE ‘%oo%‘
Filtering on multiple columns
- Get the rows where one condition and another condition holds with WHERE condn1 AND condn2
SELECT department, inception_year, insentive FROM employees WHERE inception_year < 1950 AND insentive > 50
- Get the rows where one condition or another condition holds with WHERE condn1 OR condn2
SELECT department, inception_year, insentive FROM employees WHERE inception_year < 1950 OR insentive > 50
Filtering on missing data
- Get rows where values are missing with WHERE col IS NULL
SELECT department, sales FROM employees WHERE sales IS NULL
- Get rows where values are not missing with WHERE col IS NOT NULL
SELECT department, sales FROM employees WHERE sales IS NOT NULL
Aggregating Data
Simple aggregations
- Get the total number of rows SELECT COUNT(*)
SELECT COUNT(*) FROM employees
- Get the total value of a column with SELECT SUM(col)
SELECT SUM(insentive) FROM employees
- Get the mean value of a column with SELECT AVG(col)
SELECT AVG(insentive) FROM employees
- Get the mean value of a column with SELECT AVG(col)
SELECT AVG(insentive) FROM employees
- Get the maximum value of a column with SELECT MAX(col)
SELECT MAX(insentive) FROM employees
Grouping, filtering, and sorting
- Get summaries grouped by values with GROUP BY col
SELECT client_name, COUNT(*) FROM employees GROUP BY client_name
- Get summaries grouped by values, in order of summaries with GROUP BY col ORDER BY smmry DESC
SELECT original_medium, SUM(sales) AS total_products FROM employees GROUP BY original_medium ORDER BY total_products DESC
- Get rows where values in a group meet a criterion with GROUP BY col HAVING condn
SELECT original_medium, SUM(sales) AS total_products FROM employees GROUP BY original_medium ORDER BY total_products DESC HAVING total_products > 10
- Filter before and after grouping with WHERE condn_before GROUP BY col HAVING condn_after
SELECT original_medium, SUM(sales) AS total_products FROM employees WHERE client_name = ‘UG Tech Holdings’ GROUP BY original_medium ORDER BY total_products DESC HAVING total_products > 10
MySQL-Specific Syntax
Not all code works in every dialect of SQL. The following examples work in MySQL, but are not guaranteed to work in other dialects.
- Limit the number of rows returned, offset from the top with LIMIT m, n
SELECT * FROM employees LIMIT 2, 3
- By default, MySQL uses case insensitive matching in WHERE clauses.
SELECT * FROM employees WHERE client_name = ‘UG Tech Holdings‘
- To get case-sensitive matching, use WHERE BINARY condn
SELECT * FROM employees WHERE BINARY client_name = ‘UG Tech Holdings‘
- Get the current date with CURDATE() and the current datetime with NOW() or CURTIME()
SELECT NOW(), CURDATE(), CURTIME()
Conclusion
In conclusion, MySQL is a widely used and powerful relational database management system known for its performance and reliability. This comprehensive MySQL cheat sheet provides essential commands and tips for database administrators and beginners alike. It covers various aspects of MySQL usage, from querying and filtering data to aggregating and sorting information. Additionally, it offers insights into MySQL-specific syntax, ensuring you can leverage the full potential of this database system.
