oracle公司内部sql培训——les02【过滤和排序数据】

   目标

1. 1. 查询中过滤
   2. 查询中排序

一、使用where字句进行过滤  

    1，  select employee_id,job_id,last_name,department_id
              from employees
              where department_id='90';

 二，字符、数字和日期

• •      数字直接书写
  •      字符和日期需要使用单引号
  •      字符大小写敏感，日期格式敏感（DD-MON-RR）

    例1   select employee_id,job_id,last_name,department_id
         from employees
         where last_name='King';

三，常用操作符

                    算术运算符                                                              其他比较运算                                          

      

eg：

     select job_id,last_name,department_id,salary
             from employees
            where salary<>3000;

 

eg：

select job_id,last_name,department_id,salary
 from employees
where salary between 1000 and 5000;

eg：IN 关键之使用

select job_id,last_name,department_id,salary
from employees
where salary in(3000,5000,3500,2800);

eg：LIke关键字使用：%代表一个或者多个字符，_代表一个字符

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

eg：

select job_id,first_name,department_id,salary
from employees
where first_name like '_h%';

eg：NULL值的判断

select job_id,first_name,department_id,salary,manager_id
from employees
where manager_id is not null;

 

四，逻辑运算（and，or，not）

 eg：and

select job_id,first_name,department_id,salary,manager_id
from employees
where salary>=5000 and manager_id is not null

 

eg：or

select job_id,first_name,department_id,salary,manager_id
from employees
where salary>=5000 or
manager_id is not null;

eg：not

select job_id,first_name,department_id,salary,manager_id
from employees
where department_id not in(60,90);

 

 

五，运算优先级

eg：

select last_name,job_id,salary
from  employees
where job_id='SA_REP'
or   job_id='AD_PRES'
and  SALARY>=1500;

注意：这句表示的条件是： job_id='SA_REP'或者  job_id='AD_PRES' and  SALARY>=1500（两个条件同时满足的情况and的优先级要高于or，所以最后看or在最外层）

select last_name,job_id,salary
from  employees
where （job_id='SA_REP'）
or   （job_id='AD_PRES'
and  SALARY>=1500）;

 

六、排序

 order by （ASC：升序，DESC:降序）

eg：

select last_name,job_id,salary,hire_date
from employees
order by hire_date;

eg；按照别名排序

select last_name,job_id,salary,hire_date hiredate
from employees
order by hiredate;

eg：降序排列（多个排序的时候，默认的排序是升序。如果要降序排列，就需要在每个项下面加一个DESC）

select last_name,job_id,department_id,salary
from employees
order by department_id DESC,salary DESC;