library(pacman)
p_load(tidyverse,DBI,RSQLite)
mydb = dbConnect(RSQLite::SQLite(), "data/TYSQL.sqlite")2 必知必会第2课：检索数据
2.1 数据库的连接
2.2 基于SQL的数据操作
# 2.2
dbGetQuery(mydb,"SELECT prod_name FROM Products;") prod_name
1 8 inch teddy bear
2 12 inch teddy bear
3 18 inch teddy bear
4 Fish bean bag toy
5 Bird bean bag toy
6 Rabbit bean bag toy
7 Raggedy Ann
8 King doll
9 Queen doll# 2.3
dbGetQuery(mydb,"SELECT prod_id, prod_name, prod_price
FROM Products;") prod_id prod_name prod_price
1 BR01 8 inch teddy bear 5.99
2 BR02 12 inch teddy bear 8.99
3 BR03 18 inch teddy bear 11.99
4 BNBG01 Fish bean bag toy 3.49
5 BNBG02 Bird bean bag toy 3.49
6 BNBG03 Rabbit bean bag toy 3.49
7 RGAN01 Raggedy Ann 4.99
8 RYL01 King doll 9.49
9 RYL02 Queen doll 9.49# 2.4
dbGetQuery(mydb,"SELECT *
FROM Products;") prod_id vend_id prod_name prod_price
1 BR01 BRS01 8 inch teddy bear 5.99
2 BR02 BRS01 12 inch teddy bear 8.99
3 BR03 BRS01 18 inch teddy bear 11.99
4 BNBG01 DLL01 Fish bean bag toy 3.49
5 BNBG02 DLL01 Bird bean bag toy 3.49
6 BNBG03 DLL01 Rabbit bean bag toy 3.49
7 RGAN01 DLL01 Raggedy Ann 4.99
8 RYL01 FNG01 King doll 9.49
9 RYL02 FNG01 Queen doll 9.49
prod_desc
1 8 inch teddy bear, comes with cap and jacket
2 12 inch teddy bear, comes with cap and jacket
3 18 inch teddy bear, comes with cap and jacket
4 Fish bean bag toy, complete with bean bag worms with which to feed it
5 Bird bean bag toy, eggs are not included
6 Rabbit bean bag toy, comes with bean bag carrots
7 18 inch Raggedy Ann doll
8 12 inch king doll with royal garments and crown
9 12 inch queen doll with royal garments and crown# 2.5
dbGetQuery(mydb,"SELECT vend_id
FROM Products;") vend_id
1 BRS01
2 BRS01
3 BRS01
4 DLL01
5 DLL01
6 DLL01
7 DLL01
8 FNG01
9 FNG01dbGetQuery(mydb,"SELECT DISTINCT vend_id
FROM Products;") vend_id
1 BRS01
2 DLL01
3 FNG01# 2.6
dbGetQuery(mydb,"SELECT prod_name
FROM Products
LIMIT 5;") prod_name
1 8 inch teddy bear
2 12 inch teddy bear
3 18 inch teddy bear
4 Fish bean bag toy
5 Bird bean bag toydbGetQuery(mydb,"SELECT prod_name
FROM Products
LIMIT 5 OFFSET 5;") prod_name
1 Rabbit bean bag toy
2 Raggedy Ann
3 King doll
4 Queen doll# 2.7
dbGetQuery(mydb,"SELECT prod_name -- 这是一条注释
FROM Products;") prod_name
1 8 inch teddy bear
2 12 inch teddy bear
3 18 inch teddy bear
4 Fish bean bag toy
5 Bird bean bag toy
6 Rabbit bean bag toy
7 Raggedy Ann
8 King doll
9 Queen doll2.3 基于tidyverse的数据操作
mydb_products = tbl(mydb,"Products")
# 2.2
mydb_products %>%
select(prod_name)# Source: SQL [9 x 1]
# Database: sqlite 3.46.0 [F:\Data\R4SQL\data\TYSQL.sqlite]
prod_name
<chr>
1 8 inch teddy bear
2 12 inch teddy bear
3 18 inch teddy bear
4 Fish bean bag toy
5 Bird bean bag toy
6 Rabbit bean bag toy
7 Raggedy Ann
8 King doll
9 Queen doll # 2.3
mydb_products %>%
select(prod_id, prod_name, prod_price)# Source: SQL [9 x 3]
# Database: sqlite 3.46.0 [F:\Data\R4SQL\data\TYSQL.sqlite]
prod_id prod_name prod_price
<chr> <chr> <dbl>
1 BR01 8 inch teddy bear 5.99
2 BR02 12 inch teddy bear 8.99
3 BR03 18 inch teddy bear 12.0
4 BNBG01 Fish bean bag toy 3.49
5 BNBG02 Bird bean bag toy 3.49
6 BNBG03 Rabbit bean bag toy 3.49
7 RGAN01 Raggedy Ann 4.99
8 RYL01 King doll 9.49
9 RYL02 Queen doll 9.49# 2.4
mydb_products# Source: table<`Products`> [9 x 5]
# Database: sqlite 3.46.0 [F:\Data\R4SQL\data\TYSQL.sqlite]
prod_id vend_id prod_name prod_price prod_desc
<chr> <chr> <chr> <dbl> <chr>
1 BR01 BRS01 8 inch teddy bear 5.99 8 inch teddy bear, comes with …
2 BR02 BRS01 12 inch teddy bear 8.99 12 inch teddy bear, comes with…
3 BR03 BRS01 18 inch teddy bear 12.0 18 inch teddy bear, comes with…
4 BNBG01 DLL01 Fish bean bag toy 3.49 Fish bean bag toy, complete wi…
5 BNBG02 DLL01 Bird bean bag toy 3.49 Bird bean bag toy, eggs are no…
6 BNBG03 DLL01 Rabbit bean bag toy 3.49 Rabbit bean bag toy, comes wit…
7 RGAN01 DLL01 Raggedy Ann 4.99 18 inch Raggedy Ann doll
8 RYL01 FNG01 King doll 9.49 12 inch king doll with royal g…
9 RYL02 FNG01 Queen doll 9.49 12 inch queen doll with royal …# 2.5
mydb_products %>%
select(vend_id)# Source: SQL [9 x 1]
# Database: sqlite 3.46.0 [F:\Data\R4SQL\data\TYSQL.sqlite]
vend_id
<chr>
1 BRS01
2 BRS01
3 BRS01
4 DLL01
5 DLL01
6 DLL01
7 DLL01
8 FNG01
9 FNG01 mydb_products %>%
distinct(vend_id)# Source: SQL [3 x 1]
# Database: sqlite 3.46.0 [F:\Data\R4SQL\data\TYSQL.sqlite]
vend_id
<chr>
1 BRS01
2 DLL01
3 FNG01 # 2.6
mydb_products %>%
select(prod_name) %>%
head(5)# Source: SQL [5 x 1]
# Database: sqlite 3.46.0 [F:\Data\R4SQL\data\TYSQL.sqlite]
prod_name
<chr>
1 8 inch teddy bear
2 12 inch teddy bear
3 18 inch teddy bear
4 Fish bean bag toy
5 Bird bean bag toy mydb_products %>%
select(prod_name) %>%
mutate(row_id = row_number()) %>%
filter(row_id > 5) %>%
head(5)# Source: SQL [4 x 2]
# Database: sqlite 3.46.0 [F:\Data\R4SQL\data\TYSQL.sqlite]
prod_name row_id
<chr> <int>
1 Rabbit bean bag toy 6
2 Raggedy Ann 7
3 King doll 8
4 Queen doll 9# 2.7
mydb_products %>%
select(prod_name) # 这是一条注释# Source: SQL [9 x 1]
# Database: sqlite 3.46.0 [F:\Data\R4SQL\data\TYSQL.sqlite]
prod_name
<chr>
1 8 inch teddy bear
2 12 inch teddy bear
3 18 inch teddy bear
4 Fish bean bag toy
5 Bird bean bag toy
6 Rabbit bean bag toy
7 Raggedy Ann
8 King doll
9 Queen doll 2.4 转译机制
可以使用show_query函数来调查tidyverse包是如何把相关代码转译为SQL语句的。
mydb_products %>%
select(prod_name) %>%
show_query()<SQL>
SELECT `prod_name`
FROM `Products`2.5 练习
- 使用转译机制，看看R语言把相关代码都转译成什么SQL语句。
- 请使用SQL和R两种方式，解决课后的挑战题。