【軟體實施面試】MySQL和Oracle聯合查詢以及聚合函式面試總結

龍騰萬里sky發表於2022-02-18
面試MySqlOracle函式

軟體實施面試系列文章第二彈，MySQL和Oracle聯合查詢以及聚合函式的面試總結。放眼望去全是MySQL，就不能來點Oracle嗎？之前面過不少公司，也做過不少筆試題，現在已經很少做筆試題了。你肚子有多少墨水，有經驗的面試官一問基本上就知道個大概了。趁著還有點微薄的記憶，就徹底分享出來啦。

系列文章已收錄至github倉庫:

https://github.com/cnwangk/SQL-study

前言

那個用心作題圖，用腳寫文件的就是我龍騰萬里sky啦。

如果不想自己去新建示例，也想找一個完整的示例進行測試練習，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：部門表；

軟體實施系列文章第二彈，本來在去年就想寫出來的，一直鴿到現在，哈哈。

正文

比擺爛，誰最強，自己一次比一次強。現在回顧自己以前寫的那些部落格，雖然也是自己真實實踐和驗證過才發出來的，但自己都感覺稀爛。雖然我寫的文件很爛，但是比之前有進步就行了，一兩年之後你會發現的進步是可觀的，知識寶庫越來豐富。

多思考，多練習。不要只停留在想上面，而要立即動起來。親自去實踐，去求證。多問一個為什麼，思考事情的本質。看一萬遍，不如自己親手實踐一遍來的效果好。

我的測試環境基於：

  • 作業系統：Windows10；
  • 資料庫：MySQL8.0.28和Oracle11g；
  • 使用查詢工具：MySQL8.0自帶命令列以及Oracle自帶的SQLplus；
  • 第三方工具SQLyog和PLSQL Developer。

一、聯合查詢

圖解聯合查詢

內連線：統計的內容是table1和table2的重合部分。

inner join on

左外連線：可以省略掉outer，統計的內容是以table1為主的部分。

left outer join on

右外連線：同樣可以省略掉outer，統計的內容是以table2為主的部分。

right outer join on

1、聯合查詢

1.1、MySQL中的聯合查詢示例

  • inner join on：內連線
  • right join on：右外連線
  • left join on：左外連線

MySQL中的內連線查詢關鍵字：inner join on，只作為演示，就不執行explain執行計劃去判斷執行效率了。小小的建議，在測試這些個聯合查詢的時候，可以不用帶太多的過濾條件看看三種聯合查詢的區別。

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

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

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：代表了表名。

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條資料：

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

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

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

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

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

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展示資料結構，尤其是配合開發進行聯調的時候很常用：

mysql> desc world.city;

查詢world資料庫中的city表前5條資料：

mysql> select * from city limit 0,5;

2.2、Oracle的分頁查詢使用rownum偽列

同樣使用desc關鍵字查詢emp表結構：

SQL> desc scott.emp;

分頁查詢示例：使用rownum關鍵字進行演示Oracle中的分頁查詢。

查詢scott使用者中emp（員工表）的員工empno：編號、ename：員工姓名以及偽列rowid，只查詢前5條資料：

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則是擷取到小數點後兩位數。

-- count:統計條目數,sum:求和,substr:擷取,avg:取平均值
select count(*), sum(t.sal), substr(avg(t.sal), 0, 7) from scott.emp t;

返回平均值avg，一般配合substr關鍵字去擷取，通過計算保留小數點後兩位。

統計某公司員工的平均薪資：

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

返回統計行數count

統計某公司員工總數：

-- 統計函式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

檢視員工中薪水最高的那一位：

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

返回最小值min

檢視員工中薪水最低的那一位：

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

Oracle中的rownum偽列

統計公司員工中的最後一條記錄，通過rownum實現：

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來實現，上面也介紹了。

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進行分組，然後統計公司各部門員工的薪資：

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的部門：

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

總結

能看到這裡的，都是帥哥靚妹。以上就是對SQL常用聯合查詢以及聚合函式的一個總結。希望能對你的工作與學習有所幫助。感覺寫的還行，就反手一鍵三連吧。公眾號上更新可能要快一點，目前還在完善中。如果感覺總結不到位，也希望能留下您寶貴的意見，我會定期在文章中進行調整優化。

相關文章