3  必知必会第3课：排序检索数据

3.1 数据库的连接

library(pacman)
p_load(tidyverse,DBI,RSQLite)

mydb = dbConnect(RSQLite::SQLite(), "data/TYSQL.sqlite")

3.2 基于SQL的数据操作

# 3.1
dbGetQuery(mydb,"SELECT prod_name
FROM Products
ORDER BY prod_name;") 

            prod_name
1  12 inch teddy bear
2  18 inch teddy bear
3   8 inch teddy bear
4   Bird bean bag toy
5   Fish bean bag toy
6           King doll
7          Queen doll
8 Rabbit bean bag toy
9         Raggedy Ann

# 3.2 
dbGetQuery(mydb,"SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price, prod_name;")

  prod_id prod_price           prod_name
1  BNBG02       3.49   Bird bean bag toy
2  BNBG01       3.49   Fish bean bag toy
3  BNBG03       3.49 Rabbit bean bag toy
4  RGAN01       4.99         Raggedy Ann
5    BR01       5.99   8 inch teddy bear
6    BR02       8.99  12 inch teddy bear
7   RYL01       9.49           King doll
8   RYL02       9.49          Queen doll
9    BR03      11.99  18 inch teddy bear

# 3.3
dbGetQuery(mydb,"SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY 2, 3;")

  prod_id prod_price           prod_name
1  BNBG02       3.49   Bird bean bag toy
2  BNBG01       3.49   Fish bean bag toy
3  BNBG03       3.49 Rabbit bean bag toy
4  RGAN01       4.99         Raggedy Ann
5    BR01       5.99   8 inch teddy bear
6    BR02       8.99  12 inch teddy bear
7   RYL01       9.49           King doll
8   RYL02       9.49          Queen doll
9    BR03      11.99  18 inch teddy bear

# 3.4
dbGetQuery(mydb,"SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC;")

  prod_id prod_price           prod_name
1    BR03      11.99  18 inch teddy bear
2   RYL01       9.49           King doll
3   RYL02       9.49          Queen doll
4    BR02       8.99  12 inch teddy bear
5    BR01       5.99   8 inch teddy bear
6  RGAN01       4.99         Raggedy Ann
7  BNBG01       3.49   Fish bean bag toy
8  BNBG02       3.49   Bird bean bag toy
9  BNBG03       3.49 Rabbit bean bag toy

dbGetQuery(mydb,"SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC, prod_name;")

  prod_id prod_price           prod_name
1    BR03      11.99  18 inch teddy bear
2   RYL01       9.49           King doll
3   RYL02       9.49          Queen doll
4    BR02       8.99  12 inch teddy bear
5    BR01       5.99   8 inch teddy bear
6  RGAN01       4.99         Raggedy Ann
7  BNBG02       3.49   Bird bean bag toy
8  BNBG01       3.49   Fish bean bag toy
9  BNBG03       3.49 Rabbit bean bag toy

3.3 基于tidyverse的数据操作

mydb_products = tbl(mydb,"Products")

# 3.1
mydb_products %>%
  select(prod_name) %>% 
  arrange(prod_name)

# Source:     SQL [9 x 1]
# Database:   sqlite 3.46.0 [F:\Data\R4SQL\data\TYSQL.sqlite]
# Ordered by: prod_name
  prod_name          
  <chr>              
1 12 inch teddy bear 
2 18 inch teddy bear 
3 8 inch teddy bear  
4 Bird bean bag toy  
5 Fish bean bag toy  
6 King doll          
7 Queen doll         
8 Rabbit bean bag toy
9 Raggedy Ann        

# 3.2
mydb_products %>% 
  select(prod_id, prod_price, prod_name) %>% 
  arrange(prod_price, prod_name)

# Source:     SQL [9 x 3]
# Database:   sqlite 3.46.0 [F:\Data\R4SQL\data\TYSQL.sqlite]
# Ordered by: prod_price, prod_name
  prod_id prod_price prod_name          
  <chr>        <dbl> <chr>              
1 BNBG02        3.49 Bird bean bag toy  
2 BNBG01        3.49 Fish bean bag toy  
3 BNBG03        3.49 Rabbit bean bag toy
4 RGAN01        4.99 Raggedy Ann        
5 BR01          5.99 8 inch teddy bear  
6 BR02          8.99 12 inch teddy bear 
7 RYL01         9.49 King doll          
8 RYL02         9.49 Queen doll         
9 BR03         12.0  18 inch teddy bear 

# 3.4
mydb_products %>% 
  select(prod_id, prod_price, prod_name) %>% 
  arrange(desc(prod_price)) 

# Source:     SQL [9 x 3]
# Database:   sqlite 3.46.0 [F:\Data\R4SQL\data\TYSQL.sqlite]
# Ordered by: desc(prod_price)
  prod_id prod_price prod_name          
  <chr>        <dbl> <chr>              
1 BR03         12.0  18 inch teddy bear 
2 RYL01         9.49 King doll          
3 RYL02         9.49 Queen doll         
4 BR02          8.99 12 inch teddy bear 
5 BR01          5.99 8 inch teddy bear  
6 RGAN01        4.99 Raggedy Ann        
7 BNBG01        3.49 Fish bean bag toy  
8 BNBG02        3.49 Bird bean bag toy  
9 BNBG03        3.49 Rabbit bean bag toy

mydb_products %>% 
  select(prod_id, prod_price, prod_name) %>% 
  arrange(desc(prod_price),prod_name)

# Source:     SQL [9 x 3]
# Database:   sqlite 3.46.0 [F:\Data\R4SQL\data\TYSQL.sqlite]
# Ordered by: desc(prod_price), prod_name
  prod_id prod_price prod_name          
  <chr>        <dbl> <chr>              
1 BR03         12.0  18 inch teddy bear 
2 RYL01         9.49 King doll          
3 RYL02         9.49 Queen doll         
4 BR02          8.99 12 inch teddy bear 
5 BR01          5.99 8 inch teddy bear  
6 RGAN01        4.99 Raggedy Ann        
7 BNBG02        3.49 Bird bean bag toy  
8 BNBG01        3.49 Fish bean bag toy  
9 BNBG03        3.49 Rabbit bean bag toy

3.4 练习

• 请使用SQL和R两种方式，解决课后的挑战题。