Select Statement of mariadb and mariadbselect statement

Select Statement of mariadb and mariadbselect statement

Mariadb query Flowchart

 

Clause analysis sequence of select statements: from (filter table) --> where (filter row) --> group by (group) --> having (group filter) --> order by (SORT) --

> Select (select field) --> limit (query limit) --> final result

 

DISTINCT: deduplication

SQL _CACHE: explicitly specifies that the query results are stored in the cache.

SQL _NO_CACHE: explicit query results are not cached

Show global variables like '% query % ';

Query_cache_type | ON indicates that the cache is enabled.

Query_cache_size | 0 indicates the cache space size. If it is 0, it is not cached.

When query_cache_type is set to 'Demo', SELECT statements explicitly specified for SQL _CACHE are cached.

The cache does not cache all query results, for example, select now ();

 

WHERE clause: Specifies filtering conditions for "selection ".

Arithmetic Operators: +,-, *,/, %

Comparison OPERATOR: = ,! =, <>, <=>, >,>=, <, <=

BETWEEN min_num AND max_num

IN (element1, element2 ,...)

IS NULL

IS NOT NULL

LIKE:

%: Any character of any length

_: Any single character

RLIKE

Logical operators: NOT, AND, OR

GROUP: groups query results based on specified conditions for "aggregation" operations: avg (), max (), min (), count (), sum ()

HAVING: specify a filtering condition for the results after grouping aggregation.

Order by: Sort query results based on specified fields: ASC in ascending ORDER: DESC

LIMIT [[offset,] row_count]: LIMIT the number of output rows for the query results

 

For example:

Select name, age from students where age/2 = 11;

Select name, age from students where age + 30> 50;

 

Select distinct gender from students;

 

 

Select name as stuname from students;

 

 

Select name, classid from students where classid is null;

 

Select avg (age), gender from students group by gender;

 

 

Select avg (age) as ages, gender from students group by gender having ages> 20;

 

 

Select count (stuid) as NO, classid from students group by classid;

 

Select count (stuid) as NO, classid from students group by classid having NO> 2;

 

Select name, age from students order by age limit 10, 10; (the first one indicates 10 offsets, and the second one indicates 10 offsets)

 

Multi-Table query:

Crossover: Cartesian Product (a query that consumes the most resources), for example, select * from students and teachers. If students has 20 rows and teachers has 20 rows, 400 rows are displayed.

Internal Connection:

Equijoin: allows the fields between tables to establish a connection relationship with the "equivalence;

Non-equivalent connection

Natural connection

Self-connection

External Connection:

Outer left join example: FROM tb1 left join tb2 ON tb1.col = tb2.col

Outer right join example: FROM tb1 right join tb2 ON tb1.col = tb2.col

 

Equivalent connection:

 

 

Select s. name as studentname, t. name as teachername from students as s, teachers as t where s. teacherid = t. tid; (this is complex enough)

 

 

Select s. name, c. class from students as s, classes as c where s. classid = c. classid;

 

 

Left Outer Join:

Select s. name, c. class from students as s left join classes as c on s. classid = c. classid;

 

Use the left table as the reference

 

Select s. name, c. class from students as s right join classes as c on s. classid = c. classid;

Base on the right table

 

Subquery: the query statement is nested in the query statement, and the query result is re-queried Based on the query result of a statement (mariadb is not optimized enough for subquery. We recommend that you do not use it in general)

Select name, age from students where age> (select avg (age) from students );

Select name, age from students where age in (select age from teachers );

 

Select s. ages, s. classid from (select avg (age) as ages, classid from students where classid is not null group by classid) as s where s. ages> 30;

 

Joint query: UNION

Select name, age from students union select name, age from teachers;