Mysql5_子查询

最新推荐文章于 2022-10-11 21:01:55 发布

ginger_mr

最新推荐文章于 2022-10-11 21:01:55 发布

阅读量319

点赞数

分类专栏： Mysql_new

本文链接：https://blog.csdn.net/qq_40332952/article/details/114240194

版权

Mysql_new 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

文章目录

子查询

子查询

说明：当一个查询语句中又嵌套了另一个完整的select语句，则被嵌套的select语句称为子查询或内查询
外面的select语句称为主查询或外查询。

分类：
按子查询出现的位置进行分类：
	1、select后面
		要求：子查询的结果为单行单列（标量子查询）
	2、from后面
	    将子查询结果充当一张表，要求必须起别名
		要求：子查询的结果可以为多行多列
	3、where或having后面 ★		
		标量子查询 √
		列子查询 √
		行子查询
		表子查询
	4、exists后面
		要求：子查询结果必须为单列（相关子查询）
		语法：exists(完整的查询语句)，结果：1或0。

按结果集的行列数不同：
	标量子查询（结果集只有一行一列）
	列子查询（结果集只有一列多行）
	行子查询（结果集有一行多列）
	表子查询（结果集一般为多行多列）
	
特点：
	1、子查询放在条件中，要求必须放在条件的右侧。
	2、子查询一般放在小括号中。
	3、子查询的执行优先于主查询。
	4、单行子查询对应了 单行操作符：> < >= <= = <>。
	   多行子查询对应了 多行操作符：any/some  all in。   

子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果，相关子查询除外。

1.放在where或having后面

1.标量子查询

# 案例1：谁的工资比 Abel 高?
# ①查询Abel的工资。
mysql> SELECT salary
FROM employees
WHERE last_name  = 'Abel';
# ②查询salary>①的员工信息。
mysql> SELECT last_name, salary 
FROM employees WHERE salary > (
	SELECT salary 
	FROM employees 
	WHERE last_name = 'Abel'
);
+-----------+----------+
| last_name | salary   |
+-----------+----------+
| K_ing     | 24000.00 |
| Kochhar   | 17000.00 |
| De Haan   | 17000.00 |
| Greenberg | 12000.00 |
| Russell   | 14000.00 |
| Partners  | 13500.00 |
| Errazuriz | 12000.00 |
| Ozer      | 11500.00 |
| Hartstein | 13000.00 |
| Higgins   | 12000.00 |
+-----------+----------+
10 rows in set (0.03 sec)

# 案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资。
# ①查询141号员工的job_id。
SELECT job_id
FROM employees
WHERE employee_id = 141;
# ②查询143号员工的salary。
SELECT salary
FROM employees
WHERE employee_id = 143;
# ③查询job_id=① and salary>②的信息。
mysql> SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
	SELECT job_id
	FROM employees
	WHERE employee_id = 141
) AND salary > (
	SELECT salary
	FROM employees
	WHERE employee_id = 143
);
+-------------+----------+---------+
| last_name   | job_id   | salary  |
+-------------+----------+---------+
| Nayer       | ST_CLERK | 3200.00 |
| Mikkilineni | ST_CLERK | 2700.00 |
| Bissot      | ST_CLERK | 3300.00 |
| Atkinson    | ST_CLERK | 2800.00 |
| Mallin      | ST_CLERK | 3300.00 |
| Rogers      | ST_CLERK | 2900.00 |
| Ladwig      | ST_CLERK | 3600.00 |
| Stiles      | ST_CLERK | 3200.00 |
| Seo         | ST_CLERK | 2700.00 |
| Rajs        | ST_CLERK | 3500.00 |
| Davies      | ST_CLERK | 3100.00 |
+-------------+----------+---------+
11 rows in set (0.04 sec)

# 案例3：返回公司工资最少的员工的last_name,job_id和salary。
# ①查询最低工资。
SELECT MIN(salary)
FROM employees;
# ②查询salary=①的员工的last_name,job_id和salary。
mysql> SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees
);
+-----------+----------+---------+
| last_name | job_id   | salary  |
+-----------+----------+---------+
| Olson     | ST_CLERK | 2100.00 |
+-----------+----------+---------+
1 row in set (0.03 sec)

# 案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资。
# ①查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id = 50;
# ②查询各部门的最低工资，筛选看哪个部门的最低工资>①。
mysql> SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT MIN(salary)
	FROM employees
	WHERE department_id = 50
);
+-------------+---------------+
| MIN(salary) | department_id |
+-------------+---------------+
|     7000.00 | NULL          |
|     4400.00 |            10 |
|     6000.00 |            20 |
|     2500.00 |            30 |
|     6500.00 |            40 |
|     4200.00 |            60 |
|    10000.00 |            70 |
|     6100.00 |            80 |
|    17000.00 |            90 |
|     6900.00 |           100 |
|     8300.00 |           110 |
+-------------+---------------+
11 rows in set (0.04 sec)

# 非法使用标量子查询 （使用单行操作符，子查询却返回多行记录。）
mysql> SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT  salary
	FROM employees
	WHERE department_id = 80  
);
Subquery returns more than 1 row

2.列子查询

in:判断某字段是否在指定列表内  
x in(10,30,50)

any/some:判断某字段的值是否满足其中任意一个
x>any(10,30,50)
x>min()

x=any(10,30,50)：我试过any里面放一些常量，sql语法报错。any里面必须是一个子查询才可以，in 里面放常量和子查询都可以。（all 同理）
x in(10,30,50)

all:判断某字段的值是否满足里面所有的
x>all(10,30,50)
x>max()

# 案例1：返回location_id是1400或1700的部门中的所有员工姓名。
# ①查询location_id是1400或1700的部门。
SELECT department_id
FROM departments
WHERE location_id IN(1400,1700)
# ②查询department_id = ①的姓名。
mysql> SELECT last_name
FROM employees
WHERE department_id IN(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);
+------------+
| last_name  |
+------------+
| K_ing      |
| Kochhar    |
| De Haan    |
| Hunold     |
| Ernst      |
| Austin     |
| Pataballa  |
| Lorentz    |
| Greenberg  |
| Faviet     |
| Chen       |
| Sciarra    |
| Urman      |
| Popp       |
| Raphaely   |
| Khoo       |
| Baida      |
| Tobias     |
| Himuro     |
| Colmenares |
| Whalen     |
| Higgins    |
| Gietz      |
+------------+
23 rows in set (0.08 sec)

# 案例2：返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary。
# ①查询job_id为‘IT_PROG’部门的工资。
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG';
# ②查询其他部门的工资<任意一个①的结果。
mysql> SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ANY(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
);
+-------------+-------------+------------+---------+
| employee_id | last_name   | job_id     | salary  |
+-------------+-------------+------------+---------+
|         104 | Ernst       | IT_PROG    | 6000.00 |
|         105 | Austin      | IT_PROG    | 4800.00 |
|         106 | Pataballa   | IT_PROG    | 4800.00 |
|         107 | Lorentz     | IT_PROG    | 4200.00 |
|         110 | Chen        | FI_ACCOUNT | 8200.00 |
|         111 | Sciarra     | FI_ACCOUNT | 7700.00 |
|         112 | Urman       | FI_ACCOUNT | 7800.00 |
|         113 | Popp        | FI_ACCOUNT | 6900.00 |
|         115 | Khoo        | PU_CLERK   | 3100.00 |
|         116 | Baida       | PU_CLERK   | 2900.00 |

# 等价于
mysql> SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<(
	SELECT MAX(salary)
	FROM employees
	WHERE job_id = 'IT_PROG'
);
+-------------+-------------+------------+---------+
| employee_id | last_name   | job_id     | salary  |
+-------------+-------------+------------+---------+
|         104 | Ernst       | IT_PROG    | 6000.00 |
|         105 | Austin      | IT_PROG    | 4800.00 |
|         106 | Pataballa   | IT_PROG    | 4800.00 |
|         107 | Lorentz     | IT_PROG    | 4200.00 |
|         110 | Chen        | FI_ACCOUNT | 8200.00 |
|         111 | Sciarra     | FI_ACCOUNT | 7700.00 |
|         112 | Urman       | FI_ACCOUNT | 7800.00 |
|         113 | Popp        | FI_ACCOUNT | 6900.00 |
|         115 | Khoo        | PU_CLERK   | 3100.00 |
|         116 | Baida       | PU_CLERK   | 2900.00 |

# 演示：any里面不可以放常量只能放子查询，in常量和子查询则都可以。
# 测试放常量
mysql> SELECT DISTINCT department_id
       FROM departments
       WHERE location_id = any(1400,1500);
       1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1400,1500)' at line 3
mysql> SELECT DISTINCT department_id
FROM departments
WHERE location_id in (1400,1500);
+---------------+
| department_id |
+---------------+
|            60 |
|            50 |
+---------------+
2 rows in set (0.02 sec)
# 测试放子查询
mysql> SELECT last_name
FROM employees
 WHERE department_id = any(
  SELECT DISTINCT department_id
  FROM departments where location_id = 1400
);
+-----------+
| last_name |
+-----------+
| Hunold    |
| Ernst     |
| Austin    |
| Pataballa |
| Lorentz   |
+-----------+
5 rows in set (0.03 sec)
mysql> SELECT last_name
	   FROM employees
	   WHERE department_id in (
		   SELECT DISTINCT department_id
	       FROM departments where location_id = 1400
	   );
+-----------+
| last_name |
+-----------+
| Hunold    |
| Ernst     |
| Austin    |
| Pataballa |
| Lorentz   |
+-----------+
5 rows in set (0.02 sec)

# 案例3：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工 的员工号、姓名、job_id 以及salary。
# ①查询job_id为‘IT_PROG’部门的工资。
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG';
# ②查询其他部门的工资<所有①的结果。
mysql> SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ALL(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
);
+-------------+-------------+----------+---------+
| employee_id | last_name   | job_id   | salary  |
+-------------+-------------+----------+---------+
|         115 | Khoo        | PU_CLERK | 3100.00 |
|         116 | Baida       | PU_CLERK | 2900.00 |
|         117 | Tobias      | PU_CLERK | 2800.00 |
|         118 | Himuro      | PU_CLERK | 2600.00 |
|         119 | Colmenares  | PU_CLERK | 2500.00 |
|         125 | Nayer       | ST_CLERK | 3200.00 |
|         126 | Mikkilineni | ST_CLERK | 2700.00 |
|         127 | Landry      | ST_CLERK | 2400.00 |
|         128 | Markle      | ST_CLERK | 2200.00 |
|         129 | Bissot      | ST_CLERK | 3300.00 |

# 等价于
mysql> SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<(
	SELECT MIN(salary)
	FROM employees
	WHERE job_id = 'IT_PROG'
);
+-------------+-------------+----------+---------+
| employee_id | last_name   | job_id   | salary  |
+-------------+-------------+----------+---------+
|         115 | Khoo        | PU_CLERK | 3100.00 |
|         116 | Baida       | PU_CLERK | 2900.00 |
|         117 | Tobias      | PU_CLERK | 2800.00 |
|         118 | Himuro      | PU_CLERK | 2600.00 |
|         119 | Colmenares  | PU_CLERK | 2500.00 |
|         125 | Nayer       | ST_CLERK | 3200.00 |
|         126 | Mikkilineni | ST_CLERK | 2700.00 |
|         127 | Landry      | ST_CLERK | 2400.00 |
|         128 | Markle      | ST_CLERK | 2200.00 |
|         129 | Bissot      | ST_CLERK | 3300.00 |

3.行子查询

# 案例：查询员工编号最小并且工资最高的员工信息
# ①查询最小的员工编号
SELECT MIN(employee_id)
FROM employees

# ②查询最高工资
SELECT MAX(salary)
FROM employees

# ③查询员工信息最小编号=①最高工资=②
mysql> SELECT *
FROM employees
WHERE employee_id=(
	SELECT MIN(employee_id)
	FROM employees
)AND salary=(
	SELECT MAX(salary)
	FROM employees
);
+-------------+------------+-----------+-------+--------------+---------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name | last_name | email | phone_number | job_id  | salary   | commission_pct | manager_id | department_id | hiredate            |
+-------------+------------+-----------+-------+--------------+---------+----------+----------------+------------+---------------+---------------------+
|         100 | Steven     | K_ing     | SKING | 515.123.4567 | AD_PRES | 24000.00 | NULL           | NULL       |            90 | 1992-04-03 00:00:00 |
+-------------+------------+-----------+-------+--------------+---------+----------+----------------+------------+---------------+---------------------+
1 row in set (0.04 sec)

# 其实上面的子查询的结果还是标量子查询，如果想用行子查询应该是下面这种写法。
mysql> SELECT * 
FROM employees
WHERE (employee_id,salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
);
+-------------+------------+-----------+-------+--------------+---------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name | last_name | email | phone_number | job_id  | salary   | commission_pct | manager_id | department_id | hiredate            |
+-------------+------------+-----------+-------+--------------+---------+----------+----------------+------------+---------------+---------------------+
|         100 | Steven     | K_ing     | SKING | 515.123.4567 | AD_PRES | 24000.00 | NULL           | NULL       |            90 | 1992-04-03 00:00:00 |
+-------------+------------+-----------+-------+--------------+---------+----------+----------------+------------+---------------+---------------------+
1 row in set (0.05 sec)

4.表子查询

# 放在where或having后面的表子查询，其实语法是支持的，我就演示下就可以了，使用较少。
mysql> SELECT * 
FROM employees
WHERE (employee_id,salary) in (
	SELECT employee_id,salary
	FROM employees where department_id = 20 # 这里的子查询返回的是多行多列，也就是表子查询。
);
+-------------+------------+-----------+----------+--------------+--------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name | last_name | email    | phone_number | job_id | salary   | commission_pct | manager_id | department_id | hiredate            |
+-------------+------------+-----------+----------+--------------+--------+----------+----------------+------------+---------------+---------------------+
|         201 | Michael    | Hartstein | MHARTSTE | 515.123.5555 | MK_MAN | 13000.00 | NULL           |        100 |            20 | 2016-03-03 00:00:00 |
|         202 | Pat        | Fay       | PFAY     | 603.123.6666 | MK_REP |  6000.00 | NULL           |        201 |            20 | 2016-03-03 00:00:00 |
+-------------+------------+-----------+----------+--------------+--------+----------+----------------+------------+---------------+---------------------+
2 rows in set (0.05 sec)

2.放在select后面

1.标量子查询

# 案例：查询每个部门的员工个数
mysql> SELECT d.*,(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id = d.`department_id`
 ) 个数
 FROM departments d;
+---------------+-----------------+------------+-------------+------+
| department_id | department_name | manager_id | location_id | 个数 |
+---------------+-----------------+------------+-------------+------+
|            10 | Adm             |        200 |        1700 |    1 |
|            20 | Mar             |        201 |        1800 |    2 |
|            30 | Pur             |        114 |        1700 |    6 |
|            40 | Hum             |        203 |        2400 |    1 |
|            50 | Shi             |        121 |        1500 |   45 |
|            60 | IT              |        103 |        1400 |    5 |
|            70 | Pub             |        204 |        2700 |    1 |
|            80 | Sal             |        145 |        2500 |   34 |
|            90 | Exe             |        100 |        1700 |    3 |
|           100 | Fin             |        108 |        1700 |    6 |

# 案例2：查询员工号=102的部门名
mysql> SELECT (
	SELECT department_name
	FROM departments d
	INNER JOIN employees e
	ON d.department_id=e.department_id
	WHERE e.employee_id=102
) 部门名;
+--------+
| 部门名 |
+--------+
| Exe    |
+--------+
1 row in set (0.03 sec)

3.放在from后面

# 将子查询结果充当一张表，要求必须起别名。

# 案例：查询每个部门的平均工资的工资级别
#①查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

# ②连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal
mysql> SELECT  ag_dep.*,g.`grade_level`
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
+--------------+---------------+-------------+
| ag           | department_id | grade_level |
+--------------+---------------+-------------+
|  7000.000000 | NULL          | C           |
|  4400.000000 |            10 | B           |
|  9500.000000 |            20 | C           |
|  4150.000000 |            30 | B           |
|  6500.000000 |            40 | C           |
|  3475.555556 |            50 | B           |
|  5760.000000 |            60 | B           |
| 10000.000000 |            70 | D           |
|  8955.882353 |            80 | C           |
| 19333.333333 |            90 | E           |
|  8600.000000 |           100 | C           |
| 10150.000000 |           110 | D           |
+--------------+---------------+-------------+
12 rows in set (0.03 sec)

4.放在exists后面

# 案例1 ：查询有无名字叫“张三丰”的员工信息
mysql> SELECT EXISTS(
	SELECT * 
	FROM employees
	WHERE last_name = '张三丰'
) 有无张三丰;
+------------+
| 有无张三丰 |
+------------+
|          0 |  表视无
+------------+
1 row in set (0.03 sec)

mysql> SELECT EXISTS(
	SELECT * 
	FROM employees
	WHERE last_name = 'Abel'

) 有无Abel;
+----------+
| 有无Abel |
+----------+
|        1 |  表示有
+----------+
1 row in set (0.04 sec)

# 案例2：查询有员工的部门名
mysql> #in
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
	SELECT department_id
	FROM employees
);
+-----------------+
| department_name |
+-----------------+
| Adm             |
| Mar             |
| Pur             |
| Hum             |
| Shi             |
| IT              |
| Pub             |
| Sal             |
| Exe             |
| Fin             |
| Acc             |
+-----------------+
11 rows in set (0.03 sec)

mysql> # exists
SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.`department_id`=e.`department_id` # 如果 exists 返回1，就把该条记录返回。
);
+-----------------+
| department_name |
+-----------------+
| Adm             |
| Mar             |
| Pur             |
| Hum             |
| Shi             |
| IT              |
| Pub             |
| Sal             |
| Exe             |
| Fin             |
| Acc             |
+-----------------+
11 rows in set (0.04 sec)

# 案例3：查询没有女朋友的男神信息
mysql># not in 
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
	SELECT DISTINCT boyfriend_id
	# 因为该子查询中有null值这个这个结果永远不会成立，也就是查询不出来值，这个null必须要过滤掉。这个原因在我的Oracle-查询博客里面有详细介绍。
	FROM beauty where boyfriend_id is not null 
);
+----+---------+--------+
| id | boyName | userCP |
+----+---------+--------+
|  1 | 张无忌  |    100 |
+----+---------+--------+
1 row in set (0.02 sec)

mysql># not exists 
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
	SELECT boyfriend_id
	FROM beauty b WHERE bo.`id`=b.`boyfriend_id`
);
+----+---------+--------+
| id | boyName | userCP |
+----+---------+--------+
|  1 | 张无忌  |    100 |
+----+---------+--------+
1 row in set (0.02 sec)

ginger_mr

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql5_子查询

文章目录子查询1.放在where或having后面1.单行子查询2.多行子查询子查询说明：当一个查询语句中又嵌套了另一个完整的select语句，则被嵌套的select语句称为子查询或内查询外面的select语句称为主查询或外查询。分类：按子查询出现的位置进行分类： 1、select后面要求：子查询的结果为单行单列（标量子查询） 2、from后面要求：子查询的结果可以为多行多列 3、where或having后面 ★ 要求：子查询的结果必须为单列单行子查询多行子
复制链接

扫一扫

专栏目录