Are there nested query statements in mysql?

There are nested query statements in mysql, and the syntax is "SELECT statement WHERE condition (SELECT statement)"; this statement is also called a subquery statement and can be nested after where in an existing query statement. Set a layer of query statements, that is, use the inner query results as the data referenced by the outer query.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

Is there a nested query statement in mysql?

Nested query, also called subquery, is a query method often used in actual work. Subquery is actually a layer of query statements nested after where in the existing query statement, that is, the inner query result is used as the data table referenced by the outer query.

In work, we often encounter 4 subqueries, which contain comparison operators (>, >=,

# 创建学员信息表 
CREATE TABLE stu_info
(
id INT AUTO_INCREMENT PRIMARY KEY, 
iname VARCHAR(20), 
gender CHAR(1), 
department VARCHAR(10), 
age TINYINT, 
province VARCHAR(10), 
email VARCHAR(50), 
mobilephone CHAR(11)
);
# 向学员表中插入数据 
INSERT INTO stu_info(iname,gender,department,age,province,email,mobilephone) VALUES 
('张勇','男','数学系',23,'河南','sfddf123dd@163.com','13323564321'), 
('王兵','男','数学系',25,'江苏','lss1993@163.com','17823774329'), 
('刘伟','男','计算机系',21,'江苏','qawsed112@126.com','13834892240'), 
('张峰','男','管理系',22,'上海','102945328@qq.com','13923654481'), 
('董敏','女','生物系',22,'浙江','82378339@qq.com','13428439022'), 
('徐晓红','女','计算机系',24,'浙江','xixiaohong@gmail.com','13720097528'), 
('赵伊美','女','数学系',21,'江苏','zhaomeimei@163.com','13417723980'), 
('王建国','男','管理系',24,'浙江','9213228402@qq.com','13768329901'), 
('刘清','女','统计系',23,'安徽','lq1128@gmail.com','17823651180'), 
('赵家和','男','计算机系',28,'山东','dcrzdbjh@163.com','13827811311');

# 创建学员成绩表 
CREATE TABLE stu_score( id INT , Excel TINYINT, Tableau TINYINT, MySQL TINYINT ); 
# 向成绩表中插入数据 
INSERT INTO stu_score VALUES 
(1,87,72,88), 
(3,90,66,72), 
(2,90,70,86), 
(4,88,82,76), 
(8,92,67,80), 
(10,88,82,89), 
(5,79,66,60), 
(7,91,78,90), 
(6,82,79,88), 
(9,85,70,85); 

# 1.查询年龄超过所有学员平均年龄的学员信息 
SELECT * FROM stu_info 
WHERE age >= avg(age); 
#需要注意的是Where后面不能使用聚合函数
#应该修改成
SELECT AVG(age) FROM stu_info;
SELECT * FROM stu_info
WHERE age>=23.3
#二合一 
# 1.查询年龄超过所有学员平均年龄的学员信息 
SELECT * FROM stu_info 
WHERE age >= (SELECT AVG(age) FROM stu_info);

# 2.查询年龄不低于所属系平均年龄的学员信息 
SELECT * FROM stu_info AS s1 
WHERE age>= ( SELECT avg(age) FROM stu_info AS s2 
			  WHERE s1.department = s2.department);

When using nested queries containing comparison operators, please note that the subquery following the comparison operator can only return one result.

(2) Nested queries containing ANY or ALL keywords
For nested queries containing comparison operators, the nested part of the query statement can only return one value. If the subquery returns multiple values, you need to use the ANY or ALL keyword. Usually, the ANY / ALL keyword is often used with comparison operators. The following are the results of the combination of 6 comparison operators and the ANY / ALL keyword:

# 1.查询非管理系中比管理系任意一个学员年龄小的学员信息 SELECT * FROM stu_info 
WHERE age <p><img src="/static/imghwm/default1.png" data-src="https://img.php.cn/upload/article/000/000/067/4324f62c03299e4da154e72d7d28ebb8-1.png?x-oss-process=image/resize,p_40" class="lazy" alt="Are there nested query statements in mysql?"><br> The query here The logic is as follows: first query the ages of students in the management department (remove duplicates), and the results obtained are 22 and 24; then query the information of students in the non-management department who are younger than 22 or 24 (that is, non-management students who are younger than 24). Management student information). </p><pre class="brush:php;toolbar:false"># 2.查询非管理系中比管理系所有学员年龄大的学员信息 SELECT * FROM stu_info 
WHERE age > ALL (SELECT DISTINCT age FROM stu_info WHERE department = '管理系') 
      AND department != '管理系';

The query logic here is as follows: first query the ages of students in the management department (removal of duplications), and the results obtained are 22 and 24; then query the age ratio of students in non-management departments Information about students who are both 22 and 24 years old (that is, information about non-management students who are older than 24).

(3) Nested query containing IN keyword
When the query conditions involve certain known enumerable discrete values, we can select the IN keyword to complete data extraction. The IN keyword has two uses:

1. Write enumerable discrete values ​​directly in the value list
2. When the discrete value is based on the filtering results of other tables, you can Use nested queries, that is, write the query statement block of another table in parentheses after the IN keyword.

# 1.查询数学系和计算机系的学员信息 
SELECT * FROM stu_info WHERE department IN('数学系','计算机系'); 
# 2.查询与张勇、刘伟同一个系的学员信息 
SELECT * FROM stu_info 
WHERE department IN (SELECT department FROM stu_info WHERE iname IN('张勇','刘伟')); 
# 3.查询MySQL成绩大于85分的学员信息 
SELECT * FROM stu_info 
WHERE id IN (SELECT id FROM stu_score WHERE MySQL > 85);

It should be noted that when using the nested query of the IN keyword, the nested part can only return information about one field (such as the department field or id field above). If it is returned Syntax errors will occur if there are two or more field information.

(4) Nested query containing the EXISTS keyword
The role of the EXISTS keyword is very similar to the IN keyword. The difference is that the nested query using the EXISTS keyword does not return a specific value. Set, but a logical value that satisfies the condition (that is, True/False). In other words, the function of EXISTS is to "determine whether there is a record that meets certain conditions". If such a record exists, it returns True (True). If such a record does not exist, it returns False (False).

# 查询MySQL成绩大于85分的学员信息 SELECT * FROM stu_info 
WHERE EXISTS(SELECT * FROM stu_score WHERE stu_score.id = stu_info.id AND MySQL > 85);

It should be noted that there are no parameters between the nested statement WHERE using the EXISTS keyword and the EXISTS keyword. This is because EXISTS only requires one parameter, usually adding a subquery to the right of EXISTS. statement. In addition, in the subquery after EXISTS, you can write any field in the table or an asterisk or a constant after SELECT, because the subquery after EXISTS only cares about whether there are records that meet the conditions. The results returned below are all the same:

[Supplement] Regarding the two keywords IN and EXISTS, there are two extended keywords NOT IN and NOT EXISTS

# 查询数学系和计算机系之外的学员信息 
# 方法一 
SELECT * FROM stu_info 
WHERE department NOT IN('数学系','计算机系'); 
#方法二 
SELECT * FROM stu_info 
WHERE NOT EXISTS(SELECT * FROM stu_score WHERE department IN('数学系','计算机系') and stu_score.id = stu_info.id); 
# not exists的逻辑比较复杂，需要大家慢慢领会 
# 主要看not exists括号中的sql语句是否有结果，无结果：才会继续执行where条件；有结果：视为 where条件不成立。 
# 当子查询和主查询有关联条件时，相当于从主查询中去掉子查询的数据。

For the two keywords IN and EXISTS Keywords can be replaced with each other in most cases. The main difference is the efficiency of use. Usually, using EXISTS is more efficient than IN, but it also depends on the actual situation: IN is suitable for large external tables and small internal tables. Situation; EXISTS is suitable for situations where the appearance is small but the interior is large.

Above we only learned about the subquery after the where statement. In addition, the subquery can also be placed after the select statement, from statement, and having statement.

Recommended learning: mysql video tutorial

The above is the detailed content of Are there nested query statements in mysql?. For more information, please follow other related articles on the PHP Chinese website!