[MySQL] 기본 명령어(2) - Table 에서 정보 검색하기

Intro

---

이번에 MySQL문법을 따르는, table에서 정보를 검색하는 query를 정리하겠습니다.

최근 코딩테스트에서 SQL문제가 나오는 경우가 많은데, 아래 명령어들을 참고해서 문제를 해결하는 연습을 해보면 좋을거 같습니다.

추천 사이트: 프로그래머스

기초 query (제 기준ㅎㅎ)

---

SELECT

1. 기본 형식
   SELECT col_name
   FROM table_name;
2. 중복 제거 검색
   SELECT DISTINCT col_name
3. 개수 검색 ##합: SUM, 평균: AVG
   SELECT COUNT(col_name)
4. UNIQUE값 개수 검색
   SELECT COUNT(DISTINCT col_name)
5. 검색 결과 중 n개만 보기
   Query 마지막에 LIMIT n;
6. NULL값 채우기: IFNULL( , )
   SELECT IFNULL(col_name, 0)

 

 

ORDER BY

1. 기본 형식
   SELECT col_name, column1, column2
   FROM table_name
   ORDER BY
       column1 [ASC|DESC], ##1순위 정렬
       column2 [ASC|DESC], ##2순위 정렬
       ...;
2. 실행 순서
   FROM -> SELECT -> ORDER BY
3. FIELD: 정렬할 때 숫자나, 알파벳 순으로 정렬하지 않고, 정렬 순서를 지정하고 싶을 때 사용
   ORDER BY FILED ([col_name], priority1, priority2, …, );

WHERE

1. 기본 형식
   SELECT lastname, firstname
   FROM employees
   WHERE condition1 (AND/OR condition2 AND/OR condition3 ….)
   ORDER BY firstname;
   A. AND가 OR보다 우선 순위임, OR먼저 하려면 괄호를 사용해야함
2. 실행 순서
   FROM -> WHERE -> SELECT -> ORDER BY
3. BETWEEN - 범위 조건에 사용
   WHERE officeCode BETWEEN 1 AND 3
4. LIKE - %, _을 이용해서 패턴 매칭, %은 문자열 길이 상관 X, _은 1글자만 가능
   WHERE lastName LIKE ‘%son’ ## lastName이 son으로 끝나는 사람 조회
5. IN – membership 조건
   WHERE officeCode IN (1, 2, 3)
6. IS (NOT) NULL – NULL 있(없)는지 확인
   WHERE officeCode IS (NOT) NULL
7. CAST- 형 변환에 사용
   CAST('2003-01-01' AS DATE) ##string to date variable

MIN(), MAX()

1. MIN
   SELECT MIN(Price) AS SmallestPrice
   FROM Products;
2. MAX
   SELECT MAX(Price) AS LargestPrice
   FROM Products;

Aliases (AS)

1. 이름을 바꿀 때 사용, 이때, 바꿀 이름에 띄어쓰기가 있으면 “”이나 []로 감싸야함

고오급 query (제 기준ㅎㅎ)

---

INDEX

1. 역할
   A. DB를 full-scan하지 않고 자주 필요한 정보를 빠르게 찾기 위해서 위치를 저장해 놓음
2. 사용 예시: CREATE INDEX index_category ON book_store(category)
   A. 테이블 book-store의 컬럼 category에 대해서 인덱스 index_category생성
   B. 이후 SELECT * FROM book_store WHERE category = 'java'할 때 full-scan없이 탐색 가능 (category가 ‘java’인 instance에 id를 통해 바로 접근 가능)

JOIN

1. 역할
   A. 공통 컬럼을 기준으로 2개 이상의 테이블의 instance (컬럼까지)를 합치는 연산
2. 사용 예시
   SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
   FROM Orders
   INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
3. INNER JOIN
   A. 대상 테이블들에 공통된 값을 가진 instance들을 합침
   B. 3개 이상의 테이블에 대해서 join을 할 때는, 계산 효율성을 따져서 하나씩 join
4. LEFT (RIGHT) JOIN
   A. 기준 테이블의 모든 instance를 유지한 채로, 매칭되는 값이 있는 다른 테이블의 값을 가져오는 JOIN, 매칭되지 않은 instance의 다른 테이블 컬럼 값은 NULL이 된다.
5. FULL OUTER JOIN
   A. JOIN에 참여하는 모든 테이블의 instance를 합침, 결과가 매우 커질 수 있음
   B. 최소 크기는 JOIN에 참여하는 테이블 중 가장 큰 테이블, 최대는 모든 테이블의 합
6. SELF JOIN
   A. 자기 자신과 하는 JOIN, JOIN을 쓰지 않음
   B. 기본 형태
       SELECT column_name(s)
       FROM table1 T1, table1 T2 ## 같은 테이블에 대한 다른 aliases
       WHERE condition;
   C. 예시
   SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
   FROM Customers A, Customers B
   WHERE A.CustomerID <> B.CustomerID AND A.City = B.City
   ORDER BY A.City;

UNION

1. 여러 개의 SELECT문으로부터 나온 결과의 합집합을 보여주는 명령어 (행 방향 CONCAT)
2. 단, 두 SELECT문의 컬럼 순서가 달라도 무조건 붙여서 보여주기 때문에 주의해야함
3. 기본 예시
   SELECT City, Country FROM Customers
   WHERE Country='Germany'
   UNION ## UNION ALL로 하면 중복 제거 없이 보여줌
   SELECT City, Country FROM Suppliers
   WHERE Country='Germany'
   ORDER BY City;
4. UNION하는 두 결과를 구분하고 싶을 때
   SELECT 'Customer' AS Type, ContactName, City, Country
   FROM Customers
   UNION
   SELECT 'Supplier', ContactName, City, Country
   FROM Suppliers;

DATE_FORMAT

1. 날짜를 지정한 형식으로 출력
2. 예시
   SELECT DATE_FORMAT(NOW(),'%Y-%m-%d') AS DATE
   FROM DUAL

IF

1. IF(조건, ‘참인 경우 출력’, ‘거짓인 경우 출력’)
2. 예시
   SELECT user.seq, IF(user.seq <= 3, 'A', IF(user.seq <=6, 'B', 'C')) AS if_result
   FROM user
3. Case문 변환
   SELECT user.seq,
   CASE
   WHEN (user.seq <= 3) THEN 'A'
   WHEN (user.seq BETWEEN 4 AND 6) THEN 'B'
   ELSE 'C'
4. END AS case_result
   FROM user

GROUP BY

1. The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.
2. 예시 (ShipperName별 주문 수를 확인하기 위한 query)
   SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders
   FROM Orders
   LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
   GROUP BY ShipperName;

HAVING

1. The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. -> aggregated function의 결과에 조건을 명시할 때 사용
2. 예시
   SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
   FROM Orders
   INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
   WHERE LastName = 'Davolio' OR LastName = 'Fuller'
   GROUP BY LastName
   HAVING COUNT(Orders.OrderID) > 25;

ANY, ALL

1. WHERE, HAVING절과 함께 사용
2. ANY
   A. Subquery의 row 중 조건을 만족하는 row가 1개 이상이면 true
   B. 예제
   SELECT ProductName
   FROM Products
   WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);
3. SELECT DISTINCT(ProductName)
   FROM Products
   INNER JOIN OrderDetails ON Products.ProductID = OrderDetails.ProductID
   WHERE OrderDetails.Quantity = 10;
4. ALL
   A. Subquery의 모든 row가 조건을 만족하면 true

WITH (CTE, Common Table Expressions)

1. Single statement의 범위 안에서 존재하는 임시 결과 set
2. CTE사용을 위해선 WITH 명령어를 함께 사용, ‘,’로 구분해서 세부 내용 채우기
   A. 기본 형태
   WITH [RECURSIVE]
   cte_name [(col_name [, col_name] ...)] AS (subquery)
   [, cte_name [(col_name [, col_name] ...)] AS (subquery)]
3. i. Recursive는 여러 cte중 하나라도 recursive인게 (cte가 자기 자신 참고) 있으면 명시해야함
   B. 예시
   WITH
   cte1 AS (SELECT a, b FROM table1),
   cte2 AS (SELECT c, d FROM table2)
   SELECT b, d FROM cte1 JOIN cte2
   WHERE cte1.a = cte2.c;
   cte_name [(col_name [, col_name] ...)] AS (subquery)
   i. 각 CTE (cte1, cte2)는 ; 이전의 어디에서나 reference될 수
4. RECURSIVE CTE
   A. 예시
   WITH RECURSIVE cte (n) AS (
   SELECT 1
   UNION ALL # 행방향 concat
   SELECT n + 1 FROM cte WHERE n < 5
   )
   SELECT * FROM cte;

마무리

---

다음에는 본격적인, TABLE에서 정보를 추출하기 위한 Query에 대해서 정리하겠습니다!

References

w3school
carami님 블로그