Importing data into MySQL

Rather than using a Perl script, for example, data can be imported directly into MySQL using LOAD DATA LOCAL INFILE. Note that if you don't include LOCAL, the file you want to import must be located on the server, rather than a client.

For example, suppose you have an ASCII file containing comma-delimited data that you want to insert into the table my_table. Type:

LOAD DATA LOCAL INFILE 'data.txt'
INTO TABLE my_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(field1, field2, field3, field4, field5);

Here we have specified that the data is comma-delimited, and each line of the input file corresponds to a row in the table. The columns that each piece of data should be inserted into are also specified.

MySQL can read only the lines in a data file containing a specified prefix if necessary. For example, if each line with real data begins with DATA_ENTRY, and you want to ignore all others, add:

LINES STARTING BY 'DATA_ENTRY'

MySQL will skip the prefix, and anything that comes before it. Lines not including the prefix will be completely skipped.

Simple example: using the MySQL C API

Here we present a very simple example illustrating how to access a MySQL database using the C API. Here we connect to a MySQL database called test_db on a host with IP address 192.168.0.1, username "user", and password "pass". A simple query is executed, and the results printed.

#include <mysql.h>
#include <stdio.h>
#include <iostream>

MYSQL *conn;

using namespace std;

int main(int argc, char *argv[])
{

// initialize a MYSQL object

   conn = mysql_init(NULL);

   if (conn == NULL)
   {
      cerr << "ERROR: mysql_init failed" << endl;
      exit(1);
   }

// connect to the MySQL database

   if (mysql_real_connect(conn, "192.168.0.1", "user", "pass",
                          "test_db", 0, NULL, 0) == NULL)
   {
      cerr << "ERROR: mysql_real_connect() failed" << endl;
      exit(1);
   }

// define a query

   char *query = "SELECT * from my_table;";

   if (mysql_query (conn, query) != 0)
   {
      cerr << "ERROR: mysql_query failed" << endl;
      exit(1);
   }
   else
   {

// initiate a result set retrieval

      MYSQL_RES *res_set;
      res_set = mysql_use_result(conn);
      if (res_set == NULL)
      {
         cerr << "ERROR: mysql_use_result failed" << endl;
         exit(1);
      }
      else
      {
         MYSQL_ROW row;

// retrieve a row of data from the result set

         while ((row = mysql_fetch_row(res_set)) != NULL)
         {

// print the results for the current row

            for (int i=0; i<mysql_num_fields(res_set); i++)
            {
               cout << row[i] << " ";  
            } 
            cout << endl;
         }
      }
   }

// de-allocate memory

   mysql_free_result(res_set);

// close the MySQL connection

   mysql_close(conn);
}

Here we have used mysql_use_result. This initiates a result set retrieval, but does not actually retrieve the data. This must be done for each row using mysql_fetch_row. An alternative is to use mysql_store_result instead of mysql_use_result, which reads the entire set of results into memory.

Using matplotlib to plot data from a MySQL table

matplotlib is a Python 2D plotting library and can be easily used to plot data directly from a MySQL database. You will need to install the MySQL support for Python if you don't already have it.

The following code plotmysql.py makes a simple 2D plot using data from a MySQL database:

import sys
from pylab import *
import MySQLdb

db = MySQLdb.connect(sys.argv[1], sys.argv[2], sys.argv[3],
                    sys.argv[4])
cursor = db.cursor()

query = sys.argv[5]
cursor.execute(query)
result = cursor.fetchall()

t = []
s = []

for record in result:
  t.append(record[0])
  s.append(record[1])

plot(t, s, 'ko')
axis([min(t), max(t), min(s), max(s)])
title(query)
grid(True)

F = gcf()
DPI = F.get_dpi()
F.savefig('plot.png',dpi = (80))

The resulting file is a png image called plot.png. To run the code, type:

python plotmysql.py [host] [user] [password] [database] [query]

The five required arguments here are the hostname of the MySQL database, username, passowrd, database and the query. For example:

python plotmysql.py localhost testuser testpass testdb "select x, y from data"

will plot column y versus column x from the table called data in the database called testdb. This query is obviously very simple, however a plot can of course be made from any query no matter how complex, provided there are 2 values per row in the set of results.

Quick introduction to MySQL views

Suppose we have table called names with two columns, first and last. A simple SELECT gives this, for example:

+-------+---------+
| first | last    |
+-------+---------+
| John  | Citizen |
| Jane  | Smith   |
+-------+---------+

Using CONCAT we can combine the first and last names together:

SELECT CONCAT(first,' ',last) FROM names;

which gives, for example:

+------------------------+
| CONCAT(first,' ',last) |
+------------------------+
| John Citizen           |
| Jane Smith             |
+------------------------+

If we frequently need to do such queries, we can create a view:

CREATE VIEW full_names AS SELECT CONCAT(first,' ',last) FROM names;

This creates a virtual table called full_names (it's called a virtual table because it's defined in terms of another table through a query). The view can which can be used in the following way:

SELECT * FROM full_names;

which gives:

+------------------------+
| CONCAT(first,' ',last) |
+------------------------+
| John Citizen           |
| Jane Smith             |
+------------------------+

We therefore no longer need to perform the SELECT statement using CONCAT.

To obtain information about a view:

SHOW CREATE VIEW view_name;

A view can be deleted in the following way:

DROP VIEW view_name;

We'll now consider a slightly more complicated example. Consider two tables, books:

+--------------------------------+--------------+------+
| title                          | author       | id   |
+--------------------------------+--------------+------+
| Elementary Mechanics           | Bob Smith    |    1 |
| Popular Science                | Reg Hammer   |    2 |
| Beginner's Guide to Mechanics  | R. G. Wesley |    3 |
| Advanced Statistical Mechanics | O. R. Ange   |    4 |
+--------------------------------+--------------+------+

and publishers:

+---------------+------+
| publisher     | id   |
+---------------+------+
| Smith & Smith |    1 |
| Davidson's    |    2 |
| Davidson's    |    3 |
| Smith & Smith |    4 |
+---------------+------+

We can use a join to obtain a list of book titles, authors and publishers:

SELECT title, author, publisher FROM books, publishers WHERE books.id = publishers.id;

which gives:

+--------------------------------+--------------+---------------+
| title                          | author       | publisher     |
+--------------------------------+--------------+---------------+
| Elementary Mechanics           | Bob Smith    | Smith & Smith |
| Popular Science                | Reg Hammer   | Davidson's    |
| Beginner's Guide to Mechanics  | R. G. Wesley | Davidson's    |
| Advanced Statistical Mechanics | O. R. Ange   | Smith & Smith |
+--------------------------------+--------------+---------------+

Instead of performing this join whenever we want to obtain this data, we could create a view:

CREATE VIEW bookshop AS SELECT title, author, publisher FROM books, publishers WHERE books.id = publishers.id;

Now we can just do this:

SELECT * FROM bookshop;

which gives the same set of results:

+--------------------------------+--------------+---------------+
| title                          | author       | publisher     |
+--------------------------------+--------------+---------------+
| Elementary Mechanics           | Bob Smith    | Smith & Smith |
| Popular Science                | Reg Hammer   | Davidson's    |
| Beginner's Guide to Mechanics  | R. G. Wesley | Davidson's    |
| Advanced Statistical Mechanics | O. R. Ange   | Smith & Smith |
+--------------------------------+--------------+---------------+

As expected, we can use a WHERE clause in the normal way:

SELECT * from bookshop WHERE publisher = "Davidson's";

which would give:

+-------------------------------+--------------+------------+
| title                         | author       | publisher  |
+-------------------------------+--------------+------------+
| Popular Science               | Reg Hammer   | Davidson's |
| Beginner's Guide to Mechanics | R. G. Wesley | Davidson's |
+-------------------------------+--------------+------------+

It's important to note that if the data in the original tables changes, the data in the view changes as well. Views can of course be much more complicated, and include calculations and aggregate functions.

It should be clear how useful views are. One of which is that users can perform simple SELECT queries on a view, rather than having to perform joins on multiple tables. A user can even be given permission only to have access to a view and not the underlying tables.

A few notes on full-text searching in MySQL

Firstly we'll create a table to experiment with.

CREATE TABLE books(title VARCHAR(50), author VARCHAR(50), FULLTEXT(title, author));
INSERT INTO books (title,author) VALUES("Elementary Mechanics","Bob Smith");
INSERT INTO books (title,author) VALUES("Popular Science","Reg Hammer");
INSERT INTO books (title,author) VALUES("Beginner's Guide to Mechanics","R. G. Wesley");
INSERT INTO books (title,author) VALUES("Advanced Statistical Mechanics","O. R. Ange");

Our table therefore contains the following:

+--------------------------------+--------------+
| title                          | author       |
+--------------------------------+--------------+
| Elementary Mechanics           | Bob Smith    |
| Popular Science                | Reg Hammer   |
| Beginner's Guide to Mechanics  | R. G. Wesley |
| Advanced Statistical Mechanics | O. R. Ange   |
+--------------------------------+--------------+

Suppose we want to find all books with the word "mechanics" in the title. This can be done easily using:

SELECT * FROM books WHERE MATCH(title,author) AGAINST ('mechanics' IN BOOLEAN MODE);

This gives:

+--------------------------------+--------------+
| title                          | author       |
+--------------------------------+--------------+
| Elementary Mechanics           | Bob Smith    |
| Beginner's Guide to Mechanics  | R. G. Wesley |
| Advanced Statistical Mechanics | O. R. Ange   |
+--------------------------------+--------------+

If we want to find all books containing the word "mechanics" in the title but not the word "elementary":

SELECT * FROM books WHERE MATCH(title,author) AGAINST ('mechanics -elementary' IN BOOLEAN MODE);

This gives:

+--------------------------------+--------------+
| title                          | author       |
+--------------------------------+--------------+
| Beginner's Guide to Mechanics  | R. G. Wesley |
| Advanced Statistical Mechanics | O. R. Ange   |
+--------------------------------+--------------+

A very brief introduction to MySQL

We will assume that MySQL has already been installed and setup on your system. If not, please visit www.mysql.com.

Adding users

To setup a user called basil with full privileges to do anything to any database or table, with access only from the local host:

GRANT ALL PRIVILEGES ON *.* to 'basil'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

We now create a user fred with SELECT only privileges only on a specific table, with access from any host:

GRANT SELECT ON my_database.my_table to 'fred'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

To see a list of all users and their privileges:

SELECT * FROM mysql.user;

Creating databases and tables

To create a database called site:

CREATE DATABASE site;

To use this database:

USE site;

To see a list of all databases:

SHOW DATABASES;

To see a list of tables for the current database:

SHOW TABLES;

Example creation of a table:

CREATE TABLE users (name VARCHAR(50), email VARCHAR(50), user VARCHAR(30), id INT(3) NOT NULL AUTO_INCREMENT, PRIMARY KEY (id));

The table users has 4 columns: name, email, user and id. id contains automatically incremented integers. Every row added to this table will be assigned a unique id.

To see how a table is setup:

DESCRIBE users;

will give for the above example:

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| name  | varchar(50) | YES  |     | NULL    |                |
| email | varchar(50) | YES  |     | NULL    |                |
| user  | varchar(30) | YES  |     | NULL    |                |
| id    | int(3)      | NO   | PRI | NULL    | auto_increment |
+-------+-------------+------+-----+---------+----------------+

For storing numerical data, there are variety of choices. For integers, you can use TINYINT, SMALLINT, MEDIUMINT, INT or BIGINT, depending on the magnitude of the data. For positive only integers, you can specify UNSIGNED, for example TINYINT UNSIGNED. It's a good idea to try to choose the most appropriate data type in order to minimize the storage required. For example, if you require only numbers between 0 and 200, use TINYINT UNSIGNED rather than INT or BIGINT.

For floating point numbers, FLOAT or DOUBLE can be used.

If you want to specify the precision of the data to be stored, the DECIMAL type could be used. For example,

price DECIMAL(5,2)

Here the column price can contain numbers with 5 digits and 2 decimal places, i.e. numbers in the range -99.99 to 999.99.

If you want to store a date and time in a table in the YYYY-MM-DD HH:MM:SS format, there are two data type choices: TIMESTAMP or DATETIME. Be aware that TIMESTAMP has a side-effect, in that if you update a row, the TIMESTAMP column will automatically be updated. If you don't want this to happen, use DATETIME instead.

Adding data to a table

For the example table given above, we can easily insert a row into the table using INSERT. For example:

INSERT INTO users (name,email,user) VALUES ("John Smith", "john.smith@smith.com", "john.smith");

will add one row into the table, modifying the columns name, email and user. The table now contains the following:

+------------+----------------------+------------+----+
| name       | email                | user       | id |
+------------+----------------------+------------+----+
| John Smith | john.smith@smith.com | john.smith |  1 |
+------------+----------------------+------------+----+

Simple queries

Suppose you have a table containing a list of item names and prices, for example a table created using:

CREATE TABLE shop (item VARCHAR(50), price DECIMAL(5,2));

i.e. the table looks like this:

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| item  | varchar(50)  | YES  |     | NULL    |       |
| price | decimal(5,2) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

To see all rows and columns in the table shop:

SELECT * FROM shop;

To only see a list of item names, not prices as well:

SELECT item FROM shop;

Finding the price of a specific item is very simple. We need to use a WHERE clause, which specifies what conditions need to be met for a row to be included in the set of results:

SELECT price FROM shop WHERE item = "Little toy train";

What above listing all products with a price greater than 10.00?

SELECT * FROM shop WHERE price > 10.00;

To show only the first 10 results, for example:

SELECT * FROM shop WHERE price > 10.00 LIMIT 10;

To show 10 results, starting from the 30th:

SELECT * FROM shop WHERE price > 10.00 LIMIT 30, 10;

To show results in descending order of price:

SELECT * FROM shop WHERE price > 10.00 ORDER BY price DESC;

If we just had ORDER BY price, the results would be listed in ascending order of price. However, we have included DESC as well, which specifies that we want the results in descending order.

What if you want to find the name of an item where the price is within specific limits?

SELECT item FROM shop WHERE price BETWEEN 10.00 AND 30.00;

Aggregrate functions

To determine the number of rows in a table, use COUNT, for example:

SELECT COUNT(*) FROM shop;

Sums and averages of columns can easily be calculated using SUM and AVG:

SELECT SUM(price) FROM shop;
SELECT AVG(price) FROM shop;

Similarly, the minimum and maximum values can be found easily:

SELECT MIN(price) FROM shop;
SELECT MAX(price) FROM shop;

Suppose we have a table for storing the prices of items in a shop, and we now have a category column:

CREATE TABLE shop (item VARCHAR(50), price DECIMAL(5,2), category VARCHAR(50));

Categories could include CD, DVD, and book, for example. How do we find the maximum price in each category? This can be done using GROUP BY. For example:

SELECT category, MAX(price) FROM shop GROUP BY category;

This will list the maximum price from each category.

Regular expressions

Regular expressions can be used in queries. Suppose we have a table called names with columns first and last. Then:

SELECT first, last FROM names;

gives, for example:

+-------+---------+
| first | last    |
+-------+---------+
| John  | Citizen |
| Jane  | Smith   |
+-------+---------+

What if we want to search for all first names that end in the character "n"?

SELECT first,last FROM names WHERE first REGEXP 'n$';

Other useful functions

The CONCAT function allows you to combine results from different columns together, with additional text if required. Again, suppose we have a table called names with columns first and last. What if we want to combine the first and last names together? To do this, we use CONCAT.

SELECT CONCAT(first,' ',last) FROM names;

gives:

+------------------------+
| CONCAT(first,' ',last) |
+------------------------+
| John Citizen           |
| Jane Smith             |
+------------------------+

Modifying rows

To delete all rows from a table:

DELETE FROM my_table;

To delete specific rows from a table we just introduce a WHERE clause:

DELETE FROM my_table WHERE name = "Bob";

Suppose we have a table with 5 columns: a, b, c, d, and e, containing numerical data.

Simple update of a single row in a single column:

UPDATE my_table SET b = 42 WHERE a = 21;

Updating an entire column:

UPDATE my_table SET b = a*c;

Using a WHERE clause enables you to easily modify only rows that satisfy a specified condition:

UPDATE my_table SET b = a*c WHERE d > 5;

Updating multiple columns is very straightforward:

UPDATE my_table SET b = a*c, d = a*c/e WHERE e > 0;

Modifying columns

The following:

ALTER TABLE my_table ADD name VARCHAR(50);

will insert a new column called name at the end of the existing columns. To insert a new column somewhere else, for example after a column called user:

ALTER TABLE my_table ADD name VARCHAR(50) AFTER user;

To insert a new column before all other columns:

ALTER TABLE my_table ADD name VARCHAR(50) FIRST;

We can also delete a column, for example:

ALTER TABLE my_table DROP COLUMN product_description;

The data type of a column can also be changed, for example, to change a column id to type TINYINT:

ALTER TABLE my_table MODIFY id TINYINT;

To rename a column, you need to specify the current column name, the new column name, and the current data type:

ALTER TABLE my_table CHANGE product_id id INT;

To add a default value to a column:

ALTER TABLE my_table ALTER COLUMN product_id SET DEFAULT 0;

Deleting databases and tables

To delete a database:

DROP DATABASE my_database;

To delete a table:

DROP TABLE my_table;

Note that this deletes the table itself, not just the contents of the table.

Copying a table

Suppose we want to make a copy of my_table1 called my_table2. Firstly, make my_table2:

CREATE my_table2 LIKE my_table1;

Then populate it:

INSERT INTO my_table2 SELECT * FROM my_table1;

Using INSERT INTO allows you to modify the data in some way if you want to, or copy from a table with a different number of columns.

Alternatively, copying a table can be done in one line:

CREATE TABLE my_table2 SELECT * FROM my_table1;

If the new table might already exist, it's safer to do this:

CREATE TABLE IF NOT EXISTS my_table2 SELECT * FROM my_table1;

Renaming a table

RENAME TABLE my_table1 TO my_table2;

A table can be moved from one database to another using RENAME TABLE, for example:

RENAME TABLE my_database1.my_table TO my_database2.my_table;