CREATE OR REPLACE DATABASE school;
CREATE TABLE school.teachers (
employee_number INT AUTO_INCREMENT KEY,
name VARCHAR(200) NOT NULL,
email VARCHAR(200) UNIQUE NOT NULL,
birthday DATE NOT NULL
);
CREATE TABLE school.courses (
code CHAR(10) NOT NULL,
credits DOUBLE(3,2) NOT NULL DEFAULT 1,
name VARCHAR(200) NOT NULL,
description VARCHAR(1000),
teacher INT NOT NULL,
semester CHAR(5) NOT NULL
);
INSERT INTO school.teachers (employee_number, name, email, birthday)
VALUES (1, 'James', 'jhoffman@cshawi.ca', '1990-01-01'), (2, 'Mathieu', 'mstyves@cshawi.ca', '1980-02-02'), (3, 'Stevens', 'sgagnon@cshawi.ca', '1970-03-03'), (4, 'Marco', 'mguilmette@cshawi.ca', '1975-04-04');
INSERT INTO school.courses(code, credits, name, description, teacher, semester)
VALUES ('420-0Q4-SW', 2, 'Initiation à la profession', NULL, 1, 'A2021'), ('420-0Q4-SW', 2, 'Initiation à la profession', NULL, 1, 'A2020'), ('420-0Q7-SW', 2.33, 'Base de données 1', NULL, 1, 'A2021'), ('420-2SS-SW', 2, 'Développement d''applications mobiles', NULL, 1, 'A2021'), ('420-2SS-SW', 2, 'Développement d''applications mobiles', NULL, 1, 'A2020'), ('420-0Q7-SW', 2.33, 'Base de données 1', NULL, 2, 'A2021'), ('420-2SS-SW', 2, 'Développement d''applications mobiles', NULL, 2, 'A2020'), ('420-0SU-SW', 1.33, 'Web: Client 1', NULL, 2, 'A2021'), ('420-2SU-SW', 2.33, 'Web: Serveur 2', NULL, 2, 'A2021'), ('420-1SY-SW', 2.66, 'Analyse objet', NULL, 2, 'A2021'), ('420-0SV-SW', 1.66, 'Échange de données 1', 'Développement d''applications client-serveur', 3, 'A2021'), ('420-1SX-SW', 1.66, 'Robotique', 'Programmer un robot pour suivree un tracé', 3, 'A2021');
SELECT [ DISTINCT ] select_expr [, select_expr, ... ]
FROM table_reference
[WHERE where_condition]
[GROUP BY column_def [ASC | DESC], ... ] -- Sépare les données en groupes qui possèdent
-- la même valeur pour la/les colonnes spécifiées
[HAVING group_by_where_condition] -- Condition sur des éléments calculés par le GROUP BY
[ORDER BY column_def [ASC | DESC], ... ] -- Tri
[LIMIT row_count OFFSET offset ] -- Nombre limité d'enregistrements, OFFSET est zero-based
Recherche via un pattern simple dans les chaînes de caractères
-- % pour n'importe quels caractères
-- _ pour 1 seul caractère
-- La recherche est case-INsensitive
SELECT * FROM school.courses
WHERE name LIKE "web%"; -- qui débute par web
SELECT * FROM school.teachers
WHERE name NOT LIKE "%a%"; -- qui NE contient PAS a
SELECT * FROM school.courses
WHERE code LIKE "420-0Q_-SW"; -- _ peut être remplacé par n'importe quel caractère
📚 LIKE
-- Bornes incluses, expression >= a AND expression <= b
SELECT * FROM school.courses
WHERE credits BETWEEN 2 AND 3;
SELECT * FROM school.courses
WHERE LEFT(name, 1) BETWEEN 'a' AND 'd'; -- Cours dont la 1ere lettre du nom est a, b, c ou d
SELECT * FROM school.teachers
WHERE birthday BETWEEN '1970-01-01' AND '1980-12-31';
-- Si on ne précise pas d'heure, la comparaison s'effectue à 00:00,
-- donc une valeur avec une heure plus tard sur la même date sera rejetée
SELECT * FROM school.teachers
WHERE birthday BETWEEN '1970-03-03' AND '1980-12-31';
SELECT * FROM school.teachers
WHERE birthday BETWEEN '1970-03-03 09:45' AND '1980-12-31';
📚 BETWEEN
Raccourci élégant pour écrire une succession de conditions
SELECT * FROM school.teachers
WHERE employee_number IN(1, 2, 3, 7);
-- employee_number = 1 || employee_number = 2 || employee_number = 3 || employee_number = 7
SELECT * FROM school.courses
WHERE code NOT IN('autre', '420-0Q4-SW', '420-0Q7-SW');
-- code != 'autre' && code != '420-0Q4-SW' && code != '420-0Q7-SW'
📚 IN
SELECT DISTINCT teacher -- Valeurs possibles dans la colonne teacher
FROM school.courses;
-- Le DISTINCT s'applique sur la combinaison des colonnes spécifiées
SELECT DISTINCT teacher, code
FROM school.courses;
SELECT * FROM school.courses
ORDER BY credits DESC, name ASC; -- Tri
SELECT *
FROM school.teachers
LIMIT 1 OFFSET 2; -- Le 3eme enseignant
📚 ORDER BY
📚 LIMIT
Applique un calcul à des données regroupées(SUM, AVG, COUNT, MIN, MAX)
SELECT *, teacher, COUNT(code) AS `Cours/prof carriere` FROM school.courses
GROUP BY teacher;
SELECT *, COUNT(semester) AS `Cours/session` FROM school.courses
GROUP BY semester;
SELECT *, COUNT(code) AS `Cours/prof/session` FROM school.courses
GROUP BY teacher, semester; -- Par enseignant ET session
SELECT *, COUNT(code) AS `Cours/prof/session > 2` FROM school.courses
GROUP BY teacher, semester
HAVING `Cours/prof/session > 2` > 2;
📚 GROUP BY
Combien d'enseignants ont donné chaque cours? chaque, par, ...
ORDER BY pour visualiser les regroupementsSELECT * FROM school.courses
ORDER BY code; -- Le code identifie un cours
SELECT code FROM school.courses
GROUP BY code;
SELECT code, COUNT(teacher) AS `Profs/cours(erreur)` FROM school.courses
GROUP BY code;
--
-- Si un meme enseignant a donné plusieurs fois le même cours
--
SELECT * FROM school.courses
ORDER BY code;
SELECT code, COUNT(DISTINCT teacher) AS `Profs/cours` FROM school.courses
GROUP BY code;
-- La moyenne de crédits des cours de chaque enseignant
SELECT * FROM school.courses
ORDER BY teacher;
SELECT teacher, AVG(credits) as `Moyenne credits` FROM school.courses
GROUP BY teacher;
-- Qui donnent des 'gros' cours, > 2 credits en moyenne
SELECT teacher, AVG(credits) as `Moyenne credits` FROM school.courses
WHERE credits > 2
-- WHERE `Moyenne credits` > 2 -- Invalide
GROUP BY teacher;
SELECT teacher, AVG(credits) as `Moyenne credits` FROM school.courses
GROUP BY teacher
HAVING `Moyenne credits` > 2; -- Condition sur l'aggregation
-- La moyenne de crédits des cours de chaque enseignant, PAR SESSION
SELECT teacher, AVG(credits) as `Moyenne credits/session` FROM school.courses
GROUP BY teacher, semester;
SELECT * FROM school.courses
ORDER BY code;
SELECT * FROM school.courses
GROUP BY code;
-- Quel enseignant ou session est affiché?
SET sql_mode=(SELECT CONCAT(@sql_mode, ',ONLY_FULL_GROUP_BY')); -- group by stricts
SELECT * FROM school.courses
GROUP BY code;
SELECT code, name, credits, COUNT(DISTINCT teacher) AS `Profs/cours` FROM school.courses
GROUP BY code, name, credits;
SET sql_mode=(SELECT REPLACE(@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
SELECT name, TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS `age`
FROM school.teachers
WHERE `age` < 45
SELECT name, TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS `age`
FROM school.teachers
HAVING `age` < 45
-- Ordre d'execution MySQL/MariaDB
FROM
WHERE
SELECT, DISTINCT <- MARIADB evalue ICI
GROUP BY
HAVING
........ <- Mais standard SQL ici
ORDER BY
LIMIT
-- Donc, les alias du select ne sont pas disponibles dans le where
-- mais dans le having oui, qui s'applique sur regroupement implicite de chaque ligne
SELECT name AS `Nom`,
-- IFNULL(expression, value_true)
IFNULL(description, '[vide]') AS `Description`,
-- IF(condition, value_true, value_false)
IF(credits > 2, 'inférieure', 'supérieure') AS `Charge`,
-- CASE WHEN (condition) THEN value_true ... ELSE value END
CASE WHEN (credits < 1) THEN 'Tres petit'
WHEN (credits BETWEEN 1 AND 2) THEN 'Petit'
WHEN (credits = 2) THEN 'Standard'
WHEN (credits BETWEEN 2 AND 3) THEN 'Moyen'
WHEN (credits > 3) THEN 'Gros'
END AS `Pondération`,
-- CASE (exp) WHEN value THEN value_true ... ELSE value END
CASE teacher WHEN 1 THEN 'James'
WHEN 2 THEN 'Mathieu'
WHEN 3 THEN 'Marco'
ELSE '???'
END AS `Nom enseignant`
FROM school.courses;
SELECT name AS `Nom`,
CHAR_LENGTH(name) AS `Longueur Nom`,
CONCAT_WS(' | ', code, name, credits ) AS `Sommaire`,
FORMAT(credits, 1) AS `Format credits`,
SUBSTRING(code, 5, 3) AS `Identifiant`
FROM school.courses;
SELECT
CURDATE() AS `Aujourd'hui`,
NOW() AS `Maintenant`,
name AS `Nom`,
birthday AS `Date de naissance`,
DATEDIFF(CURDATE(), birthday) AS `Age jours`,
TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS `Age annees`,
CONCAT_WS(' ', DAYNAME(birthday), DAY(birthday), MONTHNAME(birthday), YEAR(birthday)) AS `Jour de naissance`,
DATE_FORMAT(birthday, '%W %e %M %Y','fr_CA') AS `Format jour de naissance`
FROM school.teachers;