Summary of MySQL SELECT statement

Single-Table query:

Query specified record select .... where

Query with in keyword not in

Range query with between and

Select Column1,column2 from table1 where column between 3 and 14;

Query with like% matches characters of any length

Query the name of the fruit that starts with B and ends with Y

Select F_name from fruit where f_name like ' b%y ';

Underline ' _ ', can only match any one character at a time.

Query null value: is null/is NOT null

SELECT * from where c_email is null;

Multi-criteria query with and

Select f_id, f_price from fruits where s_id = ' 101 ' and F_price >= 5;

A multi-conditional query with or, the OR and in operations achieve the same effect. But in more concise

Query results are not duplicated:

Select distinct s_id from fruits;

Sort:

ORDER BY id desc (Descending) default ASC (Ascending)

If the order by is followed by more than one field, the first one is sorted, if the first compares equal, in the order of the following;

Group query:

Group BY column have < conditional expressions >

This is often used with aggregation functions with Max () min () count () Avg () sum ()

Select s_id, COUNT (*) as total from Fruits Group by s_id;

The above query the name of the fruit supplied by the ID supplier;

Select s_id, Group_concat (f_name) as names from fruit Group by s_id;

Group_concat () Displays the name of each group

Having filter group

Select s_id, Group_concat (f_name) as names from Fruits Group by s_id have count (f_name) > 1;

Using the WITH rollup in the GROUP BY clause

Add a row to show the sum of the fields

Multi-field grouping is also possible

SELECT * from Fruit Group by s_id, F_name;

Limit () Limits query results

Connection query:

Internal connection: (inner join)

Select suppliers.s_id, S_name, f_price from fruit, suppliers

where fruits.s_id = Suppliers.s_d;

Internal connection Query statement:

Select ... form fruits INNER JOIN suppliers on fruit.s_id = suppliers.s_id;

Summary of MySQL SELECT statement