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.

GSL - GNU Scientific Library

The GNU Scientific Library (GSL) is a very useful numerical library, consisting of a wide range of mathematical functions, including:

• interpolation
• minimization
• linear algebra
• root-finding
• least squares fitting
• histograms
• differential equations

These are just a few examples of the over 1000 functions included. And the best thing is that it's free and is released under the GPL.

Introduction to Perl modules

Perl modules enable you to make your Perl codes as re-usable as possible, and in addition, can be used for object-oriented programming.

A very simple first example

We'll begin with an extremely simple example. We want to have a subroutine that returns the number of days in a specified year. Rather than put this subroutine into lots of different codes, we want to make a module, and then make use of this module in all our codes.

Firstly, we make a file called Example.pm containing the following code:

package Example;

sub daysInYear
{
   my $year = shift;
   if ($year % 4 == 0)
   {
      if ($year % 100 == 0 && $year % 400 != 0)
      {
         return(365);
      }
      else
      {
         return(366);
      }
   }
   else
   {
      return(365);
   }
}

1; # do not forget this line!

The package name is Dates and has a single subroutine daysInYear that returns the days in a year. Note that the last line must return true.

Here's a simple example showing how to use this package:

#!/usr/bin/perl

use Example;

$days = Example::maxDaysInYear(2004);
print "days in 2004 were: $days\n";

Here we assume this Perl program and Example.pm are in the same directory.

This is the simplest way to make re-usable code. You just need to put all your frequently-used subroutines into modules (.pm files) with appropriate package names, and then call them from all your Perl programs.

Creating and using classes

Now we consider the following module, called Book.pm, which stores information about a book:

package Book;

sub new
{
   my $class = shift;
   my $self = {
      _title => shift,
      _author => shift,
      _publisher => shift
   };
   bless $self, $class;
   return $self;
}

sub getTitle
{
   my ($self) = @_;
   return $self->{_title};
}

sub getAuthor
{
   my ($self) = @_;
   return $self->{_author};
}

sub getPublisher
{
   my ($self) = @_;
   return $self->{_publisher};
}

1;

In this example we are making a class called Book. In order to create objects, we need to have a constructor method that returns a new object rather than just a normal data type. Here we define an object constructor called new (although this can have any name you want, new is most frequently used).

The line containing bless is important. bless is usually called as the last action in a constructor method, and is used in the following ways:

bless REF
bless REF, CLASSNAME

This tells the entity referenced by REF that it is now an object in the CLASSNAME package. If CLASSNAME is omitted, it tells the entity referenced by REF that it is now an object in the current package.

In this module we have also defined 3 methods: getTitle, getAuthor and getPublisher, which are simple subroutines that return the title, author and publisher respectively. These subroutines enable you to access the data in the Book class.

This module can be used in the following way:

#!/usr/bin/perl

use Book;

$object = new Book("Principles of Modern Chemistry",
           "John Citizen", "Smith Books");

$title = $object->getTitle();
$author = $object->getAuthor();
$publisher = $object->getPublisher();

print "title = $title\n";
print "author = $author\n";
print "publisher = $publisher\n";

Inheritance

Suppose we want a new class Chapter. We will want to know the title, author and publisher of the book that the Chapter comes from. It thus makes sense to inherit Book. Contents of Chapter.pm:

package Chapter;

use Book;
@ISA = ("Book");
1;

This creates a derived class called Chapter which inherits from the base class and does nothing else.

How to we add extra methods to the subclass, for example a method getText which returns text. We just need to add the following to Chapter.pm:

sub getText
{
   my ($self) = @_;
   return $self->{_text};
}

We'll also need to modify the Chapter constructor (we of course don't want to add the text data to Book, as this is something that should only be in Chapter). We can add the following to Chapter.pm:

sub new
{
   my $class = shift;
   my $self  = $class->SUPER::new();
   $self->{_text} = $_[3];
   bless $self, $class;
   return $self;
}

To summarize, we have created a class called Book which contains information about the a specific book. We also created a derived class called Chapter which inherits Book, and in addition contains additional data and an additional method, not present in Book.

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.

Fitting functions to data

Ever needed to find a function to fit to some experimental data? fityk provides an easy way to do this. This is a GUI based application that will fit analytic functions to data using a variety of different optimization methods. fityk has many pre-defined functions, including peak shaped functions (including Gaussians) as well as polynomials. You can even define your own functions.

An alternative is to write your own software using CERN's MINUIT function minimization library, for example, however this would be much more time-consuming (although in some situations this might be the best option).

fityk is available for Mac OS X, Linux and Windows. And it's free!

gnuplot basics

gnuplot is a nice program for quickly making plots. Here we quickly list a number of useful features (the ones that I most often use). Note that we concentrate on plotting data from files rather than plotting mathematical functions.

To generate a plot as a png file of size 640 by 480 using the courier font with size 8:

set terminal png size 640,480 font cour 8

To generate a plot as a colour postscript file, with solid lines and enhanced text (e.g. superscripts and subscripts can be included):

set terminal postscript enhanced color solid

Note that portrait is used by default. For landscape, use this:

set terminal postscript enhanced color solid landscape

To set the output file:

set output "plot.png"

To specify the x-axis range:

set xrange [0:5]

To specify the y-axis range:

set yrange [0:100]

To set the number of x-axis minor ticks to 8, for example:

set mxtics 8

To have a date/time x-axis:

set xdata time
set timefmt "%Y-%m-%dT%H:%M:%S"

This is useful for a data file using the format YYYY-MM-DDTHH:MM:SS. To only show years and months only in the plot x-axis:

set format x "%Y-%m"

This is useful if showing the full date/time value takes up too much space, or if showing the full timestamp is not necessary (for example, if a plot only covers a period of 20 minutes, you might not want to show years, months, days and hours).

Note that "%Y" means a 4-digit year, e.g. 2004, however you could also use "%y" which means a 2-digit year, e.g. 04 represents 2004.

Example setting the x-axis range when date/time is used:

set xrange ["2005-02-01T00:00:00Z":"2005-04-01:T00:00:00Z"]

To set the widths of the major x-axis ticks when a time scale is used, for example:

set xtics "00:00:00","04:00:00","23:00:00"

This is for a plot with a time x-axis for the range 00:00 to 23:00, with major ticks every 4 fours.

To have a grid:

set grid

To include multiple plots in a single page, we use the multiplot command. For example, to have 3 rows with 2 columns each:

set multiplot layout 3,2 title "My 6 plots"

Note that if you are using a very old version of gnuplot you won't be able to specify a layout, and will have to manually specify the sizes and locations of each individual plot.

If you have trouble with multiple plots not lining up nicely, manually force margins. For example, to ensure the left margin of each plot is lined up vertically:

set lmargin 5

If you have several rows with different magnitudes of data, you can easily end up with plots not lining up vertically, so setting a left margin is very useful.

If you just have a single plot and are not using the multiplot command, a title can be specified like this:

set title "Time history of magnitude since event"

To plot data from an ASCII file data.txt using lines:

plot "data.txt" using 1:2 title "data 01" with lines

This will plot data using x values from column 1 and y values from column 2. Note that the first column is column 1, not 0. Replace "with lines" with "with points" to use points instead of lines.

To plot multiple sets of data from the same file:

plot "data.txt" using 1:2 title "data 01" with lines,\
"data.txt" using 1:3 title "data 02" with lines,\
"data.txt" using 1:4 title "data 03" with lines

If you want to exclude some data from a plot, for example negative values:

plot "data.txt" using 1:(($2 >= 0) ? $2 : 1/0) title "data (positive only)" with points

It's easy to include simple maths:

plot "data.txt" using 1:(2.5*$2/$3) title "values" with points

This will plot the ratio of column 2 to column 3 multiplied by 2.5.

The text specified by title in the plot command is used in the legend. To not show any legend:

set key off

To display a key in the bottom right of the plot, for example, use:

set key bottom right

To set x-axis and y-axis labels:

set xlabel "Time since event"
set ylabel "Magnitude"

To show a timestamp at the bottom of a plot:

set timestamp
show timestamp

If your x-axis major tick values take up too much space and overlap, you can rotate them:

set xtics nomirror rotate by -45

This will rotate them by -45 degrees.

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.

How to include rich text in a QImage

One method is to use a QTextDocument. In the example code fragment below we assume that text is a QString containing some rich text.

  QTextDocument doc;
  doc.setHtml(text);
  double offsetV = doc.size().height()*0.5;
  double offsetH = 0.;
  painter->translate(QPointF(-offsetH, -offsetV));
  doc.drawContents(painter);
  painter->translate(QPointF(offsetH, offsetV));

This will give left-aligned text at the current position. For centre-aligned text use

   double offsetH = doc.size().width()*0.5;

or for right-aligned text

   double offsetH = doc.size().width();

C++ inheritance: a simple example

Suppose we want to write a scientific plotting application using Qt that can make simple 2D plots as well as 3D plots. One option would be to make two classes, Plot2D and Plot3D. For example:

class Plot2D
{
   public:
      Plot2D();
      ~Plot2D();
      void setAxisLimitsX(int, int);
      void setAxisLimitsY(int, int);
      void setNumDataPoints(int);
      void setData(float *x, float *y);
   private:
      QImage *plot;
};

Plot3D
{
   public:
      Plot3D();
      ~Plot3D();
      void setAxisLimitsX(int, int);
      void setAxisLimitsY(int, int);
      void setAxisLimitsZ(int, int);
      void setNumDataPoints(int);
      void setData(float *x, float *y, float *z);
   private:
      QImage *plot;
};

Clearly, both classes are quite similar. Both have methods for setting the x-axis limits, the y-axis limits, the number of data points, and a QImage containing the actual plot. The only differences are that Plot3D has an additional method for setting the z-axis limits, and the setData method has an additional array for the 3rd component of the data.

An alternative to the above is to use inheritance. We make a class called Plot:

class Plot
{
   public:
      Plot();
      ~Plot();
      void setAxisLimitsX(int, int);
      void setAxisLimitsY(int, int);
      void setNumDataPoints(int);
      void setData(float *x, float *y);
   private:
      QImage *plot;
};

This is very general and has methods and data that apply to both 2D and 3D plots. We can now define the Plot2D and Plot3D classes:

class Plot2D : public Plot
{
   public:
      Plot2D();
      ~Plot2D();
};

class Plot3D : public Plot
{
   public:
      Plot3D();
      ~Plot3D();
      void setAxisLimitsZ(int, int);
      void setData(float *x, float *y, float *z);
};

These two classes (known as derived classes) inherit from the Plot class, and contain all public and protected variables of the base class Plot (but not private variables). For example, both Plot2D and Plot3D here have access to setAxisLimitsX and setAxisLimitsY. However, note that the constructors and destructors of base classes are not inherited.

The use of inheritance enables code to be reused. After defining the base class, we don't need to define the same features in multiple derived classes. This sames time!

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;

Qt: running a time-consuming operation in a separate thread to the GUI

In this example we assume that a function my_func takes a long time to run, and would cause the GUI to freeze if it were run in the main GUI thread. In order to avoid the GUI freezing, we run my_func in a separate thread (sometimes referred to as a worker thread). The GUI will be therefore be fully functional while my_func is running.

When this application launched, a small GUI will appear containing a single button. When the button is clicked, the function my_func is run in a separate thread. When the thread finishes running, a message appears on the screen, thus also illustrating a very useful feature of Qt: signals can be sent from one thread to another.

The example code consists of: main.C, user_def.h, window.C, window.h, thread.C and thread.h.

main.C

Here we define the time-consuming operation. In this example, it is just a summation. Of course, in a real application it can be something much more complex.

#include <QApplication>

#include "window.h"
#include "user_def.h"

//----------------------------------------------------------------------------
//
// the user's time-consuming function
//
//----------------------------------------------------------------------------

void my_func(void)
{
// time-consuming code
   double sum;
   for (int i=0; i<100000; i++)
   {
      for (int j=0; j<100000; j++)
      {
         sum = sum * i / j;
      }
   }
}

//----------------------------------------------------------------------------
//
// main program
//
//----------------------------------------------------------------------------

int main (int argc, char **argv)
{
   QApplication app(argc, argv);
   Window window;
   window.show();
   return app.exec();
}

user_def.h

#ifndef USER_DEF_H_
#define USER_DEF_H_

void my_func(void);

#endif /*USER_DEF_H_*/

window.C

Here we setup a very simple GUI just containing a single button. When the button is clicked, the time-consuming operation is run in another thread when QThread::start() is called. The other thread sends a signal to the GUI thread notifying that it has finished. When the GUI thread receives this signal, it displays a message on the screen using a QMessageBox.

#include "window.h"

//----------------------------------------------------------------------------
//
// the main GUI
//
//----------------------------------------------------------------------------

//------------
// constructor
//------------

Window::Window(QWidget *parent) : QWidget(parent)
{
   setWindowTitle("Qt example 01");
   runButton = new QPushButton("Run the function...");
   connect(runButton, SIGNAL(clicked()), 
           this, SLOT(run_thread()));

   QHBoxLayout *layout = new QHBoxLayout;
   layout->addWidget(runButton);
   setLayout(layout);

// the other thread

   otherThread = new timeConsumingThread();

// handle finished signal from the other thread

   connect(otherThread, SIGNAL(finished()),
           this, SLOT(displayFinishedBox()));

}

//-----
// slot
//-----

void Window::run_thread()
{
   if (!otherThread->isRunning())
   {
      otherThread->start();
   }
}

//----------------
// display message
//----------------

void Window::displayFinishedBox()
{
   QMessageBox::information(this, tr("Qt Example 01"), 
                tr("The function my_func has finished."));
}

window.h

#ifndef WINDOW_H_
#define WINDOW_H_

#include <QPushButton>
#include <QThread>
#include <QHBoxLayout>
#include <QMessageBox>

#include "thread.h"

class Window : public QWidget
{ 
   Q_OBJECT 

   public:
      Window(QWidget *parent = 0);

   private:
      QPushButton *runButton;
      timeConsumingThread *otherThread;

   public slots:
      void run_thread();
      void displayFinishedBox();
};

#endif /*WINDOW_H_*/

thread.C

Here we setup the other thread. In the run() reimplementation we define what is to be run in the other thread; in this example we just run the function my_func.

#include "thread.h"
#include "user_def.h"

//----------------------------------------------------------------------------
//
// thread which runs the time-consuming function
//
//----------------------------------------------------------------------------

//------------
// constructor
//------------

timeConsumingThread::timeConsumingThread(QObject *parent) 
   : QThread(parent)
{

}

//----
// run
//----

void timeConsumingThread::run()
{
   my_func();
}

thread.h

#ifndef THREAD_H_
#define THREAD_H_

#include <QThread>

class timeConsumingThread : public QThread
{  
   Q_OBJECT

   public:
      timeConsumingThread(QObject *parent = 0);

   protected:
      void run();

};

#endif /*THREAD_H_*/

This example can be easily compiled using

qmake -project
qmake
make