How to MySQL

This is my personal collection of MySQL notes and examples. I'm putting this stuff here to keep track of what I'm learning. This is not a guide!

Select Statement

With the SELECT statement, we can query data from a table.

If there is a table with the name "shows" and with the columns id and name, we can query it by calling:

-- This shows as every column which exists inside the shows table.
SELECT * FROM shows;

-- This would only show us the name of the show inside the shows table.
SELECT name FROM shows;

-- We can of course also show multiple columns.
SELECT name, genre FROM shows;

Distinct

If we want to see the unique values inside a table, we can use the DISTINCT keyword.

As an example, you have a database with 100+ shows. Many of the shows share the same genre, so if you would make a query for the genre, you would see the same multiple times.

-- Add DISTINCT after the SELECT keyword to query only unqiues.
SELECT DISTINCT genre FROM shows;

Where

We can filter information based on conditions. For that, we will need to use the WHERE clause.

Imagine that inside the show's table is a column called ratings. There are all ratings stored with a value between 1 and 100. Now we want to query all the shows which have at least a rating of 60.

SELECT *
FROM shows
WHERE ratings < 60;

Like

Let's continue with the show's table. We want to look for shows which begin with the name S. We can easily do this by using the LIKE keyword:

SELECT *
FROM shows
WHERE name LIKE 'S%';

The %-sign is a placeholder and means everything after S. Also, did you notice how we combined WHERE and LIKE together?

Between

We can use the BETWEEN operator inside a WHERE clause to filter a result within a certain range. The range must be separated with an AND keyword.

-- Search for all shows between 2018 and 2022
SELECT *
FROM shows
WHERE year
BETWEEN 2018 AND 2022;

When the provided values are text, BETWEEN filters within the alphabetical range.

-- Display every show where name begins with A, B, C, D.
SELECT *
FROM shows
WHERE name
BETWEEN 'A' AND 'D';

Order By

With the ORDER BY statement, we can sort the rows of the table in ascending or descending order. By default, it sorts in ascending order.

Let's say we want to see all our shows we have in our database by its release year, ordered descending:

SELECT name, genre, stream, year
FROM shows
ORDER BY year DESC;
-- We can sort it in the ascending order by removing the DESC from our statement.

Aggregate functions

Count

With the Count function, we can return how many rows are inside a table.

-- The asteriks inside COUNT means that we are counting every row.
SELECT COUNT(*)
FROM movies;

Min and Max

The Min function returns the smallest value inside a column. For example, let's see in a Movie Database, which is the lowest year of a movie?

SELECT MIN(year)
FROM movies;

And with the Max function we would see the highest value, obviously.

SELECT MAX(year)
FROM movies;

Sum

The sum function returns us the total value of all numbers count in a column. Here I have some movies with the years between 2021-2024, that's a bad example but imagine it would calculate the sum of all product prices.

SELECT SUM(year)
FROM movies;

Avg

With the avg function, we can get the average of the column. For example, the average price.

SELECT AVG(year)
FROM movies;

Group by

The GROUP BY statement groups rows of data with the same values into buckets.

We can combine it with our other Aggregate functions, like for example with Count. Want to see how much movies were released in those years?

SELECT year, COUNT(*)
FROM movies
GROUP BY year;

Output of our previous code.