过滤和排序数据1

过滤和排序数据

过滤

使用WHERE 子句，将不满足条件的行过滤掉。

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

FROM table

[WHERE condition(s)];

WHERE 子句紧随 FROM 子句。

 

字符和日期

字符和日期要包含在单引号中。

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

默认的日期格式是 DD-MON-RR。

SELECT last_name, job_id, department_id

FROM   employees

WHERE  last_name = 'Whalen';

 

比较运算

SELECT last_name, salary

FROM   employees

WHERE  salary <= 3000;

BETWEEN

 

使用 BETWEEN 运算来显示在一个区间内的值。

SELECT last_name, salary

FROM   employees

WHERE  salary BETWEEN 2500 AND 3500;

 

IN

SELECT employee_id, last_name, salary, manager_id

FROM   employees

WHERE  manager_id IN (100, 101, 201);

 

AND

SELECT employee_id, last_name, job_id, salary

FROM   employees

WHERE  salary >=10000

AND    job_id LIKE '%MAN%';

 

 

NOT

SELECT last_name, job_id

FROM   emp

WHERE  job_id

       NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');