Query Data from a Single Table

This document describes how to use SQL to query data from a single table in a database.

Before you start

Make sure you have already Deployed standalone MatrixOne.

Preparation

Create a database named token_demo firstly:

  1. CREATE DATABASE token_demo;
  2. USE token_demo;

Create a table named token_count to prepare for querying:

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

Simple query

Execute the following SQL statement in a MySQL client:

  1. mysql> SELECT id, token FROM token_count;

Result is as below:

  1. +------+----------------------------------+
  2. | id   | token                            |
  3. +------+----------------------------------+
  4. |   21 | e45703b64de71482360de8fec94c3ade |
  5. |   22 | e45703b64de71482360de8fec94c3ade |
  6. |   18 | 346d1cb63c89285b2351f0ca4de40eda |
  7. |   17 | ca6ddeb689e1b48a04146b1b5b6f936a |
  8. |   16 | ca6ddeb689e1b48a04146b1b5b6f936a |
  9. |   26 | a71250b7ed780f6ef3185bfffe027983 |
  10. |   24 | 4d75906f3c37ecff478a1eb56637aa09 |
  11. |   25 | 4d75906f3c37ecff478a1eb56637aa09 |
  12. |   27 | a71250b7ed780f6ef3185bfffe027983 |
  13. |   28 | a71250b7ed780f6ef3185bfffe027983 |
  14. |   29 | a71250b7ed780f6ef3185bfffe027983 |
  15. +------+----------------------------------+

Filter results

To filter query results, you can use the WHERE statement.

  1. mysql> SELECT * FROM token_count WHERE id = 25;

Result is as below:

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

Sort results

To sort query results, you can use the ORDER BY statement.

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

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

Result is as below:

  1. +------+----------------------------------+---------------------+
  2. | id   | token                            | times               |
  3. +------+----------------------------------+---------------------+
  4. |   29 | a71250b7ed780f6ef3185bfffe027983 | 1999-12-27 09:45:05 |
  5. |   28 | a71250b7ed780f6ef3185bfffe027983 | 1999-12-27 09:44:36 |
  6. |   26 | a71250b7ed780f6ef3185bfffe027983 | 1999-12-27 09:44:24 |
  7. |   27 | a71250b7ed780f6ef3185bfffe027983 | 1999-12-27 09:44:24 |
  8. |   24 | 4d75906f3c37ecff478a1eb56637aa09 | 1999-12-23 17:29:12 |
  9. |   25 | 4d75906f3c37ecff478a1eb56637aa09 | 1999-12-23 17:29:12 |
  10. |   21 | e45703b64de71482360de8fec94c3ade | 1999-12-23 17:22:21 |
  11. |   22 | e45703b64de71482360de8fec94c3ade | 1999-12-23 17:22:21 |
  12. |   18 | 346d1cb63c89285b2351f0ca4de40eda | 1999-12-23 11:58:04 |
  13. |   17 | ca6ddeb689e1b48a04146b1b5b6f936a | 1999-12-23 11:36:53 |
  14. |   16 | ca6ddeb689e1b48a04146b1b5b6f936a | 1999-12-23 11:36:53 |
  15. +------+----------------------------------+---------------------+

Limit the number of query results

To limit the number of query results, you can use the LIMIT statement.

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

Result is as below:

  1. +------+----------------------------------+---------------------+
  2. | id   | token                            | times               |
  3. +------+----------------------------------+---------------------+
  4. |   29 | a71250b7ed780f6ef3185bfffe027983 | 1999-12-27 09:45:05 |
  5. |   28 | a71250b7ed780f6ef3185bfffe027983 | 1999-12-27 09:44:36 |
  6. |   26 | a71250b7ed780f6ef3185bfffe027983 | 1999-12-27 09:44:24 |
  7. |   27 | a71250b7ed780f6ef3185bfffe027983 | 1999-12-27 09:44:24 |
  8. |   24 | 4d75906f3c37ecff478a1eb56637aa09 | 1999-12-23 17:29:12 |
  9. +------+----------------------------------+---------------------+

Aggregate queries

To have a better understanding of the overall data situation, you can use the GROUP BY statement to aggregate query results.

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

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

Result is as below:

  1. +------+-------+---------------------+
  2. | id   | count | times               |
  3. +------+-------+---------------------+
  4. |   29 |     4 | 1999-12-27 09:45:05 |
  5. |   28 |     3 | 1999-12-27 09:44:36 |
  6. |   26 |     5 | 1999-12-27 09:44:24 |
  7. |   27 |     3 | 1999-12-27 09:44:24 |
  8. |   24 |     3 | 1999-12-23 17:29:12 |
  9. +------+-------+---------------------+