chap2.qmd

---
title: "必知必会第2课：检索数据"
author: "黄天元"
format: html
editor: visual
---

## 数据库的连接

```{r}
library(pacman)
p_load(tidyverse,DBI,RSQLite)

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

## 基于SQL的数据操作

```{r}
# 2.2
dbGetQuery(mydb,"SELECT prod_name FROM Products;") 

# 2.3
dbGetQuery(mydb,"SELECT prod_id, prod_name, prod_price
FROM Products;")

# 2.4
dbGetQuery(mydb,"SELECT *
FROM Products;")

# 2.5
dbGetQuery(mydb,"SELECT vend_id
FROM Products;")
dbGetQuery(mydb,"SELECT DISTINCT vend_id
FROM Products;")

# 2.6
dbGetQuery(mydb,"SELECT prod_name
FROM Products
LIMIT 5;")
dbGetQuery(mydb,"SELECT prod_name
FROM Products
LIMIT 5 OFFSET 5;")

# 2.7
dbGetQuery(mydb,"SELECT prod_name -- 这是一条注释
FROM Products;")
```

## 基于tidyverse的数据操作

```{r}
mydb_products = tbl(mydb,"Products")

# 2.2
mydb_products %>%
  select(prod_name)

# 2.3
mydb_products %>% 
  select(prod_id, prod_name, prod_price)

# 2.4
mydb_products

# 2.5
mydb_products %>% 
  select(vend_id)
mydb_products %>% 
  distinct(vend_id)

# 2.6
mydb_products %>% 
  select(prod_name) %>% 
  head(5)

mydb_products %>% 
  select(prod_name) %>% 
  mutate(row_id = row_number()) %>% 
  filter(row_id > 5) %>% 
  head(5)

# 2.7
mydb_products %>%
  select(prod_name) # 这是一条注释
```

## 转译机制

可以使用`show_query`函数来调查tidyverse包是如何把相关代码转译为SQL语句的。

```{r}
mydb_products %>% 
  select(prod_name) %>% 
  show_query()
```

## 练习

1.  使用转译机制，看看R语言把相关代码都转译成什么SQL语句。
2.  请使用SQL和R两种方式，解决课后的挑战题。

```{r}
#| echo: false
#| eval: false 

## SQL

# 参考：https://forta.com/books/0135182794/challenges/

## R

# 1
tbl(mydb,"Customers") %>% 
  select(cust_id)

# 2
tbl(mydb,"OrderItems") %>% 
  distinct(prod_id)

# 3
tbl(mydb,"Customers") %>% 
  select(cust_id) # 在最左侧放入井号可以注释掉，也可以只运行上面一行
```