MySQL Summary: Basics

This post is my personal summary of this Mosh Hamedani's Youtube video, check it out:

Also, check out his website codewithmosh, I've taken the Docker course and I highly recommend it!

Here are the basic commands

Query everything in a table in a database:

USE database_name;
SELECT * FROM table_name

Another form that gives the same result:

SELECT * FROM database_name.table_name

Say we have a database called store_db that contains a table called orders

Filtering

This is the orders table:

• Greater/less than and equal > < >= <= =

Filter orders with a total cost > 30 and show only order with order_id = 6:

SELECT *
FROM store_db.orders
WHERE unit_price * quantity > 30 AND order_id = 6

• IN

Filter only orders for products with ids 4, 6 or 10

SELECT *
FROM store_db.orders
WHERE product_id IN (4, 6, 10)

• NOT

Let's filter now products that do NOT have the mentioned ids:

SELECT *
FROM store_db.orders
WHERE product_id NOT IN (4, 6, 10)

• BETWEEN

Get orders for quantities between (and including) 5 and 10

SELECT *
FROM store_db.orders
WHERE quantity BETWEEN 5 AND 10

• LIKE

Here we are talking about data that matches specific pattern, not specific values.

So let's get a look at this table

The % symbol:

Filter customers where their last name starts with the letter b

SELECT *
FROM store_db.customers
WHERE last_name LIKE 'b%'

So % here basically means "anything" and in this context: b% it means "anything that starts with the letter b".

%b then means "anything that ends with b" and %b% means "anything that has the letter b" it can be in the middle, beginning or the end.

The _ symbol

Every _ means one character, so if we filter for ___ it will return all items that has 3 characters, and if we say _____y we'll get all data with five characters and end with the character y.

Let's try it out:

SELECT *
FROM sql_store.customers
WHERE last_name LIKE '_____y'

The result:

• REGEXP

Similar to LIKE but more powerful.

Let's say we want to find all customers that have "trail" or "avenue" in their address. First let's look how can we do that using LIKE:

WHERE address LIKE '%trail%' or address LIKE '%avenue%'

And using REGEXP:

WHERE address REGEXP 'trail|avenue'

So the pipe symbol in regex | mean OR, so the query says: "find all customers where the address contains the words "trail" or "avenue""

Another basic regex symbols are ^ which indicates the start of the word. And $ which indicates the end of the word. Examples:

Run the following query

SELECT *
FROM sql_store.customers
WHERE address REGEXP '^53'

It will return the customer with this address 538 Mosinee Center because it starts with 53

Now this query:

SELECT *
FROM sql_store.customers
WHERE address REGEXP 'junction$'

It will return the customer with this address 251 Springs Junction because it ends with junction

Regular expressions are very powerful and they can get really complex, so I won't talk further about the different possible ways we can write them, maybe I'll create a dedicated post in the future.

That's it for the basics. The more advanced stuff will be in future posts 🕺