What Is MySQL? Relational Databases for Web Beginners

Tables, columns and keys

A table holds one kind of thing, such as customers or orders. Each column has a name and a data type, and each row is one record. A primary key uniquely identifies every row, and a foreign key stores the primary key of a row in another table, which is how relationships are expressed.

CREATE TABLE customers (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  email      VARCHAR(255) NOT NULL UNIQUE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE orders (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  total       DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

InnoDB is the default storage engine in MySQL 8 and supports transactions and foreign keys. utf8mb4 is the default character set and stores every Unicode character, including emoji. Use DECIMAL for money, because floating-point types introduce rounding errors.

The four basic operations

Almost every web feature comes down to creating, reading, updating or deleting rows, often shortened to CRUD.

INSERT INTO customers (name, email) VALUES ('Ravi', '[email protected]');

SELECT id, name FROM customers WHERE email = '[email protected]';

UPDATE customers SET name = 'Ravi Kumar' WHERE id = 1;

DELETE FROM customers WHERE id = 1;

In a real application these values come from visitors, so you never type them into the SQL string. You send them separately as parameters, as shown in our PHP guide to prepared statements.

Combining tables with JOIN

Because customers and orders live in separate tables, you need a JOIN to see them together. The query below lists each customer with how many orders they have placed and how much they have spent.

SELECT c.name, COUNT(o.id) AS orders, COALESCE(SUM(o.total), 0) AS spent
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY spent DESC;

The ON clause states how rows match. A LEFT JOIN keeps customers who have no orders yet, and COALESCE turns their empty total into zero. GROUP BY collapses each customer's orders into one summary row.

Indexes and query speed

An index works like the index at the back of a book: MySQL can jump straight to matching rows instead of reading the whole table. Primary keys and UNIQUE columns are indexed automatically. Add indexes to columns you often filter, join or sort on.

CREATE INDEX idx_orders_customer ON orders (customer_id);
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

EXPLAIN shows whether a query uses an index. Indexes speed up reads but slightly slow down writes and take disk space, so add them where queries need them rather than on every column.

Designing a schema that stays clean

• Store each fact in one place. If a customer's email appears in five tables, one copy will eventually be wrong.
• Give every table a primary key, and name foreign key columns clearly, such as customer_id.
• Choose the smallest data type that fits, and use NOT NULL wherever a value is always required.
• Use transactions when several statements must succeed or fail together, such as creating an order and reducing stock.

This process of splitting data into related tables is called normalisation. Sketching your tables and their relationships on paper before writing any SQL prevents painful changes later.

Security and backups

• Create a separate database user for each application and grant only the permissions it needs, never the root account.
• Keep the database server off the public internet; your application should connect over localhost or a private network.
• Take regular backups with mysqldump or your host's backup tool, and test that you can restore them.
• Store passwords only as hashes created by your application, never in a readable column.

MariaDB, a community fork of MySQL, is largely compatible for everyday queries, and most hosting control panels offer one or the other. Database design and SQL are covered with hands-on projects in the SkillNewton online web development course.

Common mistakes

• Building SQL strings from user input instead of using prepared statements with placeholders.
• Running UPDATE or DELETE without a WHERE clause and changing every row.
• Storing money in FLOAT columns, which causes rounding errors in totals.
• Using SELECT * everywhere, which fetches columns you do not need and breaks code when the table changes.
• Connecting the application as the root user, which gives an attacker full control if the application is compromised.

Frequently asked questions