My Structured Query Language (MySQL)

Databases is a cornerstone if software projects. There’s a famous quote saying:

Information is the oil of the 21st century

Information, if you think about it, is a interesting thing itself. You don’t know what you don’t know, if you know a lot you probably get a feeling of what you’re missing to know and you probably shouldn’t share everything you know or at least not with everybody. But what does that all even mean and how does that work?

reliable.codes is a platform about programming and IT so in this article I’ll focus on how you can store information, no matter if just a small amount or much more. And how can this be done in a fraction of a millisecond?

Theres many (different) ways to store data. In this article I’ll introduce you to MySQL, which is a relational database. Feel free to jump around between the sections. You don’t need to know everything to get started, it really depends on your project.

Tables, Rows, Columns #

MySQL Databases have a fix structure. There’s a list off tables and each table has a list of columns, like a gigantic excel spreadsheet. Every information is a row in this table and must follow the given structure of that table.

Table 1

Table 2

In MySQL Databases the structure (tables, columns) make up the rules on how to store data. The data itself then goes into this tables row by row, that you can create, read, update and delete (known as CRUD).

You cannot write to or read from a table or column that doesn’t exist. But in case needed, you can change the structure by adding / removing tables or columns.

Data types #

When defining tables, you must define one data type per each column. All rows must then follow this definition, it cant be inconsistent. Let’s say I want to have a table listing my friends names and their age:

Friends

You now couldn’t add another row where you would set (willingly or not) the age to “test”. MySQL wouldn’t accept this. MySQL would ensure that your data is consistent.

Most used data types in MySQL:

For many datatypes you can or need to specify a quantifier, to specify the limit (INT(10) means 0 - 99).

How to create a table in MySQL #

Once you are logged in to your MySQL database, you can run commands to communicate with it. Let’s create the table of friends mentioned above, in a very straight-forward way. You can find a more detailed example at the end of the article.

Creating tables in MySQL can be as easy as this:

1
2
3
4
5

CREATE TABLE friends
(
    name VARCHAR(20),
    age  int(3)
);

With CREATE TABLE you ask MySQL to create a new table for you, then you tell it the name. In the braces, you put in the list of columns. There can be more details, more on that later.

How to change a table in MySQL #

As long as you have empty table you probably can just re-create the table, but how would you change a table that holds data?

In this article, I’ll cover it just on a high level. Changing databases, depending on your project, can be a complex topic. This is for multiple reasons, to name at least two

• Usually your database is used by some code. If you change your database, you need to change your code as well.
• If you change table structures, MySQL needs (internally) to migrate all your data. During that time, you may cannot read from and/or write to this table.

That is a quiet generic statement. There’s many “asterisks” to it. You can work around that, so technically it’s not fully true. But it’s an intro-article, it’s OK for now.

If, for example, you want to add another column to the friends table to store the city, you can do it like this:

1
2

ALTER TABLE friends
    ADD COLUMN city VARCHAR(50);

Let’s run that query and then ask MySQL to show us the tables final structure:

mysql> ALTER TABLE friends ADD COLUMN city VARCHAR(50);
Query OK, 0 rows affected (0.015 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE friends;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | VARCHAR(20) | YES  |     | NULL    |       |
| age   | INT         | YES  |     | NULL    |       |
| city  | VARCHAR(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.003 sec)

Finally you can drop the column again:

1
2

ALTER TABLE friends
    DROP COLUMN city;

How to delete a table in MySQL #

Similar to CREATE TABLE for table creation you can use DROP TABLE for table deletion. You don’t need anything else then this:

1

DROP TABLE friends;

Be warned: There’s no confirmation dialog or anything. Once you submit the query, the table is gone.

Working with MySQL data (CRUD) #

MySQL is there for you to work with data. Once you created a row, you can read it but also you can change or delete it.

INSERT data into MySQL tables (Create) #

Let’s re-use our example table of friends from the previous chapter and add some of them to this table. Adding data to a MySQL table is called INSERT.

1
2

INSERT INTO friends(name, age)
    VALUE ("Tris", 21);

With INSERT INTO you ask MySQL to insert data into some table. Now of course you need to provide the name of the table next. Also, you need to tell it, which columns to insert data to, which you do in the first braces (name and age). Now you need to tell about what data to put in, which you can do with VALUE and then in braces the data per column. The order must follow the names of columns you defined after the table name, so here you go with Tris and 21.

If you run the code, MySQL will let you know about the result:

mysql> INSERT INTO friends(name, age) VALUE ("Tris", 21);
Query OK, 1 row affected (0.005 sec)

As you can see, the query was executed successfully (and it took just 5 milliseconds to store the data).

Why do I have to repeat the columns on every MySQL insert?

As you can see later, columns in MySQL tables can be empty or have default values. So not always you’ll insert data to all columns. This is why you need to tell MySQL which columns you want to insert data to.

Now, let’s quickly add the other friends and move on:

1
2
3
4

INSERT INTO friends(name, age)
VALUES ("Golo", 22),
       ("Michael", 23),
       ("Robert", 60);

See this? When using VALUES instead of VALUE, you can insert multiple rows with one query.

mysql> INSERT INTO friends(name, age) VALUES ("Golo", 22), ("Michael", 23), ("Robert", 60);
Query OK, 3 rows affected (0.014 sec)
Records: 3  Duplicates: 0  Warnings: 0

Insert incorrect data to a MySQL table #

In the section about tables, rows and columns I told you, you just cannot insert “wrong” data. Let’s see how this works. Let’s mix up age and name:

1
2

INSERT INTO friends(age, name)
    VALUE ("Golo", 22);

This query now would try to define Golo as age and 22 as name. Both will not work since the data types doesn’t match.

mysql> INSERT INTO friends(age, name) VALUE ("Golo", 22);
ERROR 1366 (HY000): Incorrect integer value: 'Golo' for column 'age' at row 1

As you can see, this query didn’t work well. MySQL also informs us that Golo is an incorrect int value.

If you spend time on well-defining your data structure, MySQL will prevent you from mistakenly storing wrong data.

SELECT data from a MySQL table (Read) #

OK fine, now you have a table of our friends and you’ve put some data to it. How can you now get the list back?

Reading data from a MySQL table is done using a SELECT query. Why it’s called that you you’ll see in a second:

1
2

SELECT name, age
FROM friends;

With SELECT you ask MySQL to get data from some table. Next you tell MySQL which columns you wanna have (name and age). Finally, you tell it where to get those columns from, in our case the table friends.

mysql> SELECT name, age FROM friends;
+---------+------+
| name    | age  |
+---------+------+
| Tris    |   21 |
| Golo    |   22 |
| Michael |   23 |
| Robert  |   60 |
+---------+------+
4 rows in set (0.003 sec)

Search data in MySQL #

Not always you wanna read all data from MySQL tables, sometimes you need to answer a very specific question. Let’s say you wanna look up “How old is Golo?”. Obviously now you just need the age, but also you just need the age of our friend Golo.

1
2
3

SELECT age
FROM friends
WHERE name = 'Golo';

You can ask MySQL to filter the data for us using WHERE, now for sure you need to tell it how to filter the data. You can specify a list of criteria each having the target column and the filter. In our case you explicitly search for friends where name equals to Golo.

mysql> SELECT age FROM friends WHERE name = 'Golo';
+------+
| age  |
+------+
|   22 |
+------+
1 row in set (0.001 sec)

Using MySQL you now have the answer: “Golo is at the age of 22!”.

Let’s go the other way around and look up “Which friends are in their 20s?”.

1
2
3

SELECT name
FROM friends
WHERE age BETWEEN 20 AND 29;

The filter criteria you can use is somewhat tied to the data type of the column you want to target. You defined age to be int so we can use BETWEEN to search for a specific range of numbers.

mysql> SELECT name FROM friends WHERE age BETWEEN 20 AND 29;
+---------+
| name    |
+---------+
| Tris    |
| Golo    |
| Michael |
+---------+
3 rows in set (0.001 sec)

As you can see, MySQL tells you: “Tris, Golo and Michael are between 20 and 29 years old”.

Order data of MySQL tables #

When talking about ORDER in MySQL, we just talk about how the data is given back to you, not how MySQL actually stores it. Remember: You don’t care about how databases store data. You really don’t. Trust me, it’s crazy what happens behind the scenes.

OK, now let’s get an ordered list of our friends:

1
2
3

SELECT name, age
FROM friends
ORDER BY name ASC;

As you can see, you can fully re-use the SELECT query from before, now you just tell MySQL to ORDER the output and for sure now the query is BY what to order, right? So next you define one (can be more) column to ORDER BY, you go with name. When talking about ORDER the final question is, in which direction? You use ASC to get “ascending” list of our friends (a to z). If you like you also can go with DESC to get it “descending” (z to a).

mysql> SELECT name, age FROM friends ORDER BY name ASC;
+---------+------+
| name    | age  |
+---------+------+
| Golo    |   22 |
| Michael |   23 |
| Robert  |   60 |
| Tris    |   21 |
+---------+------+
4 rows in set (0.008 sec)

Here you have it, all our friends, ordered by name are: “Golo (22), Michael (23), Robert (60) and Tris (21)”.

Group data in MySQL #

MySQL let’s you GROUP data, that you select from the query. Grouping is important if you want to count something or you want to sum up a column or get an average. There’s more use-cases.

Since grouping comes into play, you need some data that can be grouped, so let’s add some new friends:

mysql> INSERT INTO friends(name, age) VALUES ("Golo", 40), ("Michael", 33), ("Robert", 50);
Query OK, 3 rows affected (0.005 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM friends;
+---------+------+
| name    | age  |
+---------+------+
| Tris    |   21 |
| Golo    |   22 |
| Michael |   23 |
| Robert  |   60 |
| Golo    |   40 |
| Michael |   33 |
| Robert  |   50 |
+---------+------+
7 rows in set (0.002 sec)

Now you have some names doubled, but all have different ages. Let’s find out how many friends we have, by their name:

1
2
3

SELECT name, count(0)
FROM friends
GROUP BY name;

You ask MySQL to select the name and COUNT(0) (which basically means “number of rows”) from our friends table and GROUP BY the name.

mysql> SELECT name, COUNT(0) FROM friends GROUP BY name;
+---------+----------+
| name    | COUNT(0) |
+---------+----------+
| Tris    |        1 |
| Golo    |        2 |
| Michael |        2 |
| Robert  |        2 |
+---------+----------+
4 rows in set (0.001 sec)

You can also find the average age per name:

1
2
3

SELECT name, AVG(age)
FROM friends
GROUP BY name;

Now instead of COUNT(0) you use AVG(age) (AVG means average) and in the braces you tell it what column to average on, in our case it’s the age:

mysql> SELECT name, AVG(age) FROM friends GROUP BY name;
+---------+----------+
| name    | AVG(age) |
+---------+----------+
| Tris    |  21.0000 |
| Golo    |  31.0000 |
| Michael |  28.0000 |
| Robert  |  55.0000 |
+---------+----------+
4 rows in set (0.002 sec)

Limit data of MySQL tables #

Maybe this is a little edge case, but I found it interesting to at least know about this. How could you answer the question “Who is our oldest friend?”…

It is just now when you realise, if you know the pieces, you can orchestrate them to your needs.

1
2
3
4

SELECT name
FROM friends
ORDER BY age DESC
LIMIT 1;

You ask MySQL to SELECT the name of our friends, ORDER BY their age and simply LIMIT the output to just 1.

mysql> SELECT name FROM friends ORDER BY age DESC LIMIT 1;
+--------+
| name   |
+--------+
| Robert |
+--------+
1 row in set (0.004 sec)

And also here, faster then the blink of an eye, MySQL tells us: “Robert is your oldest friend”.

Change data in MySQL (Update) #

Another year went by, our friends got older, it’s time to update our list of friends, right? If you do this for the first time, it probably looks odd.

This is a cornerstone of working with databases, over time, you’ll more and more understand the concept of MySQL table updates. You might find yourself in situations where you feel like you have a knot in your head, unsure of what exactly is happening. Thats OK. You’re fine. Just go ahead :)

Let’s first go with the most-easy update we can do in the current context:

1
2
3

UPDATE friends
SET age = 61
WHERE name = "Robert";

You start by asking MySQL to UPDATE our friends table. It is probably un-surprising by now, if you read the article from start. Next you need to tell which columns of that table to update (SET), per column also you need to define the value that should be written, so you go with age and 61. But not all our friends are now 61, right? So you may want to add some filters, re-using WHERE that we’ve used in the previous chapter already.

mysql> UPDATE friends SET age = 61 WHERE name = "Robert";
Query OK, 1 row affected (0.007 sec)
Rows matched: 1  Changed: 1  Warnings: 0

It worked, good. But that’s some tedious work now, to run queries per each friend, no? I’ll set back Robert’s age to 60 and show you some more MySQL magic.

MySQL is updating record by record, that matches with your filters. “During” the update, you can access the current data. I mean, you can access the current age, per friend, that you want to update. On top of that, you can ask MySQL to do the maths for us. Actually, you can do this:

1
2

UPDATE friends
SET age = age + 1;

You ask MySQL to UPDATE our friends and increase their age by 1.

mysql> UPDATE friends SET age = age + 1;
Query OK, 4 rows affected (0.004 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> SELECT * FROM friends;
+---------+------+
| name    | age  |
+---------+------+
| Tris    |   22 |
| Golo    |   23 |
| Michael |   24 |
| Robert  |   61 |
+---------+------+
4 rows in set (0.020 sec)

As you can see, the update went through successfully, if we select the data again from the table, we see the new values.

Welcome to the world of databases. Now it’s time to question the decisions of your life and why you picked age INT(3) instead of date_of_birth DATE which would be more precise and lift your job of updating this damn table every year…

Remove data from MySQL (Delete) #

As time goes by, you and Michael change you perspective’s of life, you dislike him now, up to a point where you don’t call him a friend any longer. Let’s cross his name off the list.

1
2
3

DELETE
FROM friends
WHERE name = "Michael";

First you ask MySQL to DELETE something which obviously you need to specify a little more with where to delete FROM, in our case the table friends.

Warning: DELETE FROM friends; is a valid MySQL query and it will delete all your friends without even asking. If you don’t want to end up without friends, you better NEVER forget to apply some filter to MySQL DELETE queries.

After this warning, you better provide a little more context to our query and target just friends where the name equals to Michael.

mysql> DELETE FROM friends WHERE name = "Michael";
Query OK, 1 row affected (0.010 sec)

mysql> SELECT * FROM friends;
+--------+------+
| name   | age  |
+--------+------+
| Tris   |   22 |
| Golo   |   23 |
| Robert |   61 |
+--------+------+
3 rows in set (0.006 sec)

That’s it. As you can see, MySQL deleted Michael from the list.

Rules / Constraints #

MySQL will prevent you storing incorrect data, as long as you feed it enough information. One part of the information MySQL can leverage to validate data is, as you saw, data types. But also you can tell it some rules and constraints. This will help the database to understand your needs even better and speed up your SELECT queries significantly.

Unique row identifier (PRIMARY KEY) #

In relational databases it’s somewhat common (you’ll see later why), that you have a unique identifier per row. Often, but not always, called id.

This identifier is called PRIMARY KEY and it is a label that you can put to any column in your table. Just remember that it’s unique, there can’t be two rows having the same value.

In our list of friends, you neither can use name or age as a PRIMARY KEY, so I’d add a new column. I personally put the PRIMARY KEY as the first column in my table:

1
2
3
4
5
6

CREATE TABLE friends
(
    id   INT PRIMARY KEY,
    name VARCHAR(20),
    age  INT
);

Incrementing numeric unique row identifier (AUTO_INCREMENT) #

If you use an numeric id for our PRIMARY KEY, wouldn’t it be tedious to manually keep track of all used numbers just to have the next number at hand whenever you want to INSERT some new row? MySQL has your back: AUTO_INCREMENT.

1
2
3
4
5
6

CREATE TABLE friends
(
    id   INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20),
    age  INT
);

Now MySQL will keep track and always provide new rows with the next number. I’ve re-created my friends table we used before, it’s empty now. Let’s add back our four friends and see, what MySQL is adding to the table:

mysql> INSERT INTO friends(name, age) VALUES ("Tris", 21), ("Golo", 22), ("Michael", 23), ("Robert", 60);
Query OK, 4 rows affected (0.007 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM friends;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | Tris    |   21 |
|  2 | Golo    |   22 |
|  3 | Michael |   23 |
|  4 | Robert  |   60 |
+----+---------+------+
4 rows in set (0.002 sec)

As you can see, now all of our friends do have an id that we haven’t defined ourself.

At this point, MySQL knows already, that the next row, will get 5 as it’s id. Even, if we delete Robert before adding a new row.

See what’s the next AUTO_INCREMENT value #

That will be a absolute edge case, but sometimes it’s useful: If you ever need to find out the next value that MySQL will use for AUTO_INCREMENT, you can do this by reading through the “table status”:

1

SHOW TABLE STATUS LIKE 'friends';

In this output, you search for the column “Auto_increment”:

mysql> SHOW TABLE STATUS LIKE 'friends';
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name    | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| friends | InnoDB |      10 | Dynamic    |    4 |           4096 |       16384 |               0 |            0 |         0 |              5 | 2025-05-18 06:16:11 | 2025-05-18 06:16:16 | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.041 sec)

Changing next AUTO_INCREMENT value #

The next value of AUTO_INCREMENT, as you saw, is part of the table itself, not one row or column. If you ever would need to change that value (again: this will be an edge case) you need to change the table:

1
2

ALTER TABLE friends
    AUTO_INCREMENT = 10;

Now the next row you enter to this table will have 10 as it’s id.

Prevent data duplication (UNIQUE) #

For some columns, even if they are not PRIMARY_KEY, it would be interesting to ensure their value is across the whole table. Just for the point of this section, let’s say the name must be unique:

1
2
3
4
5
6

CREATE TABLE friends
(
    id   INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) UNIQUE,
    age  INT
);

I’ve re-created my friends table we’d used before, it’s empty now. So now let’s try to add back our friends and by accident, execute it twice:

mysql> INSERT INTO friends(name, age) VALUES ("Tris", 21), ("Golo", 22), ("Michael", 23), ("Robert", 60);
Query OK, 4 rows affected (0.019 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO friends(name, age) VALUES ("Tris", 21), ("Golo", 22), ("Michael", 23), ("Robert", 60);
ERROR 1062 (23000): Duplicate entry 'Tris' for key 'friends.name'

mysql> SELECT * FROM friends;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | Tris    |   21 |
|  2 | Golo    |   22 |
|  3 | Michael |   23 |
|  4 | Robert  |   60 |
+----+---------+------+
4 rows in set (0.001 sec)

As you can see, the second execution didn’t work, since it would corrupt with the idea to just have one name once in the table.

Ensure groups of column to be unique #

Think of all persons you met in your life. Do you really have no two with the same name? I doubt it. So let’s say, instead of just having the name unique, it should be the combination of name and age (even if this is not really better).

Adding UNIQUE to both columns will not ensure the combination is unique, but would ensure for both columns, that values can’t be used twice. This would make the situation worse. So how can we do that?

1
2
3
4
5
6
7

CREATE TABLE friends
(
    id   INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20),
    age  INT,
    UNIQUE (name, age)
);

When you use CREATE TABLE, in the braces you put columns, but also you can put more information. I personally, even if it’s no official rule, put columns first and then all other constraints of the particular table.

Now let’s add back our friends. But on top of that, let’s add another Michael who is 34:

1
2
3
4
5
6

INSERT INTO friends(name, age)
VALUES ("Tris", 21),
       ("Golo", 22),
       ("Michael", 23),
       ("Michael", 34),
       ("Robert", 60);

And now let’s run it twice again, to have two questions answered:

1. Can you add Michael twice (one is 23, the other 34)?
2. Can you have duplicated entries where name and age are identical?

mysql> INSERT INTO friends(name, age) VALUES ("Tris", 21), ("Golo", 22), ("Michael", 23), ("Michael", 34), ("Robert", 60);
Query OK, 5 rows affected (0.020 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> INSERT INTO friends(name, age) VALUES ("Tris", 21), ("Golo", 22), ("Michael", 23), ("Michael", 34), ("Robert", 60);
ERROR 1062 (23000): Duplicate entry 'Tris-21' for key 'friends.name'

mysql> SELECT * FROM friends;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  2 | Golo    |   22 |
|  3 | Michael |   23 |
|  4 | Michael |   34 |
|  5 | Robert  |   60 |
|  1 | Tris    |   21 |
+----+---------+------+
5 rows in set (0.002 sec)

Enforcing columns to be provided (NOT NULL) #

With INSERT INTO you always define the list of columns, that you want to insert data to. What, if you forget one column to specify? Will this work?

1
2

INSERT INTO friends(name)
    VALUE ("Tris");

By default, all columns in a table, are optional. So yes, it’ll work:

mysql> INSERT INTO friends(name) VALUE ("Tris");
Query OK, 1 row affected (0.023 sec)

mysql> SELECT * FROM friends;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  2 | Golo    |   22 |
|  3 | Michael |   23 |
|  4 | Michael |   34 |
|  5 | Robert  |   60 |
| 11 | Tris    | NULL |
|  1 | Tris    |   21 |
+----+---------+------+
6 rows in set (0.001 sec)

You probably guess it already, NULL means there’s no value defined. You probably should fix this, no?

1
2
3
4
5
6

CREATE TABLE friends
(
    id   INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    age  INT NOT NULL
);

Using NOT NULL you can inform MySQL to enforce this data to be provided. Now our query will fail:

mysql> INSERT INTO friends(name) VALUE ("Tris");
ERROR 1364 (HY000): Field 'age' doesn't have a default value

Prefill columns (DEFAULT) #

What if you have columns that must not be empty but in most cases get the same value? Also here it would be tedious to always specify the value. In this case you can define the DEFAULT value per column for the table.

Let’s add the country_code to our list of friends, it should be DE by default:

1
2
3
4
5
6
7

CREATE TABLE friends
(
  id           INT AUTO_INCREMENT PRIMARY KEY,
  country_code VARCHAR(3)  NOT NULL DEFAULT 'DE',
  name         VARCHAR(20) NOT NULL,
  age          INT         NOT NULL
);

And now let’s add our friends to it:

mysql> INSERT INTO friends(name, age) VALUES ("Tris", 21), ("Golo", 22), ("Michael", 23), ("Michael", 34), ("Robert", 60);
Query OK, 5 rows affected (0.008 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM friends;
+----+--------------+---------+-----+
| id | country_code | name    | age |
+----+--------------+---------+-----+
|  1 | DE           | Tris    |  21 |
|  2 | DE           | Golo    |  22 |
|  3 | DE           | Michael |  23 |
|  4 | DE           | Michael |  34 |
|  5 | DE           | Robert  |  60 |
+----+--------------+---------+-----+
5 rows in set (0.000 sec)

If you use DEFAULT values in MySQL, it’ll not complain if you insert data without specifying this field but just use the default value in this case. As soon as you specify a value, it’ll use yours:

1
2

INSERT INTO friends(country_code, name, age)
    VALUE ("US", "Jayme", 42);

Now, since you specify country_code, MySQL will use this value for Jayme, instead of the default one.

mysql> INSERT INTO friends(country_code, name, age) VALUE ("US", "Jayme", 42);
Query OK, 1 row affected (0.011 sec)

mysql> SELECT * FROM friends;
+----+--------------+---------+-----+
| id | country_code | name    | age |
+----+--------------+---------+-----+
|  1 | DE           | Tris    |  21 |
|  2 | DE           | Golo    |  22 |
|  3 | DE           | Michael |  23 |
|  4 | DE           | Michael |  34 |
|  5 | DE           | Robert  |  60 |
|  6 | US           | Jayme   |  42 |
+----+--------------+---------+-----+
6 rows in set (0.002 sec)

Prefill time values (CURRENT_TIMESTAMP) #

DEFAULT values have more use cases then just dealing with lazy developers, for sure. Let’s say, and this is probably a better use then the country_code, you want to know when you added someone to the list.

1
2
3
4
5
6
7
8

CREATE TABLE friends
(
    id           INT AUTO_INCREMENT PRIMARY KEY,
    country_code VARCHAR(3)  NOT NULL DEFAULT 'DE',
    name         VARCHAR(20) NOT NULL,
    age          INT         NOT NULL,
    added        DATETIME             DEFAULT CURRENT_TIMESTAMP
);

Again I recreated the table and added back the four friends, then Jayme a little later:

mysql> INSERT INTO friends(name, age) VALUES ("Tris", 21), ("Golo", 22), ("Michael", 23), ("Michael", 34), ("Robert", 60);
Query OK, 5 rows affected (0.007 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> INSERT INTO friends(country_code, name, age) VALUE ("US", "Jayme", 42);
Query OK, 1 row affected (0.017 sec)

mysql> SELECT * FROM friends;
+----+--------------+---------+-----+---------------------+
| id | country_code | name    | age | added_at            |
+----+--------------+---------+-----+---------------------+
|  1 | DE           | Tris    |  21 | 2025-05-18 08:34:05 |
|  2 | DE           | Golo    |  22 | 2025-05-18 08:34:05 |
|  3 | DE           | Michael |  23 | 2025-05-18 08:34:05 |
|  4 | DE           | Michael |  34 | 2025-05-18 08:34:05 |
|  5 | DE           | Robert  |  60 | 2025-05-18 08:34:05 |
|  6 | US           | Jayme   |  42 | 2025-05-18 08:35:07 |
+----+--------------+---------+-----+---------------------+
6 rows in set (0.001 sec)

Now MySQL is ensuring you, without any work, always know when you added someone to this list.

Add business rules (CHECK) #

Since MySQL 8 you can add even more complex checks, that maybe are tied more towards your business rules. I can’t recall having worked on a project using this, probably because it’s somewhat “new” and there’s maybe some downsides. But I find it to be interesting, so I cover it at least on a high level now.

Let’s say we just want to be able to add friends at the age of 20 or above.

1
2
3
4
5
6
7
8

CREATE TABLE friends
(
    id           INT AUTO_INCREMENT PRIMARY KEY,
    country_code VARCHAR(3)  NOT NULL DEFAULT 'DE',
    name         VARCHAR(20) NOT NULL,
    age          INT         NOT NULL CHECK (age >= 20),
    added        DATETIME             DEFAULT CURRENT_TIMESTAMP
);

It can be as easy as this, now you can’t add anybody who’s younger than 20.

mysql> INSERT INTO friends(name, age) VALUES ("Tris", 21), ("Golo", 22), ("Michael", 23), ("Michael", 34), ("Robert", 60);
Query OK, 5 rows affected (0.010 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> INSERT INTO friends(country_code, name, age) VALUE ("US", "Jayme", 19);
ERROR 3819 (HY000): Check constraint 'friends_chk_1' is violated.

Let’s do one more thing. First I dislike friends_chk_1, that doesn’t tell much. And while fixing this, let’s go with “just friends from germany must be at least 20 years old”. Just for the sake of the article.

Same as for UNIQUE contraints, now you can’t just put the check to the columns, you need to have it as a dedicated line in the table definition:

1
2
3
4
5
6
7
8
9

CREATE TABLE friends
(
    id           INT AUTO_INCREMENT PRIMARY KEY,
    country_code VARCHAR(3)  NOT NULL DEFAULT 'DE',
    name         VARCHAR(20) NOT NULL,
    age          INT         NOT NULL,
    added        DATETIME             DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT from_de_min_age_20 CHECK (country_code != 'DE' OR (country_code = 'DE' AND age >= 20))
);

This now adds our rule / constraint with the name from_de_min_age_20. The rule says, you can add if:

1. Either contry_code is not DE OR
2. The country_code is DE AND the age is at least 20

If you now re-create the table, fill our friends again, two of them below 20 and one of those two from germany, we’ll see the CHECK working:

mysql> INSERT INTO friends(name, age) VALUES ("Tris", 21), ("Golo", 22), ("Michael", 23), ("Michael", 34), ("Robert", 60);
Query OK, 5 rows affected (0.007 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> INSERT INTO friends(country_code, name, age) VALUE ("US", "Jayme", 19);
Query OK, 1 row affected (0.004 sec)

mysql> INSERT INTO friends(country_code, name, age) VALUE ("DE", "Peter", 18);
ERROR 3819 (HY000): Check constraint 'from_de_min_age_20' is violated.

As you can see you could add Jayme from US (19 years old) but not Peter from DE (18 years old).

Find data fast (INDEX) #

As said, databases is a crazy topic but at the same time it’s a cornerstone in software development. Databases can deal with a ton of data even if there’s one gigantic table. If you have a growing number of records in your table, you can find yourself in the situation where the very same query is getting slower and slower over time.

Since this is a complex topic, I’ll cover it just on a high level in this article. It’s too important to not at least mention it.

Remember our query to find the age of Golo?

1
2
3

SELECT age
FROM friends
WHERE name = 'Golo';

With our current table definition, MySQL is walking through all rows in the table and checks if the name is equal to Golo. You can see this if you let MySQL EXPLAIN what it’s doing:

mysql> EXPLAIN SELECT age FROM friends WHERE name = 'Golo';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | friends | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |    16.67 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.011 sec)

Ignore everything beside the column rows. This tells you how many records in your table needed to be checked if they match your query. You always want to have this number low. How low really depends on your project.

Using an INDEX you can tell MySQL, what columns you will often use in our queries. Using this information, MySQL then won’t just note down where to find ALL of our friends, but also where to find particular GROUPS of friends (non-official term I guess). To do this, MySQL needs some more memory (RAM) to hold this information, so again: Use it wisely in the context of your current project.

Even if this is not a particular benchmark, let’s at least add some more entries so you have round about 100 friends:

INSERT INTO friends(name, age)
VALUES ("Alice", 25), ("Bob", 30), ("Charlie", 22), ("David", 28), ("Eva", 35), ("Frank", 40), ("Grace", 27), ("Hannah", 32), ("Ian", 29), ("Jane", 34), ("Kevin", 26), ("Linda", 31), ("Michael", 23), ("Nancy", 37), ("Oliver", 24), ("Paul", 33), ("Quincy", 21), ("Rachel", 36), ("Sam", 28), ("Tina", 30), ("Uma", 25), ("Victor", 32), ("Wendy", 29), ("Xander", 35), ("Yara", 27), ("Zach", 31), ("Aaron", 22), ("Bella", 34), ("Carl", 26), ("Diana", 30), ("Eli", 28), ("Fiona", 33), ("Gary", 24),
       ("Heidi", 36), ("Ivy", 29), ("Jack", 31), ("Kate", 25), ("Leo", 32), ("Mia", 27), ("Nate", 30), ("Olivia", 23), ("Peter", 34), ("Quinn", 28), ("Rita", 31), ("Samantha", 26), ("Tony", 32), ("Ursula", 29), ("Vince", 35), ("Wanda", 27), ("Xavier", 30), ("Yasmine", 24), ("Zara", 33), ("Abby", 28), ("Ben", 31), ("Cara", 25), ("Dan", 32), ("Ella", 29), ("Finn", 34), ("Gina", 27), ("Hank", 30), ("Iris", 23), ("Jake", 31), ("Kara", 28), ("Liam", 32), ("Mona", 26), ("Nick", 30),
       ("Owen", 24), ("Pam", 33), ("Quinn", 29), ("Ryan", 31), ("Sara", 27), ("Ted", 32), ("Una", 28), ("Vera", 30), ("Will", 25), ("Xena", 31), ("Yara", 29), ("Zoe", 34), ("Aiden", 27), ("Beth", 32), ("Caleb", 28), ("Dana", 30), ("Evan", 24), ("Faye", 33), ("Gabe", 29), ("Holly", 31), ("Ian", 26), ("Jade", 30), ("Kyle", 25), ("Lana", 32), ("Mike", 28), ("Nina", 31), ("Oscar", 27), ("Phoebe", 30), ("Quincy", 24), ("Rose", 33), ("Sam", 29), ("Tara", 31), ("Una", 28),
       ("Vera", 30), ("Will", 25), ("Xena", 32), ("Yara", 29), ("Zach", 34);
       
mysql> EXPLAIN SELECT age FROM friends WHERE name = 'Golo';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | friends | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  110 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.004 sec)

Now you have 110 rows that needed to be checked. Let’s run the query 5 times:

mysql> SELECT age FROM friends WHERE name = 'Golo';
+-----+
| age |
+-----+
|  22 |
+-----+
1 row in set (0.008 sec)

mysql> SELECT age FROM friends WHERE name = 'Golo';
+-----+
| age |
+-----+
|  22 |
+-----+
1 row in set (0.001 sec)

mysql> SELECT age FROM friends WHERE name = 'Golo';
+-----+
| age |
+-----+
|  22 |
+-----+
1 row in set (0.001 sec)

mysql> SELECT age FROM friends WHERE name = 'Golo';
+-----+
| age |
+-----+
|  22 |
+-----+
1 row in set (0.001 sec)

mysql> SELECT age FROM friends WHERE name = 'Golo';
+-----+
| age |
+-----+
|  22 |
+-----+
1 row in set (0.000 sec)

2,2 ms per Query in average. Let’s add an INDEX and see what would change:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE friends
(
    id           INT AUTO_INCREMENT PRIMARY KEY,
    country_code VARCHAR(3)  NOT NULL DEFAULT 'DE',
    name         VARCHAR(20) NOT NULL,
    age          INT         NOT NULL,
    added        DATETIME             DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT from_de_min_age_20 CHECK (country_code != 'DE' OR (country_code = 'DE' AND age >= 20)),
    INDEX names (name)
);

I recreated the table and added back the exact same 110 records, let’s explain again how many records needed to be filtered:

mysql> EXPLAIN SELECT age FROM friends WHERE name = 'Golo';
+----+-------------+---------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | friends | NULL       | ref  | names         | names | 82      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.002 sec)

As you can see, now it’s just 1 row left. The reason is that now MySQL can use our INDEX that you created (names) and directly find all relevant rows, which currently is just 1.

Let’s run our 5 queries again:

mysql> SELECT age FROM friends WHERE name = 'Golo';
+-----+
| age |
+-----+
|  22 |
+-----+
1 row in set (0.004 sec)

mysql> SELECT age FROM friends WHERE name = 'Golo';
+-----+
| age |
+-----+
|  22 |
+-----+
1 row in set (0.001 sec)

mysql> SELECT age FROM friends WHERE name = 'Golo';
+-----+
| age |
+-----+
|  22 |
+-----+
1 row in set (0.001 sec)

mysql> SELECT age FROM friends WHERE name = 'Golo';
+-----+
| age |
+-----+
|  22 |
+-----+
1 row in set (0.001 sec)

mysql> SELECT age FROM friends WHERE name = 'Golo';
+-----+
| age |
+-----+
|  22 |
+-----+
1 row in set (0.001 sec)

1.6 ms per Query in average.

Note: PRIMARY KEY always is an INDEX as well, so you don’t need to use INDEX on a PRIMARY KEY column.

Keep in mind: This is no true, reliable benchmark. But just have a look on the rows that really need to be checked against your query. It’s 1 instead of 110. It’s somewhat obvious that it will be faster, esp. with more data. But keep in mind that it comes with a price of more memory. This is why you cannot simply add an index to all your columns, which would be a misuse of this feature.

Store just specific values (ENUM / SET) #

Let’s add some more data to our friends. You want to know, if they have pets. But you don’t care about all possible pets on the planet, you especially want to know if they have a dog, a cat or a rabbit.

For the sake of the demonstration, I’ll reduce the table to just name, age and animal.

1
2
3
4
5
6

CREATE TABLE friends
(
    name   VARCHAR(20) NOT NULL,
    age    INT         NOT NULL,
    animal ENUM ('cat', 'dog', 'rabbit')
);

You can see that ENUM is used. It’s an abbreviation for enumeration and has evolved from times where ENUM was basically a mapping from numbers to related values. Today the term ENUM just means “one of the following”.

Let’s add back our four friends and then let’s add another one who has a cat:

mysql> INSERT INTO friends(name, age) VALUES ("Tris", 21), ("Golo", 22), ("Michael", 23), ("Michael", 34), ("Robert", 60);
Query OK, 5 rows affected (0.007 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> INSERT INTO friends(name, age, animal) VALUE ("Jayme", 19, "cat");
Query OK, 1 row affected (0.004 sec)

mysql> SELECT * FROM friends;
+---------+-----+--------+
| name    | age | animal |
+---------+-----+--------+
| Tris    |  21 | NULL   |
| Golo    |  22 | NULL   |
| Michael |  23 | NULL   |
| Michael |  34 | NULL   |
| Robert  |  60 | NULL   |
| Jayme   |  19 | cat    |
+---------+-----+--------+
6 rows in set (0.002 sec)

You haven’t defined animal as NOT NULL so you have some friends without any animal and we have Jayme who has a cat.

Tris now has a horse, what will happen if we try to update him? Let’s see:

mysql> UPDATE friends SET animal = 'horse' WHERE name = 'Tris';
ERROR 1265 (01000): Data truncated for column 'animal' at row 1

The UPDATE didn’t work, even if the error message is somewhat cryptic. But what if Tris instead of a horse now does have a rabbit as well as a dog?

This is when a SET can be used instead of an ENUM:

1
2
3
4
5
6

CREATE TABLE friends
(
    name    VARCHAR(20) NOT NULL,
    age     INT         NOT NULL,
    animals SET ('cat', 'dog', 'rabbit')
);

Now you can concatenate all possible values with a , and store multiple animals per row:

mysql> UPDATE friends SET animals = 'dog,rabbit' WHERE name = 'Tris';
Query OK, 1 row affected (0.004 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM friends;
+---------+-----+------------+
| name    | age | animals    |
+---------+-----+------------+
| Tris    |  21 | dog,rabbit |
| Golo    |  22 | NULL       |
| Michael |  23 | NULL       |
| Michael |  34 | NULL       |
| Robert  |  60 | NULL       |
| Jayme   |  19 | cat        |
+---------+-----+------------+
6 rows in set (0.001 sec)

So how do you now find all friends that do have a rabbit?

mysql> SELECT * FROM friends WHERE animals = 'rabbit';
Empty set (0.007 sec)

Now this can come somewhat unexpected, but indeed it’s correct. Currently no one has “just” a rabbit. If you want to find those who “at least” have a rabbit, you can do it like this:

mysql> SELECT * FROM friends WHERE animals LIKE '%rabbit%';
+------+-----+------------+
| name | age | animals    |
+------+-----+------------+
| Tris |  21 | dog,rabbit |
+------+-----+------------+
1 row in set (0.001 sec)

Referencing other tables (FOREIGN KEY) #

MySQL is a relational database, as I said. This indicates beside the structure of the data, you can also define the relation between the data. This is another komplex topic, which I’ll talk about on a high level in the “Relations” topic of this artile down below.

Cross-table references in MySQL (Relation) #

This article so far covered topics that worked with one single table. With MySQL you can define relations between tables. This is a complex topic, one of the core concepts of relational databases like MySQL. For this introduction I’ll give a rough overview so you know about it. Feel free to read around, you’ll find many articles online for this topic.

Let’s think of an invoicing system, where you have clients and invoices. Invoices for example could be paid or not, and based on that, you would like to identify the clients that you need to call. And I would like to prevent clients from being deleted, as long as they have invoices.

How relations work #

In MySQL relations work so, that you can add a column to a table and inform MySQL that it’s value references another tables record. You can do this by naming the table and the column where to search for the given value. The column you pick is typicall the PRIMARY KEY but you also can use any UNIQUE column.

Finding link direction #

When thinking about relations, the question is “Who relates to whom?”. In this case: Is the client pointing invoices or is the invoice pointing to a client?

“Client to invoice” vs. “invoice to client”

If you read the section tables, rows and columns, you probably imagine already that storing such a reference can only work on the invoice. That is, because in this example, one invoice is clearly for one client, but (hopefully) over time one client could have multiple invoices.

Link two tables with each other #

Let’s define our clients first and then see, how you can link the invoices to them.

1
2
3
4
5
6
7

CREATE TABLE clients
(
  id    INT AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(128) NOT NULL,
  email VARCHAR(256),
  phone VARCHAR(50)
);

For this section, this should be enough information, so let’s check out the invoices:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE invoices
(
  id         INT AUTO_INCREMENT PRIMARY KEY,
  client_id  INT      NOT NULL,
  total      DOUBLE   NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  paid_at    DATETIME,

  FOREIGN KEY (client_id) REFERENCES clients (id)
);

That is not too special, isn’t it? All this we’ve done before in this article, except the one line defining the reference. It is a FOREIGN KEY and you use the column client_id to store it. It references the id column of the clients table.

That’s it. MySQL will now deal with it. Let’s see it in action:

mysql> INSERT INTO clients(name, email, phone) VALUES ("Tris", "tris@reliable.codes", NULL), ("Golo", "golo@reliable.codes", "0123456789");
Query OK, 2 rows affected (0.007 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM clients;
+----+------+---------------------+------------+
| id | name | email               | phone      |
+----+------+---------------------+------------+
|  1 | Tris | tris@reliable.codes | NULL       |
|  2 | Golo | golo@reliable.codes | 0123456789 |
+----+------+---------------------+------------+
2 rows in set (0.000 sec)

mysql> INSERT INTO invoices(client_id, total) VALUE (1, 13.37);
Query OK, 1 row affected (0.007 sec)

mysql> SELECT * FROM invoices;
+----+-----------+-------+---------------------+---------+
| id | client_id | total | created_at          | paid_at |
+----+-----------+-------+---------------------+---------+
|  1 |         1 | 13.37 | 2025-05-19 05:10:01 | NULL    |
+----+-----------+-------+---------------------+---------+
1 row in set (0.001 sec)

At first, it looks not very impressive, doesn’t it? All of this would have worked without the FOREIGN KEY too. So let’s try to mess it up.

Reference non-existing data #

As you saw in the section before, you have two clients (id 1 and 2), so what happens if you create an invoice for another id, one that doesn’t exist?

mysql> INSERT INTO invoices(client_id, total) VALUE (5, 13.37);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`invoices`, CONSTRAINT `invoices_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`))

As you can see, adding an invalid reference doesn’t work. MySQL will protect you from corrupting your data, as long as you have a well-defined data structure.

Deleting referenced data #

This opens up the question, what happens if you want delete a client, that has invoices?

mysql> DELETE FROM clients WHERE id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`invoices`, CONSTRAINT `invoices_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`))

Again the query fails. Now MySQL tells you that other tables are referencing to this record and you can’t delete it.

React on changes of referenced data #

It now can look like you always need to run multiple queries to get data deleted, that is in use. Gladly you’re in control of that.

Whenever you create a relation in MySQL, in case needed, you can define how to handle the change. There’s two possible changes:

1. The reference was updated
2. The referenced record was deleted

Let’s allow deleting clients that have invoices. In this case the invoices should be deleted as well. You can do this by extending the FOREIGN KEY

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE invoices
(
    id         INT AUTO_INCREMENT PRIMARY KEY,
    client_id  INT      NOT NULL,
    total      DOUBLE   NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    paid_at    DATETIME,

    FOREIGN KEY (client_id) REFERENCES clients (id) ON DELETE CASCADE 
);

As you can see, you now react to deletion of the referenced record and “cascade” it. This means, you also delete all invoices, that reference this client.

mysql> DELETE FROM clients WHERE id=1;
Query OK, 1 row affected (0.005 sec)

mysql> SELECT * FROM clients;
+----+------+---------------------+------------+
| id | name | email               | phone      |
+----+------+---------------------+------------+
|  2 | Golo | golo@reliable.codes | 0123456789 |
+----+------+---------------------+------------+
1 row in set (0.001 sec)

mysql> SELECT * FROM invoices;
Empty set (0.001 sec)

This is a neat feature, but be careful: There’s no confirmation dialog or any feedback. You can find yourself in situations where you link multiple tables together, if you ON DELETE CASCADE all of them, deleting “just one row” can end up deleting hundrets of rows across multiple tables.

Run queries against linked data #

Let’s restore Tris to our list of clients and add two invoices, one of them marked as paid.

mysql> INSERT INTO clients(id, name, email, phone) VALUES (1, "Tris", "tris@reliable.codes", NULL);
Query OK, 1 row affected (0.004 sec)

mysql> INSERT INTO invoices(client_id, total, paid_at) VALUES (1, 13.37, NULL), (2, 26.74, CURRENT_TIMESTAMP), (1, 40.11, NULL);
Query OK, 3 rows affected (0.006 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM invoices;
+----+-----------+-------+---------------------+---------------------+
| id | client_id | total | created_at          | paid_at             |
+----+-----------+-------+---------------------+---------------------+
|  4 |         1 | 13.37 | 2025-05-19 05:42:35 | NULL                |
|  5 |         2 | 26.74 | 2025-05-19 05:42:35 | 2025-05-19 05:42:35 |
|  6 |         1 | 40.11 | 2025-05-19 05:42:35 | NULL                |
+----+-----------+-------+---------------------+---------------------+
2 rows in set (0.001 sec)

Now, let’s find the list of clients that you should reach out for payment. Here’s the plan:

1. Show name, email, phone and the total outstanding amount of open invoices of the clients
2. which have invoices where paid_at is NULL
3. but don’t show the same client twice
4. and SORT BY total outstanding amount desc, so that we call first the client with highest open invoice amount

1
2
3
4
5
6
7

SELECT name, email, phone, sum(invoices.total) as `open_total`
FROM clients
         LEFT JOIN invoices on clients.id = invoices.client_id
WHERE
    invoices.paid_at IS NULL
GROUP BY clients.id
ORDER BY open_total DESC

Let me explain:

• I do SELECT as described before. I gave an alias to the SUM, so the output is better to read.
• I read from clients but I mix it up with invoices using LEFT JOIN. It’s worth mentioning that you can join tables on any field, not just PRIMARY KEY, UNIQUE or FOREIGN KEY.
• I filter down to client-invoice combinations where the invoice is not paid yet
• Then I group by the id of the client, because
  • I don’t want to see the same client twice
  • SUM can just work if you have GROUP BY
• Now I can order by the SUM

mysql> SELECT name, email, phone, sum(invoices.total) as `open_total` FROM clients LEFT JOIN invoices on clients.id = invoices.client_id WHERE invoices.paid_at IS NULL GROUP BY clients.id ORDER BY open_total DESC;
+------+---------------------+-------+------------+
| name | email               | phone | open_total |
+------+---------------------+-------+------------+
| Tris | tris@reliable.codes | NULL  |      53.48 |
+------+---------------------+-------+------------+
1 row in set (0.001 sec)

As you can see now I have the list I was looking for and MySQL again was lightning fast.

Example: Online shop #

To wrap it up, let’s run through a possible MySQL structure for an online shop. It will not be suitable for production usage, but it’s enough to wrap up the article.

Table structure #

First let’s layout our tables. Before jumping into SQL, let’s draw it down:

You have Users that can place Orders. You have Products that can be ordered. You have OrderItems to track which Product is in which Order how often and what it sold for. Those Orders can have Payments and for sure all OrderItems must be shipped so we have Shipments. To keep track of what was shipped and what not, we add ShipmentItems.

Products #

For Products I guess we’re good to go with these fields:
sku: Stock keeping unit, common information in eCommerce. It’s UNIQUE to prevent double use.
name: The name/title of the product.
price: The price for one item of this product.
stock: How much of this product is in stock.

1
2
3
4
5
6
7
8

CREATE TABLE products
(
  id    INT PRIMARY KEY AUTO_INCREMENT,
  sku   VARCHAR(10)  NOT NULL UNIQUE,
  name  VARCHAR(200) NOT NULL,
  price DOUBLE       NOT NULL,
  stock DOUBLE
);

Users #

For Users I guess we’re good to go with just their email.

1
2
3
4
5

CREATE TABLE users
(
  id    INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(256) NOT NULL UNIQUE
);

Orders #

For Orders I guess we’re good to go with these fields:
user_id: Reference to the User, that did order.
created_at: When the order was created, pre-filled by MySQL.
fully_shipped: Whether or not this order was fully shipped.
total: The orders total amount.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE orders
(
  id            INT PRIMARY KEY AUTO_INCREMENT,
  user_id       INT      NOT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  fully_shipped BOOL     NOT NULL DEFAULT FALSE,
  total         DOUBLE   NOT NULL,

  FOREIGN KEY (user_id) REFERENCES users (id)
);

OrderItems #

For OrderItems I guess we’re good to go with these fields:
order_id: Reference to the Order, where this item was ordered in.
product_sku: This is a “soft reference” to the Product. I did not use a MySQL reference, because maybe the Product will be deleted at some point.
quantity: How often this item was ordered.
item_price: The price per item.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE order_items
(
  id          INT PRIMARY KEY AUTO_INCREMENT,
  order_id    INT         NOT NULL,
  product_sku VARCHAR(10) NOT NULL,
  quantity    DOUBLE      NOT NULL,
  item_price  DOUBLE      NOT NULL,

  FOREIGN KEY (order_id) REFERENCES orders (id)
);

Payments #

For Payments I guess we’re good to go with these fields:
order_id: Reference to the Order, where this item was ordered in.
payment_amount: How much was paid.
payment_method: How it was paid.

1
2
3
4
5
6
7
8
9

CREATE TABLE payments
(
  id             INT PRIMARY KEY AUTO_INCREMENT,
  order_id       INT         NOT NULL,
  payment_amount DOUBLE      NOT NULL,
  payment_method VARCHAR(50) NOT NULL,

  FOREIGN KEY (order_id) REFERENCES orders (id)
);

Shipments #

For Shipments I guess we’re good to go with these fields:
created_at: When the shipment was created, pre-filled by MySQL.
order_id: Reference to the Order, where this shipment is for.
tracking_id: Stores the package id of the carrier.

1
2
3
4
5
6
7
8
9

CREATE TABLE shipments
(
  id          INT PRIMARY KEY AUTO_INCREMENT,
  created_at  DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  order_id    INT         NOT NULL,
  tracking_id VARCHAR(30) NOT NULL,

  FOREIGN KEY (order_id) REFERENCES orders (id)
);

ShipmentItems #

For ShipmentItems I guess we’re good to go with these fields:
order_id: Reference to the Order.
order_item_id: Reference to the OrderItem, that is shipped.
quantity: How many of this items are shipped.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE shipment_items
(
  id            INT PRIMARY KEY AUTO_INCREMENT,
  order_id      INT    NOT NULL,
  order_item_id INT    NOT NULL,
  quantity      DOUBLE NOT NULL CHECK (quantity > 0),

  FOREIGN KEY (order_id) REFERENCES orders (id),
  FOREIGN KEY (order_item_id) REFERENCES order_items (id)
);

Example queries #

This structure in place, let’s simulate one order throughout the entire process, also with some analytics queries.

Creating users #

If new Users do register, you just need their email, this is quiet straight-forward:

1
2

INSERT INTO users(email) 
    VALUE ('user@reliable.codes');

Creating products #

If new Products are created, this is straight-forward as well:

1
2

INSERT INTO products(sku, name, price, stock) 
    VALUE ('product1', 'Some nice product', 9.99, 5);

Restock products #

Re-stocking Products (add more stock) can be as easy as this:

1
2
3
4

UPDATE products 
SET stock = stock + 5
WHERE
    id = 1;

Deleting products #

Deleting Products, in case you don’t want to sell them any longer, would be like this:

1
2
3

DELETE FROM products 
WHERE
    id = 1;

Adding orders #

Adding Orders is a sequence of queries.

1. You need to add to Orders
2. You need to add to OrderItems
3. You need to decrease the Products stock

1
2
3
4
5
6
7
8
9

INSERT INTO orders(user_id, total)
    VALUE (1, 9.99);

INSERT INTO order_items(order_id, product_sku, quantity, item_price)
    VALUE (1, 'product1', 1, 9.99);

UPDATE products
SET stock = stock - 1
WHERE id = 1;

Now if you read Orders and OrderItems you can find the orders:

mysql> SELECT * FROM orders;
+----+---------+---------------------+---------------+-------+
| id | user_id | created_at          | fully_shipped | total |
+----+---------+---------------------+---------------+-------+
|  1 |       1 | 2025-05-19 17:18:33 |             0 |  9.99 |
+----+---------+---------------------+---------------+-------+
1 row in set (0.002 sec)

mysql> SELECT * FROM order_items;
+----+----------+-------------+----------+------------+
| id | order_id | product_sku | quantity | item_price |
+----+----------+-------------+----------+------------+
|  1 |        1 | product1    |        1 |       9.99 |
+----+----------+-------------+----------+------------+
1 row in set (0.001 sec)

Adding payments #

Once the customer did pay, you can add to Payments and likely also update the Order:

1
2

INSERT INTO payments(order_id, payment_amount, payment_method)
    VALUE (1, 9.99, 'PayPal');

Adding shipments #

Now, after customer did pay, you probably want to ship the OrderItems. Again, this is multiple queries:

1. You need to add to Shipments
2. You need to add to ShipmentItems
3. You need to update the Order

1
2
3
4
5
6
7
8
9

INSERT INTO shipments(order_id, tracking_id)
    VALUE (1, 'ABCDEF');

INSERT INTO shipment_items(order_id, order_item_id, quantity)
    VALUE (1, 1, 1);

UPDATE orders
SET fully_shipped = 1
WHERE id = 1;

Finally, let’s verify the three tables:

mysql> SELECT * FROM shipments;
+----+---------------------+----------+-------------+
| id | created_at          | order_id | tracking_id |
+----+---------------------+----------+-------------+
|  1 | 2025-05-19 17:25:04 |        1 | ABCDEF      |
+----+---------------------+----------+-------------+
1 row in set (0.001 sec)

mysql> SELECT * FROM shipment_items;
+----+----------+---------------+----------+
| id | order_id | order_item_id | quantity |
+----+----------+---------------+----------+
|  1 |        1 |             1 |        1 |
+----+----------+---------------+----------+
1 row in set (0.001 sec)

mysql> SELECT * FROM orders;
+----+---------+---------------------+---------------+-------+
| id | user_id | created_at          | fully_shipped | total |
+----+---------+---------------------+---------------+-------+
|  1 |       1 | 2025-05-19 17:18:33 |             1 |  9.99 |
+----+---------+---------------------+---------------+-------+
1 row in set (0.001 sec)

Finding orders with outstanding payments #

This is somewhat more complex. You can solve it better, but I just wanted to showcase this. You will let MySQL find Orders where the SUM of their Payments does not match their total.

You cannot use WHERE here, since you first need to run the math, and then just drop what you don’t want to have. So better don’t use it like this in production (performance).

This “dropping” you can do using HAVING instead of WHERE. Note: It must be placed after GROUP BY in this case.

1
2
3
4
5
6

SELECT orders.id, SUM(payments.payment_amount) AS `amount_paid`
FROM orders
         LEFT JOIN payments ON orders.id = payments.order_id
GROUP BY orders.id
HAVING SUM(payments.payment_amount) != AVG(orders.total)
ORDER BY MIN(orders.created_at) ASC;

List products with all their sales #

Let’s have a list of Products and their total amount of sold items, ordered by the top-sellers:

1
2
3
4
5

SELECT products.name, SUM(order_items.quantity) as `items_sold`
FROM products
         LEFT JOIN order_items ON products.sku = order_items.product_sku
GROUP BY products.sku
ORDER BY items_sold DESC;

Now let’s see it in action:

mysql> SELECT products.name, SUM(order_items.quantity) as `items_sold` FROM products LEFT JOIN order_items ON products.sku = order_items.product_sku GROUP BY products.id ORDER BY items_sold DESC;
+-------------------+------------+
| name              | items_sold |
+-------------------+------------+
| Some nice product |          1 |
+-------------------+------------+
1 row in set (0.002 sec)

Calculate all earnings per month #

And since eCommerce is about money, let’s see what you earn per month:

1
2
3
4

SELECT DATE_FORMAT(created_at, '%Y-%m') as `month`, SUM(total) as `revenue`
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY month DESC;

And there we go with our revenue per month:

mysql> SELECT DATE_FORMAT(created_at, '%Y-%m') as `month`, SUM(total) as `revenue` FROM orders GROUP BY DATE_FORMAT(created_at, '%Y-%m') ORDER BY month DESC;
+---------+---------+
| month   | revenue |
+---------+---------+
| 2025-05 |    9.99 |
+---------+---------+
1 row in set (0.000 sec)

Now you know MySQL #

Congratulations, if you worked through the whole article. Now you have a good starting point to work with MySQL databases. And to be honest, many of the concepts and also the pattern you can apply to more databases than just MySQL.