Easy Tutorial
R Input Csv File R Bar Charts R Package R Input Json File R Scatterplots Charts R Line_Charts R Setup R Factor R Tutorial R Array R List R Linear Regression R Decision Making R Vector R Input Excel File R String R Examples R Data Reshaping R Java R Functions R Charts Cn R Matrix R Func Mean R Basic Operators R Loop R Comments R Func Median R Input Xml File R Pie Charts R Data Frame R Mysql Connect R Basic Syntax R Data Types R Calculate The Most Element
❮ R Data Frame R Basic Syntax ❯

R MySQL Connection

MySQL is the most popular relational database management system, and it is one of the best RDBMS (Relational Database Management System) applications for web applications.

If you are not familiar with MySQL, you can refer to the MySQL Tutorial.

To read and write MySQL files in R, you need to install the extension package. You can install it by entering the following command in the R console:

install.packages("RMySQL", repos = "https://mirrors.ustc.edu.cn/CRAN/")

To check if the installation was successful:

> any(grepl("RMySQL", installed.packages()))
[1] TRUE

MySQL is currently owned by Oracle, so many people use its clone version MariaDB. MariaDB is open-source under the GNU GPL, and its development is led by some of the original MySQL developers, so the syntax and operations are quite similar:

install.packages("RMariaDB", repos = "https://mirrors.ustc.edu.cn/CRAN/")

Create a table named tutorialpro in the test database with the following table structure and data code:

Example

--
-- Table structure for `tutorialpro`
--

CREATE TABLE `tutorialpro` (
  `id` int(11) NOT NULL,
  `name` char(20) NOT NULL,
  `url` varchar(255) NOT NULL,
  `likes` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `tutorialpro`
--

INSERT INTO `tutorialpro` (`id`, `name`, `url`, `likes`) VALUES
(1, 'Google', 'www.google.com', 111),
(2, 'tutorialpro', 'www.tutorialpro.org', 222),
(3, 'Taobao', 'www.taobao.com', 333);

Next, we can use the RMySQL package to read the data:

Example

library(RMySQL)

# dbname is the database name, please fill in the parameters according to your actual situation
mysqlconnection = dbConnect(MySQL(), user = 'root', password = '', dbname = 'test', host = 'localhost')

# List tables
dbListTables(mysqlconnection)

Next, we can use dbSendQuery to read the tables from the database, and the result set can be fetched using the fetch() function:

Example

library(RMySQL)
# Query the sites table, CRUD operations can be achieved through the SQL statement in the second parameter
result = dbSendQuery(mysqlconnection, "select * from sites")

# Fetch the first two rows of data
data.frame = fetch(result, n = 2)
print(data.frame)
❮ R Data Frame R Basic Syntax ❯