查询累计到当前月亮mysql,MySql的回顾六:子查询(内查询)

ゝ一纸荒年。 2023-01-17 08:23 121阅读 0赞

MySql的回顾六:子查询(内查询)

![7747d6ac93c8eca9dca1d0940ce64b97.png][]西北望乡何处是，东南见月几回圆。

月亮又慢悠悠的挂上了天空，趁着睡前梦呓，我就带领各位可爱的读者们探索MySql最后的子查询部分。

说明:有些查询结果出来结果截图与题目要求不一样会出现多余的字段是为了方便展示结果的可读性。实际操作的读者可以删除SELECT后面多余的字段得到正确的结果。

\#WHERE或HAVING后面

\#1.标量子查询(单行子查询)

\#2.列子查询(多行子查询)

\#3.行子查询(多列多行)

\#特点:

\# ①子查询放在小括号内

\# ②子查询一般放在条件的右侧

\# ③标量子查询:一般搭配着单行操作符使用

\# 单行操作符:> < >= <= <> !-\# 列子查询，一般搭配着多行操作符使用

\# IN,ANY/SOME(任意),ALL

\# ④子查询的执行优先与主查询执行,主查询的条件用到了子查询的结果。

\#1.标量子查询

\#案例1:谁的工资比Abel高？

\#①查询Abel的工资

SELECT salary

FROM employees

WHERE last\_name= "Abel";

![f58c8ac21ddaed683d2e4934cf60e5f8.png][]

\#案例2.返回job\_id与141号员工相同，salary比143号员工多的员工姓名，job\_id,工资。

\#①查141员工的job\_id

SELECT job\_id

FROM employees

WHERE employee\_id="141";

![c20a6b90340090dfc5f8d75d8ff51c20.png][]

\#③最后合并结果

SELECT CONCAT(last\_name,first\_name) AS 姓名,

job\_id AS 工种编号,

salary AS 工资

FROM employees

WHERE job\_id=(

SELECT job\_id

FROM employees

WHERE employee\_id="141"

)

AND salary>(

SELECT salary

FROM employees

WHERE employee\_id="143");

![f182d8849451711557e5bce95028eadd.png][]

SELECT

last\_name AS 姓,

salary AS 工资,

job\_id AS 工种编号

FROM employees

WHERE salary=(

SELECT MIN(salary)

FROM employees

);

![ae18d37396db0ca9fc2a2a252e9ead15.png][]

\#分组后，筛选条件①.【不用排除没有部门的所以不筛选部门编号】

SELECT department\_id AS 部门编号,

MIN(salary) AS 月薪

FROM employees

\#WHERE department\_id

GROUP BY department\_id

HAVING 月薪>(

SELECT MIN(salary)

FROM employees

);

![57267594536e68116c895f19694335f9.png][]

\#案例1.返回location\_id是1400或1700的部门中的所有员工姓名。

\#①查询location\_id是1400或1700的部门编号

SELECT DISTINCT department\_id

FROM departments

WHERE location\_id IN(1400,1700);

![60338310920678f776a5345062854e68.png][]1400,1700)

);

![456d7c3319d820b667202fc1406182e8.png][]\#案例.返回location\_id不是1400或1700的部门中的所有员工姓名。

SELECT CONCAT(last\_name,first\_name) AS 姓名

FROM employees

WHERE department\_id NOT IN(

SELECT DISTINCT department\_id

FROM departments

WHERE location\_id IN(1400,1700)

);==============================SELECT CONCAT(last\_name,first\_name) AS 姓名

FROM employees

WHERE department\_id <> ALL(

SELECT DISTINCT department\_id

FROM departments

WHERE location\_id IN(1400,1700)

);

![532a02b85fa73fff6c6036a1dabbd6a8.png][]SELECT DISTINCT salary

FROM employees

WHERE job\_id="IT\_PROG";

![45e6fbe290a7c5e19c1b2deaf03840d8.png][]

\#③合并①与②在员工表中查出来

SELECT employee\_id AS 员工编号,

CONCAT(last\_name,first\_name) AS 姓名,

job\_id AS 工种编号,

salary AS 工资

FROM employees

WHERE job\_id!= "IT\_PROG"

AND salary

SELECT salary

FROM employees

WHERE job\_id="IT\_PROG"

);

![e4bfc3fcbcc9da08c1b9911bf7b2d8f5.png][]\#案例3.返回其他部门中比job\_id为‘IT\_PROG’部门所有工资都低的员工

\#的员工号，姓名，job\_id以及salary。

\#①先把IT\_PROG部门的工资查出来。

SELECT DISTINCT salary

FROM employees

WHERE job\_id="IT\_PROG";

![6b435d52478e1019416e34f54acc07a5.png][]=============================MIN替代ALL

SELECT employee\_id AS 员工号,

CONCAT(last\_name,first\_name) AS 姓名,

job\_id AS 工种编号,

salary AS 工资

FROM employees

WHERE salary

SELECT MIN(salary)

FROM employees

WHERE job\_id="IT\_PROG"

)

AND job\_id <> "IT\_PROG";

![c8d650cf8bdb9a4130be7c18abf174cc.png][]

SELECT \*

FROM employees

WHERE employee\_id = (

SELECT MIN(employee\_id)

FROM employees

)

AND salary = (

SELECT MAX(salary)

FROM employees

);

![3d4bd2dea610b4a453b52f96a138b906.png][]\#二.SELECT子查询\#仅仅支持标量子查询，结果是一行一列

\#案例1.查询每个部门的员工个数

SELECT d.\*,(SELECT COUNT(\*) FROM employees)

FROM departments d;

![97bf3c0b11fe7062eaf065e7fb2dadd1.png][]

\#案例2.查询员工号=102的部门名。

SELECT department\_name

FROM departments;==============SELECT employee\_id

FROM employees

WHERE employee\_id = 102;

![e1a28971baa50bfcfd9d494874995668.png][]

![cfe843b2042b42117c9fc85339a3256b.png][]

SELECT e.平均工资,j.grade\_level

FROM job\_grades AS j

SELECT ROUND(AVG(salary),2) AS 平均工资,department\_id

FROM employees

GROUP BY department\_id

) AS e

WHERE e.平均工资 BETWEEN j.lowest\_sal AND j.highest\_sal;

\#1999语法，老师答案

SELECT e.\*,j.grade\_level

FROM (

SELECT ROUND(AVG(salary),2) AS 平均工资,department\_id

FROM employees

GROUP BY department\_id

) AS e

INNER JOIN job\_grades j

ON e.平均工资 BETWEEN j.lowest\_sal AND j.highest\_sal;

![ea982173c0e51919f8f5079065c445d3.png][]

查询工资3W的员工信息SELECT EXISTS(SELECT \* FROM employees WHERE salary=30000);

![89c06ac37973363940b9330cd20f7951.png][]

\#查部门名

SELECT department\_name

FROM departments;

![56c0c740691ac2da3bbfc4ba2056f7b4.png][]=d.department\_id;

![81a46a452db564d3c39bd3d33b9d43d0.png][]\*

FROM employees e

WHERE d.department\_id=e.department\_id

);

![b5rn5z0faqq.jpg][]\#案例2.查询没有女朋友的男神信息

\#IN方法

SELECT \*

FROM boys bo

WHERE bo.id NOT IN(

SELECT boyfriend\_id

FROM beauty be

);

===============

\#EXISTS方法

SELECT \*

FROM boys bo

WHERE NOT EXISTS(

SELECT boyfriend\_id

FROM beauty be

WHERE bo.id=be.boyfriend\_id

);

![0yeopqzvt4y.jpg][]\#引入案例1.:查询部门编号>90或者邮箱包含A的员工信息

SELECT\* FROM employees

WHERE email LIKE "%a%" OR department\_id>90;

联合查询

SELECT\* FROM employees WHERE email LIKE "%a%"

UNION

SELECT \* FROM employees WHERE department\_id>90;

![spcyctputxa.jpg][]

感谢能认真读到这里的伙伴们，MySql查询部分结束，相信屏幕前的你照着我博客里的模板可以完成一些简单的SQL查询语句，SQL既然学了，以后还是要多练习一下，SQL1992与1999语法在主流的关系型数据库都是通用的。后续我会继续进行对MySql的知识进行扩展，感兴趣的同志互相关注一呗！o(^▽^)o

MySql的回顾六:子查询(内查询)相关教程

[7747d6ac93c8eca9dca1d0940ce64b97.png]: /images/20221021/216013602cd24a6cb61fe9415a3c9a40.png
[f58c8ac21ddaed683d2e4934cf60e5f8.png]: /images/20221021/981830e698634d4f87620f393fcdc50b.png
[c20a6b90340090dfc5f8d75d8ff51c20.png]: /images/20221021/f5850eb4a3004bb9a99299be5a78fed8.png
[f182d8849451711557e5bce95028eadd.png]: /images/20221021/79d31a2ff46f48eaa687c231c2ceb530.png
[ae18d37396db0ca9fc2a2a252e9ead15.png]: /images/20221021/e85efbae43e9492a87fd2896877bf7c9.png
[57267594536e68116c895f19694335f9.png]: /images/20221021/93f904ef777647bb9bdc27db9f6cb1f6.png
[60338310920678f776a5345062854e68.png]: /images/20221021/5c93d5039b54440a8a7c375163859613.png
[456d7c3319d820b667202fc1406182e8.png]: /images/20221021/287bf4958fb341b395fe762b684c6ea6.png
[532a02b85fa73fff6c6036a1dabbd6a8.png]: /images/20221021/667dc50cdc3c454e88ffce05c3499e56.png
[45e6fbe290a7c5e19c1b2deaf03840d8.png]: /images/20221021/71ba26a1058c4b0b879577241c5ba22c.png
[e4bfc3fcbcc9da08c1b9911bf7b2d8f5.png]: /images/20221021/ab47783f88ac43d6b58887faf26429f7.png
[6b435d52478e1019416e34f54acc07a5.png]: /images/20221021/434059c21aba4417b077e847886b80d3.png
[c8d650cf8bdb9a4130be7c18abf174cc.png]: /images/20221021/2e476e5270b34d4fa21354bda0c3b91b.png
[3d4bd2dea610b4a453b52f96a138b906.png]: /images/20221021/5cc05670f4e84c75860063c27f25c2ec.png
[97bf3c0b11fe7062eaf065e7fb2dadd1.png]: /images/20221021/37a23ef9f4f54bd8a57c76e86a2d5832.png
[e1a28971baa50bfcfd9d494874995668.png]: /images/20221021/7b114a4d93ba4432bd23ffb607f9b6f1.png
[cfe843b2042b42117c9fc85339a3256b.png]: /images/20221021/ee715f5a417a41409f3ae0145befcf94.png
[ea982173c0e51919f8f5079065c445d3.png]: /images/20221021/5c6c155a653342ef9f644320d6da5ce3.png
[89c06ac37973363940b9330cd20f7951.png]: /images/20221021/ae988145d84649c78b52f047ac6dec38.png
[56c0c740691ac2da3bbfc4ba2056f7b4.png]: /images/20221021/baceafce74f94fe9a45d8b3cd2bd9f38.png
[81a46a452db564d3c39bd3d33b9d43d0.png]: /images/20221021/ebdf12ca38734b54aa4e97d3eba7d3d2.png
[b5rn5z0faqq.jpg]: /images/20221021/cfc9c399e94a4f9faf64067815af3545.png
[0yeopqzvt4y.jpg]: /images/20221021/fbfd37ecc2a84ae18c95ea6547093020.png
[spcyctputxa.jpg]: /images/20221021/774705e06ba54274bb4b3daf00dcabc9.png