[My SQL] Logical Operators

1. NOT Equal : !=

SELECT title, released_year FROM books WHERE released_year != 2017;

SELECT title, author_lname FROM books WHERE author_lname != 'Harris';

2. NOT LIKE

ex. w-로 시작하는 단어가 들어가 있는 제목 제외하고 모두 출력

SELECT title FROM books WHERE title NOT LIKE 'w%';

3. >, >=, <, <=

SELECT title, stock_quantity FROM books WHERE stock_quantity >= 100;

4. Logical AND

Condition1 AND Condition2
---> both sides must be true.

SELECT title, author_lname, released_year 
FROM books 
WHERE author_lname = 'Eggers' AND 
	  released_year > 2010
ORDER BY released_year;

5. Logical OR

Condition1 OR Condition2
---> Either one side must be true.

SELECT title, author_lname, released_year 
FROM books 
WHERE author_lname = 'Eggers' OR 
	  released_year > 2010
ORDER BY released_year;

6. BETWEEN X AND Y

• Select the books published in between 2004 and 2015

<,>를 이용:

SELECT title, released_year FROM books
WHERE released_year >= 2004 AND
      released_year <= 2015;

BETWEEN 이용:

SELECT title, released_year FROM books
WHERE released_year BETWEEN 2004 AND 2015;

• NOT BETWEEN
  : BETWEEN 의 반대 데이터 출력

SELECT title, released_year FROM books
WHERE released_year NOT BETWEEN 2004 AND 2015;

7. IN & NOT IN

여러개의 항목을 선택하는 경우 OR을 이용하여 코드를 짤 수 있으나,

SELECT title, author_lname FROM books
WHERE author_lname = 'Carver' OR 
      author_lname = 'Lahiri' OR
      author_lname = 'Smith';

IN()을 사용하면 생성 및 수정이 수월해진다.

SELECT title, author_lname FROM books 
WHERE author_lname IN ('Carver', 'Lahiri', 'Smith');

숫자도 가능
짝수 연도를 제외하고 출력

SELECT title, author_lname, released_year FROM books 
WHERE released_year >= 2000 AND
      released_year NOT IN (2000,2002,2004,2006,2008,2010,2012,2014,2016);

++ 위 코드에 대한 응용버전 : modulo 연산을 이용하여 짝수 연도를 제외

SELECT title, author_lname, released_year FROM books 
WHERE released_year >= 2000 AND
      released_year % 2 != 0;

8. CASE STATEMENT

datatype 내용을 조건에 따라 나누어 출력하는 방법

SELECT title, stock_quantity,
       CASE
        WHEN stock_quantity <= 50 THEN '*'
        WHEN stock_quantity <= 100 THEN '**'
        ELSE "***"
       END AS STOCK
FROM books;

SELECT title, author_lname, 
              CASE
               WHEN COUNT(*) = 1 THEN '1 book'
               ELSE CONCAT(COUNT(*), ' ', 'books')
              END AS 'COUNT'
FROM books
GROUP BY author_lname, author_fname;