bts informatique de gesyion

TP n°1 - Installation du SGBDR PostgreSQL sous Linux Ubuntu
SLAM3 – 2016/2017
Pré-requis : Vous devez disposer d'une machine virtuelle Ubuntu Server fonctionnelle (voir TP0) et réaliser
un clone de celle-ci que vous nommerez serveur-postgres.
1
Installation de PostgreSQL sur votre serveur virtuel
1. Sur votre serveur virtuel, installez le paquet « postgresql »
2. L'installation ajoute un nouvel utilisateur linux et un utilisateur de la base de données
nommé postgres. Il est seul autorisé, pour l'instant, à se connecter à la base de données. Il ne
possède pas de mot de passe, et par mesure de sécurité nous ne lui en attribuerons pas. Nous
allons créer un nouvel utilisateur. Connectez avec l'utilisateur postgres en utilisant la
commande suivante :
sudo -i -u postgres
3. Lancez l'invite de commande Postgresql à l'aide de la commande psql.
4. Vous êtes désormais connecté à Postgresql en mode administrateur.
5. Créer un utilisateur nommé test (comme votre utilisateur linux)
CREATE USER test ;
6. Donnez-lui le droit de créer un base de données.
ALTER ROLE test WITH CREATEDB ;
7. Créez un base portant le même nom que l'utilisateur :
CREATE DATABASE test OWNER test ;
8. Ajoutez un mot de passe à votre utilisateur
ALTER USER test WITH ENCRYPTED PASSWORD 'password' ;
Personnalisez ici votre mot de passe pour sécuriser votre base.
9. Quittez Postgresl avec \q
Quittez l'utilisateur postgres avec exit
Lancez Postgresql avec la command psql (normalement vous êtes automatiquement
connecté avec la base de données test).
A vous de jouer
Une fois connecté avec votre compte test à Postgresql, vous pouvez utiliser les commandes
suivantes :
\h pour obtenir de l'aide sur les commandes SQL
\? pour obtenir de l'aide sur les commandes Postgresql
\q pour quitter
Quelle commande devez-vous taper pour :
1. afficher la liste des bases de données dans Postgresql ?
2. afficher les tables de la base courante ?
3. créer une nouvelle base de données nommée test2 (voir annexe 1) ?
4. vous connecter à la nouvelle base de données ?
5. Afficher la liste des utilisateurs dont le nom commence par « t ».
2
Connexion à l'aide d'un client (votre machine hôte)
Par défaut, votre serveur ne répond qu'aux requêtes provenant de lui-même. Pour se connecter
depuis une machine distante, il faut modifier les fichiers de configuration de Postgresql sur votre
serveur.
1. En mode super-utilisateur, éditez le fichier /etc/postgresql/9.5/main/postgresql.conf.
2. Modifiez la ligne suivante pour autoriser n'importe qu'elle machine à se connecter
listen_addresses = '*' (n'oubliez d'enlever le commentaire #)
3. Editez également le fichier /etc/postgresql/9.5/main/pg_hba.conf
4. Ajoutez la nouvelle ligne suivante pour autoriser les connexions provenant de tout le réseau
des salles 121 et 122 :
host all all 172.17.199.0/24
md5
host all all 172.17.122.0/24
md5
5. Redémarrez le service postgresql à l'aide de la commande
sudo service postgresql restart
6. Récupérez l'adresse IP de votre serveur (avec ifconfig):
7. Sur votre machine hôte, installez le paquet postgresql-client
8. Connectez-vous depuis la machine hôte avec la commande suivante :
psql -h 172.17.___.___ test test
9. Lorsque tout fonctionne bien, appelez-moi et réalisez un instantané de la machine virtuelle.
3
Installation d’un client graphique
1. Les dépôts Ubuntu contiennent une application graphique d’administration d’une base de
données : PgAdmin. Installez le paquet pgadmin3 sur votre machine hôte.
2. Connectez-vous à votre serveur « virtuel » de base de données. Les manipulation de la
section précédentes doivent fonctionner pour que cela fonctionne.
A vous de jouer
Comment installer le client web phppgadmin sur le serveur et qu'il soit accessible depuis
n'importe quelle machine du réseau?
La procédure est à chercher sur internet !
4
Création d'une base de données
A travers la création d’une base de données, testez les possibilités des différentes interfaces (ligne
de commande, pgadmin, phppgadmin).
1. En vous aidant de l'annexe 1, créez à l'aide de Postgresql la base de données ayant le schéma
relationnel suivant :
SALARIE (Matricule, Nom, Prenom, DateDerniereFormation)
DEMANDER (#Matricule, #CodeFormation, DateDemande, AvisSuperviseur)
SUIVRE ( #Matricule, #CodeFormation)
FORMATION ( CodeFormation, LibelleFormation, ...)
2. Insérez quelques lignes inventées dans chaque table.
3. Répondez aux questions suivantes en SQL, puis testez vos requêtes sur votre serveur :
◦ Liste des noms de salariés avec la date de leur dernière formation.
◦ Liste des formations qui n'ont jamais été demandée depuis le 01/01/2013.
◦ Liste des formations (libellé de la formation, date de la demande et avis du superviseur)
demandées par le salarié de matricule E540 depuis le début de l’année 2011.
◦ Liste des salariés (nom et prénom du salarié, avis du superviseur) n’ayant pas été admis à suivre
la formation de code F116.
Annexe 1 (Gestion des tables en SQL)
1. CRÉER UNE BASE
CREATE
DATABASE
nom_base_de_données
[ [ OWNER [=] propriétaire_de_la_base ]
[ TABLESPACE [=] tablespace ] ] ;
Tablespace : Cela définit l’espace de travail
par défaut dans lequel les objets de la base
de données seront rangés.
SUPPRIMER UNE BASE
C’est une opération très dangereuse qui ne peut être réalisée que par le propriétaire.
DROP DATABASE nom_base_de_données ;
2. GÉRER UNE TABLE
2.1. CRÉER UNE TABLE
CREATE TABLE nom_table
Exemple 1 :
(champ1 type contraintes de create table client
(numcli integer primary key,
colonne,
nomcli char(20),
...
remise real);
champn type,
contraintes de tables);
Les contraintes principales sont : Exemple 2 :
create table lignecommande
– primary key,
(numclient integer,
– foreign key,
numcommande integer,
– check, ...
quantite integer,
primary key (numcli, numcommande),
foreign key (numclient) references client(numcli),
foreign key (numcommande) references cde(numcde));
2.2. SUPPRIMER UNE TABLE
DROP
TABLE
[CASCADE] ;
nom_table Attention, on peut supprimer une table
pleine, mais on ne peut pas supprimer une
table qui est référencée dans d'autres tables
(par REFERENCES).
2.3. MODIFIER UNE TABLE
ALTER TABLE nomtable
Exemple :
ADD ou DROP ...;
alter table client
add column service char(10);
ALTER TABLE nomtable
RENAME champ TO nouveaunomchamp ;
ALTER TABLE nomtable
RENAME TO nouveaunomtable ;
ALTER TABLE nomtable
SET SCHEMA nouveauschema;
Il existe bien d'autres options. Consulter
l'aide de Postgres pour en savoir plus.
2.4. INSÉRER DANS UNE TABLE
INSERT INTO nom_table
VALUES
(valeurchamp1,
valeurchampn) ;
Exemple :
..., insert into client
values (101, 'toto', 0.1, 12);
2.5. SUPPRIMER DANS UNE TABLE
DELETE FROM nom_table
WHERE condition ;
Exemple :
delete from client
where numcli = 101;
2.6. MODIFIER DANS UNE TABLE
UPDATE nom_table
SET champ = valeur
WHERE condition ;
Exemple :
update client
set nom = 'titi'
where numcli = 101