3.6 examples of common queries

3.6 examples of common queries

Here are some examples of using MySQL to solve common problems.

In some examples, the shop table is used to store the price of each item (item number) of a merchant (dealer. Assuming that each merchant has a fixed price for each item, (article, dealer) is the primary keyword of the record.

Start the command line tool mysql and select the database:

shell> mysql your-database-name

(In most MySQL databases, you can use the test database ).

You can use the following statement to create an example table:

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

After the statement is executed, the table should contain the following:

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

3.6.1 maximum value of a column
"What is the largest item number ?"

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

3.6.2 rows with the maximum value of a column

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

It is easy to use a subquery:

SELECT article, dealer, priceFROM shopWHERE price=(SELECT MAX(price) FROM shop);+---------+--------+-------+| article | dealer | price |+---------+--------+-------+| 0004 | D | 19.95 |+---------+--------+-------+

Another solution is to use left join or sort all rows in descending order of price, and then use the MySQL-specific LIMIT clause to retrieve only the first row:

SELECT s1.article, s1.dealer, s1.priceFROM shop s1LEFT JOIN shop s2 ON s1.price < s2.priceWHERE s2.article IS NULL;SELECT article, dealer, priceFROM shopORDER BY price DESCLIMIT 1;

Note:: If there are multiple most expensive items, if each item is 19.95, then LIMIT will only take one of them.

3.6.3 identify the maximum value of a column by group
Task: Find the highest price for each item.

SELECT article, MAX(price) AS priceFROM shopGROUP BY article;+---------+-------+| article | price |+---------+-------+| 0001 | 3.99 || 0002 | 10.99 || 0003 | 1.69 || 0004 | 19.95 |+---------+-------+

3.6.4 rows with the maximum value between groups for a field

Task: Find the most expensive dealer for each item.

This problem can be solved using a subquery:

SELECT article, dealer, priceFROM shop s1WHERE price=(SELECT MAX(s2.price)FROM shop s2WHERE s1.article = s2.article);+---------+--------+-------+| article | dealer | price |+---------+--------+-------+| 0001 | B | 3.99 || 0002 | A | 10.99 || 0003 | C | 1.69 || 0004 | D | 19.95 |+---------+--------+-------+

The preceding example uses an associated subquery, Which is inefficient (see Section 13.2.10.7, "Correlated Subqueries "). other possible solutions use an unrelated subquery or left join in the From clause.

Unrelated subqueries:

SELECT s1.article, dealer, s1.priceFROM shop s1JOIN (SELECT article, MAX(price) AS priceFROM shopGROUP BY article) AS s2ON s1.article = s2.article AND s1.price = s2.price;

LEFT JOIN:

SELECT s1.article, s1.dealer, s1.priceFROM shop s1LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.priceWHERE s2.article IS NULL;

Left join is effective when s1.price reaches the maximum value. At that time, s2.price will not have a larger value and s2 will be NULL. View Section 13.2.9.2, "JOIN
Syntax ".

3.6.5 use custom Variables