Bases de données (2/4) Stéphane Gonnord Bases de données (2/4) SQL avant la théorie Contexte global Requêtes SQL Format général Sélection, projection Opérations ensemblistes Joindre deux tables Stéphane Gonnord Produit cartésien : non ! Jointure Utilité des clés Calculs d’agrégats [email protected] www.mp933.fr Principe Formellement Exercices Finalement... Lycée du parc - Lyon Mercredi 14 et 21 mai 2014 Lycée du parc Plan Bases de données (2/4) Stéphane Gonnord Contexte global Requêtes SQL Format général Sélection, projection Opérations ensemblistes Contexte global Requêtes SQL Format général Sélection, projection Opérations ensemblistes Joindre deux tables Produit cartésien : non ! Jointure Utilité des clés Joindre deux tables Produit cartésien : non ! Jointure Utilité des clés Calculs d’agrégats Principe Formellement Exercices Finalement... Calculs d’agrégats Principe Formellement Exercices Finalement... Bases de données (2/4) Rappels Stéphane Gonnord I Base de donnée : ensemble de tables/relations. I I I Communes Départements Régions Contexte global Requêtes SQL Format général Sélection, projection Opérations ensemblistes Joindre deux tables I Table/relation : ensemble de n-uplets/lignes/tuples ayant tous la même structure/le même schéma. ... 1198 ... 69123 ... I ... 1 ... 69 ... ... Joyeux ... Lyon ... ... 223 ... 484344 ... Schéma relationnel : décrit la structure des tables d’une base commune(id:entier, dep:texte, nom:texte, pop:entier) Produit cartésien : non ! Jointure Utilité des clés Calculs d’agrégats Principe Formellement Exercices Finalement... Autres exemples Bases de données (2/4) Stéphane Gonnord I Prénoms I Contexte global Schéma relationnel : (prenom:texte, nombre:entier, sexe:texte, annee:entier) Requêtes SQL Format général Sélection, projection I Exemples de ligne : (’Antonio’, 5, ’M’, 2012) (’Stéphane’, 6, ’M’, 2013) I Communes/départements/régions I Schéma relationnel : Opérations ensemblistes Joindre deux tables Produit cartésien : non ! Jointure Utilité des clés Calculs d’agrégats Principe Formellement Exercices Finalement... communes(id:entier,dep:texte,nom:texte,pop:entier) departements(id:texte, reg:entier, nom:texte) regions(id:entier, nom:texte) I Exemples de lignes/tuples : (’2A041’, ’2A’, ’Bonifacio’, 2955) (’2A’, 94, ’Corse-du-Sud’) (94, ’Corse’) Communes, régions et départements Bases de données (2/4) Stéphane Gonnord Contexte global commune id dep nom pop Requêtes SQL ... 69023 ... 2BO50 ... ... 69 ... 2B ... ... Lyon ... Calvi ... ... 484344 ... 5394 ... Sélection, projection Format général Opérations ensemblistes Joindre deux tables Produit cartésien : non ! Jointure Utilité des clés Calculs d’agrégats Principe Formellement departement region id reg nom id nom ... 69 ... 2B ... ... 82 ... 94 ... ... Rhône ... Haute-Corse ... ... 82 ... 94 ... ... Rhône-Alpes ... Corse ... Exercices Finalement... Bases de données (2/4) Élèves, colleurs et colles Stéphane Gonnord eleves Contexte global profs id nom prenom id nom prenom Requêtes SQL 0 1 ... Lyons Laurent ... Jacques-Louis Jean ... 0 1 ... Théron Brun ... Pierre Jules ... Sélection, projection Format général Opérations ensemblistes Joindre deux tables Produit cartésien : non ! Jointure Utilité des clés Calculs d’agrégats colles Principe prof eleve semaine note 2 1 ... 8 0 ... 1 6 ... 16 19 ... Formellement Exercices Finalement... SQL, DDL, DML : WTF ? Bases de données (2/4) Stéphane Gonnord I I I I SQL : Structured Query Language (the name of the game ; « sequel »). DDL : Data Definition Language. CREATE, DROP. Pour créer/supprimer une table dans une BD, selon un schéma relationnel. DML : Data Manipulation Language. INSERT, UPDATE, DELETE. Pour alimenter les tables d’une BD : nouvelle ligne, suppression, modification. Interrogation des données : SELECT. Pour faire une requête. Contexte global Requêtes SQL Format général Sélection, projection Opérations ensemblistes Joindre deux tables Produit cartésien : non ! Jointure Utilité des clés Calculs d’agrégats Principe Formellement Exercices Finalement... Format général Bases de données (2/4) Stéphane Gonnord Contexte global SELECT <expressions> FROM <tables> WHERE <conditions> GROUP BY <attributs> HAVING <conditions> ORDER BY <attributs> Requêtes SQL Format général Sélection, projection Opérations ensemblistes Joindre deux tables Produit cartésien : non ! Jointure Utilité des clés Calculs d’agrégats Principe Formellement Dans telles tables, tu prends les lignes vérifiant telles conditions ; tu les groupes selon tels critères. Dans les groupes, tu va juste prendre ceux dont telle moyenne (par exemple) sur tel attribut vérifie telle condition. Ah et puis tu vas me donner le résultat sous forme triée selon tels attributs ! Exercices Finalement... Un exemple musclé Bases de données (2/4) Stéphane Gonnord Contexte global Requêtes SQL Wow ! SELECT eleve, eleves.nom, COUNT(*) AS plantages FROM eleves JOIN colles ON eleve = ide WHERE note<8 GROUP BY eleve HAVING plantages>=5 ORDER BY plantages Alors, que vient-on de demander ? Don’t panic... on va commencer par des choses plus simples ! Format général Sélection, projection Opérations ensemblistes Joindre deux tables Produit cartésien : non ! Jointure Utilité des clés Calculs d’agrégats Principe Formellement Exercices Finalement... Sélection, projection Bases de données (2/4) Stéphane Gonnord I Requête de base : SELECT <tels attributs> FROM <telle table> WHERE <telle(s) condition(s)> I Exemples : I SELECT * FROM communes I SELECT nom,pop FROM communes I SELECT nom,pop FROM communes WHERE pop > 100000 I SELECT * FROM triangles WHERE ab = bc AND bc = ac Contexte global Requêtes SQL Format général Sélection, projection Opérations ensemblistes Joindre deux tables Produit cartésien : non ! Jointure Utilité des clés Calculs d’agrégats Principe Formellement Exercices Finalement... Opérations ensemblistes Bases de données (2/4) Stéphane Gonnord I Union, intersection, différence : Bof... I I I I I I SELECT ... FROM ... UNION SELECT ... FROM ... SELECT ... FROM ... INTERSECT SELECT ... FROM ... SELECT ... FROM ... EXCEPT SELECT ... FROM ... Et si les attributs sont différents ? Je ne veux même pas savoir ce qui se passe ! Produit cartésien : I Définition : comme en maths : A × B = {(a, b) | a ∈ A et b ∈ B } I Contexte global Requêtes SQL Format général Sélection, projection Opérations ensemblistes SQL : I I Rarement utile... Souvent déconnant (torchons, serviettes...) SQL : pas comme en maths ! SELECT * from table1 , table2 Joindre deux tables Produit cartésien : non ! Jointure Utilité des clés Calculs d’agrégats Principe Formellement Exercices Finalement... De l’inutilité du produit cartésien Bases de données (2/4) Stéphane Gonnord I Deux bases : Contexte global commune I nom dep Lyon Calvi Corte 69 2B 2B departement id nom 69 2B Rhône Haute-Corse Requêtes SQL Format général Sélection, projection Opérations ensemblistes Joindre deux tables Produit cartésien : non ! Jointure Utilité des clés Et leur produit : Calculs d’agrégats commune×departement Principe Formellement nom Lyon Lyon Calve Calvi Corte Corte dep 69 69 2B 2B 2B 2B id 69 2B 69 2B 69 2B nom Rhône Haute-Corse Rhône Haute-Corse Rhône Haute-Corse Exercices Finalement... Bases de données (2/4) Jointure Stéphane Gonnord I Deux bases : Contexte global commune I nom dep Lyon Calvi Corte 69 2B 2B departement id nom 69 2B Rhône Haute-Corse Lyon Calvi Corte I 69 2B 2B Sélection, projection Opérations ensemblistes Joindre deux tables Produit cartésien : non ! Utilité des clés Calculs d’agrégats communeo ndepartement dep Format général Jointure Et leur jointure naturelle : nom Requêtes SQL id 69 2B 2B nom Rhône Haute-Corse Haute-Corse Une base de données est pensée dès le départ autour des jointures de tables. Principe Formellement Exercices Finalement... Bases de données (2/4) Jointure Stéphane Gonnord I Formellement : R1 o n R2 ⊂ R1 × R2 ... a=b I Et si R1 et R2 ont deux attributs de même nom ? I En SQL : deux syntaxes équivalentes : I I SELECT ... FROM table1 JOIN table2 ON condition WHERE... SELECT ... FROM table1 , table2 WHERE condition AND ... I SQL, encore : I I WHERE table1.foo = table2.bar SELECT ... FROM table1 JOIN table2 JOIN table3 ON condition1 AND condition2 WHERE... Contexte global Requêtes SQL Format général Sélection, projection Opérations ensemblistes Joindre deux tables Produit cartésien : non ! Jointure Utilité des clés Calculs d’agrégats Principe Formellement Exercices Finalement... Exemples, utilité des clés Bases de données (2/4) Stéphane Gonnord I I Clé : ensemble d’attributs caractérisant les éléments d’une table. Clé primaire : UN attribut caractérisant les éléments d’une table. I Bien avoir le schéma relationnel devant les yeux. I Exemples : I FROM communes JOIN departements ON communes.dep = departements.id I FROM eleves JOIN colles ON ide = eleve I FROM eleves JOIN colles JOIN profs WHERE ide = eleve AND prof = idp I FROM clubs c1 JOIN matchs JOIN clubs c2 ON c1.idc = eq1 AND c2.idc=eq2 Contexte global Requêtes SQL Format général Sélection, projection Opérations ensemblistes Joindre deux tables Produit cartésien : non ! Jointure Utilité des clés Calculs d’agrégats Principe Formellement Exercices Finalement... Bases de données (2/4) Agrégats Stéphane Gonnord I Principe : I I Contexte global On regroupe (en général) les lignes suivant des attributs ; on applique une fonction d’agrégation à chacun de ces groupes. Requêtes SQL Format général Sélection, projection Opérations ensemblistes Joindre deux tables Produit cartésien : non ! Jointure I Fonctions : MIN, MAX, COUNT(...), AVG, SUM(...) Utilité des clés Calculs d’agrégats I SQL : Principe Formellement SELECT reg,departements.nom,count(*) FROM departements JOIN regions ON reg = regions.id GROUP BY reg reg nom count(*) 1 11 ... Guadeloupe Seine-et-Marne ... 1 8 ... Exercices Finalement... Bases de données (2/4) Agrégats Stéphane Gonnord I Condition en amont (WHERE) et/ou aval (HAVING) SELECT reg,departements.nom,count(*) FROM departements JOIN regions ON reg = regions.id GROUP BY reg HAVING count(*)>=5 Contexte global Requêtes SQL Format général Sélection, projection Opérations ensemblistes Joindre deux tables Produit cartésien : non ! Jointure Utilité des clés Calculs d’agrégats reg nom count(*) 11 24 ... Seine-et-Marne Cher ... 8 6 ... Principe Formellement I À retenir : On projette les attributs selon lesquels on a groupé les lignes. Exercices Finalement... Exercices Bases de données (2/4) Stéphane Gonnord Avec les tables aux schémas relationnels déjà vus, déterminer... I la note maximale parmi toutes les colles ; I la note maximale parmi toutes les colles de Jaques-Louis Lions ; Contexte global Requêtes SQL Format général Sélection, projection Opérations ensemblistes Joindre deux tables Produit cartésien : non ! Jointure Utilité des clés I la liste des moyennes des élèves ; I la liste des départements avec leur nombre de communes ; I le département qui a le plus de communes ; I le département qui est le plus peuplé. Calculs d’agrégats Principe Formellement Exercices Finalement... Un algorithme de conception de requêtes Bases de données (2/4) Stéphane Gonnord Plusieurs passages sont possibles... 1. SELECT : quels attributs (et/ou agrégats) nous intéressent ? Contexte global Requêtes SQL Format général Sélection, projection Opérations ensemblistes 2. FROM : issus de quelles tables ? 3. JOIN ... ON : si on joint n tables, il y a a priori n − 1 conditions de jointures. 4. WHERE : quelles conditions/restrictions en amont ? Joindre deux tables Produit cartésien : non ! Jointure Utilité des clés Calculs d’agrégats Principe Formellement Exercices 5. GROUP BY : comment veut-on regrouper les tuples ? ajouter au SELECT. 6. HAVING : restrictions en aval, portant sur les agrégats. 7. Sous-requètes éventuelles, paramétrées ou non. Finalement... C’est fini Bases de données (2/4) Stéphane Gonnord Contexte global Merci de votre attention Requêtes SQL Format général Sélection, projection Opérations ensemblistes Joindre deux tables Produit cartésien : non ! Jointure Utilité des clés Calculs d’agrégats Principe Formellement Exercices Finalement...
© Copyright 2024 ExpyDoc
