3.5 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 |
+---------+--------+-------+

Subsections

• 3.5.1 The Maximum Value for a Column
• 3.5.2 The Row Holding the Maximum of a Certain Column
• 3.5.3 Maximum of Column per Group
• 3.5.4 The Rows Holding the Group-wise Maximum of a Certain Field
• 3.5.5 Using user variables
• 3.5.6 Using Foreign Keys
• 3.5.7 Searching on Two Keys
• 3.5.8 Calculating Visits Per Day
• 3.5.9 Using AUTO_INCREMENT

User Comments

I promise this is the last time I'll do this, as it's sure
to be annoying for others, but ... here's some info
for other newbies (please feel free to correct
any mistakes): INT(N) is an integer with up to N
digits; UNSIGNED means that the integer cannot be
preceded by a symbol/sign, thus it can't be a
negative integer; ZEROFILL means that the entry will
be automatically extended to N digits using zeroes
added to the left of the number; DEFAULT '' defines
the 'zero' entry, i.e. the entry that MySQL will make
automatically, should no integer be entered; NOT
NULL means that each row must have an entry in
this column, i.e. it can't be empty; CHAR(N) you've
met already, but it is CHAR and not VARCHAR in this
case so that each entry will be numerically identical
(20 bytes in this case) and can be used in a
comparison; DOUBLE(M,D) means a double-
precision floating-point value: the M indicates the
maximum size of the value [in this case up to
9999999999999.99 {15 digits plus 1 point = 16}]
and the D indicates the number of digits after the
point [in this case 2]; PRIMARY KEY() specifies the
unique identity of each entry, achieved in this case
by combining the article key with the dealer key. This
table doesn't contain an extra 'primary key' column
as it's not necessary.
Use the search tool to find out about all the code in
the rest of the tutorial because, as I say, there are
very few detailed explanations for the uninitiated
from this point on and I wouldn't want to clutter up
the pages with superfluous comments.

i started working with SQL today, just wondering, what constitutes the best aspect of your table. How do you choose a primary id?

The best primary key is something that is unique to each entry, will not change, and every entry will have.
for example given
table(firstName varchar(20), lastName varchar(20), homePhone char(13), SSN char(13))
(("Bill", "Clinton", "(630)555-1212", "123-45-6789"),
("Hillary", "Clinton" "(630)555-1212", "567-89-1234"),
("Bill", "Smith", "(210)752-5911", "765-43-2109"))
The best primary key would be the SSN. Sometimes we don't have access to a good primary key, so we just create an entry that automatically increments. 1,2,3...
Jim

Add your own comment.