Récupération avancée | Base de données

class: main-title

<h1>Base de données</h1>

<h2>Récupération avancée</h2>

---

<ul>
<li>Conditions</li>
<li>Tri</li>
<li>Limite</li>
<li>Aggrégation</li>
</ul>

---
class: section-title

<h1>BD de démonstration</h1>

---

<h1 class="slide-only">Base de données de démonstration</h1>

<pre><code class="language-sql">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');
</code></pre>

---
class: section-title

<h1>Récupération, suite</h1>

---

<h1 class="menu">Récupération, suite</h1>

<pre><code class="language-sql">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
</code></pre>

---

<h1 class="menu">WHERE LIKE/NOT LIKE</h1>

<p>Recherche via un <em>pattern</em> simple dans les chaînes de caractères</p>

<pre><code class="language-sql">-- % 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
</code></pre>

---

<h1 class="menu">WHERE BETWEEN/NOT BETWEEN</h1>

<pre><code class="language-sql">-- Bornes incluses, expression &gt;= a AND expression &lt;= 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';
</code></pre>

<p>📚 <a href="https://mariadb.com/kb/en/between-and/" target="_blank" rel="noopener noreferrer">BETWEEN</a></p>

---

<h1 class="menu">WHERE IN/NOT IN</h1>

<p>Raccourci élégant pour écrire une succession de conditions</p>

<pre><code class="language-sql">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' &amp;&amp; code != '420-0Q4-SW' &amp;&amp; code != '420-0Q7-SW'
</code></pre>

---

<h1 class="menu">DISTINCT, ORDER BY, LIMIT</h1>

<pre><code class="language-sql">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
</code></pre>

<p>📚 <a href="https://mariadb.com/kb/en/order-by/" target="_blank" rel="noopener noreferrer">ORDER BY</a></p>

<p>📚 <a href="https://mariadb.com/kb/en/limit/" target="_blank" rel="noopener noreferrer">LIMIT</a></p>

---

<h1 class="menu">GROUP BY</h1>

<p>Applique un calcul à des données regroupées(SUM, AVG, COUNT, MIN, MAX)<br></p>

<pre><code class="language-sql">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 &gt; 2` FROM school.courses
GROUP BY teacher, semester
HAVING `Cours/prof/session &gt; 2` &gt; 2;
</code></pre>

<p>📚 <a href="https://mariadb.com/kb/en/group-by/" target="_blank" rel="noopener noreferrer">GROUP BY</a></p>

<p>📚 <a href="https://mariadb.com/kb/en/aggregate-functions/" target="_blank" rel="noopener noreferrer">Fonctions AGREGATE</a></p>

---

<h1>GROUP BY, élaboration</h1>

<p>Combien d'enseignants ont donné chaque cours? <em>chaque</em>, <em>par</em>, ...</p>

<ul>
<li>On peut utiliser <code>ORDER BY</code> pour visualiser les regroupements</li>
</ul>

<pre><code class="language-sql">SELECT * 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;
</code></pre>

---

<h1 class="slide-only">GROUP BY</h1>

<pre><code class="language-sql">-- 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, &gt; 2 credits en moyenne
SELECT teacher, AVG(credits) as `Moyenne credits` FROM school.courses
WHERE credits &gt; 2
-- WHERE `Moyenne credits` &gt; 2 -- Invalide
GROUP BY teacher;

SELECT teacher, AVG(credits) as `Moyenne credits` FROM school.courses
GROUP BY teacher
HAVING `Moyenne credits` &gt; 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;
</code></pre>

---

<h1>GROUP BY, <span style="color: red">pièges MariaDB</span></h1>

<pre><code class="language-sql">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', ''));
</code></pre>

---

<h1>HAVING, <span style="color: red">pièges MariaDB</span></h1>

<pre><code class="language-sql">SELECT name, TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS `age` 
FROM school.teachers 
WHERE `age` &lt; 45

SELECT name, TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS `age` 
FROM school.teachers 
HAVING `age` &lt; 45

-- Ordre d'execution MySQL/MariaDB

FROM
WHERE
SELECT, DISTINCT &lt;- MARIADB evalue ICI
GROUP BY
HAVING
........ &lt;- 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
</code></pre>

---
class: section-title

<h1>Fonctions</h1>

<p>📚 <a href="https://mariadb.com/kb/en/built-in-functions/" target="_blank" rel="noopener noreferrer">Documentation</a></p>

---

<h1 class="menu">Fonctions - Décisions</h1>

<pre><code class="language-sql">SELECT  name AS `Nom`,
        -- IFNULL(expression, value_true)
        IFNULL(description, '[vide]') AS `Description`,          
        -- IF(condition, value_true, value_false)
        IF(credits &gt; 2, 'inférieure', 'supérieure') AS `Charge`,
        -- CASE WHEN (condition) THEN value_true ... ELSE value END
        CASE WHEN (credits &lt; 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 &gt; 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;
</code></pre>

<p>📚 <a href="https://mariadb.com/kb/en/control-flow-functions/" target="_blank" rel="noopener noreferrer">Control Flow Functions</a></p>

---

<h1 class="menu">Fonctions - Chaîne de caractères</h1>

<pre><code class="language-sql">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;
</code></pre>

<p>📚 <a href="https://mariadb.com/kb/en/string-functions/" target="_blank" rel="noopener noreferrer">String Functions</a></p>

---

<h1 class="menu">Fonctions - Date et heure</h1>

<pre><code class="language-sql">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;
</code></pre>

<p>📚 <a href="https://mariadb.com/kb/en/date-time-functions/" target="_blank" rel="noopener noreferrer">Date & Time Functions</a></p>

---
class: section-title

<h1>Exercices</h1>

<p>👉  <a href="/sessions/2023A/0Q7/documentation/3.4-exercices">Énoncé</a></p>