22-Oracle入门之总结

发布时间：2020-12-12 15:28:24 所属栏目：百科来源：网络整理

导读：重要知识点：基本语法 select a,b,c from tab1,tab2 where col = 10 where col 10 where col between 1000 and 2000 where col in (10,20,null) wehre col not in (10,null) order by col1 asc,col desc order by 别名表达 3 order by nulls last group by

重要知识点：

基本语法

select a,b,c
from tab1,tab2

where col = 10
where col > 10
where col between 1000 and 2000
where col in (10,20,null)
wehre col not in (10,null)
order by col1 asc,col desc
order by 别名表达 3
order by nulls last
group by a,c
having ....

字符串-->数字
select '111' + 222 from dual ; ===>333
数字--->字符串
select '111' || 222 from dual ; ===> 111222

字符串-->日期

日期--->字符串

select *
from emp
where to_char(hiredate,'yyyy-mm-dd') > '1981-01-01'

select *
from emp
where hiredate > to_date('1981-01-01','yyyy-mm-dd' ) ;

select *
from emp
where hiredate > '1-1月 -81'

--查询员工的薪水:两位小数本地货币代码千位符

select ename,to_char(sal,'L9,999.99')
from emp ;

￥1,600.00
select to_number('￥1,600.00 ',999.99') from dual

-----外连接 eg:按部门统计员工人数: 部门号部门名称各部门人数

1 10 20 30 分组
2 多表
3 count() 组函数

步骤1
select d.deptno,d.dname,count(e.empno)
from dept d,emp e
where d.deptno = e.deptno
group by d.deptno,d.dname

步骤2
select d.deptno,emp e
where e.deptno (+) = d.deptno
group by d.deptno,d.dname
order by 1

常见操作

1 求所有部门的平均奖金
select avg(nvl( comm,0) ) from emp;

select avg(sal) from emp;

2 求各部门的平均薪水

select deptno,avg(sal)
from emp
group by deptno

====>错误案例
select deptno,ename,avg(sal)
from emp
group by deptno
/

select d
from emp
group by a,c

3 求各部门每个工种的平均薪水,并显示部门编号、工种、平均薪水

select deptno,job,avg(sal)
from emp
group by deptno,job -- 先按deptno、job分组，出来n组数据，在这个基础之上，再进行计算

4 求各部门每个工种大于2000的薪水
select sal
from emp
where sal>2000

4-1 求各部门每个工种，平均薪水大于2000的薪水

select deptno,job
having avg(sal) >2000

5 求10号部门的平均工资(2种写法)
select deptno,avg(sal)
from emp
where deptno=10
group by deptno

select deptno,avg(sal)
from emp
group by deptno
having deptno = 10

6 创建一个学生表
sid
sname
email
sex
age
7 并向表中插入一条数据

create table student
(
sid number,
sname varchar2(20),
email varchar2(64),
sex number,
age number
)
insert into student(sid,sname,email,sex,age) values(1,'tom11','11@163.com',1,20)

//只显示大于4个人的部门信息

select deptno,count(*)
from emp
group by deptno
having count(deptno) > 4
/

-- 创建表

-- 1 查询员工号姓名月薪年薪年收入部门名称

select e.deptno,e.ename,e.sal,e.sal*12 yearsal,(e.sal*12+nvl(e.comm,0)) incoming,d.dname
from emp e,dept d
where e.deptno = d.deptno

====>

---2 创建一个表,把第一步骤查询的信息,存放在另外一张表中
create table s1
as
(
select e.deptno,dept d
where e.deptno = d.deptno
)

create view myview1
as
(
select e.deptno,dept d
where e.deptno = d.deptno
)

create table empincome
as

select e.empno,e.sal*12 annalsal,sal*12+nvl(comm,0) income,d.dname
from emp e,dept d
where e.deptno = d.deptno;

--创建表的同时copy数据

常见面试题：

1 select empno,sal from emp order by sal desc 2 select rownum,empno,sal from emp ROWNUM EMPNO ENAME SAL ---------- ---------- ---------- ---------- 1 7369 SMITH 800 2 7499 ALLEN 1600 3 7521 WARD 1250 4 7566 JONES 2975 5 7654 MARTIN 1250 6 7698 BLAKE 2850 7 7782 CLARK 2450 8 7788 SCOTT 3000 9 7839 KING 5000 10 7844 TURNER 1500 11 7876 ADAMS 1100 12 7900 JAMES 950 13 7902 FORD 3000 14 7934 MILLER 1300 已选择14行。 select rownum,sal from emp order by sal desc select rownum,sal from emp where rownum <= 3 order by sal desc oracle的ROWNUM机制 1 ROWNUM就是这么设计 rownum是属于服务器返回的结果集的固有属性 2 where rownum<=3 不能使用 where rownum>=3 生成结果集是1行1行的生成的. 没有第1行就没有第2行没有第2行就没有第3行没有第3行就没有第4行 =====>Top-N select * from (select empno,sal from emp order by sal desc) / select rownum,sal from (select empno,sal from emp order by sal desc) where rownum<=3 =======分页问题 1 select rownum,sal from emp order by sal desc) where rownum>=5 and rownum<=8 select r,sal from ( select rownum r,sal from emp order by sal desc) where rownum<=8 ) where r>=5 解决分页的思路总的原则: 内层:排序外层选... （内层排序外层选，需要三层查询）内：排序。中：使用rownum选择前n条；并给rownum指定一个别名，以供最外层过滤使用。外：去掉前m条结果。查找员工表中薪水大于本部门平均薪水的员工。第一种方法多表查询分析1 10 20 30 要分组 select deptno,avg(sal) avgsal from emp group by deptno 分析2 可以把部门平均薪水的数据看成一个表多表查询分析3 组函数分析4 连接条件分析5 别名 select e.empno,d.avgsal from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d where e.deptno=d.deptno and e.sal > d.avgsal 第二种方法相关子查询子查询分为2种一般子查询相关子查询找到员工表中薪水大于本部门平均薪水的员工。 1 select empno,sal,(员工所在部门的平均工资) avgsal from emp where sal > (员工所在部门的平均工资) avgsal 员工所在部门的平均工资==> select avg(sal) avgsal from emp where deptno = 10 2 select e.empno,( select avg(sal) avgsal from emp where deptno = e.deptno) avgsal from emp e where sal > (select avg(sal) avgsal from emp where deptno = e.deptno ) ===总结主查询通过别名把主查询的值传递给子查询 ..

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!