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.4We 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.1As 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.3This only scratches the surface of the SQL statements that could be run to extract data from a MySQL database.
