0x00 What we learned before?
We have learned how to select particular rows or columns and how to select particular data by the combination of rows and columns.
Use SELECT * FROM tablename WHERE xx=‘xx‘; to select particular row.
Use SELECT col_name FROM table_name; to select a particular column.
(Use AND and OR to select some particular data.)
Use
SELECT col_name FROM table_name
WHERE xx=‘xx‘;
to combine two ways to select a particular data.
After that, we will learn how to sort rows by ORDER BY clause.
0x01 Sorting Rows
We sort rows in order to inspect the table easier and effectively. Use ORDER BY to sort rows in some meaningful way.SELECT col_name1, col_name2 FROM table_name ORDER BY col_name1
then the table will be sorted by order of col_name1.
As you can see, the table was sorted by the order of the pets‘ birth.
the default sort order is ascending, and if you want to sort it in reverse order(descending), use DESC after the col_name like this:
mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
+----------+------------+
| name | birth |
+----------+------------+
| Puffball | 1999-03-30 |
| Chirpy | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim | 1996-04-29 |
| Claws | 1994-03-17 |
| Fluffy | 1993-02-04 |
| Fang | 1990-08-27 |
| Bowser | 1989-08-31 |
| Buffy | 1989-05-13 |
+----------+------------+
wtf, copy it is so fucking ez.
Oh, there‘s a little point need to be careful: on character type columns, sorting is performed in a case-insensitive fashion.
If you want to avoid this, use BINARY to make it case-sensitive.ORDER BY BINARY col_name
0x02 Sorting Multiple Columns Or Sorting Different Columns In Different Directions
Example:
mysql> SELECT name, species, birth FROM pet
ORDER BY species, birth DESC;
+----------+---------+------------+
| name | species | birth |
+----------+---------+------------+
| Chirpy | bird | 1998-09-11 |
| Whistler | bird | 1997-12-09 |
| Claws | cat | 1994-03-17 |
| Fluffy | cat | 1993-02-04 |
| Fang | dog | 1990-08-27 |
| Bowser | dog | 1989-08-31 |
| Buffy | dog | 1989-05-13 |
| Puffball | hamster | 1999-03-30 |
| Slim | snake | 1996-04-29 |
+----------+---------+------------+
Something to be careful:
1.Species is sorted in an ascending way. It wasn‘t affected by DESC.
2.Birth is sorted in descending, because of the DESC after it.