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 …