数据库开发基础-Oracle-SQL基础-004，面试的经验与分享怎么写

–distinct 关键字，去除其后指定

–的字段的重复行。

–distinct 可以对多个字段去除重复行，

–去重原则是这些字段值得组合没有重复。

查看公司共有多少种职位?

select distinct job,deptno from emp;

–排序

order by 字句

order by 字句会根据其后指定的

字段的进行升序或降序将结果集

进行排列后显示。

升序(asc):asc 可以不写，默认升序，

降序(desc)：从小到大排列

desc 要注意与 查看表结构的sql命令desc区分

查看工资排名：

select ename,sal from emp order by sal desc;

order by 亦可以对多列进行排序，

排序优先级从左到右。

select ename,deptno,sal from emp order by deptno desc,sal;

排序的字段中若有 null 值， null被认作最大值。

select ename,comm from emp order by comm desc;

order by 子句必须写在 select 语句的最后一个子句上。

–聚合函数

聚合函数会将指定字段查询出的每一条记录

对应的值全部进行统计，然后得出一个结果。

max,min

求指定字段在查询出的所有记录中的最大值与最小值。

select max(sal),min(sal) from emp;

avg ，sum

求平均值与总和

select avg(sal),sum(sal) from emp;

select avg(comm),sum(comm) from emp;

–上面只考虑不为null的记录，会忽略为null记录的统计

聚合函数会忽略null值。

可使用nvl解决这个问题

select avg(nvl(comm,0)),sum(comm) from emp;

count()

统计给定字段值（不为null）的

的记录数。

查看公司共多少人？（emp表的记录数）

select count(ename) from emp;

通常查看表中记录总数使用count(*)

select count(*) from emp;

–分组

group by 子句

group by 是配合聚合函数进行分组统计结果使用的。

group by 会根据后面的给定的字段值相同的记录进行分组，

然后配合聚合函数根据每个组再统计结果。

查看每个部门的平均工资？

select avg(sal),deptno from emp group by deptno;

当select子句中有聚合函数时，凡不在聚合函数中的其他字段

都必须出现在 group by 子句中。反过来则不是必须的。

查看每个职位的最高工资是多少？

select max(sal),job from emp group by job;

group by 子句可以按照多个字段分组，

分组原则是这些记录的指定字段值的组合相同。

查看每个部门每个职位的平均工资？

select avg(sal), job, deptno from emp group by job, deptno;

–引入having

查看每个部门的平均工资，前提是该部门平均工资高于2000.

–错误写法

select avg(sal), deptno from emp where avg(sal) > 2000 group by deptno;

where 不能使用聚合函数作为过滤条件

原因在于：时机不对

having 子句

having 必须跟在 group by子句后，作用是在分组统计后，基于统计结果

再次进行过滤使用的。

having 中可以使用聚合函数的统计结果作为过滤条件来筛选分组。

select avg(sal), deptno

from emp

group by deptno

having avg(sal) > 2000;

查看平均工资高于2000的部门的最高薪水是多少？

select max(sal), deptno from emp group by deptno having avg(sal) > 2000;

where 的过滤时机是在第一次检索表中

数据时进行过滤的。where 决定着从表

中查询出多少数据。

having 是在分组统计后再进行过滤的，

二分组是在查询出的数据基础上进行的

所以 having 的过滤是在 where 之后进行的

having 决定着取舍那些分组。

部门最高工资大于2900的那些部门的平均工资？

select deptno,avg(sal) from emp group by deptno having max(sal)>2900;

查看30号部门的平均工资？

select avg(sal) from emp where deptno = 30;

select * from emp;

select * from dept;

–关联查询

将多张表数据联系起来，进行相关的查询工作。

查看每名员工的名字以及其所在部门的名字？

select ename, dname from emp, dept where emp.deptno = dept.deptno;

查看SALES部门的员工名字？

select ename,dname

from emp, dept

where emp.deptno = dept.deptno

and dname = ‘SALES’;

表可以使用别名，这样字段可以用表的别名

标注以避免sql语句过于繁琐。

select e.ename, d.deptno, d.dname

from emp e, dept d

where e.deptno = d.deptno

select e.ename, d.loc

from emp e, dept d

where e.deptno = d.deptno

and d.loc = ‘CHICAGO’;

关联查询必须定义连接条件，若不定义

则会出现“笛卡尔积”

笛卡尔积：一个无意义的结果集。会将

参与关联的两张表每条数据都连接一遍。

产生的数据量是两张表总数据量的乘积。

select * from emp,dept;

N张表联合查询，至少要定义N-1个连接条件

内连接：

另一种关联查询

select e.ename, d.dname

from emp e

join dept d

on e.deptno = d.deptno

where d.dname = ‘SALES’;

外连接：

关联查询中不会将不满足连接条件的

记录查询出来，当需要将不满足连接

条件的记录也查询出来时，要使用外连接。

外连接分为：左外连接，右外连接。和

全外连接

左外连接：以join左侧的表作为驱动表

该表中的数据全部要显示出来，那么不满足连接条件

的记录在结果集中来自右侧表中的字段全部以null作为值显示

select e.ename, d.dname

from emp e

left outer join dept d

on e.deptno = d.deptno;

select *

from emp e

right outer join dept d

on e.deptno = d.deptno;

select e.ename, d.dname

from emp e

full outer join dept d

on e.deptno = d.deptno;

select * from emp e, dept d where e.deptno(+) = d.deptno–右连接

自连接：