MySQL Learning note 006

There is no previously review

Null value operation

Null is a special value that indicates "no value" or "Unknown value", remembering that it is a different value.

In order to test null, you cannot use arithmetic to compare operators don't believe you try

The operation is as follows:

SELECT 1 = null, 1 <> NULL, 1 < NULL, 1 > NULL;

mysql> SELECT 1 = null, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
| NULL | NULL | NULL | NULL |
+----------+-----------+----------+----------+
1 row in Set (0.00 sec)

Mysql>

I said no , you try.

So what's the way to try it? (Doing things)

The gods of ancient times had left these two operators in the Earth before heaven to be used by all, and they were:

Is null and is not NULL for these two operators.

The operation is as follows:

SELECT 1 is null, 1 are NOT null;

mysql> SELECT 1 is null, 1 are NOT null;
+-----------+---------------+
| 1 is NULL | 1 is not NULL |
+-----------+---------------+
| 0 | 1 |
+-----------+---------------+
1 row in Set (0.08 sec)

Mysql>

Warm tips (The book is also blind to worry about): in GROUP by two null values are considered the same (I have not said group by yet).

When you execute an order BY, run the order by ... ASC (Ascending), the null value appears at the top, if you run the order by ... DESC, the null value appears in the last face.

mysql> SELECT 0 is null, 0 are NOT NULL, "is NULL," is not null;
+-----------+---------------+------------+----------------+
| 0 is NULL | 0 is not NULL | ' Is NULL | ' is not NULL |
+-----------+---------------+------------+----------------+
| 0 | 1 | 0 | 1 |
+-----------+---------------+------------+----------------+
1 row in Set (0.00 sec)

Mysql>

Pattern matching

Note: When using SQL pattern matching, you cannot use = and! = instead you should use a like or not-like comparison operator. and ignore the big Little write.

SQL pattern matching allows you to use "_" to match any single character, while "%" matches any number of characters (including 0 characters).

Give me a chestnut: I want to find a name that starts with ' B '.

The operation is as follows:

SELECT * FROM pet WHERE name like ' 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 |
+--------+--------+---------+------+------------+------------+
2 rows in Set (0.11 sec)

Mysql>

Give me another chestnut: I want to find the name ending with ' fy '.

The operation is as follows:

SELECT * FROM pet WHERE name like '%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 |
+--------+--------+---------+------+------------+-------+
2 rows in Set (0.00 sec)

Mysql>

Then a chestnut: I want to find out the name contains ' W '. (Dynamic search (Seniors told me))

The operation is as follows:

SELECT * FROM pet WHERE name like '%w% ';

Mysql> SELECT * FROM pet
, WHERE name like '%w% ';
+----------+-------+---------+------+------------+------------+
| name | Owner | Species | sex | Birth | Death |
+----------+-------+---------+------+------------+------------+
| Whistler | Gwen | Bird | NULL | 1997-12-09 | NULL |
| Claws | Gwen | Cat | m | 1994-03-17 | NULL |
| Bowser | Diane | Dog | m | 1989-08-31 | 1995-07-29 |
+----------+-------+---------+------+------------+------------+
3 Rows in Set (0.00 sec)

Mysql>

Or chestnuts: a customer of a brain remnant is looking for a five-word name. At this time wit you (??????)?? You should use ' _ ' to match.

The operation is as follows:

SELECT * FROM pet WHERE name like ' _____ ';(don't count five underscores)

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 |
+-------+--------+---------+------+------------+-------+
2 rows in Set (0.00 sec)

Mysql>

If it is a very complex matching principle (for example, if I want to find an ID number in text), I will use the regular expression.

This is the use of both operators REGEXP and not REGEXP (or rlike and not rlike, they are synonyms).

Let's start by listing a few simple, detailed ones. If not updated ...

• '. ' matches any single character.
• The character class “[...]” matches any character within the square brackets. For example, “[abc]” match “a” , “b” or “c” . To name a range of characters, use a "-". “[a-z]”matches any letter, and “[0-9]” matches any number.
• "*" matches 0 or more characters in front of it. For example, match any number of “x*” “x” characters, match any number of “[0-9]*” numbers, and ". *" matches any number of characters.
• To locate a pattern so that it must match the beginning or end of the value being tested, use at the beginning of the pattern “^” or在模式的结尾用“$”;

I'll re-knock the chestnut up again.

Find names that start with ' B '

The operation is as follows:

SELECT * from pet WHERE name Rlike ' ^b ';

Mysql> SELECT * FROM pet
WHERE name Rlike ' ^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 |
+--------+--------+---------+------+------------+------------+
2 rows in Set (0.05 sec)

Mysql>

If you have to be case-sensitive, you should write this.

SELECT * from pet WHERE name Rlike BINARY ' ^b ';

Find the name ending with ' FY '

The operation is as follows:

SELECT * from pet WHERE name Rlike ' fy$ ';

Mysql> SELECT * from pet WHERE name Rlike ' fy$ ';
+--------+--------+---------+------+------------+-------+
| name | Owner | Species | sex | Birth | Death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | Cat | f | 1993-02-04 | NULL |
| Buffy | Harold | Dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+
2 rows in Set (0.00 sec)

Mysql>

Find out what's in the name ' W '.

The operation is as follows:

SELECT * from pet WHERE name Rlike ' W ';

Mysql> SELECT *
From pet
WHERE name Rlike ' W ';
+----------+-------+---------+------+------------+------------+
| name | Owner | Species | sex | Birth | Death |
+----------+-------+---------+------+------------+------------+
| Whistler | Gwen | Bird | NULL | 1997-12-09 | NULL |
| Claws | Gwen | Cat | m | 1994-03-17 | NULL |
| Bowser | Diane | Dog | m | 1989-08-31 | 1995-07-29 |
+----------+-------+---------+------+------------+------------+
3 Rows in Set (0.00 sec)

Mysql>

Find a 5-letter name for pet

The operation is as follows:

SELECT * from pet WHERE name Rlike ' ^.....$ ';

Mysql> SELECT * from pet WHERE name Rlike ' ^.....$ ';
+-------+--------+---------+------+------------+-------+
| name | Owner | Species | sex | Birth | Death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | Cat | m | 1994-03-17 | NULL |
| Buffy | Harold | Dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
2 rows in Set (0.00 sec)

Mysql>

You can also write this:

SELECT * from pet WHERE name Rlike ' ^. {5}$ ';

Mysql> SELECT * from pet WHERE name Rlike ' ^. {5}$ ';
+-------+--------+---------+------+------------+-------+
| name | Owner | Species | sex | Birth | Death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | Cat | m | 1994-03-17 | NULL |
| Buffy | Harold | Dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
2 rows in Set (0.00 sec)

Mysql>

To be Continued ...

MySQL Learning note 006