Skip to main content
HomeCheat sheetsSQL

MySQL Basics Cheat Sheet

With this MySQL basics cheat sheet, you'll have a handy reference guide to basic querying tables, filtering data, and aggregating data
Oct 2023  · 6 min read

Have this cheat sheet at your fingertips

Download PDF

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 franchises by gross revenue. Each row contains one franchise, and the table is named franchises.

franchise

inception_year

total_revenue_busd

original_medium

owner

n_movies

Star Wars

1977

46.7

movie

The Walt Disney Company

12

Mickey Mouse and Friends

1928

52.2

cartoon

The Walt Disney Company

 

Anpanman

1973

38.4

book

Froebel-kan

33

Winnie the Pooh

1924

48.5

book

The Walt Disney Company

6

Pokémon

1996

88

video game

The Pokémon Company

24

Disney Princess

2000

45.4

movie

The Walt Disney Company

 

Querying tables

Get all the columns from a table using SELECT *

SELECT *
	FROM franchises

Get a column from a table by name using SELECT col

SELECT franchise
	FROM franchises

Get multiple columns from a table by name using SELECT col1, col2

SELECT franchise, inception_year
	FROM franchises

Override column names with SELECT col AS new_name

SELECT franchise, inception_year AS creation_year
FROM franchises

Arrange the rows in ascending order of values in a column with ORDER BY col

SELECT franchise, inception_year
	FROM franchises
	ORDER BY inception_year

Arrange the rows in descending order of values in a column with ORDER BY col DESC

SELECT franchise, total_revenue_busd
	FROM franchises
	ORDER BY total_revenue_busd DESC

Limit the number of rows returned with LIMIT n

SELECT *
	FROM franchises
    LIMIT 2

Get unique values with SELECT DISTINCT

SELECT DISTINCT owner
	FROM franchises

Filtering Data

Filtering on numeric columns

Get rows where a number is greater than a value with WHERE col > n

SELECT franchise, inception_year
	FROM franchises
	WHERE inception_year > 1928

Get rows where a number is greater than or equal to a value with WHERE col >= n

SELECT franchise, inception_year
	FROM franchises
	WHERE inception_year >= 1928

Get rows where a number is less than a value with WHERE col < n

SELECT franchise, inception_year
	FROM franchises
	WHERE inception_year < 1977

Get rows where a number is less than or equal to a value with WHERE col <= n

SELECT franchise, inception_year
	FROM franchises
	WHERE inception_year <= 1977

Get rows where a number is equal to a value with WHERE col = n

SELECT franchise, inception_year
	FROM franchises
	WHERE inception_year = 1996

Get rows where a number is not equal to a value with WHERE col <> n or WHERE col != n

SELECT franchise, inception_year
	FROM franchises
	WHERE inception_year <> 1996

Get rows where a number is between two values (inclusive) with WHERE col BETWEEN m AND n

SELECT franchise, inception_year
	FROM franchises
	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 franchise, original_medium
	FROM franchises
	WHERE original_medium = 'book' 

Get rows where text is one of several values with WHERE col IN ('x', 'y')

SELECT franchise, original_medium
	FROM franchises
	WHERE original_medium IN ('movie', 'video game')

Get rows where text contains specific letters with WHERE col LIKE '%abc%' (% represents any characters)

SELECT franchise, original_medium
	FROM franchises
	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 franchise, inception_year, total_revenue_busd
	FROM franchises
	WHERE inception_year < 1950
		AND total_revenue_busd > 50

Get the rows where one condition or another condition holds with WHERE condn1 OR condn2

SELECT franchise, inception_year, total_revenue_busd
	FROM franchises
	WHERE inception_year < 1950
		OR total_revenue_busd > 50

Filtering on missing data

Get rows where values are missing with WHERE col IS NULL

SELECT franchise, n_movies
	FROM franchises
	WHERE n_movies IS NULL

Get rows where values are not missing with WHERE col IS NOT NULL

SELECT franchise, n_movies
	FROM franchises
	WHERE n_movies IS NOT NULL

Aggregating Data

Simple aggregations

Get the total number of rows SELECT COUNT(*)

SELECT COUNT(*)
	FROM franchises

Get the total value of a column with SELECT SUM(col)

SELECT SUM(total_revenue_busd)
	FROM franchises

Get the mean value of a column with SELECT AVG(col)

SELECT AVG(total_revenue_busd)
	FROM franchises

Get the maximum value of a column with SELECT MAX(col)

SELECT MAX(total_revenue_busd)
	FROM franchises

Grouping, filtering, and sorting

Get summaries grouped by values with GROUP BY col

SELECT owner, COUNT(*)
	FROM franchises
	GROUP BY owner

Get summaries grouped by values, in order of summaries with GROUP BY col ORDER BY smmry DESC

SELECT original_medium, SUM(n_movies) AS total_movies
	FROM franchises
	GROUP BY original_medium
	ORDER BY total_movies DESC

Get rows where values in a group meet a criterion with GROUP BY col HAVING condn

SELECT original_medium, SUM(n_movies) AS total_movies
	FROM franchises
	GROUP BY original_medium
	ORDER BY total_movies DESC
	HAVING total_movies > 10

Filter before and after grouping with WHERE condn_before GROUP BY col HAVING condn_after

SELECT original_medium, SUM(n_movies) AS total_movies
	FROM franchises
	WHERE owner = 'The Walt Disney Company'
	GROUP BY original_medium
	ORDER BY total_movies DESC
	HAVING total_movies > 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 franchises
LIMIT 2, 3

By default, MySQL uses case insensitive matching in WHERE clauses.

SELECT *
	FROM franchises
	WHERE owner = 'THE WALT DISNEY COMPANY'

To get case sensitive matching, use WHERE BINARY condn

SELECT *
	FROM franchises
	WHERE BINARY owner = 'THE WALT DISNEY COMPANY'

Get the current date with CURDATE() and the current datetime with NOW() or CURTIME()

SELECT NOW(), CURDATE(), CURTIME()

List available tables with show tables

show tables
Topics

Start Your Data Analysis Journey Today!

Course

Introduction to SQL

2 hr
619.2K
Learn how to create and query relational databases using SQL in just two hours.
See DetailsRight Arrow
Start Course
See MoreRight Arrow
Related

PostgreSQL vs. MySQL: Choosing the Right Database for Your Project

Explore the key differences and similarities between PostgreSQL and MySQL to find the best database solution for your project's needs.
Jake Roach's photo

Jake Roach

8 min

SQL Developer Salaries: Expectations in 2024

In this article, we're going to learn what an SQL developer does, what factors impact SQL developer salaries, how the average SQL developer salary compares to the average salaries of other developer profiles, and how you can increase your salary as an SQL developer.
Elena Kosourova's photo

Elena Kosourova

7 min

Scaling Enterprise Analytics with Libby Duane Adams, Chief Advocacy Officer and Co-Founder of Alteryx

RIchie and Libby explore the differences between analytics and business intelligence, generative AI and its implications in analytics, the role of data quality and governance, Alteryx’s AI platform, data skills as a workplace necessity, and more. 
Richie Cotton's photo

Richie Cotton

43 min

[Radar Recap] Building a Learning Culture for Analytics Functions, with Russell Johnson, Denisse Groenendaal-Lopez and Mark Stern

In the session, Russell Johnson, Chief Data Scientist at Marks & Spencer, Denisse Groenendaal-Lopez, Learning & Development Business Partner at Booking Group, and Mark Stern, VP of Business Intelligence & Analytics at BetMGM will address the importance of fostering a learning environment for driving success with analytics.
Adel Nehme's photo

Adel Nehme

41 min

[Radar Recap] From Data Governance to Data Discoverability: Building Trust in Data Within Your Organization with Esther Munyi, Amy Grace, Stefaan Verhulst and Malarvizhi Veerappan

Esther Munyi, Amy Grace, Stefaan Verhulst and Malarvizhi Veerappan focus on strategies for improving data quality, fostering a culture of trust around data, and balancing robust governance with the need for accessible, high-quality data.
Richie Cotton's photo

Richie Cotton

39 min

[Radar Recap] Scaling Data ROI: Driving Analytics Adoption Within Your Organization with Laura Gent Felker, Omar Khawaja and Tiffany Perkins-Munn

Laura, Omar and Tiffany explore best practices when it comes to scaling analytics adoption within the wider organization
Richie Cotton's photo

Richie Cotton

40 min

See MoreSee More