Oracle 数据常用查询技巧 – ben - 生命不止，贲流不息！

常用的多表连接查询

连接：
- 内连接 inner join ..on
- 外连接 left/right join ..on
- 自连接 table t1 , table t2 把一张表当两张表来使用交叉连接笛卡儿积
内连接：等值连接、不等值连接、自然连接
外连接：左外连接、全连接、右外连接

SQL函数

包括：单行函数、聚合函数、分析函数
单行函数：
- 单行函数对于从表中查询的每一行只返回一个值可以出现在 SELECT 子句中和 WHERE 子句中单行函数可以大致划分为：数字函数、字符函数、日期函数、转换函数、其他函数
聚合函数：
- 聚合函数基于一组行来返回结果，为每一组行返回一个值COUNT\SUM\AVG\MIN\MAX（又称统计函数）
配合 GROUP BY子句使用，用于将信息划分为更小的组，每一组行返回针对该组的单个结果HAVING子句，用于指定 GROUP BY 子句检索行的条件
分析函数：
- 分析函数根据一组行来计算聚合值（作用：用于计算完成聚集的累计排名等）分析函数为每组记录返回多个行

以下三个分析函数用于计算一个行在一组有序行中的排位，序号从1开始

ROW_NUMBER 返回连续的排位，不论值是否相等
RANK 具有相等值的行排位相同，序数随后跳跃
DENSE_RANK 具有相等值的行排位相同，序号是连续的

分页的使用（利用Oracle伪列rownum）

先按照条件：教师编号降序排列，进行排序查询
筛选最大范围内的数据结果集 1-6
筛选当前结果集中最小范围的数据 4-6

select *
from (select e.tname, rownum rn
from (select tname from teacher order by tno) e
where rownum <= 6)
where rn >= 4;

一般优化技巧

--用 EXISTS 代替 distinct 优化 查询有多少不同的机构类型
select DISTINCT d.deptno ,d.dname from teacher t 
INNER JOIN dept d ON t.deptno=d.deptno;

SELECT d.deptno ,d.dname FROM dept d 
WHERE EXISTS (SELECT 'X' FROM teacher t WHERE t.deptno=d.deptno);

--exists代替in 查询

SELECT count(t.deptno) FROM teacher t
WHERE t.deptno IN (SELECT d.deptno FROM dept d WHERE d.deptno='20' );

SELECT count(t.deptno) FROM teacher t 
WHERE EXISTS (SELECT 'X' FROM dept d 
WHERE d.deptno=t.deptno 
AND d.deptno='20');

表连接和驱动表选择

--先根据名称查询
select * from TEACHER t WHERE trim(T.TNAME)='李寻欢'

--后创建索引
create index IDX_TEACHER_NAME on TEACHER (tname);

--在查询进行对比
SELECT * FROM TEACHER T WHERE T.TNAME = '李寻欢'

解释计划窗口查看驱动表
选择驱动表有如下3个规则：
1. A\B两张表都没有索引的情况下，from 后面靠后的那个表就是驱动表
2. A\B两张表中，一张有索引，一张没索引的情况下，驱动表是没索引的那张
3. 驱动表要选择小表（查询后，得到的结果集数据量小的那张表

创建索引

SELECT COUNT(0) FROM scms_org_code t1 , scms_zone_code t2 WHERE t1.zone_code=t2.zone_code AND t1.hositype_code='C220';

SELECT COUNT(0) FROM scms_org_code t1 , scms_zone_code t2 WHERE t1.hositype_code='C220' and t1.zone_code=t2.zone_code

创建及使用视图

--分页查询
select t1.* from 
(select t.*,rownum rn from 
(select * from teacher order by tno) t
) t1
where t1.rn >=6 
and t1.rn <=10

--创建视图需要sysdba赋予权限（例如system用户执行授权）
命令为：grant create any view to bdqn

/*create view v_teacher_page as 
select t1.* from 
(select t.*,rownum rn from 
(select * from teacher order by tno) t
) t1*/

--视图只能用于查询，大多用于报表查询
select * from v_teacher_page 
where rn >=6 and rn<=10


--无权限，则记得授权
grant create any view to bdqn;
--部门以及部门下有几名教师。
create or replace view v_dept
AS select d.deptno ID,d.dname NAME,d.loc loc,(SELECT COUNT(0)
FROM teacher t WHERE t.deptno=d.deptno) num from DEPT d ;


INSERT INTO v_dept(ID,NAME,loc) VALUES(90,'ss','ss');

DML 操作

insert into dept(deptno,dname,loc) values(10,'研究院','北京海淀');
insert into teacher(tno,tname,gendar,sal) values(1001,'陈老师','女',30000);
insert into teacher(tno,tname,gendar,sal) values(1002,'蒋老师','男',30000);
commit;
-- 创建序列
create sequence sq_teacher
start with 1001
increment by 1;

--插入数据
insert into teacher(tno,tname,gendar,sal) values(sq_teacher.nextval,'韩老师','女',30000);
commit;

--更新数据
update teacher set deptno=10;
commit;
update teacher set sal=sal+2000;
commit;

--删除数据
delete from teacher where tname like '蒋%';

--回滚数据
rollback;

--删除整张表所有数据
delete from dept

--清空表数据
truncate table teacher;

--移除表结构
drop table teacher; 
drop table dept;

基础查询

-- 根据条件查询
select tno,tname from teacher where gendar='女';

-- 按条件模糊查询
select tno,tname,tid from teacher where gendar='女' and tname like '韩%';

-- 查询所有薪资大于等于1万，小于20000的老师信息
select * from teacher where sal>=10000 and sal<20000;

-- 查询讲师或研发岗位人员的信息，按薪资降序显示
select * from teacher where job in ('讲师','研发') order by sal desc;

-- 把部门所有数据的SQL插入语句作为数据存入数据库（Oracle数据库的字符串拼接）
-- 演变过程1-4
-- 1.select 'insert into dept values(',deptno,dname,loc,');' from dept;

-- 2.select 'insert into dept values('||deptno||dname||loc||');' from dept;

-- 3.select 'insert into dept values('||deptno||','||dname||','||loc||');' from dept;

-- 4.select 'insert into dept values('||deptno||','''||dname||''','''||loc||''');' from dept;

子查询

select t.tname from teacher t
where t.gendar='男' 
and t.deptno =(select d.deptno from dept d where d.dname='招生部');

-- 方法1：
select t.tno,t.tname from teacher t
where t.deptno in (select d.deptno from dept d 
where d.dname in ('招生部','人力部'));

-- 方法2：
select t.tno,t.tname from teacher t
where exists (select d.deptno from dept d 
where d.deptno = t.deptno 
and d.dname in ('招生部','人力部'));

连接查询

select t.tname from teacher t 
inner join dept d on t.deptno=d.deptno 
where d.dname='招生部' and t.gender='男';
--条件的筛选顺序是从后往前，所以要把能过滤掉大量数据的条件写在最后

--select tname from teacher t natural join dept d where d.dname='招生部';
--自然连接 内连接特殊情况(两个表同名的列不能超过1个)，不推荐使用

select tname from teacher t 
inner join dept d on t.deptno=d.deptno ;
--发现少了些老师,因为有些老师没有部门


select tname from teacher t 
left join dept d on t.deptno=d.deptno ;
--如果要显示所有老师，则把内连接改为左外连接即可

--简便的一种写法,效果等同于以dept为主表的左外连接
select tname,dname from teacher t,dept d 
where d.deptno=t.deptno(+);

--自连接
select t.tname 姓名,t1.tname as 导师,d.dname as 部门 
from teacher t 
left join teacher t1 on t.mgrno=t1.tno 
left join dept d on t.deptno=d.deptno

单行函数

--四舍五入至指定位数 -1代表截取到十位，以此类推/截取舍弃相应位数的小数
select round(1115.2,-1),trunc(111.2,1) from dual;

--截取字符串，从第3个字符开始截取4个字符
select substr('abcdefg',3,4) from dual;

--拼接字符串
select concat('Hello','World') from dual;

--字符函数 从第1位开始截取2个数字，搜索字符c所在的位置
select substr('oracle',1,2),instr('oracle','c') from dual;

--转换函数to_date
select to_date('2013-01-12 12:14:14','yyyy-mm-dd hh24:mi:ss') from dual;

--转换函数to_char,to_number
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(123,'$99999.99'),to_number('1111') from dual;

--日期函数，获取当前系统日期，在日期基础上增减天数
select sysdate,sysdate+10,sysdate-30 from dual;

--转换函数NVL，当值为NULL时，无法进行数字运算，利用它可以转换NULL为0，
DECODE类似java中的switch-case-default
SELECT NVL(comm,0)+sal,NVL2(comm,comm,0)+sal,DECODE(comm,NULL,0,comm)+sal FROM teacher;

DECODE(comm,NULL,0,comm)+sal
--判断comm是否为NULL，如果是则用0计算，否则用comm原值

聚合函数

select 
avg(sal),
min(sal),
max(sal),
sum(sal),
count(*) 
from teacher 
group by deptno 
having count(*)>5;

分析函数

/* 分析函数
rank 根据deptno排名 排名条件为sal，允许并列排名，但排名不连续，直接顺延
例如：1 2 2 4
dense_rank 根据deptno排名 排名条件为sal，允许并列排名，但排名连续
例如：1 2 2 3
row_number 根据deptno排名 排名条件为sal，不允许并列排名
例如：1 2 3 4
over() 条件函数
partition by 排名字段
order by 排序字段
*/
select tname,deptno,sal, 
rank() over(partition by deptno order by sal desc),
dense_rank() over(partition by deptno order by sal desc) ,
row_number() over(partition by deptno order by sal desc) 
from teacher;

rank() over是的作用是查出指定条件后进行一个排名，但是有一个特点。假如是对学生排名，那么实用这个函数，成绩相同的两名是并列，例如下图1 2 2 4。
dense_rank()的作用和rank()很像，唯一的一点区别就是，领命学生的成绩并列以后，下一位同学并不空出并列所占的名次，例如下图1 2 2 3。
row_number()就不一样了，它和上面两种的区别就很明显了，这个函数不需要考虑是否并列，哪怕根据条件查询出来的数值相同也会进行连续排名，如下图

分页查询

select rowid,rownum,t.* from teacher;
--rowid 伪列 oracle的物理地址，rownum为结果集序号

--获取前五条数据
select rownum,tname from teacher where rownum <=5;

--获取按薪水排名第6到第10条数据,rownum以结果集为基础进行查询
select * from (select rownum as rn,tname from 
(select * from teacher order by sal desc)e
where rownum<=10) t
where t.rn >=6
/*
SQL分页步骤：
1.给SQL语句加限制条件进行子查询所有数据
2.给结果集过滤最大范围
3.给结果及过滤出最小的范围

按排名进行查询 */
select * from (select teacher.*,dense_rank() over(order by sal desc) mingci from teacher)
where mingci = 5;

常用的多表连接查询

SQL函数

以下三个分析函数用于计算一个行在一组有序行中的排位，序号从1开始

分页的使用（利用Oracle伪列rownum）

一般优化技巧

表连接和驱动表选择

创建索引

创建及使用视图

DML 操作

基础查询

子查询

连接查询

单行函数

聚合函数

分析函数

分页查询

留言 取消回复

留言取消回复