Oracle(三) SQL 语句

Oracle 过滤递归查询

 

select id from department d where d.is_parent = 0 start with d.id= 3 connect by prior d.id=d.parent_id

然后可以该分类下凡是是没子节点的分类员工信息  select id,name from emp where department_id in (上一个查询) 

Oracle 分页查询

尽量少用或不用between startnum and endnum;

select id from (

select id,row_number() over ( order by name) rn from mytable
) where rn between 100 and 150;

推荐使用虚拟表

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'