SQL Structured Query Language Introduction Interrogation simple Cration

SQL Structured Query Language Introduction Interrogation simple Création de table Manipulation des n-uplets

Introduction Questions ? Qu’est ce que Structured Query Language ? Comment l’utiliser ?

SQL q Définition n n Langage pour accéder aux données gérées par les SGBD Peut être associé avec d’autres langages (c++, java, PHP, …), m n Langage déclaratif (non procédural) dérivé du calcul relationnel m n n Encapsulation de SQL dans un autre langage déclarer ce que l’on veut Langage standard Chaque implémentation de SQL a de légères variantes Thomas Devogele ISI 204 3

SQL q 3 sous langages n Langage de Manipulation de Données (LMD) Permet d’insérer des n uplets, de modifier leur valeur, de les supprimer m Permet d’interroger le SGBD : requêtes m n Langage de définition de données (LDD) m n Permet de créer des tables de les modifier et les supprimer de les renommer Langage de contrôle de données (LCD) m Thomas Devogele Permet de gérer le contrôle d’accès aux données différents utilisateurs ISI 204 4

SQL q Vue n q Contrainte n q Permet de définir des contraintes sur les valeurs des données Indexation et regroupement (cluster) n q Permet de définir des représentations pour différents utilisateurs Permet d’utiliser des mécanisme pour accélérer l’accès aux données Transaction n n Permet de regrouper des commandes SQL Permet de définir des points de retour à des moments ou les valeurs des données étaient cohérentes Thomas Devogele ISI 204 5

Tables d’exemples q Table EMP n La table des employés EMP qui a le schéma suivant m q EMP (NUMERO, NOM, JOB, MRG, HIREDATE, SALAIRE, COMM, DEPTNO) Table DEP n La table des départements DEPT qui a le schéma suivant m Thomas Devogele DEPT (DEPTNO, DNAME, LOC) ISI 204 6

Table d’exemple : EMP Thomas Devogele ISI 204 7

Table d’exemple : DEPT Thomas Devogele ISI 204 8

Requêtes simples Select … from … where …

Manipulation de données q q Recherche d’information : requête (Queries) SELECT colonnes FROM tables WHERE prédicats (condition de recherche) Stockage de données INSERT INTO table [col 1, col 2, . . . , coln] VALUES (value 1, value 2, . . . , valuen) Thomas Devogele ISI 204 10

La sélection de n-uplets q q Permet de sélectionner certaines colonnes d'une table, ainsi que certaines lignes d'une table (les lignes étant sélectionnées en fonction de leur contenu). Permet de combiner des informations venant de plusieurs tables. Thomas Devogele ISI 204 11

Requête q Syntaxe globale select [distinct] { ' [nom table. ]nom _col 1 [, nom _col 2. . . ] } from ( nom table [, nom table 2] } [where prédicat] [group by nom _col 3 [, nom _col 4] [having prédicat] ] [order by nom _col 5 [desc] [, nom _col 6 [desc]. . . ] q q [ ] facultatif Traduction n Sélection des colonnes des tables qui répondent à la condition regroupé par groupes qui répondent à la condition sur les groupes ordonné suivant les valeurs des colonnes Thomas Devogele ISI 204 12

Sélection des colonnes (1) q L'ordre le plus simple a la syntaxe suivante : n select * from nom_table. q Dans ce cas, toutes lignes de la table sont sélectionnées. q Limitation de la sélection à certaines colonnes, n n n q en indiquant une liste de noms de colonnes à la place de l'astérisque select col 1 co 12. . . from nom_table Dans ce cas, on parlera projection exemple : n n sélectionner les noms des employés avec leur JOB associé. select ENAME JOB from EMP; Thomas Devogele ISI 204 13

Sélection des colonnes (2) q q La clause DISTINCT ajoutée derrière l'ordre SELECT permet d'éliminer les duplications si dans le résultat plusieurs n -uplets sont identiques, un seul sera conservé. exemple : n n q lister les jobs présents dans la table EMP. select DISTINCT JOB from EMP; Remarque : n le terme DISTINCT s'applique à toutes les colonnes sélectionnées. Thomas Devogele ISI 204 14

Sélection des lignes (1) q q q La clause WHERE permet de spécifier quelles sont les lignes à sélectionner. Cette clause est suivie d'un prédicat qui sera évalué pour chaque ligne de la table. Seules lignes pour lesquelles le prédicat est vrai seront sélectionnées. Dans ce cas, on parlera de restriction Syntaxe n select … from nom_table where prédicat Thomas Devogele ISI 204 15

Sélection des lignes (2) q Un prédicat n q une expression logique ayant la valeur vrai ou faux. L'expression logique peut contenir des n n n Opérateurs de comparaisons Opérateurs logiques AND, OR NOT BETWEEN LIKE IS NULL Thomas Devogele ISI 204 16

Opérateurs de comparaisons q Opérateurs de comparaisons : m q =, !=, >, >=, <, < Exercice n sélectionner les employés dont la commission est supérieure au salaire Thomas Devogele ISI 204 17

Opérateurs logiques q Opérateurs logiques n q q Les opérateurs AND et OR peuvent être utilisés pour combiner plusieurs prédicats. Exercice n q AND, OR sélectionner les employés du département 30 ayant un salaire supérieur à 1500. Remarque : n n L'opérateur AND est prioritaire par rapport à OR. Des parenthèses peuvent être utilisées Thomas Devogele ISI 204 18

Opérateurs NOT, BETWEEN et IN q L'opérateur NOT n q Opérateur BETWEEN n n q placé devant un prédicat en inverse le sens. expr 1 BETWEEN expr 2 AND expr 3 Ce prédicat est vrai si expr 1 est compris entre expr 2 et expr 3, bornes incluses. Opérateur IN : n n expr 1 IN ( expr 2, expr 3. . ) Ce prédicat est vrai si expr 1 est égale à l'une des expressions de la liste entre parenthèses. Thomas Devogele ISI 204 19

Opérateurs LIKE et IS NULL q Opérateur LIKE n n expr LIKE chaîne où chaque chaîne est une chaîne de caractères pouvant contenir l'un des caractères jokers "_" : remplace 1 caractère exactement. m "%" : remplace une chaîne de caractères de longueur quelconque, y compris de longueur nulle. m q Opérateur IS NULL n n expr IS NULL Ce prédicat est vrai si l'expression à la valeur NULL Thomas Devogele ISI 204 20

Opérateurs q Exemple n n sélectionner les employés qui ont été embauchés en 1981 syntaxe select ENAME, HIREDATE from EMP whrere HIREDATE like '%‑%‑ 81'; q Exercice n n Sélectionner tous les employés qui ont une commission ? Sélectionner les employés qui ont un salaire inférieur à 1200 parmi les clerks et les salesmans. Thomas Devogele ISI 204 21

Les expressions (1) q q Une expression est soit n n une variable désignée par un nom de colonne, une constante. n numérique Caractère date Types des expressions Thomas Devogele ISI 204 22

Les expressions (2) q Constante numérique : n n q nombre contenant éventuellement un signe, un point décimal et un exposant de puissance de dix. Exemple : ‑ 10, 2. 5, 1. 2 E‑ 10 Constante alphanumérique : n n n elle se désigne par une chaîne de caractères entre apostrophes. Exemple 'MARTIN'. Attention majuscule minuscule Thomas Devogele m ‘MARTIN’ ≠ ‘martin’ ≠ ‘Martin’ ISI 204 23

Les expressions (3) q Constante date : n n elle se désigne par une chaîne de caractères au format suivant jour‑mois‑année. jour sur deux chiffres m le mois est désigné par les trois premières lettres de son nom en anglais m l'année est sur deux chiffres m n n Exemple '1‑FEB 85‘ Le format est modifiable Thomas Devogele ISI 204 24

Classement des n-uplets sélectionnés (1) q q q On peut, grâce au mot clé ORDER BY, imposer un ordre dans une requête. Cet ordre peut porter sur une ou plusieurs colonnes. II peut être croissant ou décroissant. La clause ORDER BY devra être placée derrière la clause FROM, et après la clause WHERE si elle existe. Syntaxe n q select col 1 , col 2. . . from nom table where prédicat order by col 1 [DESC], col 2 [DESC]. . . Le tri se fait n n d'abord selon la première colonne spécifiée dans l'ORDER BY, puis les n uplets ayant la même valeur dans la première colonne sont triés selon la deuxième colonne de l'ORDER BY, . . Thomas Devogele ISI 204 25

Classement des n-uplets sélectionnés (2) q Exercices n n sélectionner par ordre croissant tous les employés qui ont été embauché entre le 1 ier Décembre 1980 et le 31 Mars 1981. sélectionner les employés triés par job, et pour chaque job triés par salaire décroissant. Thomas Devogele ISI 204 26

La Jointure q q La jointure permet d'obtenir des informations venant de plusieurs tables dans un même n-uplet résultat. Il existe plusieurs type de jointures n n n l'équi‑jointure La jointure externe La jointure d'une table à elle‑même Thomas Devogele ISI 204 27

Equi‑jointure (1) q Une jointure se formule en spécifiant plusieurs tables dans la clause FROM du SELECT. n Syntaxe select. . . from nom_table 1, nom_table 2. . . Where … q Si aucune condition de sélection WHERE, n q résultat est le produit cartésien des deux tables, Dans notre exemple, n n EMP et DEPT peuvent être "reliées" par l'attribut DEPTNO. C'est en utilisant cet attribut que l'on pourra effectuer une jointure. Thomas Devogele ISI 204 28

Equi‑jointure (2) q Si l'attribut, qui désigne le numéro de département, a le même nom dans les deux tables, n n n il ait nécessaire de préfixer l'attribut DEPTNO par le nom de la table dans le critère de jointure. deptno et emp. deptno Par contre, le nom des attributs ENAME et LOC n'ont pas besoin d'être préfixés puisqu'il n'y a pas d'ambiguïté sur la table à laquelle ces attributs appartiennent. Thomas Devogele ISI 204 29

Equi‑jointure (3) Personnes nom prénom adresse téléphone Martin Pierre 7 allée des vers 0258941236 Dupond Jean 32 allé Poivrot 0526389152 Bibliothèque nom Dernierlivre Dupond Robinson Jospin Faust Martin Misère On joint les deux tables, grâce à la colonne nom. SELECT Personnes. prénom, dernierlivre FROM Personnes, Bibliothèque WHERE Personnes. nom = Bibliothèque. nom Thomas Devogele prénom Dernierlivre Jean Robinson Pierre Misère ISI 204 Et on combine cette jointure à une projection sur les attributs nom et dernierlivre. Attention à lever toute ambi guïté sur les noms d’attribut dans le cas où deux tables possèdent des colonnes de même nom. 30

Equi‑jointure (4) q q Les requêtes utilisant très souvent les jointures, il a été créé des syntaxes plus rapide si les attributs ont le même nom. n Syntaxe SELECT Personnes. nom, nblivres FROM Personnes INNER JOIN Bibliothèque USING (nom) n ce qui signifie que les deux relations Personnes et Bibliothèque sont concaténées (INNER JOIN) en utilisant (USING) l’attribut nom. Thomas Devogele ISI 204 31

Equi‑jointure (5) q q Si les attributs servant pour la jointure ne portent pas le même nom, il faut utiliser la syntaxe ON. Ainsi la jointure précédente peut s’écrire aussi : n n n q SELECT Personnes. nom, nblivres FROM Personnes INNER JOIN Bibliothèque ON Personnes. nom = Bibliothèque. emprunteur Remarque n La méthode INNER JOIN n’inclus les enregistrements de la première table que s’ils ont une correspondance dans la seconde table. Personnes Nom Prénom Bibliothèque Emprunteur Nblivres Résultat de la jointure Nom Nblivres Martin Jean Martine 5 Tartan 10 Tartan Pion Tartan 10 Dupond 3 ISI 204 Thomas Devogele. Jacques Dupond 32

Equi‑jointure (6) q Exercice n q obtenir la liste des employés avec la localité dans laquelle ils travaillent synonyme n n On peut associer un synonyme à un nom de table, il pourra alors être utilisé en préfixant les noms d'attributs. Par contre, l'ancien nom de la table ne pourra plus être utilisé lors du SELECT. Syntaxe m Thomas Devogele Select EMPNO, DEPTNO from EMP e // e est l’alias de EMP where e. DEPTNO = 20; ISI 204 33

La jointure externe (1) q q q Lorsqu'un n-uplet d'une table figurant dans une jointure n'a pas de correspondant dans les autres tables, elle ne satisfait pas le critère d'équi‑jointure et donc ne figure pas dans le résultat de la jointure. Pour obtenir ces n-uplets, il faut accoler (+) au nom de la colonne de la table dans laquelle manquent des éléments, dans la condition d'équi‑jointure. Exemple select ENAME, LOC from EMP, DEPT where EMP. DEPTNO(+) = DEPTNO; q Exercice n sélectionner les départements n'ayant pas d'employés Thomas Devogele ISI 204 34

La jointure externe (2) Le (+) n’existe pas en My. SQL q Pour remédier aux limites de INNER JOIN, il existe la syntaxe LEFT JOIN qui inclus tous les enregistrements de la première table même s’ils n’ont pas de correspondance dans la seconde table. Dans ce cas précis, l’attribut non renseigné prendra la valeur NULL. La jointure devient : q SELECT Personnes. nom, nblivres FROM Personnes LEFT JOIN Bibliothèque ON Personnes. nom = Bibliothèque. nom q RIGHT JOIN est l’opération symétrique q Personnes Bibliothèque Résultat de la jointure Nom Prénom Nblivres Nom Nblivres Martin Jean Martine 5 Martin NULL Tartan Pion Tartan 10 Dupond Jacques Dupond 3 Thomas Devogele ISI 204 35

Jointure d'une table à elle‑même q II peut être utile de rassembler des informations venant d'un n-uplet d'une table avec des informations venant d'un autre n-uplet de la même table. n Utilisation des synonymes obligatoire Thomas Devogele ISI 204 36

Jointure d'une table à elle‑même (2) q Exemple n n Lister les employés en indiquant pour chacun le nom de son manager Syntaxe m q Select e. ENAME, m. ENAME from EMP e, EMP m where e. MGR = m. EMPNO(+); Exercice n Sélectionner les employés gagnant plus que JONES Thomas Devogele ISI 204 37

Modification des données Création, modification, suppression de données

Modification des données q q Le LMD permet également de modifier les informations contenues dans la base. II existe trois commandes pour effectuer des modifications n n n UPDATE: modifie les valeurs d'une table INSERT : ajoute des n uplets à une table DELETE : supprime des n uplets à une table Thomas Devogele ISI 204 39

Mise à jour ‑ UPDATE (1) q q La commande UPDATE permet de modifier les valeurs d'un ou plusieurs champs syntaxe n q q update table set col 1 = nv_valeur, col 2 = nv_valeur 2. . [where prédicat] Les valeurs col 1, col 2. . . sont mises à jour dans tous les n-uplets satisfaisant au prédicats. En l'absence de la clause WHERE, tous les n-uplets sont mis à jours. Thomas Devogele ISI 204 40

Mise à jour ‑ UPDATE (2) q q On peut faire référence aux anciennes valeurs des champs du n-uplets. Exercice n le salaire des analystes est augmenté de 10 Thomas Devogele ISI 204 41

Ajout de n-uplets ‑ INSERT (1) q q La commande INSERT permet d'insérer un n-uplet dans une table en spécifiant les valeurs à insérer. Syntaxe n q insert into table (col 1, col 2. . . ) values (valeur, valeur. . ) La liste des noms de colonne est optionnelle. n Si elle est omise, m n la liste des colonnes sera par défaut la liste de l'ensemble des colonnes de la table dans l'ordre de création de la table. Si une liste de colonne est spécifiée, m Thomas Devogele les colonnes ne figurant pas dans la liste auront la valeur NULL. ISI 204 42

Ajout de n-uplets ‑ INSERT (2) q exemple, ajouter un nouvel employé n q insert into EMP values (17657, 'WILSON', 'ANALYST', 7566. ‘ 10‑MAY‑ 85' , 3400, NULL, 20); II est possible d'insérer dans une tables plusieurs n-uplets provenant d'une autre table. n Syntaxe m Thomas Devogele insert into table (col 1, col 2. . . ) select. . ISI 204 43

Suppression de n-uplets q La commande DELETE permet de supprimer les n-uplets d'une table n Syntaxe m q En l'absence de la clause WHERE n q delete from table [where prédicat]; tous les n uplets de la table sont supprimés. Exemple : n n supprimer tous les employés du département 20 delete from EMP where DEPTNO = 20 ; Thomas Devogele ISI 204 44

Langage de définition de données Création, modification, suppression de tables

Langage de définition de données (LDD) q q Le LDD permet de créer, de modifier et de supprimer la description d'une table. II existe 4 commandes pour effectuer des modifications n n CREATE TABLE: crée une table ALTER TABLE : modifie la structure d'une table DROP TABLE : supprime une table RENAME : renomme la table Thomas Devogele ISI 204 46

création d'une table (1) q q q A la création, la table sera vide. Cependant, un certain espace lui sera alloué. Syntaxe n n create table nom_table (col 1 type 1 [NOT NULL], col 2 type 2 [NOT NULL], . . . ) n n assure que le SGBD interdira lors d'un INSERT ou d'un UPDATE que cette colonne contienne la valeur NULL. Par défaut, la valeur NULL est autorisée. n Char (n) L'option NOT NULL Les principaux types des éléments sont : n n n m n caractères maximum (n <= 255) m nombre à d chiffres maximum (38 chiffres significatifs) m nombre à d chiffres maximum dont p après le point décimal m élément de type DATE. Number (d) Number (d, p) Date Thomas Devogele ISI 204 47

création d'une table (2) q Exemple n Création de la table EMP on écrira m q create table EMP ( EMPNO number(4) not null, ENAME char(10), JOB char(9), MGR number(4), HIREDATE date, SAL number(7, 2), COMM number(7, 2), DEPTNO number(2) not null ); Exercice n Créer une table BONUS, en y insérant les noms et les salaires des MANAGERS. Thomas Devogele ISI 204 48

Modification d'une table (1) q II est possible de modifier la définition d'une table, n n n q soit ajouter une colonne. soit modifier une colonne existante. II n'est pas possible de supprimer une colonne Ajout d'une colonne : ADD n Syntaxe m n alter table nom_table add col 1 type 1, col 2 type 2, . . ) Les valeurs de cette colonne seront alors toutes égales à NULL. Thomas Devogele ISI 204 49

Modification d'une table (2) q Modification d'une colonne ‑ MODIFY n II est possible de modifier la définition d'une colonne, à condition que la nouvelle définition soit compatible avec le contenu de la colonne : il est possible d'augmenter la taille maximale de la colonne, m il est possible de spécifies NOT NUL si la colonne né contient aucune valeur NULL. . . m n Syntaxe m Thomas Devogele alter table nom_table modify (col 1 nv_type 1, col 2 nv_type 2, . . ) ISI 204 50

Suppression d'une table q q q II est possible de supprimer une table les n-uplets et la définition de la table sont détruites. Syntaxe n drop table nom_table Thomas Devogele ISI 204 51

Renommer une table q q II est possible de renommer une table Syntaxe n rename table ancien_nom_table to nouveau_nom_table Thomas Devogele ISI 204 52