Package and Function:

  R Package : RMySQL

  R Function :dbConnect(MySQL(),user=root,password=,dbname=sangeeth,host=localhost)to create a connection object to MySQL database

  R Function : dbListTables(connection object) -- to list the tables available in the database

  R Function : dbSendQuery(connection,select * from table) -- to selectdata from table

  R Function : dbSendQuery(connection,INSERT INTO `tab_2`(`s.no`,`name`,`age`,`income`)VALUES([value-1],[value-2],[value-3],[value-4])) -- to insert data into table

  R Function : dbSendQuery(connection,UPDATE `tab_2` SET`s.no`=[value-1],`name`=[value-2],`age`=[value-3] ,`income`=[value-4] WHERE 1)-- to update data into table

  R Function : dbWriteTable(connection,connection, iris, iris[,],overwrite =TRUE)-- to create table in MySQL

  R Function : dbSendQuery(connection,drop table if exists table) -- to drop table from MySQL

#Loading Required Packages

#install.packages(“RMySQL”)
library(“RMySQL”)

#Create connection object to MySQL database
connection<-dbConnect(MySQL(),user=’root’,password=”root123″,dbname=”sangeetha”,host=”localhost”)

#List tables available in this database
dbListTables(connection)

#Query “tab_1″ table to get all its rows
tab_2_row<-dbSendQuery(connection,”select * from tab_2″)
tab_data<-fetch(tab_2_row)
print(tab_data)

#Select with age using query
tab_2_age<-dbSendQuery(connection,”select * from tab_2 where age=’22′”)
age_data<-fetch(tab_2_age)
print(age_data)

#Insert data into tables
dbSendQuery(connection,”INSERT INTO `tab_2`(`s.no`, `name`, `age`, `income`) VALUES (‘9′,’kushi’,’20’,’195′)”)

#Update rows in the table
dbSendQuery(connection , “UPDATE `tab_2` SET `s.no`=’4′,`name`=’loganathan’,`age`=’56’,`income`=’834′ WHERE name=’loganathan'”)

#Creating table in MySQL
dbWriteTable(connection, “iris”, iris[,],overwrite=TRUE)

#Dropping table in MySQL
dbSendQuery(connection,”drop table if exists tab_1″)