作者:LAMP小白 点击:1890 发布日期:2012-10-18 00:16:00 返回列表
关于NULL
NULL是一个很特殊的值,我们不能用 = <> != 来测试,如果要判断等于或者不等于
可以用 IS NULL 或 IS NOT NULL来判断
mysql> SELECT * FROM president WHERE death IS NULL;
+-----------+------------+--------+--------+-------+------------+-------+
| last_name | first_name | suffix | city | state | birth | death |
+-----------+------------+--------+--------+-------+------------+-------+
| KAGAMI | MINAMI | NULL | TOYOTA | 15 | 2012-10-01 | NULL |
| mio | haha | a | CN | 16 | 2012-10-01 | NULL |
+-----------+------------+--------+--------+-------+------------+-------+
2 rows in set (0.00 sec)
关于IF
如果我们对这些数据按 ORDER BY desth排序
mysql> SELECT * FROM president;
+-----------+------------+--------+--------+-------+------------+------------+
| last_name | first_name | suffix | city | state | birth | death |
+-----------+------------+--------+--------+-------+------------+------------+
| KAGAMI | MINAMI | NULL | TOYOTA | 15 | 2012-10-01 | NULL |
| mio | haha | a | CN | 16 | 2012-10-01 | NULL |
| YOSITA | KONA | NULL | JP | 14 | 2012-09-11 | NULL |
| NARUTO | YOZIMAKI | NULL | JP | 17 | 2012-09-03 | 2012-11-07 |
| SILA | ARK | D | US | 77 | 2012-09-02 | 2012-11-28 |
| HEMA | DEE | K | EU | 78 | 2012-09-10 | 2013-07-17 |
+-----------+------------+--------+--------+-------+------------+------------+
6 rows in set (0.00 sec)
当ORDER BY denth DESC时,所有有数值的行会排在上面,而NULL行会在最下面,但是如果我们既希望按照DESC 又希望NULL行在上面时,应该怎么做呢?
那么就需要使用IF了,IF会对紧随其后的第一个参数进行求值,返回第二个参数或第三个参数
于是:
mysql> SELECT * FROM president ORDER BY IF(death IS NULL,0,1), death DESC;
+-----------+------------+--------+--------+-------+------------+------------+
| last_name | first_name | suffix | city | state | birth | death |
+-----------+------------+--------+--------+-------+------------+------------+
| KAGAMI | MINAMI | NULL | TOYOTA | 15 | 2012-10-01 | NULL |
| mio | haha | a | CN | 16 | 2012-10-01 | NULL |
| YOSITA | KONA | NULL | JP | 14 | 2012-09-11 | NULL |
| HEMA | DEE | K | EU | 78 | 2012-09-10 | 2013-07-17 |
| SILA | ARK | D | US | 77 | 2012-09-02 | 2012-11-28 |
| NARUTO | YOZIMAKI | NULL | JP | 17 | 2012-09-03 | 2012-11-07 |
+-----------+------------+--------+--------+-------+------------+------------+
6 rows in set (0.00 sec)
表达式
MYSQL还能进行一些数学运算和合并,但一般不这么做
mysql> SELECT 17, FORMAT(SQRT(25+2), 3);
+----+-----------------------+
| 17 | FORMAT(SQRT(25+2), 3) |
+----+-----------------------+
| 17 | 5.196 |
+----+-----------------------+
1 row in set (0.00 sec)
mysql> SELECT CONCAT(first_name,'---',last_name) AS haha FROM president;
+-------------------+
| haha |
+-------------------+
| MINAMI---KAGAMI |
| haha---mio |
| KONA---YOSITA |
| YOZIMAKI---NARUTO |
| ARK---SILA |
| DEE---HEMA |
+-------------------+
6 rows in set (0.00 sec)
上一篇:实现微博授权浮层效果 下一篇:快递查询API