Shulou(Shulou.com)06/02 Report--

This article mainly explains "how to use select, distinct and limit in MySQL". The explanation in this article is simple and clear, easy to learn and understand. Please follow the ideas of Xiaobian and study and learn "how to use select, distinct and limit in MySQL" together!

1. Introduction

This blog will be very basic, if you have MySQL experience can skip, the reason for writing this blog is for beginners to see. The following explains how to use select to view individual columns, multiple columns, and 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',`name` varchar(255) NOT NULL COMMENT ' username',`age` int(11) NOT NULL COMMENT 'age',`sex` smallint(6) NOT NULL COMMENT 'sex', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

The table data are as follows:

INSERT INTO `user` VALUES (1, 'Li Ziba', 18, 1);INSERT INTO `user` VALUES (2, 'Zhang San', 22, 1);INSERT INTO `user` VALUES (3, 'Li Si', 38, 1);INSERT INTO `user` VALUES (4, 'Wangwu', 25, 1);INSERT INTO `user` VALUES (5, ' Liumazi', 13, 0);INSERT INTO `user` VALUES (6, 'Tianqi', 37, 1);SET FOREIGN_KEY_CHECKS = 1;

Note that after MySQL 4.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. select2.1 Query single column

First, use to specify the database you want to operate on.

mysql> use liziba;Database changed

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

select column_name from table_name;mysql> select name from user;+--------+| name |+--------+| Liziba|| Zhang San || Li si || King the fifth || Liumazi|| Tianqi |+-------+6 rows in set (0.00 sec)2.2 Query 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 |+--------+-----+| Liziba| 18 ||Zhang San | 22 ||Li si | 38 ||King the fifth | 25 ||Liumazi| 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 way is to list all column names in the above two ways.

mysql> select id,name,age,sex from user;+----+--------+-----+-----+| id | name | age | sex |+----+--------+-----+-----+| 1 |Liziba| 18 | 1 || 2 |Zhang San | 22 | 1 || 3 |Li si | 38 | 1 || 4 |King the fifth | 25 | 1 || 5 |Liumazi| 13 | 0 || 6 |Tianqi | 37 | 1 |+----+--------+-----+-----+6 rows in set (0.00 sec)

The second type, which is also the most used SQL sentence by some programmers, uses *** wildcard ** instead of all columns of the table.

select * from table_name;

mysql> select * from user;+----+--------+-----+-----+| id | name | age | sex |+----+--------+-----+-----+| 1 |Liziba| 18 | 1 || 2 |Zhang San | 22 | 1 || 3 |Li si | 38 | 1 || 4 |King the fifth | 25 | 1 || 5 |Liumazi| 13 | 0 || 6 |Tianqi | 37 | 1 |+----+--------+-----+-----+6 rows in set (0.00 sec)

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

3、distinct

If you need to query data whose column values are not duplicate, you can use the distinct keyword to deduplicate.

We insert a new entry in the table above, where age is equal to plum eight and sex is the same.

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

You can see that the age column has equal values

mysql> select * from user;+----+--------+-----+-----+| id | name | age | sex |+----+--------+-----+-----+| 1 |Liziba| 18 | 1 || 2 |Zhang San | 22 | 1 || 3 |Li si | 38 | 1 || 4 |King the fifth | 25 | 1 || 5 |Liumazi| 13 | 0 || 6 |Tianqi | 37 | 1 || 7 |thank-you gift | 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 to precede columns that need 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 a problem to note here, distinct keyword de-duplication will affect all fields, if distinct keyword followed by more than one field, then the values of multiple fields are not equal to not duplicate.

For example, if there is no age and name duplicate data in the user table, then the distinct keyword does not take effect, but does not exist.

mysql> select distinct age,name from user;+-----+--------+| age | name |+-----+--------+| 18 |Liziba|| 22 |Zhang San || 38 |Li si || 25 |King the fifth || 13 |Liumazi|| 37 |Tianqi || 18 |thank-you gift |+-----+--------+7 rows in set (0.00 sec)

If the values of the fields following the distinct keyword are not equal, the distinct keyword can still be deduplicated. For example, Li Ziba and Xie Li's age and gender are equal, at this time distinct keyword will filter a 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 criteria. If we only need a specified number of records, we can use the limit keyword to limit the rows returned; this scenario is mostly used for data paging.

The value of limit must be an integer greater than or equal to 0. If negative numbers and decimals are 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 |Liziba| 18 | 1 |+----+--------+-----+-----+1 row in set (0.00 sec)

If limit is given a value greater than the row record value of the table, all data will be returned. For example, we query the record value of the user table by select count(1), a total of 7 pieces of data, at this time we pass 8, and will not report an error, MySQL will put back all the data in the 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 |Liziba| 18 | 1 || 2 |Zhang San | 22 | 1 || 3 |Li si | 38 | 1 || 4 |King the fifth | 25 | 1 || 5 |Liumazi| 13 | 0 || 6 |Tianqi | 37 | 1 || 7 |thank-you gift | 18 | 1 |+----+--------+-----+-----+7 rows in set (0.00 sec)

limit can be used with two parameters to indicate the starting value and the ending value, respectively, and the closed interval (including the starting value and the ending value). If followed by a parameter, it indicates the end value, and the starting value defaults to 0. Note that MySQL data starts with an index value of 0.

limit 2 , 4 means to query the third data to the fifth data, and its line numbers are 2 to 4.

mysql> select * from user limit 2, 4;+----+--------+-----+-----+| id | name | age | sex |+----+--------+-----+-----+| 3 |Li si | 38 | 1 || 4 |King the fifth | 25 | 1 || 5 |Liumazi| 13 | 0 || 6 |Tianqi | 37 | 1 |+---+-----+----+---+---+4 rows in set (0.00 sec) Thank you for reading, the above is "how to use MySQL select, distinct, limit" content, after the study of this article, I believe that everyone on how to use MySQL select, distinct, limit this problem has a deeper experience, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.