sql里面null值的处理,count()函数的使用注意事项

 

1.mysql中int(1) 和 int(11) 的区别

(1) int(m) 长度M与你存放的数值型的数的大小无关.

(2)create table  demo ( id int UNSIGNED );

  说明:默认是有正负号的

          UNSIGNED表示无符号的

 

测试的sql

MariaDB [db10]> insert into demo select 4294967295 ;

Query OK, 1 row affected (0.138 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

MariaDB [db10]> insert into demo select 4294967294;

Query OK, 1 row affected (0.132 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

MariaDB [db10]> select * from demo;

+------------+

| id         |

+------------+

| 4294967295 |

| 4294967294 |

+------------+

2 rows in set (0.000 sec)

 

MariaDB [db10]> insert into demo select 4294967296;

ERROR 1264 (22003): Out of range value for column 'id' at row 1

(3) unsigned zerofill 修饰int类型的使用

create table demo(id int(2) unsigned zerofill);

Query OK, 0 rows affected (0.275 sec)

 

MariaDB [db10]> insert into demo select 1;

Query OK, 1 row affected (0.033 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

MariaDB [db10]> select * from demo;

+------+

| id   |

+------+

|    01 |

+------+

1 row in set (0.000 sec)

 

MariaDB [db10]> insert into

    -> demo

    -> select 1000000;

Query OK, 1 row affected (0.123 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

MariaDB [db10]> select * from demo;

+---------+

| id      |

+---------+

|       01 |

| 1000000 |

+---------+

2 rows in set (0.000 sec)

 

2.oracle的nvl函数和mysql的ifnull函数一样

(1) 回顾mysql的ifnull函数

出现这个函数的原因

因为在mysql中null和任何数据值相加都是0

create table demo(n1 int , n2 int);

insert into demo select 1, 1;

insert into demo select 1, 1;

insert into demo select 1, null;

insert into demo select 1, null;

select n1+n2 from demo;

+-------+

| n1+n2 |

+-------+

|     2 |

|     2 |

|   NULL |

|  NUL L |

+-------+

4 rows in set (0.000 sec)

 

select n1+ifnull(n2,0) from demo;

+-----------------+

| n1+ifnull(n2,0) |

+-----------------+

|               2 |

|               2 |

|                1 |

|               1 |

+-----------------+

4 rows in set (0.000 sec)

(2)oracle的nvl函数

desc demotest;

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------

N1                                                 NUMBER(38)

N2                                                 NUMBER(38)

 

SQL> insert into demotest select 1,1;

insert into demotest select 1,1

                              *

ERROR at line 1:

ORA-00923: FROM keyword not found where expected

 

 

SQL> insert into demotest select 1,1 from dual;

 

1 row created.

 

SQL> insert into demotest select 1,1 from dual;

 

1 row created.

 

SQL> insert into demotest select 1,null from dual;

 

1 row created.

 

SQL> insert into demotest select 1,null from dual;

 

1 row created.

 

SQL> select * from demotest;

 

        N1         N2

---------- ----------

         1          1

         1          1

         1

         1

 

SQL> select n1+n2 from demotest;

 

     N1+N2

----------

         2

         2

 

 

 

SQL> select n1+(nvl(n2,0)) from demotest;

 

N1+(NVL(N2,0))

--------------

             2

             2

             1

             1

 

SQL>

 

3.count(1)和conut(*)和count(主键)和count(普通列名)

的使用

 

假如表没有主键(Primary key), 那么count(1)比count(*)快，

如果有主键的话，那主键作为count的条件时候count(主键)最快

如果你的表只有一个字段的话那count(*)就是最快的。

 

count(column)是返回非空的记录数