251024 [ Day 73 ] - SQL (3)

시작하며

오늘부터 조금씩 복잡한 내용을 배우기 시작했다. 오늘은 JOIN에 대해서 배웠는데, 처음에는 잘 이해가 안 갔지만 직접 코드를 작성하면서 사용해보니까 어느 상황에 어떻게 사용하는지 감을 잡을 수 있었다.

UPDATE 문

• 테이블에 이미 존재하는 데이터를 수정할 때 사용
  • WHERE절 없이 실행하면 모든 행이 수정됨

DELETE 문

• 테이블에서 데이터를 삭제할 때 사용
  • WHERE절을 생략하면 전체 데이터가 삭제됨

변수

• MySQL에서 값을 저장하고 재사용하기 위한 임시 저장 공간
• 쿼리 내에서 값을 저장 , 계산 결과 유지 , 조건 처리 등에 활용
  • 사용자 변수 : 세션 내에서 자유롭게 선언 가능
  • 시스템 변수 : MySQL 환경 설정용 변수
• 방법 1

SET @age_limit = 24;
SELECT name, age FROM student WHERE age < @age_limit;

• 방법 2

SELECT @gender_type := "남" COLLATE utf8mb4_unicode_ci;
SELECT name, gender FROM student WHERE gender = @gender_type;

변수로 문자열 사용 시 COLLATE 지정 필요

DCL

• DCL (Data Control Language) : 데이터를 제어하는 명령어
  • GRANT : 권한 부여 명령어
  • REVOKE : 권한 해제 명령어

권한 관련 코드

• 현재 사용자 확인

SELECT USER();

• 존재하는 계정 확인

SELECT * FROM mysql.user;

• 모든 데이터베이스 확인

SHOW DATABASES;

• 권한 확인

# 현재 계정
SHOW GRANTS;
# 특정 계정
SHOW GRANTS FOR "root"@"localhost";

• 계정 생성

CREATE USER "유저이름"@"localhost" IDENTIFIED BY "비밀번호";

• 권한 부여

GRANT DELETE ON 데이터베이스.테이블 TO "유저이름"@"localhost";

• 권한 삭제

REVOKE DELETE ON 데이터베이스.테이블 FROM "유저이름"@"localhost";

• 계정 삭제

DROP USER "유저이름"@"localhost";

JOIN

• 둘 이상의 테이블을 공통된 키를 기준으로 연결하여 하나의 결과로 조회하는 SQL 연산
• SQL에서 JOIN은 공통된 컬럼(예: ID) 을 기준으로 연결하는 연산
  • 외래 키로 연결되어 있지 않아도 ON 절로 조건만 맞으면 JOIN 수행 가능
  • 그러나 FK 설정을 권장

종류

• INNER JOIN : 양쪽 테이블 모두에 존재하는 행만 반환
• LEFT JOIN : 왼쪽 테이블의 모든 행 + 오른쪽에 일치하는 행
• RIGHT JOIN : 오른쪽 테이블의 모든 행 + 왼쪽에 일치하는 행
• FULL OUTER JOIN : 양쪽 테이블의 모든 행을 결합(MySQL에서는 UNION으로 우회)
• CROSS JOIN : 두 테이블의 모든 행을 서로 곱하여 가능한 모든 조합을 반환
• SELF JOIN : 자기 자신과 조인

INNER JOIN (교집합)

• 두 테이블에서 일치하는 데이터만 반환
• 일반적으로 JOIN은 INNER JOIN 을 가리킴

SELECT c.name, o.order_date, c.customer_id
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id;

LEFT JOIN (왼쪽 외부 조인)

• 왼쪽(기준 ) 테이블의 모든 행을 포함하고, 오른쪽 테이블에 일치하는 값이 있을 경우 함께 결합, 없을 경우에는 NULL로 채워서 반환

SELECT *
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id;

RIGHT JOIN (오른쪽 외부 조인)

• 오른쪽(기준 ) 테이블의 모든 행을 포함하고, 왼쪽 테이블에 일치하는 값이 있을 경우 함께 결합, 없을 경우에는 NULL로 채워서 반환

SELECT *
FROM customers c
RIGHT JOIN orders o
ON c.customer_id = o.customer_id;

UNION

• 두 개 이상의 SELECT 쿼리 결과를 수직으로 합쳐 하나의 결과 집합으로 반환하는 SQL 연산자
  • SELECT 문들의 결과 컬럼 수와 데이터 타입이 같아야 함
  • 기본적으로 중복된 행은 제거
• UNION ALL : 여러 SELECT 결과를 그대로 모두 합쳐, 중복도 포함한 전체 결과를 반환

SELECT name, email FROM customers
UNION 
SELECT order_date, order_status FROM orders;

FULL OUTER JOIN

• 양쪽 테이블의 모든 데이터를 포함하며, 어느 한쪽에 일치하는 값이 없는 경우에는 NULL로 채워서 반환
  • 개념적으로 LEFT JOIN과 RIGHT JOIN의 합집합
  • MySQL은 FULL OUTER JOIN 을 지원하지 않기 때문에 UNION으로 대체

SELECT * FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
UNION
SELECT * FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

CROSS JOIN

• 두 테이블의 모든 행을 서로 곱하여 가능한 모든 조합을 반환하는 JOIN 방식
• 주로 테스트 데이터 생성, 모든 조합 확인 등의 특정 목적에만 사용

SELECT product_name, color
FROM products
CROSS JOIN colors;

SELF JOIN

• 하나의 테이블을 마치 두 개의 테이블처럼 사용하여 자기 자신과 조인하는 것
• 테이블 내부에 있는 행들 간의 관계를 표현할 때 사용

SELECT e.name, m.name
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.employee_id;

여러 테이블 JOIN

SELECT c.name, o.order_id, p.product_id, p.name, oi.quantity, p.price
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;

마치며

SQL에 대해 공부했던 한 주가 끝났다. 아직까지는 크게 어려운 내용은 없어서 몇 번 복습하니까 금방 익힐 수 있었다. 다음 주에는 더 복잡한 내용을 다룰 것 같으니까 주말 동안 복습을 탄탄하게 해둬야겠다.