-- First, insert data into the user table as follows

TRUNCATE TABLE UserInfo ;

INSERT  INTO `userinfo`(`ID`,`UserName`,`UserLogin`,`UserPassword`,`UserEmail`,`UserType`)

VALUES (1,' Zhang San ','zhangsan','zhangsan','zhangsan@qq.com',1),

(2,' Li Si ','lisi','lisi','lisi@qq.com',2),

(3,' Wang Wu ','wangwu','wangwu','wangwu@qq.com',1),

(4,' Zhao Liu ','zhaoliu','zhaoliu','zhaoliu@qq.com',3),

(5,' Zhao Liu ','zhaoliu1','zhaoliu1','zhaoliu1@qq.com',3),

(6,NULL,'test1','test1',NULL,NULL),

(7,NULL,'test2','test2',NULL,NULL),

(8,NULL,'test3','test3',NULL,NULL),

(9,'','TEST5','TEST5','',1),

(10,'','TEST6','TEST6','',2),

(11,'','TEST7','TEST7','',3),

(12,'','TEST8','TEST8','',1),

(13,'','TEST9','TEST9','',2),

(14,'','TEST0','TEST0','',3);

-- The query user name is empty (null) User information for

SELECT * FROM UserInfo WHERE userName IS NULL ;

SELECT * FROM UserInfo WHERE ISNULL(UserName) ;

-- The query user name is an empty string ('') User information for

SELECT * FROM UserInfo WHERE UserName ='' ;

-- Query user name is Not empty (null) User information for     !=null and is not null Is different By default !=null return 0 Row result

It is often used when the filter is not empty is not null and !=null, These two methods are similar only from the literal point of view , In fact, the running results are very different !

null It means nothing , You can't =、>、< … All judgment , The result is false, All you can do is is null Judge . By default , Recommended is not null To judge , because SQL By default !=null Your judgment will always return to 0 That's ok , But there are no grammatical errors ;

SELECT * FROM UserInfo WHERE userName IS NOT NULL ;

SELECT * FROM UserInfo WHERE UserName != NULL ;

-- Query user name Not an empty string ('') And it's not empty (null) User information for

SELECT * FROM UserInfo WHERE UserName <> '' ;

-- There's another problem count(columnName) The function is not statistical null It's worth it

SELECT COUNT(*) FROM UserInfo ;

SELECT COUNT(UserName) FROM UserInfo ;

-- IFNULL Function USES

-- IFNULL(expr1,expr2)  If expr1 No NULL, return expr1, Otherwise it returns expr2.

SELECT *,IFNULL(UserName,' Invalid user ') AS Remark FROM UserInfo

SELECT IFNULL(0,1) ;

SELECT IFNULL(0/1,' Instead of null') ;.

SELECT IFNULL(1/0,' Instead of null') ;

-- Expand IF function

-- IF(expr1,expr2,expr3)  If expr1 yes TRUE(expr1<>0 And expr1<>NULL), return expr2, Otherwise it returns expr3.

SELECT IF(TRUE,'true value ','false value ') ;

SELECT IF(FALSE,'true value ','false value ') ;

SELECT IF(1>2,1,2) ;

SELECT IF(1<2,2,1) ;

MySQL NULL More related articles

  1. MySQL NULL Value processing

    MySQL NULL Value processing We already know MySQL Use SQL SELECT Orders and WHERE Clause to read data in the data table , But when the query criteria field provided is NULL when , The order may not work . in order to ...

  2. Oracle And Mysql NULL value , An empty string &#39;&#39; The difference between

    Oracle(null Equivalent to empty characters '') 1.oracle Insert an empty string and replace it with null 2.oracle Inquire about (null And replaced empty characters ) When using is null/is not null 3. Using aggregate functions ...

  3. The 21st 、MySQL NULL Value processing

    MySQL NULL Value processing We already know MySQL Use SQL SELECT Orders and WHERE Clause to read data in the data table , But when the query criteria field provided is NULL when , The order may not work . ...

  4. DB-MySQL:MySQL NULL Value processing

    ylbtech-DB-MySQL:MySQL NULL Value processing 1. Return to the top 1. MySQL NULL Value processing We already know MySQL Use SQL SELECT Orders and WHERE Clause to read the number ...

  5. MySQL NULL The pit brought by using

    MySQL  The basic chapter Three paradigms MySQL catch MySQL To configure MySQL User management and permission settings MySQL Introduction to common functions MySQL Field type introduction MySQL Multi column sorting MySQL Transfer line column Column turned M ...

  6. mysql NULL function grammar

    mysql NULL function grammar effect : If a column in the table is optional , Then we can insert a new record or update an existing record without adding a value to the column . This means that the field will be in NULL Worth saving . explain :NULL The way values are handled depends on ...

  7. mysql null Value processing details

    Talking about null Before , Let's start with an example The table data is as follows : 3306>select * from t1; +------+-------+ | id | name | +------+-------+ | ...

  8. mysql=null The elegant solution

    Not all fields are not null Of mysql In terms of table design ,mysql Provides a <=> The operator . stay oracle We usually deal with it in a similar way : where a = #{var} or #{v ...

  9. mysql null Special treatment of values

    example Try the following example : root@host# mysql -u root -p password; Enter password:******* mysql> use RUNOOB; Databa ...

  10. mysql null Value problem

    mysql> create table test( sn int, -> `createdTime` datetime NOT NULL COMMENT ' Creation time ', -> `up ...

Random recommendation

  1. JAVA Design patterns Portfolio model

    purpose Portfolio model (Component) Combine objects into a tree structure to represent “ part - whole ” Hierarchical structure . The combination pattern makes the use of single object and combination object unique . The combination pattern is a structural pattern . structure

  2. Tornado- brief introduction

    Definition Tornado Its full name is Tornado Web Server, It's a use. Python Written Web Server and application framework , In the end by the FriendFeed Company use , later FriendFeed By Facebook After acquisition , ...

  3. BZOJ2961: Common circle

    It's been a long time CDQ Divide and conquer , See for details XHR The paper of … /************************************************************** Problem: 29 ...

  4. (3) Select element ——(4)css Selectors (CSS selectors)

    The jQuery library supports nearly all of the selectors included in CSS specifications 1 through 3, ...

  5. Tensorflow practice :CNN Realization MNIST Handwriting recognition model

    Preface This paper assumes that you are right CNN.softmax The principle is quite familiar , The focus is on using Tensorflow Yes CNN In simple practice . So the algorithm will not be introduced in detail , It mainly explains the definition and usage of some functions used in the code , And give ...

  6. Batch testing GoAhead In series servers Digest Authentication server weak password

    I'm learning to use it recently python Write crawler tools , I came across it one day GoAhead The login mode of series servers is different from that of most websites , Not with POST Other methods , Find out by looking up the information GoAhead It's open source ( Business license ). Simple . light . Powerful ...

  7. forms- Hide processing

    obtain pin code . View source code of webpage <form action="" method="post">    PIN:<br>    <inpu ...

  8. Tomcat utilize MSM Realization Session Sharing solutions

    Session There are many solutions to sharing , There are four commonly used :1) client Cookie preservation 2) Server room Session Sync 3) Use cluster management Session( Such as MSM) 4) hold Session Persist to database For the above Sess ...

  9. Luogu U36590 Moving books

    Background Mr. Chen likes to buy books online , I often buy one hundred and eighty copies at a time , And then resell it , Profiteering . The other day , New students from senior one are coming , He's just as usual , Peddling his books , After a lot of talking , The students decided to buy his book , But there are three piles of books on Mr. Chen's desk , Every pile is ...

  10. js A summary of the actual combat of various cross domain problems in

    What is cross-domain ? Why cross domain ?   This is because JavaScript For safety reasons , Cross domain calls to objects on other pages are not allowed . That is to say, only resources in the same domain can be accessed . I think it's necessary to understand javascript The homology in ...