1 一些概念
1.1 null与not null
null 和 not null是mysql的字段属性，或称为数据类型的属性，不属于任何类型。null值也不等同于空值，空值是不占用存储空间的，null占用存储空间，如对myisam表占用1bit额外存储空间。
1.2 is null与 is not null
is null和 is not null是mysql的运算符。查询某字段为空时用is null，而不能使用"=null",因为mysql中的null不等于任何其他值，也不等于另外一个null，优化器会把"=null"的查询过滤掉而不返回任何数据；查询某字段为非空时使用is not null。
1.3 ifnull():语法如下：
IFNULL(expr1,expr2)
如果expr1不是NULL，IFNULL()返回expr1，否则它返回expr2。IFNULL()返回一个数字或字符串值，取决于它被使用的上下文环境。
2 创建测试表及数据
2.1 创建测试表
[sql] view plain
copy
CREATETABLEPLAYERS
(PLAYERNOINTEGERNOTNULL,
NAMECHAR(15)NOTNULL,
INITIALSCHAR(3)NOTNULL,
BIRTH_DATEDATE,
SEXCHAR(1)NOTNULL,
JOINEDSMALLINTNOTNULL,
STREETVARCHAR(30)NOTNULL,
HOUSENOCHAR(4),
POSTCODECHAR(6),
TOWNVARCHAR(30)NOTNULL,
PHONENOCHAR(13),
LEAGUENOCHAR(4),
PRIMARYKEY(PLAYERNO));
2.2 插入测试数据
[sql] view plain
copy
INSERTINTOPLAYERSVALUES(2,'Everett','R','1948-09-01','M',1975,'StoneyRoad','43','3575NH','Stratford','070-237893','2411');
INSERTINTOPLAYERSVALUES(6,'Parmenter','R','1964-06-25','M',1977,'HaseltineLane','80','1234KK','Stratford','070-476537','8467');
INSERTINTOPLAYERSVALUES(7,'Wise','GWS','1963-05-11','M',1981,'EdgecombeWay','39','9758VB','Stratford','070-347689',NULL);
INSERTINTOPLAYERSVALUES(8,'Newcastle','B','1962-07-08','F',1980,'StationRoad','4','6584WO','Inglewood','070-458458','2983');
INSERTINTOPLAYERSVALUES(27,'Collins','DD','1964-12-28','F',1983,'LongDrive','804','8457DK','Eltham','079-234857','2513');
INSERTINTOPLAYERSVALUES(28,'Collins','C','1963-06-22','F',1983,'OldMainRoad','10','1294QK','Midhurst','010-659599',NULL);
INSERTINTOPLAYERSVALUES(39,'Bishop','D','1956-10-29','M',1980,'EatonSquare','78','9629CD','Stratford','070-393435',NULL);
INSERTINTOPLAYERSVALUES(44,'Baker','E','1963-01-09','M',1980,'LewisStreet','23','4444LJ','Inglewood','070-368753','1124');
INSERTINTOPLAYERSVALUES(57,'Brown','M','1971-08-17','M',1985,'EdgecombeWay','16','4377CB','Stratford','070-473458','6409');
INSERTINTOPLAYERSVALUES(83,'Hope','PK','1956-11-11','M',1982,'MagdaleneRoad','16A','1812UP','Stratford','070-353548','1608');
INSERTINTOPLAYERSVALUES(95,'Miller','P','1963-05-14','M',1972,'HighStreet','33A','5746OP','Douglas','070-867564',NULL);
INSERTINTOPLAYERSVALUES(100,'Parmenter','P','1963-02-28','M',1979,'HaseltineLane','80','6494SG','Stratford','070-494593','6524');
INSERTINTOPLAYERSVALUES(104,'Moorman','D','1970-05-10','F',1984,'StoutStreet','65','9437AO','Eltham','079-987571','7060');
INSERTINTOPLAYERSVALUES(112,'Bailey','IP','1963-10-01','F',1984,'VixenRoad','8','6392LK','Plymouth','010-548745','1319');
3 实例分析
3.1 实例1
获取拥有一个联盟会员号码的每个球员的号码和联盟会员的号码。
[sql] view plain
copy
selectplayerno,leagueno
fromplayers
whereleaguenoisnotnull;
注意：is null不能用=号代替。
3.2 实例2
如果要获得非联盟成员的所有球员的信息，只需将is not null改为is null 即可
[sql] view plain
copy
selectplayerno，leagueno
fromplayers
whereleaguenoisnull;
3.3 实例3 isfull()函数1
[sql] view plain
copy
mysql>SELECTIFNULL(1,'test');
+------------------+
|IFNULL(1,'test')|
+------------------+
|1|
+------------------+
由于 expr1 是 1，不为 NULL，所以函数返回1.
[sql] view plain
copy
mysql>SELECTIFNULL(1/0,'test');
+--------------------+
|IFNULL(1/0,'test')|
+--------------------+
|test|
+--------------------+
由于 expr1 是NULL，所以函数返回test.
4 一些注意事项
4.1 排序时MySQL将null作为最小值处理。
4.2 not null字段不能插入null，但可以插入空值。
