[MySQL database development 4] MySQL processing mode/common query/mode matching, etc.

The articles on this site are original by Li huaming Himi. The original Article must be clearly stated: Reprinted from [Black Rice GameDev block] original link: www.himigame.commysql781.html click to subscribe to this blog ..

This site articles are Li huaming Himi original, reprint must note in the obvious: Reprinted from [Black Rice GameDev block] original link: http://www.himigame.com/mysql/781.html click to subscribe to this blog ..

Reprinted from the original article link of [Heimi GameDev block:

☞Click to subscribe☜The latest developments in this blog! Notify you of the latest blog in time!

Continue with the previous article to explain MySQL related knowledge points;

1. NULL value operation:

The NULL value may be strange until you get used to it. In concept, NULL means "no value" or "unknown value", and it is regarded as a different value. To test NULL, you cannot use arithmetic comparison operators such as =, <或!=。为了说明它，试试下列查询：< p>

Obviously, you cannot get meaningful results through these comparisons. Instead, use the is null and is not null operators:

Note: you can insert 0 or an empty string in the column defined as not null. The server space is actually not null.

2. Pattern Matching:

MySQL provides standard SQL mode matching and an extended regular expression mode matching format based on Unix utilities such as vi, grep, and sed. SQL mode matching allows you to use "_" to match any single character, while "%" matches any number of characters (including zero characters ). Note that = or! =; Instead, use the LIKE or not like comparison operator;

Find all names starting with y;

Find all names ending with I;

Find all the names containing h;

To find the name that exactly contains three characters, use the three "_" pattern characters:

Other types of pattern matching provided by MySQL use extended regular expressions. When you perform a match test on this type of pattern, use the REGEXP and not regexp operators (or RLIKE and not rlike, which are synonyms ).

3. Use of regular expressions;

Some Characters of the extended regular expression are:

· '.' Matches any single character.

· The character class "[...]" matches any character in square brackets. For example, "[abc]" matches "a", "B", or "c ". To name the character range, use a hyphen (-). "[A-z]" matches any letter, and "[0-9]" matches any number.

· "*" Matches zero or multiple characters before it. For example, "x *" matches any number of "x" characters, "[0-9] *" matches any number, and ". * "matches any number of characters.

If the REGEXP mode matches any part of the tested value and the Hong Kong virtual host, the mode matches (unlike the LIKE mode match, the mode matches only the whole value ).

To locate a pattern so that it must match the start or end of the tested value, use "^" at the start of the pattern or "$" at the end of the pattern ".

To demonstrate how the extended regular expression works, use REGEXP to rewrite the LIKE Query shown above:

To find the name starting with "B", use "^" to match the start of the name:

Mysql> SELECT * FROM pet WHERE name REGEXP '^ B '; + -------- + --------- + ------ + ------------ + | name | owner | species | sex | birth | death | + -------- + --------- + ------ + ------------ + | buffy | Harold | dog | f | 1989-05-13 | NULL | bow.| Diane | dog | m | 1989-08-31 | 1995-07-29 | + -------- + --------- + ------ + ------------ +

If you want to force REGEXP to be case sensitive, use the BINARY keyword to convert a string to a BINARY string. This query only matches the lowercase 'B' of the first letter of the name '.

Mysql> SELECT * FROM pet WHERE name regexp binary '^ B ';

To find the name ending with "fy", use "$" to match the end Of the name:

Mysql> SELECT * FROM pet WHERE name REGEXP 'fy $ '; + -------- + --------- + ------ + ------------ + ------- + | name | owner | species | sex | birth | death | + -------- + --------- + ------ + ------------ + ------- + | fluffy | Harold | cat | f | 1993-02-04 | NULL | Buffy | Harold | dog | f | 1989-05-13 | NULL | + -------- + --------- + ------ + ------------ + ------- +

To locate the name containing "w", use the following query:

Mysql> SELECT * FROM pet WHERE name REGEXP 'W '; + ---------- + ------- + --------- + ------ + ------------ + | name | owner | species | sex | birth | death | + ---------- + ------- + --------- + ------ + ------------ + | claws | Gwen | cat | m | 1994-03-17 | NULL | bow.| Diane | dog | m | 1989-08-31 | 1995-07-29 | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | + ---------- + ------- + --------- + ------ + ------------ +

Since a regular expression appears anywhere in the value, its pattern matches, you do not have to place a wildcard on both sides of the pattern in the previous query so that it matches the entire value, U.S. space, just as if you were using an SQL mode.