The data is stored in a normalized format in the R database system. To carry statistical computing we need complex and advanced SQL queries. But in R programming we can connect very easily to many relational databases like Mysql, Sql server, Oracle, etc. It becomes a normal dataset when available in R programming and can be manipulated or analyzed using all the powerful functions and packages.
RMySQL Package
RMySQL is the built-in package in R programming, which provides built-in connectivity between MySql databases. The package can be installed in the R environment with the help of the following command.
Connecting R to MySql
We can create a connection object in R to connect to the database when the package is installed. It will take the username, password, database name, and hostname as input.
Output:
Querying the Tables in R Database
With the use of dbSendQuery() function, you can query the database tables in MySql. The query will be executed in MySql and the result set is returned using the R fetch() function.
Output:
Query with Filter Clause
To get the result we can pass any valid select query.
Output:
Updating Rows in the Tables
Rows can be updated in the Mysql table bypassing the update query to the dbSendQuery() function.
The table will be updated in the MySql Environment after executing this query.
Inserting Data into the Tables
The rows in the table will be inserted in the MySql Environment after executing this query.
Creating Tables in MySql
Using the function dbWriteTable() we can create tables in MySql. If the table already exists it overwrites the table and takes a data frame as input.
Dropping Tables in MySql
We can drop the tables in the MySql database passing the drop table statement into the dbSendQuery() in the same way we used it for querying data from tables.
After passing the drop table statement into the dbSendQuery() tables can be dropped in the MySql database in the same way we used it for querying data from tables.
The above query will drop the student table from the database in the MySql Environment.