[패스트캠퍼스] 데이터분석 부트캠프12기 학습일지_05

 

---

 

---

2023년 12월 18일부터 데이터분석 부트캠프를 진행하게 되었습니다.

오늘은 2024년 02월 02일로 시작한지 47일째 되는 날이네요!

---

파이썬 프로젝트가 끝나고 바로 SQL 강의를 들었습니다.

현재 SQL 문법 강의는 다 듣고 코딩테스트를 위한 문제를 계속 풀고 있습니다 :)

 

지금까지 배운 SQL 문법들을 정리해보겠습니다!

 

---

* 쿼리를 작성하기 전에 각 테이블에 어떤 컬럼이 있는지 확인하고 문제를 풀면 더 편하게 풀 수 있었습니다.

---

내용정리 1

1. 데이터베이스 생성

2. 테이블 생성

3. 수정

컬럼 타입 수정 MODIFY COLUMN 

컬럼명 수정 CHANGE COLUMN 

새로운컬럼 추가 ADD COLUMN

 

 

1. 데이터베이스 생성 방법

# 1. 데이터베이스 생성 방법
create database woojin;

1-1. 생성한 데이터베이스 사용 방법

use woojin;

1-2. 생성한 데이터베이스 모두 보기

show databases;

1-2 생성한 데이터베이스 삭제하기

drop database woojin;

 

2. 데이터베이스 테이블 생성 방법

# 데이터베이스 테이블 생성 방법
create table friends( # 테이블에 들어가는 컬럼들 생성
	id int [unsigned] [not null] [auto_increment]
	);

• [ ] 는 옵션임 

• 한 컬럼에 해당하는 여러 옵션들은 , (콤마)로 구분하지 않음

• 숫자값인데 -값이 안오는것은 unsigned로 지정하고 int라고 함 

• not null : 해당 컬럼에는 빈값이 오면 안됨

• auto_increment : 특정값들은 유니크여야함(프라이머리키), 일일히 지정하기 어려울 때,

내부에서 자체적으로 증가시켜 부여함

 

2-1. 생성된 데이터베이스의 테이블 보기

show tables;

2-2 테이블 수정하기

-- 컬럼 타입옵션 변경하기
alter table WOOJINTB
modify column name varchar(20) not null;

-- 컬럼 이름 변경하기
ALTER TABLE WOOJINTB
CHANGE COLUMN modelN model_num varchar(10) NOT NULL;

-- 새로운 컬럼 추가
alter table WOOJINTB
add column PHONE VARCHAR(10) not null;

• 컬럼 타입 옵션 변경하기

ALTER TABLE 테이블명 MODIFY COLUMN 컬럼명 바꾸고싶은타입 [옵션];

 

• 컬럼 이름 변경하기

ALTER TABLE 테이블명 CHANGE COLUMN 기존컬럼명 바꾸고싶은 컬럼명 [옵션];

 

•  새로운 컬럼 추가

ALTER TABLE 테이블명 ADD COLUMN 추가하고싶은 컬럼명 [옵션];

 

---

---

내용정리 2

1. GROUP BY : 특정 카테고리, 컬럼으로 묶기

2. ORDER BY : 정렬하기 (기본 오름차순 ASC, 내림차순 DESC)

3. 집계함수 COUNT, SUM, AVG, MAX, MIN,

4. DISTINCT : 중복값 없이 조회하기

5. HAVING : GROUP BY로 묶은 값들을 계산 할 때 (집계함수 사용)

 

 

1. GROUP BY : 특정 카테고리, 컬럼으로 묶기

-- 국가별로 총 Population과 해당하는 국가코드를 검색하기
SELECT CountryCode,SUM(Population) FROM	city GROUP BY CountryCode;

각 국가별로 국가코드와 각 국가의 인구수를 볼 수 있다. 

 

2. ORDER BY : 정렬하기 (기본 오름차순 ASC, 내림차순 DESC)

country 테이블에서 각 Region 별로 IndepYear가 1800이상인 국가의 평균 GNP를 하위 5개 검색
SELECT AVG(GNP) FROM country WHERE IndepYear>=1800 GROUP BY Region ORDER BY AVG(GNP) LIMIT 5;

 

3.  집계함수 사용하기

payment 테이블에서 렌탈비용 합계, 평균, 최대값, 최소값 검색하기
SELECT SUM(amount),AVG(amount),MAX(amount),MIN(amount) FROM payment;

 

4. 중복값 없이 검색하기 

# film 테이블의 등급(rating) 종류를 조회하기
SELECT DISTINCT rating FROM	film GROUP BY rating;

film 테이블의 등급(rating) 종류를 조회하기

 

5. HAVING

# 각 카테고리의 영화의 수가 30개 이상인 장르를 출력하시오
SELECT name, COUNT(*)
FROM category c
JOIN film_category fc ON c.category_id = fc.category_id
GROUP BY name
HAVING COUNT(*) >= 30;

각 카테고리의 영화의 수가 30개 이상인 장르를 출력하기

---

---

내용정리 3

 

1. 서브쿼리

2. 인덱스

3. 다양한 함수

4. JOIN

 

1. 서브쿼리

# 카테고리가 Family 인 영화의 렌탈 횟수 출력하기
SELECT COUNT(*) FROM rental
WHERE inventory_id IN (
	SELECT inventory_id FROM inventory
    WHERE film_id IN (
		SELECT film_id FROM film_category
        WHERE category_id IN (
			SELECT category_id FROM category
            WHERE name = "Family"
        )
    )
);

 

2. 인덱스 

•  인덱스를 왜 만들까? : 내부적으로 데이터를 빨리 찾을 수 있음

•  인덱스 종류: 클러스터형 인덱스, 보조 인덱스

•  프라이머리키 : 클러스터형 인덱스

•  유니크, 포린키 : 보조 인덱스

•  인덱스 생성 : CREATE INDEX 인덱스명 ON 테이블명(컬럼명);

•  인덱스 확인 : SHOW INDEX FROM 테이블명;

•  인덱스 삭제 : ALTER TABLE 테이블명 DROP INDEX 인덱스명;

 

3. 다양한 함수

•  LENGTH() : 문자열의 길이 조회

•  LOWER,UPPER :  문자열을 소문자,대문자로 변환

•  CONCAT : 두개 이상의 문자열 하나로 연결

•  SUBSTRING : 문자열에서 부분 문자열 추출

•  DATE_ADD : 날짜에 간격 추가하기 #year, month, day, minute, second

•  DATE_SUB : 날짜에 간격 빼기하기 #year, month, day, minute, second

•  EXTRACT : 날짜에서 일부만 추출 #year, month, day, month, minute, second

•  HOUR(), MINUTE(), SECOND() : 해당하는 시간부분만 확인

•  DAYOFWEEK : 요일 확인

•  TIMESTAMPDIFF : 두 날짜 사이의 기간 계산 #year, month, day, month, minute, second

•  DATE_FORMAT: 날짜 형식 변환

•  ABS : 숫자의 절대갓 반환

•  CEIL : 숫자보다 크거나 같은 가장 작은 정수 반환

•  FLOOR : 숫숫자 이하의 가장 큰 정수 반환

•  ROUND : 소수점 N번째에 반올림

•  SQRT : 제곱근

 

4. JOIN : INNER JOIN, OUTER JOIN

# INNER JOIN
select * from ranking r
join items i on i.item_code = r.item_code
where r.item_ranking <=5 
order by item_ranking;

#OUTER JOIN: [LEFT],[RIGHT]
SELECT COUNT(a.address_id)
FROM address a
LEFT OUTER JOIN customer c ON a.address_id = c.address_id
WHERE c.address_id is NULL;

---

---

내용정리 4

1. UNION, UNION ALL, INTERSECT, EXCEPT

2. TRANSACTION

3. VIEW

4. WITH

5. CASE WHEN

 

 

3. VIEW 생성, 삭제, 수정

# VIEW  생성하기
CREATE VIEW ActorName AS
SELECT first_name, last_name
FROM actor
WHERE actor_id < 10;

# VIEW 삭제하기
DROP VIEW ActorName;

# VIEW 수정하기
CREATE OR REPLACE VIEW ActorName AS
SELECT first_name, last_name
FROM actor
WHERE actor_id < 30;

 

4. WITH

WITH Film_Inventory AS (
SELECT DISTINCT film_id FROM inventory
)
SELECT F.film_id, F.title
FROM film F
JOIN Film_Inventory FI ON F.film_id = FI.film_id;

내가 조회한 쿼리를 새테이블처럼 보이게 해서 그 다음 쿼리에서 방금 만든 테이블을 사용할수 있다.

다만 일회성이다.

---

 

여기까지 SQL 기본 문법을 익히고 다양한 예제를 풀면서 코딩테스트 준비를 하고있습니다!

 

 

 

---