MySQL query examples can be used to study MySQL and for the workshop in MySQL query writing.

To select from the database tables, use the SELECT statement.

Query Q001. Example of a MySQL query to retrieve only the required fields in the desired sequence:

SELECT dt, product_id, amount
FROM m_income;

Query Q002. In this MySQL query, the asterisk (*) is used to display all columns of the m_product table, in other words, to get all the fields of the relation m_product:

SELECT *
FROM m_product;

Query Q003. The DISTINCT statement is used to exclude duplicate records and retrieve a set of unique records:

SELECT DISTINCT product_id
FROM m_income;

Query Q004. The ORDER BY clause is used to sort (order) the records by the values of a particular field. The field name is indicated by the ORDER BY statement:

SELECT *
FROM m_income
ORDER BY price;

Query Q005. The ASC instruction is used as an addition to the ORDER BY clause and serves to determine the sorting in ascending order. The DESC instruction is used as a complement to the ORDER BY clause and serves to define sorting in descending order. In the case where neither ASC nor DESC is specified, ASC (default) is implied:

SELECT *
FROM m_income
ORDER BY dt DESC , price;

Query Q006. To select the necessary records from the table, use different logical expressions that express the selection condition. The logical expression is given after the WHERE clause. Example of obtaining from the m_income table of all records for which the amount is greater than 200:

SELECT *
FROM m_income
WHERE amount>200;

Query Q007. In order to express complex conditions, use logical operations AND (conjunction), OR (disjunction) and NOT (logical negation). An example of a MySQL query to retrieve all records from the m_outcome table for which the value amount is 20 and the price is greater than or equal to 10:

SELECT dt, product_id, amount, price
FROM m_outcome
WHERE amount=20 AND price>=10;

Query Q008. To combine the data of two or more tables, use the INNER JOIN, LEFT JOIN, RIGHT JOIN instructions. The following example retrieves the dt, product_id, amount, price fields from the m_income table and the title field from the m_product table. The record of the m_income table is connected to the record of the m_product table when the value m_income.product_id is equal to the value m_product.id:

SELECT dt, product_id, title, amount, price
FROM m_income INNER JOIN m_product 
ON m_income.product_id=m_product.id;

Query Q009. In this MySQL query example, you need to pay attention to two things: 1) the search text is enclosed in single quotes ( ' ); 2) the date is shown in the format "Year-Month-Day". In other systems, the format of the date spelling may be different. Example of output of information on milk intake on June 12, 2011. Note the date format "2011-6-12":

SELECT dt, product_id, title, amount, price
FROM m_income INNER JOIN m_product 
ON m_income.product_id=m_product.id
WHERE title='Молоко' And dt="2011-6-12";

Query Q010. The BETWEEN statement is used to check for membership in a range of values. A MySQL query that displays information about products received between the 1st and the 30th of June 2011:

SELECT *
FROM m_income INNER JOIN m_product 
ON m_income.product_id=m_product.id
WHERE dt BETWEEN "2011-6-1" And "2011-6-30";