条件查询

最新推荐文章于 2024-05-20 16:29:28 发布

半醉看夕阳

最新推荐文章于 2024-05-20 16:29:28 发布

阅读量944

点赞数

分类专栏： Mysql

本文链接：https://blog.csdn.net/bbb695480667/article/details/55806732

版权

Mysql 专栏收录该内容

16 篇文章 2 订阅

订阅专栏

条件查询需要用到where语句，where必须放到from语句表的后面

支持如下运算符

运算符			说明
=			等于
<>或!=			不等于
<			小于
<=			小于等于
>			大于
>=			大于等于
between … and ….	两个值之间,等同于 >= and <=
is null			为null（is not null 不为空）
and			并且
or			或者
in			包含，相当于多个or（not in不在这个范围中）
not			not可以取非，主要用在is 或in中
like			like称为模糊查询，支持%或下划线匹配，%匹配任意个字符下划线，一个下划线只匹配一个字符

一等号操作符

查询月薪为5000的员工

mysql> select empno, ename, sal from emp where sal = 5000;
+-------+-------+---------+
| empno | ename | sal     |
+-------+-------+---------+
|  7839 | KING  | 5000.00 |
+-------+-------+---------+

二<>操作符

查询月薪不等于5000的员工

mysql> select empno, ename, sal from emp where sal <> 5000;
+-------+--------+---------+
| empno | ename  | sal     |
+-------+--------+---------+
|  7369 | SMITH  |  800.00 |
|  7499 | ALLEN  | 1600.00 |
|  7521 | WARD   | 1250.00 |
|  7566 | JONES  | 2975.00 |
|  7654 | MARTIN | 1250.00 |
|  7698 | BLAKE  | 2850.00 |
|  7782 | CLARK  | 2450.00 |
|  7788 | SCOTT  | 3000.00 |
|  7844 | TURNER | 1500.00 |
|  7876 | ADAMS  | 1100.00 |
|  7900 | JAMES  |  950.00 |
|  7902 | FORD   | 3000.00 |
|  7934 | MILLER | 1300.00 |
+-------+--------+---------+

也可以采用!=

mysql> select empno, ename, sal from emp where sal != 5000;
+-------+--------+---------+
| empno | ename  | sal     |
+-------+--------+---------+
|  7369 | SMITH  |  800.00 |
|  7499 | ALLEN  | 1600.00 |
|  7521 | WARD   | 1250.00 |
|  7566 | JONES  | 2975.00 |
|  7654 | MARTIN | 1250.00 |
|  7698 | BLAKE  | 2850.00 |
|  7782 | CLARK  | 2450.00 |
|  7788 | SCOTT  | 3000.00 |
|  7844 | TURNER | 1500.00 |
|  7876 | ADAMS  | 1100.00 |
|  7900 | JAMES  |  950.00 |
|  7902 | FORD   | 3000.00 |
|  7934 | MILLER | 1300.00 |
+-------+--------+---------+

三between...and...运算符

查询月薪在1600到3000的员工

mysql> select empno, ename, sal from emp where sal between 1600 and 3000;
+-------+-------+---------+
| empno | ename | sal     |
+-------+-------+---------+
|  7499 | ALLEN | 1600.00 |
|  7566 | JONES | 2975.00 |
|  7698 | BLAKE | 2850.00 |
|  7782 | CLARK | 2450.00 |
|  7788 | SCOTT | 3000.00 |
|  7902 | FORD  | 3000.00 |
+-------+-------+---------+

between...and...是包含最小值和最大值的。

也可以用<=加>=运算符

mysql> select empno, ename, sal from emp where sal >=1600 and sal <=3000;
+-------+-------+---------+
| empno | ename | sal     |
+-------+-------+---------+
|  7499 | ALLEN | 1600.00 |
|  7566 | JONES | 2975.00 |
|  7698 | BLAKE | 2850.00 |
|  7782 | CLARK | 2450.00 |
|  7788 | SCOTT | 3000.00 |
|  7902 | FORD  | 3000.00 |
+-------+-------+---------+

四is null运算符

查询津贴为空的员工

mysql> select empno, ename, comm from emp where comm is null;
+-------+--------+------+
| empno | ename  | comm |
+-------+--------+------+
|  7369 | SMITH  | NULL |
|  7566 | JONES  | NULL |
|  7698 | BLAKE  | NULL |
|  7782 | CLARK  | NULL |
|  7788 | SCOTT  | NULL |
|  7839 | KING   | NULL |
|  7876 | ADAMS  | NULL |
|  7900 | JAMES  | NULL |
|  7902 | FORD   | NULL |
|  7934 | MILLER | NULL |
+-------+--------+------+

五and运算符

and表示并且的意思，即and前的条件后and后的条件都要满足。

查询工作岗位为MANAGER,薪水大于2500的员工

mysql> select empno, ename, job, sal from emp where job = 'manager' and sal > 2500;
+-------+-------+---------+---------+
| empno | ename | job     | sal     |
+-------+-------+---------+---------+
|  7566 | JONES | MANAGER | 2975.00 |
|  7698 | BLAKE | MANAGER | 2850.00 |
+-------+-------+---------+---------+

六or运算符

or表示或者的意思，只要满足条件即可

查询出job为manager或者job为salesman的员工

mysql> select empno, ename, job from emp where job = 'manager' or job = 'salesman';
+-------+--------+----------+
| empno | ename  | job      |
+-------+--------+----------+
|  7499 | ALLEN  | SALESMAN |
|  7521 | WARD   | SALESMAN |
|  7566 | JONES  | MANAGER  |
|  7654 | MARTIN | SALESMAN |
|  7698 | BLAKE  | MANAGER  |
|  7782 | CLARK  | MANAGER  |
|  7844 | TURNER | SALESMAN |
+-------+--------+----------+

七in运算符

in表示包含的意思，完全可以采用or来表示，采用in会更简洁一些

查询出job为manager或者job为salesman的员工

mysql> select empno, ename, job from emp where job in('manager', 'salesman');
+-------+--------+----------+
| empno | ename  | job      |
+-------+--------+----------+
|  7499 | ALLEN  | SALESMAN |
|  7521 | WARD   | SALESMAN |
|  7566 | JONES  | MANAGER  |
|  7654 | MARTIN | SALESMAN |
|  7698 | BLAKE  | MANAGER  |
|  7782 | CLARK  | MANAGER  |
|  7844 | TURNER | SALESMAN |
+-------+--------+----------+

八not运算符

查询出津贴不为null的所有员工

mysql> select empno, ename from emp where comm is not null;
+-------+--------+
| empno | ename  |
+-------+--------+
|  7499 | ALLEN  |
|  7521 | WARD   |
|  7654 | MARTIN |
|  7844 | TURNER |
+-------+--------+

注：not与in连用，表示不在什么中时，不能自动去空值(NULL),需要程序员手动去空值，但in为自动去掉空值。

九like运算符

Like可以实现模糊查询，like支持%和下划线匹配

查询姓名以M开头所有的员工

mysql> select * from emp where ename like 'm%';
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7934 | MILLER | CLERK    | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+----------+------+------------+---------+---------+--------+

查询姓名以N结尾的所有的员工

mysql> select * from emp where ename like '%N';
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+

查询姓名中包含O的所有的员工

mysql> select * from emp where ename like '%o%';
+-------+-------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB     | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+---------+------+------------+---------+------+--------+
|  7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7902 | FORD  | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+---------+------+------------+---------+------+--------+

%可以表示0到n个字符

查询姓名中第二个字符为A的所有员工

mysql> select * from emp where ename like '_A%';
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7900 | JAMES  | CLERK    | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+

下划线只匹配一个字符

like中的表达式必须放在引号中。

十表达式的优先级

查询薪水大于1800，并且部门代码为20或30的员工（错误的写法）

mysql> select * from emp where sal > 1800 and deptno = 20 or deptno = 30;
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER  | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER  | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7788 | SCOTT  | ANALYST  | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7900 | JAMES  | CLERK    | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST  | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
+-------+--------+----------+------+------------+---------+---------+--------+

以上输出不是预期结果，薪水小于1800的数据也被查询上来了，原因是表达式的优先级导致的，首先过滤sal > 1800 and deptno = 20，然后再将deptno = 30员工合并过来，所以是不对的。

以下为正确写法

mysql> select * from emp where sal > 1800 and (deptno = 20 or deptno = 30);
+-------+-------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB     | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+---------+------+------------+---------+------+--------+
|  7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7902 | FORD  | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+---------+------+------------+---------+------+--------+

注：没必要特别去记优先级，打括号即可很好的解决问题。