MySQL getting started

This article will introduce you to MySQL common query statements, including statements with maximum values of a column, rows with maximum values of a column, and user variables.

Example database:

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

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

1. Maximum column values

What is the largest item number?

Select max (article) FROM shop;

2. Rows with the maximum value of a column

Find the number, seller, and price of the most expensive item?

SELECT article, dealer, price FROM shop WHERE price = (select max (price) FROM shop ); another solution is to sort all rows by price in descending order and use the MySQL specific LIMIT clause to get only the first row:

SELECT article, dealer, price FROM shop order by price desc limit 1; 3. Maximum Value of the column: What is the maximum price of each item in a group? 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 |
+ --------- + ------- +
4. For each item in the row with the maximum value between groups with a field, find the dealer of the most expensive item? SELECT article, dealer, price
FROM shop s1
WHERE price = (select max (s2.price)
FROM shop s2
WHERE s1.article = s2.article );
5. Use user variables to find the items with the highest or lowest price?

Mysql> SELECT @ min_price: = MIN (price), @ max_price: = MAX (price) FROM shop;
Mysql> SELECT * FROM shop WHERE price = @ min_price OR price = @ max_price;
+ --------- + -------- + ------- +
| Article | dealer | price |
+ --------- + -------- + ------- +
| 1, 0003 | D | 1.25 |
| 1, 0004 | D | 19.95 |
+ --------- + -------- + ------- +
6. Search for two keywords combined by OR based on the two keys:

SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1' OR field2_index = '1'

You can also use UNION to combine the output of two separate SELECT statements:

SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1' UNIONSELECT field1_index, field2_index FROM test_table WHERE field2_index = '1 ';