MySQL -- 復合查詢

數據庫的查詢是數據庫使用中比較重要的環節，前面的基礎查詢比較簡單，不做介紹，可自行查閱。本文主要介紹復合查詢，并結合用例進行講解。

本文的用例依據Soctt模式的經典測試表，可以自行下載，也可以自己創建
鏈接：點這里跳轉
自行創建步驟如下：

0、預備工作

0.1 建表

-- 創建 dept 表（部門表）
CREATE TABLE dept (deptno INT PRIMARY KEY,  -- 部門編號dname VARCHAR(14),       -- 部門名稱loc VARCHAR(13)          -- 部門位置
);-- 創建 emp 表（員工表）
CREATE TABLE emp (empno INT PRIMARY KEY,   -- 員工編號ename VARCHAR(10),       -- 員工姓名job VARCHAR(9),          -- 職位mgr INT,                 -- 上級經理編號hiredate DATE,           -- 入職日期sal DECIMAL(7,2),        -- 工資comm DECIMAL(7,2),       -- 獎金deptno INT,              -- 部門編號FOREIGN KEY (deptno) REFERENCES dept(deptno)
);-- 創建 salgrade 表（工資等級表）
CREATE TABLE salgrade (grade INT PRIMARY KEY,   -- 工資等級losal DECIMAL(7,2),      -- 最低工資hisal DECIMAL(7,2)       -- 最高工資
);

0.2 插入測試數據

-- 插入 dept 表數據
INSERT INTO dept (deptno, dname, loc) VALUES
(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');-- 插入 emp 表數據
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES
(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, NULL, 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30),
(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, NULL, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30),
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, NULL, 30),
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, NULL, 10),
(7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000.00, NULL, 20),
(7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000.00, NULL, 10),
(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30),
(7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100.00, NULL, 20),
(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, NULL, 30),
(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, NULL, 20),
(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, NULL, 10);-- 插入 salgrade 表數據
INSERT INTO salgrade (grade, losal, hisal) VALUES
(1, 700, 1200),
(2, 1201, 1400),
(3, 1401, 2000),
(4, 2001, 3000),
(5, 3001, 9999);

創建好以后，如果表的內容和下圖一樣，那基本就沒問題了

在這里插入圖片描述

1、復合表的查詢

1.查詢工資高于500 或崗位為MANAGER 的雇員，同時還要滿足他們的姓名首字母為大寫的J

根據上述的要求，我們可以發現，要查詢的表總共兩個條件，工資高于500 或崗位為MANAGER，首字母為J。 根據上述的兩個條件，我們可以寫出對應的sql查詢語句： select name, job , sal from emp where (sal >= 500 or job = 'MANAGER') and enum like 'J%'; 這兩個條件可以看成是并列條條件

在這里插入圖片描述

2.按照部門號升序而雇員工資降序的順序對表的內容排序

先觀察一下這里的需求，首先就是要部門號升序，然后就是雇員的工資降序，所以這個案例的需求非常簡單。根據這個要求我們可以寫出sql查詢語句：select deptno , sal from emp order by deptno asc , sal desc;

在這里插入圖片描述

3. 使用年薪進行降序排序
這里我們需要特別注意的一個點就是年薪這個概念，年薪在這里是包括了12個月的月薪加上獎金，而這里獎金就是comm，但是獎金這一列很多都是NULL，而NULL是不參與計算的，所以這里就需要用ifnull(expression ，values)(如果expression為null，返回的值為values否則返回expression)利用這個函數的特點，我們就可以算出年薪 = 12 x sal + ifnull(comm,0)；根據這個要求我們可以寫出sql查詢語句：select sal * 12 + ifnull(comm,0) 年薪 from emp order by 年薪 desc;

在這里插入圖片描述

4.顯示工資最高的員工名字和工作崗位
這條語句的要求非常簡單，我們可以直接寫出對應sql查詢語句： select ename , job from emp where sal = (select max(sal) from emp);這里select是可以嵌套使用的，執行順序就和C語言的中函數一樣。當然，這里我們也可以分兩步走，先把最高工資打印出來，再讓第二條語句中 sal = 最高工資，結果是一樣的。

在這里插入圖片描述

5. 顯示工資高與平均工資的員工信息
這個例子的要求和上面一個例子相差無幾，做法也都差不多，先求出平均工資，再作比較即可。我們可以直接寫出對應sql查詢語句：select * from emp where sal >= (select avg(sal) from emp);

在這里插入圖片描述

6.顯示每個部門的平均工資與最高工資
這里也是只有兩個條件，我們將平均工資和最高工資列出即可。我們可以直接寫出對應sql查詢語句：select deptno ,avg(sal),max(sal) from emp group by deptno;這里是先分組，然后再對內中內容進行篩查。

在這里插入圖片描述

7.顯示平均工資低于兩千的部門號和它的平均工資
這個例子就需要和上面的例子一樣，先對部門進行分組，分完組后就可以計算平均工資，然后再比對工資低于兩千的部門。根據上述的條件，我們可以直接寫出對應sql查詢語句：select deptno ,avg(sal) 平均工資 from emp group by deptno having 平均工資 < 2000;這里的having是最后執行的，所以可以使用平均工資這個別名。

在這里插入圖片描述

8.顯示每種崗位的雇員總數，平均工資
這個例子和上面幾個例子大差不差，這里不再贅述，直接把對應的語句寫出：select deptno ,count(job) 人數,avg(sal) 平均工資 from emp group by job;

在這里插入圖片描述

2、多表復合查詢

前面我們介紹了單張表下的復合查詢，但在日常生活中還存在非常的多表查詢的情況。

1、顯示每一個雇員名，雇員工資和部門名稱
這個例子中和上面不同就是我們需要去查詢部門名稱，部門名稱是在dept這張表中，而雇員名稱以及工資在emp這張表中。這就需要我們將兩張表的內容合并成一張表，也就是對第一張表的每一行內容與第二張表整張表進行組合，這種窮舉的方式也叫作笛卡爾積。當然這種方式會生成很多沒有啥意義的組合(部門號不對應)。這里我們就可以使用where進行篩查，select * from emp, dept where emp.deptno = dept.deptno結果如下圖

經過上述sql語句的篩查基本已經把要求給完成了，我們這里稍微完善一下即可：select ename,sal,dname from emp, dept where emp.deptno = dept.deptno

3、自鏈接

前面我們了解了不同表之間的進行鏈接查詢，下面介紹一下同一張表之間鏈接。以下面這個例子為例：a.顯示員工FORD的上級領導的編號和姓名（mgr是員工領導的編號–empno）。這個例子中，我們需要將在FORD的領導編號查出，然后在從當前這張表中查領導的相關信息。由此我們可以寫出對應的sql語句：select empno,ename from emp where emp.empno=(select mgr from emp where ename=‘FORD’);

4、子查詢

概念：子查詢是指嵌入在其他sql語句中的select語句，也叫嵌套查詢。前面已有描述，這里就不再贅述

4.1、單行子查詢

單行子查詢表示的就是返回結果只有一行的子查詢，以下面這一個通過下面這個例子幫助了解：
顯示SMITH同一部門的員工
這個案例比較簡單，我們只需先對SMITH先做子查詢即可：select * from emp where deptno = (select deptno from emp where ename=‘smith’);

4.3、多行子查詢

這種子查詢返回結果有很多行，但是原本子查詢返回結果只有一行，所以這里我們需要將引入一些關鍵字，才能使其達到多行子查詢的效果。

in關鍵字
all關鍵字
any關鍵字

首先我們可以通過一個例子了解一下in關鍵字：a.查詢和10號部門的工作崗位相同的雇員的名字，崗位，工資，部門號，但是不包含10自己.
在這個例子中，我們要先查詢10號部門的工作崗位相同的雇員，然后才是其它的相關信息，最后剔除十號部門。
第一步：select distinct job from emp where deptno=10;
第二步：select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=10);這里的in在where中作為一種條件判斷，表示判斷job否在子查詢中的表中。
第三步：select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=10) and deptno<>10;

然后，我們通過另外一個例子來增加對all的理解：顯示工資比部門30的所有員工的工資高的員工的姓名、工資和部門號.這里的all關鍵字其實和上面的in關鍵字一樣，也是where中作為一種條件判斷，代表子查詢的所有結果。我們可以直接寫出對應的sql語句：select ename, sal, deptno from emp where sal > all(select sal from emp where deptno=30);

最后一個any，作用和上面兩個關鍵字大同小異，表示子查詢的任意一個結果，用一個例子幫助理解：顯示工資比部門30的任意員工的工資高的員工的姓名、工資和部門號（包含自己部門的員工）.，對應的sql語句：select ename, sal, deptno from emp where sal > any(select sal from emp where deptno=30);

4.4、多列子查詢

單行子查詢是指子查詢只返回單列，單行數據；多行子查詢是指返回單列多行數據，都是針對單列而言的，而多列子查詢則是指查詢返回多個列數據的子查詢語句。以下面例子為例：查詢和SMITH的部門和崗位完全相同的所有雇員，不含SMITH本人這里第一步就是篩出SMITH部門和崗位相同的雇員，然后剔除SMITH，我們可以用括號的方式來對子查詢各列的數據進行查詢比對，具體方式如下： select ename from emp where (deptno, job)=(select deptno, job from emp where ename=‘SMITH’) and ename <> ‘SMITH’;

4.5、from字句中使用子查詢

通過對前面的相關知識的了解，我們可以知道，子查詢本質其實就是一張新的臨時表，所以本質上我們查表都是在查一張表，就是將臨時表和原表進行處理后的一張新表。既然上面 where 后面能夠使用子查詢，那from后面也必然是可以使用子查詢的。from后面使用子查詢就是將子查詢生成的臨時表與另外的表做笛卡爾積生成新的表，在從新的表中對數據進行篩查。下面用一個例子來幫助理解：顯示每個高于自己部門平均工資的員工的姓名、部門、工資、平均工資
對應的sql語句：//獲取各個部門的平均工資，將其看作臨時表 select ename, deptno, sal, format(asal,2) from emp, (select avg(sal) asal, deptno dt from emp group by deptno) tmp where emp.sal > tmp.asal and emp.deptno=tmp.dt;需要需要注意的時from中子查詢形成的臨時表要有臨時的名稱。

5、合并查詢

由于這個合并查詢并不多見，使用率也較低，所以這里簡單介紹即可。

union操作符：該操作符用于取得兩個結果集的并集。當使用該操作符時，會自動去掉結果集中的重復行。例子：將工資大于2500或職位是MANAGER的人找出來。 sql語句：select ename, sal, job from emp where sal>2500 union select ename, sal, job fromemp where job='MANAGER';

union all操作符：該操作符用于取得兩個結果集的并集。當使用該操作符時，不會去掉結果集中的重復行。例子：將工資大于25000或職位是MANAGER的人找出來。sql語句：select ename, sal, job from emp where sal>2500 union all select ename, sal, job from emp where job='MANAGER';