library(pacman)
p_load(tidyverse,DBI,RSQLite,fs,dbplyr)
file_copy(path = "data/TYSQL.sqlite",
new_path = "data/TYSQL_copy.sqlite",overwrite = T)
c1= dbConnect(RSQLite::SQLite(), "data/TYSQL_copy.sqlite")18 必知必会第18课：使用视图
18.1 数据库的连接
本次实验创建会对数据库进行拷贝，然后连接拷贝的数据库。
18.2 数据操作代码
# 18.2.1
dbExecute(c1,"CREATE VIEW ProductCustomers AS
SELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num;") [1] 0dbGetQuery(c1,"SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';") cust_name cust_contact
1 Fun4All Denise L. Stephens
2 The Toy Store Kim Howard# 18.2.2
dbExecute(c1,"CREATE VIEW VendorLocations AS
SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')'
AS vend_title
FROM Vendors;") [1] 0dbGetQuery(c1,"SELECT * FROM VendorLocations;") vend_title
1 Bears R Us (USA)
2 Bear Emporium (USA)
3 Doll House Inc. (USA)
4 Furball Inc. (USA)
5 Fun and Games (England)
6 Jouets et ours (France)# 18.2.3
dbExecute(c1,"CREATE VIEW CustomerEMailList AS
SELECT cust_id, cust_name, cust_email
FROM Customers
WHERE cust_email IS NOT NULL;") [1] 0dbGetQuery(c1,"SELECT *
FROM CustomerEMailList;") cust_id cust_name cust_email
1 1000000001 Village Toys sales@villagetoys.com
2 1000000003 Fun4All jjones@fun4all.com
3 1000000004 Fun4All dstephens@fun4all.com# 18.2.4
dbExecute(c1,"CREATE VIEW OrderItemsExpanded AS
SELECT order_num,
prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM OrderItems") [1] 0dbGetQuery(c1,"SELECT *
FROM OrderItemsExpanded
WHERE order_num = 20008;") order_num prod_id quantity item_price expanded_price
1 20008 RGAN01 5 4.99 24.95
2 20008 BR03 5 11.99 59.95
3 20008 BNBG01 10 3.49 34.90
4 20008 BNBG02 10 3.49 34.90
5 20008 BNBG03 10 3.49 34.9018.3 练习
- 请使用SQL和R两种方式，解决课后的挑战题。
18.4 关闭数据库
这一步，我们会关闭数据库连接。
# 关闭数据库
dbDisconnect(c1)