MySQL must know the query

Objective:

According to many friends around the reflection, SQL long time without words will inevitably unfamiliar! Several examples of queries in this article allow you to quickly recall the basic query syntax commonly used in MySQL in 5 minutes!

------------

Examples of how to use MySQL to solve some common problems

In some cases, the database table "shop" 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, then (item, merchant) is the primary key for that record.

Start the command line tool MySQL and select the database:

shell> Use test

You can use the following statement to create a sample table:

Mysql> CREATE TABLE Shop (
-Article INT (4) UNSIGNED zerofill DEFAULT ' 0000 ' not NULL,
-Dealer CHAR (+) DEFAULT ' not NULL,
, Price DOUBLE (16,2) The DEFAULT ' 0.00 ' not NULL,
-PRIMARY KEY (article, dealer));
Mysql> 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:

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 |
+---------+--------+-------+
1. Maximum value of the column
"What's the biggest item number?" ”

SELECT MAX  as  from shop;

+---------+
| Article |
+---------+
| 4 |
+---------+

2. Rows that have a maximum value for a column
Task: Find the number, seller and price of the most expensive item.
This is easy to do with a subquery:

SELECT article, dealer, Price  from  Shop WHERE Price=(SELECTMAX from shop);

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

SELECT article, dealer, Price  from  Shop ORDER  by DESC  1;

Note: If you have several of the most expensive items (for example, each price is 19.95), the limit solution only shows one of them!

3. Maximum value of column: by group
Task: What is the maximum price for each item?

SELECT MAX  as  Price  from  Shop GROUP  by article

+---------+-------+
| Article | Price |
+---------+-------+
| 0001 | 3.99 |
| 0002 | 10.99 |
| 0003 | 1.69 |
| 0004 | 19.95 |
+---------+-------+

4. Rows that have the largest inter-group value for a field
Task: For each item, find the most expensive item of the dealer.

You can use such a subquery to resolve the problem:

SELECT article, dealer, Price  from Shop S1 WHERE Price=(SELECTMAX(s2.price) from shop S2 WHERE = s2.article);

5. Using User variables
You can empty the MySQL user variables to record the results without having to save them to the client's temporary variables.
For example, to find the item with the highest or lowest price, the method is:

Mysql> SELECT @min_price: =min (Price), @max_price: =max-shop;
Mysql> SELECT * from shop WHERE [email protected]_price or [email protected]_price;
+---------+--------+-------+
| Article | dealer | Price |
+---------+--------+-------+
| 0003 | D | 1.25 |
| 0004 | D | 19.95 |
+---------+--------+-------+

Add: Single table longest MySQL query statement
How can I find out the top three of the product categories and corresponding sales quantities sold in the sale of goods, from 2001-01-01 to 2014-01-01, with a quantity of not less than 2?

MySQL>Select from Goodsmessage;

Experimental data:

+---------+-------+------------+
| Goodsid | Gtype | Gdate |
+---------+-------+------------+
| 1 | Teaching Materials | 2001-10-02 |
| 2 | Snacks | 2015-06-23 |
| 4 | Books | 2015-06-23 |
| 6 | Teaching Materials | 2008-10-03 |
| 7 | Teaching Materials | 2001-10-03 |
| 8 | Press | 2001-04-01 |
| 9 | Press | 2001-01-01 |
| 10 | Teaching Materials | 2001-10-01 |
| 11 | Teaching Materials | 2001-10-01 |
| 12 | Teaching Materials | 2001-10-01 |
| 13 | Teaching Materials | 2001-10-01 |
| 14 | Teaching Materials | 2001-10-01 |
| 15 | Teaching Materials | 2001-10-01 |
| 16 | Teaching Materials | 2001-10-01 |
| 17 | Teaching Materials | 2001-10-01 |
| 18 | Teaching Materials | 2015-06-11 |
| 19 | Teaching Materials | 2015-06-11 |
| 20 | Snacks | 2015-06-23 |
| 21 | Snacks | 2015-06-23 |
| 23 | Books | 2012-01-02 |
| 24 | Books | 2012-01-02 |
| 25 | Books | 2012-01-02 |
| 26 | Drinks | 2015-06-23 |
| 27 | Drinks | 2015-06-23 |
+---------+-------+------------+
Rows in Set (0.00 sec)
---------------------------------

Mysql> SelectGtypeCOUNT(*) asTotal fromGoodsmessageWhereGdatebetween '2001-01-01'  and '2014-01-01' Group  byGtype havingTotal>= 2 Order  byTotalDESCLimit3;

Query output results:

+-------+-------+
| Gtype | Total |
+-------+-------+
| Teaching Materials | 11 |
| Books | 3 |
| Press | 2 |
+-------+-------+

================================

(Note: The above section is directly referenced from the "MySQL reference manual" translation version)

MySQL must know the query