One database, one table, built up step by step. We create it, shape it, back it up, sort it and count it, then take a plain-English, hands-on look at SQL injection.
This is the full written version of an interactive, slide-by-slide lesson deck used in teaching. Everything covered in the deck is below — open the interactive version to work through it with live diagrams, worked examples and a practice quiz where included.
Before we can make any tables, we need somewhere to keep them. CREATE DATABASE tells MySQL to start a fresh, empty space with the name we give it. We run this in MySQL Workbench, then click the refresh icon on the Schemas panel to see our new bookshop appear.
Create a database called bookshop in MySQL Workbench. Then double-click it in the Schemas panel so it becomes your active database (its name turns bold).
CREATE DATABASE bookshop;A table is just a grid of rows and columns, a bit like a spreadsheet with rules. Every column needs a name and a data type. We use INT for whole numbers, VARCHAR(100) for short text (100 is the longest it can hold), and DECIMAL for money. For now we make price an INT on purpose, and we fix that in step 4.
Inside bookshop, create a table called books with four columns: id (INT), title (VARCHAR 100), author (VARCHAR 100) and price (INT).
USE bookshop;
CREATE TABLE books (
id INT,
title VARCHAR(100),
author VARCHAR(100),
price INT
);Tables are not set in stone. With ALTER TABLE ADD COLUMN we can add a new field at any time, and none of the data we already have is lost. Let us say the shop now wants to keep track of how many copies of each book are in stock.
ALTER TABLE books ADD COLUMN stock_count INT;Right now price is an INT, so it can only hold whole numbers. But a book costs $19.99, not $19. MODIFY COLUMN lets us change the type of a column we already have. DECIMAL(6,2) means up to 6 digits in total, with 2 of them after the decimal point, which is perfect for prices.
ALTER TABLE books MODIFY COLUMN price DECIMAL(6,2);A primary key is the column that gives every row its own identity. No two rows can share the same value, and it can never be left blank. id is the obvious choice here, because every book gets its own number and nothing else has to be unique.
ALTER TABLE books ADD PRIMARY KEY (id);Typing an id by hand for every new book is slow and easy to get wrong. AUTO_INCREMENT asks MySQL to do the counting for us. When we add a book without giving an id, MySQL fills in the next free number by itself (1, 2, 3 and so on). In MySQL a column has to be a key before it can auto-increment, which is why we did step 5 first.
Make id AUTO_INCREMENT, then add a new book without giving it an id. Leave id out of the column list and watch MySQL fill in the number for you.
ALTER TABLE books MODIFY COLUMN id INT AUTO_INCREMENT;ALTER TABLE books ADD COLUMN stock_count INT;ALTER TABLE books ADD COLUMN pages INT AFTER title;ALTER TABLE books ADD COLUMN sku INT FIRST;A foreign key is a column that points at the primary key of another table, which is how two tables get linked. It also enforces referential integrity: the database refuses to store a row pointing at a record that does not exist, and can stop you deleting a record other rows still depend on.
Never build a query by concatenating user input into a string. Use parameterised queries or prepared statements so the input is always treated as a value, never as executable SQL. Validating input and limiting the database account's permissions reduce the damage if something does get through, but parameterisation is the actual fix.
The interactive deck adds live diagrams, step-by-step reveals and practice activities that this written version can't carry.