oracle的学习(各种摘抄)

 今天主要接触了oracle的with as的用法。

1.with 别名 as (select * from *) select * from 别名   -- 相当于建个临时表

2.多表就是:

with a as (select '1','2' from dual), --逗号

b as (select '1','2' from dual) --第二个表不需要with关键字

select * from a,b;

是个临时存储,一般是在存储过程里使用的

可以做多个表的连接，结果集的连接查询

----再次总结with as

WITH AS短语，也叫做子查询部分（subquery factoring），该语句实质上就是创建临时视图，使用WITH AS短语，则只要执行一遍即可。如果WITH AS短语所定义的表名被调用两次以上，则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里，如果只是被调用一次，则不会。

语法：
WITH query_name AS (subquery)
[, query_name AS (subquery) ]...

1. 最简单的使用方法：

如查询部门名称包含“A”的所有员工信息

01--with clause 02 03with a as04 05(select deptno from dept where dname like'%A%') 06 07select* from emp where deptno in(select* froma); 08 09with a as10 11(select deptno from dept where dname like'%A%'),--a结果集 12 13a2 as(select* from a where deptno>20)--a1结果集直接从a中筛选 14 15select* from emp where deptno in(select* from a2);




2. 多层同级只能用一个with，并且后面的结果集可以使用前面的结果集：

查询部门名称包含“A”并且部门编号大于20的所有员工信息

1with a as2 3(select deptno from dept where dname like'%A%'),--a结果集 4 5a2 as(select* from a where deptno>20)--a1结果集直接从a中筛选 6 7select* from emp where deptno in(select* from a2);




3. 不同级查询可以使用多个with：

查询部门名称包含“A”并且部门编号大于20的所有员工信息的另外一种实现方式如下

01with a as02 03(select deptno from dept where dname like'%A%')--a结果集 04 05select* from emp where deptno in(--括号内层作为子查询，为第二级 06 07with a2 as(select* froma where deptno>20)--a1结果集直接从a中筛选 08 09select* from a2 10 11);





例1：获取student表全部数据

with s as

(select * from students)

select * from s;



例2：获取小于平均成绩的学生学号和成绩

with c as
(select avg(score) as value from courseresults)
select sid,score from courseresults,c where score>c.value;



例3：获取课程平均成绩大于85分的男生的学号

with
s1 as
(select sid from courseresults group by sid having avg(score)>85),
s2 as
(select sid from students where sex='m')
select * from s1 intersect select * from s2;

同时还接触了游标。

用oracle sql对数字进行操作: 取上取整、向下取整、保留N位小数、四舍五入、数字格式化

取整（向下取整）：
select floor(5.534) from dual;
select trunc(5.534) from dual;
上面两种用法都可以对数字5.534向下取整，结果为5.


如果要向上取整 ，得到结果为6，则应该用ceil
select ceil(5.534) from dual;




四舍五入：
SELECT round(5.534) FROM dual;
SELECT round(5.534,0) FROM dual;
SELECT round(5.534,1) FROM dual;
SELECT round(5.534,2) FROM dual;
结果分别为 6, 6, 5.5, 5.53




保留N位小数（不四舍五入）：
select trunc(5.534,0) from dual;
select trunc(5.534,1) from dual;
select trunc(5.534,2) from dual;
结果分别是 5,5.5,5.53，其中保留0位小数就相当于直接取整了。




数字格式化：
select to_char(12345.123,'99999999.9999') from dual;
结果为12345.123

select to_char(12345.123,'99999999.9900') from dual;
小数后第三第四为不足补0，结果为12345.1230

select to_char(0.123,'99999999.9900') from dual;
select to_char(0.123,'99999990.9900') from dual;
结果分别为 .123, 0.123