3.6. Examples of Common Queries

MySQL 5.0

3.6. Examples of Common Queries

Here are examples of how to solve some common problems with MySQL.

Some of the examples use the table to hold the price of each article (item number) for certain traders (dealers). Supposing that each trader has a single fixed price per article, then (, ) is a primary key for the records.

Start the command-line tool mysql and select a database:

shell> 

(In most MySQL installations, you can use the database named ).

You can create and populate the example table with these statements:

mysql> 
    -> 
    -> 
    -> 
    -> 
mysql> 
    -> 
    -> 

After issuing the statements, the table should have the following contents:

mysql> 
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.45 |
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | B      |  1.45 |
|    0003 | C      |  1.69 |
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

3.6.1. The Maximum Value for a Column

What's the highest item number?

SELECT MAX(article) AS article FROM shop;

+---------+
| article |
+---------+
|       4 |
+---------+

3.6.2. The Row Holding the Maximum of a Certain Column

Task: Find the number, dealer, and price of the most expensive article.

This is easily done with a subquery:

SELECT article, dealer, price
FROM   shop
WHERE  price=(SELECT MAX(price) FROM shop);

Another solution is to sort all rows descending by price and get only the first row using the MySQL-specific clause:

SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;

Note: If there were several most expensive articles, each with a price of 19.95, the solution would show only one of them.

3.6.3. Maximum of Column per Group

Task: Find the highest price per article.

SELECT article, MAX(price) AS price
FROM   shop
GROUP BY article

+---------+-------+
| article | price |
+---------+-------+
|    0001 |  3.99 |
|    0002 | 10.99 |
|    0003 |  1.69 |
|    0004 | 19.95 |
+---------+-------+

3.6.4. The Rows Holding the Group-wise Maximum of a Certain Field

Task: For each article, find the dealer or dealers with the most expensive price.

This problem can be solved with a subquery like this one:

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article);

3.6.5. Using User-Defined Variables

You can employ MySQL user variables to remember results without having to store them in temporary variables in the client. (See Section 9.3, “User-Defined Variables”.)

For example, to find the articles with the highest and lowest price you can do this:

mysql> 
mysql> 
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

3.6.6. Using Foreign Keys

In MySQL, tables support checking of foreign key constraints. See Section 14.2, “The Storage Engine”, and Section 1.9.5.5, “Foreign Keys”.

A foreign key constraint is not required merely to join two tables. For storage engines other than , it is possible when defining a column to use a () clause, which has no actual effect, and serves only as a memo or comment to you that the column which you are currently defining is intended to refer to a column in another table. It is extremely important to realize when using this syntax that:

  • MySQL does not perform any sort of to make sure that actually exists in (or even that itself exists).

  • MySQL does not perform any sort of action on such as deleting rows in response to actions taken on rows in the table which you are defining; in other words, this syntax induces no or behavior whatsoever. (Although you can write an or clause as part of the clause, it is also ignored.)

  • This syntax creates a column; it does not create any sort of index or key.

  • This syntax will cause an error if used in trying to define an table.

You can use a column so created as a join column, as shown here:

CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    PRIMARY KEY (id)
);

INSERT INTO person VALUES (NULL, 'Antonio Paz');

SELECT @last := LAST_INSERT_ID();

INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);

INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');

SELECT @last := LAST_INSERT_ID();

INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);

SELECT * FROM person;
+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | Antonio Paz         |
|  2 | Lilliana Angelovska |
+----+---------------------+

SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style   | color  | owner |
+----+---------+--------+-------+
|  1 | polo    | blue   |     1 |
|  2 | dress   | white  |     1 |
|  3 | t-shirt | blue   |     1 |
|  4 | dress   | orange |     2 |
|  5 | polo    | red    |     2 |
|  6 | dress   | blue   |     2 |
|  7 | t-shirt | white  |     2 |
+----+---------+--------+-------+


SELECT s.* FROM person p INNER JOIN shirt s
   ON s.owner = p.id
 WHERE p.name LIKE 'Lilliana%'
   AND s.color <> 'white';

+----+-------+--------+-------+
| id | style | color  | owner |
+----+-------+--------+-------+
|  4 | dress | orange |     2 |
|  5 | polo  | red    |     2 |
|  6 | dress | blue   |     2 |
+----+-------+--------+-------+

When used in this fashion, the clause is not displayed in the output of or :

SHOW CREATE TABLE shirt\G
*************************** 1. row ***************************
Table: shirt
Create Table: CREATE TABLE `shirt` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`style` enum('t-shirt','polo','dress') NOT NULL,
`color` enum('red','blue','orange','white','black') NOT NULL,
`owner` smallint(5) unsigned NOT NULL,
PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

The use of in this way as a comment or “reminder” in a column definition works with both and tables.

3.6.7. Searching on Two Keys

An using a single key is well optimized, as is the handling of .

The one tricky case is that of searching on two different keys combined with :

SELECT field1_index, field2_index FROM test_table
WHERE field1_index = '1' OR  field2_index = '1'

This case is optimized from MySQL 5.0.0. See Section 7.2.6, “Index Merge Optimization”.

You can also solve the problem efficiently by using a that combines the output of two separate statements. See Section 13.2.7.2, “ Syntax”.

Each searches only one key and can be optimized:

SELECT field1_index, field2_index
    FROM test_table WHERE field1_index = '1'
UNION
SELECT field1_index, field2_index
    FROM test_table WHERE field2_index = '1';

3.6.8. Calculating Visits Per Day

The following example shows how you can use the bit group functions to calculate the number of days per month a user has visited a Web page.

CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
             day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
            (2000,2,23),(2000,2,23);

The example table contains year-month-day values representing visits by users to the page. To determine how many different days in each month these visits occur, use this query:

SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
       GROUP BY year,month;

Which returns:

+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 |    01 |    3 |
| 2000 |    02 |    2 |
+------+-------+------+

The query calculates how many different days appear in the table for each year/month combination, with automatic removal of duplicate entries.

3.6.9. Using AUTO_INCREMENT

The attribute can be used to generate a unique identity for new rows:

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
 );

INSERT INTO animals (name) VALUES 
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

Which returns:

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

You can retrieve the most recent value with the SQL function or the C API function. These functions are connection-specific, so their return values are not affected by another connection which is also performing inserts.

Note: For a multiple-row insert, and actually return the key from the first of the inserted rows. This allows multiple-row inserts to be reproduced correctly on other servers in a replication setup.

For and tables you can specify on a secondary column in a multiple-column index. In this case, the generated value for the column is calculated as ) + 1 WHERE prefix=. This is useful when you want to put data into ordered groups.

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
);

INSERT INTO animals (grp,name) VALUES 
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

Which returns:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

Note that in this case (when the column is part of a multiple-column index), values are reused if you delete the row with the biggest value in any group. This happens even for tables, for which values normally are not reused.

If the column is part of multiple indexes, MySQL will generate sequence values using the index that begins with the column, if there is one. For example, if the table contained indexes and , MySQL would ignore the for generating sequence values. As a result, the table would contain a single sequence, not a sequence per value.

To start with an value other than 1, you can set that value with or , like this:

mysql> 

More information about is available here: