======= Introduction à la gestion des bases de données avec MySQL =======
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é du Québec à Rimouski, octobre 2013.
===== Presentation =====
[[http://prezi.com/df-rwsabc09g/introduction-a-la-gestion-des-bases-de-donnees-avec-mysql/|Lien vers la présentation Prezi]]
[[http://prezi.com/df-rwsabc09g/present/?auth_key=4csgnis&follow=GuillaumeLarocque&kw=present-df-rwsabc09g&rc=ref-19631691|Lien vers la présentation simultanée]]
===== Ressources utiles =====
* [[http://dev.mysql.com/doc/refman/5.7/en/index.html|Manuel d'utilisateur de MySQL 5.7.]]
* [[http://dev.mysql.com/doc/refman/5.0/fr/index.html| Manuel d'utilisation de MySQL 5.0 en français]]
* [[http://www.artfulsoftware.com/infotree/queries.php|Requêtes MySQL fréquentes]] - Site très utile avec des centaines d'exemples de commandes MySQL.
* [[http://www.springerlink.com/content/978-1-85233-716-2/|Database Design Manual: using MySQL for Windows]] - Ce livre peut être téléchargé gratuitement via la plupart des universités.
====== Installation de MySQL ======
* Visitez la [[http://www.mysql.com/downloads/mysql/|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 [[http://www.libreoffice.org/download/|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 [[http://dev.mysql.com/downloads/connector/odbc/|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**
[[http://dev.mysql.com/downloads/connector/j/|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.
===== Accès à PHPMyAdmin =====
https://secure.qcbs.ca/systeme/phpmyadmin/
login:qcbsworkshop-ro
mot de passe:YyQphZLHxWtPR7fN
====== Discussion de groupe ======
[[http://qcbs.ca/wp-content/uploads/2012/10/Crustacean_plankton_Canada.ods|Cliquez ici pour télécharger le jeu de données (feuille de calcul en format LibreOffice Calc)]]
====== 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 ====
|[[http://dev.mysql.com/doc/refman/5.6/en/show.html|SHOW]]|voir les bases de données ou les tables|
|[[http://dev.mysql.com/doc/refman/5.6/en/describe.html|DESCRIBE]]|décrire une table|
|[[http://dev.mysql.com/doc/refman/5.6/en/create-table.html|CREATE]]|créez une base de données ou une table|
|[[http://dev.mysql.com/doc/refman/5.6/en/insert.html|INSERT]]|insérer une/plusieurs lignes dans une table|
|[[http://dev.mysql.com/doc/refman/5.6/en/select.html|SELECT]]|effectuer une requête|
|[[http://dev.mysql.com/doc/refman/5.6/en/update.html|UPDATE]]|modifier des colonnes existantes|
|[[http://dev.mysql.com/doc/refman/5.6/en/delete.html|DELETE]]|effacer des colonnes|
|[[http://dev.mysql.com/doc/refman/5.6/en/alter-table.html|ALTER TABLE]]|ajouter des nouvelles colonnes, ou modifier le type de colonne.|
|[[http://dev.mysql.com/doc/refman/5.6/en/drop-table.html|DROP]]|effacer une[[http://dev.mysql.com/doc/refman/5.5/en/drop-database.html|base de données]] ou une [[http://dev.mysql.com/doc/refman/5.5/en/drop-table.html|table]]|
===== Exercice 1 - déclarations CREATE et INSERT =====
[[http://dev.mysql.com/doc/refman/5.0/en/data-type-overview.html|Cliquez ici]] ou [[http://www.htmlite.com/mysql003.php|ici]] pour la liste complète des formats de colonnes MySQL.
Formats de colonnes les plus communs
^fonction^description^exemple^
|int()|integer(longueur maximal)|5|
|decimal(,)|decimal(longueur totale, nombre de décimales)|122.52|
|varchar()|character(longueur maximale)| 'hirondelle bicolore'|
|text|texte d'une longueur non-définie| 'Il était une fois une hirondelle...'|
|datetime|date 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.
^Prenom^Nom_de_famille^Date_naissance^Sexe^Grandeur^Marque_distinctive^
|Bruce|Willis|1955-03-19|M|1.81|Joue fréquemment des hommes qui souffrent d'une tragédie, ont perdu quelque chose, ou qui ont une crise de confiance ou de conscience.|
|Emma|Watson|1990-04-15|F|1.65|Joue 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.
===== Exercice 2 - Importer des données dans MySQL =====
Téléchargez les fichiers suivants sur votre ordinateur (utilisez le bouton de droite pour cliquer sur le lien... sauvergarder sous).
[[http://qcbs.ca/wp-content/uploads/2012/10/Lakes.csv|Lakes.csv]]
[[http://qcbs.ca/wp-content/uploads/2012/10/Lakes_Species.csv|Lakes_Species.csv]]
[[http://qcbs.ca/wp-content/uploads/2012/10/Species_Acro.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.
===== Importation de données dans LibreOffice Base, en utilisant LibreOffice Calc =====
=== 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.
===== RÉFÉRENCES =====
** 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|
[[http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html|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é|
|LIKE|Requê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;
===== Exercice 3 - commande SELECT =====
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) ++ \\
===== Exercice 4 - REGROUPEMENT =====
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%';
===== Exercice 5 - Travailler avec plusieurs tables =====
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;
^Full_name^Count^
| Daphnia pulex Leydig, 1860 | 234 |
| Daphnia similis Claus, 1876 | 17 |
| Daphnia ambigua Scourfield, 1947 | 59 |
| Daphnia catawba Coker, 1926 | 28 |
| Daphnia dubia Herick, 1883 | 71 |
| Daphnia galeata Sars, 1864 | 17 |
| Daphnia longiremis G. O. Sars, 1862 | 426 |
| Daphnia longispina hyalina ceresiana | 3 |
| Daphnia longispina hyalina microcephela | 27 |
| Daphnia magna Straus, 1820 | 18 |
| Daphnia mendotae Birge, 1918 | 433 |
| Daphnia middendorffiana Fischer, 1851 | 117 |
| Daphnia parvula Fordyce, 1901 | 28 |
| Daphnia pulicaria Forbes, 1893 | 110 |
| Daphnia retrocurva Forbes, 1882 | 324 |
| Daphnia rosea G.O. Sars, 1862 | 52 |
| Daphnia thorata Forbes, 1893 | 14 |
++++ \\
[[http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html|Cliquez ici pour une liste des fonctions mathématiques disponibles dans MySQL]]
===== Exercice 6 - Opérations de jointures (JOIN) =====
|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';
===== Exercice 7 - Sous-requêtes (Subqueries) =====
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;\\ ++
====== Utilisation de l'interface LibreOffice Base ======
===== Créer des tables et des formulaires =====
On vous donne un jeu de données dans le format suivant
^Parcelle 1^
|Acer rubrum|12.4|vivant|
|Acer saccharum|25.3|vivant|
|Fagus grandifolia|14.1|vivant|
|Fagus grandifolia|66.0|malade (écorce)|
|Fraxinus americana|30.1|vivant|
^Parcelle 2^
|Quercus rubra|12.4|vivant|
|Fraxinus americana|64.2|malade (écorce)|
|Fraxinus americana|53.1|mort|
|Carpinus caroliana|10.3|vivant|
:?: 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|
:?: Utilisez le mode ébauche (design view) pour créer la table suivante. Spécifiez le champs Espece_ID comme la clé primaire avec le type de fichier INTEGER(++ cliquez avec le bouton de droite de la souris sur la ligne correspondante| {{:screen.png}} ++) et avec 'Autovaleur' activé.
Nom de la table: Especes_arbres
^Espece_ID^Nom_Espece^
|1|Quercus rubra|
|2|Acer saccharum|
|3|Acer rubrum|
|4|Fagus grandifolia|
|5|Fraxinus americana|
|6|Carpinus caroliana|
:?: Créez un formulaire (en utilisant l'assistant) pour remplir les tables plus haut.
\\
:?: Créez et remplissez cette table en utilisant une formulaire. Sante_ID est la clé primaire.
Nom de la table: Sante
^Sante_ID^Details_sante^
|1|vivant|
|2|malade (écorce)|
|3|malade (branches)|
|4|mort|
:?: Configurez cette table dans le mode ébauche (design view) 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 la santé des arbres à partir des tables créées plus haut. Arbre_ID est la clé primaire.
Nom de la table: Parcelles_arbres
^Parcelle_ID^Arbre_ID^Espece_ID^Diametre^Sante_ID^
|1|1|3|12.4|1|
|1|2|2|25.3|1|
|1|3|4|14.1|1|
|1|4|4|66.0|2|
|1|5|4|30.1|1|
|2|6|1|40.1|1|
|2|7|5|64.2|3|
|2|8|5|53.1|4|
|2|9|6|10.3|1|
++++
====== Lier R avec MySQL ======
Dans R:
lors de la première utilisation seulement :
install.packages('RMySQL')
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.
lakesqc <- dbGetQuery(con,'SELECT * FROM Lakes WHERE province="Quebec"')
hist(lakes$TMEAN_AN)