R Language
- Getting started with R Language
- Awesome Book
- Awesome Community
- Awesome Course
- Awesome Tutorial
- Awesome YouTube
- *apply family of functions (functionals)
- .Rprofile
- Aggregating data frames
- Analyze tweets with R
- ANOVA
- Arima Models
- Arithmetic Operators
- Bar Chart
- Base Plotting
- Bibliography in RMD
- boxplot
- caret
- Classes
- Cleaning data
- Code profiling
- Coercion
- Color schemes for graphics
- Column wise operation
- Combinatorics
- Control flow structures
- Creating packages with devtools
- Creating reports with RMarkdown
- Creating vectors
- Data acquisition
- Data frames
- data.table
- Date and Time
- Date-time classes (POSIXct and POSIXlt)
- Debugging
- Distribution Functions
- dplyr
- Expression: parse + eval
- Extracting and Listing Files in Compressed Archives
- Factors
- Fault-tolerant/resilient code
- Feature Selection in R -- Removing Extraneous Features
- Formula
- Fourier Series and Transformations
- Functional programming
- Generalized linear models
- Get user input
- ggplot2
- GPU-accelerated computing
- Hashmaps
- heatmap and heatmap.2
- Hierarchical clustering with hclust
- Hierarchical Linear Modeling
- I/O for database tables
- I/O for foreign tables (Excel, SAS, SPSS, Stata)
- I/O for geographic data (shapefiles, etc.)
- I/O for raster images
- I/O for R's binary format
- Implement State Machine Pattern using S4 Class
- Input and output
- Inspecting packages
- Installing packages
- Introduction to Geographical Maps
- Introspection
- JSON
- Linear Models (Regression)
- Lists
- lubridate
- Machine learning
- Matrices
- Meta: Documentation Guidelines
- Missing values
- Modifying strings by substitution
- Natural language processing
- Network analysis with the igraph package
- Non-standard evaluation and standard evaluation
- Numeric classes and storage modes
- Object-Oriented Programming in R
- Parallel processing
- Pattern Matching and Replacement
- Performing a Permutation Test
- Pipe operators (%>% and others)
- Pivot and unpivot with data.table
- Probability Distributions with R
- Publishing
- R code vectorization best practices
- R in LaTeX with knitr
- R Markdown Notebooks (from RStudio)
- R memento by examples
- Random Forest Algorithm
- Random Numbers Generator
- Randomization
- Raster and Image Analysis
- Rcpp
- Reading and writing strings
- Reading and writing tabular data in plain-text files (CSV, TSV, etc.)
- Recycling
- Regular Expression Syntax in R
- Regular Expressions (regex)
- Reproducible R
- Reshape using tidyr
- Reshaping data between long and wide forms
- RESTful R Services
- RMarkdown and knitr presentation
- RODBC
- roxygen2
- Run-length encoding
- Scope of variables
- Set operations
- Shiny
- Solving ODEs in R
- Spark API (SparkR)
- spatial analysis
- Speeding up tough-to-vectorize code
- Split function
- sqldf
- Standardize analyses by writing standalone R scripts
- String manipulation with stringi package
- strsplit function
- Subsetting
- Survival analysis
- Text mining
- The character class
- The Date class
- The logical class
- tidyverse
- Time Series and Forecasting
- Updating R and the package library
- Updating R version
- Using pipe assignment in your own package %<>%: How to ?
- Using texreg to export models in a paper-ready way
- Variables
- Web Crawling in R
- Web scraping and parsing
- Writing functions in R
- xgboost
R Language I/O for database tables Reading Data from MySQL Databases
Example
General
Using the package RMySQL we can easily query MySQL as well as MariaDB databases and store the result in an R dataframe:
library(RMySQL)
mydb <- dbConnect(MySQL(), user='user', password='password', dbname='dbname',host='127.0.0.1')
queryString <- "SELECT * FROM table1 t1 JOIN table2 t2 on t1.id=t2.id"
query <- dbSendQuery(mydb, queryString)
data <- fetch(query, n=-1) # n=-1 to return all results
Using limits
It is also possible to define a limit, e.g. getting only the first 100,000 rows. In order to do so, just change the SQL query regarding the desired limit. The mentioned package will consider these options. Example:
queryString <- "SELECT * FROM table1 limit 100000"
