SQL이 포함된 코테를 보게 되어 MySQL도 정리해보고자 한다...!
쿼리 기본 작성 순서
SQL 코딩테스트는 일반적으로 주어진 데이터 테이블에서 원하는 자료를 선택하여 출력하는 것을 요구한다.
이를 위해서는 SQL의 여러 명령어 중 SELECT를 사용해 쿼리를 만들면 된다. 다음은 쿼리의 기본 구조를 나타낸다.
SELECT [ALL | DISTINCT] [column list]
FROM [table list]
WHERE [where_condition]
GROUP BY [col_name | expr | position]
HAVING [where_condition]
ORDER BY [col_name | expr | position] [ASC | DESC]
LIMIT {[offset,] row_count | row_count OFFSET offset}];SELECT와FROM까지는 필수이고, 그 이후는 필요에 따라 사용한다.SELECT뒤에는 내가 보고 싶은 열을 적는다. 이때 중복을 제거하고 싶다면 명시적으로DISTINCT를 앞에 적는다.FROM뒤에는 원하는 정보를 얻는데 필요한 테이블을 적는다.WHERE뒤에는 행 선택 조건을 작성한다.GROUP BY는 특정 열을 기준으로 행의 그룹을 생성하는데 사용한다.HAVING은 그룹 생성 이후 그룹을 선택하는 조건을 나타낸다. 따라서 GROUP BY가 존재해야 HAVING을 사용할 수 있다.ORDER BY는 특정 열의 값에 따라 행을 특정 순서로 정렬하는 데 사용한다. 기본은 오름차순이며, 내림차순을 원하면 각 열 뒤에 명시적으로DESC를 적어야 한다.LIMIT은 출력할 행의 개수를 명시적으로 적는 부분이다.
SELECT-FROM-WHERE 예시
가장 기초적으로 테이블에서(FROM) 조건에 맞는 행을 선택하고(WHERE) 특정 열을 출력하는(SELECT) 예시를 살펴보자.
INSTRUCTOR테이블에서 DEPT-NAME이 PHYSICS이고, SALARY가 8만 이상인 행의 NAME을 얻기
SELECT NAME
FROM INSTRUCTOR
WHERE DEPT_NAME = 'PHYSICS' AND SALARY >= 80000;INSTRUCTOR테이블의 ID와TEACHES테이블의 ID가 같으면서 YEAR이 2010인 행의 NAME과 COURSE_ID 얻기
SELECT NAME, COURSE_ID
FROM INSTRUCTOR, TEACHES
WHERE INSTRUCTOR.ID = TEACHES.ID AND YEAR = 2010;위 예시에서는 두 개의 테이블이 사용되었는데, 이렇게 단순히 콤마(,)로 작성해줘도 알아서 JOIN을 수행해준다. 다만 아마도 Cartesian-product의 방식일 거라 비효율적이긴 하다.
조인(Join)
Join은 여러 테이블의 데이터를 사용해야 하는 경우 사용하는 연산이다. 이는 테이블 간의 연산이므로 FROM 절에서만 나타날 수 있다.
Natural Join
NATURAL JOIN을 사용하면 공통된 열의 값이 동일한 것들만 이어 붙인 하나의 테이블을 얻을 수 있다.
다음 예시는 INSTRUCTOR 테이블과 TEACHES 테이블에 대해 natural join을 수행한 결과를 모두(*) 나타내는 것이다.
SELECT *
FROM INSTRUCTOR NATURAL JOIN TEACHES;Join
JOIN을 사용하면 join을 수행할 조건을 명시적으로 설정할 수 있다.
조건을 선택하는 방식에는 USING과 ON이 있다.
USING은 두 테이블이 공통으로 갖고 있는 열을 선택할 수 있다.
SELECT *
FROM INSTRUCTOR JOIN TEACHES USING(ID);ON은 두 테이블 열의 이름이 서로 다르더라도 조인 조건으로 선택할 수 있다는 이점이 있다.
SELECT *
FROM INSTRUCTOR JOIN TEACHES ON(INSTRUCTOR.ID = TEACHES.TEACHER_ID);Outer Join
Outer join도 사용할 수 있으며, 추가되는 열에 대한 정보가 없다면 NULL로 설정된다는 점은 동일하다.
NATURAL LEFT OUTER JOINNATURAL RIGHT OUTER JOINNATURAL FULL OUTER JOIN
별칭 설정(Alias)
출력되는 결과의 열 이름을 변경해야 하는 경우, 또는 쿼리 작성 과정에서 각 테이블의 전체 이름을 적기 번거로운 경우에 alias를 수행할 수 있다.
AS를 사용해서 테이블의 별칭을 설정할 수 있으며, 출력되는 결과의 열 이름을 변경할 수 있다.
- 예시 1: 열 이름 변경
SELECT ID, NAME SALARY/12 AS MONTHLY_SALARY
FROM INSTRUCTOR- 예시 2: 테이블 별칭 설정
SELECT DISTINCT T.NAME
FROM INSTRUCTOR AS T, INSTRUCTOR AS S
WHERE T.SALARY > S.SALARY AND S.DEPT_NAME = 'COMP. SCI.'MySQL에서는 AS를 명시적으로 적지 않고 띄어쓰기 직후에 새로 설정할 이름을 적어도 alias가 가능하다.
SELECT DISTINCT T.NAME
FROM INSTRUCTOR T, INSTRUCTOR S # 동일한 결과
WHERE T.SALARY > S.SALARY AND S.DEPT_NAME = 'COMP. SCI.'다만 뒤에서 이야기 할, FROM절에서의 subquery에서는 AS를 반드시 작성해야 한다.
연산자
WHERE 문에서 행 선택 조건을 나타내기 위해서는 여러 연산자를 사용하게 된다.
비교 연산자
A = B: A의 값이 B와 같다. (==가 아니라 = 임에 유의)A > B: A의 값이 B보다 크다.A >= B: A의 값이 B보다 크거나 같다.A < B: A의 값이 B보다 작다.A <= B: A의 값이 B보다 작거나 같다.A != B: A의 값이 B와 같지 않다.
SQL 연산자
BETWEEN a AND b: 값이 a와 b 사이에 있다(a, b도 포함).IN (list): 값이 list에 있는 값 중에 하나라도 일치한다.LIKE '비교문자열': 비교문자열과 같은 문자열이다.IS NULL: NULL 값이다.IS NOT NULL: NULL 값이 아니다.
논리 연산자
AND: 앞의 조건과 뒤의 조건이 모두 참인 경우 참, 나머지는 거짓OR: 앞의 조건과 뒤의 조건 중 하나라도 참이면 참, 아니라면 거짓NOT: 뒤의 조건의 반대 결과를 나타냄
이 중에서는 논리 연산자의 우선 순위가 가장 낮다.
괄호를 사용해서 우선 순위를 변경할 수도 있다.
와일드카드(Wildcards)
SQL에서 LIKE 연산으로 특정 문자열을 찾으려고 할 때, 완전히 동일한 문자열이 아니라 어떤 문자열이 포함된 값을 찾기 원할 수도 있다.
이러한 상황에서 임의의 문자 또는 문자열을 나타내는 wildcard를 사용할 수 있다. 와일드카드에는 두 가지가 있다.
- Percent(%): 임의 길이의 문자열을 나타낸다(길이 0도 포함).
SELECT NAME
FROM INSTRUCTOR
WHERE NAME LIKE '%dar%'- Underscore(_): 임의의 한 문자를 나타낸다.
SELECT NAME
FROM INSTRUCTOR
WHERE NAME LIKE '_dar_'만약 %이나 _가 포함된 문자열이 필요하다면 escape 문자인 백슬래시(\)를 사용하면 된다.
집계 함수(Aggregate Functions)
집계 함수로는 AVG, MIN, MAX, SUM, COUNT 등이 포함된다.
INSTRUCTOR테이블에서 DEPT_NAME이 Comp. sci.인 행의 SALARY의 평균 얻기
SELECT AVG (SALARY)
FROM INSTRUCTOR
WHERE DEPT_NAME = 'Comp. sci.';TEACHES테이블에서 SEMESTER가 SPRING이고 YEAR이 2010인 행의 구분되는 ID의 개수 얻기
SELECT COUNT (DISTINCT ID)
FROM TEACHES
WHERE SEMESTER = 'SPRING' AND YEAR = 2010;COURSE테이블의 전체 행 개수 얻기
SELECT COUNT (*)
FROM COURSE;GROUP BY 활용
- DEPT_NAME이 같은 것들끼리 그룹을 생성하여, 각 DEPT_NAME 그룹의 평균 SALARY를 얻는 예시
SELECT DEPT_NAME, AVG (SALARY) AS AVG_SALARY
FROM INSTRUCTOR
GROUP BY DEPT_NAME;HAVING 활용
- DEPT_NAME이 같은 것들끼리 그룹을 생성하여, 각 DEPT_NAME 그룹의 평균 SALARY이 42000을 넘는 경우만 얻는 예시
SELECT DEPT_NAME, AVG (SALARY)
FROM INSTRUCTOR
GROUP BY DEPT_NAME
HAVING AVG (SALARY) > 42000;NULL이 있는 경우
집계 함수의 연산에는 NULL이 포함되지 않는다. 다만 COUNT(*)만 유일하게 NULL을 포함하여 계산한다.
서브쿼리(Subquery)
Nested Subquries
해당 list에 값이 존재하는 여부, 또는 존재하지 않는 여부를 확인하는 IN 과 NOT IN을 활용하는데 사용할 수 있다.
이는 주로 where 절에서 사용된다.
SELECT DISTINCT COURSE_ID
FROM SECTION
WHERE SEMERTER = 'FALL' AND YEAR = 2009
AND COURSE_ID IN (SELECT COURSE_ID
FROM SECTION
WHERE SEMESTER = 'SPRING' AND YEAR = 2010);SELECT COUNT (DISTINCT ID)
FROM TAKES
WHERE (COURSE_ID, SEC_ID, SEMESTER, YEAR)
IN (SELECT COURSE_ID, SEC_ID, SEMESTER, YEAR
FROM TEACHES
WHERE TEACHES.ID = 10101);이는 집합의 비교 형태로도 활용할 수 있다.
SOME은 아무 값 하나,ALL은 모든 값을 본다.
SELECT DISTINCT T.NAME
FROM INSTRUCTOR AS T, INSTRUCTOR AS S
WHERE SALARY > SOME (SELECT SALARY
FROM INSTRUCTOR
WHERE DEPT_NAME = 'Biology');Correlation Variables
SELECT COURSE_ID
FROM SECTION AS S
WHERE SEMESTER = 'FALL' AND YEAR = 2009 AND
EXISTS (SELECT *
FROM SECTION AS T
WHERE SEMESTER = 'SPRING' AND YEAR = 2010
AND S.COURSE_ID = T.COURSE_ID);여기서는 앞선 내용과 다르게 괄호 내부를 먼저 계산하지 않는데, 그 안에서 S라는 테이블이 필요하고, 이 S는 괄호 바깥에서 주어지기 때문이다. 그래서 일단 현재 테스트 중인 S를 가지고 들어가게 된다.
Derived Relations
FROM 절에 사용되는 derived table은 이름을 새로 정해서 적어주어야 한다(MariaDB 기준)
SELECT DEPT_NAME, AVG_SALARY
FROM (SELECT DEPT_NAME, AVG (SALATY) AS AVG_SALARY
FROM INSTRUCTOR
GROUP BY DEPT_NAME) AS DEPT_AVG
WHERE AVG_SALARY > 42000;SELECT DEPT_NAME, AVG_SALARY
FROM (SELECT DEPT_NAME, AVG (SALARY)
FROM INSTRUCTOR
GROUP BT DEPT_NAME) AS DEPT_AVG (DEPT_NAME, AVG_SALARY)
WHERE AVG_SALARY > 42000;집합 연산자
합집합은 UNION, 교집합은 INTERSECT, 차집합은 EXCEPT 를 사용한다.
(SELECT COURSE_ID FROM SECTION WHERE SEM = 'FALL' AND YEAR = 2009)
UNION
(SELECT COURSE_ID FROM SECTION WHERE SEM = 'SPRING' AND YEAR = 2010)- 특히
UNION에서는 중복을 허용하거나 제거하기 위해DISTINCT를 사용할 지,ALL을 사용할 지를 잘 고려해야 한다. - 다만 MySQL에서는
INTERSECT를 지원하지 않는다…. Nested subquery를 활용해야 한다.
알아야 할 함수
(코테 풀다가 이거다 싶으면 추가 예정)
