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;--关于空值  ,  不为0select employee_id , last_name , salary , salary*(1+commission_pct), commission_pctfrom 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 employeeswhere department_id =90;select last_name,salary from employeeswhere salary >= 5000 ;--字符是区分大小写的select * from employees where last_name = 'Grant';select last_name , hire_datefrom 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 , salaryfrom employeeswhere salary between 6000 and 8000and salary != 6000 and salary != 8000 ;select last_name , salaryfrom employeeswhere hire_date > '17-6月-1997';--in(...,..,...)取in中的值.select employee_id , department_idfrom 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 employeeswhere 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_namefrom employeeswhere last_name like '%a%e%' or last_name like '%a%e%';select employee_id , last_name from employeeswhere last_name like '%a%e%' or last_name like '%e%a%';select employee_id , last_name from employeeswhere last_name like '__a%'; ---名字中第三个子符是a的update employees set last_name ='Wh-alen'where employee_id = 200;select employee_id , last_namefrom employeeswhere employee_id = 200 ; --查询名字中第3个字符是_且第4个字符是a的人select employee_id , last_namefrom employeeswhere last_name like '__$_a%'escape '$';select employee_id , last_name ,commission_pctfrom employeeswhere commission_pct is not null;--排序: order byselect employee_id , last_name , salaryfrom employees--order bywhere department_id = 90order by salary descselect employee_id , last_name , department_id , salaryfrom employeesorder by department_id asc , salary desc , last_name asc;--可以按照别名排序select employee_id , last_name ,salary *12 "annual sal"from employeesorder by "annual sal"--word1--显示表departments的结构，并查询其中的全部数据select * from employeesselect distinct * from employees;--显示出表employees的全部列，各个列之间用逗号连接，列头显示成OUT_PUTselect employee_id||','||last_name||','||email||','||hire_date OUT_PUTfrom employees-----------------------------------------第三节 单行函数select employee_id "wangqi" , last_namefrom employees;--字符函数--大小写控制 select lower('Hello Word')from dual;select * from employeeswhere 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 employeeswhere 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 employeeswhere 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_namefrom employees e, departments dwhere e.department_id =d.department_id ;select last_name , e.department_id , department_namefrom employees e , departments d where e.department_id = d.department_id;--last_name  , department_id , department_name , cityselect last_name , e.department_id , department_name , l.cityfrom 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_levelfrom employees e , job_grades j where e.salary between j.lowest_sal and j.highest_sal;--自连接select * from employees;--XXX worked for XXXselect e1.first_name , e2.first_namefrom employees e1 ,employees  e2where e1.manager_id = e2.employee_id;select emp.employee_id , emp.first_name , mgr.employee_id , mgr.first_namefrom  employees emp , employees mgrwhere emp.manager_id = mgr.employee_id;--外连接--左外连接:除了输出满足条件的行以外，还输出了左表中不满足条件的数据select employee_id , last_name , e.department_id , department_namefrom employees e , departments dwhere e.department_id = d.department_id ;select employee_id , last_name , e.department_id , department_namefrom employees e , departments dwhere e.department_id = d.department_id(+) ;select employee_idfrom employeeswhere department_id is null;--右外连接：除了输出满足条件的行以外，还输出了右表中不满足条件的数据select employee_id , last_name , d.department_id , department_namefrom employees e , departments d where e.department_id(+) = d.department_id;--如下的写法是错误的。select employee_id , last_name , d.department_id , department_namefrom employees e , departments dwhere e.department_id(+) = d.department_id(+);--sql99语法--cross join：会出现笛卡尔集的错误select employee_id , department_namefrom employees cross join departments;--natural join :会将两个表中的相同列名的列都作为连接条件select employee_id , department_name from employees natural join departments;select employee_id , department_namefrom employees e , departments dwhere e.department_id = d.department_idand e.manager_id = d.manager_id;--join ... using..会将指定的列（要求两个表中的列名一致）作为两个表的连接条件select employee_id , department_namefrom employees join departmentsusing (department_id);--join  ... on...将两个表中指定的列作为连接条件。具有通用性select employee_id , department_namefrom employees e join departments don e.department_id = d.department_idselect employee_id , department_namefrom employees e join departments don e.department_id = d.department_idjoin locations lon d.location_id = l.location_id;--左外联接select employee_id , department_namefrom employees e left outer join departments don e.department_id = d.department_id;--右外联接select employee_id , department_name from employees e right outer join departments don e.department_id = d.department_id;--满外联接select employee_id , department_namefrom employees e full outer join departments don e.department_id = d.department_id ;--测试2--查询工资大于12000的员工姓名和工资select last_name , salaryfrom employeeswhere salary>12000;--查询员工号为176的员工的姓名和部门号select Last_name , department_id from employeeswhere employee_id =176;--选择工资不在5000到12000的员工的姓名和工资select last_name , salaryfrom 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_datefrom 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 employeeswhere 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_idselect last_name , job_id from employeeswhere manager_id is null ;--选择公司中有奖金的员工姓名，工资和奖金级别select last_name , salary , commission_pctfrom employeeswhere commission_pct is not null ;--选择员工姓名的第三个字母是a的员工姓名select last_namefrom employeeswhere last_name like '__a%'--选择姓名中有字母a和e的员工姓名select last_name from employeeswhere 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 employeesorder 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 employeesorder by "workded_month" desc ;--做一个查询，产生下面的结果/*<last_name> earns <salary> monthly but wants <salary*3>Dream SalaryKing 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                  gradeAD_PRES            AST_MAN             BIT_PROG             CSA_REP              DST_CLERK           E产生下面的结果Last_nameJob_idGradekingAD_PRESA*/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 dwhere e.department_id = d.department_id ;--查询90号部门员工的job_id和90号部门的location_idselect job_id , location_id from employees e join departments don e.department_id = d.department_id where e.department_id = 90 ;--3.选择所有有奖金的员工的--last_name , department_name , location_id , cityselect last_name , department_name , d.location_id , cityfrom employees e ,departments d , locations l where  e.department_id = d.department_id andd.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 don e.department_id = d.department_idjoin locations l on d.location_id = l.location_id ;/*选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式employeesEmp#managerMgr#kochhar101king100*/select e1.last_name "employees" , e1.employee_id "EMP#" ,e2.last_name "Manger" , e2.employee_id "MGR#"from employees e1 , employees e2where e1.manager_id = e2.employee_id ;</span>