In the relational database management system, the data is stored in a normalized format. Therefore, to complete statistical computing, we need very advanced and complex SQL queries. The large and huge data which is present in the form of tables require SQL queries to extract the data from it.
R can easily connect with many of the relational databases like MySql, SQL Server, Oracle, etc. When we extract the information from these databases, by default, the information is extracted in the form of data frame. Once, the data comes from the database to the R environment; it will become a normal R dataset. The data analyst can easily analyze or manipulate the data with the help of all the powerful packages and functions.
RMySQL package is one of the most important built-in package of R. This package provides native connectivity between the R and MySql database. In R, to work with MySql database, we first have to install the RMySQL package with the help of the familiar command, which is as follows:
When we run the above command in the R environment, it will start downloading the package RMySQL.
We have created a database employee in which there is a table employee_info, which has the following record.
We will use the data which we have mentioned above in our upcoming topics.
Create a connection between R and MySql
To work with MySql database, it is required to create a connection object between R and the database. For creating a connection, R provides dbConnect() function. This function takes the username, password, database name, and host name as input parameters. Let's see an example to understand how the dbConnect() function is used to connect with the database.
R MySQL Commands
In R, we can perform all the SQL commands like insert, delete, update, etc. For performing the query on the database, R provides the dbSendQuery() function. The query is executed in MySQL, and the result set is returned using the R fetch () function. Finally, it is stored in R as a data frame. Let's see the example of each and every SQL command to understand how dbSendQuery() and fetch() functions are used.
R provides an additional function to create a table into the database i.e., dbWriteTable(). This function creates a table in the database; if it does not exist else, it will overwrite the table. This function takes the data frame as an input.
We can simply select the record from the table with the help of the fetch() and dbSendQuery() function. Let's see an example to understand how to select query works with these two functions.
Select with where clause
We can select the specific record from the table with the help of the fetch() and dbSendQuery() function. Let's see an example to understand how to select query works with where clause and these two functions.
We can insert the data into tables with the help of the familiar method dbSendQuery() function.
Updating a record in the table is much easier. For this purpose, we have to pass the update query to the dbSendQuery() function.
Below is an example in which we delete a specific row from the table by passing the delete query in the dbSendQuery() function.
Below is an example in which we drop a table from the database by passing the appropriate drop query in the dbSendQuery() function.