R markdown 連接mysql數據

```{r warning =FALSE, message = FALSE,echo=FALSE,output.var="info_query"}

library(RMySQL)

db <-dbConnect(RMySQL::MySQL(),host="hos",port =8306,dbname="database",user="uesrname",password="123456")

dbSendQuery(db,SET NAMES gbk) # 防止亂碼，如不行改成utf8

```

```{sql,connection=db,max.print = 20,warnig = FALSE}

select user_name,

sum(shows),

sum(charge)

from table

where stdate between 20170630 and 20170630

group by user_name limit 20

···

SQL

SQLThe SQL engine uses the DBI package to execute SQL queries, print their results, and optionally assign the results to a data frame. The SQL engine is available only in the most recent version of knitr (v1.14) which you can install as follows:

install.packages("knitr")##

To use the knitr SQL engine you first need to establish a DBI connection to a database (typically via the dbConnect function). You can make use of this connection in a SQL chunk via the connection option. For example:

```{r}library(DBI)db <- dbConnect(RSQLite::SQLite(), dbname = "sql.sqlite")``````{sql, connection=db}SELECT * FROM trials```

By default SELECT queries will display the first 10 records of their results within the document.

Number of Records Displayed

The number of records displayed is controlled by the max.print option, which is turn derived from the global knitr option sql.max.print (i.e. opts_knit$set(sql.max.print = 10)). For example, the following code chunk displays the first 20 records:

```{sql, connection=db, max.print = 20}SELECT * FROM trials```

You can specify no limit on the records to be displayed via max.print = -1 or max.print = NA.

Table Captions

By default the knitr SQL engine includes a caption that indicates the total number of records displayed. You can override this caption using the tab.cap chunk option. For example:

```{sql, connection=db, tab.cap = "My Caption"}SELECT * FROM trials```

You can specify that you want no caption all via tab.cap = NA.

Assigning Results to a Data Frame

If you want to assign the results of the SQL query to an R data frame, you can do this using the output.var option, for example:

```{sql, connection=db, output.var="trials"}SELECT * FROM trials```

When the results of a SQL query are assigned to a data frame no records are printed within the document (if desired, you can manually print the data frame in a subsequent R chunk).

Using R Variables in Queries

If you need to bind the values of R variables into SQL queries, you can do so by prefacing R variable references with a ?. For example:

```{r}subjects <- 10```

```{sql, connection=db, output.var="trials"}SELECT * FROM trials WHERE subjects >= ?subjects```

Setting a Default Connection

If you have many SQL chunks, it may be helpful to set a default for the connection chunk option in the setup chunk, so that it is not necessary to specify the connection on each individual chunk. You can do this as follows:

```{r setup}library(DBI)db <- dbConnect(RSQLite::SQLite(), dbname = "sql.sqlite")knitr::opts_chunk$set(connection = "db")```

Note that the connection parameter should contain a string naming the connection object (not the object itself). Once set, you can execute SQL chunks without naming an explicit connection:

```{sql}SELECT * FROM trials``