Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
bdlibre [2016/10/25 15:44] qcbs [Accéder à PostgreSQL] |
bdlibre [2024/03/11 16:25] (current) qcbs [Ressources utiles] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| ======= Introduction à la gestion des bases de données avec des logiciels libres ======= | ======= Introduction à la gestion des bases de données avec des logiciels libres ======= | ||
| - | Atelier offert par [[http://qcbs.ca/fr/ressources/professionnel-de-recherche/professionnels-de-recherche-guillaume-larocque-phd/|Guillaume Larocque]], professionnel de recherche au CSBQ, à l'Université de Sherbrooke les 26 et 27 octobre 2016. | + | Atelier offert par [[http://qcbs.ca/fr/ressources/professionnel-de-recherche/professionnels-de-recherche-guillaume-larocque-phd/|Guillaume Larocque]], professionnel de recherche au CSBQ. |
| ===== Presentation ===== | ===== Presentation ===== | ||
| Line 11: | Line 10: | ||
| ===== Ressources utiles ===== | ===== Ressources utiles ===== | ||
| - | * [[http://www.postgresql.org/docs/current/static/reference.html|PostgreSQL 9.6 Reference Manual]] | + | * [[http://www.postgresql.org/docs/current/static/reference.html|PostgreSQL Reference Manual]] |
| * [[http://link.springer.com/book/10.1007%2F978-1-4302-0018-5|Beginning databases with PostgreSQL]] | * [[http://link.springer.com/book/10.1007%2F978-1-4302-0018-5|Beginning databases with PostgreSQL]] | ||
| * [[https://wiki.postgresql.org/wiki/Main_Page|PostgreSQL wiki]] | * [[https://wiki.postgresql.org/wiki/Main_Page|PostgreSQL wiki]] | ||
| Line 163: | Line 162: | ||
| * Pour l'URL de la source de données, tapez ceci: | * Pour l'URL de la source de données, tapez ceci: | ||
| <file postgresql> | <file postgresql> | ||
| - | dbname=atelier hostaddr=127.0.0.1 port=5432 | + | dbname=atelier host=localhost port=5432 |
| </file> | </file> | ||
| * Cliquez sur suivant et indiquez le nom d'usager, et choisissez 'mot de passe requis'. Vous pouvez ensuite cliquer sur 'Tester la connexion' pour vous assurer que ça fonctionne. | * Cliquez sur suivant et indiquez le nom d'usager, et choisissez 'mot de passe requis'. Vous pouvez ensuite cliquer sur 'Tester la connexion' pour vous assurer que ça fonctionne. | ||
| Line 170: | Line 169: | ||
| ===== Commandes de base ===== | ===== Commandes de base ===== | ||
| - | Se connecter à une base de données | + | Se connecter à la base de données |
| <file postgresql> | <file postgresql> | ||
| \c atelier | \c atelier | ||
| Line 213: | Line 212: | ||
| [[http://www.postgresql.org/docs/current/static/datatype.html|Liste complète des type de données PostgreSQL.]] | [[http://www.postgresql.org/docs/current/static/datatype.html|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é. | ||
| + | |||
| + | <file postgresql> | ||
| + | \c atelier | ||
| + | </file> | ||
| Créer la table | Créer la table | ||
| Line 370: | Line 375: | ||
| ===== Exercice 3 - commande SELECT ===== | ===== Exercice 3 - commande SELECT ===== | ||
| - | Afficher 'Bonjour' | ||
| - | <file postgresql> | ||
| - | SELECT 'Bonjour'; | ||
| - | </file> | ||
| - | |||
| - | Calculs mathématiques | ||
| - | <file postgresql> | ||
| - | SELECT cos(4*3); | ||
| - | </file> | ||
| - | |||
| Sélectionnez toutes les lignes de la table Lakes | Sélectionnez toutes les lignes de la table Lakes | ||
| <file postgresql> | <file postgresql> | ||
| Line 410: | Line 405: | ||
| </file> | </file> | ||
| - | Select all lakes from Quebec where the mean annual temperature is below -5. | + | Sélectionner tous les lacs du Québec où la température annuelle moyenne est en bas de -5 C. |
| - | Sélectionner tous les lacs du Québec où la température annuelle moyenne est a bas de -5 C. | + | |
| <file postgresql> | <file postgresql> | ||
| SELECT lake_name, tmean_an FROM lakes WHERE province='QUEBEC' AND tmean_an<-5; | SELECT lake_name, tmean_an FROM lakes WHERE province='QUEBEC' AND tmean_an<-5; | ||
| </file> | </file> | ||
| - | Ou, pour compter le nombre de lac | + | Ou, pour compter le nombre de lacs |
| <file postgresql> | <file postgresql> | ||
| SELECT count(*) FROM lakes WHERE province='QUEBEC' AND tmean_an<-5; | SELECT count(*) FROM lakes WHERE province='QUEBEC' AND tmean_an<-5; | ||
| Line 528: | Line 522: | ||
| WHERE lakes.lake_id=lakes_species.lake_id | WHERE lakes.lake_id=lakes_species.lake_id | ||
| GROUP BY lakes.lake_id | GROUP BY lakes.lake_id | ||
| - | ORDER BY Num_Species DESC LIMIT 20; | + | ORDER BY num_species DESC LIMIT 20; |
| </file> | </file> | ||
| Line 632: | Line 626: | ||
| :?: 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. | :?: 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 | + | Nom de la table: especes_arbres |
| ^espece_id^nom_espece^ | ^espece_id^nom_espece^ | ||
| |1|Quercus rubra| | |1|Quercus rubra| | ||
| Line 644: | Line 638: | ||
| :?: 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. | :?: 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 | Nom de la table: parcelles_arbres | ||
| - | ^parcelle_id^arbre_id^espece_id^DHP^etat^commentaires^ | + | ^parcelle_id^arbre_id^espece_id^dhp^etat^commentaires^ |
| |1|1|3|12.4|vivant| | | |1|1|3|12.4|vivant| | | ||
| |1|2|2|25.3|vivant| | | |1|2|2|25.3|vivant| | | ||
| Line 673: | Line 667: | ||
| **Liens utiles** | **Liens utiles** | ||
| - | * [[http://www.codecademy.com/|Code Academy]] Site web génial pour apprendre HTML, CSS, PHP, Javascript JQuery ou autre. | + | * [[http://www.codecademy.com/|Code Academy]] Site web génial pour apprendre HTML, CSS, PHP, Javascript JQuery, SQL ou autre. |
| * [[http://link.springer.com/book/10.1007/978-1-4302-0136-6|Beginning PHP and PostgreSQL 8. ]] Livre téléchargeable en e-book. Un peu vieux. | * [[http://link.springer.com/book/10.1007/978-1-4302-0136-6|Beginning PHP and PostgreSQL 8. ]] Livre téléchargeable en e-book. Un peu vieux. | ||
| Line 689: | Line 683: | ||
| Pour linux: | Pour linux: | ||
| <file> | <file> | ||
| - | sudo apt-get install postgresql-9.3 apache2 php5 phppgadmin php5-pgsql | + | sudo apt-get install postgresql-9.5 apache2 php5 phppgadmin php5-pgsql |
| </file> | </file> | ||
| + | en modifiant selon la version de postgresql disponible dans votre distribution. | ||
| + | |||
| ===== Partie 1 - Formulaire HTML ===== | ===== Partie 1 - Formulaire HTML ===== | ||
| Line 696: | Line 692: | ||
| <file html> | <file html> | ||
| - | <!DOCTYPE html> | + | <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"> |
| <html> | <html> | ||
| <head> | <head> | ||
| + | <meta charset="UTF-8"> | ||
| <title>Titre du document</title> | <title>Titre du document</title> | ||
| </head> | </head> | ||
| Line 943: | Line 940: | ||
| library(RPostgreSQL) | library(RPostgreSQL) | ||
| drv <- dbDriver("PostgreSQL") | drv <- dbDriver("PostgreSQL") | ||
| - | con <- dbConnect(drv, user="your_username", password="your_password", dbname="atelier"); | + | con <- dbConnect(drv,host="localhost", user="your_username", password="your_password", dbname="atelier"); |
| lakes <- dbGetQuery(con,'SELECT * FROM lakes'); | lakes <- dbGetQuery(con,'SELECT * FROM lakes'); | ||
| </file> | </file> | ||
| Line 951: | Line 948: | ||
| lakesqc <- dbGetQuery(con,"SELECT * FROM lakes WHERE province='QUEBEC'") | lakesqc <- dbGetQuery(con,"SELECT * FROM lakes WHERE province='QUEBEC'") | ||
| hist(lakes$tmean_an) | hist(lakes$tmean_an) | ||
| + | </file> | ||
| + | |||
| + | ===== Using the dplyr package===== | ||
| + | |||
| + | <file rsplus> | ||
| + | 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 | ||
| </file> | </file> | ||
