Oracle(SQL之查询)

--查询语句
select * from emp;
-- 查询deptno,sal,job 去掉重复部门号
select distinct deptno,sal,job from emp;
-- 设置执行时间
set timing on;
-- 查找emp 表中员工的年薪
select sal*12+nvl(comm,0)*12 '年工资',ename,job from emp;
-- 查找 工资大于三千的员工
select ename,sal from emp where sal>3000;
-- 查询雇佣在1-1月-1982 之后的员工
select * from emp where hiredate>'1-1月-1982';
-- 查询工资在2500到3000之间的员工
select * from emp where sal>2500 and sal<3000;
-- 查询员工姓名中 S 开头的员工的所有信息
select * from emp where ename like 'S%';
-- 查询员工姓名第三个字母是L的员工
select * from emp where ename like '__L%';
-- 查询部门号
select deptno from emp ;
-- 查询10号和20号部门的员工的所有信息
select * from emp where deptno in(10,20);
-- 查询没有上级的员工 写法1
select * from emp where mgr is null;
-- 查询没有上级的员工 写法2
select * from emp where mgr='';

-- 查询工资高于500或者是岗位为MANAGER的雇员，
-- 同时还要满足他们的姓名首写字母为大写的J：
select * from emp where (sal>500 or job='MANAGER')and ename like 'J%';

-- 查询按照工资的从低到高的顺序显示员工的信息
select ename,sal from emp order by sal
select ename,sal from emp order by sal asc;
-- 降序 排列
select ename,sal from emp order by sal desc;
-- 使用别名排序
select sal*12+nvl(comm,0)*12 "年薪" from emp order by "年薪" desc;
select ename,(sal+nvl(comm,0))*12 as "年薪" from emp order by "年薪";
--oracle分组函数是对一批数据进行操作之后返回一个值。
--一批数据可能是整个表，也可以是按某些条件把表分成的组。
-- 常用分组函数有max、min、avg、count、sum。
select max(sal) from emp;
select min(sal) from emp;
select avg(sal) from emp;
select count(job) from emp;
select sum(sal) from emp;
select max(sal),min(sal) from emp;

-- 查询所有员工最高工资的员工姓名
select ename,sal from emp where sal=(select max(sal) from emp);

--查询工资高于平均工资的员工信息
select * from emp where sal > (select avg(sal)from emp);
--查询每个部门的平均工资和最高工资 (分组函数)
select avg(sal),max(sal),deptno from emp group by deptno;
--查询每个部门的每种岗位的平均工资和最低工资
select avg(sal),min(sal),deptno,job from emp group by deptno,job;
--查询平均工资低于2000的部门号和它的平均工资和最高工资 having
--（也就是查询部门的平均工资和最高工资并且要低于平均工资2000的部门号）：
select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)<2000;

--1 分组函数只能出现在select语句的列里，或者在having、order by字句中。
--2 如果在select语句中同时包含有group by、having、order by字句，
--那么它们的顺序必须是：先group by 再having 再order by。比如下面语句：
--select avg(sal),max(sal),deptno from emp group by deptno having arg(sal)<2000 order by arg(sal);
--3 在select后面有列、表达式、和分组函数时，那么这些列和表达式必须有一个出现在group by字句中，否则会报错的。
select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)<2000 order by avg(sal);
-- 多表查询是基于两个或两个以上的表或是视图的查询。
select * from dept;
select e.ename,e.sal, d.deptno from emp e, dept d where e.deptno=d.deptno;
--查询部门号为10的部门名称、员工名及工资
select e.ename,d.dname,e.sal from dept d,emp e where d.deptno=10;
--查询各个员工的姓名、工资、工资级别：
select e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;
--查询员工名、工资及所在部门的名字，并按部门编号进行排序
select e.ename,e.sal,d.deptno from dept d,emp e where d.deptno=e.deptno order by d.deptno desc ;
--查询员工ford的上级
select e.ename from emp e where mgr=null;
select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno and worker.ename='ford';
-- 查询与smith同一部门的所有员工： ALLEN
select * from emp where deptno=(select deptno from emp where ename='ALLEN');
-- 查询和部门号为10的工作(job)相同的员工名、岗位、工资、部门号
select distinct e.ename,e.job,e.sal,e.deptno from emp e where deptno=10;
select * from emp where job in(select distinct job from emp where deptno=10);
--注意：这里job不能用=，必须用关键字in，
--因为用=是表示一条记录，而现在子查询语句是多行数据，必须用in关键字。

-- 查询显示工资比部门为30的所有员工的工资高的员工名、工资、部门号。 all语句
select ename, sal, deptno from emp where sal>all(select sal from emp where deptno=30);
-- 第二种写法
select ename,sal,deptno from emp where sal>(select max(sal) from emp where deptno=30);
-- 第二种写法效率很高 尽量使用函数

--查询显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门 any语句
select ename,sal,deptno from emp where sal>any (select min(sal) from emp where deptno=30);
select ename,sal,deptno from emp where sal>any (select sal from emp where deptno=30);
select ename,sal,deptno from emp where sal>(select min(sal) from emp where deptno=30);

-- 多列子查询
-- 查询部门号和工作或者是薪水和ALLEAN 相同的
select ename "员工姓名", sal "工资" from emp ;
select * from emp where (deptno,sal)=(select deptno,sal from emp where ename='ALLEAN');
select * from emp where (deptno,job)=(select deptno,job from emp where ename='ALLEAN');
-- 查询如何显示高于自己部门平均工资的员工的信息
select ename ,sal from emp where sal>(select avg(sal) from emp where deptno=10);
-- 再次注意：oracle的表名和字段名是不区分大小写，但是字段里的数据是要区分大小写的。
--首先查询各个部门的平均工资和部门号
select avg(sal),deptno from emp group by deptno;
--将上面的查询结果看做是一张表(子表)，然后和emp进行多表查询
select e1.ename,e1.sal, e1.deptno,e2.mysal from emp e1,(select avg(sal) mysal,deptno from emp group by deptno)e2 where e1.deptno=e2.deptno and
e1.sal>e2.mysal;
-- tochar 函数
select * from emp where to_char(hiredate,'yyyy')=1981；
select * from emp where to_char(hiredate,'mm')=12;
select * from emp where to_char(hiredate,'dd')=3;
select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;
-- dual
select sysdate from dual;