02. MySQL - MySQL Tutorial for Beginners [Full Course] 내용 정리

OS	Windows 10 PRO 64bit 버전 20H2 (OS 빌드 19042.867)
MySQL	8.0.23 Community Server

영상 (3시간 10분)

시작

종류

관계형 데이터베이스 구조

RDBMS 는 종류가 여래개 있지만, 모두 표준 SQL 을 준수하여 비슷비슷하다

비관계형 데이터베이스 구조

데이터 베이스 선택

USE 데이터베이스명

테이블 간단 조회

- 데이터 베이스를 선택한 경우

SELECT * FROM 테이블명

- 데이터 베이스를 선택하지 않은 경우

SELECT * FROM 데이터베이스명.테이블명

- 산술 연산이 가능하고 별칭을 줄 수 있습니다, 띄어쓰기를 하려면 '' 로 감싸주어야 합니다

중복된 데이터 제거하기

SELECT DISTINCT 컬럼명 FROM 테이블명

SELECT 간단 문제

데이터베이스의 모든 제품을 반환하는 SQL 쿼리를 작성하시요.

상품명, 가격, 단가 만 출력 (unit price 처럼 띄어쓰기는 _ 로 사용하여 작성하세요)

[정답]

SELECT name, unit_price, unit_price * 1.1 as 'new_price' 
FROM 테이블

WHERE 조건절

- 숫자

# 포인트가 3000 보다 큰 고객을 출력
SELECT *
FROM Customers
WHERE points > 3000

- 문자

# state 가 'VA' 인 고객 출력 (문자는 "" 또는 '' 로 감싸주어야 한다)
SELECT *
FROM Customers
WHERE state = 'VA'

- 날짜

# 생일이 1990년 01월 01일 이후인 고객 출력 (날짜 또한 "" 또는 ''로 묶어주어야 한다)
SELECT *
FROM Customers
WHERE birth_date > '1990-01-01'

WHERE 간단문제

올해의 주문내역을 출력하시요

[정답]

# 임시 정답
SELECT *
FROM orders
WHERE '2019-01-01' <= order_date AND order_date < '2020-01-01'

AND, OR, NOT 간단 문제

주문항목 테이블에서, 총 가격이 30보다 크고 주문번호가 6인 주문상품들 출력

[정답]

SELECT *
FROM order_items
WHERE order_id = 6 AND unit_price * quantity > 30

IN

state = 'VA' and state = 'FL' and state = 'GA' 와 같은 기능이다

SELECT *
FROM customers
WHERE state IN ('VA', 'FL', 'GA')

NOT 도 사용가능하다

SELECT *
FROM customers
WHERE state NOT IN ('VA', 'FL', 'GA')

간단 문제

재고 수량이 48, 38, 72 인 상품 출력

SELECT *
FROM products
WHERE quantity_in_stock IN (49, 38, 72)

BETWEEN 숫자1 또는 날짜1 AND 숫자2 또는 날짜2

태어난 날이 1990년 1월 1일 부터 2000년 1월 1일 고객 출력

SELECT *
FROM customers
WHERE birth_date BETWEEN '1990-01-01' AND '2000-01-01'

ORDER BY

해당 컬럼명을 기준으로 오름차순 내림차순 정렬 할 때 사용합니다

SELECT *
FROM customers
ORDER BY first_name DESC

날짜순으로 정렬 후 이름으로 정렬하기 위해서는 ,를 이용해서 작성할 수 있습니다

SELECT *
FROM customers
ORDER BY birth_date, first_name

LIMIT

LIMIT offsetStart
또는
LIMIT offsetStart, offsetEnd

SELECT *
FROM customers
LIMIT 100

1~100 번째 까지 출력

SELECT *
FROM customers
LIMIT 100, 10

101~110 번째 까지 출력

JOIN

연관된 여러개의 테이블의 컬럼을 합칠 때 사용한다

SELECT *
FROM orders
JOIN customers
	ON orders.customer_id = customers.customer_id

고객 아이디를 이용하여 주문내역 테이블과 고객 테이블 합치기

JOIN 만 쓰는 경우 기본 INNER JOIN 이다

SELECT order_id, customer_id, first_name, last_name
FROM orders
JOIN customers
	ON orders.customer_id = customers.customer_id

customer.id 는 orders 의 customer_id 인지 customers 의 customer_id 인지 구분하지 못하기 때문에

아래와 같이 테이블명.customer_id 로 작성해주어야 한다

SELECT order_id, orders.customer_id, first_name, last_name
FROM orders
JOIN customers
	ON orders.customer_id = customers.customer_id

또는 별칭을 주어도 가능하다

SELECT order_id, o.customer_id, first_name, last_name
FROM orders o
JOIN customers c
	ON orders.customer_id = customers.customer_id

상품과 주문내역의 단가

SELECT order_id, oi.product_id, quantity, unit_price
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id

product_id 는 order_items 테이블의 product_id 이든, products 테이블의 product_id 이든 그렇게 중요하지 않다
하지만, unit_price 의 단가는 다르다. 단가는 바뀔 수 있기 때문에 현재 단가와 주문했을 때의 단가가 다를 수 있기 때문에 둘 중 선택해야한다. 현재는 주문내역 테이블을 조회하기 때문에 주문했을 때의 단가를 원하기 때문에 oi.unit_price 로 적어주는 것이 맞다

SELECT order_id, order_items.product_id, quantity, order_items.unit_price
FROM order_items
JOIN products USING(product_id)

ON 대신 USING 을 사용하여 Join 을 할 수도 있다. ON 과 USING 의 차이는 ON 은 조인하려는 2개의 테이블의 컬럼명이 다를 경우, USING 은 2개의 테이블의 컬럼명이 같은 경우 사용한다

SELF JOIN

SELECT
    e.employee_id,
    e.first_name,
    m.first_name AS manager
FROM employees e
JOIN employees m
	ON e.reports_to = m.employee_id

reports_to(상위보고자) 를 통하여 계층구조를 알아 낼 수 있다

MASTER | MANAGER | CLERK 직급이 나누어져있을 때

- MAANGER 컬럼 + MASTER 컬럼

- CLERK 컬럼 + MANAGER 컬럼

이렇게 조회된다

3개 이상의 테이블 JOIN 하기

orders 테이블에 2개의 테이블을 JOIN 하기위해 아래와 같이 작성한다
order_status 는 배송준비, 배송중, 배송완료 들이 들어간다

SELECT 
    o.order_id,
    o.order_date,
    c.first_name,
    c.last_name,
    os.name AS status
FROM orders o
JOIN customers c
	ON o.customer_id = c.cusomer_id
JOIN order_statuses os
	ON o.status = os.order_status_id

Payment 결제

payment : 결제사
payment_methods : 결제수단 = 지불방법 (카드, KakaoPay, Point)

SELECT *
FROM payments p
JOIN  clients c
	ON p.client_id = c.client_id
JOIN payment_methods pm
	ON p.payment_method = pm.payment_method_id

SELECT 
    p.date,
    p.invoice_id,
    p.amount,
    c.name,
    pm.name
FROM payments p
JOIN  clients c
	ON p.client_id = c.client_id
JOIN payment_methods pm
	ON p.payment_method = pm.payment_method_id

payment 테이블과 pamyent_methods 테이블 (컬럼명 명시)

복합 결합 조건 (Compound Join Condition) - Order_Items 주문내역 테이블 - 복합키

order_id 와 product_id 는 기본키이다 - 2개의 키를 갖는 복합키

- 복합 JOIN

SELECT *
FROM order_items oi
JOIN order_item_notes oin
    ON oi.order_id = oin.order_id
    AND oi.product_id = oin.product_id

암시적 조인 구문 (Implicity Join Syntax) - Cross Josin (교차조인)

SELECT *
FROM orders o, customers c
WHERE o.customer_id = c.customer_id

order 에 10개 데이터가 있고, customers 에 10개에 데이터가 있다면 크로스 조인을 하는 경우 100개가 출력된다

외부 조인 Outer Join

고객(customers) 와 주문(orders) 를 조인할 때 inner join 을 하는 경우 주문한 고객들만 출력 되기 때문에 주문하지 않은 고객을 출력하려면 외부조인을 활용해야 한다

SELECT 
	c.customer_id,
    c.first_name,
    o.order_id
FROM customers c
LEFT JOIN orders o
	ON c.customer_id = o.customer_id

- LEFT (OUT) JOIN, RIGHT (OUT) JOIN : OUT 은 생략이 가능하다

JOIN 간단 문제

상품 테이블과 주문 아이템
주문하지 않은 경우에 조회했을 때 수량이 null 이 된다

SELECT 
    p.product_id,
    p.name,
    oi.quantity
FROM products p
LEFT JOIN order_items oi
	ON p.product_id = oi.product_id

+ 한 번도 주문되지 않은 상품 조회하기

SELECT 
    p.product_id,
    p.name,
    oi.quantity
FROM products p
LEFT JOIN order_items oi
	ON p.product_id = oi.product_id
WHERE oi.quantity is null

여러 테이블 간의 외부 조인 (Outer Joins Between Multiple Tables)

내부 조인에 대해 여러 테이블 간에 외부조인을 사용할 수 있다
배송업체 (shippers)

SELECT 
    p.product_id,
    p.name,
    oi.quantity
FROM products p
LEFT JOIN order_items oi
	ON p.product_id = oi.product_id
JOIN shippers sh # 잘못된 예
    ON o.shipper_id = sh.shipper_id

SELECT 
    p.product_id,
    p.name,
    oi.quantity
    sh.name AS shipper
FROM products p
LEFT JOIN order_items oi
	ON p.product_id = oi.product_id
LEFT JOIN shippers sh
    ON o.shipper_id = sh.shipper_id

오른쪽 조인과 왼쪽 조인을 번갈아가면서 쓰는 경우 데이터가 어떻게 생길지 예측하기 어렵기 때문에 되도록이면 LEFT JOIN 을 활용해야 한다고 한다

OUTER JOIN 간단 문제

배송조회가 있던 없던 조회하기 위해 shipper 테이블만 LEFT JOIN 을 작성해준다

SELECT 
    o.order_id,
    o.order_date,
    c.first_name AS customer,
    sh.name AS shipper,
    os.name AS status
FROM orders o 
JOIN customers c 
	ON o.customer_id = c.customer_id
LEFT JOIN shippers sh
	ON o.shipper_id = sh.shipper_id
JOIN order_statuses os
	ON o.status  = os.order_status_id

자체 외부 조인 (Self Outer Joins)

LEFT OUTER JOIN 을 이용하여 관리자가 있든 없든 모든 직원을 조회할 수 있다
가장 높은 직급인 사람은 바로 위에 상사가 존재하지 않기 때문에 조회가 안되지만, LEFT JOIN 을 이용하여 조회할 수 있다

SELECT 
    e.employee_id,
    e.first_name,
    m.first_name AS manager	
FROM employees e
LEFT JOIN employees m
    ON e.reports_to = m.employee_id

USING 절

USING 을 이용하여 쿼리문을 조금 간단하게 작성할 수 있다

SELECT 
    o.order_id,
    c.first_name
    sh.name AS shipper
FROM orders o 
JOIN customers
    USING(customer_id)
JOIN shippers sh
    USING(shipper_id)

- USING 은 복합키 조인을 할 때 유용합니다

# ON 절
SELECT *
FROM order_items oi
JOIN order_items_notes oin
    ON oi.order_id = oin.order_id
    AND oi.product_id = oin.product_id
    
    
# USING 절
SELECT *
FROM order_items oi
JOIN order_items_notes oin
   USING(order_id, product_id)

간단 문제

SELECT 
    p.date,
    c.name AS client,
    p.amound,
    pm.name AS payment_method    
FROM payments p
JOIN clients c 
    USING (client_id)
JOIN payment_methods pm
    ON p.payment_method = pm.payment_method_id

- 키워드가 다른 경우에는 USING 절을 사용할 수 없기 때문에 ON 절을 사용한다

자연 조인 (Natural Joins)

간단한 방법으로 두 개의 테이블을 연결하는 것을 Natural Join 이라고 한다

코드짜는 것은 쉽지만, 때로는 예상치 못한 결과를 보여줍니다

하지만 어디선가 본다면 어떻게 작동하는지 빠르게 보여드리겠습니다

SELECT *
FROM orders o
NATURAL JOIN customers c

Natural Join 은 열이름을 정확히 지정하지 않습니다 따라서 데이터베이스 엔진은 이 두 테이블을 보고 공통 열을 기반으로 자동으로 조인해줍니다

쉽게 조인을 할 수 있지만 데이터베이스 엔진이 추측을 잘못하면 잘못된 결과를 초래할 수 있어 사용을 하지 않습니다

교차 조인 = 크로스 조인 (Cross Join)

# 명시적 크로스 조인
SELECT * 
FROM customers
CROSS JOIN products

# 묵시적 크로스 조인
SELECT *
FROM customers, products

A 테이블이 10개 데이터가있고 B 테이블에 10개 데이터가 있을 때 100개의 결과를 출력합니다

크로스 조인 간단 문제

모든 배송업체와 모든 제품들의 조합의 결과를 볼 수 있다

SELECT
    sh.name AS shipper,
    p.name AS product
FROM shippers sh, product p

조합 (Union)

Join을 활용하면 열을 여러 테이블과 결합할 수 있지만 SQL 에서는 Uinon 으로 행을 여러 테이블과 결합할 수 있다

현재 연도에 첫 번째 주문이 접수되었음을 알 수 있다. 2019년, 다른 모든 주문은 이전 연도에 이루어졌다
보고서를 만들고 모든 주문을 가져오고 각 주문 옆에 레이블을 추가합니다 현재 연도에 주문한 경우 테이블은 다음과 같습니다 활성화 될 예정이며 이전 연도에 주문한 경우 아카이브(보관)으로 레이블을 지정하고 싶습니다

SELECT 
    order_id,
    order_date,
    'Active' AS status
FROM orders
WHERE order_date >= '2019-01-01'
UNION
SELECT 
    order_id,
    order_date,
    'Archived' AS status
FROM orders
WHERE order_date < '2019-01-01';

유의할 점은 합칠 때 컬럼의 수가 동일하지 않은 경우 오류가 발생합니다

Union 간단문제

SELECT
    customer_id,
    first_name,
    points,
    'Bronze' AS type
FROM customers
WHERE points < 2000
UNION
SELECT
    customer_id,
    first_name,
    points,
    'Silver' AS type
FROM customers
WHERE points BETWEEN 2000 AND 3000
UNION
SELECT
    customer_id,
    first_name,
    points,
    'Gold' AS type
FROM customers
WHERE points > 3000

- 컬럼명에 CASE 구문을 이용하면 더 쉽게 작성할 수 있다

- type 같은 경우 정렬하기 위해 사용자 정의 정렬을 만들어 낼 수 있다 (Bronze - Silver - Gold)

여러 행 삽입 (Inserting Multiple Rows)

INSERT INTO shipper (name)
VALUES 
    ('shipper1'),
    ('shipper2'),
    ('shipper3')

계층 적 행 삽입 (Inserting Hierarchical Rows)

LAST_INSERT_ID() 를 이용하여 마지막 인덱스를 알아낼 수 있다

INSERT INTO orders (customer_id, order_date, status)
VALUES (1, '2019-01-02', 1);

INSERT INTO order_items
VALUES (LAST_INSERT_ID(), 1, 1, 2.95);

테이블 사본 생성

테이블 복사 (빈 데이터)

CREATE TABLE orders_archived AS 
SELECT * FROM orders

조건에 만족하는 값들만 넣기

INSERT INTO orders_archived
SELECT * FROM orders
WHERE order_date < '2019-01-01'

저작자표시

'30. MySQL > 기초' 카테고리의 다른 글

01. MySQL DCL 설정 (0)	2021.09.08

02. MySQL - MySQL Tutorial for Beginners [Full Course] 내용 정리

'30. MySQL > 기초' 카테고리의 다른 글

댓글

이 글 공유하기

티스토리툴바