SQL语句查漏总结

butter7372

1.获得工资收入最高的前5个人(注意思考思路)
  1、先对根据工资进行数据排序
create view b
as
select * from emp order by sal desc;
  2、再对排序后的数据加上编号
create view c
as
select rownum id,b.* from b;
  3、根据编号获得前5位
select * from c where id<=5;
  4、合并成一条SQL语句
select * from (select rownum id,t.* from
(select * from emp order by sal desc) t) tt where tt.id<=5;
获得工资第五到第八位的人员信息
select * from (select rownum id,b.* from
(select * from emp order by sal desc) b) c where c.id between 5 and 8;


2.列出工龄最长的员工姓名和入职日期
select ename,hiredate from emp where (sysdate-hiredate)=(select max(sysdate-hiredate) from emp)


3. 查询部门员工收入差异最大的组
select stddev(sal) from emp group by deptno   --stddev是方差函数.


4. 查询emp表中存在的部门号
select deptno from emp group by deptno
也可以使用 select distinct deptno from emp;

5.查询各个部门的平均工资
select avg(sal),deptno from emp group by deptno

where的作用：行的过滤(选择)
having：组过滤

6、使用Having子句进行分组过滤
查询部门号为10和20的部门的平均工资
select avg(sal),deptno from emp
group by deptno
having deptno in(10,20);

7、组合使用where和Group by子句
查询部门号为10和20的部门的平均工资
select deptno,avg(sal) from emp where deptno in(10,20) group by deptno

8、组合使用Where、group by和Having
查询10、20的部门的 录
select deptno,avg(sal) from emp where deptno in(10,20) group by deptno
having avg(sal)<2500

9.以下3条语句等价：  
92：select e.ename,d.dname from emp e JOIN dept d ON e.deptno=d.deptno
92：select e.ename,d.dname from emp e JOIN dept d USING(deptno) --JOIN ... ON的特例
86：select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno

10. 关于多于两个表的连接
     体现在连接条件中，连接条件的个数是表的个数减去1
     --查询所有员工的员工名、部门名和工资等级
     SQL86
     select e.ename,d.dname,g.grade from emp e,dept d,salgrade g
     where d.deptno=e.deptno and e.sal between g.losal and g.hisal;
     SQL92
     select e.ename,d.dname,g.grade from emp e join dept d
     on
     d.deptno=e.deptno
     join salgrade g
     on
     e.sal between g.losal and g.hisal


11.自连接：对同一个表进行连接，解决表中行与行之间的关系
     列出每一个员工的上司
     1、SQL86
     select e.ename 员工,m.ename
     from emp e,emp m where e.mgr=m.empno
     2、SQL92
     select e.ename 员工,m.ename
     from emp e join emp m on e.mgr=m.empno

12.在ANY和ALL操作符之前必须使用比较运算符

查询工资低于salgrade表中低级工资的员工名和其工资
select ename,sal from emp where sal<any(select losal from salgrade);
  >any (2000,3000,4000)
查询工资低于salgrade表中所有的低级工资的员工名和其工资
select ename,sal from emp where sal<all(select losal from salgrade);
在一个例子
查询比各个部门平均工资都要低的员工名和工资
select ename,sal from emp where sal<ALL(select avg(sal) from emp group by deptno)
查询低于任意部门平均工资的员工的名字和工资
select ename,sal from emp where sal<ANY(select avg(sal) from emp group by deptno)

13.列出每个部门中工资最低的员工的姓名和工资
select ename,sal from emp
where (sal,deptno) in
(select min(sal),deptno from emp group by deptno)

14.关联子查询:
1、范例
查询高于自己部门平均工资的员工名和工资
select ename,sal from emp out
where sal>(
select avg(sal) from emp inner where inner.deptno=out.deptno
)
2、exists、not exists：检查行的存在性
查询可以管理其他员工的员工姓名
1、select ename from emp where exists(select 1 from emp inner where emp.empno=inner.mgr)
2、select ename from emp where empno in(select mgr from emp)
查询没有员工的部门号
1、select deptno from dept where not exists(select 1 from emp where dept.deptno=emp.deptno)
2、select deptno from dept where deptno not in(select deptno from emp)

关于exists（not exists）与in（not in）的比较
通常来讲，因为in（not in）需要检查实际值，而exists（not exists）只需要检查存在性，
所以exists（not exists）效率更高一些

15.高级查询:
集合操作：（SQL Server中只是支持Union和Union all）
Union结果并集，去掉重复行
Union all结果并集，保留重复行
Intersect结果的交集
Minus结果的差集
select * from emp where deptno=10 and job='CLERK';
交集：取两个查询公共的数据
select * from emp where deptno=10
intersect
select * from emp where job='CLERK'
差集：
把第一个查询结果中和第二个查询结果相同的行去掉，剩下的数据
两个查询的先后顺序就敏感了
select ename from emp where deptno=10 minus
select ename from emp where job='CLERK'

select ename from emp where job='CLERK' minus
select ename from emp where deptno=10;


16.行列转换
       ID NAME         SUBJECT     SCORE
-- -------------------- ---------- ----------
       1 张三          物理        78
       2 张三          化学        80
       3 张三          英语        90
       1 吴用          物理        60
       1 吴用          化学        70
       1 吴用          英语        98
行列转换
       姓名物理化学英语
       张三788090
       吴用607098
      
  select name 姓名,
  sum(case subject when '物理' then score end) 物理,
  sum(case subject when '化学' then score end) 化学,
  sum(case subject when '英语' then score end) 英语
  from a group by name

行列转换——在实际开发中，打印报表时非常普遍的使用
如何实现行列转换？(SQL Server CASE)
select name 姓名,
sum(decode(subject,'物理',score)) 物理,
sum(decode(subject,'化学',score)) 化学,
sum(decode(subject,'英语',score)) 英语
from a group by name

17.使用case表达式：
case表达式可以在SQL中实现类似if...else语句，其功能和decode相似。
1、使用简单case表达式
   case search_express
     when express1 then result1
     when express2 then result2
     ...
     when expressn then resultn
     else default_result
   end
   列出所有员工的名字和部门代码，代码对应如下：
   10——A，20——B，30——C，其余的——X
select ename,
case deptno
when 10 then 'A'
when 20 then 'B'
when 30 then 'C'
else 'X'
end deptno
from emp  
2、使用搜索case表达式
   case
     when condition1 then result1
     when condition2 then result2
     ...
     when conditionn then resultn
     else default_result
   end  
   1、列出所有员工的名字和部门代码，代码对应如下：
     10——A，20——B，30——C，其余的——X
     select ename,
     case
       when deptno=10 then 'A'
       when deptno=20 then 'B'
       when deptno=30 then 'C'
       else 'X'、
       end from emp;


18.DECODE函数==case操作
   非常类似于if ... else语句
   if deptno=20 then
     sal=sal+20;
   elsif deptno=30 then
     sal=sal+30;
   elsif deptno=10 then
     sal=sal+10;
   else
     sal=sal;
    end if;
使用decode函数：
decode(deptno,
   10,
     sal+10,
   20,
     sal+20,
   30,
     sal+30,
   sal
   )
decode函数是一个无限参数函数，格式：
decode(表达式，值1，返回表达式1，值2，返回表达式2，....，返回表达式n）

19.
greatest：取最大值
least：取最小值
根据第一个参数的数据类型，自动转换其余的数据类型，使其一直，如转换失败
表达式报错。

nvl：空值处理函数
nvl2：空值处理函数
nvl(表达式，返回值）
select nvl(comm,0) from emp
员工的总收入（sal+comm）
select sal+nvl(comm,0) from emp
nvl2（表达式，返回值1，返回值2）
select nvl2(comm,comm,0) from emp
  员工的总收入（sal+comm）
select nvl2(comm,comm+sal,sal) from emp
这篇博客最初发表在我的百度博客中,现在整理到csdn中:
http://hi.baidu.com/lk_well/blog/item/ed78cfac6eb78f1f4b36d69a.html