Using NULL
Conceptually, NULL is a missing unknown value that is treated differently from other values.
Using IS NULL, IS NOT NULL operators, as follows:
mysql> SELECT 1 IS NULL, 1 IS NOT NULL; +-----------+---------------+ | 1 IS NULL | 1 IS NOT NULL | +-----------+---------------+ | 0 | 1 | +-----------+---------------+
Because 1 is a number, or an integer value, so for a specific integer value, it is not NULL, so, 1 IS NULL is false, so it shows 0, and 1 IS NOT NULL, it means true, returns 1.
For NULL, you can't use arithmetic operators, such as =,<>,<,>, etc., as follows:
mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL; +----------+-----------+----------+----------+ | 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL | +----------+-----------+----------+----------+ | NULL | NULL | NULL | NULL | +----------+-----------+----------+----------+
In MySQL, 0 or NULL denotes false, any other value denotes true, and the default true value is 1.
Matching patterns
MySQL provides a standard sql pattern matching tool, just like the Unix tool (such as vi, grep, sed) based on extended regular expressions.
sql pattern matching can use "" to match any single character, and "%" can be used to match any number of characters (including 0 characters). In MySQL, the case of sql pattern matching is not sensitive by default. Here are some examples. When you use sql pattern, don't use = or <>, but use LIKE or NOT LIKE.
To find a name that begins with "b":
mysql> SELECT * FROM PET WHERE name LIKE 'b%'; +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+
Find a name that ends with'fy':
mysql> SELECT * FROM PET WHERE name LIKE '%fy'; +--------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +--------+--------+---------+------+------------+-------+
Find the name that contains the character "w":
mysql> SELECT * FROM PET WHERE name LIKE '%w%'; +----------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+------------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | +----------+-------+---------+------+------------+------------+
Use five "" pattern characters to find names that contain five characters:
mysql> SELECT * FROM PET WHERE name LIKE '_____'; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+
Other pattern matching types provided by MySQL use extended regular expressions. When you use this type to test a match, use REGEXP and NOT REGEXP operations (or RLIKE and NOT RLINKE, which are synonyms).
The following list describes the characteristics of some extended expressions:
"." Matches any single character.
A character class "[...]" matches any character in parentheses, such as "[abc]" matches "a","b" or "c". To specify a character range, you can use "-", for example, "[a-z]" matches any letter, and "[0-9]" matches any number.
"*" Matches 0 or more things in front of it. For example, "x*" matches any x character, "[0-9]*" matches any number, and ". *" matches any character.
A successful REGEXP pattern matching condition is that the pattern matches anywhere in the test value. (Unlike LIKE pattern matching, successful LIKE pattern matching requires matching the entire value).
To start or end a pattern matching test value, you can use "^" as the beginning of the pattern or "$" as the end of the pattern.
Verify regular expressions
Use "^" to match and find the name beginning with "b"
mysql> SELECT * FROM pet WHERE name REGEXP '^b'; +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+
Regular expressions are case-sensitive
mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';
To find a name that ends 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 find a name that contains a "w" name, use this query:
mysql> SELECT * FROM pet WHERE name REGEXP 'w'; +----------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+------------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | +----------+-------+---------+------+------------+------------+
To find a name that contains only five characters, use "^" and "$" to match the beginning and end of the name, and place five "." in the middle. As follows:
mysql> SELECT * FROM pet WHERE name REGEXP '^.....$'; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+
You can also use the {n} (repeat n times) operator to write the previous query:
mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+