Oracle多表连接与子查询
Oracle多表连接与子查询
1:等值连接 迪卡尔集连接 select ename, a.deptno as a_deptno,b.deptno as b_deptno ,b.dname as 部门 from emp a, dept b 等值连接 select ename, a.deptno as a_deptno,b.deptno as b_deptno ,b.dname as 部门 from emp a, dept b where a.deptno = b.deptno; 2:非等值连接,在emp表和salgrade表中查找员工的姓名,工资,等级,工资上线,工资下线 select ename as 姓名, sal as 工资, grade as 工资等级,losal as 工资上线,hisal as 工资下线 from emp, salgrade where sal between losal and hisal; 3:外连 右外连 select e.ename, d.dname, e.deptno from emp e,dept d where e.deptno = d.deptno(+); 左外连与右外连相反 以hr登陆查询表employees 和departments 表 select first_name as 姓名, department_name as 部门名称, d.department_id as 部门编号 from employees e, departments d where e.department_id= d.department_id(+) ; 4:自连:在同一个表中查询每个员工及上司的工号和姓名 select a.empno as 员工编号, a.ename as 员工姓名, a.mgr as 上司的员工编号, b.ename as 上司姓名 from emp a, emp b where a.mgr = b.empno; 5:sql99 交叉连接 cross join------相当于迪卡尔集 select e.ename,d.dname from emp e cross join dept d; 自然连接 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子句 ------当列名不同时用on子句 用on查询两张表 select e.ename, d.dname from emp e join dept d on e.deptno = d.deptno; 用on查询多张表 select e.ename, d.dname from emp e join dept d on e.deptno = d.deptno join 第三个表 on 列1 = 列2; 内连接(Inner join)-------内连接只返回满足连接条件的数据 select employee_id, last_name, salary, department_id, department_name from employees inner join departments using(department_id); 左外连 select employee_id, last_name, salary, department_id, department_name from employees left join departments using(department_id); 右外连 select employee_id, last_name, salary, department_id, department_name from employees right join departments using(department_id); 满外连 select employee_id, last_name, salary, department_id, department_name from employees full outer join departments using(department_id); 子查询------------ 查找出工资比scott高的人 select ename, sal from emp where sal> (select sal from emp where ename='SCOTT'); 查找那些人和scott相同职位的人 select ename, job from emp where job= (select job from emp where ename='SCOTT') and ename <> 'SCOTT'; any的用法 < any意味着小于最大、> any大于最小 select empno, ename, sal, job from emp where sal<any(select sal from emp where job='SALESMAN'); all的用法 < all:小于所有,即小于最小、> all:大于所有,即大于最大 select empno, ename, sal, job from emp where sal<all(select sal from emp where job='SALESMAN');