和燕十八学习PHP-第二十六天-奇怪的NULL

跟燕十八学习PHP-第二十六天-奇怪的NULL

/** 
燕十八 公益PHP培训 
课堂地址:YY频道88354001 
学习社区:www.zixue.it 
**/

mysql> #建表时,列后面 not null default '',default 0,这是什么意思
mysql> #答:就是让这个列值不为NULL,如果某个列确实没填值,也有默认值,也不为null
mysql> # 为什么不希望让列的值为null呢?
mysql> create table test9 (
    -> sname varchar(20)
    -> )engine myisam charset utf8;
Query OK, 0 rows affected (0.20 sec)


mysql> insert into test9
    -> values
    -> ('lisi','wangwu','null');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into test9
    -> values
    -> ('lisi'),('wangwu',('null');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds 


to your MySQL server version for the right syntax to use near '' at line 3
mysql> insert into test9
    -> values
    -> ('lisi'),('wangwu'),('null');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0


mysql> select * from test9;
+--------+
| sname  |
+--------+
| lisi   |
| wangwu |
| null   |
+--------+
3 rows in set (0.00 sec)


mysql> insert into test9 values (NULL);
Query OK, 1 row affected (0.02 sec)


mysql> select * from test9;
+--------+
| sname  |
+--------+
| lisi   |
| wangwu |
| null   |
| NULL   |
+--------+
4 rows in set (0.00 sec)


mysql> delete from test9 where sname='null';
Query OK, 1 row affected (0.03 sec)


mysql> select * from test9;
+--------+
| sname  |
+--------+
| lisi   |
| wangwu |
| NULL   |
+--------+
3 rows in set (0.00 sec)


mysql> #查询出用户名不为null的行
mysql> select * from test9 where sname!=null;
Empty set (0.00 sec)


mysql> #lisi wangwu没查出来? 为什么
mysql> #查sname为null的行
mysql> select * from test9 where sname=null;
Empty set (0.00 sec)


mysql> #又是空
mysql> select 2>1;
+-----+
| 2>1 |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)


mysql> select 2 +-----+
| 2 +-----+
|   0 |
+-----+
1 row in set (0.00 sec)


mysql> select 39 +-------+
| 39 +-------+
|     1 |
+-------+
1 row in set (0.00 sec)


mysql> select 'lisi'=null;
+-------------+
| 'lisi'=null |
+-------------+
|        NULL |
+-------------+
1 row in set (0.00 sec)


mysql> # null为假, lisi=null是假
mysql> select null=null;
+-----------+
| null=null |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)


mysql> # null=null,还是null,还是假
mysql> select null!=null;
+------------+
| null!=null |
+------------+
|       NULL |
+------------+
1 row in set (0.00 sec)


mysql> # null 是空,四大皆空的空. 
mysql> # null的比较需要用特殊的运算符  is null ,is not null
mysql> select * from sname where sname is not null;
ERROR 1146 (42S02): Table 'test.sname' doesn't exist
mysql> select * from test9 where sname is not null;
+--------+
| sname  |
+--------+
| lisi   |
| wangwu |
+--------+
2 rows in set (0.00 sec)


mysql> select * from test9 where sname is null;
+-------+
| sname |
+-------+
| NULL  |
+-------+
1 row in set (0.00 sec)


mysql> exit


燕十八老师太幽默了, 昨天的视频如下:

http://www.tudou.com/programs/view/-A_S1EMsNDs/