oracle的sql

增加:先增加主键,再增外键
删除:先删除外键,在删主键

*分析函数*
1)允许并列名次、名次不间断，DENSE_RANK()，结果如122344456……
2)不允许并列名次、相同值名次不重复，ROW_NUMBER()，结果如123456……
3)允许并列名次、复制名次自动空缺，rank()，结果如12245558……

union:省略重复的
union all:显示全部

*分页查询*
1.查询你所要查询的内容,以及是否要排序
2.查询伪列,以便于第三步的分页
3.根据伪列进行分页的查询
如果没有排列和查询条件,则'1'可以省略不写

 select s.* from(select rownum r,t.* from (select *from teacher order by sal) t) s where s.r>=1 and s.r<=5;


select * from dept;
select * from teacher;
select tname, sal
  from teacher
 where sal >= 10000
   and sal < 20000;

select * from teacher where job in ('讲师', '研发') order by sal desc;

select t.tname
  from teacher t
  join dept d
    on d.deptno = t.deptno
   and d.dname = '招生部'
   and t.gendar = '男';

select t.tname
  from teacher t
 where t.gendar = '男'
   and t.deptno in (select deptno from dept where dname = '招生部');

select t.tname,
       (select d.dname from dept d where d.deptno = t.deptno) as 部门
  from teacher t;

select t.tname
  from teacher t
 where t.deptno in
       (select d.deptno from dept d where d.dname in ('招生部', '人力部'))

  select t.tname, d.dname
          from teacher t
          left join dept d
            on d.deptno = t.deptno;


select t1.tname, t2.tname
  from teacher t1
  left join teacher t2
    on t1.mgrno = t2.tno;

select t1.tname, d.dname, t2.tname
  from teacher t1
  left join dept d
    on d.deptno = t1.deptno
  left join teacher t2
    on t1.mgrno = t2.tno;

select tname, birthdate from teacher;

select tname from teacher t, dept d where t.deptno = d.deptno;

select tname from teacher
union all
select dname from dept;

 select s.* from(
        select rownum r,t.* from (
               select * from teacher order by sal
        ) t
 ) s
 where s.r>=1 and s.r<=5;
 
 select * from(select rownum r,t.* from (select * from teacher order by sal) t
 where rownum<=5) s where s.r>0;