Mysql学习笔记006

 

 这里并没有前情回顾

NULL值操作

Null是一个特殊的值，它表示着“没有值”或者“未知值”,记住它是一个与众不同的值。

为了测试NULL，不能用算数比较操作符 不信你试试

操作如下：

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>

都说了不能了，你还试。

 

那应该用什么办法试试呢？（搞事情）

远古时代的上神在升天之前曾经遗留下这两个操作符在人间供凡人们使用，他们分别是：

IS NULL 和 IS NOT NULL 这两个操作符。

操作如下：

SELECT 1 IS NULL, 1 IS NOT NULL;

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
| 0 | 1 |
+-----------+---------------+
1 row in set (0.08 sec)

mysql>

 

 

温馨提示（书上又瞎操心了）:在GROUP BY 中两个NULL值视为相同（我还没讲GROUP BY呢）。

执行ORDER BY时， 如果运行 ORDER BY ... ASC (升序)， 则NULL值出现在最前面，若运行ORDER BY ...DESC ，则NULL值出现在最后面。

 

mysql> SELECT 0 IS NULL, 0 IS 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>

 

 

模式匹配

注意：使用SQL模式匹配的时候，不能使用= 和 ！= 而应该使用 LIKE 或 NOT LIKE 比较操作符。还有忽略大小写。

 

 

SQL模式匹配允许你使用 "_" 匹配任何单个字符，而 "%" 匹配任意数目字符(包括零字符)。

 

 

 

 

举个栗子：我想找以'b'开头的名字。

操作如下：

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>

 

再举个栗子：我想找出以'fy'结尾的名字。

操作如下：

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>

 

接着举个栗子：我想找出名字中包含'w'的。（动态查找（学长告诉我的））

操作如下：

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>

 

 

还是栗子：某个脑残客户要找五个字的名字。这时候机智的你(•́⌄•́๑)૭✧就应该用'_'来匹配了

操作如下：

SELECT * FROM pet WHERE name LIKE '_____';(别数了是五个下划线)

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>

 

 

如果是非常复杂的匹配原则（比如我要在text 中找有没有身份证号）就会使用到正则表达式了。

这是就用到了这两个操作符 REGEXP 和 NOT REGEXP（或RLIKE 和 NOT RLIKE，他们是同义词）。

先列举几个简单的，详细的之后会有的。如果要是不更新的话。。。

• '.' 匹配任何单个的字符。
• 字符类“[...]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”、“b”或“c”。为了命名字符的范围，使用一个“-”。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。
• "*" 匹配零个或多个在它前面的字符。例如，“x*”匹配任何数量的“x”字符，“[0-9]*”匹配任何数量的数字，而 ".*" 匹配任何数量的任何字符。
• 为了定位一个模式以便它必须匹配被测试值的开始或结尾，在模式开始处使用“^”或在模式的结尾用“$”;

 

我再把上面的栗子重新敲一遍。

 

找出以'b'开头的名字

操作如下：

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>

 

如果你非要区分大小写就应该这么写

SELECT * FROM pet WHERE name RLIKE BINARY '^b';

 

 

找出以'fy'结尾的名字

操作如下：

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>

 

找出名字中有'w'的

操作如下:

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>

 

 

找出5个字母的名字的pet

操作如下：

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>

 

也可以这么写：

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...