SQL avant la théorie Stéphane Gonnord, Laurent...

Contexte global Requêtes SQL Joindre deux tables Calculs d’agrégats Finalement...

Bases de données (2/4)SQL avant la théorie

Stéphane Gonnord, Laurent Jouhet

Lycée du parc - Lyon

PlanContexte global

Requêtes SQLFormat généralSélection, projectionOpérations ensemblistes

Joindre deux tablesProduit cartésien : non !JointureUtilité des clés

Calculs d’agrégatsPrincipeFormellementExercicesExercices

Finalement...

Rappels : Structured Query Language

• Base de donnée : ensemble de tables/relations.• Communes• Départements• Régions

• Table/relation : ensemble de n-uplets/lignes/tuples ayant tous lamême structure/le même schéma.

... ... ... ...1198 1 Joyeux 223

... ... ... ...69123 69 Lyon 484344

... ... ... ...• Schéma relationnel : décrit la structure des tables d’une base

commune(id:entier, dep:texte, nom:texte, pop:entier)

... ... ... ...1198 1 Joyeux 223

... ... ... ...69123 69 Lyon 484344

... ... ... ...

• Schéma relationnel : décrit la structure des tables d’une basecommune(id:entier, dep:texte, nom:texte, pop:entier)

... ... ... ...1198 1 Joyeux 223

... ... ... ...69123 69 Lyon 484344

... ... ... ...• Schéma relationnel : décrit la structure des tables d’une base

commune(id:entier, dep:texte, nom:texte, pop:entier)

Autres exemples

• Prénoms• Schéma relationnel :

(prenom:texte, classe:entier, sexe:texte)

• Exemples de ligne :

(’Pierre’, 843, ’M’)(’Olivier’, 842, ’M’)

• Communes/départements/régions• Schéma relationnel (3 tables) :

• communes(id:texte, dep:texte, nom:texte, pop:entier)• departements(id:texte, reg:entier, nom:texte)• regions(id:entier, nom:texte)

• Exemples de lignes/tuples :• (’2A041’, ’2A’, ’Bonifacio’, 2955)• (’2A’, 94, ’Corse-du-Sud’)• (94, ’Corse’)

Autres exemples

Communes, régions et départements

communeid dep nom pop

. . . . . . . . . . . .69023 69 Lyon 484344

. . . . . . . . . . . .2BO50 2B Calvi 5394

. . . . . . . . . . . .

departementid reg nom

. . . . . . . . .69 82 Rhône. . . . . . . . .2B 94 Haute-Corse. . . . . . . . .

regionid nom

. . . . . .82 Auvergne-Rhône-Alpes. . . . . .94 Corse. . . . . .

Élèves, colleurs et colles

elevesid nom prenom

0 Lyons Jacques-Louis1 Laurent Jean

. . . . . . . . .

profsid nom prenom

0 Théron Pierre1 Brun Jules

. . . . . . . . .

collesprof eleve semaine note

2 8 1 161 0 6 19

. . . . . . . . . . . .

Élèves, colleurs et colles

elevesid nom prenom

0 Lyons Jacques-Louis1 Laurent Jean

. . . . . . . . .

profsid nom prenom

0 Théron Pierre1 Brun Jules

. . . . . . . . .

collesprof eleve semaine note

2 8 1 161 0 6 19

. . . . . . . . . . . .

SQL, DDL, DML : ? ? ?

• SQL : Structured Query Language (« sequel »).

• DDL : Data Definition Language. CREATE, DROP. Pourcréer/supprimer une table dans une BD, selon un schémarelationnel.

• 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.

Format général : à connaître !

SELECT <attributs>, <calculs d’agrégats>FROM <tables>

JOIN ... ON ...WHERE <conditions>GROUP BY <attributs>HAVING <conditions>ORDER BY <attribut> ASC, <attribut> DESC

Dans telles tables, tu prends les lignes vérifiant telles conditions ; tules groupes selon tels critères. Dans les groupes, tu vas juste prendreceux dont telle moyenne (par exemple) sur tel attribut vérifie tellecondition.

Ah et puis tu vas me donner le résultat sous forme triée selon telsattributs !

Format général : à connaître !

SELECT <attributs>, <calculs d’agrégats>FROM <tables>

JOIN ... ON ...WHERE <conditions>GROUP BY <attributs>HAVING <conditions>ORDER BY <attribut> ASC, <attribut> DESC

Dans telles tables, tu prends les lignes vérifiant telles conditions ; tules groupes selon tels critères. Dans les groupes, tu vas juste prendreceux dont telle moyenne (par exemple) sur tel attribut vérifie tellecondition.Ah et puis tu vas me donner le résultat sous forme triée selon telsattributs !

Un exemple musclé

Ouch !

SELECT eleve, eleves.nom, COUNT(*) AS plantagesFROM eleves

JOIN colles ON eleve = ideWHERE note < 8GROUP BY eleveHAVING plantages = 5ORDER BY plantages

Alors, que vient-on de demander ?

Don’t panic... on va commencer par des choses plus simples !

Un exemple musclé

Ouch !

SELECT eleve, eleves.nom, COUNT(*) AS plantagesFROM eleves

JOIN colles ON eleve = ideWHERE note < 8GROUP BY eleveHAVING plantages = 5ORDER BY plantages

Alors, que vient-on de demander ?

Don’t panic... on va commencer par des choses plus simples !

Sélection, projection

• Requête de base :

SELECT <tels attributs>FROM <telle table>WHERE <telle(s) condition(s)>

• Exemples :• SELECT * -- tous les attributsFROM communes

• SELECT nom, popFROM communes

• SELECT nom, popFROM communesWHERE pop > 100000

• SELECT *FROM trianglesWHERE ab = bc AND bc = ac -- un seul =

Opérations ensemblistes

• Union, intersection, différence : Bof...

• Rarement utile en pratique• Mais au programme !• Attention à ne pas écrire n’importe quoi (torchons, serviettes...)

• SQL :• SELECT ... FROM ...

UNIONSELECT ... FROM ...

• SELECT ... FROM ... INTERSECT SELECT ... FROM ...• SELECT ... FROM ... EXCEPT SELECT ... FROM ...

• Et si les attributs sont différents ? n’importe quoi• Produit cartésien :

• Définition : comme en maths :

A×B = {(a,b) |a ∈ A et b ∈ B}

• SQL : pas comme en maths !SELECT * from table1 , table2

• Union, intersection, différence : Bof...• Rarement utile en pratique

• Mais au programme !• Attention à ne pas écrire n’importe quoi (torchons, serviettes...)

A×B = {(a,b) |a ∈ A et b ∈ B}

• Union, intersection, différence : Bof...• Rarement utile en pratique• Mais au programme !

• Attention à ne pas écrire n’importe quoi (torchons, serviettes...)• SQL :

• SELECT ... FROM ...UNIONSELECT ... FROM ...

A×B = {(a,b) |a ∈ A et b ∈ B}

• Union, intersection, différence : Bof...• Rarement utile en pratique• Mais au programme !• Attention à ne pas écrire n’importe quoi (torchons, serviettes...)

• Et si les attributs sont différents ?

n’importe quoi• Produit cartésien :

A×B = {(a,b) |a ∈ A et b ∈ B}

• Et si les attributs sont différents ? n’importe quoi

• Produit cartésien :• Définition : comme en maths :

A×B = {(a,b) |a ∈ A et b ∈ B}

De l’inutilité du produit cartésien

• Deux tables :communenom dep

Lyon 69Calvi 2BCorte 2B

departementid nom

69 Rhône2B Haute-Corse

• Et leur produit :commune×departement

nom dep id nom

Lyon 69 69 RhôneLyon 69 2B Haute-CorseCalvi 2B 69 RhôneCalvi 2B 2B Haute-CorseCorte 2B 69 RhôneCorte 2B 2B Haute-Corse

De l’inutilité du produit cartésien

departementid nom

• Et leur produit :commune×departement

nom dep id nom

Lyon 69 69 RhôneLyon 69 2B Haute-CorseCalvi 2B 69 RhôneCalvi 2B 2B Haute-CorseCorte 2B 69 RhôneCorte 2B 2B Haute-Corse

Jointure

departementid nom

• Et une jointure naturelle :communeondepartement

nom dep id nom

Lyon 69 69 RhôneCalvi 2B 2B Haute-CorseCorte 2B 2B Haute-Corse

• Une base de données est pensée dès le départ autour desjointures de tables.

Jointure

departementid nom

• Et une jointure naturelle :communeondepartement

nom dep id nom

Lyon 69 69 RhôneCalvi 2B 2B Haute-CorseCorte 2B 2B Haute-Corse

• Une base de données est pensée dès le départ autour desjointures de tables.

Jointure

• Formellement : R1 ona=b

R2 ⊂ R1×R2... (cf. algèbre relationnelle)

• Et si R1 et R2 ont deux attributs de même nom ?

• En SQL : deux syntaxes équivalentes :

• SELECT ...FROM table1 JOIN table2

ON conditionWHERE...

• SELECT ...FROM table1, table2WHERE condition AND ...

• SQL, encore :

• WHERE table1.foo = table2.bar• SELECT ...FROM table1 JOIN table2 JOIN table3

ON condition1 AND condition2WHERE...

Jointure

• SQL, encore :

Jointure

• SQL, encore :

Jointure

• SQL, encore :

Exemples, utilité des clés

• Clé : ensemble d’attributs caractérisant les éléments d’une table.

• Clé primaire : attribut UNIQUE caractérisant les éléments d’unetable.

• Bien avoir le schéma relationnel devant les yeux.• Exemples :

• FROM communes JOIN departements -- Ne pas oublier SELECTON communes.dep = departements.id

• FROM eleves JOIN collesON ide = eleve

• FROM eleves JOIN colles JOIN profsON ide = eleve AND prof = idp

• FROM clubs AS c1 JOIN matchs JOIN clubs AS c2ON c1.idc = eq1 AND c2.idc = eq2

• Bien avoir le schéma relationnel devant les yeux.

• Exemples :• FROM communes JOIN departements -- Ne pas oublier SELECT

ON communes.dep = departements.id• FROM eleves JOIN colles

ON ide = eleve• FROM eleves JOIN colles JOIN profs

ON ide = eleve AND prof = idp• FROM clubs AS c1 JOIN matchs JOIN clubs AS c2

ON c1.idc = eq1 AND c2.idc = eq2

Agrégats

• Principe :

1. On regroupe (en général) les lignes suivant des attributs ;2. On applique une fonction d’agrégation à chacun de ces groupes.3. On peut encore une fois sélectionner des lignes avec HAVING

• Fonctions : MIN, MAX, COUNT(...), AVG(...), SUM(...)

• SQL :

SELECT reg, departements.nom, COUNT(*)FROM departements JOIN regions

ON reg = regions.idGROUP BY reg

COUNT(*) va compter le nombre de lignes dans chaque groupe !

reg nom COUNT(*)

1 Guadeloupe 111 Seine-et-Marne 8. . . . . . . . .

Agrégats

• Principe :

• SQL :

reg nom COUNT(*)

Agrégats

• Principe :

• SQL :

reg nom COUNT(*)

Agrégats

• Principe :

• SQL :

reg nom COUNT(*)

Agrégats

• Condition en amont (WHERE) et/ou aval (HAVING)

SELECT reg, departements.nom, COUNT(*) AS nb_depFROM departements JOIN regions

ON reg = regions.idGROUP BY regHAVING nb_dep >= 5

reg nom count(*)

11 Seine-et-Marne 824 Cher 6. . . . . . . . .

• À retenir :

Penser à projeter (SELECT) les attributs selon lesquels on agroupé (GROUP BY) les lignes.

Agrégats

reg nom count(*)

• À retenir :

Agrégats

reg nom count(*)

• À retenir :

Exercices

Avec les tables aux schémas relationnels déjà vus, déterminer...

• la note maximale parmi toutes les colles ;

• SELECT MAX(note)FROM colles

• la note maximale parmi toutes les colles de Jaques-Louis Lions ;

• SELECT MAX(note)FROM colles JOIN eleves

ON ide = eleveWHERE nom="Lions"

• la liste des moyennes des élèves ;

• SELECT eleve, nom, AVG(note)FROM colles JOIN eleves

ON ide = eleveGROUP BY eleve

Exercices

• la liste des départements avec leur nombre de communes ;

• SELECT dep, departements.nom, COUNT(*)FROM departements JOIN communes

ON dep = departements.idGROUP BY dep

• la liste des départements avec leur nombre de communes ;

• SELECT dep, departements.nom, COUNT(*)FROM departements JOIN communes

ON dep = departements.idGROUP BY dep

Un algorithme de conception de requêtes

Plusieurs passages sont possibles...

1. SELECT : quels attributs (et/ou agrégats) nous intéressent ?

2. FROM : issus de quelles tables ?

3. JOIN ... ON : si on joint n tables, il y a a priori n−1 conditionsde jointures.

4. WHERE : quelles conditions/restrictions en amont ?

5. GROUP BY : comment veut-on regrouper les tuples ? ajouter auSELECT.

6. HAVING : restrictions en aval, portant sur les agrégats.

7. Sous-requètes éventuelles, paramétrées ou non ; utilisation dePython...

SQL avant la théorie Stéphane Gonnord, Laurent...

Documents

III. Le langage d'interrogation SQL · 2020. 1. 28. · SQL est un langage textuel aux règles syntaxiques précises. ... SQL interactif SQL intégré SQL dynamique LDD LMD LCD CREATE

Manuels 5429fr2 Complements 5532 FR2de Lp Ch1

Langage SQL (1) - univ-orleans.fr · SQL server Access MySQL Sébastien Limet, Denys Duchier Langage SQL (1) ... Introduction Le langage SQL : données Le langage SQL : …

SQL Le langage de définition des données de SQL · BDA.5.2 Introduction n SQL : Structured Query Language n SQL est normalisé uSQL 2: adopté (SQL 92) uSQL 3: adopté (SQL 99)

RAPPELS ET COMPLEMENTS DE THERMODYNAMIQUE …

COMPLEMENTS - Vaucluse

LP Informatique(DA2I), F7 : Administration Système ...nakechb.free.fr/LP_DA2I/ch1_ABD.pdf · 2.3 Architecture client-serveur ... SQL SQL SQL SQL. 2.5 Architecture répartie SGBD

Sql Loader

Expose SQL

Migrez vos bases de données vers SQL Server et SQL Azure avec Microsoft SQL Server migration assistant 2008

Cours SGBD · Introduction au langage PL/SQL PL/SQL (Procedural Language / Structured Query Language) est une extension procédurale à SQL : PL/SQL intègre parfaitement le langage

Initiation SQL

Oracle 12c - SQL, PL/SQL, SQL*Plus - multimedia.fnac.commultimedia.fnac.com/multimedia/editorial/pdf/9782746094451.pdf · 29,90 € ISBN : 978-2-7460-9445-1 Oracle 12c - SQL, PL/SQL,

BASES DE DONNEES RELATIONNELLE Algèbre relationnelle et ...bliaudet.free.fr/IMG/pdf/Cours_SGBD-SQL_-_04-Complements.pdf · COMPLEMENTS DE SQL 3 1. Gestion des utilisateurs et de

Acf liste de prix lits & complements acf

CALC Les complements a 10 Je connais par coeur tous les ...ekladata.com/p9HHxQVqurcPy5p7DQIornzRaT8/MES-TRACES-ECRITES... · CALC 1 Les complements a 10 Je connais par coeur tous

COMPLEMENTS AUX «LOIS DU JEU DE FOOTBALL»

PL/ SQL - salihayacoub.comsalihayacoub.com/420Keh/Semaine 3/PLSQLIntro.pdf · PL/ SQL •Introduction: –PL/SQL (Procedural Language SQL) est un langage procedural structuré en

Codage des nombres (entiers) Laure Gonnord › bloc1 › bloc1v2 › DIU_bloc1... · 2020-06-28 · Codage des nombres (entiers) Laure Gonnord Universit e de Lyon Mars 2020. Source

Le langage PL/SQL 2 - Compléments · ! dans des outils Oracle, Forms et Report en particulier . Bernard ESPINASSE - Introduction à PL/SQL : 2 - Complements 9 • Un