.~~~ 准备员工表与部门表测试数据.Sql 执行顺序1、SELECT 查询语句语法顺序如下SELECT DISTINCT select_list FROM left_table join_type JOIN right_table ON join_condition WHERE where_condition GROUP BY group_by_list HAVING having_condition ORDER BY order_by_condition2、SELECT 查询语句执行顺序如下FROM 表名 # 选取表将多个表数据通过笛卡尔积变成一个表。 ON 筛选条件 # 对笛卡尔积的虚表进行筛选 JOIN join, left join, right join... join表 # 指定join用于添加数据到on之后的虚表中例如left join会将左表的剩余数据添加到虚表中 WHERE where条件 # 对上述虚表进行筛选 GROUP BY 分组条件 # 分组 SUM()等聚合函数 # 用于having子句进行判断在书写上这类聚合函数是写在having判断里面的 HAVING分组筛选 # 对分组后的结果进行聚合筛选 SELECT 返回数据列表 # 返回的单列必须在 group by子句中聚合函数除外. DISTINCT # 数据除重 ORDER BY 排序条件 # 排序--统计每个部门的人数没有员工的部门统计个数展示为0。count(empno) 忽略统计为 null 的行. select t.deptno, t.dname, count(empno) from (select d.deptno, d.dname, e.empno, e.ename from dept d left join emp e on d.deptno e.deptno) t group by t.deptno, t.dname;连接查询内连接Inner Join只返回两张表中满足匹配条件的记录。左连接Left (Outer) Join返回左表的所有行以及右表中的匹配行对于右表中未匹配上的行其列值在结果集中用NULL填充。右连接Right(Outer) Join与左连接类似只是主表为右表。返回右表的所有行以及左表中的匹配行对于左表中未匹配上的行其列值在结果集中用NULL填充。全连接Full (Outer) Join返回两个表的所有行无论是否匹配对于左右表中未匹配上的行其列值在结果集中用NULL填充。自连接Self Join一种独特的连接类型其中表与自身连接。当你需要比较同一表中的行时就适合用自连接。交叉连接Cross Join也称为笛卡尔连接返回两个表的笛卡尔积这意味着第一个表的每一行都与第二个表的所有行相结合。比如若 AB 两个表的行数分别为 m 和 n则交叉连接后结果集中的总行数为m * n。笛卡尔积1、笛卡尔集会在下面条件下产生省略连接条件、连接条件无效、所有表中的所有行互相连接。2、为了避免笛卡尔集可以在 WHERE 加入有效的连接条件。在实际运行环境下应避免使用全笛卡尔全集。3、该查询将每个员工与每个部门组合在一起产生一个将每个员工与每个部门配对的列表。这种情况会产生很多错误的数据另外由于笛卡尔积产生的行数量比较多所以会影响查询性能特别是连接表的记录数较高时。--多表查询没加 where 或者 on 条件此时就是笛卡尔积/交叉连接 --CROSS JOIN 可以省略 select * from emp CROSS JOIN dept; select * from emp, dept;内连接1、假如左表中某一行对应了右表中的两行则查询结果为2行。隐式内连接在笛卡尔积的基础上加上 where 条件后就是隐式内连接。比较常用的操作--笛卡尔积加上 where 条件就是隐式内连接。 --只要员工的deptno等于部门数据的deptno则两者就可以返回比如共有20个员工5个部门其中有15个员工匹配了其中4个部门则结果就是15条数据。 --没有分配到部门的员工无法展示 select e1.*,d1.* from emp e1,dept d1 where e1.deptno d1.deptno; -- 查询员工工资大于部门平均工资的人 select t1.empno, t1.ename, t1.sal, t2.dept_avg_sal from emp t1, (select e.deptno, round(avg(nvl(e.sal, 0)), 2) dept_avg_sal from emp e group by e.deptno) t2 where t1.deptno t2.deptno and t1.sal t2.dept_avg_sal;显式内连接select * from 表1 inner join 表2 on 连接条件--inner 关键字可以省略。显示内连接必须用 on而不是 where--查询员工姓名及其所在部门的位置 --只要员工的deptno等于部门数据的deptno则两者就可以返回比如共有20个员工5个部门其中有15个员工匹配了其中4个部门则结果就是15条数据。 select e1.ename,d1.loc from emp e1 inner join dept d1 on e1.deptno d1.deptno; --查询员工姓名及其所在部门的名称 select e1.ename,d1.dname from emp e1 join dept d1 on e1.deptno d1.deptno;--必须是 on而不是 where完整示例人员信息同步视图.sql不等值连接--查询员工信息员工号 姓名 月薪工资级别 select e.empno, e.ename, e.sal, s.grade from emp e, salgrade s where e.sal between s.losal and s.hisal;左、右外连接1、left outer join onouter 可省略返回左表的全部数据以及右表中满足条件数据如果右表没有对应的记录则右表相关列结果为空如果左表与右表是一对多则查询结果是多条2、right outer join onouter 可省略返回右表的全部数据以及左表中满足条件的数据如果左表没有对应的记录则左表相关列结果为空--查询每个部门的员工同时没有员工的部门也要全部显示。外连接必须用 on 而不是 where select d1.*,e1.* from dept d1 left outer join emp e1 on d1.deptno e1.deptno; -- 查询全部员工没有分配部门的员工也会展示 select e1.*,d1.* from dept d1 right outer join emp e1 on d1.deptno e1.deptno;连表查询时条件放在on后边还是放在where后边的1、对于 left join(左外连接)不管 on 后面跟什么条件左表的数据全部会查出来因此要想过滤结果必须把条件放到where后面右外连接也是同理。2、对于 inner join(内连接)满足on后面的条件表的数据才能查出可以起到过滤作用也可以把条件放到where后面。3、左连接时 on 条件过滤的右表的数据左表数据全部返回右连接时on 条件过滤的左表的数据右表数据全部返回。要想过滤整个查询结果则需要再加 where 条件对虚表结果进行过滤。-- 查询部门编号 10 的部门信息及其下面的员工信息, on 条件会结果放入到虚表中然后使用 where 条件进行结果过滤 SELECT T.*,t2.* FROM dept t left join emp t2 on t.deptno t2.deptno where t.deptno 10; --按部门统计员工人数 select d.deptno 部门号, d.dname 部门名称, count(e.empno) 人数 from emp e right join dept d on e.deptno d.deptno group by d.deptno, d.dname order by d.deptno; -- 查询所有部门信息同时查询部门编号为 30 的部门下的员工中工资超过 5k 的员工 -- tips左连接时 on 条件过滤的右表的数据左表数据全部返回右连接时on 条件过滤的左表的数据右表数据全部返回 -- 要想过滤整个查询结果则需要再加 where 条件对虚表结果进行过滤 SELECT T.*, t2.* FROM dept t left join emp t2 on t.deptno t2.deptno and t.deptno 30 and t2.sal 5000 order by t.deptno;多表连接示例1-- BAS_PERSON_EXT、BAS_PERSON_MONTH 表的主键 per_id 关联 BAS_PERSON_INFO 表的主键 per_id -- 查询所有人员的基本(info)信息人员有扩展(ext)信息时也一起返回人员有月数(month)信息时也一起返回. select A.PER_ID, A.IDEN_TYPE_ID, A.IDEN_TYPE_CODE, A.IDEN_TYPE_NAME, B.SALA_GR_ID, B.SALA_GR_CODE, B.SALA_GR_NAME, M.M_POS_SALA, M.M_GRAD_SALA, M.M_NATION_REG_SUB from BAS_PERSON_INFO A left join BAS_PERSON_EXT B on A.Per_Id B.Per_Id left join BAS_PERSON_MONTH M on A.PER_ID M.per_id;多表连接示例2-- 补充背景emp 表新加 flow_id 列关联 work_flow 表的主键is_end 表示流程是否终审(1是2否) create table WORK_FLOW ( flow_id NUMBER not null, is_end NUMBER(1) not null ); comment on column WORK_FLOW.flow_id is 主键ID; comment on column WORK_FLOW.is_end is 是否终审(1是2否); alter table WORK_FLOW add constraint PK_WORK_FLOW primary key (FLOW_ID); -- 多表连接示例1查询终审且有上级领导的员工信息同时展示员工的部门名称 SELECT e.*, e2.ename as mgr_name, w.is_end, d.dname FROM emp e inner join emp e2 on e.mgr e2.empno inner join work_flow w on e.flow_id w.flow_id and w.is_end 1 left join dept d on e.deptno d.deptno; -- 多表连接示例2查询终审且工资大于4.5k的员工信息同时展示员工的部门名称以及领导的姓名 SELECT e.*, e2.ename as mgr_name, w.is_end, d.dname FROM emp e inner join work_flow w on e.flow_id w.flow_id and w.is_end 1 left join emp e2 on e.mgr e2.empno left join dept d on e.deptno d.deptno where e.sal 4500;Oeacel () 语法1、() 是 **Oracle 独有的老式外连接语法**属于 left-join 出现之前的写法MySQL、PostgreSQL、**达梦数据库虽然兼容但更推荐 left‑join 标准写法**。2、() 标记**从表**不能两边同时写加号。3、加号写在等号「哪一边」哪边就是被匹配的一方匹配不到时这一侧字段返回 NULL另一侧主表数据一定保留。4、多字段关联时每个关联条件都必须带 ()不带 () 的条件 Oracle 会当成**连接后 where 过滤条件**匹配不到的 NULL 行会被过滤掉。-- 左外连接查询所有部门信息同时查询部门下的人员. select * from dept d, emp e where d.deptno e.deptno() ORDER BY d.deptno; -- 右外连接查询所有员工信息同时对应的部门. select * from dept d, emp e where d.deptno() e.deptno ORDER BY d.deptno; select * from dept d, emp e where d.deptno e.deptno() AND d.deptno e.sal(); --等价于 dept d left jion emp e on d.deptno e.deptno AND d.deptno e.sal select * from dept d, emp e where d.deptno e.deptno() AND d.deptno e.sal; --等价于 dept d left jion emp e on d.deptno e.deptno where d.deptno e.sal select * from dept d, emp e where d.deptno e.deptno() AND e.sal() 2000; --等价于 dept d left jion emp e on d.deptno e.deptno AND e.sal 2000全连接1、Full [outer] Join on返回两个表的所有行无论是否匹配对于左、右表中未匹配上的行其列值在结果集中用NULL填充。outer 可以省略。-- 查询全部部门信息与全部员工信息 -- 没有员工的部门信息会展示没有分配部门的员工也会展示 select d1.*,e1.* from dept d1 FULL join emp e1 on d1.deptno e1.deptno;自连接Self Join1、一种独特的连接类型其中表与自身连接。当你需要比较同一表中的行时就适合用自连接。--查询员工及其领导姓名(没有领导的员工不会展示) SELECT T1.*,t2.ename 经理名称 FROM emp t1 join emp t2 on t1.mgr t2.empno order by t1.ename; --查询全部员工及其领导姓名(没有领导的员工也会展示) SELECT T1.*,t2.ename 经理名称 FROM emp t1 left join emp t2 on t1.mgr t2.empno order by t1.ename;优化说明1、为了确保 SQL 查询高效运行请考虑以下优化技术索引在连接条件的匹配列上使用索引这样可以有效提升匹配查询速度。连接类型选择适当的连接类型以尽量减少返回的行数。提前过滤在连接之前应用WHERE子句以减小结果集的大小。子查询最低优先级在兼容的情况下尽量选用EXISTS或IN子句而非子查询。连接顺序连接中表的顺序会影响性能。较小的表或具有更多过滤器的表通常应该首先连接。避免不必要的列只选择必要的列以减少数据负载。2、在使用连接时要注意这些常见的陷阱笛卡尔积如果忘记ON子句结果将会导致笛卡尔积从而创建一个过大的结果集。连接类型错误使用错误的连接类型将会返回不期望的结果。空值在连接可能包含空值的列时要小心因为它们可能会影响结果集。性能问题连接使用不当如过度使用嵌套子查询将会导致性能问题。完整示例人员信息同步视图.sql小表驱动大表1、“小表驱动大表”Small Table Drives Large Table指的是在多表JOIN时优先使用参与JOIN的行数少的表小表作为驱动表外层循环行数多的表大表作为被驱动表内层循环。通过减少外层循环的执行次数降低整体查询的IO成本和计算开销。2、JOIN本质是“行匹配”过程驱动表的每一行都需要与被驱动表的行进行匹配基于JOIN条件。若驱动表行数少外层循环次数少即使被驱动表行数多总匹配次数也会显著减少。3、举个直观的例子小表A有10行大表B有1000行JOIN条件为A.id B.a_id。若A驱动B外层循环10次每次匹配B的1000行总匹配次数10×100010,000。若B驱动A外层循环1000次每次匹配A的10行总匹配次数1000×1010,000。表面看次数相同但实际中被驱动表若有JOIN字段的索引每次匹配是“索引查找”成本低此时外层次数少更优10次索引查找总开销 1000次。小表数据更易加载到内存减少磁盘IO大表可能无法全加载需频繁读盘。4、需注意“小表”指的是经过WHERE条件过滤后实际参与JOIN的行数少的表而非物理存储大小如数据文件大小。子查询1、在 SELECT、UPDATE、DELETE 语句内部可以出现 SELECT 语句内部的 SELECT 语句结果可以作为外部语中条件子句的一部分也可以作为外部查询的临时表。子查询分为单行子查询、多行子查询。2、子查询语句用括号括起来一般嵌入位置可以是where 后面、from 后面、select、update 后面、having 后面。不可以在 group by 后面使用子查询。3、子查询(内查询) 在主查询之前一次执行完成子查询的结果被主查询使用 (外查询)。一般先执行子查询再执行主查询但相关子查询例外。4、理论上多表查询/连接查询比子查询性能更好如果多表查询能替代的优先使用多表查询/连接查询。单行子查询不向外部返回结果或者只返回一行结果。select * from emp where sal (select max(sal) from emp);--查询工资最高的员工信息1、如果内部查询不返回任何记录则外部条件中字段 sal 与 NULL 比较永远为 false外部查询不返还任何结果。2、在单行子查询中外部查询可以使用 、、、≥、、 等比较运算符3、内部查询返回的结果类型必须与外部查询条件中的字段 ( sal ) 匹配。4、如果内部查询返回多行结果则会报错多行子查询向外部返回零行、一行或者多行结果。--查询工资小于员工 SCOTT 的所有员工信息 select t.*,t.rowid from emp t where t.sal (select sal from emp where enameSCOTT) ; --查询工资大于自己部门平均工资的员工信息 --相关子查询将主查询中的某些值作为参数传递给子查询 select e.empno, e.ename, e.sal, (select trunc(avg(sal), 2) from emp where deptno e.deptno) avgsal from emp e where e.sal (select avg(sal) from emp where deptno e.deptno); --查询每个部门最低薪水的员工姓名薪水以及他所属的部门名称。 select e1.ename, e1.sal, d1.dname from emp e1, (select deptno, min(sal) as minsal from emp group by deptno) t1, dept d1 where e1.deptno t1.deptno and e1.sal t1.minsal and e1.deptno d1.deptno;-- 子查询 配合删除 DELETE FROM emp t where t.empno in (SELECT T2.Empno FROM emp t2 where t2.deptno 10); -- 子查询配合新增插入 -- 当表中不存在待插入数据的主键ID时才新增否则不新增 insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM (SELECT 1234 as EMPNO, 约翰逊 as ENAME, 总统 as JOB, null as MGR, to_date(2018-05-06, yyyy-mm--dd) as HIREDATE, 87889.89 as SAL, 887.98 as COMM, 10 as DEPTNO from dual) T where (SELECT count(1) FROM emp t2 where t2.empno t.empno) 0;-- 子查询配合更新 -- 通过 BAS_AGENCY_INFO 单位基本信息表 更新 ele_agency 单位要素表 update ele_agency b set (b.ele_name, b.mof_dep_id, b.agency_kind_id) (select agency_name, mof_dep_id, agency_type_id from BAS_AGENCY_INFO a where b.is_deleted 2 and b.version 2023rcdtwh and b.ele_code a.agency_code and b.mof_div_code a.mof_div_code and rownum 1) where exists (select 1 from BAS_AGENCY_INFO a where b.is_deleted 2 and b.version 2023rcdtwh and a.agency_code b.ele_code and b.mof_div_code a.mof_div_code); ​select 子查询1、当一个子查询位于select和from之间时这类子查询就叫做标量子查询形如 SELECT a, b, (SELECT xxx) c FROM t1 ,写法虽然简单易懂但是经常会引发性能问题相当于是循环嵌套外面每查一行里面的子查询就需要走一遍。--查询所有员工数据以及员工所属的部门名称及地址 · 使用 select 子查询 select t1.*, (select t2.dname from dept t2 where t1.deptno t2.deptno) as dname, (select t2.loc from dept t2 where t1.deptno t2.deptno) as loc from EMP t1 order by t1.empno; --相关子查询将主查询中的某些值作为参数传递给子查询 --查询部门信息以及每个部门的员工个数 SELECT t.deptno, t.dname, t.loc, (SELECT count(1) FROM emp e where t.deptno e.deptno) as count_ FROM dept t;2、实际中推荐改成下面这种等价的模式来提供性能。-- 查询部门信息以及每个部门的员工个数以及每个部门最高的薪水 SELECT t.deptno, t.dname, t.loc, count_, sal_max FROM dept t LEFT JOIN (SELECT COUNT(1) count_, MAX(e.sal) sal_max, deptno FROM emp e GROUP BY e.deptno) e2 ON t.deptno e2.deptno ORDER BY t.deptno; --查询所有员工数据以及员工所属的部门名称及地址 SELECT t1.*, d.dname, d.loc FROM EMP t1 LEFT JOIN dept d ON t1.deptno d.deptno ORDER BY t1.empno;示例统计每个单位的人数和工资合计-- 不推荐的写法 SELECT a.mof_div_code, a.agency_code, a.version, (SELECT COUNT(1) FROM bas_person_info T1 WHERE t1.mof_div_code a.mof_div_code AND t1.agency_code a.agency_code AND t1.version a.version AND t1.is_deleted 2 AND t1.ui_code IN (202004, 202005, 202006) AND t1.per_sta_code 3) AS 在职人数总计, (SELECT COUNT(1) FROM bas_person_info T1 WHERE t1.mof_div_code a.mof_div_code AND t1.agency_code a.agency_code AND t1.version a.version AND t1.is_deleted 2 AND t1.ui_code 202005 AND t1.per_sta_code 3) AS 行政编制在职人数, (SELECT SUM(nvl(t2.basic_mon, 0)) FROM bas_person_info T1 LEFT JOIN bas_person_ext t2 ON t1.per_id t2.per_id WHERE t1.mof_div_code a.mof_div_code AND t1.agency_code a.agency_code AND t1.version a.version AND t1.is_deleted 2 AND t1.ui_code IN (202004, 202005, 202006) AND t1.per_sta_code 3) AS 在职人员工资合计 --...... FROM BAS_PERSON_INFO A LEFT JOIN BAS_PERSON_EXT B ON A.Per_Id B.Per_Id WHERE a.is_deleted 2 GROUP BY a.mof_div_code, a.agency_code, a.version ORDER BY mof_div_code,agency_code; -- 推荐的写法 SELECT T2.mof_div_code, t2.agency_code, t2.version, SUM(是否在职) AS 在职人数总计, SUM(是否行政编制在职) AS 行政编制在职人数, SUM(在职人员工资) AS 在职人员工资合计 FROM ( SELECT a.mof_div_code, a.agency_code, a.version, (CASE WHEN ui_code IN (202004, 202005, 202006) AND per_sta_code 3 THEN 1 ELSE 0 END ) 是否在职, (CASE WHEN ui_code 202005 AND per_sta_code 3 THEN 1 ELSE 0 END ) 是否行政编制在职, (CASE WHEN ui_code IN (202004, 202005, 202006) AND per_sta_code 3 THEN basic_mon ELSE 0 END) 在职人员工资 --...... FROM BAS_PERSON_INFO A LEFT JOIN BAS_PERSON_EXT B ON A.Per_Id B.Per_Id WHERE a.is_deleted 2 ) t2 GROUP BY t2.mof_div_code, t2.agency_code, t2.version ORDER BY mof_div_code,agency_code;exists 子查询1、格式与示例格式1select cloumn1,cloumn2... from tableName whereexists ( select 子查询)。如果子查询语句有结果则返回 true否则返回 false.格式2select cloumn1,cloumn2... from tableName wherenot exists ( select 子查询)。如果子查询语句没有结果则返回 true否则 false.select * from dept d1 whereexists(select * from emp e1 where e1.deptno d1.deptno); --查询有员工的部门信息--如果不存在用户 KING或者存在用户 SCOTT则返回 1否则返回空select 1from dual t1where not exists (select 2 from emp t2 where t2.ename KING)or exists (select 3 from emp t2 where t2.ename SCOTT);dept 表的第一条数据会逐个对比 emp 表中的所有数据只要有一条能匹配上则 exists 结果为 true就会返回部门信息就会显示否则不显示然后 dept 表的第二条数据开始逐个比对 emp 表以此类推。ORA-01427: 单行子查询返回多个行https://gitee.com/wangmx1993/material/blob/master/doc/oracle/Oracle%20常见错误代码处理%2001400-01499.md#知识拓展rownum 行号 与 分页rownum 行号概述与注意事项1、rownum 是 Oracle 特有的用于表示行号的关键字行号由系统自动生成起始值为1每查询出一条结果则 rownum 自动加1。通过 rownum 伪列可以限制查询结果集中返回的行数比如 rownum 3 表示只取前3条数据类似 Mysql 的 limit。2、rownum 因为从1开始所以不推荐做大于判断适用做小于等于判断。select * from emp;--查询所有员工 select rownum ,e1.* from emp e1;--查询所有员工同时显示行号 select * from emp where rownum 3;--查询前3条数据 --注意如下所示是错误的不会有值。因为 rownum 是从1开始永远不会大于2where 条件永不成立 --因为没有结果查询出来所以 rownum 也不能加一永远是1 select * from emp where rownum 2;3、再次提醒查询 SQL 执行顺序from ...- where ...-group by...-having ...-select ...- order by.select rownum,e1.* from emp e1 order by sal desc; --所以这里的行号是混乱的并不是由1开始逐个递增--查询薪水最高的3个人 select * from emp e1 order by sal desc;--第一步先按薪水降序 select t1.* from (select * from emp e1 order by sal desc) t1 where rownum 3;--第二步截取前3个人 --限制每次delete数据的记录条数(更新操作也是同理) delete from 表名 where 待删除数据的条件 and rownum100000 ;rownum 行号分页1、rownum 还有一个重要的作用就是分页Oracle 中分页需要借助子查询。select * from emp;--查询所有员工 --查询第 5-10 条数据。Oralce 分页需要借助子查询。 select * from (select rownum r,e1.* from emp e1) t where t.r5 and t.r10; select * from (select rownum r,e1.* from emp e1) t where t.r between 4 and 8; --排序后再分页 select * from (select rownum r, t.* from (select * from emp order by sal desc,empno) t) t2 where t2.r between 6 and 12; --推荐方式第二层除了查询行号外也可以加上分页的结束位置减少第三层时的数据量 select * from (select rownum r, e1.* from (select * from emp order by sal desc, empno) e1 where rownum 12) where r 6;rownum 判断数据是否存在1、业务代码中需要根据一个或多个条件查询是否存在记录而不关心有多少条记录比如某个部门是否存在普遍的 SQL写法如下select count(1) from dept t where t.deptno 30 and t.loc like %长沙% ;2、推荐写法如下select 1 from dept t where t.deptno 30 and t.loc like %长沙% and rownum 1; -- Oracle 写法select 1 from dept t where t.deptno 30 and t.loc like %长沙% limit 1; -- Mysql 写法3、SQL 不再使用count而是改用 limit1 或者 rownum 1让数据库查询时遇到一条就返回不要再继续查找还有多少条了业务代码中直接判断结果是否非空即可。根据查询条件查出来的条数越多性能提升的越明显。row_number() over 分页row_number() over 分组排序分页只需要嵌套1层子查询。select t2.* from (select row_number() over(order by t1.sal desc, empno) as r, t1.* from emp t1) t2 where t2.r between 6 and 12;offset rows fetch first/next 分页1、从Oracle 12c开始可以使用SQL标准的 OFFSET x ROWS FETCH FIRST/NEXT y ROWS ONLY 来对结果进行分页。x表示需要跳过的行数(偏移量比如 5 表示跳过前5行结果)。y表示想从剩余的记录在跳过x行后中选取出来的行数比如 7 表示取后续结果中的7行数据。-- 从Oracle 12c开始可以使用SQL标准的 OFFSET x ROWS FETCH FIRST/NEXT y ROWS ONLY 来对结果进行分页。 -- x表示需要跳过的行数(偏移量比如 5 表示跳过前5行结果)。 -- y表示想从剩余的记录在跳过x行后中选取出来的行数比如 7 表示取后续结果中的7行数据。 SELECT e.* FROM emp e ORDER BY e.ename OFFSET 5 ROWS FETCH NEXT 7 ROWS ONLY;分页通用计算语句1、前端进行分页时主要是两个指标一是当前展示的页码即当前是第几页从1开始二是每页展示的数据条数比如 20、50、100等等。后台数据库Sql对应的计算如下select * from (select rownum r, t.* from (select * from emp order by sal desc,empno) t) t2 where t2.r between (页码 -1) * 每页条数 1 and 页码 * 每页条数; select * from (select rownum r, e1.* from (select * from emp order by sal desc, empno) e1 where rownum 页码 * 每页条数) where r (页码 -1) * 每页条数 1; select t2.* from (select row_number() over(order by t1.sal desc, empno) as r, t1.* from emp t1) t2 where t2.r between (页码 -1) * 每页条数 1 and 页码 * 每页条数; SELECT e.* FROM emp e ORDER BY e.ename OFFSET (页码 -1) * 每页条数 ROWS FETCH NEXT 每页条数 ROWS ONLY;集合运算1、集合运算就是将两个或者多个结果集组合成为一个结果集集合运算包括INTERSECT(交集)返回两个查询共有的记录。UNION ALL(并集)返回各个查询的所有记录包括重复记录。UNION(并集)返回各个查询的所有记录不包括重复记录。MINUS(差集)返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。2、使用集合操作的时候要注意查询所返回的列数以及列的类型必须匹配列名可以不同采用第一个集合作为最后的表头采用最后一个集合上的 order by 进行最后的排序union 并集运算1、并集运算用于对两个结果进行合并union 会去重union all 不会去重。union 左边的结果排在前右边的结果排在后。select * from emp;--查询所有员工 --查询薪水大于 2000或者部门号为 30 的员工。union 会自动去重 select * from emp where sal 2000 union select * from emp where deptno 30; --union all 不会去重 select * from emp where sal 2000 union all select * from emp where deptno 30; --虽然这个需求也可以使用 or 关键字来解决但是 or 只能是在同一张表内而 union 并集运算可以对不同的表进行运算 select * from emp where sal 2000 or deptno 30;minus 差集运算select * from emp;--查询所有员工 --查询 1981 年入职的员工但是不包括总裁PRESIDENT和经理MANAGER select * from emp where to_char(hiredate,yyyy) 1981 minus select * from emp where job PRESIDENT or job MANAGER; --虽然本需求使用 and 关键字也可以实现但是 and 只能是表内操作而 minus 支持不同表之间求差集 select * from emp where job ! PRESIDENT and job ! MANAGER and to_char(hiredate,yyyy) 1981;INTERSECT(交集)--查询有员工的部门号select t1.deptno from emp t1intersectselect t2.deptno from dept t2;列个数类型不一致解决方式1、集合运算注意事项表与表之间列的个数、类型、顺序必须一致对不齐的可以使用 null 补齐否则报错。--错误类型1俩表之间列的个数不一致。解决办法是调整好顺序与类型 -- ORA-01789: 查询块具有不正确的结果列数. select e1.empno,e1.ename,e1.sal from emp e1 where to_char(hiredate,yyyy) 1981 minus select e2.empno,e2.ename from emp e2 where job PRESIDENT or job MANAGER; --错误类型2俩表之间列的类型/顺序不一致。解决办法是 e2 表缺省的字段可以用 null 值代替 -- ORA-01790: 表达式必须具有与对应表达式相同的数据类型 select e1.empno,e1.ename,e1.sal from emp e1 where to_char(hiredate,yyyy) 1981 minus select e2.empno,e2.sal,e2.ename from emp e2 where job PRESIDENT or job MANAGER;