This is an old revision of the document!


Introduction à la gestion des bases de données avec MySQL

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

Installation de MySQL

  • 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.

Configuration de la connection LibreOffice/MySQL

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

Discussion de groupe

Exercices MySQL

Démarrer MySQL

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.

Créer une nouvelle base de données:

CREATE DATABASE QCBSWORK;

Utilisez cette base de données:

USE QCBSWORK;

Voir la liste de bases de données disponibles:

SHOW DATABASES;

Commandes MySQL principales

SHOWvoir les bases de données ou les tables
DESCRIBEdécrire une table
CREATEcréez une base de données ou une table
INSERTinsérer une/plusieurs lignes dans une table
SELECTeffectuer une requête
UPDATEmodifier des colonnes existantes
DELETEeffacer des colonnes
ALTER TABLEajouter des nouvelles colonnes, ou modifier le type de colonne.
DROPeffacer unebase de données ou une table

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

Formats de colonnes les plus communs

fonctiondescriptionexemple
int()integer(longueur maximal)5
decimal(,)decimal(longueur totale, nombre de décimales)122.52
varchar()character(longueur maximale) 'hirondelle bicolore'
texttexte d'une longueur non-définie 'Il était une fois une hirondelle…'
datetimedate et heure '2012-10-21 10:03:21'

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.

PrenomNom_de_familleDate_naissanceSexeGrandeurMarque_distinctive
BruceWillis1955-03-19M1.81Joue fréquemment des hommes qui souffrent d'une tragédie, ont perdu quelque chose, ou qui ont une crise de confiance ou de conscience.
EmmaWatson1990-04-15F1.65Joue souvent des personnages littéraires.

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

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

Importation d'une table

  • 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)

AVG() La moyenne
COUNT(DISTINCT COLUMN) Le nombre d'entrées distinctes dans une colonne
COUNT() Le nombre de lignes
GROUP_CONCAT() Concaténation de multiples entrées textuelles
MAX() Valeur maximale
MIN() Valeur minimale
STDDEV_POP() Déviation standard de la population
STDDEV_SAMP() Déviation standard de l'échantillon
SUM() Somme
VAR_POP() Variance de la population
VAR_SAMP() Variance de l'échantillon

Liste complète

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

= Égal
> Plus grand que
< Moins que
>= Plus grand que ou égal
< = Plus petit que ou égal
<> or !=Non-égalité
LIKERequête d'équivalence d'entrées textuelles (string matching)

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

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
Question 4 :?: - Combien d'espèces de Daphnia sont-elles listées dans la table Species_Acro?
réponse

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

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


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

JOIN (INNNER JOIN, CROSS JOIN) Garder toutes les combinaisons possibles des lignes des tables A et B.
LEFT JOIN Garder toutes les entrées de la table A, et joindre avec les entrées de B qui se trouvent également dans A (via un identificateur conjoint).
RIGHT JOIN Garder toutes les entrées de la table B, et joindre avce les entrées de A qui se trouvent également dans B (via un identificateur conjoint).
OUTER JOIN Garder toutes les entrées des tables A et B

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

Utilisation de l'interface LibreOffice Base

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

Parcelle 1
Acer rubrum12.4vivant
Acer saccharum25.3vivant
Fagus grandifolia14.1vivant
Fagus grandifolia66.0malade (écorce)
Fraxinus americana30.1vivant
Parcelle 2
Quercus rubra12.4vivant
Fraxinus americana64.2malade (écorce)
Fraxinus americana53.1mort
Carpinus caroliana10.3vivant

:?: 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

Lier R avec MySQL

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)