sqldf: SQL select on R data frames

sqldf is an R package for runing SQL statements on R data frames, optimized for convenience. The user simply specifies an SQL statement in R using data frame names in place of table names and a database with appropriate table layouts/schema is automatically created, the data frames are automatically loaded into the database, the specified SQL statement is performed, the result is read back into R and the database is deleted all automatically behind the scenes making the database's existence transparent to the user who only specifies the SQL statement. Surprisingly this can at times be faster than the corresponding pure R calculation (although the purpose of the project is convenience and not speed). sqldf is free software published under the GNU General Public License that can be downloaded from CRAN.

JDBC Tutorial

The JDBC API is a Java API that can access any kind of tabular data, especially data stored in a Relational Database.

JDBC stands for Java Database Connectivity.

JDBC works with Java on a variety of platforms, such as Windows, Mac OS, and the various versions of UNIX.

This tutorial gives a complete understanding on JDBC.

Export CSV directly from MySQL

How ofter were you asked by a client for a CSV (or excel) file with data from their app? I get asked that question quite often, so I wanted make the process as easy as possible. And guess what? You can create CSV files directly from MySQL with just one query!

Let’s say you want to export the id, name and email fields from your users table to a CSV file. Here is your code:

SELECT id, name, email INTO OUTFILE '/tmp/result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ‘\\’
LINES TERMINATED BY '\n'
FROM users WHERE 1

Well, if you know MySQL, you’ll know how to customize this query to spit out the the right data. Your csv file can be found in /tmp/result.csv
Make sure your MySQL server has write permissions to the location where you want to store your results file.

Select previous and next row from a Mysql table

Previous ID:

SELECT id FROM $mytable WHERE id < $id ORDER BY ID DESC LIMIT 1;

Next ID:

SELECT id FROM $mytable WHERE id > $id ORDER BY ID ASC LIMIT 1;

(thanks to Bob Field)

Calcolo del Profit Loss daily

In arrivo

Load CSV file into Mysql table

LOAD DATA INFILE can be used to read files obtained from external sources. For example, many programs can export data in comma-separated values (CSV) format, such that lines have fields separated by commas and enclosed within double quotation marks, with an initial line of column names. If the lines in such a file are terminated by carriage return/newline pairs, the statement shown here illustrates the field- and line-handling options you would use to load the file:

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES;

MySQL Visual Explain – Hierarchical View of Query Execution Plan

EXPLAIN statement is used to obtain information about how MySQL executes a SELECT statement to check whether indexes are being used properly, and what kind of join is being performed. EXPLAIN output can sometimes be very hard to understand when it comes to long and complex queries.
Here comes MySQL Visual Explain, a command-line tool that transforms EXPLAIN output into a hierarchical view of the query execution plan. MySQL Visual Explain collects the output of EXPLAIN in MySQL and visualize it in treeview to make the reading more understandable. It is very useful when you have large and complex queries.
How It Works:
MySQL Visual Explain tries to reverse-engineer MySQL’s EXPLAIN output into a query execution plan, which it then formats as a left-deep tree — the same way the plan is represented inside MySQL.
Example query:
SELECT actor_id, (SELECT COUNT(film_id) FROM sakila.film JOIN sakila.film_actor USING(film_id)) FROM sakila.actor;