Atelier offert par Guillaume Larocque, professionnel de recherche au CSBQ.

Lien vers la présentation Prezi

• PostgreSQL Reference Manual
• Beginning databases with PostgreSQL
• PostgreSQL wiki
• PostgreSQL Tutorial
• SQL Murder Mystery

• Visitez la page de PostgreSQL et téléchargez la version appropriée pour votre plate-forme. Choisissez la version 64 bits.
• Éxécutez le fichier et suivez les étapes d'installation en acceptant les options par défaut.
• Quand on vous le demande, choisissez un mot de passe approprié pour l'utilisateur postgres. Prenez en note ce mot de passe.

Dans un terminal de commande, tapez:

sudo apt-get install postgresql libreoffice libreoffice-sdbc-postgresql

Il est possible que la version de PostgreSQL disponible pour votre distribution soit antérieure à 9.5. Dans ce cas, installez la version disponible.

Pour d'autres distributions, vérifiez cette page.

Sur Windows et Mac:

• Trouvez PostgreSQL dans le menu démarrer/Finder et cliquez sur “SQL shell (psql)”
• Acceptez les paramètres par défaut en cliquant sur ENTER et tapez le mot de passe approprié. Vous ne verrez rien lorsque vous taperez votre mot de passe, c'est normal.

Sur Linux Tapez simplement:

psql

dans un terminal.

Si ça ne fonctionne pas sur Mac ou Linux...

sudo launchctl unload /Library/LaunchDaemons/com.edb.launchd.postgresql-9.6.plist

sudo launchctl load /Library/LaunchDaemons/com.edb.launchd.postgresql-9.6.plist

Autres instructions sous Linux...

     $ sudo -u postgres psql
     postgres=> alter user postgres password 'apassword';
     postgres=> create user yerusername createdb createuser password 'somepass';
     postgres=> create database yerusername owner yerusername;

createuser -P -s -e your_linux_username

psql

SHOW hba_file;

local all postgres peer

local all postgres md5

psql worshop -U your_username -W

Cliquez ici pour télécharger le jeu de données (feuille de calcul en format LibreOffice Calc)

Si vous voulez, vous pouvez créer un nom d'usager autre que 'postgres'. Pour ce faire, tapez la commande suivante dans psql, en remplacant nom_usager et mot_de_passe par des valeurs choisies.

CREATE USER your_username WITH SUPERUSER PASSWORD 'your_password';

Créer une base de données 'atelier'

CREATE DATABASE atelier;

Étape important afin de sécurisé l'accès.

REVOKE CONNECT ON DATABASE atelier FROM PUBLIC;

GRANT ALL ON DATABASE atelier TO your_username;

• Ouvrir DBeaver et choisir PostgreSQL dans la liste des systèmes.
• Pour le nom de la base de données, choisissez “postgres”, pour l'utilisateur, choisissez “postgres” et entrez le mot de passe que vous avez choisi lors de l'installation.

Se connecter à la base de données

\c atelier

Voir la liste de bases de données disponibles:

\l

Voir la liste des tables:

\d+

Voir les colonnes dans une table

\d nom_de_la_table

Lister tous les usagers

\du

Quitter Postgresql

\q

Formats de colonnes les plus communs

Liste complète des type de données PostgreSQL.

Assurez-vous d'avoir créé la base de données atelier et de vous y être connecté.

\c atelier

Créer la table

CREATE TABLE obs_oiseaux (obs_id SERIAL PRIMARY KEY,
obs_time timestamp,espece text,nombre integer,lat real,"long" real);

Note: nous utilisons les doubles guillements “” pour marquer l'utilisation d'un mot réservé, d'un de caractères spéciaux ou d'espaces dans le nom d'une table ou d'une colonne.

Insérer une nouvelle ligne dans la table

INSERT INTO obs_oiseaux (obs_time,espece,nombre,lat,"long") VALUES ('2012-06-19 12:31:16','Sturnus vulgaris',40,45.3522,-73.7930);

Notez que nous n'avons pas entré le champ obs_id puisqu'il est entré automatiquement avec le type de caractère SERIAL, tel que spécifié lors de la création de la table.

Pour visualiser la table

SELECT * FROM obs_oiseaux;

Question 1 Reproduisez la table suivante dans PostgreSQL, nommez la 'Acteur' et visualisez là dans PostgreSQL.

Note: quand on entre du texte, il faut doubler les ' dans le texte ('') pour éviter que PostgreSQL ne les utilise comme marqueur de fin de l'entrée texte.

Téléchargez les fichiers suivants sur votre ordinateur (utilisez le bouton de droite pour cliquer sur le lien… sauvergarder sous). Lakes.csv species_acro.csv lakes_species.csv

Sous Windows: copiez ces trois fichiers vers un dossier nommé 'bdlibre' dans le répertoire C: (i.e. C:/bdlibre). Sous Mac ou Linux: copiez ces trois fichiers vers un dossier facilement accessible (e.g. /home/votrenome/bdlibre). Modifiez les commandes plus bas en remplaçant "C:/bdlibre" avec le chemin vers ce dossier.

Créer une table contenant l'information environnementale de chaque lac. - Lakes.

CREATE TABLE lakes (lake_id SERIAL PRIMARY KEY,numero INT, lake_name text, 
province text, latitude DEC(10,5),longitude DEC(10,5), number_of_species INT, ecoprov VARCHAR(50), 
ecozone VARCHAR(50), gss DEC(10,2),gse DEC(10,2), gsl DEC(10,2), gdd10 DEC(10,2), egdd DEC(10,2), 
mean_ele DEC(10,2), pe_ann_p DEC(10,2), totp_ann DEC(10,2), srann_me DEC(10,2), shann_me DEC(10,2), 
tmax_ann DEC(10,2), tmean_an DEC(10,2), vpann_me DEC(10,2));

Charger le fichier CSV dans cette table.

COPY lakes FROM 'C:/bdlibre/lakes.csv' WITH csv HEADER DELIMITER AS ',';

Si cette commande vous donne un message 'access denied' sous Windows

Si cette commande vous donne un message d'erreur 'access denied' sur Mac ou Linux

Créez la table contentant les présences de espèces pour chaque lac et charger le fichier CSV dans cette table.- lakes_species

CREATE TABLE lakes_species (lake_id INT NOT NULL, species_id INT NOT NULL);
COPY lakes_species FROM 'C:/bdlibre/lakes_species.csv' WITH csv HEADER DELIMITER AS ',';

Pour la troisième table, nous allons utiliser l'interface de DBeaver. Cliquez avec le bouton de gauche de la souris sur Tables dans Schemas>public dans le menu de gauche, et sélectionnez Import Data. Cliquez sur Next et choisissez le fichier CSV species_acro.csv sur votre ordinateur. Dans Tables mapping, assurez-vous que le nom de la table, le nom des colonnes et le format des colonnes est adéquat. Après avoir cliqué sur Next à nouveau, cliquez sur Proceed.

Commande PostgreSQL correspondante

CREATE TABLE species_acro (species_id INT NOT NULL, full_name varchar(100), short_name varchar(25));
COPY species_acro FROM 'C:/bdlibre/species_acro.csv' WITH csv HEADER DELIMITER AS ',';

Fonctions mathématiques et d'aggrégation (GROUP BY)

Liste complète

Sélection conditionnelle pour utilisation dans une clause 'WHERE'

Ordre des opérations dans une requête 'SELECT':

SELECT columns FROM tables WHERE conditions JOIN GROUP BY columns HAVING condition ORDER BY columns;

Sélectionnez toutes les lignes de la table Lakes

SELECT * FROM lakes;

Sélectionner toutes les lignes, mais n'afficher que les noms des lacs et la province

SELECT lake_name, province FROM lakes;

Voir tous les noms de provinces différents

SELECT DISTINCT province from lakes;

Voir tous le noms de provinces, utiliser une alias (renommer cette colonne dans les résultats) et trier par ordre descendant de nom.

SELECT DISTINCT province as "province name" FROM lakes ORDER BY province DESC;

Voir l'année de naissance de chaque acteur

SELECT prenom, nom_de_famille, EXTRACT(year from date_naissance) FROM acteur;

Voir les initiales de chaque acteur

SELECT prenom, nom_de_famille, concat(substr(prenom,1,1),' ',substr(nom_de_famille,1,1)) as initiales FROM acteur;

Sélectionner tous les lacs du Québec où la température annuelle moyenne est en bas de -5 C.

SELECT lake_name, tmean_an FROM lakes WHERE province='QUEBEC' AND tmean_an<-5;

Ou, pour compter le nombre de lacs

SELECT count(*) FROM lakes WHERE province='QUEBEC' AND tmean_an<-5;
autre alternative:
SELECT count(lake_id) FROM lakes WHERE province='QUEBEC' AND tmean_an<-5;

Question 1 - Combien de lacs en Colombie-Britannique reçoivent plus de 3000 mm de précipitation (colonne totp_ann)?

réponse

SELECT count(*) FROM lakes WHERE totp_ann>3000 AND province='BRITISH COLUMBIA';

Question 2 - Quel est l'altitude moyenne (colonne mean_ele) de tous les lacs dans l'écozone 'Montane Cordillera' (utilisez la fonction avg())?

réponse

SELECT avg(mean_ele) FROM lakes WHERE ecozone='Montane Cordillera';

Note: Le symbole % est utilisé pour remplacer le début ou la fin d'une entrée textuelle.
Sélectionnez tous les noms de lacs qui contiennent le mot 'Small'

SELECT lake_name FROM lakes WHERE lake_name like '%Small%';

Sélectionnez tous les noms de lacs qui contiennent le mot 'Small' et qui ne sont pas situés en Ontario

SELECT lake_name, province 
FROM lakes
WHERE lake_name like '%Small%' AND province!='ONTARIO';
autre possibilité:
SELECT lake_name, province 
FROM lakes 
WHERE lake_name like '%Small%' AND province NOT LIKE 'ONTARIO';

Question 3 - Quelle est la latitude maximale de tous les lacs dans un Ecozone dont le nom commence par 'Taiga'

réponse

SELECT max(latitude) FROM lakes WHERE ecozone LIKE 'Taiga%';

Question 4 - Combien d'espèces de Daphnia sont-elles listées dans la table species_acro?

réponse

SELECT count(*) FROM species_acro WHERE full_name like 'Daphnia%';

Calculez la température annuelle moyenne de tous les lacs dans chaque province

SELECT province, avg(tmean_an) as mean_an_t
FROM  lakes
GROUP BY province;

Même table, mais triée par ordre de températures croissantes

SELECT province, avg(tmean_an) as mean_an_t
FROM  lakes
GROUP BY province ORDER BY avg(tmean_an);

Question 5 - Quelle province a le lac ayant la température maximale (tmax_ann) la plus élevée?

réponse

SELECT province, max(tmax_ann) as max_temp FROM lakes GROUP BY province ORDER BY max_temp DESC;

Commande 'UPDATE'

Changer toutes les noms de province qui contiennent 'NWT', pour qu'ils aient le même nom ('NWT'). - Faites attention, cette opération modifie la table!

UPDATE lakes
SET province='NWT'
WHERE province like '%NWT%';

Compter le nombre d'espèces dans chaque lac. Afficher le nom du lac et le nombre de chaque espèce.

SELECT lake_name, COUNT(DISTINCT species_id) as num_species FROM lakes, lakes_species 
WHERE lakes.lake_id=lakes_species.lake_id 
GROUP BY lakes.lake_id
ORDER BY num_species DESC;

Même requête, mais on limite l'affichage aux 20 premières entrées.

SELECT lake_name, COUNT(DISTINCT species_id) as num_species FROM lakes, lakes_species 
WHERE lakes.lake_id=lakes_species.lake_id 
GROUP BY lakes.lake_id
ORDER BY num_species DESC LIMIT 20;

Compter le nombre d'espèces dans chaque Ecozone et Ecoprovince.

SELECT ecozone, ecoprov, COUNT(species_id) as num_species FROM lakes, lakes_species 
WHERE lakes.lake_id=lakes_species.lake_id 
GROUP BY ecozone, ecoprov
ORDER BY ecozone, COUNT(species_id);

Question 6 Générer une table qui liste le nom complet de chaque espèce de Daphnia et le nombre de lacs où on retrouve cette espèce.

Réponse

SELECT full_name, count(DISTINCT b.lake_id) as cnt FROM species_acro a, lakes_species b WHERE full_name like 'Daphnia%' AND a.species_id=b.species_id GROUP BY full_name;

Créer une liste de toutes les espèces présentes dans les lacs du Québec, avec les noms complets des espèces et le nombre de lacs dans lesquelles elles sont présentes.

SELECT full_name, count(full_name) 
FROM species_acro,lakes_species,lakes 
WHERE species_acro.species_id=lakes_species.species_id AND  lakes.lake_id=lakes_species.lake_id AND province='QUEBEC' 
GROUP BY full_name;

De façon équivalente…

SELECT full_name, count(full_name) 
FROM lakes
LEFT JOIN lakes_species ON (lakes.lake_id=lakes_species.lake_id)
LEFT JOIN species_acro ON (species_acro.species_id=lakes_species.species_id)
WHERE province='QUEBEC' 
GROUP BY full_name;

Pour toutes les combinaisons possibles de lacs se trouvant dans l'Ecoprovince Baffin Uplands, calculer la différence entre leurs températures annuelles moyennes.

SELECT concat(a.lake_name,'-',b.lake_name) as lakes, a.tmean_an-b.tmean_an as temp_diff 
FROM lakes a, lakes b 
WHERE a.ecoprov='Baffin Uplands' AND b.ecoprov='Baffin Uplands';

Créez une table contenant le fréquence de chaque espèce dans les lacs au sud et au nord du 50e parallèle (latitude).

SELECT a.species_id, count_50south, count_50north
FROM 
 (SELECT species_id, count(d.species_id) as count_50south FROM lakes c, lakes_species d WHERE c.lake_id=d.lake_id AND latitude<=50 GROUP BY species_id) a,
 (SELECT species_id, count(f.species_id) as count_50north FROM lakes e, lakes_species f WHERE e.lake_id=f.lake_id AND latitude>50 GROUP BY species_id) b 
WHERE a.species_id=b.species_id;

Question 7 En utilisant une sous-requête, trouvez l'élévation moyenne (colonne MEAN_ELE) des lacs dans lesquels au moins une espèce de Daphnia est présente.
Réponse 474.627172
Code: SELECT avg(mean_ele) FROM (SELECT DISTINCT a.lake_id, mean_ele FROM lakes a,species_acro b,lakes_species c WHERE b.full_name like 'Daphnia%' AND a.lake_id=c.lake_id AND c.species_id=b.species_id ORDER BY a.lake_id) d;

On vous donne un jeu de données dans le format suivant

On vous dit que des milliers d'autres arbres devront être entrés. Comment pensez-vous construire une base de données pour entreposer cette information?

Créez la table suivante avec la commande 'CREATE TABLE' dans PostgreSQL. Spécifiez le champs espece_id comme la clé primaire avec le type de fichier SERIAL PRIMARY KEY. Vous pouvez ensuite remplir la table avec les valeurs dans Libreoffice Base. Nom de la table: especes_arbres

Créez cette table dans PostgreSQL et créez un formulaire pour la remplir avec le mode ébauche de formulaire, avec des menus déroulants pour sélectionner les espèces et des boutons radios pour la santé des arbres. arbre_id est la clé primaire. Nom de la table: parcelles_arbres

• Dans Windows, téléchargez le pilote PostgreSQLODBC et installez-le sur votre ordinateur.
• Créez vos tables dans PostgreSQL.
• Dans Access, cliquez sur “External data… ODBC database”, et ensuite sélectionnez “Link to the data source by creating a linked table” et choisissez un nouveau nom de DSN. Spécifiez un nom pour le fichier et remplissez l'information pour vous connecter à la base de données. Le serveur est localhost si la base de données est sur votre propre ordinateur. Vous pouvez ensuite choisir les tables que vous voulez lier.
• Ces tables seront ensuite liées avec PostgreSQL et vous pouvez les modifier à partir de Access ou via des formulaire Access.

Lien vers phppgadmin

Dans R: lors de la première utilisation seulement :

| installer le paquet RPostgreSQL

install.packages('RPostgreSQL')

| Code R

library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv,host="localhost", user="your_username", password="your_password", dbname="atelier");
lakes <- dbGetQuery(con,'SELECT * FROM lakes');

S'assurer de changer le nom d'utilisateur et le mot de passe au besoin.

Générer un graphique à partir des données extraites

lakesqc <- dbGetQuery(con,"SELECT * FROM lakes WHERE province='QUEBEC'")
hist(lakes$tmean_an)

library(dplyr)
src<-src_postgres(dbname="workshop",host="localhost", port="5432",user="your_username",password="your_password")
lakes <- tbl(src, "lakes") # Define lakes table
lakes_qc<-filter(lakes, province  %=% 'QUEBEC') # Select lakes in Quebec
prov_tmean<-summarise(group_by(lakes, province), mean(tmean_an)) # Mean annual temperature per province
prov_tmean=collect(prov_tmean) # Transfer result to standard R data frame
lakes_qc2<-tbl(src, sql("SELECT * FROM lakes WHERE province='QUEBEC'")) #Perform any SQL statement