Oracle WHERE & AND

 如果想取得在emp table內 job為SALESMAN的資料

query如下:

SELECT *

FROM emp

WHERE job = 'SALESMAN'

可得到以下內容

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7499	ALLEN	SALESMAN	7698	1981/02/20	1600	300	30
7521	WARD	SALESMAN	7698	1981/02/22	1250	500	30
7654	MARTIN	SALESMAN	7698	1981/09/28	1250	1400	30
7844	TURNER	SALESMAN	7698	1981/09/08	1500	0	30

提醒,column, table 名稱不分大小寫

但資料內容是有分大小寫的

所以如果你輸入以下query

SELECT *

FROM emp

WHERE job= 'salesman'

會找不到資料

如果想取得在emp table內 job不為SALESMAN的資料

query:

SELECT *

FROM emp

WHERE job != 'SALESMAN'

會得到以下結果

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7839	KING	PRESIDENT	-	1981/11/17	5000	-	10
7698	BLAKE	MANAGER	7839	1981/05/01	2850	-	30
7782	CLARK	MANAGER	7839	1981/06/09	2450	-	10
7566	JONES	MANAGER	7839	1981/04/02	2975	-	20
7788	SCOTT	ANALYST	7566	1982/12/09	3000	-	20
7902	FORD	ANALYST	7566	1981/12/03	3000	-	20
7369	SMITH	CLERK	7902	1980/12/17	800	-	20
7876	ADAMS	CLERK	7788	1983/01/12	1100	-	20
7900	JAMES	CLERK	7698	1981/12/03	950	-	30
7934	MILLER	CLERK	7782	1982/01/23	1300	-	10

如果想取得在emp table內 job為SALESMAN的資料以及sal大於等於1500的資料

query:

SELECT *

FROM emp

WHERE job= 'SALESMAN'

AND sal >= 1500

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7499	ALLEN	SALESMAN	7698	1981/02/20	1600	300	30
7844	TURNER	SALESMAN	7698	1981/09/08	1500	0	30

也可以比較column的值,

例如我想找comm > sal 的資料

query:

SELECT *

FROM emp

WHERE comm > sal

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7654	MARTIN	SALESMAN	7698	1981/09/28	1250	1400	30

小結:

查詢的順序

1. SELECT

2. FROM

3. WHERE

4. AND

...