728x90
1. 데이터베이스 다루기
- 테이블이란?
- DB에서 데이터 형태를 정해 모은 저장 공간
- 행과 열로 이루어진 데이터 표
- USE [database 이름] 를 통해 사용할 database를 지정해주면 query문을 작성할 때 [database 이름].name이 아닌 name과 같이 간략화할 수 있다.
- ⭐️ DROP과 TRUNCATE의 차이점
- DROP : 테이블 자체를 삭제
- TRUNCATE : 테이블 구조는 냅두고 값만 삭제. 즉, 초기화시킴
- 실습 예제
- 'pokemon' DB 만들기
- 'mypokemon' 테이블 만들기
- 칼럼 이름 및 데이터 타입 : (1) number : INT (2) name : VARCHAR(20) (3) type : VARCHAR(10)
- 포켓 데이터 집어넣기
/// MISSION (1) pokemon DB 만들고 mypokemon 테이블 속에 데이터 추가
// 1. DB 만들기
CREATE DATABASE pokemon;
USE pokemon;
// 2. 테이블 만들기
CREATE TABLE mypokemon (
number INT,
name VARCHAR(20),
type VARCHAR(10)
);
// 3. 데이터 집어넣기
INSERT INTO mypokemon (number, name, type)
VALUES (10, "caterpie", "bug");
INSERT INTO mypokemon (number, name, type)
VALUES (25, "pikachu", "electric");
INSERT INTO mypokemon (number, name, type)
VALUES (133, "eevee", "normal");
// mypokemon 테이블 속 요소들 전부 조회
SELECT * FROM mypokemon;
// 테이블 초기화 (테이블의 구조와 속성을 유지하지만, 인덱스를 포함한 모든 데이터가 삭제)
TRUNCATE TABLE mypokemon;
/// MISSION (2) mynewpokemon 테이블 추가
CREATE TABLE mynewpokemon (
number INT,
name VARCHAR(20),
type VARCHAR(10)
);
INSERT INTO mynewpokemon (number, name, type)
VALUES (77, "포니타", "불꽃");
INSERT INTO mynewpokemon (number, name, type)
VALUES (132, "메타몽", "노말");
INSERT INTO mynewpokemon (number, name, type)
VALUES (151, "뮤", "에스퍼");
/// MISSION (3) 테이블 변경 및 지우기
ALTER TABLE mypokemon RENAME myoldpokemon;
ALTER TABLE myoldpokemon CHANGE COLUMN name eng_name VARCHAR(20);
ALTER TABLE mynewpokemon CHANGE COLUMN name kor_name VARCHAR(20);
// MISSION (4) ‘myoldpokemon' 초기화, ‘mynewpokemon' 삭제
TRUNCATE TABLE myoldpokemon;
DROP TABLE mynewpokemon;2. 데이터베이스 가져오기 실습
- 키워드 AS
- 가져온 데이터에 별명을 지정하는 키워드
- AS [칼럼 별명] 형식으로 사용
- ⭐️ 실제 컬럼 이름은 변하지 않으며, 지정한 별명은 쿼리 내에서만 유효함
- 키워드 LIMIT
- LIMIT [로우 수] 형식으로 사용
- 쿼리의 가장 마지막에 위치
- ⭐️ 보통 특정 기준으로 정렬 후 최상단의 값만을 가져오고 싶을 때 사용
- 키워드 DISTINCT
- SELECT 절에 위치하여 칼럼의 중복된 데이터는 제외하고 같은 값은 한 번만 가져옴
- 실습 예제
// 사전 입력 정보
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);// MISSION (1) 123 x 456
SELECT 123 * 456;
// MISSION (2) 2310 / 30
SELECT 2310 / 30;
// MISSION (3) '피카츄'라는 문자열을 '포켓몬'이라는 이름의 칼럼 별명으로 가져오기
SELECT '피카츄' AS '포켓몬';
// MISSION (4) 포켓몬 테이블에서 모든 포켓몬들의 칼럼 값 전체를 가져오기
SELECT * FROM mypokemon;
// MISSION (5) 포켓몬 테이블에서 모든 포켓몬들의 이름 가져오기
SELECT name FROM mypokemon;
// MISSION (6) 포켓몬 테이블에서 모든 포켓몬들의 이름과 키, 몸무게를 가져와 주세요.
SELECT name, height, weight FROM mypokemon;
// MISSION (7) 포켓몬 테이블에서 포켓몬들의 키를 중복 제거하고 가져와 주세요.
SELECT DISTINCT height FROM mypokemon;
// MISSION (8) 포켓몬 테이블에서 모든 포켓몬들의 공격력을 2배 해 ‘attack2’라는 별명으로 이름과 함께 가져와 주세요.
SELECT name, 2*attack AS 'attack2' FROM mypokemon;
// MISSION (9) 포켓몬 테이블에서 모든 포켓몬들의 이름을 ‘이름’이라는 한글 별명으로 가져와 주세요.
포켓몬 테이블에서 모든 포켓몬들의 이름을 ‘이름’이라는 한글 별명으로 가져와 주세요.
// MISSION (10) 포켓몬 테이블에서 모든 포켓몬들의 공격력은 ‘공격력’이라는 한글 별명으로, 방어력은 ‘방어력’이라는 한글 별명으로 가져와 주세요.
SELECT attack AS '공격력', defense AS '방어력' FROM mypokemon;
// MISSION (11) 포켓몬 테이블에서 모든 포켓몬들의 키를 cm단위로 환산하여 ‘height(cm)’라는 별명으로 가져와 주세요.
SELECT height * 100 AS 'height(cm)' FROM mypokemon;
// MISSION (12) 포켓몬 테이블에서 첫번째 로우에 위치한 포켓몬 데이터만 컬럼 값 전체를 가져와 주세요.
SELECT * FROM mypokemon LIMIT 1;
// MISSION (13) 포켓몬 테이블에서 2개의 포켓몬 데이터만 이름은 ‘영문명’이라는 별명으로,
// 키는 ‘키(m)’라는 별명으로, 몸무게는 ‘몸무게(kg)’이라는 별명으로 가져와 주세요.
SELECT name AS '영문명', height AS "키(m)", weight AS "몸무게(kg)" FROM mypokemon LIMIT 2;
// MISSION (14) 포켓몬 테이블에서 모든 포켓몬들의 이름과 능력치의 합을 가져오고,
// 이 때 능력치의 합은 ‘total’이라는 별명으로 가져와 주세요.
// 조건1. 능력치의 합은 공격력, 방어력, 속도의 합을 의미합니다.
SELECT name, attack + defense + speed AS 'total' FROM mypokemon;
// MISSION (15) 포켓몬 테이블에서 모든 포켓몬들의 BMI 지수를 구해서 ‘BMI’라는 별명으로 가져와 주세요.
// 이 때, 포켓몬을 구분하기 위해 이름도 함께 가져와 주세요.
// 조건1. BMI 지수 = 몸무게(kg) ÷ (키(m))2
// 조건2. 포켓몬 테이블 데이터의 체중은 kg 단위, 키는 m 단위입니다.
SELECT name, weight / (height ^ 2) AS 'BMI' FROM mypokemon;3. 조건에 맞는 데이터 가져오기
- 키워드 WHERE
- 가져올 데이터의 조건을 지정해주는 키워드
- WHERE 조건식 형식으로 사용함
- ⭐️ 특정 문자열이 포함된 데이터를 선택하는 키워드, LIKE
- e로 끝나는 문자열 : LIKE '%e'
- e로 시작하는 문자열 : LIKE 'e%'
- e를 하나라도 포함하는 문자열 : LIKE '%e%'
- e로 끝나고 e 앞에 1개 이상의 문자가 있는 문자열 : LIKE '%_e'
- 데이터가 NULL인지 아닌지 확인하는 키워드 : IS NULL
- 실습 예제
// MISSION (1) 이브이의 타입을 가져와 주세요.
SELECT type FROM mypokemon WHERE name = "eevee";
// MISSION (2) 캐터피의 공격력과 방어력을 가져와 주세요.
SELECT attack, defense FROM mypokemon WHERE name = "caterpie";
// MISSION (3) 몸무게가 6kg보다 큰 포켓몬들의 모든 데이터를 가져와 주세요.
SELECT * FROM mypokemon WHERE weight > 6;
// MISSION (4) 키가 0.5m보다 크고, 몸무게가 6kg보다 크거나 같은 포켓몬들의 이름을 가져와 주세요.
SELECT name FROM mypokemon WHERE height > 0.5 AND weight >= 6;
// MISSION (5) 포켓몬 테이블에서 공격력이 50 미만이거나,
// 방어력이 50 미만인 포켓몬들의 이름을 ‘weak_pokemon’이라는 별명으로 가져와 주세요.
SELECT name AS "weak_pokemon" FROM mypokemon WHERE attack < 50 OR defense < 50;
// MISSION (6) 노말 타입이 아닌 포켓몬들의 데이터를 전부 가져와 주세요.
SELECT * FROM mypokemon WHERE type != 'normal';
// MISSION (7) 타입이 (normal, fire, water, grass) 중에 하나인 포켓몬들의 이름과 타입을 가져와 주세요.
SELECT name, type FROM mypokemon WHERE type IN ('normal', 'fire', 'water', 'grass');
// MISSION (8) 공격력이 40과 60 사이인 포켓몬들의 이름과 공격력을 가져와 주세요.
SELECT name, attack FROM mypokemon WHERE attack BETWEEN 40 AND 60;
// MISSION (9) 이름에 ‘e’가 포함되는 포켓몬들의 이름을 가져와 주세요.
SELECT name FROM mypokemon WHERE name LIKE '%e%';
// MISSION (10) 이름에 ‘i’가 포함되고, 속도가 50 이하인 포켓몬 데이터를 전부 가져와 주세요.
SELECT * FROM mypokemon WHERE name LIKE '%i%' AND speed <= 50;
// MISSION (11) 이름이 ‘chu’로 끝나는 포켓몬들의 이름, 키, 몸무게를 가져와 주세요.
SELECT name, height, weight FROM mypokemon WHERE name LIKE '%chu';
// MISSION (12) 이름이 ‘e’로 끝나고, 방어력이 50 미만인 포켓몬들의 이름, 방어력을 가져와 주세요.
SELECT name, defense FROM mypokemon WHERE name LIKE '%e' AND defense < 50;
// MISSION (13) 공격력과 방어력의 차이가 10 이상인 포켓몬들의 이름, 공격력, 방어력을 가져와 주세요.
SELECT name, attack, defense FROM mypokemon WHERE (attack - defense) >= 10 OR (defense - attack) >= 10;
// MISSION (14) 능력치의 합이 150 이상인 포켓몬의 이름과 능력치의 합을 가져와 주세요.
// 이 때, 능력치의 합은 ‘total’이라는 별명으로 가져와 주세요.
// 조건1. 능력치의 합은 공격력, 방어력, 속도의 합을 의미합니다.
SELECT name, attack + defense + speed AS 'total' FROM mypokemon WHERE (attack + defense + speed) >= 150;
4. 원하는 데이터 만들기
- 가져온 데이터를 정렬해주는 키워드 ORDER BY
- OREDER [칼럼 이름] 형식으로 사용
- ⭐️ 기본 정렬 규칙은 오름차순
- 내림차순 정렬을 원할 경우 DESC 키워드를 추가
- 데이터를 정렬해 순위를 만들어주는 RANK 문법
- ⭐️ ⭐️ 데이터 간에 순위를 만드는 함수 비교하기
- RANK : 공동 순위가 있으면 다음 순서로 건너 뜀
- DENSE_RANK : 공동 순위가 있어도 다음 순위를 뛰어 넘지 않음
- ROW_NUMBER : 공동 순위를 무시함
- 실습 예제 1. 데이터를 요청대로 만들어보자
// MISSION (1) : 포켓몬 테이블에서 포켓몬의 이름과 이름의 글자 수를 글자 기준으로 정렬해서 가져와 주세요.
// (정렬순서는글자수가적은것부터많은것순으로해주세요.)
SELECT name, LENGTH(name) FROM mypokemon ORDER BY LENGTH(name);
// MISSION (2) : 포켓몬 테이블에서 방어력 순위를 보여주는 컬럼을 새로 만들어 ‘defense_rank’라는 별명으로 가져와 주세요.
// 이 때, 포켓몬 이름 데이터도 함께 가져와 주세요.
// 조건1: 방어력 순위란 방어력이 큰 순서대로 나열한 순위를 의미합니다.
// 조건2: 공동 순위가 있으면 다음 순서로 건너 뛰어 주세요.
SELECT name, ROW_NUMBER() OVER (ORDER BY defense DESC) AS 'defense_rank' FROM mypokemon;
// MISSION (3) : 포켓몬 테이블에서 포켓몬을 포획한 지 기준 날짜까지 며칠이 지났는 지를 ‘days’라는 별명으로 가져와 주세요.
// 이 때, 포켓몬의 이름도 함께 가져와 주세요.
// 조건: 기준 날짜는 2022년 2월 14일입니다.
SELECT name, DATEDIFF('2022-02-14 00:00:00', capture_date) AS 'days' FROM mypokemon;- 실습 예제 2. 다양한 함수를 사용해보자
// MISSION (1) : 포켓몬의 이름을 마지막 3개 문자만, ‘last_char’이라는 별명으로 가져와 주세요.
SELECT RIGHT(name, 3) AS 'last_char' FROM mypokemon;
// MISSION (2) : 포켓몬 이름을 왼쪽에서 2개 문자를 ‘left2’라는 별명으로 가져와 주세요.
SELECT LEFT(name, 2) AS 'last_char' FROM mypokemon;
// MISSION (3) : 실습포켓몬 이름에서 이름에 o가 포함된 포켓몬만
// 모든 소문자 o를 대문자 O로 바꿔서 ’bigO’라는 별명으로 가져와 주세요.
// Ex) 이름이 ‘pokemon’일 경우, ’bigO’ 값은 ’pOkemOn’이 됩니다.
SELECT REPLACE(name, 'o', 'O') FROM mypokemon WHERE name LIKE '%o%';
// MISSION (4) : 포켓몬 타입을 가장 첫번째 글자 1자,가장 마지막 글자 1자를 합친 후,
// 대문자로변환해서 ‘type_code’라는 별명으로 가져와 주세요.
// 이 때, 이름도 함께 가져와 주세요.
// Ex) 타입이 ‘water’일 경우, ‘type_code’ 값은 ‘w’와 ‘r’를 대문자로 바꾼 ‘WR’이 됩니다.
SELECT name, UPPER(CONCAT(LEFT(type,1), RIGHT(type,1))) AS 'type_code' FROM mypokemon;
// MISSION (5) : 포켓몬 이름의 글자 수가 8보다 큰 포켓몬의 데이터를 전부 가져와 주세요.
SELECT * FROM mypokemon WHERE LENGTH(name) > 8;
// MISSION (6) : 모든 포켓몬의 공격력 평균을 정수로 반올림해 ‘avg_of_attack’이라는 별명으로 가져오기
SELECT ROUND(AVG(attack),0) AS 'avg_of_attack' FROM mypokemon;
// MISSION (7) : 모든 포켓몬의 방어력 평균을 정수로 내림해 ‘avg_of_defense’이라는 별명으로 가져오기
SELECT FLOOR(AVG(defense)) AS 'avg_of_defense' FROM mypokemon;
// MISSION (8) : 이름의 길이가 8미만인 포켓몬의 공격력의 2 제곱을 ‘attack2’라는 별명으로 가져와 주세요.
// 이 때, 이름 도 함께 가져와 주세요.
SELECT name, POWER(attack, 2) AS 'attack2' FROM mypokemon WHERE LENGTH(name) < 8;
// MISSION (9) : 모든 포켓몬의 공격력을 2로 나눈 나머지를 ‘div2’라는 별명으로 가져와 주세요.
// 이 때, 이름도 함께 가져 와 주세요.
SELECT name, attack % 2 AS 'div2' FROM mypokemon;
// MISSION (10) : 공격력이 50 이하인 포켓몬의 공격력을 방어력으로 뺀 값의 절댓값을 ‘diff’라는 별명으로 가져와 주세요.
// 이때,이름도함께가져와주세요.
SELECT name, ABS(attack - defense) AS diff FROM mypokemon WHERE attack <= 50;
// 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', DAYOFMONTH(capture_date) AS 'day' FROM mypokemon;5. 데이터 그룹화하기
- 칼럼에서 동일한 값을 가지는 로우를 그룹화하는 키워드 GROUP BY
- GROUP BY [칼럼 이름] 형식으로 사용
- ⭐️ GROUP BY가 쓰인 쿼리의 SELECT 절에는 GROUP BY 대상 칼럼과 그룹 함수만 사용 가능
- 만약, GROUP BY 대상 칼럼이 아닌 칼럼을 SELECT하게 되면 에러가 발생함
- 여러 칼럼으로 그룹화할 수도 있음. 키워드 뒤에 [칼럼 이름]을 복수 개 입력하면 됨
- 가져올 데이터 그룹에 조건을 지정해주는 키워드 HAVING
- HAVING 조건식 형식으로 사용함
- HAVING 절의 조건식에는 그룹 함수를 활용
- 대표적인 그룹 함수 : COUT, SUM, AVG, MIN, MAX
- 그룹의 값을 세는 함수 COUNT
- ⭐️ COUNT(*) : NULL값을 포함하여 전부 센다.
- ⭐️ COUNT(1) , COUNT([칼럼 이름]) : NULL값을 제외하고 전부 센다.
- ⭐️⭐️⭐️ 6가지 핵심 쿼리 키워드 및 실행 순서
- 실습 예제
// MISSION (1)
// 포켓몬 테이블에서 이름의 길이가 5보다 큰 포켓몬들을 타입(type)을 기준으로 그룹화하고,
// 몸무게(weight)의 평균이 20 이상인 그룹의 타입과, 몸무게의 평균을 가져와 주세요.
// 이 때, 결과는 몸무게의 평균을 내림차순으로 정렬해 주세요.
SELECT type, AVG(weight)
FROM mypokemon
WHERE LENGTH(name) > 5
GROUP BY type
HAVING AVG(weight) >= 20
ORDER BY AVG(weight) DESC;
// MISSION (2)
// 포켓몬 테이블에서 번호(number)가 200보다 작은 포켓몬들을 타입(type)을 기준으로 그룹화한 후에,
// 몸무게(weight)의 최댓값이 10보다 크거나 같고 최솟값은 2보다 크거나 같은 그룹의
// 타입, 키(height)의 최솟값, 최댓값을 가져와 주세요.
// 이 때, 결과는 키의 최솟값의 내림차순으로 정렬해 주시고,
// 만약 키의 최솟값이 같다면 키의 최댓값의 내림차순으로 정렬해주세요.
SELECT type, MIN(height), MAX(height)
FROM mypokemon
WHERE number < 200
GROUP BY type
HAVING MAX(weight) >= 10 AND MIN(weight) >= 2
ORDER BY MIN(height) DESC;
- 추가 실습 예제 (모든 문제 결과에 타입값을 포함하기)
// MISSION (1) : 포켓몬의 타입 별 키의 평균을 가져와 주세요.
SELECT type, AVG(height)
FROM mypokemon
GROUP BY type;
// MISSION (2) : 포켓몬의 타입 별 몸무게의 평균을 가져와 주세요.
SELECT type, AVG(weight)
FROM mypokemon
GROUP BY type;
// MISSION (3) : 포켓몬의 타입 별 키의 평균과 몸무게의 평균을 함께 가져와 주세요.
SELECT type, AVG(height), AVG(weight)
FROM mypokemon
GROUP BY type;
// MISSION (4) : 키의 평균이 0.5 이상인 포켓몬의 타입을 가져와 주세요.
SELECT type
FROM mypokemon
GROUP BY type
HAVING AVG(height) >= 0.5;
// MISSION (5) : 몸무게의 평균이 20 이상인 포켓몬의 타입을 가져와 주세요.
SELECT type
FROM mypokemon
GROUP BY type
HAVING AVG(weight) >= 20;
// MISSION (6) : 포켓몬의 타입 별 번호(number)의 합을 가져와 주세요.
SELECT type, sum(number)
FROM mypokemon
GROUP BY type;
// MISSION (7) : 키가 0.5 이상인 포켓몬이 포켓몬의 type 별로 몇 개씩 있는지 가져와 주세요.
SELECT type, count(1)
FROM mypokemon
WHERE height >= 0.5
GROUP BY type;
// MISSION (8) : 포켓몬 타입 별 키의 최솟값을 가져와 주세요.
SELECT type, min(height)
FROM mypokemon
GROUP BY type;
// MISSION (9) : 포켓몬 타입 별 몸무게의 최댓값을 가져와 주세요
SELECT type, max(weight)
FROM mypokemon
GROUP BY type;
// MISSION (10) : 키의 최솟값이 0.5보다 크고 몸무게의 최댓값이 30보다 작은 포켓몬 타입을 가져와 주세요.
SELECT type
FROM mypokemon
GROUP BY type
HAVING min(height) > 0.5 AND max(weight) < 30;6. 규칙 만들기
- 규칙 만들기
- 하나의 조건을 만들 때 사용하는 키워드 : IF
- 여러 개의 조건들을 만들 때 사용하는 키워드 : CASE
- ⭐️ MySQL Workbench에서 사용자 지정 함수 생성 방법 및 주의사항
- 실습 예제 1. 함수를 만들고 사용해보자
// MISSION
// 공격력과 방어력의 합이 120보다 크면 ‘very strong’, 90보다 크면 ‘strong’,
// 모두 해당 되지 않으면 ‘not strong’를 반환하는 함수 ‘isStrong’을 만들고 사용해주세요.
// 조건1: attack과 defense를 입력값으로 사용하세요.
// 조건2: 결과값 데이터 타입은 VARCHAR(20)로 해주세요.
SET GLOBAL log_bin_trust_function_creators = 1;
DELIMITER //
CREATE FUNCTION isStrong(attack INT, defense INT)
RETURNS VARCHAR(20)
BEGIN
DECLARE sum INT;
DECLARE result VARCHAR(20);
SET sum = attack + defense;
SET result = CASE
WHEN sum > 120 THEN 'very strong'
WHEN sum > 90 THEN 'strong'
ELSE 'not strong'
END;
RETURN result;
END //
DELIMITER ;- 실습 예제 2. 조건문을 만들어보자
// MISSION (1)
// 포켓몬의 번호가 150보다 작으면 값을 ‘old’로 반환하고,
// 번호가 150보다 크거나 같으면 값을 ‘new’로 반환해서 ‘age’라는 별명으로 가져와 주세요.
SELECT name, IF(number >= 150, 'new', 'old') AS 'age'
FROM mypokemon;
// MISSION (2)
// 포켓몬의 공격력과 방어력의 합이 100보다 작으면 값을 ‘weak’로 반환하고,
// 100보다 크거나 같으면 값을 ‘strong’로 반환해서 ‘ability’라는 별명으로 가져와 주세요.
SELECT name, IF(attack + defense >= 100, 'strong', 'weak') AS 'ability'
FROM mypokemon;
// MISSION (3)
// 포켓몬의 타입 별 공격력의 평균이 60 이상이면 값을 True(1)로 반환하고,
// 60보다 작으면 False(0)를 반환해 ‘is_strong_type’이라는 별명으로 가져와 주세요.
SELECT type, IF(AVG(attack) >= 60, 1, 0) AS 'is_strong_type'
FROM mypokemon
GROUP BY type;
// MISSION (4)
// 포켓몬의 공격력이 100보다 크고, 방어력도 100보다 크면 값을 True(1)로 반환하고,
// 둘 중 하나라도 100보다 작으면 False(0)를 반환해 ‘ace’라는 별명으로 가져와 주세요.
SELECT name, IF(attack > 100 AND defense > 10, 1, 0) AS 'ace'
FROM mypokemon;
// MISSION (5)
// 포켓몬의 번호가 100보다 작으면 값을 ‘<100’을 반환하고,
// 200보다 작으면 값을 ‘<200’을 반환하고, 500보다 작으면 값을 ‘<500’을 반환하는 규칙을 만들고,
// 각 포켓몬 별 규칙 적용 값을 ‘number_bin’이라는 별명으로 가져와 주세요.
SELECT name,
CASE
WHEN number < 100 THEN '<100'
WHEN number < 200 THEN '<200'
ELSE '<500'
END AS 'number_bin'
FROM mypokemon;
// MISSION (6)
// 아래 표에 따른 값을 반환하는 규칙을 만들고,
// 각 포켓몬 별 규칙 적용 값을 ‘age_attack’이라는 별명으로 가져와 주세요.
SELECT name,
CASE
WHEN attack >= 50 AND number >= 150 THEN 'new_strong'
WHEN attack >= 50 AND number < 150 THEN 'old_strong'
WHEN attack < 50 AND number >= 150 THEN 'new_weak'
ELSE 'old_weak'
END AS 'age_attack'
FROM mypokemon;
// MISSION (7)
// 타입 별 포켓몬 수가 1개면 ‘solo’, 3개 미만이면 ‘minor’, 3개 이상이면 ‘major’를 반환하고,
// ‘count_by_type’이라는 별명으로 가져와 주세요.
SELECT type,
CASE
WHEN count(type) >= 3 THEN 'major'
WHEN count(type) >= 2 THEN 'minor'
ELSE 'solo'
END AS 'count_by_type'
FROM mypokemon
GROUP BY type;7. 테이블 합치기
- 테이블을 합칠 때 사용하는 키워드 : JOIN
- JOIN의 종류
- INNER JOIN : 두 테이블 모두에 있는 값만 합치기
- LEFT JOIN : 왼쪽 테이블에 있는 값만 합치기
- RIGHT JOIN : 오른쪽 테이블에 있는 값만 합치기
- OUTER JOIN : 두 테이블에 있는 모든 값 합치기 (사실상 LEFT JOIN UNION RIGHT JOIN)
- CROSS JOIN : 두 테이블에 있는 모든 값을 각각 합치기
- SELF JOIN : 같은 테이블에 있는 값 합치기
- LEFT JOIN 문법
- JOIN의 종류
// 1. FROM 절을 기준으로 FROM 절의 테이블로 합치게 된다.
// 2. ON 절에 있는 등식은 좌변식과 우벼식이 바뀌어도 상관 없다.
SELECT [컬럼 이름]
FROM [테이블 A 이름]
LEFT JOIN [테이블 B 이름]
ON [테이블 A 이름].[컬럼 A 이름] = [테이블 B 이름].[컬럼 B 이름]
WHERE 조건식;
- 실습 예제 1. 다양한 방식으로 테이블을 합쳐보자.
// MISSION (1)
// 포켓몬 테이블과 능력치 테이블을 합쳐서 포켓몬 이름, 공격력, 방어력을 한번에 가져와 주세요.
// 이 때, 포켓몬 테이블에 있는 모든 포켓몬의 데이터를 가져와 주세요.
// 만약, 포켓몬의 능력치 데이터를 구할 수 없다면, NULL을 가져와도 좋습니다.
SELECT mypokemon.name, ability.attack, ability.defense
FROM mypokemon
LEFT JOIN ability
ON mypokemon.number = ability.number;
// MISSION (2)
// 포켓몬 테이블과 능력치 테이블을 합쳐서 포켓몬 번호와 이름을 한번에 가져와 주세요.
// 이 때, 능력치 테이블에 있는 모든 포켓몬의 데이터를 가져와 주세요.
// 만약, 포켓몬의 이름 데이터를 구할 수 없다면, NULL을 가져와도 좋습니다.
SELECT ability.number, mypokemon.name
FROM mypokemon
RIGHT JOIN ability
ON mypokemon.number = ability.number;
- 실습 예제 2. 테이블을 합쳐 원하는 값을 가져오자.
// MISSION (1)
// 포켓몬 타입별 키의 평균을 가져와주세요.
SELECT mypokemon.type, AVG(height)
FROM mypokemon
LEFT JOIN ability
ON mypokemon.number = ability.number
GROUP BY type;
// MISSION (2)
// 포켓몬의 타입 별 몸무게의 평균을 가져와 주세요.
SELECT mypokemon.type, AVG(weight)
FROM mypokemon
LEFT JOIN ability
ON mypokemon.number = ability.number
GROUP BY type;
// MISSION (3)
// 포켓몬의 타입 별 키의 평균과 몸무게의 평균을 함께 가져와 주세요.
SELECT mypokemon.type, AVG(height) , AVG(weight)
FROM mypokemon
LEFT JOIN ability
ON mypokemon.number = ability.number
GROUP BY type;
// MISSION (4)
// 번호가 100 이상인 내 포켓몬들의 번호, 이름, 공격력, 방어력을
SELECT mypokemon.number, mypokemon.name, ability.attack, ability.defense
FROM mypokemon
LEFT JOIN ability
ON mypokemon.number = ability.number
WHERE mypokemon.number >= 100;
// MISSION (5)
// 공격력과 방어력의 합이 큰 순서대로 내 포켓몬들의 이름을 나열해 주세요.
SELECT mypokemon.name
FROM mypokemon
LEFT JOIN ability
ON mypokemon.number = ability.number
ORDER BY (ability.attack + ability.defense) DESC;
// MISSION (6)
// 속도가 가장 빠른 내 포켓몬의 이름을 가져와 주세요.
SELECT mypokemon.name
FROM mypokemon
LEFT JOIN ability
ON mypokemon.number = ability.number
ORDER BY ability.speed DESC
LIMIT 1;
8. 여러 테이블 한 번에 다루기
- ⭐️ UNION vs UNION ALL
- UNION : 중복 허용 X
- UNION ALL : 중복 허용 O
- 둘 다 쿼리의 결과 값 개수가 같아야한다.
- 합집합 : UNION, UNION ALL로 구현
- 교집합 : INNER JOIN으로 구현
- 차집합 : LEFT JOIN으로 구현
- 실습 예제 1. 여러 테이블의 데이터를 한 번에 조회해보자
// MISSION (1)
// 내포켓몬과친구의포켓몬에어떤타입들이있는지중복제외하고같은타입은한번씩만가져와주세요.
SELECT type
FROM mypokemon
UNION
SELECT type
FROM friendpokemon;
// MISSION (2)
// 내 포켓몬과 친구의 포켓몬 중에 풀(grass) 타입 포켓몬들의 포켓몬 번호와 이름을
// 중복 포함하여 전부 다 가져와 주세요.
FROM mypokemon AS A
WHERE A.type = 'grass'
UNION ALL
SELECT B.number, B.name
FROM friendpokemon AS B
WHERE B.type = 'grass';
- 실습 예제 2. 여러 테이블을 다뤄서 원하는 값을 가져와보자
// MISSION (1)
// 나도 가지고 있고, 친구도 가지고 있는 포켓몬의 이름을 가져와 주세요.
SELECT A.name
FROM mypokemon AS A
INNER JOIN friendpokemon AS B
ON A.name = B.name;
// MISSION (2)
// 나만 가지고 있고, 친구는 안 가지고 있는 포켓몬의 이름을 가져와 주세요.
SELECT A.name
FROM mypokemon AS A
LEFT JOIN friendpokemon AS B
ON A.name = B.name
WHERE B.name IS NULL;
9. 조건에 조건 더하기
- 서브쿼리
- 특징
- ⭐️ 하나의 쿼리 내 포함된 또 하나의 쿼리를 의미한다.
- 서브 쿼리는 반드시 괄호 안에 있어야 한다.
- SELECT, FROM, WHERE, HAVING, ORDER BY 절에서 사용 가능하다.
- 주의점
- ⭐️ SELECT 절의 서브 쿼리는 반드시 하나의 값이어야 한다.
- ⭐️ FROM 절의 서브 쿼리는 반드시 결과값이 하나의 테이블이어야 한다.
- ⭐️ WHERE 절의 서브 쿼리는 반드시 결과값이 칼럼이어야 한다. 그렇기에 중첩 서브 쿼리라고도 불린다.
- 특징
- 실습 예제 1. 서브쿼리로 복잡한 조건을 하나의 쿼리로 만들어 보자 (1)
// MISSION (1)
// 내 포켓몬 중에 몸무게가 가장 많이 나가는 포켓몬의 번호를 가져와 주세요.
SELECT number
FROM (SELECT number, rank() OVER (ORDER BY weight DESC) AS weight_rank
FROM ability)AS A
LIMIT 1;
// MISSION (2)
// 속도가 모든 전기 포켓몬의 공격력보다 하나라도 작은 포켓몬의 번호를 가져와 주세요.
SELECT number
FROM ability
WHERE speed < ALL(SELECT speed FROM ability WHERE type = 'electric');
// MISSION (3)
// 공격력이 방어력보다 큰 포켓몬이 있다면 모든 포켓몬의 이름을 가져와 주세요.
SELECT name
FROM mypokemon
WHERE EXISTS(SELECT *
FROM ability
WHERE attack > defense);- 실습 예제 2. 서브쿼리로 복잡한 조건을 하나의 쿼리로 만들어 보자 (2)
// MISSION (1)
// 이브이의 번호 133을 활용해서, 이브이의 영문 이름, 키, 몸무게를 가져와 주세요.
// 이 때, 키는 height, 몸무게는 weight이라는 별명으로 가져와 주세요.
SELECT name,
(SELECT height FROM ability WHERE number = 133) AS height,
(SELECT weight FROM ability WHERE number = 133) AS weight
FROM mypokemon
WHERE name = 'eevee'
// MISSION (2)
// 속도가 2번째로 빠른 포켓몬의 번호와 속도를 가져와 주세요.
SELECT A.number, A.speed
FROM (SELECT number, speed, rank() OVER (ORDER BY speed DESC) AS speed_rank
FROM ability)AS A
WHERE A.speed_rank = 2;
// MISSION (3)
// 방어력이 모든 전기 포켓몬의 방어력보다 큰 포켓몬의 이름을 가져와 주세요.
SELECT A.name
FROM mypokemon AS A
LEFT JOIN ability AS B
ON A.number = B.number
WHERE B.defense > ALL(SELECT defense FROM ability WHERE type = 'electric');
