》分页查询6-10的数据:
select * from
(select rownum rn,t.* from(select id,title,content,occurtime from news order by occurtime desc) t where rn<=10)
where rn>=6;
》连接查询:
bookstudent
内连接:
Select * from book as b join student as s ON b.StudentId=s.StudentId
左连接:
select * from book as b left join student as s ON b.StudentId=s.StudentId
select * from book as b ,student as s where b.studentid(+)=s.studentid
右连接:
select * from book as b right join student as s ON b.StudentId=s.StudentId
select * from book as b ,student as s where b.studentid=s.studentid(+)
全连接:
select * from book as b full outer join student as s ON b.StudentId=s.StudentId
交叉连接:
select * from book as b CROSS Join student as a Order by b.BookId
union和union all的区别是,union会自动压缩多个结果集合中的重复结果,而union all则将所有的结果全部显示出来,不管是不是重复
union:
select ename,job,sal from xcr_emp where job='MANAGER'
union
select ename,job,sal from xcr_emp where sal>2500;
union all:
select ename,job,sal from xcr_emp where job='MANAGER'
union all
select ename,job,sal from xcr_emp where sal>2500;
over():开窗函数,Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是:对于每个组返回多行,而聚合函数对于每个组只返回一行
rank()和dense_rank()可以将所有的都查找出来,rank可以将并列第一名的都查找出来;rank()和dense_rank()区别:rank()是跳跃排序,有两个第二名时接下来就是第四名。
row_number()该函数可以根据指定的字段分组,再按照指定的字段排序,然后生成组内连续且唯一的数字。
select ename,sal,deptno,row_number() over(partition by deptno order by sal desc)chen from xcr_emp;
select ename,sal,deptno,rank() over(partition by deptno order by sal desc)chen from xcr_emp;
连续但唯一
select ename,sal,deptno,dense_rank() over(partition by deptno order by sal desc)chen from xcr_emp;
连续但不唯一