第 三 天 连接查询和多表查询
第 三 天 连接查询和多表查询
1.分组函数
avg
max
min
sum
count
执行顺序：
select
where
group by
having
order by
返回不重复的部门编号数
SQL> select count(distinct deptno) from emp;
得到每个部门的人数和最低薪水，平均薪水
SQL> select deptno,count(*),min(sal),avg(sal) from emp group by deptno;
显示每个部门的每种岗位的平均工资和最低工资
select avg(sal),max(sal),deptno,job from emp group by deptno,job;
注意：
出现在分组函数group by后面的字段可以不包含在select语句后
SQL>select avg(sal) from emp group by deptno;
出现在select语句后的非单值函数必须出现在group by 之后
SQL>select deptno,ename,avg(sal) from emp group by deptno,ename;
2.过滤
having 必须是使用了分组函数之后
having 分组条件
3、笛卡尔积
在没有连接条件的情况下，会出现笛卡尔积的全部结果
4、连接
连接n个表，至少需要n-1个连接条件
【内连接】
只连接匹配的行(交集)
select A.c1,B.c2 from A join B on A.c3=B.c3;
select A.c1,B.c2 from A,B where A.c3=B.c3;
■等值连接
select ename,dname from emp,dept where emp.deptno=dept.deptno;
■非等值连接
查询员工薪水的等级，姓名和工资
select ename,sal,grade from emp,salgrade where sal between losal and hisal;
【外连接】
A.c(+)=B.c 没有+的B一侧显示全部记录，有+的A显示符合匹配条件的记录
SQL> select e.ename,d.dname,d.deptno from emp e,dept d
2 where e.deptno(+)=d.deptno;
■全外连接，包含左右连个表中的全部行
select A.c1,B.c2 from A full outer join B on A.c3=B.c3;
■左外连接left outer join:包含表的全部行（不管右边表中是否匹配的行以及右边表中全部匹配的行）
select A.c1,B.c2 from A left outer join B on A.c3=B.c3;
或select A.c1,B.c2 from A,B where A.c3=B.c3(+);
■右外连接
select A.c1,B.c2 from A right outer join B on A.c3=B.c3;
或select A.c1,B.c2 from A,B where A.c3(+)=B.c3;
■自连接
对同一个采用别名的方式表进行连接(看成是两个表)
查询SMITH员工的上级(先查出所有员工的上级，然后在选择员工为SMITH的记录)
select e.ename,m.ename from emp e,emp m
where e.mgr=m.empno and e.ename='SMITH';
■交叉连接：cross join和笛卡尔积相同
■自然连接:natural join,以相同列为条件创建等值的列，不推荐使用，没有任何相同的字段，就会差生笛卡尔积对数据库的维护产生影响
select e.ename,d.dname from emp e natural join dept d;
使用using语句，使用两个表中列相同的情况下
select e.ename,d.dname from emp e join dept d using(deptno);
on子句的应用，指定额外的连接条件
格式： A join B on 条件
SQL> select ename,dname from emp join dept on dept.deptno=emp.deptno;
SQL> select ename,dname,city from emp,depart,loc
2 where emp.deptno=depart.did and depart.locid=loc.locid;
SQL> select ename,dname,city from emp
2 join depart on emp.deptno=depart.did
3 join loc on depart.locid=loc.locid;
■the ta 连接
使用等值以外的条件来匹配左右两个表中的行
select A.c1,B.c2 from A join B on A.c3!=B.c3;
5.子查询：
单行子查询,返回一行数据的子查询
多行子查询，返回多行数据的子查询
大于SCOTT薪水的用户
SQL> select ename,sal from emp where sal>(select sal from emp where ename='SCOTT');
获得职位和SCOTT相等的员工
SQL> select ename,job from emp where job=(select job from emp where ename='SCOTT');
获得职位和SCOTT相等的员工，不包括SCOTT
select ename,job from emp
where job=(select job from emp where ename='SCOTT')
and ename!='SCOTT';
得到高于平均工资的人
select * from emp where sal>(select avg(sal) from emp);
多行子查询
查询部门编号为10的工作相同的雇员的所有信息
SQL> select * from emp where job in(
2 select distinct job from emp where deptno=10);
使用having子句
SQL> select deptno,min(sal) from emp group by deptno
2 having min(sal)>(select min(sal) from emp where deptno=20);
6.多表查询
查询每个部门工资最高的人的详细资料
<any 小于最大值
>any大于最小值
<all 小于最小值
>all大于最大值
SQL> select empno,ename,sal,job
2 from emp
3 where sal<any
4 (select sal from emp where job='MANAGER');
