Nicolas Zozor [email protected]
Retour sur les clés Une clé primaire est une clé d’une table qui est composée
d’un ou plusieurs (dit clé composée) attributs (colonnes) de la table et est utilisée pour identifier chaque ligne de manière unique. La clé primaire ne doit pas contenir de colonne valeur NULL.
Une clé unique possède les mêmes propriétés qu’une clé primaire à l’exception qu’une colonne définie comme clé unique peut être NULL. Elle contrôle l’unicité de la valeur
Une clé étrangère représente une relation entre les tables. Elle est composée d’une ou plusieurs attributs (colonnes) dont les valeurs dépendent de clés primaires (ou uniques) d’autres tables
On parle d’intégrité référentielle quand on veut signifier que les relations représentées par les clés sont maintenues. Elle assure la cohérence des données.
Entité et tables Lorsque l’on crée la base de donnée, l’entité devient une
table.
Requête Une requête est une question posée pour interroger une base
de donnée Permet d’extraire des données d’une ou plusieurs tables
Algèbre relationnelle
Outils de traitement permettant de formaliser les opérations pouvant être réalisées sur un schéma relationnel.
Opérateurs algébriques: (SELECTION,PROJECTION,JOINTURE)
Opérateurs ensemblistes:(UNION, INTERSECTION, DIFFERENCE, PRODUIT
CARTESIEN)
PROJECTION
Consiste à ne retenir que certains attributs(colonnes) d’une relation( 1 relation = 1 entité )
Le nombre de colonnes se trouve ainsi limité
PROJECTION Client(codeClient, nom, prénom, adresse,
codePostal, ville, telephone, mail) Materiels(codeMateriel, Désignation, Fabricant,
dateAchat, #codeClient) Interventions(numInterventions, descriptifPannne,
dateIntervention, temps_passé, #codeMateriel, #codeInterventions)
TypesInterventions(codeIntervention, designation, prixHoraire)
PiècesNeuves(refPièceNeuve, libellé, prix) Utiliser(#numInterventions,#refPièceNeuve, quantité)
PROJECTION
codeClient
nom prénom adresse codePostal
ville telephone
230 Vins Diesel Rue F1 V8769 Los Ang 08600 Vins@
231 Deep Johnny Pont 9 97233 Cap Est 07990 Deep@
232 Judor Eric Rue H 97200 FDF 05969 Judor@
Table Client : Mode feuille de données
Table d’origine EXEMPLE 1
PROJECTION
R1 = SELECT * FROM Client; * sélectionne tous les attributs de la table
EXEMPLE 1
PROJECTION
codeClient
nom prénom adresse codePostal
ville telephone
230 Vins Diesel Rue F1 V8769 Los Ang 08600 Vins@
231 Deep Johnny Pont 9 97233 Cap Est 07990 Deep@
232 Judor Eric Rue H 97200 FDF 05969 Judor@
Table Client : Mode feuille de données
Résultat de la requête: EXEMPLE 1
PROJECTION
codeClient
nom prénom adresse codePostal
ville telephone
230 Vins Diesel Rue F1 V8769 Los Ang 08600 Vins@
231 Deep Johnny Pont 9 97233 Cap Est 07990 Deep@
232 Judor Eric Rue H 97200 FDF 05969 Judor@
Table Client : Mode feuille de données
Table d’origine EXEMPLE 2
PROJECTION
R1 = SELECT nom, prénom FROM Client; “Nom » et « prénom » sont 2 attributs de la table
client
EXEMPLE 2
PROJECTION
nom prénom
Vins Diesel
Deep Johnny
Judor Eric
Table Client : Mode feuille de données
Résultat de la requête: EXEMPLE 2
SELECTION
Consiste à n’afficher que certaines lignes d’une table en utilisant:
Opérateurs de comparaison: <, <=, >, >= Connecteurs logiques: AND, OR, NOT Opérateurs ensemblistes: IN, BETWEEN, LIKE La clause NULL (champs vide)
SELECTION
codeClient
nom prénom adresse codePostal
ville nbFilms
230 Vins Diesel Rue F1 V8769 Los Ang 51
231 Deep Johnny Pont 9 97233 Cap Est 55
232 Judor Eric Rue H 97200 FDF 5
Table Client : Mode feuille de données
Table d’origine EXEMPLE 1
SELECTION
R1 = SELECT * FROM Client WHERE nbFilms = 51; Sélectionner tous les attributs des clients ayant
joués 51 films
EXEMPLE 1
SELECTION
Table Client : Mode feuille de données
Résultat de la requête:
codeClient
nom prénom adresse codePostal
ville nbFilms
230 Vins Diesel Rue F1 V8769 Los Ang 51
EXEMPLE 1
SELECTION
codeClient
nom prénom adresse codePostal
ville nbFilms
230 Vins Diesel Rue F1 V8769 Los Ang 51
231 Deep Johnny Pont 9 97233 Cap Est 55
232 Judor Eric Rue H 97200 FDF 5
Table Client : Mode feuille de données
Table d’origineEXEMPLE 2
SELECTION
R1 = SELECT * FROM Client WHERE nbFilms = 5; Sélectionner tous les attributs des clients ayant
joués dans 5 films
EXEMPLE 2
SELECTION
Table Client : Mode feuille de données
Résultat de la requête:
codeClient
nom prénom adresse codePostal
ville nbFilms
232 Judor Eric Rue H 97200 FDF 5
EXEMPLE 2
SELECTION
codeClient
nom prénom adresse codePostal
ville nbFilms
230 Vins Diesel Rue F1 V8769 Los Ang 51
231 Deep Johnny Pont 9 97233 Cap Est 55
232 Judor Eric Rue H 97200 FDF 5
Table Client : Mode feuille de données
Table d’origineEXEMPLE 3
SELECTION
R1 = SELECT * FROM Client WHERE nbFilms > 50; Sélectionner tous les attributs des clients ayant
joués dans 50 films et plus
EXEMPLE 3
SELECTION
Table Client : Mode feuille de données
Résultat de la requête:
codeClient
nom prénom adresse codePostal
ville nbFilms
230 Vins Diesel Rue F1 V8769 Los Ang 51
231 Deep Johnny Pont 9 97233 Cap Est 55
EXEMPLE 3
SELECTION
codeClient
nom prénom adresse codePostal
ville nbFilms
230 Vins Diesel Rue F1 V8769 Los Ang 51
231 Deep Johnny Pont 9 97233 Cap Est 55
232 Judor Eric Rue H 97200 FDF 5
Table Client : Mode feuille de données
Table d’origineEXEMPLE 4
SELECTION
R1 = SELECT nom, prénom FROM Client WHERE nbFilms > 50;
Sélectionner les noms et prénoms des clients ayant joués dans 50 films et plus
EXEMPLE 4
SELECTION
Table Client : Mode feuille de données
Résultat de la requête:
nom prénom
Vins Diesel
Deep Johnny
EXEMPLE 4
SELECTION
codeClient
nom prénom adresse codePostal
ville nbFilms
230 Vins Diesel Rue F1 V8769 Los Ang 51
231 Deep Johnny Pont 9 97233 Cap Est 55
232 Judor Eric Rue H 97200 FDF 5
Table Client : Mode feuille de données
Table d’origineEXEMPLE 5
SELECTION
R1 = SELECT * FROM Client WHERE nbFilms BETWEEN 2 AND 52;
Sélectionner tous les attributs des clients ayant joués entre 2 et 52 films
EXEMPLE 5
SELECTION
Table Client : Mode feuille de données
Résultat de la requête:
codeClient
nom prénom adresse codePostal
ville nbFilms
230 Vins Diesel Rue F1 V8769 Los Ang 51
232 Judor Eric Rue H 97200 FDF 5
EXEMPLE 5
SELECTION
codeClient
nom prénom adresse codePostal
ville nbFilms
230 Vins Diesel Rue F1 V8769 Los Ang 51
231 Deep Johnny Pont 9 97233 Cap Est 55
232 Judor Eric Rue H 97200 FDF 5
Table Client : Mode feuille de données
Table d’origineEXEMPLE 6
SELECTION
R1 = SELECT * FROM Client WHERE nom LIKE ‘de*’
Sélectionner tous les attributs des clients ayant un nom commençant par ‘de’
EXEMPLE 6
SELECTION
Table Client : Mode feuille de données
Résultat de la requête:
codeClient
nom prénom adresse codePostal
ville nbFilms
231 Deep Johnny Pont 9 97233 Cap Est 55
EXEMPLE 6
SELECTION
codeClient
nom prénom adresse codePostal
ville nbFilms
230 Vins Diesel Rue F1 V8769 Los Ang 51
231 Deep Johnny Pont 9 97233 Cap Est 55
232 Judor Eric Rue H 97200 NULL 5
Table Client : Mode feuille de données
Table d’origineEXEMPLE 7
SELECTION
R1 = SELECT * FROM Client WHERE ville IS NULL Sélectionner tous les attributs des clients ayant
l’attribut ville vide (null)
EXEMPLE 7
SELECTION
Table Client : Mode feuille de données
Résultat de la requête:
codeClient
nom prénom adresse codePostal
ville nbFilms
232 Judor Eric Rue H 97200 NULL 5
EXEMPLE 7
SELECTION
codeClient
nom prénom adresse codePostal
ville nbFilms
230 Vins Diesel Rue F1 V8769 Los Ang 51
231 Deep Johnny Pont 9 97233 Cap Est 55
232 Judor Eric Rue H 97200 NULL 5
Table Client : Mode feuille de données
Table d’origineEXEMPLE 8
SELECTION
R1 = SELECT * FROM Client WHERE ville IS NOT NULL
Sélectionner tous les attributs des clients n’ayant pas l’attribut ville vide (null)
EXEMPLE 8
SELECTION
Table Client : Mode feuille de données
Résultat de la requête:
codeClient
nom prénom adresse codePostal
ville nbFilms
230 Vins Diesel Rue F1 V8769 Los Ang 51
231 Deep Johnny Pont 9 97233 Cap Est 55
EXEMPLE 8
DISTINCT
Il est possible supprimer les doublons en ajoutant la close DISTINCT
SELECT DISTINCT NomColonne FROM TABLE
JOINTURE
Une jointure Permet d’obtenir des données provenant de plusieurs tables
JOINTURE
Schéma relationnel AUTEURS(NumAuteur, NomAuteur, EmailAuteur,) ARTICLES(NumArti, TitreArti, TexteArti, DateArti, #NumAuteur)
C.I.F
EXEMPLE 1
JOINTUREEXEMPLE 1
NumAuteur NomAuteur PrenomAuteur EmailAuteur
1 Dubois Marcel [email protected]
2 Dupont Jacques [email protected]
3 Leroy Nicole [email protected]
Auteurs
JOINTUREEXEMPLE 1
NumArti
TitreArti TextArti DateArti #NumAuteur
1 Wall Street xxxxxxxxxx 12/12/2011 2
2 La Fed blablabla 05/05/2007 1
3 La loi Hadop yyyyyyyyyy 03/03/2009 2
Articles
JOINTURE
Afficher tous les titres d’articles écrit part Dupont Jacques. Afficher également l’e-mail de l’auteur correspondant:
R1 = SELECT TitreArti, EmailAuteur
FROM ARTICLE,AUTEUR
WHERE ARTICLE.NumAuteur = Auteur.NumAuteur
AND AUTEUR.NumAuteur = 2
EXEMPLE 1
JOINTUREEXEMPLE 1
TitreArti EmailAuteur
Wall Street [email protected]
La loi Hadop [email protected]
Résultat de la requête
JOINTURE
Schéma relationnel FOURNISSEUR(NumFrs, NomFrs, AdrFrs, VilleFrs) ARTICLE(NumArt, LibArt, Stock, PrixUnitHT) ACHAT(#NumFrs,#NumArt, PrixAchat, Délai, EnCde)
C.I.M
EXEMPLE 2
JOINTURE
NumFrs NomFrs AdrFrs VilleFrs
230 Gcom Rue F1 FDF
231 Exnet Pont 9 Lamentin
232 Webplus Rue H FDF
Table Client : Mode feuille de données
Table FournisseurEXEMPLE 2
JOINTURE
NumArt LibArt Stock PrixUnitHT
100 PCportable 20 450
101 TelPortabl 50 70
102 clavierUsb 100 30
103 HautParleur
200 100
Table ARTICLE: Mode feuille de données
Table ArticleEXEMPLE 2
JOINTURE
#NumFrs,#NumArt PrixAchat Délai EnCde
230,100 350 6 validé
232,102 20 9 validé
231,101 50 2 validé
Table ACHAT: Mode feuille de données
Table AchatEXEMPLE 2
JOINTURE
Déterminer le Numéro et délai de livraison des articles pour lesquels le prix unitaire est compris entre 50€ et 500€
R1 = SELECT NumArt, Délai
FROM ACHAT,ARTICLE
WHERE ACHAT.NumArt = ARTICLE.NumArt
AND PrixUnitHt BETWEEN 50 AND 500
EXEMPLE 2
JOINTURE
NumFrs NumArt Délai
230 100 6
231 101 2
EXEMPLE 2
FONTIONS D’AGREGATION Permettent d’effectuer des calculs
statistiques COUNT() SUM() AVG() MAX() MIN() STDEV() VAR()
COUNT
Dénombre le nombre d’éléments de la colonne sélectionnée
COUNT
NumArt LibArt Stock PrixUnitHT
100 PCportable 20 450
101 TelPortabl 50 70
102 clavierUsb 100 30
103 HautParleur
200 100
Table ARTICLE: Mode feuille de données
Table ArticleEXEMPLE 1
COUNT
Compter le nombre d’articles de la table ARTICLES
R1 = SELECT COUNT(NumArt) AS [Nombre d’articles]
FROM ARTICLES
EXEMPLE 1
COUNT
Nombre d’articles
4
Résultat de la requêteEXEMPLE 1
COUNT
Compter le nombre de ligne dela table ARTICLES
R1 = SELECT COUNT(*) AS [Nombre d’articles]
FROM ARTICLES
EXEMPLE 2
COUNT
Nombre d’articles
4
Résultat de la requêteEXEMPLE 1
SUM
Additionne les valeurs d’un champ numérique
Calcule la somme d’une colonne Ignore les valeurs NULL (champs vides)
SUM
NumArt LibArt Stock PrixUnitHT
100 PCportable 20 450
101 TelPortabl 50 70
102 clavierUsb 100 30
103 HautParleur
200 100
Table ARTICLE: Mode feuille de données
Table ArticleEXEMPLE 1
SUM
Calculer le total d’articles en stock R1 = SELECT SUM(numArt) AS
[total stock] FROM ARTICLES
EXEMPLE 2
SUM
total stock
370
Résultat de la requêteEXEMPLE 1
FONTIONS D’AGREGATION Permettent d’effectuer des calculs
statistiques COUNT() SUM() AVG() Moyenne MAX() valeur maximum MIN() valeur minimum STDEV() écart type VAR() variance
TRI ET REGROUPEMENT
ORDER BY : résultat trié GROUP BY : forme des groupes de
lignes ayant une valeur commune
ORDER BY
NumArt LibArt Stock PrixUnitHT
100 PCportable 20 450
101 TelPortabl 50 70
102 clavierUsb 100 30
103 HautParleur 200 100
Table ARTICLE: Mode feuille de données
Table ArticleEXEMPLE 1
ORDER BY
ASC ordre croissant DESC: ordre décroissant
R1 = SELECT LibArt FROM ARTICLES ORDER BY LibArt ASC
Sélectionne tous les libellés de la table ARTICLE et tri par ordre alphabétique
EXEMPLE 1
ORDER BY
LibArt
clavierUsb
HautParleur
PCportable
TelPortabl
Table ARTICLE: Mode feuille de données
RésultatEXEMPLE 1
GROUP BY
Forme des groupes de lignes ayant une valeur commune
GROUP BY
Table contenant les achats et leurs clients respectifs
Table ACHATSEXEMPLE 1
GROUP BY
Somme totale des achats par groupe de clients
R1 = SELECT Customer, SUM(OrderPrice) FROM ACHATSGROUP BY Customer
EXEMPLE 1
GROUP BY
Somme par Clients
Résultat de la requêteEXEMPLE 1
GROUP BYEXEMPLE 2
GROUP BY
Somme totale Clients confondus
Résultat de la requêteEXEMPLE 2
HAVING
RESTRICTIONS (selection) sur les groupes crées avec GROUP BY
HAVING
Table contenant les achats et leurs clients respectifs
Table ACHATSEXEMPLE 1
HAVING
Somme totale des achats par groupe de clientsRESTRICTIONS sur cette Somme
R1 = SELECT Customer, SUM(OrderPrice) FROM ACHATSGROUP BY CustomerHAVING SUM(OrderPrice) < 2000
EXEMPLE 1
HAVING
Somme par Clients et Sommes inférieures à 2000 €
Résultat de la requêteEXEMPLE 1
Top Related