1. SQL processes AND before OR

• A OR B AND C와 (A OR B) AND C는 다르다.
• A or B and C는 B AND C가 먼저 실행된다(AND가 OR보다 먼저 실행된다.)

2. Wildcard

Like연산자를 통해 특정 패턴과 매칭되는 데이터를 찾을 수 있다. 이때 문자열을 대체하기 위해 쓰이는 것을 wiildcard라고 한다.

• %는 0개 이상의 문자열을 대체하기 위해 쓰인다.
• _는 1개의 문자열을 대체하기 위해 쓰인다.

SELCT * FROM users WHERE name='김%'; -> 이름이 김으로 시작하는 유저 찾기
SELECT * FROM users WHERE name='이_'; -> 이름이 이로 시작하면서 외자인 유저 찾기

• Wildcard를 쓰면 쿼리 속도가 느리다.
• 따라서 가능하다면 =, <, >등의 연산을 쓰는 것이 좋다.
• Statements with wildcards will take longer to run if used at the end of search patterns

3. order by

• 항상 SELECT 문의 맨 뒤에 와야한다.
• ORDER BY 1, ORDER BY 1, 2 이런 식으로 컬럼 명 대신 컬럼의 순서를 명시해 order by를 할 수 있다.

4. Order of Operations (연산 실행 순서)

• Parentheses(괄호)
• Exponents (지수)
• Multiplication (*)
• Division (/)
• Addition (+)
• Subtraction (-)

"Please excuse my dear Aunt Sally"

5. Aggregate Functions

• AVG(): Averages a column of values
• COUNT(): Counts the number of values
• MIN(): Finds the minumum value. NULL값은 무시된다.
• MAX(): Finds the maximum value. NULL값은 무시된다.
• SUM(): Sums the column values

SELECT COUNT(*) FROM users : users 테이블의 총 row수를 구한다. NULL value까지 포함한다.
SELECT COUNT(column) FROM users: users테이블의 특정 컬럼 수를 구한다. NULL value는 포함되지 않는다.

6. Group by

• GROUP BY 절에 여러 column이 올 수 있다.

• SELECT statement에 오는 모든 컬럼은 GROUP BY caluse에 명시되어야 한다.(aggregate관련 컬럼 제외)

• NULL값이 포함된 컬럼으로 GROUP BY를 하면 NULL끼리 그룹화된다.

• GROUP BY 에서 filtering을 할 땐 'WHERE'이 아닌 'HAVING'을 사용한다.

• WHERE은 그룹핑 되기 전에 실행되고, HAVING은 그룹핑 된 이후에 실행된다.

• Rows eliminated by the WHERE clause will not be a included in the group

• 팁: GROUP BY할 땐 ORDER BY를 같이 쓰는게 good practice다.

• 예시

SELECT
CoustomerID
, COUNT (*) AS orders
FROM Orders
GROUP BY CustomerID
HAVING COUNT (*) >=2;

7. Key SQL Clauses

3. Subqueries, Join

1. Subquries

• 항상 innermost SELECT문이 먼저 실행된다. subquery가 메인쿼리보다 먼저 실행된다는 뜻.
• subquery 개수에는 제한이 없다.
• 하지만 서브쿼리를 너무 많이 쓰면 성능 저하가 생긴다.
• 서브쿼리는 컬럼 1개만 리턴할 수 있다.
• 서브쿼리 사용시 indent를 써서 가독성을 높여주자.
• indent 신경 안쓴 안좋은 예시
  
• indent 좋은 예시
  

2. Cartesian(Cross) Joins

• 자주 사용 되지 않음
• Computationally taxing(비싼 연산)
• a 테이블의 row 과 b 테이블의 row를 단순하게 곱한다.
• 예시
  

3. Inner Joins

4. Self Joins

• 동일한 테이블을 조인함.
• 반드시 Alias를 사용해야 함.
• 문법
  

5. Left join

• 아래 쿼리문을 치면 모든 user가 출력되고, 그 옆에 order_number가 있다면 해당 값이, 없다면 NULL값이 출력된다.

SELECT u.username, o.order_number FROM users u LEFT JOIN orders o ON u.id=o.user_id;

6. Full Outer Joins

• mysql은 Full Outer Joins 지원 안함.
• left, right을 union할 것.

7. Union

• 2개 이상의 SELECT statement의 결과를 합치는 것.
• 각 SELECTX statement는 같은 컬럼 수를 가져야 하고, 같은 순서로 존재해야 하며, similar한 data type이어야 한다.
  

8. Inner, Left, Right, Full Outer joins 정리

• users테이블에서 username과 orders테이블에서 order_number를 추출한다고 하자.
• username이 없는 유저도 있고, 주문한 적이 없어서 order_number가 없는 유저도 있다고 치자.
• Inner Join은 username, order_number 둘 다 있는 데이터만 추출
• Left Join(from users Join orders)은 모든 username을 추출하고, order_number 값이 있다면 해당 값을, 없다면 NULL을 추출한다.
• Right Join은 Left Join에서 테이블 순서만 바뀐 형태다.
• Full Outer Joins는 NULL이 있든 없든 모든 것을 전부 다 출력한다.
  

4. Modifing and analyzing data

string

1. concatenate

2. substring

3. trim

4. upper, lower

datetime

"As long as your data contains only the date portion, your queries will work as expected. However, if a time portion is involved, it gets more complicated."

"The most difficult part when working with dates is to be sure that the format of the date you are trying to insert, matches the format of the date column in the database."

• datetime 형태는 DB종류에 따라서 다르다. 잘 확인해야 한다.

• DATE
  - Format: YYYY-MM-DD
• DATETIME
  - Format: YYYY-MM-DD HH:MI:SS
• TIMESTAMP
  - Format: YYYY-MM-DD HH:MI:SS
  

-SQLite date time funcions

• timestrings
  

strftime

• datetime데이터에서 년, 월, 일 등 원하는 데이터만 뽑는다.
• extract certain elements of a date/time string.
• 아래 예시는 sqlite버전이다.

SELECT Birthdate, STRFTIME('%Y', Birthdate) as Year from employees

• mysql에는 STRFTIME은 없고 대신 DATE_FORMAT이 있다.
  

now

• 현재 시간 출력하기
• sqlite 버전
  SELECT DATE('now')
SELECT STRFTIME('%Y %m %d', 'now'): 현재 년,월,일 출력
  SELECT STRFTIME('%H %M %S %s', 'now): 현재시간 milliseconds까지 뽑기
• mysql 버전
  SELECT NOW();
  

case

• mysql에서 CASE WHEN 사용해보기 결과

SELECT
id, gender, 
CASE WHEN gender='여성' THEN 'F'
WHEN gender='남성' THEN 'M'
ELSE 'other'
END gender_eng
FROM users
LIMIT 10;

Views

• A stored query
• can add or remove columns without changing schema
• use it to encapsulate queries
• the view will be removed after database connection has ended
• SQLite create view syntax

CREATE [TEMP] VIEW [IF NOT EXISTS]
view_name(column-name-list) 
AS select-statement```

• SELECT * FROM view_name: view에서 데이터 select
• DROP VIEW view_name: view 지우기

https://blog.sqlauthority.com/category/sql-puzzle/
https://sqlzoo.net/