Cours 3
This presentation is the property of its rightful owner.
Sponsored Links
1 / 41

COURS 3 PowerPoint PPT Presentation


  • 140 Views
  • Uploaded on
  • Presentation posted in: General

COURS 3. Introduction à SQL. Motivation : Améliorer la lisibilité des requêtes. SQL (Structured Query Language) Langage de Manipulation de Bases de Données développé par IBM (San José, 1981) Interrogation de bases de données ; Manipulation de bases de données dans des programmes.

Download Presentation

COURS 3

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Cours 3

COURS 3

Introduction à SQL


Motivation am liorer la lisibilit des requ tes

Motivation : Améliorer la lisibilité des requêtes

SQL (Structured Query Language)

Langage de Manipulation de Bases de Données développé par IBM (San José, 1981)

Interrogation de bases de données ; Manipulation de bases de données dans des programmes.

qUn standard industriel

NB. Il existe des dialectes “ constructeurs ”

e.g., le dialecte Access


Plan de la pr sentation

Plan de la présentation

1)Interrogation de la base de données

2)Mise à jour de la base de données

3)Gestion des tables

4)Un exemple de construction de requête


Mod les repr sentatifs d une bd

Modèles représentatifs d’une BD

Modèle Conceptuel de DonnéesModèle Logique de données

Schéma Relationnel

EQUIPES (N°Equipe, NomEquipe, Ville, Entraineur)

JOUEURS (N°Joueur, N°Equipe, NomJoueur, PrénomJoueur)

MATCHS (N°Match, N°Locaux, N°Visiteurs, Date, NbSpectateurs, NbPtsLocaux, NbPtsVisiteurs)

A_JOUE (N°Joueur, N°Match, NbPoints Marqués, NbFautes)


Une extension

Une extension

  • Table EQUIPES

  • Table JOUEURS

  • Table MATCHS


Une extension1

Une extension

  • Table A_JOUE


1 l interrogation

-1- L’interrogation

  • Forme générale :

    SELECT liste-de-noms-de-colonnes

    FROM liste-de-tables ;

    SELECT *

    FROM JOUEURS ;

    Algèbre relationnelle :

    JOUEURS


1 1 la projection

-1.1- La projection

  • La forme générale réalise une PROJECTION sans élimination des doubles (sélection de colonnes d'une table) :

    SELECTPrénomJoueur

    FROMJOUEURS ;

    Algèbre relationnelle :

    pPrénomJoueur (JOUEURS)

  • Projection avec élimination des doubles

    SELECTDISTINCT liste-de-noms-de-colonnes

    FROM liste-de-tables ;

    SELECTDISTINCT JOUEURS.PrénomJoueur

    FROM JOUEURS ;


1 2 la s lection

-1.2- La sélection

SELECT liste-de-noms-de-colonnes

FROM liste-de-tables

WHERE qualification ;

Une sélection est une restriction selon la qualification suivie d'une projection pour ne garder que les colonnes désignées.

  • Liste des joueurs de l'équipe 1 :

    SELECTNomJoueur, N°Equipe

    FROMJOUEURS

    WHEREN°Equipe = 1 ;

    Algèbre relationnelle :

    p NomJoueur, N°Equipe (sN°Equipe = 1 (JOUEURS))


1 2 1 pr sentation du r sultat

-1.2.1- Présentation du résultat

Tri des lignes de la table résultat.

ORDER BY nom-de-colonne [ordre] [, nom-de-colonne [ordre]] ...

ordre : ASC ou DESC ascendant ou descendant

SELECT NomJoueur, N°Equipe

FROM JOUEURS

WHERE N°Equipe = 1

ORDER BYNomJoueur ASC;


1 2 1 qualification compos e

-1.2.1- Qualification composée

La qualification peut être composée de plusieurs conditions combinées à l’aide des opérateurs booléens AND (et), OR(ou) et NOT (négation).

Les opérateurs de comparaisons sont =, <> ou !=, <, <=, > et enfin >=.

  • Liste des matchs ayant eu lieu chez l'équipe 1 avec plus de 6000 spectateurs :

    SELECTN°Match, N°Locaux, N°Visiteurs, Date, NbSpectateurs

    FROM MATCHS

    WHEREN°Locaux = 1 AND NbSpectateurs > 6000 ;

    Algèbre relationnelle :

    pN°Match, N°Locaux, N°Visiteurs, Date, NbSpectateurs

    (sN°Locaux = 1 AND NbSpectateurs > 6000 (MATCHS))


1 3 le produit cart sien

-1.3- Le produit cartésien

Produit cartésien entre EQUIPES et MATCHS avec une projection pour ne pas avoir toutes les colonnes des deux tables :

SELECTEQUIPES.N°Equipe, EQUIPES.NomEquipe,

MATCHS.N°Match , MATCHS.N°Locaux,

MATCHS.Date, MATCHS.NbSpectateur,

FROM EQUIPES, MATCHS ;

pEQUIPES.N°Equipe,EQUIPES.NomEquipe,MATCHS.N°Match,MATCHS.N°Locaux,MATCHS.Date, MATCHS.NbSpectateur(EQUIPESÄMATCHS )


1 4 la jointure

-1.4- La jointure

La jointure est le produit cartésien de deux relations avec une sélection.

  • Liste des matchs où les équipes reçoivent :

    SELECTEQUIPES.N°Equipe, EQUIPES.NomEquipe,

    MATCHS.N°Match , MATCHS.N°Locaux,

    MATCHS.Date, MATCHS.NbSpectateur,

    FROM EQUIPES, MATCHS

    WHEREEQUIPES.N°Equipe = MATCHS.N°Locaux;

    pEQUIPES.N°Equipe,EQUIPES.NomEquipe, MATCHS.N°Match

    MATCHS.N°Locaux, MATCHS.Date, MATCHS.NbSpectateurs

    (EQUIPES EQUIPES.N°Equipe=MATCHS.N°Locaux MATCHS)

    pEQUIPES.N°Equipe,EQUIPES.NomEquipe,MATCHS.N°Match,MATCHS.N°Locaux,MATCHS.Date, MATCHS.NbSpectateur(EQUIPESÄMATCHS )


1 4 1 jointure de 3 tables

-1.4.1- Jointure de 3 tables

  • Liste des points marqués par les joueurs à domicile contre l'équipe 3.

    SELECTJOUEURS.NomJoueur, MATCHS.N°Match,

    MATCHS.Date, A_JOUE.NbPointsMarqués,

    A_JOUE.NbFautes, MATCHS.N°Visiteur

    FROM MATCHS ,JOUEURS, A_JOUE

    WHEREJOUEURS.N°Joueur = A_JOUE.N°Joueur

    and MATCHS.N°Match = A_JOUE.N°Match

    and MATCHS.N°Visiteur=3 ;

    pJOUEURS.NomJoueur, MATCHS.N°Match, MATCHS.Date,

    A_JOUE.NbPointsMarqués, A_JOUE.NbFautes, MATCHS.N°Visiteur

    (sJOUEURS.N°Joueur = A_JOUE.N°Joueur

    and MATCHS.N°Match = A_JOUE.N°Match

    and MATCHS.N°Visiteur=3

    (MATCHS Ä JOUEURS Ä A_JOUE) )

    pEQUIPES.N°Equipe,EQUIPES.NomEquipe,MATCHS.N°Match,MATCHS.N°Locaux,MATCHS.Date, MATCHS.NbSpectateur(EQUIPESÄMATCHS )


1 4 2 imbrication de requ tes

-1.4.2- Imbrication de requêtes

  • par utilisation d'opérateurs ensemblistes :

    IN : appartenance à un ensemble

    {pour Access, l’ensemble peut aussi être une base

    de données externe, dBase ou Paradox par exemple}

    NOT IN :non appartenance

  • Même requête que la précédente mais avec projection sur

    NomJoueur seulement.

    SELECT JOUEURS.NomJoueur

    FROMJOUEURS

    WHEREJOUEURS.N°Joueur IN

    (SELECT A_JOUE.N°Joueur

    FROM MATCHS, A_JOUE

    WHEREMATCHS.N°Match = A_JOUE N°Match

    and MATCHS.N°Visiteur=3 );


1 4 2 imbrication de requ tes1

-1.4.2- Imbrication de requêtes

Remarque : pour Access, IN est aussi un opérateur qui détermine si la valeur d’une expression est égale à une ou plusieurs valeurs d’une liste.

Algèbre relationnelle :

pJOUEURS.NomJoueur

(sJOUEURS.N°Joueur = A_JOUE.N°Joueur

and MATCHS.N°Match = A_JOUE.N°Match

and MATCHS.N°Visiteur=3

(MATCHS Ä JOUEURS Ä A_JOUE) )

ANY :Un ou plusieurs éléments de l'ensemble satisfont

la condition.

ALL :Tous les éléments de l'ensemble satisfont

la condition.


1 4 2 imbrication de requ tes2

-1.4.2- Imbrication de requêtes

  • Liste des matchs où le tiers des points marqués par les locaux est inférieur à au moins un nombre de points marqués par un joueur :

  • SELECTMATCHS.N°Match, MATCHS.N°Locaux,

    MATCHS.NbPtsLocaux , A_JOUE.N°Joueur,

    A_JOUE.NbPointsMarqués

    FROMMATCHS, A_JOUE

    WHERE MATCHS.N°Match = A_JOUE.N°Match

    andMATCHS.NbPtsLocaux / 3 < ANY

    (SELECT NbPointsMarqués

    FROM A_JOUE);


1 5 autres possibilit s d interrogation

-1.5- Autres possibilités d ’interrogation

UNION : sélection de lignes appartenant à l'une ou à l'autre

de 2 relations (avec suppression des doublons).

  • Liste des noms des entraineurs et des joueurs :

    SELECTJOUEURS.NomJoueur

    FROMJOUEURS

    UNION

    SELECT EQUIPES.Entraineur

    FROMEQUIPE;

  • D’autres opérateurs SQL existent ...

    INTERSECT :sélection de lignes appartenant aux 2 relations.

    MINUS : sélection de lignes d'une relation n'appartenant pas

    à l'autre relation.


1 6 op rateurs d agr gats

-1.6- Opérateurs d’agrégats

AVG :Moyenne d'un ensemble de valeurs

SUM :Somme d'un ensemble de valeurs

COUNT :Nombre de valeurs d'un ensemble

MAX :Valeur Maximum d'un ensemble de valeurs

MIN :Valeur Minimum d'un ensemble de valeurs

  • Moyenne de spectateurs par match :

    SELECTAVG(MATCHS.NbSpectateurs)

    FROM MATCHS;

    ou

    SELECTSUM(MATCHS.NbSpectateurs) / COUNT(*)

    FROM MATCHS;


1 6 op rateurs d agr gats1

-1.6- Opérateurs d’agrégats

  • Maximum de spectateurs dans un match :

    SELECTMAX(MATCHS.NbSpectateurs)

    FROM MATCHS;

  • Nombre total de joueurs :

    SELECTCOUNT(*)

    FROM JOUEURS;


1 7 partition de relations

-1.7- Partition de relations

GROUP BY : application des opérateurs d'agrégat sur des

sous-relations obtenues.

  • Liste des moyennes de points marqués par les joueurs et leur moyenne de fautes :

    SELECTA_JOUE.N°Joueur,

    AVG(A_JOUE.NbPointsMarqués),

    AVG(A_JOUE.NbFautes)

    FROM A_JOUE

    GROUP BYA_JOUE.N°Joueur;


1 7 partition de relations1

-1.7- Partition de relations

HAVING : possibilité d'appliquer des conditions sur

les sous-relations.

  • Liste des moyennes de points marqués par les joueurs et leur moyenne de fautes pour ceux dont la moyenne est > à 2.5 :

    SELECT A_JOUE.N°Joueur,

    AVG(A_JOUE.NbPointsMarqués),

    AVG(A_JOUE.NbFautes)

    FROM A_JOUE

    GROUP BY A_JOUE.N°Joueur

    HAVINGAVG(A_JOUE.NbFautes) > 2.5 ;


1 7 partition de relations2

-1.7- Partition de relations

WHERE et GROUP BY :

  • Liste des moyennes de points marqués par les joueurs et leur moyenne de fautes, pour les matchs 2 et 3 :

    SELECTA_JOUE.N°Joueur, AVG(A_JOUE.NbPointsMarqués),

    AVG(A_JOUE.NbFautes)

    FROM A_JOUE

    WHEREA_JOUE.N°Match=2 OR A_JOUE.N°Match=3

    GROUP BY A_JOUE.N°Joueur;


1 7 partition de relations3

-1.7- Partition de relations

WHERE, GROUP BY et HAVING :

Sélection selon la condition du WHERE, puis réalisation du GROUP BY et enfin sélection selon la condition du HAVING.

  • Pour les matchs 2 et 3, donner la liste des moyennes de points marqués par les joueurs et leur moyenne de fautes pour ceux dont la moyenne est > à 2.5 :

    SELECTA_JOUE.N°Joueur, AVG(A_JOUE.NbPointsMarqués),

    AVG(A_JOUE.NbFautes)

    FROM A_JOUE

    WHEREA_JOUE.N°Match=2 OR A_JOUE.N°Match=3

    GROUP BY A_JOUE.N°Joueur

    HAVINGAVG(A_JOUE.NbFautes) > 2.5 ;


1 8 interrogation avec access

-1.8- Interrogation avec ACCESS

  • Liste des points marqués par les joueurs à domicile contre l'équipe 3.

    SELECT JOUEURS.NomJoueur, MATCHS.N°Match,

    MATCHS.Date, A_JOUE.NbPointsMarqués,

    A_JOUE.NbFautes, MATCHS.N°Visiteur

    FROM MATCHS ,JOUEURS, A_JOUE

    WHERE JOUEURS.N°Joueur = A_JOUE.N°Joueur

    and MATCHS.N°Match = A_JOUE.N°Match and MATCHS.N°Visiteur=3 ;

    devient en SQL Access :

    SELECTDISTINCTROW JOUEURS.NomJoueur, MATCHS.[N°Match],

    MATCHS.Date, A_JOUE.NbPointsMarqués, A_JOUE.NbFautes,

    MATCHS.[N°Visiteur]

    FROM MATCHS INNER JOIN

    (JOUEURS INNER JOIN A_JOUE ON

    JOUEURS.[N°Joueur] = A_JOUE.[N°Joueur]) ON

    MATCHS.[N°Match] = A_JOUE.[N°Match]

    WHERE ((MATCHS.[N°Visiteur]=3));


Requ tes avec l aide graphique access

Requêtes avec l ’aide graphique ACCESS

Remarques :

MATCHS.[N°Match]les crochets entourent des noms comportant des caractères spéciaux ou des espaces.

INNER JOIN ... ONjointure "interne"

DISTINCT/DISTINCTROW

DISTINCTROW

élimination des doublons avant projection.

DISTINCT

élimination des doublons après projection.


Requ tes avec l aide graphique access1

Requêtes avec l ’aide graphique ACCESS

SELECTJOUEURS.[N°Equipe], JOUEURS.NomJoueur,

JOUEURS.PrénomJoueur

FROM JOUEURS INNER JOIN A_JOUE

ON JOUEURS.[N°Joueur] = A_JOUE.[N°Joueur];


Requ tes avec l aide graphique access2

Requêtes avec l ’aide graphique ACCESS

  • Requête avec DISTINCTROW :

    SELECTDISTINCTROW JOUEURS.[N°Equipe], JOUEURS.PrénomJoueur

    FROMJOUEURS INNER JOIN A_JOUE

    ON

    JOUEURS.[N°Joueur] = A_JOUE.[N°Joueur];

  • Requête avec DISTINCT :

    SELECTDISTINCT JOUEURS.[N°Equipe], JOUEURS.PrénomJoueur

    FROM JOUEURS INNER JOIN A_JOUE

    ON JOUEURS.[N°Joueur] = A_JOUE.[N°Joueur];

    Remarques :

    MATCHS.[N°Match]les crochets entourent des noms comportant des caractères spéciaux ou des espaces.

    INNER JOIN ... ONjointure "interne"

    DISTINCT/DISTINCTROW

    DISTINCTROW

    élimination des doublons avant projection.

    DISTINCT

    élimination des doublons après projection.


2 mise jour d une base

-2- Mise à jour d’une base

  • Insertion d'une ligne :

    INSERTINTO nom-relation [ ( Champ1, Champ2, ... )

    VALUES(Valeur1, Valeur2, ... ) ;

    INSERTINTO JOUEURS ( N°Joueur, N°Equipe, NomJoueur,

    PrénomJoueur )

    VALUES (17, 1, "Joyeux", "Michel");

  • Modification des valeurs de champs d'une ligne :

    UPDATE nom-relation

    SET Champ1 = Valeur1, Champ2 = Valeur2, ...

    WHERE condition ;

    UPDATE JOUEURS

    SET PrénomJoueur = "Alain"

    WHERE [N°Joueur] = 17;


2 mise jour d une base1

-2- Mise à jour d’une base

  • Suppression d'une ligne :

    DELETE FROM nom-relation

    WHERE condition ;

    DELETE FROM JOUEURS

    WHERE [N°Joueur] = 17;


3 gestion des tables

-3- Gestion des tables

  • Création de table.

    CREATETABLE nom-table

    (nom-d'attribut description, ... ) ;

    Créer une table de nom Nouvelle Table avec deux champs de type Texte, un champ de type Entier et un champ Date/Heure. Le champ NSS est contraint à être la clé primaire.

    CREATE TABLE NouvelleTable

    (Nom TEXT, PrénomTEXT,

    NuméroSS INTEGER

    CONSTRAINTIndexPrim PRIMARY KEY ,

    DateNaissance DATETIME);


3 gestion des tables1

-3- Gestion des tables

  • Suppression de table.

    DROP nom-table

  • Création d'index.

    CREATE [UNIQUE] INDEX nom-index

    ON nom-relation ( nom-d'attribut, ... );

    Créer un index de nom IndNomJoueur :

    CREATE INDEX IndNomJoueur

    ON JOUEURS (NomJoueur);

  • Suppression d'index.

    DROP INDEX nom-index


4 exemple de cr ation de requ te

-4- Exemple de création de requête

  • Calculer la somme des points marqués par les équipes

    dans tous ses matchs à domicile.

    1) Choisir les champs que l'on veut voir apparaitre dans

    le résultat de la requête.

    NomEquipe, somme des points marqués

    SELECT EQUIPES.NomEquipe, SUM(MATCHS.NbPtsLocaux)

    2) En déduire les tables utiles pour obtenir ces champs.

    EQUIPES, MATCHS

    FROM EQUIPES, MATCHS


4 exemple de cr ation de requ te1

-4- Exemple de création de requête

3) Ajouter éventuellement les tables participant à la

requête mais dont les champs ne sont pas visualisés.

4) Déterminer les jointures entre ces tables.

jointure EQUIPES.N°Equipe avec MATCHS.N°Locaux

WHERE EQUIPES.N°Equipe = MATCHS.N°Locaux

5) Déterminer les regroupements nécessaires

regroupement par NomEquipe

GROUP BY EQUIPES.NomEquipe


4 exemple de cr ation de requ te2

-4- Exemple de création de requête

SELECTEQUIPES.NomEquipe,

SUM (MATCHS.NbPtsLocaux)

FROM EQUIPES, MATCHS

WHEREEQUIPES.[N°Equipe]=MATCHS.[N°Locaux]

GROUP BYEQUIPES.NomEquipe;


Suppl ment imbrication de clauses where

Supplément : imbrication de clauses WHERE

Soit la requête

SELECT EQUIPES.N°Equipe, EQUIPES.NomEquipe, MATCHS.N°Match , MATCHS.N°Locaux, MATCHS.Date, MATCHS.NbSpectateur,

FROM EQUIPES, MATCHS

WHERE EQUIPES.N°Equipe = MATCHS.N°Locaux

AND MATCHS.NbSpectateurs > 10000;


Suppl ment imbrication de clauses where1

Supplément : imbrication de clauses WHERE

Cette requête peut aussi s’écrire par imbrication de clauses WHERE en utilisant les opérateurs ensemblistes suivants :

IN : appartenance à un ensemble

NOT IN :non appartenance

SELECT EQUIPES.[N°Equipe], EQUIPES.NomEquipe

FROM EQUIPES

WHERE EQUIPES.[N°Equipe] IN

( SELECT MATCHS.[N°Locaux]

FROM MATCHS

WHERE (MATCHS.NbSpectateurs > 10000));


Suppl ment any some et all

Supplément : ANY, SOME et ALL

ANY : Un ou plusieurs éléments de l'ensemble satisfont la condition.

ALL :Tous les éléments de l'ensemble satisfont la condition.

Utilisez ANY ou SOME, qui sont synonymes, pour extraire de la requête principale des enregistrements qui répondent à la comparaison avec au moins un enregistrement extrait de la sous-requête. L’exemple suivant retourne tous les produits dont le prix unitaire est supérieur à celui d’au moins un produit vendu avec une remise de 25 pour cent ou plus:

SELECT * FROM Produits

WHERE [Prix unitaire] > ANY

(SELECT [Prix unitaire] FROM [Détails commandes]

WHERE [Remise] >= .25);


Suppl ment any some et all1

Supplément : ANY, SOME et ALL

Utilisez ALL pour n’extraire de la requête principale que les enregistrements qui répondent à la comparaison avec tous les enregistrements extraits de la sous-requête.

Si vous remplaciez ANY par ALL dans l’exemple ci-dessus, la requête retournerait seulement les produits dont le prix unitaire est supérieur à celui de tous les produits vendus avec une remise de 25 pour cent ou plus. Ceci est beaucoup plus restrictif.


Suppl ment distinctrow

Supplément : DISTINCTROW

DISTINCTROW permet d’omettre les données relatives à des enregistrements entièrement dupliqués, et pas seulement des champs dupliqués. Par exemple, vous pourriez créer une requête qui joint les tables Clients et Commandes par l’intermédiaire du champ Code client. La table Clients ne contient pas de doublon du champ Code client, contrairement à la table Commandes, étant donné qu’à chaque client peuvent correspondre de nombreuses commandes. L’instruction SQL suivante vous montre comment utiliser DISTINCTROW pour produire la liste des sociétés qui ont passé au moins une commande, mais sans aucun détail concernant ces commandes:

SELECT DISTINCTROW Société

FROM Clients INNER JOIN Commandes

ON Clients.[Code client] = Commandes.[Code client]

ORDER BY Société;


Suppl ment distinctrow1

Supplément : DISTINCTROW

Sans DISTINCTROW, cette requête produit plusieurs lignes pour chaque société qui a passé plus d’une commande.

DISTINCTROW n’a d’effet que si vous sélectionnez des champs dans une partie seulement des tables utilisées dans la requête.

DISTINCTROW est ignoré si vous choisissez des champs dans toutes les tables ou si votre requête n’inclut qu’une table.


  • Login