需求找出年龄是 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;
再用上条查看,就会有结果了