AND 与 (需满足AND两边的条件,才可返回符合条件的某些值)
OR 或 (满足OR一边的条件,即可返回符合单条件的某些值)
NOT 非 (满足NOT的条件都不返回,返回条件之外的某些值)
例:查询部门编号为20并且工资大于等于2000的员工信息;
SQL@itkaifang> SELECT empno,ename,sal,deptno FROM emp WHERE deptno=20 AND sal>=2000; EMPNO ENAME SAL DEPTNO ---------- ---------- ---------- ---------- 7566 JONES 2975 20 7788 SCOTT 3000 20 7902 FORD 3000 20 SQL@itkaifang>
例:查询部门编号为30或工资大于1000的员工信息;
SQL@itkaifang> SELECT empno,ename,sal,deptno FROM emp WHERE deptno=30 OR sal>1000; EMPNO ENAME SAL DEPTNO ---------- ---------- ---------- ---------- 7499 ALLEN 1600 30 7521 WARD 1250 30 7566 JONES 2975 20 7654 MARTIN 1250 30 7698 BLAKE 2850 30 7782 CLARK 2450 10 7788 SCOTT 3000 20 7839 KING 5000 10 7844 TURNER 1500 30 7876 ADAMS 1100 20 7900 JAMES 950 30 7902 FORD 3000 20 7934 MILLER 1300 10 8888 QIN_BING 9999 14 rows selected. SQL@itkaifang>
例:查询工种为“CLERK”、“ANALYST”以外的所有员工信息;
SQL@itkaifang> SELECT empno,ename,sal,job,deptno FROM emp WHERE job NOT IN ('CLERK','ANALYST'); EMPNO ENAME SAL JOB DEPTNO ---------- ---------- ---------- --------- ---------- 7499 ALLEN 1600 SALESMAN 30 7521 WARD 1250 SALESMAN 30 7566 JONES 2975 MANAGER 20 7654 MARTIN 1250 SALESMAN 30 7698 BLAKE 2850 MANAGER 30 7782 CLARK 2450 MANAGER 10 7839 KING 5000 PRESIDENT 10 7844 TURNER 1500 SALESMAN 30 8 rows selected. SQL@itkaifang>