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 创建测试 表
CREATE TABLE PLAYERS (PLAYERNO INTEGER NOT NULL , NAME CHAR (15) NOT NULL , INITIALS CHAR (3) NOT NULL , BIRTH_DATE DATE , SEX CHAR (1) NOT NULL , JOINED SMALLINT NOT NULL , STREET VARCHAR (30) NOT NULL , HOUSENO CHAR (4) , POSTCODE CHAR (6) , TOWN VARCHAR (30) NOT NULL , PHONENO CHAR (13) , LEAGUENO CHAR (4) , PRIMARY KEY (PLAYERNO));
2.2 插入测试数据
INSERT INTO PLAYERS VALUES (2, 'Everett' , 'R' , '1948-09-01' , 'M' , 1975, 'Stoney Road' , '43' , '3575NH' , 'Stratford' , '070-237893' , '2411' ); INSERT INTO PLAYERS VALUES (6, 'Parmenter' , 'R' , '1964-06-25' , 'M' , 1977, 'Haseltine Lane' , '80' , '1234KK' , 'Stratford' , '070-476537' , '8467' ); INSERT INTO PLAYERS VALUES (7, 'Wise' , 'GWS' , '1963-05-11' , 'M' , 1981, 'Edgecombe Way' , '39' , '9758VB' , 'Stratford' , '070-347689' , NULL ); INSERT INTO PLAYERS VALUES (8, 'Newcastle' , 'B' , '1962-07-08' , 'F' , 1980, 'Station Road' , '4' , '6584WO' , 'Inglewood' , '070-458458' , '2983' ); INSERT INTO PLAYERS VALUES (27, 'Collins' , 'DD' , '1964-12-28' , 'F' , 1983, 'Long Drive' , '804' , '8457DK' , 'Eltham' , '079-234857' , '2513' ); INSERT INTO PLAYERS VALUES (28, 'Collins' , 'C' , '1963-06-22' , 'F' , 1983, 'Old Main Road' , '10' , '1294QK' , 'Midhurst' , '010-659599' , NULL ); INSERT INTO PLAYERS VALUES (39, 'Bishop' , 'D' , '1956-10-29' , 'M' , 1980, 'Eaton Square' , '78' , '9629CD' , 'Stratford' , '070-393435' , NULL ); INSERT INTO PLAYERS VALUES (44, 'Baker' , 'E' , '1963-01-09' , 'M' , 1980, 'Lewis Street' , '23' , '4444LJ' , 'Inglewood' , '070-368753' , '1124' ); INSERT INTO PLAYERS VALUES (57, 'Brown' , 'M' , '1971-08-17' , 'M' , 1985, 'Edgecombe Way' , '16' , '4377CB' , 'Stratford' , '070-473458' , '6409' ); INSERT INTO PLAYERS VALUES (83, 'Hope' , 'PK' , '1956-11-11' , 'M' , 1982, 'Magdalene Road' , '16A' , '1812UP' , 'Stratford' , '070-353548' , '1608' ); INSERT INTO PLAYERS VALUES (95, 'Miller' , 'P' , '1963-05-14' , 'M' , 1972, 'High Street' , '33A' , '5746OP' , 'Douglas' , '070-867564' , NULL ); INSERT INTO PLAYERS VALUES (100, 'Parmenter' , 'P' , '1963-02-28' , 'M' , 1979, 'Haseltine Lane' , '80' , '6494SG' , 'Stratford' , '070-494593' , '6524' ); INSERT INTO PLAYERS VALUES (104, 'Moorman' , 'D' , '1970-05-10' , 'F' , 1984, 'Stout Street' , '65' , '9437AO' , 'Eltham' , '079-987571' , '7060' ); INSERT INTO PLAYERS VALUES (112, 'Bailey' , 'IP' , '1963-10-01' , 'F' , 1984, 'Vixen Road' , '8' , '6392LK' , 'Plymouth' , '010-548745' , '1319' );
3 实例分析
3.1 实例1
获取拥有一个联盟会员号码的每个球员的号码和联盟会员的号码。
select playerno, leagueno from players where leagueno is not null ;
注意:is null不能用=号代替。
3.2 实例2
如果要获得非联盟成员的所有球员的信息,只需将is not null改为is null 即可
select playerno, leagueno from players where leagueno is null ;
3.3 实例3 isfull()函数1
mysql> SELECT IFNULL(1, 'test' ); + | IFNULL(1,'test' ) | + | 1 | +
由于 expr1 是 1,不为 NULL,所以函数返回1.
mysql> SELECT IFNULL(1/0, 'test' ); + | IFNULL(1/0,'test' ) | + | test | +
由于 expr1 是NULL,所以函数返回test.
4 一些注意事项
4.1 排序时MySQL 将null作为最小值处理。
4.2 not null字段不能插入null,但可以插入空值。