Langage de manipulation des données (LMD) Chapitre III Utilisation d’un SGBD – SQL : Langage de manipulation des données (LMD) • SQL est un langage créé par Codd pour mettre en œuvre son modèle relationnel. • Langage le plus diffusé au sein des SGBD relationnels (DB2, Oracle, postgreSQL, MySQL, …) • C’est un langage de définition (LDD) et de manipulation (LMD) de données. • Langage normalisé. 1. SELECT (requêtes 1. SELECT (requêtessimples) simples) SELECT [ DISTINCT | ALL ] <nom-attr-1>, <nom-attr-2>, … FROM <nom-table> [ WHERE <condition>] [ ORDER BY <nom-attr-i> [ ASC | DESC ], … ]; 4 instructions principales : SELECT pour l’interrogation d’une ou plusieurs tables afficher le contenu des tables ou une fonction du contenu INSERT pour l’ajout de lignes (tuples) dans une table UPDATE pour la modification de lignes DELETE pour la suppression de lignes Alias d’attributs et de tables Résultat Structure de base : Le Langage de manipulation des données (LMD) comprend A1,A2,…,An R1 Résultat Alias d’un attribut : SELECT <nom-attr-i> as <alias-attr-1>, <nom-attr-j> as <alias—attr-2>,… FROM … WHERE … ORDER BY… ; CONDITION où <nom-attr-1> est le nom d’un attribut (= d’une colonne) de la table <nom-table>. R1 Afficher tous les attributs et toutes les tuples (= toute la table) : Alias d’une table : SELECT … FROM <nom-table-1> <alias-table-1>,<nom-table-2> <alias-table-2> WHERE … ORDER BY… ; SELECT * FROM <nom-table> ; 2. Fonctions et expressions Expressions Les opérateurs arithmétiques sont disponibles : -, +, *, / Quelques fonctions mathématiques : ABS, SIN, COS, TAN, ASIN, ACOS, ATAN, COT, EXP, LN, LOG PI(), POWER(a,b), RANDOM(), ROUND, SIGN, SQRT … Expressions logiques Une expression logique est soit vraie (différent de 0 et de NULL), soit fausse (valeur 0), soit indéterminée (NULL) Opérateurs logiques : NOT <expr> <expr1> AND <expr2> <expr1> OR <expr2> <expr1> XOR <expr2> Comparaisons : = > >= < <= BETWEEN <expr-1> AND <expr-2> <expr-1> [NOT] IN (<expr-2>, <expr-3>, …) 1 Expressions pour la valeur NULL Expressions pour chaînes de caractères La valeur NULL Chaîne de caractères <expr-1>||<expr-2> upper(<nom-attr>),lower(<nom-attr>) substring overlay char_length(<str>) trim(<nom-attr>) -- supprime les espaces du début <expr> IS NULL <expr> IS NOT NULL Renvoie de la première expression non nulle COALESCE(<expr-1>,<expr-2>,…) Comparaisons : <expr> LIKE <modèle> ex : SELECT coalesce (note,0) FROM presenceModule ; (Attention : sans l’utilisation de ‘coalesce’, on ne voit pas les valeurs manquantes) Possibilité d’utiliser des caractères spéciaux : _ apparié avec un caractère quelconque % apparié avec un ou plusieurs caractères Expressions pour les dates Requêtes 3. Requêtessur surplusieurs plusieurstables tables Date, heure Il est possible de faire une requête sur plusieurs tables (lien CURRENT_DATE, CURRENT_TIMESTAMP (à la seconde près) now() Opération sur les dates date ‘2009-09-19’ + integer ‘7’ date ‘2009-09-19’ + interval ‘1 hour’ date ‘2009-09-19’ + time ‘23 hours’ Extraction des champs de dates extract(year from <expr-date>) date_part(‘hour’, timestamp <expr-date>) DAYOFMONTH(<expr>), DAYOFYEAR(<expr>), WEEKDAY(<expr>), DATE(<expr-date-heure>), TIME(<expr-date-heure>) clé primaire / étrangère) 2 méthodes : • • Sous-requêtes (subquery) Jointure En général : Les sous-requêtes sont préférables pour comparer des agrégations à d’autres valeurs. Les jointures sont idéales pour afficher des résultats provenant de plusieurs tables. Méthode 1 : Sous-requêtes Méthode 2 : Jointure interne Création d’une requête SELECT dans la clause WHERE Ajout d’une condition de jointure dans la clause WHERE : SELECT … FROM … WHERE <champ-i> <comparaison> | IN | NOT IN ( SELECT <champ1>, <champ2>, … FROM … WHERE … ) ORDER BY … ; SELECT <champ1>, <champ2>, … FROM <nom-table-1>, <nom-table-2>, … WHERE <condition-jointure> ORDER BY <champ1>, <champ2>, …; Résultat champ-j champ-i = Table1 Table2 La condition de jointure doit être du type : Table1.champ-i =Table2.champ-j 2 4.Insertion Insertions de tuple 4.Suppression Modification et Suppression / Modification Insertion d’une ligne (tuple) Modification du contenu : INSERT INTO <nom-table> (<nom-attr-1>, <nom-attr-2>,…) VALUES (<valeur-1>,<valeur-2>,…); Rq: Si on donne la valeur de tous les champs on peut simplifier par: INSERT INTO <nom-table> UPDATE <nom-table1> SET <nom-attr-i> = <expr-1>, <nom-attr-j> = <expr-2>, … WHERE <expr-condition>; VALUES (<valeur-1>,<valeur-2>,…); Insertion multiple à l’aide d’une requête : INSERT INTO <nom-table> (<nom-attr-1>, <nom-attr-2>,…) SELECT …; -- la requête Suppression du contenu : DELETE FROM <nom-table> WHERE <expr-condition>; -- permet d’insérer un commentaire LDD B. Langage de définition des données (LDD ) Instructions SQL permettant d’agir sur les éléments constituant d’un schéma de base de données relationnelles : Tables Vues Index Les instructions principales permettront de : Créer Modifier Supprimer Renommer Création/suppression d’une table Création : CREATE TABLE <nom-table> ( <nom-attr-1> <type> [ <options> ], <nom-attr-2> <type> [ <options> ], … PRIMARY KEY (<nom-attr-i>, <nom-attr-j>,…) ); Modifier une table Renommer : ALTER TABLE <nom-table> RENAME TO <nouveau-nom-table>; Ajouter un attribut ALTER TABLE <nom-table> ADD COLUMN <nom-attr> <type> [ CHECK]; Supprimer un attribut Suppression : ALTER TABLE <nom-table> DROP COLUMN <nom-attr>; DROP TABLE <nom-table>; 3 Modifier une table Modifier une table Changer un attribut : Afficher la définition d’une table : \d <nom-table>; ALTER TABLE <nom-table> RENAME COLUMN <ancien-nom-attribut> TO <nom-attr>; Attention : postgreSQL seulement ! Cette commande ne fait pas partie du standard SQL. Modifier la valeur par défaut d’un attribut ALTER TABLE <nom-table> ALTER COLUMN <nom-attribut> SET DEFAULT <val-par-défaut>; Ajouter/retirer une contraine : ALTER TABLE <nom-table> ADD CHECK ( <nom-attribut> <> ‘’); ALTER TABLE <nom-table> ADD CONSTRAINT <constraint>; ALTER TABLE <nom-table> ADD FOREIGN KEY ( <nom-attribut> ) REFERENCES <nom-table> ; Modifier le type de données d’un attribut ALTER TABLE <nom-table> ALTER COLUMN <nom-attribut> TYPE <nouveau-type>; Types de données SMALLINT entier 2 octets (2 octets=2 x 8bits=16 bits) INTEGER entier 32 bits BIGINT entier 64 bits DECIMAL(d) taille précisée entre () NUMERCI(n) taille précisée entre () REAL 32 bits (précision de 6 décimales) ALTER TABLE <nom-table> ALTER COLUMN <nom-attribut> SET NOT NULL; Types de données CHAR(<longueur>) Texte max 255 octets , longueur fixe VARCHAR(<longueur>) Texte max 255 octets , longueur fixe CHAR VARYING (<longueur>) Texte max 255 octets , longueur variable DOUBLE PRECISION 64 bits (précision de 15 décimales) TEXT Texte longueur variable illimitée DATE Date, format aaa-mm-jj MEDIUMTEXT Texte max 224-1 octets TIME Heure, format hh:mm:ss LONGTEXT Texte max 232-1 octets TIMESTAMP Date et heure, format aaa-mm-jj hh:mm:ss n : taille d’affichage - total d : taille d’affichage – chiffres après la virgule Option d’un attribut PRIMARY KEY Clé primaire, doublons et valeur NULL interdites NOT NULL Valeur NULL interdite UNIQUE Doublons interdits DEFAULT <valeur> Spécifie une valeur par défaut Démarche A partir du schéma relationnel et de l’ensemble des contraintes d’intégrité, on appliquera les étapes suivantes : 1. Création des tables relationnelles A l’aide du LDD créer la structure et les contraintes Le choix des types de données (domaines) est important 2. Chargement des données A l’aide du LMD insérer les données tuple par tuple ou avec des outils d’importations propre à la BD Faire attention aux contraintes lors d’importation de gros volumes 3. Réalisation des requêtes 4