mysql table query statement

MySQL is an open source relational database management system commonly used for back-end processing of web applications. When using the MySQL database, table query statements are one of the most frequently used operations. This article will introduce the table query statements in the MySQL database in detail.

1. SELECT statement

The SELECT statement is one of the most common query statements in the MySQL database. Use the SELECT statement to retrieve specified data columns from one or more tables and filter the data based on conditions. The basic syntax of the SELECT statement is as follows:

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

Among them, column1, column2,... represent the names of the data columns to be retrieved, table_name represents the name of the table from which the data is to be retrieved, and condition represents the conditions required to retrieve the data. condition. For example, to retrieve the "first_name" and "last_name" data columns from the "employees" table and filter the records with the last name "Smith", the SELECT statement is as follows:

SELECT first_name, last_name FROM employees WHERE last_name = 'Smith';

2. JOIN statement

The JOIN statement is used to join (correlate) data from multiple tables to obtain a more complete picture. JOIN statements include INNER JOIN, LEFT JOIN, RIGHT JOIN and other different types. For example, to retrieve employees' names and department names from the "employees" and "departments" tables, the JOIN statement can be written in the following form:

SELECT employees.first_name, employees.last_name, departments.department_name 
FROM employees 
INNER JOIN departments 
ON employees.department_id = departments.department_id;

In this statement, INNER JOIN means using internal connections, and the ON sub- Sentence represents the connection condition. The INNER JOIN operation returns only rows of data where there is a match between the two tables.

3. GROUP BY statement

The GROUP BY statement is used to group data rows and perform aggregate calculations (such as COUNT, SUM, AVG, MAX, MIN, etc.). For example, to retrieve the number of employees in each department from the "employees" table, the GROUP BY statement is as follows:

SELECT department_id, COUNT(*) AS num_employees 
FROM employees 
GROUP BY department_id;

In this statement, the COUNT(*) function is used to calculate the number of data rows, and the AS keyword is used To specify an alias for the calculation result.

4. ORDER BY statement

The ORDER BY statement is used to sort the retrieved data rows by specified columns, and can be arranged in ascending or descending order. For example, to sort from the "employees" table in descending order by salary, the ORDER BY statement is as follows:

SELECT * FROM employees ORDER BY salary DESC;

In this statement, DESC (descending order) means sorting from high to low by salary.

5. LIMIT statement

The LIMIT statement is used to limit the number of data rows to be retrieved. For example, to retrieve the top 10 employees with the last name "Smith" from the "employees" table, the LIMIT statement is as follows:

SELECT * FROM employees WHERE last_name = 'Smith' LIMIT 10;

6. UNION statement

The UNION statement is used to combine multiple SELECT statements The results are combined into one result set. For example, to retrieve records named "John" from the "employees" table and "customers" table, the UNION statement is as follows:

SELECT first_name, last_name FROM employees WHERE first_name = 'John'
UNION
SELECT contact_firstname, contact_lastname FROM customers WHERE contact_firstname = 'John';

In this statement, UNION combines the results of the two SELECT statements and returns a Result set containing all records for "John".

Summary

Table query statements are one of the most common operations in the MySQL database, mainly including SELECT, JOIN, GROUP BY, ORDER BY, LIMIT and UNION statements. Proficient use of these statements can improve the efficiency of data retrieval and enable more complex data analysis and processing operations.

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