Sharing of complex query techniques in MySQL

MySQL is a widely used relational database management system. Complex queries are one of the very common requirements when using MySQL. How to elegantly complete complex query tasks is an essential skill for MySQL users. This article will share some complex query techniques in MySQL to help readers better master MySQL queries.

1. Use the WHERE clause to filter data

The WHERE clause is a very common clause in MySQL used to limit the result set returned. By using the WHERE clause, we can easily filter out unnecessary data and return qualified data. The syntax format of the WHERE clause is as follows:

SELECT column_name(s) FROM table_name WHERE condition;

Among them, condition is the condition used for filtering, which can be the following types of conditions: comparison conditions, range conditions, NULL conditions, logical operators and wildcards, etc.

For example, if we want to query all records in the student table whose age field is greater than or equal to 18 years old, we can write like this:

SELECT * FROM student WHERE age>=18;

Using the WHERE clause can greatly improve the query efficiency and limit the results. Within the scope of our needs. At the same time, we can also use multiple conditions to filter data, for example:

SELECT * FROM student WHERE age>=18 AND gender='male';

2. Use JOIN to query multiple tables

JOIN is one of the most widely used query methods in MySQL. Query multiple tables through JOIN and combine the result sets based on corresponding fields in these tables. The JOIN operation can combine two sets of data according to certain conditions to achieve the purpose of "linking" two tables.

There are three JOIN operations: INNER JOIN, LEFT JOIN and RIGHT JOIN.

1. INNER JOIN

INNER JOIN is the most commonly used JOIN operation. It selects records that meet the conditions from two tables at the same time, also called an equijoin. You can use the ON clause to specify join conditions. For example, if we want to query the name of the student and the name of his class, we can write like this:

SELECT student.name, class.class_name FROM student 
INNER JOIN class ON student.class_id=class.id;

This query result will return the name of the student and the name of his class.

2. LEFT JOIN

The LEFT JOIN operation returns all records from the left table, as well as records matching the right table (if any). If there is no matching record in the right table, NULL is returned. For example, if we want to query the name of the class and the name of the students, we can write like this:

SELECT class.class_name, student.name FROM class 
LEFT JOIN student ON student.class_id=class.id;

This query result will return the names of all classes and the names of students (if there are students), if there are no students, the name field Display NULL.

3. RIGHT JOIN

The RIGHT JOIN operation is similar to the LEFT JOIN operation, except that all records from the right table are returned, and the records that meet the conditions in the left table are returned (if if any). If there is no matching record in the left table, NULL is returned. For example, if we want to query the names of students and the names of their classes, we can write like this:

SELECT student.name, class.class_name FROM student 
RIGHT JOIN class ON student.class_id=class.id;

This query result will return the names of all students and the names of their classes (if any). If there is no matching If the condition is a class, the class name field displays NULL.

3. Use subquery

Subquery refers to nesting another query within one query. In MySQL, subqueries can be used in the WHERE clause, FROM clause and SELECT clause. Subqueries can be used to achieve very complex query requirements, such as querying the maximum or minimum value of a certain field in a table.

For example, if we want to query the record of the student with the third highest score among the students, we can write:

SELECT * FROM student WHERE score=(
  SELECT DISTINCT(score) FROM student GROUP BY score ORDER BY score DESC LIMIT 2,1);

This query result will return the record of the student with the third highest score among the students.

4. Using temporary tables

Temporary tables are a very useful function in MySQL. You can create a temporary table at runtime and save the query results to this temporary table to perform More complex data operations. Temporary tables can be created using the CREATE TEMPORARY TABLE statement or constructed using the SELECT INTO statement.

For example, if we want to query the average score and highest score of a student over the years, we can write like this:

CREATE TEMPORARY TABLE history_score (  
  `stu_id` INT NOT NULL,
  `avg_score` DECIMAL(5,2),
  `max_score` INT,
  PRIMARY KEY (`stu_id`)
);

INSERT INTO history_score(stu_id, avg_score, max_score)
SELECT s.id, AVG(score) AS avg_score, MAX(score) AS max_score FROM score AS sc 
INNER JOIN student AS s ON sc.stu_id = s.id
GROUP BY s.id;

SELECT st.name, sc.avg_score, sc.max_score FROM student AS st 
INNER JOIN history_score AS sc ON st.id = sc.stu_id;

This query will create a temporary table history_score, which will record the average score and the highest score of each student over the years. The average score and the highest score are stored in this temporary table, and then INNER JOIN is used to connect the student's name to the data in this temporary table to obtain the final query result.

Summary

By using the above techniques, we can perform complex data queries in MySQL, improve query efficiency, and make the query results more in line with our needs. Of course, before conducting complex queries, we need to fully understand SQL syntax and the data structure and characteristics of MySQL, so that we can better use these advanced query techniques.

The above is the detailed content of Sharing of complex query techniques in MySQL. For more information, please follow other related articles on the PHP Chinese website!