SQL 실습 | MYSQL 설치 GROUP BY WHERE IN LIKE ORDER CASE JOIN UNION 유데미 추천

SMALL

무료 MySQL (Mac M 시리즈) 다운로드 & 셋업

1. Comunity Server 다운로드: SQL 동작 가능

https://dev.mysql.com/downloads/

MySQL :: MySQL Community Downloads

The world's most popular open source database Contact MySQL | Login | Register

dev.mysql.com

최신 버전 설치. 어떤 버전이든 잘 작동되면 괜찮음.

root user를 위한 비밀번호 입력

MySQL Workbench를 Community Server에 연결할 때 쓰이므로 잘 메모/기억되어야 한다.
암호를 잊어버리면 Community Server를 삭제하고 다시 설치해야 한다.
특수문자는 나중에 문제 만들 수 있으므로 사용 지양

2. MySQL Workbench 다운로드: SQL 쿼리 작성 및 동작 가능

https://dev.mysql.com/downloads/workbench/

MySQL :: Download MySQL Workbench

Select Operating System: Select Operating System… Microsoft Windows Ubuntu Linux Red Hat Enterprise Linux / Oracle Linux Fedora macOS Source Code Select OS Version: All Windows (x86, 64-bit) Recommended Download: Other Downloads: Windows (x86, 64-bit), M

dev.mysql.com

3. Workbench를 Server에 연결: server에 연결하면 SQL 쿼리를 동작시키는 Workbench를 사용 가능

기본 세팅된 Local instance 3306을 지워도 되고, +로 new connection을 새로 생성해도 된다.

4. Workbench Interface 확인: Workbench 레이아웃, 주요 구성 요소에 익숙해지기

Schemas Tab, Query Editor Window, Result Grid, Action Output

스키마 탭: 데이터베이스에 어떤 테이블 또는 뷰가 있는지 확인
쿼리 편집창: 쿼리 작성 및 실행
결과 그리드: server에서 SQL 쿼리 실행 결과
액션 출력: 서버가 하는 일을 요약. Response 칼럼은 특히 오류 있을 때 트러블슈팅하기에 좋다.

5. Database 생성: SQL로 16개 테이블 데이터베이스 생성

create_mavenmovies.sql 열고 ⚡️ 클릭해서 실행 -> 테이블 생성 완료

Schemas 탭에서 새로고침 하여 mavenmovies 데이터베이스와 내부 테이블 등을 확인할 수 있다.

이 비즈니스의 가장 중요한 부분인 Rental 테이블을 먼저 확인한다.

SQL SELECT문 BIG 6 요소

SELECT columnName
FROM tableName
WHERE (옵션) logicalCondition
GROUP BY (옵션) columnName
HAVING (옵션) logicalCondition (GROUP BY 필수)
ORDER BY (옵션) columnName

USE mavenmovies;

Schemas 탭에서 mavenmovies database 오른쪽 마우스 클릭 - Set as Default Schema 하고 쿼리 작성하는 게 좋다.

여러 column을 ,로 같이 나열해도 되며, FROM 앞에는 ,를 제외한다.

SELECT
	customer_id,
	rental_date
FROM rental;

SELECT DISTINCT

고유한 값 반환. 항상 사용 추천.

SELECT DISTINCT title, rental_duration
FROM film
WHERE rental_duration <> 3 AND rental_duration <> 5 AND rental_duration <> 7;

SELECT *
FROM payment
WHERE payment_date > '2006-01-01' AND amount = 0.99;

많이 쓰는 논리 연산자

=, >, <, <=, >=
<>
BETWEEN
LIKE
IN()

WHERE BETWEEN

SELECT *
FROM payment
WHERE customer_id BETWEEN 1 AND 100
AND amount > 5
AND payment_date > '2006-01-01';

WHERE OR

-- Could you please write a query to pull all payments from customers 42, 53, 60, and 75,
-- along with payments over $5, from any customer?
SELECT *
FROM payment
WHERE amount > 5
	OR customer_id = 42
	OR customer_id = 53
	OR customer_id = 60
	OR customer_id = 75;

WHERE IN()

여러 개의 OR 조건 대신에 IN() 사용하여 시간 절약

SELECT *
FROM payment
WHERE amount > 5
	OR customer_id in (42, 53, 60, 75);

WHERE LIKE

매칭되는 패턴
LIKE '%pattern%' % 몇 글자든 pattern 포함
LIKE '_erry' _ 한 글자
NOT LIKE
대소문자 구분

SELECT
	title,
    special_features
FROM film
WHERE special_features LIKE '%Behind the Scenes%'

GROUP BY

피봇 테이블처럼 여러 세그먼트를 구성
그룹별로 값을 요약하기 위해 COUNT, SUM 등 집계 함수와 같이 사용할 수 있다.
GROUP BY에는 SELECT에 사용된 열 중 집계 함수가 적용되지 않은 모든 열이 와야 한다. 각 그룹(e.g. rating)의 고유한 값(e.g. PG-13, NC-17)을 식별하기 위해 필요하다.
GROUP BY 없이 집계 함수를 사용하면 데이터베이스는 어떤 기준으로 그룹을 나눠야 할지 알 수 없어 오류가 발생할 수 있다.

SELECT
	rating,
    COUNT(film_id)
FROM film
GROUP BY
	rating;

-- how long our movies tend to be rented out for
-- a count of titles sliced by rental duration?
SELECT
	rental_duration,
    count(film_id) as films_with_this_rental_duration
FROM film
GROUP BY rental_duration;

다중 차원[multi dimensions] GROUP BY

SELECT
	rental_duration,
    rating,
    replacement_cost,
    count(film_id) as films_with_this_rental_duration
FROM film
GROUP BY
	rental_duration,
    rating,
    replacement_cost;

HAVING

그룹화된 데이터에 대한 필터링 조건을 설정하는 데 사용한다.

HAVING 절은 반드시 GROUP BY 절과 함께 사용해야 한다. 그룹이 없다면 필터링 대상이 없기 때문.
WHERE 절과 차이점: WHERE 절은 전체 데이터를 대상으로 필터링하는 반면, HAVING 절은 그룹화된 데이터를 대상으로 필터링한다.

HAVING COUNT(*) > 1
HAVING SUM(payment) > 10
HAVING MIN(rental_date) < '2005-05-25'

ORDER BY

쿼리 마지막 위치에서 쿼리 결과를 정렬. 기본값은 오름차순(ASC)이나, 내림차순(DESC)도 가능.

여러 열을 사용할 경우, 첫 번째 열을 기준으로 정렬하고, 동일한 값이 있을 때 두 번째 열을 기준으로 정렬하는 방식으로 진행된다.

SELECT customer_name, order_date, total_amount
FROM orders
ORDER BY order_date DESC, total_amount ASC;

CASE WHEN .. THEN ... ELSE error_message END as ....

CASE로 시작해서 WHEN.. THEN ... 을 넣고 END로 끝.

CASE WHEN logic1 THEN value1 WHEN logic2 THEN value2 ELSE value3 END

CASE
 WHEN category IN ('horror', 'suspense') THEN 'too scary'
 WHEN length > 90 THEN 'too long'
 ELSE 'we should see it'
END

ELSE에 오류 확인 메세지를 꼭 넣기를 추천한다. '어라.. 로직 확인해봐!" 처럼 써서, 에러를 잡아내기 좋다.

SELECT DISTINCT
	length,
    CASE
    	WHEN length < 60 THEN 'under 1 hr'
        WHEN length BETWEEN 60 and 90 THEN '1-1.5 hrs'
        WHEN length > 90 THEN 'over 1.5 hrs'
        ELSE 'uh oh..check logic!'
    END As length_bucket
FROM film

조건이 여러 개면 가장 위에서 만족한 조건이 적용된다. 위에서부터 아래로.

END에 도달할 때까지 아무 조건도 만족하지 않으면, NULL 값이 반환된다.

-- Q: I'd like to know which store each customer goes to, and whether or not they are active.
-- Could you pull a list of first and last names of all customers, and label them as either 'store 1 active', 'store 1 inactive', 'store 2 active', or 'store 2 inactive'?

SELECT *
FROM CUSTOMER;

SELECT DISTINCT active
from CUSTOMER;

SELECT
	first_name,
    last_name,
    store_status,
	CASE
		WHEN store_id = 1 AND active = 1 THEN 'store 1 active'
		WHEN store_id = 1 AND active = 0 THEN 'store 1 inactive'
		WHEN store_id = 2 AND active = 1 THEN 'store 2 active'
		WHEN store_id = 2 AND active = 0 THEN 'store 2 inactive'
		ELSE 'error!!'
	END AS store_status
FROM customer

CASE Pivot: COUNT 안에 CASE문을 써서 엑셀 Pivot 테이블처럼 사용

Use GROUP BY to define row labels, and CASE to pivot to columns

SELECT
    film_id,
	COUNT(CASE WHEN store_id = 1 THEN film_id ELSE NULL END) AS count_of_store_1_inventory,
    COUNT(CASE WHEN store_id = 2 THEN film_id ELSE NULL END) AS count_of_store_2_inventory
FROM inventory
GROUP BY
	film_id
ORDER BY
	film_id

-- I'm curious how many inactive customers we have at each store.
-- Could you please create a table to count the number of customers broken down by store_id (in rows),
-- and active status (in columns)?

SELECT *
FROM 
customer;

SELECT store_id,
	COUNT(CASE WHEN active = 1 THEN 1 ELSE NULL END) AS count_of_active,
    COUNT(CASE WHEN active = 0 THEN 1 ELSE NULL END) AS count_of_inactive
FROM customer
GROUP BY store_id;

데이터베이스 정규화

중복 최소화[minimize redundancy] & 데이터 무결성 보장[preserve data integrity] 과정

이점: 중복 데이터 제거로 저장 공간, 쿼리 처리 효율 증대, 에러 및 이상 현상[anomalies] 방지

정규화는 하나의 큰 테이블을 여러 개의 관련된 테이블로 분리하는 과정이다. 각 테이블은 고유한 값을 가지는 단일 레코드를 포함하며, 다른 테이블과는 외래 키(Foreign Key, FK)와 기본 키(Primary Key, PK)를 통해 연결된다.

e.g. 영화와 주소 정보가 포함된 하나의 테이블에서 영화 정보와 주소 정보를 각각 별도의 테이블로 분리하고, 영화 ID, 주소 ID를 사용하여 연결하는 방식으로 정규화한다.

카디널리티[Cardinality]

카디널리티는 테이블 간의 관계에서 각 엔티티가 다른 엔티티와 맺을 수 있는 인스턴스의 수

관계의 종류

일대일 관계(One-to-One): 한 테이블의 레코드가 다른 테이블의 하나의 레코드와만 연결되는 관계.
일대다 관계(One-to-Many): 한 테이블의 레코드가 다른 테이블의 여러 레코드와 연결되는 관계.
다대다 관계(Many-to-Many): 한 테이블의 레코드가 다른 테이블의 여러 레코드와 연결되고, 그 반대도 성립하는 관계.

예시

영화 테이블과 영화사 테이블의 관계: 일대다 관계 (한 영화사는 여러 영화를 제작할 수 있지만, 한 영화는 하나의 영화사에만 속함)
고객 테이블과 주문 테이블의 관계: 일대다 관계 (한 고객은 여러 주문을 할 수 있지만, 한 주문은 하나의 고객에게만 속함)

주요 개념

기본 키(Primary Key): 테이블에서 각 레코드를 유일하게 식별하는 열. 중복되지 않으며 NULL 값을 가질 수 없음.
외래 키(Foreign Key): 다른 테이블의 기본 키를 참조하는 열. 중복 가능하며 NULL 값을 가질 수 있음.

INNER JOIN 테이블들에서 겹치는 부분만.

두 테이블에 공통으로 존재하는 데이터만 반환한다.

FROM 왼쪽테이블이름 INNER JOIN 오른쪽테이블이름

INNER JOIN 오른쪽테이블이름 ON 왼쪽테이블.열이름 = 오른쪽테이블.열이름

FROM rental
INNER JOIN customer
ON rental.customer_id = customer.customer_id

INNER JOIN 예시

SELECT DISTINCT
	inventory.inventory_id, 
FROM inventory
	INNER JOIN rental
		ON inventory.inventory_id = rental.inventory_id;

inventory와 rental이라는 두 개의 테이블을 연결한다.
ON 절을 사용하여 inventory.inventory_id와 rental.inventory_id가 일치하는 데이터만 선택한다.

inventory_id 에러 발생: inventory_id가 두 테이블에 모두 존재하기 때문에 시스템이 어떤 테이블의 inventory_id를 사용할지 모호해져 에러가 발생한다.

에러를 해결하려면: 테이블 이름을 명확하게 지정한다. inventory.inventory_id 또는 rental.inventory_id로 사용해야 한다.

여러 테이블을 조인할 때는 모든 열에 테이블 이름을 명시하는 것이 좋다. 어떤 테이블의 데이터를 참조하는지 명확하게 하여 오류를 방지하고, 가독성도 높인다.

-- Can you pull for me a list of each film we have in inventory?
-- I would like to see the film's title, description, and the store_id value
-- associated with each item, and its inventory_id.
SELECT DISTINCT
	inventory.inventory_id, film.title, film.description, inventory.store_id
FROM film
	INNER JOIN inventory
    ON inventory.film_id = film.film_id;

LEFT JOIN

왼쪽 테이블의 모든 레코드와 오른쪽 테이블에서 일치하는 레코드를 반환한다. 왼쪽 테이블의 모든 데이터가 포함되며, 오른쪽 테이블에는 일치하는 데이터가 없으면 NULL 값으로 채워진다.

FROM 왼쪽테이블이름 LEFT JOIN 오른쪽테이블이름

- RIGHT JOIN: 오른쪽 테이블의 모든 레코드와 왼쪽 테이블에서 일치하는 레코드를 반환한다. 이는 LEFT JOIN과 반대되는 결과를 제공한다. 실무에서는 LEFT JOIN을 더 많이 사용한다.

-- One of our investors is interested in the films we carry and how many actors are listed for each film title.
-- Can you pull a list of all titles, and figure out how many actors are associated with each title?"
SELECT
	film.title,
    COUNT(film_actor.actor_id) number_of_actors
FROM film
LEFT JOIN
	film_actor
    on film.film_id = film_actor.film_id
GROUP BY
	film.title

INNER JOIN을 쓰게 되면 배우가 없는 영화는 출력되지 않는다.

INNER JOIN은 film_actor.actor_id 값이 없는 film title을 결과에 포함하지 않는다. // LEFT JOIN은 film_actor.actor_id 값이 없어도 title을 반환한다.

FULL (OUTER) JOIN

두 테이블의 모든 레코드를 반환한다. 일치하는 레코드는 하나의 행으로, 일치하지 않는 레코드는 NULL 값으로 표현된다.

FROM 왼쪽테이블이름 FULL JOIN 오른쪽테이블이름

실무에 잘 쓰이진 않으나, 두 테이블의 모든 데이터를 합칠 때 유용하다.

서로 직접 연결되지 않은 테이블 연결하기

두 개의 테이블이 직접적으로 연관되지 않을 때, 공통된 키를 포함하고 있는 세 번째 테이블을 찾아보는 게 좋다. 이 세 번째 테이블이 "다리" 역할을 해서 두 테이블을 연결할 수 있다.
예를 들어, customer 테이블과 city 테이블을 직접 연결할 수 있는 키가 없지만, customer 테이블을 address 테이블과 address_id로 연결하고, 다시 address 테이블을 city 테이블과 city_id로 연결할 수 있다. 이 경우 address 테이블이 city, customer 두 테이블을 연결해주는 다리 역할을 한다.

-- Customers often ask which films their favorite actors appear in.
-- It would be great to have a list of all actors, with each title that they appear in.
-- Could you please pull that for me?
SELECT
	actor.first_name,
    actor.last_name,
    film.title
FROM actor
JOIN film_actor
	ON actor.actor_id = film_actor.actor_id
JOIN film
	ON film.film_id = film_actor.film_id

다중 조건 조인 [Multi-Condition Joins]

두 테이블을 조인할 때, 특정 조건을 만족하는 데이터만 가져오려면 조건을 `WHERE` 절에 넣거나 `JOIN`에 포함할 수 있다.

WHERE; `film`과 `category` 테이블을 조인한 후, `WHERE` 절을 사용해 카테고리가 'horror'인 영화만 필터링할 수 있다. 이 방식은 먼저 모든 데이터를 가져오고 나서 'horror' 카테고리의 영화를 추려낸다.
JOIN ON .. AND ..; `JOIN` 자체에 AND 조건을 추가해 'horror'인 데이터만 조인할 수도 있다. 조건에 맞는 데이터만 조인에 포함되기 때문에, 불필요한 데이터를 걸러내고 필요한 데이터만 선택적으로 조인하여, 쿼리 성능이 개선될 수 있다.

SELECT f.title, c.name 
FROM film f
JOIN film_category fc ON f.film_id = fc.film_id
JOIN category c ON fc.category_id = c.category_id
WHERE c.name = 'Horror';

SELECT f.title, c.name 
FROM film f
JOIN film_category fc ON f.film_id = fc.film_id
JOIN category c ON fc.category_id = c.category_id AND c.name = 'Horror';

두 방법 모두 동일한 결과를 반환하지만, 성능 면에서 차이가 있을 수 있다.

일반적으로 where 필터링보다 JOIN ON에 조건을 추가하면, 불필요한 데이터를 조인하지 않고 필터링할 수 있어 성능이 더 좋다. 그러나 일부 경우에는 데이터베이스 엔진이 WHERE 절을 최적화해 비슷한 성능을 낼 수 있다. 따라서 성능을 평가할 때는 실제 쿼리를 실행해보고 결과를 비교하는 것이 중요하다.

-- The Manager from Store 2 is working on expanding our film collection there.
-- Could you pull a list of distinct titles and their descriptions,
-- currently available in inventory at store 2?
SELECT DISTINCT
	film.title,
    film.description
FROM film
	INNER JOIN inventory
		ON film.film_id = inventory.film_id
		AND inventory.store_id = 2;

UNION

두 개의 테이블에서 선택된 데이터를 하나의 결과 집합으로 합친다.

SELECT FROM 첫번째테이블이름 UNION SELECT FROM 두번째테이블이름

Union은 첫 번째 SELECT 문을 통해 첫 번째 테이블의 모든 데이터를 가져오고, 두 번째 테이블의 데이터를 그 뒤에 추가하는 방법이다. 예를 들어, advisor 테이블에서 first name과 last name을 선택하고, 이를 investor 테이블에서 가져온 동일한 필드와 합친다. 이 과정에서 두 테이블의 결과를 위아래로 쌓는 것과 같은 효과를 얻게 된다.

Union은 기본적으로 중복된 데이터를 제거하고, 고유한 값만 결과에 남긴다. 중복된 데이터를 모두 포함하고 싶다면 UNION ALL을 사용해야 한다.
Union을 사용할 때 주의할 점:
1. 두 SELECT 문이 동일한 열 수를 가져야 한다.
2. 선택된 열의 순서가 동일해야 한다.
3. 각 테이블에서 선택된 열이 동일한 데이터 타입을 가져야 한다.

주의: JOIN은 옆으로 행들을 연결하는 반면, UNION은 테이블들을 위아래로 쌓는 형태로 데이터를 합친다.

-- We will be hosting a meeting with all of our staff and advisors soon.
-- Could you pull one list of all staff and advisor names,
-- and include a column noting whether they are a staff membmer or advisor? Thanks!
SELECT
	'staff' AS type,
	first_name,
    last_name
FROM staff
UNION
SELECT
	'advisor' AS type,
	first_name,
    last_name
FROM advisor

유데미 강의

SQL for Data Analysis: Beginner MySQL Business Intelligence

저작자표시 비영리 변경금지

'IT.모바일 > 배움' 카테고리의 다른 글

이커머스 SQL (0)	2024.08.31
뉴스레터 (0)	2024.07.19
뉴스레터 발송 \| Meta 광고 종료 (0)	2024.07.18
뉴스레터 \| (0)	2024.07.17
카톡 플친 메세지, Meta 영상광고 \| 유데미 취업 부트캠프 - 디지털 마케팅 27일차 (0)	2024.07.16

May Your Days Bueno

SQL 실습 | MYSQL 설치 GROUP BY WHERE IN LIKE ORDER CASE JOIN UNION 유데미 추천

무료 MySQL (Mac M 시리즈) 다운로드 & 셋업

1. Comunity Server 다운로드: SQL 동작 가능

2. MySQL Workbench 다운로드: SQL 쿼리 작성 및 동작 가능

3. Workbench를 Server에 연결: server에 연결하면 SQL 쿼리를 동작시키는 Workbench를 사용 가능

4. Workbench Interface 확인: Workbench 레이아웃, 주요 구성 요소에 익숙해지기

5. Database 생성: SQL로 16개 테이블 데이터베이스 생성

SQL SELECT문 BIG 6 요소

SELECT DISTINCT

많이 쓰는 논리 연산자

WHERE BETWEEN

WHERE OR

WHERE IN()

WHERE LIKE

GROUP BY

다중 차원[multi dimensions] GROUP BY

HAVING

ORDER BY

CASE WHEN .. THEN ... ELSE error_message END as ....

CASE Pivot: COUNT 안에 CASE문을 써서 엑셀 Pivot 테이블처럼 사용

데이터베이스 정규화

카디널리티[Cardinality]

관계의 종류

예시

주요 개념

INNER JOIN 테이블들에서 겹치는 부분만.

INNER JOIN 예시

LEFT JOIN

FULL (OUTER) JOIN

다중 조건 조인 [Multi-Condition Joins]

UNION

유데미 강의

SQL for Data Analysis: Beginner MySQL Business Intelligence

'IT.모바일 > 배움' 카테고리의 다른 글

댓글

티스토리툴바

SQL 실습 | MYSQL 설치 GROUP BY WHERE IN LIKE ORDER CASE JOIN UNION 유데미 추천

무료 MySQL (Mac M 시리즈) 다운로드 & 셋업

1. Comunity Server 다운로드: SQL 동작 가능

2. MySQL Workbench 다운로드: SQL 쿼리 작성 및 동작 가능

3. Workbench를 Server에 연결: server에 연결하면 SQL 쿼리를 동작시키는 Workbench를 사용 가능

4. Workbench Interface 확인: Workbench 레이아웃, 주요 구성 요소에 익숙해지기

5. Database 생성: SQL로 16개 테이블 데이터베이스 생성

SQL SELECT문 BIG 6 요소

SELECT DISTINCT

많이 쓰는 논리 연산자

WHERE BETWEEN

WHERE OR

WHERE IN()

WHERE LIKE

GROUP BY

다중 차원[multi dimensions] GROUP BY

HAVING

ORDER BY

CASE WHEN .. THEN ... ELSE error_message END as ....

CASE Pivot: COUNT 안에 CASE문을 써서 엑셀 Pivot 테이블처럼 사용

데이터베이스 정규화

카디널리티[Cardinality]

관계의 종류

예시

주요 개념

INNER JOIN 테이블들에서 겹치는 부분만.

INNER JOIN 예시

LEFT JOIN

FULL (OUTER) JOIN

다중 조건 조인 [Multi-Condition Joins]

UNION

유데미 강의

SQL for Data Analysis: Beginner MySQL Business Intelligence

'IT.모바일 > 배움' 카테고리의 다른 글

관련글

댓글

티스토리툴바