14  必知必会第14课:组合查询

作者

黄天元

14.1 数据库的连接

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

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

14.2 基于SQL和tidyverse的数据操作

# 14.2.1
dbGetQuery(mydb,"SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI');") 
      cust_name cust_contact            cust_email
1  Village Toys   John Smith sales@villagetoys.com
2       Fun4All    Jim Jones    jjones@fun4all.com
3 The Toy Store   Kim Howard   kim@thetoystore.com
dbGetQuery(mydb,"SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';") 
  cust_name       cust_contact            cust_email
1   Fun4All          Jim Jones    jjones@fun4all.com
2   Fun4All Denise L. Stephens dstephens@fun4all.com
dbGetQuery(mydb,"SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';") 
      cust_name       cust_contact            cust_email
1       Fun4All Denise L. Stephens dstephens@fun4all.com
2       Fun4All          Jim Jones    jjones@fun4all.com
3 The Toy Store         Kim Howard   kim@thetoystore.com
4  Village Toys         John Smith sales@villagetoys.com
tbl(mydb,"Customers") %>% 
  filter(cust_state %in% c('IL','IN','MI')) %>% 
  select(cust_name, cust_contact, cust_email) %>% 
  union(
    tbl(mydb,"Customers") %>% 
      filter(cust_name == 'Fun4All') %>% 
      select(cust_name, cust_contact, cust_email)
  )
# Source:   SQL [4 x 3]
# Database: sqlite 3.46.0 [D:\数据库技术及应用\R4SQL\data\TYSQL.sqlite]
  cust_name     cust_contact       cust_email           
  <chr>         <chr>              <chr>                
1 Fun4All       Denise L. Stephens dstephens@fun4all.com
2 Fun4All       Jim Jones          jjones@fun4all.com   
3 The Toy Store Kim Howard         kim@thetoystore.com  
4 Village Toys  John Smith         sales@villagetoys.com
dbGetQuery(mydb,"SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI') OR cust_name = 'Fun4All';")
      cust_name       cust_contact            cust_email
1  Village Toys         John Smith sales@villagetoys.com
2       Fun4All          Jim Jones    jjones@fun4all.com
3       Fun4All Denise L. Stephens dstephens@fun4all.com
4 The Toy Store         Kim Howard   kim@thetoystore.com
tbl(mydb,"Customers") %>% 
  filter(cust_state %in% c('IL','IN','MI') | cust_name == 'Fun4All') %>% 
  select(cust_name, cust_contact, cust_email)
# Source:   SQL [4 x 3]
# Database: sqlite 3.46.0 [D:\数据库技术及应用\R4SQL\data\TYSQL.sqlite]
  cust_name     cust_contact       cust_email           
  <chr>         <chr>              <chr>                
1 Village Toys  John Smith         sales@villagetoys.com
2 Fun4All       Jim Jones          jjones@fun4all.com   
3 Fun4All       Denise L. Stephens dstephens@fun4all.com
4 The Toy Store Kim Howard         kim@thetoystore.com  
# 14.2.3
dbGetQuery(mydb,"SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION ALL
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';")
      cust_name       cust_contact            cust_email
1  Village Toys         John Smith sales@villagetoys.com
2       Fun4All          Jim Jones    jjones@fun4all.com
3 The Toy Store         Kim Howard   kim@thetoystore.com
4       Fun4All          Jim Jones    jjones@fun4all.com
5       Fun4All Denise L. Stephens dstephens@fun4all.com
tbl(mydb,"Customers") %>% 
  filter(cust_state %in% c('IL','IN','MI')) %>% 
  select(cust_name, cust_contact, cust_email) %>% 
  union_all(
    tbl(mydb,"Customers") %>% 
      filter(cust_name == 'Fun4All') %>% 
      select(cust_name, cust_contact, cust_email)
  )
# Source:   SQL [5 x 3]
# Database: sqlite 3.46.0 [D:\数据库技术及应用\R4SQL\data\TYSQL.sqlite]
  cust_name     cust_contact       cust_email           
  <chr>         <chr>              <chr>                
1 Village Toys  John Smith         sales@villagetoys.com
2 Fun4All       Jim Jones          jjones@fun4all.com   
3 The Toy Store Kim Howard         kim@thetoystore.com  
4 Fun4All       Jim Jones          jjones@fun4all.com   
5 Fun4All       Denise L. Stephens dstephens@fun4all.com
# 14.2.4
dbGetQuery(mydb,"SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All'
ORDER BY cust_name, cust_contact;")
      cust_name       cust_contact            cust_email
1       Fun4All Denise L. Stephens dstephens@fun4all.com
2       Fun4All          Jim Jones    jjones@fun4all.com
3 The Toy Store         Kim Howard   kim@thetoystore.com
4  Village Toys         John Smith sales@villagetoys.com
tbl(mydb,"Customers") %>% 
  filter(cust_state %in% c('IL','IN','MI')) %>% 
  select(cust_name, cust_contact, cust_email) %>% 
  union(
    tbl(mydb,"Customers") %>% 
      filter(cust_name == 'Fun4All') %>% 
      select(cust_name, cust_contact, cust_email)
  ) %>% 
  arrange(cust_name, cust_contact)
# Source:     SQL [4 x 3]
# Database:   sqlite 3.46.0 [D:\数据库技术及应用\R4SQL\data\TYSQL.sqlite]
# Ordered by: cust_name, cust_contact
  cust_name     cust_contact       cust_email           
  <chr>         <chr>              <chr>                
1 Fun4All       Denise L. Stephens dstephens@fun4all.com
2 Fun4All       Jim Jones          jjones@fun4all.com   
3 The Toy Store Kim Howard         kim@thetoystore.com  
4 Village Toys  John Smith         sales@villagetoys.com

14.3 练习

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