Le langage sql
This presentation is the property of its rightful owner.
Sponsored Links
1 / 66

Le langage SQL PowerPoint PPT Presentation


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

Le langage SQL. Histoire langages pour les BD relationnelless incluant toutes les possibilités de l'algèbre relationnelle : QUEL, SEQUEL, SQL (79, sur Oracle) Normalisation en 87 par ANSI de SQL plus d'une centaine de produits supportant SQL aujourd'hui

Download Presentation

Le langage SQL

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


Le langage sql

Le langage SQL

  • Histoire

    • langages pour les BD relationnelless incluant toutes les possibilités de l'algèbre relationnelle : QUEL, SEQUEL, SQL (79, sur Oracle)

    • Normalisation en 87 par ANSI de SQL

    • plus d'une centaine de produits supportant SQL aujourd'hui

  • SQL n'est pas un langage de programmation

    • 2 douzaines d'instructions environ

    • peut trouver sa place dans un langage de programmation

Maria Berger - Maîtrise d'AES 2003-2004


Caract ristiques

Caractéristiques

  • SQL assure l'indépendance des données

  • SQL est un langage déclaratif (par opposition à procédural)

  • Mode interactif ou programmé

    • interactif : instruction SQL tapée directement, instantanément traitée

    • à l'intérieur d'un pgm : appel de SQL dans un pgm en C, ...

  • instruction SQL = requête

  • résultat d'une requête = table

Maria Berger - Maîtrise d'AES 2003-2004


Caract ristiques1

Caractéristiques

  • SQL est un langage à dimension triple :

    • le DDL (Data Definition Language) : commandes de SQL permettant de créer, modifier ou effacer la définition (schéma) d'une BD ou d'une table.

    • le DML (Data Manipulation Language) : interrogation et modification de l'information contenue dans les tables

    • le DCL (Data Control Language) : sécurité et confidentialité de la BD.

Maria Berger - Maîtrise d'AES 2003-2004


Sql1 86

SQL1 - 86

  • LANGAGE DE DEFINITIONS DE DONNEES

    • CREATE TABLE

    • CREATE VIEW

  • LANGAGE DE MANIPULATION DE DONNEES

    • SELECT OPEN

    • INSERT FETCH

    • UPDATE CLOSE

    • DELETE

  • LANGAGE DE CONTROLE DE DONNEES

    • GRANT et REVOKE

    • BEGIN et END TRANSACTION

    • COMMIT et ROLLBACK

Maria Berger - Maîtrise d'AES 2003-2004


Le language de manipulation des donn es la consultation de donn es

Le Language de Manipulation des Données : la consultation de données

  • Instruction SELECT : 3 parties principales :

    • clause (obligatoire) Select : précise les valeurs qui constituent chaque ligne du résultat

    • clause (obligatoire) from : indique les tables desquelles le résultat tire ses valeurs

    • clause where : donne la condition de sélection que doivent satisfaire les lignes qui fournissent le résultat

      SELECT schéma

      FROM table

      WHERE condition ;

Maria Berger - Maîtrise d'AES 2003-2004


Interrogation en sql

Interrogation en SQL

SELECT <liste de colonnes>

FROM <liste de tables>

[WHERE <critère de jointure> AND <critère de sélection>]

[GROUP BY <attributs de partitionnement>]

[HAVING <citère de restriction>]

  • Exemple :

    SELECT NomCoureur

    FROM Coureurs

    WHERE CodePays = ‘SUI’;

Maria Berger - Maîtrise d'AES 2003-2004


Interrogation d une seule table

Interrogation d’une seule table

CRU(Nom_Cru,Commune,Région,Coul)

VINS(Région,Coul,Millésime,Qualité)

CEPAGE_REGION(Cepage,R_PROD,Coul)

OU

EMP(Num,Nom,Fonction,N_Sup,Embauche, Salaire,Comm,N_Dep)

DEPT(N_Dept,Nom,Lieu)

Maria Berger - Maîtrise d'AES 2003-2004


Projection

PROJECTION

  • Extraction simple : afficher une table entièrement

    SELECT *

    FROM Nom_table;

  • Exemple :

    • Tous les crus ?

    • Expression algébrique : cru

    • SQL : SELECT *

      FROM CRU;

Maria Berger - Maîtrise d'AES 2003-2004


Projection1

PROJECTION

SELECT Nom_Col1, ..., Nom_ColN

FROM Nom_table;

  • Exemple :

    Requête : Liste des noms de crus

    Algèbre : Nom_Cru (CRU)

    SQL : SELECT Nom_Cru

    FROM CRU

    La clause DISTINCT permet d'éliminer les doublons.

Maria Berger - Maîtrise d'AES 2003-2004


Selection

SELECTION

SELECT Nom_Colonne

FROM Nom_Table

WHERE critère ;

  • Exemple :

    • Requête : La liste des noms de crus rouges

    • Algèbre : Nom_Cru ( Coul = rouge(CRU))

    • SQL : SELECT Nom_Cru

      FROM CRU

      WHERE Coul=‘Rouge’

Maria Berger - Maîtrise d'AES 2003-2004


Op rateurs de comparaison

Opérateurs de comparaison

  • =,>,<,>=,<=,<> (ou != ou ^=)

  • In et not in

    • Requête : quels sont les bons millésimes de Bordeaux ?

    • SQL : SELECT Millésime

      FROM VINS

      WHERE Région = ‘Bordeaux’ AND Qualité =‘Bonne’ OR Qualité =‘Très Bonne’ OR Qualité =‘Excellente’

      OU

      SELECT Millésime

      FROM VINS

      WHERE Région = ‘Bordeaux’ AND Qualité IN (‘Bonne’, ‘Très bonne’, Excellente’ )

Maria Berger - Maîtrise d'AES 2003-2004


Op rateurs de comparaison1

Opérateurs de comparaison

  • LIKE : appartenance à une chaîne de caractères

    • ‘_’ remplace n'importe quel caractère

    • ‘%’ remplace n'importe quelle chaîne de caractères

  • Exemple :

    • Requête : Quels sont les noms de cru qui commencent par Ch ?

    • SQL : SELECT Nom_Cru

      FROM CRU

      WHERE Nom_Cru like ‘Ch%’

    • Requête : Quels sont les noms de cru possédant un ‘a’ en seconde position ?

    • SQL : SELECT Nom_Cru

      FROM CRU

      WHERE Nom_Cru like ‘_a%’

Maria Berger - Maîtrise d'AES 2003-2004


Op rateurs de comparaison2

Opérateurs de comparaison

  • BETWEEN : appartenance à un intervalle

  • Exemple :

    • Requête : Quels sont les salariés gagnant entre 20000 et 25000 ?

    • SQL : SELECT Nom, Salaire

      FROM EMP

      WHERE Salaire BETWEEN 20000 AND 25000

Maria Berger - Maîtrise d'AES 2003-2004


Nom de colonne

Nom de colonne

  • Les colonnes constituant le résultat d’un SELECT peuvent être renommées dans le SELECT

    • Requête : salaire de chaque employé

    • SQL : SELECT Nom,Salaire « SALAIRE MENSUEL »

      FROM EMP;

Maria Berger - Maîtrise d'AES 2003-2004


Op rateurs de comparaison3

Opérateurs de comparaison

  • IS NULL et IS NOT NULL

  • Exemple :

    • Requête : le nom des crus dont la commune n’a pas été renseignée

    • SQL : SELECT Nom_Cru

      FROM CRU

      WHERE Commune IS NULL

Maria Berger - Maîtrise d'AES 2003-2004


Tri des r sultats

Tri des résultats

  • ORDER BY

    SELECT attribut1, attribut2, ...

    FROM Nom_table

    ORDER BY attribut1 [ASC], attribut2 [DESC], ... ;

  • Exemple :

    • Requête : Les bordeaux classés par millésimes et par couleur

    • SQL : SELECT *

      FROM VINS

      WHERE Région = ‘Bordeaux’

      ORDER BY Millésime, Coul

Maria Berger - Maîtrise d'AES 2003-2004


Fonctions

Fonctions

  • La relation résultat

    • ne comportera qu'une ligne

    • ou pourra simplement être considérée comme un nombre

  • Fonctions numériques :

    • AVG : moyenne

    • Exemple : Age moyen des professeurs

    • SQL : SELECT ‘2001-10-10’-AVG(Date_Naissance)

      FROM PROFESSEUR;

    • SUM : somme

    • Exemple : CA généré par les vendeurs de PACA:

    • SQL : SELECT SUM(CA)

      FROM Vendeur

      WHERE Region = 'PACA'’;

Maria Berger - Maîtrise d'AES 2003-2004


Fonctions1

Fonctions

  • COUNT : nombre d'éléments sélectionnés

    • Requête : Nombre de cépages bordelais ?

    • SQL :SELECT COUNT ( *)

      FROM CEPAGE_REGION

      WHERE R_Prod = ‘Bordeaux’;

  • MIN, MAX, ...

  • Expressions et Fonctions sur les chaînes de caractères

    • || : seul opérateur sur les chaînes de caractères : la concaténation.

      • Résultat : chaîne de caractères obtenue en écrivant d'abord la chaîne à gauche de || puis celle à droite de ||.

    • LOWER(chaîne) : Renvoie chaîne en ayant mis toutes ses lettres en minuscules.

      • ...

Maria Berger - Maîtrise d'AES 2003-2004


Fonctions2

Fonctions

  • Expressions et fonctions sur les dates

    • Opérateurs sur les dates : + et -

      • date +/- nombre : le resultat est une date obtenue en ajoutant le nombre de jours nombre à la date date.

      • date2 - date1 : le resultat est le nombre de jours entre les deux dates.

Maria Berger - Maîtrise d'AES 2003-2004


Regroupements

Regroupements

  • Il est possible de subdiviser la table en groupes

  • Permet d’appliquer les fonctions d’aggrégation à des sous-groupes

    • Requête : Combien de crus rouges et de crus blancs sont produits dans chaque commune ?

    • SQL : SELECT Commune, Count(*)

      FROM CRU

      GROUP BY Commune, Coul;

Maria Berger - Maîtrise d'AES 2003-2004


R sultat

Résultat

Maria Berger - Maîtrise d'AES 2003-2004


Selection des groupes

Selection des groupes

  • HAVING : conditions imposées aux groupes (de lignes) à sélectionner

    • pour éviter la confusion avec la clause WHERE (qui ne s'applique qu'à des lignes seules)

  • Exemple :

    • Requête : Donner les communes où au moins 2 crus rouges ou crus blancs sont produits?

    • SQL : SELECT Commune, Count(*)

      FROM CRU

      GROUP BY Commune, Coul

      HAVING Count(*) >2;

Maria Berger - Maîtrise d'AES 2003-2004


Quantificateurs

Quantificateurs

  • ALL + opérateur de comparaison : teste si une expression est vérifiée dans tous les cas de figure

    • Requête : Tous les salariés ont-ils été recrutés avant le 1er janvier 2001 ?

    • SQL : ‘2001-01-01’ > ALL (SELECT Embauche FROM EMP)

  • SOME ou ANY : expression vraie si la comparaison est vérifiée pour au moins une valeur

    • Requête : vérifiez si au moins un salarié a été recruté depuis un an

    • SQL :‘2000-10-01’ > ANY (SELECT Embauche FROM EMP)

Maria Berger - Maîtrise d'AES 2003-2004


Quantificateurs1

Quantificateurs

  • EXISTS, NOT EXISTS

Maria Berger - Maîtrise d'AES 2003-2004


Comment interpr ter une requ te complexe monotable

Comment interpréter une requête complexe monotable ?

  • on considère la table spécifiée dans la clause FROM

  • on sélectionne les lignes sur la base de la clause WHERE

  • on classe ces lignes en groupes comme spécifié dans la clause GROUP BY

  • on ne retient que les groupes qui vérifient la clause HAVING

  • de chacun de ces groupes, on extrait les valeurs demandées dans la clause SELECT

  • les valeurs demandées sont ordonnées selon la clause ORDER BY éventuelle.

Maria Berger - Maîtrise d'AES 2003-2004


Interrogations sur plusieurs tables

Interrogations sur plusieurs tables :

  • SQL permet la liaison de plusieurs tables via 3 possibilités :

    • Les opérations de jointure entre 2 tables en se basant sur l'égalité entre l'un des attributs de chaque table

    • Le principe des requêtes imbriquées qui repose sur le fait que le résultat d'une requête est une table

    • L'utilisation d'opérations ensemblistes pour combiner le résultat de plusieurs requêtes.

Maria Berger - Maîtrise d'AES 2003-2004


Jointures

Jointures

SELECT ...

FROM nom_table1, nom_table2...

WHERE critère;

  • pas de condition de sélection : résultat obtenu = produit cartésien des tables présentes derrière le FROM.

  • Requête : à partir de quel cépage principal est produit le Meursault ?

  • SQL :SELECT Cépage

    FROM CEPAGE_REGION, CRU

    WHERE région = R_Prod AND Commune = Meursault’

Si l’attribut Région avait le même nom dans CRU que dans CEPAGE_REGION on aurait écrit : CRU.Région = CEPAGE_REGION.Région

Maria Berger - Maîtrise d'AES 2003-2004


Auto jointure

Auto-jointure :

  • Requête : Donner pour chaque employé le nom de son supérieur hiérarchique.

  • SQL : SELECT EMP.Nom, chef.Mom

    FROM EMP, EMP chef

    WHERE EMP.N_sup= chef.Num;

Maria Berger - Maîtrise d'AES 2003-2004


Autres jointures

Autres jointures

  • Le critère d'égalité est le critère de jointure le plus naturel. Mais on peut utiliser d'autres types de comparaisons comme critères de jointures.

    • Requête : Quels sont les employés gagnant plus que SIMON ?

    • SQL :SELECT EMP.nom, EMP.salaire, EMP.fonction

      FROM EMP, EMP empbis

      WHERE EMP.salaire > empbis.salaire AND empbis.nom = 'SIMON';

Maria Berger - Maîtrise d'AES 2003-2004


Requ tes imbriqu es

Requêtes imbriquées

  • une clause WHERE est elle-même le résultat d'un SELECT

  • renvoient une ou plusieurs valeurs

Maria Berger - Maîtrise d'AES 2003-2004


Requ tes imbriqu es1

Requêtes imbriquées

  • Sous-interrogation ramenant une seule valeur

    • Requête : Quels sont les vins qui sont produits dans la même commune que La Grappe d’Or ?

    • SQL : SELECT Nom_Cru

      FROM VINS

      WHERE Commune = (SELECT Commune

      FROM VINS

      WHERE Nom_Cru = ‘La Grappe d’Or’)

    • Remarque : auto-jointure possible pour répondre à cette question

Maria Berger - Maîtrise d'AES 2003-2004


Requ tes imbriqu es2

Requêtes imbriquées

  • une sous-interrogation qui ne ramène aucune ligne se termine avec un code d'erreur.

  • une sous-interrogation ramenant plusieurs lignes provoquera aussi, dans ce cas, une erreur

Maria Berger - Maîtrise d'AES 2003-2004


Requ tes imbriqu es3

Requêtes imbriquées

  • Sous-interrogation ramenant plusieurs lignes

  • Avec des opérateurs de comparaison admettant à leur droite un ensemble de valeurs comme :

    • l'opérateur IN

    • les opérateurs obtenus en ajoutant ANY ou ALL à la suite d'un opérateur de comparaison classique (=, <>, >, >=, <, <=)

Maria Berger - Maîtrise d'AES 2003-2004


Requ tes imbriqu es4

Requêtes imbriquées

  • Requête : Quels sont les employés gagnant plus que tous les employés du département 30 ?

  • SQL : SELECT Nom, Salaire

    FROM Emp

    WHERE salaire > ALL(SELECT Salaire

    FROM Emp

    WHERE N_Dep=30);

Maria Berger - Maîtrise d'AES 2003-2004


Requ tes imbriqu es5

Requêtes imbriquées

  • Sous-interrogation ramenant plusieurs colonnes

    • Exemple : Quels sont les employés ayant la même fonction et le même supérieur que CODD ?

    • SQL : SELECT Nom, Fonction, N_Sup

      FROM Emp

      WHERE (Fonction,N_Sup) = (SELECT Fonction,N_Sup FROM Emp

      WHERE Nom = 'CODD');

Maria Berger - Maîtrise d'AES 2003-2004


Requ tes imbriqu es6

Requêtes imbriquées

  • Sous-interrogation ramenant au moins une ligne

  • L'opérateur EXISTS permet de construire un prédicat vrai si la sous-interrogation qui suit ramène au moins une ligne.

    • Requête : Quels sont les employés travaillant dans un département qui a procédé à des embauches depuis le début de l'année 2001 ?

    • SQL : SELECT *

      FROM Emp Empbis

      WHERE EXISTS (SELECT *

      FROM EMP

      WHERE Embauche >= '01-jan-01'

      AND N_Dept = Empbis.N_Dept);

Maria Berger - Maîtrise d'AES 2003-2004


Les op rateurs ensemblistes

Les opérateurs ensemblistes

  • permettent de "joindre" des tables verticalement c'est-à-dire de combiner dans un résultat unique des lignes provenant de deux interrogations. Les opérateurs ensemblistes sont les suivants :

    • l'union : UNION

    • l'intersection : INTERSECT

    • la différence relationnelle : MINUS

  • La syntaxe d'utilisation est la même pour ces trois opérateurs :

  • SELECT ... {UNION | INTERSECT | MINUS } SELECT ...

Maria Berger - Maîtrise d'AES 2003-2004


Les op rateurs ensemblistes1

Les opérateurs ensemblistes

  • Dans une requête utilisant des opérateurs ensemblistes :

    • Tous les SELECT doivent avoir le même nombre de colonnes sélectionnées, et leur types doivent être un à un identiques.

    • Les doubles sont éliminés (DISTINCT implicite).

    • Les noms de colonnes sont ceux du premier SELECT.

  • On peut combiner le résultat de plus de deux SELECT au moyen des opérateurs UNION, INTERSECT, MINUS.

    SELECT ... UNION SELECT ... MINUS SELECT ...

    • Expresion évaluée de gauche à droite. Modification de l'ordre d'évaluation par des parenthèses.

      SELECT ...

      UNION (SELECT ...

      MINUS

      SELECT ...)

Maria Berger - Maîtrise d'AES 2003-2004


Les op rateurs ensemblistes2

Les opérateurs ensemblistes

  • Exemple : Lister tous les enseignants

    SELECT Nom, Prénom

    FROM MdC

    UNION

    SELECT Nom, Prénom

    FROM Professeur ;

Maria Berger - Maîtrise d'AES 2003-2004


Coment interpr ter une requ te complexe multitable

Coment interpréter une requête complexe multitable ?

  • on considère les tables spécifiées dans la clause FROM

  • on effectue la jointure de ces tables selon le critère de jointure de la clause WHERE

  • on sélectionne les lignes de la jointure sur la base des autres conditions de la clause WHERE

  • on classe ces lignes en groupes comme spécifié dans la clause GROUP BY

  • on ne retient que les groupes qui vérifient la clause HAVING

  • de chacun de ces groupes, on extrait les valeurs demandées dans la clause SELECT

  • les valeurs demandées sont ordonnées selon la clause ORDER BY éventuelle.

Maria Berger - Maîtrise d'AES 2003-2004


Coment interpr ter une requ te complexe multitable1

Coment interpréter une requête complexe multitable ?

  • Exemple :

    SELECT N°Client, COUNT(*), SUM(QtéCom)

    FROM Commande C, LigneCom L

    WHERE C.N°Com = L.N°Com

    AND N°Pro = ‘PA 60’

    GROUP BY N°Client

    HAVING COUNT(*) >= 2

    ORDER BY N°Client

Maria Berger - Maîtrise d'AES 2003-2004


Le language de manipulation des donn es la modification de donn es

Le Language de Manipulation des Données : la modification de données

  • Insertion de nouveaux n-uplets

    INSERT INTO nom_table(nom_col1, nom_col2,...)

    VALUES (val1, val2...)

  • Exemple :

    INSERT INTO Etudiant (n°Et, Nom, Prénom)

    VALUES(96035, ‘Diego’, ‘Berger’)

Maria Berger - Maîtrise d'AES 2003-2004


La modification de donn es

La modification de données

  • Il est possible d'insérer dans une table des lignes provenant d'une autre table. La syntaxe est la suivante :

    INSERT INTO nom_table(nom_col1, nom_col2, ...)

    SELECT ...

  • Exemple : Insérer dans la table Bonus les noms et salaires des directeurs.

    INSERT INTO bonus

    SELECT nom, salaire

    FROM emp

    WHERE fonction = 'directeur';

Maria Berger - Maîtrise d'AES 2003-2004


La modification de donn es1

La modification de données

  • Modification de lignes

    • La commande UPDATE permet de modifier les valeurs d'une ou plusieurs colonnes, dans une ou plusieurs lignes existantes d'une table. La syntaxe est la suivante :

      UPDATE nom_table

      SET nom_col1 = {expression1 | ( SELECT ...) },

      nom_col2 = {expression2 | ( SELECT ...) }

      [WHERE critère];

  • Exemple : Augmenter de 10% les salaires [des ingénieurs].

    UPDATE emp

    SET salaire = salaire * 1.1

    [WHERE fonction = 'ingenieur' ];

Maria Berger - Maîtrise d'AES 2003-2004


La modification de donn es2

La modification de données

  • Suppression de lignes

    • La commande DELETE permet de supprimer des lignes d'une table. La syntaxe est la suivante :

      DELETE FROM nom_table

      WHERE critère;

    • Toutes les lignes pour lesquelles le critère est évalué à vrai sont supprimées. En l'absence de clause WHERE, toutes les lignes de la table sont supprimées.

    • Exemple :

      DELETE FROM emp

      WHERE fonction = ‘retraité‘;

Maria Berger - Maîtrise d'AES 2003-2004


Le language de d finition de donn es

Le language de définition de données

  • Création d'une table

    CREATE TABLE nom_table

    (nom_col1 TYPE1,[NOTNULL/

    PRIMARY KEY/FOREIGN KEY]

    nom_col2 TYPE2,[.../.../...]

    ...);

    • Types acceptés :

    • CHAR(longueur), VARCHAR(longueur)

    • SMALLINT, INTEGER, DECIMAL(m,n), FLOAT, SERIAL(n)

    • DATE

Maria Berger - Maîtrise d'AES 2003-2004


Cr ation d une table

Création d'une table

CREATE TABLE Departement

(NDep SERIAL(20),NOTNULL,PRIMARY KEY,

NomDep CHAR(20),

Directeur CHAR(20),

Budget DECIMAL(6,0));

CREATE TABLE Etudiant

(NEtud SERIAL(20),NOTNULL,PRIMARY KEY,

Nom CHAR(20),

Prénom CHAR(20),

DateNaissance DATE,

Rue CHAR(80),

CodePostal DECIMAL(5,0),

Ville CHAR(20),DEFAULT ‘‘PARIS’’,

NDep SERIAL(20),NOTNULL,FOREIGN KEY);

Maria Berger - Maîtrise d'AES 2003-2004


Suppresion et modification d une table

Suppresion et modification d'une table

  • DROP TABLE nom_table ;

    • Exemple : DROP TABLE Etudiant ;

  • Modification d'une table

    • Ajoût d'une ou plusieurs colonnes :

      ALTER TABLE nom_table

      ADD(nom_col1 TYPE1, nom_col2 TYPE2, ...);

    • option : [BEFORE nom_col_before]

    • Exemple : On aimerait connaître le téléphone des étudiants

      ALTER TABLE Etudiant

      ADD(Téléphone DECIMAL(10,0)

      BEFORE NDep);

Maria Berger - Maîtrise d'AES 2003-2004


Modification d une table

Modification d'une table

  • Suppression d'une colonne :

    ALTER TABLE nom_table

    DROP nom_col;

    • Attention aux problèmes d'intégrité !

  • Modification d’une table :

    ALTER TABLE nom_table

    MODIFY(nom_col1 TYPE1,nom_col2 TYPE2,...);

    • Exemple : Un nom peut dépasser 20 caractères

      ALTER TABLE Etudiant

      MODIFY(Nom Char(25));

Maria Berger - Maîtrise d'AES 2003-2004


Modification d une table1

Modification d'une table

  • Changement de nom de tables ou de colonnes :

    RENAME TABLE ancien_nom TO nouveau_nom ;

    RENAME COLUMN nom_relation.ancien_nom_col TO nouveau_nom_col ;

    • Exemple :

      RENAME COLUMN Etudiant.DateNaissance

      TO BirthDay;

Maria Berger - Maîtrise d'AES 2003-2004


Les vues

Les vues

  • Les vues permettent d'assurer l'objectif d'indépendance logique. Grace à elles, chaque utilisateur pourra avoir sa vision propre des données.

  • Créer une vue

    • La commande CREATE VIEW permet de créer une vue en spécifiant le SELECT constituant la définition de la vue :

      CREATE VIEW nom_vue [(nom_col1,...)]

      AS SELECT ...

      WITH CHECK OPTION ;

Maria Berger - Maîtrise d'AES 2003-2004


Les vues1

Les vues

  • Exemple : Création d'une vue constituant une restriction de la table emp aux employés du departement 10.

    CREATE VIEW emp10 AS

    SELECT *

    FROM emp

    WHERE n_dept = 10 ;

  • Le CHECK OPTION permet de vérifier que la mise à jour ou l'insertion faite à travers la vue ne produisent que des lignes qui font partie de la sélection de la vue.

  • Supprimer une vue

    DROP VIEW nom_vue;

Maria Berger - Maîtrise d'AES 2003-2004


Les index

Les index

  • Considérons le SELECT suivant :

    SELECT *

    FROM Etudiant

    WHERE nom = 'MARTIN'

    • moyen de recherche des lignes avec nom = 'MARTIN' : balayer toute la table

    • prohibitif dès que nb lignes > qq centaines

  • Solution offerte par tous les SGBD : création d'index

  • Les index sont des structures permettant de retrouver une ligne dans une table à partir de la valeur d'une colonne ou d'un ensemble de colonnes.

Maria Berger - Maîtrise d'AES 2003-2004


Choix des index

Choix des index

  • Indexer en priorité :

    • les clés primaires

    • les colonnes servant de critère de jointure

    • les colonnes servant souvent de critère de recherche

  • Ne pas indexer :

    • les colonnes contenant peu de valeurs distinctes (index alors peu efficace)

    • les colonnes fréquemment modifiées

Maria Berger - Maîtrise d'AES 2003-2004


Cr er un index

Créer un index

  • Un index peut être créé par la commande suivante :

    CREATE [UNIQUE] INDEX nom_index

    ON nom_table (nom_col1 , nom_col2, ...)

  • Option UNIQUE : interdiction que deux lignes aient la même valeur dans la colonne indexée.

  • Les requêtes SQL sont transparentes au fait qu'il existe un index ou non.

  • Effacement d'un index :

    DROP INDEX Nom_de_l’index

Maria Berger - Maîtrise d'AES 2003-2004


Les contraintes d int grit

Les contraintes d'intégrité

  • Sur les colonnes :

    • NOT NULL : force la saisie de la colonne

    • DEFAULT : précise une valeur par défaut

    • UNIQUE : vérifie que toutes les valeurs sont différentes

    • CHECK : vérifie la condition précisée

    • CONSTRAINT : permet de nommer une contrainte

  • Sur la table globalement :

    • CONSTRAINT

    • PRIMARY KEY : clé primaire

    • FOREIGN KEY (liste_col1) REFERENCES table(liste_col2): clé étrangère => intégrité référentielle

Maria Berger - Maîtrise d'AES 2003-2004


Les contraintes d int grit1

Les contraintes d'intégrité

  • Clé étrangère :

    • Attribut ou groupe d’attribut dans une table T1 dont les valeurs doivent exister comme valets de la clé candidate dans la table T2.

    • T1 : table qui référence : table secondaire du lien (contient la clé étrangère)

    • T2 : table référencée : table primaire du lien

  • Du point de vue de la table qui référence (T1) :

    • FOREIGN KEY (T1) REFERENCES table(T2): clé étrangère => intégrité référentielle

  • Du point de vue de la table référencée (T2) :

    • Modification de la clé primaire => effets sur les autres tables utilisant cette clé comme clé étrangère ?

    • REFERENCES complétée par ON UPDATE ou ON DELETE

Maria Berger - Maîtrise d'AES 2003-2004


Les contraintes d int grit2

Les contraintes d'intégrité

  • RESTRICT : échec de la modification ou suppression de la clé primaire

  • CASCADE : modification ou suppression des lignes correspondantes en cascade

  • SET NULL : mettre NULL

  • SET DEFAULT : mettre la valeur par défaut

Maria Berger - Maîtrise d'AES 2003-2004


Gestion des transactions

Gestion des transactions

  • Plusieurs utilisateurs peuvent accéder à la base de données de façon concurrente, "en même temps".

    • Exemple : un même système de réservations de billets d'avions est utilisé en concurrence par des centaines d'employés d'agences de voyage.

  • Ce concept pose des problèmes quant à la cohérence et l'intégrité de la base de données.

  • Le SGBD devra donc gérer les utilisations concurrentes sur les données avec le plus d'efficacité possible.

Maria Berger - Maîtrise d'AES 2003-2004


Gestion des transactions1

Gestion des transactions

  • Une transaction est un ensemble de requêtes élémentaires sur les données d'une base.

    • Exemple : vente de produit à un client = 2 parties

      • mise à jour de l'inventaire en soustrayant la quantité vendue du produit, et

      • mise à jour de la table des encaissements à réaliser pour facturer le client.

  • Fin de transaction :

    • COMMIT : Validation.

    • ROLLBACK : suite à un echec, le SGBD doit revenir à l'état précédant le début de l’action. Ceci est fait automatiquement par la commande ROLLBACK.

Maria Berger - Maîtrise d'AES 2003-2004


Administration d une base de donn es

Administration d'une Base de Données

  • GESTION DES DROITS

    • protéger les données de la base contre les accès non autorisés

      • enregistrer et authentifier les utilisateurs

      • définir des autorisations de manipulation des objets de la base par les utilisateurs (relation, vue, contrainte d'intégrité, index)

  • LES DIFFERENTS UTILISATEURS

    • ADMINISTRATEUR BD

      • possède tous les droits

    • UTILISATEURS PRIVILEGIES

      • peuvent créer des relations

    • UTILISATEURS FINAUX

      • peuvent manipuler des relations

Maria Berger - Maîtrise d'AES 2003-2004


Les diff rents droits sur une relation

Les différents droits sur une relation

  • DROIT D'INTERROGATION

  • DROIT DE MISE A JOUR (insertion, modification, suppression de tuples)

  • DROIT D'ADMINISTRATION (déclaration de clés, définition d'index, modif. / suppression du schéma, transmission des droits)

  • DROIT DE CREATION (création schéma relation)

Maria Berger - Maîtrise d'AES 2003-2004


Droits sur une relation

Droits sur une relation

  • EXPRESSION DES DROITS EN SQL

    • DROITS GERES PAR SQL

      • -> droits de manipulation des relations de base et des vues

  • ATTRIBUTION DE DROITS

    • grant <droits> on <relation> to <usagers>

  • grant SELECT on VINS to Grobuveur

  • REVOCATION DES DROITS (SQL2)

    • revoke <droits > on < relation > to <usagers>

  • revoke SELECT on VINS to grobuveur

  • Maria Berger - Maîtrise d'AES 2003-2004


    Le mode client serveur

    Machine A

    Machine B

    requête

    Logiciel

    Client

    Logiciel

    Serveur

    Utilisateur

    réponse

    Le mode Client-Serveur

    • Serveur : serveur de ressources communes

    • Client : logiciel qui accède au serveur

    Besoin de permettre la connexion de n’importe quel type de client avec n’importe quel type de serveur

    • Naissance de protocoles comme ODBC (Open DataBase Connectivity) ou JDBC (Java DataBase Connectivity)

    Maria Berger - Maîtrise d'AES 2003-2004


    Le mode client serveur1

    Le mode Client-Serveur

    • permettent de traduire les requêtes du logiciel client en ordres exploitables par le serveur puis à traduire le format des données renvoyées par le serveur en données manipulables par le client

    • Problème : le choix du bon constructeur. Que mettre dans le client et que mettre sans le serveur ?

    Maria Berger - Maîtrise d'AES 2003-2004


    Le langage sql

    • Le mode Client-Serveur

    ODBC : accès aux bases de données dans le monde de Microsoft

    Maria Berger - Maîtrise d'AES 2003-2004


  • Login