데이터분석 부프캠프 14기

728x90

◆ 목차

MySQL
실습 문제 풀기

Mysql

1. 데이터

◆ 데이터

- 컴퓨터가 처리할 수 있는 문자, 숫자, 소리, 그림 따위의 형태로 된 정보

◆ 데이터베이스(DB)

- 여러 사람이 공유하고 사용할 목적으로 통합 관리되는 데이터 저장소

■ 특징

1) 자료를 구조화하여 저장하기 때문에, 효율적인 관리가 가능하다.

2) 여러 업무에 여러 사용자가 동시에 사용 가능하다.

3) 사용자가 데이터베이스의 기능을 사용하기 위해서는 응용 프로그램을 활용해야 한다.

→ 여기서 말하는 응용 프로그램이란? DBMS

◆ DBMS

- 데이터베이스의 기능을 제공하는 프로그램

■ 종류

- Oracle, MySQL, MSSQL, MariaDB

2. SQL

◆ SQL

- 데이터베이스와 대화하기 위해 사용하는 약속의 언어

■ 활용

1) 사용자가 요청을 → SQL 사용 → DBMS에 요청할 SQL을 입력하여 데이터베이스(DB)로 보냄

2) 데이터베이스에서 결과 → DBMS에 전달 → 사용자에게 보여줌

☞ 여기서 SQL에 입력하는 문장을 "쿼리"라고 함

3. MySQL

☞ 설치는 SQL 메뉴에서 설치 방법 올려져 있음

◆ Workbench 사용법

-- 주석 달기 (--, #) 사용

-- 여러줄 주석
/*
1
2
*/

-- 모든 데이터베이스 목록 보기
show databases;  

-- 데이터베이스 만들기
CREATE DATABASE mydatabase;

-- 사용할 데이터베이스 지정하기
USE mydatabase;

-- 테이블 만들기
CREATE TABLE mytable (
		col1 INT,
		col2 CHAR(2)
);

-- 사용할 데이터베이스 지정하기
INSERT INTO mytable (col1, col2)
VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'), (5, 'e');

-- 모든 데이터 가져오기
SELECT * FROM mytable;

4. MySQL - 데이터베이스 다루기

◆ 데이터 타입

- 숫자, 문자, 그림, 영상 데이터를 말한다.

■ 데이터 이해하기

- 정수형 : 소수점이 없는 숫자 데이터 ex) 123

- 실수형 : 소수점이 있는 숫자 데이터 ex) 1.12

☞ 정수형 + 실수형 = 숫자형

- 문자형 : 텍스트로 구성된 문자열 데이터 ex) "ABC", "가가가"

- 날짜형 : 날짜와 시간 데이터 ex) "2024-06-05 11:06:01"

■ 데이터 이해해야 하는 이유

- MySQL에서는 데이터를 저장하기 전에 저장 공간의 데이터 타입을 미리 정해줘야 함

ex) 저장공간 = 날짜형, 문자형, 숫자형 데이터 따로 지정

→ 숫자형 데이터 저장 공간에 123 저장 가능, "ABC" 저장 불가능

◆ 데이터 종류

■ 숫자형 데이터 타입

- 정수형 : 소수점이 없는 숫자 데이터

(TINYINT, SMALLINT, MEDIUMINT, INT, BIGING)

- 실수형 : 소수점이 있는 숫자 데이터 -> 정수형+실수형 합쳐서 숫자형

(FLOAT, DOUBLE)

■ 문자형 데이터 타입

- 문자형 : 텍스트로 구성된 문자열 데이터/ 문자형 데이터를 사용 시 " " 또는 ''를 함께 사용

(CHAR(n), VARCHAR(n), TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT)

■ 날짜형 데이터 타입

- 날짜형 : 날짜나 시간을 나타낼 때 사용되는 데이터

(DATE, DATETIME, TIME, YEAR)

-> MySQL에서는 데이터 저장하기 전에 저장 공간의 데이터 타입을 미리 정해야 하고 정해둔 데이터 타입과 저장할 데이터 타입이 맞지 않으면 저장은 X

◆ 데이터 사용하기

■ 숫자형 데이터

- 데이터 간 연산 가능

SELECT 1 + 2;   -- 3
SELECT 20 / 5;  -- 4.0000

■ 문자형 데이터

- " " 또는 ' ' 사용, 따옴표를 사용하지 않으면 키워드나 함수, 데이터베이스/테이블/컬럼의 이름으로 인식

SELECT id;    -- 20221122
SELECT "id";  -- id

5. MySQL - 테이블 만들기

◆ 테이블

1) 테이블이란?

- 데이터베이스에서 데이터를 형태로 정해 모아 놓은 저장 공간(행/열로 이루어진 데이터 표)

2) 컬럼(열)

- 데이터를 저장하기 위한 틀(컬럼 이름은 동일한 테이블 내에서 중복X)

3) 행

- 관계된 값의 리스트

4) 값

- 컬럼에 속한 실제 데이터 값

■ 데이터베이스의 의미

1) 여러 사람이 공유하고 사용할 목적으로 통합 관리되는 데이터 저장소

2) 테이블을 저장해 두는 저장소 = 스키마(Schema)

※ 테이블 만드는 순서

- 데이터베이스 생성 → 데이터베이스 내에 테이블 생성

◆ 데이터베이스, 테이블, 컬럼 이름 정하기 규칙

1. 문자, 숫자, _를 사용

2. 이름에 쓰이는 문자는 주로 영문 소문자 사용

3. 예약어는 사용X

4. 단어와 단어 사이는 빈칸대신 _ 를 사용

5. 문자로 시작(숫자, _로 시작은 X)

6. 데이터베이스 이름은 중복 불가능

◆ 여러 쿼리 사용

■ 데이터베이스의 만들기

CREATE DATABASE [데이터베이스 이름];

■ 데이터베이스 목록 보기

SHOW DATABASES;

■ 데이터베이스 사용하기

USE [데이터베이스 이름];

■ 테이블 만들기

CREATE TABLE [테이블 이름] (
	[컬럼이름] [데이터 타입],
        [컬럽이름] [데이터 타입], ...
);

■ 테이블 이름 변경

ALTER TABLE [테이블 이름] RENAME [새로운 테이블 이름];

■ 새로운 컬럼 추가

ALTER TABLE [테이블 이름] ADD COLUMN [컬럼이름] [데이터타입];

■ 기존 컬럼 타입 변경하기

ALTER TABLE [테이블 이름] MODIFY COLUMN [컬럼이름] [새로운 데이터 타입];

■ 기존 컬럼 이름과 타입 변경하기

ALTER TABLE [테이블 이름]
CHANGE COLUMN [컬럼이름] [새로운 컬럼이름] [새로운 데이터 타입];

■ 컬럼 지우기

ALTER TABLE [테이블 이름] DROP COLUMN [컬럼이름];

6. MySQL - 테이블 지우기

■ 데이터베이스 지우기

DROP DATABASE [데이터베이스 이름];

■ 테이블 지우기

DROP TABLE [테이블 이름];

■ 테이블 값만 지우기

TRUNCATE TABLE [테이블 이름];

■ 데이터베이스 / 테이블이 존재한다면 지우기

DROP DATABASE IF EXISTS [데이터베이스 이름];

DROP TABLE IF EXISTS [테이블 이름];

7. MySQL - 데이터 삽입, 삭제, 수정하기

■ 데이터 하나 삽입하기

INSERT INTO [테이블 이름]([컬럼이름1],[컬럼이름2],[컬럼이름3]) 
VALUES([컬럼1 값],[컬럽2 값],[컬럼3 값]);

■ 데이터 삭제하기

DELETE FROM [테이블 이름]
WHERE [조건 값];

■ 데이터 수정하기

UPDATE [데이터 이름]
SET [컬럼 이름]=[새 값]
WHERE [조건 값];

8. MySQL - 데이터 가져오기

◆ SELECT

- 가져올 컬럼을 선택하는 키워드

■ SELECT 특징

- 숫자, 문자 데이터를 가지고 올 수 있음

(SELECT 데이터 형식)

- 컬럼을 선택해 컬럼의 값을 가져올 수 있음

(SELECT [컬럼 이름] 형식

- *를 통해 컬럼 전체를 가져올 수 있음

(SELECT * 형식) / * 는 컬럼 전체를 의미

◆ FROM

- 데이터를 가져올 테이블을 지정하는 키워드

■ FROM 특징

- FROM [테이블 이름] 형식으로 사용

■ 컬럼을 하나만 선택하는 경우

SELECT [컬럼 이름]
FROM [데이터베이스 이름].[테이블 이름];

■ 컬럼을 여러개 선택하는 경우

SELECT [컬럼 이름], [컬럼 이름],...[컬럼 이름]
FROM [데이터베이스 이름].[테이블 이름];

■ 컬럼 전체를 선택

SELECT *
FROM [데이터베이스 이름].[테이블 이름];

■ 데이터베이스 지정

USE [데이터베이스 이름];

SELECT [컬럼 이름]
FROM [테이블 이름];

◆ AS(ALIAS)

- 가져온 데이터에 별명을 지정하는 키워드

■ AS 특징

- AS [컬럼 별명] 형식으로 사용

- 쿼리 내에서만 유효 → 실제 테이블에서 컬럼 이름이 바뀌는건 X

(실제 테이블 컬럼명을 변경하고 싶다면 → ALTER TABLE 사용)

SELECT [컬럼 이름] AS [컬럼 별명]
FROM [테이블 이름];

◆ LIMIT

- 가져올 데이터의 로우 개수를 지정하는 키워드

■ LIMIT 특징

- LIMIT [로우 수] 형식으로 사용

- 쿼리의 가장 마지막에 위치

◆ DISTINCT

- 중복된 데이터는 제외, 같은 값은 한번만 가져오는 키워드

■ DISTINCT 특징

- DISTINCT [컬럼 이름] 형식으로 사용

- SELECT 절에 위치하여 컬럼의 유일한 값들을 가져온다.

9. MySQL - 조건에 맞는 데이터 가져오기

◆ WHERE

- 조건에 맞는 데이터를 가져오는 방법 키워드

■ WHERE 특징

- WHERE [조건식] 형식으로 사용

- 조건식이 True(참)이 되는 row(로우)만 선택

SELECT [컬럼 이름]
FROM [테이블 이름]
WHERE 조건식;

10. MySQL - 연산자 종류

◆ 연산자 종류

■ 비교 연산자

연산자	활용	의미
=	A = B	A와 B가 같다
!=	A != B	A와 B가 같지 않다
>	A > B	A가 B보다 크다
>=	A >= B	A가 B보다 크거나 같다
<	A < B	A가 B보다 작다
<=	A <= B	A가 B보다 작거나 같다

■ 논리 연산자

연산자	활용	의미
AND	A AND B	A와 B 모두 True이면 True
OR	A OR B	A와 B 둘 중 하나만 True이면 True
NOT	NOT A	A가 아니라면 True

■ 기타 주요 연산자

- BETWEEN : 특정 범위 내의 데이터를 선택

[컬럼 이름] BETWEEN A AND B 형식 (A와 B사이에 포함되는 값을 가진 row만 선택)

SELECT [컬럼 이름]
FROM [테이블 이름]
WHERE [컬럼 이름] BETWEEN [조건1] AND [조건2];

- IN : 목록 내 포함되는 데이터를 선택할 때 사용

[컬럼 이름] IN (A,B,...G) 형식(해당 컬럼의 값이 ( ) 내의 값에 포함되는 값을 가진 row만 선택)

[컬럼 이름] = A OR [컬럼 이름] = B 와 동일

SELECT [컬럼 이름]
FROM [테이블 이름]
WHERE [컬럼 이름] IN ([조건1],[조건2],...[조건4]);

11. MySQL - 문자 데이터 다루기

◆ LIKE

- 문자형 데이터 다루는 키워드

■ LIKE 특징

- [컬럼 이름] LIKE [검색할 문자열] 형식으로 사용

- 해당 컬럼 값이 [검색할 문자열]을 포함하고 있는 row(로우)만 선택

- [검색할 문자열] 내에 와일드카드를 사용하여 검색 조건을 구체적으로 표현

SELECT [컬럼 이름]
FROM [테이블 이름]
WHERE [컬럼 이름] LIKE [겸색할 문자열];

■ 와일드카드

와일드카드	의미
%	0개 이상의 문자
_	1개의 문자

12. MySQL - NULL 데이터 다루기

◆ NULL

- 데이터 값이 존재하지 않는다는 표현

- 0이나 공백이 아닌 알 수 없는 값을 의미

예제> 공백과 NULL 비교

--type값을 공백으로 입력
INSERT INTO mypokemon(name, type)
VALUES("kkobugi", "");

입력하지 않은 값들은 NULL 보여지고 공백으로 입력한것은 공백으로 보여짐

◆ IS NULL

- 데이터가 NULL인지 아닌지를 확인하는 연산자

■ IS NULL 특징

- [컬럼 이름] IS NULL 형식으로 사용

- 해당 컬럼이 NULL이 있는 row만 선택

- NULL이 아닌 데이터를 검색하고 싶다면 IS NOT NULL을 사용

SELECT [컬럼 이름]
FROM [테이블 이름]
WHERE [컬럼 이름] IS NULL;

13. MySQL - 데이터 줄세우기

◆ ORDER BY

- 가져온 데이터를 정렬해주는 키워드

■ ORDER BY 특징

- ORDER BY [컬럼 이름] 형식으로 사용

- 입력한 [컬럼 이름]의 값을 기준으로 모든 row 정렬

- 기본 정렬 구칙은 오름차순

ORDER BY [컬럼 이름] = ORDER BY [컬럼 이름] ASC

SELECT [컬럼 이름] FROM [테이블 이름]
WHERE 조건식 ORDER BY [컬럼 이름] ASC;

- 내림차순 정렬

ORDER BY [컬럼 이름] DESC

SELECT [컬럼 이름] FROM [테이블 이름]
WHERE 조건식 ORDER BY [컬럼 이름] DESC;

14. MySQL - 데이터 순위 만들기

◆ RANK

- 데이터를 정렬해 순위를 만들어주는 함수

■ RANK 특징

- RANK( ) OVER(ORDER BY [컬럼 이름]) 형식으로 사용

- 공동 순위가 있으면 다음 순서로 건너 뜀

SELECT [컬럼 이름], ..., RANK() OVER(ORDER BY [컬럼이름] DESC)
FROM [테이블 이름]
WHERE 조건식;

■ DENSE_RANK

- 공동 순위가 있어도 다음 순위를 뛰어 넘지 않음

■ ROW_NUMBER

- 공동 순위를 무시함

15. MySQL - 문자형 데이터

◆ 함수

- 함수 이름(함수를 적용할 값 또는 컬럼 이름) 형식으로 사용

- 결과 값을 새로운 컬럼으로 반환

■ 문자형 데이터 함수 종류

함수	활용 예시	설명
LOCATE	LOCATE("A","ABC")	"ABC"에서 "A"는 몇 번째에 위치해 있는지 검색해 위치 반환
SUBSTRING	SUBSTRING("ABC",2)	"ABC"에서 2번째 문자부터 반환
RIGHT	RIGHT("ABC",1)	"ABC"에서 오른쪽의 1번째 문자까지 반환
LEFT	LEFT("ABC",1)	"ABC"에서 왼쪽의 1번째 문자까지 반환
TRIM	TRIM(" ABC ")	" ABC "에서 양쪽 공백을 제거
UPPER	UPPER("abc")	"abc"를 대문자로 바꿔 반환
LOWER	LOWER("ABC")	"ABC"를 소문자로 바꿔 반환
LENGTH	LENGTH("ABC")	"ABC"의 글자 수를 반환
CONCAT	CONCAT("ABC","DEF")	"ABC" 문자열과 "CDF" 문자열을 합쳐서 반환
REPLACE	REPLACE("ABC", "A", "Z")	"ABC"의 "A"를 "Z"로 바꿔 반환
INSTR	INSTR("ABC DEF", "DEF")	"ABC DEF"의 "DEF"가 처음 등장하는 위치를 반환
REPLACE	REPLACE("AB CD", "CD", "EF")	"AB CD" 에서 "CD" 을 "EF" 로 대체
LPAD()	LPAD( "123", 5, "0")	"0" 을 "123" 왼쪽으로 5 길이의 문자열로 변경

16. MySQL - 숫자형 데이터

■ 숫자형 데이터 함수 종류

함수	활용	설명
ABS	ABS(숫자)	숫자의 절댓값 반환
CEILING	CEILING(숫자)	숫자를 정수로 올림해서 반환
FLOOR	FLOOR(숫자)	숫자를 정수로 내림해서 반환
ROUND	ROUND(숫자, 자릿수)	숫자를 소수점 자릿수까지 반올림해서 반환
TRUNCATE	TRUNCATE(숫자, 자릿수)	숫자를 소수점 자릿수까지 버림해서 반환
POWER	POWER(숫자A, 숫자B)	숫자A의 숫자B 제곱 반환
MOD	MOD(숫자A, 숫자B)	숫자A를 숫자B로 나눈 나머지 반환
SIGN	SIGN(숫자)	숫자의 부호를 반환하는 함수 숫자가 양수인 경우 1, 음수인 경우 -1, 0인 경우 0 반환

17. MySQL - 날짜형 데이터

■ 날짜형 데이터 함수 종류

함수	활용	설명
NOW	NOW( )	현재 날짜와 시간 반환
SYSDATE	SYSDATE( )	현재 시스템 날짜와 시간을 반환
CURRENT_DATE	CURRENT_DATE( )	현재 날짜 반환
CURRENT_TIME	CURRENT_TIME( )	현재 시간 반환
YEAR	YEAR(날짜)	날짜의 연도 반환
MONTH	MONTH(날짜)	날짜의 월 반환
DAY	DAY(날짜)	날짜의 일 반환
MONTHNAME	MOTHNAME(날짜)	날짜의 월을 영어로 반환
DAYNAME	DAYNAME(날짜)	날짜의 요일을 영어로 반환
DAYOFMONTH	DAYOFMONTH(날짜)	날짜의 일 반환
DAYOFWEEK	DAYOFWEEK(날짜)	날짜의 요일을 숫자로 반환
WEEK	WEEK(날짜)	날짜가 해당 연도에 몇 번째 주인지 반환
HOUR	HOUR(시간)	시간의 시 반환
MINUTE	MINUTE(시간)	시간의 분 반환
SECOND	SECOND(시간)	시간의 초 반환
DATE_FORMAT	DATEFORMAT(날짜/시간, 형식)	날짜/시간의 형식을 형식으로 바꿔 반환
DATEDIFF	DATEDIFF(날짜1, 날짜2)	날짜1과 날짜2의 차이 반환(날짜1 - 날짜2)
TIMEDIFF	TIMEDIFF(시간1, 시간2)	시간1과 시간2의 차이 반환(시간1 - 시간2)
ADDDATE	ADDDATE(날짜1, 날짜2)	날짜1에서 날짜2를 더해서 반환(날짜1 + 날짜2)
LAST_DAY	LAST_DAY(날짜1)	주어진 날짜1의 해당 월의 마지막 날짜를 반환
SLEEP	SLEEP(시간)	지정된 초(s) 동안 실행을 지연

18. MySQL - NULL 처리 데이터

■ NULL 처리 데이터 함수 종류

함수	활용	설명
IFNULL	IFNULL("실제데이터", "대체값")	"실제데이터" 값이 NULL 일때 "대체값"으로 반환
COALESCE	COALESCE(NULL, "데이터2", "데이터3")	여러 개의 인자를 받아, 첫번째부터 null이 아닌 값을 반환
NULLIF	NULLIF("데이터1", "데이터2")	두개의 값이 같으면 NULL, 아니면 왼쪽 데이터값 반환
ISNULL	ISNULL("데이터")	값이 NULL이라면 1, NULL이 아니라면 0

19. MySQL - 데이터 그룹화하기

◆ GROUP BY

- 컬럼에서 동일한 값을 가지는 row를 그룹화하는 키워드

■ GROUP BY 특징

- GROUP BY [컬럼 이름] 형식 사용

- 그룹 별 데이터를 집계할 때 사용 = 엑셀의 피벗 기능과 비슷

- GROUP BY가 쓰인 쿼리의 SELECT 절에는 GROUP BY 대상 컬럼과 그룹 함수만 사용 가능

- 여러 컬럼으로 그룹화 가능

- 키워드 뒤에 [컬럼 이름]을 복수 개 입력 가능

SELECT [GROUP BY 대상 컬럼 이름], ...,[그룹 함수]
FROM [테이블 이름]
WHERE 조건식
GROUP BY[컬럼 이름];

예시 1) type이 같은것끼리 그룹화하기

SELECT type FROM mypokemon
GROUP BY type;

20. MySQL - 그룹에 조건 추가하기

◆ HAVING

- 가져올 데이터그룹에 조건을 지정해주는 키워드

■ HAVING 특징

- HAVING 조건식 형식으로 사용

- 조건식이 True(참)이 되는 그룹만 선택

- HAVING 절의 조건식에서는 그룹 함수를 활용

SELECT [컬럼 이름], ...,[그룹 함수]
FROM [테이블 이름]
WHERE 조건식
GROUP BY [컬럼 이름]
HAVING 조건식;

21. MySQL - 다양한 그룹 함수 알아보기

◆ COUNT

- 그룹의 값 수를 세는 함수

■ COUNT 특징

- COUNT([컬럼 이름]) 형식으로 SELECT, HAVING 절에 사용

- COUNT(1)은 하나의 값을 1로 세어주는 표

SELECT [컬럼 이름], ...,COUNT([컬럼 이름])
FROM [테이블 이름]
GROUP BY [컬럼 이름]
HAVING 조건문;

◆ SUM

- 그룹의 합을 계산하는 함수

■ SUM 특징

- SUM([컬럼 이름]) 형식으로 SELECT, HAVING 절에서 사용

SELECT [컬럼 이름], ...,SUM([컬럼 이름])
FROM [테이블 이름]
GROUP BY [컬럼 이름]
HAVING 조건문;

◆ AVG

- 그룹의 평균을 계산하는 함수

■ AVG 특징

- AVG ([컬럼 이름]) 형식으로 SELECT, HAVING 절에서 사용

SELECT [컬럼 이름], ...,AVG([컬럼 이름])
FROM [테이블 이름]
GROUP BY [컬럼 이름]
HAVING 조건문;

◆ MIN

- 그룹의 최솟값을 반환하는 함수

■ MIN 특징

- MIN ([컬럼 이름]) 형식으로 SELECT, HAVING 절에서 사용

SELECT [컬럼 이름], ...,MIN([컬럼 이름])
FROM [테이블 이름]
GROUP BY [컬럼 이름]
HAVING 조건문;

◆ MAX

- 그룹의 최댓값을 반환하는 함수

■ MAX 특징

- MAX ([컬럼 이름]) 형식으로 SELECT, HAVING 절에서 사용

SELECT [컬럼 이름], ...,MAX([컬럼 이름])
FROM [테이블 이름]
GROUP BY [컬럼 이름]
HAVING 조건문;

예시 1) type을 기준으로 그룹화하고 전체 값의 합과 키의 평균값과 몸무게의 최댓값을 가져오기

- COUNT, AVG, MAX 사용

SELECT type, COUNT(*), COUNT(1), AVG(height), MAX(weight) FROM mypokemon
GROUP BY type;

예시 2) type을 기준으로 그룹화하고 전체 값의 합과 키의 평균값과 몸무게의 최댓값을 가져오기. 단, 조건식에 맞는 값만 가져오기.

- COUNT, AVG, MAX 사용

SELECT type, COUNT(*), COUNT(1), AVG(height), MAX(weight) FROM mypokemon
GROUP BY type
HAVING COUNT(1) = 2;

22. MySQL - 쿼리 실행 순서 알아보기

키워드	문법	순서	실행 순서
SELECT	SELECT는 모든 쿼리에 필수 키워드 SELECT [컬럼 이름]	1	5
FROM	FROM [테이블 이름]	2	1
WHERE	WHERE 조건식	3	2
GROUP BY	GROUP BY [컬럼 이름]	4	3
HAVING	HAVING 조건식	5	4
ORDER BY	ORDER BY [컬럼 이름]	6	6

예제 1) 실행 순서

SELECT type, COUNT(1), MAX(weight)  -- 5
FROM pokemon.mypokemon              -- 1
WHERE name LIKE '%a%'               -- 2
GROUP BY type                       -- 3
HAVING MAX(height) > 1              -- 4
ORDER BY 3;                         -- 6

→ 실행 순서 1

mypokemon
number	name	type	height	weight
10	caterpie	bug	0.3	2.9
25	pikachu	electric	0.4	6
26	raichu	electric	0.8	30
125	electabuzz	electric	1.1	30
133	eevee	normal	0.3	6.5
137	porygon	normal	0.8	36.5
152	chikoirita	grass	0.9	6.4
153	bayleef	grass	1.2	15.8
172	pichu	electric	0.3	2
470	leafeon	grass	1	25.5

→ 실행 순서 2

number	name	type	height	weight
10	caterpie	bug	0.3	2.9
25	pikachu	electric	0.4	6
26	raichu	electric	0.8	30
125	electabuzz	electric	1.1	30
152	chikoirita	grass	0.9	6.4
153	bayleef	grass	1.2	15.8
470	leafeon	grass	1	25.5

→ 실행 순서 3

type	number	name	height	weight
bug	10	caterpie	0.3	2.9
electric	25	pikachu	0.4	6
	26	raichu	0.8	30
	125	electabuzz	1.1	30
grass	152	chikoirita	0.9	6.4
	153	bayleef	1.2	15.8
	470	leafeon	1	25.5

→ 실행 순서 4

type	number	name	height	weight
electric	25	pikachu	0.4	6
	26	raichu	0.8	30
	125	electabuzz	1.1	30
grass	152	chikoirita	0.9	6.4
	153	bayleef	1.2	15.8
	470	leafeon	1	25.5

→ 실행 순서 5

type	COUNT(1)	MAX(weight)
electric	3	30
grass	3	25.5

→ 실행 순서 6

type	COUNT(1)	MAX(weight)
grass	3	25.5
electric	3	30

23. MySQL - 실습 문제 풀기

테이블 만들고 데이터 넣기(CREATE, INSERT)

■ 실습 1

pokemon 데이터베이스와 mypokemon 테이블을 만들고 캐터피, 피카츄, 이브이의 포켓몬 번호, 영문 이름, 타입 데이터를 넣기/ 데이터 타입 : number-> int, name-> varchar(20), type-> varchar(10)

→ 실습 코드

pokemon 데이터베이스 생성

CREATE DATABASE pokemon;

pokemon 데이터베이스 사용

USE pokemon;

mypokemon 테이블 만들기

CREATE TABLE mypokemon (
    number INT,
    name VARCHAR(20),
    type VARCHAR(10)
);

mypokemon 테이블에 값 넣기

-- INSERT INTO mypokemon(number, name, type)
VALUES (10, "caterpie", "bug"), (25, "pikachu", "electric"), (133, "eevee", "normal")

■ 실습 2

pokemon 데이터베이스에 mynewpokemon 테이블을 만들고 포니타, 메타몽, 뮤의 포켓몬 번호, 이름, 타입 데이터를 넣기/ 데이터 타입 : number-> int, name-> varchar(20), type-> varchar(10)

→ 실습 코드

mynewpokemon 테이블 생성

CREATE TABLE mynewpokemon (
    number INT,
    name VARCHAR(20),
    type VARCHAR(10)
);

mynewpokemon 테이블에 값 넣기

INSERT INTO mynewpokemon(number, name, type)
VALUES (77, "포니타", "불꽃"), (132, "메타몽", "노말"), (151, "뮤", "에스퍼");

■ 실습 3

pokemon 데이터베이스에 mypokemon 테이블 -> myoldpokemon으로 변경/ myoldpokemon, mynewpokemon 테이블 안에 name을 각각 eng_name, kor_name으로 변경

mypokemon -> myoldpokemon 테이블 이름 변경

ALTER TABLE mypokemon RENAME myoldpokemon;

myoldpokemon 테이블에 name 컬럼 이름을 eng_nm 변경

ALTER TABLE myoldpokemon
CHANGE COLUMN name eng_nm VARCHAR(20);

mynewpokemon 테이블에 name 컬럼 이름을 kor_nm 변경

ALTER TABLE mynewpokemon
CHANGE COLUMN name kor_nm VARCHAR(20);

■ 실습 4

pokemon 데이터베이스에 myoldpokemon 테이블 값만 삭제/ mynewpokemon 테이블 전체 삭제

myoldpokemon 테이블에 값만 지우기

TRUNCATE TABLE myoldpokemon;

mynewpokemon 테이블 전부 삭제

DROP TABLE mynewpokemon;

테이블에서 데이터를 가져오기(SELECT, FROM)

※ 기준 테이블 정보

■ 실습 1 ) 123 곱하기 456

SELECT 123 * 456;

■ 실습 2) 2310 나누기 30

SELECT 2310 / 30;

■ 실습 3

'피카츄'라는 문자열을 '포켓몬'이라는 이름의 컬럼 별명으로 가져오기

SELECT name AS 포켓몬
FROM pokemon.mypokemon;

→ 설명을 잘못 이해함(문자열 피카츄가 들어가있는 열의 컬럴명인 name이라고 생각했음)

SELECT "피카츄" AS "포켓몬";

■ 실습 4

pokemon 테이블에서 모든 포켓몬들의 컬럼과 값 전체를 가져오기

SELECT * FROM pokemon.mypokemon;

■ 실습 5

pokemon 테이블에서 모든 포켓몬들의 이름을 가져오기

SELECT name FROM pokemon.mypokemon;

■ 실습 6

pokemon 테이블에서 모든 포켓몬들의 이름과 키, 몸무게를 가져오기

SELECT name, height, weight FROM pokemon.mypokemon;

■ 실습 7

pokemon 테이블에서 포켓몬들의 키를 중복 제거하고 가져오기

SELECT DISTINCT height FROM pokemon.mypokemon;

■ 실습 8

pokemon 테이블에서 모든 포켓몬들의 공격력을 2배 해 'attact2'라는 별명으로 이름과 함께 가져오기

SELECT name, attack*2 AS attack2 FROM pokemon.mypokemon;

■ 실습 9

pokemon 테이블에서 모든 포켓몬들의 이름을 '이름'이라는 한글 별명으로 가져오기

SELECT name AS 이름 FROM pokemon.mypokemon;

■ 실습 10

pokemon 테이블에서 모든 포켓몬들의 공격력은 '공격력'이라는 한글 별명으로. 방어력은 '방어력'이라는 한글 별명으로 가져오기

SELECT attack AS 공걱력, defense AS 방어력 FROM pokemon.mypokemon;

■ 실습 11

현재 pokemon 테이블의 키 컬럼은 m단위 (1m = 100cm)

pokemon 테이블에서 모든 포켓몬들의 티를 cm단위로 환상하여 'height(cm)'라는 별명으로 가져오기

SELECT height*100 AS "height(cm)" FROM pokemon.mypokemon;

■ 실습 12

pokemon 테이블에서 첫번째 로우에 위치한 포켓몬 데이터만 컬럼값 전체를 가져오기

SELECT * FROM pokemon.mypokemon
LIMIT 1;

■ 실습 13

pokemon 테이블에서 2개의 포켓몬 데이터만 이름은 '영문명'이라는 별명으로, 키는 '키(m)'라는 별명으로, 몸무게는 '몸무게(kg)'이라는 별명으로 가져오기

SELECT name AS 영문명, height AS "키(m)", weight AS "몸무게(kg)" FROM pokemon.mypokemon
LIMIT 2;

■ 실습 14

pokemon 테이블에서 모든 포켓몬들의 이름과 능력치의 합을 가져오고, 이때 능력치의 합은 'total'이라는 별명으로 가져오기(참고, 능력치의 합은 공격력, 방어력, 속도의 합)

SELECT name, attack + defense + speed AS "total" FROM pokemon.mypokemon;

■ 실습 15

pokemon 테이블에서 모든 포켓몬들의 BMI 지수를 구해서 'BMI'라는 별명, 이름과 함께 가지고 오기

조건1 BMI 지수 = 몸무게(kg) / (키(m))2

조건2 pokemon 테이블 데이터의 체중은 kg 단위, 키는 m 단위

(참고, 제곱은 ^ 표현 / 10의 제곱은 10^2 표현)

SELECT name, weight / height^2  AS "BMI" FROM pokemon.mypokemon;

주어진 조건에 맞는 데이터를 가져오기(WHERE)

■ 실습 1) 이브이의 타입을 가져오기

SELECT type FROM mypokemon
WHERE name = "eevee";

■ 실습 2) 캐터피의 공격력과 방어력을 가져오기

SELECT attack, defense FROM mypokemon
WHERE name = "caterpie";

■ 실습 3) 몸무게가 6kg보다 큰 포켓몬들의 모든 데이터를 가져오기

SELECT * FROM mypokemon
WHERE weight > 6;

■ 실습 4) 키가 0.5m보다 크고, 몸무게가 6kg보다 크거나 같은 포켓몬들의 이름을 가져오기

SELECT name FROM mypokemon
WHERE height > 0.5 and weight >= 6;

■ 실습 5)

pokemon 테이블에서 공격력이 50 미만이거나, 방어력이 50 미만인 포켓몬들의 이름을 'weak_pokemon'이라는 별명으로 가져오기

SELECT name AS weak_pokemon FROM mypokemon
WHERE attack < 50 OR defense < 50;

■ 실습 6) 노말 타입이 아닌 포켓몬들의 데이터를 전부 가져오기

SELECT * FROM mypokemon
WHERE type != "normal";

→ 또 다른 방법

SELECT * FROM mypokemon
WHERE NOT(type = "normal");

■ 실습 7)

타입이 normal, fire, water, grass 중에 하나인 포켓몬들의 이름과 타입을 가져오기

SELECT name, type FROM mypokemon
WHERE type IN ("normal", "fire", "water", "grass");

■ 실습 8)

공격력이 40과 60 사이인 포켓몬들의 이름과 공격력을 가져오기

SELECT name, attack FROM mypokemon
WHERE attack BETWEEN 40 AND 60;

→ 또 다른 방법

SELECT name, attack FROM mypokemon
WHERE attack >= 40 AND <= 60;

■ 실습 9)

이름에 'e'가 포함되는 포켓몬들의 이름을 가져오기

SELECT name FROM mypokemon
WHERE name LIKE "%e%";

■ 실습 10)

이름에 ' i '가 포함되고, 속도가 50 이하인 포켓몬 데이터를 전부 가져오기

SELECT * FROM mypokemon
WHERE name LIKE "%i%" AND speed <= 50;

■ 실습 11)

이름이 'chu'로 끝나는 포켓몬들의 이름, 키, 몸무게를 가져오기

SELECT name, height, weight FROM mypokemon
WHERE name LIKE "%chu";

■ 실습 12)

이름이 'e'로 끝나고, 방어력이 50 미만인 포켓몬들의 이름, 방어력을 가져오기

SELECT name, defense FROM mypokemon
WHERE name LIKE "%e" AND defense < 50;

■ 실습 13)

공격력과 방어력의 차이가 10 이상인 포켓몬들의 이름, 공격력, 방어력을 가져오기

SELECT name, attack, defense FROM mypokemon
WHERE attack - defense >= 10 OR defense - attack >= 10;

■ 실습 14)

능력치의 합이 150 이상인 포켓몬들의 이름과 능력치의 합을 가져오기

참고, 능력치의 합 공격력, 방어력, 속도의 합을 말하며 'total'이라는 별명으로 가져오기

SELECT name, attack + defense + speed AS total FROM mypokemon
WHERE attack + defense + speed >= 150;

데이터 요청 대로 만들어보기(ORDER BY)

■ 실습 1)

pokemon 테이블에서 포켓몬의 이름과 이름의 글자 수를 이름의 글자 수로 정렬해서 가져오기

(정렬 순서는 글자 수가 적은것부터 많은것 순으로 하기)

SELECT name, LENGTH(name) FROM mypokemon
ORDER BY LENGTH(name) ASC;

■ 실습 2)

pokemon 테이블에서 방어력 순위를 보여주는 컬럼을 새로 만들어서 'defense_rank'라는 별명과 포켓몬 이름 데이터도 같이 가져오기

조건 1: 방어력 순위란 방어력이 큰 순서대로 나열한 순위를 의미

조건 2: 공동 순위가 있으면 다음 순서로 건너 뛰기

SELECT name, RANK() OVER(ORDER BY defense DESC) AS defense_rank
FROM mypokemon;

■ 실습 3)

pokemon 테이블에서 포켓몬을 포획한 지 기준 날짜까지 며칠이 지났는 지를 'days'라는 별명과 포켓몬의 이름도 같이 가져오기

조건 1: 기준 날짜는 2024년 6월 8일

SELECT name, DATEDIFF("2022-02-14", capture_date) AS days
FROM mypokemon;

■ 실습 4)

포켓몬의 이름을 마지막 3개 문자만, 'last_char'이라는 별명으로 가져오기

SELECT RIGHT(name,3) AS last_char FROM mypokemon;

■ 실습 5)

포켓몬 이름을 왼쪽에서 2개 문자를 'left2'라는 별명으로 가져오기

SELECT LEFT(name,2) AS left2 FROM mypokemon;

■ 실습 6)

포켓몬 이름에서 이름에 o가 포함된 포켓몬만 모든 소문자 o를 대문자 O로 바꿔서 'bigO'라는 별명으로 가져오기

참고: 이름이 'pokemon'일 경우, 'bigO' 값은 'pOkemOn' 이여야함

SELECT REPLACE(name,"o","O") AS bigO FROM mypokemon
WHERE name LIKE "%o%";

■ 실습 7)

포켓몬 타입을 가장 첫번째 글자 1자, 가장 마지막 글자 1자를 합친 후 대문자로 변환해서 'type_code'라는 별명과 이름을 같이 가져오기

참고:타입이 'water'일 경우, 'type_code' 값은 'w'와 'r'를 대문자로 바꾼 'WR'임

SELECT name, UPPER(CONCAT(LEFT(type, 1),RIGHT(type, 1))) AS type_code 
FROM mypokemon;

■ 실습 8)

포켓몬 이름의 글자 수가 8보다 큰 포켓몬의 데이터를 전부 가져오기

SELECT * FROM mypokemon
WHERE LENGTH(name) > 8;

■ 실습 9)

모든 포켓몬의 공격력 평균을 정수로 반올림해서 'avg_of_attack'이라는 별명으로 가져오기

-- SUM을 사용해서 하면 될줄 알았는데 이렇게도 가능하더라....
SELECT CEILING(SUM(attack)/8) AS avg_of_attack FROM mypokemon;

→ 풀이를 보니 이렇게 사용하는게 안전하다고 생각

SELECT ROUND(AVG(attack)) AS avg_of_attack
FROM mypokemon;

■ 실습 10)

모든 포켓몬의 방어력 평균을 정수로 내림해서 'avg_of_defense'이라는 별명으로 가져오기

-- SUM을 사용했지만.. 값은 맞게 나옴...
SELECT FLOOR(SUM(defense)/8) AS avg_of_defense FROM mypokemon;

→ 풀이에 사용된 함수를 사용하는게 안전할 것 같다

SELECT FLOOR(AVG(defense)) AS avg_of_defense FROM mypokemon;

■ 실습 11)

이름의 길이가 8미만인 포켓몬의 공격력의 2 제곱을 'attack2'라는 별명과 이름을 함께 가져오기

SELECT name, POWER(attack, 2) FROM mypokemon
WHERE LENGTH(name) < 8;

■ 실습 12)

모든 포켓몬의 공격력을 2로 나눈 나머지를 'div2'라는 별명과 이름을 함께 가져오기

SELECT name, MOD(attack, 2) AS div2 FROM mypokemon;

■ 실습 13)

공격력이 50 이하인 포켓몬의 공격력을 방어력으로 뺀 값의 절갯값을 'diff'라는 별명과 이름을 함께 가져오기

SELECT name, ABS(attack - defense) AS diff FROM mypokemon
WHERE attack <= 50;

■ 실습 14)

현재 날짜와 시간을 가져오고 각각 now_date, now_time이라는 별명으로 가져오기

SELECT CURRENT_DATE() AS now_data, CURRENT_TIME() AS now_time;

■ 실습 15)

포켓몬을 포획한 달(월, MONTH)을 숫자와 영어로 가져오고 숫자는 month_num, 영어는 month_eng라는 별명으로 가져오기

SELECT MONTH(capture_date) AS month_num, MONTHNAME(capture_date) AS month_eng 
FROM mypokemon;

■ 실습 16)

포켓몬을 포획한 날의 요일을 숫자와 영어로 가져오고 숫자는 day_num, 영어는 day_eng라는 별명으로 가져오기

SELECT DAYOFWEEK(capture_date) AS day_num, DAYNAME(capture_date) AS day_eng FROM mypokemon;

■ 실습 17)

포켓몬을 포획한 날의 연도, 월, 일을 각각 숫자로 가져오고 연도는 year, 월은 month, 일은 day라는 별명으로 가져오기

SELECT YEAR(capture_date) AS year, MONTH(capture_date) AS month, 
DAYOFMONTH(capture_date) AS day 
FROM mypokemon;

데이터를 그룹화해서 통계를 내보자(GROUP BY, HAVING)

■ 실습 1)

pokemon 테이블에서 이름(name)의 길이가 5보다 큰 포켓몬들을 타입(type)을 기준으로 그룹화하고, 몸무게(weight)의 평균이 20 이상인 그룹의 타입과, 몸무게의 평균을 가져오기

- 결과는 몸무게의 평균을 내림차순으로 정렬

SELECT type, AVG(weight) FROM pokemon.mypokemon
WHERE length(name) > 5
GROUP BY type
HAVING AVG(weight) >= 20
ORDER BY AVG(weight) DESC;

→ORDER BY 다른 방법

SELECT type, AVG(weight) FROM pokemon.mypokemon
WHERE number < 200
GROUP BY type
HAVING MAX(weight) >= 10 AND MIN(weight) >= 2
ORDER BY 2 DESC;

■ 실습 2)

pokemon 테이블에서 번호(number)가 200보다 작은 포켓몬들을 타입(type)을 기준으로 그룹화한 후에, 몸무게(weight)의 최댓값이 10보다 크거나 같고 최솟값은 2보다 크거나 같은 그룹의 타입, 키(height)의 최솟값, 최댓값을 가져오기

- 결과는 키의 최솟값의 내림차순으로 정렬해주고 / 만약 키의 최솟값이 같다면 키의 최댓값의 내림차순으로 정렬

SELECT type, MIN(height), MAX(height) FROM pokemon.mypokemon
WHERE number < 200
GROUP BY type
HAVING MAX(weight) >= 10 AND MIN(weight) >= 2
ORDER BY 2 DESC, 3 DESC;

728x90

저작자표시

'데이터분석 부트캠프' 카테고리의 다른 글

데이터 관련 직무 (0)	2024.06.12
데이터분석 부프캠프 14기 - 8주차_MySQL (0)	2024.06.12
Python 프로젝트 (0)	2024.06.05
데이터분석 부프캠프 14기 - 5주차 (0)	2024.05.24
데이터분석 부프캠프 14기 - 4주차 (0)	2024.05.17

푸리세상

데이터분석 부프캠프 14기 - 7주차_MySQL

◆ 목차

Mysql

1. 데이터

2. SQL

3. MySQL

4. MySQL - 데이터베이스 다루기

5. MySQL - 테이블 만들기

6. MySQL - 테이블 지우기

7. MySQL - 데이터 삽입, 삭제, 수정하기

8. MySQL - 데이터 가져오기

9. MySQL - 조건에 맞는 데이터 가져오기

10. MySQL - 연산자 종류

11. MySQL - 문자 데이터 다루기

12. MySQL - NULL 데이터 다루기

13. MySQL - 데이터 줄세우기

14. MySQL - 데이터 순위 만들기

15. MySQL - 문자형 데이터

16. MySQL - 숫자형 데이터

17. MySQL - 날짜형 데이터

18. MySQL - NULL 처리 데이터

19. MySQL - 데이터 그룹화하기

20. MySQL - 그룹에 조건 추가하기

21. MySQL - 다양한 그룹 함수 알아보기

22. MySQL - 쿼리 실행 순서 알아보기

23. MySQL - 실습 문제 풀기

테이블 만들고 데이터 넣기(CREATE, INSERT)

테이블에서 데이터를 가져오기(SELECT, FROM)

※ 기준 테이블 정보

주어진 조건에 맞는 데이터를 가져오기(WHERE)

데이터 요청 대로 만들어보기(ORDER BY)

데이터를 그룹화해서 통계를 내보자(GROUP BY, HAVING)

'데이터분석 부트캠프' 카테고리의 다른 글

티스토리툴바

데이터분석 부프캠프 14기 - 7주차_MySQL

◆ 목차

Mysql

1. 데이터

2. SQL

3. MySQL

4. MySQL - 데이터베이스 다루기

5. MySQL - 테이블 만들기

6. MySQL - 테이블 지우기

7. MySQL - 데이터 삽입, 삭제, 수정하기

8. MySQL - 데이터 가져오기

9. MySQL - 조건에 맞는 데이터 가져오기

10. MySQL - 연산자 종류

11. MySQL - 문자 데이터 다루기

12. MySQL - NULL 데이터 다루기

13. MySQL - 데이터 줄세우기

14. MySQL - 데이터 순위 만들기

15. MySQL - 문자형 데이터

16. MySQL - 숫자형 데이터

17. MySQL - 날짜형 데이터

18. MySQL - NULL 처리 데이터

19. MySQL - 데이터 그룹화하기

20. MySQL - 그룹에 조건 추가하기

21. MySQL - 다양한 그룹 함수 알아보기

22. MySQL - 쿼리 실행 순서 알아보기

23. MySQL - 실습 문제 풀기

테이블 만들고 데이터 넣기(CREATE, INSERT)

테이블에서 데이터를 가져오기(SELECT, FROM)

※ 기준 테이블 정보

주어진 조건에 맞는 데이터를 가져오기(WHERE)

데이터 요청 대로 만들어보기(ORDER BY)

데이터를 그룹화해서 통계를 내보자(GROUP BY, HAVING)

'데이터분석 부트캠프' 카테고리의 다른 글

관련글

티스토리툴바