oracle笔记

1.获取唯一记录：distinct

select distinct e.employee_name,s.salary

from employees e,salary s

where e.employee_id=s.salary;

 

2.分组：group by 

select  e.employee_id, e.employee_name, sum(s.salary) total_salary

from employees e, salary s

where e.employee_id=s.employee_id

group by e.employee_id,e.employee_name;

 

3.过滤分组：having 

where 子句可以过滤from 子句所指定的数据源，但是对于group by 子句所产生的分组无效。为了半分组按一定条件进行过滤。having 子句是依附于group by 子句存在而存在的。

select  e.employee_id, e.employee_name, sum(s.salary) total_salary

from employees e, salary s

where e.employee_id=s.employee_id

group by e.employee_id,e.employee_name;

having (sum(s.salary)) > 10000;

 

4.排序：order by （asc 同时也是默认排序方式，降序 desc)

select distinct e.employee_name, s.salary

from employees e, salary s

where e.employee_id=s.employee_id

order by s.salary desc;

 

5.order by 与 group by

select e.employee_name, sum(s.salary) total_salary

from employees e, salary s

where e.employee_id=s.employee_id

group by e.employee_id,e.employee_name

order by total_salary desc;

注：order by 后面不能接除 e.employee_name, total_salary之外的列进行排序。

 

6.order by 与 distinct

order by 与 distinct 关键字同时使用时，也必须遵循一个规则：order by 子句所指定的排序列，必须出现在

select 表达式中。

select e.employee_name

from employees e,salary s

where e.employee_id=s.employee_id

order by s.salary;-------成功

但：

select distinct e.employee_name

from employee e,salary s

where e.employee_id=s.employee_id

order by s.salary;-------失败