Single table query

This article describes how to use SQL to query data in a database.

Prepare before starting

You need to confirm that before you start, the following tasks have been completed:

Data preparation

Create a new database named token_demo

CREATE DATABASE token_demo;
USE token_demo;

Create a new table named token_count

CREATE TABLE token_count (
id int,
token varchar(100) DEFAULT '' NOT NULL,
count int DEFAULT 0 NOT NULL,
qty int,
phone char(1) DEFAULT '' NOT NULL,
times datetime DEFAULT '2000-01-01 00:00:00' NOT NULL
);
INSERT INTO token_count VALUES (21,'e45703b64de71482360de8fec94c3ade',3,7800,'n','1999-12-23 17:22:21');
INSERT INTO token_count VALUES (22,'e45703b64de71482360de8fec94c3ade',4,5000,'y','1999-12-23 17:22:21');
INSERT INTO token_count VALUES (18,'346d1cb63c89285b2351f0ca4de40eda',3,13200,'b','1999-12-23 11:58:04');
INSERT INTO token_count VALUES (17,'ca6ddeb689e1b48a04146b1b5b6f936a',4,15000,'b','1999-12-23 11:36:53');
INSERT INTO token_count VALUES (16,'ca6ddeb689e1b48a04146b1b5b6f936a',3,13200,'b','1999-12-23 11:36:53');
INSERT INTO token_count VALUES (26,'a71250b7ed780f6ef3185bfffe027983',5,1500,'b','1999-12-27 09:44:24');
INSERT INTO token_count VALUES (24,'4d75906f3c37ecff478a1eb56637aa09',3,5400,'y','1999-12-23 17:29:12');
INSERT INTO token_count VALUES (25,'4d75906f3c37ecff478a1eb56637aa09',4,6500,'y','1999-12-23 17:29:12');
INSERT INTO token_count VALUES (27,'a71250b7ed780f6ef3185bfffe027983',3,6200,'b','1999-12-27 09:44:24');
INSERT INTO token_count VALUES (28,'a71250b7ed780f6ef3185bfffe027983',3,5400,'y','1999-12-27 09:44:36');
INSERT INTO token_count VALUES (29,'a71250b7ed780f6ef3185bfffe027983',4,17700,'b','1999-12-27 09:45:05');

Simple query

Enter and execute the following SQL statements in a MySQL Client or other client:

mysql> SELECT id, token FROM token_count;

The output result is as follows:

+-------+--------------------------------------------------+
| id | token |
+-------+--------------------------------------------------+
| 21 | e45703b64de71482360de8fec94c3ade |
| 22 | e45703b64de71482360de8fec94c3ade |
| 18 | 346d1cb63c89285b2351f0ca4de40eda |
| 17 | ca6ddeb689e1b48a04146b1b5b6f936a |
| 16 | ca6ddeb689e1b48a04146b1b5b6f936a |
| 26 | a71250b7ed780f6ef3185bfffe027983 |
| 24 | 4d75906f3c37ecff478a1eb56637aa09 |
| 25 | 4d75906f3c37ecff478a1eb56637aa09 |
| 27 | a71250b7ed780f6ef3185bfffe027983 |
| 28 | a71250b7ed780f6ef3185bfffe027983 |
| 29 | a71250b7ed780f6ef3185bfffe027983 |
+-------+--------------------------------------------------+

Filter results

If you need to filter out the results you need from the results obtained by many queries, you can filter the results of the query through the WHERE statement to find the part you want to query.

In SQL, you can use the WHERE clause to add filtering conditions:

mysql> SELECT * FROM token_count WHERE id = 25;

The output result is as follows:

+------------------------------------------------------------------------------------------------------------------------------
| id | token | count | qty | phone | times |
+------------------------------------------------------------------------------------------------------------------------------
| 25 | 4d75906f3c37ecff478a1eb56637aa09 | 4 | 6500 | y | 1999-12-23 17:29:12 |
+------------------------------------------------------------------------------------------------------------------------------

Sort the results

Use the ORDER BY statement to get the query results sorted in the desired way.

For example, the data of the token_count table can be sorted in descending order (DESC) by the times column by the following SQL statement.

mysql> SELECT id, token, times FROM token_count ORDER BY times DESC;

The output result is as follows:

+------------------------------------------------------------------------------------------------------------------------------
| id | token | times |
+------------------------------------------------------------------------------------------------------------------------------
| 29 | a71250b7ed780f6ef3185bfffe027983 | 1999-12-27 09:45:05 |
| 28 | a71250b7ed780f6ef3185bfffe027983 | 1999-12-27 09:44:36 |
| 26 | a71250b7ed780f6ef3185bfffe027983 | 1999-12-27 09:44:24 |
| 27 | a71250b7ed780f6ef3185bfffe027983 | 1999-12-27 09:44:24 |
| 24 | 4d75906f3c37ecff478a1eb56637aa09 | 1999-12-23 17:29:12 |
| 25 | 4d75906f3c37ecff478a1eb56637aa09 | 1999-12-23 17:29:12 |
| 21 | e45703b64de71482360de8fec94c3ade | 1999-12-23 17:22:21 |
| 22 | e45703b64de71482360de8fec94c3ade | 1999-12-23 17:22:21 |
| 18 | 346d1cb63c89285b2351f0ca4de40eda | 1999-12-23 11:58:04 |
| 17 | ca6ddeb689e1b48a04146b1b5b6f936a | 1999-12-23 11:36:53 |
| 16 | ca6ddeb689e1b48a04146b1b5b6f936a | 1999-12-23 11:36:53 |
+------------------------------------------------------------------------------------------------------------------------------

Limit the number of query results

If you want to return only partial results, you can use the LIMIT statement to limit the number of records returned by the query result.

mysql> SELECT id, token, times FROM token_count ORDER BY times DESC LIMIT 5;

The operation results are as follows:

+------------------------------------------------------------------------------------------------------------------------------
| id | token | times |
+------------------------------------------------------------------------------------------------------------------------------
| 29 | a71250b7ed780f6ef3185bfffe027983 | 1999-12-27 09:45:05 |
| 28 | a71250b7ed780f6ef3185bfffe027983 | 1999-12-27 09:44:36 || 26 | a71250b7ed780f6ef3185bfffe027983 | 1999-12-27 09:44:24 |
| 27 | a71250b7ed780f6ef3185bfffe027983 | 1999-12-27 09:44:24 |
| 24 | 4d75906f3c37ecff478a1eb56637aa09 | 1999-12-23 17:29:12 |
+------------------------------------------------------------------------------------------------------------------------------

Aggregation Query

If you want to focus on the overall situation of the data, rather than part of the data, you can use the GROUP BY statement to combine the aggregation function to help you have a better understanding of the overall situation of the data.

For example, you can group the basic information by id, count, and times columns, and then count them separately:

mysql> SELECT id, count, times FROM token_count GROUP BY id, count, times
ORDER BY times DESC
LIMIT 5;

The operation results are as follows:

+------+-----------------------------------------------------------------------------------------------------------------------
| id | count | times |
+------+-----------------------------------------------------------------------------------------------------------------------
| 29 | 4 | 1999-12-27 09:45:05 |
| 28 | 3 | 1999-12-27 09:44:36 |
| 26 | 5 | 1999-12-27 09:44:24 |
| 27 | 3 | 1999-12-27 09:44:24 |
| 24 | 3 | 1999-12-23 17:29:12 |
+------+-----------------------------------------------------------------------------------------------------------------------