oracle 常用sql

-- 在sql中只有别名时用的是双引号
select employee_id,last_name,12*salary as yearSalary from employees;

select last_name from employees;

select department_id from department;

select last_name,department_id from employees;

select last_name from employees;

select employee_id,last_name,department_id from employees;


select employee_id,last_name,department_id from employees where employee_id > 200 ;

-- 日期和字符串是放在的单引号中的在字符串中区分大小写的
select employee_id,last_name from employees where last_name = 'Chen' ;

select employee_id,last_name from employees where hire_date = '7-6月-1994' ;


select last_name from employees where to_char(hire_date,'yyyy-mm-dd') = '1994-06-07' and salary >4000;

select last_name ,salary from employees where


select last_name from employees where salary between  4000 and 7000;


select last_name,salary from employees where salary in (4000 , 7000);
select last_name from employees where last_name like 's' ;

select last_name,commission_pct from employees where commission_pct no is null ;

-- 单行函数

select count(employee_id) from employees;

select lower('ATGUIFU'),UPPER('atguigu'),initcap('Atguigu java') from dual;

select last_name from employees where lower(last_name) = 'king';

select substr('hello world',0,5) from dual

select instr('hello java','L') from dual;

select employee_id last_name lpad(salary,10,' ') from employees;

select trim('h' from 'hsdfsfsfasdh') lpad(salary,10,' ') from employees;

select replace('abcsdbb','b','X') from dual;

-- 数字函数

select round(435.45,2),round(435.45),round(435.45,-2) from dual;

select trunc(435.45,2),trunc(435.45),trunc(435.45,-2) from dual;


select sysdate,sysdate+1 ,sysdate-3,from dual;

select employee_id ,last_name, sysdate-hire_date as workDays from employees;

select add_months(sysdate,2),add_months(sysdate,-3),next_day(sysdate,'星期日') from dual:


select employee_id ,last_name, hire_date from employees where hire_date=last_day-1;

select employee_id, hire_date from employees where hire_date  = to_char(hire_date,'yyyy-mm-dd') = '1997-06-07';


select employee_id, hire_date from employees where to_date('1997-06-25','yyyy-mm-dd') = hire_date;

-- number to char

select to_char(122323444.89,'999,999,9999,.99') from dual;

-- 前缀加当地的货币符号
select to_char(122323444.89,'L999,999,9999,.99') from dual;
-- 前缀加$符号
select to_char(122323444.89,'$999,999,9999,.99') from dual;
-- 用0填充
select to_char(122323444.89,'000,999,9999,.99') from dual;

 select to_number('$001,234,234.89','$000,000,999.99') from dual;

-- 求员工的年薪
select employee_id,last_name,salary*12*(1+commission_pct) from employees;

select employee_id,last_name,nvl(department_id,'没有部门') from employees;

select last_name,commission_pct ,nvl(commission_pct+0.015,commission_pct+0.01) as tt from employees;


select 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  as ss
from employees;

select last_name,department_id,decode(department_id,10 ,salary *1.1
,20 ,salary *1.2
,30 ,salary *1.3
,salary
)   as ss
from employees;

select TO_CHAR(sysdate,'yyyy"年"MM"月"dd"日" HH:mm:ss') from dual;

-- 多表查询


SQL> select employee_id,departments.department_id ,department_name from employee
s ,departments;

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





select q.employee_id,w.department_id ,w.department_name
 from employees as q,departments as w
 where w.department_id = q.department_id;

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

-- 左外连接
select e.last_name,e.department_id,d.department_name from employees e, departments d  where e.department_id = d.department_id(+);
-- 内连接，等值&不等值的
--外连接 左外连接，又外连接
select e.last_name,e.department_id,d.department_name from employees e, departments d  where e.department_id(+) = d.department_id;

select e.last_name,e.department_id,d.department_name,city from employees e left join departments d  on e.department_id = d.department_id ;

-- 全连接
select e.last_name,e.department_id,d.department_name from employees e  full
  join departments d  on e.department_id = d.department_id ;

-- 自连接
select e1.employee_id,e1.last_name,e1.manager_id from employees e join employees e1 on e.manager_id = e.employee_id where e.last_name = 'Chen';
-- 分组函数

select salary from employees group by salary;

select max(salary)from employees group by salary;

 select min(salary),avg(salary),count(1),sum(salary)  from employees ;
 
 -- count 计算的是不为空的值
 select count(commission_pct) from employees;
  select  commission_pct  from employees WHERE commission_pct is  ;
 
 select avg (nvl(commission_pct，0)) from employees;
 
 select avg(salary) from employees group by department_id;
 
 select distinct department_id from employees;
 
 -- group by 后面的字段之间用逗号连接
 select department_id ,job_id,avg(salary) from  employees group by department_id,job_id;
 
  select avg(salary) from employees group by department_id;
 
 
  select department_id ,job_id,avg(salary) from  employees group by department_id;