What MySQL is and how it works

Why databases exist

Every useful application remembers things: customers, orders, posts, scores. You could keep that information in text files, but the moment two people use the app at once, or you need to find every order from last March, files stop working. A database is software built for exactly this job. It stores data safely on disk, lets many users read and write at the same time, finds records quickly and refuses to save data that breaks your rules.

MySQL is one of the most widely used databases in the world. It runs behind WordPress sites, Laravel and PHP applications, and countless business systems. It is open source, free to use, and fast enough for small projects and very large ones alike.

The relational model

MySQL is a relational database. That means data lives in tables, and tables can be linked to each other.

A table looks like a spreadsheet:

• A column has a name and a data type, such as email (text) or price (a decimal number). Columns describe the shape of the data.
• A row is one record, for example one customer. Every row has a value for every column, although that value can be NULL ("unknown").
• A primary key is a column (or a few columns) whose value is different in every row, so each record can be identified exactly.

The "relation" part is what makes this powerful. Instead of copying a customer's address onto every order, you store the customer once and each order stores the customer's id. To see an order together with its customer, you join the two tables at query time. You will do this in the joins section.

customers                         orders
id | first_name | city           id | customer_id | order_date
1  | Asha       | Delhi          1  | 1           | 2024-01-05
2  | Ben        | London         3  | 2           | 2024-01-20

Here orders.customer_id points to customers.id. That link is called a foreign key relationship.

Server, client and SQL

Three pieces work together:

1. The server (mysqld) is a program that runs in the background. It owns the data files, enforces rules and executes your requests.
2. A client connects to the server over the network (or a local socket). The mysql command-line program, MySQL Workbench and your PHP, Python or Node application are all clients.
3. SQL (Structured Query Language) is the language a client sends. It is a standard shared by most databases. MySQL adds some extras of its own, which this course marks when they come up.

When you type a statement, the client sends the text to the server, the server works out the fastest way to answer it, and sends back a result set: a small table of rows.

SELECT 2 + 3 AS total;

+-------+
| total |
+-------+
|     5 |
+-------+

That query does not touch any table. It only asks the server to calculate. Once you have installed MySQL and loaded the sample database, queries like this one will read real data:

SELECT first_name, city FROM customers LIMIT 3;

+------------+--------+
| first_name | city   |
+------------+--------+
| Asha       | Delhi  |
| Ben        | London |
| Chloe      | Paris  |
+------------+--------+

The kinds of SQL statement

SQL statements are usually grouped by what they do. The names are worth knowing because documentation and job interviews use them.

This course follows roughly that order: you read data first, then change it, then learn to design tables, and finally cover speed, safety and advanced features.

MySQL, MariaDB and PostgreSQL

MySQL is owned by Oracle and developed in the open. MariaDB began as a community fork of MySQL and still behaves very similarly for everyday work. PostgreSQL is a separate project with a different history and some different syntax. Everything in the first sections of this course (SELECT, joins, grouping) works almost unchanged on all of them, so the skills carry over.

The examples in this course were run on MySQL 8.4 LTS, a long-term support release. Where a feature needs MySQL 8.0 or newer, such as window functions, the lesson says so.

What you need

A computer you can install software on and a little patience. You do not need to know programming. In the next lesson you install the server, then you connect to it and load a small shop database that every later lesson reuses.

Common mistakes

• Thinking MySQL is the same thing as SQL. SQL is the language; MySQL is one server that understands it.
• Treating a table like a spreadsheet you can sort by hand. Rows in a table have no guaranteed order unless your query asks for one.
• Storing the same fact in many places. Copying a customer's address onto every order makes updates painful. Store it once and link to it.
• Forgetting that the server must be running. If a client cannot connect, the first thing to check is whether the server process is up.

Exercise

Without running anything, answer these about a library system that has a books table and a members table:

1. Name three columns the books table might have, and say which could be the primary key.
2. A member borrows a book. Which table would you add so the link is stored once, and which two foreign keys would it hold?
3. Is INSERT a query, DML or DDL?