Use of select, distinct, and limit in MySQL

1. Introduction

This blog will be very basic, if you have MySQL experience you can skip it, the reason for writing this blog is for beginners. The following will explain how to use select to view a single column, multiple columns, or all columns of a specified table.

First, prepare a table with the following structure:

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
  `name` varchar(255) NOT NULL COMMENT 'User name',
  `age` int(11) NOT NULL COMMENT 'Age',
  `sex` smallint(6) NOT NULL COMMENT 'Gender',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

The table data is as follows:

INSERT INTO `user` VALUES (1, '李子８', 18, 1);
INSERT INTO `user` VALUES (2, '张三', 22, 1);
INSERT INTO `user` VALUES (3, '李四', 38, 1);
INSERT INTO `user` VALUES (4, '王五', 25, 1);
INSERT INTO `user` VALUES (5, '六麻子', 13, 0);
INSERT INTO `user` VALUES (6, '田七', 37, 1);

SET FOREIGN_KEY_CHECKS = 1;

Note that after MySQL4.1 , database keywords are completely case-insensitive; database names, table names, and column names are case-insensitive by default, but can be modified (not recommended).

2. select

2.1 Querying a Single Column

First, use use to specify the database to be operated.

mysql> use liziba;
Database changed

Next, use select to query name column from the user table. select is followed by the column name, and from is followed by the table name.

select column_name from table_name;

mysql> select name from user;
+--------+
| name |
+--------+
| Plum Eight|
| Zhang San|
| Li Si|
| Wang Wu|
| Liu Mazi|
| Tianqi|
+--------+
6 rows in set (0.00 sec)

2.2 Querying multiple columns

The difference between querying multiple columns and a single column is that select is followed by multiple column names, separated by commas.

select column_name1,column_name2,column_name3 from table_name;

mysql> select name,age from user;
+--------+-----+
| name | age |
+--------+-----+
| Plum Eight | 18 |
| Zhang San | 22 |
| Li Si | 38 |
| Wang Wu| 25 |
| Six pockmarks | 13 |
| Tianqi | 37 |
+--------+-----+
6 rows in set (0.00 sec)

2.3 Query all columns

There are two ways to query all columns. The first is the two derived methods above, listing all column names.

mysql> select id,name,age,sex from user;
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | Plum 8 | 18 | 1 |
| 2 | Zhang San | 22 | 1 |
| 3 | Li Si | 38 | 1 |
| 4 | Wang Wu | 25 | 1 |
| 5 | Liu Mazi | 13 | 0 |
| 6 | Tianqi | 37 | 1 |
+----+--------+-----+-----+
6 rows in set (0.00 sec)

The second type, which is also the most commonly used SQL by some programmers, uses the *** wildcard character ** to replace all columns of the table.

select * from table_name;

mysql> select * from user;
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | Plum 8 | 18 | 1 |
| 2 | Zhang San | 22 | 1 |
| 3 | Li Si | 38 | 1 |
| 4 | Wang Wu | 25 | 1 |
| 5 | Liu Mazi | 13 | 0 |
| 6 | Tianqi | 37 | 1 |
+----+--------+-----+-----+
6 rows in set (0.00 sec)

Tip: This is a taboo for programmers. If we do not need to obtain all the columns of the table and the column names of the table are transplanted, we should not use the query for all data, but should specify the database column query, which can improve the query performance.

3. distinct

If you need to query data with unique column values, you can use distinct keyword to remove duplicates.

We insert a new data into the above table. The data age is equal to Li Zi Ba, and sex is also the same.

mysql> insert into user (name, age, sex) values('谢礼', 18, 1);
Query OK, 1 row affected (0.01 sec)

Now you can see that the age column has equal values

mysql> select * from user;
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | Plum 8 | 18 | 1 |
| 2 | Zhang San | 22 | 1 |
| 3 | Li Si | 38 | 1 |
| 4 | Wang Wu | 25 | 1 |
| 5 | Liu Mazi | 13 | 0 |
| 6 | Tianqi | 37 | 1 |
| 7 | Thank you | 18 | 1 |
+----+--------+-----+-----+
7 rows in set (0.00 sec)

At this point we want to get the ages of the users in the user table. We can use the distinct keyword and apply it in front of the column that needs to be deduplicated.

mysql> select distinct age from user;
+-----+
|age|
+-----+
| 18 |
| 22 |
| 38 |
| 25 |
| 13 |
| 37 |
+-----+
6 rows in set (0.00 sec)

There is one thing you need to note here . distinct keyword deduplication will apply to all fields. If the distinct keyword is followed by multiple fields, the values ​​of the multiple fields must be different to be considered non-duplicate.

For example, if there is no unique data for both age and name in the user table, distinct keyword is not ineffective, but it does not exist.

mysql> select distinct age,name from user;
+-----+--------+
| age | name |
+-----+--------+
| 18 | Plum Eight |
| 22 | Zhang San|
| 38 | Li Si|
| 25 | Wang Wu|
| 13 | Six pockmarks |
| 37 | Tianqi |
| 18 | Thank you gift |
+-----+--------+
7 rows in set (0.00 sec)

If the field values ​​following the distinct keyword are not equal, distinct keyword can still be used to remove duplicates. For example, Li Ziba and Xie Li have the same age and gender, so the distinct keyword will filter out one piece of data.

mysql> select distinct age,sex from user;
+-----+-----+
| age | sex |
+-----+-----+
| 18 | 1 |
| 22 | 1 |
| 38 | 1 |
| 25 | 1 |
| 13 | 0 |
| 37 | 1 |
+-----+-----+
6 rows in set (0.00 sec)

4. Limit

The previous query will return all records that meet the conditions. If we only need a specified number of records, we can use limit keyword to limit the returned rows; this scenario is often used for data paging.

The value of limit must be an integer greater than or equal to 0. If a negative number or decimal is passed in, an error will be reported.

mysql> select * from user limit 0;
Empty set (0.00 sec)

mysql> select * from user limit 1;
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | Plum 8 | 18 | 1 |
+----+--------+-----+-----+
1 row in set (0.00 sec)

If the value given by limit is greater than the row record value of the table, all data will be returned. For example, if we query the record value of the user table through select count(1) , there are 7 data in total. At this time, if we pass in 8, there will be no error, and MySQL will put back all the data in user table.

mysql> select count(1) from user;
+----------+
| count(1) |
+----------+
| 7 |
+----------+
1 row in set (0.01 sec)

mysql> select * from user limit 8;
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | Plum 8 | 18 | 1 |
| 2 | Zhang San | 22 | 1 |
| 3 | Li Si | 38 | 1 |
| 4 | Wang Wu | 25 | 1 |
| 5 | Liu Mazi | 13 | 0 |
| 6 | Tianqi | 37 | 1 |
| 7 | Thank you | 18 | 1 |
+----+--------+-----+-----+
7 rows in set (0.00 sec)

limit can be followed by two parameters to represent the start value, end value, or closed interval (including the start value and end value). If followed by a parameter, it indicates the end value, and the starting value defaults to 0. Note that the index of MySQL data starts at 0.

limit 2 , 4 means querying the third to fifth data items, whose row numbers are 2 to 4.

mysql> select * from user limit 2, 4;
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 3 | Li Si | 38 | 1 |
| 4 | Wang Wu | 25 | 1 |
| 5 | Liu Mazi | 13 | 0 |
| 6 | Tianqi | 37 | 1 |
+----+--------+-----+-----+
4 rows in set (0.00 sec)

This is the end of this article about the use of MySQL select, distinct, and limit. For more information about the use of MySQL select, distinct, and limit, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!