[패스트캠퍼스 DA 부트캠프 11기]6주차 학습

지난 주까지 python 프로젝트를 마치고, 이번주부터 SQL학습에 들어갔다!

python에 비해서 비교적 쉬운 문법구조를 가지지만, 개인적으로 python보다 낯설어서 복습을 자주 해줘야 할 것 같다.

 

 

🤠데이터베이스

• 데이터베이스의 특징
  • 특징 1. 자료를 구조화하여 저장하기 때문에, 효율적인 관리가 가능하다.
  • 특징 2. 여러 업무에 여러 사용자가 동시에 사용 가능하다.
  • 특징 3. 사용자가 데이터베이스의 기능을 사용 하기 위해서는 응용 프로그램을 활용해야 한다
• DBMS : 데이터베이스의 기능을 제공하는 프로그램(MySQL도 DBMS의 한 종류!)

• 단축키
  • 주석처리 : #, —, /**/
  • 한줄실행 단축키 : Ctrl + Enter
  • 전체실행 단축키 : ctrl + shift + enter
  • 자동정렬 : ctrl + b
  • 그 줄 삭제 : ctrl + l
  • 해당 줄 바로 밑으로 복사 : ctrl + d
  • 글씨 전각/반각 변경 : Alt + =

---

😶‍🌫️데이터 타입

• 데이터 타입 : 정수형, 실수형, 문자형, 날짜형
• MySQL은 저장공간의 데이터 타입을 미리 지정해줘야 한다!

---

📑테이블

• 데이터베이스의 좁은 의미 : 테이블을 저장해두는 저장소 → 이때 DB = 스키마(Schema)

• 생성순서 : 데이터베이스 생성 후 그 안에 테이블 생성
• 데이터베이스, 테이블, 컬럼 이름 정하는 규칙
  • 키워드는 주로 대문자, 사용자 정의 이름에는 소문자 사용
  • 단어와 단어 사이는 빈칸 대신 _ 사용
  • 중복 규칙
    • 테이블 이름은 하나의 DB 내에서 중복될 수 없음
    • 컬럼 이름은 하나의 테이블 내에서 중복될 수 없

1. DB관련 쿼리(생성, 삭제)

CREATE DATABASE [데이터베이스 이름];
# 작성시 주의할 점 : 세미콜론(;)을 항상 붙여줘야 한다
# 키워드는 약속

SHOW DATABASES;
# 끝에 S 꼭 붙여주기
# 현재 존재하는 모든 데이터베이스 목록을 보여주세요

USE [데이터베이스 이름];
# 이제부터 [데이터베이스 이름] DB를 사용할게

# DB 지우기(해당 DB가 존재하지 않으면 error 발생)
DROP DATABASE [데이터베이스 이름];

# 존재한다면 지우기 -> 테이블에도 똑같이 적용
DROP DATABASE **IF EXISTS** [데이터베이스 이름];

1. TABLE관련 쿼리(생성, 삭제)

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

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

# 새로운 컬럼 추가
ALTER TABLE [테이블 이름] ADD COLUMN [컬럼 이름] [데이터 타입];

# 기존 컬럼 타입 변경하기
ALTER TABLE [테이블 이름] MODIFY COLUMN [컬럼 이름][새로운 데이터 타입];

# 기존 컬럼 이름과 타입 변경하기
ALTER TABLE [테이블 이름]
CHANGE COLUMN [컬럼 이름] [새로운 컬럼 이름] [새로운 데이터 타입];

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

# 테이블 삭제(해당 테이블이 존재하지 않으면 error 발생)
DROP TABLE [테이블 이름];

# 테이블 **값만** 지우기 -> 테이블 구조는 남아있
TRUNCATE TABLE [테이블 이름];

#################################### EXAMPLE ##########################################
CREATE TABLE idol(
					name VARCHAR(20),
					age INT,
					group VARCHAR(50)
);

ALTER TABLE costomer RENAME customers;
ALTER TABLE customers ADD COLUMN age INT;
ALTER TABLE customers MODIFY COLUMN age FLOAT;
ALTER TABLE customers
CHANGE COLUMN age new_age FLOAT;

ALTER TABLE customers DROP COLUMN new_age;
#################################### EXAMPLE ##########################################

1. 데이터 관련 쿼리(row 단위라는 것 잊지말기)

# 데이터 하나(row) 삽입하기
INSERT INTO [테이블 이름]([컬럼1 이름],[컬럼2 이름],[컬럼3 이름])
VALUES ([컬럼1 값],[컬럼2 값],[컬럼3 값]);

# 데이터 여러 개(rows) 삽입하기
INSERT INTO [테이블 이름]([컬럼1 이름],[컬럼2 이름],[컬럼3 이름])
VALUES ([컬럼1 값],[컬럼2 값],[컬럼3 값]),
			 ([컬럼1 값],[컬럼2 값],[컬럼3 값]),
			 ...
			 ([컬럼1 값],[컬럼2 값],[컬럼3 값]);

# 데이터 삭제하기(table이나 DB는 DROP 키워드 사용했었음)
DELETE FROM [테이블 이름]
WHERE [조건 값];

# 데이터 수정하기
UPDATE [테이블 이름]
SET [컬럼 이름] = [새값]
WHERE [조건 값];

#################################### EXAMPLE ##########################################
INSERT INTO idol (name, age, group)
VALUES ("제니",27,"블랙핑크");
#################################### EXAMPLE ##########################################

---

🕹️데이터 가져오기

• SELECT : 데이터 가져오기, *를 사용해 데이블로부터 전체 컬럼을 가져올 수 있음
• FROM : 테이블에서 데이터 가져오기
• AS : 컬럼 별명 붙이기(해당 쿼리에서만 유효) → table상의 실제 컬럼명은 변하지 않음!
• LIMIT : 데이터 일부만 가져오기(row수 지정). 쿼리의 가장 마지막에 위치.
• DISTINCT : 중복 제거하기(주로 컬럼에 어떤 값이 있는지 확인)

#################################### EXAMPLE ##########################################
SELECT DISTINCT type
FROM pokemon.mypokemon;

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

# 변수명에 아래와 같이 특수기호가 들어갈 때는 ""또는 ''로 감싸서 표현해야 한다
SELECT height * 100 AS 'height(cm)'
FROM mypokemon;

SELECT name, weight / height^2 AS BMI
FROM mypokemon;
#################################### EXAMPLE ##########################################

---

🔎조건에 맞는 데이터 가져오기(단위는 row!)

• WHERE : 원하는 데이터의 조건이 True가 되게끔 조건식 생성. 참은 1, 거짓은 0
• 비교연산자(=, >, <) : 파이썬과 다르게 비교 연산자가 = 하나 임을 주의하기
• 논리연산자(NOT, AND, OR)

• BETWEEN : 특정 범위 내의 데이터를 선택할 때 (컬럼명 BETWEEN A AND B). 범위는 이상 이하
• IN : 목록 내 포함되는 데이터를 선택할 때 (컬럼명 IN (A,B,…,C))
• LIKE : 특정 문자열이 포함된 데이터를 선택할 때 ( 컬럼명 LIKE 검색할 문자열 ) . 검색할 문자열 내에 와일드카드 사용가능
  • % : 0개 이상의 문자
  • _ : 1개의 문자
  • 사용예제
    • %e : e로 끝나는 문자열 (e, ee, eevee,pineapple)
    • e% : e로 시작하는 문자열 ( e, ee, eevee, eric)
    • %e% : e를 포함하는 문자열 ( e, ee, eevee, pineapple, eric, aespa)

• IS NULL : 데이터가 NULL인지 아닌지를 확인하는 연산
  • NULL : 0이나 공백과는 다름. 알 수 없는 값을 의미
  • IS NOT NULL

#################################### EXAMPLE ##########################################
DROP DATABASE IF EXISTS pokemon;
CREATE DATABASE pokemon;
USE pokemon;
CREATE TABLE mypokemon (
number int,
name varchar(20),
type varchar(20),
height float,
weight float,
attack float,
defense float,
speed float
);
INSERT INTO mypokemon (number, name, type, height, weight, attack, defense, speed)
VALUES (10, 'caterpie', 'bug', 0.3, 2.9, 30, 35, 45),
(25, 'pikachu', 'electric', 0.4, 6, 55, 40, 90),
(26, 'raichu', 'electric', 0.8, 30, 90, 55, 110),
(133, 'eevee', 'normal', 0.3, 6.5, 55, 50, 55),
(152, 'chikoirita', 'grass', 0.9, 6.4, 49, 65, 45);

#4 (&는 안먹음)
/*
MISSION (4)
키가 0.5m보다 크고, 몸무게가 6kg보다 크거나 같은 포켓몬들의 이름을 가져와 주세요.
*/
SELECT name FROM mypokemon
WHERE height > 0.5 AND weight >=6;

# 13
/*
MISSION (13)
공격력과 방어력의 차이가 10 이상인 포켓몬들의 이름, 공격력, 방어력을 가져와 주세요.
*/
SELECT name, attack, defense FROM mypokemon
WHERE abs(attack-defense) >=10;

# 14(where 절에서 alias는 안먹음)
/*
MISSION (14)
능력치의 합이 150 이상인 포켓몬의 이름과 능력치의 합을 가져와 주세요.
이 떄, 능력치의 합은 ‘total’이라는 별명으로 가져와 주세요.
조건1. 능력치의 합은 공격력, 방어력, 속도의 합을 의미합니다.
*/
SELECT name, attack+defense+speed as total FROM mypokemon
WHERE attack+defense+speed >=150;
#################################### EXAMPLE ##########################################

• 실습에서 틀린 것
  1. 파이썬에서 AND, OR은 &,| 로도 적용이 되었는데, MySQL에서는 먹지 않는다!(4번문제)
  2. SELECT절에서 준 ALIAS는 WHERE절에서 인식하지 못한다!(14번 문제)

---

📈원하는 순서로 데이터 만들기

• ORDER BY: 데이터 줄세우기.
  • 기본은 오름차순.
  • 컬럼 번호로도 정렬가능(이때의 번호는 SELECT절 기준)
  • 해당 쿼리내에서만 유효. 실제 테이블에 영향을 주지 않음

# 오름차순(default)
ORDER BY [컬럼 이름] = ORDER BY [컬럼 이름] ASC

# 내림차순
ORDER BY [컬럼 이름] DESC

# 여러 컬럼으로도 정렬 가능 -> 먼저 순서한 컬럼으로 정 후 그 다음 컬럼으로 정렬
ORDER BY [컬럼 1], [컬럼 2]

• RANK : 데이터 순위 만들기(하나의 컬럼을 만들 때)
  • RANK() OVER (ORDER BY [컬럼 이름])
  • 항상 ORDER BY와 함께 사용
  • 해당 쿼리내에서만 유효. 실제 테이블에 영향을 주지 않음
  • DENSE_RANK, ROW_NUMBER와의 차이? - 공동순위를 처리하는 방

#################################### EXAMPLE ##########################################
SELECT name, attack,
				RANK() OVER (ORDER BY attack DESC) AS attack_rank
FROM pokemon.mypokemon;
#################################### EXAMPLE ##########################################

• 문자형 데이터의 함수

함수 활용 예시 설명

LOCATE	LOCATE(“A”, “ABC”)	“ABC”에서 “A”는 몇 번째에 위치해 있는지 검색해 위치 반환
찾는 문자가 없을 시 0 반환
SUBSTRING	SUBSTRING(“ABC”, 2)	“ABC”에서 2번째 문자부터 반환
만약 입력한 숫자가 문자열의 길이보다 크다면 아무것도 가져오지 않음
RIGHT	RIGHT(“ABC”, 1)	“ABC”에서 오른쪽에서 1번째 문자까지 반환
LEFT	LEFT(“ABC”, 1)	“ABC”에서 왼쪽에서 1번째 문자까지 반환
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”로 바꿔 반환

• 숫자형 데이터의 함수

함수 활용 예시 설명

ABS	ABS(숫자)	숫자의 절댓값 반환
CEILING	CEILING(숫자)	숫자를 정수로 올림해서 반환
FLOOR	FLOOR(숫자)	숫자를 정수로 내림해서 반환
ROUND	ROUND(숫자,자릿수)	숫자를 소수점 자릿수까지 반올림해서 반환
TRUNCATE	TRUNCATE(숫자, 자릿수)	수자를 소수점 자릿수까지 버림해서 반환
자릿수에 0 입력시 정수 반환
POWER	POWER(숫자1, 숫자2)	숫자1의 숫자2 제곱 반환
MOD	MOD(숫자1, 숫자2)	숫자1을 숫자2로 나눈 나머지 반환

• 날짜형 데이터의 함수

함수 활용 예시 설명

NOW	NOW()	현재 날짜와 시간 반환
CURRENT_DATE	CURRENT_DATE()	현재 날짜 반환
CURRENT_TIME	CURRENT_TIME()	현재 시간 반환
YEAR	YEAR(날짜)	날짜의 연도 반환
MONTH	MONTH(날짜)	날짜의 월 반환
MONTHNAME	MONTHNAME(날짜)	날짜의 월을 영어로 반환
DAYNAME	DAYNAME(날짜)	날짜의 요일을 영어로 반환
DAYOFMONTH	DAYOFMONTH(날짜)	날짜의 일 반환
DAYOFWEEK	DAYOFWEEK(날짜)	날짜의 요일을 숫자로 반환
WEEK	WEEK(날짜)	날짜가 해당 연도에 몇 번째 주인지 반환
HOUR	HOUR(시간)	시간의 시 반환
MINUTE	MINUTE(시간)	시간의 분 반환
SECOND	SECOND(시간)	시간의 초 반환
DATE_FORMAT	DATE_FORMAT(날짜/시간, 형식)	날짜/시간의 형식을 형식으로 바꿔 반환
DATEDIFF	DATEDIFF(날짜1, 날짜2)	날짜1과 날짜2의 차이 반환(날짜1 - 날짜2)
TIMEDIFF	TIMEDIFF(시간1, 시간2)	시간1과 시간2의 차이 반환(시간1 - 시간2)

#################################### EXAMPLE ##########################################
DROP DATABASE IF EXISTS pokemon;
CREATE DATABASE pokemon;
USE pokemon;
CREATE TABLE mypokemon (
number INT,
name VARCHAR(20),
type VARCHAR(10),
attack INT,
defense INT,
capture_date DATE
);
INSERT INTO mypokemon (number, name, type, attack, defense, capture_date)
VALUES (10, 'caterpie', 'bug', 30, 35, '2019-10-14'),
(25, 'pikachu', 'electric', 55, 40, '2018-11-04'),
(26, 'raichu', 'electric', 90, 55, '2019-05-28'),
(125, 'electabuzz', 'electric', 83, 57, '2020-12-29'),
(133, 'eevee', 'normal', 55, 50, '2021-10-03'),
(137, 'porygon', 'normal', 60, 70, '2021-01-16'),
(152, 'chikoirita', 'grass', 49, 65, '2020-03-05'),
(153, 'bayleef', 'grass', 62, 80, '2022-01-01');
/*
MISSION (3)
포켓몬 이름에서 이름에 o가 포함된 포켓몬만 모든 소문자 o를 대문자 O로 바꿔서
 ’bigO’라는 별명으로 가져와 주세요.
Ex) 이름이 ‘pokemon’일 경우, ’bigO’ 값은 ’pOkemOn’이 됩니다.
*/
SELECT REPLACE(name, 'o', 'O') AS bigO
FROM mypokemon
WHERE name LIKE '%o%';

/*
MISSION (6)
모든 포켓몬의 공격력 평균을 정수로 반올림해서 ‘avg_of_attack’이라는 별명으로 가져와 주세요.
*/
SELECT ROUND(AVG(attack)) AS avg_of_attack
FROM mypokemon;

/*
MISSION (11)
현재 날짜와 시간을 가져와 주세요. 각각 now_date, now_time이라는 별명으로 가져와 주세요.
*/
SELECT CURRENT_DATE() AS now_date, CURRENT_TIME() AS now_time;

/*
MISSION (12)
포켓몬을 포획한 달(월, MONTH)을 숫자와 영어로 가져와 주세요. 
숫자는 month_num, 영어는 month_eng이라는 별명으로 가져와 주세요.
*/
SELECT MONTH(capture_date) AS month_num, MONTHNAME(capture_date) AS month_eng
FROM mypokemon;

/*
MISSION (13)
포켓몬을 포획한 날의 요일을 숫자와 영어로 가져와 주세요. 
숫자는 day_num, 영어는 day_eng이라는 별명으로 가져와 주세요.
*/
SELECT DAYOFWEEK(capture_date) AS day_num, DAYNAME(capture_date) AS day_eng
FROM mypokemon;

/*
MISSION (14)
포켓몬을 포획한 날의 연도, 월, 일을 각각 숫자로 가져와 주세요. 
연도는 year, 월은 month, 일은 day라는 별명으로 가져와 주세요.
*/
SELECT YEAR(capture_date) AS year, MONTH(capture_date) AS month, DAY(capture_date) AS day
FROM mypokemon;
#################################### EXAMPLE ##########################################

---

👨🏻‍👩🏻‍👧🏻‍👦🏻데이터 그룹화하기

• GROUP BY
  • 그룹 별 데이터 집계할 때 사용, 엑셀의 피벗 기능과 유사
  • GROUP BY가 쓰인 쿼리의 SELECT 절에는 GROUP BY 대상 컬럼과 그룹 함수만 사용 가능
  • 여러 컬럼으로 그룹화도 가능
  • 컬럼 번호로도 그룹화 가능(이때의 번호는 SELECT절 기준)

• HAVING
  • 그룹에 조건을 주고 싶을 때
  • GROUP BY 뒤에 사용
  • HAVING 절의 조건식에서는 그룹함수를 활용

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

• 그룹 함수

함수 활용 예시 설명

COUNT	COUNT(컬럼)
COUNT(1)	그룹의 값 수를 세는 함수(NULL값은 세어주지 않는다)
하나의 값을 1로 세어주는 표현
SUM	SUM(컬럼)	그룹의 합을 계산하는 함수
집계할 컬럼이름은 그룹의 기준이 되는 컬럼이름과 같지 않아도 된다
AVG	AVG(컬럼)	그룹의 평균을 계산하는 함
MIN	MIN(컬럼)	그룹의 최솟값을 반환하는 함수
MAX	MAX(컬럼)	그룹의 최댓값을 반환하는 함수

#################################### EXAMPLE ##########################################
DROP DATABASE IF EXISTS pokemon;
CREATE DATABASE pokemon;
USE pokemon;
CREATE TABLE mypokemon (
number int,
name varchar(20),
type varchar(10),
height float,
weight float
);
INSERT INTO mypokemon (number, name, type, height, weight)
VALUES (10, 'caterpie', 'bug', 0.3, 2.9),
(25, NULL, '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); 

SELECT type, COUNT(*), COUNT(1), COUNT(name)
FROM pokemon.mypokemon
GROUP BY type;
#################################### EXAMPLE ##########################################

• 쿼리 실행 순서

#################################### EXAMPLE ##########################################
SELECT type, COUNT(1), MAX(weight)
FROM pokemon.mypokemon
WHERE name LIKE '%a%'
GROUP BY type
HAVING MAX(height)>1
ORDER BY 3;
#################################### EXAMPLE ##########################################

#################################### EXAMPLE ##########################################
/*
MISSION (1)
포켓몬 테이블에서 이름(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 2 DESC;

/*
MISSION (2)
포켓몬 테이블에서 번호(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;
#################################### EXAMPLE ##########################################

---

📑규칙 만들기

• IF 함수
  • 주로 SELECT절에 사용하는 함수. 결과값은 새로운 컬럼으로 반환
  • IF(조건, 참, 거짓)
  • IFNULL(컬럼이름, NULL일때 값)
• CASE
  • IF는 조건을 하나, CASE는 조건을 여러 개
  • ELSE 문장 생략 시 모든 조건에서 걸러진 값은 NULL값 반환
  • CASE문은 순서가 중요(파이썬에서 if, elif 짤 때 생각해보기)

# 형식 1
CASE
		WHEN 조건식1 THEN 결과값1
		WHEN 조건식2 THEN 결과값2
	  ELSE 결과값3
END

# 형식 2
CASE [컬럼 이름] 
		WHEN 조건값1 THEN 결과값1
		WHEN 조건값2 THEN 결과값2
	  ELSE 결과값3
END

• CREATE FUNCTION

# 함수 생성
CREATE FUNCTION [함수 이름] ([입력값 이름] [데이터 타입], ...)
			RETURNS [결과값 데이터 타입]
BEGIN
			DECLARE [임시값 이름] [데이터 타입];
			SET [임시값 이름] = [입력값 이름];
			쿼리;
			RETURN 결과값
END

# 함수 삭제
DROP FUNCTION [함수 이름] 

MySQL Workbench에서 함수 생성 시 주의할 점

#################################### EXAMPLE ##########################################
/*
MISSION
공격력과 방어력의 합이 120보다 크면 ‘very strong’, 90보다 크면 ‘strong’,
모두 해당 되지 않으면 ‘not strong’를 반환하는 함수 ‘isStrong’을 만들고 사용해주세요.
조건1: attack과 defense를 입력값으로 사용하세요.
조건2: 결과값 데이터 타입은 VARCHAR(20)로 해주세요.

*/
DROP DATABASE IF EXISTS pokemon;
CREATE DATABASE pokemon;
USE pokemon;

CREATE TABLE mypokemon (
number int,
name varchar(20),
type varchar(10),
attack int,
defense int
);

INSERT INTO mypokemon (number, name, type, attack, defense)
VALUES (10, 'caterpie', 'bug', 30, 35),
	   (25, 'pikachu', 'electric', 55, 40),
       (26, 'raichu', 'electric', 90, 55),
       (125, 'electabuzz', 'electric', 83, 57),
       (133, 'eevee', 'normal', 55, 50),
       (137, 'porygon', 'normal', 60, 70),
       (152, 'chikoirita', 'grass', 49, 65),
       (153, 'bayleef', 'grass', 62, 80),
       (172, 'pichu', 'electric', 40, 15),
       (470, 'leafeon', 'grass', 110, 130);

# 방법1
SET GLOBAL log_bin_trust_function_creators = 1;
DELIMITER //

CREATE FUNCTION isStrong(attack INT, defense INT)
    RETURNS VARCHAR(20)
BEGIN
    DECLARE powers VARCHAR(20);

    SET powers = CASE
        WHEN attack + defense > 120 THEN 'very strong'
        WHEN attack + defense > 90 THEN 'strong'
        ELSE 'not strong'
		END;

    RETURN powers;
END//

DELIMITER ;

SELECT name, isStrong(attack, defense) AS isStrong
FROM mypokemon;

# 방법2
CREATE FUNCTION isStrong(attack INT, defense INT)
	RETURNS VARCHAR(20)
BEGIN
	DECLARE a INT;
    DECLARE b INT;
    DECLARE isstrong VARCHAR(20);
    SET a = attack;
    SET b = defense;
    SELECT CASE
			WHEN a + b > 120 THEN 'very strong'
            WHEN a + b > 90 THEN 'strong'
            ELSE 'not strong'
			END INTO isstrong;
	RETURN isstrong;
END

# 내가 처음에 계속 에러나던 코드
# (Error Code: 1415. Not allowed to return a result set from a function)
CREATE FUNCTION isStrong(attack INT, defense INT)
    RETURNS VARCHAR(20)
BEGIN
    DECLARE a INT;
    DECLARE b INT;
    DECLARE powers VARCHAR(20);
    SET a = attack;
    SET b = defense;
    SELECT 
	CASE
		WHEN a + b >= 120 THEN 'very strong'
		WHEN a + b >= 90 THEN 'strong'
		ELSE 'not strong'
	END AS powers;
    RETURN powers;
END
# 함수 안의 SELECT 쿼리에서 INTO로 변수할당은 가능하지만 SELECT문을 사용하여
# 값을 반환하려고 하니 자꾸 에러가 났던 것
#################################### EXAMPLE ##########################################

• 공부하다가 Chat-GPT한테 물어서 알아낸 것(AS 와 INTO에 대해서)
  • MySQL에서는 함수 내에서 SELECT ... INTO를 사용하여 변수에 값을 할당하는 것은 허용되지만, 직접적으로 SELECT 문을 사용하여 함수의 반환값으로 값을 가져오는 것은 허용되지 않습니다.
  • AS와 INTO 모두 데이터베이스의 테이블 구조에는 영향을 주지 않습니다. 이 두 명령은 쿼리의 실행 및 결과에만 영향을 미칩니다.
  • INTO 문을 사용하여 결과 값을 변수에 할당하기 전에 해당 변수를 선언해야 합니다. 변수 선
  • 언은 DECLARE 문을 사용하여 수행됩니다.

#################################### EXAMPLE ##########################################
/*
MISSION (2)
포켓몬의 공격력과 방어력의 합이 100보다 작으면 값을 ‘weak’로 반환하고, 
100보다 크거나 같으면 값을 ‘strong’로 반환해서 ‘ability’라는 별명으로 가져와 주세요.
*/
SELECT name, IF(attack + defense < 100, 'weak', 'strong') AS ability
FROM mypokemon;

/*
MISSION (3)
포켓몬의 타입 별 공격력의 평균이 60 이상이면 값을 True(1)로 반환하고, 
60보다 작으면 False(0)를 반환해 ‘is_strong_type’이라는 별명으로 가져와 주세요. 
*/
SELECT type, IF(AVG(attack) >= 60, True, False) AS is_strong_type
FROM mypokemon
GROUP BY type;

/*
MISSION (7)
타입 별 포켓몬 수가 1개면 ‘solo’, 3개 미만이면 ‘minor’, 3개 이상이면 ‘major’를 반환하고,
‘count_by_type’이라는 별명으로 가져와 주세요.
*/
SELECT type, 
CASE
WHEN count(1) = 1 THEN 'solo'
WHEN count(1) < 3 THEN 'minor'
ELSE 'major'
END AS count_by_type
FROM mypokemon
GROUP BY type;
#################################### EXAMPLE ##########################################

---

🙏테이블 합치기

• INNER JOIN
  • 가장 기본 JOIN으로, default값임
  • 두 테이블 모두에 있는 값만 합치기

SELECT [컬럼 이름]
FROM [테이블 A 이름]
INNER JOIN [테이블 B 이름]
ON [테이블 A 이름].[컬럼 A 이름] = [테이블 B 이름].[컬럼 B 이름] 
WHERE 조건식;

• LEFT, RIGHT JOIN
  • 한쪽에 있는 값만 합치기
  • 기준 테이블과 연결되는 값이 없을 때, NULL로 채워짐

SELECT [컬럼 이름]
FROM [테이블 A 이름]
LEFT JOIN [테이블 B 이름]
ON [테이블 A 이름].[컬럼 A 이름] = [테이블 B 이름].[컬럼 B 이름] 
WHERE 조건식;

SELECT [컬럼 이름]
FROM [테이블 A 이름]
RIGHT JOIN [테이블 B 이름]
ON [테이블 A 이름].[컬럼 A 이름] = [테이블 B 이름].[컬럼 B 이름] 
WHERE 조건식;

• OUTER, CROSS, SELF JOIN
  • OUTER : MySQL에는 OUTER JOIN 키워드가 없음! LEFT+RIGHT 로 구현(UNION)
  • CROSS : 두 테이블에 있는 모든 값을 모든 경우의 수로 각각 다 합치기
  • SELF : 같은 테이블에 있는 값 합치기

# OUTER JOIN
SELECT [컬럼 이름]
FROM [테이블 A 이름]
LEFT JOIN [테이블 B 이름]
ON [테이블 A 이름].[컬럼 A 이름] = [테이블 B 이름].[컬럼 B 이름] 
UNION  # 두 쿼리의 결과를 중복을 제외하고 합쳐서 보여주는 집합연산자
SELECT [컬럼 이름]
FROM [테이블 A 이름]
RIGHT JOIN [테이블 B 이름]
ON [테이블 A 이름].[컬럼 A 이름] = [테이블 B 이름].[컬럼 B 이름] 
WHERE 조건식;

# CROSS JOIN
SELECT [컬럼 이름]
FROM [테이블 A 이름]
CROSS JOIN [테이블 B 이름]
WHERE 조건식;

# SELF JOIN
SELECT [컬럼 이름]
FROM [테이블 A 이름] AS t1
INNER JOIN [테이블 A 이름 ] AS t2
ON t1.[컬럼 A 이름] = t2.[컬럼 A 이름]
WHERE 조건식;

#################################### EXAMPLE ##########################################
/*
MISSION (2)
포켓몬 테이블과 능력치 테이블을 합쳐서 포켓몬 번호와 이름을 한번에 가져와 주세요.
이 때, 능력치 테이블에 있는 모든 포켓몬의 데이터를 가져와 주세요. 
만약, 포켓몬의 이름 데이터를 구할 수 없다면, NULL을 가져와도 좋습니다.

*/
# JOIN할 두 테이블에 같은 이름의 COLUMN이 존재하면, 명확히 명시해줘야 한다
SELECT ability.number, name 
FROM mypokemon
RIGHT JOIN ability
ON mypokemon.number = ability.number;

/*
MISSION (3)
포켓몬의 타입 별 키의 평균과 몸무게의 평균을 함께 가져와 주세요.
*/
SELECT type, AVG(height) AS '키의 평균', AVG(weight) AS '몸무게의 평균'
FROM mypokemon LEFT JOIN ability
ON mypokemon.number = ability.number
GROUP BY type;

/*
MISSION (4)
번호가 100 이상인 내 포켓몬들의 번호, 이름, 공격력, 방어력을 가져와 주세요.
*/
SELECT ability.number, name, attack, defense
FROM mypokemon LEFT JOIN ability
ON mypokemon.number = ability.number
WHERE ability.number >= 100;

/*
MISSION (5)
공격력과 방어력의 합이 큰 순서대로 내 포켓몬들의 이름을 나열해 주세요.
*/
SELECT name
FROM mypokemon LEFT JOIN ability
ON mypokemon.number = ability.number
ORDER BY attack + defense DESC;

/*
MISSION (6)
속도가 가장 빠른 내 포켓몬의 이름을 가져와 주세요.
*/
SELECT name
FROM mypokemon LEFT JOIN ability
ON mypokemon.number = ability.number
ORDER BY speed DESC
LIMIT 3;
#################################### EXAMPLE ##########################################

•