DATA-SCIENCE-WITH-R/05-Working-with-Databases-2.Rmd at main · TirendazAcademy/DATA-SCIENCE-WITH-R · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
---
Konu: "RSQLite ile Veri Tabanları"
Anlatan: Tiredaz Akademi "https://youtube.com/c/TirendazAkademi"
---

#Veri setini yükleyelim

```{r}
library(RSQLite)
library(DBI)
data("mtcars")
str(mtcars)
```

```{r}
head(mtcars)
```

```{r}
mtcars$car_names<-rownames(mtcars)
rownames(mtcars)<-c()
head(mtcars)
```

Veri tabanına bağlanıp Arabalar.db veri tabanı oluşturalım.

```{r}
con<-dbConnect(RSQLite::SQLite(), "Arabalar.db")
```

```{r}
dbWriteTable(con,"cars_table", mtcars)
```

```{r}
dbListTables(con)
```
```{r}
car<-c("Camaro","California","Mustang","Explorer")
make<-c("Chevrolet","Ferrari","Ford","Ford")
df1<-data.frame(car,make)
```

```{r}
car<-c("Corolla","Lancer","Sportage","XE")
make<-c("Toyota","Misubishi","Kia","Jaguar")
df2<-data.frame(car,make)
```

```{r}
dfList<-list(df1,df2)

for (k in 1:length(dfList)){
  dbWriteTable(con, "Cars_Makes", dfList[[k]], append=TRUE)
}
dbListTables(con)
```
#SQL komutları çalışma

```{r}
dbGetQuery(con,"SELECT * FROM Cars_Makes")
```

```{r}
dbGetQuery(con, "SELECT * FROM cars_table LIMIT 10")
```

```{r}
dbGetQuery(con, "SELECT car_names, hp, cyl FROM cars_table WHERE cyl=8")
```


```{r}
dbGetQuery(con, "SELECT car_names, gear, cyl
           FROM cars_table
           WHERE car_names LIKE 'H%'
           AND cyl IN (4,6)")
```
```{r}
s<-dbGetQuery(con, "SELECT cyl, AVG(hp) AS 'average_hp', AVG(mpg) AS 'average_mpg' FROM cars_table
                  GROUP BY cyl
                  ORDER BY average_hp")
s
```
```{r}
class(s)
```
#SQL komutlarında parametre kullanma

```{r}
mpg<-18
cyl<-6
Sonuc<-dbGetQuery(con, "SELECT car_names, mpg, cyl FROM cars_table
                  WHERE mpg>=? AND cyl>=?", params=c(mpg,cyl))
```


```{r}
Sonuc
```

```{r}
dbGetQuery(con, "SELECT * FROM cars_table LIMIT 10")
```

21.0	6	160.0	110	3.90	2.620	16.46	0	1	4 160.0	110	3.90	2.620	16.46	0	1	4	4	Mazda RX4

```{r}
dbExecute(con ,"DELETE FROM cars_table WHERE car_names='Mazda RX4'")
```
```{r}
dbGetQuery(con, "SELECT * FROM cars_table LIMIT 10")
```
```{r}
dbExecute(con, "INSERT INTO cars_table VALUES (21.0,6,160.0,110,3.90,2.620,16.46,0,1,4,4,'Mazda RX4')")
```
```{r}
dbGetQuery(con, "SELECT * FROM cars_table")
```

#Veri tabanı ile bağlantıyı kesme

```{r}
dbDisconnect(con)
```