국비학원 25일차 : SQL_4

ERD (Entity Relationship Diagram)

테이블 간의 관계를 설명해주는 다이어그램

SQL에서는 테이블 구조를 정의하여 저장할 다양한 유형의 메타데이터를 나타내는 열을 포함하도록 테이블 구조를 정의하여 메타데이터를 저장할 수 있다.

Entity : 실제로 존재 하는 것. 열차예약에서는 Train, Station, Seat
Relation : '열차는 정거장에서 출발, 도착, 정거한다' 하는 관계
Attribute : 필드들. 기본키는 밑줄로 구분한다.

MySQL에서 그린 EER Diagram

1대n 의 경우 n을 먼저 찍어주고 1의 관계를 찍어 연결해주면 된다.
점선 : 참조하는 요소가 primary key 설정 안되어있으면 점선
실선 : 참조하는 요소가 primary key 설정 되어있으면 실선

선에 비어있는 동그라미가 있으면, 요소들의 값이 NOT NULL 해제되어 있는 상태. 필수로 들어가는 값이 아니라는 뜻 / 두개의 막대가 그어져있으면 NOT NULL이 설정되어 필수 값이라는 뜻.

train과 station의 관계 (n : 1)

station의 station_number이 train의 departure_station과 arrival_station에 관계를 맺고 있다. 한 역에 여러대의 열차가 오고 갈 수 있으므로 일 대 다의 관계가 된다. departure_station과 arrival_station이 primary key가 아니기때문에 점선으로 연결되어 있다.

train과 seat의 관계 (n : 1)

train의 train_number이 seat의 train_number과 관계를 맺고 있다. 한 열차에 여러개의 좌석을 갖고있으므로 일 대 다의 관계가 된다.

train과 station의 관계 (n : m)

train 과 station이 다 대 다의 관계를 가질때 train has station테이블이 생성되는데 이 테이블이 stop_station테이블이 된다. 한 열차가 여러 역을 거치면서 중간중간 정차역이 생기게 되고, 한 정차역 또한 여러대의 열차가 지나가게 되는 관계이다. 그러므로 정차역에는 출발, 도착 시간이 생기게 된다. 이 stop_station 테이블은 각각 train_number와 station_number로 관계된다.

station과 station의 관계 (n : m)

역과 역 사이를 열차가 지나 이동하는 거리에 따라 요금이 매겨지게 되면서 생기는 관계다. station이 station과 맺어지는 다 대 다의 관계다. 이 테이블이 cost 테이블이 된다. station_number에 따라서 관계가 맺어져 출발 역과 도착역이 생기게 된다.

다이어그램을 그릴때 각각의 데이터베이스에 대한 설명 필수!

Join

여러개의 테이블에서 관계로 연결되어 있는 표현을 하나로 검색하도록 해주는 쿼리

데이터베이스에서 join은 둘 이상의 테이블에서 관련 열을 기준으로 행을 결합하는 작업입니다. join의 목적은 테이블 간의 관계가 있는 서로 다른 테이블의 데이터를 결합하여 복잡한 쿼리에 응답하거나 특정 정보를 검색하는 데 사용할 수 있도록 한다.

INNER JOIN

join 중인 두 테이블의 값이 일치하는 행만 반환합니다.

-- INNER JOIN
-- 1. FROM 첫번째 테이블 INNER JOIN 두번째 테이블 ON 조건;
-- 2. FROM 첫번째 테이블 JOIN 두번째 테이블 ON 조건;
-- 3. FROM 첫번째 테이블, 두번째 테이블 WHERE 조건;

SELECT R.room_number AS '방번호', C.name AS '고객이름'
FROM Room R INNER JOIN Custom C # 별칭을 붙이면 수월하게 사용할수 있다.
ON C.id = R.custom_id; # 걸어준 조건에 맞는게 나오게 된다

SELECT *
FROM Room JOIN Custom; # 전부다 매칭해서 나오게 된다. cross join

SELECT * 
FROM Room, Custom
WHERE Room.custom_id = Custom.id; 
# join하지 않고 where을 사용해서 같은 결과 만들수 있다

LEFT JOIN

왼쪽 테이블의 모든 행과 오른쪽 테이블의 일치하는 행을 반환합니다. 오른쪽 테이블에 일치하는 행이 없는 경우에도 결과에는 오른쪽 테이블의 열에 NULL 값을 가진 왼쪽 테이블의 행이 계속 포함된다.

-- FROM 첫번째 테이블 LEFT JOIN 두번째 테이블 ON 조건;

SELECT *
FROM Room LEFT JOIN Custom
ON Room.custom_id = Custom.id; # null인 경우에도 표현이 되게 된다. 

INSERT INTO Custom
VALUES (20, 'DAVID', 'David@gamil.com', 30, 'new york', 1);

RIGHT JOIN

오른쪽 테이블의 모든 행과 왼쪽 테이블의 일치하는 행을 반환합니다. 왼쪽 테이블에 일치하는 행이 없는 경우에도 결과에는 왼쪽 테이블의 열에 NULL 값이 있는 오른쪽 테이블의 행이 계속 포함됩니다.

-- FROM 첫번째 테이블 RIGHT JOIN 두번째테이블 ON 조건;

SELECT *
FROM Room RIGHT JOIN Custom
ON Room.custom_id = Custom.id;

🤔LEFT JOIN과 RIGHT JOIN의 차이?

Chat-GPT에 물어본 결과.

LEFT JOIN과 오른쪽 조인의 주요 차이점은 모든 행이 반환되는 테이블입니다.

LEFT JOIN에서는 왼쪽 테이블의 모든 행과 오른쪽 테이블의 일치하는 행이 반환됩니다. 오른쪽 테이블에 일치하는 항목이 없는 경우에도 오른쪽 테이블의 열에 NULL 값이 있는 왼쪽 테이블의 행이 계속 포함됩니다.

RIGHT JOIN에서는 오른쪽 테이블의 모든 행과 왼쪽 테이블의 일치하는 행이 반환됩니다. 왼쪽 테이블에 일치하는 항목이 없는 경우에도 결과에는 왼쪽 테이블의 열에 NULL 값이 있는 오른쪽 테이블의 행이 계속 포함됩니다.

차이를 더 잘 이해하기 위해 다음 두 표를 고려합니다: 표 A 및 표 B. 표 A에는 5개의 행이 있고 표 B에는 3개의 행이 있습니다. 테이블 A와 테이블 B의 왼쪽 조인은 테이블 A의 모든 5개 행을 반환하고 테이블 B의 일치하는 2개 행만 반환합니다. 표 A와 표 B를 오른쪽으로 조인하면 표 B의 모든 행이 반환되고 표 A의 일치하는 두 행만 반환됩니다.

일반적으로 LEFT 및 RIGHT JOIN은 한 테이블의 모든 행을 포함하고 다른 테이블의 일치하는 행만 포함하려는 경우에 사용됩니다. 사용할 JOIN 선택은 수행 중인 분석 또는 쿼리에 대해 어떤 테이블이 더 중요한지에 따라 달라집니다.

Sub Query

관계형 데이터베이스 관리 시스템(RDBMS)의 다른 쿼리 내에 있는 쿼리
외부쿼리는 SELECT, INSERT, UPDATE, DELETE, SET, DO 문이 있다.

1. 서브쿼리는 쿼리를 구조화시키므로, 쿼리의 각 부분을 명확히 구분할 수 있다.
2. 서브쿼리는 복잡한 JOIN이나 UNION과 같은 동작을 수행할 수 있는 또 다른 방법을 제공한다.
3. 서브쿼리는 복잡한 JOIN이나 UNION 보다 좀 더 읽기 편하다.

서브쿼리는 반드시 괄호(())로 감싸져 있어야 한다.

IN() : 인수로 전달된 값과 동일한 값이 하나라도 존재한다면 true를 반환

-- 복잡한 join문을 조금 더 강결하게 사용할 수 있도록 해주는 쿼리
-- SELECT, INSERT, UPDATE, DELETE, SET, DO 에서 사용가능
-- FROM과 WHERE 절에서 사용가능

-- WHERE절 사용
SELECT *
FROM Room
#고객 한명당 여러개의 방을 예약가능하고, 동명이인지 존재할 수 있다.
WHERE custom_id IN ( 
# 조건 자리에 중복 허용 값이 있으면 in붙여서 적용가능하고, 
# 아니라면 = 가능하다
	SELECT id #하나의 컬럼만 포함할수 있다. 오류코드 1241
    FROM Custom
    WHERE name = 'Michle'
);
# Custom 테이블에서 이름이 Michle인 id와 
# Room 테이블의 custom_id가 같을때의 Room의 모든 레코드 반환

SELECT id
FROM Custom
WHERE name = 'Michle'; 

-- ex) IN 연산  + LIKE 연산 : 이럴때 서브쿼리를 사용하면 된다.

SELECT *
FROM Custom
WHERE id IN(
	SELECT id
    FROM Custom
    WHERE name LIKE 'M%'
    OR name LIKE 'D%'
);
# Custom 테이블의 name에 M과 D를 포함하는 id와 
# Custom테이블의 id가 같을때 Custom 테이블의 모든 레코드 반환

-- FROM절 사용
SELECT CustomId
FROM (
	SELECT id AS CustomId, email AS CustomEmail
    FROM Custom
) C;
# Custom테이블에서 id와 email을 선택하고, 
# (각각 id를 CustomId로, email을 CustomEmail로 별칭), 
# 이 테이블의 별칭도 C로 설정.
# C에서 CustomId을 레코드로 반환

SELECT id AS CustomId, email AS CustomEmail
FROM Custom;

Order By

-- 특정 필드를 기준으로 오름차순, 내림차순 정렬하여 결과를 반환
SELECT *
FROM Namgu;

-- 내림차순 DESC
SELECT * FROM Namgu
ORDER BY 세대수 DESC; #내림차순
-- 오름차순 ASC
SELECT * FROM Namgu
ORDER BY 통 DESC, 반 ASC;