mysql比较运算符和函数

 

mysql> SELECT 15 BETWEEN 1 AND 22;
+---------------------+
| 15 BETWEEN 1 AND 22 |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT 22 BETWEEN 1 AND 22;
+---------------------+
| 22 BETWEEN 1 AND 22 |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT 32 BETWEEN 1 AND 22;
+---------------------+
| 32 BETWEEN 1 AND 22 |
+---------------------+
| 0 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT 32 NOT BETWEEN 1 AND 22;
+-------------------------+
| 32 NOT BETWEEN 1 AND 22 |
+-------------------------+
| 1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT 10 IN(5,10,15);
+----------------+
| 10 IN(5,10,15) |
+----------------+
| 1 |
+----------------+
1 row in set (0.03 sec)

mysql> SELECT 13 IN(5,10,15);
+----------------+
| 13 IN(5,10,15) |
+----------------+
| 0 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT NULL IS NULL;
+--------------+
| NULL IS NULL |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT '' IS NULL;
+------------+
| '' IS NULL |
+------------+
| 0 |
+------------+
1 row in set (0.00 sec)

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

mysql> SELECT * FROM test;
+----+----------+
| id | username |
+----+----------+
| 1 | John |
| 2 | Mary |
| 3 | 1%Lily |
| 4 | Rose |
| 5 | NULL |
+----+----------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM test WHERE USERNAME IS NULL;
+----+----------+
| id | username |
+----+----------+
| 5 | NULL |
+----+----------+

mysql> SELECT * FROM test WHERE USERNAME IS NOT NULL;
+----+----------+
| id | username |
+----+----------+
| 1 | John |
| 2 | Mary |
| 3 | 1%Lily |
| 4 | Rose |
+----+----------+