一。多表查询
- – 隐式内连接
select * from emp e,dept d where e.deptno=d.deptno; - – 显式内连接 select * from emp e inner join dept d where e.deptno=d.deptno;
- – 左外连接 select ename ,dname from emp e left join dept d on e.deptno=d.deptno;
- – 右外连接 select ename ,dname from dept d left join emp e on e.deptno=d.deptno;-- from谁就显示哪边
- – 笛卡尔积现象 select * from emp,dept;
左表的每条数据和右表的每条数据组合的效果
但不是所有数据组合都有用,所以要设置条件过滤
例如:select emp.deptno,dept.deptno from emp ,dept where emp.deptno=dept.deptno;
二。子查询
- – 查询结果单行单列, 查询工资最高员工
select max(sal) from emp;
select * from emp where sal=(select max(sal) from emp);
- – 多行单列(查询工资大于2000的员工,来自于哪些部门)
select deptno from emp where sal>2000;
select deptno,dname from dept where deptno in(select deptno from emp where sal>2000);
- 多行多列(薪资高于部门平均工资的员工)
select deptno , avg(sal) from emp group by deptno;
select *
from emp e
join
(select deptno , avg(sal) 'avg_sal' from emp group by deptno) d
on e.deptno=d.deptno
where e.sal>d.avg_sal;
二。视图
-- 创建视图
create view shitu
as
select deptno , avg(sal) d from emp group by deptno;
-- 查询视图
select * from shitu;
-- 视图被当成表被加入可用来多表查询
select * from emp e
join shitu
on e.deptno=d.deptno
where e.sal> shitu.avg(sal);
-- 删除视图
drop view shitu;