Contexte
Durée : 2h
Environnement de travail : psql
Pré-requis : Aucun
Impossible d'accéder à la ressource audio ou vidéo à l'adresse :
La ressource n'est plus disponible ou vous n'êtes pas autorisé à y accéder. Veuillez vérifier votre accès puis recharger la vidéo.
Lorsque vous devez implémenter une base de données relationnelle, le choix peut être complexe. De nombreux systèmes de gestion de bases de données existent : MySQL, PostgreSQL, Oracle Database, SQL Server, etc.
Comment s'y retrouver ? Chaque système a en effet ses avantages et ses inconvénients : certains sont plus performants quand le volume de données est faible, d'autres sont payants, d'autres encore proposent des fonctionnalités spécifiques à certains domaines métiers, etc.
Tout au long de ces modules, vous apprendrez à installer et à manipuler PostgreSQL, un système de gestion de bases de données libre développé depuis 1996. Il est reconnu pour sa performance, adapté pour la plupart des usages et largement adopté par les éditeurs d'applications.
Présentation et installation de PostgreSQL
Impossible d'accéder à la ressource audio ou vidéo à l'adresse :
La ressource n'est plus disponible ou vous n'êtes pas autorisé à y accéder. Veuillez vérifier votre accès puis recharger la vidéo.
Objectif
Installer et mettre en service PostgreSQL sur un système Linux.
Mise en situation
PostgreSQL est un des systèmes de gestion de bases de données relationnelles les plus utilisés aujourd'hui.
Si une application web comme DBFiddle permet d'essayer des SGBD comme PostgreSQL et de vérifier la validité des requêtes SQL, elle n'est pas suffisante pour une « vraie »
application.
En effet, une application réelle communique avec le SGBD grâce à du code, ce que ne permet pas l'interface de DBFiddle.
De plus, l'installation d'un SGBD sur une de vos machines permet de le configurer finement et d'optimiser les performances. Dans ce module, vous apprendrez à installer PostgreSQL sur un système Linux.
Avantage de PostgreSQL
PostgreSQL est :
un SGBDR,
libre (licence BSD),
multi-plate-formes (Unix, Linux, Windows, MacOS, etc.),
puissant,
très respectueux du standard SQL,
très bien documenté.
Fondamental : Documentation de PostgreSQL
La ressource de référence pour ce SGBDR mais aussi pour SQL est sa documentation :
En français : docs.postgresqlfr.org
Attention :
Une installation a minima de PostgreSQL uniquement à des fins de test et d'apprentissage, et pour un usage local est présentée ici. Pour mettre en production une base de données PostgreSQL sur le réseau, il faut suivre des directives supplémentaires, notamment pour assurer la sécurité du système, sa sauvegarde, etc. Ces thèmes ne sont pas abordés dans le cadre de ce module.
Fondamental : Installation de PostgreSQL
Une présentation complète de l'installation de PostgreSQL est disponible sur le document ici : https://www.postgresql.org/download/
Exemple : Installation sous Debian ou Ubuntu à partir des paquets de la distribution
Pour installer PostgreSQL sur des distribution de Linux de famille Debian comme Ubuntu, on peut exécuter la commande suivante en tant qu'administrateur :
apt-get update
apt install postgresql
La première commande met à jour les dépôt des paquets pour apt.
La seconde commande télécharge et installe le méta-package postgresql qui :
Installe le serveur
postgresqlet le client textuelpsql(ainsi que le client graphique pgAdmin),Lance le service
postgresql,Crée un utilisateur Linux
postgres(que l'on peut voir dans/etc/passwd),Crée également une base de données par défaut nommée
postgreset un utilisateur par défaut pour le SGBD nommépostgres.
On peut voir l'état de fonctionnement du serveur postgresql avec la commande :
systemctl status postgresql.service
Méthode : Tester son installation
sudo su postgres
psql
sudo su postgrespermet de devenir l'utilisateur Linux postgres qui a été créé lors de l'installation.psqlpermet d'établir la connexion par défaut entre le client psql et le serveur postgresql en utilisant l'utilisateur Linux et une base de données du même nom, donc ici postgres.Le client connecté affiche son numéro de version, propose de taper help et une invite de commande
postgres=#.\lpermet d'afficher la liste des base de données existantes (on retrouve la base postgres à laquelle on est connecté).\qpermet de quitter.
Complément : Commande SHOW ALL ;
La commande SHOW ALL; permet de voir tous les paramètres du serveur PostgreSQL.
Par exemple data_directory permet de connaître le répertoire de stockage utilisé sur le disque dur.
Impossible d'accéder à la ressource audio ou vidéo à l'adresse :
La ressource n'est plus disponible ou vous n'êtes pas autorisé à y accéder. Veuillez vérifier votre accès puis recharger la vidéo.
Appliquer la notion
Installer PostgreSQL.
apt-get update
apt install postgresql
Reading package lists... Done
Building dependency tree
Reading state information... Done
Suggested packages:
postgresql-doc
The following NEW packages will be installed:
postgresql
0 upgraded, 1 newly installed, 0 to remove and 93 not upgraded.
Need to get 5,392 B of archives.
After this operation, 59.4 kB of additional disk space will be used.
Get:1 http://archive.ubuntu.com/ubuntu xenial-updates/main amd64 postgresql
all 9.5+173ubuntu0.3 [5,392 B]
Fetched 5,392 B in 0s (120 kB/s)
debconf: delaying package configuration, since apt-utils is not installed
Selecting previously unselected package postgresql.
(Reading database ... 30282 files and directories currently installed.)
Preparing to unpack .../postgresql_9.5+173ubuntu0.3_all.deb ...
Unpacking postgresql (9.5+173ubuntu0.3) ...
Setting up postgresql (9.5+173ubuntu0.3) ...
Comment obtenir des informations sur le statut du service PostgreSQL ?
systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Wed 2020-05-06 12:11:15 UTC; 2s ago
Process: 6194 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 6194 (code=exited, status=0/SUCCESS)
Le service est bien chargé dans le système (Loaded: loaded) et il est bien actif (Active: active).
Le client textuel « psql »
Impossible d'accéder à la ressource audio ou vidéo à l'adresse :
La ressource n'est plus disponible ou vous n'êtes pas autorisé à y accéder. Veuillez vérifier votre accès puis recharger la vidéo.
Objectif
Savoir dialoguer avec PostgreSQL via un terminal.
Mise en situation
Une fois PostgreSQL installé sur votre système, il y a deux manières de l'utiliser.
La première est d'écrire du code qui communique directement avec PostgreSQL, et c'est ce que vous ferez si vous développez une application web.
La seconde est de dialoguer avec PostgreSQL à l'aide d'un terminal et de commandes SQL, comme vous le feriez avec un outil comme DBFiddle.
Pour ce faire, on utilisera le shell PosgtreSQL, ou psql.
Définition : psql
psql est le client textuel de PostgreSQL. C'est une interface en ligne de commande ou CLI (pour command line interface).
Il permet une utilisation interactive de PostgreSQL.
Méthode : Connexion à un serveur
Le plus souvent, on interagit avec un serveur PostgreSQL distant. Pour initier la connexion avec psql, on utilise la commande suivante :
psql -h adresse.serveur -U user -d mydb
Cette commande opère la connexion avec la machine d'adresse adresse.serveur avec un utilisateur PostgreSQL nommé user à la base de données mydb.
Pour que cette commande fonctionne, il faut que :
Le serveur PostgreSQL s’exécute sur la machine d'adresse
adresse.serveur(sur le port standard5432) ;L'utilisateur
userexiste sur PostgreSQL et avoir un mot de passe défini ;Une base de données
mydbexiste sur PostgreSQL et que l'utilisateurusery ait accès ;Le mot de passe de l'utilisateur
usersoit renseigné.
Lorsque l'on est connecté au serveur on obtient le prompt de la forme :
psql (9.5.21)
Type "help" for help.
user=>
À partir de là, on peut saisir des requêtes SQL mais aussi des commandes psql.
On sort de psql avec :
user=> \q
Syntaxe : Écrire une instruction SQL
Une requête SQL peut être directement exécutée. On veillera à ne pas oublier le point virgule «
qui indique la fin de celle-ci.; »
user=> SELECT * FROM matable ;
Syntaxe : Écrire une instruction SQL sur plusieurs lignes
Une instruction SQL peut s'écrire sur une ou plusieurs lignes, le « retour chariot »
« ; »
user=> SELECT *
user-> FROM matable
user-> ;
On notera dans psql la différence entre les caractères «
et => »«
selon que l'on a ou pas effectué un retour chariot.-> »
Fondamental : Commandes de base : aide
\? : Liste des commandes psql
\h : Liste des instructions SQL
\h CREATE TABLE : Description de l'instruction SQL CREATE TABLE
Fondamental : Commandes de base : catalogue
\d : Liste des relations (catalogue de données)
\d maTable : Description de la relation maTable
Complément : Documentation de psql
Pour obtenir toutes les options sur psql, on pourra se référer à la documentation de ce client ici :
Complément : pgAdmin 4
Le client pgAdmin4 est un client graphique disponible sous Linux et sous Windows.
pgAdmin4 offre une interface graphique permettant d'effectuer les mêmes opérations qu'avec le client psql et offrant une navigation simplifiée et un monitoring facilité.
Impossible d'accéder à la ressource audio ou vidéo à l'adresse :
La ressource n'est plus disponible ou vous n'êtes pas autorisé à y accéder. Veuillez vérifier votre accès puis recharger la vidéo.
Appliquer la notion
Créer la table tabledetest avec pour seul attribut un id de type entier via psql en exécutant une requête SQL.
Lister les différentes tables présentes dans la base de données pour s'assurer de sa création.
On veillera à ne pas oublier le point-virgule pour exécuter la requête.
CREATE TABLE tabledetest (id INTEGER);
On peut ensuite lister les tables avec :
\d
on a :
List of relations
Schema | Name | Type | Owner
--------+-------------+-------+----------
public | tabledetest | table | postgres
(1 row)
Supprimer maintenant cette table.
On supprime la table avec :
DROP TABLE tabledetest;
Comment peut-on avoir un aperçu du tampon ayant stocké la dernière requête ?
On parcourra la page de manuel de psql : https://docs.postgresql.fr/9.6/app-psql.html
On a le tampon de la dernière commande avec la commande \p ou \print.
postgres=# \p
DROP TABLE TableDeTest;
Créer des bases de données et des utilisateurs
Impossible d'accéder à la ressource audio ou vidéo à l'adresse :
La ressource n'est plus disponible ou vous n'êtes pas autorisé à y accéder. Veuillez vérifier votre accès puis recharger la vidéo.
Objectifs
Comprendre le concept d'utilisateur et de permissions au sens PostgreSQL.
Savoir créer des bases de données et des utilisateurs.
Mise en situation
Une installation PostgreSQL peut gérer plusieurs bases de données. Quand a-t-on besoin de plusieurs bases de données pour une même application ? Un grand classique consiste à utiliser une base de données pour les tests, et une base de données pour la production. Qui dit base de données différente dit aussi utilisateurs différents et autorisations différentes : on peut imaginer que la base de test soit accessible à plus d'utilisateurs que la base de production.
Sur PostgreSQL, une base et un utilisateurs nommés « postgres »
sont créés par défaut. Dans ce module, vous apprendrez à créer de nouvelles bases et de nouveaux utilisateurs avec une syntaxe proche de SQL.
Syntaxe : Créer un utilisateur
CREATE USER user1 PASSWORD 'password';
Syntaxe : Créer une base de données
CREATE DATABASE mydb OWNER user1;
La clause OWNER permet de spécifier le propriétaire (owner) de la base de données. Celui-ci a tous les droits sur sa base de données. Il pourra créer, modifier et détruire les tables de le base de données.
Syntaxe : psql : catalogues des utilisateurs et des bases de données
psql dispose de commandes pour consulter les catalogues des utilisateurs et des bases de données :
\du: liste des utilisateurs,\l: liste des bases de données.
Syntaxe : psql : changer d'utilisateur
Si on est connecté à une base de donnée avec un utilisateur et que l'on veut changer d'utilisateur et/ou de base, avec psql on utilise :
\c db user: pour se connecter à la base db avec le compte user.
Impossible d'accéder à la ressource audio ou vidéo à l'adresse :
La ressource n'est plus disponible ou vous n'êtes pas autorisé à y accéder. Veuillez vérifier votre accès puis recharger la vidéo.
Appliquer la notion
Cet exercice va permettre de découvrir la création de rôle et de psql.
On parcourra la page de manuel de psql: https://docs.postgresql.fr/9.6/app-psql.html
Créer un utilisateur nommé paul avec un mot de passe de votre choix et une base éponyme dont il est le propriétaire.
On utilise :
CREATE USER paul WITH PASSWORD 'wVyHZYpiRFzKK03jnrJJJADJ2IBsk1ufQfxwAexF/1U=';
CREATE DATABASE paul OWNER paul;
Lister les bases de données disponibles.
Quelle autre base de données d'un utilisateur différent apparaît ?
On utilise :
\l
On obtient :
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
paul | paul | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
La base de données de l'utilisateur postgres apparaît aussi.
Changer le propriétaire de la base de données paul pour postgres.
On utilise :
ALTER DATABASE paul OWNER TO postgres;
Exécuter des instructions SQL depuis un fichier
Impossible d'accéder à la ressource audio ou vidéo à l'adresse :
La ressource n'est plus disponible ou vous n'êtes pas autorisé à y accéder. Veuillez vérifier votre accès puis recharger la vidéo.
Objectif
Savoir exécuter des instructions SQL depuis un fichier.
Mise en situation
Il est assez fréquent d'avoir une liste d'instructions SQL pré-définies à exécuter.
Vous pourriez recevoir le code SQL pour créer la structure d'une base de données à des fins de test, par exemple. Ou vous pourriez vouloir migrer une base de données de MySQL vers une base de données PostgreSQL, ce qui se traduira par l'exécution d'un long code SQL pour reconstruire la structure des tables et insérer les données dans PostgreSQL.
Dans ces cas, il est intéressant d'exécuter un fichier contenant une liste de requêtes SQL, plutôt que de les entrer une par une dans un client, ou de les copier-coller.
Syntaxe :
La commande \i monfichier permet d'exécuter les commandes SQL contenues dans le fichier monfichier.
user=> \i chemin/fichier.sql
Méthode : Programmer une base de données avec PostgreSQL
Pour programmer une base de données sous PostgreSQL, voici une méthode générale :
Rendez-vous dans un répertoire de travail :
cd /home/me/bdd1.Créez ou ouvrez un fichier texte
bdd.sqlavec un éditeur de texte.Ouvrez un terminal et exécutez votre client psql.
Écrivez votre code SQL, et testez-le au fur et à mesure :
\i.bdd.sql
Conseil : Tester votre code régulièrement
Afin de tester régulièrement votre base de données, pensez à insérer en début de script des instructions de destruction des tables.
On supprime les tables dans l'ordre inverse de leur création, on peut ajouter la clause IF EXISTS afin d'éviter les erreurs lorsqu'une exécution précédente avait déjà échoué.
DROP TABLE IF EXISTS t2 ;
DROP TABLE IF EXISTS t1 ;
CREATE TABLE t1 (a VARCHAR PRIMARY KEY);
CREATE TABLE t2 (a VARCHAR REFERENCES t1(a));
Impossible d'accéder à la ressource audio ou vidéo à l'adresse :
La ressource n'est plus disponible ou vous n'êtes pas autorisé à y accéder. Veuillez vérifier votre accès puis recharger la vidéo.
Appliquer la notion
On se donne les données suivantes :
nom_film | num_salle | prix | date_projection | horaire_projection |
I Origins | 12 | 7.90 | 20 Mars 2020 | 13:37 |
Drive | 47 | 8.90 | 15 Août 2020 | 13:45 |
Mr Nobody | 12 | 16.90 | 4 Mai 2020 | 7:20 |
Écrire la requête permettant de créer cette table.
CREATE TABLE ticket(
nom_film VARCHAR(24) PRIMARY KEY,
num_salle INTEGER,
prix FLOAT(2),
date_projection DATE,
horaire_projection TIME);
Écrire les requêtes permettant d'insérer les données.
On utilise :
INSERT INTO ticket VALUES('I Origins', 12, 7.90, '2020-03-20', '13:37');
INSERT INTO ticket VALUES('Drive', 47, 8.90, '2020-08-15', '13:45');
INSERT INTO ticket VALUES('Mr Nobody', 12, 16.90, '2020-05-04', '7:20');
Mettre en place un fichier film.sql qui permettra d'exécuter toutes ces requêtes en même temps et de lire le contenu de la table créée à la fin.
On veillera à supprimer la table en début de script si elle existe.
DROP TABLE IF EXISTS Ticket;
CREATE TABLE ticket(
nom_film VARCHAR(24) PRIMARY KEY,
num_salle INTEGER,
prix FLOAT(2),
date_projection DATE,
horaire_projection TIME);
INSERT INTO ticket VALUES('I Origins', 12, 7.90, '2020-03-20', '13:37');
INSERT INTO ticket VALUES('Drive', 47, 8.90, '2020-08-15', '13:45');
INSERT INTO ticket VALUES('Mr Nobody', 12, 16.90, '2020-05-04', '7:20');
SELECT * FROM ticket;
nom_film | num_salle | prix | date_projection | horaire_projection
-----------+-----------+------+-----------------+--------------------
I Origins | 12 | 7.9 | 2020-03-20 | 13:37:00
Drive | 47 | 8.9 | 2020-08-15 | 13:45:00
Mr Nobody | 12 | 16.9 | 2020-05-04 | 07:20:00
(3 rows)
Documentation de PostgreSQL
Impossible d'accéder à la ressource audio ou vidéo à l'adresse :
La ressource n'est plus disponible ou vous n'êtes pas autorisé à y accéder. Veuillez vérifier votre accès puis recharger la vidéo.
Objectif
Savoir trouver des informations sur SQL et PostgreSQL.
Mise en situation
Les systèmes de gestion de bases de données sont des outils complets et complexes. Si leur configuration par défaut suffit pour la plupart des usages, il est nécessaire de la modifier pour améliorer la performance de certaines applications. Des cas typiques pourraient être un grand volume de données, ou une base avec beaucoup de lectures et peu d'écritures, etc.
Lorsque l'on a un problème ou une question, se référer à la documentation de la technologie utilisée est le meilleur point de départ. La documentation officielle de PostgreSQL est la ressource la plus complète qui existe. Elle est disponible pour toutes les versions supportées de PostgreSQL.
Fondamental : Documentation en ligne
La documentation de PostgreSQL est hébergée sur un serveur dédié :
en français : docs.postgresql.fr/.
Méthode : Fonction de recherche
La fonction de recherche de la documentation en ligne permet de chercher un mot-clé particulier pour une version ou toutes les versions de PostgreSQL.
Fondamental : Documentation en manuel
Une version de la documentation peut directement être consultée via le terminal avec :
apt install PostgreSQL-doc
On peut y avoir accès avec la commande :
man postgres
Fondamental : Documentation pdf
Une version de la documentation en disponible au format PDF en téléchargement en ligne ici : https://docs.PostgreSQL.fr/12/pg12.pdf
Chapitres sur SQL
La partie 2 contient des chapitres sur les spécificités de SQL auxquels on peut se référer lorsque l'on débute. En particulier, on se référera :
Au chapitre 4 sur la syntaxe SQL,
Au chapitre 5 sur la définition des données,
Au chapitre 6 sur la manipulation des données,
Au chapitre 7 sur les requêtes.
Chapitres sur PostgreSQL
La partie 3 contient des chapitres sur les spécificités de PostgreSQL auxquels on peut se référer lorsque l'on débute. En particulier, on se référera :
Au chapitre 19 sur la configuration du serveur,
Au chapitre 20 sur l'authentification du client,
Au chapitre 21 sur les rôles de la base de données,
Au chapitre 22 sur l'administration de la base de données.
Impossible d'accéder à la ressource audio ou vidéo à l'adresse :
La ressource n'est plus disponible ou vous n'êtes pas autorisé à y accéder. Veuillez vérifier votre accès puis recharger la vidéo.
Appliquer la notion
À quoi correspond le type cidr
La solution se trouve dans le chapitre 8.
C'est un type utilisé pour stocker une adresse réseau IPv4 ou IPv6.
À quoi sert un tablespace ?
La solution se trouve dans le chapitre 22.
Un tablespace sert à spécifier emplacements sur le disque d'une installation PostgreSQL.
Quel fichier stocke les informations pour l'authentification du client ?
La solution se trouve dans le chapitre 20.
Il s'agit du fichier pg_hba.conf.
Essentiel
Impossible d'accéder à la ressource audio ou vidéo à l'adresse :
La ressource n'est plus disponible ou vous n'êtes pas autorisé à y accéder. Veuillez vérifier votre accès puis recharger la vidéo.
L'installation de PostgreSQL sous Linux est très simple : en une seule commande, vous obtenez un système prêt à être utilisé à des fins de tests.
Le shell PostgreSQL, inclus dans l'installation, est très puissant. En plus d'autoriser l'exécution des requêtes SQL classiques, contenues dans un fichier ou entrées à la main, il permet de créer de nouvelles bases de données et de nouveaux utilisateurs.
PostgreSQL intègre également un systèmes de permissions, qui indique finement ce que les utilisateurs peuvent faire ou ne pas faire pour telle ou telle base de données.
Avant de mettre un production un système PostgreSQL, il faudra s'assurer que la configuration répond bien à vos besoins, et pour ce faire une documentation très complète est disponible en ligne ou directement depuis votre terminal.
Auto-évaluation
Exercice final
Quiz - Culture
Qu'est-ce qu'une CLI ?
Une interface en ligne de commande
Un type propre à PostgreSQL
Un utilisateur par défaut de PostgreSQL
Parmi les propositions suivantes, lesquelles sont des clients de PostgreSQL ?
psql
postgresql
pgAdmin4
Un client lourd est un client... :
Dont la taille une fois installé est plus importante que celle d'un client léger.
Qui repose sur les ressources de la machine qui l'utilise.
Est un client ancien qui n'est plus utilisé.
Quiz - Méthode
On crée généralement plusieurs utilisateurs... :
Pour partager l'usage du serveur entre plusieurs bases de données.
Pour avoir une gestion fine des droits.
Parce qu'il faut nécessairement un propriétaire pour une table.
Parce qu'il faut nécessairement un propriétaire différent pour chaque table.
On crée plusieurs bases de données... :
Car il en faut autant que de tables.
Car il en faut nécessairement une par utilisateur.
Pour permettre d'isoler différentes tables d'une application donnée.
Les bonnes premières choses à réaliser lors de la mise en place d'un serveur pour une application sont :
La création d'un nouvel utilisateur qui n'a pas tous les droits de l'utilisateur par défaut postgres.
La création d'une nouvelle base de données pour cette application.
Le changement du mot de passe de l'utilisateur par défaut postgres.
Quiz - Code
On exécute avec succès, via psql, cette instruction :
\copy fleur (variete, couleur) FROM '/tmp/orchidees.csv' WITH DELIMITER ','
Que peut-on en déduire ?
Une table fleur a été créé par cette instruction.
Il existe une table fleur avec au moins deux colonnes dans la base de données.
Les données d'un fichier CSV orchidees.csv ont été importées dans la table fleur.
Le nombre d'enregistrements dans la table fleur est égal au nombre d'enregistrements présents dans le fichier orchidees.csv.
Que permet de réaliser la commande : \timing sous psql ?
Elle indique le temps d'exécution de la dernière commande.
Elle active l'affichage du temps d'exécution des prochaines commandes.
Elle donne le temps d'exécution de toutes les commandes exécutées dans la session.
Que permet de réaliser la commande : \e sous psql ?
Elle permet d'éditer la dernière commande entrée dans la session psql via l'éditeur de texte par défaut.
Elle liste les paramètres du système qui ont été manuellement changés.
Elle liste tous les encodages utilisés dans la base de données courante.
Elle retourne la liste des événements importants de sécurité du SGBD.
Que permet de réaliser la commande : \H sous psql ?
Elle fournit de l'aide sur les commandes SQL.
Elle fournit de l'aide sur les commandes psql.
Elle présente les résultats des requêtes au format HTML.
Quiz - Culture
Qu'est-ce qu'une CLI ?
Une interface en ligne de commande
Un type propre à PostgreSQL
Un utilisateur par défaut de PostgreSQL
Parmi les propositions suivantes, lesquelles sont des clients de PostgreSQL ?
psql
postgresql
pgAdmin4
psql
postgresql
C'est le nom du programme serveur de PostgreSQL.
pgAdmin4
Un client lourd est un client... :
Dont la taille une fois installé est plus importante que celle d'un client léger.
Qui repose sur les ressources de la machine qui l'utilise.
Est un client ancien qui n'est plus utilisé.
Quiz - Méthode
On crée généralement plusieurs utilisateurs... :
Pour partager l'usage du serveur entre plusieurs bases de données.
Pour avoir une gestion fine des droits.
Parce qu'il faut nécessairement un propriétaire pour une table.
Parce qu'il faut nécessairement un propriétaire différent pour chaque table.
On crée plusieurs bases de données... :
Car il en faut autant que de tables.
Car il en faut nécessairement une par utilisateur.
Pour permettre d'isoler différentes tables d'une application donnée.
Les bonnes premières choses à réaliser lors de la mise en place d'un serveur pour une application sont :
La création d'un nouvel utilisateur qui n'a pas tous les droits de l'utilisateur par défaut postgres.
La création d'une nouvelle base de données pour cette application.
Le changement du mot de passe de l'utilisateur par défaut postgres.
Quiz - Code
Une table fleur a été créé par cette instruction.
Il existe une table fleur avec au moins deux colonnes dans la base de données.
Les données d'un fichier CSV orchidees.csv ont été importées dans la table fleur.
Le nombre d'enregistrements dans la table fleur est égal au nombre d'enregistrements présents dans le fichier orchidees.csv.
Une table fleur a été créé par cette instruction.
\copy ne crée pas la table, elle copie des données dans une table existante.
Il existe une table fleur avec au moins deux colonnes dans la base de données.
Les données d'un fichier CSV orchidees.csv ont été importées dans la table fleur.
Le nombre d'enregistrements dans la table fleur est égal au nombre d'enregistrements présents dans le fichier orchidees.csv.
Il est possible que des enregistrements étaient présents avant l'ajout de données.
Que permet de réaliser la commande : \timing sous psql ?
Elle indique le temps d'exécution de la dernière commande.
Elle active l'affichage du temps d'exécution des prochaines commandes.
Elle donne le temps d'exécution de toutes les commandes exécutées dans la session.
Que permet de réaliser la commande : \e sous psql ?
Elle permet d'éditer la dernière commande entrée dans la session psql via l'éditeur de texte par défaut.
Elle liste les paramètres du système qui ont été manuellement changés.
Elle liste tous les encodages utilisés dans la base de données courante.
Elle retourne la liste des événements importants de sécurité du SGBD.
Que permet de réaliser la commande : \H sous psql ?
Elle fournit de l'aide sur les commandes SQL.
Elle fournit de l'aide sur les commandes psql.
Elle présente les résultats des requêtes au format HTML.
Elle fournit de l'aide sur les commandes SQL.
C'est la commande \h (minuscule) qui fait cela.
Elle fournit de l'aide sur les commandes psql.
C'est la commande \? qui fait cela.
Elle présente les résultats des requêtes au format HTML.
Défi
On dispose des informations suivantes sur des étudiants :
pknumsecu | knumetu | nom | prenom |
1800675001066 | AB3937098X | Dupont | Pierre |
2820475001124 | XGB67668 | Durand | Anne |
pknumsecu est une clé primaire qui contient exactement 13 caractères, knumetu est une chaîne de caractères d'au plus 20 caractères qui ne peut être nulle.
On dispose de plus des informations suivantes sur des cours :
pkcode | fketu |
NF17 | 1800675001066 |
NF26 | 1800675001066 |
NF29 | 1800675001066 |
pkcode est une chaîne de 4 caractères exactement et fketu est une clé étrangère référençant pknumsecu. Ces deux attributs forment la clé primaire.
Créer dans la base de données les tables etu et cours, et y ajouter les données.
CREATE TABLE etu (
pknumsecu CHAR(13) PRIMARY KEY,
knumetu VARCHAR(20) UNIQUE NOT NULL,
nom VARCHAR,
prenom VARCHAR);
CREATE TABLE cours (
pkcode CHAR(4) NOT NULL,
fketu CHAR(13) NOT NULL,
PRIMARY KEY (pkcode, fketu),
FOREIGN KEY (fketu) REFERENCES etu(pknumsecu)
);
INSERT INTO etu (pknumsecu, knumetu, nom, prenom)
VALUES ('1800675001066', 'AB3937098X', 'Dupont', 'Pierre');
INSERT INTO etu (pknumsecu, knumetu, nom, prenom)
VALUES ('2820475001124', 'XGB67668', 'Durand', 'Anne');
INSERT INTO cours (pkcode, fketu)
VALUES ('NF17', '1800675001066');
INSERT INTO cours (pkcode, fketu)
VALUES ('NF26', '1800675001066');
INSERT INTO cours (pkcode, fketu)
VALUES ('NF29', '1800675001066');
Via psql, quelles sont les commandes pour accéder au catalogue et vérifier la création de la table ?
\d
\d etu
\d cours
Utiliser deux instructions SELECT pour vérifier le contenu de la table.
SELECT *
FROM etu;
SELECT *
FROM cours;
Nous allons à présent réinitialiser la base avec des données contenues dans un fichier.
Exécuter les instructions nécessaires afin de supprimer les données existantes dans les tables et non les tables.
Vérifier que les tables sont vides.
On utilisera l'instruction DELETE du SQL.
DELETE FROM cours;
SELECT * FROM cours;
DELETE FROM etu;
SELECT * FROM etu;
Ce sont des fichiers CSV qui contiennent respectivement des informations sur les étudiants et sur les cours.
Exécuter le code suivant en remplaçant /tmp par l'emplacement des fichiers sur notre ordinateur.
\copy etu (pknumsecu, knumetu, nom, prenom) FROM '/tmp/gi.csv' WITH CSV DELIMITER ';' QUOTE '"'
\copy cours (fketu, pkcode) FROM '/tmp/P2018.csv' WITH CSV DELIMITER ';' QUOTE '"'
Montrer que de nouvelles données ont été insérées dans les tables etu et cours.
SELECT *
FROM etu;
pknumsecu | knumetu | nom | prenom
---------------+------------+----------+---------
1800675001066 | AB3937098X | Dupont | Pierre
2820475001124 | XGB67668 | Durand | Anne
1 | A | Dupont | Pierre
2 | B | Durand | Georges
3 | C | Duchemin | Paul
4 | D | Dugenou | Alain
5 | E | Dupied | Albert
SELECT *
FROM cours;
pkcode | fketu
--------+---------------
NF17 | 1800675001066
NF26 | 1800675001066
NF29 | 1800675001066
NF17 | 1
NF18 | 1
NF19 | 1
NF20 | 1
LA13 | 1
PH01 | 1
NF17 | 2
NF18 | 2
NF19 | 2
TN01 | 2
LA14 | 2
PH01 | 2
NF17 | 3
NF18 | 3
NF19 | 3
NF21 | 3
LA14 | 3
PH01 | 3