ORACLE SQL大全
简单查询 1、选择所有 select * from table;2、选择部分列 select column1,column2 from table;
3、使用算数表达式 select salary+300 from emlpoyees；
4、使用 ()select 12*(salary+20) from emlpoyees；
5、使用列别名 select first_name Fname from employees；
6、使用字符串连接符 select first_name || last_name from employees;
7、过滤重复select distinct department_id from employees；
8、where子句查询数据 select employee_id,last_name,job_id from employees where department_id=90；
9、order by 子句排序 select last_name from employees order by hire_date；
10、group 分组查询
select avg (salary),
max (salary),
min (salary),
sum (salary),
count (*),
count (commission_pct)
from employees where job_id like ‘%REP%’；
select department_id,
avg (salary)
from employees group by department_id;
select department_id,
max(salary)
from employees
group by department_id having max(salary) >10000;
select job_id ,
sum(salary) payroll
from employees
where job_id not like '%rep%'
group by job_id having sum(salary)>13000
order by sum(salary);
11、子查询
单行子查询
select last_name
from employees
where salary>(select salary from employees where last_name ='Abel');
select last_name ,job_id,salary
from employess
where job_id=(select job_id
from employees
where employee_id=141)
and salary>(select salary
from employees
where employee_id=143);
select last_name,job_id,salary
from employess
where salary= (select min(salary) from employees);
select department_id,min(salary)
from employees
group by department_id
having min(salary)>(select min(salary) from employees where department_id=50);
多行子查询
select employee_id,last_name,job_id,salary
from employees
where salary
页:
[1]