YouTip

# R MySQL Connection MySQL is the most popular relational database management system. In terms of web applications, MySQL is one of the best RDBMS (Relational Database Management System) application software. If you are not familiar with MySQL, you can first refer to: (#) To read and write MySQL files in R, you need to install an extension package. We 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())) TRUE MySQL is currently owned by Oracle, so many people use its fork, MariaDB. MariaDB is open source under the GNU GPL. The development of MariaDB is led by some of the original developers of MySQL, so the syntax and operations are very similar: install.packages("RMariaDB", repos = "https://mirrors.ustc.edu.cn/CRAN/") Create a table named `` in the `test` database. The table structure and data code are as follows: ## Example -- -- Table structure for table ``-- CREATE TABLE``(`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 ``-- INSERT INTO``(`id`, `name`, `url`, `likes`)VALUES(1, 'Google', 'www.google.com', 111), (2, '', 'www.', 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 here 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 table from the database. The result set is obtained via the `fetch()` function: ## Example library(RMySQL) # Query the sites table. Insert, delete, update, and select operations can be performed via 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)