Oracle多表操作练习

一.多表查询

-- 多表查询
-- 查询员工和部门表的信息
SELECT * FROM EMP,DEPT;
-- 多个表一起查只要中间用逗号隔开就可以了
-- 但是会出现笛卡尔积的现象 查询速度会变慢 , 怎么办呢 ?

-- 连接查询 内连接
SELECT * FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO;
-- 不好看 ? 变形
SELECT * FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
-- 传说中的内连接查询 用外键表中的外键列与主键表中的
-- 主键列匹配 , 比如第一条数据 外键ID 20 
-- 那么就与 dept中 ID 为 20 的关联即可

-- 查询员工编号,姓名,部门编号名称和地址
SELECT E.EMPNO,E.ENAME,D.* FROM EMP E INNER JOIN DEPT D ON 
E.DEPTNO = D.DEPTNO

-- 表的自关联 : 查询出每个员工的上级领导
SELECT E.ENAME 员工名称,ME.ENAME 经理名称 
FROM EMP E INNER JOIN EMP ME 
ON E.MGR = ME.EMPNO

-- 查询员工上级领导和所属部门
SELECT E.ENAME 员工姓名,ME.ENAME 部门经理,D.DNAME 所属部门 
FROM EMP E 
INNER JOIN EMP ME ON E.MGR = ME.EMPNO
INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO

-- 经验 : 先看主体表 外键对主键 再看要查啥 别名字段用熟练

-- 不等值连接,emp并没有外键对应salgrade表中的某一列数据
-- emp中的工资sal属于salgrade中的某一工资等级的范围(大于最小工资,小于最大工资)
-- 像这种范围查询,就叫不等值连接
select e.ename 姓名, grade 工资等级 from emp e
inner join salgrade s on e.sal>s.losal and e.sal<s.hisal 

-- 难度增强 : 附加工资等级,领导的 和自己的  使用了decode函数
SELECT 
E.ENAME 员工姓名,ME.ENAME 领导姓名,D.DNAME 所属部门,
DECODE(ESG.GRADE,1,'一级',2,'二级',3,'三级',4,'四级',4,'五级','六级') 员工工资等级,
DECODE(MESG.GRADE,1,'一级',2,'二级',3,'三级',4,'四级',4,'五级','六级') 经理工资等级 
FROM EMP E
INNER JOIN EMP ME ON E.MGR = ME.EMPNO
INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO 
INNER JOIN SALGRADE ESG ON E.SAL BETWEEN ESG.LOSAL AND ESG.HISAL 
INNER JOIN SALGRADE MESG ON ME.SAL BETWEEN MESG.LOSAL AND MESG.HISAL

-- 总结 : 难度不大

-- 外链接 : 为保证连接的两张表中某一张表的数据完整
-- 左外链接 : 保证左表完整 如果右表没有与之匹配数据以NULL填充
-- 查询所有部门
SELECT * FROM DEPT D LEFT JOIN EMP E ON D.DEPTNO = E.DEPTNO

-- 多一条数据 40 这个部门没有员工 但是为了确保部门表数据都能
-- 查询出来 所以员工表就必须以null 填充完成查询;
-- 右外连接 : 与左外连接正好相反
SELECT * FROM EMP E RIGHT JOIN DEPT D ON E.DEPTNO = D.DEPTNO

-- 注意 : 由外链接尝尝会出现左表数据为NULL的情况,这样通常第一列
-- 又是一张表的主键,所以这样会破坏主键上的索引,查询会很慢
-- 经验 : 所有的右连接都能以左连接代替.能用左连接不用右连接
-- 能用内连接,不用外连接;

二.子查询

-- 子查询

-- 查询比员工7654工资高和员工7788从事一样工作的人
SELECT * FROM EMP E WHERE 
E.SAL > (SELECT E.SAL FROM EMP E WHERE E.EMPNO = 7654) AND 
E.JOB = (SELECT E.JOB FROM EMP E WHERE E.EMPNO = 7788)


-- 查询每个部门的最低工资和最低工资的员工姓名
SELECT * FROM EMP E INNER JOIN (
SELECT E.DEPTNO,MIN(E.SAL) MINSAL FROM EMP E GROUP BY E.DEPTNO
) A ON E.DEPTNO = A.DEPTNO AND E.SAL = A.MINSAL; 

三.面试题练习

-- 统计每年入职的人数

-- 课堂练习
-- 面试题
-- 第一题 : 找到工资最高的前三名员工
-- 考点 : Oracle伪列 rownum 的特性
-- rownum 对插叙数据开始从1排序 在order by 之前排序
SELECT * FROM 
(SELECT * FROM EMP E ORDER BY E.SAL DESC) A WHERE ROWNUM < 4 
-- rownum 属于伪列有伪列特性 不包含1查询不出来

-- 面试题 2 : 找出员工表中工资大于本部门平均工资的
-- 考点 : SQL的综合应用
-- 先计算出每个部门的平均工资 然后与员工表连接查询即可
SELECT * FROM EMP E 
INNER JOIN ( SELECT E.DEPTNO,ROUND(AVG(E.SAL),0) AVGSAL FROM EMP E GROUP BY E.DEPTNO ) A ON E.DEPTNO = A.DEPTNO
WHERE E.SAL > A.AVGSAL ORDER BY E.EMPNO 


-- 面试题 3 : 统计每年入职员工个数
-- 考点 : SQL的综合应用
-- 典型算术题 巧用计数器是关键
-- 首先看有几个年份
SELECT DISTINCT TO_CHAR(E.HIREDATE,'yyyy') 年份 FROM EMP E;
-- 得到 1987 1980 1982 1981
-- 计数器
SELECT COUNT(E.EMPNO) 总人数,
SUM(DECODE(TO_CHAR(E.HIREDATE,'yyyy'),'1987',1,0)) "1987年", 
SUM(DECODE(TO_CHAR(E.HIREDATE,'yyyy'),'1980',1,0)) "1980年",
SUM(DECODE(TO_CHAR(E.HIREDATE,'yyyy'),'1982',1,0)) "1982年",
SUM(DECODE(TO_CHAR(E.HIREDATE,'yyyy'),'1981',1,0)) "1981年"
FROM EMP E;


-- 补充知识点 : Oracle 中的分页
-- 分页公式 : 假设页码为 pageIndex 页容量为 pageSize 的话
-- 每页的数据取值范围 大于 (pageIndex-1)*pageSize 并且 小于等于
-- pageIndex*pageSize
-- rownum 的使用可以方便的获取数据编号 
-- 三层分页 : 1.数据层 2.伪列层 3.分页层
-- 比如 : 根据工资高低排序 并以每页三条数据的形式展示
-- 1. 数据层 : 提供分页原始数据
SELECT e.*,d.dname,d.loc FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO ORDER BY E.SAL DESC

-- 2. 位列层 : 子查询提供数据编号
SELECT A.*,ROWNUM RN FROM ( SELECT e.*,d.dname,d.loc FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO ORDER BY E.SAL DESC ) A 

-- 3. 分页层 : 根据位列层提供的伪列 将伪列变为虚拟表的普通列
SELECT B.* FROM (
SELECT ROWNUM RN,A.* FROM ( SELECT e.*,d.dname,d.loc FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO ORDER BY E.SAL DESC ) A 
) B WHERE B.RN > (2-1)*3 AND B.RN <= 2*3


-- 另一个伪列 rowid 
-- rowid 记录了表的每行数据存储的真实物理位置
-- 可以用以区分表中两条完全 一模一样的数据

四.集合运算

集合运算效率很低,在实际工程中严禁使用

-- 集合运算符

-- 并集 union 和 union all

-- union 将两个完全相同类型的查询结果连接到一起 排序 去重
-- 查询工资大于1500 或者 部门编号是20 的员工
SELECT * FROM EMP E WHERE E.SAL >1500
UNION 
SELECT * FROM EMP E WHERE E.DEPTNO = 20

-- 连接条件 两个SQL查询的字段个数类型必须一一对应 完全一致
-- union all 不排序 不去重 提高性能
SELECT * FROM EMP E WHERE E.SAL >1500
UNION ALL
SELECT * FROM EMP E WHERE E.DEPTNO = 20

-- 两种并集查询效率十分低下 建议不要使用这种方式

-- 交集 : 取两个SQL语句中共有的部分
-- 查询工资大于1500 并且 部门编号为20的员工
SELECT * FROM EMP E WHERE E.SAL  > 1500 
INTERSECT 
SELECT * FROM EMP E WHERE E.DEPTNO = 20

-- 差集 : 获取包含在第一个SQL语句中 但不包含在第二个
-- SQL语句中的数据
-- 获取1981年入职的普通职工
SELECT * FROM EMP E WHERE TO_CHAR(E.HIREDATE,'yyyy') = '1981' 
MINUS 
SELECT * FROM EMP E WHERE E.EMPNO IN (SELECT DISTINCT MGR FROM EMP WHERE MGR IS NOT NULL)