oracle表的查询

--查看表结构
desc dept;
--查询所有列
select * from dept;
--查询指定列
select ename,sal,job,deptno from emp;
--取消重复行
select distinct deptno,job from emp;
--查询SMITH的薪水，工作，所在部门
select sal,job,deptno from emp where ename='SMITH';
--查看共有多少行
select count(*) from emp;


--使用算术表达式
--查看每个雇员的年薪
select sal*12,ename from emp;
--年薪+奖金（comm为空的都没有工资了）
select ename,sal*12+comm*12 from emp;
--正确写法：nul(comm,0)如果comm为null,则用0代替
select ename,sal*12+nvl(comm,0)*13 from emp;
--使用别名(as可以省略)
select ename as "姓名",sal*12 as "年薪" from emp;
--连接字符串
select ename||' is a '||job from emp;
--查找1982.1.1后入职的员工
select ename,hiredate from emp where hiredate>'1-1月-1982';
--查找工作在2000到2500的员工
select ename,sal from emp where sal>2000 and sal<2500;
--如何使用like操作符
--%表示0到多个字符
select ename,sal from emp where ename like 'S%';
--下划线_表示单个字符
select ename,sal from emp where ename like '_M%';
--在where条件中使用in
select * from emp where empno in(7369,7499,7521);


--使用逻辑操作符号
--查找工作高于500或是为MANAGER的雇员，同时还要满足他们的姓名大写字母为J
select * from emp where (sal>500 or job='manager') and ename like 'J%';
--使用 order by 子句（desc降序，默认是asc升序） 
--升序排列
select ename,sal from emp order by sal; 
--部门号升序，工资降序排列
select * from emp order by deptno asc,sal desc;
--使用列的别名排序
select ename,(sal+nvl(comm,0))*12 as "年薪" from emp order by "年薪" desc;


--数据分组——max,min,avg,sum,count
--查找所以员工工资最高的和最低的
select max(sal),min(sal) from emp; 
--查找所以员工的平均工资和工资总和
select avg(sal) as "平均工资",sum(sal) as "工资总和" from emp;
--共有多少员工
select count(*) from emp;
--查找工资最高的员工的名字，工作岗位
select ename,job from emp where sal=(select max(sal) from emp);
--查找工资高于平均工资的员工信息
select * from emp where sal>(select avg(sal) from emp);




--group by 和 having 子句
--group by 用于对查询结果的分组统计
--having 用于限制分组先睡结果


--查找每个部门的平均工资和最高工资
select avg(sal),max(sal),deptno from emp group by deptno;
--查找每个部门的每种岗位的平均工资和最高工资
select avg(sal),max(sal),deptno,job from emp group by deptno,job;
--查找平均工资低于2000的部门号和他的平均工资(having用于限制分组)
select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)<2000;
--如果在select 语句中同时包含有group by ,having, order by 
--那么他们的顺序是group by >having>order by   例如：
select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)<2000 order by avg(sal);
--在选择列中如果有列，表达式，和分组函数，那么这些列和表达式有一个出现在group by 子句中
--*这里deptno就一定要出现在group by中
select deptno , avg(sal) , max(sal) from emp group by deptno having avg(sal)<2000;


--多表查询
--查找雇员名，雇员工资及及所在的部门
select ename,sal,dept.dname from emp,dept where emp.deptno = dept.deptno;
--查找部门号为10的部门名，员工名和工资
select dept.dname,emp.ename,emp.sal from dept,emp where dept.deptno = emp.deptno and dept.deptno = 10;
--显示各个员工的姓名，工资，及其工资的级别(级别在SALGRADE)
select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal;
--显示雇员名雇员工资及所在部门的名字，并按部门排序
select a1.ename,a1.sal,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno order by a1.deptno;


--自连接：指在同一张表的连接查询
select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno;
--显示FORD的上级
select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno and worker.ename='FORD';


--子查询
--单行子查询
--查找与SMITH同一部门的所有员工
select * from emp where deptno = (select deptno from emp where ename = 'SMITH');
--多行子查询
--查找和部门10的工作相同的名字，岗位，工资和部门号
select ename,job,sal,deptno from emp where job in(select job from emp where deptno=10);
--使用 all 操作符：查找工资比部门30的所有员工的工资高的员工的姓名，工资和部门号
select ename,sal,deptno from emp where sal>all(select sal from emp where deptno=30);
--使用 any 操作符：查找工资比部门30的任意员工的工资高的员工的姓名，工资和部门号
select ename,sal,deptno from emp where sal>any(select sal from emp where deptno=30);
--多列子查询
--查询与SMITH的部门和岗位完全相同的所有雇员
select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');
--分页查询
--rownum 分页
select a1.*,rownum rn from (select * from emp)a1;
--选择前10的
select a1.*,rownum rn from (select * from emp)a1 where rownum<=10;
--选择6--10
select * from (select a1.*,rownum rn from (select * from emp)a1 where rownum<=10) where rn>5;


--合并查询 ：union ,union all,intersect,minus
--union(联合):A union B (并集)会取消重复行
select ename,sal,job from emp where sal>2500 union select ename,sal,job from emp where job='MANAGER';
--union all : 不会取消重复行
select ename,sal,job from emp where sal>2500 union all select ename,sal,job from emp where job='MANAGER';
--intersect　：交集
select ename,sal,job from emp where sal>2500 intersect select ename,sal,job from emp where job='MANAGER';
--minus:差集
select ename,sal,job from emp where sal>2500 minus select ename,sal,job from emp where job='MANAGER';