Workshop given by Guillaume Larocque, research professional at the QCBS, at McGill University on November 2 and 9, 2012.

Link to Prezi presentation - Simultaneous presentation

Link to Prezi presentation

• MySQL 5.5. Reference Manual
• Common MySQL Queries - Very useful website with hundreds of example MySQL commands.
• Database Design Manual: using MySQL for Windows - This book can be downloaded for free from most universities.

• Visit the Download section of the MySQL website and download the version of MySQL Community Server appropriate for your platform (Windows users: choose the 32 bit version!). Note that you DO NOT have to register. Simply click on “No thanks, just take me to the downloads!” at the bottom.
• Follow the installation instructions. Use the Typical Setup Type.
• If a Window appears to register for a MySQL Entreprise support, close the window.
• Choose to proceed to create and configure a MySQL instance. Select the standard configuration type.
• On Windows, choose to install MySQL as a Windows service. Also choose a password for the root user (administrator). WRITE DOWN THIS PASSWORD!!
• Download LibreOffice 3.6 for your platform and follow the installation instructions. Choose the Typical installation option.
• On Windows: Install the 32 bit version of the MySQL ODBC connector.

https://secure2.creationmw.com/systeme/phpmyadmin/

login:qcbsworkshop-ro
password:YyQphZLHxWtPR7fN

Click here to download the original spreadsheet

On Linux: Open a terminal and type

mysql -u root -p

and type your password. You are now in MySQL.

On MAC:

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

On Windows: In the Start menu, find MySQL Server… Command Line Client.

Create a new database:

CREATE DATABASE QCBSWORK;

Use this database:

USE QCBSWORK;

To see the list of available databases:

SHOW DATABASES;

Click here or here for the full list of MySQL data types

More common data types

CREATE TABLE Bird_sightings (Sight_ID int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
Obs_Time datetime,Species text,Sp_count int(10),Lat decimal(8,5),`Long` decimal(8,5));

SHOW TABLES;

DESC Bird_sightings;

INSERT INTO Bird_sightings SET Obs_Time='2012-06-19 12:31:16',Species='Tachycineta bicolor',Sp_count=10,Lat=45.21231,`Long`=-73.79102;

Note that we did not specify the Sight_ID field since it is set to AUTO_INCREMENT and it will be assigned a value automatically.

Other syntax for the insert command:

INSERT INTO Bird_sightings (Obs_Time,Species,Sp_count,Lat,`Long`) VALUES ('2012-06-19 12:31:16','Sturnus vulgaris',40,45.3522,-73.7930);

To see the table:

SELECT * FROM Bird_sightings;

Question 1 Reproduce the following table in MySQL, name it 'Actors' and visualize it in MySQL

Download the following files to your computers (right-click, save link as…): Lakes.csv Lakes_Species.csv Species_Acro.csv

On Windows: Copy those three files to a folder named 'mysqlwork' in the C: folder (i.e. C:\mysqlwork) On Mac or Linux: Copy those three files to a folder in your path that you can easily identify (e.g. /home/myname/mysqlwork). Modify the commands below accordingly by replacing "C:/mysqlwork" with your path.

Create the table containing the environmental information of each lake - 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));

Load the CSV file into this 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: if the above command gives you 'command not allowed' error. Type 'exit;' and restart MySQL with the following command

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

Create the table containing the Species presence at each lakes and load CSV into it - Lakes_Species

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 '\n'   IGNORE 1 LINES;

For the third table, we could use the command line with the following:

Command

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;

However, we will use LibreOffice instead…

On Windows Open LibreOffice Base. Select database…Connect to an existing database…MySQL. On the next screen, choose 'Connect using ODBC'. On the next screen, click on Browse and choose organize in the bottom right. Then, choose to add a data source and find 'MySQL ODBC 5.2w driver'. In the next screen… Data source name: MySQL ODBC. TCP/IP Server : localhost. User: root. Password: Your password. Database: QCBSWORK. Then click OK, Select MySQL ODBC and click OK again.

On the next screen: Username:root. Select 'Password required'. Select Next…Finish and then save the database in a folder of your choice.

On Mac Download the tar.gz archive here. Open LibreOffice Writer. In the top menu find Preferences…LibreOffice»Java»Class path»Add archive and select the archive you just downloaded. Open LibreOffice Base. Select database…Connect to an existing database…MySQL. On the next screen, choose 'Connect using JDBC'. On the next screen… Database name: QCBSWORK. Server: localhost.

On linux Open LibreOffice Base. Select database…Connect to an existing database…MySQL. On the next screen, choose 'Connect using JDBC'. On the next screen… Database name: QCBSWORK. Server: localhost. If this fails, go to the top menu in LibreOffice and choose Tools»Options»LibreOffice»Java and select an installation of a JRE.

• Open the Species_Acro.csv file with LibreOffice Calc (In Windows, find LibreOffice Calc in the start menu). Specify that the file is separated by comma.
• Select the entire worksheet with ctrl-a;
• Open LibreOffice Base and go to Edit…paste. Select to use the column headers as headers for the table
• Follow the wizard… Select all fields except the Unique_ID field. Specify the proper data format (verify the types of attributes in the table) for the other columns.
• When prompted to create a primary key, click yes.

MATH AND GROUP BY (Aggregate) Functions

Full list

Conditional selections used in the where clause

Order of operators in a SELECT statement:

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

Select 'Bonjour';

Select cos(4*3);

Select all rows from the Lakes table.

SELECT * FROM Lakes;

Select just the lake names and province.

SELECT Lake_name, province FROM Lakes;

Show all province names in table

SELECT DISTINCT Province from Lakes;

Show all province names in table, use an alias for province and order by descending alphabetical order.

SELECT DISTINCT Province as `Province name` FROM Lakes ORDER BY Province DESC;

Show the birth year of each actor

SELECT First_name, Last_name, year(Birth_date) FROM Actors;

Show initials of each actor

SELECT First_name, Last_name, concat(substr(Firstname,1,1),' ',substr(Lastname,1,1)) as Initials FROM Actors;

Select all lakes from Quebec where the mean annual temperature is below -5.

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

Or simply, to count the number of lakes

SELECT count(*) FROM Lakes WHERE Province='Quebec' AND `TMEAN_AN`<-5;
or equivalently:
SELECT count(Lake_ID) FROM Lakes WHERE Province='Quebec' AND `TMEAN_AN`<-5;

Question 1 - How many lakes in British Columbia receive more than 3000 mm of precipitation (TOTP_ANN column)?
answer 12
Question 2 - What is the average Elevation (MEAN_ELE column) of all lakes in the Montane Cordillera Ecozone (use the avg() operator)?
answer 1364.36

Note: the % operator is a wildcard used to replace the beginning or the end of a string.
Select all lake names that contain the word 'Small'

SELECT Lake_name FROM Lakes WHERE Lake_name like '%Small%';

Select all lake names that contain the word 'Small' and that are not located in Ontario.

SELECT Lake_name, province 
FROM Lakes
WHERE Lake_name like '%Small%' AND province!='ONTARIO';
other possibility:
SELECT Lake_name, province 
FROM Lakes 
WHERE Lake_name like '%Small%' AND province NOT LIKE 'ONTARIO';

Question 3 - What is the maximum latitude of all lakes in Ecozones with names that start with 'Taiga'
answer 68.8
Question 4 - How many species of Daphnia are there in the Species_Acro table?
answer 17 (21 have the word 'Daphnia' somewhere in their name)

Calculate the average annual temperature of all lakes within each province

SELECT province, AVG(TMEAN_AN) as Mean_AN_T
FROM  Lakes
GROUP BY Province;

Same table ordered by increasing temperatures

SELECT province, AVG(TMEAN_AN) as Mean_AN_T
FROM  Lakes
GROUP BY Province ORDER BY AVG(TMEAN_AN);

Question 5 - Which province has the lake with the highest maximum precipitation (column TMAX_ANN)?
answer BRITISH COLUMBIA 14.60

Update statement: Give all rows where the province name includes NWT the same province name 'NWT' - Careful, this actually modifies the table!

UPDATE Lakes
SET Province='NWT'
WHERE Province like '%NWT%';

Count the number of species in each lake. Display lake name and species number.

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;

Same query, but only display the first 20.

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;

Count the number of species in each Ecozone and Ecoprovince. Display lake name and species number.

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

List the species that are found in only one Ecoprovince.

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 Generate a table that lists the full name of each species of Daphnia and the number of lakes where this species is present?

Answer

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;

Click here for a list of MySQL mathematical functions

Create a list of all the species found in the lakes in Quebec, with the full names of the species and the number of occurences

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;

Alternatively, and equivalently…

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;

For all possible combination of lakes found in the Baffin Uplands Ecoprovince, return the difference in mean annual temperature

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

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

Generate a table containing the count of the presence of each species in lakes North and South of the 50th parallel (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;

Equivalently,

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 Using a subquery, find the mean elevation of lakes (MEAN_ELE column) where are species of Daphnia is present
Answer 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;

You are given a dataset in the following format:

You are told that thousands more trees will have to be entered in this way. How would you go about designing a database to store this information?

FOLLOW THESE STEPS TO CREATE THE DATABASE IN LIBREOFFICE BASE

From within R: On first use only:

install RMySQL package

install.packages('RMySQL')

R Code

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

Plot some data

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