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

This article mainly explains "how to use MySQL order by", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let Xiaobian take you to learn "how to use MySQL order by"!

1. Introduction

When using select statements, you can sort the data of a query in conjunction with order by. If you do not use order by, MySQL returns the dataset by default in the same order as it is in the underlying table, which may or may not be the same as the order in which you added the data to the table (MySQL will sort the memory when you modify, delete, etc., and the order of the data will change at this time). Therefore, if we want the data to be ordered, we should specify the sorting method.

2. Text

First, prepare a User table. DDL and table data are shown below. You can copy it directly.

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) CHARACTER SET utf8 COLATE utf8_general_ci NOT NULL COMMENT ' username',`age` int(11) NOT NULL COMMENT 'year',`sex` smallint(6) NOT NULL COMMENT ' sex', PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLATE = utf8_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of user-- ----------------------------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);INSERT INTO `user` VALUES (7, ' Xie Li', 18, 1);SET FOREIGN_KEY_CHECKS = 1;

The initial order of data is as follows:

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)2.1 Sorting individual columns

Let's start by sorting a single column using order by.

Requirements:

Sort by user age in ascending order.

Statement:

select * from user order by age;

Results:

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

Analysis:

You can see that the user table is output in ascending order of age, so you can guess that MySQL defaults to ascending order. In addition, I use * wildcard to query all columns here, and do not explicitly indicate the query age column; in fact, the columns followed by MySQL order by do not have to be queried, such as select name from user order by age; this is also the same effect.

2.2 Multiple column sorting

Order by not only sorts a single column, it can also sort multiple columns, just need to sort the columns in order after the order by can be.

Before we test, let's add an age-equal entry to the table

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

Requirements:

Sort by user name in ascending order of age.

Statement:

select * from user order by age, name;

Results:

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

Analysis:

Order by can be applied to multiple columns, MySQL will sort exactly according to the order of the columns after order by. MySQL, however, for Chinese sort need to consider the database character set encoding problem, if not very understand the recommendations do not sort Chinese, because most of the cases we need to get the sort results Pinyin, but often unsatisfactory Oh! In addition, here we have no sorting method after age and name, so the default is ascending, first sorting according to age ascending and then sorting according to name ascending. Select id, name, age from user order by age, name desc;

2.3 sort of way

There are two ways to order by, they are:

ASC -> Ascending sort (default sort)

DESC -> Descending sort

Note: In the above, order by sorts multiple columns. The sorting method will only work on one column. For example, if you need to sort the data in the user table according to age and name at the same time, you should indicate descending order in both columns.

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

If you specify only descending sort for the age column and not for the name column, MySQL sorts by descending sort for the age column and then defaults to ascending sort for the name column.

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

It can be seen that the sorting method of Li Ziqi, Li Ziqi and Xie Li has changed.

2.4 order by limit

Order by combined with limit can obtain the number of sorted data row records. For example, we get the oldest user from the user table. We can see that Comrade Li Si is thirty-eight years old, relatively old, and one of the oldest.

mysql> select * from user order by age desc limit 1;+----+------+-----+-----+| id | name | age | sex |+----+------+-----+-----+| 3 |Li si| 38 | 1 |+----+------+-----+-----+1 row in set (0.00 sec)

limit needs to follow order by, if the position is not correct, MySQL will throw an exception message.

mysql> select * from user limit 1 order by age des;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order by age des' at line 1 At this point, I believe that everyone has a deeper understanding of "how to use MySQL in order by," you may wish to actually operate it! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to us, continue to learn!

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.