mysql子查询

mysql子查询
千次阅读

2021-02-07 14:39:14

一、MySQL子查询的位置当一个查询是另一个查询的子部分是，称之为子查询(查询语句中嵌套含有查询语句)。子查询也是使用频率比较高的一种查询类型。因此，优化

一、MySQL子查询的位置

当一个查询是另一个查询的子部分是，，称之为子查询(查询语句中嵌套含有查询语句)。子查询也是使用频率比较高的一种查询类型。因此，优化子查询，对于整个系统的性能也有直接的影响。

从查询出现在SQL语句的位置来看，它可以出现在目标列中，也可以出现在from子句中，还可以出现在JOIN/ON子句、GROUPBY子句、HAVING子句、ORDERBY子句等位置。下面依次来看这几种形式的子查询，以及对他们进行优化的一些想法。

1、子查询出现在目标列位置

当子查询出现在目标列位置的时候，这种查询只能是标量子查询。也就是说子查询返回的结果只能是一个元组的一个属性。否则，数据库会返回错误信息。

下面为了实验上面这段话，我们来新建一些表，并插入一些数据。create table t1 (k1 int primary key, c1 int);

create table t2 (k2 int primary key, c2 int);

insert into t2 values (1, 10), (2, 2), (3,30);

SQL语句的结果为：mysql> select t1.c1, (select t2.c2 from t2) from t1, t2;

Empty set (0.00sec)

t1表中插入一些数据：mysql> insert into t1 values (1, 1), (2, 2), (3, 3);

Query OK, 3 rows affected (0.00 sec)

a中的查询，我们可以看到执行的结果mysql>select t1.c1, (select t2.c2 from t2) from t1, t2;

ERROR 1242(21000): Subquery returns more than 1 row

t2表，然后再执行a中所做的查询。mysql>delete from t2;

QueryOK, 3 rows affected (0.00 sec)

mysql> select t1.c1, (select t2.c2 from t2) from t1, t2;

Empty set (0.00 sec)

t2表中删除的数据在插入到t2表：mysql>insert into t2 values (1, 10), (2, 2), (3, 30);

Query OK,3 rows affected (0.00 sec)

然后执行如下查询：mysql> select t1.c1, (select t2.c2 from t2 where k2=1) from t1, t2;

+------+-----------------------------------+

| c1 | (select t2.c2 from t2 where k2=1) |

+------+-----------------------------------+

| 1 | 10 |

| 2 | 10 |

| 3 | 10 |

| 1 | 10 |

| 2 | 10 |

| 3 | 10 |

| 1 | 10 |

| 2 | 10 |

| 3 | 10 |

+------+-----------------------------------+

我们可以清楚的看到MySQL为我们返回的结果。

f、我们对e中的查询再换一种写法，可以看到返回的结果为mysql> select t1.c1, (selectt2.c2 from t2 where c2 > 1) from t1, t2;

ERROR 1242 (21000): Subqueryreturns more than 1 row

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：php中文网

更多相关内容

MySQL子查询

千次阅读 2022-05-10 08:52:35

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。 SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出...

一、子查询定义

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。

SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。

二、子查询的基本使用

1、子查询（内查询）在主查询之前一次执行完成。
2、子查询的结果被主查询（外查询）使用。
3、注意事项：

（1）子查询要包含在括号内
（2）将子查询放在比较条件的右侧
（3）单行操作符对应单行子查询，多行操作符对应多行子查询

在这里插入图片描述
三、子查询分类

1、分类方式一

我们按内查询的结果返回一条还是多条记录，将子查询分为 单行子查询、多行子查询。
在这里插入图片描述
2、分类方式二

我们按内查询是否被执行多次，将子查询划分为 相关(或关联)子查询和不相关(或非关联)子查询。

子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做不相关子查询。

如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为相关子查询。

四、单行子查询

1、单行比较操作符
在这里插入图片描述
举例：

# 查询工资大于149号员工工资的员工的信息
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>(
              SELECT salary
              FROM employees
              WHERE employee_id=149);

# 返回job_id与141号员工相同，salary比143号员工多的员工姓名,job_id和工资
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
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
              SELECT MIN(salary)
              FROM employees);

# 查询与141号员工的manager_id和department_id相同的其他员工
# 法一：(正常思路)
SELECT employee_id,manager_id,department_id
FROM employees
WHERE manager_id=(
                  SELECT manager_id
                  FROM employees
                  WHERE employee_id=141)
AND department_id=(
                  SELECT department_id
                  FROM employees
                  WHERE employee_id=141)
AND employee_id<>141;# 注意去除141号员工本身

# 法二：
SELECT employee_id,manager_id,department_id
FROM employees
WHERE (manager_id,department_id)=(
                  SELECT manager_id,department_id
                  FROM employees
                  WHERE employee_id=141)
AND employee_id<>141;# 注意去除141号员工本身

在这里插入图片描述
2、HAVING 中的子查询

#首先执行子查询，再向主查询中的HAVING 子句返回结果。
# 查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id  # 只能想到使用group by...having,因为where中不能使用聚合函数
HAVING MIN(salary)>(
                    SELECT MIN(salary)
                    FROM employees         
                    WHERE department_id=50);

在这里插入图片描述
3、CASE中的子查询

# 在CASE表达式中使用单列子查询

# 显示员工的employee_id,last_name和location。
# 其中，若员工department_id与location_id为1800 的department_id相同，则location为’Canada’，其余则为’USA’。
SELECT employee_id,last_name,
CASE department_id WHEN (SELECT department_id FROM departments WHERE location_id = 1800) THEN 'Canada'
ELSE 'USA' END "location" # CASE...WHEN...THEN...用在第一行

FROM employees;

在这里插入图片描述

4、子查询中的空值问题

SELECT last_name, job_id
FROM   employees
WHERE  job_id =(
                 SELECT job_id
                 FROM   employees
                 WHERE  last_name = 'Haas');# 公司没有Haas这个人，子查询返回空值，最终结果也是空值，但不会报错

在这里插入图片描述

5、非法使用子查询

# 报错： Subquery returns more than 1 row
# 原因：因为子查询的结果是多行数据，而父查询使用单行操作符=，不知道到底等于哪个数据
SELECT employee_id, last_name
FROM   employees
WHERE  salary =(
                SELECT   MIN(salary)
                FROM     employees
                GROUP BY department_id);

五、多行子查询

1、多行比较操作符
在这里插入图片描述
举例：

# 返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary< ANY(
                  SELECT salary
                  FROM employees     
                  WHERE job_id='IT_PROG')
AND job_id<>'IT_PROG'; # 注意去除IT_PROG本身

# 返回其它job_id中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary< ALL(
                  SELECT salary
                  FROM employees     
                  WHERE job_id='IT_PROG')
AND job_id<>'IT_PROG'; # 注意去除IT_PROG本身

# 查询平均工资最低的部门id
# 法一
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
			SELECT MIN(avg_sal) # 将子查询出来的数据作为一个临时表，一定要为所查询的列取别名和临时表取表名
			FROM(
				    SELECT AVG(salary) avg_sal
				    FROM employees
				    GROUP BY department_id
				)   t_dept_avg_sal
			);

# 法二
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)<=ALL(  # 小于等于平均工资即相当于等于最小平均工资
                        SELECT AVG(salary)
                        FROM employees
                        GROUP BY department_id);

在这里插入图片描述
2、空值问题

SELECT last_name
FROM employees
WHERE employee_id NOT IN ( #返回结果为控制是因为子查询中查询出的manager_id有一个null值，导致not in判断有问题
			                  SELECT manager_id
			                  FROM employees
                             #WHERE manager_id IS NOT NULL    
			               );

在这里插入图片描述

六、相关子查询

1、相关子查询执行流程

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询。

相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。
在这里插入图片描述

举例：

# 查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id

# 法一：相关子查询
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary>(
              SELECT AVG(salary)
              FROM employees e2   
              WHERE department_id=e1.department_id); # 注意“本部门”关键字眼

# 法二：在from中声明子查询

# from型的子查询：子查询是作为from的一部分，子查询要用()引起来，并且要给这个子查询取别
#名， 把它当成一张“临时的虚拟的表”来使用。
SELECT e.last_name,e.salary,e.department_id #注意此处要标明department_id所属的表，不然会报错(Column 'department_id' in field list is ambiguous)
FROM employees e,(
                  SELECT department_id,AVG(salary) avg_sal     
                  FROM employees
                  GROUP BY department_id) t_dept_avg_sal
WHERE e.department_id=t_dept_avg_sal.department_id
AND e.salary>t_dept_avg_sal.avg_sal;

# 查询员工的id,salary,按照department_name 排序
SELECT employee_id,salary
FROM employees e
ORDER BY (SELECT department_name # department_name在departmnets表中
          FROM departments d
          WHERE e.department_id=d.department_id);

# 重要结论：在SELECT中，除了GROUP BY 和 LIMIT之外，其他位置都可以声明子查询


# 若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同
# id的员工的employee_id,last_name和其job_id
SELECT e.employee_id,e.last_name,e.job_id
FROM employees e
WHERE 2<=(SELECT COUNT(*)
          FROM job_history
          WHERE employee_id=e.employee_id);

在这里插入图片描述
2、EXISTS 与 NOT EXISTS关键字

（1）关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
（2）如果在子查询中不存在满足条件的行：

条件返回 FALSE ；
继续在子查询中查找。

（3）如果在子查询中存在满足条件的行：

不在子查询中继续查找；
条件返回 TRUE。

（4）NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

举例：

# 查询公司管理者的employee_id，last_name，job_id，department_id信息
# 方式一：自连接
SELECT DISTINCT e1.employee_id,e1.last_name,e1.job_id,e1.department_id # 因为管理者可能管理多个员工，所以需要去重
FROM employees e1 JOIN employees e2 # 相当于求交集
WHERE e1.employee_id=e2.manager_id;

# 方式二：子查询
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN(
                    SELECT DISTINCT manager_id
                    FROM employees);

# 方式三：exists
SELECT e1.employee_id,e1.last_name,e1.job_id,e1.department_id
FROM employees e1
WHERE EXISTS(
              SELECT *
              FROM employees e2
              WHERE e1.employee_id=e2.manager_id
            );


# 查询departments表中，不存在于employees表中的部门的department_id和department_name
# 方式一：右连接
SELECT d.department_id,d.department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL; # employees表中没有的department_id

# 方式二：not exists(与上面一题方式三类似)
SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS (
		SELECT *
		FROM employees e
		WHERE d.department_id= e.department_id
		);

在这里插入图片描述
七、小练习

#1.查询和Zlotkey相同部门的员工姓名和工资

SELECT last_name,salary
FROM employees
WHERE department_id IN (
			SELECT department_id
			FROM employees
			WHERE last_name = 'Zlotkey'
			);

#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。

SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (
		SELECT AVG(salary)
		FROM employees
		);

#3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary

SELECT last_name,job_id,salary
FROM employees
WHERE salary > ALL(
		SELECT salary
		FROM employees
		WHERE job_id = 'SA_MAN'
		);


#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

SELECT employee_id,last_name
FROM employees 
WHERE department_id IN (
			SELECT DISTINCT department_id
			FROM employees
			WHERE last_name LIKE '%u%'
			);


#5.查询在部门的location_id为1700的部门工作的员工的员工号

SELECT employee_id
FROM employees
WHERE department_id IN (
			SELECT department_id
			FROM departments
			WHERE location_id = 1700
			);


#6.查询管理者是King的员工姓名和工资

SELECT last_name,salary,manager_id
FROM employees
WHERE manager_id IN (
			SELECT employee_id
			FROM employees
			WHERE last_name = 'King'
			);



#7.查询工资最低的员工信息: last_name, salary

SELECT last_name,salary
FROM employees
WHERE salary = (
		SELECT MIN(salary)
		FROM employees
		);


#8.查询平均工资最低的部门信息
#方式1：
SELECT *
FROM departments
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary ) = (
						SELECT MIN(avg_sal)
						FROM (
							SELECT AVG(salary) avg_sal
							FROM employees
							GROUP BY department_id
							) t_dept_avg_sal

						)
			);
#方式2：

SELECT *
FROM departments
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary ) <= ALL(
						SELECT AVG(salary)
						FROM employees
						GROUP BY department_id
						)
			);

#方式3： LIMIT

SELECT *
FROM departments
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary ) =(
						SELECT AVG(salary) avg_sal
						FROM employees
						GROUP BY department_id
						ORDER BY avg_sal ASC
						LIMIT 1		
						)
			);

#方式4：

SELECT d.*
FROM departments d,(
		SELECT department_id,AVG(salary) avg_sal
		FROM employees
		GROUP BY department_id
		ORDER BY avg_sal ASC
		LIMIT 0,1
		) t_dept_avg_sal
WHERE d.`department_id` = t_dept_avg_sal.department_id
		
#9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）
#方式1：
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_sal
FROM departments d
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary ) = (
						SELECT MIN(avg_sal)
						FROM (
							SELECT AVG(salary) avg_sal
							FROM employees
							GROUP BY department_id
							) t_dept_avg_sal

						)
			);

#方式2：

SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_sal
FROM departments d
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary ) <= ALL(
						SELECT AVG(salary)
						FROM employees
						GROUP BY department_id
						)
			);

#方式3： LIMIT

SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_sal
FROM departments d
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary ) =(
						SELECT AVG(salary) avg_sal
						FROM employees
						GROUP BY department_id
						ORDER BY avg_sal ASC
						LIMIT 1		
						)
			);

#方式4：

SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_sal
FROM departments d,(
		SELECT department_id,AVG(salary) avg_sal
		FROM employees
		GROUP BY department_id
		ORDER BY avg_sal ASC
		LIMIT 0,1
		) t_dept_avg_sal
WHERE d.`department_id` = t_dept_avg_sal.department_id

#10.查询平均工资最高的 job 信息

#方式1：
SELECT *
FROM jobs
WHERE job_id = (
		SELECT job_id
		FROM employees
		GROUP BY job_id
		HAVING AVG(salary) = (
					SELECT MAX(avg_sal)
					FROM (
						SELECT AVG(salary) avg_sal
						FROM employees
						GROUP BY job_id
						) t_job_avg_sal
					)
		);

#方式2：
SELECT *
FROM jobs
WHERE job_id = (
		SELECT job_id
		FROM employees
		GROUP BY job_id
		HAVING AVG(salary) >= ALL(
				     SELECT AVG(salary) 
				     FROM employees
				     GROUP BY job_id
				     )
		);

#方式3：
SELECT *
FROM jobs
WHERE job_id = (
		SELECT job_id
		FROM employees
		GROUP BY job_id
		HAVING AVG(salary) =(
				     SELECT AVG(salary) avg_sal
				     FROM employees
				     GROUP BY job_id
				     ORDER BY avg_sal DESC
				     LIMIT 0,1
				     )
		);

#方式4：
SELECT j.*
FROM jobs j,(
		SELECT job_id,AVG(salary) avg_sal
		FROM employees
		GROUP BY job_id
		ORDER BY avg_sal DESC
		LIMIT 0,1		
		) t_job_avg_sal
WHERE j.job_id = t_job_avg_sal.job_id
		
#11.查询平均工资高于公司平均工资的部门有哪些?

SELECT department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary) > (
			SELECT AVG(salary)
			FROM employees
			);


#12.查询出公司中所有 manager 的详细信息

#方式1：自连接  xxx worked for yyy
SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_id
FROM employees emp JOIN employees mgr
ON emp.manager_id = mgr.employee_id;

#方式2：子查询

SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (
			SELECT DISTINCT manager_id
			FROM employees
			);

#方式3：使用EXISTS
SELECT employee_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS (
	       SELECT *
	       FROM employees e2
	       WHERE e1.`employee_id` = e2.`manager_id`
	     );

	
#13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?

#方式1：
SELECT MIN(salary)
FROM employees
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING MAX(salary) = (
						SELECT MIN(max_sal)
						FROM (
							SELECT MAX(salary) max_sal
							FROM employees
							GROUP BY department_id
							) t_dept_max_sal
						)
			);

SELECT *
FROM employees
WHERE department_id = 10;

#方式2：
SELECT MIN(salary)
FROM employees
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING MAX(salary) <= ALL (
						SELECT MAX(salary)
						FROM employees
						GROUP BY department_id
						)
			);

#方式3：
SELECT MIN(salary)
FROM employees
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING MAX(salary) = (
						SELECT MAX(salary) max_sal
						FROM employees
						GROUP BY department_id
						ORDER BY max_sal ASC
						LIMIT 0,1
						)
			);
			
#方式4：
SELECT MIN(salary)
FROM employees e,(
		SELECT department_id,MAX(salary) max_sal
		FROM employees
		GROUP BY department_id
		ORDER BY max_sal ASC
		LIMIT 0,1
		) t_dept_max_sal
WHERE e.department_id = t_dept_max_sal.department_id


#14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

#方式1：
SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id = ANY (
			SELECT DISTINCT manager_id
			FROM employees
			WHERE department_id = (
						SELECT department_id
						FROM employees
						GROUP BY department_id
						HAVING AVG(salary) = (
									SELECT MAX(avg_sal)
									FROM (
										SELECT AVG(salary) avg_sal
										FROM employees
										GROUP BY department_id
										) t_dept_avg_sal
									)
						)
			);

#方式2：
SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id = ANY (
			SELECT DISTINCT manager_id
			FROM employees
			WHERE department_id = (
						SELECT department_id
						FROM employees
						GROUP BY department_id
						HAVING AVG(salary) >= ALL (
								SELECT AVG(salary) avg_sal
								FROM employees
								GROUP BY department_id
								)
						)
			);

#方式3：
SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id IN (
			SELECT DISTINCT manager_id
			FROM employees e,(
					SELECT department_id,AVG(salary) avg_sal
					FROM employees
					GROUP BY department_id
					ORDER BY avg_sal DESC
					LIMIT 0,1
					) t_dept_avg_sal
			WHERE e.`department_id` = t_dept_avg_sal.department_id
			);


#15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号

#方式1：
SELECT department_id
FROM departments
WHERE department_id NOT IN (
			SELECT DISTINCT department_id
			FROM employees
			WHERE job_id = 'ST_CLERK'
			);

#方式2：
SELECT department_id
FROM departments d
WHERE NOT EXISTS (
		SELECT *
		FROM employees e
		WHERE d.`department_id` = e.`department_id`
		AND e.`job_id` = 'ST_CLERK'
		);



#16. 选择所有没有管理者的员工的last_name

SELECT last_name
FROM employees emp
WHERE NOT EXISTS (
		SELECT *
		FROM employees mgr
		WHERE emp.`manager_id` = mgr.`employee_id`
		);

#17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'

#方式1：
SELECT employee_id,last_name,hire_date,salary
FROM employees
WHERE manager_id IN (
		SELECT employee_id
		FROM employees
		WHERE last_name = 'De Haan'
		);

#方式2：
SELECT employee_id,last_name,hire_date,salary
FROM employees e1
WHERE EXISTS (
		SELECT *
		FROM employees e2
		WHERE e1.`manager_id` = e2.`employee_id`
		AND e2.last_name = 'De Haan'
		); 


#18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）

#方式1：使用相关子查询
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (
		SELECT AVG(salary)
		FROM employees e2
		WHERE department_id = e1.`department_id`
		);

#方式2：在FROM中声明子查询
SELECT e.last_name,e.salary,e.department_id
FROM employees e,(
		SELECT department_id,AVG(salary) avg_sal
		FROM employees
		GROUP BY department_id) t_dept_avg_sal
WHERE e.department_id = t_dept_avg_sal.department_id
AND e.salary > t_dept_avg_sal.avg_sal


#19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询）

SELECT department_name
FROM departments d
WHERE 5 < (
	   SELECT COUNT(*)
	   FROM employees e
	   WHERE d.department_id = e.`department_id`
	  );


#20.查询每个国家下的部门个数大于 2 的国家编号（相关子查询）

SELECT * FROM locations;

SELECT country_id
FROM locations l
WHERE 2 < (
	   SELECT COUNT(*)
	   FROM departments d
	   WHERE l.`location_id` = d.`location_id`
	 );

/* 
子查询的编写技巧（或步骤）：① 从里往外写  ② 从外往里写
如何选择？
① 如果子查询相对较简单，建议从外往里写。一旦子查询结构较复杂，则建议从里往外写
② 如果是相关子查询的话，通常都是从外往里写。
*/

所用数据库请看文章末尾：SQL语言和基本的select语句

收起

展开全文

mysql sql 子查询

MYSQL 子查询
千次阅读 2021-02-26 09:24:30
十六、子查询 如果在一个查询语句中出现了另一个查询，称为子查询。因此子查询按照查询出现的位置分为外部查询和子查询。 1、查询的结果集分为几种情况（1）返回标量值 select sum(quanlity) from t_orderitem （2...
MYSQL 子查询

创建示例表
表1的创建
表2的创建

子查询概念集合
字查询的优点和限制
独立子查询
相关子查询
运算符EXISTS与NOT EXISTS
ANY、IN和SOME，ALL，<>
参考源头
创建示例表

表1的创建
```
create table 双列表1
(
id int PRIMARY key,
name varchar(30)
)

insert into 双列表1 select 1,'小明';
insert into 双列表1 select 0,'张三';
insert into 双列表1 select 0,'沸羊羊';
insert into 双列表1 select 0,'李阳';
insert into 双列表1 select 0,'灰太狼';
insert into 双列表1 select 0,'唐三';
insert into 双列表1 select 0,'路飞';
```
表2的创建
```
create table 双列表2
(
id int PRIMARY key,
name varchar(30)
)

insert into 双列表2 select 1,'小红';
insert into 双列表2 select 2,'罗老师';
insert into 双列表2 select 3,'美羊羊';
insert into 双列表2 select 5,'红太狼';
insert into 双列表2 select 7,'娜美';
insert into 双列表2 select 9,'英语';
```
子查询概念集合

子查询可以按照两种方式进行分类。若按照期望的数量，可以将子查询分为标量子查询和多值子查询；若按查询对外部查询的依赖，可分为独立子查询（self-contained subquery）和相关子查询（correlated subquery）。标量子查询和多值子查询可以是独立子查询，也可以是相关子查询。

字查询的优点和限制

子查询是指在一个SELECT语句中嵌套另一个SELECT语句。
MYSQL数据库从4.1版本开始支持子查询，并且支持所有SQL标准的子查询，也扩展了一些其独有的子查询标准。下面是一个子查询：
```
SELECT * FROM t1 WHERE colimn1=(SELECT column1 FROM t2);
```
在这个实例中，SELECT * FROM t1 是外部查询(outer query),SELECT column1 FROM t2是子查询。一般来说，称子查询嵌套(nested)于外部查询中。实际也可以将两个或两个以上的子查询进行嵌套。需要注意的是，子查询必须包含括号。

一个子查询会返回一个标量（单一值）、一个行、一个列或一个表（一行或多行及一列或多列），这些子查询被称为标量、列、行和表子查询。可返回一个特定种类结果的子查询经常只能用于特定的语境中。子查询可以包括普通SELECT可以包含的任何关键词或子句，如DISTINCT、GROUP BY、ORDER BY、LIMIT、JOIN、UNION等。

子查询的限制是其外部语句必须是一下语句之一：SELECT、INSERT、UPDATE、DELETE、SET或DO。还有一个限制是，目前用户不能既在一个子查询中修改一个表，又在同一个表中进行选择，虽然这样的操作可用于普通的DELETE、INSERT、REPLACE和UPDATE语句中，但是对子查询不可以同时进行这样的操作。

独立子查询

独立子查询是不依赖外部查询而运作的子查询。与相关子查询相比，独立子查询更便于SQL语句的调试。

标量子查询可以出现在查询中希望产生标量值的任何地方，而多值子查询可以出现在查询中希望产生多值集合的任何地方。只要标量子查询返回的是单个值或NULL值，就说明该子查询是有效的。

标量子查询
```
select *
from 双列表1
where id=(select id from 双列表2 where id=3)
```
多值子查询
```
select *
from 双列表1
where id in(select id from 双列表2)
```
大多数情况下，MYSQL数据库都将独立子查询转换为相关子查询。（可通过explain关键字来查看语句的执行计划）
InnoSQL数据库支持在慢查询日志中记录InnoDB的逻辑IO和物理IO的次数（物理IO就是实际读取磁盘的次数），故开启IO记录，可在慢查询日志中查看

相关子查询

相关子查询（Dependent Subquery 或 Correlated Subquery) 是指引用了外部查询列的子查询，即子查询会对外部查询的每行进行一次计算。但是在优化器内部，这是一个动态的过程，随情况的变化会有所不同，会有不止一种优化方式来处理相关子查询。
```
select *
from 双列表1 a
where exists(select * from 双列表2 b where a.id=b.id )
```
标量子查询
```
select *
from 双列表1 a
where id=(select id from 双列表2 b where a.id=b.id)
```
多值子查询
```
select *
from 双列表2 a
where id in(select id from 双列表1 b where a.id>=b.id)
```
运算符EXISTS与NOT EXISTS

运算符EXISTS和NOT EXISTS 只会测试某个子查询是否返回了行。如果有返回，则EXISTS的结果为真，而NOT EXISTS的结果为假。

在使用EXISTS和NOT EXISTS 时，子查询通常将“ * ”用作输出列的列表。因为这两个运算符是根据子查询是否返回了行来判断真假的，并不关心行所包含的具体内容，所以没必要显式地列出列名。事实上，可以在子查询的列选取列表里编写任何东西，但如果想要确保在子查询成功时返回一个真值，则可以把它写成 SELECT 1，而不要写成 SELECT *
。
相关子查询通常用在EXISTS 和 NOT EXISTS 子查询里，这类子查询主要用于在某个表里查找在另一个表里有匹配行或没有匹配行的表。
相关子查询的工作原理是：把值从外层查询传递到子查询，并检查它们是否满足子查询里指定的那些条件。因此，如果列名会引起歧义（在多个表里有同名列），那么必须使用表名来限定这些列名。
```
select *
from 双列表2 a
where exists(select id from 双列表1 b where a.id>=b.id)
```
ANY、IN和SOME，ALL，<>

ANY关键词必须与一个比较符一起使用。ANY关键词的意思是“对于子查询返回的列中的任一数值，如果比较结果为TRUE，则返回TRUE”。
```
select *
from 双列表1 a
where a.id=ANY(select id from 双列表2 b)
```
词语 IN 是=ANY的别名。因此，这两个语句是一样的。
```
select *
from 双列表1 a
where a.id in (select id from 双列表2 b)
```
词语SOME是ANY的别名。因此这两个语句是一样的。
```
select *
from 双列表1 a
where a.id =SOME (select id from 双列表2 b)
```
词语ALL必须与比较操作符一起使用。ALL的意思是“对于子查询返回的列中的所有值，如果比较结果为TRUE，则返回TRUE”
```
select *
from 双列表2 a
where a.id >ALL(select id from 双列表1 b)
```
下面关于空表中，有些是关于NULL的比较问题。

对于大多数的编程语言而言，逻辑表达式的值只能有两种：TRUE和FALSE。但是在关系数据库中起逻辑表达式作用的并非只有两种，还有一种称为三值逻辑的表达式。这是因为在数据库中对NULL值的比较与大多数编程语言不同。在C语言中，NULL==NULL的比较返回是1，即相等，而在关系数据库中，NULL的比较则完全不是那么回事。

第一个NULL值的比较返回的是NULL而不是0，第二个NULL值的比较返回的仍然是NULL，而不是1。对于比较返回值为NULL的情况，用户应该将其视为UNKNOWN，即表示未知的。因为在某些情况下，NULL返回值可能代表1，即NULL等于NULL，而有时NULL返回值可能代表0。
对于ON过滤条件下的NULL值比较，此时的比较结果为UNKNOWN，却被视为FALSE来进行处理，即两个NULL并不相同。但是在下面两种情况下认为两个NULL值的比较是相等的：
GROUP BY 子句把所有NULL值分到同一组。
ORDER BY 子句把所有NULL值排列在一起。
```
create table 空双列表(
id int PRIMARY key,
name varchar(30)
)
```
上方N/A为不适用(Not applicable)等相似意思，因为表中没有任何数据。
```
select *
from 双列表1
where 1>all(
select id
from 空双列表)
```
1与all(N/A)比较
```
select *
from 双列表1
where 1>(
select id
from 空双列表)
```
1与N/A比较
```
select *
from 双列表1
where 1>all(
select max(id)
from 空双列表)
```
1与all(NULL)比较

<> 是！=的意思
```
select *
from 双列表2 a
where a.id !=ALL(select id from 双列表1 b)
```
```
select *
from 双列表2 a
where a.id <>ALL(select id from 双列表1 b)
```
参考源头

参考书籍
MySQL技术内幕：SQL编程.姜承尧
MySQL技术内幕第5版
MSQL官方参考手册
https://dev.mysql.com/doc/refman/8.0/en/
收起
展开全文
mysql sql 子查询
MySQL子查询慢现象的解决
千次阅读 2021-01-19 00:26:49

当你在用explain工具查看sql语句的执行计划时，若select_type 字段中出现“DEPENDENT SUBQUERY”时，你要注意了，你已经掉入了mysql子查询慢的“坑"。下面我们来看一个具体的例子有这样一条查询语句：SELECT gid,...

当你在用explain工具查看sql语句的执行计划时，若select_type 字段中出现“DEPENDENT SUBQUERY”时，你要注意了，你已经掉入了mysql子查询慢的“坑"。

下面我们来看一个具体的例子

有这样一条查询语句：

SELECT gid,COUNT(id) as count FROM shop_goods g1 WHERE status =0 and gid IN (SELECT gid FROM shop_goods g2 WHERE sid IN (1519066,1466114,1466110,1466102,1466071,1453929))GROUP BY gid;

用explain看了一下，出现关键字“DEPENDENT SUBQUERY”，意味着子查询的第一个select依赖外部的查询;

SUBQUERY：子查询中的第一个SELECT；DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询。

换句话说，就是子查询对 g2 的查询方式依赖于外层 g1 的查询。它意味着两步：

第一步，MySQL 根据 select gid,count(id) from shop_goods where status=0 group by gid; 得到一个大结果集 t1，其数据量为rows=850672 了；

第二步，上面的大结果集 t1 中的每一条记录，都将与子查询 SQL 组成新的查询语句：select gid from shop_goods where sid in (15...blabla..29) and gid=%t1.gid%。等于说，子查询要执行85万次……即使这两步查询都用到了索引，但不慢才怪；

如此一来，子查询的执行效率居然受制于外层查询的记录数，那还不如拆成两个独立查询顺序执行呢。

对于此类语句一般的优化策略是拆成两个查询语句，你不想拆成两个独立查询的话，也可以与临时表join查询，：

你不想拆成两个独立查询的话，也可以与临时表联表查询，如下所示优化后的sql：

SELECT g1.gid,count(1) FROM shop_goods g1,(select gid from shop_goods WHERE sid in (1519066,1466114,1466110,1466102,1466071,1453929)) g2 where g1.status=0 and g1.gid=g2.gid GROUP BY g1.gid;

用explain看了一下，这次又有了一个新的关键字"DERIVED"，意思是用于 from 子句里有子查询的情况。MySQL 会递归执行这些子查询，把结果放在临时表里，然后再做join操作；

DERIVED 的官方含义为：用于 from 子句里有子查询的情况。MySQL 会递归执行这些子查询，把结果放在临时表里。

《高性能MySQL》的第4.4节“MySQL查询优化器的限制(Limitations of the MySQL Query Optimizer)”之第4.4.1小节“关联子查询(Correlated Subqueries)”也有类似的论述：mysql 在处理子查询时，会改写子查询。通常情况下，我们希望由内到外，先完成子查询的结果，然后再用子查询来驱动外查询的表，完成查询。

例如：select * from test where tid in(select fk_tid from sub_test where gid=10)；通常我们会感性地认为该 sql 的执行顺序是：sub_test 表中根据 gid 取得 fk_tid(2,3,4,5,6)记录，然后再到 test 中，带入 tid=2,3,4,5,6，取得查询数据。

但是实际mysql的处理方式为：

select * from test where exists (select * from sub_test where gid=10 and sub_test.fk_tid=test.tid);

mysql 将会扫描 test 中所有数据，每条数据都将会传到子查询中与 sub_test 关联，子查询不会先被执行，所以如果 test 表很大的话，那么性能上将会出现问题。

收起

展开全文
mysql 子查询 group by的使用
千次阅读 2021-03-03 20:20:36

1 group by表示排序，后面查询出来的是每一组的第一条数据，where后面表示提出条件之类的，如果对排序需要有条件筛选，应该在属性名后接having +条件。而不能使用where2 聚合函数sum() count() avg() max() min()...

收起
MySQL子查询的空值问题
千次阅读 2022-01-23 15:40:06

实际开发中遇到的子查询空值问题

收起

mysql
MySQL子查询（嵌套查询）-----详细
千次阅读 2021-11-19 16:01:14

MySQL子查询（嵌套查询）一、子查询 1、子查询允许把一个查询嵌套在另一个查询当中（也就是嵌套查询） 2、子查询可以包含普通select可以包括的任何子句，比如：distinct、 group by、order by、 limit、join和union...

收起

软件测试 mysql sql 测试工程师
MySQL子查询 嵌套查询
万次阅读 多人点赞 2018-09-27 20:18:58

子查询：嵌套在其他查询中的查询。有三张表分别如下： customers: 存储顾客信息 orderitems：只存储订单信息，无客户信息 orders：存储订单号和顾客id 注意：一般在子查询中，程序先运行在嵌套在最内...

收起

MySQL 子查询嵌套查询
mysql 子查询与join性能对比
千次阅读 2021-01-18 20:39:31

有如下的数据表：application_daily 11万条数据application_info400多条数据查询结果数据为19498来看下mysql 子查询的例子。例1，子查询 (0.01s)复制代码代码示例:select sql_no_cache * from application_daily ...

收起
MySQL 子查询——查询最大值
千次阅读 2021-01-18 18:35:56

子查询指将一个查询语句嵌套在另一个查询语句中。子查询可以在 SELECT、UPDATE 和 DELETE 语句中使用，而且可以进行多层嵌套。在实际开发时，子查询经常出现在 WHERE 子句中。子查询在 WHERE 中的语法格式如下：...

收起

mysql查询最大值
Mysql子查询关键字的使用（exists）
千次阅读 2022-03-09 11:11:48

大于all表示指定列中的值必须要大于子查询集中的每一个值，即必须要大于子查询集的最大值；如果是小于即小于子查询中的最小值。 1.3 操作： 2. any（some） 1.1 格式： 1.2 特点： any：与子查询返回的所有值...

收起

数据库开发
在MySQL子查询中选择多个列/字段
千次阅读 2021-02-02 07:33:30

是的，你可以这样做。您需要的诀窍是有两种方法可以从表服务器中获取表。一种方法是......FROM TABLE A另一种方式是FROM (SELECT col as name1, col2 as name 2 FROM ...) B请注意，select子句及其周围的括号是一个...

收起
mysql 子查询的排序问题
千次阅读 2019-09-26 16:07:35

(内层)子查询：子查询还可以包括子查询。子查询分为嵌套子查询和相关子查询。嵌套子查询：执行不依赖与外部的查询。一般可以分为：返回单值的子查询 和返回一个列表的子查询。例如：select s.*,(select country...

收起
sql优化-mysql子查询索引失效问题解决
千次阅读 2020-11-05 17:58:19

解决调整子查询为关联查询： EXPLAIN SELECT COUNT( 1 ) FROM fich_base t1 LEFT JOIN cont_base t2 ON t1.contract_id = t2.id LEFT JOIN orga_base t3 ON t3.id = t1.transfer_organization_id LEFT JOIN user_...

收起

mysql 索引 sql
MySQL子查询，子查询结果当成派生表千万记得加别名！
千次阅读 2020-12-14 14:45:02

子查询 1 what是子查询 一个查询的结果做为另一个查询的条件有查询的嵌套，内部的查询称为子查询 子查询要使用括号 2 子查询的结果是一个值的时候 SELECT 查询字段 FROM 表 WHERE 字段=（子查询）; 案例：查询...

收起
Mysql子查询作为查询字段，且查询结果起别名，在后续查询中引用别名再做条件筛选
千次阅读 2021-07-09 10:36:55

直接上代码 SELECT gu.id, gu.school_name, gu.student_no, gu.student_name, gu.id_card, gu.Job_category, gu.actual_employment_unit, gu.specialty, gu.phone, gu.affirm, gu.labor_contract,gu.diploma, gu....

收起

mysql
mysql子查询中不能使用LIMIT
千次阅读 2019-01-11 05:21:29

mysql子查询中不能使用LIMIT /* 今天遇到个问题，如下： This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' */ 直接po截图和代码我们可以通过LIMIT来删除表中的数据第一...

收起

mysql LIMIT 子查询
mysql子查询
千次阅读 2015-12-08 14:49:09

mysql子查询: where子查询、from子查询、exists子查询

收起

mysql子查询 mysql
mysql子查询表中列不存在，正常执行语句
千次阅读 2020-07-10 00:05:33

实际开发中，经常会编造复合查询语句用于跨表查询，以select子查询、join on语句为主，今天来探讨一个神奇的sql语句。背景这里有两个表test1、test2，现在需要查出所有test1表中id在test2表存在的记录 test1 ...

收起

mysql sql
mysql子查询与join的区别
千次阅读 2019-07-14 12:38:23

子查询理解：①先知道需要查询并将数据拿出来(若from 后的表也是一个子查询结果)。②在去寻找满足判断条件的数据(where,on,having 后的参数等)。而这些查询条件通常是通过子查询获得的。 子查询是一种根据结果找条件...

收起
MYSQL 子查询返回多列显示
千次阅读 2017-10-28 14:35:51

因工作需要，目前研究出一种mysql 技能，与大家分享一下。...所以采用多列拼接子查询，然后根据SUBSTRING_INDEX(SOURCE,SEPARETOR,INDEX+1)。原理：子查询返回拼接列；函数截取还原列可执行脚本： SELECT

收起

mysql 子查询
Mysql 子查询效率优化
万次阅读 2016-10-11 14:39:57

一、MySQL子查询的位置当一个查询是另一个查询的子部分是，称之为子查询（查询语句中嵌套含有查询语句）。子查询也是使用频率比较高的一种查询类型。因此，优化子查询，对于整个系统的性能也有直接的影响。 ...

收起

mysql 优化
Mysql子查询使用limit
千次阅读 2017-11-28 16:06:22

Mysql子查询使用limit关联查询是日常开发中常见的场景，但两张大表join操作消耗的资源操作通常是很大的。

收起

mysql 优化
MySQL子查询和join
千次阅读 2015-03-18 10:25:49

今天做了一个很简单的子查询，表a有4w多条的数据，而b 有6k多条，代码如下： SELECT * FROM a WHERE id IN (SELECT id FROM b) 但是效率却很慢，查询了书和网上的信息得到了如下的结论：可以使用关联查询代替子查询...

收起

mysql 优化子查询