GNU Info

(mysql.info)Examples

---

Next: Batch mode Prev: Getting information Up: Tutorial

---

Enter node , (file) or (file)node

Examples of Common Queries
==========================

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

Some of the examples use the table `shop' 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 (`article',
`dealer') is a primary key for the records.

Start the command line tool `mysql' and select a database:

     mysql your-database-name

(In most MySQL installations, you can use the database-name 'test').

You can create the example table as:

     CREATE TABLE shop (
      article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
      dealer  CHAR(20)                 DEFAULT ''     NOT NULL,
      price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,
      PRIMARY KEY(article, dealer));
     
     INSERT INTO shop VALUES
     (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),(3,'C',1.69),
     (3,'D',1.25),(4,'D',19.95);

Okay, so the example data is:

     mysql> SELECT * FROM shop;
     
     +---------+--------+-------+
     | 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 |
     +---------+--------+-------+

example-Maximum-column

The maximum value for a column

example-Maximum-row

The row holding the maximum of a certain column

example-Maximum-column-group

Maximum of column per group

example-Maximum-column-group-row

The rows holding the group-wise maximum of a certain field

example-user-variables

Using user variables

example-Foreign keys

Using foreign keys

Searching on two keys

Searching on Two Keys

Calculating days

Calculating visits per day