chap 3 : structered query language

56
Ramzi Trabelsi 1 Chapitre III : Le langage de commande SQL 2014-2015

Upload: ramzi-trabelsi

Post on 07-Apr-2017

199 views

Category:

Technology


1 download

TRANSCRIPT

Page 1: Chap 3 : structered query language

Ramzi Trabelsi

1

Chapitre III : Le langage de commande SQL

2014-2015

Page 2: Chap 3 : structered query language

2014-20152

Pour faire des interrogations sur les données de ma base :• QBE (Query By Exemple)• SQL (Structured Query Langage),• ...

SQL : standard de fait (norme ANSI) il permet : - la création, interrogation et manipulation des données d'une base - déclarer les relations, créer les occurrences - faire toute opération définie par l'algèbre relationnelle.

SELECT liste des colonnesFROM nom des tablesWHERE conditions

Le Langage SQL

Page 3: Chap 3 : structered query language

2014-20153

Langage de Définition de Données CREATE, ALTER, DROP, RENAME, TRUNCATE

Langage de Manipulation de Données INSERT, UPDATE, DELETE

Langage d'Interrogation des Données SELECT

Langage de Contrôle des Données GRANT, DENY, REVOKE

Le Langage SQL

Quatre grandes familles de commandes

Page 4: Chap 3 : structered query language

2014-20154

LE LANGAGE SQL : Définition des données

1 – Création de table: CREATE TABLE

2 – Modification de la table : ALTER TABLE

Page 5: Chap 3 : structered query language

Modélisation à plusieurs niveaux

5

Réel

Modèle conceptuel

Indépendant du modèle de données

Indépendant du SGBD

Modèle logique

Dépendant du modèle de données

Indépendant du SGBD

Codasyl Relationnel Objet XML

Modèle Physique

Dépendant du modèle de données

Dépendant du SGBD

Oracle MySQL MS.ACESS SQL SERVER

Médecin effectue Visite

Page 6: Chap 3 : structered query language

2014-20156

EpreuvesCodeEpNomEpCoefEp

EtudiantsCodeEtNomEtPrenomEtVilleEt…

NotesCodeNtNoteNtAvoir

Note

appartenir

1,11,N

1,1

0,NMCD

Page 7: Chap 3 : structered query language

2014-20157

EpreuvesCodeEpNomEpCoefEp

EtudiantsCodeEtNomEtPrenomEtVilleEt…

NotesCodeNtNoteNt#CodeEp#CodeEt

1,1

0,NMLD

Page 8: Chap 3 : structered query language

Etude de cas

2014-20158

MPD

Page 9: Chap 3 : structered query language

Création de tableCREATE TABLE nom_de_la_table (     colonne1 type_donnees [Contrainte],     colonne2 type_donnees,     colonne3 type_donnees,     colonne4 type_donnees )

Type_données : VARCHAR(taille) INTEGER NUMERIC (ou DECIMAL ou DEC)  DATE

Contrainte : NOT NULL , PRIMARY KEY, FORIGN KEY, UNIQUE

2014-20159

Page 10: Chap 3 : structered query language

Création de tablesCREATE TABLE Etudiants(CodeEt VARCHAR(50) PRIMARY KEY,NomEt VARCHAR(50) NOT NULL,PrenomEt VARCHAR(50) NOT NULL,VilleEt VARCHAR(50) );

2014-201510

Page 11: Chap 3 : structered query language

Création de tables CREATE TABLE Epreuves ( CodeEp VARCHAR(10), NomEp VARCHAR(50) UNIQUE, CoefEp NUMERIC NOT NULL);

ALTER TABLE Epreuves ADD CONSTRAINT PK_CodeEp PRIMARY KEY(CodeEp) ;

2014-201511

Page 12: Chap 3 : structered query language

Création de tables CREATE TABLE Etudiants( CodeEt VARCHAR(10) PRIMARY KEY, NomEp VARCHAR(50) NOT NULL, NomEt VARCHAR(50) NOT NULL, VilleEt VARCHAR(50));

2014-201512

Page 13: Chap 3 : structered query language

Création de tables CREATE TABLE Etudiants( CodeEt VARCHAR(10), NomEp VARCHAR(50) NOT NULL, NomEt VARCHAR(50) NOT NULL, VilleEt VARCHAR(50));

ALTER TABLE Etudiants ADD CONSTRAINT PK_CodeEt PRIMARY KEY(CodeEp) ;

2014-201513

Page 14: Chap 3 : structered query language

Création de tables CREATE TABLE Notes( CodeNt VARCHAR(10) PRIMARY KEY NoteNt NUMERIC, CodeEp VARCHAR(10) FOREIGN KEY

REFERENCES Epreuves(CodeEp), CodeEt VARCHAR(10) FOREIGN KEY

REFERENCES Etudiants(CodeEt));

2014-201514

Page 15: Chap 3 : structered query language

Création de tables CREATE TABLE Notes( CodeNt VARCHAR(10) PRIMARY KEY NoteNt NUMERIC, CodeEp VARCHAR(10) , CodeEt VARCHAR(10), Etudiants(CodeEt));ALTER TABLE Etudiants ADD CONSTRAINT

FK_CodeEp CodeEp FOREIGN KEY REFERENCES Epreuves(CodeEp);

ALTER TABLE Etudiants ADD CONSTRAINT FK_CodeEt CodeEp FOREIGN KEY REFERENCES Etudiants(CodeEt);

2014-201515

Page 16: Chap 3 : structered query language

Création de tables CREATE TABLE Notes( CodeNt VARCHAR(10) PRIMARY KEY NoteNt NUMERIC, CodeEp VARCHAR(10) , CodeEt VARCHAR(10), Etudiants(CodeEt));ALTER TABLE Etudiants ADD CONSTRAINT PK_CodeNt

PRIMARY KEY(CodeNt);ALTER TABLE Etudiants ADD CONSTRAINT FK_CodeEp

CodeEp FOREIGN KEY REFERENCES Epreuves(CodeEp);ALTER TABLE Etudiants ADD CONSTRAINT FK_CodeEt

CodeEp FOREIGN KEY REFERENCES Etudiants(CodeEt);

2014-201516

Page 17: Chap 3 : structered query language

2014-201517

LE LANGAGE SQL : modification des données

1 – Insertion dans la base : Insert

2 – Suppression dans la base : Delete

3 – Modification dans la base : Update

Page 18: Chap 3 : structered query language

2014-201518

INSERT INTO Agent (IdAgent, Anom, Aprenom) VALUES (25236, 'Dalton', 'Joe');

• Pour insérer une ligne dans une table on utilise la commande SQL INSERT

Le Langage SQL : Mise-à-jour des données

DELETE FROM Agent WHERE IdAgent = 25236 ;

• Pour supprimer des données dans une table on utilise la commande SQL DELETE

UPDATE Agent SET ANom = ‘DURANT’ WHERE Salaire = 2500 ;

• Pour modifier des données dans une table on utilise la commande SQL UPDATE

Page 19: Chap 3 : structered query language

2014-201519

LE LANGAGE SQL : Interrogation des données

1 – Interrogation de la base : LID

2 – Les jointures

3 – Les sous-interrogations et fonctions de groupe

Page 20: Chap 3 : structered query language

2014-201520

SELECT Sélection des colonnes à interroger.

FROM Choix des tables à partir desquelles on fait la sélection.

WHERE Sélection des lignes (application d'un filtre).

Le Langage SQL : Interrogation des données

Page 21: Chap 3 : structered query language

2014-201521

Sélectionner des colonnes spécifiques

• Choix des colonnes par la clause SELECT.

• Afficher toutes les colonnes d'une table.

SELECT * FROM Animaux ;

SELECT ID, Espèce, Nom FROM Animaux;

ID Espèce Nom -- ------ --- 1 Chien Bidou 2 Chat Groucha 3 Yeti Georges 4 Elephant Dumbo 5 Girafe Flip Flap

Page 22: Chap 3 : structered query language

2014-201522

AgentIdAgent

...

FournisseurIdFourS

...

FournitureIdFourT

...

CollectiviteIdCollect

...

ServiceIdService

...Commander

IdComDépendre Rattacher

1,1

1,n1,1

1,n 0,n1,n

0,n

Autre exemple de BD

Page 23: Chap 3 : structered query language

2014-201523

FournisseurIdFourS FNom FAdresse FType

CommandeIdCom Agent_Id FourT_Id FourS_Id DateC DateL Prix Quantite

Service IdService SNom Collect_Id

Agents

IdAgent ANom APrenom DateE Fonction Responsable Salaire

Service_Id

Collectivité IdCollect CDesignation

Fourniture IdFourT FDesignation Quantite_Stock

Commission

Autre exemple de BD

Page 24: Chap 3 : structered query language

2014-201524

Expressions Arithmétiques

• Créer des expressions sur des données de type NUMBER ou DATE avec : + - * /

SELECT ANom, Salaire*12FROM Agent ; Remarque : la colonne salaire*12

existe uniquement à l’affichage.

SELECT ANom, Salaire*12 AS ‘Salaire Annuel’ FROM Agent ;

• Alias sur colonne : AS

• La clause ORDER BY permet de trier les lignes : ASC DESC

SELECT ANom, Fonction, SalaireFROM AgentORDER BY Salaire DESC;

Page 25: Chap 3 : structered query language

2014-201525

Il est possible de limiter le nombre de lignes rapportées en utilisant la clause WHERE :

• permet d'exprimer une condition.

SELECT ANom, Fonction, SalaireFROM AgentWHERE Service_Id = 10;

Sélection des Lignes

SELECT ANom, Fonction, SalaireFROM AgentWHERE ANom = 'Dupont'

AND APrenom LIKE '_ea%';

chaîne de caractères• permet de faire des comparaisons

Opérateurs de comparaison logiques . = > >= < <= != <> Opérateurs de comparaison . BETWEEN, AND, IN, LIKE, IS NULL Opérateurs logiques . AND, OR, NOT

Page 26: Chap 3 : structered query language

2014-201526

SELECT ANom, APrenom, Fonction, SalaireFROM AgentWHERE DateE BETWEEN #08-AOU-93#

AND #08- AOU-99#;

Opérateurs de comparaison

• On utilise l'opérateur BETWEEN pour tester l'appartenance à un intervalle.

SELECT IdService, SNom, Collect_IdFROM ServiceWHERE Collect_Id IN (38,42,69,01);

• On utilise l'opérateur IN pour tester l'appartenance à une liste de valeurs.

format des dates par défaut

Page 27: Chap 3 : structered query language

2014-201527

SELECT UPPER(ANom), CONCAT(ANom, APrenom), SalaireFROM AgentWHERE LOWER(Fonction) = ‘directeur ’;

Fonctions Mono-Ligne

• LOWER convertit en minuscule• UPPER convertit en majuscule• INITCAP convertit l’initiale en capitale• CONCAT concatène des valeurs• SUBSTR retourne une sous-chaîne• LENGTH retourne un nombre de caractères

• ROUND(col,n) arrondit une valeur à n décimales spécif.• TRUNC (col,n) tronque une valeur à n décimales spécif.• MOD(m,n) retourne le reste de la division de m par n

Page 28: Chap 3 : structered query language

2015-201628

SELECT *FROM Notes, Etudiants

Les jointures

WHERE CodeEt=CodeEtWHERE Notes.CodeEt=Etudiants.CodeEt

FROM Notes N, Etudiants EWHERE N.CodeEt=E.CodeEt

SELECT N.CodeNt, N.NoteNt, E.NomEt, E.PrenomEtFROM Notes N, Etudiants EWHERE N.CodeEt=E.CodeEt ANDN.CodeNt=‘N0045’

Page 29: Chap 3 : structered query language

2014-201529

• Une jointure est utilisée pour rechercher les données dans plusieurs tables.• Les lignes sont reliées grâce aux valeurs communes contenues dans les clés primaires et étrangères.• La condition de jointure s’exprime dans le WHERE.

Les jointures

SELECT Ag.ANom , Ag.Service_Id , Se.IdServiceFROM Agent Ag, Service SeWHERE Ag.Service_Id = Se.IdService ;

Page 30: Chap 3 : structered query language

2014-201530

Les Sous-Interrogations

• Il est possible d'imbriquer une, ou plusieurs, interrogation (SELECT) dans une autre.

• On parle alors de sous-interrogation.

• Elle est exécutée avant la requête principale ; son résultat est utilisé par la requête principale.

• Elle permet de rechercher des données basées sur un critère inconnu.

SELECT ANom, FonctionFROM AgentWHERE Service_Id = ( SELECT Service_Id

FROM Agent WHERE ANom = 'Dupont' ) ;

Page 31: Chap 3 : structered query language

2014-201531

SELECT ANom, Salaire, Service_IdFROM AgentWHERE Salaire = ( SELECT MIN (Salaire)

FROM Agent );

•MIN, MAX,AVG,SUM,COUNT •Si une sous-interrogation retourne plus d'une ligne

SELECT ANom, Salaire, Service_IdFROM AgentWHERE Salaire = ( SELECT MIN (Salaire)

FROM Agent GROUP BY Service_Id );

Il faut remplacer = par IN (opérateur multi-lignes).

Réponses Multi-Lignes (1/2)

Page 32: Chap 3 : structered query language

2014-201532

Réponses Multi-Lignes (2/2)

• Autre exemple

SELECT Service_Id, AVG(Salaire)FROM AgentGROUP BY Service_Id HAVING AVG(Salaire) > ( SELECT AVG(Salaire)

FROM Agent WHERE Service_Id = 10 );

Salaire moyen par service, dont le salaire moyen est supérieur à celui du service 10.

Page 33: Chap 3 : structered query language

Syntaxe SelectSELECT codeEp,AVG(NoteNt)FROM Table1, Table2WHERE JoitureAND Autres_fGroup By (codeEp)HAVING AVG(NoteNt) >12ORDER BY AVG(NoteNt) DESC

2014-201533

Page 34: Chap 3 : structered query language

Groupement

2014-201534

SELECT Categorie,SUM(Qte) as S_Qte FROM ProduitWHERE Couleur=‘Rouge’GROUP BY CategorieHAVING SUM(Qte)>100ORDER BY SUM(Qte) DESC

Page 35: Chap 3 : structered query language

Le nom , prénom et note DS de tous les étudiants

2014-201535

1

SELECT NomEt , PrenomEt, NoteNt as Notes_DSFROM Etudiants E , Notes NWHERE E.CodeEt = N.CodeEtAND CodeEp=‘DS’;

Page 36: Chap 3 : structered query language

Le nom , prénom et note DS de tous les étudiants ayant une note entre 8 et 12

2014-201536

2

SELECT NomEt , PrenomEt, NoteNt as Notes_DSFROM Etudiants E , Notes NWHERE E.CodeEt = N.CodeEtAND CodeEp=‘DS’AND NoteNt BETWEEN 8 AND 12;

Page 37: Chap 3 : structered query language

Le nombre des étudiants ayant une note entre 8 et 12 en DS

2014-201537

3

SELECT Count(*)FROM Etudiants E , Notes NWHERE E.CodeEt = N.CodeEtAND CodeEp=‘DS’AND NoteNt BETWEEN 8 AND 12;

Page 38: Chap 3 : structered query language

Max et Min des notes de DS

2014-201538

4

SELECT Max(NoteNt),Min(NoteNt)FROM Notes

WHERE CodeEp=‘DS’

Page 39: Chap 3 : structered query language

Le nom , prénom et note DS de tous les étudiants ayant une note supérieure à la moyenne des DS

2014-201539

5

SELECT NomEt , PrenomEt, NoteNt as Notes_DSFROM Etudiants E , Notes NWHERE E.CodeEt = N.CodeEtAND CodeEp='DS'AND NoteNt > SELECT AVG(NoteNT)

FROM NotesWHERE CodeEp= 'DS'

Calcul de la moyenne des notes DS

Page 40: Chap 3 : structered query language

Le nom , prénom et Examen triés de la note la plus petite à la plus grande

2014-201540

6

SELECT NomEt , PrenomEt, NoteNt as Notes_ExamenFROM Etudiants E , Notes NWHERE E.CodeEt = N.CodeEtAND CodeEp='EX‘AND NoteNt IS NOT NULLORDER BY NoteNt ASC ;

Page 41: Chap 3 : structered query language

Le nom , prénom des étudiants n’ayant pas passé l’examen

2014-201541

7

SELECT NomEt , PrenomEt FROM Etudiants E , Notes NWHERE E.CodeEt = N.CodeEtAND CodeEp='EX'AND NoteNt IS NULL

Page 42: Chap 3 : structered query language

Le maximum en DS , EX et NP

2014-201542

8

SELECT CodeEp,NomEp, Max(NoteNt) AS max_NoteFROM Notes GROUP BY CodeEp

Page 43: Chap 3 : structered query language

Le maximum en Devoir Surveillé , Examen et Présentiel

2014-201543

9

SELECT NomEp, Max(NoteNt) AS max_NoteFROM Notes N, Epreuves EWHERE N.CodeEP= E.CodeEpGROUP BY NomEp

Page 44: Chap 3 : structered query language

Le nom et Prénom de l’étudiant ayant la note de DS la plus élevée SELECT NomEt, PrenomET , NoteNtFROM Etudiants E, Notes NWHERE E.CodeEt = N.CodeEtAND CodeEp='DS‘ AND NoteNt =

2014-201544

10

SELECT MAX(NoteNT)FROM NotesWHERE CodeEp= 'DS'

Trouver le max note DS

Page 45: Chap 3 : structered query language

Le nom , prénom et moyenne générale de la matière

2014-201545

11

SELECT NomEt, PrenomEt, SUM(NoteNt*CoefEp )FROM Epreuves Ep, Notes N , Etudiants EtWHERE Et.CodeEt = N.CodeETAND N.CodeEp=Ep.CodeEpGROUP BY NomEt , PrenomEt

Page 46: Chap 3 : structered query language

Le nom , prénom et moyenne générale triés par Moy Générale

2014-201546

12

SELECT NomEt, PrenomEt, SUM(NoteNt*CoefEp )FROM Epreuves Ep, Notes N , Etudiants EtWHERE Et.CodeEt = N.CodeETAND N.CodeEp=Ep.CodeEpGROUP BY NomEt , PrenomEtORDER BY SUM(NoteNt*CoefEp ) DESC

Page 47: Chap 3 : structered query language

Le nom , prénom et moyenne générale triés (seule moy>=10)

*HAVING n’est utilisée qu’avec GROUP BY quand il s’agit de filtrer selon l’une des fonctions de groupement

2014-201547

13

SELECT NomEt, PrenomEt, SUM(NoteNt*CoefEp )FROM Epreuves Ep, Notes N , Etudiants EtWHERE Et.CodeEt = N.CodeETAND N.CodeEp=Ep.CodeEpGROUP BY NomEt , PrenomEtHAVING SUM(NoteNt*CoefEp ) >=10ORDER BY SUM(NoteNt*CoefEp ) DESC

Page 48: Chap 3 : structered query language

Le nom et prénom de la fille ayant la plus grande note de NP

2014-201548

14

SELECT NomEt, PrenomET , NoteNtFROM Etudiants E, Notes NWHERE E.CodeEt = N.CodeEtAND CodeEp=‘NP‘ AND SexeEt='F'AND NoteNt = SELECT MAX(NoteNT)

FROM Notes N, Etudiants EWhere N.CodeEt=E.CodeEtAND SexeEt= 'F'

Trouver La note TP la plus élevée obtenue par une fille

Page 49: Chap 3 : structered query language

Le maximum et minimum de note par Ville

2014-201549

15

SELECT VilleEt, MAX(NoteNt) , MIN(NoteNt)FROM Etudiants E, Notes NWHERE E.CodeEt = N.CodeEtGROUP BY (VilleEt)

Page 50: Chap 3 : structered query language

La note d’examen maximale par ville et par sexe

2014-201550

16

SELECT VilleEt, MAX(NoteNt)FROM Etudiants E, Notes NWHERE E.CodeEt = N.CodeEtAND CodeEp = 'EX'GROUP BY VilleEtol , SexeEt;

Page 51: Chap 3 : structered query language

La note d’examen maximale par ville et par sexe dépassant 15

2014-201551

17

SELECT VilleEt, MAX(NoteNt)FROM Etudiants E, Notes NWHERE E.CodeEt = N.CodeEtAND CodeEp = 'EX'GROUP BY VilleEt , SexeEtHAVING MAX(NoteNt)>15 ;

Page 52: Chap 3 : structered query language

2014-201552

Fournisseur

CodeFNomFVilleF

BoutiqueCodeBNomBVilleB

PieceCodePNomPCouleurPPoidsPNbrP

Fournit

Qte

VendQte

1,N0,N

1,N

1,N

Page 53: Chap 3 : structered query language

2014-201553

BoutiqueCodeBNomBVilleB

PieceCodePNomPcCouleurPPoidsP

Note#CodeP#CodeBQteVend

Page 54: Chap 3 : structered query language

2014-201554

Fournisseur

CodeFNomFVilleF

BoutiqueCodeBNomBVilleB

PieceCodePNomPCouleurPPoidsP

Fournit

Qte

0,N 1,N

1,N

Page 55: Chap 3 : structered query language

2014-201555

Fournisseur

CodeFrNomFrVilleFr

BoutiqueCodeBNomBVilleB

PieceCodePNomPCouleurPPoidsP

Fournit#CodeP#CodeF#CodeBQteFrn

Page 56: Chap 3 : structered query language

2014-201556

CodeF

NomF

VilleF

F1 P.M.S

Tunis

F2 S.T.M

Tunis

F3 F.R.P

Sousse

F4 M.T SfaxF5 N.X Tuni

sF6 K.L Sous

se

CodeP

NomP CouleurP

PoidsP

Pc1 Papier

Rouge 500

Pc2 Plateau

Vert 30

Pc3 Rideau

Vert 450

Pc3 Cadre Orange 250Pc4 Verre Rouge 20Pc5 Assiet

teOrange 40

CodeB

NomB

VilleB

B1 Promi TunisB2 Fonis TunisB3 Breco SousseB4 Bord NabeulB5 Halu BizerteB6 Bonp

roSfax

CodeF

CodeP

CodeB

QteFrn

F1 Pc1 B1 200F1 Pc2 B2 150F2 Pc3 B1 600F4 Pc1 B2 100F1 Pc4 B1 300F3 Pc6 B3 250