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
(PLAYERNO INTEGERNOTNULL,
NAMECHAR(15)NOTNULL,
INITIALS CHAR(3)NOTNULL,
BIRTH_DATE DATE,
SEX CHAR(1)NOTNULL,
JOINED SMALLINTNOTNULL,
STREET VARCHAR(30)NOTNULL,
HOUSENO CHAR(4) ,
POSTCODE CHAR(6) ,
TOWN VARCHAR(30)NOTNULL,
PHONENO CHAR(13) ,
LEAGUENO CHAR(4) ,
PRIMARYKEY(PLAYERNO));
2.2 插入测试数据
[sql] view plain
copy
INSERTINTOPLAYERSVALUES(2,'Everett','R','1948-09-01','M', 1975,'Stoney Road','43','3575NH','Stratford','070-237893','2411');
INSERTINTOPLAYERSVALUES(6,'Parmenter','R','1964-06-25','M', 1977,'Haseltine Lane','80','1234KK','Stratford','070-476537','8467');
INSERTINTOPLAYERSVALUES(7,'Wise','GWS','1963-05-11','M', 1981,'Edgecombe Way','39','9758VB','Stratford','070-347689',NULL);
INSERTINTOPLAYERSVALUES(8,'Newcastle','B','1962-07-08','F', 1980,'Station Road','4','6584WO','Inglewood','070-458458','2983');
INSERTINTOPLAYERSVALUES(27,'Collins','DD','1964-12-28','F', 1983,'Long Drive','804','8457DK','Eltham','079-234857','2513');
INSERTINTOPLAYERSVALUES(28,'Collins','C','1963-06-22','F', 1983,'Old Main Road','10','1294QK','Midhurst','010-659599',NULL);
INSERTINTOPLAYERSVALUES(39,'Bishop','D','1956-10-29','M', 1980,'Eaton Square','78','9629CD','Stratford','070-393435',NULL);
INSERTINTOPLAYERSVALUES(44,'Baker','E','1963-01-09','M', 1980,'Lewis Street','23','4444LJ','Inglewood','070-368753','1124');
INSERTINTOPLAYERSVALUES(57,'Brown','M','1971-08-17','M', 1985,'Edgecombe Way','16','4377CB','Stratford','070-473458','6409');
INSERTINTOPLAYERSVALUES(83,'Hope','PK','1956-11-11','M', 1982,'Magdalene Road','16A','1812UP','Stratford','070-353548','1608');
INSERTINTOPLAYERSVALUES(95,'Miller','P','1963-05-14','M', 1972,'High Street','33A','5746OP','Douglas','070-867564',NULL);
INSERTINTOPLAYERSVALUES(100,'Parmenter','P','1963-02-28','M', 1979,'Haseltine Lane','80','6494SG','Stratford','070-494593','6524');
INSERTINTOPLAYERSVALUES(104,'Moorman','D','1970-05-10','F', 1984,'Stout Street','65','9437AO','Eltham','079-987571','7060');
INSERTINTOPLAYERSVALUES(112,'Bailey','IP','1963-10-01','F', 1984,'Vixen Road','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,但可以插入空值。