oracle常用语句练习

select userenv('language') from dual;--AMERICAN_AMERICA.ZHS16GBK

select * from V$NLS_PARAMETERS--AMERICAN

--===========解锁scott用户并重新设置密码

alter user scott account unlock;

alter user scott identified by tiger;

--===========基本查询

--1.查询出所有emp中的信息，并用中文进行字段重命名

select empno as "员工编号",ename "员工姓名",job 工作,mgr "上级领导",hiredate "入职日期",sal "工资",comm "奖金",deptno "部门编号" from emp;

--2.查询emp表中员工的job信息，并去除重复信息

select distinct(job) from emp;

--3.查询emp表中员工的全年的工资总和(sal总和)

select ename,sal* 12 from emp;

--4.查询emp表中员工的全年收入总和(sal+comm的总和)

select ename,sal*12 + nvl(comm,0) from emp;

--5.查询emp表中员工编号，姓名

--输出格式如下：编号:xxx，姓名：xxx

----Concat拼接方式

select concat('编号:',empno) from emp;

select concat(concat('编号:',empno),concat('，姓名：',ename)) from emp;

----Oracle的||方式

select '编号:'||empno||'，姓名：'||ename from emp;

--=============================================条件查询

--1.查询工资大于1500的员工

select * from emp where sal > 1500;

--2.查询工资大于1500并且有奖金的雇员

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

--3.查询工资大于1500或者有奖金的雇员

select * from emp where sal > 1500 or comm is not null;

--4.查询工资大于1500并且没有奖金的雇员

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

--5.查询员工姓名为smith的员工

select * from emp where ename = 'SMITH';

--=============================================范围查询

--1.查询工资大于1500但小于3000的全部雇员

---->=,<=方式

select * from emp where sal >=1500 and sal <= 3000;

----between and方式

select * from emp where sal between 1500 and 3000;

--2.查询1981-1-1到1981-12-31号入职的雇员（between and）

select * from emp where hiredate between to_date('1981-1-1','yyyy-MM-dd') and to_date('1981-12-31','yyyy-MM-dd')

--3.查询员工编号是7369,7654,7566的员工

----OR方式

select * from emp where empno = 7369 or empno = 7654 or empno = 7566;

----IN方式

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

--4.查询雇员姓名是'SMITH','ALLEN','WARD'的雇员信息

----IN方式

select * from emp where ename in ('SMITH','ALLEN','WARD');

--=============================================模糊查询like

--1.查询所有雇员姓名中第二个字符有‘M’的雇员

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

--2.查询名字中带有‘M’的雇员

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

--3.查询雇员编号不是7369的雇员信息

----<>方式

select * from emp where empno <> 7369;

----!=方式

select * from emp where empno != 7369;

--=============================================排序 order by

--1.查询雇员的工资进行降序排序

select * from emp order by sal desc;

--2.查询雇员的奖金并做降序排序（关于nulls first/nulls last）

select * from emp order by comm desc nulls last;

--3.查询雇员的工资做降序排列并且其中奖金部分是升序排序

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

--===========单行函数

--字符函数

--1.将'smith'转换成大写--关键字:upper

select upper('smith') from dual;

--2.将'SMITH'转换成小写--关键字:lower

select lower(ename) from emp;

--3.将'smith'首字母大写--关键字:initcap

select initcap('smith') from dual;

--4.将'helloworld'截取字符串成'hello'--关键字substr

select substr('helloworld',0,5) from dual;

--5.获取'hello'的字符串长度--关键字length

select length('hello' ) from dual;

--6.将'hello'中的l用x进行替换--关键字replace

select replace('hello','l','x') from dual;

--数值函数

--1.将15.66进行四舍五入（从-2到2）--关键字round

select round(15.66) from dual;   --16

select round(15.66,-2) from dual;--0

select round(15.66,-1) from dual;--20

select round(15.66,0) from dual; --16

select round(15.66,1) from dual; --15.7

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

--2.将15.66进行截断（从-2到2）--关键字trunc

select trunc(15.66) from dual;

select trunc(15.66,-2) from dual;  --0

select trunc(15.66,-1) from dual;  --10

select trunc(15.66,0) from dual;   --15

select trunc(15.66,1) from dual;   --15.6

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

--3.对15/3进行求余数--关键字mod

select mod(15,3) from dual;

--日期函数

--1.查询系统时间--关键字sysdate

select sysdate from dual;

--2.查询雇员进入公司的周数

select ename,(sysdate-hiredate)/7 from emp;

--3.查询雇员进入公司的月数--关键字months_between

select ename,months_between(sysdate,hiredate) from emp;

--4.求出三个月后的日期--关键字add_months

select ename,add_months(hiredate,3) from emp;

--转换函数

--1.将系统日期显示为yyyy-mm-dd hh:mi:ss(去掉补零和24小时显示时间)--关键字to_char

select to_char(sysdate,'yyyyfm-mm-dd hh24:mi:ss') from dual;

----显示成年月日

select  to_char(sysdate,'yyyyfm-mm-dd hh24:mi:ss') from dual;

select to_char(sysdate,'yyyy')||'年'||to_char(sysdate,'MM')||'月' from dual;

--2.将字符串'1981-1-1'转换成日期类型--关键字to_date

select to_date('1981-1-1','yyyy-MM-dd') from dual;

select to_number('99.') from dual;

select to_char(99) from dual;

--通用函数

--1.空值的处理函数

select nvl(comm,0) from emp;

--2.nvl2(判断值,空返回值,非空返回值) 

select nvl2(3,'1','2') from dual;

192.168.124.29/

--条件表达式

--1.查询员工的job内容并转成中文显示

----decode方式

select ename,decode(job,'CLERK','柜员','SALESMAN','销售','MANAGER','管理','其他') from emp;

----case when then end方式

select ename,case job 

 when 'CLERK' then '柜员'

    when 'SALESMAN' then '销售'

       when 'MANAGER' then '管理'

         else

            '其他'

         end from emp;

--===========多行函数

--1.查询所有员工记录数--关键字count

select count(empno) from emp;

--2.查询佣金的总数--（如何查询某个字段的总数量）

select sum(sal) from emp;

--3.查询最低工资--关键字min

select min(sal) from emp;

--4.查询最高工资--关键字max

select max(sal) from emp;

--5.查询平均工资--关键字avg

select avg(sal) from emp;

--6.查询20号部门的员工工资总和

select sum(sal) from emp where deptno = 20;

--======================================分组函数

--1.查询部门编号及人数--分组查询关键字group by

select deptno,count(*) from emp group by deptno;

--2.查询每个部门编号及平均工资

select deptno,avg(sal) from emp group by deptno;

--3.查询部门名称，部门编号，平均工资

select dname,emp.deptno,avg(sal) from emp,dept where emp.deptno = dept.deptno group by dname,emp.deptno;

--4.查询出部门人数大于5人的部门

select deptno,count(*) from emp group by deptno having count(*)>5 ;

--5.查询部门编号，部门名称，平均工资且平均工资大于2000

select emp.deptno,dname,avg(sal) from emp,dept where emp.deptno = dept.deptno

group by emp.deptno,dname

having avg(sal) > 2000;

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

--======================================多表关联查询

--查询员工编号,员工姓名,员工部门编号,员工部门名称,员工部门地址,中文显示员工工资等级,

--及领导编号,领导姓名,领导部门编号,领导部门名称,中文显示领导工资等级

select e1.empno,e1.ename,e1.deptno,d1.dname,d1.loc,decode(s1.grade,1,'一级',2,'二级',3,'三级',4,'四级',5,'五级','其他') as sallevel,e2.empno,

e2.ename,e2.deptno,d2.dname,d2.loc,decode(s2.grade,1,'一级',2,'二级',3,'三级',4,'四级',5,'五级','其他') as sallevel from emp e1,dept d1,salgrade s1,emp e2,dept d2,salgrade s2 

where e1.mgr = e2.empno and e1.deptno = d1.deptno and e1.sal between s1.losal and s1.hisal

and e2.deptno = d2.deptno and e2.sal between s2.losal and s2.hisal;

select * from emp;

select * from salgrade;

select * from dept;

--1.查询员工编号,员工姓名,领导编号,领导姓名

--2.查询员工编号,员工姓名,员工部门编号,员工部门名称,员工部门地址,领导编号,领导姓名,领导部门编号,领导部门名称

--3.查询员工编号,员工姓名,员工部门编号,员工部门名称,员工部门地址,员工工资等级,领导编号,领导姓名,领导部门编号,领导部门名称,领导工资等级

--4.查询员工编号,员工姓名,员工部门编号,员工部门名称,员工部门地址,中文显示员工工资等级,领导编号,领导姓名,领导部门编号,领导部门名称,中文显示领导工资等级

select e1.empno,e1.ename,e1.deptno,d1.dname,d1.loc,

decode(s1.grade,1,'一级',2,'二级',3,'三级',4,'四级',5,'五级','其他') as sallevel,

e2.empno,e2.ename,e2.deptno,d2.dname,

decode(s2.grade,1,'一级',2,'二级',3,'三级',4,'四级',5,'五级','其他') as sallevel

 from emp e1,salgrade s1,dept d1,dept d2,salgrade s2,emp e2

where e1.mgr = e2.empno and d1.deptno = e1.deptno and e1.sal 

between s1.losal and s1.hisal and e2.sal between s2.losal and s2.hisal

and d2.deptno = e2.deptno

--======================================外连接

--1.查询员工编号，姓名，领导编号，领导姓名，包括没领导的

----left join on方式

select e1.empno,e1.ename,e2.empno,e2.ename from emp e1 left join emp e2 on e1.mgr = e2.empno;

----Orcl的(+)方式

select e1.empno,e1.ename,e2.empno,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno(+);

--2.查询出所有部门信息(包括没员工的部门)及部门下的员工信息

select * from dept,emp where dept.deptno = emp.deptno(+);

--===========子查询

--1.查询比雇员7654工资高，同时从事和7788的工作一样的员工

select * from emp where sal > (select sal from emp where empno = 7654)

and job = (select job from emp where empno = 7788);

--2.查询每个部门最低工资及最低工资的部门名称和雇员名称

select deptno,min(sal) from emp group by deptno; --查询部门最低工资

select e1.deptno,dname,e1.minsal,e2.ename from (select deptno,min(sal) minsal 

from emp group by deptno) e1,emp e2,dept where e1.deptno = dept.deptno

 and e1.deptno = e2.deptno and e1.minsal = e2.sal

select * from dept;

select * from emp;

--===========课堂练习

--1.找到员工表中工资最高的前三名

select rownum,emp.* from emp order by sal desc;

select rownum,e.* from (select * from emp order by sal desc) e where rownum <= 3;

--2.找到员工表中薪水大于本部门平均工资的所有员工

select e2.ename,e2.deptno,e2.sal,e1.avgsal from (select deptno,avg(sal) avgsal from emp group by deptno) e1,emp e2

where e1.deptno = e2.deptno and e1.avgsal < e2.sal;

--3.统计每年入职的员工个数

select to_char(hiredate,'yyyy'),count(*) from emp group by to_char(hiredate,'yyyy') 

--求出总人数

select sum(e1.hcount) as Total from (select to_char(hiredate,'yyyy') hdate,count(*) hcount from emp group by to_char(hiredate,'yyyy')) e1 

--统计每年入职的员工个数

select sum(e1.hcount) as Total,sum(decode(e1.hdate,'1980',e1.hcount)) as "1980",min(decode(e1.hdate,'1981',e1.hcount)) as "1981",max(decode(e1.hdate,'1982',e1.hcount)) as "1982",avg(decode(e1.hdate,'1987',e1.hcount)) as "1987" from (select to_char(hiredate,'yyyy') hdate,count(*) hcount from emp group by to_char(hiredate,'yyyy')) e1 

--===========分页查询

--1.查询员工表，将员工工资进行降序查询，并进行分页取出第一页，一页三条记录

select rownum,e.* from (select * from emp order by sal desc) e where rownum <= 3;

--分页公式

select * from (select rownum r,e.* from (select * from emp order by sal desc) e) e1

 where e1.r > 3 and e1.r <= 6;

--===========集合运算（了解）

--1.查询工资大于1200并且job是SALESMAN(intersect)

select * from emp where sal > 1200 

intersect

select * from emp where job = 'SALESMAN';

--2.查询工资大于1200或者job是SALESMAN(union)

select * from emp where sal > 1200 

union

select * from emp where job = 'SALESMAN';

--3.求工资大约1200和job是SALESMAN的差集(minus)

select * from emp where sal > 1200 

minus

select * from emp where job = 'SALESMAN';

--==========================exists / not exists

--1.查询出有员工的部门

select * from dept where not exists (select * from emp where emp.deptno = dept.deptno);

select * from dept where 1=1; -- select * from emp where exists (select * from dept)