Detailed explanation of mysql subquery statement

MySQL is a relational database management system that is widely used in data storage and management. In MySQL, subquery is a common statement form. This article will introduce the subquery statements in MySQL and give some examples to help readers better understand and use subqueries.

1. What is a subquery

A subquery is a query statement contained in another query, usually nested in a SELECT, INSERT, UPDATE or DELETE statement. In MySQL, subqueries can be executed as part of a query and provide search conditions or calculated items. Subquery is a very powerful statement form that can make queries more flexible, precise and efficient. Subqueries can be nested multiple levels, but be careful not to make the query too complex.

2. Subquery syntax

The subquery syntax in MySQL can be divided into two types: expression subquery and SELECT subquery.

1. Expression subquery syntax:

SELECT … FROM … WHERE column_name operator (SELECT column_name FROM table_name WHERE condition);

In this syntax, Operators between constants and field names can be equal to, not equal to, less than, greater than, etc. In addition, the result of the subquery must have only one row and one column, otherwise an error will be returned. At the same time, you can also use subqueries to query the maximum value, minimum value, sum, and average in the result set.

2. SELECT subquery syntax:

SELECT column_name1, column_name2, … FROM table_name WHERE column_name operator (SELECT column_name FROM table_name WHERE condition);

In this syntax, the SELECT subquery is added to the WHERE clause of the outer query. The subquery must return a single value, otherwise an error will be returned. Additionally, a SELECT subquery can perform any type of comparison operation in the WHERE clause.

3. Examples of subqueries

The following examples illustrate the usage of subqueries in MySQL.

1. Query the information of all students whose scores are higher than the class average.

SELECT student_id, student_name, score FROM student WHERE score > (SELECT AVG(score) FROM student);

In this statement, each student is first queried from the student table student number, name, and score, then calculate the average score of the class through a subquery, and compare the average score with each student's score. Eventually, eligible student information will be returned.

2. Query the name of the department whose employee salary is higher than the average salary and the average salary of the department.

SELECT department_name, AVG(salary) as avg_salary FROM employee INNER JOIN department ON employee.department_id = department.department_id WHERE salary > (SELECT AVG(salary) FROM employee) GROUP BY department_name;

In this statement, first join the employees whose salary is higher than the average salary with the department table, and use a subquery to calculate the overall average salary of the company. Finally, group by department name to get the name and average salary of each department.

3. Query how many books the author has written that are more than 100 pages long.

SELECT COUNT(*) FROM books WHERE author_id IN (SELECT author_id FROM author WHERE name = 'John Smith') AND length > 100;

In this statement, first query The ID of the author "John Smith" is then used to filter out books that meet the criteria based on the author ID and book length. Finally, use the COUNT function to return the number of records in the query's result set.

4. Summary

The subquery in MySQL is a very useful statement form that can further improve the accuracy and efficiency of the query. This article introduces the subquery syntax and examples in MySQL. Through learning, you can better understand and master the subquery statements of MySQL, and it also helps readers to better apply the MySQL database.

The above is the detailed content of Detailed explanation of mysql subquery statement. For more information, please follow other related articles on the PHP Chinese website!