R MySQL Connection

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

To read and write MySQL files in R, you need to install the extension package, and we can input the following command in the R console to install it:

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

Check if the installation is successful:

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

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

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

Create the data table w in the test database3codebox, the table structure and data code are as follows:

--
--　Table structure `w3codebox`
--
　
CREATE TABLE `w3codebox(
　　int(11NOT NULL
　　char(2NOT NULL
　　varchar(255NOT NULL
　　`likes` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
　
--
--　Transfer data in table `w3codebox`
--
　
INSERT INTO `w3codebox` (`id`, `name`, `url`, `likes`) VALUES
(1, 'Google', 'www.google.com',　111),
(2, 'w3codebox', 'www.oldtoolbag.com',　222),
(3, 'Taobao', 'www.taobao.com',　333);

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

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

Next, we can use dbSendQuery to read the database table, and the result set is obtained through the fetch() function:

library(RMySQL)
# Query the sites table, CRUD operations can be implemented through the SQL statement in the second parameter
result = dbSendQuery(mysqlconnection, "select　*　from sites)
# Get the first two rows of data
data.frame = fetch(result, n =　2)
print(data.fame)