Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
mysql [2012/11/08 16:52] glaroc |
mysql [2014/04/01 16:56] (current) glaroc |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| ======= Introduction to Database Management Systems with MySQL ======= | ======= Introduction to Database Management Systems with MySQL ======= | ||
| - | [[http://prezi.com/mvnivafqdmfs/present/?auth_key=cevu1xx&follow=ktr6emnemif_|Link to Prezi presentation - Simultaneous presentation]] | + | Workshop given by Guillaume Larocque, research professional at the QCBS, at McGill University on November 2 and 9, 2012. |
| + | |||
| + | [[http://prezi.com/vswzpe89tbwp/present/?auth_key=gv8krfa&follow=ktr6emnemif_|Link to Prezi presentation - Simultaneous presentation]] | ||
| [[http://prezi.com/vswzpe89tbwp/introduction-to-database-management-with-mysql/|Link to Prezi presentation]] | [[http://prezi.com/vswzpe89tbwp/introduction-to-database-management-with-mysql/|Link to Prezi presentation]] | ||
| Line 34: | Line 36: | ||
| ==== CREATE YOUR FIRST DATABASE ==== | ==== CREATE YOUR FIRST DATABASE ==== | ||
| - | **On Linux/Mac**: | + | **On Linux**: |
| Open a terminal and type | Open a terminal and type | ||
| <file> | <file> | ||
| Line 40: | Line 42: | ||
| </file> | </file> | ||
| and type your password. You are now in MySQL. | and type your password. You are now in MySQL. | ||
| + | |||
| + | **On MAC**: | ||
| + | <code> | ||
| + | /usr/local/mysql/bin/mysql -u root | ||
| + | </code> | ||
| **On Windows**: In the Start menu, find MySQL Server... Command Line Client. | **On Windows**: In the Start menu, find MySQL Server... Command Line Client. | ||
| Line 68: | Line 75: | ||
| |[[http://dev.mysql.com/doc/refman/5.6/en/delete.html|DELETE]]|delete columns| | |[[http://dev.mysql.com/doc/refman/5.6/en/delete.html|DELETE]]|delete columns| | ||
| |[[http://dev.mysql.com/doc/refman/5.6/en/alter-table.html|ALTER TABLE]]|add new columns, or modify the column types| | |[[http://dev.mysql.com/doc/refman/5.6/en/alter-table.html|ALTER TABLE]]|add new columns, or modify the column types| | ||
| - | |[[http://dev.mysql.com/doc/refman/5.6/en/drop.html|DROP]]|delete database or table| | + | |[[http://dev.mysql.com/doc/refman/5.6/en/drop-table.html|DROP]]|delete [[http://dev.mysql.com/doc/refman/5.5/en/drop-database.html|database]] or [[http://dev.mysql.com/doc/refman/5.5/en/drop-table.html|table]]| |
| ===== Exercise 1 - CREATE AND INSERT statements ===== | ===== Exercise 1 - CREATE AND INSERT statements ===== | ||
| Line 157: | Line 164: | ||
| </file> | </file> | ||
| ++++ | ++++ | ||
| - | However, we will use LibreOffice instead | + | However, we will use LibreOffice instead... |
| ===== Importing data with LibreOffice ===== | ===== Importing data with LibreOffice ===== | ||
| Line 185: | Line 192: | ||
| ** MATH AND GROUP BY (Aggregate) Functions** | ** MATH AND GROUP BY (Aggregate) Functions** | ||
| - | |AVG() |Return the average value of the argument| | + | |avg() |Return the average value of the argument| |
| - | |COUNT(DISTINCT) |Return the count of a number of different values| | + | |count(DISTINCT) |Return the count of a number of different values| |
| - | |COUNT() |Return a count of the number of rows returned| | + | |count() |Return a count of the number of rows returned| |
| - | |GROUP_CONCAT() |Return a concatenated string| | + | |group_concat() |Return a concatenated string| |
| - | |MAX() |Return the maximum value| | + | |max() |Return the maximum value| |
| - | |MIN() |Return the minimum value| | + | |min() |Return the minimum value| |
| - | |STD() |Return the population standard deviation| | + | |stddev() |Return the population standard deviation| |
| - | |STDDEV_POP() |Return the population standard deviation| | + | |stddev_pop() |Return the population standard deviation| |
| - | |STDDEV_SAMP() |Return the sample standard deviation| | + | |stddev_samp() |Return the sample standard deviation| |
| - | |STDDEV() |Return the population standard deviation| | + | |sum() |Return the sum| |
| - | |SUM() |Return the sum| | + | |var_pop() |Return the population standard variance| |
| - | |VAR_POP() |Return the population standard variance| | + | |var_samp() |Return the sample variance| |
| - | |VAR_SAMP() |Return the sample variance| | + | |variance() |Return the population standard variance| |
| - | |VARIANCE() |Return the population standard variance| | + | [[http://www.postgresql.org/docs/9.3/static/functions-aggregate.html|Full list]] |
| - | [[http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html|Full list]] | + | |
| \\ | \\ | ||
| \\ | \\ | ||
| Line 323: | Line 329: | ||
| </file> | </file> | ||
| - | ===== Exercise 4 - Working with multiple tables ===== | + | ===== Exercise 5 - Working with multiple tables ===== |
| Count the number of species in each lake. Display lake name and species number. | Count the number of species in each lake. Display lake name and species number. | ||
| Line 362: | Line 368: | ||
| <code>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;</code> | <code>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;</code> | ||
| - | ^Full_name^count(DISTINCT a.Lake_ID)^ | + | ^Full_name^Count^ |
| | Daphnia pulex Leydig, 1860 | 234 | | | Daphnia pulex Leydig, 1860 | 234 | | ||
| | Daphnia similis Claus, 1876 | 17 | | | Daphnia similis Claus, 1876 | 17 | | ||
| Line 385: | Line 391: | ||
| [[http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html|Click here for a list of MySQL mathematical functions]] | [[http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html|Click here for a list of MySQL mathematical functions]] | ||
| - | ===== Exercise 5 - JOIN Operations ===== | + | ===== Exercise 6 - JOIN Operations ===== |
| |JOIN (INNNER JOIN, CROSS JOIN)| Keep all possible combinations of rows from A and B| | |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| | |LEFT JOIN| Keep all records of table A, join with elements from B that are present in A| | ||
| Line 420: | Line 426: | ||
| </file> | </file> | ||
| - | ===== Exercise 6 - Subqueries ===== | + | ===== Exercise 7 - Subqueries ===== |
| Generate a table containing the count of the presence of each species in lakes North and South of the 50th parallel (latitude). | Generate a table containing the count of the presence of each species in lakes North and South of the 50th parallel (latitude). | ||
| Line 461: | Line 467: | ||
| :?: 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? | :?: 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? | ||
| - | ++++ Steps to follow| | + | ++++ FOLLOW THESE STEPS TO CREATE THE DATABASE IN LIBREOFFICE BASE| |
| - | :?: Use the Table design view to create the following table. Specify the Species_ID field as the primary key and with AutoValue: on. | + | :?: Use the Table design view to create the following table. Specify the Species_ID field as the primary key (++ right click on row| {{:screen.png}} ++) and with AutoValue: on. |
| Table name: Tree_species | Table name: Tree_species | ||
| ^Species_ID^Species_name^ | ^Species_ID^Species_name^ | ||
| Line 473: | Line 479: | ||
| |6|Carpinus caroliana| | |6|Carpinus caroliana| | ||
| - | :?: Create a form which allows you to fill the table above. | + | :?: Create a form (with the form wizard) which allows you to fill the table above. |
| \\ | \\ | ||
| :?: Repeat the process with this table. You can fill this one directly in the design view if you want. Health_ID is the primary key. | :?: Repeat the process with this table. You can fill this one directly in the design view if you want. Health_ID is the primary key. | ||
| Line 483: | Line 489: | ||
| |4|Dead| | |4|Dead| | ||
| - | :?: Create this table in the design view and create a form to fill it, with drop-down menus (list box) to select the species names and the tree health from the tables above. Tree_ID is the primary key. | + | :?: Create this empty table in the design view and create a form to fill it in the form design view, with drop-down menus (list boxes) to select the species names and the tree health from the tables above. Tree_ID is the primary key. |
| Table name: Tree_Plots | Table name: Tree_Plots | ||
| ^Plot_ID^Tree_ID^Species_ID^DBH^Health_ID^ | ^Plot_ID^Tree_ID^Species_ID^DBH^Health_ID^ | ||
