学习如何在R中使用SQLite，这是一种非常轻量级的关系数据库管理系统(RDBMS)。
创建数据库和表
第一步是创建数据库。使用dbConnect()函数为mtcars数据集创建一个适当的数据库。
代码语言：javascript复制# Load the RSQLite Library
library(RSQLite)
# Load the mtcars as an R data frame put the row names as a column.
data("mtcars")
mtcars$car_names <- rownames(mtcars)
rownames(mtcars) <- c()
head(mtcars)
# Create a connection to our new database, CarsDB.db
# you can check that the .db file has been created on your working directory
conn <- dbConnect(RSQLite::SQLite(), "CarsDB.db")
一旦创建了数据库，就可以继续使用dbWriteTable()函数在数据库中创建一个表。这个函数可以接受多个参数:
conn:连接到你的SQLite数据库 name:您想要用于表的名称 value:插入的数据
之后，可以使用函数dbListTables()和SQLite数据库连接作为参数，检查是否已经成功创建了表。
代码语言：javascript复制# Write the mtcars dataset into a table names mtcars_data
dbWriteTable(conn, "cars_data", mtcars)
# List all the tables available in the database
dbListTables(conn)
##'cars_data'
如果你有多个数据，可以通过在dbWriteTable()中设置可选参数append = TRUE，在已有的表中添加更多的数据。例如，可以通过添加两个不同的数据来创建一个新的表，其中包含一些汽车和制造商。
代码语言：javascript复制# Create toy data frames
car <- c('Camaro', 'California', 'Mustang', 'Explorer')
make <- c('Chevrolet','Ferrari','Ford','Ford')
df1 <- data.frame(car,make)
car <- c('Corolla', 'Lancer', 'Sportage', 'XE')
make <- c('Toyota','Mitsubishi','Kia','Jaguar')
df2 <- data.frame(car,make)
# Add them to a list
dfList <- list(df1,df2)
# Write a table by appending the data frames inside the list
for(k in 1:length(dfList)){
dbWriteTable(conn,"Cars_and_Makes", dfList[[k]], append = TRUE)
}
# List all the Tables
dbListTables(conn)
##"Cars_and_Makes" "cars_data"
确保所有数据都在新表中
代码语言：javascript复制dbGetQuery(conn, "SELECT * FROM Cars_and_Makes")
car | make |
|---|---|
Camaro | Chevrolet |
California | Ferrari |
Mustang | Ford |
Explorer | Ford |
Corolla | Toyota |
Lancer | Mitsubishi |
Sportage | Kia |
XE | Jaguar |
执行SQL查询
可以使用dbGetQuery()执行有效的SQL查询，该函数有以下参数：
conn:连接SQLite数据库 query:执行的SQL查询
NOTE:通过RSQLIte，可以执行任何查询，从简单的SELECT语句到JOINS(除了RIGHT OUTER JOINS和FULL OUTER JOINS，这是在SQLite中不支持的)。
代码语言：javascript复制# Gather the first 5 rows in the cars_data table
dbGetQuery(conn, "SELECT * FROM cars_data LIMIT 5")
# Get the car names and horsepower starting with M that have 6 or 8 cylinders
dbGetQuery(conn,"SELECT car_names, hp, cyl FROM cars_data
WHERE car_names LIKE 'M%' AND cyl IN (6,8)")
car_names | hp | cyl |
|---|---|---|
Mazda RX4 | 110 | 6 |
Mazda RX4 Wag | 110 | 6 |
Merc 280 | 123 | 6 |
Merc 280C | 123 | 6 |
Merc 450SE | 180 | 8 |
Merc 450SL | 180 | 8 |
Merc 450SLC | 180 | 8 |
Maserati Bora | 335 | 8 |
要将查询的结果存储，以便在R中继续执行进一步的操作，只需将查询的结果赋值给一个变量即可。
代码语言：javascript复制avg_HpCyl <- dbGetQuery(conn,"SELECT cyl, AVG(hp) AS 'average_hp'FROM cars_data
GROUP BY cyl
ORDER BY average_hp")
avg_HpCyl
加入变量查询
从R操作SQLite数据库的最大优点之一是能够使用参数化查询。也就是说，能够使用R工作空间中可用的变量查询SQLite数据库。
代码语言：javascript复制# Lets assume that there is some user input that asks us to look only into cars that have over 18 miles per gallon (mpg)
# and more than 6 cylinders
mpg <- 18
cyl <- 6
Result <- dbGetQuery(conn, 'SELECT car_names, mpg, cyl FROM cars_data WHERE mpg >= ? AND cyl >= ?', params = c(mpg,cyl))
Result
car_names | mpg | cyl |
|---|---|---|
Mazda RX4 | 21.0 | 6 |
Mazda RX4 Wag | 21.0 | 6 |
Hornet 4 Drive | 21.4 | 6 |
Hornet Sportabout | 18.7 | 8 |
Valiant | 18.1 | 6 |
Merc 280 | 19.2 | 6 |
Pontiac Firebird | 19.2 | 8 |
Ferrari Dino | 19.7 | 6 |
不返回表格数据的查询
可能希望执行不一定返回表格数据的SQL查询。这些操作的例子包括插入、更新或删除表记录。为此，我们可以使用函数dbExecute()，它以一个SQLite数据库连接和一个SQL查询作为参数。
代码语言：javascript复制# Visualize the table before deletion
dbGetQuery(conn, "SELECT * FROM cars_data LIMIT 10")
# Delete the column belonging to the Mazda RX4.
dbExecute(conn, "DELETE FROM cars_data WHERE car_names = 'Mazda RX4'")
# Visualize the new table after deletion
dbGetQuery(conn, "SELECT * FROM cars_data LIMIT 10")
关闭
在R中完成了SQLite数据库的操作，调用函数dbDisconnect()是很重要的。这确保释放了数据库连接一直在使用的资源。
代码语言：javascript复制# Close the database connection to CarsDB
dbDisconnect(conn)
