sql structured query language

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

Upload: duc

Post on 24-Jan-2016

113 views

Category:

Documents


2 download

DESCRIPTION

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. Définition Langage pour accéder aux données gérées par les SGBD - PowerPoint PPT Presentation

TRANSCRIPT

Page 1: SQL Structured Query Language

SQL Structured Query Language

IntroductionInterrogation simple

Création de tableManipulation des n-uplets

Page 2: SQL Structured Query Language

Introduction

Questions ?Qu’est ce que Structured Query Language ?

Comment l’utiliser ?

Page 3: SQL Structured Query Language

Thomas Devogele ISI 204 3

SQL

Définition Langage pour accéder aux données gérées par

les SGBD Peut être associé avec d’autres langages (c++,

java, PHP, …), Encapsulation de SQL dans un autre langage

Langage déclaratif (non procédural) dérivé du calcul relationnel

déclarer ce que l’on veut Langage standard Chaque implémentation de SQL a de légères

variantes

Page 4: SQL Structured Query Language

Thomas Devogele ISI 204 4

SQL

3 sous langages Langage de Manipulation de Données (LMD)

Permet d’insérer des n-uplets, de modifier leur valeur, de les supprimer

Permet d’interroger le SGBD : requêtes Langage de définition de données (LDD)

Permet de créer des tables de les modifier et les supprimer de les renommer

Langage de contrôle de données (LCD) Permet de gérer le contrôle d’accès aux données des

différents utilisateurs

Page 5: SQL Structured Query Language

Thomas Devogele ISI 204 5

SQL

Vue Permet de définir des représentations pour différents

utilisateurs Contrainte

Permet de définir des contraintes sur les valeurs des données

Indexation et regroupement (cluster) Permet d’utiliser des mécanisme pour accélérer l’accès aux

données Transaction

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

Page 6: SQL Structured Query Language

Thomas Devogele ISI 204 6

Tables d’exemples

Table EMP La table des employés EMP qui a le schéma

suivant EMP (NUMERO, NOM, JOB, MRG, HIREDATE,

SALAIRE, COMM, DEPTNO) Table DEP

La table des départements DEPT qui a le schéma suivant

DEPT (DEPTNO, DNAME, LOC)

Page 7: SQL Structured Query Language

Thomas Devogele ISI 204 7

Table d’exemple : EMP

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 3000 10 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 - TURNER SALESMAN 7698 08-SEP-81 1500 30 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10

Page 8: SQL Structured Query Language

Thomas Devogele ISI 204 8

Table d’exemple : DEPT

DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPÉRATIONS BOSTON

Page 9: SQL Structured Query Language

Requêtes simples

Select … from … where …

Page 10: SQL Structured Query Language

Thomas Devogele ISI 204 10

Manipulation de données

Recherche d’information : requête (Queries) SELECT colonnes FROM tables WHERE prédicats (condition de recherche)

Stockage de données INSERT INTO table [col1, col2, ..., coln] VALUES (value1, value2, ..., valuen)

Page 11: SQL Structured Query Language

Thomas Devogele ISI 204 11

La sélection de n-uplets

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.

Page 12: SQL Structured Query Language

Thomas Devogele ISI 204 12

Requête Syntaxe globale

select [distinct] { ' [nom table.]nom _col1 [,nom _col2 ...] } from ( nom table [, nom table2] } [where prédicat] [group by nom _col3 [, nom _col4] [having prédicat] ] [order by nom _col5 [desc] [,nom _col6 [desc] ...]

[ ] facultatif Traduction

Sélection des colonnes des tablesqui répondent à la conditionregroupé par groupesqui répondent à la condition sur les groupesordonné suivant les valeurs des colonnes

Page 13: SQL Structured Query Language

Thomas Devogele ISI 204 13

Sélection des colonnes (1)

L'ordre le plus simple a la syntaxe suivante : select * from nom_table.

Dans ce cas, toutes les lignes de la table sont sélectionnées.

Limitation de la sélection à certaines colonnes, en indiquant une liste de noms de colonnes à la place de

l'astérisque select col1 co12 ... from nom_table Dans ce cas, on parlera projection

exemple : sélectionner les noms des employés avec leur JOB associé. select ENAME JOB from EMP;

Page 14: SQL Structured Query Language

Thomas Devogele ISI 204 14

Sélection des colonnes (2)

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 : lister les jobs présents dans la table EMP. select DISTINCT JOB from EMP;

Remarque : le terme DISTINCT s'applique à toutes les

colonnes sélectionnées.

Page 15: SQL Structured Query Language

Thomas Devogele ISI 204 15

Sélection des lignes (1)

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 les lignes pour lesquelles le prédicat est vrai seront sélectionnées.

Dans ce cas, on parlera de restriction Syntaxe

select … from nom_table where prédicat

Page 16: SQL Structured Query Language

Thomas Devogele ISI 204 16

Sélection des lignes (2)

Un prédicat une expression logique ayant la valeur vrai ou

faux. L'expression logique peut contenir des

Opérateurs de comparaisons Opérateurs logiques AND, OR NOT BETWEEN LIKE IS NULL

Page 17: SQL Structured Query Language

Thomas Devogele ISI 204 17

Opérateurs de comparaisons

Opérateurs de comparaisons : =, !=, >, >=, <, <

Exercice sélectionner les employés dont la commission est

supérieure au salaire

Page 18: SQL Structured Query Language

Thomas Devogele ISI 204 18

Opérateurs logiques

Opérateurs logiques AND, OR

Les opérateurs AND et OR peuvent être utilisés pour combiner plusieurs prédicats.

Exercice sélectionner les employés du département 30 ayant un

salaire supérieur à 1500.

Remarque : L'opérateur AND est prioritaire par rapport à OR. Des parenthèses peuvent être utilisées

Page 19: SQL Structured Query Language

Thomas Devogele ISI 204 19

Opérateurs NOT, BETWEEN et IN

L'opérateur NOT placé devant un prédicat en inverse le sens.

Opérateur BETWEEN expr1 BETWEEN expr2 AND expr3 Ce prédicat est vrai si expr1 est compris entre

expr2 et expr3, bornes incluses. Opérateur IN :

expr1 IN ( expr2, expr3 ....) Ce prédicat est vrai si expr1 est égale à l'une des

expressions de la liste entre parenthèses.

Page 20: SQL Structured Query Language

Thomas Devogele ISI 204 20

Opérateurs LIKE et IS NULL

Opérateur LIKE 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. "%" : remplace une chaîne de caractères de longueur

quelconque, y compris de longueur nulle. Opérateur IS NULL

expr IS NULL Ce prédicat est vrai si l'expression à la valeur

NULL

Page 21: SQL Structured Query Language

Thomas Devogele ISI 204 21

Opérateurs

Exemple sélectionner les employés qui ont été embauchés

en 1981 syntaxe

select ENAME, HIREDATE from EMPwhrere HIREDATE like '%‑%‑81';

Exercice 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.

Page 22: SQL Structured Query Language

Thomas Devogele ISI 204 22

Les expressions (1)

Une expression est soit une variable désignée par un nom de colonne, une constante.

Types des expressions numérique Caractère date

Page 23: SQL Structured Query Language

Thomas Devogele ISI 204 23

Les expressions (2)

Constante numérique : 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 :

elle se désigne par une chaîne de caractères entre apostrophes.

Exemple 'MARTIN'. Attention majuscule minuscule

‘MARTIN’ ≠ ‘martin’ ≠ ‘Martin’

Page 24: SQL Structured Query Language

Thomas Devogele ISI 204 24

Les expressions (3)

Constante date : elle se désigne par une chaîne de caractères au

format suivant jour‑mois‑année.

jour sur deux chiffres le mois est désigné par les trois premières lettres de son

nom en anglais l'année est sur deux chiffres

Exemple '1‑FEB 85‘ Le format est modifiable

Page 25: SQL Structured Query Language

Thomas Devogele ISI 204 25

Classement des n-uplets sélectionnés (1)

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

select col1 , col2 ... from nom table where prédicat order by col1 [DESC], col2 [DESC] ...

Le tri se fait 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, ....

Page 26: SQL Structured Query Language

Thomas Devogele ISI 204 26

Classement des n-uplets sélectionnés (2)

Exercices sélectionner par ordre croissant tous les employés

qui ont été embauché entre le 1ier 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.

Page 27: SQL Structured Query Language

Thomas Devogele ISI 204 27

La Jointure

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 l'équi‑jointure La jointure externe La jointure d'une table à elle‑même

Page 28: SQL Structured Query Language

Thomas Devogele ISI 204 28

Equi‑jointure (1)

Une jointure se formule en spécifiant plusieurs tables dans la clause FROM du SELECT. Syntaxe

select ... from nom_table1, nom_table2 ...Where …

Si aucune condition de sélection WHERE, résultat est le produit cartésien des deux tables,

Dans notre exemple, EMP et DEPT peuvent être "reliées" par l'attribut DEPTNO. C'est en utilisant cet attribut que l'on pourra effectuer une

jointure.

Page 29: SQL Structured Query Language

Thomas Devogele ISI 204 29

Equi‑jointure (2)

Si l'attribut, qui désigne le numéro de département, a le même nom dans les deux tables, il ait nécessaire de préfixer l'attribut DEPTNO par

le nom de la table dans le critère de jointure. dept.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.

Page 30: SQL Structured Query Language

Thomas Devogele ISI 204 30

Equi‑jointure (3)nom Dernierlivre

Dupond Robinson

Jospin Faust

Martin Misère

nom prénom adresse téléphone

Martin Pierre 7 allée des vers 0258941236

Dupond Jean 32 allé Poivrot 0526389152

Personnes

SELECT Personnes.prénom, dernierlivreFROM Personnes, BibliothèqueWHERE Personnes.nom = Bibliothèque.nom

On joint les deux tables, grâce à la colonne nom.

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.

Bibliothèque

prénom Dernierlivre

Jean Robinson

Pierre Misère

Page 31: SQL Structured Query Language

Thomas Devogele ISI 204 31

Equi‑jointure (4)

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. Syntaxe

SELECT Personnes.nom, nblivresFROM Personnes INNER JOIN BibliothèqueUSING (nom)

ce qui signifie que les deux relations Personnes et Bibliothèque sont concaténées (INNER JOIN) en utilisant (USING) l’attribut nom.

Page 32: SQL Structured Query Language

Thomas Devogele ISI 204 32

Equi‑jointure (5)

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 : SELECT Personnes.nom, nblivres FROM Personnes INNER JOIN Bibliothèque ON Personnes.nom = Bibliothèque. emprunteur

Remarque La méthode INNER JOIN n’inclus les enregistrements de la

première table que s’ils ont une correspondance dans la seconde table.

Nom Prénom

Martin Jean

Tartan Pion

Dupond Jacques

Personnes

Emprunteur Nblivres

Martine 5

Tartan 10

Dupond 3

Bibliothèque

Nom Nblivres

Tartan 10

Dupond 3

Résultat de la jointure

Page 33: SQL Structured Query Language

Thomas Devogele ISI 204 33

Equi‑jointure (6) Exercice

obtenir la liste des employés avec la localité dans laquelle ils travaillent

synonyme 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

Select EMPNO, DEPTNO from EMP e // e est l’alias de EMPwhere e.DEPTNO = 20;

Page 34: SQL Structured Query Language

Thomas Devogele ISI 204 34

La jointure externe (1)

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.

Exempleselect ENAME, LOC from EMP, DEPT where EMP.DEPTNO(+) = DEPT.DEPTNO;

Exercice sélectionner les départements n'ayant pas d'employés

Page 35: SQL Structured Query Language

Thomas Devogele ISI 204 35

La jointure externe (2) Le (+) n’existe pas en MySQL 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 : SELECT Personnes.nom, nblivres

FROM Personnes LEFT JOIN BibliothèqueON Personnes.nom = Bibliothèque.nom

RIGHT JOIN est l’opération symétrique

Nom Prénom

Martin Jean

Tartan Pion

Dupond Jacques

Personnes

Nom Nblivres

Martine 5

Tartan 10

Dupond 3

Bibliothèque Résultat de la jointure

Nom Nblivres

Martin NULL

Tartan 10

Dupond 3

Page 36: SQL Structured Query Language

Thomas Devogele ISI 204 36

Jointure d'une table à elle‑même

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. Utilisation des synonymes obligatoire

Page 37: SQL Structured Query Language

Thomas Devogele ISI 204 37

Jointure d'une table à elle‑même (2)

Exemple Lister les employés en indiquant pour chacun le

nom de son manager Syntaxe

Select e.ENAME, m.ENAMEfrom EMP e, EMP mwhere e.MGR = m.EMPNO(+);

Exercice Sélectionner les employés gagnant plus que

JONES

Page 38: SQL Structured Query Language

Modification des données

Création, modification, suppression de données

Page 39: SQL Structured Query Language

Thomas Devogele ISI 204 39

Modification des données

Le LMD permet également de modifier les informations contenues dans la base.

II existe trois commandes pour effectuer des modifications UPDATE: modifie les valeurs d'une table INSERT : ajoute des n-uplets à une table DELETE : supprime des n-uplets à une table

Page 40: SQL Structured Query Language

Thomas Devogele ISI 204 40

Mise à jour ‑ UPDATE (1)

La commande UPDATE permet de modifier les valeurs d'un ou plusieurs champs

syntaxe update table

set col1 = nv_valeur, col2 = nv_valeur2 .... [where prédicat]

Les valeurs col1, col2 ... 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.

Page 41: SQL Structured Query Language

Thomas Devogele ISI 204 41

Mise à jour ‑ UPDATE (2)

On peut faire référence aux anciennes valeurs des champs du n-uplets.

Exercice le salaire des analystes est augmenté de 10

Page 42: SQL Structured Query Language

Thomas Devogele ISI 204 42

Ajout de n-uplets ‑ INSERT (1)

La commande INSERT permet d'insérer un n-uplet dans une table en spécifiant les valeurs à insérer.

Syntaxe insert into table (col1, col2 ...)

values (valeur, valeur ....) La liste des noms de colonne est optionnelle.

Si elle est omise, 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,

les colonnes ne figurant pas dans la liste auront la valeur NULL.

Page 43: SQL Structured Query Language

Thomas Devogele ISI 204 43

Ajout de n-uplets ‑ INSERT (2)

exemple, ajouter un nouvel employé 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. Syntaxe

insert into table (col1, col2 ...) select ....

Page 44: SQL Structured Query Language

Thomas Devogele ISI 204 44

Suppression de n-uplets

La commande DELETE permet de supprimer les n-uplets d'une table Syntaxe

delete from table [where prédicat];

En l'absence de la clause WHERE tous les n-uplets de la table sont supprimés.

Exemple : supprimer tous les employés du département 20 delete from EMP

where DEPTNO = 20 ;

Page 45: SQL Structured Query Language

Langage de définition de données

Création, modification, suppression de tables

Page 46: SQL Structured Query Language

Thomas Devogele ISI 204 46

Langage de définition de données (LDD)

Le LDD permet de créer, de modifier et de supprimer la description d'une table.

II existe 4 commandes pour effectuer des modifications CREATE TABLE: crée une table ALTER TABLE : modifie la structure d'une table DROP TABLE : supprime une table RENAME : renomme la table

Page 47: SQL Structured Query Language

Thomas Devogele ISI 204 47

création d'une table (1)

A la création, la table sera vide. Cependant, un certain espace lui sera alloué. Syntaxe

create table nom_table (col1 type1 [NOT NULL], col2 type2 [NOT NULL], ...)

L'option NOT NULL 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.

Les principaux types des éléments sont : Char (n)

n caractères maximum (n <= 255) Number (d)

nombre à d chiffres maximum (38 chiffres significatifs) Number (d,p)

nombre à d chiffres maximum dont p après le point décimal Date

élément de type DATE.

Page 48: SQL Structured Query Language

Thomas Devogele ISI 204 48

création d'une table (2)

Exemple Création de la table EMP on écrira

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 Créer une table BONUS, en y insérant les noms et les

salaires des MANAGERS.

Page 49: SQL Structured Query Language

Thomas Devogele ISI 204 49

Modification d'une table (1)

II est possible de modifier la définition d'une table, soit ajouter une colonne. soit modifier une colonne existante. II n'est pas possible de supprimer une colonne

Ajout d'une colonne : ADD Syntaxe

alter table nom_tableadd col1 type1, col2 type2, ....)

Les valeurs de cette colonne seront alors toutes égales à NULL.

Page 50: SQL Structured Query Language

Thomas Devogele ISI 204 50

Modification d'une table (2)

Modification d'une colonne ‑ MODIFY 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,

il est possible de spécifies NOT NUL si la colonne né contient aucune valeur NULL...

Syntaxe alter table nom_table

modify (col1 nv_type1, col2 nv_type2, ....)

Page 51: SQL Structured Query Language

Thomas Devogele ISI 204 51

Suppression d'une table

II est possible de supprimer une table les n-uplets et la définition de la table sont

détruites. Syntaxe

drop table nom_table

Page 52: SQL Structured Query Language

Thomas Devogele ISI 204 52

Renommer une table

II est possible de renommer une table Syntaxe

rename table ancien_nom_table to nouveau_nom_table