Prise en main de PostgreSQL pour les débutants à l'aide de pgAdmin III

Cet article a pour but d'expliquer aux débutants comment prendre en main PostgreSQL très simplement à l'aide de l'outil graphique PgAdmin III.

Article lu   fois.

L'auteur

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

0. Introduction

L'objectif est l'utilisation d'une base de données pour gérer une liste de films avec l'année de sortie ou de réalisation ainsi que le genre du film un peu comme IMDB
Le nom donné à cette base sera AMDB

Hypothèses
Le film a un seul nom Un film a une seule année de sortie, et peut avoir un ou plusieurs genres 3 tableaux seront utilisés un comportant le nom des films, un autre les années et un troisième les genres. Le tableau année pourra servir à d'autres relations avec d'autres tableaux dans le cas d'une extension de la base. Il faudra créer un 4è tableau pour les relations multiples entre les films et les genres ( Tab_film_genre).

Relations entre les tables

1. CREATION DE LA BASE DE DONNEES

Juste après l'installation, vous avez des palettes de composants en plus :

Sélectionner postgrest - ajouter une objet - ajouter une base de données

indiquer le nom de la base amdb

on obtient

2. CREATION DES TABLES

2.1. TABLE GENRE

2.1.1. Ouvrir schéma public

ensuite table , souris bouton droit sélectionner ajouter une table

Ajout d'une table tabgenre Et validation

La table est crée

2.1.2. création des colonnes

La table comporte 2 colonnes Genre et idgenre, idgenre sera utilisée comme clé primaire avec incrémentation automatique

Ajout des colonnes

Nom de la colonne : idgenre

sélectionner le type de données sérial pour l'incrémentation automatique valider la commande par ok

La colonne idgenre est créée

Création de la colonne genre De 40 caractères Non nuls

La table contient 2 colonnes :genre et idgenre

2.1.3. création d'une clé primaire

nom de la clé : clégenre

ensuite sélectionner colonne
Sélectionner comme colonne idgenre correspondant à clé primaire Ajouter et ok

2.1.4. Visualiser le contenu d'une table

il n'y a rien , seules les colonnes apparaissent

2.1.5. Le panneau sql affiche les commandes, pour la colonne idgenre :

2.1.6. table année tabannée

2 colonnes :
idannée type serial
année type nombreentier
cléannée clé primaire sur idannée

2.1.7. table film tabfilm

et enfin la table tabfilm
idfilm : type serial, titre : type 40 caractères, annéesortie : type entier (clé etrangère, pointe sur la tabannée). )
cléfilm clé primaire colonne idfilm

2.2. RELATION UN A PLUSIEURS ENTRE DEUX TABLES

Entre film et année, une seule année de sortie pour un film et plusieurs films pour une année
( Créer une colonne du type annéesortie dans tabfilm)
La colonne annéesortie de la table tabfilm va pointer sur la cléannée de tabannée type de relation n - 1
Annéesortie et cléannée sont de même type intéger
La clé étrangère est créée dans la table ou il y a « plusieurs »

2.2.1.. Créer une clé étrangère

Nom de la clé étrangère = keyannée

Référence = la table tabannée

2.3. RELATION PLUSIEURS A PLUSIEURS ENTRE DEUX TABLES

Entre film et genre, plusieurs genres pour un film et plusieurs films pour un genre

Relation entre deux tables du type 1- n et n - 1
Une table intermédiaire est nécessaire

2.3.1. table intermédiaire entre tabfilm et tagenre

Créer une table intermédiaire ou de jointure tab_film_genre

avec 3 colonnes
la 1ère pour la clé de la table key de type serial
la 2è pour pointer sur la clé du tableau tabfilm nom de la colonne film type entier
la 3è pour pointer sur la clé de tabgenre nom de la colonne genre type entier
clé primaire sur colonne key

2.3.2. Ajouter les clés étrangères pour les liaisons avec tabfilm et tabgenre

Créer une clé étrangère ayant pour nom titre et référence la table tabfilm

ensuite

définir la colonne locale film qui va correspondre à la clé idfilm de tabfilm.

2.3.3. Créer une deuxième clé étrangère genre

genre
qui a pour référence la table tabgenre

avec colonne locale genre et
référencé dans le tableau tabgenre par la clé de ce tableau idgenre

Résultat

3. INSERER LES DONNEES DANS LES TABLES

3.1. Insérer les données

Pour insérer des données dans une table utiliser la fonction script : cliquer sur l'icône SQL

On obtient

3.2. Insérer les genres ligne par ligne pour le tableau genre

on indique le nom du tableau et ensuite les valeurs dans l'ordre des colonnes
idgenre = 1 et genre = comédie
INSERT INTO tabgenre VALUES ( 1,'comédie');

ou uniquement la valeur de genre, idgenre sera automatiquement incrémenté
INSERT INTO tabgenre(genre) VALUES ( 'drame');

résultats

ajouter les genres suivants
INSERT INTO tabgenre(genre) VALUES ( 'policier');
INSERT INTO tabgenre(genre) VALUES ('horreur');
INSERT INTO tabgenre(genre) VALUES ('aventure');
INSERT INTO tabgenre(genre) VALUES ( 'histoire');
INSERT INTO tabgenre(genre) VALUES ( 'fiction');

3.3. Insérer des données dans tabannée

INSERT INTO tabannée(année) VALUES ( 2000');

3.4. Insérer des données dans tabfilm

ajouter des films
INSERT INTO tabfilm(titre,annéesortie) VALUES ( 'Men ', 2);
INSERT INTO tabfilm(titre,annéesortie) VALUES ('Forge', 3);
INSERT INTO tabfilm(titre,annéesortie) VALUES ( 'Star', 4);
INSERT INTO tabfilm(titre,annéesortie) VALUES ( 'histoire', 2);
INSERT INTO tabfilm(titre,annéesortie) VALUES ( 'Soleil' , 1);

3.5. Insérer des données dans tab_film_genre

structure de la table

INSERT INTO tab_film_genre(film,genre) VALUES ( 4,2);
INSERT INTO tab_film_genre(film,genre) VALUES ( 1,1);
INSERT INTO tab_film_genre(film,genre) VALUES ( 1,4);
INSERT INTO tab_film_genre(film,genre) VALUES ( 3,2);
INSERT INTO tab_film_genre(film,genre) VALUES ( 2,3);
INSERT INTO tab_film_genre(film,genre) VALUES ( 2,6);
INSERT INTO tab_film_genre(film,genre) VALUES ( 1,1);

Contenu de la table

4. POUR AFFICHER LE CONTENU D'UNE SEULE TABLE

4.1. le contenu d'une table

SELECT * from tabannée;

4.2. Le contenu d'une colonne d'une table

SELECT année FROM tabannée;

4.3. Le contenu sélectionné d'une colonne d'une table

SELECT année FROM tabannée WHERE année>2000;

4.4. Le contenu trié d'une colonne d'une table

SELECT * FROM tabannée ORDER BY année;

ou SELECT année FROM tabannée ORDER BY année Pour afficher la colonne année

4.5. Même principe pour genre

4.6. Compter le nombre de films

pour la liste des films
SELECT titre FROM tabfilm;

pour le nombre de films
SELECT count( titre )FROM tabfilm;

5. AFFICHER LE CONTENU DE DEUX TABLES LIEES PAR UNE CLE ETRANGERE

5.1. Afficher le film et l'année de sortie

SELECT F.titre,A.année FROM tabfilm AS F, tabannée AS A WHERE A.idannée=F.annéesortie

5.2. afficher le film et l'année de sortie, tri sur l'année

SELECT tabfilm.titre,tabannée.année FROM tabfilm , tabannée WHERE tabannée.idannée=tabfilm.annéesortie ORDER BY année
il est plus rapide de remplacer le nom de la table par une lettre
SELECT F.titre,A.année FROM tabfilm AS F, tabannée AS A WHERE A.idannée=F.annéesortie ORDER BY année

5.3. afficher le film et l'année de sortie, sur une année

SELECT F.titre,A.année FROM tabfilm AS F, tabannée AS A WHERE A.idannée=F.annéesortie and a.année=2000

5.4. afficher le film et l'année de sortie, depuis une année

SELECT F.titre,A.année FROM tabfilm AS F, tabannée AS A WHERE A.idannée=F.annéesortie and a.année>2000

6. AFFICHER LE CONTENU DE DEUX TABLES LIEES PAR UNE TABLE INTERMEDIAIRE DE TYPE n-n

tabfilm F

tab_film_genre A

tabgenre G

6.1. Edition triée des genres par films

SELECT F.titre,G.genre
FROM tabfilm AS F, tabgenre AS G ,tab_film_genre AS A
where
F.idfilm = A.film
AND
A.genre=G.idgenre
ORDER BY titre, genre

6.2. Edition triée des films par genres

SELECT F.titre,G.genre
FROM tabfilm AS F, tabgenre AS G ,tab_film_genre AS A where F.idfilm = A.film AND A.genre=G.idgenre
ORDER BY genre,titre

Variante avec du texte

6.3. Edition triée des films par genres

6.4. Edition du nombre de films par genre et avec du texte

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

Copyright © 2007 L. Danichert. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.