Common query types and their usage in MySQL

MySQL is a commonly used relational database management system. It supports various query types and can perform different types of query operations according to specific needs. This article will introduce common query types and their usage in MySQL.

1. SELECT query

SELECT query is the most commonly used MySQL query type, which is used to retrieve data in the table. SELECT query statements usually consist of SELECT clause, FROM clause and WHERE clause. Among them, the SELECT clause is used to select the columns to be queried, the FROM clause is used to specify the table to be queried, and the WHERE clause is used to set conditions and filter out data that meets the conditions.

Example:

SELECT * FROM users WHERE age > 18;

The above query statement will return all users who are older than 18 years old in the table named "users" Record.

2. INSERT query

INSERT query is used to insert one or more pieces of data into the table. The INSERT statement usually consists of the INSERT INTO clause, table name and VALUES clause. The VALUES clause contains the data to be inserted.

Example:

INSERT INTO users (id, name, age) VALUES (1, 'Tom', 20);

The above query statement will be in the user named " Insert a record with id 1, name "Tom", and age 20 into the table "users".

3. UPDATE query

UPDATE query is used to update the data in the table. The UPDATE statement usually consists of the UPDATE clause, table name, SET clause and WHERE clause. The SET clause is used to set the column to be updated and its new value, and the WHERE clause is used to set the conditions for updating.

Example:

UPDATE users SET age = 25 WHERE name = 'Tom';

The above query statement will be named "Tom" in the table named "users" "The recorded age was updated to 25 years old.

4. DELETE query

DELETE query is used to delete data in the table. The DELETE statement usually consists of a DELETE FROM clause and a WHERE clause. The WHERE clause is used to set the conditions for deletion.

Example:

DELETE FROM users WHERE age < 18;

The above query statement deletes records whose age is less than 18 years old in the table named "users".

5. GROUP BY clause query

The GROUP BY clause is used to group query results. It is usually used together with aggregate functions (such as SUM, AVG) to perform aggregate calculations on grouped data.

Example:

SELECT department, SUM(salary) FROM employees GROUP BY department;

The above query statement sorts the data in the table named "employees" by department Group into groups and then calculate the total salary for each department.

6. JOIN query

JOIN query is used to perform join queries between multiple tables. The JOIN statement usually consists of a SELECT clause, a FROM clause, a JOIN clause and a WHERE clause. The JOIN clause can include multiple clauses such as LEFT JOIN, RIGHT JOIN and INNER JOIN.

Example:

SELECT a.name, b.phone FROM users a INNER JOIN phones b ON a.id = b.user_id;

The above query statement will be from Select user names and phone numbers from a table named "users" and a table named "phones" and join the two tables together by user ID.

7. UNION query

UNION query is used to merge the results of multiple SELECT statements together and return a data set. The joined SELECT statements must have the same number of columns and data types.

Example:

SELECT id, name FROM users WHERE age > 18 UNION SELECT id, name FROM students WHERE grade > 80;

The above query statement will name The records that meet the conditions in the table named "users" and the table named "students" are merged together and the ID and name are returned.

8. LIKE query

LIKE query is used to match strings. It can use wildcard characters (% and _) in the query to match arbitrary characters and single characters.

Example:

SELECT * FROM users WHERE name LIKE '%Tom%';

The above query statement will return the table named "users" that contains "Tom "Record of string.

9. IN query

IN query is used to specify multiple possible values ​​in a column. It is equivalent to a combination of multiple OR queries.

Example:

SELECT * FROM users WHERE id IN (1, 2, 3);

The above query statement will return the ID in the table named "users" A record of 1, 2 or 3.

Summary:

MySQL supports various query types and can perform different types of query operations according to different needs. This article introduces common MySQL query types and their usage, including SELECT query, INSERT query, UPDATE query, DELETE query, GROUP BY clause query, JOIN query, UNION query, LIKE query and IN query. By understanding these query types, you can better utilize MySQL to manage and manipulate your data.

The above is the detailed content of Common query types and their usage in MySQL. For more information, please follow other related articles on the PHP Chinese website!