(8): mysql Query operation and regular expression summary _ MySQL

(8): mysql Query operation and regular expression summary regular expression

BitsCN.com

What should I do? Text edited with markdown cannot be used in blog parks, and I don't know how to handle it.

I. sorting

1. Sort by multiple columns
Separated by commas (,). if the direction is specified, the column name is next to the column name to be sorted.
For the sorting of multiple columns, first sort by the first column and then sort by the last column.
For example:

SELECT * FROM a2 ORDER BY a_id DESC,t_id desc

The data result is as follows:

2,order byAndlimit

SELECT * FROM a2 ORDER BY a_id DESC,t_id desc LIMIT 1

Note:
order byLocation,fromAfter,limitBefore.

2. data filtering operator (and/or/in/not)

Note: Priority
WhenandAndor,andThe priority is higherorSo executeandThe solution is to use()Brackets have a higher priorityandTo eliminate ambiguity.
For example:

SELECT * FROM a2 WHERE (a_id = 2 or t_id>4) AND id>3

inOperator features
inThe operator completesorThe same functions, such:

SELECT * FROM a2 WHERE t_id in(1,2,3)SELECT * FROM a2 WHERE t_id =1 OR t_id=2 OR t_id=3

The above two functions are the same.
In this case, why?inNext, let's talk aboutinAdvantages:

inAdvantages:
1. when using a long list of valid options,INOperator clearer and more intuitive
2. easy management of computing order
3. ratioORFast execution
4.INThe biggest advantage is that other select statements can be used to dynamically create a Where clause.

SELECT * FROM a2 WHERE t_id in(    SELECT t_id FROM a2 WHERE id=5);

NOT inverse
NOTPairIN,BETWEEN,existsClause.

3. Data filter wildcard

% Wildcard

%: Indicates the number of times any character appears

LIKE 'HP % ': starting with 'HP' LIKE '% HP': ending with 'HP 'LIKE' % HP % ': including 'HP' LIKE's % E ': starts with s and ends with e.

Note:

1. in addition to one or more characters, % can match 0 characters.
2. trailing spaces may interfere with Wildcard Matching. for example, if there is one or more spaces after a great is saved%greatWill not match them, because there are extra characters (spaces) behind them, the solution is to use double %,%great%A better way is to use the function to remove spaces at the beginning and end.
3.% does not matchNULL.

_ Wildcard
The underline wildcard is the same as %, but it only matches a single character, not multiple.

SELECT * FROM a WHERE name LIKE '_ood'  #goodSELECT * FROM a WHERE name LIKE 'goo_'  #goodSELECT * FROM a WHERE name LIKE 'go_d'  #good

Optimization:
Wildcards are slower in search processing than others, so remember the following tips:

1. do not over-use wildcards
2. do not use them at the beginning of the search mode. Otherwise, the search will be slow.
3. pay attention to the location of the wildcard. do not make any mistakes.

4. use regular expressions

This section describes howWhereClause uses regular expressions to better control data filtering.
For more details, refer to required regular expressions.
1. matching of basic characters

SELECT * FROM a1 WHERE name regexp '000000' # match all rows whose names contain 1000 SELECT * FROM a1 WHERE name regexp '. 000 '# match all rows ending with 000 ,(. regular expression: match any character)

We can see that the regular expression can simulate the use of wildcard characters LIKE,Note:Regular expressions are not needed when wildcards are completed, because regular expressions may be slow. of course, regular expressions can also simplify wildcards to play a greater role. So you have to make a choice.

LIKEAndREGEXPDifferences:

 SELECT * FROM a1 WHERE name LIKE 'a' SELECT * FROM a1 WHERE name regexp 'a' 

The first and second statements in the following two statements do not return data.
The reason is as follows:

LIKEWhen the entire column value is matched, it is not found, and the corresponding row is not returned (unless wildcards are used)
REGEXPThe system automatically finds and returns the matching results.
SoREGEXPThe entire column value can also be matched.^And$Positioning character!

Case insensitive

Mysql will match the regular expression in case sensitivity.binaryKeywords, such:

SELECT * FROM a1 WHERE name LIKE binary '% J %' # Use LIKE + wildcard to match uppercase JSELECT * FROM a1 WHERE name regexp binary 'j' # use regular expression to match lowercase j

2. proceedORMatch

|For a regular expressionOROperator, indicating matching one of them

SELECT * FROM a1 WHERE name regexp binary 'a|j|G' 

3. match specific characters

Use[]To match any single character.

SELECT * FROM a1 WHERE name regexp '[12]st'

The above '[12] St' regular expression, [12] defines a group of characters, which means matching 1 or 2, so the result is as follows:

As you can see,[]Is anotherORStatement. [123] st can be the abbreviation of [1 | 2 | 3] st, or the latter can be used,Note:1|2|3 stThis is not recommended because mysql will assume that you mean to match '1', '2', or '3st' unless you set the character|Including in a collection, for example:[1|2|3]st
The characters can also be negative.^This means that, for example[^123]stIt means matching data except 1st, 2st, and 3st.

4. matching Range

Regular expressions can be used-Match a range, such[0-9]Match any number, whether it is 1, 11, or 10111,[a-z]Match any lowercase letter.

5. match special characters
As shown above,./-/[]Is a special character of a regular expression. if you want to match data containing these characters, you need to use an escape (escaping ),//. For example//.Indicates searching '.'.//It is also used to reference metacharacters (characters with special meanings), such:
//f: Indicates page feed
//n: Line feed
//r: Enter
//t: Tabulation
//v: Vertical Tabulation
Notes:

If the backslash itself () is matched, use///
Why does Mysql use two backslash (/), while many languages use one backslash escape, because mysql interprets one by itself, and the regular expression library interprets one.

6. Match strings

BitsCN.com