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

转载 于 2021-03-27 10:51:04 发布 · 115 阅读 · 0
文章标签：

#查询累计到当前月亮mysql

本文深入探讨了MySQL中的子查询，包括标量子查询、列子查询和行子查询。通过实例展示了如何利用子查询进行条件过滤，如找出工资高于Abel的员工、特定job_id的最低工资等。同时讲解了IN、ANY/SOME、ALL等多行操作符的使用，并探讨了子查询与主查询的执行顺序。文章还涉及了SELECT子查询和联合查询的应用，帮助读者掌握SQL查询的更多技巧。

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.png1400,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.pngSELECT 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的回顾六:子查询(内查询)相关教程

泰錫哥
关注
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符  | 博主筛选后可见
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值