In a previous post we have looked at opening and closing connections from R to a MySQL database and some basic operations for creating and deleting tables. In this post we will consider using SQL queries to extract parts of a table from the database based on different search criteria.
We can run an SQL query with the dbGetQuery function which expects a connection object and a string with the SQL commands. If we wanted to take a subset of the CO2 dataset and view only those observations taken in Quebec we could use the following code:
> dbGetQuery(con, paste("select * from co2 where Type = 'Quebec'"))
row_names Plant Type Treatment conc uptake
1 1 Qn1 Quebec nonchilled 95 16.0
2 2 Qn1 Quebec nonchilled 175 30.4
...
41 41 Qc3 Quebec chilled 675 39.6
42 42 Qc3 Quebec chilled 1000 41.4 |
We can narrow the search by specifying a condition on one of the other columns, for example limit the search to concentrations under 300 units:
> dbGetQuery(con, paste("select * from co2 where Type = 'Quebec' and Conc < 300"))
row_names Plant Type Treatment conc uptake
1 1 Qn1 Quebec nonchilled 95 16.0
2 2 Qn1 Quebec nonchilled 175 30.4
...
17 37 Qc3 Quebec chilled 175 21.0
18 38 Qc3 Quebec chilled 250 38.1 |
As a final example we could look for all observations with a concentration less than 300 units buy order the data by increasing values of uptake:
> dbGetQuery(con, paste("select * from co2 where Conc < 300 order by uptake"))
row_names Plant Type Treatment conc uptake
1 71 Mc2 Mississippi chilled 95 7.7
2 29 Qc2 Quebec chilled 95 9.3
...
35 38 Qc3 Quebec chilled 250 38.1
36 17 Qn3 Quebec nonchilled 250 40.3 |
This only scratches the surface of the SQL statements that could be run to extract data from a MySQL database.
