1 / 35

2. UTILISER LES BASES DE DONNEES LE LANGAGE SQL-DML

2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives. 2. UTILISER LES BASES DE DONNEES LE LANGAGE SQL-DML. 2.5 Extraction simple 2.9 Les sous-requêtes

keefe-huber
Download Presentation

2. UTILISER LES BASES DE DONNEES LE LANGAGE SQL-DML

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives 2. UTILISER LES BASES DE DONNEES LE LANGAGE SQL-DML

  2. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives Contenu 2.5 Extraction simple 2.6 Conditions plus complexes 2.7 Données extraites et données dérivées 2.8 Les fonctions agrégatives 2.9 Les sous-requêtes 2.10 Les jointures 2.11 Modification des données LE LANGAGE SQL-DML

  3. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives LE LANGAGE SQL-DML • SQL (Structured Query Language) est le langage standard d’interaction avec une base de données • Il comprend plusieurs sous-langages : • SQL-DDL (Data Definition Language) : permet de créer des structures de données et de les modifier. • SQL-DML (Data Manipulation Language) : permet de manipuler les données (extraire, insérer, modifier, supprimer) • SQL-DCL (Data Control Language) : permet de définir les modalités d’accès aux données, notamment le contrôle des accès

  4. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives • Le sous-langage DML de SQL permet de consulter le contenu des tables et de le modifier. Il comporte 4 verbes. • La requête select extrait des données des tables • La requête insert insère de nouvelles lignes dans une table • La requête delete supprime des lignes d'une table • La requête update modifie les valeurs de colonnes de lignes existantes

  5. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives Objection : je ne veux pas devenir programmeur de base de données ! Réponse : pas de danger (il faudrait 150h de BD) utile pour comprendre ce qu'on peut faire d'une BD indispensable pour aborder la question de la sécurité

  6. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives 2.5 Extraction simple

  7. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives NCLI NOM LOCALITE B062 B112 B332 B512 C003 C123 C400 D063 F010 F011 F400 K111 K729 L422 S127 S712 GOFFIN HANSENNE MONTI GILLET AVRON MERCIER FERARD MERCIER TOUSSAINT PONCELET JACOB VANBIST NEUMAN FRANCK VANDERKA GUILLAUME Namur Poitiers Genève Toulouse Toulouse Namur Poitiers Toulouse Poitiers Toulouse Bruxelles Lille Toulouse Namur Namur Paris 2.5 Extraction simple select NCLI, NOM, LOCALITE from CLIENT; select * from CLIENT; * = liste des colonnes

  8. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives NCLI NOM B512 C003 D063 F011 K729 GILLET AVRON MERCIER PONCELET NEUMAN 2.5 Extraction simple select NCLI, NOM from CLIENT where LOCALITE = 'Toulouse';

  9. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives 2.6 Conditions plus complexes

  10. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives select NCLI from CLIENT where CAT = null; NCLI select NCLI from CLIENT where CAT is null; NCLI D063 K729 2.6 Conditions plus complexes - les valeurs null null ne peut être comparé à rien, même pas à lui-même ! select NCLI from CLIENT where CAT is not null;

  11. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives 2.6 Conditions plus complexes - in et between select NCLI from CLIENT where CAT in ('C1','C2','C3'); select NCLI from CLIENT where LOCALITE not in ('Toulouse','Breda'); select NCLI from CLIENT where COMPTE between 1000 and 4000;

  12. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives 2.6 Conditions plus complexes - Les masques select NCLI from CLIENT where CAT like 'B_'; '_' = un caractère quelconque masques select NPRO from PRODUIT where LIBELLE like '%SAPIN%'; '%' = une chaîne quelconque Un masque définit une famille de chaînes de caractères : 'B_' 'B1' 'Bd' 'B ' '%SAPIN%' 'PL. SAPIN 200x20x2' 'Boite en SAPIN' 'SAPIN VERNI' 'B_' 'xB' 'B' 'B12' '%SAPIN%' 'Boite en Sapin' 'Achetez S A P I N !'

  13. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives 2.6 Conditions plus complexes - Combinaisons logiques select NOM, ADRESSE, COMPTE from CLIENT where LOCALITE = 'Toulouse' and COMPTE < 0; select NOM, ADRESSE, COMPTE from CLIENT where COMPTE > 0 and (CAT = 'C1' or LOCALITE = 'Paris')

  14. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives 2.7 Données extraites et données dérivées

  15. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives TVA de NPRO = 0,21*PRIX*QSTOCK TVA de TVA de TVA de TVA de CS264 PA45 PH222 PS222 = = = = 67788 12789 37770.6 47397 Produit Valeur_TVA CS264 PA45 PH222 PS222 67788 12789 37770.6 47397 2.7 Données extraires et données dérivées - expressions de calcul select 'TVA de ', NPRO, ' = ',0.21*PRIX*QSTOCK from PRODUIT where QSTOCK > 500; select NPRO as Produit, 0.21*PRIX*QSTOCK as Valeur_TVA from PRODUIT where QSTOCK > 500; "Produit" est un alias de colonne

  16. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives 2.7 Données extraires et données dérivées - Fonctions SQL Les fonctions SQL Outre les 4 opérations arithmétiques, SQL offre une large gamme de fonctions Chaînes de caractères ADRESSE || '  ’ || LOCALITE trim(both ' ' from ADRESSE)||' '||upper(LOCALITE) position('NEUVE' in upper(ADRESSE)) > 0 upper(ADRESSE) like '%'||upper(LOCALITE)||'%' Fonctions temporelles extract(year from DATECOM) + 1 extract(hour from current_time) > 18

  17. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives 2.8 Les fonctions agrégatives (statistiques)

  18. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives Namur Moyenne Ecart_max Nombre Namur -2520 4580 4 2.8 Les fonctions agrégatives (statistiques) select 'Namur',avg(COMPTE) as Moyenne, max(COMPTE)-min(COMPTE) as Ecart_max, count(*) as Nombre from CLIENT where LOCALITE = 'Namur'; le résultat ne comprend qu'une seule ligne select sum(QSTOCK*PRIX) from PRODUIT where LIBELLE like '%SAPIN%';

  19. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives 2.9 Les sous-requêtes

  20. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives NCLI B062 C123 L422 S127 2.9 Les sous-requêtes - Principe Les numéros des clients de Namur : select NCLI from CLIENT where LOCALITE = 'Namur';

  21. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives NCLI B062 C123 L422 S127 2.9 Les sous-requêtes - Principe Les numéros des clients de Namur : Les numéros des commandes des clients de Namur : select NCOM, DATECOM from COMMANDE where NCLI in ('B062','C123','L422','S127'); ne marche qu'une fois mieux : select NCOM, DATECOM from COMMANDE where NCLI in (select NCLI from CLIENT where LOCALITE = 'Namur'); marchera toujours

  22. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives 2.9 Les sous-requêtes - Principe select * from PRODUIT where NPRO in (select NPRO from DETAIL where NCOM in (select NCOM from COMMANDE where NCLI in (select NCLI from CLIENT where LOCALITE='Namur')));

  23. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives les clients de Namur les commandes des clients de Namur les détails des commandes des clients de Namur les produits référencés par les détails des commandes des clients de Namur 2.9 Les sous-requêtes - Principe select * from PRODUIT where NPRO in (select NPRO from DETAIL where NCOM in (select NCOM from COMMANDE where NCLI in (select NCLI from CLIENT where LOCALITE='Namur')));

  24. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives 2.9 Les sous-requêtes - Condition d'association Une condition in (sous-requête) correspond le plus souvent à une condition d'association = qui sont associés à ... select * from T where CT in (select CS from S where <condition>); "on recherche les Tqui sont associés à des S qui ..."

  25. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives 2.9 Les sous-requêtes - Condition d'association Remarque : symétrie des conditions d'association select * from COMMANDE where NCLI in (select NCLI from CLIENT where LOCALITE = 'Namur'); select * from CLIENT where NCLI in (select NCLI from COMMANDE where DATECOM = '12-09-2009');

  26. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives 2.10 Les jointures

  27. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives 2.10 Les jointures La jointure permet de produire une table constituée de données extraites de plusieurs tables : colonne commune NCOM DATECOM NCLI NOM LOCALITE 30178 30179 30182 30184 30185 30186 30188 21/12/2008 22/12/2008 23/12/2008 23/12/2008 2/01/2009 2/01/2009 3/01/2009 K111 C400 S127 C400 F011 C400 B512 VANBIST FERARD VANDERKA FERARD PONCELET FERARD GILLET Lille Poitiers Namur Poitiers Toulouse Poitiers Toulouse table COMMANDE table CLIENT select NCOM, DATECOM, CLIENT.NCLI, NOM, LOCALITE from COMMANDE, CLIENT where COMMANDE.NCLI = CLIENT.NCLI;

  28. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives 2.10 Les jointures - Structure d'une requête de jointure NCOM DATECOM NCLI NOM LOCALITE 30178 30179 30182 30184 30185 30186 30188 21/12/2008 22/12/2008 23/12/2008 23/12/2008 2/01/2009 2/01/2009 3/01/2009 K111 C400 S127 C400 F011 C400 B512 VANBIST FERARD VANDERKA FERARD PONCELET FERARD GILLET Lille Poitiers Namur Poitiers Toulouse Poitiers Toulouse préfixe nécessaire car ambiguité select NCOM, DATECOM, CLIENT.NCLI, NOM, LOCALITE from COMMANDE, CLIENT where COMMANDE.NCLI = CLIENT.NCLI plusieurs tables condition de jointure

  29. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives 2.10 Les jointures - Variantes select CLIENT.NCLI, NOM, DATECOM, NPRO from CLIENT, COMMANDE, DETAIL where CLIENT.NCLI = COMMANDE.NCLI and COMMANDE.NCOM = DETAIL.NCOM; jointure de 3 tables select NCOM, CLIENT.NCLI, DATECOM, NOM, ADRESSE from COMMANDE, CLIENT where COMMANDE.NCLI = CLIENT.NCLI and CAT = 'C1' and DATECOM < '23-12-2009'; condition de jointure + conditions de sélection

  30. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives 2.10 Les jointures - Valeurs dérivées dans une jointure select NCOM, DETAIL.NPRO, QCOM*PRIX from DETAIL, PRODUIT where DETAIL.NPRO = PRODUIT.NPRO; select 'Montant commande 30184 = ', sum(QCOM*PRIX) from DETAIL, PRODUIT where DETAIL.NCOM = '30184' and DETAIL.NPRO = PRODUIT.NPRO;

  31. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives 2.8 Modification des données

  32. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives 3.6 Modification des données - Insertion de lignes Insérer une ligne insert into DETAIL values ('30185','PA45',12); insert into CLIENT (NCLI,NOM,ADRESSE,COMPTE,LOCALITE) values ('C402','BERNIER','avenue de France, 28',-2500,'Lausanne'); colonnes facultatives, valeurs par défaut Insérer plusieurs lignes (dans une table) insert into CLIENT_TOULOUSE select NCLI, NOM, ADRESSE from CLIENT where LOCALITE = 'Toulouse';

  33. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives 3.6 Modification des données - Suppression de lignes delete from CLIENT where NCLI = 'K111'; delete from DETAIL where NPRO in (select NPRO from PRODUIT where QSTOCK <= 0); delete from CLIENT where CAT is null;

  34. 2.5 Extraction simple 2.9 Les sous-requêtes 2.6 Conditions plus complexes 2.10 Les jointures 2.7 Données extraites et dérivées 2.11 Modification des données 2.8 Les fonctions agrégatives 3.6 Modification des données - Modification de lignes update CLIENT set ADRESSE = '29, av. de la Magne', LOCALITE = 'Niort' where NCLI = 'F011'; update PRODUIT set PRIX = PRIX * 1.05 where LIBELLE like '%SAPIN%';

More Related