SQL 基礎之where過濾和 運算符（五）

使用WHERE子句，將不滿足條件的行過濾掉:

WHERE過濾的注意事項：

WHERE子句緊隨FROM子句。

WHERE子句后跟條件表達式

列名、表達式、常量

比較運算符：=, <=, BETWEEN, IN, LIKE, 和 NULL

–邏輯運算符： AND, OR和 NOT

替代變量

DEFINE 和 VERIFY 命令

文字值

WHERE子句后不能跟列別名

語法如下：

SELECT *|{[DISTINCT] column|expression [alias],...}

FROM table

[WHERE condition(s)];

1、比如現在查找一下部門ID為90的部門里面有哪些員工

select last_name,department_id from employees  where department_id = 90;

2、查找部門ID 為100的人員信息，如：姓名、員工編號等

select first_name,employee_id,job_id,department_id  from employees where department_id = 100;

3、當條件為字符和日期要包含在單引號中。

字符大小寫敏感，日期格式敏感。

默認的日期格式是 DD-MON-RR

3.1 查找last_name為Whalen的員工departmen id等信息

select last_name,job_id,department_id from employees where last_name = 'Whalen';

3.2 查找入職日期為07年12月19日的員工first_name 和部門ID

select first_name,department_id,hire_date from employees where hire_date = '19-DEC-07';

4、比較運算符

4.1 查找一下工資小于2800 的員工的名字和部門id號

select last_name,department_id,salary from employees where salary < 2800 order by salary;

4.2 查找一下工資大于等于3000 的員工 的名字和job_id,并采用升序排列

select last_name,job_id,salary from employees where salary>=3000 orader by salary;

4.3 查找一下工資大于等于10000的員工姓名，并按照 xxx 's salary is 10000的格式輸入

select first_name||q'['s salary is ]' || salary as "salary is 10000 of emp" 

from employees 

where salary=10000;

4.4  查找一下工資在8000-10000之間的員工姓名和員工id

select employee_id,first_name,salary from employees where  salary between 8000 and 10000 order by salary;

4.5 查找一下工資為7000、3100、8100、9000、10000的員工名字

select first_name,salary from employees where salary in(7000,3100,8000,9000,10000);

4.6 查找員工名稱為S開頭的job_id 

select first_name,job_id from employees where first_name like 'S%';

4.7 查找員工名稱中帶有s的 job_id

select first_name,job_id from employees where first_name like '%s';

4.8 查找名字第二個字母是o的員工信息

select first_name,job_id from employees where first_name like '_o%';

4.9 查找名字第三個字母為e和a的員工名字

select employee_id,last_name,salary,department_id from employees where manager_id= &mgr_num order by &order_col

4.9.1 查找manager_id 為空的名字

select  first_name,manager_id  from employees where manager_id is null;

4.9.2 查找到04年入職的員工姓名

select last_name,hire_date from employees where hire_date like '%04';

5、邏輯運算符

5.1、查找工資大于10000并且 job_id 含有MAN的員工信息

select first_name,employee_id,salary from employees where salary>=1000 and  job_id like '%MAN%';

5.2、查找工資大于等于10000 或者job_id 含有MAN的員工 的名字和employee_id

select first_name,employee_id,job_id,salary from employees where salary>=10000  or  job_id  like '%MAN%';

5.3 查找job_id  不在'HR_EMP','ST_MAN','ST_CLERK' 這工作id里面的員工名字和job_id

select last_name,job_id from employees  where job_id not in('HR_EMP','ST_MAN','ST_CLERK');

5.4  列出工資不在 5000-12000 范圍的員工的姓名和工資

SQL >select last_name,salary from employees where salary not between 5000 and 12000 order by salary;

LAST_NAME      SALARY

------------------------- ----------

Olson2100

Philtanker2200

Markle2200

Landry2400

Gee       2400

Vargas2500

Patel2500

Colmenares2500

Marlow2500

Sullivan       2500

Perkins        2500

OConnell       2600

Grant2600

Matos2600

Himuro2600

Mikkilineni2700

Seo        2700

Atkinson       2800

Geoni2800

Tobias2800

Jones2800

Rogers2900

Baida2900

Gates2900

Feeney3000

Cabrio3000

Walsh3100

Fleaur3100

Khoo3100

Davies3100

Stiles3200

Nayer3200

Taylor3200

McCain3200

Bissot3300

Mallin3300

Dellinger        3400

Rajs        3500

Dilly        3600

Ladwig3600

Chung3800

Everett         3900

Bell        4000

Bull        4100

Sarchand        4200

Lorentz         4200

Whalen4400

Pataballa        4800

Austin4800

Higgins                 12008

Greenberg       12008

Hartstein               13000

Partners               13500

Russell                14000

De Haan                17000

Kochhar                17000

King               24000

57 rows selected.

6、優先級（可以使用括號改變優先級順序）

1、查找部門為SA_REP或者AD_PRES部門里面工資大于15000的員工名字、job_id、工資等信息

SELECT last_name, job_id, salary FROM employees WHERE job_id = 'SA_REP' OR job_id = 'AD_PRES' AND salary > 15000;

2、查找job_id 為SA_REP或者AD_PRES部門里面工資大于15000的員工姓名、job_id

SELECT last_name, job_id, salary FROM employees WHERE (job_id = 'SA_REP' OR job_id = 'AD_PRES') AND salary > 15000;

3、列出工資不在 5000-12000,部門在 20 或 50 的員工的姓名和工資

select last_name,department_id,salary from employees where salary not between 5000 and 12000 and department_id=20 or department_id=50;

 或

select last_name,department_id,salary from employees where salary not between 5000 and 12000 and department_id in(20,50);