Introduction to Database Management Systems with MySQL

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

Installing MySQL

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

Access to PHPMyAdmin

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

login:qcbsworkshop-ro
password:YyQphZLHxWtPR7fN

Group discussion

MySQL Exercises

CREATE YOUR FIRST DATABASE

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;

MAJOR MySQL commands

SHOWshow databases or tables
DESCRIBEdescribe table
CREATEcreate database or table
INSERTinsert a row in a table
SELECTperform queries
UPDATEmodify existing columns
DELETEdelete columns
ALTER TABLEadd new columns, or modify the column types
DROPdelete database or table

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

More common data types

function callmeaningexample
int()integer(maximum length)5
decimal(,)decimal(total digits, digits after period)122.52
varchar()character(maximum length) 'tree swallow'
texttext of unspecified length 'Once upon a time, there was…'
datetimedate and time '2012-10-21 10:03:21'
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

first_namelast_nameBirth_dategenderheightTrade_mark
BruceWillis1955-03-19M1.81Frequently plays a man who suffered a tragedy, had lost something or had a crisis of confidence or conscience.
EmmaWatson1990-04-15F1.65Often portrays literary characters

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

However, we will use LibreOffice instead…

Configuring the LibreOffice/MySQL connexion

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.

Importing a table

  • 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

avg() Return the average value of the argument
count(DISTINCT) Return the count of a number of different values
count() Return a count of the number of rows returned
group_concat() Return a concatenated string
max() Return the maximum value
min() Return the minimum value
stddev() Return the population standard deviation
stddev_pop() Return the population standard deviation
stddev_samp() Return the sample standard deviation
sum() Return the sum
var_pop() Return the population standard variance
var_samp() Return the sample variance
variance() Return the population standard variance

Full list

Conditional selections used in the where clause

= Equal
> Greater than
< Less than
>= Greater than or equal
< = Less than or equal
<> or !=Not equal to
LIKEString matching

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
Question 2 :?: - What is the average Elevation (MEAN_ELE column) of all lakes in the Montane Cordillera Ecozone (use the avg() operator)?
answer

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
Question 4 :?: - How many species of Daphnia are there in the Species_Acro table?
answer

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

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


Click here for a list of MySQL mathematical functions

JOIN (INNNER JOIN, CROSS JOIN) Keep all possible combinations of rows from A and B
LEFT JOIN Keep all records of table A, join with elements from B that are present in A
RIGHT JOIN Keep all records of table B, join with elements from A that are present in B
OUTER JOIN Keep all records from table A and B

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

Using the LibreOffice Base front-end

You are given a dataset in the following format:

Plot 1
Acer rubrum12.4alive
Acer saccharum25.3alive
Fagus grandifolia14.1alive
Fagus grandifolia66.0Diseased bark
Fraxinus americana30.1alive
Plot 2
Quercus rubra12.4alive
Fraxinus americana64.2diseased bark
Fraxinus americana53.1dead
Carpinus caroliana10.3alive

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

Linking R with MySQL

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)