MySQL — The basics

Guide 02 / 4

MySQL essentials

An annotated cheat sheet: what each element is for, how to write it and when to use it.

Expected outcome :

Choose and use the essential elements to complete this step of the blog.

Reading
0%

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.

Terminal : mysql -u votre_compte -p
SQL : USE blog_recettes;
SQL : SHOW TABLES;
SQL : DESCRIBE recettes;
Remember :

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.

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.

titre VARCHAR(120)
ingredients TEXT
duree_minutes SMALLINT UNSIGNED
portions TINYINT UNSIGNED
cree_le TIMESTAMP DEFAULT CURRENT_TIMESTAMP
Remember :

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.

CONSTRAINT ck_duree CHECK (duree_minutes BETWEEN 1 AND 1440),
CONSTRAINT fk_categorie FOREIGN KEY (categorie_id)
  REFERENCES categories(id) ON DELETE RESTRICT
Remember :

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.

INSERT INTO categories (nom) VALUES ('Goûter');
INSERT INTO categories (nom) VALUES ('Chef d''un jour');
Remember :

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.

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.

SELECT id, titre FROM recettes
WHERE duree_minutes <= 20 AND portions = 2;
Remember :

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.

SELECT titre FROM recettes WHERE titre LIKE '%pommes%';
Remember :

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.

SELECT id, titre, duree_minutes FROM recettes
ORDER BY duree_minutes ASC, id ASC
LIMIT 2;
Remember :

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.

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.

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;
Remember :

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.

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;
Remember :

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.

START TRANSACTION;
DELETE FROM recettes WHERE id = 3;
SELECT COUNT(*) AS total FROM recettes;
ROLLBACK;
SELECT COUNT(*) AS total FROM recettes;
Remember :

Expected on the initial database: 2 during the trial, then 3 after cancelling. The supplied script does not delete any table.