SQL 노트

SQL 공부했던거 잊으면 아까우니까 기록해야지.

SQL이 뭐지?

Structured Query Language의 약자로, 데이터베이스에 접근하고 조작하기 위한 표준 언어다.
여러개의 연결된 테이블, 관계형 데이터데이스를 제어할 수 있게 해준다.
테이블은 컬럼(Column)과 레코드(Record)로 구성되어 있다.

SELECT

SELECT 이거 저거 요거 뽑을래! 검색할 컬럼
FROM 어느 테이블에서 뽑을꺼야?

SELECT * --가지고 있는 데이터 다 줘!

SELECT DISTINCT --중복된 컬럼을 제거하고 보여줘!

DISTINCT 뒤에 칼럼을 2개 이상 적으면, 한 쪽 컬럼에 중복이 있다고 해도 다른 쪽 컬럼의 값이 다르면 다르게 취급한다.

WHERE

SELECT 칼럼
FROM 테이블
WHERE name = "malza"; name 이 malza 인 데이터를 가져와줘!

여러개의 조건

SELECT *
FROM score
WHERE english > 90
WHERE english < 90
WHERE english >= 90
WHERE english <= 90
WHERE english = 90
WHERE english <> 90  -- != 도 가능하긴 하다!
WHERE english > 90 and math > 80
WHERE english > 90 or math < 80
WHERE english BETWEEN 80 AND 90
WHERE A IN B (B에 A가 포함된 값!)
WHERE A NOT IN B (B 에 A가 포함되지 않은 값!)

유사한 값 찾기 LIKE

SELECT *
FROM book
WHERE title LIKE '%수학'; ('수학'으로 끝나는 책!)
WHERE title LIKE '%수학%'; ('수학'으로 시작하는 책!)
WHERE title LIKE '수학%'; ('수학'을 포함하는 책!)

정렬

SELECT *
FROM score
ORDER BY korean ASC; (DEFAULT 가 오름차순이다. 작은 값부터 시작!)
ORDER BY korean DESC; (내림차순이다. 높은 값부터 시작!)

테이블에 데이터 삽입

컬럼을 명시하지 않으면 순서대로 값을 삽입!

INSERT INTO 테이블(컬럼 1, 컬럼 2, 컬럼 3...)
VALUES(값, 값, 값...);

테이블의 데이터 수정

UPDATE book -- 테이블
SET title = '수학의 정서'  -- 변경할 값!
WHERE title = '수학의 정석' -- 조건!

테이블의 데이터 삭제하기

DELETE
FROM book -- 테이블!
WHERE title = '수학의 정석'; -- 조건! 조건이 없다면 모든 데이터를 삭제한다.

COUNT

검색한 데이터의 개수를 가져오는 내장함수

SELECT COUNT(*)
FROM book;

LIMIT

SELECT *
FROM book
LIMIT 5; -- 5개만 뽑아줘!
LIMIT 1, 5; -- 2번째 데티어부터 5개만 뽑아줘!

SUM 과 AVG

총합과 평균값을 구한다.

SELECT SUM(korean)
FROM score;

SELECT AVG(korean), AVG(math)
FROM score;

MAX 와 MIN

최대값과 최소값을 구한다.

SELECT MAX(korean)
FROM score;

SELECT MIN(korean)
FROM score;

GROUP BY

원하는 데이터끼리 그룹화

SELECT user_id, COUNT(*)
FROM rental
GROUP BY user_id;

SUM, AVG, MAX, MIN 과 함께 활용 가능하다!

SELECT user_id, SUM(칼럼) -- AVG, MAX, MIN 가능
FROM rental
GROUP BY user_id;

데이터 그룹에 조건 적용

SELECT user_id, COUNT(*)
FROM rental
GROUP BY user_id
HAVING COUNT(user_id) > 1;

JOIN

투개의 테이블 정보를 한 번에 조회 할 수 있다.

SELECT * FROM rental
INNER JOIN user;

JOIN에 조건을 적용 'ON'

SELECT * FROM rental
INNER JOIN user
ON user.id = rental.user_id; -- id 칼럼을 기준으로 연결시킨다.

LEFT JOIN과 RIGHT JOIN

LEFT JOIN은 왼쪽 테이블의 모든 값을 포함해서 연결한다.
RIGHT JOIN 오른쪽 테이블의 모든 값을 포함해서 연결한다.

서브쿼리

하나의 쿼리 안에 포함된 또 하나의 쿼리다.
메인쿼리가 실행되기 전에 한 번만 실행한다.
한 문장에서 여러 번 사용 가능하다.

SELECT * FROM employee
WHERE salary > 
(SELECT salary FROM employee WHERE name='malza')

malza 사원의 급여를 알지 못하지만 malza 보다 급여가 높은 사원의 데이터를 뽑아냈다.

서브쿼리 사용방법

1. 괄호와 함께 사용해야 한다.
2. 서브쿼리 안에서 ORDER BY 절은 사용 할 수 없다.
3. 연산사의 오른쪽에 사용되야 한다.
4. 오로지 SELECT문으로만 작성 할 수 있다.

단일 행 서브쿼리

결과가 한 행만 나오는 서브쿼리다. 결과를 1개의 값만 반환하며, 결과를 메인쿼리로 전달한다.

다중 행 서브쿼리

결과를 2개 이상 반환하고, 결과를 메인쿼리로 전달한다.

SELECT *
FROM emp
where salary = any
(SELECT max(salary) from emp GROUP BY deptno)

다중 행 연산자

IN : 하나라도 만족하면 반환
ANY : 하나라도 만족하면 반환 비교 연산 가능
ALL : 모두 만족하면 반환 비교 연산 가능

스칼라 서브쿼리

SELECT 절에서 사용하는 서브쿼리다. 오로지 하나의 행만 반환한다.
마치 JOIN을 사용한 것과 같은 결과를 나타낸다.
데이터가 많을때는 스칼라 서브쿼리의 속도가 JOIN보다 빠르다.

SELECT students.name, (
	SELECT korean
    FROM test as t
    WHERE t.student_id = students.student_id
) as test_avg
FROM students;

집합연산자와 계층형질의

UNION : 합집합
INTERSECT : 교집합 (MySQL 지원 안됨!)
EXCEPT : 차집합 (MySQL 지원 안됨!)
CROSS JOIN : 카디션 프로덕트

UNION

두개 이상의 테이블에서 조인을 사용하지 않고 연관된 테이블을 조회한다.
테이블에서 SELECT한 컬럼의 수와 각 컬럼의 데이터타입이 테이블 간 상호 호환이 가능해야 한다.
합친 후에는 중복된 데이터를 제거한다. 이 과정에서 정렬이 일어난다. 물론 제대로 된 결과를 얻기 위해선 OREDR BY 절로 다시 정렬!

UNION ALL

중복에 대한 처리가 UNION과 다르다. 중복을 처리하지 않는다.

계층형 데이터

동일 테이블에 계층적으로 상위와 하위 데이터가 포함되어 있는 데이터

Oracle 계층형 질의

SELECT LEVEL, 자식 컬럼, 부모 컬럼, 원하는 컬럼
FROM 테이블명
START WITH 부모 컬림 IS NULL -- 부모 컬럼이 NULL인 행이 ROOT가 된다.
CONNECT BY PRIOR 자식 컬럼 = 부모 컬림; -- 상위와 하위 데이터 연결방식

Oracle CONNECT BY 키워드

LEVEL : 검색 항목의 깊이를 의미한다. ROOT의 레벨이 1이다.
CONNECT_BY_ROOT : 현재 전개할 데이터의 ROOT 데이터 값 표시
CONNECT_BY_ISLEAF : 현재 전개할 데이터가 LEAF 데이터 인지에 대한 값 표시(0 or 1)
SYS_CONNECT_BY_PATH(A, B) : ROOT 데이터부터 현재까지 전개한 경로 표시(A:컬럼명, B:구분자)

MySQL/MariaDB 계층형 질의

SQL Server version.2005 이후 CTE(Common Table Expression)을 이용해 재귀 호출한다.

WITH RECURSIVE CTE(member_id, manager_id, lvl) as(
select member_id, manager_id, 0 as lvl
from MEMBER
where manager_id is null

union all

select m.member_id, m.manager_id, c.lvl + 1
from MEMBER as m
join CTE as c
on m.manager_id = c.member_id
)

select member_id, manager_id, lvl
from CTE
order by member_id asc, lvl asc;

JOIN 심화

EQUI JOIN

두개의 테이블 간에 서로 정확하게 일치하는 경우를 활용하는 조인

Non EQUI JOIN

두개의 테이블 간에 서로 정확하게 일치하지 않는 경우를 활용하는 조인

FROM절 JOIN 형태

INNER JOIN

내부 JOIN으로, JOIN 조건에서 동일한 값이 있는 행만 반환한다. INNER 키워드 생략 가능하다.

SELECT * FROM USER a JOIN CLASS b
ON a.CLASS_ID = b.ID # ON 조건절은 칼럼명이 달라도 JOIN 조건을 사용가능

USING

같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 등가조인이 가능하다. SQL 서버에서는 지원하지 않는다.

SELECT * FROM 테이블 A JOIN 테이블 B
USING(칼럼);

NATURAL JOIN

두 테이블간의 동일한 이름을 갖는 모든 칼럼들에 대해 등가 조인을 수행한다. 애초에 조건이 필요 없다.

SELECT * FROM 테이블 A NATURAL JOIN 테이블 B

CROSS JOIN

JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 조회한다. CROSS를 생략 가능한데, INNER JOIN은 ON절이 반드시 필요하므로 ON 절이 없는 JOIN 이라면 CROSS JOIN 이다.

OUTER JOIN

두 개의 테이블 간에 교집합을 조회하고 한쪽 테이블에만 있는 데이터도 포함시켜서 조회한다. 빈 곳은 NULL 값으로 출력한다.
WHERE 조건절에서 한쪽에만 있는 데이터를 포함시킬 테이블 쪽에 (+)를 위치시킨다.
LEFT OUTER JOIN은 왼쪽 데이블이 기준
RIGHT OUTER JOIN은 오른쪽 테이블이 기준
FULL OUTER JOIN은 양쪽 테이블이 모두 포함된다. LEFT와 RIGHT JOIN 의 결과를 UNION 한 것과 같다. 중복은 지우고, 각각의 테이블에 없는건 NULL이 출력될 것이다. (MySQL 지원 안함. UNION으로 해결)

SELF JOIN

동일 테이블 사이의 조인이다. 동일 테이블 간에 조인을 수행하면 테이블과 칼럼 모두 동일하기 때문에 식별을 위해 별칭이 필요하다.