Scribd Logo
Importer

Bienvenue sur Scribd !

  • 17 vues

    Chapitre 2

    Transféré par

    mahamadou anza

    Droits d'auteur :

    © All Rights Reserved
    Téléchargez comme PPTX, PDF, TXT ou lisez en ligne sur Scribd

    Chapitre 2

    Transféré par

    mahamadou anza
    17 vues30 pages

    Titre original

    Chapitre2

    Copyright

    © © All Rights Reserved

    Formats disponibles

    PPTX, PDF, TXT ou lisez en ligne sur Scribd

    Avez-vous trouvé ce document utile ?

    Ce contenu est-il inapproprié ?

    Droits d'auteur :

    © All Rights Reserved
    Téléchargez comme PPTX, PDF, TXT ou lisez en ligne sur Scribd
    Télécharger au format pptx, pdf ou txt
    17 vues30 pages

    Chapitre 2

    Transféré par

    mahamadou anza

    Droits d'auteur :

    © All Rights Reserved
    Téléchargez comme PPTX, PDF, TXT ou lisez en ligne sur Scribd
    Télécharger au format pptx, pdf ou txt
    sur 30

    C h a p i t r e 2 : Langage SQL

    Qu’est-ce que S QL ?

    Structured Query Language


    Issu de SEQUEL (Structured English as a Query Language)
    LDD LMD LCD
    Permet la définition, la manipulation et le contrôle d’une
    base de données relationnelle.

    S Q L se base sur l’algèbre relationnelle.

    Standard depuis 1986.

    2
    Types de données principaux

    NUMBER(n) : nombre entier à n chiffres

    NUMBER(n, m) : nombre réel à n chiffres au total


    (virgule comprise) et m chiffres après la virgule

    VARCHAR(n) : chaîne de caractères de taille n

    DATE : date au format ‘JJ-MM-AAAA’


    3
    Contraintes d’intégrité
    Mot clé CONSTRAINT

    Identification par un nom de

    contrainte

    Clé primaire :
    PRIMARY KEY (clé)

    Clé étrangère :
    FOREIGN KEY (clé) REF EREN C ES table(attribut)

    Contrainte de domaine :
    CHECK (condition) 4
    Définition des données (1/2)

    ex.

    CREATE TABLE Etudiant ( NumEtu NUMBER(8),


    Nom VARCHAR(255),
    Prenom VARCHAR(255),
    DateNaiss DATE,
    Rue VARCHAR(255),
    CP NUMBER(5),
    Ville VARCHAR(255),

    CONSTRAINT EtuClePri PRIMARY KEY (NumEtu) )

    5
    Définition des données (2/2)
    ex.
    CREATE TABLE Passer ( NumEtu NUMBER(8),
    CodeEpr VARCHAR(10),
    Note NUMBER(5, 2),

    CONSTRAINT PassClePri PRIMARY KEY (NumEtu, CodeEpr),

    CONSTRAINT PassCleEtrEtu FOREIGN KEY (NumEtu)


    REFERENCES Etudiant (NumEtu),

    CONSTRAINT PassCleEtrEpr FOREIGN KEY (CodeEpr)


    REFERENCES Epreuve (CodeEpr),

    CONSTRAINT NoteValide CHECK (Note >= 0 AND Note <=


    20) 6
    Modifications structurelles (1/2)

    Ajout d’attributs
    ALTER TABLE nom_table A D D (attribut TYPE, … )
    ex. ALTER TABLE Etudiant A D D (tel NUMBER(8))

    Modifications d’attributs
    ALTER TABLE nom_table MODIFY (attribut TYPE, … )
    ex. ALTER TABLE Etudiant MODIFY (tel NUMBER(10))

    Suppression d'attributs
    ALTER TABLE nom_table D R O P COLUMN attribut, ...
    ex. ALTER TABLE Etudiant D R O P COLUMN tel

    7
    Modifications structurelles (2/2)

    Ajout de contrainte
    ALTER TABLE nom_table
    A D D CONSTRAINT nom_contrainte définition_contrainte
    ex. ALTER TABLE Epreuve
    A D D CONSTRAINT LieuValide CHECK (Lieu IN (‘Say’, ‘Aubrac’))

    Suppression de contrainte
    ALTER TABLE nom_table D R O P CONSTRAINT nom_contrainte
    ex. ALTER TABLE Epreuve
    D R O P CONSTRAINT LieuValide

    8
    Index

    Définition : Structure de données physique permettant


    d'accélérer les accès aux données

    Exemple : CREATE I N D EX IdxNomEtu O N Etudiant (Nom)

    N B : La clé primaire d'une relation


    est automatiquement indexée.

    9
    Vues

    Définition : Une vue est une table virtuelle calculée


    à partir d’autres tables grâce à une requête.

    Création d’une vue

    CREATE VIEW nom_vue A S requête

    ex. CREATE VIEW lesNoms A S


    SELECT Nom, Prenom F R O M Etudiant

    10
    Intérêt des vues (1/2)

    Simplification de l’accès aux données


    en masquant les opérations de jointure
    ex. CREATE VIEW notesParEtudiant A S
    SELECT E.NumEtu, Nom, Prenom, NumEpr, Note
    F R O M Etudiant E, Passer P
    W H E R E E.NumEtu = P.NumEtu

    SELECT NumEtu, N o m F R O M notesParEtudiant


    W H E R E Note > 1 0

    11
    Intérêt des vues (2/2)

    Sauvegarde indirecte de requêtes complexes


    Présentation de m ê m e s données s o us
    différentes formes adaptées aux différents
    usagers particuliers
    Support de l’indépendance logique
    ex. Si la table Etudiant est remaniée, la vue
    notesParEtudiant doit être refaite, mais les requêtes
    qui utilisent cette vue n’ont pas à être remaniées.
    Renforcement de la sécurité des données par m a s quag e
    des lignes et des colonnes sensibles aux usagers non
    habilités
    12
    Mise à jour des données
    Ajout d’un n-uplet
    ex. INSERT INTO Matiere
    VALUES (‘BDM1MBFA’, ‘Bases de données’)

    Modification de la valeur d’un attribut


    ex. UPDATE Etudiant SET Nom=‘Dudule’
    WH ERE NumEtu = 3 3 3 3 3 3
    ex. UPDATE Passer SET Note = Note + 1

    Suppression de n-uplets
    ex. DELETE F RO M Etudiant
    WH ERE Ville = ‘Lyon’
    ex. DELETE F RO M Epreuve

    13
    Interrogation des données

    Par l’exemple, sur la base ETUDIANTS


    CARTE_IZLY (NumCarte, SoldeCROUS)
    GROUPE_TD (CodeGroupe)
    ETUDIANT (NumEtu, Nom, Prénom, DateNaiss, Rue, CP, Ville,
    NumCarte#, CodeGroupe#)
    MATIERE (CodeMat, Intitulé)
    EPREUVE (CodeEpr, DateEpr, Lieu, CodeMat# )
    PASSER (NumEtu#, CodeEpr#, Note)

    Note : Les symboles [ ] indiquent une clause optionnelle d’une requête


    dans les transparents suivants.
    14
    Étoile, tri et champ calculé
    Tous les n-uplets d’une table : étoile (*)
    ex. SELECT * F R O M Etudiant

    Tri du résultat
    ex. Par ordre alphabétique [inverse] de nom
    SELECT * F R O M Etudiant
    O R D E R BY N o m [DESC]

    Champs calculés
    ex. Transformation de notes sur 2 0 en notes sur 1 0
    SELECT Note / 2 F R O M Passer

    15
    Projection et restriction
    Projection
    ex. N o m s et Prénoms des étudiant·es, uniquement (pas
    les autres attributs)
    SELECT Nom, Prénom F R O M Etudiant
    Suppression des doublons
    ex. SELECT DISTINCT N o m F R O M Etudiant
    Restriction
    ex. Étudiant·es qui habitent à Lyon
    SELECT * F R O M Etudiant
    W H E R E Ville = ‘Lyon’

    16
    Opérateurs de restriction (1/3)
    ex. Épreuves se déroulant après le 01/01/2016
    SELECT * FROM Epreuve
    WHERE DateEpr >= '01-01-2016'

    ex. Notes comprises entre 10


    et 20
    SELECT * FROM Passer
    WHERE Note BETWEEN 10
    AND 20

    ex. Notes indéterminées (sans


    valeur)
    SELECT * FROM Passer 17
    Opérateurs de restriction (2/3)

    ex. Étudiant·es habitant une ville dont le nom se termine


    par sur-Saône

    SELECT * FROM Etudiant


    WHERE Ville LIKE ‘%sur-Saône’

    ‘sur-Saône%’ ⇒ commence par


    sur-Saône
    ‘%sur%’ ⇒ contient le mot
    sur

    18
    Opérateurs de restriction (3/3)

    ex. Prénoms des étudiant·es dont le nom est Dupont,


    Durand ou Martin
    SELECT Prénom FROM Etudiant
    WHERE Nom IN (‘Dupont’, ‘Durand’, ’Martin’)

    NB : Possibilité d’utiliser la négation pour tous ces prédicats


    ⇒ NOT BETWEEN, NOT NULL, NOT LIKE, NOT IN.

    19
    Opérateurs logiques

    ET. ex. Épreuves se déroulant le 15/01/2016 en salle D 2 0 1


    SELECT * F R O M Epreuve
    W H E R E DateEpr = ‘15-01-2016’ A N D Lieu = ‘D201’
    OU. ex. Étudiant·es né·es avant 1 9 9 0 ou habitant hors Lyon
    SELECT * F R O M Etudiant
    W H E R E DateNaiss < ‘01-01-1990’ O R Ville < > ‘Lyon’
    Combinaisons. ex. Étudiant·es né·es après 1 9 9 0 et habitant
    Lyon ou Vienne
    SELECT * F R O M Etudiant
    W H E R E DateNaiss > ‘31-12-1990’
    A N D (Ville = ‘Lyon’ O R Ville = ‘Vienne’)

    20
    Fonctions d’agrégat
    Elles opèrent sur un ensemble de valeurs et les agrègent.
    AVG(), VARIANCE(), STDDEV() : moyenne, variance et
    écart-type des valeurs
    SUM() : s o m m e des valeurs
    MIN(), MAX() : valeur minimum, valeur maximum
    COUNT() : nombre de valeurs
    ex. Moyenne des notes
    SELECT AVG(Note) F R O M Passer
    21
    Fonction COUNT et opérateur DISTINCT

    ex. Nombre total de notes

    SELECT COUNT(*) FROM Passer


    SELECT COUNT(NumEtu) FROM Passer

    ex. Nombre d'étudiant·es noté·es

    SELECT COUNT(DISTINCT NumEtu) FROM Passer

    22
    Exemple COUNT/DISTINCT

    Table PASSER

    NumEtu CodeEpr Note


    101 INFO1 10
    103 INFO1 15
    103 ECO1 12

    COUNT(NumEtu) ⇒ Résultat = 3

    COUNT(DISTINCT NumEtu) ⇒ Résultat = 2

    23
    Jointure (1/3)

    ex. Liste des notes avec le nom des étudiant·es

    SELECT Nom, CodeEpr, Note


    RESULTAT
    FROM Etudiant, Passer

    WHERE Etudiant.NumEtu = Passer.NumEtu Nom,


    CodeEpr, Note

    NumEtu NumEtu
    =

    ETUDIANT PASSE
    R
    24
    Jointure (2/3)

    ex. Idem avec le numéro d'étudiant en plus


    SELECT E.NumEtu, Nom, CodeEpr, Note
    FROM Etudiant E, Passer P
    WHERE E.NumEtu = P.NumEtu
    ORDER BY Nom, Note DESC

    NB : Utilisation d’alias (E
    et P) pour alléger l’écriture
    + tri par nom (croissant) et
    note (décroissante). 25
    Jointure (3/3)

    Jointure exprimée avec le prédicat IN

    ex. Notes des épreuves passées le 23 septembre 2016


    SELECT Note FROM Passer

    WHERE CodeEpr IN (
    S ou s-
    SELECT CodeEpr FROM Epreuve requête
    WHERE DateEpr = ‘23-09-2016’ )

    NB : Il est possible d’imbriquer des requêtes.

    26
    Groupement (1/2)

    ex. Moyenne de chaque étudiant·e


    SELECT NumEtu, AVG(Note)
    FROM Passer
    GROUP BY NumEtu

    ex. Nombre de notes par


    étudiant·e
    SELECT NumEtu, COUNT(*)
    FROM Passer
    GROUP BY NumEtu
    27
    Groupement (2/2)
    ex. Note moyenne pour les étudiant·es ayant passé moins de 5
    épreuves
    SELECT NumEtu, AVG(Note)
    FROM Passer
    GROUP BY NumEtu
    HAVING COUNT(*) < 5

    Attention : La clause HAVING


    ne s’utilise qu’avec GROUP
    BY.

    NB : HAVING : évaluation de condition sur un résultat


    de groupement (a posteriori)
    ≠ WHERE : évaluation de condition a priori 28
    Solution logique
    SELECT NumEtu
    FROM Etudiant Et
    WHERE NOT EXISTS (
    SELECT *
    FROM Epreuve Ep
    WHERE NOT EXISTS (
    SELECT *
    FROM Passer
    P
    WHERE Et.NumEtu = P.NumEtu
    AND P.CodeEpr = Ep.CodeEpr )
    29
    Opérations ensemblistes

    INTERSECT, MINUS, UNION

    ex. Code des épreuves ayant soit lieu dans l’Amphi


    Aubrac, soit ayant été passées par l'étudiant·e n° 102

    SELECT CodeEpr FROM Epreuve


    WHERE Lieu = 'Amphi Aubrac‘
    UNION
    SELECT CodeEpr FROM Passer
    WHERE NumEtu = 102
    30

    Vous aimerez peut-être aussi