Oracle进阶指令

select * from emp;

select empno,ename,job from emp;

select * from emp;

select job from emp;

select distinct job from emp;

select empno,ename,sal*12,sal/30 from emp;

select empno as "编号",ename as "姓名",(sal+200)*12+1500 as "年薪+奖金",sal/30 as "日薪" from emp;

select empno as "编号",ename as "姓名",(sal+200)*12+1500 as "年薪+奖金",sal/30 as "日薪" , '￥' as 货币 from emp;

select '编号是：' ||empno|| '姓名' ||ename|| '年薪是:' ||(sal+200)*12 员工信息 from emp;

select * from emp where deptno=10;

select * from emp where empno in(7788,7369,7499);

select * from emp where sal>1500;

select * from emp where sal<=1200;

select *
from emp
where sal<=1500;

select empno,ename,job,hiredate,sal,comm,deptno
from emp
where ename='KING';

select empno,ename,job,hiredate,sal,comm,deptno
from emp
where ename='KING';

select *
from emp
where sal>=1200 AND sal<=3000;

select *
from emp
where sal>=1200 AND sal<=3000;

select *
from emp
where job='SALESMAN' and sal>1200;

select * from emp where sal in(1200,3000);

select * from emp where sal=1200 or sal=3000;

select *
from emp
where (not job='CLERK' OR sal<=1200);

select *
from emp
where hiredate between '01-1月 -81' and '31-12月 -81';

select *
from emp
where comm is not null;

select *
from emp
where not comm is null;

select *
from emp
where comm is null;

select *
from emp
where comm is null and sal>2000;

select *
from emp
where comm is null or comm<100;

select distinct job
from emp
where comm is null;

select *
from emp
where empno=7369 or empno=7788 or empno=7566;
select *
from emp
where empno in(7369,7788,7566);

select *
from emp
where empno not in(7369,7788,7566);

select *
from emp
where empno in(null,7788,7566);

--select *
--from emp
--where empno not in(null);

select *
from emp
where job like 'S%';
--模糊查询

select *
from emp
where ename like 'S%';

select *
from emp
where ename like '_M%';
--第二个字母为M的数据

select *
from emp
where ename like '%F%';
--包含字母F的数据

select *
from emp
where ename like '______%';
--6个或6个以上的字符

select *
from emp
where sal like '%1%' or hiredate like '%81%';

select *
from emp
where sal like '%%' or ename like '%%' or deptno like '%%' or empno like '%%' or job like '%%' or mgr like '%%';

select *
from emp
where ((deptno=10 and job='MANAGER')
or(deptno=20 and job='CLERK')
or(job not in('MANAGER','CLERK') and sal>2000))
and(ename like '%S%' or ename like '%K%');

select *
from emp
order by sal asc;
--排序升序

select *
from emp
order by sal desc;
--排序降序

select *
from emp
where job='CLERK'
order by sal desc;

select *
from emp
order by sal desc,hiredate asc;

--转化
select employee_id,hire_date
from employees
--where to_char(hire_date,'yyyy-mm-dd')='1994-06-07'
--where to_char(hire_date,'yyyy/mm/dd')='1994-06-07'
where to_date('1994-06-07','yyyy-mm-dd') = hire_date

select employee_id,to_char(hire_date,'yyyy"年"mm"月"dd"日"')
from employees
where to_char(hire_date,'yyyy"年"mm"月"dd"日"')='1994年06月07日'

EMPLOYEE_ID TO_CHAR(HIRE_DATE,'YYYY"年"MM"
----------- ------------------------------
203 1994年06月07日
204 1994年06月07日
205 1994年06月07日
206 1994年06月07日

 

select last_name,commission_pct,nvl2(commission_pct,commission_pct + 0.015,0.01)
from employees

select employee_id,last_name,department_id,
case department_id
when 10 then salary * 1.1
when 20 then salary * 1.2
else salary * 1.3 end new_sal
from employees
where department_id in(10,20,30)

 

select employee_id,last_name,department_id,
case department_id
when 10 then salary * 1.1
when 20 then salary * 1.2
when 30 then salary * 1.3
else salary end new_sal
from employees
where department_id in(10,20,30)

 

select employee_id,last_name,department_id,
decode(department_id,10,salary * 1.1,
20,salary * 1.2,
salary)new_sal
from employees
where department_id in(10,20,30)

 

select to_char(sysdate, 'YYYY"年"MM"月"DD"日" HH:MI:SS')
from dual

 

select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh:mi:ss') from employees

 

select employees.employee_id,employees.department_id,departments.department_name
from employees,departments
where employees.department_id = departments.department_id

select e.employee_id,e.department_id,d.department_name
from employees e,departments d
where e.department_id = d.department_id

select employee_id,e.department_id,department_name
from employees e,departments d
where e.department_id = d.department_id

 

select employee_id,e.department_id,department_name,l.city
from employees e,departments d,locations l
where e.department_id = d.department_id and d.location_id = l.location_id

 

--内连接：等值 & 不等值
select employee_id,last_name,salary,grade_level
from employees e,job_grades j
where e.salary between j.lowest_sal and j.highest_sal

 

--左外连接(左外联接)
select e.employee_id,e.last_name , e.department_id,d.department_name
from employees e,departments d
where e.department_id = d.department_id(+)

--右外连接(右外联接)
select e.employee_id,e.last_name , e.department_id,d.department_name
from employees e,departments d
where e.department_id(+) = d.department_id

 

select employee_id,last_name , department_id,department_name
from employees join departments
using(department_id)

 

select employee_id,last_name , d.department_id,department_name
from employees e join departments d
on e.department_id = d.department_id

 

select employee_id,last_name , d.department_id,department_name,city
from employees e join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id

 

--第一步
select last_name,manager_id
from employees
where lower(last_name) ='chen'
--第二步
select last_name,salary,email
from employees
where employee_id=108
--自连接
--结合
select emp.last_name,manager.last_name,manager.salary,manager.email
from employees emp , employees manager
where emp.manager_id = manager.employee_id and lower(emp.last_name)='chen'

--子查询
select last_name,salary
from employees
where employee_id = (
select manager_id
from employees
where lower(last_name)='chen')

--------------------------------------------------------------------------------------------

 

select last_name,e.department_id,department_name
from employees e , departments d
where e.department_id = d.department_id

select last_name,department_name,d.location_id,city
from employees e join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id
where e.commission_pct is not null

-------------------------------------------------------------------------------
select employee_id ,
first_name ,
last_name ,
email ,
phone_number ,
to_char(hire_date,'yyyy-mm-dd'),
job_id,
to_char(salary,'L999,999.99'),
grade_level,
to_char((salary+nvl(commission_pct,0))*12,'L999,999.99') as new_salary,
nvl(commission_pct,0) ,
e.department_id ,
department_name,
l.city
from employees e , departments d , locations l , job_grades j
where e.department_id = d.department_id(+)
and d.location_id = l.location_id(+)
and e.salary between j.lowest_sal and j.highest_sal(+)

----------------------------------------------------------------------------------

select department_id,avg(salary)
from employees
group by department_id

 

--------------------------------------------------------------------------------------
select last_name,salary
from employees
where salary > (select salary
from employees
where last_name='Abel')

-----------------------------------------------------------------------------------
select last_name,salary
from employees
where employee_id = (
select manager_id
from employees
where lower(last_name)='chen')

 

select last_name,job_id,salary
from employees
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 employees
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 < any(
select salary
from employees
where job_id = 'IT_PROG'
) and job_id <> 'IT_PROG'

 

select employee_id , last_name , job_id , salary
from employees
where salary < all(
select salary
from employees
where job_id = 'IT_PROG'
) and job_id <> 'IT_PROG'

 

 

--查询各部门的平均工资
--select avg(salary)
--from employees
--group by department_id

--查询公司中的各部门的平均工资中最少
--select min(avg(salary))
--from employees
--group by department_id

--哪个部门的平均工资=2的结果
select department_id
from employees
having avg(salary)=(
select min(avg(salary))
from employees
group by department_id
)
group by department_id

 

select *
from departments
where department_id=(
select department_id
from employees
having avg(salary)=(
select min(avg(salary))
from employees
group by department_id
)
group by department_id
)

 

select d.* ,(select avg(salary) from employees where department_id = d.department_id)
from departments d
where department_id=(
select department_id
from employees
having avg(salary)=(
select min(avg(salary))
from employees
group by department_id
)
group by department_id
)

 

 

 

select *
from jobs
where job_id in (
select job_id
from employees
having avg(salary) = (
select max(avg(salary))
from employees
group by job_id
)
group by job_id
)

 

select department_id,avg(salary)
from employees
group by department_id
having avg(salary) > (
select avg(salary)
from employees
)

 

select *
from employees
where employee_id in (
select manager_id
from employees
)

 

select last_name,hire_date
from employees
where department_id = (
select department_id
from employees
where lower(last_name) = 'zlotkey'
)

select last_name,hire_date
from employees
where department_id = (
select department_id
from employees
where lower(last_name) = 'zlotkey'
)
and lower(last_name) <> 'zlotkey'

 

====================================================================================

create table journey_emp(
id number(10),
name varchar2(30),
salary number(10,2),
hire_date date
)

--创建表的第二种方式
--依托于现有的表
create table emp1
as
select employee_id id,last_name name,hire_date,salary
from employees

--过滤
create table emp1
as
select employee_id id,last_name name,hire_date,salary
from employees
where department_id = 80

--不要数据将where设置为空
create table emp1
as
select employee_id id,last_name name,hire_date,salary
from employees
where department_id = 800

 

create table emp5(
id number(7),
fist_name varchar2(25),
last_name varchar2(25),
dept_id number(7)
)

alter table emp5
modify(last_name varchar2(50))

alter table dept
add(test_column number(10))

--在表emp5中将列test_column设置成不可用，之后删除
alter table emp5
set unused column test_column

alter table emp5
drop unused columns

--直接删除表emp中的列dept_id
alter table emp
drop column dept_id

 

--插入
insert into employees2
values(10001,'journey',sysdate,10000)

insert into employees2
values(1002,'jim',to_date('1993-02-24' , 'yyyy-mm-dd'),20000)

--把已有的一些数据导入新表中
insert into emp1(employee_id,hire_date,last_name,salary)
select employee_id ,hire_date,last_name,salary
from employees
where department_id = 80

 

--创建脚本
insert into emp1(employee_id, last_name ,salary,hire_date)
values(&id , '&name' , '&salary','&hire_date')

--修改
update employees2
set salary = 12000
where employee_id = 179

--写死rollback回不去
commit;

--撤销
rollback;

 

-------------------------------------------------
update employees2
set job_id = (
select job_id
from employees2
where employee_id = 205
),
salary = (
select salary
from employees2
where employee_id = 205
)
where employee_id = 114

select * from employees2 where employee_id = 114

 

============================================================================================

update employees
set salary=(
select max(salary)
from employees
where department_id = (
select department_id
from employees
where employee_id = 108
)
),job_id = (
select job_id
from employees
group by job_id
having avg(salary) = (
select min(avg(salary))
from employees
group by job_id
)
)
where employee_id = 108