MySQL復(fù)合查詢從基礎(chǔ)到多表關(guān)聯(lián)與高級(jí)技巧全解析
前言：
本文主要講解了在MySQL中的復(fù)合查詢，下面是關(guān)于本文章所需要數(shù)據(jù)的建表語(yǔ)句
創(chuàng)建表的語(yǔ)句：
DROP database IF EXISTS `scott`; CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; USE `scott`; DROP TABLE IF EXISTS `dept`; CREATE TABLE `dept` ( `deptno` int(2) unsigned zerofill NOT NULL COMMENT '部門(mén)編號(hào)', `dname` varchar(14) DEFAULT NULL COMMENT '部門(mén)名稱', `loc` varchar(13) DEFAULT NULL COMMENT '部門(mén)所在地點(diǎn)' ); DROP TABLE IF EXISTS `emp`; CREATE TABLE `emp` ( `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇員編號(hào)', `ename` varchar(10) DEFAULT NULL COMMENT '雇員姓名', `job` varchar(9) DEFAULT NULL COMMENT '雇員職位', `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇員領(lǐng)導(dǎo)編號(hào)', `hiredate` datetime DEFAULT NULL COMMENT '雇傭時(shí)間', `sal` decimal(7,2) DEFAULT NULL COMMENT '工資月薪', `comm` decimal(7,2) DEFAULT NULL COMMENT '獎(jiǎng)金', `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部門(mén)編號(hào)' ); DROP TABLE IF EXISTS `salgrade`; CREATE TABLE `salgrade` ( `grade` int(11) DEFAULT NULL COMMENT '等級(jí)', `losal` int(11) DEFAULT NULL COMMENT '此等級(jí)最低工資', `hisal` int(11) DEFAULT NULL COMMENT '此等級(jí)最高工資' ); insert into dept (deptno, dname, loc) values (10, 'ACCOUNTING', 'NEW YORK'); insert into dept (deptno, dname, loc) values (20, 'RESEARCH', 'DALLAS'); insert into dept (deptno, dname, loc) values (30, 'SALES', 'CHICAGO'); insert into dept (deptno, dname, loc) values (40, 'OPERATIONS', 'BOSTON'); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10); insert into salgrade (grade, losal, hisal) values (1, 700, 1200); insert into salgrade (grade, losal, hisal) values (2, 1201, 1400); insert into salgrade (grade, losal, hisal) values (3, 1401, 2000); insert into salgrade (grade, losal, hisal) values (4, 2001, 3000); insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);
1.基本查詢回顧：
1.1.查詢工資高于500或崗位為MANAGER的雇員，同時(shí)還要滿足他們的姓名首字母為大寫(xiě)的J
select * from EMP where (sal>500 or job='MANAGER') and ename like 'J%';
'J_' 和'J%' 的區(qū)別
這里‘J_’和‘J%’的主要區(qū)別在于它們所代表的字符串匹配模式的嚴(yán)格程度和范圍。‘J_’更為嚴(yán)格，只匹配特定長(zhǎng)度的字符串；而‘J%’則更為靈活，能匹配任意長(zhǎng)度的字符串（只要以‘J’開(kāi)頭）。
1.2.按照部門(mén)號(hào)升序而雇員的工資降序排序
select * from EMP order by deptno, sal desc;
排序的時(shí)候默認(rèn)是升序，降序需要自己寫(xiě)desc
1.3.使用年薪進(jìn)行降序排序
注意為空的情況，要使用函數(shù)ifnull進(jìn)行轉(zhuǎn)換為0，不然用null參與計(jì)算還是null
函數(shù)ifnull（）的用法
為什么最終的年薪是NULL呢，因?yàn)槲覀冊(cè)诒碇邪l(fā)現(xiàn)有人的獎(jiǎng)金是NULL，而我們的年薪計(jì)算方式就是月薪*12 + 獎(jiǎng)金，而NULL是不參與計(jì)算的，因此最終的結(jié)果就是NULL，所以我們需要使用函數(shù)ifnull（comm,0），這樣就把所有為NULL的數(shù)字變成0
注意前面的年薪使用過(guò)''之后，后面的年薪就不用加上引號(hào)了。
1.4.顯示工資最高的員工的名字和工作崗位
select ename, job from EMP where sal = (select max(sal) from EMP);
這就是復(fù)合查詢的體現(xiàn)，使用max(sal)獲得最大薪資，然后獲得最大數(shù)的薪資即可。
1.5.顯示工資高于平均工資的員工信息
select ename, sal from EMP where sal>(select avg(sal) from EMP);
跟上一題本質(zhì)是一樣的
1.6.顯示每個(gè)部門(mén)的平均工資和最高工資
select deptno, format(avg(sal), 2) , max(sal) from EMP group by deptno;
format（）格式化函數(shù)的用法
FORMAT(AVG(sal), 2) 的作用是將 AVG(sal)（平均工資）格式化為具有兩位小數(shù)的形式。
這是沒(méi)有格式化的效果：
這是格式化數(shù)據(jù)的效果：
select deptno ,format(avg(sal),2), max(sal) from emp group by deptno;
1.7.顯示平均工資低于2000的部門(mén)號(hào)和它的平均工資
select deptno, avg(sal) as avg_sal from EMP group by deptno having avg_sal<2000;
為什么不能用where？
在SQL中，WHERE和HAVING子句有不同的用途，這就是為什么在你的查詢中不能使用WHERE來(lái)過(guò)濾聚合結(jié)果。
WHERE 和 HAVING 的區(qū)別
WHERE子句：- 用途：
WHERE用于在數(shù)據(jù)聚合之前過(guò)濾行。它不能用于過(guò)濾聚合函數(shù)的結(jié)果。 - 執(zhí)行順序：在SQL查詢中，
WHERE子句在GROUP BY之前執(zhí)行。因此，它只能用于過(guò)濾單個(gè)行，而不是聚合后的結(jié)果。 - 適用性：
WHERE適用于在分組前對(duì)行進(jìn)行過(guò)濾，例如，過(guò)濾出特定部門(mén)的員工。
- 用途：
HAVING子句：- 用途：
HAVING用于在數(shù)據(jù)聚合之后過(guò)濾組。它可以用于過(guò)濾聚合函數(shù)的結(jié)果。 - 執(zhí)行順序：
HAVING子句在GROUP BY之后執(zhí)行，因此它可以用于過(guò)濾聚合后的結(jié)果。 - 適用性：
HAVING適用于過(guò)濾聚合后的結(jié)果，例如，過(guò)濾出平均工資大于2000的部門(mén)。
- 用途：
- 為什么不能使用
WHERE
在你的查詢中，你想過(guò)濾出平均工資大于2000的部門(mén)。由于平均工資是一個(gè)聚合結(jié)果，你需要使用HAVING來(lái)過(guò)濾這個(gè)結(jié)果。WHERE不能用于這個(gè)目的，因?yàn)樗诰酆现皥?zhí)行，無(wú)法訪問(wèn)聚合函數(shù)的結(jié)果。
1.8.顯示每種崗位的雇員總數(shù)，平均工資
select job,count(*), format(avg(sal),2) from EMP group by job;
統(tǒng)計(jì)全部人數(shù)直接用count（*）。
2. 多表查詢
實(shí)際開(kāi)發(fā)中往往數(shù)據(jù)來(lái)自不同的表，所以需要多表查詢。本節(jié)我們用一個(gè)簡(jiǎn)單的公司管理系統(tǒng)，有三張表EMP,DEPT,SALGRADE來(lái)演示如何進(jìn)行多表查詢。
案例：
2.1.顯示雇員名、雇員工資以及所在部門(mén)的名字
因?yàn)樯厦娴臄?shù)據(jù)來(lái)自EMP和DEPT表，因此要聯(lián)合查詢。
其實(shí)我們只要emp表中的deptno = dept表中的deptno字段的記錄
select EMP.ename, EMP.sal, DEPT.dname from EMP, DEPT where EMP.deptno =DEPT.deptno;
顯示部門(mén)號(hào)為10的部門(mén)名，員工名和工資
select ename, sal,dname from EMP, DEPT where EMP.deptno=DEPT.deptno and DEPT.deptno = 10;
注意判斷條件用and進(jìn)行連接，不要直接連著寫(xiě)。
2.2.顯示各個(gè)員工的姓名，工資，及工資級(jí)別
select ename, sal, grade from emp,salgrade where emp.sal between losal and hisal;
3.自連接
自連接是指在同一張表連接查詢
案例：
3.1.顯示員工FORD的上級(jí)領(lǐng)導(dǎo)的編號(hào)和姓名（mgr是員工領(lǐng)導(dǎo)的編號(hào)--empno）
使用的子查詢：
select empno,ename from emp where emp.empno=(select mgr from emp where ename='FORD');
使用多表查詢（自查詢）
-- 使用到表的別名 --from emp leader, emp worker，給自己的表起別名，因?yàn)橐茸龅芽柗e，所以別名可以先識(shí) 別 select leader.empno,leader.ename from emp leader, emp worker where leader.empno = worker.mgr and worker.ename='FORD';
4.子查詢
子查詢是指嵌入在其他sql語(yǔ)句中的select語(yǔ)句，也叫嵌套查詢
4.1 單行子查詢
返回一行記錄的子查詢
4.1.1.顯示SMITH同一部門(mén)的員工
select * from EMP WHERE deptno = (select deptno from EMP where ename='smith');
4.2 多行子查詢
返回多行記錄的子查詢
4.2.1.in關(guān)鍵字；
查詢和10號(hào)部門(mén)的工作崗位相同的雇員的名字，崗位，工資，部門(mén)號(hào)，但是不包含10自
己的
如果用 = 就會(huì)報(bào)錯(cuò)，因?yàn)椴樵兘Y(jié)果是多行，所以要用 in 關(guān)鍵字
select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=10) and deptno<>10;
4.2.2.all關(guān)鍵字；
顯示工資比部門(mén)30的所有員工的工資高的員工的姓名、工資和部門(mén)號(hào)
mysql> select ename, sal, deptno from emp where sal > all(select sal from emp where deptno=30);
當(dāng)然直接用函數(shù)書(shū)寫(xiě)也可以。
select ename,sal,deptno from emp where sal > (select max(sal) from emp where deptno = 30);
4.2.3.any關(guān)鍵字；
顯示工資比部門(mén)30的任意員工的工資高的員工的姓名、工資和部門(mén)號(hào)（包含自己部門(mén)
的員工）
select ename, sal, deptno from EMP where sal > any(select sal from EMP where deptno=30);
當(dāng)然用min函數(shù)也可以：
select ename,sal,deptno from emp where sal > (select min(sal) from emp where deptno = 30);
4.3 多列子查詢
單行子查詢是指子查詢只返回單列，單行數(shù)據(jù)；多行子查詢是指返回單列多行數(shù)據(jù)，都是針對(duì)單列而言的，而多列子查詢則是指查詢返回多個(gè)列數(shù)據(jù)的子查詢語(yǔ)句
案例：
4.3.1.查詢和SMITH的部門(mén)和崗位完全相同的所有雇員，不含SMITH本人
代碼：
mysql> select ename from EMP where (deptno, job)=(select deptno, job from EMP where ename='SMITH') and ename <> 'SMITH';
注意點(diǎn)：
順序必須一致，不然就會(huì)報(bào)錯(cuò)
嵌套的select語(yǔ)句必須加上括號(hào)
4.4 在from子句中使用子查詢
子查詢語(yǔ)句出現(xiàn)在from子句中。這里要用到數(shù)據(jù)查詢的技巧，把一個(gè)子查詢當(dāng)做一個(gè)臨時(shí)表使用。
案例：
4.4.1.顯示每個(gè)高于自己部門(mén)平均工資的員工的姓名、部門(mén)、工資、平均工資
獲取各個(gè)部門(mén)的平均工資，將其看作臨時(shí)表
//獲取各個(gè)部門(mén)的平均工資，將其看作臨時(shí)表 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;
4.4.2.查找每個(gè)部門(mén)工資最高的人的姓名、工資、部門(mén)、最高工資
select ename, sal,emp.deptno,最高工資 from emp, (select deptno ,max(sal) '最高工資' from emp group by deptno) tmp where emp.deptno = tmp.deptno and emp.sal = 最高工資;
4.4.3.顯示每個(gè)部門(mén)的信息（部門(mén)名，編號(hào)，地址）和人員數(shù)量
-- 1. 對(duì)EMP表進(jìn)行人員統(tǒng)計(jì) select count(*), deptno from EMP group by deptno; -- 2. 將上面的表看作臨時(shí)表 select DEPT.deptno, dname, mycnt, loc from DEPT, (select count(*) mycnt, deptno from EMP group by deptno) tmp where DEPT.deptno=tmp.deptno;
4.5 合并查詢
在實(shí)際應(yīng)用中，為了合并多個(gè)select的執(zhí)行結(jié)果，可以使用集合操作符 union，union all
4.5.1 union（自動(dòng)去重）
該操作符用于取得兩個(gè)結(jié)果集的并集。當(dāng)使用該操作符時(shí)，會(huì)自動(dòng)去掉結(jié)果集中的重復(fù)行。
案例：將工資大于2500或職位是MANAGER的人找出來(lái)
select * from emp where sal > 2500 union select * from emp where job = 'MANAGER';
4.5.2 union all（不去重）
該操作符用于取得兩個(gè)結(jié)果集的并集。當(dāng)使用該操作符時(shí)，不會(huì)去掉結(jié)果集中的重復(fù)行。
案例：將工資大于25000或職位是MANAGER的人找出來(lái)
select * from emp where sal > 2500 union all select * from emp where job = 'MANAGER';
5.表的內(nèi)連和外連
5.1 內(nèi)連接
內(nèi)連接實(shí)際上就是利用where子句對(duì)兩種表形成的笛卡兒積進(jìn)行篩選，我們前面學(xué)習(xí)的查詢都是內(nèi)連接，也是在開(kāi)發(fā)過(guò)程中使用的最多的連接查詢。
語(yǔ)法：
select 字段 from 表1 inner join 表2 on 連接條件 and 其他條件；
備注：前面學(xué)習(xí)的都是內(nèi)連接
案例：顯示SMITH的名字和部門(mén)名稱
-- 用前面的寫(xiě)法 select ename, dname from EMP, DEPT where EMP.deptno=DEPT.deptno and ename='SMITH'; -- 用標(biāo)準(zhǔn)的內(nèi)連接寫(xiě)法 select ename, dname from EMP inner join DEPT on EMP.deptno=DEPT.deptno and ename='SMITH';
5.2 外連接
外連接分為左外連接和右外連接
5.2.1 左外連接
如果聯(lián)合查詢，左側(cè)的表完全顯示我們就說(shuō)是左外連接。
語(yǔ)法：
select 字段名 from 表名1 left join 表名2 on 連接條件
案例：
-- 建兩張表 create table stu (id int, name varchar(30)); -- 學(xué)生表 insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono'); create table exam (id int, grade int); -- 成績(jī)表 insert into exam values(1, 56),(2,76),(11, 8);
查詢所有學(xué)生的成績(jī)，如果這個(gè)學(xué)生沒(méi)有成績(jī)，也要將學(xué)生的個(gè)人信息顯示出來(lái)
-- 當(dāng)左邊表和右邊表沒(méi)有匹配時(shí)，也會(huì)顯示左邊表的數(shù)據(jù) select * from stu left join exam on stu.id=exam.id;
如果我們使用內(nèi)連接，左表的數(shù)據(jù)就不會(huì)完全顯示出來(lái)，但是用左連接，左表的數(shù)據(jù)就算為空也會(huì)顯示出來(lái)！
5.2.2 右外連接
如果聯(lián)合查詢，右側(cè)的表完全顯示我們就說(shuō)是右外連接。
語(yǔ)法：
select 字段 from 表名1 right join 表名2 on 連接條件；
其實(shí)左連接和右連接是一樣的，如果將兩個(gè)表名交換過(guò)來(lái)，左連接也可以是右連接！
案例：
對(duì)stu表和exam表聯(lián)合查詢，把所有的成績(jī)都顯示出來(lái)，即使這個(gè)成績(jī)沒(méi)有學(xué)生與它對(duì)應(yīng)，也要
顯示出來(lái)
select * from stu right join exam on stu.id=exam.id;
左連接和右連接的互相轉(zhuǎn)換：
列出部門(mén)名稱和這些部門(mén)的員工信息，同時(shí)列出沒(méi)有員工的部門(mén)
方法一： select d.dname, e.* from dept d left join emp e on d.deptno=e.deptno; 方法二： select d.dname, e.* from emp e right join dept d on d.deptno=e.deptno;
總結(jié)：
小技巧：
任何時(shí)刻，查詢出來(lái)的臨時(shí)結(jié)構(gòu)，本質(zhì)在邏輯上也是表結(jié)構(gòu)。
解決多表問(wèn)題的本質(zhì)：想辦法將多表轉(zhuǎn)化為單表，所以MySQL中，所有select的問(wèn)題全部都可以轉(zhuǎn)成單表問(wèn)題！
到此這篇關(guān)于MySQL復(fù)合查詢?nèi)馕觯簭幕A(chǔ)到多表關(guān)聯(lián)與高級(jí)技巧的文章就介紹到這了,更多相關(guān)mysql復(fù)合查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
- MySQL數(shù)據(jù)庫(kù)復(fù)合查詢與內(nèi)外連接圖文詳解
- MySQL復(fù)合查詢(多表查詢、子查詢)的實(shí)現(xiàn)
- MySQL復(fù)合查詢的實(shí)現(xiàn)示例
- MySQL復(fù)合查詢操作實(shí)戰(zhàn)案例
- MySQL數(shù)據(jù)庫(kù)復(fù)合查詢操作實(shí)戰(zhàn)
- MySQL多表關(guān)聯(lián)查詢方式及實(shí)際應(yīng)用
- MySQL?UPDATE多表關(guān)聯(lián)更新的實(shí)現(xiàn)示例
- Mysql多表關(guān)聯(lián)不走索引的原因及分析
- MySQL詳細(xì)講解多表關(guān)聯(lián)查詢
- MySQL 多表關(guān)聯(lián)一對(duì)多查詢實(shí)現(xiàn)取最新一條數(shù)據(jù)的方法示例
相關(guān)文章
本篇文章為大家介紹了MYSQL數(shù)據(jù)庫(kù)學(xué)習(xí)中三大范式的規(guī)則詳解，有需要的朋友可以借鑒參考下，希望可以對(duì)大家的數(shù)據(jù)庫(kù)學(xué)習(xí)有所幫助2021-09-09
本文主要介紹了MYSQL主鍵約束和唯一約束的區(qū)別，文中通過(guò)示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2021-09-09
mysql?in索引慢查詢優(yōu)化實(shí)現(xiàn)步驟解析
這篇文章主要為大家介紹了mysql?in慢查詢優(yōu)化實(shí)現(xiàn)步驟的示例詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪2023-05-05
MySQL表的內(nèi)外連接和視圖使用實(shí)戰(zhàn)練習(xí)
這篇文章主要給大家介紹了關(guān)于MySQL表的內(nèi)外連接和視圖使用的相關(guān)資料,MySQL的內(nèi)外連接查詢是用于將兩個(gè)或多個(gè)表中的數(shù)據(jù)進(jìn)行關(guān)聯(lián)查詢的一種方法,文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下2024-01-01
這篇文章主要介紹了MySQL中創(chuàng)建表的三種方法，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-02-02
MySQL性能優(yōu)化的一些技巧幫助你的數(shù)據(jù)庫(kù)
你完成了你的品牌新的應(yīng)用程序,一切工作就像一個(gè)魅力;突然間,一個(gè)大爆發(fā)的用戶你的MySQL服務(wù)器,您的網(wǎng)站已關(guān)閉,是什么問(wèn)題導(dǎo)致的呢？以下是MySQL性能優(yōu)化的一些技巧,將幫助你,幫助你的數(shù)據(jù)庫(kù)2013-01-01
Linux下安裝mysql-5.6.12-linux-glibc2.5-x86_64.tar.gz
這篇文章主要介紹了Linux下安裝mysql-5.6.12-linux-glibc2.5-x86_64.tar.gz的相關(guān)資料,非常不錯(cuò)，具有參考借鑒價(jià)值，需要的朋友可以參考下2016-09-09
本文給大家匯總介紹了下載Linux系統(tǒng)中操作mysql的一些常用的命令，非常實(shí)用，有需要的小伙伴可以參考下2018-02-02
這篇文章主要介紹了Mysql 5.6.24安裝實(shí)例教程,非常不錯(cuò)，具有參考借鑒價(jià)值，需要的朋友可以參考下2016-12-12
vscode安裝mysql相關(guān)插件的詳細(xì)步驟
這篇文章主要給大家介紹了關(guān)于vscode安裝mysql相關(guān)插件的詳細(xì)步驟,這將幫助你更高效地管理數(shù)據(jù)庫(kù)操作,文中通過(guò)圖文介紹的非常詳細(xì),需要的朋友可以參考下2024-07-07