Oracle常用代码总结(3)

java开发中的总结 大家可以看看哦

;(含有附加连接的连接)


分组函数：
select avg(salary),max(salary),min(salary),sum(salary)from guyuan e where job_id like '开发%';（四个函数）
select max(hiredate),min(hiredate) from guyuan;(日期最大值和最小值)
select count(*) from guyuan;（查询总记录数）
select count(*) from guyuan where job_id = '开发人员';（按条件查询记录数）
select count(commission) from guyuan;（查询某列不为空记录条数）
select count(commission) from guyuan where department_id = 20;（查询某列不为空且满足条件2的记录条数）
select count(distinct department_id) from guyuan;（不重复的记录条数查询）
select avg(nvl(commission,0)) from guyuan;（不忽略空值的计算）
select department_id,avg(salary) from guyuan group by department_id;(组函数分组查询)
select department_id,job_id,avg(salary) from guyuan group by department_id,job_id;（多条件组函数+分组查询）
select department_id,max(salary) from guyuan group by department_id having max(salary)>6000;(having字句过滤分组)
select job_id,sum(salary) from guyuan where job_id not like '开发%' group by job_id having sum(salary)>10000 order by sum(salary);(having字句+分组+排序)
select max(avg(salary)) from guyuan group by department_id;(嵌套组函数+分组)

子查询：
select fast_name,job_id,department_id from guyuan where job_id = (select job_id from guyuan where employee_id = 1005) and salary >
(select salary from guyuan where employee_id = 1006) （and连接两个子查询条件）
select fast_name,job_id,department_id,salary from guyuan where salary = (select min(salary) from guyuan)（子查询嵌套分组函数）
select department_id,min(salary) from guyuan group by department_id having min(salary) > (select min(salary) from guyuan where department_id = 30)（分组+子函数）
select fast_name,job_id from guyuan where job_id = (select job_id from guyuan where fast_name = 'Carry')（子查询）
select employee_id,fast_name,job_id,salary from guyuan where salary < all(select salary from guyuan where job_id = '项目经理' )and job_id <> '项目经理'（all-全部子查询）

变量：
select employee_id,fast_name,department_id,job_id,manager_id from guyuan where employee_id = &employee_id;（输入变量进行查询）
select * from guyuan where job_id = '&工作';（查找指定工作）
select * from guyuan &工资 where &条件 order by &排序列;(多条件查询)
select employee_id,fast_name,&&column_name from guyuan order by &column_name;（列名控制查询）
set verify on（换行样式）
select employee_id,fast_name,salary,department_id from guyuan where employee_id = &employee_num

columm操作:
column Emloyee_name heading 'first_name';修改列名
column sal justify left form
at $99,999,999.00;货币显示符号
alter table guyuan add (age number(4)) (增加一列)
alter table employees drop (phone_number,hire_date);(删除列)
alter table