MySQL_section8_Refining our selections

#1. DISTINCT : Select unique data
::SELECT DISTINCT <컬럼명> FROM <테이블명>;
EX1. SELECT author_lname FROM books;
EX2. SELECT DISTINCT author_lname FROM books;
EX3. SELECT DISTINCT CONCAT(author_fname, ' ', author_lname) FROM books;
EX4. SELECT DISTINCT author_fname, author_lname FROM books;

 

#2. ORDER BY : 정렬
::SELECT <컬럼명> FROM <테이블명> ORDER BY <정렬 기준 컬럼명>;
::SELECT <컬럼명> FROM <테이블명> ORDER BY <정렬 기준 컬럼명> DESC; #내림차순
::SELECT <컬럼명> FROM <테이블명> ORDER BY <정렬 기준 컬럼명> ASC; #오름차순
::SELECT <컬럼명1, 컬럼명2, 컬럼명3,...> FROM <테이블명> ORDER BY <정렬 기준 컬럼 순번>;
EX1. SELECT author_lname FROM books ORDER BY author_lname;
EX2. SELECT author_lname FROM books ORDER BY author_lname DESC;
EX3. SELECT released_year FROM books ORDER BY released_year ASC;
EX4. SELECT title, author_lname, author_fname FROM books ORDER BY 2;
EX5. SELECT title, author_lname, author_fname FROM books ORDER BY 2 DESC;
EX6. SELECT author_fname, author_lname FROM books ORDER BY author_lname, author_fname;

 

#3. LIMIT : python의 head()와 같음
::SELECT <컬럼명> FROM <테이블명> LIMIT <보고싶은 행만큼의 숫자>;
EX1. SELECT title FROM books LIMIT 3;
EX2. SELECT title, released_year FROM books ORDER BY released_year DESC LIMIT 3;
EX3. SELECT title, released_year FROM books ORDER BY released_year DESC LIMIT 14;
EX4. SELECT title, released_year FROM books ORDER BY released_year DESC LIMIT 0, 3; #0행부터 행 3개를 보여줘
EX5. SELECT title, released_year FROM books ORDER BY released_year DESC LIMIT 1, 3; #1행부터 행 3개를 보여줘
EX6. SELECT title, released_year FROM books ORDER BY released_year DESC LIMIT 10, 1; #10행 하나만 보여줘
EX6. SELECT title FROM books LIMIT 5, 18798718797898; #5행부터 끝까지 보여줘

 

#4. LIKE : search..python의 정규표현식이랑 비슷함
::SELECT <컬럼명> FROM <테이블명> WHERE <컬럼명> LIKE <조건>;
::LIKE '%' #find everything
::LIKE '__' #find two digit
::LIKE '___' #find three digit
::LIKE '%\_%%' # \ 뒤에 있는 것을 문자로 인식
::LIKE '%_%%' # fine everything
EX1. SELECT title, author_fname FROM books WHERE author_fname LIKE '%da%'; #author_fname에서 da가 들어간걸 찾아줘
EX2. SELECT title, author_fname FROM books WHERE author_fname LIKE 'da%'; #author_fname에서 da로 시작하는걸 찾아줘
EX3. SELECT title FROM books WHERE title LIKE 'the'; #title이 the인걸 찾아줘
EX4. SELECT title FROM books WHERE title LIKE '%the'; #title에서 the로 끝나는걸 찾아줘
EX5. SELECT title FROM books WHERE title LIKE '%the%'; #title에서 the가 들어간걸 찾아줘
EX6. SELECT title FROM books WHERE title LIKE 'the%'; #title에서 the로 시작되는걸 찾아줘
EX7. SELECT title, stock_quantity FROM books WHERE stock_quantity LIKE '%';
EX8. SELECT title, stock_quantity FROM books WHERE stock_quantity LIKE '__';
EX9. SELECT title, stock_quantity FROM books WHERE stock_quantity LIKE '___';
EX10. SELECT title FROM books WHERE title LIKE '%\_%%'; # \ 뒤에 있는 것을 문자로 인식
EX11. SELECT title FROM books WHERE title LIKE 'fake_book';
EX12. SELECT title FROM books WHERE title LIKE '%_%%'; #find all
EX13. SELECT title FROM books WHERE title LIKE '%';

 

#5. Excercise

 

(1) Select all story collections : titles that contain 'stories'
 SELECT title FROM books WHERE title LIKE '%stories%';

 

(2) Find the Longest book : Print out the title and page count
 SELECT `title`, `pages` FROM books ORDER BY `pages` DESC LIMIT 1;

 

(3) Print a summary containing the title and year, for the 3 most recent books
 SELECT CONCAT(title, '-', released_year) AS 'summary' FROM books ORDER BY `released_year` DESC LIMIT 3;

 

(4) Find all books with an author_lname that contains a space(" ")
 SELECT title, author_lname FROM books WHERE author_lname LIKE '% %';

 

(5) Find the 3 books with the lowest stock
 SELECT title, released_year, stock_quantity FROM books ORDER BY stock_quantity ASC, title LIMIT 3;

 

(6) Print title and author_lname, sorted first by author_lname and then by title
 SELECT title, author_lname FROM books ORDER BY author_lname, title;
 SELECT title, author_lname FROM books ORDER BY 2, 1;

 

(7) Sorted alphabetically by last name
 SELECT CONCAT('My FAVORITE AUTHOR IS ', UPPER(author_fname), ' ', UPPER(author_lname), '!') AS 'yell' FROM books ORDER BY author_lname;