IN S T I TUT SUPERIEUR INFORMATIQUE ISI الـمعهـد العـالـي لإلعـالمــيـة TP 5 (L1SE) Langage de Manipulation et d’Interrogation de Données LMD : Insertion, modification et suppression des données LID : Langage d’Interrogation des Données Application 1. Le Langage de Manipulation des Données (LMD) Les commandes de mise-à-jour sont: Insertion (insert into), Destruction (delete) et la Modification (update) des données. L'insertion s'effectue avec la commande INSERT INTO dont la syntaxe est : INSERT INTO Nom_table (Cl1, Cl2, ...) VALUES (V1,V2,…); Etude de cas : soit la base de données « gestion_employés » formée par les tables suivantes : Employé (numE, nom, adresse, salaire, dept#) Projet (nump, designation, budget) Depart (dept, dir#, appellation) Role (num#, nump#, role_emp) Exemple1 : INSERT INTO Employé (numE, nom, salaire) VALUES (34099, 'Arnaud', 456) ;//Les colonnes adresse, dep et sup auront alors des valeurs NULL. INSERT INTO Depart (dept, dir, appellation) VALUES (340, 34099, ' Informatique ') ; Cette syntaxe permet d’insérer une ligne en indiquant les valeurs (V1,V2,…) à introduire pour chaque colonne spécifiées (en respectant l’ordre). Les colonnes non spécifiées seront donc à NULL (ou à la valeur par défaut). On peut aussi insérer toute la ligne sans spécifier les colonnes : INSERT INTO Nom_table VALUES (V1,..., Vn); Mais dans ce cas L’ordre des colonnes doit rester identique sinon certaines valeurs prennent le risque d’être insérées dans la mauvaise colonne. A noter : lorsque le champ à remplir est de type Chaîne de caractères ou Date il faut indiquer le texte entre côtes. En revanche, lorsque la colonne est un numérique il suffit juste d’indiquer le nombre. Exemple2 : INSERT INTO Employé VALUES (34098, 'Gilles', '3456, Gaspe, Mtl', 340) ; Bien entendu le nombre des colonnes et le type de ces derniers doivent être cohérents. Suppression s’effectue avec la clause DELETE dont la syntaxe est : DELETE FROM <table> [WHERE condition] ; Base de Données A.U.2019/2020 Page 1 sur 5 IN S T I TUT SUPERIEUR INFORMATIQUE ISI الـمعهـد العـالـي لإلعـالمــيـة Delete permet de supprimer des lignes dans une table. En utilisant cette commande associée à WHERE il est possible de sélectionner les lignes concernées qui seront supprimées. Exemple 1 : Supprimer les employés dont le nom commence par M DELETE FROM Employé WHERE nom LIKE 'M%' ; Exemple 2 : Supprimer les employés dont les numéros: 68754, 34099 DELETE FROM Employé WHERE NumE IN (68754, 34099); La modification des données s'effectue avec la clause UPDATE dont la syntaxe est : UPDATE nom_table SET {<nomcl> = <expression>, <nomcl> = <expression>, …} WHERE <condition> ; La commande UPDATE permet d’effectuer des modifications sur des lignes existantes. Très souvent cette commande est utilisée avec WHERE pour spécifier sur quelles lignes doivent porter la ou les modifications. Exemple 1 : UPDATE Employé SET adresse = '3456, Gaspe, Mtl' WHERE numE= 34098; Exemple 2 Augmenter le salaire de l’employé n° 34098, par 1000 dinars. UPDATE Employé SET Salaire = Salaire + 1000 WHERE numE=34098; 2. Le Langage d‘Interrogation des Données (LID) : Accès aux données avec la commande SELECT : Syntaxes: SELECT [ ALL | DISTINCT ] {<liste_colonnes> | *} FROM <liste_tables> WHERE <condition> ; (La Clause WHERE Permet de restreindre les lignes) Distinct : retourne uniquement les valeurs différentes La commande SELECT permet d’extraire des informations d’une base de données dont l’utilisation offre les possibilités suivantes : Projection : SQL permet de choisir dans une table, les colonnes que l’on souhaite ramener (afficher) au moyen d’une requête. Vous pouvez déterminer autant de colonnes que vous le souhaitez : SELECT Cl1, Cl2,… FROM T ; Exemple1: Donner les noms et adresses des employés : SELECT nom, adresse FROM Employé; Exemple 2: Donner la liste des projets: SELECT * FROM Projet; Base de Données A.U.2019/2020 Page 2 sur 5 IN S T I TUT SUPERIEUR INFORMATIQUE ISI الـمعهـد العـالـي لإلعـالمــيـة Sélection : SQL permet de choisir dans une table, les lignes que l’on souhaite ramener au moyen d’une requête. Divers critères de sélection sont disponibles à cet effet : SELECT * FROM T WHERE C ; Exemple3: donner la liste des employés tel que lesalaire est différent de 1000 select * from Employé where salaire!=1000; Jointure : SQL permet de joindre des données stockées dans différentes tables, en créant un lien par le biais d’une colonne commune à chacune des tables : SELECT * FROM T1, T2 WHERE T1.C1 = T2.C1 and … T1.Cli = T2.Cli ; avec Ci colonnes communes à T1 et T2 Selon la condition, la clause WHERE peut traduire l’opération restriction ou jointure dans l’algèbre relationnelle Exemple 4: Donner les noms et adresses des employés qui travaillent au département 'Informatique' SELECT E.nom, E.adresse E.dept=D.dept; FROM Employé E, Depart D WHERE D.appellation = 'Informatique' AND E et D : sont des aléas relatifs aux tables Employé et Depart Fonctions de calcul : Ce sont des fonctions qui permettent de faire des calculs et qui s’utilisent dans les clauses : select et having : COUNT, SUM, AVG, MAX, MIN Exemples : - Donner le nombre d’employés qui travaillent au département 'Informatique': SELECT COUNT (*) FROM Employé E, Depart D WHERE appellation = 'Informatique' AND E.dept=D.dept; - Donner le budget total des projets sous la responsabilité de l’employé numéro 35677 : SELECT SUM (budget) FROM Role R, Projet P WHERE P.nump = R.nump AND role_emp =‘responsable’ AND R.num = 35677; Forme de conditions : IN (NOT IN) : Exemple : Donner la liste des directeurs de départements : SELECT * FROM Employé WHERE numE IN (SELECT dir FROM Depart); WHERE R.nump = P.nump AND R.num = 34888); IS (NOT) NULL : Exemple : Donner la liste des employés sans adresse : SELECT * FROM Employé WHERE adresse IS NULL; Base de Données A.U.2019/2020 Page 3 sur 5 IN S T I TUT SUPERIEUR INFORMATIQUE ISI الـمعهـد العـالـي لإلعـالمــيـة (NOT) EXISTS : Exemple : Donner les noms des employés qui ne sont impliqués dans aucun projet : SELECT nom FROM Employé E WHERE EXISTS (SELECT * FROM Role R WHERE R.num = E.numE ); Clause de regroupement : la Clause est : GROUP BY <liste_colonnes> Exemple : Donner le nombre des employés regroupés par projet SELECT R.nump, count (numE) FROM Employé E, Role R WHERE E.numE = R.num GROUP BY R.nump; Clause : HAVING <condition> Exemple : Donner les numéros de projets dans lesquels interviennent au moins deux employés. SELECT R.nump, count(numE) FROM Employé E, Role R WHERE E.numE = R.num GROUP BY nump HAVING COUNT(nume) > =2; Ordonnancement des résultats : la Clause est : ORDER BY {<colonnes> [ASC | DESC]} Exemple Donner la liste des projets dans lesquels participe l'employé 33549 par ordre décroissant de budget SELECT * FROM Projet WHERE nume = 33549 ORDER BY budget DESC; Syntaxes générale SELECT {<colonne1>,… {<colonnen>| *} FROM {table| table1,…tablej} [ WHERE < liste_conditions> AND <liste_jointures> ] [ GROUP BY <liste_colonnes>] [ HAVING <condition_groupe> ] [ ORDER BY {<colonne> [ASC | DESC]}]; Si on extrait des données provenant de n tables alors on doit avoir au moins (n-1) conditions de jointures. SELECT [DISTINCT] (<nom_colonne>)* liste des colonnes de projection FROM (<nom_table>)* liste des tables touchées par la question [WHERE (<condition | jointure>)*] liste des critères de restriction [GROUP BY (<nom_colonne>)*] liste des colonnes d’agrégation/groupement [HAVING (<condition_sur_le groupe>)* ] liste des critères de restrictions sur les agrégats/ groupes [ORDER BY (<nom_colonne> [ASC/DESC])*]; liste des colonnes de tri du résultat Base de Données A.U.2019/2020 Page 4 sur 5 IN S T I TUT SUPERIEUR INFORMATIQUE ISI الـمعهـد العـالـي لإلعـالمــيـة Application Usine NumUsine 1 2 3 4 NomUsine Citroen Peugeot Citroen Renault adresse Paris Sochaux Sochaux Londres Fournisseur NumF NomF 1 Monroe 2 Au bon siège 3 Saint Gobain Livraison NumProd 3 1 1 2 3 3 3 Produit NumProd 1 2 3 4 Statut Producteur Soustraitant Producteur NumUsine 1 2 3 2 1 2 3 NumF 2 3 3 3 1 1 1 NomProd Plaquette siège siège Tapis Couleur Noir rouge vert Noir Poids 0.257 15.230 15.230 1.01 Adresse Lyon Limoges Paris Qte 40 2500 3000 120 49 45 78 a) Insérer les données aux différentes tables. b) Mettre à jour: i. Augmenter la quantité, du fournisseur dont le numéro est 2, de 20. ii. Ajouter un nouveau fournisseur : < 45, Omar, sous-traitant, Saint-Etienne >. iii. Supprimer tous les produits de couleur noire et de numéro compris entre 100 et 199. iv. Changer la ville du fournisseur n01 : il a déménagé à Lyon. c) Exprimer en SQL les requêtes suivantes : i. Donner le numéro, le nom et la ville de toutes les usines. ii. Donner le numéro, le nom et la ville de toutes les usines qui ne sont pas situées à Londres. iii. Donner les numéros de fournisseurs qui approvisionnent l'usine n°1 en produit n°3. iv. Donner le nom et la couleur des produits livrés par le fournisseur n°2. v. Donner les numéros des fournisseurs qui approvisionnent l'usine n°1 en produit rouge. Base de Données A.U.2019/2020 Page 5 sur 5