MySQL - like 검색기능

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),
('10% Happier', 'Dan', 'Harris', 2014, 29, 256),
('fake_book', 'Freida', 'Harris', 2001, 287, 428),
('Lincoln In the Bardo', 'George', 'Saunders', 2017, 156, 375);

만약 책 제목에 the가 들어가는 걸 찾고 싶다고 가정 했을때 이렇게 합니다.

select *
from books
where title like 'the';  -- 이럴경우 책 제목이 the인것만 찾기 때문에 아무런 데이터도 나오지 않습니다.

-- 그렇다면 어떻게 해야할까?
select *
from books
where title like '%the%';
-- 이렇게 작성 해주셔야 책 제목에 the가 들어간걸 찾을 수 있다

-- stock_quantity 의 숫자가 두자리인 데이터만 가져오시오. _ 언더스코어로 표시. 문자열에도 적용 가능

select*
from books
where stock_quantity like '__' ;

 

응용 ) 

-- page 수는 100보다 크고, 책 제목에 the가 들어간 책을 가져오되,
-- 재고수량 내림차순으로 데이터를 3개만 가져오시오.

select *
from books
where pages > 100 and title like '%the%'
order by stock_quantity desc
limit 3;