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 '部門編號',
  `dname` varchar(14) DEFAULT NULL COMMENT '部門名稱',
  `loc` varchar(13) DEFAULT NULL COMMENT '部門所在地點(diǎn)'
);
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇員編號',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇員姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇員職位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇員領(lǐng)導(dǎ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 '部門編號'
);
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `grade` int(11) DEFAULT NULL COMMENT '等級',
  `losal` int(11) DEFAULT NULL COMMENT '此等級最低工資',
  `hisal` int(11) DEFAULT NULL COMMENT '此等級最高工資'
);
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í)還要滿足他們的姓名首字母為大寫的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)格，只匹配特定長度的字符串；而‘J%’則更為靈活，能匹配任意長度的字符串（只要以‘J’開頭）。

1.2.按照部門號升序而雇員的工資降序排序

select * from EMP order by deptno, sal desc;

排序的時(shí)候默認(rèn)是升序，降序需要自己寫desc

1.3.使用年薪進(jìn)行降序排序

注意為空的情況，要使用函數(shù)ifnull進(jìn)行轉(zhuǎn)換為0，不然用null參與計(jì)算還是null

函數(shù)ifnull（）的用法

為什么最終的年薪是NULL呢，因?yàn)槲覀冊诒碇邪l(fā)現(xiàn)有人的獎(jiǎng)金是NULL，而我們的年薪計(jì)算方式就是月薪*12 + 獎(jiǎng)金，而NULL是不參與計(jì)算的，因此最終的結(jié)果就是NULL，所以我們需要使用函數(shù)ifnull（comm,0），這樣就把所有為NULL的數(shù)字變成0

注意前面的年薪使用過''之后，后面的年薪就不用加上引號了。

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è)部門的平均工資和最高工資

select deptno, format(avg(sal), 2) , max(sal) from EMP group by deptno;

format（）格式化函數(shù)的用法

FORMAT(AVG(sal), 2) 的作用是將 AVG(sal)（平均工資）格式化為具有兩位小數(shù)的形式。

這是沒有格式化的效果：

這是格式化數(shù)據(jù)的效果：

select deptno ,format(avg(sal),2), max(sal) from emp group by deptno;

1.7.顯示平均工資低于2000的部門號和它的平均工資

select deptno, avg(sal) as avg_sal from EMP group by deptno having
avg_sal<2000;

為什么不能用where？

在SQL中，WHERE和HAVING子句有不同的用途，這就是為什么在你的查詢中不能使用WHERE來過濾聚合結(jié)果。

WHERE 和 HAVING 的區(qū)別

WHERE 子句：
- 用途：WHERE用于在數(shù)據(jù)聚合之前過濾行。它不能用于過濾聚合函數(shù)的結(jié)果。
- 執(zhí)行順序：在SQL查詢中，WHERE子句在GROUP BY之前執(zhí)行。因此，它只能用于過濾單個(gè)行，而不是聚合后的結(jié)果。
- 適用性：WHERE適用于在分組前對行進(jìn)行過濾，例如，過濾出特定部門的員工。
HAVING 子句：
- 用途：HAVING用于在數(shù)據(jù)聚合之后過濾組。它可以用于過濾聚合函數(shù)的結(jié)果。
- 執(zhí)行順序：HAVING子句在GROUP BY之后執(zhí)行，因此它可以用于過濾聚合后的結(jié)果。
- 適用性：HAVING適用于過濾聚合后的結(jié)果，例如，過濾出平均工資大于2000的部門。
為什么不能使用WHERE

在你的查詢中，你想過濾出平均工資大于2000的部門。由于平均工資是一個(gè)聚合結(jié)果，你需要使用HAVING來過濾這個(gè)結(jié)果。WHERE不能用于這個(gè)目的，因?yàn)樗诰酆现皥?zhí)行，無法訪問聚合函數(shù)的結(jié)果。

1.8.顯示每種崗位的雇員總數(shù)，平均工資

select job,count(*), format(avg(sal),2) from EMP group by job;

統(tǒng)計(jì)全部人數(shù)直接用count（*）。

2. 多表查詢

實(shí)際開發(fā)中往往數(shù)據(jù)來自不同的表，所以需要多表查詢。本節(jié)我們用一個(gè)簡單的公司管理系統(tǒng)，有三張表EMP,DEPT,SALGRADE來演示如何進(jìn)行多表查詢。

案例：

2.1.顯示雇員名、雇員工資以及所在部門的名字

因?yàn)樯厦娴臄?shù)據(jù)來自EMP和DEPT表，因此要聯(lián)合查詢。

其實(shí)我們只要emp表中的deptno = dept表中的deptno字段的記錄

select EMP.ename, EMP.sal, DEPT.dname from EMP, DEPT where EMP.deptno =DEPT.deptno;

顯示部門號為10的部門名，員工名和工資

select ename, sal,dname from EMP, DEPT where EMP.deptno=DEPT.deptno and
DEPT.deptno = 10;

注意判斷條件用and進(jìn)行連接，不要直接連著寫。

2.2.顯示各個(gè)員工的姓名，工資，及工資級別

select ename, sal, grade from emp,salgrade where emp.sal between losal and hisal;

3.自連接

自連接是指在同一張表連接查詢

案例：

3.1.顯示員工FORD的上級領(lǐng)導(dǎo)的編號和姓名（mgr是員工領(lǐng)導(dǎ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，所以別名可以先識
別
select leader.empno,leader.ename from emp leader, emp worker where
leader.empno = worker.mgr and worker.ename='FORD';

4.子查詢

子查詢是指嵌入在其他sql語句中的select語句，也叫嵌套查詢

4.1 單行子查詢

返回一行記錄的子查詢

4.1.1.顯示SMITH同一部門的員工

select * from EMP WHERE deptno = (select deptno from EMP where
ename='smith');

4.2 多行子查詢

返回多行記錄的子查詢

4.2.1.in關(guān)鍵字；

查詢和10號部門的工作崗位相同的雇員的名字，崗位，工資，部門號，但是不包含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)鍵字；

顯示工資比部門30的所有員工的工資高的員工的姓名、工資和部門號

mysql> select ename, sal, deptno from emp where sal > all(select sal from emp where deptno=30);

當(dāng)然直接用函數(shù)書寫也可以。

 select ename,sal,deptno from emp where sal > (select max(sal) from emp where deptno = 30);

4.2.3.any關(guān)鍵字；

顯示工資比部門30的任意員工的工資高的員工的姓名、工資和部門號（包含自己部門
的員工）

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ù)，都是針對單列而言的，而多列子查詢則是指查詢返回多個(gè)列數(shù)據(jù)的子查詢語句

案例：

4.3.1.查詢和SMITH的部門和崗位完全相同的所有雇員，不含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語句必須加上括號

4.4 在from子句中使用子查詢

子查詢語句出現(xiàn)在from子句中。這里要用到數(shù)據(jù)查詢的技巧，把一個(gè)子查詢當(dāng)做一個(gè)臨時(shí)表使用。

案例：

4.4.1.顯示每個(gè)高于自己部門平均工資的員工的姓名、部門、工資、平均工資

獲取各個(gè)部門的平均工資，將其看作臨時(shí)表

//獲取各個(gè)部門的平均工資，將其看作臨時(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è)部門工資最高的人的姓名、工資、部門、最高工資

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è)部門的信息（部門名，編號，地址）和人員數(shù)量

-- 1. 對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的人找出來

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的人找出來

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子句對兩種表形成的笛卡兒積進(jìn)行篩選，我們前面學(xué)習(xí)的查詢都是內(nèi)連接，也是在開發(fā)過程中使用的最多的連接查詢。

語法：

select 字段 from 表1 inner join 表2 on 連接條件 and 其他條件；

備注：前面學(xué)習(xí)的都是內(nèi)連接

案例：顯示SMITH的名字和部門名稱

-- 用前面的寫法
select ename, dname from EMP, DEPT where EMP.deptno=DEPT.deptno and
ename='SMITH';
-- 用標(biāo)準(zhǔn)的內(nèi)連接寫法
select ename, dname from EMP inner join DEPT on EMP.deptno=DEPT.deptno and
ename='SMITH';

5.2 外連接

外連接分為左外連接和右外連接

5.2.1 左外連接

如果聯(lián)合查詢，左側(cè)的表完全顯示我們就說是左外連接。

語法：

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); -- 成績表
insert into exam values(1, 56),(2,76),(11, 8);

查詢所有學(xué)生的成績，如果這個(gè)學(xué)生沒有成績，也要將學(xué)生的個(gè)人信息顯示出來

-- 當(dāng)左邊表和右邊表沒有匹配時(shí)，也會(huì)顯示左邊表的數(shù)據(jù)
select * from stu left join exam on stu.id=exam.id;

如果我們使用內(nèi)連接，左表的數(shù)據(jù)就不會(huì)完全顯示出來，但是用左連接，左表的數(shù)據(jù)就算為空也會(huì)顯示出來！

5.2.2 右外連接

如果聯(lián)合查詢，右側(cè)的表完全顯示我們就說是右外連接。

語法：

select 字段 from 表名1 right join 表名2  on 連接條件；

其實(shí)左連接和右連接是一樣的，如果將兩個(gè)表名交換過來，左連接也可以是右連接！

案例：
對stu表和exam表聯(lián)合查詢，把所有的成績都顯示出來，即使這個(gè)成績沒有學(xué)生與它對應(yīng)，也要
顯示出來

select * from stu right join exam on stu.id=exam.id;

左連接和右連接的互相轉(zhuǎn)換：
列出部門名稱和這些部門的員工信息，同時(shí)列出沒有員工的部門

方法一：
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í)刻，查詢出來的臨時(shí)結(jié)構(gòu)，本質(zhì)在邏輯上也是表結(jié)構(gòu)。

解決多表問題的本質(zhì)：想辦法將多表轉(zhuǎn)化為單表，所以MySQL中，所有select的問題全部都可以轉(zhuǎn)成單表問題！

到此這篇關(guān)于MySQL復(fù)合查詢?nèi)馕觯簭幕A(chǔ)到多表關(guān)聯(lián)與高級技巧的文章就介紹到這了,更多相關(guān)mysql復(fù)合查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL復(fù)合查詢從基礎(chǔ)到多表關(guān)聯(lián)與高級技巧全解析

目錄

前言：