一、多表查询
一、多表查询 从多张表查询数据 ， 根据需求分析 发现得到的数据来源为多张表
1.内连接
查询特点 作为关联查询的两张表，必须有完全匹配的条件数据 才会提取记录
1）隐式内连接 select * from a,b where a.列=b.列
2）显式内连接 select * from a inner join b on a.列=b.列
2.外连接
1）左外连接 select * from a left join b on a.列=b.列
以左表为基准，左表数据全部提取，右表数据作为
补充显示，没有数据匹配 显示为null
2）右外连接
select * from b right join a on a.列=b.列
以右表为基准，右表数据全部提取，左表数据作为补充显示，
没有数据匹配 显示为null
说明：
1）内连接查询结果为两表的交集
左外查询结果为左表全部和右表中与左表有关联的内容，无数据显示null
右外查询结果为右表全部和左表中与左表有关联的内容，无数据显示null
2）内链接方式较外连接效率低，内链接存在一个消除笛卡尔集的过程，先查询两表所有数据进行一一关联（左表每条数据都会和右边每条数据合并成为一条完整数据），再通过where条件进行消除笛卡尔集
3.oracle特有外连接
使用符号 (+)
将符号（+）放在作为补充显示的表的列后面
4.自连接
自己跟自己做关联查询
规则 ：必须给表起别名用于区分两张一样的表
select * from a a1,a a2 where a1.列=a2.列 分析题目得到数据的来源都为同一张表
5.转换列值的显示
条件判断
1）通用条件表达式——–
case 列
when 列值 then 显示值
when 列值2 then 显示值2
when 列值3 then 显示值3
else
默认值
end ;—表达式结束
–2）oracle特有条件表达式
decode(列,列值1,显示值1，列值2,显示值2,’默认值’)
/*
一、多表查询 从多张表查询数据
根据需求分析 发现得到的数据来源为多张表
*/
--例：查询员工的信息和员工的部门信息
--步骤：
--1.定义查询数据 员工 部门
--2.定义数据来源 emp dept
--3.加入条件
select * from emp;
--左外连接实现
select * from emp left join dept on emp.deptno = dept.deptno;
--右外连接实现
select * from emp right join dept on emp.deptno=dept.deptno
--隐式内链接
select * from emp , dept where emp.deptno = dept.deptno;
--显示内链接
select * from emp inner join dept on emp.deptno=dept.deptno;
/*
1.内连接
查询特点 作为关联查询的两张表，必须有完全匹配的条件数据 才会提取记录
1）隐式内连接 select * from a,b where a.列=b.列
2）显式内连接 select * from a inner join b on a.列=b.列
*/
/*
2.外连接
1）左外连接 select * from a left join b on a.列=b.列
以左表为基准，左表数据全部提取，右表数据作为
补充显示，没有数据匹配 显示为null
2）右外连接
select * from b right join a on a.列=b.列
以右表为基准，右表数据全部提取，左表数据作为
补充显示，没有数据匹配 显示为null
*/
/*
3.oracle特有外连接
使用符号 (+)
将符号（+）放在作为补充显示的表的列后面
*/
select * from emp , dept where emp.deptno = dept.deptno (+);
/*
4.自连接
自己跟自己做关联查询
规则必须给表起别名用于区分两张一样的表
select * from a a1,a a2 where a1.列=a2.列
分析题目得到数据的来源都为同一张表
*/
--例1.查询员工的信息和员工的领导信息
--例2.查询员工编号 员工姓名 员工的领导编号，领导姓名，员工的部门名称
select e1.empno , e1.ename ,e2.empno,e2.ename, d.dname from emp e1
left join emp e2 on e1.mgr = e2.empno
left join dept d on e1.deptno = d.deptno -- 左外
--例3.在上面基础上 查询员工的工资等级 salgrade
select * from salgrade ;
select e1.empno 员工编号 , e1.ename 员工姓名 ,e2.empno 经理编号 ,e2.ename 经理姓名,
d.dname 部门名,s.grade 员工等级
from emp e1
left join emp e2 on e1.mgr = e2.empno
left join dept d on e1.deptno = d.deptno
left join salgrade s on e1.sal between s.losal and s.hisal
--例4.在上面基础之上 再查询领导的工资等级
select e1.empno 员工编号 , e1.ename 员工姓名 ,e2.empno 经理编号 ,e2.ename 经理姓名,
d.dname 部门名,s.grade 员工工资等级 ,s2.grade 领导工资等级
from emp e1
left join emp e2 on e1.mgr = e2.empno
left join dept d on e1.deptno = d.deptno
left join salgrade s on e1.sal between s.losal and s.hisal
left join salgrade s2 on e2.sal between s2.losal and s2.hisal
--------------错误示例--------------------------------
select e.empno,e.ename,d.dname,s1.grade,
m.empno m_empno,m.ename m_ename,s1.grade
from emp e,emp m,dept d ,salgrade s1
where e.mgr=m.empno and e.deptno=d.deptno
and e.sal between s1.losal and s1.hisal
and m.sal between s1.losal and s1.hisal
--都从同一张表中查询，这样领导和员工的工资必须在同一个等级
--与需求不同
--5.转换列值的显示
/*
if grade ==1 显示 一级 else if grade==2 显示二级 else 默认值
条件判断
通用条件表达式--------
case 列
when 列值 then 显示值
when 列值2 then 显示值2
when 列值3 then 显示值3
else
默认值
end ;---表达式结束
--oracle特有条件表达式
decode(列,列值1,显示值1，列值2,显示值2,'默认值')
*/
select e1.empno 员工编号 , e1.ename 员工姓名 ,e2.empno 经理编号
,e2.ename 经理姓名, d.dname 部门名,
case s.grade
when 1 then '一级'
when 2 then '二级'
when 3 then '三级'
when 4 then '四级'
else
'五级'
end 员工工资等级
,case s2.grade
when 1 then '一级'
when 2 then '二级'
when 3 then '三级'
when 4 then '四级'
when 5 then '五级'
else
null
end 领导工资等级
from emp e1
left join emp e2 on e1.mgr = e2.empno
left join dept d on e1.deptno = d.deptno
left join salgrade s on e1.sal between s.losal and s.hisal
left join salgrade s2 on e2.sal between s2.losal and s2.hisal
--oracle特有 ，decode(列,列值1,显示值1，列值2,显示值2,'默认值')
select e1.empno 员工编号 , e1.ename 员工姓名 ,e2.empno 经理编号
,e2.ename 经理姓名, d.dname 部门名,
decode(s.grade,1,'一级',2,'二级',3,'三级',4,'四级','五级') 员工工资等级
,decode(s2.grade,1,'一级',2,'二级',3,'三级',4,'四级',5,'五级',null) 领导工资等级
from emp e1
left join emp e2 on e1.mgr = e2.empno
left join dept d on e1.deptno = d.deptno
left join salgrade s on e1.sal between s.losal and s.hisal
left join salgrade s2 on e2.sal between s2.losal and s2.hisal
：
emp（员工表）
dept（部门表）<喎? f/ware/vc/”=”” target=”_blank” class=”keylink”>vcd4ncjxwpjxpbwcgywx0pq==”这里写图片描述” src=”/uploadfile/collfiles/20180426/20180426090446584.png” title=”\” />
salgrade（工资等级表）
二、子查询
在查询语句中嵌套查询语句 不限制一条
1单行子查询 ：子查询语句返回单列单值
select * from a where a.列=(select语句必须返回单列单值)
2多行子查询 子查询语句①返回单列多值（使用in进行条件判断），或者②多列多值（作为虚拟表使用）
① select * from a where a.列 in (select语句必须返回单列多值)
② select * from a,(select语句返回多列多值) t where a.列 =t.列
3.子查询的特殊使用
普通子查询的执行顺序—-> 先执行括号内的子查询语句 得到结果用于主查询使用
exists 存在
1）作用：
是判断 sql语句结果集是否存在
exists(sql查询语句) 没有结果集 false
有结果集 true
2）执行数序
exists作为子查询如果子查询语句关联主查询的列值 执行顺序发生更改
先走主查询得到列值 用于子查询的判断 满足条件 返回当前主查询记录
3）in和exists的使用场景区别
根据表中的记录分析 如果主查询的表记录少 推荐使用exists
如果子查询的表记录少 推荐使用in
先走的sql语句记录结果少 效率高
/*
二、 子查询的使用
在查询语句中嵌套查询语句 不限制一条
1）单行子查询
select * from a where a.列=(select语句必须返回单列单值)
2）多行子查询
① select * from a where a.列 in (select语句必须返回单列多值)
② select * from a,(select语句返回多列多值) t where a.列 =t.列
*/
--例1：查询比员工7654工资高，同时从事和7788相同工作的员工信息？
--1.员工信息
--2.emp
--3.条件 sal>7654的工资 job=7788的工作
select * from
emp
where
sal > (select sal from emp where empno = 7654)
and
job = (select job from emp where empno = 7788)
--例2；查询每个部门的最低工资，和最低工资的员工信息及员工的部门名称
--员工信息 员工的部门 部门最低工资
--内链接
select e.*,d.* from
emp e,dept d, (select min(sal) m_sal from emp group by deptno) ms
where e.deptno = d.deptno and e.sal = ms.m_sal
--外连接
select e.*,d.* from
emp e
right join dept d on e.deptno = d.deptno
right join (select min(sal) m_sal from emp group by deptno) ms
on ms.m_sal = e.sal
insert into emp values (1112,'zs','job',7369,'1990/11/11',800.00,800.00,20)
select * from emp
--例3：查询不是领导的员工信息
--1.员工信息
--2.emp
--3.不是领导
---如果其余 > < = in 判断null值 unkonw
select distinct mgr from emp where mgr is not null --查询出领导id
select * from emp where empno
not in (select distinct mgr from emp where mgr is not null);
--例4.查询每个部门的最低工资，和最低工资的员工信息及员工的部门名称
--emp员工表 ，dept部门表 ， 部门最低工资表
select deptno,min(sal) from emp group by deptno; --部门最低工资表
select e.*,d.* from
emp e,dept d, (select deptno,min(sal) m_sal from emp group by deptno) ms
where e.deptno = d.deptno and e.sal = ms.m_sal
--错误示例：
--查询结果是各部门工资，是否在 800 950 1300 中
select * from emp where sal in (select min(sal) from emp group by deptno)
/*
3.子查询的特殊使用
普通子查询的执行顺序----> 先执行括号内的子查询语句 得到结果用于主查询使用
exists 存在
1）作用是判断 sql语句结果集是否存在
exists(sql查询语句) 没有结果集 false
有结果集 true
2）执行数序
exists作为子查询如果子查询语句关联主查询的列值 执行顺序发生更改
先走主查询得到列值 用于子查询的判断 满足条件 返回当前主查询记录
3）in和exists的使用
根据表中的记录分析 如果主查询的表记录少 推荐使用exists
如果子查询的表记录少 推荐使用in
先走的sql语句记录结果少 效率高
*/
--例.查询有员工的部门信息
--1.查询数据：部门信息
--2.数据来源：dept
--3.条件：部门有员工
select distinct deptno from emp; --有员工的部门
-- in 子查询
select * from dept where deptno in (select distinct deptno from emp);
--exists
select * from dept where exists (select * from emp where emp.deptno = dept.deptno );
三、分页查询
分页提取特定的条数 mysql limit 6 ,5 扫描11行
limit 900000 ,10 扫描900010行
1.rownum：
1）概念
oracle使用rownum 是一个伪列 数据库提取记录才会生成的数值 1,2,3,4
作用是用于实现oracle的分页 必须使用子查询实现
2）执行流程（带条件）
a.查询emp表，生成emp伪表
b.成一个rownum
c.根据分页条件判断该rownum是否与该条件匹配
d.条件匹配 取出该条记录
d.生成第二个rownum重复c操作
3)别名问题
由于rownum的where判断执行在select关键字之前，
当前查询中的rownum别名不能用于条件中做判断，别名只可以用于外部条件判断
见例6的最后一个查询
2.rowid 是数据库保存记录时候生成的真实物理地址 唯一不变
作用： 数据库操作记录使用
索引值→rowid->将rowid换算成一行数据的物理地址->得到一行数据
第一部分6位表示：该行数据所在的数据对象的 data_object_id；
第二部分3位表示：该行数据所在的相对数据文件的id;
第三部分6位表示：该数据行所在的数据块的编号；
第四部分3位表示：该行数据的行的编号；
/*
三、分页提取特定的条数 mysql limit 6 ,5 扫描11行
limit 900000 ,10 扫描900010行
1.rownum： 1）概念
oracle使用rownum 是一个伪列 数据库提取记录才会生成的数值 1,2,3,4
作用是用于实现oracle的分页 必须使用子查询实现
2）执行流程（带条件）
a.查询emp表，生成emp伪表
b.成一个rownum
c.根据分页条件判断该rownum是否与该条件匹配
d.条件匹配 取出该条记录
d.生成第二个rownum重复c操作
3)别名问题
由于rownum的where判断执行在select关键字之前，
当前查询中的rownum别名不能用于条件中做判断，别名只可以用于外部条件判断
见例6的最后一个查询
*/
--例1：提取员工表前三行
select rownum, emp.* from emp where rownum <4;
--例2：提取4行之后的
--错误示例
select rownum,emp.* from emp where rownum >3; --错误
--生成第一个rownum，进行条件判断时不符合，无法提取结果，结束查询，结果为空--
--解决方案，先查询带rownum的伪表
select rownum,emp.* from emp
--查询伪表，选出4条以后的数据
select * from (select rownum r,emp.* from emp) re where re.r >3;
--例4：提取工资排行前三的员工
--错误示例：虽然查询了结果，但是似乎并不是前工资排名前三的员工
select rownum ,emp.* from emp where rownum <4 order by sal desc;
--原因分析：where条件的执行 在 order by 之前 ，也就是先生成了rownum之后，才进行了排序
-- 显然这时候序号已经生成好了
--正确示例
select * from emp order by sal desc --先排序
select rownum ,e.* from (select * from emp order by sal desc) e where rownum <4;
--例5.提取6----10的记录
--扫描全表生成伪表，再进行提取分页(表数据多时效率极低)
select * from (select rownum r, e.* from emp e ) er where er.r > 5 and er.r <11;
--提高效率后的写法，只扫描10行提取分页生成伪表
select * from (select rownum r, e.* from emp e where rownum < 11) er where er.r > 5;
--例6.排序加分页
--a排序
select * from emp order by sal desc
--b生成前10条伪表
select rownum , t1.*
from (select * from emp order by sal desc ) t1
where rownum <11
--提取6到10
select * from
(select rownum r , t1.* from (select * from emp order by sal desc ) t1
where rownum <11) t2 where t2.r >4
/*
2.rowid 是数据库保存记录时候生成的真实物理地址 唯一不变
数据库操作记录使用
索引值→rowid->将rowid换算成一行数据的物理地址->得到一行数据
第一部分6位表示：该行数据所在的数据对象的 data_object_id；
第二部分3位表示：该行数据所在的相对数据文件的id;
第三部分6位表示：该数据行所在的数据块的编号；
第四部分3位表示：该行数据的行的编号；
rownum 是查询记录生成 根据条件 排序 rownum的值不一样
用于分页
*/
select rowid,emp.* from emp;
练习
--例7.找到员工表中薪水大于本部门平均薪水的员工
--员工信息 部门平均薪水
--员工表 部门平均薪水表
--条件 员工薪水大于本部门平均薪水
--1）查询部门薪水伪表
select deptno, avg(sal) from emp group by deptno
--2）表联查 关联条件 e.deptno = a_sal.deptno
-- 需求条件 e.sal > a_sal.a
select * from
emp e, (select deptno, avg(sal) a from emp group by deptno) a_sal
where e.deptno = a_sal.deptno and e.sal > a_sal.a
例8统计每年入职的员工个数
要求显示结果如下:
--例8统计每年入职的员工个数
select to_char(hiredate,'yyyy') hire_year ,count(1) count
from emp group by to_char(hiredate,'yyyy')
---处理格式 使用decode尝试竖起来一列
select decode(t.hire_year,'1987',t.hire_count) "1987"
from
(select to_char(hiredate,'yyyy') hire_year,count(1) hire_count
from emp group by to_char(hiredate,'yyyy')) t
--聚合函数忽略null值的记录
select avg(decode(t.hire_year,'1987',t.hire_count)) "1987"
from
(select to_char(hiredate,'yyyy') hire_year,count(1) hire_count
from emp group by to_char(hiredate,'yyyy')) t
-- 使用聚合函数补全其余的列，
select avg(decode(t.hire_year,'1980',t.hire_count)) "1980",
avg(decode(t.hire_year,'1981',t.hire_count)) "1981" ,
avg(decode(t.hire_year,'1982',t.hire_count)) "1982" ,
avg(decode(t.hire_year,'1987',t.hire_count)) "1987"
from
(select to_char(hiredate,'yyyy') hire_year,count(1) hire_count
from emp group by to_char(hiredate,'yyyy')) t
--加入sum求和运算补全total
select sum(t.hire_count) total,
avg(decode(t.hire_year,'1980',t.hire_count)) "1980",
avg(decode(t.hire_year,'1981',t.hire_count)) "1981" ,
avg(decode(t.hire_year,'1982',t.hire_count)) "1982" ,
avg(decode(t.hire_year,'1987',t.hire_count)) "1987"
from
(select to_char(hiredate,'yyyy') hire_year,count(1) hire_count
from emp group by to_char(hiredate,'yyyy')) t
四、集合使用
1.交集 取两个集合共同的部分 intersect a(1,2,3) b(2,3,4) a 交b (2,3)
2**.并集**
1)取两个集合所有的部分 union a(1,2,3) b(2,3,4) a 并b (1,2,3,4)
2)合并所有的数据包含重复 union all a(1,2,3) b(2,3,4) a 并b (1,2,3,2,3,4)
3.差集 从一个集合去掉另外一个集合剩余的部分 minus a(1,2,3) b(2,3,4) a 差b (1)
a 集合的使用场景：用于跨表的数据合并
b 规则 合并的①数据列数一致 ②类型一致
/*
四、集合运算
1.交集 取两个集合共同的部分 intersect a(1,2,3) b(2,3,4) a 交b (2,3)
2.并集 1)取两个集合所有的部分 union a(1,2,3) b(2,3,4) a 并b (1,2,3,4)
2)合并所有的数据包含重复 union all a(1,2,3) b(2,3,4) a 并b (1,2,3,2,3,4)
3.差集 从一个集合去掉另外一个集合剩余的部分 minus a(1,2,3) b(2,3,4) a 差b (1)
集合的使用场景：
用于跨表的数据合并
规则 合并的数据列数一致 类型一致
*/
--例1：工资大于1500,或者是20部门下的员工
--数据源
select * from emp where sal > 1500
select * from emp where deptno =20
--or 方式
select * from emp where sal > 1500 or deptno =20
--集合方式 并集
select * from emp where sal > 1500
union
select * from emp where deptno =20
--例2：工资大于1500,并且是20部门下的员工
--and方式
select * from emp where sal > 1500
and deptno =20
select * from emp where sal > 1500
intersect
select * from emp where deptno =20
--例3：1981年入职的普通员工（不包括经理，总裁）
select * from emp where job in ('manager','president')
select * from emp where to_char (hiredate,'yyyy') =1981
--and 方式
select * from emp where to_char (hiredate,'yyyy') =1981
and job not in ('manager','president')
--集合方式 差集
select * from emp where to_char(hiredate,'yyyy')='1981'
minus
select * from emp where job in ('manager','president')
--例4
--创建manager表作为公司的所有领导
create table manager (
mid number(9),
mname varchar(10)
)
select * from manager
insert into manager values(1,'zs');
insert into manager values(2,'lis');
commit;
--------查询公司的所有员工的姓名 编号 职位
--表 员工表 领导表
select mid 员工编号 ,mname 员工名称 from manager
union
select empno ,ename from emp