This is the 8th day of my participation in the August More Text Challenge. For details, see:August is more challenging

📖 introduction

Encouragement is sometimes more important than stress!

In the process of learning, a lot of times we hear, you want to, you look at who, who, who, even if you don’t hear such a voice today, but because of the repeated hearing caused inner resistance. Although also know that they want to learn, but it is difficult to insist, learn to learn to have no direction, see there are so many will not panic, so that the final state of mind collapsed, more reluctant to learn. In fact, the pressure of programmers is not small, want to grow up is almost always need to learn, like it seems that no longer dare to say proficient in Java, the amount of knowledge is with the depth of learning, more and more deep, more and more wide. So need, happy learning, happy growth!

We all know that MySQL can be accessed via LIKE… % for fuzzy matching.

MySQLMatching other regular expressions is also supported,MySQLThe use ofREGEXPOperator to perform regular expression matching.

All of the following is related to previous posts and you can check out the blog post.Chen Yongjia’s blog

✨ expression

The purpose of regular expressions is to match text, comparing a pattern (regular expression) to a text string. MySQL to useWHEREClause provides preliminary support for regular expressions, allowing you to specify regular expressions, filtersSELECTRetrieved data.

The regular patterns in the following table apply toREGEXPIn the operator

model describe
^ Matches the starting position of the input string. If the Multiline property of the RegExp object is set, ^ also matches the position after ‘\n’ or ‘\r’.
$ Matches the end of the input string. If the Multiline property of the RegExp object is set, $also matches the position before ‘\n’ or ‘\r’.
. Matches any single character other than “\n”. To match any character including ‘\n’, use something like ‘[.\n]’
[…]. Set of characters. Matches any of the contained characters. For example, ‘[ABC]’ can match ‘a’ in ‘plain’.
[^…]. Negative character set. Matches any character that is not included. For example, ‘[^ ABC]’ can match ‘p’ in ‘plain’.
p1 p2 p3 Matches P1 or P2 or P3. For example, ‘z
  • | match zero or more child in front of expression. For example, zo* matches both “z” and “zoo”. * is equivalent to {0,}.

+ | matching the sub-expression in front of one or more times. For example, ‘zo+’ matches “zo” and “zoo”, but not “z”. + is equivalent to {1,}. {n} | n is a non-negative integer. Matches the specified n times. For example, ‘o{2}’ can’t match the ‘o’ in ‘Bob’, but it can match the two o’s in ‘food’. {n, m} | m and n are nonnegative integers, where n < = m. At least n times and at most m times are matched.

Basic character matching:

REGEXP keyword to match the re

SELECT name FROM tab1 WHERE name REGEXP ‘abc’; // Whatever follows the REGEXP is used as the regular expression

  • ‘.’ matches any character
  • ‘a | b can match a or two
  • ‘[ABC]’ matches a character in ABC
  • ‘[^a]’ matches data other than a
  • ‘[1-9]’ matches any number from 1 to 9. [a-z] and [a-z] are the same as [1-9].
  • “Is the equivalent of an escape character
  • SELECT name FROM tab1 WHERE name REGEXP ‘a|b’;

Match character class:

  • [:alnum:] Any letter or number (same as [a-za-z0-9])
  • [:alpha:] Any character (same as [a-za-z])
  • [:blank:] Spaces and tabs (same as [\t])
  • [: CNTRL :] ASCII control characters (ASCII 0 through 31 and 127)
  • [:digit:] Any digit (same as [0-9])
  • [:graph:] is the same as [:print:], but does not include Spaces
  • [:lower:] Any lowercase letter (same as [a-z])
  • [:print:] Any character that can be printed
  • Any character in [:punct:] that is neither in [:alnum:] nor in [: CNTRL :]
  • [:space:] Any whitespace character, including Spaces (same as [\f\n\r\t\v])
  • [:upper:] Any uppercase letter (same as [A-Z])
  • [:xdigit:] Any hexadecimal digit (same as [A-FA-f0-9])

To match multiple characters:

0 or more matches 1 or more matches (equal to {1,})? 0 or 1 matches (equal to {0,1}) {n} Specifies the number of matches {n,} specifies the number of matches {n,} specifies the number of matches {n,} specifies the number of matches {n,} specifies the number of matches {n,m} specifies the number of matches (m no more than 255). FROM table_name WHERE table_name REGEXP 'a*'; Matches 0 and multiple asCopy the code

Locator:

  • ‘^’ matches the beginning of the text
  • ‘$’ matches the end of the text
  • [[:<:]] The start of the matching word
  • [[:>:]] The end of the matching word

Escape with \ to match special characters:

  • \. Can match.
  • \ f page breaks
  • \ n line
  • Enter \ r
  • \ t TAB
  • \ Vertical tabulating
  • Note: To match \ itself, \\ needs to be used

Example:

The query finds all names beginning with ‘st’

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

The query finds all names ending in ‘OK’

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

SELECT name FROM person_tbl WHERE name REGEXP ‘mar’; mysql> SELECT name FROM person_tbl WHERE name REGEXP ‘mar’;

Query to find all the name begins with a vowel sound and the end of the ‘ok’ mysql > SELECT the name FROM person_tbl WHERE the name REGEXP ‘^ [aeiou] | ok $’;

The following reserved words can be used in a regular expression

^

The string that matches begins with \

mysql> select “fonfo” REGEXP “^fo$”; -> 0; mysql> select “fofo” REGEXP “^fo”; -> 1 (matching)

$

Mysql > select “fono” REGEXP “^fono$”; -> 1 mysql> select “fono” REGEXP “^fo$”; -> 0 (no match)

.

Mysql > select “fofo” REGEXP “^f.*”; -> 1 mysql> select “fonfo” REGEXP “^f.*”; -> 1 (matching)

a*

Mysql > select “Ban” REGEXP “^Ba*n”; -> 1 mysql> select “Baaan” REGEXP “^Ba*n”; -> 1 mysql> select “Bn” REGEXP “^Ba*n”; -> 1 (matching)

a+

\ mysql> select “Ban” REGEXP “^Ba+n”; -> 1 mysql> select “Bn” REGEXP “^Ba+n”; -> 0 (no match)

a?

A \ mysql> select “Bn” REGEXP “^Ba? n”; -> 1 mysql> select “Ban” REGEXP ^Ba? n”; -> 1 mysql> select “Baan” REGEXP ^Ba? n”; -> 0 (no match)

de|abc

Matching DE or ABC mysql > select “PI” REGEXP “PI | apa”; – > (1) means to match to mysql > select “axe” REGEXP “PI | apa”; – > 0 (said don’t match) mysql > select “apa” REGEXP “PI | apa”; – > (1) means to match to mysql > select “apa” REGEXP “^ (PI | apa) $”; – > > (1) means to match to mysql > select “PI” REGEXP “^ (PI | apa) $”; – > (1) means to match to mysql > select “pix REGEXP” ^ “$” (PI | apa); -> 0 (no match)

(abc)*

Mysql > select “PI” REGEXP “^(PI)*$”; mysql> select “PI” REGEXP “^(PI)*$”; -> 1 mysql> select “PIP” REGEXP “^(PI)*$”; -> 0; mysql> select “pipi” REGEXP “^(PI)*$”; -> 1 (matching)

[a-dX]

Match “a”, “b”, “c”, “d” or “X”

[^a-dX]

Matches any character except “a”, “b”, “c”, “d”, “X”.

\ mysql> select “aXbc” REGEXP “[a-dxyz]”; -> 1 mysql> select “aXbc” REGEXP”^[a-dxyz]$”; -> 0; mysql> select “aXbc” REGEXP “^[a-dxyz]+$”; -> 1 mysql> select “aXbc” REGEXP “^[^ a-dxyz]+$”; -> 0; mysql> select “gheis” REGEXP “^[^ a-dxyz]+$”; -> 1 mysql> select “gheisa” REGEXP “^[^ a-dxyz]+$”; -> 0 (no match)

🚀 Data Filtering

The WHERE clause

When we use a database, we usually only extract a subset of table data based on specific conditions. To retrieve only the required data, you need to specify search criteria, which are also called filterconditions.

SELECT name, age FROM sys_user WHERE age=18; Return rows with age=18Copy the code
Operators supported by the WHERE clause
= equal <> not equal! = No < less than <= less than or equal to > = greater than or equal to BETWEEN specified values eg: // Obtain the data whose age is greater than 20 SELECT name,age FROM sys_user WHERE age>20; SELECT name,age FROM sys_user WHERE age BETWEEN 12 and 18; SELECT name FROM sys_user WHERE age BETWEEN 12 and 18;Copy the code
Another special clause
SELECT name, age FROM sys_user WHERE name IS NULL;Copy the code
Comparison operator
combinationWHEREclause
SELECT * FROM sys_user WHERE age=20 AND name='cyj'; SELECT * FROM sys_user WHERE age=20 AND name='cyj'; SELECT name,age FROM sys_user WHERE age=20 OR age=50; "AND" AND "OR" can be used together. However, "AND" has a higher priority than "OR", so the AND operator is processed first. In addition, you can use parentheses to show the priority.Copy the code
INThe operator

Specifies a range of conditions, each of which can be matched. IN a comma-separated list of legal values, all enclosed IN parentheses.

// query age at 18,19 or 20…… The function is similar to that of OR. SELECT name,age FROM sys_user WHERE age IN (18,19,20,21,22,23);

Some advantages of IN over OR:

1) The IN operator's syntax is clearer and more intuitive when using a long list of legal options. 2) The order of calculations is easier to manage when using IN, 3) the IN operator is generally faster to execute than the OR operator list, and 4) IN can contain other SELECT statements, making it possible to create a WHERE clause more dynamicallyCopy the code
NOTThe operator

Any condition written after negating it.

// query all data whose age is not 18,19 OR 20. SELECT name,age FROM sys_user WHERE age NOT IN (18,19,20);Copy the code
BETWEENWith the date type (NOT BETWEENOn the contrary)

For the best results when using the BETWEEN operator with a date-type value, you should use a type conversion to explicitly convert the type of a column or expression to DATE.

You need to query for all orders with required dates from 2013-01-01 to 2013-01-31, but it is important to note here that you cannot query for the date of 2019-05-11.

SELECT orderNumber,
         requiredDate,
         status
FROM jmccsm_orders
WHERE requireddate
    BETWEEN CAST('2019-05-01' AS DATE)
        AND CAST('2019-5-11' AS DATE);
Copy the code

Want to know how to do, please continue to pay attention to the blogger’s blog (foreground, background, SQL can be processed oh).

LIMIT

Use the LIMIT clause in the SELECT statement to restrict the number of rows in the result set. The LIMIT clause takes one or two arguments. The values of both arguments must be zero or positive integers.

Grammar:

🐱🏍 Wildcard filtering is used

The LIKE operator (as opposed to NOT LIKE)
% wildcards
SELECT name FROM sys_user WHERE name LINK 'cyj%'; SELECT name FROM sys_user WHERE name LINK 'cyj%';Copy the code
The wildcard

Underscore (_). The underscore matches only a single character and is used in the same way as %

Pay attention to
Don't overuse wildcards. Other operators should be used if they can achieve the same purpose. Try not to use them at the beginning of the search pattern, as they can be very slowCopy the code

🎉 finally

  • For more references, see here:Chen Yongjia’s blog
  • Like the little friend of the blogger can add a concern, point a like oh, continue to update hey hey!