James Hoffman

Techniques de l'Informatique - Cégep de Shawinigan

Menu

Base de données 1

BD de démonstration

sql
copier
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 suivre un tracé', 3, 'A2021');

GROUP BY

sql
copier
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], ... ]

[LIMIT row_count OFFSET offset ]

Applique un calcul à des données regroupées(SUM, AVG, COUNT, MIN, MAX)

sql
copier
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

📚 Fonctions AGREGATE

GROUP BY, élaboration

Combien d'enseignants ont donné chaque cours? chaque, par, ...

  • On peut utiliser ORDER BY pour visualiser les regroupements
sql
copier
-- Le nombre de fois qu'un enseignant a donné chacun de ses COURS

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;
sql
copier
-- 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;

Pièges MariaDB

Only full group by

sql
copier
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', ''));

Ordre d'exécution Having

sql
copier
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

Structure d'exécution