MySQL 和 Oracle 联合查询以及聚合函数

发表于 2022-02-18 更新于 2023-05-25 分类于数据库阅读次数： 13 本文字数： 3.7k 阅读时长 ≈ 14 分钟

系列文章已收录至 github 仓库:

https://github.com/cnwangk/wangk-stick

前言

如果不想自己去新建示例，也想找一个完整的示例进行测试练习，MySQL 官网有提供示例数据库。

官方提供的 sakila 和 world 数据库，官网下载地址已经提供，可以下载进行参考学习。

sakila-db 数据库包含三个文件：

sakila-schema.sql：数据库表结构
sakila-data.sql：数据库示例模拟数据
sakila.mwb：数据库物理模型，在 MySQL workbench 中可以打开查看。

https://downloads.mysql.com/docs/sakila-db.zip

world-db 数据库，表结构与 data 数据包含在一起：

https://downloads.mysql.com/docs/world-db.zip

Oracle11g 安装后自带有 scott 用户，可以用来练习。主要用到的是 EMP 和 DEPT 表，想起了当年用 Java 的 ssh 框架写的第一个 CURD 的 demo 示例就是 Oracle 的这两张表，因为这两表有关联关系。

EMP：员工表；
DEPT：部门表；

软件实施系列文章第二弹，本来在去年就想写出来的，一直鸽到现在，哈哈。

tips：图片资源可能被防盗链（寄）了，可以右键属性复制地址在地址栏查看哈。

正文

比摆烂，谁最强，自己一次比一次强。现在回顾自己以前写的那些博客，虽然也是自己真实实践和验证过才发出来的，但自己都感觉稀烂。虽然我写的文档很烂，但是比之前有进步就行了，一两年之后你会发现的进步是可观的，知识宝库越来丰富。

多思考，多练习。不要只停留在想上面，而要立即动起来。亲自去实践，去求证。多问一个为什么，思考事情的本质。看一万遍，不如自己亲手实践一遍来的效果好。

我的测试环境基于：

操作系统：Windows10；
数据库：MySQL8.0.28 和 Oracle11g；
使用查询工具：MySQL8.0 自带命令行以及 Oracle 自带的 SQLplus；
第三方工具 SQLyog 和 PLSQL Developer。

一、联合查询

图解联合查询

内连接：统计的内容是 table1 和 table2 的重合部分。

1	inner join on

左外连接：可以省略掉 outer，统计的内容是以 table1 为主的部分。

1	left outer join on

右外连接：同样可以省略掉 outer，统计的内容是以 table2 为主的部分。

1	right outer join on

1、联合查询

1.1、MySQL 中的联合查询示例

inner join on：内连接
right join on：右外连接
left join on：左外连接

MySQL 中的内连接查询关键字：inner join on，只作为演示，就不执行 explain 执行计划去判断执行效率了。小小的建议，在测试这些个联合查询的时候，可以不用带太多的过滤条件看看三种联合查询的区别。

1
2
3

SELECT c.`ID`,c.`CountryCode`,cl.`CountryCode`,cl.`Language` 
FROM world.`city` c INNER JOIN world.`countrylanguage` cl 
ON c.`CountryCode`=cl.`CountryCode` WHERE c.`ID`>120 AND c.`ID` LIMIT 0,5;

MySQL 中的左外连接查询查询关键字：LEFT OUTER JOIN

1
2
3

SELECT c.`ID`,c.`Name`,c.`CountryCode`,cl.`IsOfficial`,cl.`CountryCode`,cl.`Language` 
FROM world.`city` c LEFT OUTER JOIN world.`countrylanguage` cl 
ON c.`CountryCode`=cl.`CountryCode` LIMIT 0,5;

MySQL 中的右外连接查询关键字：RIGHT OUTER JOIN

1
2
3

SELECT c.`ID`,c.`Name`,c.`CountryCode`,cl.`IsOfficial`,cl.`CountryCode`,cl.`Language` 
FROM world.`city` c RIGHT OUTER JOIN world.`countrylanguage` cl 
ON c.`CountryCode`=cl.`CountryCode` LIMIT 0,5;

1.2、Oracle 中的联合查询示例

主要以 SCOTT 用户作为示例，查看 SCOTT 用户下有哪些表，这种方式需要以 dba 管理员身份运行 SQL 语句查询：

ower 代表了用户名，所以直接查找 SCOTT 用户，TABLE_NAME：代表了表名。

1	select t.OWNER,t.TABLE_NAME,t.TABLESPACE_NAME from dba_tables t where t.OWNER='SCOTT';

Oracle 中的联合查询，同样以员工表（emp）和部门表（dept）进行演示操作。

Oracle 中的内连接：inner join on

根据部门编号进行关联查询，进行分页查询，每页显示 5 条数据：

1 2	select e.ename,e.empno,d.deptno,d.dname from scott.emp e inner join scott.dept d on e.deptno=d.deptno where rownum<=5;

左外连接：left outer join on

1 2	select e.ename,e.empno,d.deptno,d.dname from scott.emp e left outer join scott.dept d on e.deptno=d.deptno where rownum<=5;

右外连接：right outer join on

1 2	select e.ename,e.empno,d.deptno,d.dname from scott.emp e right outer join scott.dept d on e.deptno=d.deptno where rownum<=5;

全连接：full join on

1 2	select e.ename,e.empno,d.deptno,d.dname from scott.emp e full join scott.dept d on e.deptno=d.deptno where rownum<=5;

组合查询：union

1 2	select e.ename,e.empno from scott.emp e where rownum<=5 union select e.ename,e.empno from scott.emp e where e.ename like '%ARC%';

组合查询：union all

1 2	select e.ename,e.empno from scott.emp e where rownum<=5 union all select e.ename,e.empno from scott.emp e where e.ename like '%ARC%';

union 和 union all 是有区别的，我列举的例子进行了模糊匹配，没演示出来效果。使用 union all 后 DBMS 不会取消重复的行。

去掉后面的 like 条件，使用 union 统计的数据为 14 行，使用 union all 统计的数据为 19 行，其实不难理解，all 就是全部。

2、分页查询

2.1、MySQL 的分页查询使用 limit 关键字

tips：Windows 中 CMD 命令窗口使用 color a 即可调用出黑色背景绿色字体，color f0 则是快速调出白色背景黑色字体哟！

护眼色：R：181 G：230 B：181

示例：使用 world 数据库中 city 表进行演示分页查询，通过 desc 展示数据结构，尤其是配合开发进行联调的时候很常用：

1	mysql> desc world.city;

查询 world 数据库中的 city 表前 5 条数据：

1	mysql> select * from city limit 0,5;

2.2、Oracle 的分页查询使用 rownum 伪列

同样使用 desc 关键字查询 emp 表结构：

1	SQL> desc scott.emp;

分页查询示例：使用 rownum 关键字进行演示 Oracle 中的分页查询。

查询 scott 用户中 emp（员工表）的员工 empno：编号、ename：员工姓名以及伪列 rowid，只查询前 5 条数据：

1	SQL> select t.rowid,t.empno,t.ename from scott.emp t where rownum <=5;

Oracle 进行分页查询常用方式一，查询第 6~11 数据通过嵌套子查询，使用到关键字 rownum 和 where：

-- 统计emp数据总条目数
select count(*) from scott.emp;  
-- 查询第6~11数据通过嵌套子查询，使用到关键字rownum和where
select * from (select scott.emp.empno,rownum r from scott.emp Where rownum<=11)where r>=6;

Oracle 进行分页查询常用方式二，先进行 order by 排序，再分页查询，查询第 6~11 数据：

-- 先进行排序
select * from  emp e order by e.empno Desc;
-- 再进行分页
select * from (select e.*,rownum r_num from(select * from scott.emp e order by e.empno desc )e)b where b.r_num between 6 and 11;

二、聚合函数（Aggregate）

下面所讲的函数大多数标准 SQL 数据库是支持的，但也要依据实际情况做测试验证，个人主要验证的是 MySQL 和 Oracle。

重点：count、sum 函数在我们如果要迁移数据的时候，避免不了需要手动去统计求和对比迁移前后数据的一致性。

1、常见的聚合函数

介绍几个聚合函数：

count 函数用于统计条目数；
sum 函数用于求和；
substr 函数用于截取；
avg 函数用于取平均值；
max 函数用于取最大值；
min 函数用于取最小值。

如下则演示同时使用多个函数，查询 Oracle 数据库 scott 用户的 emp 表：

查询出来的结果：count 统计员工总数，sum 求和所有员工的薪水总额，avg 统计所有员工平均薪水，substr 则是截取到小数点后两位数。

1 2	-- count:统计条目数,sum:求和,substr:截取,avg:取平均值 select count(*), sum(t.sal), substr(avg(t.sal), 0, 7) from scott.emp t;

返回平均值 avg，一般配合 substr 关键字去截取，通过计算保留小数点后两位。

统计某公司员工的平均薪资：

1
2
3

-- avg:取平均值
select avg(t.sal) from scott.emp t;
select substr(avg(t.sal), 0, 7) from scott.emp t;

返回统计行数 count

统计某公司员工总数：

1 2	-- 统计函数count:统计emp表条目数量14 select count(*) from scott.emp;

返回总数（求和）sum，sum 函数一般会配合 decode 函数使用。上面的黑色背景看久了眼睛累，特意换了一种护眼色。字体颜色就没有特意更换，字体稍微点大了一丢丢，看的更舒服。

统计某公司所有员工薪资总和：

-- 求和函数sum的使用
select sum(t.sal) from scott.emp t;
-- 配合decode函数使用
select sum(decode(ename, 'SMITH', sal, 0)) SMITH,sum(decode(ename, 'ALLEN', sal, 0)) ALLEN,
	   sum(decode(ename, 'WARD', sal, 0)) WARD,sum(decode(ename, 'JONES', sal, 0)) JONES,
	   sum(decode(ename, 'MARTIN', sal, 0)) MARTIN,sum(decode(ename, 'BLAKE', sal, 0)) BLAKE,
	   sum(decode(ename, 'CLARK', sal, 0)) CLARK,sum(decode(ename, 'SCOTT', sal, 0)) SCOTT,
	   sum(decode(ename, 'KING', sal, 0)) KING,sum(decode(ename, 'TURNER', sal, 0)) TURNER
from scott.emp;

tips：count 函数在工作中使用的很频繁，你不清楚某张表中有多少条记录，需要统计一下再处理。

返回最大值 max

查看员工中薪水最高的那一位：

1 2	-- max函数的使用 select max(t.sal) from scott.emp t;

返回最小值 min

查看员工中薪水最低的那一位：

1	select min(t.sal) from scott.emp t;

Oracle 中的 rownum 伪列

统计公司员工中的最后一条记录，通过 rownum 实现：

1 2	select t.sal from scott.emp t where rownum <=1; select t.sal from scott.emp t where rownum <=1 order by t.sal desc;

MySQL 中的分页 limit 关键字

通过 limit 关键字实现，根据 sakila 数据库中的 actor（演员表）为例子返回最后三条记录，使用 actor_id 进行排序。

注意：limit 属于 MySQL 扩展 SQL92 后的语法，在其它数据库中不能通用。Oracle 的分页可以通过 rownum 来实现，上面也介绍了。

1	SELECT t.`first_name`,t.`actor_id` FROM sakila.`actor` t ORDER BY t.`actor_id` DESC LIMIT 0,3;

2、着重掌握的函数

group by 函数用于分组；
having 函数用于过滤，对分组后内容进行过滤。

group by 函数

配合聚合函数 sum 使用，查询 Oracle 中 scott 用户下的 emp 表。使用 group by 进行分组，然后统计公司各部门员工的薪资：

1	SELECT t.deptno, SUM(t.sal) AS sals FROM scott.emp t GROUP BY t.deptno;

having 函数

区别：having 和 where 的区别在于，having 是对聚合后的结果进行条件的过滤，而 where 是在聚合前就对记录进行过滤。如果逻辑允许，应尽可能用 where 先过滤记录，由于结果集的减小，对聚合的效率明显提升。最后再依据逻辑判断是否用 having 再次过滤。

配合聚合函数使用，Oracle 中的 scott 用户下 emp 与 dept 表。

先对部门名称进行分组，然后使用 having 过滤出薪水总和大于 10000 的部门：

1
2
3

SELECT d.dname, SUM(e.sal) AS sals FROM scott.emp e
INNER JOIN scott.dept d ON e.deptno=d.deptno
WHERE e.deptno < 30 GROUP BY d.dname  HAVING SUM(e.sal) > 10000;

三、SQL 核心知识

凡事应以实际工作场景而定。个人的以一些理解仅仅是建议，最终的应用还需结合实际应用场景。软件实施对 SQL 的函数、触发器和存储过程没有太高的要求，但也需要会基本的运用。在某些特殊的场景下，使用这些 SQL 的核心知识将有助于提高我们的工作效率。

1、函数

函数关键字：FUNCTION

使用第三方客户端工具新建函数，会自动生成一些模板：

DELIMITER $$  -- 声明关键字DELIMITER
CREATE -- 创建函数的关键字create
    /*[DEFINER = { user | CURRENT_USER }]*/
    FUNCTION `study`.`stu_num`() -- 设置函数名称
    RETURNS TYPE -- 返回值的类型
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN -- 开始业务逻辑
    -- {业务逻辑区...}
    END$$ -- 结束标志
DELIMITER ;

2、触发器

触发器关键字：TRIGGER

使用第三方客户端工具新建触发器，会自动生成一些模板：

DELIMITER $$
CREATE	-- 创建触发器的关键字create
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `study`.`stu_insert` BEFORE/AFTER INSERT/UPDATE/DELETE
    ON `study`.`<Table Name>`
    FOR EACH ROW BEGIN -- 使用到for each循环
    -- {业务逻辑区...}
    END$$
DELIMITER ;

3、存储过程

存储过程关键字：PROCEDURE

支持完整事务的存储引擎，在保证数据的完整一致性情况下，尽可能多的使用 commit 事务提交。利用函数和存储过程一个好的示例，在 MySQL 中快速生成千万级别的数据大表进行测试就可以应用到，同时还能联想到测试性能。这是勾起我们学习的动力，一个比较好的方法。

使用第三方客户端工具新建存储过程，会自动生成一些模板：

DELIMITER $$
CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `study`.`insert_study`()
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN
	-- {业务逻辑区...}
	COMMIT; -- 支持完整事务的存储引擎,在保证数据的完整一致性情况下,尽可能多的使用commit事务提交
    END$$
DELIMITER ;

4、典型的示例 sakila 数据库

这是一个 MySQL 官方提供的拥有存储过程、触发器和函数示例的电影出租信息管理系统数据库。并且官方提供了 EER 模型，便于理解每张表之间的关联关系，可以使用 MySQL workbench 打开 sakila.mwb 进行参考学习。如果你能完整的看完这篇文档，你会发现在一开始我就提供了 sakila 数据库的官网下载地址。

sakila 数据库视图：actor_info，演员信息视图

使用 DESC 关键字进行查看视图结构，这个关键字很实用哟。视图和表结构很像，以 sakila 中 actor_info 视图进行展示：

sakila 数据库存储过程：film_in_stock，电影库存

官方的一个示例：创建一个存储过程，声明了三个常量字段，然后分别赋值给演示字段，最后将找到的记录复制存到了 p_film_count 中。这里我为何说是复制呢？是因为使用到了 SELECT ... INTO 关键字。

函数、触发器和存储过程最主要的一块在 BEGIN {业务逻辑区…} END 这一块区域。

DELIMITER $$
USE `sakila`$$
DROP PROCEDURE IF EXISTS `film_in_stock`$$
CREATE DEFINER=`root`@`%` PROCEDURE `film_in_stock`(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
    READS SQL DATA
    SQL SECURITY INVOKER
BEGIN
     SELECT inventory_id
     FROM inventory
     WHERE film_id = p_film_id
     AND store_id = p_store_id
     AND inventory_in_stock(inventory_id);
     SELECT FOUND_ROWS() INTO p_film_count;
END$$
DELIMITER ;

关于函数我就不列举 MySQL 官方提供的示例了。

给出一点小小的建议，感觉对你没啥作用可以忽略掉：首先快速熟悉语法使用，对官方的示例进行解读，然后运行验证。最后，书写一些简单的示例达到熟练运用目的。不要只停留在想要执行，而是立即执行并带着思考去看待问题。多问一个为什么，思考本质。

四、看文档也要护眼哟

1、常用护眼色

颜色	RGB	16 进制
常用护眼色	R：181 G：230 B：181	#B5E6B5
黄	R：250 G：249 B：222	#FAF9DE
褐	R：250 G：242 B：226	#FFF2E2
红	R：253 G：230 B：224	#FDE6E0
绿	R：227 G：237 B：205	#E3EDCD
海天蓝	R：220 G：226 B：241	#DCE2F1
紫	R：233 G：235 B：154	#E9EBFE
灰	R：234 G：234 B：239	#EAEAEF

——END——