文章目录
1. 回顾
在上一篇博客中【MySQL】五、条件查询(where,between … and 关键字)
,我们知道,sql查询语句的语法为:
select 字段1, 字段2,..,字段n from 表名 where 条件语句;
2. 案例
2.1 找出哪些人没有津贴
我们先查看一下员工表结构;
desc emp; # 查看表的详细结构;
字段COMM为津贴
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO | int(4) | NO | PRI | NULL | |
| ENAME | varchar(10) | YES | | NULL | |
| JOB | varchar(9) | YES | | NULL | |
| MGR | int(4) | YES | | NULL | |
| HIREDATE | date | YES | | NULL | |
| SAL | double(7,2) | YES | | NULL | |
| COMM | double(7,2) | YES | | NULL | | # 津贴
| DEPTNO | int(2) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
只查询所有津贴;
select comm from emp ;
mysql> select comm from emp ;
+---------+
| comm |
+---------+
| NULL |
| 300.00 |
| 500.00 |
| NULL |
| 1400.00 |
| NULL |
| NULL |
| NULL |
| NULL |
| 0.00 |
| NULL |
| NULL |
| NULL |
| NULL |
+---------+
14 rows in set (0.00 sec)
我们发现津贴有的为NULL,有的为0.0;
在数据库中NULL不是一个值,它表示什么也没有,为空。空不是一个值,不能用等号衡量。
必须使用 is null 或者 is not null;
因此:
select ename, comm from emp where comm = 0 or comm is null;
查询结果:
+--------+---------+------+
| ename | sal | comm |
+--------+---------+------+
| SMITH | 800.00 | NULL |
| JONES | 2975.00 | NULL |
| BLAKE | 2850.00 | NULL |
| CLARK | 2450.00 | NULL |
| SCOTT | 3000.00 | NULL |
| KING | 5000.00 | NULL |
| TURNER | 1500.00 | 0.00 |
| ADAMS | 1100.00 | NULL |
| JAMES | 950.00 | NULL |
| FORD | 3000.00 | NULL |
| MILLER | 1300.00 | NULL |
+--------+---------+------+
11 rows in set (0.00 sec)
2.2 找出哪些人有津贴
select ename,sal, comm from emp where comm is not null;
查询结果:
+--------+---------+---------+
| ename | sal | comm |
+--------+---------+---------+
| ALLEN | 1600.00 | 300.00 |
| WARD | 1250.00 | 500.00 |
| MARTIN | 1250.00 | 1400.00 |
| TURNER | 1500.00 | 0.00 |
+--------+---------+---------+
4 rows in set (0.00 sec)
2.3 找出工作岗位是MANAGER 和 SALESMAN的员工
select ename, job from emp where job = 'manager' or job = 'salesman';
查询结果:
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)
注意: 这里要使用or关键字而不是and, 使用and表示岗位既是MANAGER,又是SALESMAN。
2.4 找出薪资大于3000并且部门编号是20或者30的部门员工(and 和 or 联合使用)
and 的优先级是高于or的,因此需要将后面带or的条件语句使用括号
select ename, sal, deptno from emp where sal > 3000 and (deptno = 20 or 30);
查询结果:
+-------+---------+--------+
| ename | sal | deptno |
+-------+---------+--------+
| KING | 5000.00 | 10 |
+-------+---------+--------+
1 row in set (0.00 sec)
如果不使用括号,则:
sal > 3000 and deptno 会配对,导致部门编号为30的员工都被查询出来。
select ename, sal, deptno from emp where sal > 3000 and deptno = 20 or 30; # 错误写法
+--------+---------+--------+
| ename | sal | deptno |
+--------+---------+--------+
| SMITH | 800.00 | 20 |
| ALLEN | 1600.00 | 30 |
| WARD | 1250.00 | 30 |
| JONES | 2975.00 | 20 |
| MARTIN | 1250.00 | 30 |
| BLAKE | 2850.00 | 30 |
| CLARK | 2450.00 | 10 |
| SCOTT | 3000.00 | 20 |
| KING | 5000.00 | 10 |
| TURNER | 1500.00 | 30 |
| ADAMS | 1100.00 | 20 |
| JAMES | 950.00 | 30 |
| FORD | 3000.00 | 20 |
| MILLER | 1300.00 | 10 |
+--------+---------+--------+
14 rows in set (0.00 sec)
注意:
当运算符的优先级不确定时,请加小括号!