Upload
others
View
2
Download
0
Embed Size (px)
Citation preview
Formation SQLJeanne Merle – OPAC38
Base de données, SGBD, SGBDR ?
! Une base de données est un outil permettant de stocker et retrouver des données en rapport avec une activité. ! Exemples de bases de données :
! Un fichier au format XML
! Un fichier au format Excel
! Un fichier au format texte
! Un SGBD est un Système de Gestion de Base de Données, c’est-à-dire un logiciel, une interface, permettant d’interroger, gérer et mettre à jour les données. ! Exemples de SGBD : MySQL, SQLite
! Un SGBDR est un SGBD Relationnel, mettant en relation entre elles les données par des liens et des contraintes ! Exemples de SGBDR : Oracle, SQL Server, …
Principe d’un SGBDRIDClient Nom Prénom
001 Dupont Pierre
003 Dubois Jacques
004 Mercier Marie
005 Duval Romain
006 Saturnin Laurence
TablesClésClés étrangères
IDCommande IDClient Date
0013 001 01/02/2015
0047 001 06/05/2015
0017 003 02/03/2015
0031 003 17/03/2015
0062 004 18/06/2015
IDCommande IDArticle Quantité Prix_UHT
0013 0004 1 12,50
0013 0025 2 19,90
0013 0026 1 9,90
0013 0035 5 15,75
0013 0118 5 0,20
Champs
Principe d’un SGBDRIDClient Nom Prénom
001 Dupont Pierre
003 Dubois Jacques
004 Mercier Marie
005 Duval Romain
006 Saturnin Laurence
UnicitéCascade (Clé, Clé étrangère)
Intégrité
IDCommande IDClient Date
0013 001 01/02/2015
0047 001 06/05/2015
0017 003 02/03/2015
0031 003 17/03/2015
0062 004 18/06/2015
IDCommande IDArticle Quantité Prix_UHT
0013 0004 1 12,50
0013 0025 2 19,90
0013 0026 1 9,90
0013 0035 5 15,75
0013 0118 5 0,20
Non null
Des objets pour gérer les données
! Tables ! Stockage statique des données.
! Vues ! Interrogation des tables à la demande, en direct
! Index ! « raccourcis » vers les données des tables
! Procédures stockées, packages, fonctions ! Programmes de traitement des données, utilisées par des logiciels, ou la nuit.
! Triggers ! Contraintes fortes entre plusieurs tables et plusieurs données. Règles de gestion.
Un langage pour gérer les données : SQL
! SQL (Structured Query Language) permet de :
! Interroger la base de données
! Créer, Modifier ou Supprimer des données
! Créer, Modifier ou Supprimer des objets
Les outils! Un client (« Oracle » ou autre)
! Un logiciel de développement SQL (« SQLTools » ou autre)
Le paramétrage! Fichier TNSName.ora
! Variables systèmes
Alias de la baseNom du serveurPort du serveur
Interroger la base de données
! Exercice 1 : Afficher le contenu de la table « a_collaborateurs » ! Utiliser SELECT, *, FROM
Select * from a_collaborateurs
! Exercice 2 : Afficher les champs « PRENOM_COLLAB, NOM_COLLAB, FONCTION » de la table « a_collaborateurs »
SELECT PRENOM_COLLAB, NOM_COLLAB, FONCTION
FROM a_collaborateurs
! Exercice 3 : Filtrer le résultat précédent pour la fonction « Responsable de Service » ! Utiliser WHERE, =, côtes
SELECT PRENOM, NOM_COLLAB, FONCTION
FROM a_collaborateurs
WHERE FONCTION ='Responsable de Service'
Interroger la base de données
! Améliorations du code 1: ! Aller à la ligne pour distinguer le Select, le From, la clause Where, et les autres
clauses ! Préfixer le nom de chaque champ par le nom de la table d’où il provient ([Table].
[Champ]) ! Commenter sa requête (en utilisant -- en début de ligne OU en encadrant un
paragraphe avec /* et */ )
-- Responsables de services de l’OPAC38 SELECT a_collaborateurs.PRENOM, a_collaborateurs.NOM_COLLAB, a_collaborateurs.FONCTION FROM a_collaborateurs WHERE a_collaborateurs.FONCTION ='Responsable de Service'
Interroger la base de données
! Exercice 4 : Trier par nom de famille dans l’ordre alphabétique ! Utiliser ORDER BY
-- Responsables de services de l’OPAC38
SELECT a_collaborateurs.PRENOM,
a_collaborateurs.NOM_COLLAB,
a_collaborateurs.FONCTION
FROM a_collaborateurs
WHERE a_collaborateurs.FONCTION ='Responsable de Service'
Interroger la base de données
! Exercice 5 : Compter le nombre de collaborateurs ! Utiliser Count(*)
SELECT Count(*)
FROM a_collaborateurs
! Exercice 6 : Compter le nombre de collaborateurs par Fonction ! Utiliser la clé de la table, GROUP BY( …)
SELECT Count(a_collaborateurs.cle_collaborateur), a_collaborateurs.Fonction
FROM a_collaborateurs
GROUP BY (a_collaborateurs.Fonction)
Interroger la base de données
IDClient Nom Prénom
001 Dupont Pierre
003 Dubois Jacques
004 Mercier Marie
005 Duval Romain
006 Saturnin LaurenceIDCommande IDClient Date
0013 001 01/02/2015
0047 001 06/05/2015
0017 003 02/03/2015
0031 003 17/03/2015
0062 004 18/06/2015
XX
Null
Null
! Utiliser plusieurs tables : JOINTURE ! INNER JOIN
! LEFT/RIGHT JOIN
XX
Interroger la base de données
! Exercice 7 : Faire une jointure simple entre 2 tables Pour la table A_collaborateur : récupérer le nom et le prénom Pour la table A_planning_ent_agt_grpe (planning de l’entretien des groupes) : récupérer le jour
! Utiliser INNER JOIN Select Table1.champ, ...
From Table1
Inner join Table2 on (Table1.clé = Table2.clé)
SELECT a_collaborateurs.nom_collab, a_collaborateurs.prenom, a_planning_ent_agt_grpe.jour
FROM a_collaborateurs
INNER JOIN a_planning_ent_agt_grpe ON (a_collaborateurs.cle_collaborateur = a_planning_ent_agt_grpe.fk_cle_collaborateur)
Interroger la base de données
! Exercice 8 : Supprimer les lignes en double ! Utiliser « Distinct » dans le « Select »
SELECT DISTINCT a_collaborateurs.nom_collab, a_collaborateurs.prenom, a_planning_ent_agt_grpe.jour
FROM a_collaborateurs
INNER JOIN a_planning_ent_agt_grpe ON (a_collaborateurs.cle_collaborateur = a_planning_ent_agt_grpe.fk_cle_collaborateur)
Interroger la base de données
! Exercice 9 : Amalgamer les résultats ! Mettre le prénom et le nom dans un même champ, mettre les jours sous forme de liste
séparée d’une virgule ! Utiliser || pour concaténer prénom et nom ! Utiliser WM_CONCAT(Distinct …) pour concaténer les jours d’entretien ! Utiliser GROUP BY en fin de requête pour compléter le WM_CONCAT
SELECT DISTINCT a_collaborateurs.prenom || ' ' || a_collaborateurs.nom_collab, WM_CONCAT(DISTINCT a_planning_ent_agt_grpe.jour) FROM a_collaborateurs INNER JOIN a_planning_ent_agt_grpe ON (a_collaborateurs.cle_collaborateur = a_planning_ent_agt_grpe.fk_cle_collaborateur) GROUP BY a_collaborateurs.prenom || ' ' || a_collaborateurs.nom_collab
Interroger la base de données
! Exercice 10 : Inclure les collaborateurs sans planning d’entretien ! Utiliser LEFT JOIN
SELECT DISTINCT a_collaborateurs.prenom || ' ' || a_collaborateurs.nom_collab, WM_CONCAT(DISTINCT a_planning_ent_agt_grpe.jour)
FROM a_collaborateurs
LEFT JOIN a_planning_ent_agt_grpe ON (a_collaborateurs.cle_collaborateur = a_planning_ent_agt_grpe.fk_cle_collaborateur)
GROUP BY a_collaborateurs.prenom || ' ' || a_collaborateurs.nom_collab
Interroger la base de données
! Exercice 11 : Indiquer clairement qu’il n’y a pas d’entretien ! Utiliser DECODE avec le WM_CONCAT
DECODE ( champ, valeur à vérifier, oui : valeur à retourner, non : autre valeur à retourner)
SELECT DISTINCT a_collaborateurs.prenom || ' ' || a_collaborateurs.nom_collab,
Decode (WM_CONCAT(DISTINCT a_planning_ent_agt_grpe.jour), NULL, 'Non concerné', WM_CONCAT(DISTINCT a_planning_ent_agt_grpe.jour))
FROM a_collaborateurs
LEFT JOIN a_planning_ent_agt_grpe ON (a_collaborateurs.cle_collaborateur = a_planning_ent_agt_grpe.fk_cle_collaborateur)
GROUP BY a_collaborateurs.prenom || ' ' || a_collaborateurs.nom_collab
Interroger la base de données
! Exercice 12 : Mettre en forme les noms des champs retournés ! Utiliser « AS » et un nom de colonne pour chacune des valeurs retournées par le Select
Select Table1.champ AS « Nom de colonne », …
SELECT DISTINCT a_collaborateurs.prenom || ' ' || a_collaborateurs.nom_collab AS "Collaborateur",
Decode (WM_CONCAT(DISTINCT a_planning_ent_agt_grpe.jour), NULL, 'Non concerné', WM_CONCAT(DISTINCT a_planning_ent_agt_grpe.jour)) AS "Planning entretien" FROM a_collaborateurs LEFT JOIN a_planning_ent_agt_grpe ON (a_collaborateurs.cle_collaborateur = a_planning_ent_agt_grpe.fk_cle_collaborateur) GROUP BY a_collaborateurs.prenom || ' ' || a_collaborateurs.nom_collab
Interroger la base de données
! Unir les résultats de 2 requêtes retournant les mêmes champs : UNION
Select champ2, champ2, champ3 From Table1 UNION
Select champ1, champ2, champ3
From Table2
! Conserver une requête pour une utilisation régulière : création d’une VUE CREATE or REPLACE VIEW ma_vue (champ1, champ2, champ3) AS
(Select champ1, champ2, champ3 From Table1)
Modifier les données
! Créer une Sélection des champs à modifier ! Vérifier le volume de champs qui vont être modifiés
! Vérifier la modification qui sera effectuée
Select champ1, modification du champ1
From Table
Where condition1 AND condition2 AND …
Modifier les données
! Exercice 13 : Sélectionner les jours de RTT des collaborateurs, transformer le lundi en autre chose pour une fonction choisie ! Utiliser Select, Decode, Where
SELECT Fonction, Jour_RTT, Decode(jour_RTT,'Lun', '(ex-Lun) Ven',jour_RTT) AS "Nouveau Jour"
FROM a_collaborateurs
WHERE Fonction LIKE 'Responsable%'
Modifier les données
! Exercice 14 : Mettre à jour les données ! Copier-coller le code du « SELECT » précédent
! Utiliser « UPDATE » à la place de « SELECT » pour mettre à jour le champ « Jour_RTT »
! Utiliser « ; » à la fin de chaque commande pour distinguer le « SELECT » de l’ »UPDATE »
UPDATE Table1 SET champ1 = modification du champ1
WHERE …
UPDATE a_collaborateurs
SET Jour_RTT = Decode(jour_RTT,'Lun', '(ex-Lun) Ven',jour_RTT)
WHERE Fonction LIKE 'Responsable%‘;
! ATTENTION : Seule la commande « Commit » entérine les modifications définitivement.
Supprimer les données
! Créer une Sélection des champs à supprimer ! Vérifier le volume de champs qui vont être supprimer
Select champ1, modification du champ1
From Table
Where condition1 AND condition2 AND …