Getting Data from MySQL database

In this tutorial we are going to fetch data from database. We will use famous hr_schema.

Here is link to creating hr schema on database https://www.sqltutorial.org/wp-content/uploads/2020/04/mysql.txt

And link to inserting data to the schema https://www.sqltutorial.org/wp-content/uploads/2020/04/mysql-data.txt

library(RMySQL)

mysqlconnection = dbConnect(RMySQL::MySQL(),
                            dbname='hr_schema',
                            host='localhost',
                            port=3306,
                            user='root',
                            password='6321a7b8')


dbListTables(mysqlconnection)

dataset = dbSendQuery(mysqlconnection, "select * from customers cus join orders ord
on cus.Customer_ID=ord.Customer_ID   ") 

orders=fetch(dataset,100000)

orders$Sales =( gsub("\\$", "", orders$Sales))
orders$Sales =as.numeric( gsub("\\.00", "", orders$Sales))

print(orders)                # Store the result in a R data frame object. n = 5 is used to fetch first 5 rows.

summary(orders)

library(ggplot2)
library(tidyverse)

ggplot(data = orders, aes(x = Segment, y = Profit)) +
  geom_point()

Note: Great thanks to my student Aida khanum (SQL developer and hope in future be great Data Analyist) for making this markdown article.