◆ 목차
- MySQL - 7주차 이어서
- 실습 문제 풀기
1. MySQL - 조건 만들기
◆ IF
- 조건을 만들 때 사용하는 함수
■ IF 특징
- IF(조건식, 참일 때 값, 거짓일 때 값) 형식으로 사용
- SELECT 절에 사용하며 결과 값을 새로운 컬럼으로 반환
예제 1) 만약(IF) attack이 60보다 크거나 같다에 True면 strong 반환, False면 weak 반환
SELECT name, IF(attack >= 60, "strong", "weak") AS attack_class
FROM pokemon.mypokemon;
◆ IFNULL
- 데이터가 NULL인지 아닌지를 확인해 NULL이라면 새로운 값을 반환하는 함수
■ IFNULL 특징
- IFNULL([컬럼 이름], NULL일 때 값) 형식으로 사용
- 해당 컬럼의 값이 NULL인 row에서 NULL일 때 값을 반환
- SELECT 절에 사용하며 결과 값을 새로운 컬럼으로 반환
예제 2) 만약(IFNULL) name이 NULL이라면 unknown으로 반환
SELECT name, IFNULL(name, "unknown") AS full_name
FROM pokemon.mypokemon;
2. MySQL - 여러 조건 한번에 만들기
◆ CASE
- 조건을 여러 개 만들 때 사용하는 문자
■ CASE 특징
- ELSE 문장을 생략 시 NULL 값을 반환
방법 ①
CASE
WHEN 조건식1 THEN 결과값1
WHEN 조건식2 THEN 결과값2
ELSE 결과값3
END
방법 ②
CASE [컬럼 이름]
WHEN 조건식1 THEN 결과값1
WHEN 조건식2 THEN 결과값2
ELSE 결과값3
END
예제 3) attack이 1번 조건이 맞으면 1번 반환, 아닐 시 2번 조건 확인해서 맞으면 2번 반환, 아닐 시 3번 반환
SELECT
name,
CASE
WHEN attack >= 100 THEN 'very strong' -- 1번
WHEN attack >= 60 THEN 'strong' -- 2번
ELSE 'weak' -- 3번
END AS attack_class
FROM
pokemon.mypokemon;
예제 4) attack이 1번 조건이 맞으면 1번 반환, 아닐 시 2번 조건 확인해서 맞으면 2번 반환, 아닐 시 3번 반환
SELECT name, type,
CASE type
WHEN 'bug' THEN 'grass' -- 1번
WHEN 'electric' THEN 'water' -- 2번
WHEN 'grass' THEN 'bug' -- 3번
END AS rival_type
FROM pokemon.mypokemon;
3. MySQL - 함수 만들기
◆ 함수 만들기
- 함수 생성 시 주의점
-- 사용자 계정에 function create 권한 생성
SET GLOBAL log_bin_trust_function_reators = 1;
- 쿼리 문법
CREATE FUNCTION[함수 이름]([입력값 이름][데이터 타입], ...)
RETURNS[결과값 데이터 타입]
BEGIN
DECLARE[임시값 이름][데이터 타입];
SET[임시값 이름] = [입력값 이름];
쿼리;
RETURN 결과값
END
DELIMITER // -- 함수의 시작 지정
CREATE FUNCTION getAbility(attack INT, defense INT)
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE a INT;
DECLARE b INT;
DECLARE ability INT;
SET a = attack;
SET b = defense;
SELECT a + b INTO ability;
RETURN ability;
END
//
DELIMITER ; -- 함수의 끝 지정
→ 함수가 만들어 졌다.
4. MySQL - 테이블 합치기
◆ JOIN
- 같은 의미를 가지는 컬럼의 값을 기준으로 테이블을 합칠 때 사용하는 키워드
■ JOIN 종류
- INNER JOIN : 두 테이블 모두에 있는 값만 합치기
SELECT [컬럼 이름]
FROM [테이블A 이름]
INNER JOIN [테이블B 이름]
ON [테이블A 이름].[컬럼A 이름] = [테이블B 이름].[컬럼B 이름]
WHERE 조건식;
-- 비슷하게 JOIN을 빼고 "," 가능
SELECT [컬럼 이름]
FROM [테이블A 이름], [테이블B 이름]
WHERE [테이블A 이름].[컬럼A 이름] = [테이블B 이름].[컬럼B 이름];
예제 1)
SELECT * FROM mypokemon
INNER JOIN ability
ON mypokemon.number = ability.number;
- OUTER JOIN : 두 테이블에 있는 모든 값 합치기
→ LEFT (OUTER) JOIN + RIGHT (OUTER) JOIN을 합쳐서 사용 가능 = UNION으로 합친다.(중복값 제외)
SELECT [컬럼 이름]
FROM [테이블A 이름]
LEFT JOIN [테이블B 이름]
ON [테이블A 이름].[컬럼A 이름] = [테이블B 이름].[컬럼B 이름]
UNION
SELECT [컬럼 이름]
FROM [테이블A 이름]
RIGHT JOIN [테이블B 이름]
ON [테이블A 이름].[컬럼A 이름] = [테이블B 이름].[컬럼B 이름]
예제 2)
SELECT * FROM mypokemon
LEFT JOIN ability
ON mypokemon.number = ability.number
UNION
SELECT * FROM mypokemon
RIGHT JOIN ability
ON mypokemon.number = ability.number;
- LEFT JOIN(LEFT OUTER JOIN) : 왼쪽 테이블에 있는 값만 합치기
SELECT [컬럼 이름]
FROM [테이블A 이름]
LEFT JOIN [테이블B 이름]
ON [테이블A 이름].[컬럼A 이름] = [테이블B 이름].[컬럼B 이름]
WHERE 조건식;
예제 3)
SELECT * FROM mypokemon
LEFT JOIN ability
ON mypokemon.number = ability.number;
- RIGHT JOIN (RIGHT OUTER JOIN) : 오른쪽 테이블에 있는 값만 합치기
SELECT [컬럼 이름]
FROM [테이블A 이름]
RIGHT JOIN [테이블B 이름]
ON [테이블A 이름].[컬럼A 이름] = [테이블B 이름].[컬럼B 이름]
WHERE 조건식;
예제 4)
SELECT * FROM mypokemon
RIGHT JOIN ability
ON mypokemon.number = ability.number;
- CROSS JOIN : 두 테이블에 있는 모든 값을 각각 합치기
SELECT [컬럼 이름]
FROM [테이블A 이름]
CROSS JOIN [테이블B 이름]
WHERE 조건식;
예제 5)
SELECT * FROM mypokemon
CROSS JOIN ability;
- SELF JOIN : 같은 테이블에 있는 값 합치기
SELECT [컬럼 이름]
FROM [테이블A 이름] AS t1
INNER JOIN [테이블A 이름] AS t2
ON t1.[컬럼A 이름] = t2.[컬럼B 이름]
WHERE 조건식;
예제 6)
SELECT * FROM mypokemon AS t1
INNER JOIN mypokemon AS t2
ON t1.number = t2.number;
5. MySQL - 데이터에 데이터 더하기
◆ UNION 특징
- [쿼리 A] UNION [쿼리 B] 형식으로 사용
- [쿼리 A]와 [쿼리 B]의 결과 값을 합쳐서 보여줌
- 동일한 값은 제외하고 보여줌
SELECT [컬럼 이름]
FROM [테이블A 이름]
UNION
SELECT [컬럼 이름]
FROM [테이블B 이름];
◆ UNION ALL 특징
- [쿼리 A] UNION ALL [쿼리 B] 형식으로 사용
- 동일한 값도 포함하여 보여줌
SELECT [컬럼 이름]
FROM [테이블A 이름]
UNION ALL
SELECT [컬럼 이름]
FROM [테이블B 이름];
☞ 참고
- ORDER BY는 쿼리 가장 마지막에 가능, [쿼리 A]에서 가져온 컬럼으로만 가능
6. MySQL - 데이터에서 데이터 빼기
MySQL에서는 사용X
◆ 교집합(INTERSECT)
- 교집합을 확인하고 싶은 컬럼은 모두 다 기준으로 두고 합쳐 줘야 함
SELECT [컬럼 이름] FROM [테이블A 이름] AS A
INNER JOIN [테이블B 이름] AS B
ON A.[컬럼1 이름] = B.[컬럼1 이름]
AND.....AND A.[컬럼n 이름] = B.[컬럼n 이름];
→ 같은 이름을 가지는 컬럼이 존재한다면 SELECT 할 때 어떤 테이블에서 컬럼을 가져오는지 정확하게 명시
ex)
SELECT A.name -- A 컬럼의 name을 가져온다고 명시
FROM mypokemon AS A
INNER JOIN
friendpokemon AS B ON A.number = B.number AND A.name = B.name
AND A.type = B.type
AND A.attack = B.attack
AND A.defense = B.defense;
◆ 차집합(MINUS)
SELECT [컬럼 이름]
FROM [테이블A 이름] AS A
LEFT JOIN [테이블B 이름] AS B
ON A.[컬럼1 이름] = B.[컬럼1 이름] AND...AND A.[컬럼n 이름] = B.[컬럼n 이름]
WHERE B.[컬럼 이름] IS NULL;
ex) name이 null 인것(A = O, B = X)
SELECT A.name
FROM mypokemon AS A
LEFT JOIN friendpokemon AS B
ON A.name = B.name
WHERE B.name IS NULL;
7. MySQL - 조건에 조건 더하기
◆ 서브커리 특징
- 하나의 쿼리 내 포함된 또 하나의 쿼리
- 서브 쿼리는 반드시 괄호 안에 있어야 함
8. MySQL - SELECT 절의 서브 쿼리
◆ SELECT 절의 서브 쿼리
- 스칼라 서브쿼리
- SELECT 절의 서브 쿼리는 반드시 결과값이 하나의 값이여야 함
■ SELECT절의 서브 쿼리 문법
SELECT [컬럼 이름],
(SELECT [컬럼 이름]
FROM [테이블 이름]
WHERE 조건식)
FROM [테이블 이름]
WHERE 조건식;
예제 1) 피카츄의 번호, 영문 이름, 키를 가져오기
SELECT number,name,
(SELECT height
FROM ability
WHERE number = 25) AS height
FROM mypokemon
WHERE name = 'pikachu';
9. MySQL - FROM 절의 서브 쿼리
◆ FROM절의 서브 쿼리
- 인라인 뷰 서브커
- FROM절의 서브 쿼리는 반드시 결과값이 하나의 테이블이여야 함
- 서브 쿼리로 만든 테이블은 반드시 별명을 가져야
■ SELECT절의 서브 쿼리 문법
SELECT [컬럼 이름]
FROM (SELECT [컬럼 이름]
FROM [테이블 이름]
WHERE 조건식) AS [테이블 별명]
WHERE 조건식;
예제 1) 키 순위가 3순위인 포켓몬의 번호와 키 순위를 가져오기
SELECT number, height_rank
FROM (SELECT number, RANK() OVER(ORDER BY height DESC) AS height_rank
FROM ability) AS A
WHERE height_rank = 3;
10. MySQL - WHERE 절의 서브 쿼리
◆ WHERE절의 서브 쿼리
- 중첩 서브쿼리
- WHERE절의 서브 쿼리는 반드시 결과값이 하나의 컬럼 이여야 함
- 서브 쿼리로 만든 테이블은 반드시 별명을 가져야 함
- WHERE [컬럼 이름] [연산지] [서브 쿼리] 형식으로 사용
- 서브쿼리 안에 여러개의 return 값을 구할땐 WHERE [컬럼 이름] = 이 아닌 IN으로 사용
■ SELECT절의 서브 쿼리 문법
SELECT [컬럼 이름]
FROM [테이블 이름]
WHERE [컬럼 이름][연산자](SELECT [컬럼 이름]
FROM [테이블 이름]
WHERE 조건식);
◆ 서브 쿼리에 사용하는 연산자
■ 비교 연산자
연산자 | 활용 | 의미 |
= | A = [서브 쿼리] | A와 [서브 쿼리]의 결과값이 같다 |
!= | A != [서브쿼리] | A와 [서브 쿼리]의 결과값이 같지 않다 |
> | A > [서브 쿼리] | A와 [서브 쿼리]의 결과값보다 크다 |
>= | A >= [서브 쿼리] | A와 [서브 쿼리]의 결과값보다 크거나 작다 |
< | A < [서브 쿼리] | A가 [서브 쿼리]의 결과값보다 작다 |
<= | A <= [서브 쿼리] | A가 [서브 쿼리]의 결과값보다 작거나 같다 |
■ 주요 연산자
연산자 | 활용 | 의미 |
IN | A IN ([서브 쿼리]) | A가 [서브 쿼리]의 결과값이 내에 있다 |
ALL | A < ALL ([서브쿼리]) | A가 모든 [서브 쿼리]의 결과값보다 작다 |
A > ALL ([서브 쿼리]) | A가 모든 [서브 쿼리]의 결과값보다 크다 | |
ANY | A < ANY ([서브 쿼리]) | A가 [서브 쿼리]의 결과값보다 하나라도 작다 |
A > ANY ([서브 쿼리]) | A가 [서브 쿼리]의 결과값보다 하나라도 크다 | |
EXISTS | EXISTS ([서브 쿼리]) | [서브 쿼리]의 결과값이 존재한다. |
NOT EXISTS ([서브 쿼리]) | [서브 쿼리]의 결과값이 존재하지 않는다. |
예제 1) 키가 평균 키보다 작은 포켓몬의 번호를 가져오기
SELECT number
FROM ability
WHERE height < (SELECT AVG(height) FROM ability);
예제 2) 공격력이 모든 전기 포켓몬의 공격력보다 작은 포켓몬의 번호를 가져오기
SELECT number
FROM ability
WHERE attack < ALL(SELECT attack FROM ability WHERE type = "electric");
예제 3) 방어력이 모든 전기 포켓몬의 공격력보다 하나라도 큰 포켓몬의 번호를 가져오기
SELECT number
FROM ability
WHERE attack < ALL(SELECT attack FROM ability WHERE type = "electric");
예제 4) bug 타입 포켓몬이 있다면 모든 포켓몬의 번호를 가져오기
SELECT number
FROM ability
WHERE EXISTS(SELECT * FROM ability WHERE type = "bug");
11. MySQL - 데이터 삭제, 수정하기 고급
◆ DELETE
- 데이터 삭제하기
■ DELETE 문법
DELETE FROM [테이블 이름]
WHERE 조건식;
예제 1) attack 값이 50보다 큰것은 삭제
→ 삭제 전 테이블
→삭제 후
DELETE FROM pokemon.mypokemon
WHERE attack > 50;
◆ UPDATE
- 데이터 수정하기
■ UPDATE 문법
UPDATE [테이블 이름]
SET [컬럼 이름] = [새 값]
WHERE 조건식;
예제 2) chikoirita의 type을 normal로 수정
→ 수정 전 테이블
→수정 후
UPDATE pokemon.mypokemon
SET type = "normal"
WHERE name = "chikoirita";
12. MySQL - 제약 조건
◆ 제약 조건
- 테이터를 입력할 때 실행되는 데이터 입력 규칙
- 테이블을 만들거나 변경하면서 설정
(CREATE TABLE / ALTER TABLE 구문)
■ 제약 조건의 종류
제약 조건 | 의미 |
NOT NULL | 이 컬럼에는 NULL 값을 저장할 수 없다 |
UNIQUE | 이 컬럼의 값들은 서로 다른 값을 가져야 한다 |
DEFAULT | 이 컬럼에 입력값이 없을 시 기본값을 설정된다 |
PRIMARY KEY | 이 컬럼은 테이블의 기본 키다 NOT NULL과 UNIQUE 특징을 모두 가진다 |
FOREIGN KEY | 이 컬럼은 테이블의 외래 키이다 이 컬럼은 다른 테이블의 특징 컬럼을 참조한다 |
■ 제약 조건 넣고 테이블 만들
CREATE TABLE [테이블 이름](
[컬럼 이름][데이터 타입][제약 조건],
[컬럼 이름][데이터 타입][제약 조건],
....
);
예시 1)
CREATE TABLE new_mypokemon(
number INT PRIMARY KEY,
name VARCHAR(20) UNIQUE,
type VARCHAR(10) NOT NULL,
FOREIGN KEY(number) REFERENCES mypokemon(number)
13. MySQL - 권한과 DCL
◆ SQL 분류
분류 | 종류 | 의미 | |
DDL | Data Definition Language | CREATE ALTER DROP RENAME TRUNCATE |
데이터 정의어 |
DML | Data Manipulation Language | SELECT INSERT UPDATE DELETE |
데이터 조작어 |
DCL | Data Control Language | GRANT REVOKE |
데이터 제어어 |
TCL | Transaction Control Language | COMMIT ROLLBACK SAVEPOINT |
트랜잭션 제어어 |
■ 권한
- 사용자에 따라 데이터 접근 권한을 각각 줄 수 있음
■ 사용자 확인 쿼리 문법
① MySQL 기본 데이터베이스인 mysql 데이터베이스 선택
USE mysql;
② 사용자 목록 조회
SELECT user, host FROM user;
■ 사용자 생성 문법
CREATE USER [사용자 이름]@[ip주소];
-- 비밀번호와 함께 사용자 생성
CREATE USER [사용자 이름]@[ip주소]IDENTIFIED BY'[사용자 비밀번호]';
■ 사용자 삭제 문법
DROP USER [사용자 이름];
■ 권한 부여 문법
-- 권한 부여하기
GRANT [권한] ON [데이터베이스 이름].[테이블 이름] TO [사용자 이름]@[ip주소];
-- 권한 확인하기
SHOW GRANTS FOR [사용자 이름]@[ip주소];
-- 권한 삭제하기
REVOKE [권한] ON [데이터베이스 이름].[테이블 이름] FROM [사용자 이름]@[ip주소];
-- 권한 적용하기
FLUSH PRIVILEGES;
14. MySQL - 트랜잭션과 TCL
◆ 트랜잭션
- 데이터베이스의 데이터 상태를 바꾸는 작업 묶음
■ 트랜잭션 시작 문법
START TRANSACTION;
■ 트랜잭션 확정 문법
COMMIT;
■ 트랜잭션 이전으로 돌아가기 문법
ROLLBACK;
■ 세이브포인트 문법
-- 세이브포인트 만들기
SAVEPOINT [세이브포인트 이름];
-- 세이브포인트로 돌아가기
ROLLBACK TO [세이브포인트 이름];
15. MySQL - 실습 문제 풀기
함수를 만들고 사용해보자(CREATE FUNCTION)
■ 실습 1
공격력과 방어력의 합이 120보다 크면 'very strong', 90보다 크면 'strong', 모두 해당 되지 않으면 'not strong'를 반환하는 함수 'isStrong'을 만들고 사용
조건1 : attack과 defense를 입력값으로 사용
조건2 : 결과값 데이터 타입은 VARCHAR(20)으로 하기
※ 실행 쿼리
SELECT name, isStrong(attack, defense) AS isStrong
FROM mypokemon;
① 함수 만들기
-- 권한 생성
SET GLOBAL log_bin_trust_function_creators = 1;
-- DELIMITER // 사용하여 함수 시작 알리기
DELIMITER //
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
//
DELIMITER ;
다양한 방식으로 합쳐보자(JOIN)
■ 실습 1
mypokemon 테이블과 ability 테이블을 합쳐서 포켓몬 이름, 공격력, 방어력을 한번에 가져오기
단, mypokemon 테이블에 있는 모든 포켓몬의 데이터를 가져오고 포켓몬의 능력치 데이터를 구할 수 없다면 NULL을 가져와도 된다.
SELECT name, attack, defense FROM mypokemon
LEFT JOIN ability
ON mypokemon.number = ability.number;
■ 실습 2
mypokemon 테이블과 ability 테이블을 합쳐서 포켓몬 번호와 이름을 한번에 가져오기
단, ability 테이블에 있는 모든 포켓몬의 데이터를 가져오고 포켓몬의 이름 데이터를 구할 수 없다면 NULL을 가져와도 된다.
SELECT ability.number, name FROM mypokemon
RIGHT JOIN ability
ON mypokemon.number = ability.number;
여러 테이블의 데이터를 한번에 조회해보자(UNION)
■ 실습 1
내 포켓몬과 친구의 포켓몬에 어떤 타입들이 있는지 중복 제외하고 같은 타입은 한번씩만 가져오기
SELECT type FROM mypokemon
UNION
SELECT type FROM friendpokemon;
■ 실습 2
내 포켓몬과 친구의 포켓몬 중에 풀(grass) 타입 포켓몬들의 포켓몬 번호와 이름을 중복 포함해서 전부 다 가져오기
SELECT number, name FROM mypokemon
WHERE type = "grass"
UNION ALL
SELECT number, name FROM friendpokemon
WHERE type = "grass";
→ 누구의 포켓몬인지 결과를 더 자세히 알고 싶다면
SELECT number, name, "me"
FROM mypokemon
WHERE type = "grass"
UNION ALL
SELECT number, name, "friend's"
FROM friendpokemon
WHERE type = "grass";
서브쿼리로 복잡한 조건을 하나의 쿼리로 만들기(SubQuery)
■ 실습 1
내 포켓몬 중에 몸무게가 가장 많이 나가는 포켓몬의 번호를 가져오기
SELECT number
FROM ability
WHERE weight = (SELECT MAX(weight) FROM ability);
■ 실습 2
속도가 모든 전기 포켓몬의 공격력보다 하나라도 작은 포켓몬의 번호를 가져오기
SELECT number
FROM ability
WHERE speed < ANY(SELECT attack FROM ability WHERE type = "electric");
■ 실습 3
공격력이 방어력보다 큰 포켓몬이 있다면 모든 포켓몬의 이름을 가져오기
SELECT name
FROM mypokemon
WHERE EXISTS(SELECT * FROM ability WHERE attack > defense);
'데이터분석 부트캠프' 카테고리의 다른 글
데이터분석 부프캠프 14기 - 9주차_MySQL (2) | 2024.06.19 |
---|---|
데이터 관련 직무 (0) | 2024.06.12 |
데이터분석 부프캠프 14기 - 7주차_MySQL (1) | 2024.06.08 |
Python 프로젝트 (0) | 2024.06.05 |
데이터분석 부프캠프 14기 - 5주차 (0) | 2024.05.24 |