常用的多表连接查询

  • 连接:
    • 内连接 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. 先按照条件:教师编号 降序排列,进行排序查询
  2. 筛选最大范围内的数据结果集 1-6
  3. 筛选当前结果集中最小范围的数据 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;
  1. rank() over是的作用是查出指定条件后进行一个排名,但是有一个特点。假如是对学生排名,那么实用这个函数,成绩相同的两名是并列,例如下图1 2 2 4。
  2. dense_rank()的作用和rank()很像,唯一的一点区别就是,领命学生的成绩并列以后,下一位同学并不空出并列所占的名次,例如下图1 2 2 3。
  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;