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';

Oracle PL/SQL中判断两个两个字段相等或者不相等的时候，

常常出现：字段值明明不相等(一个空，一个不空)，但是判断不相等的时候就是得不到TRUE。

经过测试和分析，发现，并不是<>不稳定，而是字段值为NULL是，

不能使用=或者<>比较值，应该使用IS NULL判断是否为空。

可以把空理解为未知,一个非空和未知比较,结果还是未知.

当字段a1,a2,b1,b2中有一个或多个为空时，下面两个语句都会失效：

1.    IF ( a1 <> a2  OR  b1<>b2 ) THEN

2.    IF ( a1 = a2  AND  b1=b2 ) THEN

例如，如果a1为空，a2, b1,b2不空时，语句应该这么写

1.   IF ( (a1 IS NULL AND  a2 IS NOT NULL) OR  b1<>b2 ) THEN

2.   IF ( (a1 IS NULL AND  a2 IS  NULL)  AND  b1=b2 ) THEN

为了解决NULL带来的“无法判断相等或不等”的问题，我们可以使用NVL函数解决，语句如下：

1.    IF ( NVL(a1,0)  <> NVL(a2,0)  OR  NVL(b1,0) <> NVL(b2,0) ) THEN
2.    IF ( NVL(a1,0)  =  NVL(a2,0)  AND  NVL(b1,0) = NVL(b2,0) ) THEN

注：NVL(args, deafultValue)，args为变量，deafultValue为当args为空时，设置的默认值（一般为0）。

其中：NVL(a1, 0)  和 NVL(a1, '0')  效果一样，最后 a1 的值都是字符串: '0' （不是字符！）.