SQL语句查漏总结

来源：互联网发布：网络的使用界限编辑：程序博客网时间：2024/05/01 19:37

1.获得工资收入最高的前5个人(注意思考思路)

1、先对根据工资进行数据排序

create view b

select * from emp order by sal desc;

2、再对排序后的数据加上编号

create view c

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

d.deptno=e.deptno

join salgrade g

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