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;
        再用上条查看,就会有结果了
 
 

转载于:https://www.cnblogs.com/mingerlcm/p/9871991.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值