How to use SQL statements to perform data filtering and conditional query in MySQL?

How to use SQL statements to perform data filtering and conditional query in MySQL?

MySQL, as a commonly used database management system, is widely used to store and manage large amounts of data. In practical applications, we often need to query data based on specific conditions to meet various needs. This article will introduce how to use SQL statements to perform data filtering and conditional query in MySQL, and give specific code examples.

1. Basic syntax

In MySQL, you can use the SELECT statement to perform data filtering and conditional query. The basic syntax is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Among them, column1, column2, ... are the column names to be queried, which can be specific column names, or wildcards * can be used to query all columns. ; table_name is the name of the table to be queried; condition is the condition of the query.

2. Use WHERE clause for conditional query

1. Simple conditional query

The simplest conditional query is to filter by using comparison operators. For example, if we want to query student records with scores greater than 80 points, we can use the following code:

SELECT * 
FROM students
WHERE score > 80;

Among them, students is the table name, score is the column name, and 80 is the condition.

2. Multi-condition query

In practical applications, it is often necessary to use multiple conditions for query. Multiple conditions can be connected using the logical operators AND and OR. For example, if we want to query student records with math scores greater than 80 points and English scores greater than 70 points, we can use the following code:

SELECT * 
FROM students
WHERE math_score > 80 AND english_score > 70;

Among them, math_score and english_score are column names.

3. Fuzzy query

Fuzzy query refers to querying based on fuzzy conditions, usually using the wildcard character % to represent any character. For example, if we want to query student records with the surname "Zhang", we can use the following code:

SELECT * 
FROM students
WHERE name LIKE '张%';

where name is the column name.

3. Sort using the ORDER BY clause

In data query, it is often necessary to sort the query results. You can use the ORDER BY clause to sort the results in ascending or descending order. For example, if we want to query student records with math scores greater than 80 and sort them in descending order, we can use the following code:

SELECT * 
FROM students
WHERE math_score > 80
ORDER BY math_score DESC;

Among them, DESC means descending order, and ASC means ascending order.

4. Use LIMIT clause for paging query

Paging query means to divide and display the query results according to a certain number of pages. You can use the LIMIT clause to paginate query results. For example, if we want to query the records of the top 10 students, we can use the following code:

SELECT * 
FROM students
ORDER BY score DESC
LIMIT 10;

where score is the column name and DESC means descending order.

5. Other commonly used query statements

In addition to the above basic query statements, MySQL also provides other commonly used query statements, such as aggregation queries, grouping queries, etc. Here is an example to query the average score and maximum score of each subject:

SELECT subject, AVG(score) AS avg_score, MAX(score) AS max_score
FROM students
GROUP BY subject;

Among them, subject and score are column names, AVG represents the average value, MAX represents the maximum value, and AS is the keyword for aliasing.

Summary:

This article introduces the basic method of using SQL statements to perform data filtering and conditional query in MySQL, and gives specific code examples. By flexibly using these query statements, the required data can be efficiently obtained from the database according to actual needs. At the same time, other commonly used query statements are also introduced, hoping to be helpful to readers.

The above is the detailed content of How to use SQL statements to perform data filtering and conditional query in MySQL?. For more information, please follow other related articles on the PHP Chinese website!