Guide 02 / 4
MySQL essentials
An annotated cheat sheet: what each element is for, how to write it and when to use it.
Choose and use the essential elements to complete this step of the blog.
01 · Connecting and defining the model
Terminal commands are not pasted into the SQL editor; SQL queries are not pasted directly into the shell.
mysql client and selecting the database
In the terminal, open the client with your account. At the mysql> prompt, select the database then look at the tables. -p asks for the password without writing it in the command.
Example
Terminal : mysql -u votre_compte -p
SQL : USE blog_recettes;
SQL : SHOW TABLES;
SQL : DESCRIBE recettes;
The “Terminal:” and “SQL:” prefixes are explanations, not part of the commands.
CREATE DATABASE and CREATE TABLE
First create the database, then the parent table categories, then recettes which references it. The complete database/schema.sql file is at the end of the workshop.
Example
CREATE TABLE categories (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
nom VARCHAR(60) NOT NULL UNIQUE
) ENGINE=InnoDB;
Mini-blog types
VARCHAR suits short titles; TEXT suits ingredients and steps; SMALLINT and TINYINT suit small integer values; TIMESTAMP suits the creation date.
Example
titre VARCHAR(120)
ingredients TEXT
duree_minutes SMALLINT UNSIGNED
portions TINYINT UNSIGNED
cree_le TIMESTAMP DEFAULT CURRENT_TIMESTAMP
For this first project, ingredients and steps are kept as lines of text; their detailed modelling will come in a more advanced course.
NOT NULL, UNIQUE, CHECK and FOREIGN KEY
NOT NULL requires a value; UNIQUE prevents duplicates of a value; CHECK imposes a condition; FOREIGN KEY checks a relationship. The two linked columns must have compatible types.
Example
CONSTRAINT ck_duree CHECK (duree_minutes BETWEEN 1 AND 1440),
CONSTRAINT fk_categorie FOREIGN KEY (categorie_id)
REFERENCES categories(id) ON DELETE RESTRICT
ON DELETE RESTRICT refuses to delete a category that is still in use.
02 · Inserting and querying
Listing the columns makes queries readable and independent of their physical order.
INSERT INTO and VALUES
INSERT adds a row. Strings use apostrophes; an inner apostrophe is doubled. The engine produces the id if you omit it.
Example
INSERT INTO categories (nom) VALUES ('Goûter');
INSERT INTO categories (nom) VALUES ('Chef d''un jour');
In the PDO course's PHP code, values will be passed as parameters rather than building the SQL strings.
SELECT, FROM and aliases
SELECT chooses the columns; FROM indicates the table. An alias temporarily names a table or a column in the result.
Example
SELECT r.id, r.titre, r.duree_minutes AS duree
FROM recettes AS r;
WHERE, AND and OR
WHERE selects rows according to a condition. AND requires both criteria. Adding parentheses makes combinations with OR explicit.
Example
SELECT id, titre FROM recettes
WHERE duree_minutes <= 20 AND portions = 2;
Expected on the initial data: soupe and salade.
LIKE and wildcards
LIKE looks for a pattern: % stands for zero or more characters; _ stands for exactly one. Case and accent sensitivity depend on the collation.
Example
SELECT titre FROM recettes WHERE titre LIKE '%pommes%';
Expected: Tarte aux pommes. The symbols % and _ have a special meaning even when the pattern is passed through a PDO parameter.
ORDER BY and LIMIT
ORDER BY sorts the results. LIMIT reduces their number. Adding an id to the sort breaks ties for a stable result.
Example
SELECT id, titre, duree_minutes FROM recettes
ORDER BY duree_minutes ASC, id ASC
LIMIT 2;
Expected: salade (10), then soupe (20). Without ORDER BY, no display order is guaranteed.
JOIN and the ON condition
JOIN links each recipe to its category. The aliases r and c shorten the query and remove ambiguity between the id columns.
Example
SELECT r.titre, c.nom AS categorie
FROM recettes AS r
JOIN categories AS c ON c.id = r.categorie_id
ORDER BY r.id;
COUNT and GROUP BY
COUNT(*) counts rows. GROUP BY groups the rows that share a value to compute a total per group.
Example
SELECT c.nom, COUNT(r.id) AS total
FROM categories AS c
LEFT JOIN recettes AS r ON r.categorie_id = c.id
GROUP BY c.id, c.nom
ORDER BY c.id;
LEFT JOIN also keeps a category with no recipe; COUNT(r.id) is then 0, unlike COUNT(*).
03 · Modifying with a clear scope
Practise only in the mini-blog's database.
UPDATE with WHERE
UPDATE modifies the selected rows. First run a SELECT with the same WHERE to check the scope.
Example
SELECT id, titre, duree_minutes FROM recettes WHERE id = 1;
START TRANSACTION;
UPDATE recettes SET duree_minutes = 25 WHERE id = 1;
SELECT id, titre, duree_minutes FROM recettes WHERE id = 1;
ROLLBACK;
Expected: the duration goes back to 20 after ROLLBACK. Without WHERE, all rows would be affected.
DELETE in a trial transaction
DELETE removes rows. In this trial, you observe the result then cancel. Run the statements on the same connection.
Example
START TRANSACTION;
DELETE FROM recettes WHERE id = 3;
SELECT COUNT(*) AS total FROM recettes;
ROLLBACK;
SELECT COUNT(*) AS total FROM recettes;
Expected on the initial database: 2 during the trial, then 3 after cancelling. The supplied script does not delete any table.