MySQL is a commonly used relational database management system. Due to its simplicity and ease of use, it has become one of the databases of choice for many companies and individuals. When using MySQL for data operations, querying is an essential part. This article will introduce how to use MySQL query statements, including query conditions, sorting, aggregation, grouping, etc.
Query conditions
The basic query statement of MySQL is the SELECT statement, and the syntax is as follows:
SELECT column1, column2, ... FROM table_name WHERE condition
Among them, column1, column2,... represent the column names to be queried, and more Column names are separated by commas; table_name represents the name of the table to be queried; condition is the query condition, which can be one or more conditions. Multiple conditions can be connected using logical operators such as AND, OR, NOT, etc.
Query conditions can include comparison operators (=, <, >, <=, >=, !=), fuzzy matching operators (LIKE, NOT LIKE), and range matching operators ( BETWEEN x AND y, NOT BETWEEN x AND y), null value judgment operators (IS NULL, IS NOT NULL), etc.
For example, if we want to query the names and ages of all students older than 18 years old from a table named students, we can write the following query statement:
SELECT name, age FROM students WHERE age > 18;
The meaning of this statement Yes, query the names and ages of all students older than 18 years old from the students table.
Sort
When querying data, we may need to sort the query results according to the value of a certain column. In MySQL, you can use the ORDER BY statement to implement sorting. The syntax is as follows:
SELECT column1, column2, ... FROM table_name WHERE condition ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...
Among them, the ORDER BY clause is used to specify the column names for sorting, and the column names are separated by commas; ASC means ascending order ( Default), DESC means descending order.
For example, if we want to query the salaries of everyone in a table named salaries and sort them from high to low, we can write the following query statement:
SELECT * FROM salaries ORDER BY salary DESC;
This statement The meaning is to query everyone's salary from the salaries table and sort them from high to low.
Aggregation
Aggregation is used to perform statistics and calculations on data in query results. In MySQL, it can be implemented using aggregate functions. Commonly used aggregate functions include SUM, AVG, MAX, MIN, COUNT, etc.
For example, if we want to query the total amount and average amount of all orders in a table named orders, we can write the following query statement:
SELECT SUM(amount), AVG(amount) FROM orders;
The meaning of this statement is, from Query the total amount and average amount of all orders in the orders table.
Grouping
Grouping is used to divide the data in the query results into several groups according to a certain column, and perform statistics and calculations respectively. In MySQL, you can use the GROUP BY clause to implement grouping queries. The syntax is as follows:
SELECT column1, column2, ... FROM table_name WHERE condition GROUP BY column1, column2, ... [HAVING condition];
Among them, the GROUP BY clause is used to specify the column names of the group, and the column names are separated by commas; the HAVING clause Used to further filter groups.
For example, if we want to query the total sales and average sales of all sales departments in a table named sales, we can write the following query statement:
SELECT department, SUM(amount), AVG(amount) FROM sales GROUP BY department;
The meaning of this statement Yes, query the total sales and average sales of all sales departments from the sales table and group them by department.
Summary
In MySQL, query is one of the foundations of operating the database. Mastering the use of query statements is crucial for data analysis and processing. This article introduces query conditions, sorting, aggregation, grouping, etc., hoping to help readers better use MySQL for data operations.
The above is the detailed content of How to write mysql query statement. For more information, please follow other related articles on the PHP Chinese website!