mysql 数据操作 单表查询 where约束 is null in

 

 

需求找出年龄是 81 或者 73 或者 28

 

mysql>  select * from employee where age=81 or age=73 or age=28;
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name      | sex    | age | hire_date  | post      | post_comment | salary   | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
|  2 | yuanhao   | male   |  73 | 2014-07-01 | teacher   | NULL         |  3500.00 |    401 |         1 |
|  3 | liwenzhou | male   |  28 | 2012-11-01 | teacher   | NULL         |  2100.00 |    401 |         1 |
| 11 | 格格      | female |  28 | 2017-01-27 | sale      | NULL         |  4000.33 |    402 |         2 |
| 12 | 张野      | male   |  28 | 2016-03-11 | operation | NULL         | 10000.13 |    403 |         3 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
4 rows in set (0.00 sec)

 

 

用in

某个范围

 

 

mysql>  select * from employee where age in(81,73,28);
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name      | sex    | age | hire_date  | post      | post_comment | salary   | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
|  2 | yuanhao   | male   |  73 | 2014-07-01 | teacher   | NULL         |  3500.00 |    401 |         1 |
|  3 | liwenzhou | male   |  28 | 2012-11-01 | teacher   | NULL         |  2100.00 |    401 |         1 |
| 11 | 格格      | female |  28 | 2017-01-27 | sale      | NULL         |  4000.33 |    402 |         2 |
| 12 | 张野      | male   |  28 | 2016-03-11 | operation | NULL         | 10000.13 |    403 |         3 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
4 rows in set (0.00 sec)

 

 

 

 

 

#5:关键字IN集合查询
    SELECT name,salary FROM employee 
        WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;

    SELECT name,salary FROM employee 
        WHERE salary IN (3000,3500,4000,9000) ;

    SELECT name,salary FROM employee 
        WHERE salary NOT IN (3000,3500,4000,9000) ;

#4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)

mysql> select name,age,post_comment from employee where post_comment is null;
+------------+-----+--------------+
| name       | age | post_comment |
+------------+-----+--------------+
| alex       |  78 | NULL         |
| yuanhao    |  73 | NULL         |
| liwenzhou  |  28 | NULL         |
| jingliyang |  18 | NULL         |
| jinxin     |  18 | NULL         |
| 成龙       |  48 | NULL         |
| 歪歪       |  48 | NULL         |
| 丫丫       |  38 | NULL         |
| 丁丁       |  18 | NULL         |
| 星星       |  18 | NULL         |
| 格格       |  28 | NULL         |
| 张野       |  28 | NULL         |
| 程咬金     |  18 | NULL         |
| 程咬银     |  18 | NULL         |
| 程咬铜     |  18 | NULL         |
| 程咬铁     |  18 | NULL         |
+------------+-----+--------------+
16 rows in set (0.00 sec)

 

 

#4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)
    SELECT name,post_comment FROM employee 
        WHERE post_comment IS NULL;

    SELECT name,post_comment FROM employee 
        WHERE post_comment IS NOT NULL;

    SELECT name,post_comment FROM employee 
        WHERE post_comment=''; 注意''是空字符串，不是null
    ps：
        执行
        update employee set post_comment='' where id=2;
        再用上条查看，就会有结果了