MySQL Memo point (top)

To see for themselves, so take the example of the main

Retrieving data

Select Retrieves a single-column SELECT name from student

Retrieving multiple columns SELECT No, name from student

Retrieve all Columns SELECT * from Student

Retrieving different rows Select DISTINCT name, sex from student DISTINCT all columns under the modified Select, that is, two data, name and sex as long as there is a different column, even if the different rows.

Limit results SELECT No from student limit 5 If the number of rows found is insufficient, then the full output　　　　　　　　

Fully qualified table name SELECT student.no from student

Sort data

Order BY column sort SELECT name from student the order by no columns that are not retrieved (No field) are also available for sorting

Sort by multiple columns SELECT * from student order by birthday The data will be sorted by sex only if the same data exists in the result set birthday　　　

Sort Direction SELECT * from Student order by birthday DESC from large to small sort, the common case is the most expensive, the date is the latest. 　　

Filtering data

Where single condition SELECT name from student WHERE no=10

Do not match SELECT name from Student WHERE No!=10

Range SELECT name from Student WHERE no between 1 and 3 are included, i.e. 1, 2, 3　　

Null value SELECT name from student WHERE sex is NULL

Data filtering

and multiple conditions SELECT name from student WHERE name= ' Deolin ' and Sex=1

OR multiple conditions SELECT name from student where Name= ' Deolin ' OR name= ' Bld ' generally coexist with and and or, with parentheses guaranteeing the order of calculation, rather than by Memory to rely on priority

In range SELECT name from student WHERE name in (' Deolin ', ' Bld ') in and or, preferred in, because faster, easier to manage, more intuitive

Not deny single condition SELECT name from student WHERE not name in (' Deolin1 ', ' Bld1 ')

wildcard characters for efficiency, not excessive use; try not to put wildcards at the beginning　　　　　　

Like% wildcard matches SELECT name from student WHERE name is like ' D%n ' any character any number of times, name is ' Dn ' ' Deolin ' ... The data is in the range; like '% ' does not match null

The like _ wildcard matches the SELECT name from student WHERE the name like ' Deol_n ' any character once, only 1 times, cannot be more or less

Regular expressions

Regexp

or match REGEXP ' 1000|2000 ' match 1000 or 2000

Character Matching REGEXP ' [Ab12]ton ' matches aton or Bton or 1ton or 2ton

Range Matching REGEXP ' [0-9] ' equals [0123456789]

REGEXP ' [3-6] ' equals [3456]

REGEXP ' [b-d] ' equals [BCD]

Escaped match REGEXP ' \\% ' match character%

metacharacters match REGEXP ' \\f ' \\f page feeds \\n Line feeds \\r carriage return \\t Tabulation \\v Vertical Tabulation

The character class matches REGEXP ' [: alnum:] ' equivalent to [a-za-z0-9], and all three have to have

REGEXP ' [: Alpha:] ' is equivalent to [a-za-z], and both have to have

REGEXP ' [:d igit:] ' equivalent to [0-9]

Multiple matches {n} specify number of matches

{N,} specifies that the number of matches is not less than n

{N,M} specified number of matches N to M

* 0 or more matches

+ equals {1,}

？ Equivalent to {0,1}

Locate Match ^ $ text start to end of text example: REGEXP ' 1000|2000 ' will match to h1000 or sd2000, but REGEXP ^ ' 1000|2000 ' $ will only match to 1000 and 20,002

Negative match [^] ^ is used to negate an expression in a set when it appears in a set (referred to as a bracket)

Calculated fields

CONCAT () field splicing SELECT CONCAT(' The name is ' +name+ '. ') From student

RTRIM () Remove right space SELECT RTRIM(name) from student LTRIM () left space, TRIM () spaces on both sides

As Alias SELECT (RTRIM (name) +sex) as name from student binding function and arithmetic operation between column and column

Function

Text Processing

Left ()/right () text character

Length () Text lengths

LOWER ()/UPPER () Convert to small/uppercase

When comparing dates and dates, use the date () Wrap field as much as possible, and the link symbol for the target value, although/and-can be, but as far as the field is consistent, use-

Date ()/time () take out the month/day/minute of the field

Year ()/month ()/day () Take out the field's annual/monthly/Daily

HOUR ()/MINUTE ()/SECOND () Take out the time/minute/sec of the field

Now () the date and time

DAYOFWEEK () weeks

Summarize data

If the aggregation function is select COUNT (no) as CNT, name from student, then although the CNT values are consistent, they will still be copied to each row, because the result set is "stretched" by the name field.

AVG () The average of a column in a clustered result set, the parameter is non-numeric and feasible, but will return strange results, AVG () ignores null value

Count () uses too much, the "aggregation" function can refer to the Count count () parameter is the column name, the null value in the column is ignored, the null value does not count into the total

Max () maximum value of a column in a clustered result set

MIN () The minimum value of a column in a clustered result set min () ignores null values

SUM () A column in a clustered result set, with arguments that are non-numeric and feasible, but return strange results

Grouped data

Group BY Create Group

Having filter group

Sub-query

In (SELECT ...) subquery as the value of the search criteria (the collection)

Select (SELECT ...) Subqueries as calculated values for retrieving items Note that the table names are fully qualified

Understanding Grouping

Creating groupings (group by)

Simply put, the result set is implicitly divided into a number of result sets, grouping is a result set;

Since the grouping is a "result set", the aggregation function in select calculates each grouping separately,

It can be thought that the purpose of grouping is to use aggregation function ;

　　Group by can be grouped with any number of column names, representing multiple columns;

Group by cannot follow the aggregation function;

A null value in a column is a special value that differs from other non-null values;

Filter grouping (having)

Very similar to where, where the grammar has almost full applicability;

Where filters for every data that does not meet the criteria in the entire result set,

Having filter is the entire result set that meets the criteria for each grouping

MySQL Memo point (top)