mysql query database table

MySQL is a relational database management system that is widely used in fields such as Web applications and large enterprise-level applications. MySQL's query statement is an important part of it, because it allows us to query the required data in the database table more efficiently.

MySQL query statements can be operated through command line terminals, graphical interface tools or programming languages. No matter which method is used, the syntax of the query is similar. The core part usually includes the following components:

1. SELECT statement, used to specify the required data columns;
## The #FROM statement is used to specify the table to be queried; the
2. WHERE statement is used to specify the filtering conditions.

Below we will introduce how to query the database table through MySQL.

STEP 1: Log in to MySQL

First, log in to the MySQL server from the command line terminal. Enter the following command in the terminal:

mysql -u root -p

In the above command, "-u" is used to specify the user name, and "-p" is used to specify the password. "root" is the default username if you haven't changed it before.

After entering the command, the terminal will prompt for a password. The password will not appear on the screen, so please enter the correct password and press Enter.

STEP 2: Select the database

After successful login, you can execute the following command to select the database to be queried:

USE database_name;

Among them, "database_name" is the corresponding database name.

When using database commands, you can view the list of all databases in the current MySQL server through the SHOW DATABASES statement.

SHOW DATABASES;

At this time, you can see a list of all available database names. If the table you want to query exists in one of the databases, select that specific database.

STEP 3: Query table

Query table is one of the most important functions of MySQL. Below we will use the SELECT statement to query the table.

First, use the following statement to list all columns and rows:

SELECT * FROM table_name;

Among them, "table_name" is the name of the table you want to query.

This statement will return all rows and columns in the table. If you have sufficient permissions, you will be able to view all data stored in the table.

To query specific columns in the table, you can use the following statement:

SELECT column1, column2, … FROM table_name;

Among them, "column1", "column2", etc. are the names of the columns you specify. This will cause the query statement to return only the column data involved in the table.

If you need to filter data based on specific conditions, you can query it through the following command:

SELECT column_name(s) FROM table_name WHERE condition;

In the above statement, "column_name(s)" is the name of the column to be queried, " "table_name" is the specified table name, and "condition" is the set filter condition.

For example, the following statement will filter out all data greater than or equal to 18 in the "age" column from the table:

SELECT name, age FROM users WHERE age >= 18;

If you want to filter out data within a period of time, you can use the following statement :

SELECT column_name(s) FROM table_name WHERE date BETWEEN 'start_date' AND 'end_date';

It should be noted that "start_date" and "end_date" here represent the specified start and end dates.

For example, the following command will retrieve all users registered between January 1, 2018 and December 31, 2018:

SELECT name, register_date FROM users WHERE register_date BETWEEN '2018-01-01' AND '2018-12-31';

STEP 4: Sort query results

By default, query results are returned in the order of insertion and update of records. However, this sorting may not be what we want. Therefore, you can use the "ORDER BY" clause to specify the collation.

The following is an example of using "ORDER BY" to equivalently sort the query results by a specific column of data:

SELECT name, age FROM users WHERE age >= 18 ORDER BY age ASC;

This statement will return all persons aged 18 and above in ascending order of age user name and age.

If you want to sort the query results in reverse order, you can use the following statement:

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

STEP 5: Statistical query results

MySQL also supports some statistics for Function to query results. The most commonly used one is the COUNT() function, which is used to count the number of records in the query result set.

The following command will return the number of records in the table "users":

SELECT COUNT(*) FROM users;

In addition, MySQL also supports some other statistical functions, such as SUM(), AVG() and MAX(), etc. .

For example, the following command will calculate the sum of all orders in the table "orders":

SELECT SUM(price) FROM orders;

This will return a number representing the total price of all orders in the table "orders".

Summary

MySQL query statements are an essential part of the development process. It allows us to retrieve the required data from the database so that we can analyze or use them. This article introduces the process of querying data tables in MySQL and provides some examples to help readers better understand how to use MySQL query statements. Whether you are developing web applications or large-scale enterprise applications, mastering these skills will be very useful.

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