MySQL Knowledge points

1.WITH ROLLUP: Statistical data is performed on a group basis.

Example: First grouping on the name field and then counting on a group basis

Mysql>SELECT Name,SUM(Singin) AsSingin_count from Employee_tbl GROUP by name with ROLLUP;+--------+--------------+|Name|Singin_count|+--------+--------------+| Xiaoli | 2 || Xiao ming | 7 ||   Xiao Wang  |7 ||  NULL | 16 |+--------+--------------+4 rows in set  ( 0.00 Sec) 
 
  2.coalesce   
     Select Coalesce(a,b,c);        

Parameter description: If a==null, select B; If b==null, select C; If A!=null, select A; if a b c is null, the return is null (meaningless).

In the following example, if the name is empty, we use the total number instead:

Mysql>SELECT COALESCE(Name, Total),SUM(Singin) AsSingin_count from Employee_tbl GROUP by name with ROLLUP;+--------------------------+--------------+|Coalesce(Name, Total) |Singin_count|+--------------------------+--------------+| Xiaoli | 2 || Xiao ming | 7 | | Xiao Wang  |7 ||   Total  |16 |+--------------------------+--------------+4in set  (0.01 Sec) 
 
  3.NULL value 
      (1) is null: This operator returns True when the value of the column is null.
(2) is not NULL: the operator returns True when the value of the column is not NULL.

  
 

   
  
 
   
(3) <=>: Comparison operators (unlike the = operator), which returns True when the two value of a comparison is NULL.
 
   
 
  
  
 
(4) The comparison of the null value to any other value (even null) returns False forever, that is, NULL = NULL returns FALSE.

MySQL Knowledge points