MySQL: The Queen of Databases

How can something be so complicated yet so easy?

When we talked about database models, we touched upon relational databases. Here, we’ll take a look at one of the best-known examples: MySQL.

⚠️ Strictly speaking, MySQL is a database management system (DBMS), not a database. But people often refer to DBMSs as “databases,” so don’t be surprised when you hear them referred to as such.

What is MySQL?

We know that MySQL is used to manage relational databases, but what exactly does that mean? Relational databases contain data that can be grouped, listed, or queried together because they have something in common (often a key or identifier of some kind).

Essentially, a MySQL database is a lot of tables that are somehow related to each other. For example, we could have the following interrelated tables: one for products, another for purchases, and a third for customers. Here’s an image you would’ve seen if you’ve read our overview of database models:

Our database consists of three tables, and the related data from the top two is visible in the third. If we ever need to store data on product delivery, we’ll be able to do so in one of these tables (or in a new one).

👉Ultimately, all we do in MySQL is create tables containing data and set up relationships between them.

Is MySQL for me?

Who needs to know MySQL? Anyone and everyone who develops web applications or sites. That’s not an exaggeration! If an app or site features any type of user accounts, or if it stores any data, then it will almost certainly use MySQL.

You might pass over this particular management system and go with, say, PostgreSQL or NoSQL. Or you might simply store the data on the client side or in a .csv or JSON file. But if you want a system that will be easy to maintain and to send to others for improvement and development, MySQL is the obvious pick.

Types of database relationships

One-to-one. The simplest type of relationship. Here, one record from one table can only correspond to one record from another table. For example, if we make a new table containing customer photos, then one customer will correspond to one photo, and vice versa.

One-to-many. Imagine we have one table for all customers and another for all their purchases. Then we’re dealing with a one-to-many relationship, because each customer can have multiple purchases.

The situation is similar with artists and paintings (if we ignore cases where artists collaborate). Each painting can belong to only one artist, but one artist can have multiple paintings.

Many-to-many. This one is tricky: it requires a separate table. We make an “intermediate” table, in which we combine data from one table with data from another. As a result, this new table has no data — it only contains relationships between records.

An example will make this easier to grasp. Let’s say you keep your to-do list in a day planner and you have various labels for tasks. The labels might look like this: “errand,” “kids,” “housekeeping,” “work.” You can assign any number of labels to any task. Thus:

• Each label can be assigned to multiple tasks.
• Each task can have multiple labels.

We can select a label and see which task it’s assigned to, or we can select a task and see which labels it has. Multiple labels can be associated with each task, and vice versa.

What can be stored in MySQL

Anything can be stored in MySQL databases, as long as you’re able to set up the relationships between the data. You can store notes, photos, music, to-do lists, or yearly goals in a MySQL database. The key is to understand how the data will actually be used.

For instance, a blog service can use a MySQL database to store posts. The database will contain the posts themselves, as well as metadata about the authors, images, publication dates, and so on. When you want to read a particular post, the site accesses the database, retrieves the article, processes it, and shows it to you.

Businesses, medical clinics, and inventory systems also use MySQL to maintain customer databases.

The database doesn’t care what kind of data it stores or how you use it. A database is simply a way to link data together and then retrieve what you need.

Why MySQL is so popular

Since it first appeared in 1995, MySQL has been known as a free, simple, and predictable database management system. This has resulted in it being used by many companies around the world, making it the unspoken standard for database management systems.

Moreover, MySQL has built-in security and access control systems. For example, you can adjust settings so that a low-level employee can only enter data, the head of the department can change it but not delete it, and the director can do anything they like.

But the main reason MySQL is popular is its full support for Structured Query Language — that is, SQL.

What is SQL?

SQL (pronounced both “sequel” and “S-Q-L”) is a special programming language for working with databases. “Structured” means that each query must have a specific structure so that the database understands how to respond to it.

Queries are entered in a special terminal that is responsible for managing the database.

We can use queries to do all kinds of things:

• Create and modify tables
• Set up relationships between tables
• Add and delete data
• Configure access for different users
• And, most importantly, search for what we need, according to various parameters

If you know SQL, you can work with any relational database that supports it.

Could you show me some queries?

Sure!

Let’s create a database called PRACTICUM_MEDIA:

CREATE DATABASE PRACTICUM_MEDIA;

Next, we need to run a command saying we’ll continue working with this particular database, i.e. select it:
USE PRACTICUM_MEDIA;

Now let’s create a table with article titles, authors, and monthly reads:
CREATE TABLE STATS (name VARCHAR(200), author VARCHAR(20), readers INT);

Load some pre-prepared data from the file into the table like this:
LOAD DATA LOCAL INFILE 'practicum/readers_stat.txt' INTO TABLE STATS;

And finally, display the data on the screen:
SELECT * FROM STATS;

This is, of course, a very simple example, but it’s just the tip of the iceberg. MySQL allows you to perform all kinds of database manipulations.

Recap

1. MySQL is a relational database management system. “Relational” means there are unambiguous relationships between databases. You can think of it as a management system for table-based databases.
2. Tables can be linked together to make it easier to find the information you need.
3. To work with relational databases, a special language is used: SQL.
4. Each command in SQL is a query to the database to find, change, add, or remove something.
5. MySQL is a trusted and reliable system that’s been in use for over 25 years.

Coming up next

Soon we’ll be discussing NoSQL and non-relational databases, like Firebase. So prepare yourself! This will be a bit of a departure from what we’ve discussed so far.

And if you found this article useful, visit Practicum and take a free course where you can learn more about developing the skills you need to take your professional skills to the next level.