MySQL - 선택사항 정교화하기

MySQL 선택사항 정교화하기

DISTINCT

중복사항 제거

SELECT DISTINCT column FROM <table-name>;
ex) SELECT DISTINCT title FROM books;

ORDER BY

정렬하기(기본: 오름차순)

- 오름차순
SELECT column FROM <table-name> ORDER BY column (ASC);
ex) SELECT title FROM books ORDER BY title (ASC);
ex) SELECT title, author_fname, author_lname FROM books ORDER BY 2(=author_fname);

or

- 내림차순
SELECT column FROM <table-name> ORDER BY column DESC;
ex) SELECT title FROM books ORDER BY title DESC;

LIMIT

반환되는 결과의 수 조절

SELECT column FROM <table-name> LIMIT number;
ex) SELECT title, page FROM books LIMIT 5; // 5개만

ex) SELECT title, page FROM books LIMIT 1,5; // 1번 다음 행부터 5개

LIKE

비슷한 결과물 검색

SELECT column FROM <table-name> WHERE column LIKE '%value%';
ex) SELECT title, author_fname, author_lname FROM books WHERE author_fname LIKE '%da%' // da가 들어간 단어 반환

SELECT column FROM <table-name> WHERE column LIKE '____'; // '_ = 1글자'
ex) SELECT * FROM books WHERE author_fname LIKE '____'; // 4글자 author_fname 반환

SELECT column FROM <table-name> WHERE column LIKE '%'; // '% = 0개 이상의 문자'

---

논리 연산자

!= (Not Equl)

같지 않다

SELECT column FROM <table-name> WHERE column != 조건;
ex) SELECT title FROM books WHERE released_year != 2017; // released_year가 2017이 아닌

NOT LIKE

같지 않다

SELECT column FROM <table-name> WHERE column NOT LIKE 조건
ex) SELECT title FROM books WHERE title NOT LIKE '% %'; // title에 ' '(공백)이 없는

> / < (Greater Than/ Less Than)

~보다 크다/~보다 작다

SELECT column FROM <table-name> WHERE column > / < 조건;
ex) SELECT title FROM books WHERE released_year > / < 2017; // released_year가 2017 보다 큰/보다 작은

>= / <= (Greater Than or Equal To/ Less Than or Equal To)

~보다 같거나 크다/~보다 같거나 작다

SELECT column FROM <table-name> WHERE column >= / <= 조건;
ex) SELECT title FROM books WHERE released_year >= / <= 2017; // released_year가 2017과 같거나 큰/같거나 작은

&& (And)

그리고

SELECT column FROM <table-name>  WHERE column의 조건1 &&(And) column의 조건2;
ex) SELECT title FROM books WHERE author_lname = 'Eggers' && released_year > 2010; // author_lname이 'Eggers'이고, released_year가 2010인

|| (Or)

또는

SELECT column FROM <table-name>  WHERE column의 조건1 ||(Or) column의 조건2;
ex) SELECT title FROM books WHERE author_lname = 'Eggers' || released_year > 2010; // author_lname이 'Eggers'이거나, released_year가 2010인

BETWEEN condition1 AND condition2

~사이

SELECT column FROM <table-name> WHERE column BETWEEN column의 조건1 AND column의 조건2;
ex) SELECT title, released_year FROM books WHERE released_year BETWEEN 2004 AND 2015; // released_year가 2004 ~ 2015 사이인

NOT BETWEEN condition1 AND condition2

~사이를 제외

SELECT column FROM <table-name> WHERE column NOT BETWEEN column의 조건1 AND column의 조건2;
ex) SELECT title, released_year FROM books WHERE released_year NOT BETWEEN 2004 AND 2015; // released_year가 2004 미만, 2015  초과

IN

|| 연산자를 통한 여러가지 값을 축약

SELECT column FROM <table-name> WHERE column IN (value1, value2, value3);
ex) SELECT author_lname FROM books WHERE author_lname = 'Carver' || author_lname = 'Lahiri';
>>	SELECT author_lname FROM books WHERE author_lname IN ('Carver', 'Lahiri');

NOT IN

|| 연산자를 통한 여러가지 값을 축약의 제외

SELECT column FROM <table-name> WHERE column NOT IN (value1, value2, value3);

ex) SELECT author_lname FROM books WHERE author_lname != 'Carver' && author_lname != 'Lahiri';
>>	SELECT author_lname FROM books WHERE author_lname NOT IN ('Carver', 'Lahiri');

%

나머지

SELECT column FROM <table-name> WHERE column % 2 = 0;
ex) SELECT released_year FROM books WHERE released_year % 2 = 0; // released_year를 2로 나눈 나머지가 0인 (짝수)

CASE

SELECT column,
	CASE 
    	WHEN column의 조건 THEN value1
        ELSE value2
    END AS value_name
FROM <table-name>;
ex) SELECT title, stock_quantity,
    	CASE
    		WHEN stock_quantity BETWEEN 0 AND 40 THEN '*'
    		WHEN stock_quantity BETWEEN 41 AND 70 THEN '**'
    		WHEN stock_quantity BETWEEN 71 AND 100 THEN '***'
    		WHEN stock_quantity BETWEEN 101 AND 140 THEN '****'
    	ELSE '*****'
        END AS stock
	 FROM books;

'BETWEEN 0 AND 40' = '<=40';
'BETWEEN 41 AND 70' = '<=70';
'BETWEEN 71 AND 100 ' = '<=100';
'BETWEEN 101 AND 140' = '<=140';

IS NULL

NULL값 찾기

SELECT column FROM <table-name> WHERE column IS NULL;
ex) SELECT * FROM books WHERE author_lname IS NULL; // NULL인 값 반환

IS NOT NULL

NULL이 아닌 값 찾기

SELECT column FROM <table-name> WHERE column IS NOT NULL;
ex) SELECT * FROM books WHERE author_lname IS NOT NULL; // NULL이 아닌 값 반환