板凳-------Mysql cookbook学习 （十--6）
第7章：排序查询结果
7.0 引言
mysql> use cookbook
Database changed
mysql> select * from driver_log;
+--------+-------+------------+-------+
| rec_id | name | trav_date | miles |
+--------+-------+------------+-------+
| 1 | Ben | 2014-07-30 | 152 |
| 2 | Suzi | 2014-07-29 | 391 |
| 3 | Henry | 2014-07-29 | 300 |
| 4 | Henry | 2014-07-27 | 96 |
| 5 | Ben | 2014-07-29 | 131 |
| 6 | Henry | 2014-07-26 | 115 |
| 7 | Suzi | 2014-08-02 | 502 |
| 8 | Henry | 2014-08-01 | 197 |
| 9 | Ben | 2014-08-02 | 79 |
| 10 | Henry | 2014-07-30 | 203 |
+--------+-------+------------+-------+
10 rows in set (0.09 sec)mysql> select * from mail;
+---------------------+---------+---------+---------+---------+---------+
| t | srcuser | srchost | dstuser | dsthost | size |
+---------------------+---------+---------+---------+---------+---------+
| 2006-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 |
| 2006-05-12 12:48:13 | tricia | mars | gene | venus | 194925 |
| 2006-05-12 15:02:49 | phil | mars | phil | saturn | 1048 |
| 2006-05-13 13:59:18 | barb | saturn | tricia | venus | 271 |
| 2006-05-14 09:31:37 | gene | venus | barb | mars | 2291 |
| 2006-05-14 11:52:17 | phil | mars | tricia | saturn | 5781 |
| 2006-05-14 14:42:21 | barb | venus | barb | venus | 98151 |
| 2006-05-14 17:03:01 | tricia | saturn | phil | venus | 2394482 |
| 2006-05-15 07:17:48 | gene | mars | gene | saturn | 3824 |
| 2006-05-15 08:50:57 | phil | venus | phil | venus | 978 |
| 2006-05-15 10:25:52 | gene | mars | tricia | saturn | 998532 |
| 2006-05-15 17:35:31 | gene | saturn | gene | mars | 3856 |
| 2006-05-16 09:00:28 | gene | venus | barb | mars | 613 |
| 2006-05-16 23:04:19 | phil | venus | barb | venus | 10294 |
| 2006-05-17 12:49:23 | phil | mars | tricia | saturn | 873 |
| 2006-05-19 22:21:51 | gene | saturn | gene | venus | 23992 |
+---------------------+---------+---------+---------+---------+---------+
16 rows in set (0.02 sec)
7.1 使用order by命令排序查询结果
mysql> select * from driver_log order by name;
+--------+-------+------------+-------+
| rec_id | name | trav_date | miles |
+--------+-------+------------+-------+
| 1 | Ben | 2014-07-30 | 152 |
| 5 | Ben | 2014-07-29 | 131 |
| 9 | Ben | 2014-08-02 | 79 |
| 3 | Henry | 2014-07-29 | 300 |
| 4 | Henry | 2014-07-27 | 96 |
| 6 | Henry | 2014-07-26 | 115 |
| 8 | Henry | 2014-08-01 | 197 |
| 10 | Henry | 2014-07-30 | 203 |
| 2 | Suzi | 2014-07-29 | 391 |
| 7 | Suzi | 2014-08-02 | 502 |
+--------+-------+------------+-------+
10 rows in set (0.00 sec)mysql> select * from driver_log order by name asc;
+--------+-------+------------+-------+
| rec_id | name | trav_date | miles |
+--------+-------+------------+-------+
| 1 | Ben | 2014-07-30 | 152 |
| 5 | Ben | 2014-07-29 | 131 |
| 9 | Ben | 2014-08-02 | 79 |
| 3 | Henry | 2014-07-29 | 300 |
| 4 | Henry | 2014-07-27 | 96 |
| 6 | Henry | 2014-07-26 | 115 |
| 8 | Henry | 2014-08-01 | 197 |
| 10 | Henry | 2014-07-30 | 203 |
| 2 | Suzi | 2014-07-29 | 391 |
| 7 | Suzi | 2014-08-02 | 502 |
+--------+-------+------------+-------+
10 rows in set (0.00 sec)mysql> select * from driver_log order by name ASC;
+--------+-------+------------+-------+
| rec_id | name | trav_date | miles |
+--------+-------+------------+-------+
| 1 | Ben | 2014-07-30 | 152 |
| 5 | Ben | 2014-07-29 | 131 |
| 9 | Ben | 2014-08-02 | 79 |
| 3 | Henry | 2014-07-29 | 300 |
| 4 | Henry | 2014-07-27 | 96 |
| 6 | Henry | 2014-07-26 | 115 |
| 8 | Henry | 2014-08-01 | 197 |
| 10 | Henry | 2014-07-30 | 203 |
| 2 | Suzi | 2014-07-29 | 391 |
| 7 | Suzi | 2014-08-02 | 502 |
+--------+-------+------------+-------+
10 rows in set (0.00 sec)mysql> select * from driver_log order by name DESC;
+--------+-------+------------+-------+
| rec_id | name | trav_date | miles |
+--------+-------+------------+-------+
| 2 | Suzi | 2014-07-29 | 391 |
| 7 | Suzi | 2014-08-02 | 502 |
| 3 | Henry | 2014-07-29 | 300 |
| 4 | Henry | 2014-07-27 | 96 |
| 6 | Henry | 2014-07-26 | 115 |
| 8 | Henry | 2014-08-01 | 197 |
| 10 | Henry | 2014-07-30 | 203 |
| 1 | Ben | 2014-07-30 | 152 |
| 5 | Ben | 2014-07-29 | 131 |
| 9 | Ben | 2014-08-02 | 79 |
+--------+-------+------------+-------+
10 rows in set (0.00 sec)mysql> select * from driver_log order by name , trav_date;
+--------+-------+------------+-------+
| rec_id | name | trav_date | miles |
+--------+-------+------------+-------+
| 5 | Ben | 2014-07-29 | 131 |
| 1 | Ben | 2014-07-30 | 152 |
| 9 | Ben | 2014-08-02 | 79 |
| 6 | Henry | 2014-07-26 | 115 |
| 4 | Henry | 2014-07-27 | 96 |
| 3 | Henry | 2014-07-29 | 300 |
| 10 | Henry | 2014-07-30 | 203 |
| 8 | Henry | 2014-08-01 | 197 |
| 2 | Suzi | 2014-07-29 | 391 |
| 7 | Suzi | 2014-08-02 | 502 |
+--------+-------+------------+-------+
10 rows in set (0.00 sec)mysql> select * from driver_log order by name DESC , trav_date DESC;
+--------+-------+------------+-------+
| rec_id | name | trav_date | miles |
+--------+-------+------------+-------+
| 7 | Suzi | 2014-08-02 | 502 |
| 2 | Suzi | 2014-07-29 | 391 |
| 8 | Henry | 2014-08-01 | 197 |
| 10 | Henry | 2014-07-30 | 203 |
| 3 | Henry | 2014-07-29 | 300 |
| 4 | Henry | 2014-07-27 | 96 |
| 6 | Henry | 2014-07-26 | 115 |
| 9 | Ben | 2014-08-02 | 79 |
| 1 | Ben | 2014-07-30 | 152 |
| 5 | Ben | 2014-07-29 | 131 |
+--------+-------+------------+-------+
10 rows in set (0.00 sec)mysql> select * from driver_log order by name DESC , trav_date ;
+--------+-------+------------+-------+
| rec_id | name | trav_date | miles |
+--------+-------+------------+-------+
| 2 | Suzi | 2014-07-29 | 391 |
| 7 | Suzi | 2014-08-02 | 502 |
| 6 | Henry | 2014-07-26 | 115 |
| 4 | Henry | 2014-07-27 | 96 |
| 3 | Henry | 2014-07-29 | 300 |
| 10 | Henry | 2014-07-30 | 203 |
| 8 | Henry | 2014-08-01 | 197 |
| 5 | Ben | 2014-07-29 | 131 |
| 1 | Ben | 2014-07-30 | 152 |
| 9 | Ben | 2014-08-02 | 79 |
+--------+-------+------------+-------+
10 rows in set (0.00 sec)mysql> select name, trav_date, miles as distance from driver_log-> order by distance;
+-------+------------+----------+
| name | trav_date | distance |
+-------+------------+----------+
| Ben | 2014-08-02 | 79 |
| Henry | 2014-07-27 | 96 |
| Henry | 2014-07-26 | 115 |
| Ben | 2014-07-29 | 131 |
| Ben | 2014-07-30 | 152 |
| Henry | 2014-08-01 | 197 |
| Henry | 2014-07-30 | 203 |
| Henry | 2014-07-29 | 300 |
| Suzi | 2014-07-29 | 391 |
| Suzi | 2014-08-02 | 502 |
+-------+------------+----------+
10 rows in set (0.00 sec)mysql> select name, trav_date, miles as distance from driver_log-> order by distance DESC;
+-------+------------+----------+
| name | trav_date | distance |
+-------+------------+----------+
| Suzi | 2014-08-02 | 502 |
| Suzi | 2014-07-29 | 391 |
| Henry | 2014-07-29 | 300 |
| Henry | 2014-07-30 | 203 |
| Henry | 2014-08-01 | 197 |
| Ben | 2014-07-30 | 152 |
| Ben | 2014-07-29 | 131 |
| Henry | 2014-07-26 | 115 |
| Henry | 2014-07-27 | 96 |
| Ben | 2014-08-02 | 79 |
+-------+------------+----------+
10 rows in set (0.00 sec)
7.2 使用表达式排序
mysql> select * from mail;
+---------------------+---------+---------+---------+---------+---------+
| t | srcuser | srchost | dstuser | dsthost | size |
+---------------------+---------+---------+---------+---------+---------+
| 2006-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 |
| 2006-05-12 12:48:13 | tricia | mars | gene | venus | 194925 |
| 2006-05-12 15:02:49 | phil | mars | phil | saturn | 1048 |
| 2006-05-13 13:59:18 | barb | saturn | tricia | venus | 271 |
| 2006-05-14 09:31:37 | gene | venus | barb | mars | 2291 |
| 2006-05-14 11:52:17 | phil | mars | tricia | saturn | 5781 |
| 2006-05-14 14:42:21 | barb | venus | barb | venus | 98151 |
| 2006-05-14 17:03:01 | tricia | saturn | phil | venus | 2394482 |
| 2006-05-15 07:17:48 | gene | mars | gene | saturn | 3824 |
| 2006-05-15 08:50:57 | phil | venus | phil | venus | 978 |
| 2006-05-15 10:25:52 | gene | mars | tricia | saturn | 998532 |
| 2006-05-15 17:35:31 | gene | saturn | gene | mars | 3856 |
| 2006-05-16 09:00:28 | gene | venus | barb | mars | 613 |
| 2006-05-16 23:04:19 | phil | venus | barb | venus | 10294 |
| 2006-05-17 12:49:23 | phil | mars | tricia | saturn | 873 |
| 2006-05-19 22:21:51 | gene | saturn | gene | venus | 23992 |
+---------------------+---------+---------+---------+---------+---------+
16 rows in set (0.00 sec)mysql> select t, srcuser, floor((size + 1023)/1024)-> from mail where size > 50000-> order by floor((size + 1023)/1024);
+---------------------+---------+---------------------------+
| t | srcuser | floor((size + 1023)/1024) |
+---------------------+---------+---------------------------+
| 2006-05-11 10:15:08 | barb | 57 |
| 2006-05-14 14:42:21 | barb | 96 |
| 2006-05-12 12:48:13 | tricia | 191 |
| 2006-05-15 10:25:52 | gene | 976 |
| 2006-05-14 17:03:01 | tricia | 2339 |
+---------------------+---------+---------------------------+
5 rows in set (0.00 sec)mysql> select t, srcuser, floor((size + 1023)/1024) as kilobytes-> from mail where size > 50000-> order by kilobytes;
+---------------------+---------+-----------+
| t | srcuser | kilobytes |
+---------------------+---------+-----------+
| 2006-05-11 10:15:08 | barb | 57 |
| 2006-05-14 14:42:21 | barb | 96 |
| 2006-05-12 12:48:13 | tricia | 191 |
| 2006-05-15 10:25:52 | gene | 976 |
| 2006-05-14 17:03:01 | tricia | 2339 |
+---------------------+---------+-----------+
5 rows in set (0.00 sec)
7.3 显示一组按照其它属性排序的值
mysql> select t, srcuser,-> concat (floor((size + 1023)/1024), 'K') as size_in_K-> from mail where size > 50000-> order by size_in_K;
+---------------------+---------+-----------+
| t | srcuser | size_in_K |
+---------------------+---------+-----------+
| 2006-05-12 12:48:13 | tricia | 191K |
| 2006-05-14 17:03:01 | tricia | 2339K |
| 2006-05-11 10:15:08 | barb | 57K |
| 2006-05-14 14:42:21 | barb | 96K |
| 2006-05-15 10:25:52 | gene | 976K |
+---------------------+---------+-----------+
5 rows in set (0.00 sec)mysql> select t, srcuser,-> concat (floor((size + 1023)/1024), 'K') as size_in_K-> from mail where size > 50000-> order by size_in_K;
+---------------------+---------+-----------+
| t | srcuser | size_in_K |
+---------------------+---------+-----------+
| 2006-05-12 12:48:13 | tricia | 191K |
| 2006-05-14 17:03:01 | tricia | 2339K |
| 2006-05-11 10:15:08 | barb | 57K |
| 2006-05-14 14:42:21 | barb | 96K |
| 2006-05-15 10:25:52 | gene | 976K |
+---------------------+---------+-----------+
5 rows in set (0.00 sec)mysql> select t, srcuser,-> concat(floor((size+1023)/1024), 'K') as size_in_K-> from mail where size > 50000-> order by size;
+---------------------+---------+-----------+
| t | srcuser | size_in_K |
+---------------------+---------+-----------+
| 2006-05-11 10:15:08 | barb | 57K |
| 2006-05-14 14:42:21 | barb | 96K |
| 2006-05-12 12:48:13 | tricia | 191K |
| 2006-05-15 10:25:52 | gene | 976K |
| 2006-05-14 17:03:01 | tricia | 2339K |
+---------------------+---------+-----------+
5 rows in set (0.00 sec)mysql> select * from roster;
+-----------+------------+
| name | jersey_num |
+-----------+------------+
| Elizabeth | 100 |
| Ella | 0 |
| Jean | 8 |
| Lynne | 29 |
| Nancy | 00 |
| Sherry | 47 |
+-----------+------------+
6 rows in set (0.01 sec)mysql> select name, jersey_num from roster;
+-----------+------------+
| name | jersey_num |
+-----------+------------+
| Elizabeth | 100 |
| Ella | 0 |
| Jean | 8 |
| Lynne | 29 |
| Nancy | 00 |
| Sherry | 47 |
+-----------+------------+
6 rows in set (0.00 sec)mysql> select name, jersey_num from roster order by jersey_num;
+-----------+------------+
| name | jersey_num |
+-----------+------------+
| Ella | 0 |
| Nancy | 00 |
| Elizabeth | 100 |
| Lynne | 29 |
| Sherry | 47 |
| Jean | 8 |
+-----------+------------+
6 rows in set (0.00 sec)mysql> select name, jersey_num from roster order by jersey_num+0;
+-----------+------------+
| name | jersey_num |
+-----------+------------+
| Ella | 0 |
| Nancy | 00 |
| Jean | 8 |
| Lynne | 29 |
| Sherry | 47 |
| Elizabeth | 100 |
+-----------+------------+
6 rows in set (0.00 sec)mysql> select t, concat(srcuser, '@', srchost) as sender, size-> from mail where size > 50000-> order by srchost, srcuser;
+---------------------+---------------+---------+
| t | sender | size |
+---------------------+---------------+---------+
| 2006-05-15 10:25:52 | gene@mars | 998532 |
| 2006-05-12 12:48:13 | tricia@mars | 194925 |
| 2006-05-11 10:15:08 | barb@saturn | 58274 |
| 2006-05-14 17:03:01 | tricia@saturn | 2394482 |
| 2006-05-14 14:42:21 | barb@venus | 98151 |
+---------------------+---------------+---------+
5 rows in set (0.00 sec)mysql> select last_name, first_name from name-> order by last_name, first_name;
+-----------+------------+
| last_name | first_name |
+-----------+------------+
| Blue | Vida |
| Brown | Kevin |
| Gray | Pete |
| White | Devon |
| White | Rondell |
+-----------+------------+
5 rows in set (0.01 sec)mysql> select concat(first_name, ' ', last_name) as full_name-> from name-> order by last_name, first_name;
+---------------+
| full_name |
+---------------+
| Vida Blue |
| Kevin Brown |
| Pete Gray |
| Devon White |
| Rondell White |
+---------------+
5 rows in set (0.00 sec)
7.4 字符串排序的大小写区分控制
mysql> select * from str_val;
+--------+--------+------------------+
| ci_str | cs_str | bin_str |
+--------+--------+------------------+
| AAA | AAA | 0x414141 |
| aaa | aaa | 0x616161 |
| bbb | bbb | 0x626262 |
| BBB | BBB | 0x424242 |
+--------+--------+------------------+
4 rows in set (0.03 sec)mysql> select ci_str from str_val order by ci_str;
+--------+
| ci_str |
+--------+
| AAA |
| aaa |
| bbb |
| BBB |
+--------+
4 rows in set (0.00 sec)mysql> select cs_str from str_val order by cs_str;
+--------+
| cs_str |
+--------+
| aaa |
| AAA |
| bbb |
| BBB |
+--------+
4 rows in set (0.00 sec)mysql> select bin_str from str_val order by bin_str;
+------------------+
| bin_str |
+------------------+
| 0x414141 |
| 0x424242 |
| 0x616161 |
| 0x626262 |
+------------------+
4 rows in set (0.00 sec)mysql> select bin_str from str_val-> order by convert(bin_str using latin1) collate latin1_swedish_ci;
+------------------+
| bin_str |
+------------------+
| 0x414141 |
| 0x616161 |
| 0x626262 |
| 0x424242 |
+------------------+
4 rows in set (0.02 sec)
7.5 基于日期的排序
--建表
drop table if exists temporal_val;create table temporal_val
(d date,dt datetime,t time,ts timestamp
);# 初始化数据
insert into temporal_val (d, dt, t, ts)values('1970-01-01','1884-01-01 12:00:00','13:00:00','1980-01-01 02:00:00'),('1999-01-01','1860-01-01 12:00:00','19:00:00','2021-01-01 03:00:00'),('1981-01-01','1871-01-01 12:00:00','03:00:00','1975-01-01 04:00:00'),
('1964-01-01','1899-01-01 12:00:00','01:00:00','1985-01-01 05:00:00');mysql> select * from temporal_val;
+------------+---------------------+----------+---------------------+
| d | dt | t | ts |
+------------+---------------------+----------+---------------------+
| 1970-01-01 | 1884-01-01 12:00:00 | 13:00:00 | 1980-01-01 02:00:00 |
| 1999-01-01 | 1860-01-01 12:00:00 | 19:00:00 | 2021-01-01 03:00:00 |
| 1981-01-01 | 1871-01-01 12:00:00 | 03:00:00 | 1975-01-01 04:00:00 |
| 1964-01-01 | 1899-01-01 12:00:00 | 01:00:00 | 1985-01-01 05:00:00 |
+------------+---------------------+----------+---------------------+
4 rows in set (0.00 sec)mysql> select * from temporal_val order by d;
+------------+---------------------+----------+---------------------+
| d | dt | t | ts |
+------------+---------------------+----------+---------------------+
| 1964-01-01 | 1899-01-01 12:00:00 | 01:00:00 | 1985-01-01 05:00:00 |
| 1970-01-01 | 1884-01-01 12:00:00 | 13:00:00 | 1980-01-01 02:00:00 |
| 1981-01-01 | 1871-01-01 12:00:00 | 03:00:00 | 1975-01-01 04:00:00 |
| 1999-01-01 | 1860-01-01 12:00:00 | 19:00:00 | 2021-01-01 03:00:00 |
+------------+---------------------+----------+---------------------+
4 rows in set (0.00 sec)mysql> select * from temporal_val order by dt;
+------------+---------------------+----------+---------------------+
| d | dt | t | ts |
+------------+---------------------+----------+---------------------+
| 1999-01-01 | 1860-01-01 12:00:00 | 19:00:00 | 2021-01-01 03:00:00 |
| 1981-01-01 | 1871-01-01 12:00:00 | 03:00:00 | 1975-01-01 04:00:00 |
| 1970-01-01 | 1884-01-01 12:00:00 | 13:00:00 | 1980-01-01 02:00:00 |
| 1964-01-01 | 1899-01-01 12:00:00 | 01:00:00 | 1985-01-01 05:00:00 |
+------------+---------------------+----------+---------------------+
4 rows in set (0.00 sec)mysql> select * from temporal_val order by t;
+------------+---------------------+----------+---------------------+
| d | dt | t | ts |
+------------+---------------------+----------+---------------------+
| 1964-01-01 | 1899-01-01 12:00:00 | 01:00:00 | 1985-01-01 05:00:00 |
| 1981-01-01 | 1871-01-01 12:00:00 | 03:00:00 | 1975-01-01 04:00:00 |
| 1970-01-01 | 1884-01-01 12:00:00 | 13:00:00 | 1980-01-01 02:00:00 |
| 1999-01-01 | 1860-01-01 12:00:00 | 19:00:00 | 2021-01-01 03:00:00 |
+------------+---------------------+----------+---------------------+
4 rows in set (0.00 sec)mysql> select * from temporal_val order by ts;
+------------+---------------------+----------+---------------------+
| d | dt | t | ts |
+------------+---------------------+----------+---------------------+
| 1981-01-01 | 1871-01-01 12:00:00 | 03:00:00 | 1975-01-01 04:00:00 |
| 1970-01-01 | 1884-01-01 12:00:00 | 13:00:00 | 1980-01-01 02:00:00 |
| 1964-01-01 | 1899-01-01 12:00:00 | 01:00:00 | 1985-01-01 05:00:00 |
| 1999-01-01 | 1860-01-01 12:00:00 | 19:00:00 | 2021-01-01 03:00:00 |
+------------+---------------------+----------+---------------------+
4 rows in set (0.00 sec)
7.6 按日历排序
--建表
drop table if exists event;
create table event
(date date,description varchar(255)
)
;--初始化表
insert into event (date,description)values('1789-07-04','US Independence Day'),('1776-07-14','Bastille Day'),('1957-10-04','Sputnik launch date'),('1958-01-31','Explorer 1 launch date'),('1919-06-28','Signing of the Treaty of Versailles'),('1732-02-22','George Washington\'s birthday'),('1989-11-09','Opening of the Berlin Wall'),('1944-06-06','D-Day at Normandy Beaches'),('1215-06-15','Signing of the Magna Carta'),('1809-02-12','Abraham Lincoln\'s birthday')
;mysql> select date, description from event-> order by month(date), dayofmonth(date);
+------------+-------------------------------------+
| date | description |
+------------+-------------------------------------+
| 1958-01-31 | Explorer 1 launch date |
| 1809-02-12 | Abraham Lincoln's birthday |
| 1732-02-22 | George Washington's birthday |
| 1944-06-06 | D-Day at Normandy Beaches |
| 1215-06-15 | Signing of the Magna Carta |
| 1919-06-28 | Signing of the Treaty of Versailles |
| 1789-07-04 | US Independence Day |
| 1776-07-14 | Bastille Day |
| 1957-10-04 | Sputnik launch date |
| 1989-11-09 | Opening of the Berlin Wall |
+------------+-------------------------------------+
10 rows in set (0.01 sec)mysql> select date, description from event order by dayofyear(date);
+------------+-------------------------------------+
| date | description |
+------------+-------------------------------------+
| 1958-01-31 | Explorer 1 launch date |
| 1809-02-12 | Abraham Lincoln's birthday |
| 1732-02-22 | George Washington's birthday |
| 1944-06-06 | D-Day at Normandy Beaches |
| 1215-06-15 | Signing of the Magna Carta |
| 1919-06-28 | Signing of the Treaty of Versailles |
| 1789-07-04 | US Independence Day |
| 1776-07-14 | Bastille Day |
| 1957-10-04 | Sputnik launch date |
| 1989-11-09 | Opening of the Berlin Wall |
+------------+-------------------------------------+
10 rows in set (0.00 sec)mysql> select dayofyear('1996-02-29'), dayofyear('1997-03-01');
+-------------------------+-------------------------+
| dayofyear('1996-02-29') | dayofyear('1997-03-01') |
+-------------------------+-------------------------+
| 60 | 60 |
+-------------------------+-------------------------+
1 row in set (0.00 sec)
7.7 按周历排序
mysql> select dayname(date) as day, date, description-> from event-> order by dayofweek(date);
+----------+------------+-------------------------------------+
| day | date | description |
+----------+------------+-------------------------------------+
| Sunday | 1776-07-14 | Bastille Day |
| Sunday | 1809-02-12 | Abraham Lincoln's birthday |
| Monday | 1215-06-15 | Signing of the Magna Carta |
| Tuesday | 1944-06-06 | D-Day at Normandy Beaches |
| Thursday | 1989-11-09 | Opening of the Berlin Wall |
| Friday | 1957-10-04 | Sputnik launch date |
| Friday | 1958-01-31 | Explorer 1 launch date |
| Friday | 1732-02-22 | George Washington's birthday |
| Saturday | 1789-07-04 | US Independence Day |
| Saturday | 1919-06-28 | Signing of the Treaty of Versailles |
+----------+------------+-------------------------------------+
10 rows in set (0.01 sec)mysql> select dayname(date), date, description-> from event-> order by mod(dayofweek(date)+5, 7);
+---------------+------------+-------------------------------------+
| dayname(date) | date | description |
+---------------+------------+-------------------------------------+
| Monday | 1215-06-15 | Signing of the Magna Carta |
| Tuesday | 1944-06-06 | D-Day at Normandy Beaches |
| Thursday | 1989-11-09 | Opening of the Berlin Wall |
| Friday | 1957-10-04 | Sputnik launch date |
| Friday | 1958-01-31 | Explorer 1 launch date |
| Friday | 1732-02-22 | George Washington's birthday |
| Saturday | 1789-07-04 | US Independence Day |
| Saturday | 1919-06-28 | Signing of the Treaty of Versailles |
| Sunday | 1776-07-14 | Bastille Day |
| Sunday | 1809-02-12 | Abraham Lincoln's birthday |
+---------------+------------+-------------------------------------+
10 rows in set (0.01 sec)
7.8 按时钟排序
mysql> select * from mail order by hour(t), minute(t),second(t);
+---------------------+---------+---------+---------+---------+---------+
| t | srcuser | srchost | dstuser | dsthost | size |
+---------------------+---------+---------+---------+---------+---------+
| 2006-05-15 07:17:48 | gene | mars | gene | saturn | 3824 |
| 2006-05-15 08:50:57 | phil | venus | phil | venus | 978 |
| 2006-05-16 09:00:28 | gene | venus | barb | mars | 613 |
| 2006-05-14 09:31:37 | gene | venus | barb | mars | 2291 |
| 2006-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 |
| 2006-05-15 10:25:52 | gene | mars | tricia | saturn | 998532 |
| 2006-05-14 11:52:17 | phil | mars | tricia | saturn | 5781 |
| 2006-05-12 12:48:13 | tricia | mars | gene | venus | 194925 |
| 2006-05-17 12:49:23 | phil | mars | tricia | saturn | 873 |
| 2006-05-13 13:59:18 | barb | saturn | tricia | venus | 271 |
| 2006-05-14 14:42:21 | barb | venus | barb | venus | 98151 |
| 2006-05-12 15:02:49 | phil | mars | phil | saturn | 1048 |
| 2006-05-14 17:03:01 | tricia | saturn | phil | venus | 2394482 |
| 2006-05-15 17:35:31 | gene | saturn | gene | mars | 3856 |
| 2006-05-19 22:21:51 | gene | saturn | gene | venus | 23992 |
| 2006-05-16 23:04:19 | phil | venus | barb | venus | 10294 |
+---------------------+---------+---------+---------+---------+---------+
16 rows in set (0.01 sec)mysql> select * from mail order by time_to_sec(t);
+---------------------+---------+---------+---------+---------+---------+
| t | srcuser | srchost | dstuser | dsthost | size |
+---------------------+---------+---------+---------+---------+---------+
| 2006-05-15 07:17:48 | gene | mars | gene | saturn | 3824 |
| 2006-05-15 08:50:57 | phil | venus | phil | venus | 978 |
| 2006-05-16 09:00:28 | gene | venus | barb | mars | 613 |
| 2006-05-14 09:31:37 | gene | venus | barb | mars | 2291 |
| 2006-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 |
| 2006-05-15 10:25:52 | gene | mars | tricia | saturn | 998532 |
| 2006-05-14 11:52:17 | phil | mars | tricia | saturn | 5781 |
| 2006-05-12 12:48:13 | tricia | mars | gene | venus | 194925 |
| 2006-05-17 12:49:23 | phil | mars | tricia | saturn | 873 |
| 2006-05-13 13:59:18 | barb | saturn | tricia | venus | 271 |
| 2006-05-14 14:42:21 | barb | venus | barb | venus | 98151 |
| 2006-05-12 15:02:49 | phil | mars | phil | saturn | 1048 |
| 2006-05-14 17:03:01 | tricia | saturn | phil | venus | 2394482 |
| 2006-05-15 17:35:31 | gene | saturn | gene | mars | 3856 |
| 2006-05-19 22:21:51 | gene | saturn | gene | venus | 23992 |
| 2006-05-16 23:04:19 | phil | venus | barb | venus | 10294 |
+---------------------+---------+---------+---------+---------+---------+
16 rows in set (0.00 sec)
7.9 按数据列的子串排序
--创建表、初始化数据
drop table if exists housewares;
create table housewares
(id VARCHAR(20),description VARCHAR(255)
);insert into housewares (id,description)values('DIN40672US', 'dining table'),('KIT00372UK', 'garbage disposal'),('KIT01729JP', 'microwave oven'),('BED00038SG', 'bedside lamp'),('BTH00485US', 'shower stall'),('BTH00415JP', 'lavatory')
;select * from housewares;drop table if exists housewares2;
create table housewares2
(id varchar(20),description varchar(255)
);insert into housewares2 (id,description)values('DIN40672US', 'dining table'),('KIT372UK', 'garbage disposal'),('KIT1729JP', 'microwave oven'),('BED38SG', 'bedside lamp'),('BTH485US', 'shower stall'),('BTH415JP', 'lavatory')
;
mysql> select * from housewares2;
+------------+------------------+
| id | description |
+------------+------------------+
| DIN40672US | dining table |
| KIT372UK | garbage disposal |
| KIT1729JP | microwave oven |
| BED38SG | bedside lamp |
| BTH485US | shower stall |
| BTH415JP | lavatory |
+------------+------------------+
6 rows in set (0.00 sec)mysql> drop table if exists housewares3;
Query OK, 0 rows affected (0.05 sec)mysql> create table housewares3-> (-> id VARCHAR(20),-> description VARCHAR(255)-> );
Query OK, 0 rows affected (0.04 sec)mysql>
mysql> insert into housewares3 (id,description)-> VALUES-> ('13-478-92-2', 'dining table'),-> ('873-48-649-63', 'garbage disposal'),-> ('8-4-2-1', 'microwave oven'),-> ('97-681-37-66', 'bedside lamp'),-> ('27-48-534-2', 'shower stall'),-> ('5764-56-89-72', 'lavatory')-> ;
Query OK, 6 rows affected (0.02 sec)
Records: 6 Duplicates: 0 Warnings: 0mysql>
mysql> select * from housewares3;
+---------------+------------------+
| id | description |
+---------------+------------------+
| 13-478-92-2 | dining table |
| 873-48-649-63 | garbage disposal |
| 8-4-2-1 | microwave oven |
| 97-681-37-66 | bedside lamp |
| 27-48-534-2 | shower stall |
| 5764-56-89-72 | lavatory |
+---------------+------------------+
6 rows in set (0.00 sec)mysql> drop table if exists hw_category;
Query OK, 0 rows affected (0.04 sec)mysql> create table hw_category-> (-> abbrev VARCHAR(3),-> name VARCHAR(20)-> );
Query OK, 0 rows affected (0.06 sec)mysql>
mysql> insert into hw_category (abbrev,name)-> values-> ('DIN', 'dining'),-> ('KIT', 'kitchen'),-> ('BTH', 'bathroom'),-> ('BED', 'bedroom')-> ;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0mysql>
mysql> select * from hw_category;
+--------+----------+
| abbrev | name |
+--------+----------+
| DIN | dining |
| KIT | kitchen |
| BTH | bathroom |
| BED | bedroom |
+--------+----------+
4 rows in set (0.00 sec)
7.10 按固定长度的子串排序
mysql> select * from housewares;
+------------+------------------+
| id | description |
+------------+------------------+
| DIN40672US | dining table |
| KIT00372UK | garbage disposal |
| KIT01729JP | microwave oven |
| BED00038SG | bedside lamp |
| BTH00485US | shower stall |
| BTH00415JP | lavatory |
+------------+------------------+
6 rows in set (0.00 sec)mysql> select * from housewares order by id;
+------------+------------------+
| id | description |
+------------+------------------+
| BED00038SG | bedside lamp |
| BTH00415JP | lavatory |
| BTH00485US | shower stall |
| DIN40672US | dining table |
| KIT00372UK | garbage disposal |
| KIT01729JP | microwave oven |
+------------+------------------+
6 rows in set (0.00 sec)mysql> select id,-> left(id, 3) as category,-> mid(id, 4, 5) as serial,-> right(id, 2) as country-> from housewares;
+------------+----------+--------+---------+
| id | category | serial | country |
+------------+----------+--------+---------+
| DIN40672US | DIN | 40672 | US |
| KIT00372UK | KIT | 00372 | UK |
| KIT01729JP | KIT | 01729 | JP |
| BED00038SG | BED | 00038 | SG |
| BTH00485US | BTH | 00485 | US |
| BTH00415JP | BTH | 00415 | JP |
+------------+----------+--------+---------+
6 rows in set (0.00 sec)mysql> select * from housewares order by left(id, 3);
+------------+------------------+
| id | description |
+------------+------------------+
| BED00038SG | bedside lamp |
| BTH00485US | shower stall |
| BTH00415JP | lavatory |
| DIN40672US | dining table |
| KIT00372UK | garbage disposal |
| KIT01729JP | microwave oven |
+------------+------------------+
6 rows in set (0.00 sec)mysql> select * from housewares order by mid(id, 4, 5);
+------------+------------------+
| id | description |
+------------+------------------+
| BED00038SG | bedside lamp |
| KIT00372UK | garbage disposal |
| BTH00415JP | lavatory |
| BTH00485US | shower stall |
| KIT01729JP | microwave oven |
| DIN40672US | dining table |
+------------+------------------+
6 rows in set (0.00 sec)mysql> select * from housewares order by right(id, 2);
+------------+------------------+
| id | description |
+------------+------------------+
| KIT01729JP | microwave oven |
| BTH00415JP | lavatory |
| BED00038SG | bedside lamp |
| KIT00372UK | garbage disposal |
| DIN40672US | dining table |
| BTH00485US | shower stall |
+------------+------------------+
6 rows in set (0.00 sec)mysql> select * from housewares order by right(id, 2), mid(id, 4, 5);
+------------+------------------+
| id | description |
+------------+------------------+
| BTH00415JP | lavatory |
| KIT01729JP | microwave oven |
| BED00038SG | bedside lamp |
| KIT00372UK | garbage disposal |
| BTH00485US | shower stall |
| DIN40672US | dining table |
+------------+------------------+
6 rows in set (0.00 sec)
7.11 按可变长度的子串排序
mysql> select * from housewares2;
+------------+------------------+
| id | description |
+------------+------------------+
| DIN40672US | dining table |
| KIT372UK | garbage disposal |
| KIT1729JP | microwave oven |
| BED38SG | bedside lamp |
| BTH485US | shower stall |
| BTH415JP | lavatory |
+------------+------------------+
6 rows in set (0.00 sec)mysql> select id, left(substring(id, 4), char_length(substring(id, 4))-2) from housewares2;
+------------+---------------------------------------------------------+
| id | left(substring(id, 4), char_length(substring(id, 4))-2) |
+------------+---------------------------------------------------------+
| DIN40672US | 40672 |
| KIT372UK | 372 |
| KIT1729JP | 1729 |
| BED38SG | 38 |
| BTH485US | 485 |
| BTH415JP | 415 |
+------------+---------------------------------------------------------+
6 rows in set (0.01 sec)mysql> select id, substring(id, 4), substring(id, 4, char_length(id)-5) from housewares2;
+------------+------------------+-------------------------------------+
| id | substring(id, 4) | substring(id, 4, char_length(id)-5) |
+------------+------------------+-------------------------------------+
| DIN40672US | 40672US | 40672 |
| KIT372UK | 372UK | 372 |
| KIT1729JP | 1729JP | 1729 |
| BED38SG | 38SG | 38 |
| BTH485US | 485US | 485 |
| BTH415JP | 415JP | 415 |
+------------+------------------+-------------------------------------+
6 rows in set (0.00 sec)mysql> select * from housewares2 order by substring(id, 4, char_length(id)-5);
+------------+------------------+
| id | description |
+------------+------------------+
| KIT1729JP | microwave oven |
| KIT372UK | garbage disposal |
| BED38SG | bedside lamp |
| DIN40672US | dining table |
| BTH415JP | lavatory |
| BTH485US | shower stall |
+------------+------------------+
6 rows in set (0.00 sec)mysql>
mysql> select * from housewares2 order by substring(id, 4, char_length(id)-5);
+------------+------------------+
| id | description |
+------------+------------------+
| KIT1729JP | microwave oven |
| KIT372UK | garbage disposal |
| BED38SG | bedside lamp |
| DIN40672US | dining table |
| BTH415JP | lavatory |
| BTH485US | shower stall |
+------------+------------------+
6 rows in set (0.00 sec)mysql> select * from housewares2 order by substring(id, 4, char_length(id)-5)+0;
+------------+------------------+
| id | description |
+------------+------------------+
| BED38SG | bedside lamp |
| KIT372UK | garbage disposal |
| BTH415JP | lavatory |
| BTH485US | shower stall |
| KIT1729JP | microwave oven |
| DIN40672US | dining table |
+------------+------------------+
6 rows in set (0.00 sec)mysql> select *, substring(id, 4, char_length(id)-5)+0 from housewares2 order by substring(id, 4, char_length(id)-5)+0;
+------------+------------------+---------------------------------------+
| id | description | substring(id, 4, char_length(id)-5)+0 |
+------------+------------------+---------------------------------------+
| BED38SG | bedside lamp | 38 |
| KIT372UK | garbage disposal | 372 |
| BTH415JP | lavatory | 415 |
| BTH485US | shower stall | 485 |
| KIT1729JP | microwave oven | 1729 |
| DIN40672US | dining table | 40672 |
+------------+------------------+---------------------------------------+
6 rows in set (0.00 sec)mysql> select * from housewares2 order by substring(id, 4)+0;
+------------+------------------+
| id | description |
+------------+------------------+
| BED38SG | bedside lamp |
| KIT372UK | garbage disposal |
| BTH415JP | lavatory |
| BTH485US | shower stall |
| KIT1729JP | microwave oven |
| DIN40672US | dining table |
+------------+------------------+
6 rows in set, 6 warnings (0.00 sec)mysql>
mysql> select * from housewares3;
+---------------+------------------+
| id | description |
+---------------+------------------+
| 13-478-92-2 | dining table |
| 873-48-649-63 | garbage disposal |
| 8-4-2-1 | microwave oven |
| 97-681-37-66 | bedside lamp |
| 27-48-534-2 | shower stall |
| 5764-56-89-72 | lavatory |
+---------------+------------------+
6 rows in set (0.00 sec)mysql> select id, substring_index(substring_index(id, '-', 2), '-', -1) as segment2,-> substring_index(substring_index(id, '-', 4), '-', -1) as segment4-> from housewares3;
+---------------+----------+----------+
| id | segment2 | segment4 |
+---------------+----------+----------+
| 13-478-92-2 | 478 | 2 |
| 873-48-649-63 | 48 | 63 |
| 8-4-2-1 | 4 | 1 |
| 97-681-37-66 | 681 | 66 |
| 27-48-534-2 | 48 | 2 |
| 5764-56-89-72 | 56 | 72 |
+---------------+----------+----------+
6 rows in set (0.00 sec)mysql>
mysql> select * from housewares3 order by substring_index(substring_index(id, '-', 2), '-', -1);
+---------------+------------------+
| id | description |
+---------------+------------------+
| 8-4-2-1 | microwave oven |
| 13-478-92-2 | dining table |
| 873-48-649-63 | garbage disposal |
| 27-48-534-2 | shower stall |
| 5764-56-89-72 | lavatory |
| 97-681-37-66 | bedside lamp |
+---------------+------------------+
6 rows in set (0.00 sec)mysql> select * from housewares3 order by substring_index(substring_index(id, '-', 2), '-', -1)+0;
+---------------+------------------+
| id | description |
+---------------+------------------+
| 8-4-2-1 | microwave oven |
| 873-48-649-63 | garbage disposal |
| 27-48-534-2 | shower stall |
| 5764-56-89-72 | lavatory |
| 13-478-92-2 | dining table |
| 97-681-37-66 | bedside lamp |
+---------------+------------------+
6 rows in set (0.00 sec)
7.12 按域名顺序排列主机名
mysql> drop table if exists hostname;
Query OK, 0 rows affected (0.04 sec)mysql> create table hostname-> (-> name varchar(64)-> );
Query OK, 0 rows affected (0.06 sec)mysql> insert into hostname (name)-> values-> ('cvs.php.net'),-> ('dbi.perl.org'),-> ('lists.mysql.com'),-> ('mysql.com'),-> ('jakarta.apache.org'),-> ('www.kitebird.com')-> ;
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0mysql> select * from hostname;
+--------------------+
| name |
+--------------------+
| cvs.php.net |
| dbi.perl.org |
| lists.mysql.com |
| mysql.com |
| jakarta.apache.org |
| www.kitebird.com |
+--------------------+
6 rows in set (0.00 sec)mysql>
mysql> select name from hostname order by name;
+--------------------+
| name |
+--------------------+
| cvs.php.net |
| dbi.perl.org |
| jakarta.apache.org |
| lists.mysql.com |
| mysql.com |
| www.kitebird.com |
+--------------------+
6 rows in set (0.00 sec)mysql> select name, substring_index(substring_index(name, '.', -3), '.', 1) as leftmost,-> substring_index(substring_index(name, '.', -2), '.', 1) as middle,-> substring_index(name, '.', -1) as rightmost-> from hostname-> ;
+--------------------+----------+----------+-----------+
| name | leftmost | middle | rightmost |
+--------------------+----------+----------+-----------+
| cvs.php.net | cvs | php | net |
| dbi.perl.org | dbi | perl | org |
| lists.mysql.com | lists | mysql | com |
| mysql.com | mysql | mysql | com |
| jakarta.apache.org | jakarta | apache | org |
| www.kitebird.com | www | kitebird | com |
+--------------------+----------+----------+-----------+
6 rows in set (0.00 sec)mysql> select name, substring_index(substring_index(concat('..', name), '.', -3), '.', 1) as leftmost,-> substring_index(substring_index(concat('.', name), '.', -2), '.', 1) as middle,-> substring_index(name, '.', -1) as rightmost-> from hostname;
+--------------------+----------+----------+-----------+
| name | leftmost | middle | rightmost |
+--------------------+----------+----------+-----------+
| cvs.php.net | cvs | php | net |
| dbi.perl.org | dbi | perl | org |
| lists.mysql.com | lists | mysql | com |
| mysql.com | | mysql | com |
| jakarta.apache.org | jakarta | apache | org |
| www.kitebird.com | www | kitebird | com |
+--------------------+----------+----------+-----------+
6 rows in set (0.00 sec)mysql> select name from hostname-> order by-> substring_index(name, '.', -1),-> substring_index(substring_index(concat('.', name), '.', -2), '.', 1),-> substring_index(substring_index(concat('..', name), '.', -3), '.', 1);
+--------------------+
| name |
+--------------------+
| www.kitebird.com |
| mysql.com |
| lists.mysql.com |
| cvs.php.net |
| jakarta.apache.org |
| dbi.perl.org |
+--------------------+
6 rows in set (0.00 sec)
7.13 按照数字顺序排序点分式ip地址
mysql> drop table if exists hostip;
Query OK, 0 rows affected (0.06 sec)mysql> create table hostip-> (-> ip varchar(64)-> );
Query OK, 0 rows affected (0.05 sec)mysql> insert into hostip (ip)-> values-> ('127.0.0.1'),-> ('192.168.0.2'),-> ('192.168.0.10'),-> ('192.168.1.2'),-> ('192.168.1.10'),-> ('255.255.255.255'),-> ('21.0.0.1')-> ;
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0mysql> select * from hostip ORDER BY ip;
+-----------------+
| ip |
+-----------------+
| 127.0.0.1 |
| 192.168.0.10 |
| 192.168.0.2 |
| 192.168.1.10 |
| 192.168.1.2 |
| 21.0.0.1 |
| 255.255.255.255 |
+-----------------+
7 rows in set (0.00 sec)mysql>
mysql> select ip from hostip-> order by-> substring_index(ip, '.', 1)+0,-> substring_index(substring_index(ip, '.', -3), '.', 1)+0,-> substring_index(substring_index(ip, '.', -2), '.', 1)+0,-> substring_index(ip, '.', -1)+0;
+-----------------+
| ip |
+-----------------+
| 21.0.0.1 |
| 127.0.0.1 |
| 192.168.0.2 |
| 192.168.0.10 |
| 192.168.1.2 |
| 192.168.1.10 |
| 255.255.255.255 |
+-----------------+
7 rows in set (0.00 sec)mysql>
mysql> select ip from hostip order by inet_aton(ip);
+-----------------+
| ip |
+-----------------+
| 21.0.0.1 |
| 127.0.0.1 |
| 192.168.0.2 |
| 192.168.0.10 |
| 192.168.1.2 |
| 192.168.1.10 |
| 255.255.255.255 |
+-----------------+
7 rows in set (0.00 sec)mysql>
mysql> select ip, ip+0 from hostip;
+-----------------+---------+
| ip | ip+0 |
+-----------------+---------+
| 127.0.0.1 | 127 |
| 192.168.0.2 | 192.168 |
| 192.168.0.10 | 192.168 |
| 192.168.1.2 | 192.168 |
| 192.168.1.10 | 192.168 |
| 255.255.255.255 | 255.255 |
| 21.0.0.1 | 21 |
+-----------------+---------+
7 rows in set, 3 warnings (0.00 sec)
7.14 将数值移动到排序结果的头部或尾部
mysql> select null = null;
+-------------+
| null = null |
+-------------+
| NULL |
+-------------+
1 row in set (0.00 sec)mysql> drop table if exists t;
Query OK, 0 rows affected (0.04 sec)mysql> create table t (-> val varchar(64)-> );
Query OK, 0 rows affected (0.04 sec)mysql> insert into t (val)-> values-> (3),-> (100),-> (null),-> (null),-> (9)-> ;
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0mysql> select val from t;
+------+
| val |
+------+
| 3 |
| 100 |
| NULL |
| NULL |
| 9 |
+------+
5 rows in set (0.00 sec)mysql> select val from t order by val;
+------+
| val |
+------+
| NULL |
| NULL |
| 100 |
| 3 |
| 9 |
+------+
5 rows in set (0.00 sec)mysql> select val from t order by if(val is null, 1, 0), val;
+------+
| val |
+------+
| 100 |
| 3 |
| 9 |
| NULL |
| NULL |
+------+
5 rows in set (0.00 sec)mysql> select t, srcuser, dstuser, size-> from mail-> order by if(srcuser='phil', 0, 1), srcuser, dstuser;
+---------------------+---------+---------+---------+
| t | srcuser | dstuser | size |
+---------------------+---------+---------+---------+
| 2006-05-16 23:04:19 | phil | barb | 10294 |
| 2006-05-12 15:02:49 | phil | phil | 1048 |
| 2006-05-15 08:50:57 | phil | phil | 978 |
| 2006-05-14 11:52:17 | phil | tricia | 5781 |
| 2006-05-17 12:49:23 | phil | tricia | 873 |
| 2006-05-14 14:42:21 | barb | barb | 98151 |
| 2006-05-11 10:15:08 | barb | tricia | 58274 |
| 2006-05-13 13:59:18 | barb | tricia | 271 |
| 2006-05-14 09:31:37 | gene | barb | 2291 |
| 2006-05-16 09:00:28 | gene | barb | 613 |
| 2006-05-15 07:17:48 | gene | gene | 3824 |
| 2006-05-15 17:35:31 | gene | gene | 3856 |
| 2006-05-19 22:21:51 | gene | gene | 23992 |
| 2006-05-15 10:25:52 | gene | tricia | 998532 |
| 2006-05-12 12:48:13 | tricia | gene | 194925 |
| 2006-05-14 17:03:01 | tricia | phil | 2394482 |
+---------------------+---------+---------+---------+
16 rows in set (0.00 sec)mysql>
mysql> select t, srcuser, dstuser, size-> from mail-> order by if(srcuser=dstuser, 0, 1), srcuser, dstuser;
+---------------------+---------+---------+---------+
| t | srcuser | dstuser | size |
+---------------------+---------+---------+---------+
| 2006-05-14 14:42:21 | barb | barb | 98151 |
| 2006-05-15 07:17:48 | gene | gene | 3824 |
| 2006-05-15 17:35:31 | gene | gene | 3856 |
| 2006-05-19 22:21:51 | gene | gene | 23992 |
| 2006-05-12 15:02:49 | phil | phil | 1048 |
| 2006-05-15 08:50:57 | phil | phil | 978 |
| 2006-05-11 10:15:08 | barb | tricia | 58274 |
| 2006-05-13 13:59:18 | barb | tricia | 271 |
| 2006-05-14 09:31:37 | gene | barb | 2291 |
| 2006-05-16 09:00:28 | gene | barb | 613 |
| 2006-05-15 10:25:52 | gene | tricia | 998532 |
| 2006-05-16 23:04:19 | phil | barb | 10294 |
| 2006-05-14 11:52:17 | phil | tricia | 5781 |
| 2006-05-17 12:49:23 | phil | tricia | 873 |
| 2006-05-12 12:48:13 | tricia | gene | 194925 |
| 2006-05-14 17:03:01 | tricia | phil | 2394482 |
+---------------------+---------+---------+---------+
16 rows in set (0.00 sec)mysql>
mysql> select t, srcuser, dstuser, size-> from mail-> order by if(srcuser=dstuser, null, srcuser), dstuser;
+---------------------+---------+---------+---------+
| t | srcuser | dstuser | size |
+---------------------+---------+---------+---------+
| 2006-05-14 14:42:21 | barb | barb | 98151 |
| 2006-05-15 07:17:48 | gene | gene | 3824 |
| 2006-05-15 17:35:31 | gene | gene | 3856 |
| 2006-05-19 22:21:51 | gene | gene | 23992 |
| 2006-05-12 15:02:49 | phil | phil | 1048 |
| 2006-05-15 08:50:57 | phil | phil | 978 |
| 2006-05-11 10:15:08 | barb | tricia | 58274 |
| 2006-05-13 13:59:18 | barb | tricia | 271 |
| 2006-05-14 09:31:37 | gene | barb | 2291 |
| 2006-05-16 09:00:28 | gene | barb | 613 |
| 2006-05-15 10:25:52 | gene | tricia | 998532 |
| 2006-05-16 23:04:19 | phil | barb | 10294 |
| 2006-05-14 11:52:17 | phil | tricia | 5781 |
| 2006-05-17 12:49:23 | phil | tricia | 873 |
| 2006-05-12 12:48:13 | tricia | gene | 194925 |
| 2006-05-14 17:03:01 | tricia | phil | 2394482 |
+---------------------+---------+---------+---------+
16 rows in set (0.00 sec)
7.15 按照用户定义排序
mysql> select *, field(name, 'Henry', 'Suzi', 'Ben') from driver_log-> order by field(name, 'Henry', 'Suzi', 'Ben');
+--------+-------+------------+-------+-------------------------------------+
| rec_id | name | trav_date | miles | field(name, 'Henry', 'Suzi', 'Ben') |
+--------+-------+------------+-------+-------------------------------------+
| 3 | Henry | 2014-07-29 | 300 | 1 |
| 4 | Henry | 2014-07-27 | 96 | 1 |
| 6 | Henry | 2014-07-26 | 115 | 1 |
| 8 | Henry | 2014-08-01 | 197 | 1 |
| 10 | Henry | 2014-07-30 | 203 | 1 |
| 2 | Suzi | 2014-07-29 | 391 | 2 |
| 7 | Suzi | 2014-08-02 | 502 | 2 |
| 1 | Ben | 2014-07-30 | 152 | 3 |
| 5 | Ben | 2014-07-29 | 131 | 3 |
| 9 | Ben | 2014-08-02 | 79 | 3 |
+--------+-------+------------+-------+-------------------------------------+
10 rows in set (0.01 sec)mysql>
mysql> select id, description, field(right(id, 2), 'US', 'UK', 'JP', 'SG') from housewares-> order by field(right(id, 2), 'US', 'UK', 'JP', 'SG');
+------------+------------------+---------------------------------------------+
| id | description | field(right(id, 2), 'US', 'UK', 'JP', 'SG') |
+------------+------------------+---------------------------------------------+
| DIN40672US | dining table | 1 |
| BTH00485US | shower stall | 1 |
| KIT00372UK | garbage disposal | 2 |
| KIT01729JP | microwave oven | 3 |
| BTH00415JP | lavatory | 3 |
| BED00038SG | bedside lamp | 4 |
+------------+------------------+---------------------------------------------+
6 rows in set (0.00 sec)
7.16 排序枚举数值
mysql> select * from weekday;
+-----------+
| day |
+-----------+
| Monday |
| Friday |
| Tuesday |
| Sunday |
| Thursday |
| Saturday |
| Wednesday |
+-----------+
7 rows in set (0.00 sec)mysql> select day, day+0 from weekday;
+-----------+-------+
| day | day+0 |
+-----------+-------+
| Monday | 2 |
| Friday | 6 |
| Tuesday | 3 |
| Sunday | 1 |
| Thursday | 5 |
| Saturday | 7 |
| Wednesday | 4 |
+-----------+-------+
7 rows in set (0.00 sec)mysql> select day, day+0 from weekday order by day;
+-----------+-------+
| day | day+0 |
+-----------+-------+
| Sunday | 1 |
| Monday | 2 |
| Tuesday | 3 |
| Wednesday | 4 |
| Thursday | 5 |
| Friday | 6 |
| Saturday | 7 |
+-----------+-------+
7 rows in set (0.00 sec)mysql> select day, day+0 from weekday order by cast(day as char);
+-----------+-------+
| day | day+0 |
+-----------+-------+
| Friday | 6 |
| Monday | 2 |
| Saturday | 7 |
| Sunday | 1 |
| Thursday | 5 |
| Tuesday | 3 |
| Wednesday | 4 |
+-----------+-------+
7 rows in set (0.00 sec)mysql>
mysql> create table color-> (-> name char(10)-> );
Query OK, 0 rows affected (0.04 sec)mysql> insert into color (name)-> values('blue'),('green'),('indigo'),('orange'),('red'),('violet'),('yellow');
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0mysql> select name from color;
+--------+
| name |
+--------+
| blue |
| green |
| indigo |
| orange |
| red |
| violet |
| yellow |
+--------+
7 rows in set (0.00 sec)mysql> select name from color order by name;
+--------+
| name |
+--------+
| blue |
| green |
| indigo |
| orange |
| red |
| violet |
| yellow |
+--------+
7 rows in set (0.00 sec)mysql> select name from color order by field(name, 'red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet');
+--------+
| name |
+--------+
| red |
| orange |
| yellow |
| green |
| blue |
| indigo |
| violet |
+--------+
7 rows in set (0.00 sec)mysql>
mysql> select name, field(name, 'red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet')-> from color-> order by field(name, 'red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet');
+--------+-----------------------------------------------------------------------------+
| name | field(name, 'red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet') |
+--------+-----------------------------------------------------------------------------+
| red | 1 |
| orange | 2 |
| yellow | 3 |
| green | 4 |
| blue | 5 |
| indigo | 6 |
| violet | 7 |
+--------+-----------------------------------------------------------------------------+
7 rows in set (0.00 sec)mysql>
mysql> alter table color-> modify name-> enum('red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet');
Query OK, 7 rows affected (0.10 sec)
Records: 7 Duplicates: 0 Warnings: 0mysql>
mysql> select name from color;
+--------+
| name |
+--------+
| blue |
| green |
| indigo |
| orange |
| red |
| violet |
| yellow |
+--------+
7 rows in set (0.00 sec)mysql> select name from color order by name;
+--------+
| name |
+--------+
| red |
| orange |
| yellow |
| green |
| blue |
| indigo |
| violet |
+--------+
7 rows in set (0.00 sec)