markdownmysql_Rmarkdown连接mysql数据

&＃96;&＃96;&＃96;{r warning &＃61;FALSE, message &＃61; FALSE,echo&＃61;FALSE,output.var&＃61;"info_query"}

library(RMySQL)

db

8306,dbname&＃61;"database",user&＃61;"uesrname",password&＃61;"123456")

dbSendQuery(db,&＃39;SET NAMES gbk&＃39;) # 防止乱码&＃xff0c;如不行改成utf8

&＃96;&＃96;&＃96;

&＃96;&＃96;&＃96;{sql,connection&＃61;db,max.print &＃61; 20,warnig &＃61; 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:

&＃96;&＃96;&＃96;{r}

library(DBI)

db

&＃96;&＃96;&＃96;

&＃96;&＃96;&＃96;{sql, connection&＃61;db}

SELECT * FROM trials

&＃96;&＃96;&＃96;

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 &＃61; 10)). For example, the following code chunk displays the first 20 records:

&＃96;&＃96;&＃96;{sql, connection&＃61;db, max.print &＃61; 20}

SELECT * FROM trials

&＃96;&＃96;&＃96;

You can specify no limit on the records to be displayed via max.print &＃61; -1 or max.print &＃61; 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:

&＃96;&＃96;&＃96;{sql, connection&＃61;db, tab.cap &＃61; "My Caption"}

SELECT * FROM trials

&＃96;&＃96;&＃96;

You can specify that you want no caption all via tab.cap &＃61; 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:

&＃96;&＃96;&＃96;{sql, connection&＃61;db, output.var&＃61;"trials"}

SELECT * FROM trials

&＃96;&＃96;&＃96;

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:

&＃96;&＃96;&＃96;{r}

subjects

&＃96;&＃96;&＃96;

&＃96;&＃96;&＃96;{sql, connection&＃61;db, output.var&＃61;"trials"}

SELECT * FROM trials WHERE subjects >&＃61; ?subjects

&＃96;&＃96;&＃96;

Setting a Default Connection

如果有多个SQL块需要写&＃xff0c;那么&＃xff0c;设置一个通用接口是一个不错的选择&＃xff0c;这样你就不必为每个分割的代码块进行相同设置而感到厌烦了。

&＃96;&＃96;&＃96;{r setup}

library(DBI)

db

knitr::opts_chunk$set(connection &＃61; "db")

&＃96;&＃96;&＃96;

以上设置完成后&＃xff0c;就可以直接输入sq语句来直接查询数据&＃xff0c;感觉省去的大量语句&＃xff0c;非常的清爽。

&＃96;&＃96;&＃96;{sql}

SELECT * FROM trials

&＃96;&＃96; &＃96;