MySQL Reference Manual-common query example _ MySQL

MySQL Reference Manual-examples of common queries below are examples of 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 merchant. Assume that each merchant's article has a separate fixed price, then (item, Merchant) is the primary key of the record.
　　
You can create an example database table as follows:
Create table shop (
Article INT (4) unsigned zerofill default '100' 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 );
　　
Well, the example data is as follows:
　　
SELECT * FROM shop
　　
+ --------- + -------- + ------- +
| Article | dealer | price |
+ --------- + -------- + ------- +
| 0001 | A | 3.45 |
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | B | 1.45 |
| 1, 0003 | C | 1.69 |
| 1, 0003 | D | 1.25 |
| 1, 0004 | D | 19.95 |
+ --------- + -------- + ------- +
　　
Maximum value of column 3.1
"What is the largest item number ?"
　　
Select max (article) AS article FROM shop
　　
+ --------- +
| Article |
+ --------- +
| 4 |
+ --------- +
　　
3.2 rows with the maximum value of a column
"Find the number, merchant, and price of the most expensive article"
　　
In the ANSI-SQL this is easily done with a subquery:
　　
SELECT article, dealer, price
FROM shop
WHERE price = (select max (price) FROM shop)
　　
In MySQL (no subquery is available), perform the following two steps:
　　
Use a SELECT statement to obtain the maximum value from the table.
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 by Price in descending order and use the MySQL-specific LIMIT clause to obtain only the first row:
　　
SELECT article, dealer, price
FROM shop
Order by price DESC
LIMIT 1
　　
Note: If there are multiple Most expensive articles (for example, each 19.95), the LIMIT solution only shows one of them!
　　
Maximum value of column 3.3: by group: only values
"What is the highest price for each article ?"
　　
SELECT article, MAX (price) AS price
FROM shop
Group by article
　　
+ --------- + ------- +
| Article | price |
+ --------- + ------- +
| 0001/3.99 |
| 0002/10.99 |
| 0003/1.69 |
| 0004/19.95 |
+ --------- + ------- +
　　
3.4 rows with the maximum value of a field between groups
"Find the most expensive traders for each article ."
　　
In ansi SQL, I can use this subquery:
　　
SELECT article, dealer, price
FROM shop s1
WHERE price = (select max (s2.price)
FROM shop s2
WHERE s1.article = s2.article)
　　
In MySQL, it is best to do this in several steps:
　　
Get a table (article, maxprice ). See 3.4 rows with the maximum value between groups in a domain.
For each article, get the row corresponding to the maximum storage price.
This can be easily done using a temporary table:
　　
Create temporary table tmp (
Article INT (4) unsigned zerofill default '100' not null,
Price DOUBLE (16,2) DEFAULT '0. 00' not null );
　　
Lock tables article read;
　　
Insert into tmp SELECT article, MAX (price) FROM shop group by article;
　　
SELECT article, dealer, price FROM shop, tmp
WHERE shop. article = tmp. articel AND shop. price = tmp. price;
　　
Unlock tables;
　　
Drop table tmp;
　　
If you do not use a TEMPORARY table, you must also lock the "tmp" table.
　　
"Can it be done in a single query ?"
　　
Yes, but there is only one rather inefficient trick to use what I call "MAX-CONCAT tricks:
　　
SELECT article,
SUBSTRING (MAX (CONCAT (LPAD (price, 6, '0'), dealer), 7) AS dealer,
0.00 + LEFT (MAX (CONCAT (LPAD (price, 6, '0'), dealer), 6) AS price
FROM shop
Group by article;
　　
+ --------- + -------- + ------- +
| Article | dealer | price |
+ --------- + -------- + ------- +
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 1, 0003 | C | 1.69 |
| 1, 0004 | D | 19.95 |
+ --------- + -------- + ------- +
　　
The final example can certainly make it more effective by splitting the joined columns in the client program.
　　
3.5 Use foreign keys
No foreign keys are required to join two tables.
　　
The only thing that MySQL does not do is CHECK to ensure that the key you are using exists in the referenced table, and it does not automatically delete rows from a table with a foreign key definition. If you use your key value as usual, it will work well!
　　
Create table persons (
Id smallint unsigned not null AUTO_INCREMENT,
Name CHAR (60) not null,
Primary key (id)
);
　　
Create table shirts (
Id smallint unsigned not null AUTO_INCREMENT,
Style ENUM ('T-shirt ', 'Polo', 'dress') not null,
Color ENUM ('red', 'Blue ', 'Orange', 'White ', 'black') not null,
Owner smallint unsigned not null references persons,
Primary key (id)
);
　　
Insert into persons VALUES (NULL, 'Antonio Paz ');
　　
Insert into shirts VALUES
(NULL, 'Polo', 'Blue ', LAST_INSERT_ID ()),
(NULL, 'dress', 'white', LAST_INSERT_ID ()),
(NULL, 'T-shirt ', 'blue', LAST_INSERT_ID ());
　　
Insert into persons VALUES (NULL, 'lilliana Angelovska ');
　　
Insert into shirts VALUES
(NULL, 'dress', 'Orange ', LAST_INSERT_ID ()),
(NULL, 'Polo', 'red', LAST_INSERT_ID ()),
(NULL, 'dress', 'Blue ', LAST_INSERT_ID ()),
(NULL, 'T-shirt ', 'white', LAST_INSERT_ID ());
　　
SELECT * FROM persons;
+ ---- + --------------------- +
| Id | name |
+ ---- + --------------------- +
| 1 | Antonio Paz |
| 2 | Lilliana Angelovska |
+ ---- + --------------------- +
　　
SELECT * FROM shirts;
+ ---- + --------- + -------- + ------- +
| Id | style | color | owner |
+ ---- + --------- + -------- + ------- +
| 1 | polo | blue | 1 |
| 2 | dress | white | 1 |
| 3 | t-shirt | blue | 1 |
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
| 7 | t-shirt | white | 2 |
+ ---- + --------- + -------- + ------- +
　　
SELECT s. * FROM persons p, shirts s
WHERE p. name LIKE 'lilliana %'
AND s. owner = p. id
AND s. color <> 'white ';
　　
+ ---- + ------- + -------- + ------- +
| Id | style | color | owner |
+ ---- + ------- + -------- + ------- +
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
+ ---- + ------- + -------- + ------- +