跟燕十八学习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<1;
+-----+
| 2<1 |
+-----+
| 0 |
+-----+
1 row in set (0.00 sec)

mysql> select 39<60;
+-------+
| 39<60 |
+-------+
| 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

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