Upload
julienne-rousseau
View
111
Download
1
Embed Size (px)
Citation preview
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
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 ( )
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.
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 [, ...]])
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.
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);
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
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 ;
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)
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" ;
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???";
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")
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 ;
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
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) ;
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
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 ;
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
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) ;
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
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) ;
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
Opération PRODUIT CARTESIEN
• SELECT * FROM table1, table2 ;
Exemple :
• SELECT * FROM Etudiants, Epreuves ;
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
V. Application sur un projet : tables, liens, schéma relationnel,
requêtes en SQL…