增加:先增加主键,再增外键
删除:先删除外键,在删主键
*分析函数*
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;
删除:先删除外键,在删主键
*分析函数*
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;