James Hoffman

Techniques de l'Informatique - Cégep de Shawinigan

Menu

Base de données 1

Permet de récupérer des données de plusieurs tables, souvent via les relations établies entres elles

sql
SELECT select_expr [, select_expr ...]
FROM table_ref [, table_ref ...]

table_ref:
table_factor
| CROSS JOIN
| INNER JOIN table_factor ON conditional_expr
| { LEFT | RIGHT } JOIN table_factor ON conditional_expr

Démo 1: Alphagrec

sql
copier
-- Reference: https://fr.wikipedia.org/wiki/Alphabet_grec

drop database if exists alphabet;
create database alphabet;

-- Different de CREATE OR REPLACE DATABASE qui est en ordre alphabetique
-- Sinon DROP TABLES IF EXISTS alphabet.greek, alphabet.english;

create table alphabet.english (
id int unsigned primary key,
letter varchar(1)
);

create table alphabet.greek (
id int unsigned primary key,
name varchar(10),
english_id int unsigned,
foreign key (english_id) references english(id) on delete cascade
);

insert into alphabet.english values (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'), (5, 'e'), (6, 'f'), (7, 'g'), (8, 'h'), (9, 'i'), (10, 'j'), (11, 'k'), (12, 'l'), (13, 'm'), (14, 'n'), (15, 'o'), (16, 'p'), (17, 'q'), (18, 'r'), (19, 's'), (20, 't'), (21, 'u'), (22, 'v'), (23, 'w'), (24, 'x'), (25, 'y'), (26, 'z');

insert into alphabet.greek values (1, 'alpha', 1), (2, 'beta', 2), (3, 'gamma', 7), (4, 'delta', 4), (5, 'epsilon', 5), (6, 'zeta', 26), (7, 'eta', null), (8, 'theta', null), (9, 'iota', 9), (10, 'kappa', 11), (11, 'lambda', 12), (12, 'mu', 13), (13, 'nu', 14), (14, 'xi', 24), (15, 'omicron', 15), (16, 'pi', 16), (17, 'rho', 18), (18, 'sigma', 19), (19, 'tau', 20), (20, 'upsilon', 21), (21, 'phi', null), (22, 'chi', null), (23, 'psi', null), (24, 'omega', null);
english +----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
...
+----+------+
greek +-----+-------------+------------+
| id | name | english_id |
+-----+-------------+------------+
| 1 | alpha | 1 |
| 2 | beta | 2 |
...
| 24 | omega | NULL |
+-----+-------------+------------+

CROSS JOIN, Combinatoire

sql
copier
select
*
from
alphabet.english
cross join
alphabet.greek;
+----+--------+----+----------+------------+
| id | letter | id | name | english_id |
+----+--------+----+----------+------------+
| 1 | a | 1 | alpha | 1 |
| 1 | a | 2 | beta | 2 |
| 1 | a | 3 | gamma | 7 |
| 1 | a | 4 | delta | 4 |
...
| 26 | z | 19 | tau | 20 |
| 26 | z | 20 | upsilon | 21 |
| 26 | z | 21 | phi | NULL |
| 26 | z | 22 | chi | NULL |
| 26 | z | 23 | psi | NULL |
| 26 | z | 24 | omega | NULL |
+----+--------+----+----------+------------+

INNER JOIN, Intersection

sql
copier
select
*
from
alphabet.english
inner join
alphabet.greek on alphabet.english.id = alphabet.greek.english_id
order by
letter;
+----+--------+----+----------+------------+
| id | letter | id | name | english_id |
+----+--------+----+----------+------------+
| 1 | a | 1 | alpha | 1 |
| 2 | b | 2 | beta | 2 |
'c' est absent, pas de correspondance grec
| 4 | d | 4 | delta | 4 |
| 5 | e | 5 | epsilon | 5 |
'f' est absent, pas de correspondance grec
| 7 | g | 3 | gamma | 7 |
...
| 26 | z | 6 | zeta | 26 |
+----+--------+----+----------+------------+

LEFT JOIN, Union partielle

sql
copier
select
*
from
alphabet.english
left join
alphabet.greek on alphabet.english.id = alphabet.greek.english_id;
+----+--------+------+----------+------------+
| id | letter | id | name | english_id |
+----+--------+------+----------+------------+
| 1 | a | 1 | alpha | 1 |
| 2 | b | 2 | beta | 2 |
| 3 | c | NULL | NULL | NULL |
| 4 | d | 4 | delta | 4 |
| 5 | e | 5 | epsilon | 5 |
| 6 | f | NULL | NULL | NULL |
| 7 | g | 3 | gamma | 7 |
...
| 24 | x | 14 | xi | 24 |
| 25 | y | NULL | NULL | NULL |
| 26 | z | 6 | zeta | 26 |
+----+--------+------+----------+------------+

LEFT JOIN, Exclusif

sql
copier
select
*
from
alphabet.english
left join
alphabet.greek on alphabet.english.id = alphabet.greek.english_id
where
alphabet.greek.id is null; -- <== exclusif à english
+----+--------+------+------+------------+
| id | letter | id | name | english_id |
+----+--------+------+------+------------+
| 3 | c | NULL | NULL | NULL |
| 6 | f | NULL | NULL | NULL |
| 8 | h | NULL | NULL | NULL |
| 10 | j | NULL | NULL | NULL |
| 17 | q | NULL | NULL | NULL |
| 22 | v | NULL | NULL | NULL |
| 23 | w | NULL | NULL | NULL |
| 25 | y | NULL | NULL | NULL |
+----+--------+------+------+------------+

RIGHT JOIN

sql
copier
select
*
from
alphabet.english
right join
alphabet.greek on alphabet.english.id = alphabet.greek.english_id
order by
alphabet.greek.id;
+------+--------+----+----------+------------+
| id | letter | id | name | english_id |
+------+--------+----+----------+------------+
| 1 | a | 1 | alpha | 1 |
| 2 | b | 2 | beta | 2 |
...
| NULL | NULL | 23 | psi | NULL |
| NULL | NULL | 24 | omega | NULL |
+------+--------+----+----------+------------+
sql
copier
select
*
from
alphabet.english
right join
alphabet.greek on alphabet.english.id = alphabet.greek.english_id
where
alphabet.english.id is null; -- exclusif à grec
+------+--------+----+--------+------------+
| id | letter | id | name | english_id |
+------+--------+----+--------+------------+
| NULL | NULL | 7 | eta | NULL |
| NULL | NULL | 8 | theta | NULL |
| NULL | NULL | 21 | phi | NULL |
| NULL | NULL | 22 | chi | NULL |
| NULL | NULL | 23 | psi | NULL |
| NULL | NULL | 24 | omega | NULL |
+------+--------+----+--------+------------+

Démo 2: School

sql
copier
-- CREATE OR REPLACE ok car courses < teachers
create or replace database school;

use school;

create table teachers (
employee_number int unsigned auto_increment primary key,
name varchar(200) not null
);

create table courses (
code char(10) not null,
teacher_employee_number int unsigned not null,
semester char(5) not null,

primary key (code, teacher_employee_number, semester),
foreign key (teacher_employee_number) references teachers (employee_number)
);

insert into teachers (employee_number, name)
values (1, 'James'), (2, 'Mathieu'), (3, 'Stevens'), (4, 'Marco'), (5, 'Lyne'), (6, 'Nicolas');

insert into courses
values ('420-0Q4-SW', 1, 'A2021'), ('420-0Q4-SW', 1, 'A2020'), ('420-0Q4-SW', 1, 'A2019'), ('420-0Q7-SW', 1, 'A2021'), ('420-2SS-SW', 1, 'A2021'), ('420-2SS-SW', 1, 'A2020'), ('420-0Q7-SW', 2, 'A2021'), ('420-2SS-SW', 2, 'A2020'), ('420-0Q4-SW', 2, 'A2019'), ('420-0SU-SW', 2, 'A2021'), ('420-2SU-SW', 2, 'A2021'), ('420-1SY-SW', 2, 'A2021'), ('420-0Q4-SW', 3, 'A2021'), ('420-0SV-SW', 3, 'A2021'), ('420-1SX-SW', 3, 'A2021'), ('420-2SS-SW', 3, 'A2020'), ('420-0Q4-SW', 4, 'A2018'), ('420-0Q4-SW', 4, 'A2017');

Avec group by

sql
copier
select
name,
count(distinct semester) as `nb sessions`,
group_concat(distinct semester) as `sessions`,
count(code) as `nb cours total`,
count(distinct code) as `nb cours different`,
group_concat(distinct code) as `cours`
from
school.teachers
inner join -- VS left
school.courses on teachers.employee_number = courses.teacher_employee_number
group by
employee_number
;
+---------+-------------+-------------------+----------------+--------------------+-------------------------------------------------------------------+
| name | nb sessions | sessions | nb cours total | nb cours different | cours |
+---------+-------------+-------------------+----------------+--------------------+-------------------------------------------------------------------+
| James | 3 | A2019,A2020,A2021 | 6 | 3 | 420-0Q4-SW,420-0Q7-SW,420-2SS-SW |
| Mathieu | 3 | A2019,A2020,A2021 | 6 | 6 | 420-0Q4-SW,420-0Q7-SW,420-0SU-SW,420-1SY-SW,420-2SS-SW,420-2SU-SW |
| Stevens | 2 | A2020,A2021 | 4 | 4 | 420-0Q4-SW,420-0SV-SW,420-1SX-SW,420-2SS-SW |
| Marco | 2 | A2017,A2018 | 2 | 1 | 420-0Q4-SW |
+---------+-------------+-------------------+----------------+--------------------+-------------------------------------------------------------------+

Avec sous-requête

Le nombre de prestations de chaque cours, pour chaque enseignant

sql
copier
with AllCourses as (
select
distinct code
from
school.courses
)
select
T.name as `enseignant`,
AC.code as `cours`,
count(C.code) as `prestations`
from
school.teachers T
cross join
AllCourses AC
left join
school.courses C on T.employee_number = C.teacher_employee_number && AC.code = C.code
group by
T.employee_number, AC.code
;
+------------+------------+-------------+
| enseignant | cours | prestations |
+------------+------------+-------------+
| James | 420-0Q4-SW | 3 |
| James | 420-0Q7-SW | 1 |
| James | 420-0SU-SW | 0 |
| James | 420-0SV-SW | 0 |
| James | 420-1SX-SW | 0 |
| James | 420-1SY-SW | 0 |
| James | 420-2SS-SW | 2 |
| James | 420-2SU-SW | 0 |
| Mathieu | 420-0Q4-SW | 1 |
| Mathieu | 420-0Q7-SW | 1 |
| Mathieu | 420-0SU-SW | 1 |
| Mathieu | 420-0SV-SW | 0 |
| Mathieu | 420-1SX-SW | 0 |
| Mathieu | 420-1SY-SW | 1 |
| Mathieu | 420-2SS-SW | 1 |
| Mathieu | 420-2SU-SW | 1 |
| Stevens | 420-0Q4-SW | 1 |
| Stevens | 420-0Q7-SW | 0 |
| Stevens | 420-0SU-SW | 0 |
| Stevens | 420-0SV-SW | 1 |
| Stevens | 420-1SX-SW | 1 |
| Stevens | 420-1SY-SW | 0 |
| Stevens | 420-2SS-SW | 1 |
| Stevens | 420-2SU-SW | 0 |
| Marco | 420-0Q4-SW | 2 |
| Marco | 420-0Q7-SW | 0 |
| Marco | 420-0SU-SW | 0 |
| Marco | 420-0SV-SW | 0 |
| Marco | 420-1SX-SW | 0 |
| Marco | 420-1SY-SW | 0 |
| Marco | 420-2SS-SW | 0 |
| Marco | 420-2SU-SW | 0 |
| Lyne | 420-0Q4-SW | 0 |
| Lyne | 420-0Q7-SW | 0 |
| Lyne | 420-0SU-SW | 0 |
| Lyne | 420-0SV-SW | 0 |
| Lyne | 420-1SX-SW | 0 |
| Lyne | 420-1SY-SW | 0 |
| Lyne | 420-2SS-SW | 0 |
| Lyne | 420-2SU-SW | 0 |
| Nicolas | 420-0Q4-SW | 0 |
| Nicolas | 420-0Q7-SW | 0 |
| Nicolas | 420-0SU-SW | 0 |
| Nicolas | 420-0SV-SW | 0 |
| Nicolas | 420-1SX-SW | 0 |
| Nicolas | 420-1SY-SW | 0 |
| Nicolas | 420-2SS-SW | 0 |
| Nicolas | 420-2SU-SW | 0 |
+------------+------------+-------------+

Plus d'exemples

INNER JOIN

sql
copier
select
*
from
school.teachers
inner join
school.courses on employee_number = teacher_employee_number
order by
name, code, semester;
+-----------------+---------+------------+-------------------------+----------+
| employee_number | name | code | teacher_employee_number | semester |
+-----------------+---------+------------+-------------------------+----------+
| 1 | James | 420-0Q4-SW | 1 | A2019 |
| 1 | James | 420-0Q4-SW | 1 | A2020 |
| 1 | James | 420-0Q4-SW | 1 | A2021 |
| 1 | James | 420-0Q7-SW | 1 | A2021 |
| 1 | James | 420-2SS-SW | 1 | A2020 |
| 1 | James | 420-2SS-SW | 1 | A2021 |
| 4 | Marco | 420-0Q4-SW | 4 | A2017 |
| 4 | Marco | 420-0Q4-SW | 4 | A2018 |
...
| 3 | Stevens | 420-0Q4-SW | 3 | A2021 |
| 3 | Stevens | 420-0SV-SW | 3 | A2021 |
| 3 | Stevens | 420-1SX-SW | 3 | A2021 |
| 3 | Stevens | 420-2SS-SW | 3 | A2020 |
+-----------------+---------+------------+-------------------------+----------+

INNER JOIN, group by

sql
copier
select
employee_number,
name,
code,
count(distinct code) as `nb cours`, -- inutile car GROUP BY
count(semester) as `nb sessions`
from
school.teachers
inner join
school.courses on employee_number = teacher_employee_number
group by
name, code; -- 2 niveaux name ET code
+-----------------+---------+------------+--------------------+-------------+
| employee_number | name | code | Nb cours (inutile) | Nb sessions |
+-----------------+---------+------------+--------------------+-------------+
| 1 | James | 420-0Q4-SW | 1 | 3 |
| 1 | James | 420-0Q7-SW | 1 | 1 |
| 1 | James | 420-2SS-SW | 1 | 2 |
| 4 | Marco | 420-0Q4-SW | 1 | 2 |
| 2 | Mathieu | 420-0Q4-SW | 1 | 1 |
...
+-----------------+---------+------------+--------------------+-------------+

LEFT JOIN

sql
copier
select *
from
school.teachers
left join
school.courses on employee_number = teacher_employee_number;
+-----------------+---------+------------+-------------------------+----------+
| employee_number | name | code | teacher_employee_number | semester |
+-----------------+---------+------------+-------------------------+----------+
| 1 | James | 420-0Q4-SW | 1 | A2019 |
| 1 | James | 420-0Q4-SW | 1 | A2020 |
| 1 | James | 420-0Q4-SW | 1 | A2021 |
| 1 | James | 420-0Q7-SW | 1 | A2021 |
...
| 4 | Marco | 420-0Q4-SW | 4 | A2017 |
| 4 | Marco | 420-0Q4-SW | 4 | A2018 |
| 5 | Lyne | NULL | NULL | NULL |
| 6 | Nicolas | NULL | NULL | NULL |
+-----------------+---------+------------+-------------------------+----------+

LEFT JOIN exclusif

sql
copier
select *
from
school.teachers
left join
school.courses on employee_number = teacher_employee_number
where
teacher_employee_number is null;
+-----------------+---------+------+-------------------------+----------+
| employee_number | name | code | teacher_employee_number | semester |
+-----------------+---------+------+-------------------------+----------+
| 5 | Lyne | NULL | NULL | NULL |
| 6 | Nicolas | NULL | NULL | NULL |
+-----------------+---------+------+-------------------------+----------+

LEFT JOIN, group by

sql
copier
select
employee_number, name, code, count(semester) as `nb sessions`
from
school.teachers
left join
school.courses on teachers.employee_number = courses.teacher_employee_number
group by
name, code
order by
employee_number;
+-----------------+---------+------------+-------------+
| employee_number | name | code | Nb sessions |
+-----------------+---------+------------+-------------+
| 1 | James | 420-0Q4-SW | 3 |
| 1 | James | 420-0Q7-SW | 1 |
| 1 | James | 420-2SS-SW | 2 |
| 2 | Mathieu | 420-2SU-SW | 1 |
| 2 | Mathieu | 420-0Q4-SW | 1 |
...
| 3 | Stevens | 420-0SV-SW | 1 |
| 4 | Marco | 420-0Q4-SW | 2 |
| 5 | Lyne | NULL | 0 |
| 6 | Nicolas | NULL | 0 |
+-----------------+---------+------------+-------------+