Manipulation des données dans SQL

Langage de manipulation de données LMD

• Les commandes du LMD sont :– INSERT : ajoute des lignes à une table.– UPDATE : mettre à jour les colonnes d’une table.– DELETE : suppression d’un ou de plusieurs

enregistrements.– SELECT : définition de la liste des colonnes que

l’on peut obtenir.

1-Insertion de données

• L’instruction INSERT ajoute des lignes à une table. Les valeurs à ajouter lors d’une insertion doivent vérifier les contraintes définies au moment de la définition des données. Tout enregistrement ne vérifiant pas les contraintes sera rejeté.

• Il existe deux formes d’insertions de données : – Insertion des valeurs pour la totalité des valeurs de la table

– Insertion à travers la copie des valeurs des colonnes d'une autre table

Insertion des valeurs pour la totalité des valeurs de la table• Syntaxe :

INSERT INTO nom_table [(les champs de la table)] VALUES (liste des valeurs) ;

• Les valeurs des colonnes de type caractère ou chaîne de caractère doivent apparaître entre deux cotes ( ' ' ).

• Remarque : • Il est possible d’insérer un enregistrement en connaissant

seulement les valeurs de quelques champs. Pour cela , il faut – Soit indiquer la liste des champs à insérer et leurs valeurs

respectives– Soit mettre NULL dans la liste de valeurs pour les champs vides.

• Les champs ayant été créés avec la contrainte Not Null devront, obligatoirement, avoir des valeurs.

Exemple

Soit la table Produit (Numprod, Desprod, Couleur, Poids, Qte_stk, Qte_seuil, Prix)

Numprod Desprod Couleur Poids Qte_stk Qte_seuil Prix

100 Ordinateur R 15.2 3 2 100.275

80 Disquette

200 Souris G

Donner la commande permettant de remplir la première ligne de la table produit.

INSERT INTO produit (Numprod, Desprod, Couleur, Poids, Qte_stk, Qte_seuil, Prix) VALUES (100, ' Ordinateur', 'R', 15.2, 3, 2, 100.275);

• Dans le cas précédent toutes les colonnes sont à saisir, on peut donc ne pas spécifier les noms des colonnes après le nom de la table. La commande devient :

INSERT INTO produit VALUES (100, ' Ordinateur', 'R', 15.2, 3, 2, 100.275);

• Donner la commande permettant de remplir la deuxième ligne du tableau précédent.

INSERT INTO produit (Numprod, Desprod) VALUES (80, 'Disquette');

• Donner la commande permettant de remplir la troisième

ligne du tableau précédent. INSERT INTO produit (Numprod, Desprod, Couleur) VALUES (200, 'Souris', 'G');

Insertion à travers la copie des valeurs des colonnes d'une autre table• Syntaxe :

INSERT INTO nom_table [(les champs de la table)] Requête;

• Exemple :

INSERT INTO client (num_clt, nom_clt, tel_clt) Select * from client_Monastir ;

2- Modification de données• L'opération de modification consiste à mettre à jour les colonnes d'une

table par de nouvelles valeurs.• Syntaxe :

UPDATE nom_table SET col1 = val1 , … , coln = valn WHERE condition ;

• Remarques :• Il n'est pas possible de mettre à jour plus qu'une table à la fois.• La modification des données n’est pas autorisée que si les contraintes

sont toujours valides. • Les valeurs peuvent être des constantes, des expressions, des résultats

de sous-requêtes ou NULL (pour supprimer la valeur initiale du champ).

• Si la clause Where n’apparaît pas dans la commande, il s’agit de mettre à jour tous les enregistrements de la table avec la même valeur.

Exemple

• Modifier la désignation du produit numéro 80 en Imprimante

UPDATE Produit SET DesProd = 'Imprimante' WHERE numProd = 80;

• Majorer de 5% les prix des produits dont le prix est supérieur à 10.

UPDATE Produit SET Prix = Prix * 1.05 WHERE prix > 10;

• Modifier les quantités de tous les produits avec la valeur 10.

UPDATE produit SET Qte_stk = 10;

3- Suppression de données

• La commande DELETE permet de supprimer un ou plusieurs enregistrements d’une table tout en gardant la base cohérente. Les enregistrements à supprimer sont spécifiés par la condition de la clause WHERE.

• Syntaxe :DELETE FROM nom_table[WHERE condition];

• Exemple :• Supprimer tous les Produits de couleurs Blanche

DELETE FROM Produit Where Couleur = 'B';

• Supprimer toutes les lignes de la table ProduitDELETE FROM Produit;

4- Consultation de données

• SQL est basé sur l'utilisation de blocks de qualification dont la structure est composée principalement de trois éléments :

• SELECT : permet de définir la liste des colonnes que l'on peut obtenir.• FROM : n'indique que les noms des tables nécessaires pour obtenir le

résultat souhaité.• WHERE : définit la condition que doit vérifier un n-uplet donné pour qu'il

fasse partie du résultat.• Syntaxe :

SELECT col1, col2, … ,colnFROM nom_table[WHERE condition];

• Remarque – Si on souhaite avoir toutes les colonnes de la table, on mettra après SELECT *.

– Si on souhaite avoir toutes les lignes de la table, on ne met pas la clause WHERE.

Exemple

• On considère la table Produit suivante :

Numprod Desprod Couleur Poids Qte_stk Qte_seuil Prix

100 Ordinateur

R 15.2 3 2 100.275

80 Disquette 20 24 4 125

200 Souris G 30 12 5 200.450

102 Tapis R 0.125 10 5 10

1-Afficher les numéros et désignations de tous les produits2-Afficher les numéros et désignations de produits existants en stock avec une quantité > 203-Afficher les produits existants en stock avec une quantité > 204-Afficher les couleurs des différents produits.

Solution

• 1-

Select numprod, desprod

From produit;

• 2-

Select numprod, desprod

From produit

Where qte_stk >20;

• 3-

Select *

From produit

Where qte_stk > 20;

• 4-

Select distinct couleur

From produit;

4-1.Notion d’alias • Chaque fois qu'une requête d'interrogation est exécutée, les noms

des colonnes définies dans la structure de la table apparaissent comme titres de colonnes. On peut modifier ces noms de colonnes, à l'affichage uniquement, en ajoutant des ALIAS. Si l’alias est composé de plusieurs mots il faut qu’il apparaisse entre cotes " ".

• La commande devient :SELECT col1 [alias 1], col2 [alias 2], … , coln [alias n]

FROM nom_table[WHERE condition];

• Exemple Afficher les numéros et désignations de tous les produits. Les titres des colonnes à afficher seront respectivement Numéro et Nom Produit

• Solution Select numprod Numéro, desprod "Nom produit" From Produit;

4-2 Spécification d’un critère dans une requête

• La condition est composée généralement de trois termes :• un nom de colonne,• un opérateur de comparaison,• et une constante, une colonne, une liste de valeurs, une expression ou une

requête.• En plus des opérateurs classiques de comparaison ( = , <> , < , > , >= , <= )

ORACLE dispose d'un certain nombre d'opérateurs spécifiques dont principalement :– IS NULL : permet de tester si le contenu d'une colonne est une valeur nulle

(indéfinie).– IN (liste de valeurs): permet de tester si le contenu d'une colonne coïncide avec

l'une des valeurs de la liste.– BETWEEN V1 and V2 : permet de tester si le contenu d'une colonne est compris

entre les valeurs V1 et V2.– LIKE chaîne générique : permet de tester si le contenu d'une colonne ressemble à

une chaîne de caractères obtenue à partir de la chaîne générique. La chaîne générique est une chaîne de caractères qui contient l'un des caractères suivants :• % : remplace une autre chaîne de caractères qui peut être même une chaîne vide.• _ : remplace un seul caractère.

• Si la condition est composée de sous conditions, on fait recours aux opérateurs logiques AND, OR et la négation NOT (NOT In, NOT Like, NOT Between, Is NOT LIKE)

• Exemple : • Afficher les numéros de produits dont la couleur n’a pas été saisie,• Afficher les produits de couleur Rouge, Bleu ou Gris,• Afficher les numéros de produits dont le prix est compris entre 100

et 200,• Afficher les produits dont la désignation commence par 'o',• Afficher les numéros et désignations des produits dont les noms

commencent par 'o' ou par 's'.• Afficher les désignations des produits contenant 'r' en deuxième

position de la désignation et existant en stock avec une quantité > 20.

Solution 1) SELECT * FROM produit WHERE couleur is NULL;

2) SELECT * FROM produit WHERE couleur IN ('R', 'B', 'G');

3) SELECT numprod FROM produit WHERE prix BETWEEN 100 and 200;

4) SELECT * FROM produit WHERE desprod LIKE 'o%';

5) SELECT numprod, desprod FROM produit WHERE desprod LIKE 'o%' or desprod like 's%';

6) SELECT desprod FROM produit WHERE desprod LIKE '_r%' and qte_stk>20;

• Remarque • Il existe une forme de création de tables accompagnée d'une

insertion. Cette forme permet de créer une table et d'y insérer des données à partir d'une ou plusieurs tables ou vues.

• Syntaxe :CREATE TABLE nom_table[ (col1 type [(taille)] [null / not null] ,col2 type [(taille)] [null / not null] ,. . .coln type [(taille)] [null / not null] ) ]AS requête ;

• Avec cette forme, la spécification des colonnes n'est pas obligatoire. La requête est une opération de sélection qui indique les colonnes sources, les tables auxquelles appartiennent ces colonnes et éventuellement des critères de sélection.

• Il est à noter qu’aucune contrainte n’est pour la nouvelle table. Il faudrait les ajouter ultérieurement.

• Exemple :• Créer une table contenant uniquement les magasins de Tunis.

CREATE TABLE magasin_tunisAS SELECT * FROM magasinWHERE ville = 'Tunis' ;

5-Les fonctions intégrées de SQL

• Dans les clauses SELECT et WHERE, on peut utiliser des expressions arithmétiques et des fonctions :• ABS(n) : permet de calculer la valeur absolue de n.• CEIL(n) : permet d'avoir le plus petit entier supérieur ou égal à n.

– Ceil(128.3) retourne 129– Ceil(128.8) retourne 129

• FLOOR(n) : permet d'avoir la partie entière de n.– FLOOR(128.3) retourne 128– FLOOR(128.7) retourne 128

• MOD(m,n) : permet d'avoir le reste de la division entière de m par n.• ROUND(m,n) : arrondit la valeur n à m décimal.

– Round(128.3) retourne 128– Round(128.8) retourne 129

• POWER(m,n) : permet d'avoir m puissance n• SIGN(n) : donne –1 si n <0, donne 0 si n=0 et donne 1 si n>1.• SQRT(n) : permet d'avoir √x.• TRUNC(m,n) : permet de tronquer la valeur après m décimales.

Si n est négatif, la valeur de m est tronquée avant le point décimal.– TRUNC(121.371,1) retourne 121.3– TRUNC(121.371,2) retourne 121.37

TRUNC(121.371,-1) retourne 120TRUNC(121.371,-2) retourne 100

5-1- Les expressions arithmétiques

Exemple • Afficher la désignation et les prix arrondis en Dinars de tous les

produits SELECT desprod, ROUND (prix) "Prix en D"FROM produit;

• En supposant que les numéros des produits sont exactement sur trois position, afficher les désignations des produits dont le numéro commence par 1SELECT desprod FROM Produit Where trunc (numprod,-2) = 100;

• Modifier la table produit de manière à majorer les prix en dinars.Update produitSet prix = CEIL(prix);

• Supprimer tous les produits dont le prix appartient à [100,101[Delete from ProduitWhere floor (prix) = 100;

5-2- Les fonctions s'appliquant aux chaînes de caractères

• RTRIM (ch) : supprime l'espace à la fin de la chaîne LTRIM.

• RPAD (ch,n) : ajoute n espaces l'espace à la fin de la chaîne LPAD.

• INITCAP(ch) : met en majuscule la première lettre de chaque mot de la chaîne.

• INSTR(ch1,ch2,n,m) : donne la position de la mième occurrence de ch2 dans ch1 à partir du caractère à la position n.

• LENGTH(ch) : renvoie la longueur d'une chaîne.

• LOWER(ch) : transforme la chaîne ch en minuscule UPPER.

• SUBSTR(ch,m,n) : permet d'extraire une sous-chaîne de ch qui commence à partir du caractère à la position m et de longueur n.

• TRANSLATE(ch,ch1,ch2) : permet de transformer dans la chaîne ch toutes les occurrences de ch1 par ch2.

• Replace(ch, ch1[,ch2]) : remplace une chaîne par une autre dans une colonne. Si on ne met pas ch2, ch1 va être remplacée par un vide.

• ch1ch2 : concatène les deux chaînes.

Exemple • Afficher les numéros des produits correspondants à des souris.

SELECT numprod From Produit Where upper(ltrim(rtrim(desprod)))=‘SOURIS';

• Supprimer les produits dont la désignation est composée au maximum de 6 caractères. DELETE FROM Produit Where lenght (desprod)<=6

• Afficher les désignations des produits avec la première lettre en majusculeSELECT distinct initcap(lower(desprod)) "désignation" FROM Produit;

• Afficher les numéros et couleurs des produits dont la désignation contient "er" à partir de la 6ème position. SELECT numprod, couleur FROM Produit Where Lower(substr(desprod,6,2))='er';

• Afficher toutes les désignations des produits en remplaçant toute "a" par "A" SELECT distinct translate (desprod, 'a', 'A') désignation FROM Produit;

Exemple

• Modifier la table Produit en remplaçant les Scanners en Imprimantes.Update ProduitSet desprod = replace (desprod, 'Imprimante', 'scanner');

• Ajouter le champ Propriete de type caractère sur 4 positions formé des trois premières caractères de la désignation concaténés à la couleur. Alter table produitAdd propriete varchar(4);

Update produitSet propriete= substr (ltrim (desprod),1,3) || couleur;

5-3- Les expressions s'appliquant à des dates

• ADD_MONTHS(d,n) : permet d'ajouter n mois à la date d sachant que n est un entier.

• GREATEST(d1,d2) : permet d'avoir la date la plus récente parmi d1 et d2 LEAST.

• MONTHS_BETWEEN(d1,d2) : permet d'avoir le nombre de mois qui se trouvent entre la date d1 et la date d2.

• LAST_DAY(d) : permet d'avoir la date du dernier jour de la date d.LAST_DAY('02/01/07') donne : 31/01/07

• SYSDATE : donne la date et l'heure système.

Exemple • Soit la table Employé (Matricule, Nom, Prenom, Date_nais,

Date_emb)– On suppose qu’un employé est à la retraite à l’âge de 60 ans,

afficher les noms et prénoms des employés ainsi que leurs dates de retraite

– En supposant qu’un employé est à la retraite après 30 ans de service. Afficher les noms et prénoms des employés ainsi que les dates prévus pour leurs retraites

– En supposant qu’un employé est à la retraite après 30 ans de service ou à l’age de 60 ans. Afficher les noms et prénoms des employés ainsi que les dates prévus pour leurs retraites

– Afficher le matricule et l’âge en mois de l’employé lors de son embauche

– Afficher le matricule et l’âge en années de l’employé lors de son embauche

– Afficher le matricule et le nombre de jours de travail pendant le premier mois d’embauche de chaque employé.

Solution • Select nom, prenom, add_months(date_Nais, 720) "retraite"

From Employe;• Select nom, prenom, add_months(date_Emb, 360) "retraite"

From Employe;• Select nom, prenom, least (add_months(date_Nais,720),

add_months(date_Emb, 360)) "retraite"From Employe;

• Select matricule, months_between(date_Emb, date_Nais) "Age en Mois" From Employe;

• Select matricule, floor (months_between(date_Emb, date_Nais)/12) "Age" From Employe;

• Select matricule, last_day (date_Emb)-date_Emb + 1 "Nombre de Jours" From Employe;

5-4 Les fonctions de conversion

• TO_CHAR(valeur-date,format-date) / TO_CHAR(nombre[,format]) : convertit une date ou une valeur numérique à une chaîne de caractères.

• TO_DATE(valeur-chaîne,format-date) : convertit une chaîne de caractères représentant une date à une date.

• TO_NUMBER(ch[,format]) : convertit une chaîne de caractères représentant un nombre en nombre.

Quelques formats de datesYYYY : Année sans virgule YY : 2 derniers chiffres de l’année YEAR : Année en toute lettre Q : Numéro de trimestre de l’année (1 à 4) MM : Numéro de mois (1 à 12).MON : Nom du mois abrégé en 3 lettreWW : Numéro de semaine de l’année (1 à 52)

W : Numéro de semaine dans le mois DDD : Numéro du jour dans l’année (1 à 366)

DD : Numéro du jour dans le mois (1 à 31) D : Numéro du jour dans la semaine (1 à 7)

DAY : Nom du jour sur 9 caractères DY : Nom du jour abrége en 3 lettres HH12 : Heure sur 12 heures (1 à 12)HH24 : Heure sur 24 heures (1 à 24)M : Minutes (1 à 60)SS : Secondes (1 à 60)SSSS : Secondes après minuit

Exemple

• Afficher les employés embauchés pendant l’année en cours ;

Select * From Employe

Where to_char(date_Emb, 'YYYY') = to_char(sysdate, 'YYYY');

• Afficher les employés ayant au moins 3 ans d’ancienneté ;

Select * From Employe

Where to_number(to_char(sysdate, 'YYYY'))-to_number(to_char(date_Emb, 'YYYY'))>=3;

6- Les expressions agrégats (ou fonctions de groupe)

• ORACLE dispose d'une fonction appelée fonctions agrégats qui s'appliquent à un ensemble de données :– AVG : permet d'avoir la moyenne arithmétique d'un ensemble

donné.

– COUNT : permet d'avoir le nombre d'occurrences des enregistrements.

– MAX : permet d'avoir la valeur maximale dans une colonne.

– MIN : permet d'avoir la valeur minimale dans une colonne.

– SUM : permet d'avoir la somme des éléments.

– STDDEV : permet d'avoir l'écart type.

– VARIANCE : permet d'avoir la variance.

• Chacune de ces fonctions a comme argument un nom de colonne ou une expression arithmétique. Elles ignorent les valeurs nulles et par défaut prennent les valeurs multiples pour des valeurs différentes.

• Pour ne prendre que les valeurs distinctes, il faut ajouter l'opérateur DISTINCT. Et si l'on veut insister pour prendre toutes les valeurs qui existent, on doit précéder la colonne ou l'expression par l'opérateur ALL.

• La fonction COUNT peut prendre comme argument le caractère * pour connaître le nombre de lignes sélectionnées.

• Remarque • On ne peut pas mettre une fonction de groupe après la clause

WHERE parce qu'elle s'agit d'une valeur inconnue.

Exemple • Donner le nombre de produits de couleurs rouge

SELECT Count(*) "Nombre" FROM Produit Where Upper(Couleur)='R';

• Afficher la quantité totale en stock des produits de couleur RougeSELECT sum(qte_stk) "quantité" FROM ProduitWhere Upper (Couleur) = 'R';

• Afficher la quantité moyenne en stock des produits de couleur RougeSELECT avg(qte_stk) "quantité" FROM ProduitWhere upper (Couleur) = 'R';

• Afficher les quantités minimales et maximale des produits en stockSELECT max(qte_stk) "maximum" , min(qte_stk) "minimum" FROM Produit;

Tri des résultats• Pour obtenir un résultat trié, il suffit d'ajouter à la requête SQL la

clause :• ORDER BY expression [asc / desc]• Remarque :

Si on ne spécifie pas asc ou desc par défaut le tri est croissant (asc).• Exemple :• Donner la liste des produits ordonnés par ordre croissant de leurs

prix.SELECT * FROM produitORDER BY prix;

• Donner la liste des produits ordonnés par ordre croissant de leurs prix et décroissant de leurs désignations.SELECT * FROM produitORDER BY prix, desprod desc;

La pseudo-colonne ROWNUM

• La pseudo-colonne ROWNUM retourne un entier indiquant

l’ordre séquentiel de chaque enregistrement extrait par la requête. • Exemple :• SQL> select rownum, Numprod, Desprod from produit;

• ROWNUM NUMPROD DESPROD -------------- --------------- ---------------

1 80 Imprimante 2 200 Souris 3 100 Ordinateur