mysql排序查询结果---
--建表
drop table if exists driver_log;
create table driver_log
(
rec_id int unsigned not null auto_incerment,
name varchar(20) not null,
trav_date date not null,
miles int not null,
primary key (rec_id)
);
--初始化数据
insert into driver_log (name,trav_date,miles)
values
('Ben','2006-08-30',152),
('Suzi','2006-08-29',391),
('Henry','2006-08-29',300),
('Henry','2006-08-27',96),
('Ben','2006-08-29',131),
('Henry','2006-08-26',115),
('Suzi','2006-09-02',502),
('Henry','2006-09-01',197),
('Ben','2006-09-02',79),
('Henry','2006-08-30',203)
;
--查看数据
select * from driver_log;
select * from mail;
----------------------使用order by命令排序查询结果-------------------------
select * from driver_log order by name;
select * from driver_log order by name asc;
select * from driver_log order by name desc;
select * from driver_log order by name, trav_date;
select * from driver_log order by name desc, trav_date desc;
select * from driver_log order by name desc, trav_date;
select name, trav_date, miles as distance from driver_log order by distance;
select name, trav_date, miles as distance from driver_log order by distance desc;
----------------------使用表达式排序-------------------------
select * from mail;
select t, srcuser, floor((size+1023)/1024)
from mail
where size > 50000
order by floor((size+1023)/1024);
select t, srcuser, floor((size+1023)/1024) as kilobytes
from mail
where size > 50000
order by kilobytes;
----------------------显示一组按照其他属性排序的值-------------------------
select t, srcuser, concat(floor((size+1023)/1024), 'K') as size_in_k
from mail where size > 50000
order by size_in_k;
select t, srcuser, concat(floor((size+1023)/1024), 'K') as size_in_k
from mail where size > 50000
order by size;
--建表
drop table if exists roster;
create table roster
(
name CHAR(30), # player name
jersey_num CHAR(3) # jersey number
);
--初始化表
insert into roster (name, jersey_num)
values
('Lynne','29'),
('Ella','0'),
('Elizabeth','100'),
('Nancy','00'),
('Jean','8'),
('Sherry','47')
;
select name, jersey_num from roster;
select name, jersey_num from roster order by jersey_num;
select name, jersey_num from roster order by jersey_num+0;
select t, concat(srcuser, '@', srchost) as sender, size
from mail where size > 50000
order by srchost, srcuser;
--建表
drop table if exists name;
create table name
(
last_name char(20),
first_name char(20)
);
--初始化数据
insert into name (first_name,last_name) VALUES('Kevin','Brown');
insert into name (first_name,last_name) VALUES('Vida','Blue');
insert into name (first_name,last_name) VALUES('Pete','Gray');
insert into name (first_name,last_name) VALUES('Devon','White');
insert into name (first_name,last_name) VALUES('Rondell','White');
select last_name, first_name from name order by last_name, first_name;
select concat(first_name, ' ', last_name) as full_name from name order by last_name, first_name;
----------------------字符串排序的大小写区分控制-------------------------
--建表
drop table if exists str_val;
create table str_val
(
ci_str char(3) character set latin1 collate latin1_swedish_ci,
cs_str char(3) character set latin1 collate latin1_general_cs,
bin_str binary(3)
);
--初始化数据
insert into str_val (ci_str,cs_str,bin_str) VALUES
('AAA','AAA','AAA'),
('aaa','aaa','aaa'),
('bbb','bbb','bbb'),
('BBB','BBB','BBB');
select * from str_val;
select * from str_val order by ci_str;
select * from str_val order by cs_str;
select * from str_val order by bin_str;
select ci_str from str_val order by ci_str;
select cs_str from str_val order by cs_str;
select bin_str from str_val order by bin_str;
select ci_str from str_val order by ci_str collate latin1_general_cs;
select cs_str from str_val order by cs_str collate latin1_swedish_ci;
select cs_str from str_val order by upper(cs_str);
select bin_str from str_val order by convert(bin_str using latin1) collate latin1_swedish_ci;
----------------------基于日期的排序-------------------------
--建表
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')
;
select * from temporal_val；
select * from temporal_val order by d;
select * from temporal_val order by dt;
select * from temporal_val order by t;
select * from temporal_val order by ts;
----------------------按日历排序-------------------------
--建表
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')
;
select date, description from event;
select date, description from event order by date;
select date, description from event order by month(date), dayofmonth(date);
select date, description from event order by dayofyear(date);
select dayofyear('1996-02-29'), dayofyear('1997-03-01');
----------------------按周历排序-------------------------
select dayname(date) as day, date, description
from event
order by dayofweek(date);
select dayname(date), date, description
from event
order by mod(dayofweek(date)+5, 7);
----------------------按时钟排序-------------------------
select * from mail order by hour(t), minute(t), second(t);
select *, time_to_sec(t) from mail order by time_to_sec(t);
----------------------按数据列的字串排序-------------------------
--创建表、初始化数据
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')
;
select * from housewares2;
drop table if exists housewares3;
create table housewares3
(
id VARCHAR(20),
description VARCHAR(255)
);
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')
;
select * from housewares3;
drop table if exists hw_category;
create table hw_category
(
abbrev VARCHAR(3),
name VARCHAR(20)
);
insert into hw_category (abbrev,name)
values
('DIN', 'dining'),
('KIT', 'kitchen'),
('BTH', 'bathroom'),
('BED', 'bedroom')
;
select * from hw_category;
--按固定长度的字串排序
select * from housewares;
select * from housewares order by id;
select id, left(id, 3) as category, mid(id, 4, 5) as serial, right(id, 2) as country from housewares;
select * from housewares order by left(id, 3);
select * from housewares order by mid(id, 4, 5);
select * from housewares order by right(id, 2);
select * from housewares order by right(id, 2), mid(id, 4, 5);
--按可变长度的字串排序
select * from housewares2;
select id, left(substring(id, 4), char_length(substring(id, 4))-2) from housewares2;
select id, substring(id, 4), substring(id, 4, char_length(id)-5) from housewares2;
select * from housewares2 order by substring(id, 4, char_length(id)-5);
select * from housewares2 order by substring(id, 4, char_length(id)-5);
select * from housewares2 order by substring(id, 4, char_length(id)-5)+0;
select *, substring(id, 4, char_length(id)-5)+0 from housewares2 order by substring(id, 4, char_length(id)-5)+0;
select * from housewares2 order by substring(id, 4)+0;
select * from housewares3;
select id, substring_index(substring_index(id, '-', 2), '-', -1) as segment2,
substring_index(substring_index(id, '-', 4), '-', -1) as segment4
from housewares3;
select * from housewares3 order by substring_index(substring_index(id, '-', 2), '-', -1);
select * from housewares3 order by substring_index(substring_index(id, '-', 2), '-', -1)+0;
----------------------按域名顺序排列主机名-------------------------
--建表
drop table if exists hostname;
create table hostname
(
name varchar(64)
);
--初始化数据
insert into hostname (name)
values
('cvs.php.net'),
('dbi.perl.org'),
('lists.mysql.com'),
('mysql.com'),
('jakarta.apache.org'),
('www.kitebird.com')
;
select * from hostname;
select name from hostname order by name;
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;
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;
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;
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);
----------------------按照数字顺序排序点分式主IP地址-------------------------
--建表
drop table if exists hostip;
create table hostip
(
ip varchar(64)
);
--初始化数据
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')
;
select * from hostip ORDER BY ip;
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;
select ip from hostip order by inet_aton(ip);
select ip, ip+0 from hostip;
----------------------将数值移动到排序结果的头部或尾部-------------------------
select null = null;
--建表
drop table if exists t;
create table t (
val varchar(64)
);
--初始化数据
insert into t (val)
values
(3),
(100),
(null),
(null),
(9)
;
select val from t;
select val from t order by val;
select val from t order by if(val is null, 1, 0), val;
select t, srcuser, dstuser, size
from mail
order by if(srcuser='phil', 0, 1), srcuser, dstuser;
select t, srcuser, dstuser, size
from mail
order by if(srcuser=dstuser, 0, 1), srcuser, dstuser;
select t, srcuser, dstuser, size
from mail
order by if(srcuser=dstuser, null, srcuser), dstuser;
----------------------按照用户定义排序-------------------------
select *, field(name, 'Henry', 'Suzi', 'Ben') from driver_log
order by field(name, 'Henry', 'Suzi', 'Ben');
select id, description, field(right(id, 2), 'US', 'UK', 'JP', 'SG') from housewares
order by field(right(id, 2), 'US', 'UK', 'JP', 'SG');
----------------------排序枚举数值-------------------------
--创建表
drop table id exists weekday;
create table weekday
(
day enum('Sunday','Monday','Tuesday','Wednesday',
'Thursday','Friday','Saturday')
);
--初始化数据
insert into weekday (day) values('Monday'),('Friday'),
('Tuesday'),('Sunday'),('Thursday'),('Saturday'),('Wednesday');
select * from weekday;
select day, day+0 from weekday;
select day, day+0 from weekday order by day;
select day, day+0 from weekday order by cast(day as char);
--创建表
drop table if exists color;
create table color
(
name cahr(10)
);
insert into color (name)
values('blue'),('green'),('indigo'),('orange'),('red'),('violet'),('yellow');
select name from color;
select name from color order by name;
select name from color order by field(name, 'red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet');
select name, field(name, 'red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet')
from color
order by field(name, 'red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet');
alter table color
modify name
enum('red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet');
select name from color;
select name from color order by name;