Je viens vers vous pour vous demandez de l'aide par apport à une requête qui me complique la vie.
J'ai 5 tables SQL: la table CLASSES, COURS, ELEVES et NOTES
pour la table CLASSES on a:
CREATE TABLE `classes` (
`id` int NOT NULL AUTO_INCREMENT,
`nom` varchar(255) NOT NULL,
`id_professeur` int DEFAULT NULL,
`id_etablissement` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id_professeurs_classes_idx` (`id_professeur`),
KEY `id_etablissement_classes_idx` (`id_etablissement`),
CONSTRAINT `id_etablissement_classes` FOREIGN KEY (`id_etablissement`) REFERENCES `etablissements` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `id_professeurs_classes` FOREIGN KEY (`id_professeur`) REFERENCES `professeurs` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Pour la table COURS on a:
CREATE TABLE `cours` (
`id` int NOT NULL AUTO_INCREMENT,
`nom` varchar(255) NOT NULL,
`total` int NOT NULL,
`id_professeur` int DEFAULT NULL,
`id_classe` int DEFAULT NULL,
`id_etablissement` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id_professeur_cours_idx` (`id_professeur`),
KEY `id_classes_cours_idx` (`id_classe`),
KEY `id_etablissement_cours_idx` (`id_etablissement`),
CONSTRAINT `id_classes_cours` FOREIGN KEY (`id_classe`) REFERENCES `classes` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `id_etablissement_cours` FOREIGN KEY (`id_etablissement`) REFERENCES `etablissements` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `id_professeur_cours` FOREIGN KEY (`id_professeur`) REFERENCES `professeurs` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Pour la table NOTES on a:
CREATE TABLE `notes` (
`id` int NOT NULL AUTO_INCREMENT,
`id_eleve` int DEFAULT NULL,
`id_classe` int DEFAULT NULL,
`id_etablissement` int DEFAULT NULL,
`id_cours` int DEFAULT NULL,
`point` varchar(255) NOT NULL,#point obtenu
`note` int NOT NULL, #mention en étoile
PRIMARY KEY (`id`),
KEY `id_eleves_notes_idx` (`id_eleve`),
KEY `id_classes_notes_idx` (`id_classe`),
KEY `id_etablissement_notes_idx` (`id_etablissement`),
KEY `id_cours_notes_idx` (`id_cours`),
CONSTRAINT `id_classes_notes` FOREIGN KEY (`id_classe`) REFERENCES `classes` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `id_cours_notes` FOREIGN KEY (`id_cours`) REFERENCES `cours` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `id_eleves_notes` FOREIGN KEY (`id_eleve`) REFERENCES `eleves` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `id_etablissement_notes` FOREIGN KEY (`id_etablissement`) REFERENCES `etablissements` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
et en fin pour la table ELEVES on a:
CREATE TABLE `eleves` (
`id` int NOT NULL AUTO_INCREMENT,
`code_secret` text NOT NULL,
`nom` varchar(255) NOT NULL,
`postnom` varchar(255) NOT NULL,
`prenom` varchar(255) NOT NULL,
`sexe` varchar(255) NOT NULL,
`dateDeNaissance` varchar(255) NOT NULL,
`id_ville` int DEFAULT NULL,
`id_classe` int DEFAULT NULL,
`id_etablissement` int DEFAULT NULL,
`photo` text NOT NULL,
PRIMARY KEY (`id`),
KEY `id_villes_eleves_idx` (`id_ville`),
KEY `id_classes_eleves_idx` (`id_classe`),
KEY `id_etablissements_eleves_idx` (`id_etablissement`),
CONSTRAINT `id_classes_eleves` FOREIGN KEY (`id_classe`) REFERENCES `classes` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `id_etablissements_eleves` FOREIGN KEY (`id_etablissement`) REFERENCES `etablissements` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `id_villes_eleves` FOREIGN KEY (`id_ville`) REFERENCES `villes` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Mon problème: Je souhaite avoir les notes de chaque eleve par apport à chaque cours existant pour une classe donnée. par exemple, pour Je souhaite avoir toutes les notes des élèves du CP1 pour chaque cours (que la note d'un cours soit NULL ou pas ).
Voici ma requête:
Use my_db;
SELECT `cours`.`id` AS id_cours, `cours`.`nom` AS nom_cours, `cours`.`total` AS total, `classes`.`id` AS id_classe, `classes`.`nom` AS nom_classe, `notes`.`point` AS point_obtenu, `notes`.`note` AS mention, `eleves`.`nom` AS nom_eleve FROM `cours` INNER JOIN `classes` ON `classes`.`id`=`cours`.`id_classe` LEFT JOIN `notes` ON `notes`.`id_cours`=`cours`.`id` INNER JOIN `eleves` ON `eleves`.`id_classe`=`classes`.`id` WHERE `cours`.`id_etablissement`=3 GROUP BY `cours`.`id`
Malheureusement, j'obtiens juste chaque cours avec une note qui lui correspond, hors ce n'est pas ce que je veux moi je veux par exemple dans un même résultat les notes de chaque élève en mathématique, franais,...
Dans ton modèle, tu répètes la clé étrangère id_etablissement dans toutes tes tables. Cela me paraît vraiment bizarre. Une classe est liée à un établissement, OK. Ensuite un cours est lié à une classe, donc pas besoin de reprendre l'établissement au niveau du cours puisqu'il dépend déjà d'une classe elle même liée à un établissement ...
Pareil pour les élèves ... un élève est lié à une classe, donc pas besoin d'y rappeler l'établissement ...
Pareil pour les notes ... une note est liée à un cours qui est lié à une classe qui est liée à un établissement ...
Pour ta question, il faut rajouter dans la jointure sur la table note une condition sur l'élève, sinon chaque note est liée à chaque élève, je pense que c'est à cause de cet oubli que tu t'es senti obligé de rajouter une clause GROUP BY ... GROUP BY ne sert à rien ici, cela s'utilise uniquement avec des fonctions de regroupement (aussi appelée fonction d'agrégat) comme COUNT, SUM, AVG, etc.
Au passage, essaye d'utiliser des alias pour simplifier l'écriture de tes requêtes.
Ta requête s'écrirait au final :
SELECT
C.id AS id_cours,
C.nom AS nom_cours,
C.total AS total,
CL.id AS id_classe,
CL.nom AS nom_classe,
N.point AS point_obtenu,
N.note AS mention,
E.nom AS nom_eleve
FROM
cours C
INNER JOIN classes CL
ON CL.id = C.id_classe
INNER JOIN eleves E
ON E.id_classe = C.id
LEFT JOIN notes N
ON N.id_cours = C.id
AND N.id_eleve = E.id
WHERE C.id_etablissement = 3
On part de la liste des cours de l'établissement 3, que l'on joint chacun à sa classe, que l'on joint chacune à ses élèves, et que l'on joint enfin chacun à sa note (si elle existe, avec LEFT JOIN).
enfaite je viens d'exécuter ton code, mais je n'ai pas le résultat attendu, car ta requête me renvoie juste une ligne, or moi je veux les notes de chaque élève pour chaque cours dispensé dans sa classe que la note existe ou non (NULL ou pas).
Je suis en train de faire une BDD de mon côté en reprenant ton objectif : "Je souhaite avoir les notes de chaque eleve par apport à chaque cours existant pour une classe donnée."
Je ne prends pas en compte les profs et les établissement puisqu'ils ne sont pas nécessaires pour satisfaire l'objectif.
Lors de l'élaboration de mon MCD, j'ai pu éviter toute valeur NULLABLE. En fait, je vois dans ta table Notes que tu as trop de FK.
J'ai créé les tables dans cet ordre :
1/
Classe
Cours
2/
Eleve
Devoir
3/ Noter (relation entre Eleve et Devoir)
J'ai établie les cardinalités suivantes :
1 Classe contient plus d'1 Élève.
1 Élève est dans 1 seule Classe
=> FK de classe dans la table Élève.
1 Cours contient plus d'1 Devoir.
1 Devoir concerne 1 seul Cours
=> FK de cours dans la table.
1 Élève est noter pour plusieurs Devoirs.
1 Devoir est corrigé pour plusieurs Élèves.
=> Relation Noter : FK Élève et FK Devoir
Le MCD est donc ainsi :
CLASSE ===1,n===1,1=== ELEVE ===1,n=== NOTER ===
1,n=== DEVOIR ===1,1===1,n=== COURS
Les colonnes indispensables pour les tables sont, en plus, des PK :
Table CLASSE :
nom_classe VARCHAR(63) # exemple : '6ème'
Table ELEVE :
nom_eleve VARCHAR(63) # exemple : 'A'
classe_eleve FK CLASSE
TABLE COURS :
matière_cours VARCHAR(63) # exemple : 'Français'
TABLE DEVOIR :
cours_devoir FK COURS
TABLE (relation) NOTER :
eleve_noter FK ELEVE
devoir_noter FK DEVOIR
valeur_noter DECIMAL(5,3) # exemple : 15.5
(pour 15,50 / 20)
# contrainte : toutes les notes seront sur 20
En faisant ainsi, tu évites les NULLABLE. Si un élève ne participe pas à un devoir, il suffit de ne pas UPDATE ou INSERT dans la table de relation NOTER qui correspond.
En créant une table DEVOIR, et en séparant les données, je peux dire que le DEVOIR numéro 1 de Français peut correspondre à toutes les classes. La différenciation se fait dans la table NOTER, avec l'élève (selon sa classe).
J'en suis là pour le moment. Je continuerai demain soir. J'ai déjà fait quelques INSERT INTO que je n'écrirai pas ici, mais, un suivi sur papier permettra de les faire plus facilement.
Comme j'ai dit, je viens de finir mon élaboration, et j'ai même testé une requête équivalente à ton objectif avec succès.
J'ai 2 fichiers sql, un pour la création et l'insertion dans les tables. Et un autre pour la requête de test. Les fichiers sont commentés.
Tu peux te servir des fichiers comme point de comparaison.
Avant de les diffuser, je tiens à dire qu'il est possible d'ajouter les tables Professeur et Établissement.
Selon moi, la liaison doit se faire entre Cours et Professeur car un professeur est majoritairement associé à un seul cours. Mais, s'il maîtrise plusieurs matières, on peut mettre une table de relation Maîtriser entre Cours et Professeur.
La liaison doit se faire entre Professeur et Établissement, Avec une table de relation Se Déplacer entre Professeur et Établissement.
Le MCD sera ainsi :
CLASSE ===1,n===1,1=== ELEVE ===1,n=== NOTER ===
1,n=== DEVOIR ===1,1===1,n=== COURS ===1,n===
MAITRISER ===1,n=== PROFESSEUR ===1,n===
SE DEPLACER ===1,n=== ETABLISSEMENT
Voici les fichiers sql, le premier que je nomme creation_insertion.sql et le deuxième requete_test.sql.
creation_insertion.sql:
-- commentaires sur une ligne
-- Création des tables : Classe, Cours puis Élève, Devoir et enfin
-- la table de relation Noter (entre Élève et Devoir)
CREATE TABLE IF NOT EXISTS Classe (
id_classe TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
nom_classe VARCHAR(63) NOT NULL,
PRIMARY KEY (id_classe));
CREATE TABLE IF NOT EXISTS Cours (
id_cours TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
matière_cours VARCHAR(63) NOT NULL,
PRIMARY KEY (id_cours));
CREATE TABLE IF NOT EXISTS Élève (
id_élève SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
nom_élève VARCHAR(63) NOT NULL,
classe_id TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (id_élève),
CONSTRAINT fk_eleve_classe_id FOREIGN KEY (classe_id) REFERENCES Classe(id_classe));
CREATE TABLE IF NOT EXISTS Devoir (
id_devoir SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
cours_id TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (id_devoir),
CONSTRAINT fk_devoir_cours_id FOREIGN KEY (cours_id) REFERENCES Cours(id_cours));
CREATE TABLE IF NOT EXISTS Noter (
id_noter SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
élève_id SMALLINT UNSIGNED NOT NULL,
devoir_id SMALLINT UNSIGNED NOT NULL,
valeur_noter DECIMAL(5,3),
PRIMARY KEY (id_noter),
CONSTRAINT fk_noter_élève_id FOREIGN KEY (élève_id) REFERENCES Élève(id_élève),
CONSTRAINT fk_noter_devoir_id FOREIGN KEY (devoir_id) REFERENCES Devoir(id_devoir));
-- Fin de la création des 5 tables
-- Insertion des données
-- Insertion dans Classe
INSERT INTO Classe (nom_classe) VALUES
('6ème'),
('5ème'),
('4ème');
-- Fin d'insertion dans Classe
-- Insertion dans Cours
INSERT INTO Cours (matière_cours) VALUES
('Français'),
('Mathématiques'),
('Anglais');
-- Fin d'insertion dans Cours
-- Insertion dans Élève
-- On va insérer 9 élèves :
-- les 3 premiers sont en 6ème
-- les 3 suivants en 5ème
-- les 3 derniers en 4ème
INSERT INTO Élève (nom_élève, classe_id) VALUES
-- les élèves suivants sont en 6ème (classe_id = 1) :
('Alice', 1),
('Bernard', 1),
('Charlotte', 1),
-- les élèves suivants sont en 5ème (classe_id = 2) :
('Damien', 2),
('Éva', 2),
('Fabien', 2),
-- les élèves suivants sont en 4ème (classe_id = 3) :
('Géraldine', 3),
('Hervé', 3),
('Isabelle', 3);
-- Fin d'insertion dans Élève
-- Insertion dans Devoir
-- Les 6ème auront 1 devoirs de chaque cours
-- Les 5ème auront 2 devoirs de chaque cours
-- Les 4ème auront 3 devoirs de chaque cours
-- La conception de la table contraint d'insérer 3 devoirs x 3 cours
-- On prend la quantité maximale de devoir par cours de toutes les classes
INSERT INTO Devoir (cours_id) VALUES
-- les devoirs suivants concernent le Français (cours_id = 1)
(1), -- id = 1
(1), -- id = 2
(1), -- id = 3
-- les devoirs suivants concernent les Mathématiques (cours_id = 2)
(2),
(2),
(2),
-- les devoirs suivants concernent l'Anglais (cours_id = 3)
(3),
(3),
(3);
-- Le 2ème devoir de Mathématiques a l'id = 5
-- info :
-- ce 2ème devoir de Mathématiques ne sera pas attribué aux 6ème
-- car ils ne font qu'un seul devoir (1 < 2) de Mathématiques
-- Fin d'insertion dans Devoir
-- Insertion dans Noter
-- ATTENTION : bien relire avant d'exécuter le fichier sql !
INSERT INTO Noter (élève_id, devoir_id, valeur_noter) VALUES
-- Les notes des 6ème sont les suivantes (absences comprises) :
-- Alice a eu 15, 12, 18 sur 20 respectivement aux devoirs de
-- Français, Mathématiques et Anglais
-- Alice(id) = 1
-- 1erDevoirFrançais(id) = 1
-- 1erDevoirMaths(id) = 4
-- 1erDevoirAnglais(id) = 7
(1, 1, 15),
(1, 4, 12),
(1, 7, 18),
-- Bernard a eu 15, été absent, 16 sur 20 respectivement aux devoirs de
-- Français, Mathématiques et Anglais
-- Bernard(id) = 2
(2, 1, 15),
-- aucune insertion pour le devoir de Mathématiques ici
(2, 7, 16),
-- Charlotte a eu 10, 20, 19 sur 20 respectivement aux devoirs de
-- Français, Mathématiques et Anglais
-- Charlotte(id) = 3
(3, 1, 10),
(3, 4, 20),
(3, 7, 19),
-- Les notes des 5ème sont les suivantes (absences comprises) :
-- Damien a eu 13 puis 15 aux 2 devoirs de Français
-- il a eu 12 puis 13 aux 2 devoirs de Mathématiques
-- il a eu 15 puis 14 aux 2 devoirs d'Anglais
-- Damien(id) = 4
-- 2emeDevoirFrançais(id) = 2
-- 2èmeDevoirMaths(id) = 5
-- 2èmeDevoirAnglais(id) = 8
-- Devoirs de Français :
(4, 1, 13),
(4, 2, 15),
-- Devoirs de Mathématiques :
(4, 4, 12),
(4, 5, 13),
-- Devoirs d'Anglais :
(4, 7, 15),
(4, 8, 14),
-- Éva a eu 13 puis a été absente aux 2 devoirs de Français
-- elle a eu 16 puis 19 aux 2 devoirs de Mathématiques
-- elle a eu 16 puis 16 aux 2 devoirs d'Anglais
-- Éva(id) = 5
(5, 1, 13),
-- absente au 2ème devoir de Français
(5, 4, 16),
(5, 5, 19),
(5, 7, 16),
(5, 8, 16),
-- Fabien a eu 18 puis 14 aux 2 devoirs de Français
-- il a eu 19 puis a été absent aux 2 devoirs de Mathématiques
-- il a eu 19 puis 18 aux 2 devoirs d'Anglais
-- Fabien(id) = 6
(6, 1, 18),
(6, 2, 14),
(6, 4, 19),
-- absent
(6, 7, 19),
(6, 8, 18),
-- Les notes des 4èmes sont les suivantes (absences comprises) :
-- Géraldine a eu 17, absente, 19 aux 3 devoirs de Français
-- elle a eu absente, 18, 12 aux 3 devoirs de Mathématiques
-- elle a eu 12, 14, 15 aux 3 devoirs d'Anglais
-- 3emeDevoirFrançais(id) = 3
-- Géraldine(id) = 7
-- Devoirs de Français :
(7, 1, 17),
-- absente
(7, 3, 19),
-- Devoirs de Mathématiques :
-- absente
(7, 5, 18),
(7, 6, 12),
-- Devoirs d'Anglais :
(7, 7, 12),
(7, 8, 14),
(7, 9, 15),
-- Hervé a eu 9, 12, 11 aux 3 devoirs de Français
-- il a eu 20, 18, 18 aux 3 devoirs de Mathématiques
-- il a eu absent, 14, 16 aux 3 devoirs d'Anglais
-- Hervé(id) = 8
(8, 1, 9),
(8, 2, 12),
(8, 3, 11),
(8, 4, 20),
(8, 5, 18),
(8, 6, 18),
-- absent
(8, 8, 14),
(8, 9, 16),
-- Isabelle a eu 6, 8, 9 aux 3 devoirs de Français
-- elle a eu absente, absente, 13 aux 3 devoirs de Mathématiques
-- elle a eu 20, 18, 19 aux 3 devoirs d'Anglais
-- Isabelle(id) = 9
(9, 1, 6),
(9, 2, 8),
(9, 3, 9),
-- absente
-- absente
(9, 6, 13),
(9, 7, 20),
(9, 8, 18),
(9, 9, 19);
-- Fin d'insertion dans Noter
requete_test.sql:
-- Je veux les notes des 4ème
-- disposées ainsi :
-- nom de l'élève | matière du devoir | note | nom de la classe
-- par ordre croissant des devoirs
-- et par ordre croissant des élèves
SELECT
ELV.nom_élève 'nom de l\'élève',
CRS.matière_cours 'matière du devoir',
NTR.valeur_noter 'note',
CLS.nom_classe 'nom de la classe'
FROM
((((Classe CLS JOIN
Élève ELV ON CLS.id_classe = ELV.classe_id) JOIN
Noter NTR ON ELV.id_élève = NTR.élève_id) JOIN
Devoir DVR ON NTR.devoir_id = DVR.id_devoir) JOIN
Cours CRS ON DVR.cours_id = CRS.id_cours)
WHERE
CLS.nom_classe LIKE '%4%' -- '4ème'
ORDER BY
DVR.id_devoir,
ELV.nom_élève;
Bonjour tout le monde, veillez m'excuser pour tout ce temps de silence, je continuais les recherches de ce problème.
CristianoRolando, merci beaucoup, car c'est exactement ce que je voulais, la seule différence majeure est que ta requête sélectionne les notes en fonction d'une classe, or moi, je voulais que ma requête me donne la liste de notes d'un élève appartenant à une classe donné.
ta requête sélectionne les notes en fonction d'une classe.
Oui, c'est exactement ta demande initiale
Pour ta nouvelle requête, il te suffit de mettre une condition avec le nom de l'élève. Ça suffit si son nom est unique, sinon tu gardes la condition de la classe.
Aide sur une requête SQL avec MySQL
× Après avoir cliqué sur "Répondre" vous serez invité à vous connecter pour que votre message soit publié.
× Attention, ce sujet est très ancien. Le déterrer n'est pas forcément approprié. Nous te conseillons de créer un nouveau sujet pour poser ta question.
Ton présent détermine ton futur et la connaissance te placera au dessus de ta génération .