Using SQL to Access Data in MySQL Databases

July 28th, 2009

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.

Comments are closed.

Statistics Books