oracle基础知识3

select ename,sal,emp.deptno,dname,loc from emp,dept
where emp.deptno=dept.deptno;//建立多表连接
hr中
select a.employee_id,a.first_name,salary,a.department_id,department_name,b.location_id,c.city
from employees a,departments b,locations c
where a.department_id=b.department_id and b.location_id=c.location_id and salary>8000;
//上述为等值连接
-------------------------------------------------------------------------------
select * from salgrade;
select empno,ename,sal,grade,losal,hisal
from emp,salgrade
where sal>=losal and sal<=hisal;//非等值连接

set linesize 150;
set pagesize 150;
select employee_id,first_name,salary,a.department_id,department_name
from employees a,departments b
where a.department_id = b.department_id(+);//右外连接 将左侧不满足的也显示

select employee_id,first_name,salary,a.department_id,department_name
from employees a,departments b
where a.department_id(+) = b.department_id;//左外连接 将右侧不满足的也显示

select a.empno,a.ename,a.mgr,b.ename from emp a,emp b
where a.mgr = b.empno; //自连接，把自己当作两个表。
----------------------------------------------------------------------------
符合sql99标准的sql语句
1,select empno,ename,sal,emp.deptno,dname form emp cross join dept;//交叉连接
<--->select empno,ename,sal,emp,depton,dname from emp,dept;
2,自然连接 natural join 基于两个表中的全部同名列建立连接，
a，从两个表中选出同名列的值均对应相等的所有行
b，若两表中同名列的数据类型不同，则出错
c，不允许在参照列上使用表名或者别名作前缀
select empno,ename,sal,depton,dname from emp natural join dept;
select empno,ename,emp.deptno,dname
from emp natural join dept; //这样写错  c
3.using 子句
若不希望参照被连接表的所有同名列进行等值连接，自然连接将无法满足要求，可以在连接时使用using子句来设置用于等值连接的列名；
select empno,ename,sal,depton,dname from emp join dept
using(depton);  不允许在参照列上使用表名或者别名作为前缀
4.on子句
若要参照非同名的列进行等值连接，或者想设置任意的连接条件，可以使用on子句。
select empno,ename,sal,emp.deptno,dname
from emp join dept on(emp.depton=dept.deptno and sal>2500);
----------------------------------------------------------------------
多表连接
select employee_id,first_name,salary,department_id,department_name,location_id,
city from employees join departments using(department_id)
natural join locations;   //locations 相对于departments有相同字段
<----->
select employee_id,first_name,salary,department_id,department_name,location_id,
city from employees join departments using(department_id)
join locations using(location_id);
---------------------------------------------------------------------
内连接和外连接    sql99标准
内连接同原来的
外连接   left outer join, right outer join,full outer join
eg:
select employee_id,first_name,salary,department_id,department_name, from employees right join departments using(department_id);//left join/full outer join
子查询
select * from emp where sal>=(select sal from emp where empno = 7654);
若子查询未返回任何行，则主查询也不会返回任何结果
select * from emp where sal>(select sal from emp where empno =8888);
若子查询返回单行结果，则为单行子查询，可以在主查询中对其使用相应2的单行记录比较运算符号
select * from emp where sal>(select sal from emp where empno=7566);
若子查询返回多行，则为多行子查询，此时不允许对其使用单行记录的比较运算符号
select * from emp where sal>(select avg(sal) from emp group by deptno);//非法
select  employee_id,last_name,salary from employees
where job_id=(select job_id from employees where last_name='Grant');
子查询返回2个结果 不能等于job_id这一个
多行子查询返回多行记录
对多行子查询只能使用多行记录比较符
in 等于表中的任何一个
any 和子查询返回的任意一个值比较
all 和子查询返回的所有值比较
select * from emp where sal>any(select avg(sal) from group by deptno);
select * from emp where job in(select job from emp where ename='MARTIN' or ename="SMITH');
select * from emp where sal>all(select avg(sal) from emp group by deptno);
select * from emp where sal>all(300,2500,1500);
--------------------------------------------------------------------------
topN查询
select * from(select * from emp order by sal desc) where rownum <=5;
rownum只能是<=或<

select rownum ,a.* from (select * from emp order by sal desc) a;//rownum是虚拟的
select rownum ,a.* from (select * from emp order by sal desc) a where rownum>=5 and rownum<=10;//非法
分析rownum>=5 ,从数据库内存中取1比较，不符合条件，丢弃。取下一个，编号依然从一开始。故永远不能满足条件。
   select * from
    (select rownum myno,a.*  from (select * from emp order by sal desc) a)
where myno >=5 and myno <=10;

select * from
    (select rownum myno,a.*  from (select * from emp order by sal desc) a)
where rownum >=5 and rownum <=10;  //不是子查询中rowmun,而是最外层的。