How to query mysql

MySQL is an open source relational database management system that is widely used in websites and enterprise applications. In addition to installing and using MySQL, a must-have skill is querying the database, because querying is very important, being able to inspect, filter and obtain the required data. This article will introduce how to query the MySQL database, including basic queries, advanced queries and connection queries.

1. Basic query

1. SELECT statement

The SELECT statement is used to retrieve data from a database table. It specifies which columns are to be retrieved, and from which table the data is to be retrieved. The syntax is as follows:

SELECT column1, column2, ... FROM table_name;

For example:

SELECT name, age FROM students;

The above statement will Retrieve data for the name and age columns from the students table.

2. WHERE statement

The WHERE statement is used to filter specific rows and only return data that meets the conditions. It is after the SELECT statement, and the syntax is as follows:

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

For example:

SELECT name, age FROM students WHERE age > 18;

The above statement will retrieve the names and ages of students older than 18 from the students table.

3. ORDER BY statement

The ORDER BY statement is used to sort the retrieved data according to the specified column. It can arrange data in ascending or descending order. The syntax is as follows:

SELECT column1, column2, ... FROM table_name WHERE condition ORDER BY column1, column2, ... ASC|DESC;

For example:

SELECT name , age FROM students WHERE age > 18 ORDER BY age DESC;

The above statement will retrieve the names and ages of students older than 18 from the students table, and sort them in descending order by age.

4. LIMIT statement

The LIMIT statement is used to limit the number of rows retrieved. It is usually used with the ORDER BY statement to get the first N rows of records or skip the first M rows of records. The syntax is as follows:

SELECT column1, column2, ... FROM table_name WHERE condition ORDER BY column1, column2, ... ASC|DESC LIMIT N OFFSET M;

For example:

SELECT name, age FROM students WHERE age > 18 ORDER BY age DESC LIMIT 10;

The above statement will retrieve the names and ages of students older than 18 from the students table, and return the first 10 rows of records .

2. Advanced query

1. LIKE statement

The LIKE statement is used to filter data of a specific style and is often used for fuzzy matching. It can be used in the WHERE statement, the syntax is as follows:

SELECT column1, column2, ... FROM table_name WHERE column_name LIKE pattern;

where pattern is a string containing wildcards, you can use % and _ wildcards to match any character and one character, for example:

SELECT name, age FROM students WHERE name LIKE '%zhang%';

The above statement will retrieve the name from the students table Student records containing "zhang".

2. IN statement

The IN statement is used for queries that match multiple values ​​in a column. It can be used in WHERE statements, the syntax is as follows:

SELECT column1, column2, ... FROM table_name WHERE column_name IN (value1, value2, ...);

For example:

SELECT name, age FROM students WHERE age IN (18, 19, 20);

The above statement will retrieve student records whose age is 18, 19 or 20 years old from the students table.

3. NOT statement

The NOT statement is used in the WHERE statement to filter rows that do not meet the conditions. The syntax is as follows:

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

For example:

SELECT name, age FROM students WHERE NOT age = 18;

The above statement will retrieve student records whose age is not 18 years old from the students table.

3. Connection query

Connection query is used to retrieve data from multiple tables and merge the results. Usually INNER JOIN or LEFT JOIN statements are used. The syntax is as follows:

SELECT column1, column2, ... FROM table_name1 JOIN table_name2 ON table_name1.column_name=table_name2.column_name WHERE condition;

where TABLE_NAME1 and TABLE_NAME2 are the names of the two tables to be connected. , the JOIN clause specifies the type to be connected, and the ON condition specifies the columns of the joined tables.

1. INNER JOIN statement

The INNER JOIN statement returns row data common to both tables. It contains rows from both tables with the same column values. The syntax is as follows:

SELECT column1, column2, ... FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name;

For example:

SELECT students.name, courses.name FROM students INNER JOIN courses ON students.course_id=courses.course_id;

The above statement will retrieve the name and course name from the students table and courses table and join them together.

2. LEFT JOIN statement

The LEFT JOIN statement returns all rows in the left table, as well as matching rows in the right table. If there are no matching rows in the right table, the right table column in the result set is NULL. The syntax is as follows:

SELECT column1, column2, ... FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name;

For example:

SELECT students.name, courses.name FROM students LEFT JOIN courses ON students.course_id=courses.course_id;

The above statement will retrieve the name and course name from the students table and include all students, even if they have not registered for any courses.

Summary

The above is how to query the basic, advanced and connection query methods of MySQL. Mastering these methods is an important part of using MySQL and can help database administrators and developers manage more effectively. and retrieve data.

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