Oracle(3) SQL 语句

Oracle 过滤递归查询

?

select id from (?
?? select id,rownum?rn from mytable where?rownum?<= 20?
)?where rn >= 2;??

?

?

?

select * from sal*12+comm*13 ?from emp

select * from (sal+nvl(comm,0))*12 from emp --comm奖金

order deptno [asc],sal desc

as 可写可不写 中文带引号 ?英文不带

数据分组函数 max min avg count等

注意查询多个字段如有一个是分组函数其他要查询的都必须为分组函数

如:select min(sal),max(sal) from table?

where后如果有分组的话要某用having要某用子查询

group by ?对查询结果分组统计 注意分组查询必须要查询要分组的字段

having 子句用于限制分组显示结果

select min(sal),avg(sal),max(sal),deptno,job from emp group deptno,job

平均工资小于2000

select avg(sal) from mytable group by deptno having avg(sal) < 2000

分组函数只能出现在选择列，having，order by 中

多表查询的笛卡尔积 表 a,b,c 。。。 表的个数为n ,where 后个数为n-1 才可以消除笛卡尔积

不加条件则查询出的数据为各个表里数据的积。

?between ?and?

自连接

在同一张表的连接查询

select a.name from emp a ,emp b where a.parentid = b.id and a.name ?= 'NameHello'

where ?... and?

注意and前后会影响性能

?

select ?ename ,sal from emp where sal >

all (select sal from emp where deptno = 30)

注：> all 可以改为 > max() 更好

? ?扩> any 可以改为 > min() 更好

?

注意Oracle是从后开始扫描

where多个字段匹配：

where (deptno,job) = (select deptno,job from emp)

查出高于自己部门员工平均工资的员工信息

?

select emp.name,emp.sal,emp.deptno,dept.avgsal from emp emp,(

select deptno,avg(sal) avgsal from emp?

) dept where emp.deptno= dept.deptno

and emp.sal > dept.avgsal

?

里面一个查询作为一个表，也可以看作是一个子表，或内嵌视图

?

as 用法给字段加as 无所谓，但是给表起别名要注意了，Oracle可能会出现问题。

表不写as 肯定没问题?

?

Oracle分页

select * from (

select a1.*,rownum rn from (select * from emp) a1 where rn <= 10

) where rn >= 6;

?

如果想只查询其中几个，只要修改最里的子查询，如排序，分组等都在里面修改。

?

?

分页效率最高的查询(写法很麻烦)

根据ROWID 来分页

select * from my_table where rowid in(

select rid from?

(select rownum rn,rid from?

( select rowid rid,cid from my_table order by cid desc)

where rownum < 1000 )

where rn > 9980)

order b cid desc

?

还有一种row_number() ?这种方式效率最差不研究?

?

select * from (

select t.*,row_number() over(order by cid desc) rk from mytable t?

) where rk < 10000 and rk > 9980

?

把查询结果创建新表

create talbe mytable(id,name,sal) as select empno,ename,sal from emp;

创建表的结构和emp的三个字段的结构一摸一样，数据也放入表中了

?

合并查询

Oracle特有的 union ,union all,intersect,minus

nuion ?和并两个查询的并集(去除重复的)

nuion ?all 不取消重复行，不会排序

intersect 去交集 只取重复的记录

minus 差集 如a minus b ，其中a和b都是一个查询，b的结果在a都有，minus取的数据a-b

这些比 and or 等速度快很多。

?

创建数据库：

工具database configuration ?assistant

一般选择new database?

?

java 连接Class.forName ? Oracle oracle.jdbc.driver.OracleDriver

"jdbc:oracle:thin:@127.0.0.1:1521:myoral"

?

insert into 批量导入

update emp set (job.sal,comm) ?= (select job,sal,comm from emp where ename= 'b')

where ename = 'A'

?

?

?

?

?

?

?

?