26
Cours N°2 Base de Données & Langage SQL Sahbi SIDHOM, ICom Lyon 2 Promo. DESS IM 02/03

Cours N°2 Base de Données & Langage SQL Sahbi SIDHOM, ICom Lyon 2 Promo. DESS IM 02/03

Embed Size (px)

Citation preview

Page 1: Cours N°2 Base de Données & Langage SQL Sahbi SIDHOM, ICom Lyon 2 Promo. DESS IM 02/03

Cours N°2

Base de Données & Langage SQL

Sahbi SIDHOM, ICom Lyon 2

Promo. DESS IM 02/03

Page 2: Cours N°2 Base de Données & Langage SQL Sahbi SIDHOM, ICom Lyon 2 Promo. DESS IM 02/03

Plan du cours :

• 1.     Modèle Relationnel et SQL

• 2.     Introduction au modèle relationnel

• 3.     Opérations propres de l’algèbre relationnelle

• 4.     Syntaxe des opérations SQL et requêtes

Page 3: Cours N°2 Base de Données & Langage SQL Sahbi SIDHOM, ICom Lyon 2 Promo. DESS IM 02/03

IV. Syntaxe des opérations en SQL : requêtes

• Opérations de l’algèbre relationnelle– PROJECTION ( )

– RESTRICTION ou « SELECTION » ( )– JOINTURE ( )– DIVISION ( ÷ )

• Opérations ensemblistes– UNION ( )– INTERSECTION ( )– DIFFERENCE ( — )– PRODUIT CARTESIEN ( )

Page 4: Cours N°2 Base de Données & Langage SQL Sahbi SIDHOM, ICom Lyon 2 Promo. DESS IM 02/03

SQL (Structured Query Language)

• Le langage SQL n'est abordé que dans le cadre des opérations évoquées ci-dessus.

• Seule l'instruction SELECT et ses multiples aspects sont donc présentés.

• La rédaction de requêtes (combinaison d'opérateurs) est illustrée par des exercices concrets.

Page 5: Cours N°2 Base de Données & Langage SQL Sahbi SIDHOM, ICom Lyon 2 Promo. DESS IM 02/03

Instruction CREATE TABLE

Syntaxe :

CREATE [TEMPORARY] TABLE table (

champ1 type [(taille)]

[NOT NULL]

[WITH COMPRESSION | WITH COMP] [index1]

[, champ2 type [(taille)]

[NOT NULL] [index2] [, ...]]

[PRIMARY KEY]

[, CONSTRAINT indexmultichamp [, ...]])

Page 6: Cours N°2 Base de Données & Langage SQL Sahbi SIDHOM, ICom Lyon 2 Promo. DESS IM 02/03

CREATE TABLE se compose des éléments suivants :• table : Nom de la table à créer.• champ1, champ2 : Nom du ou des champs à créer dans la nouvelle table.

Vous devez créer au moins un champ.• type : Type de données du champ dans la nouvelle table.• taille : Taille du champ en caractères (pour les données de type Texte ou

Binaire uniquement).• index1, index2 : Une clause CONSTRAINT définissant un index

monochamp. Pour plus d'informations sur la façon de le créer.• indexmultichamp : Une clause CONSTRAINT définissant un index

multichamp. • Clause CONSTRAINT : Une contrainte est identique à un index mais

elle peut être également utilisée pour établir une relation avec une autre table.

• L'attribut WITH COMPRESSION a été ajouté pour les colonnes CHARACTER en raison de la modification apportée au format de représentation des caractères Unicode.

Page 7: Cours N°2 Base de Données & Langage SQL Sahbi SIDHOM, ICom Lyon 2 Promo. DESS IM 02/03

Exemples avec CREATE TABLE

• CREATE TABLE ThisTable(FirstName CHAR, LastName CHAR);• CREATE TABLE MyTable(

FirstName CHAR, LastName CHAR, [DateOfBirth] DATETIME, CONSTRAINT MyTableConstraint UNIQUE(FirstName,

LastName, [DateOfBirth]));

• CREATE TABLE NewTable(FirstName CHAR, LastName CHAR, SSN INTEGER CONSTRAINT MyFieldConstraint PRIMARY

KEY);

Page 8: Cours N°2 Base de Données & Langage SQL Sahbi SIDHOM, ICom Lyon 2 Promo. DESS IM 02/03

Syntaxe simplifiée de l'instruction SELECT

SELECT [ * | DISTINCT ] att1 [ , att2, att3, ... ]

      FROM Table1 [, Table2, Table3, ...]

      [ WHERE conditions de sélection et/ou de jointure ]

      [ GROUP BY att1 [, att2, ...] [HAVING conditions de sélection ]]

      [ ORDER BY att1 [ASC | DESC] [, att2 [ASC | DESC], ... ] ;

[ ] : optionnel/ * : tout/ | : ou/ , : et

Page 9: Cours N°2 Base de Données & Langage SQL Sahbi SIDHOM, ICom Lyon 2 Promo. DESS IM 02/03

Opération PROJECTION

• SELECT DISTINCT liste_d'attributs FROM table ;• SELECT liste_d'attributs FROM table ;

Remarque : La clause DISTINCT permet d'éliminer les doublons.

Exemples :                       SELECT DISTINCT Espèce FROM Champignons ;

      SELECT DISTINCT Espèce, Catégorie FROM Champignons ;

Page 10: Cours N°2 Base de Données & Langage SQL Sahbi SIDHOM, ICom Lyon 2 Promo. DESS IM 02/03

Exercices :

1. créer la table CHAMPIGNONS :

Espèce Catégorie Conditionnement Rosé des prés Conserve Bocal

Rosé des prés Sec Verrine

Coulemelle Frais Boîte

Rosé des prés Sec Sachet plastique

2. réaliser les deux opérations de projections R1 et R2 :

Espèce

Rosé des prés

Coulemelle

Espèce Catégorie

Rosés des prés Conserve

Rosé des prés Sec

Coulemelle Frais R1 = PROJECTION (CHAMPIGNONS, Espèce) R2 = PROJECTION (CHAMPIGNONS, Espèce, Catégorie)

Page 11: Cours N°2 Base de Données & Langage SQL Sahbi SIDHOM, ICom Lyon 2 Promo. DESS IM 02/03

Opération RESTRICTION

• SELECT * FROM table WHERE condition ;

Remarque :La condition de sélection exprimée derrière la clause WHERE peut être

spécifiée à l'aide : 1. des opérateurs de comparaison : =, >, <, <=, >=, <>2. des opérateurs logiques : AND, OR, NOT3. des opérateurs : IN, BETWEEN, LIKE

Exemple :• SELECT * FROM Champignons WHERE

Catégorie="Sec" ;

Page 12: Cours N°2 Base de Données & Langage SQL Sahbi SIDHOM, ICom Lyon 2 Promo. DESS IM 02/03

Autres exemples :Soit la table ETUDIANT(N°Etudiant, Nom, Age, CodePostal, Ville)

• SELECT *FROM ETUDIANTWHERE Age IN (19, 20, 21, 22, 23) ;

• SELECT *FROM ETUDIANTWHERE Age BETWEEN 19 AND 23 ;

• SELECT *FROM ETUDIANTWHERE CodePostal LIKE "42*"; 

• SELECT *FROM ETUDIANTWHERE CodePostal LIKE "42???";

Page 13: Cours N°2 Base de Données & Langage SQL Sahbi SIDHOM, ICom Lyon 2 Promo. DESS IM 02/03

Exercices :

1. créer la table CHAMPIGNONS

Espèce Catégorie Conditionnement Rosé des prés Conserve Bocal Rosé des prés Sec Verrine

Coulemelle Frais Boîte

Rosé des prés Sec Sachet plastique

2. réaliser la restriction R2 = SELECTION (CHAMPIGNONS, Catégorie = "Sec")

Espèce Catégorie Conditionnement Rosé des prés Sec Verrine Rosé des prés Sec Sachet plastique

3. réaliser la restriction R3 = SELECTION (CHAMPIGNONS, Conditionnement = "Verrine")

Page 14: Cours N°2 Base de Données & Langage SQL Sahbi SIDHOM, ICom Lyon 2 Promo. DESS IM 02/03

Opération JOINTURE (équijointure et autres)

SELECT * FROM table1, table2, table3, ...WHERE table1.attribut1=table2.attribut1 AND table2.attribut2=table3.attribut2 AND ... ;

Remarques : • En SQL, il est possible d'enchaîner plus jointures dans la même

instruction SELECT.

• Des jointures plus complexes par l'usage de la condition de comparaison (<,>, <=,>=, <>).

Exemple : SELECT * FROM Produit, Détail_Commande

WHERE Produit.CodePrd=Détail_Commande.CodePrd ;

Page 15: Cours N°2 Base de Données & Langage SQL Sahbi SIDHOM, ICom Lyon 2 Promo. DESS IM 02/03

Exercice :

1. créer deux tables PRODUIT et DETAIL_COMMANDE

PRODUIT DETAIL_COMMANDE

CodePrd Libellé Prix unitaire N°cde CodePrd quantité

590A HD 1,6 Go 1615 97001 590A 2

588J Scanner HP 1700 97002 515J 1

515J LBP 660 1820

97003 515J 3

2. réaliser la jointure R

R = JOINTURE (PRODUIT, DETAIL_COMMANDE, Produit.CodePrd=Détail_Commande.CodePrd)

CodePrd Libellé Prix unitaire N°cde quantité

590A HD 1,6 Go 1615 97001 2

515J LBP 660 1820 97002 1

515J LBP 660 1820 97003 3

Page 16: Cours N°2 Base de Données & Langage SQL Sahbi SIDHOM, ICom Lyon 2 Promo. DESS IM 02/03

Opération DIVISION• Il n'existe pas en SQL d'équivalent direct à la division.• Cependant il est toujours possible de trouver une autre

solution, notamment par l'intermédiaire des opérations de calcul (COUNT) et de regroupement (GROUP BY).

• Fonction COUNT : Calcule le nombre d'enregistrements renvoyés par une requête.

• Exemple : on souhaite trouver les athlètes qui participent à toutes les

épreuves, et en SQL :SELECT Athlète FROM PARTICIPER

GROUP BY AthlèteHAVING COUNT(*) = ( SELECT COUNT(*) FROM EPREUVE) ;

Page 17: Cours N°2 Base de Données & Langage SQL Sahbi SIDHOM, ICom Lyon 2 Promo. DESS IM 02/03

Exercice :

1. créer les deux tables PARTICIPER et EPREUVE

2. réaliser l’opération de DIVISION (PARTICIPER, EPREUVE) pour trouver :

"Quel athlète X qui participe à toutes les épreuves ? "

PARTICIPER EPREUVE DIVISION (PARTICIPER, EPREUVE)

Athlète Epreuve Epreuve Athlète

Dupont 200 m 200 m

Dupont

Durand 400 m 400 m

Dupont 400 m

110 m H

Martin 110 m H

Dupont 110 m H

Martin 200 m

Page 18: Cours N°2 Base de Données & Langage SQL Sahbi SIDHOM, ICom Lyon 2 Promo. DESS IM 02/03

Opération UNION

• SELECT liste d'attributs FROM table1UNIONSELECT liste d'attributs FROM table 2 ;

• Exemple : SELECT n°enseignant, NomEnseignant FROM E1UNIONSELECT n°enseignant, NomEnseignant FROM E2 ;

Page 19: Cours N°2 Base de Données & Langage SQL Sahbi SIDHOM, ICom Lyon 2 Promo. DESS IM 02/03

Exercice :

1. créer les deux tables Ens_CA et Ens_SYNDIC

E1 : Enseignants élus au CA E2 : Enseignants représentants syndicaux

n° enseignant nom_enseignant n°enseignant nom_enseignant

1 DUPONT 1 DUPONT

3 DURAND 4 MARTIN

4 MARTIN 6 MICHEL

5 BERTRAND

2. On désire obtenir l'ensemble des enseignants élus au CA ou représentants syndicaux.,

réaliser l’opération de réunion R = UNION (E1, E2)

n°enseignant nom_enseignant

1 DUPONT

3 DURAND

4 MARTIN

5 BERTRAND

6 MICHEL

Page 20: Cours N°2 Base de Données & Langage SQL Sahbi SIDHOM, ICom Lyon 2 Promo. DESS IM 02/03

Opération INTERSECTION

• SELECT attribut1, attribut2, ... FROM table1INTERSECTSELECT attribut1, attribut2, ... FROM table2 ;

ou • SELECT attribut1, attribut2, ... FROM table1

WHERE attribut1 IN (SELECT attribut1 FROM table2) ;

Exemple : • SELECT n°enseignant, NomEnseignant FROM E1

INTERSECTSELECT n°enseignant, NomEnseignant FROM E2 ;

ou• SELECT n°enseignant, NomEnseignant FROM E1

        WHERE n°enseignant IN (SELECT n°enseignant FROM E2) ;

Page 21: Cours N°2 Base de Données & Langage SQL Sahbi SIDHOM, ICom Lyon 2 Promo. DESS IM 02/03

Exercice :

1. en réutilisant les deux tables E1 et E2 dans UNION :

E1 : Enseignants élus au CA E2 : Enseignants représentants syndicaux

n° enseignant nom_enseignant n°enseignant nom_enseignant

1 DUPONT 1 DUPONT

3 DURAND 4 MARTIN

4 MARTIN 6 MICHEL

5 BERTRAND

2. On désire connaître les enseignants du CA qui sont des représentants syndicaux.,

réaliser l’opération : R2 = INTERSECTION (E1, E2)

n°enseignant nom_enseignant

1 DUPONT

4 MARTIN

Page 22: Cours N°2 Base de Données & Langage SQL Sahbi SIDHOM, ICom Lyon 2 Promo. DESS IM 02/03

Opération DIFFERENCE• SELECT attribut1, attribut2, ... FROM table1

EXCEPTSELECT attribut1, attribut2, ... FROM table2 ;

ou• SELECT attribut1, attribut2, ... FROM table1

WHERE attribut1 NOT IN (SELECT attribut1 FROM table2);

Exemple : • SELECT n°enseignant, NomEnseignant FROM E1

EXCEPTSELECT n°enseignant, NomEnseignant FROM E2 ;

ou• SELECT n°enseignant, NomEnseignant FROM E1

        WHERE n°enseignant NOT IN (SELECT n°enseignant FROM E2) ;

Page 23: Cours N°2 Base de Données & Langage SQL Sahbi SIDHOM, ICom Lyon 2 Promo. DESS IM 02/03

Exercice :

1. réutiliser les tables E1 et E2

E1 : Enseignants élus au CA E2 : Enseignants représentants syndicaux

n° enseignant nom_enseignant n°enseignant nom_enseignant

1 DUPONT 1 DUPONT

3 DURAND 4 MARTIN

4 MARTIN

6 MICHEL

5 BERTRAND

2. On désire obtenir la liste des enseignants du CA qui ne sont pas des représentants syndicaux.,

Réaliser l’opération R3 = DIFFERENCE (E1, E2)

n°enseignant nom_enseignant

3 DURAND

5 BERTRAND

Page 24: Cours N°2 Base de Données & Langage SQL Sahbi SIDHOM, ICom Lyon 2 Promo. DESS IM 02/03

Opération PRODUIT CARTESIEN

• SELECT * FROM table1, table2 ;

Exemple :

• SELECT * FROM Etudiants, Epreuves ;

Page 25: Cours N°2 Base de Données & Langage SQL Sahbi SIDHOM, ICom Lyon 2 Promo. DESS IM 02/03

Exercice :

1. créer les deux tables ETUDIANTS et EPREUVES

Etudiants Epreuves

n°étudiant nom libellé épreuve coefficient

101 DUPONT Informatique 2

102 MARTIN Mathématiques 3

Gestion financière 5

2. réaliser l’opération Examen = PRODUIT_CARTESIEN (Etudiants, Epreuves)

n°étudiant nom libellé épreuve coefficient

101 DUPONT Informatique 2

101 DUPONT Mathématiques 3

101 DUPONT Gestion financière 5

102 MARTIN Informatique 2

102 MARTIN Mathématiques 3

102 MARTIN Gestion financière 5

Page 26: Cours N°2 Base de Données & Langage SQL Sahbi SIDHOM, ICom Lyon 2 Promo. DESS IM 02/03

V. Application sur un projet : tables, liens, schéma relationnel,

requêtes en SQL…