6 MySQL视图多表连接（内连接外连接）子查询

天行健

视图就是一张虚拟表

mysql> create or replace view myview as

-> select ename,dname from emp inner join dept on emp.deptno=dept.deptno;

select * from myview;

多表连接：笛卡尔积交叉连接（无意义）内连接外连接完全外连接（mysql不支持）

交叉连接 mysql> select * from emp,dept;

内连接

select * from emp,dept where emp.deptno=dept.deptno;

mysql> select * from emp right join dept on emp.deptno=dept.deptno;

mysql> select * from emp inner join dept on emp.deptno=dept.deptno;

select * from emp right outer join dept on emp.deptno=dept.deptno;

mysql> select * from emp right outer join dept on emp.deptno=dept.deptno;

mysql> select * from emp left outer join dept on emp.deptno=dept.deptno;

mysql> select ename,mgr,dname from emp inner join dept on emp.deptno=dept.deptno;

select e1.empno,e1.ename,e1.mgr,e2.empno,e2.ename from emp e1 inner join emp e2 on e1.mgr=e2.empno;

mysql> select * from (select e1.ename emp_name,e2.ename mgr_name from emp e1 inner join emp e2 on e1.mgr=e2.empno) e3 ;

mysql> select * from (select e1.ename emp_name,e2.ename mgr_name,e1.deptno deptno from emp e1 inner join emp e2 on e1.mgr=e2.empno) e3 inner join dept on e3.deptno=dept.deptno ;

子查询

嵌套子查询

select * from emp where sal=(select max(sal) from emp)