mysql query table data

MySQL is a commonly used relational database management system. It has the advantages of open source, high efficiency and reliability, and has been widely used in many application scenarios. Querying table data is a very basic operation in MySQL, and both beginners and professional developers need to be proficient in it. Next, this article will introduce in detail the various ways of querying table data in MySQL.

1. Basic concepts

In MySQL, data is organized into one or more tables. Each table consists of rows and columns, where a row contains a data record and a column is a data field in the table. To query table data, you need to use the SELECT statement. In the SELECT statement, you can use the WHERE clause to select data with specified conditions from the table, you can also use the GROUP BY clause to group the data, and the ORDER BY clause to sort the data.

2. Query all data

Querying all data is the simplest query method. You only need to use SELECT or SELECT column_name1, column_name2, ... FROM table_name statement. Among them, represents all columns in the query table; column_name1, column_name2, ... are the column names to be queried; table_name is the table name to be queried.

Example:

SELECT * FROM student;
SELECT name, age, score FROM student;

3. Query data with specified conditions

If you need to query data that meets specified conditions in the table, you can use the WHERE clause. The WHERE clause is used to limit the number of rows of returned data. Only rows that meet the conditions will be returned. The WHERE clause can be combined with AND or OR operators, and comparison operators such as =, !=, <, >, <=, >=, etc. are supported.

Example:

SELECT * FROM student WHERE age=20;
SELECT * FROM student WHERE score > 90 AND age > 20;

4. Query sorting data

Use the ORDER BY clause to sort the query results according to the specified column. By default, they are arranged in ascending order. You can use the ASC or DESC keyword to specify ascending or descending order. If you want to sort by multiple columns, use commas to separate the sort columns in the ORDER BY clause.

Example:

SELECT * FROM student ORDER BY score DESC;
SELECT * FROM student ORDER BY age ASC, score DESC;

5. Query grouped data

If you want to group the query results, you can use the GROUP BY clause. The GROUP BY clause groups the results according to the specified column and then performs an aggregation operation on each group. For example, you can use the SUM, AVG, COUNT, MIN, or MAX function to calculate summary statistics for each group.

Example:

SELECT sex, COUNT(*) FROM student GROUP BY sex;

6. Query data in another table

In MySQL, you can use the JOIN statement to connect two or more tables to obtain data from one table Get relevant data from. The JOIN statement uses the ON clause to define the columns used to match the two tables.

Example:

SELECT student.name, course.course_name 
FROM student JOIN course 
ON student.course_id = course.course_id;

7. Query different data

Use the DISTINCT keyword to delete duplicate rows from the query results. If there are duplicate rows in the query results, you can Use the SELECT DISTINCT column_name1, column_name2, ... FROM table_name statement.

Example:

SELECT DISTINCT sex FROM student;

8. Use wildcards to query data

In MySQL, you can use wildcards for fuzzy matching. Wildcards can replace any characters. For example, use % to match any character.

Example:

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

The above are the various basic operations of MySQL query table data. Of course, MySQL also has many advanced query operations, which can be used for multi-table joins, subqueries, interval queries, etc. to further improve query efficiency. I hope this article can help readers better master the skills of MySQL query table data and improve their capabilities in MySQL development.

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