MySQL 오름차순, 내림차순 정렬: 'Order by'

1. 테이블 생성

 

create table books (
book_id int not null  auto_increment,
title varchar(100),
author_fname varchar(100),
author_lname varchar(100),
released_year int,
stock_quantity int ,
pages int ,
primary key (book_id)
);

2. 데이터 삽입

 

INSERT INTO books (title, author_fname, author_lname, released_year, stock_quantity, pages)
VALUES
('The Namesake', 'Jhumpa', 'Lahiri', 2003, 32, 291),
('Norse Mythology', 'Neil', 'Gaiman',2016, 43, 304),
('American Gods', 'Neil', 'Gaiman', 2001, 12, 465),
('Interpreter of Maladies', 'Jhumpa', 'Lahiri', 1996, 97, 198),
('A Hologram for the King: A Novel', 'Dave', 'Eggers', 2012, 154, 352),
('The Circle', 'Dave', 'Eggers', 2013, 26, 504),
('The Amazing Adventures of Kavalier & Clay', 'Michael', 'Chabon', 2000, 68, 634),
('Just Kids', 'Patti', 'Smith', 2010, 55, 304),
('A Heartbreaking Work of Staggering Genius', 'Dave', 'Eggers', 2001, 104, 437),
('Coraline', 'Neil', 'Gaiman', 2003, 100, 208),
('What We Talk About When We Talk About Love: Stories', 'Raymond', 'Carver', 1981, 23, 176),
("Where I'm Calling From: Selected Stories", 'Raymond', 'Carver', 1989, 12, 526),
('White Noise', 'Don', 'DeLillo', 1985, 49, 320),
('Cannery Row', 'John', 'Steinbeck', 1945, 95, 181),
('Oblivion: Stories', 'David', 'Foster Wallace', 2004, 172, 329),
('Consider the Lobster', 'David', 'Foster Wallace', 2005, 92, 343);

 

3. 테이블 확인

select * from books;

실행결과

4. title 컬럼을 기준으로  오름차순 정렬 - order by 컬럼명; 

select *
from books
order by title;

실행결과

5. title 컬럼을 기준으로 내림차순 정렬 - order by 컬럼명 desc

select *
from books
order by title desc;

실행결과

reference 참고

dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html

MySQL :: MySQL 8.0 Reference Manual :: 8.2.1.16 ORDER BY Optimization