Atelier offert par Guillaume Larocque, professionnel de recherche au CSBQ, à l'Université du Québec à Rimouski, octobre 2013.

Lien vers la présentation Prezi

Lien vers la présentation simultanée

• Manuel d'utilisateur de MySQL 5.7.
• Manuel d'utilisation de MySQL 5.0 en français
• Requêtes MySQL fréquentes - Site très utile avec des centaines d'exemples de commandes MySQL.
• Database Design Manual: using MySQL for Windows - Ce livre peut être téléchargé gratuitement via la plupart des universités.

• Visitez la section téléchargements du site de MySQL et téléchargez la version de MySQL Community Server ou MySQL Installer appropriée pour votre ordinateur (Utilisateurs Windows: choisissez la version 32 bits de MySQL Installer). Notez que vous n'avez pas à vous inscrire. Cliquez simplement sur “No thanks, just take me to the downloads” en base de la fenêtre.
• Suivez les instructions d'installation. Vous pouvez choisir de n'installer que MySQL server.
• Si une fenêtre s'affiche vous demandant de vous inscrire, fermez cette fenêtre.
• Choisissez de continuer et de créer une “MySQL instance”. Sélectionnez la type de configuration standard.
• Sur Windows, choisissez d'installer MySQL comme un service Windows. Choisissez également un mot de passe pour l'utilisateur 'root' et prenez bien soins de noter ce mot de passe quelque part.
• Téléchargez LibreOffice 4 ou 4.x pour votre plate-forme et suivez les instructions d'installation. Choisissez l'option d'installation 'typique'.
• Sous Windows: Installez la version 32 bit du connecteur MySQL ODBC.

Sur Windows Ouvrez LibreOffice Base. Sélectionnez la base de données…Se connecter à une base de données existante (Connect to an existing database)…MySQL. Sur l'écran suivant, choisissez 'Connect using ODBC'. Sur l'écran suivant, cliquez sur 'Browse' et choisissez 'Organize' en bas à droite de la fenêtre. Ensuite, choisissez d'ajouter une nouvelle source de données et trouvez 'MySQL ODBC 5.2w driver'. Sur l'écran suivant … Nom de la source de données (Data source name): MySQL ODBC. TCP/IP Server : localhost. User: root. Password: Your password. Database: QCBSWORK. Ensuite, cliquez sur OK, sélectionnez MySQL ODBC et cliquez de nouveau sur OK.

Sur l'écrant suivant: Username:root. Sélectionnez 'Password required'. Sélectionnez Next…Finish et sauvez la base de données dans une répertoire de votre choix.

Sur Mac Téléchargez l'archive tar.gz ici. Ouvrez LibreOffice Writer. Dans le menu du haut, trouvez Préférences…LibreOffice»Java»Class path»Add archive et sélectionnez l'archive que vous venez de télécharger“. Ouvrez LibreOffice Base. Sélectionnez 'Base de données… Connection à une base de données existante…MySQL. Sur l'écran suivant, choisissez 'Connect using JDBC'. Sur l'écran suivant…nom de la base de données: QCBSWORK. Serveur: localhost. Nom d'usager: root;

Sur Linux Ouvrez LibreOffice Base. Sélectionnez 'base de données…Connexion à une base de données existante…MySQL. Sur l'écran suivant, choisissez 'Connexion en utilisant JDBC'. Sur l'écran… Nom de la base: QCBSWORK. Serveur: localhost. Si cette opération échoue, allez dans le menu principal de LibreOffice et choisissez Outils»Options»LibreOffice»Avancé et sélectionnez une installation de JRE.

https://secure.qcbs.ca/systeme/phpmyadmin/

login:qcbsworkshop-ro
mot de passe:YyQphZLHxWtPR7fN

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

Sous Linux: Ouvrez un terminal et tapez

mysql -u root -p

et tapez votre mot de passe. Vous êtes maintenant dans MySQL sous l'utilisateur root.

Sous MAC:

/usr/local/mysql/bin/mysql -u root

ou remplacer 'root' par le nom d'utilisateur créé lors de l'installation de MySQL.

Sous Windows: Dans le menu démarrer, trouvez MySQL Server… Command Line Client.

CREATE DATABASE QCBSWORK;

Utilisez cette base de données:

USE QCBSWORK;

Voir la liste de bases de données disponibles:

SHOW DATABASES;

Cliquez ici ou ici pour la liste complète des formats de colonnes MySQL.

Formats de colonnes les plus communs

Créer la table

CREATE TABLE Obs_oiseaux (Obs_ID int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
Obs_temps datetime,Espece text,Esp_freq int(10),Lat decimal(8,5),`Long` decimal(8,5));

Voir les tables existantes.

SHOW TABLES;

Décrire la table

DESC Obs_oiseaux;

Insérer une nouvelle ligne dans la table

INSERT INTO Obs_oiseaux SET Obs_temps='2012-06-19 12:31:16',Espece='Tachycineta bicolor',Esp_freq=10,Lat=45.21231,`Long`=-73.79102;

Notez que nous n'avons pas entré le champ Sight_ID puisqu'il est entré automatiquement avec le AUTO_INCREMENT, tel que spécifié lors de la création de la table. Notez aussi que le nom de colonne Long est entre `` puisque ce mot est réservé par MySQL. Il est habituellement préférable d'éviter ce genre de mot.

Autre syntaxe pour la commande INSERT

INSERT INTO Obs_oiseaux (Obs_temps,Espece,Esp_freq,Lat,`Long`) VALUES ('2012-06-19 12:31:16','Sturnus vulgaris',40,45.3522,-73.7930);

Pour visualiser la table

SELECT * FROM Obs_oiseaux;

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

Note: quand on entre du texte, il faut précéder les ' ou ” dans le texte de \ pour éviter que MySQL ne les utilise comme la 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 Lakes_Species.csv Species_Acro.csv

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

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

CREATE TABLE Lakes (Lake_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,numero INT, Lake_name text, 
Province text, Latitude DEC(10,5),Longitude DEC(10,5), Number_of_species INT, ECOPROV VARCHAR(20), 
ECOZONE VARCHAR(20), 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.

LOAD DATA LOCAL INFILE 'C:/mysqlwork/Lakes.csv' INTO TABLE Lakes FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' LINES TERMINATED BY '\n'   IGNORE 1 LINES;

Note: si cette commande ne fonctionne pas ('command not allowed'). Tapez 'exit;' et redémarrez MySQL avec la commande suivante

mysql -u root -p --local-infile=1;

LOAD DATA LOCAL INFILE 'C:/mysqlwork/Lakes.csv' INTO TABLE Lakes FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' LINES TERMINATED BY '\n'   IGNORE 1 LINES;

Si la commande ne fonctionne pas sur Mac, utilisez ceci

LOAD DATA LOCAL INFILE 'C:/mysqlwork/Lakes.csv' INTO TABLE Lakes FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' LINES TERMINATED BY '\r'   IGNORE 1 LINES;

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

Si la commande ne fonctionne pas sur Mac, utilisez ceci

CREATE TABLE Lakes_Species (Lake_ID INT NOT NULL, Species_ID varchar(25) NOT NULL);
LOAD DATA LOCAL INFILE 'C:/mysqlwork/Lakes_Species.csv' INTO TABLE Lakes_Species 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r'   IGNORE 1 LINES;

Pour la troisième table, nous pourrions utiliser cette commande:

Commande

CREATE TABLE Species_Acro (Unique_ID INT NOT NULL, Full_name varchar(100), Species_ID Varchar(25));
LOAD DATA LOCAL INFILE 'C:/mysqlwork/Species_Acro.csv' INTO TABLE Species_Acro 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'   IGNORE 1 LINES;

Par contre, nous allons utiliser LibreOffice pour se familiariser avec l'interface.

• Ouvrez le fichier Species_Acro.csv avec LibreOffice Calc (Dans Windows, trouvez LibreOffice Calc dans le menu démarrer). Spécifiez que le fichier est séparé par des virgules.
• Sélectionnez la feuille de calcul en entier avec CTRL-A.
• Ouvrez LibreOffice Base et allez à Edition…coller. Choisissez d'utiliser le nom des colonnes comme entêtes pour la table.
• Suivez chaque étape de l'assistant… Sélectionnez tous les champs sauf le champ Unique_ID. Spécifiez le format de données approprié pour les autres colonnes. (vérifiez dans la table pour valider)
• Lorsqu'on vous demande de créer une Clé primaire (primary key), cliquez sur oui.

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;

Afficher 'Bonjour'

Select 'Bonjour';

Calculs mathématiques

Select cos(4*3);

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, year(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;

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 a bas de -5 C.

SELECT Lake_name, TMEAN_AN FROM Lakes WHERE Province='Quebec' AND `TMEAN_AN`<-5;

Ou, pour compter le nombre de lac

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 recoivent plus de 3000 mm de précipitation (colonne TOTP_ANN)?
réponse 12
Question 2 - Quel est l'altitude moyenne (MEAN_ELE column) de tous les lacs dans l'écozone 'Montane Cordillera' (utilisez la fonction avg())?
réponse 1364.36

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 68.8
Question 4 - Combien d'espèces de Daphnia sont-elles listées dans la table Species_Acro?
réponse 17 (21 ont le mot 'Daphnia' quelque part dans leur nom)

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 précipitation maximale la plus élevée?
réponse BRITISH COLUMBIA 14.60

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 numéro de l'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 0,20;

Compter le nombre d'espèces dans chaque Ecozone et Ecoprovince. Afficher le nom du lac et le numéro de l'espèce.

SELECT ECOZONE, ECOPROV, COUNT(Species_ID) as Num_Species FROM Lakes, Lakes_Species 
WHERE Lakes.Lake_ID=Lakes_Species.Lake_ID 
GROUP BY ECOPROV
ORDER BY ECOZONE, COUNT(Species_ID);

Lister les espèces qui ne se trouvent que dans une seule Écoprovince.

SELECT Species_ID, ECOPROV FROM Lakes, Lakes_Species 
WHERE Lakes.Lake_ID=Lakes_Species.Lake_ID 
GROUP BY Species_ID HAVING Count(DISTINCT ECOPROV)=1
ORDER BY 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 a.Lake_ID) as `Count` FROM Lakes a, Species_Acro b, Lakes_Species c WHERE Full_name like 'Daphnia%' AND a.Lake_ID=c.Lake_ID AND b.Species_ID=c.Species_ID GROUP BY Full_name;

Cliquez ici pour une liste des fonctions mathématiques disponibles dans MySQL

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 (Species_Acro, Lakes_Species)
ON (Species_Acro.Species_ID=Lakes_Species.Species_ID AND Lakes.Lake_ID=Lakes_Species.Lake_ID) WHERE province='QUEBEC' 
GROUP BY Species_ID;

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';

De façon équivalente:
SELECT concat(a.`Lake_ID`,'-',b.`Lake_ID`) as Lakes, a.`TMEAN_AN`-b.`TMEAN_AN` as Temp_Diff 
FROM `Lakes_1665` a JOIN `Lakes_1665` 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;

De façon équivalente…

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
UNION 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;

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?

SUIVEZ CES ÉTAPES POUR CRÉER LA BASE DE DONNÉES DANS LIBREOFFICE BASE

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

installer le paquet RMySQL

install.packages('RMySQL')

Code R

library(RMySQL)
con <- dbConnect(MySQL(), user="root", password="votre mot de passe", dbname="QCBSWORK");
lakes <- dbGetQuery(con,'SELECT * FROM Lakes');

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

Générer des graphiques à partir des données

lakesqc <- dbGetQuery(con,'SELECT * FROM Lakes WHERE province="Quebec"')
hist(lakes$TMEAN_AN)