Example table for MySQL

In my MySQL posts I’ve tended to use fruit in my example tables, so in this post will standardize the table structure and data that appears in future examples. Those future posts will refer back to this post for the table structure and base data.

Table schema

Use this to create the table schema:

CREATE TABLE IF NOT EXISTS `fruit` (
  `fruit_id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `variety` varchar(50) NOT NULL,
  PRIMARY KEY  (`fruit_id`)
);

I realise that this should really be normalised with two tables, one for the fruit and another for the varieties, but this simplistic non-normalised table is going to be useful for simple examples presented in future posts.

Table data

Use the following SQL to insert the data. I’ve mixed up the order of the names and varieties so when doing a query without ORDER BY they come out in a random order.

INSERT INTO `fruit` (`fruit_id`, `name`, `variety`) VALUES
(1, 'Apple', 'Red Delicious'),
(2, 'Pear', 'Comice'),
(3, 'Orange', 'Navel'),
(4, 'Pear', 'Bartlett'),
(5, 'Orange', 'Blood'),
(6, 'Apple', 'Cox''s Orange Pippin'),
(7, 'Apple', 'Granny Smith'),
(8, 'Pear', 'Anjou'),
(9, 'Orange', 'Valencia'),
(10, 'Banana', 'Plantain'),
(11, 'Banana', 'Burro'),
(12, 'Banana', 'Cavendish');

SELECT * FROM fruit

This is the output from SELECT * FROM fruit:

+----------+--------+---------------------+
| fruit_id | name   | variety             |
+----------+--------+---------------------+
|        1 | Apple  | Red Delicious       |
|        2 | Pear   | Comice              |
|        3 | Orange | Navel               |
|        4 | Pear   | Bartlett            |
|        5 | Orange | Blood               |
|        6 | Apple  | Cox's Orange Pippin |
|        7 | Apple  | Granny Smith        |
|        8 | Pear   | Anjou               |
|        9 | Orange | Valencia            |
|       10 | Banana | Plantain            |
|       11 | Banana | Burro               |
|       12 | Banana | Cavendish           |
+----------+--------+---------------------+

Note that although the data is in order of the fruit_id, after doing some deletes and inserts (or truncates and re-inserting the data) it could be in any order. You can never count on the order of data from a SQL query unless ORDER BY is used.

Posts with this example

Refer to the "related posts" links below for posts using this example table. The first one will be posted this time next week, looking at how to order in a particular order that is different from the way ORDER BY would normally order it.