20180813 MySQL data query

Data Query Language DQL

SELECT [All | distinct] field or expression list [from clause ] [where clause ] [group by clauses ] [having clause ] [ORDER by clause ] [limit clause ];

the operators available in Where:

arithmetic operators: +-*/%

comparison operators: > >= < <= =(equals) <>(not equal)

= =(equals,mysql extension),! =(not equal to,mysql extension)

logical operators: and (with) or(or) not (non)

between syntax:

XX between value 1 and value 2;

in syntax: xx in (1,2,3,4,5) xx not in () like syntax (Fuzzy Lookup):

Grammar form: xx like ' to find characters ';

Description

1, like syntax (Fuzzy Lookup) for character matching for a field of character type lookup

2, to find the characters, there are 2 Special meanings of the characters:

2.1:% It means: Any character that represents any number

2.2: _ The meaning is: any character representing 1 characters

2.3: The character here refers to a "symbol" that is visible in reality, not a byte.

3, the actual application of fuzzy search, usually is this: like '% keyword %';

GROUP BY group clauses

COUNT (*): counts the number in a group, usually with "*" Parameters

Max ( field name ): Gets the maximum value in the Group for this field.

Min ( field name ): Gets the minimum value in the Group for this field.

Sum ( field name ): Gets the sum of the fields in the group.

AVG ( field name ): Gets the average of the field in the group.

Group_concat ( field name ): Gets all the information for this field within the group, separated by commas

having clause

Select COUNT (*) as F1, Max (F1) as F2 from TAB1 Group by F3 have F1 > 5 and F2 < 1000;

Order by clause

Order by sort field 1 [ sort by], sort field 2 [ sort by ], .....

Description

The data obtained earlier (with the FROM clause,where clause,group clause, having clause to specify that the size of a field is arranged (sorted) in 2 ways:

Positive Order: ASC(default value), you can omit

Reverse: DESC

limit clause

Form:

Limit [ start line number start], number of rows to be fetched num

Sub-query

Treat the result of one query as a condition of another query

Connection Query Extensions to Columns

A two-column table plus a three-column table join query out a five-column table

Connection mode

The data volume of the left and right two tables is different, more or less on the same line how to display

Federated Queries extension to Rows

Two tables with the same field to isolate multiple data

20180813 MySQL data query