Mysql SQL Statement Review 1. Mysql SQL Statement Review

Mysql SQL Statement Review 1. Mysql SQL Statement Review

Search the list without the same value and add the DISTINCT keyword.

Select distinct vend_id FROM products;

You can use the LIMIT clause to LIMIT the number of returned results.

SELECT prod_name FROM products LIMIT 5;

The LIMIT clause also has the offset function, which is to set the number of data records to be obtained.

SELECT prod_name FROM products LIMIT 5;

Five pieces of data starting from the fifth data

Although LIMIT has the offset function, Mysql still provides the OFFSET clause.

SELECT prod_name FROM products LIMIT 5 OFFSET 5;

The index value of mysql starts from 0.

You can use the order by clause to sort data. This clause takes the names of one or more columns and sorts the output accordingly.

SELECT prod_name FORM products order by prod_name;

This clause sorts data with non-retrieved columns.

The order by clause can be ordered BY multiple columns separated BY commas.

The order by clause must be replaced BY the WHERE clause.

The order by clause can specify the direction of sorting (ascending and descending). The default value is ascending. To change to descending, add DESC at the end of the order by clause.

The DESC keyword is only applied to the column name directly located in front of it. You can use the following query method:

SELECT prod_name, prod_price, prod_id FROM products order by prod_name DESC, prod_name;

The operator symbol in the WHERE clause is equal. Use = to judge not equal. Use the <> symbol, which is slightly different from that in programming languages.

Use the BETWEEN keyword to check the range value.

SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;

Use is null for NULL check

SELECT prod_name FROM products WHERE prod_price IS NULL

This statement returns product data with no price

Mysql contains the following logical operators:

AND OR IN NOT

SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1003 AND prod_price <= 10; intersection

SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003; get Union

SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002,1003) order by prod_name; intersection

SELECT prod_name, prod_price FROM products WHERE vend_id not in (1002,1003) order by prod_name; obtain the Union set

You can use parentheses to mark the order of calculation.

% Wildcard indicates any character order. Use the LIKE Operator

SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'Jet % ';

This query statement queries all rows whose prod_name field starts with jet.

_ Wildcard matching a single arbitrary character

SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil ';

This query statement queries all rows that end with ton andevil and have only one character before it

Do not over-use wildcards. If other operations can achieve the same purpose, use other operators.

When you do need wildcards, unless absolutely necessary, do not use them at the beginning of the search mode, which is the slowest.

Are mysql statements the same as SQL statements?

SQL statements are similar,
It's just a small difference between databases.
As long as there is one, the other will also

A one-to-many mysql SQL statement.

Select *
From m_tb
Left join
(
Select id, title, mid, max (times) as last time
From m_vs
Group by mid
) On (m_tb.id = m_vs.mid)