MySQL Chinese reference manual

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

Some examples use the database table "shop", which contains the price of each article (item number) of a businessman. Assuming that each merchant's article has a separate fixed price, then (the item, merchant) is the primary key of the record.

You can create an example database table like this:

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);

OK, here's the example data:

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

The maximum value of 3.1 columns

"What's the biggest item number?" ”

SELECT MAX(article) AS article FROM shop
+---------+
| article |
+---------+
|　　　 4 |
+---------+

3.2 Rows that have the maximum value of a column

"Find the number, merchant, and price of the most expensive articles."

在ANSI-SQL中这很容易用一个子查询做到：
SELECT article, dealer, price
FROM　 shop
WHERE　price=(SELECT MAX(price) FROM shop)

In MySQL (and no subqueries), take 2 steps:

Gets the maximum value from the table with a SELECT statement.

Use this value to compile the actual query:

SELECT article, dealer, price
FROM　 shop
WHERE　price=19.95

Another solution is to sort all rows in descending price order and use the first line of MySQL-specific limit clauses:

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

Note: If there are multiple most expensive articles (such as each 19.95), the limit solution only shows one of them!