SQL语句复习【专题四】

多表查询 sql 92
多表查询 sql92、sql99 标准
--查询所有员工的姓名，部门编号，部门名称
select * from emp
select * from dept
--笛卡尔集
select * from emp, dept
--消除笛卡尔集中的冗余的数据
select *
from emp, dept
where emp.deptno=dept.deptno--表的连接查询条件

等值连接查询
--查询所有员工的姓名，部门编号，部门名称
注意：如果多个表间存在相同的字段的名称，那么必须指明显示的字段属于哪一张表。
select ename, emp.deptno,dname
from emp, dept
where emp.deptno=dept.deptno

给表起别名:注意：给表起别名不能使用 as 。一旦给表起了别名，那么原本的表的名称就不能再使用。
select ename, e.deptno,dname
from emp e, dept d
where e.deptno=d.deptno

select ename, e.deptno,dname
from emp e, dept d
where e.deptno <> 10 and e.deptno=d.deptno

sql 92中，表的连接条件: 在where 中书写 表连接的条件。where 中既可以写行过滤，也要写 表连接。
--非等值连接
--查询所有员工的姓名，职位，薪水，薪水等级 emp 和 salgrade 两个表的多表查询
select *
from emp e, salgrade s
--非等值连接
select e.ename, e.job, e.sal ,s.grade
from emp e, salgrade s
where e.sal >=s.losal and e.sal <= s.hisal
--尽量将所有的显式的字段，都使用表名引用 ，可以提高访问的效率。

自连接
--查询 员工的编号，名字，员工的上级的编号，上级的名字
select * from emp
将一张表 看作两张表。
--一张员工表 emp。想象成一张作为领导的上级的表。
select e1.empno,e1.ename,e1.mgr,e2.ename
from emp e1, emp e2
where e1.mgr=e2.empno

外连接 【左右外连接】
左外连接
--查看不满足条件的记录
--查询 员工的编号，名字，员工的上级的编号，上级的名字，包含没有上级的员工
select e1.empno,e1.ename,e1.mgr,e2.ename
from emp e1, emp e2
where e1.mgr=e2.empno(+)--左外连接，就是将左表的不满足条件的内容也显示出来。

右外连接
--查询 员工的编号，名字，员工的上级的编号，上级的名字，包含没有下级的员工的信息
select e1.empno,e1.ename,e1.mgr,e2.ename
from emp e1, emp e2
where e1.mgr(+)=e2.empno--右外连接，就是将右表的不满足条件的内容也显示出来。

--查询部门的所有信息，以及每个部门的平均工资，包含没有员工的部门
select d.*,avg(sal)
from emp e, dept d
where e.deptno(+)=d.deptno--右外连接
group by d.deptno,d.dname,d.loc

三表连接
--查询20号部门的员工的编号，姓名，薪水，部门名称，薪水等级。
select d.deptno, e.empno,e.ename,e.sal,d.dname,s.grade
from emp e, dept d, salgrade s
where d.deptno=20 and e.deptno=d.deptno and e.sal >=s.losal and e.sal<= s.hisal

n张表，至少需要几个连接条件？ 需要n-1 个连接条件

sql 92：连接条件和 行过滤条件都在where子句中 书写。
sql 99：表的连接条件 和 行过滤条件分开了。where 只做行过滤。表的连接条件在其他的地方进行。
--交叉连接 cross join 和笛卡尔集是一样的
--sql92
select *
from emp e, dept d
--sql 99
select *
from emp e cross join dept d

--查询所有员工的姓名，部门编号，部门名称
--sql92
select e.ename,e.deptno,d.dname
from emp e, dept d
where e.deptno=d.deptno

自然连接 natural join 等值连接:
连接条件被隐式指定，自动使用两个进行自然连接的表中的相同的字段进行等值连接。作为连接条件的列，不能指定该列的表的限定。语法的要求。
优点：不需要指明连接条件，条件是隐式指定的。
缺点：不能指定连接条件，如果多个表中有多个相同的字段，那么会自动使用多个字段进行等值连接，不够灵活。只能做等值连接。
select e.ename,deptno,d.dname
from emp e
natural join dept d

Using 子句：只能做等值连接。需要连接的表中存在相同的字段，可以显式指定多表之间的等值连接条件，可以指定多个字段进行等值连接。
--sql 99
select e.ename,deptno,d.dname
from emp e
inner join dept d--内连接 inner 可以省略
Using(deptno)

select e.ename,deptno,d.dname
from emp e
join dept d--内连接 inner 可以省略
Using(deptno)

on 子句连接：既可以等值连接也可以不等值连接
--查询30部分的员工的编号，姓名，部门名称
--sql 92
select d.deptno, e.empno,e.ename,d.dname
from emp e, dept d
where d.deptno=30 and e.deptno=d.deptno
--sql 99
select d.deptno, e.empno,e.ename,d.dname
from emp e join dept d
on e.deptno=d.deptno---等值连接
where d.deptno=30

--查询10部门员工的姓名，薪水等级---不等值连接
--sql 92
select e.deptno,e.ename, s.grade
from emp e, salgrade s
where e.deptno=10 and e.sal >= s.losal and e.sal <= s.hisal
--sql 99
select e.deptno,e.ename, s.grade
from emp e join salgrade s
on e.sal >= s.losal and e.sal<=s.hisal
where e.deptno=10

自连接
--查询 员工的编号，名字，员工的上级的编号，上级的名字
--sql 92
select e1.empno,e1.ename,e1.mgr,e2.ename
from emp e1, emp e2
where e1.mgr=e2.empno
--sql99
select e1.empno,e1.ename,e1.mgr,e2.ename
from emp e1 join emp e2
on e1.mgr=e2.empno

外连接
1）左外连接 left [outer] join 左边的表的不满连接条件的信息全部显式
2）右外连接 right [outer] join 右边的表的不满连接条件的信息全部显式
3）全外连接 full [outer] join 左边和右边的表的不满连接条件的信息全部显式
--sql 99 左外 包含没有上级的员工的信息
--sql 92
select e1.empno,e1.ename,e1.mgr,e2.ename
from emp e1,emp e2
where e1.mgr=e2.empno(+)
--sql99
select e1.empno,e1.ename,e1.mgr,e2.ename
from emp e1 left outer join emp e2
on e1.mgr=e2.empno

--sql 99 右外 包含没有下级的员工的信息
--sql 92
select e1.empno,e1.ename,e1.mgr,e2.ename
from emp e1, emp e2
where e1.mgr(+)=e2.empno
--sql 99
select e1.empno,e1.ename,e1.mgr,e2.ename
from emp e1 right join emp e2
on e1.mgr=e2.empno

--sql 99 全外 包含没有上下级的员工的信息
--sql99
select e1.empno,e1.ename,e1.mgr,e2.ename
from emp e1 full join emp e2
on e1.mgr=e2.empno

--sql 99 查询部门的所有信息，以及每个部门的平均工资，包含没有员工的部门
--sql 92
select d.*,avg(sal)
from emp e, dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname,d.loc
--sql99
select d.*,avg(sal)
from emp e right outer join dept d
on e.deptno=d.deptno
group by d.deptno,d.dname,d.loc

--查询20号部门的员工的编号、姓名、薪水、部门名称，薪水等级
--三表
--sql 92
select d.deptno ,e.empno,e.ename,e.sal,d.dname,s.grade
from emp e, dept d, salgrade s
where d.deptno=20 and e.deptno=d.deptno and e.sal >= s.losal and e.sal<=s.hisal

--sql 99 natural join 隐式连接的字段不能添加表的限定
select deptno,e.empno,e.ename,e.sal,d.dname,s.grade
from emp e natural join dept d join salgrade s
on e.sal>=s.losal and e.sal<=s.hisal
where deptno=20

--sql 99 Using + on Using 等值连接的字段，不要指定表的限定
select deptno,e.empno,e.ename,e.sal,d.dname,s.grade
from emp e join dept d
Using(deptno)
join salgrade s
on e.sal>=s.losal and e.sal<=s.hisal
where deptno=20

--sql 99 on on 中的等值连接部分，如果显式等值连接的字段，需要对字段指定属于哪一个表。
select d.deptno,e.empno,e.ename,e.sal,d.dname,s.grade
from emp e join dept d
on e.deptno=d.deptno
join salgrade s
on e.sal>=s.losal and e.sal<=s.hisal
where d.deptno=20