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

L. Danichert

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. ♪

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

- Auteur : L. Danichert

- Parution : 1 janvier 2007

- Licence : 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.

Sommaire

- I. Introduction

- II. CRÉATION DE LA BASE DE DONNÉES

- III. CRÉATION DES TABLES

- III-A. TABLE GENRE

- III-A-1. Ouvrir schéma public

- III-A-2. création des colonnes

- III-A-3. création d'une clé primaire

- III-A-4. Visualiser le contenu d'une table

- III-A-5. Le panneau sql affiche les commandes, pour la colonne idgenre :

- III-A-6. table année tabannée

- III-A-7. table film tabfilm

- III-B. RELATION UN À PLUSIEURS ENTRE DEUX TABLES

- III-B-1. Créer une clé étrangère

- III-B-2. RELATION PLUSIEURS À PLUSIEURS ENTRE DEUX TABLES

- III-B-2-a. table intermédiaire entre tabfilm et tagenre

- III-B-2-b. Ajouter les clés étrangères pour les liaisons avec tabfilm et tabgenre

- III-B-2-c. Créer une deuxième clé étrangère genre

- III-C. INSÉRER LES DONNÉES DANS LES TABLES

- III-C-1. Insérer les données

- III-C-2. Insérer les genres ligne par ligne pour le tableau genre

- III-C-3. Insérer des données dans tabannée

- III-C-4. Insérer des données dans tabfilm

- III-C-5. Insérer des données dans tab_film_genre

- III-D. POUR AFFICHER LE CONTENU D'UNE SEULE TABLE

- III-D-1. le contenu d'une table

- III-D-2. Le contenu d'une colonne d'une table

- III-D-3. Le contenu sélectionné d'une colonne d'une table

- III-D-4. Le contenu trié d'une colonne d'une table

- III-D-5. Même principe pour genre

- III-D-6. Compter le nombre de films

- III-E. AFFICHER LE CONTENU DE DEUX TABLES LIÉES PAR UNE CLÉ ÉTRANGÈRE

- III-E-1. Afficher le film et l'année de sortie

- III-E-2. afficher le film et l'année de sortie, tri sur l'année

- III-E-3. afficher le film et l'année de sortie, sur une année

- III-E-4. afficher le film et l'année de sortie, depuis une année

- III-F. AFFICHER LE CONTENU DE DEUX TABLES LIÉES PAR UNE TABLE INTERMÉDIAIRE DE TYPE n-n

- III-F-1. Édition triée des genres par films

- III-F-2. Édition triée des films par genres

- III-F-3. Édition triée des films par genres

- III-F-4. Édition du nombre de films par genre et avec du texte

I - 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 trois 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 4e tableau pour les relations multiples entre les films et les genres (Tab_film_genre).

Relations entre les tables

II - CRÉATION DE LA BASE DE DONNÉES

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

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

indiquer le nom de la base amdb

on obtient :

III - CRÉATION DES TABLES

III-A - TABLE GENRE

III-A-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

III-A-2 - création des colonnes

La table comporte deux 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 deux colonnes :genre et idgenre

III-A-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

III-A-4 - Visualiser le contenu d'une table

il n'y a rien, seules les colonnes apparaissent

III-A-5 - Le panneau sql affiche les commandes, pour la colonne idgenre :

III-A-6 - table année tabannée

Deux colonnes :

idannée type serial

année type nombreentier

cléannée clé primaire sur idannée

III-A-7 - table film tabfilm

et enfin la table tabfilm

idfilm : type serial, titre : type 40 caractères, annéesortie : type entier (clé étrangère, pointe sur la tabannée). )

cléfilm clé primaire colonne idfilm

III-B - RELATION UN À 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 »

III-B-1 - Créer une clé étrangère

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

Référence = la table tabannée

III-B-2 - RELATION PLUSIEURS À 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

III-B-2-a - table intermédiaire entre tabfilm et tagenre

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

avec 3 colonnes

la 1re pour la clé de la table key de type serial

la 2e pour pointer sur la clé du tableau tabfilm nom de la colonne film type entier

la 3e pour pointer sur la clé de tabgenre nom de la colonne genre type entier

clé primaire sur colonne key

III-B-2-b - 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.

III-B-2-c - 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

III-C - INSÉRER LES DONNÉES DANS LES TABLES

III-C-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

III-C-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');

III-C-3 - Insérer des données dans tabannée

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

III-C-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);

III-C-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

III-D - POUR AFFICHER LE CONTENU D'UNE SEULE TABLE

III-D-1 - le contenu d'une table

SELECT * from tabannée;

III-D-2 - Le contenu d'une colonne d'une table

SELECT année FROM tabannée;

III-D-3 - Le contenu sélectionné d'une colonne d'une table

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

III-D-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

III-D-5 - Même principe pour genre

III-D-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;

III-E - AFFICHER LE CONTENU DE DEUX TABLES LIÉES PAR UNE CLÉ ÉTRANGÈRE

III-E-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

III-E-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

III-E-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

III-E-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

III-F - AFFICHER LE CONTENU DE DEUX TABLES LIÉES PAR UNE TABLE INTERMÉDIAIRE DE TYPE n-n

tabfilm F

tab_film_genre A

tabgenre G

III-F-1 - Édition 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

III-F-2 - Édition 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

III-F-3 - Édition triée des films par genres

III-F-4 - Édition du nombre de films par genre et avec du texte