Chapitre 4 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é. Langage de manipulation des données (LMD) Le Langage de manipulation des données (LMD) comprend 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 1. SELECT (requêtes 1. SELECT (requêtessimples) simples) Résultat Structure de base : SELECT [ DISTINCT | ALL ] <nom-attr-1>, <nom-attr-2>, … FROM <nom-table> [ WHERE <condition>] [ ORDER BY <nom-attr-i> [ ASC | DESC ], … ]; A1,A2,…,An R1 Résultat 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) : SELECT * FROM <nom-table> ; Alias d’attributs et de tables Alias d’un attribut : SELECT <nom-attr-i> as <alias-attr-1>, <nom-attr-j> as <alias—attr-2>,… FROM … WHERE … ORDER BY… ; Alias d’une table : SELECT … FROM <nom-table-1> <alias-table-1>,<nom-table-2> <alias-table-2> WHERE … ORDER BY… ; 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>, …) Expressions pour la valeur NULL La valeur NULL <expr> IS NULL <expr> IS NOT NULL Renvoie de la première expression non nulle COALESCE(<expr-1>,<expr-2>,…) ex : SELECT coalesce (note,0) FROM presenceModule ; (Attention : sans l’utilisation de ‘coalesce’, on ne voit pas les valeurs manquantes) Expressions pour chaînes de caractères 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 Comparaisons : <expr> LIKE <modèle> 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 Date, heure 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>) Requêtes 3. Requêtessur surplusieurs plusieurstables tables Il est possible de faire une requête sur plusieurs tables (lien 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 : Requêtes imbriquées Création d’une requête SELECT dans la clause WHERE SELECT … FROM … WHERE <champ-i> <comparaison> | IN | NOT IN ( SELECT <champ1>, <champ2>, … FROM … WHERE … ) ORDER BY … ; Méthode 2 : Jointure interne Ajout d’une condition de jointure dans la clause WHERE : SELECT <champ1>, <champ2>, … FROM <nom-table-1>, <nom-table-2>, … WHERE <condition-jointure> ORDER BY <champ1>, <champ2>, …; Résultat = Table1 La condition de jointure doit être du type : Table1.champ-i =Table2.champ-j champ-j champ-i Table2 4.Insertion Insertions de tuple Insertion d’une ligne (tuple) 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> 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 -- permet d’insérer un commentaire 4.Suppression Modification et Suppression / Modification Modification du contenu : UPDATE <nom-table1> SET <nom-attr-i> = <expr-1>, <nom-attr-j> = <expr-2>, … WHERE <expr-condition>; Suppression du contenu : DELETE FROM <nom-table> WHERE <expr-condition>; B. Langage de définition des données (LDD ) 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>,…) ); Suppression : DROP TABLE <nom-table>; 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 ALTER TABLE <nom-table> DROP COLUMN <nom-attr>; Modifier une table Changer un attribut : ALTER TABLE <nom-table> RENAME COLUMN <ancien-nom-attribut> TO <nom-attr>; Modifier la valeur par défaut d’un attribut ALTER TABLE <nom-table> ALTER COLUMN <nom-attribut> SET DEFAULT <val-par-défaut>; Modifier le type de données d’un attribut ALTER TABLE <nom-table> ALTER COLUMN <nom-attribut> TYPE <nouveau-type>; Modifier une table Afficher la définition d’une table : \d <nom-table>; Attention : postgreSQL seulement ! Cette commande ne fait pas partie du standard SQL. 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> ; ALTER TABLE <nom-table> ALTER COLUMN <nom-attribut> SET NOT NULL; 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) DOUBLE PRECISION 64 bits (précision de 15 décimales) DATE Date, format aaa-mm-jj TIME Heure, format hh:mm:ss 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 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 TEXT Texte longueur variable illimitée MEDIUMTEXT Texte max 224-1 octets LONGTEXT Texte max 232-1 octets 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