Introduction to databases & MySQL

A database is an organised collection of data that a program can store, search and update safely, even when thousands of people use it at once. MySQL is the world's most popular open-source relational database. It powers WordPress, huge parts of Facebook, Booking.com, GitHub and countless small apps. You talk to it in SQL (Structured Query Language), a language that has been around since the 1970s and is still one of the most valuable skills in tech.

In this course you will go from "what is a table?" all the way to transactions, indexes, stored procedures and replication. This first lesson gives you the mental model everything else builds on.

What "relational" means#

A relational database stores data in tables. Think of a spreadsheet:

• The whole grid is a table (here: users).
• Each horizontal line is a row (also called a record): one user.
• Each vertical line is a column (a field) with a name and a data type: id is an integer, name is text.

Unlike a spreadsheet, a database is strict. Every value in a column must match the column's type, and rules (called constraints) can stop bad data from ever getting in.

Keys: how tables relate#

• A primary key uniquely identifies each row. It is usually a single column like id. No two rows can share the same primary key value, and it can never be NULL (empty).
• A foreign key is a column in one table that points at the primary key of another. That is how tables relate. For example, orders.user_id refers to users.id, so every order belongs to exactly one user.

users                        orders
+----+--------------+        +-----+---------+--------+
| id | name         |        | id  | user_id | total  |
+----+--------------+        +-----+---------+--------+
|  1 | Ada Lovelace | <----- | 101 |       1 |  49.99 |
|  2 | Alan Turing  | <----- | 102 |       2 |  15.00 |
+----+--------------+   \--- | 103 |       1 |   8.50 |
                             +-----+---------+--------+

Ada's name is stored once, in users. Her two orders simply refer to her id. Change her name in one place and every order still points at the right person. Avoiding duplicated facts like this is the heart of relational design. You will study it properly in the Normalisation & schema design lesson.

SQL in one minute#

SQL statements read almost like English and end with a semicolon. They fall into a few families:

Keywords are not case-sensitive (select works the same as SELECT), but writing them in capitals makes queries easier to read. Whether table names are case-sensitive depends on the operating system, so pick one style (lowercase with underscores, like order_items) and stick to it.

Talking to MySQL#

MySQL is a client–server system. The server (mysqld) runs in the background and owns the data files. You connect to it with a client: the mysql command-line tool, a GUI such as MySQL Workbench or DBeaver, or your application's database driver. The next lesson shows how to install everything. Once it is installed, connect like this:

mysql -u root -p

-u is the user name and -p asks for the password. Then try some starter commands:

SHOW DATABASES;
CREATE DATABASE shop;
USE shop;
SELECT VERSION();

USE shop; tells MySQL which database the following statements apply to. SELECT VERSION(); shows which server you are running. This course uses MySQL 8 (8.0 or 8.4 LTS).

Creating your first table#

CREATE TABLE users (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    email      VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Reading that line by line:

• INT AUTO_INCREMENT PRIMARY KEY: an integer id that MySQL fills in automatically (1, 2, 3, …) and that uniquely identifies each row.
• VARCHAR(100): variable-length text, up to 100 characters.
• NOT NULL: the column must always have a value.
• UNIQUE: no two rows may share the same email.
• DEFAULT CURRENT_TIMESTAMP: fills in the current date and time if you don't give one.

Check what you built:

DESCRIBE users;

+------------+--------------+------+-----+-------------------+-------------------+
| Field      | Type         | Null | Key | Default           | Extra             |
+------------+--------------+------+-----+-------------------+-------------------+
| id         | int          | NO   | PRI | NULL              | auto_increment    |
| name       | varchar(100) | NO   |     | NULL              |                   |
| email      | varchar(255) | NO   | UNI | NULL              |                   |
| created_at | timestamp    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+--------------+------+-----+-------------------+-------------------+

Inserting and reading rows#

INSERT INTO users (name, email) VALUES
    ('Ada Lovelace', 'ada@example.com'),
    ('Alan Turing',  'alan@example.com'),
    ('Grace Hopper', 'grace@example.com');

We leave out id and created_at and MySQL fills them in for us. Now read the data back, this time choosing the columns we want:

SELECT id, name, email FROM users;

+----+--------------+-------------------+
| id | name         | email             |
+----+--------------+-------------------+
|  1 | Ada Lovelace | ada@example.com   |
|  2 | Alan Turing  | alan@example.com  |
|  3 | Grace Hopper | grace@example.com |
+----+--------------+-------------------+

The database protects you too. Try inserting a duplicate email:

INSERT INTO users (name, email) VALUES ('Imposter', 'ada@example.com');

ERROR 1062 (23000): Duplicate entry 'ada@example.com' for key 'users.email'

The UNIQUE rule rejected the row. A spreadsheet would have happily accepted it.

Common beginner mistakes#

• Forgetting the semicolon. The mysql client waits for ; before it runs anything. If you see a -> prompt, type ; and press Enter.
• Using the wrong quotes. Text values go in single quotes: 'Ada'. Backticks (`order`) are only for quoting identifiers such as table or column names, and double quotes are best avoided.
• Working in the wrong database. If MySQL says No database selected, run USE shop; first, or write shop.users.

Why relational databases last#

Relational databases give you structure (types and constraints), relationships (keys), a powerful query language (SQL) and transactions that keep data consistent even when the power fails. That combination is why MySQL, PostgreSQL, SQL Server and Oracle still run most of the world's business data.

What's next#

Next you will install MySQL 8 and its clients on your own machine, so you can run every example in this course yourself.