introduction aux introduction aux sql structured query...

8
Introduction aux Introduction aux Bases de Données Relationnelles Bases de Données Relationnelles Introduction Modèle relationnel Conception d'une BdD Langage SQL 4. Langage SQL 4. Langage SQL Introduction aux Bases de Données Relationnelles 3/41 Généralités (1) Généralités (1) SQL : S tructured Q uery L anguage Développé par IBM en 1970 Présent dans de nombreux SGBD Peut-être intégré dans des langages de programmation exemple : Php (utilisation de BdD via des sites Web) Langage qui permet : Créer une BdD : Requêtes de définition définition des tables Modifier l'instance d'une BdD : Requêtes de mise à jour ajout/suppression/modifications d'enregistrements Interroger une BdD : Requêtes d'interrogation 4. Langage SQL 4. Langage SQL Introduction aux Bases de Données Relationnelles 4/41 Généralités (2) Généralités (2) Données manipulées par SQL : Types à spécifier pour chaque champ : INT, FLOAT C, CHAR(n), VARCHAR(n), TEXT DATE, TIME Possibilité d'appliquer des opérations suivant le type : Numérique : +, -, *, /, … Chaînes : length, concat, … 4. Langage SQL 4. Langage SQL Introduction aux Bases de Données Relationnelles 5/41 Requêtes de définition (1) Requêtes de définition (1) Création d'une table : Spécifier tout ce qui constitue la table Les champs ( type), les clés ( primaires, étrangères ), … CREATE TABLE nomTable ( Champ1 format, Champ2 format, ) format = type [PRIMARY KEY [AUTO_INCREMENT] ] [NOT NULL] [DEFAULT = valeur] [FOREIGN KEY (cléEtr) REFERENCES table(clePrim) ]

Upload: nguyendan

Post on 10-Sep-2018

216 views

Category:

Documents


0 download

TRANSCRIPT

Page 1: Introduction aux Introduction aux SQL Structured Query ...iutsa.unice.fr/~escazut/ext/M2104/Cours/I-BdD_Support4.pdf · 4. Langage SQL – Introduction aux Bases de Données Relationnelles

Introduction aux Introduction aux Bases de Données RelationnellesBases de Données Relationnelles

IntroductionModèle relationnel

Conception d'une BdDLangage SQL

4. Langage SQL4. Langage SQL – –

Introduction aux Bases de Données Relationnelles 3/41

Généralités (1)Généralités (1)

● SQL : Structured Query Language – Développé par IBM en 1970– Présent dans de nombreux SGBD– Peut-être intégré dans des langages de programmation

exemple : Php (utilisation de BdD via des sites Web)– Langage qui permet :

• Créer une BdD : Requêtes de définitiondéfinition des tables

• Modifier l'instance d'une BdD : Requêtes de mise à jour ajout/suppression/modifications d'enregistrements

• Interroger une BdD : Requêtes d'interrogation

4. Langage SQL4. Langage SQL – –

Introduction aux Bases de Données Relationnelles 4/41

Généralités (2)Généralités (2)

● Données manipulées par SQL :– Types à spécifier pour chaque champ :

• INT, FLOAT• C, CHAR(n), VARCHAR(n), TEXT• DATE, TIME• …

– Possibilité d'appliquer des opérations suivant le type :• Numérique : +, -, *, /, … • Chaînes : length, concat, …• …

4. Langage SQL4. Langage SQL – –

Introduction aux Bases de Données Relationnelles 5/41

Requêtes de définition (1)Requêtes de définition (1)

● Création d'une table : Spécifier tout ce qui constitue la table Les champs ( type), les clés ( primaires, étrangères ), …

CREATE TABLE nomTable (Champ1 format,Champ2 format,…

)

format = type [PRIMARY KEY [AUTO_INCREMENT] ][NOT NULL][DEFAULT = valeur][FOREIGN KEY (cléEtr) REFERENCES table(clePrim) ]…

Page 2: Introduction aux Introduction aux SQL Structured Query ...iutsa.unice.fr/~escazut/ext/M2104/Cours/I-BdD_Support4.pdf · 4. Langage SQL – Introduction aux Bases de Données Relationnelles

4. Langage SQL4. Langage SQL – –

Introduction aux Bases de Données Relationnelles 6/41

Requêtes de définition (2)Requêtes de définition (2)

– Exemple : Base de Données AirBaseCréation des tables : PILOTES et AVIONS

CREATE TABLE PILOTES ( pl INT NOT NULL PRIMARY KEY AUTO INCREMENT,

nom VARCHAR(30),adr VARCHAR(50),salaire FLOAT

)

CREATE TABLE AVIONS ( av INT NOT NULL PRIMARY KEY AUTO INCREMENT,

type VARCHAR(10),loc VARCHAR(30) DEFAULT "Edinburgh",cap INT

)

4. Langage SQL4. Langage SQL – –

Introduction aux Bases de Données Relationnelles 7/41

Requêtes de définition (3)Requêtes de définition (3)

– Exemple : Base de Données AirBaseCréation de la table VOLS

CREATE TABLE VOLS ( vol CHAR(5) NOT NULL PRIMARY KEY,

#pl INT,#av INT,depart VARCHAR(20),arrivee VARCHAR(20),heure TIME,durée INT,quand DATE,FOREIGN KEY (#pl) REFERENCES PILOTES (pl),

FOREIGN KEY (#av) REFERENCES AVIONS (av) )

4. Langage SQL4. Langage SQL – –

Introduction aux Bases de Données Relationnelles 8/41

Requêtes de définition (4)Requêtes de définition (4)

● Modification du schéma d'une table :– Ajouter un champ

– Modifier le type d'un champ

– Supprimer un champ

● Suppression d'une table :

ALTER TABLE nomTable ADD Champ format

ALTER TABLE nomTable CHANGE Champ format

ALTER TABLE nomTable DROP Champ format

DROP TABLE nomTable

4. Langage SQL4. Langage SQL – –

Introduction aux Bases de Données Relationnelles 9/41

Requêtes de mise à jour (1)Requêtes de mise à jour (1)

● Insertion d'un nouvel enregistrement :– Avec des valeurs pour tous les champs

– Avec des valeurs pour certains champs

INSERT INTO nomTable VALUES ( Val1, Val2, … )

INSERT INTO nomTable ( Champs1, Champs2, … ) VALUES ( Val1, Val2, … )

Page 3: Introduction aux Introduction aux SQL Structured Query ...iutsa.unice.fr/~escazut/ext/M2104/Cours/I-BdD_Support4.pdf · 4. Langage SQL – Introduction aux Bases de Données Relationnelles

4. Langage SQL4. Langage SQL – –

Introduction aux Bases de Données Relationnelles 10/41

Requêtes de mise à jour (2)Requêtes de mise à jour (2)

● Modification d'enregistrements :– Modifier la valeur d'un champ pour tous les enregistrements

– Modifier la valeur d'un champ pour certains enregistrements

UPDATE nomTable SET Champ = valeur

UPDATE nomTable SET Champ = valeur

WHERE Condition

4. Langage SQL4. Langage SQL – –

Introduction aux Bases de Données Relationnelles 11/41

Tous les pilotes sont augmentés de 100€

Le vol AF240 sera effectué par le pilote n°12

4. Langage SQL4. Langage SQL – –

Introduction aux Bases de Données Relationnelles 13/41

Requêtes de mise à jour (4)Requêtes de mise à jour (4)

● Suppression d'enregistrements :– Suppression de tous les enregistrements

– Suppression de certains enregistrements

DELETE FROM nomTable

DELETE FROM nomTable WHERE Condition

4. Langage SQL4. Langage SQL – –

Introduction aux Bases de Données Relationnelles 14/41

Requêtes d'interrogation (1)Requêtes d'interrogation (1)

● Bloc de base :

SELECT correspond à une projection ( π )  : le résultat affichéFROM spécifie le nom des tables concernéesWHERE (facultatif) correspond à une condition :

– une sélection ( σ ) – une jointure ( )

Exemple

SELECT [ DISTINCT ] listeChamps FROM listeTables[ WHERE condition ]

!"

SELECT nom FROM PILOTESWHERE salaire > 5000

πnom ( σSalaire>5000 (PILOTES) )

Page 4: Introduction aux Introduction aux SQL Structured Query ...iutsa.unice.fr/~escazut/ext/M2104/Cours/I-BdD_Support4.pdf · 4. Langage SQL – Introduction aux Bases de Données Relationnelles

4. Langage SQL4. Langage SQL – –

Introduction aux Bases de Données Relationnelles 15/41

SELECT DISTINCT type FROM AVIONS

4. Langage SQL4. Langage SQL – –

Introduction aux Bases de Données Relationnelles 17/41

La requête affiche :a - tous les numéros de vols entre Nice et Edinburghb - toutes les informations sur les vols entre Nice et

Edinburghc - Rien : Il y a une énorme erreur !d - tous les numéros de vols au départ de Nice et arrivant

à Edinburgh

FROM VOLSWHERE depart = Nice AND arrivee = Edinburgh

4. Langage SQL4. Langage SQL – –

Introduction aux Bases de Données Relationnelles 19/41

FROM PILOTESWHERE nom LIKES "D_%"

4. Langage SQL4. Langage SQL – –

Introduction aux Bases de Données Relationnelles 21/41

Requêtes d'interrogation (5)Requêtes d'interrogation (5)

● Trier les résultats : – Ajouter la clause ORDER BY– Ordre croissant par défaut– Ordre décroissant : DESC– Possibilité d'avoir plusieurs clés de tri– Exemple :

SELECT nom, salaire FROM PILOTESWHERE salaire > 5000ORDER BY salaire DESC, nom

Page 5: Introduction aux Introduction aux SQL Structured Query ...iutsa.unice.fr/~escazut/ext/M2104/Cours/I-BdD_Support4.pdf · 4. Langage SQL – Introduction aux Bases de Données Relationnelles

4. Langage SQL4. Langage SQL – –

Introduction aux Bases de Données Relationnelles 22/41

Requêtes d'interrogation (6)Requêtes d'interrogation (6)

● Jointure sans clé étrangère : – Exprimer simplement la jointure dans la clause WHERE– Exemple :

SELECT nom, adr FROM AVIONS, PILOTESWHERE loc = adr

4. Langage SQL4. Langage SQL – –

Introduction aux Bases de Données Relationnelles 23/41

Quel est le nom des pilotes qui habitent dans laville où est localisé un A320 ?

4. Langage SQL4. Langage SQL – –

Introduction aux Bases de Données Relationnelles 25/41

Requêtes d'interrogation (8)Requêtes d'interrogation (8)

● Jointure avec clé étrangère : INNER JOIN … ONPour spécifier :• Une des tables de la jointure • La condition de jointure

– Exemple :

– Remarques : • Le nom des tables ne doit apparaître qu'une seule fois

(FROM ou INNER JOIN)• Possibilité d'avoir plusieurs INNER JOIN dans une requête

SELECT nom, adr FROM PILOTES

INNER JOIN VOLS ON pl = #pl

4. Langage SQL4. Langage SQL – –

Introduction aux Bases de Données Relationnelles 26/41

Quel est le nom des pilotes effectuant un vol audépart  de Nice ?

Page 6: Introduction aux Introduction aux SQL Structured Query ...iutsa.unice.fr/~escazut/ext/M2104/Cours/I-BdD_Support4.pdf · 4. Langage SQL – Introduction aux Bases de Données Relationnelles

4. Langage SQL4. Langage SQL – –

Introduction aux Bases de Données Relationnelles 28/41

Pour tous les vols de la compagnie, afficher lenom du pilote et le type d'avion

4. Langage SQL4. Langage SQL – –

Introduction aux Bases de Données Relationnelles 30/41

INNER JOIN PILOTES ON pl = #pl INNER JOIN AVIONS ON av = #av

WHERE type = "A320"

La requête affiche :a - le nom des pilotes qui volent sur un A320b - le nom des A320 en volc - Rien : Il y a ENCORE une erreur !d - les pilotes qui ont fêté leur anniversaire à bord d'un A320

4. Langage SQL4. Langage SQL – –

Introduction aux Bases de Données Relationnelles 32/41

Requêtes d'interrogation (12)Requêtes d'interrogation (12)

● Requêtes avec calcul statistique : À mettre dans la clause SELECT (c'est ce qui est affiché) :

• SUM ( Champ )• MAX ( Champ )• MIN ( Champ )• AVG ( Champ )• AVG ( DISTINCT Champ )• COUNT ( Champ )• COUNT ( DISTINCT Champ )

4. Langage SQL4. Langage SQL – –

Introduction aux Bases de Données Relationnelles 33/41

Affiche le nombre de vols audépart de Nice

Affiche le nombre dedestinations différentes

Page 7: Introduction aux Introduction aux SQL Structured Query ...iutsa.unice.fr/~escazut/ext/M2104/Cours/I-BdD_Support4.pdf · 4. Langage SQL – Introduction aux Bases de Données Relationnelles

4. Langage SQL4. Langage SQL – –

Introduction aux Bases de Données Relationnelles 35/41

Requêtes d'interrogation (14)Requêtes d'interrogation (14)

● Requêtes avec calcul statistique sur des groupes : Ajouter la clause GROUP BY champ– Étape 1 : les enregistrements sont rangés par groupe (en

fonction de champ)– Étape 2 : la requête est exécutée sur chaque groupe

Possibilité d'ajouter une condition pour ne garder que certainsgroupes : HAVING Condition

Remarque : les champs servant à faire les groupes doivent êtredans la clause SELECT (affichés dans le résultat)

4. Langage SQL4. Langage SQL – –

Introduction aux Bases de Données Relationnelles 36/41

Requêtes d'interrogation (15)Requêtes d'interrogation (15)

Exemples SELECT adr, AVG (salaire) FROM PILOTESGROUP BY adr Fait des groupes de pilotes en fonction

de l'adresse et calcule la moyenne dusalaire pour chaque groupe

SELECT adr, AVG (salaire) FROM PILOTESGROUP BY adr HAVING COUNT (adr)>5

Fait des groupes de pilotes en fonctionde l'adresse et calcule la moyenne dusalaire uniquement pour les groupescontenant plus de 5 pilotes

4. Langage SQL4. Langage SQL – –

Introduction aux Bases de Données Relationnelles 37/41

Requêtes d'interrogation (16)Requêtes d'interrogation (16)

● Requêtes imbriquées – La requête la plus imbriquée produit un résultat utilisé par la

requête englobante– Exemple :

– Remarque : Si la requête imbriquée retourne un ensemble devaleurs, l'opérateur entre les 2 requêtes doit être un opérateurensembliste  (IN, … )

SELECT nom FROM PILOTESWHERE salaire = SELECT MAX (salaire)

FROM PILOTES

4. Langage SQL4. Langage SQL – –

Introduction aux Bases de Données Relationnelles 38/41

WHERE cap > SELECT cap FROM AVIONS

INNER JOIN VOLS ON av = #avWHERE vols = "AF200"

La requête affiche :a - le type des avions ayant une capacité supérieure à la

capacité de l'avion ayant effectué le vol AF200b - le type des avions qui effectuent le vol AF200c - Rien : Il y a ENCORE une erreur … Décidément !d - les types qui pilotent l'avion AF200

Page 8: Introduction aux Introduction aux SQL Structured Query ...iutsa.unice.fr/~escazut/ext/M2104/Cours/I-BdD_Support4.pdf · 4. Langage SQL – Introduction aux Bases de Données Relationnelles

4. Langage SQL4. Langage SQL – –

Introduction aux Bases de Données Relationnelles 40/41

Quel est le nom des pilotes qui habitent dans uneville d'arrivée ?