RSQLite系列1——入门教程

浏览: 2241
SQL

1.什么是SQLite

SQLite 是一个软件库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。SQLite 是在世界上最广泛部署的 SQL 数据库引擎。SQLite 源代码不受版权限制。

2.为何选择SQLite

选择SQLite的原因很简单，因为它:

  • 开源
  • 轻量级
  • 安装配置简单
  • 不存在繁琐的用户管理
  • 兼容标准的SQL语句操作

这几个特性对一个SQL新手来说，是最好不过的。

3.RSQLite入门

基本概念

  • Table：观测的集合
  • Field：类似于R中data.frame的column names(列名)
  • Column：变量
  • Row：观测
  • data types:
    • NULL
    • INTEGER,带符号的整数
    • REAL,浮点值
    • TEXT,文本字符串
    • BLOB,blob数值（binary large object）,主要针对图片视频等数据对象。

安装配置

install.packages("RSQLite")

建立一个连接对象

library(RSQLite)


## Loading required package: DBI

## Loading required package: methods


db<- dbConnect(SQLite(), dbname = 'Test.sqlite')

这样，一个连接数据库的对象就建立起来了。不过，严格来说，这个时候，数据库还未建立起来。

建立一个表格

首先，使用CREATE语句建立一个表格。

#当已经存在该table,把它删除，否则后面建立table时会报错

dbSendQuery(conn = db,

            "drop table if exists MOBILE_PHONE")


## <SQLiteResult>


dbSendQuery(conn = db,

            "CREATE TABLE MOBILE_PHONE

            (Product_ID INTEGER,

            product_Name TEXT,

            price REAL,

            Brand_name TEXT)")


## <SQLiteResult>

导入数据

向表格添加数据——手动添加

向表格添加数据可以使用INSERT语句。

dbSendQuery(conn = db,

            "INSERT INTO MOBILE_PHONE

            VALUES(1,'iPhone 6s',6000,'Apple')")


## <SQLiteResult>


dbSendQuery(conn = db,

            "INSERT INTO MOBILE_PHONE

            VALUES(2,'华为P8',3000,'华为')")


## <SQLiteResult>


dbSendQuery(conn = db,

            "INSERT INTO MOBILE_PHONE

            VALUES(3,'三星 Galaxy S6',5000,'三星')")


## <SQLiteResult>

查询结果

# the tables in the database

dbListTables(db)


## [1] "MOBILE_PHONE"


#the columns in a table

dbListFields(db,"MOBILE_PHONE")


## [1] "Product_ID"   "product_Name" "price"        "Brand_name"


#the data in the table

head(dbReadTable(db,"MOBILE_PHONE"))


##   Product_ID   product_Name price Brand_name

## 1          1      iPhone 6s  6000      Apple

## 2          2         华为P8  3000       华为

## 3          3 三星 Galaxy S6  5000       三星

向表格添加数据——导入外部数据(csv,excel,data.frame)

以ISLR包中的Hitters数据集为例，导入该数据集。该数据集描述了美国1986年和1987年的棒球运动员相关数据。

library(ISLR)

str(Hitters)


## 'data.frame':	322 obs. of  20 variables:

##  $ AtBat    : int  293 315 479 496 321 594 185 298 323 401 ...

##  $ Hits     : int  66 81 130 141 87 169 37 73 81 92 ...

##  $ HmRun    : int  1 7 18 20 10 4 1 0 6 17 ...

##  $ Runs     : int  30 24 66 65 39 74 23 24 26 49 ...

##  $ RBI      : int  29 38 72 78 42 51 8 24 32 66 ...

##  $ Walks    : int  14 39 76 37 30 35 21 7 8 65 ...

##  $ Years    : int  1 14 3 11 2 11 2 3 2 13 ...

##  $ CAtBat   : int  293 3449 1624 5628 396 4408 214 509 341 5206 ...

##  $ CHits    : int  66 835 457 1575 101 1133 42 108 86 1332 ...

##  $ CHmRun   : int  1 69 63 225 12 19 1 0 6 253 ...

##  $ CRuns    : int  30 321 224 828 48 501 30 41 32 784 ...

##  $ CRBI     : int  29 414 266 838 46 336 9 37 34 890 ...

##  $ CWalks   : int  14 375 263 354 33 194 24 12 8 866 ...

##  $ League   : Factor w/ 2 levels "A","N": 1 2 1 2 2 1 2 1 2 1 ...

##  $ Division : Factor w/ 2 levels "E","W": 1 2 2 1 1 2 1 2 2 1 ...

##  $ PutOuts  : int  446 632 880 200 805 282 76 121 143 0 ...

##  $ Assists  : int  33 43 82 11 40 421 127 283 290 0 ...

##  $ Errors   : int  20 10 14 3 4 25 7 9 19 0 ...

##  $ Salary   : num  NA 475 480 500 91.5 750 70 100 75 1100 ...

##  $ NewLeague: Factor w/ 2 levels "A","N": 1 2 1 2 2 1 1 1 2 1 ...


#建立连接

db.hitters<-dbConnect(SQLite(),dbname = "Hitters.sqlite")



#写入数据

dbWriteTable(conn = db.hitters,name = "Hitters",value = Hitters,overwrite = T,row.names = FALSE)


## [1] TRUE


#读取表格

tmp = dbReadTable(db.hitters,"Hitters")

head(tmp)


##   AtBat Hits HmRun Runs RBI Walks Years CAtBat CHits CHmRun CRuns CRBI

## 1   293   66     1   30  29    14     1    293    66      1    30   29

## 2   315   81     7   24  38    39    14   3449   835     69   321  414

## 3   479  130    18   66  72    76     3   1624   457     63   224  266

## 4   496  141    20   65  78    37    11   5628  1575    225   828  838

## 5   321   87    10   39  42    30     2    396   101     12    48   46

## 6   594  169     4   74  51    35    11   4408  1133     19   501  336

##   CWalks League Division PutOuts Assists Errors Salary NewLeague

## 1     14      A        E     446      33     20     NA         A

## 2    375      N        W     632      43     10  475.0         N

## 3    263      A        W     880      82     14  480.0         A

## 4    354      N        E     200      11      3  500.0         N

## 5     33      N        E     805      40      4   91.5         N

## 6    194      A        W     282     421     25  750.0         A

建立基本查询

使用SELECT语句建立一个关于行的查询。

dbGetQuery(db.hitters,"select * from Hitters where Salary >= 1000")[1:5,]


##   AtBat Hits HmRun Runs RBI Walks Years CAtBat CHits CHmRun CRuns CRBI

## 1   401   92    17   49  66    65    13   5206  1332    253   784  890

## 2   591  168    19   80  72    39     9   4478  1307    113   634  563

## 3   627  177    25   98  81    70     6   3210   927    133   529  472

## 4   677  238    31  117 113    53     5   2223   737     93   349  401

## 5   614  163    29   89  83    75    11   5017  1388    266   813  822

##   CWalks League Division PutOuts Assists Errors Salary NewLeague

## 1    866      A        E       0       0      0   1100         A

## 2    319      A        W      67     147      4   1200         A

## 3    313      A        E     240     482     13   1350         A

## 4    171      A        E    1377     100      6   1975         A

## 5    617      N        W     303       6      6   1900         N

使用SELECT语句建立一个关于列的查询。

dbGetQuery(db.hitters,"select League,Hits,Salary from Hitters where League = 'A'")[1:5,]


##   League Hits Salary

## 1      A   66     NA

## 2      A  130    480

## 3      A  169    750

## 4      A   73    100

## 5      A   92   1100

其他更复杂的查询

可以结合使用SQL逻辑操作符(AND,OR,NOT等)，以及行，列选取等建立其他更复杂的查询操作。

dbGetQuery(db.hitters,"select League,Hits,Salary from Hitters where League = 'A' AND  Salary >= 1000")[1:5,]


##   League Hits  Salary

## 1      A   92 1100.00

## 2      A  168 1200.00

## 3      A  177 1350.00

## 4      A  238 1975.00

## 5      A  148 1861.46

4.参考文献

推荐 0
本文由 黄耀鹏 创作，采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。
转载、引用前需联系作者，并署名作者且注明文章出处。
本站文章版权归原作者及原出处所有 。内容为作者个人观点， 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台，并不用于任何商业目的，如果有任何问题，请及时联系我们，我们将根据著作权人的要求，立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

0 个评论

要回复文章请先登录或注册