Orcale sql语句

<span style="font-size:14px;">第一节 :基本的sql -SELECT语句
select sysdate from dual;
select * from employees;
select * from departments;
select * from locations;
select employee_id , last_name 
from employees;
select (12+2)*3 from dual ;--42
--数值型的数据可以进行+-运算
select employee_id , salary+1000 , salary*2 from employees;
--日期型的变量只能进行加减，不可以乘除
select employee_id , hire_date ,  hire_date+2 , hire_date +7 from employees; 
--日期1，日期2相差
select sysdate , sysdate +2 , sysdate -3 , sysdate - hire_date from employees;
--关于空值  ,  不为0
select employee_id , last_name , salary , salary*(1+commission_pct), commission_pct
from employees;
select commission_pct + 1 from employees;
--关于列的别名
select employee_id ,last_name  ,salary  from employees;
select employee_id as id,last_name as"wangqi" ,salary "sal" from employees;
select employee_id id , 12*salary "annual"
from employees;
--关于连接符:||
select 123 || 'hello' || 123 from dual;
--xxxx's emal is ***n
--日期和字符只能在但引号中出现
select Last_name ||'‘iemal is' ||  email "员工信息" from employees;
select last_name || '`s email is ' || email "员工的信息"
from employees;
--去除表中的重复行(distinct)
select department_id from employees;
select distinct department_id from employees;
--如下的写法是错误的
--select  department_id ,distinct department_id
--from employee;

--第2节 过滤排序数据 

--1.查询90号部门员工的信息
select * from employees
where department_id =90;

select last_name,salary 
from employees
where salary >= 5000 ;

--字符是区分大小写的
select * 
from employees 
where last_name = 'Grant';

select last_name , hire_date
from employees
--where hire_date = '17-6月-1987';
--where to_char(hire_date,'yyyy/mm/dd')='1987/06/17';
where to_date ('1987/06/17','yyyy/mm/dd') = hire_date;

select last_name , salary 
from employees 
--where salary >= 6000 and salary <=8000;
--where salary <> 6000;

--between ...and...(包含边界)
select last_name , salary
from employees
where salary between 6000 and 8000
and salary != 6000 and salary != 8000 ;

select last_name , salary
from employees
where hire_date > '17-6月-1997';

--in(...,..,...)取in中的值.
select employee_id , department_id
from employees
--where department_id in (60 , 70 , 80);
where department_id = 60 or department_id = 70 or department_id = 80;

--like模糊查询
select employee_id , last_name 
from employees
where last_name like '%a%';--名字中韩字幕a的

select employee_id , hire_date 
from employees
--where hire_date like '%87';
where to_char(hire_date , 'yyyy') = '1987';

select employee_id , last_name
from employees
where last_name like '%a%e%' or last_name like '%a%e%';

select employee_id , last_name 
from employees
where last_name like '%a%e%' or last_name like '%e%a%';

select employee_id , last_name 
from employees
where last_name like '__a%'; ---名字中第三个子符是a的

update employees 
set last_name ='Wh-alen'
where employee_id = 200;

select employee_id , last_name
from employees
where employee_id = 200 ; 

--查询名字中第3个字符是_且第4个字符是a的人
select employee_id , last_name
from employees
where last_name like '__$_a%'escape '$';

select employee_id , last_name ,commission_pct
from employees
where commission_pct is not null;

--排序: order by
select employee_id , last_name , salary
from employees
--order by
where department_id = 90
order by salary desc

select employee_id , last_name , department_id , salary
from employees
order by department_id asc , salary desc , last_name asc;

--可以按照别名排序
select employee_id , last_name ,salary *12 "annual sal"
from employees
order by "annual sal"

--word1
--显示表departments的结构，并查询其中的全部数据
select * from employees
select distinct * from employees;

--显示出表employees的全部列，各个列之间用逗号连接，列头显示成OUT_PUT
select employee_id||','||last_name||','||email||','||hire_date 
OUT_PUT
from employees

--------------------------------------
---第三节 单行函数
select employee_id "wangqi" , last_name
from employees;
--字符函数
--大小写控制 
select lower('Hello Word')from dual;
select * 
from employees
where upper(last_name) = 'GRANT';
--字符空值符
select concat('hello','word')from dual;
select concat (first_name , last_name)from employees;
--数据库中的下角标从一开始
select substr('hellowworld', 2 ,5)from dual;
select instr('helloworld','u')from dual;
select employee_id , last_name , lpad(salary , 10 , '*')
from employees;
select trim (' ' from '   dsjsds  fkf  ')from dual;
select replace('abcdcd' , 'cd' , 'n')from dual;
--数值函数
select round (345.543 , 0) , round(345.543,1) , 
round(345.543,-1) , round (345.543 , -2)from dual;
select trunc(345.543,0) ,trunc(345.543,1) ,trunc(345.543,-2) ,
trunc(345.543,-4) from dual;
select mod(12,5) from dual;
--日期时间
select sysdate from dual;

select employee_id , last_name , sysdate - hire_date "worked_days"
from employees order by "worked_days" desc;

select employee_id , lastName , (sysdate - hire_date)/30/12 "worked_years" ,
 months_between(sysdate - hire_date)/12
 from employees
 order by "worked_years" desc;
 
 select employee_id , hire_date , add_months(hire_date , 2 ) from
 employees;
 
 select next_day (sysdate , '星期日 ') from dual;
 
 select last_day(sysdate) from dual ;
 
--查询出hire_date是当月的倒数第二天来的员工信息

 select last_name , hire_date 
 from employees
 where hire_date  = last_day(hire_date) - 1;
 
 --转换 函数
 --隐式转换
 select '2' + 3 from dual ;
 
 select sysdate , sysdate + '2' from dual ;
 
 select sysdate + 2 from dual;
 
 --￥21,000 + 100;
 --显示转换
 select to_char (sysdate , 'yyyy"年"mm"月"dd"日" hh:mi:ss' )from dual ;
 
 select to_char(12345678.9 , 'L999,999,999.99') from dual ;
 
 select to_char(12345678.9 , '$999,999,999.99')from dual ;
 
 select last_name , to_char(salary , '$999,999,999.99' ) from employees ;
 
 select to_number('$24,000.00','$999,999,999.99')from dual;
 
 select '￥23,453,543.90'+1 from dual ;--不对

 select to_number('￥23,453,543.90' , 'L999,999,999.99')+1 from dual;
 
 ---通用函数
 ---求公司员工的年薪(含commission_pct)
 select employee_id *( 1 + nvl(commission_pct , 0))*12 "annual_sal" from employees
 order by "annual_sal";
 
 ----输出last_name,department_id，当department_id为null时，显示‘没有部门’。
 
 select last_name,nvl(to_char(department_id),'没有部门')from employees;
 
 select last_name,nvl2(commission_pct ,'有奖金' , '没有奖金' )from employees;
 
--查询员工的奖金率，若为空，返回0.01,若不为空，返回实际奖金率+0.015
 
 select last_name , nvl2(commission_pct , commission_pct +0.015 ,0.01 )from 
 employees;
 
 --查询员工的奖金率，若为空，返回0.01,若不为空，返回实际奖金率+0.015
 
 select last_name , nvl2(commission_pct , commission_pct + 0.0034 , 89) 
 from employees ;
 
 --条件表达式
--若部门号为 10, 则打印其工资的 1.1 倍, 
--20 号部门, 则打印其工资的 1.2 倍, 
--30 号部门打印其工资的 1.3 倍数
--若是其他部门，打印其本身的工资

select last_name , salary , 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 last_name , salary , department_id , decode(department_id , 10 , salary * 1.1 ,
                                                                   20 , salary *1.2 ,
                                                                   salary *1.3)"new_sal"
from employees
where department_id in (10,20,30) ;

select employee_id "j", job_id "k" from  employees;

--第4节 多表查询
/*    
  内连接:
      >等值连接 vs不等值连接
      >自连接 vs 非自连接
  外连接:
*/
--迪卡尔集的错误
select e.last_name , e.department_id , d.department_name 
from employees e ,departments d;

select last_name from employees;
select department_name from departments;

--多表查询连接。要求一定要有连接条件。否则会出现迪卡尔集的错误
select e.last_name , e.department_id , d.department_name
from employees e, departments d
where e.department_id =d.department_id ;

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

--last_name  , department_id , department_name , city
select last_name , 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 * from job_grades;

select e.last_name , e.salary , j.grade_level
from employees e , job_grades j 
where e.salary between j.lowest_sal and j.highest_sal;

--自连接
select * from employees;

--XXX worked for XXX
select e1.first_name , e2.first_name
from employees e1 ,employees  e2
where e1.manager_id = e2.employee_id;

select emp.employee_id , emp.first_name , mgr.employee_id , mgr.first_name
from  employees emp , employees mgr
where emp.manager_id = mgr.employee_id;

--外连接
--左外连接:除了输出满足条件的行以外，还输出了左表中不满足条件的数据
select employee_id , last_name , e.department_id , department_name
from employees e , departments d
where e.department_id = d.department_id ;

select employee_id , last_name , e.department_id , department_name
from employees e , departments d
where e.department_id = d.department_id(+) ;

select employee_id
from employees
where department_id is null;

--右外连接：除了输出满足条件的行以外，还输出了右表中不满足条件的数据
select employee_id , last_name , d.department_id , department_name
from employees e , departments d 
where e.department_id(+) = d.department_id;

--如下的写法是错误的。
select employee_id , last_name , d.department_id , department_name
from employees e , departments d
where e.department_id(+) = d.department_id(+);

--sql99语法
--cross join：会出现笛卡尔集的错误
select employee_id , department_name
from employees cross join departments;

--natural join :会将两个表中的相同列名的列都作为连接条件
select employee_id , department_name 
from employees natural join departments;

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

--join ... using..会将指定的列（要求两个表中的列名一致）作为两个表的连接条件

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

--join  ... on...将两个表中指定的列作为连接条件。具有通用性
select employee_id , department_name
from employees e join departments d
on e.department_id = d.department_id

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

--左外联接
select employee_id , department_name
from employees e left outer join departments d
on e.department_id = d.department_id;

--右外联接
select employee_id , department_name 
from employees e right outer join departments d
on e.department_id = d.department_id;

--满外联接
select employee_id , department_name
from employees e full outer join departments d
on e.department_id = d.department_id ;

--测试2
--查询工资大于12000的员工姓名和工资
select last_name , salary
from employees
where salary>12000;

--查询员工号为176的员工的姓名和部门号
select Last_name , department_id 
from employees
where employee_id =176;

--选择工资不在5000到12000的员工的姓名和工资
select last_name , salary
from employees

--where salary < 5000 or salary >12000;
where salary not between 5000 and 1200;

---选择雇用时间在1998-02-01到1998-05-01之间的员工姓名，job_id和雇用时间
select last_name , job_id , hire_date
from employees
--where hire_date between to_date('1998_02_01' , 'yyyy_mm_dd') and
--to_date('1998-05-01' ,'yyyy-mm-dd' );

--选择在20或50号部门工作的员工姓名和部门号
select last_name , department_id 
from employees
where department_id in (20 , 50)
where department_id in (20,50);

--选择在1994年雇用的员工的姓名和雇用时间
select last_name , hire_date 
from employees
--where hire_date like '%94';
where to_char(hire_date , 'yyyy') = '1994';

--选择公司中没有管理者的员工姓名及job_id
select last_name , job_id 
from employees
where manager_id is null ;

--选择公司中有奖金的员工姓名，工资和奖金级别
select last_name , salary , commission_pct
from employees
where commission_pct is not null ;

--选择员工姓名的第三个字母是a的员工姓名
select last_name
from employees
where last_name like '__a%'

--选择姓名中有字母a和e的员工姓名
select last_name 
from employees
where last_name like '%a%e%' or last_name like '%e%a%' ;

--测试
--显示系统时间(注：日期+时间)
select to_char (sysdate , 'yyyy"年"mm"月"dd"日" hh:mi:ss')
from dual ;

--查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
select employee_id , last_name , salary , salary * (1+0.2) "new Salary"
from employees;

--将员工的姓名按首字母排序，并写出姓名的长度（length）
select last_name , length(last_name)
from employees
order by last_name asc ;

--查询各员工的姓名，并显示出各员工在公司工作的月份数
select last_name , round(months_between(sysdate , hire_date))
from employees;

--查询员工的姓名，以及在公司工作的月份数（worked_month），并按月份数降序排列
select last_name , round (months_between(sysdate,hire_date) , 1)
"workded_month"
from employees
order by "workded_month" desc ;

--做一个查询，产生下面的结果
/*
<last_name> earns <salary> monthly but wants <salary*3>
Dream Salary
King earns $24000 monthly but wants $72000
*/
select last_name||' earns '||to_char(salary , '$9999999')||
' monthly but wants ' || to_char(salary *3 , '$999999')
from employees ;

--使用decode函数，按照下面的条件：
/*
job                  grade
AD_PRES            A
ST_MAN             B
IT_PROG             C
SA_REP              D
ST_CLERK           E
产生下面的结果
Last_name	Job_id	Grade
king	AD_PRES	A
*/
select last_name , job_id , decode(job_id , 'A_PRES','A',
                                   'ST_MAN' , 'B',
                                   'IT_MAN','B',
                                   'SA_PROG','C',
                                   'SA_REP','D',
                                   'ST_CLERK','E','W')"Grade"
from employees;

select last_name , job_id ,case job_id when 'AD_PRES' then 'A'
                                       when 'ST_MAN' then 'B'
                                       when 'IT_MAN' then 'C'
                                       when 'SA_REP' then 'D'
                                       when 'ST_CLERK' then 'E'
                                       end "Grade"
from employees;

--显示所有员工的姓名，部门号和部门名称。
select last_name , e.department_id , department_name 
from employees e, departments d
where e.department_id = d.department_id ;

--查询90号部门员工的job_id和90号部门的location_id
select job_id , location_id 
from employees e join departments d
on e.department_id = d.department_id 
where e.department_id = 90 ;

--3.	选择所有有奖金的员工的
--last_name , department_name , location_id , city
select last_name , department_name , d.location_id , city
from employees e ,departments d , locations l 
where  e.department_id = d.department_id and
d.location_id = l.location_id 
and e.commission_pct is not null ;

/*
选择city在Toronto工作的员工的
last_name , job_id , department_id , department_name 
*/
select last_name , job_id , e.department_id , department_name  
from employees e join departments d
on e.department_id = d.department_id
join locations l 
on d.location_id = l.location_id ;

/*
选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，
结果类似于下面的格式
employees	Emp#	manager	Mgr#
kochhar	101	king	100
*/
select e1.last_name "employees" , e1.employee_id "EMP#" ,
e2.last_name "Manger" , e2.employee_id "MGR#"
from employees e1 , employees e2
where e1.manager_id = e2.employee_id ;







</span>